-- v3 教职工社团数据迁移至 v4。
-- 仅迁移可明确映射的数据；不伪造审核人、审核意见或没有来源的业务字段。
-- 执行要求：指定 zhgh_swufe 为默认库，且 mysql 客户端禁止使用 --force。

USE zhgh_swufe;

SET @migration_time = UNIX_TIMESTAMP() * 1000;
SET @backup_suffix = '20260813_v3_club';
SET @create_backup = (
  SELECT CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END
  FROM information_schema.TABLES
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'bak_sys_club_20260813_v3_club'
);

-- 本批次专属备份：保留清理前的业务表、社团角色和对应流程全链路。
CREATE TABLE IF NOT EXISTS bak_sys_club_20260813_v3_club AS SELECT * FROM sys_club WHERE 1 = 0;
INSERT INTO bak_sys_club_20260813_v3_club SELECT * FROM sys_club WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_club_user_20260813_v3_club AS SELECT * FROM club_user WHERE 1 = 0;
INSERT INTO bak_club_user_20260813_v3_club SELECT * FROM club_user WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_club_user_apply_20260813_v3_club AS SELECT * FROM club_user_apply WHERE 1 = 0;
INSERT INTO bak_club_user_apply_20260813_v3_club SELECT * FROM club_user_apply WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_club_pay_record_20260813_v3_club AS SELECT * FROM club_pay_record WHERE 1 = 0;
INSERT INTO bak_club_pay_record_20260813_v3_club SELECT * FROM club_pay_record WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_sys_club_sponsor_20260813_v3_club AS SELECT * FROM sys_club_sponsor WHERE 1 = 0;
INSERT INTO bak_sys_club_sponsor_20260813_v3_club SELECT * FROM sys_club_sponsor WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_sys_club_evaluate_20260813_v3_club AS SELECT * FROM sys_club_evaluate WHERE 1 = 0;
INSERT INTO bak_sys_club_evaluate_20260813_v3_club SELECT * FROM sys_club_evaluate WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_sys_club_examine_register_20260813_v3_club AS SELECT * FROM sys_club_examine_register WHERE 1 = 0;
INSERT INTO bak_sys_club_examine_register_20260813_v3_club SELECT * FROM sys_club_examine_register WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_sys_club_examine_register_detailed_20260813_v3_club AS SELECT * FROM sys_club_examine_register_detailed WHERE 1 = 0;
INSERT INTO bak_sys_club_examine_register_detailed_20260813_v3_club SELECT * FROM sys_club_examine_register_detailed WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_sys_club_manager_20260813_v3_club AS SELECT * FROM sys_club_manager WHERE 1 = 0;
INSERT INTO bak_sys_club_manager_20260813_v3_club SELECT * FROM sys_club_manager WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_sys_club_refresh_20260813_v3_club AS SELECT * FROM sys_club_refresh WHERE 1 = 0;
INSERT INTO bak_sys_club_refresh_20260813_v3_club SELECT * FROM sys_club_refresh WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_sys_club_rule_20260813_v3_club AS SELECT * FROM sys_club_rule WHERE 1 = 0;
INSERT INTO bak_sys_club_rule_20260813_v3_club SELECT * FROM sys_club_rule WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_sys_club_user_20260813_v3_club AS SELECT * FROM sys_club_user WHERE 1 = 0;
INSERT INTO bak_sys_club_user_20260813_v3_club SELECT * FROM sys_club_user WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_sys_club_user_back_history_20260813_v3_club AS SELECT * FROM sys_club_user_back_history WHERE 1 = 0;
INSERT INTO bak_sys_club_user_back_history_20260813_v3_club SELECT * FROM sys_club_user_back_history WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_sys_club_files_20260813_v3_club AS SELECT * FROM sys_club_files WHERE 1 = 0;
INSERT INTO bak_sys_club_files_20260813_v3_club SELECT * FROM sys_club_files WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_sys_user_role_club_20260813_v3_club AS SELECT * FROM sys_user_role WHERE 1 = 0;
INSERT INTO bak_sys_user_role_club_20260813_v3_club SELECT * FROM sys_user_role WHERE NULLIF(TRIM(clubId), '') IS NOT NULL AND @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_wf_process_instance_club_20260813_v3_club AS SELECT i.* FROM wf_process_instance i WHERE 1 = 0;
INSERT INTO bak_wf_process_instance_club_20260813_v3_club
SELECT i.* FROM wf_process_instance i JOIN wf_process_define d ON d.id = i.processDefineId
WHERE d.name IN ('XHZC','XHRH','XHTH','XHPY','XHNS','XHBGLS','XHHJBG','XHZCXD') AND @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_wf_process_task_club_20260813_v3_club AS SELECT * FROM wf_process_task WHERE 1 = 0;
INSERT INTO bak_wf_process_task_club_20260813_v3_club
SELECT t.* FROM wf_process_task t JOIN bak_wf_process_instance_club_20260813_v3_club i ON i.id = t.processInstanceId WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_wf_process_task_actor_club_20260813_v3_club AS SELECT * FROM wf_process_task_actor WHERE 1 = 0;
INSERT INTO bak_wf_process_task_actor_club_20260813_v3_club
SELECT a.* FROM wf_process_task_actor a JOIN bak_wf_process_task_club_20260813_v3_club t ON t.id = a.processTaskId WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_wf_process_cc_instance_club_20260813_v3_club AS SELECT * FROM wf_process_cc_instance WHERE 1 = 0;
INSERT INTO bak_wf_process_cc_instance_club_20260813_v3_club
SELECT c.* FROM wf_process_cc_instance c JOIN bak_wf_process_instance_club_20260813_v3_club i ON i.id = c.processInstanceId WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_outlay_manage_club_refs_20260813_v3_club AS
SELECT o.* FROM outlay_manage_club o JOIN sys_club c ON c.id = o.clubId WHERE @create_backup = 1;
CREATE TABLE IF NOT EXISTS bak_article_club_refs_20260813_v3_club AS
SELECT a.* FROM article a JOIN sys_club c ON c.id = a.clubId WHERE @create_backup = 1;

DROP TEMPORARY TABLE IF EXISTS tmp_club_guard;
CREATE TEMPORARY TABLE tmp_club_guard (name VARCHAR(64), actual BIGINT, expected BIGINT, bad INT AS (actual <> expected) STORED) ENGINE=InnoDB;
INSERT INTO tmp_club_guard(name, actual, expected) SELECT 'source_club_count', COUNT(*), 12 FROM union_swufe.sys_club;
INSERT INTO tmp_club_guard(name, actual, expected) SELECT 'source_valid_member_count', COUNT(*), 14 FROM union_swufe.sys_club_user WHERE clubid IS NOT NULL AND status = 5;
INSERT INTO tmp_club_guard(name, actual, expected) SELECT 'source_sponsor_count', COUNT(*), 2 FROM union_swufe.sys_club_sponsor;
INSERT INTO tmp_club_guard(name, actual, expected)
SELECT 'source_users_missing_v4', COUNT(*), 0
FROM union_swufe.sys_club_user s LEFT JOIN sys_user u
  ON CONVERT(u.id USING utf8mb4) = CONVERT(s.userid USING utf8mb4) COLLATE utf8mb4_0900_ai_ci
WHERE s.clubid IS NOT NULL AND s.status = 5 AND u.id IS NULL;
INSERT INTO tmp_club_guard(name, actual, expected)
SELECT 'source_club_contact_missing_v4', COUNT(*), 0
FROM union_swufe.sys_club s LEFT JOIN sys_user u
  ON CONVERT(u.id USING utf8mb4) = CONVERT(s.concatPerson USING utf8mb4) COLLATE utf8mb4_0900_ai_ci
WHERE NULLIF(TRIM(s.concatPerson), '') IS NOT NULL AND u.id IS NULL;

DROP TEMPORARY TABLE IF EXISTS tmp_club_guard_fail;
CREATE TEMPORARY TABLE tmp_club_guard_fail(id INT NOT NULL) ENGINE=InnoDB;
INSERT INTO tmp_club_guard_fail SELECT NULL FROM tmp_club_guard WHERE bad = 1 LIMIT 1;

START TRANSACTION;

-- 先删除流程子表，再删除社团业务表，避免残留无主任务和角色关系。
DELETE a FROM wf_process_task_actor a
JOIN wf_process_task t ON t.id = a.processTaskId
JOIN wf_process_instance i ON i.id = t.processInstanceId
JOIN wf_process_define d ON d.id = i.processDefineId
WHERE d.name IN ('XHZC','XHRH','XHTH','XHPY','XHNS','XHBGLS','XHHJBG','XHZCXD');
DELETE c FROM wf_process_cc_instance c
JOIN wf_process_instance i ON i.id = c.processInstanceId
JOIN wf_process_define d ON d.id = i.processDefineId
WHERE d.name IN ('XHZC','XHRH','XHTH','XHPY','XHNS','XHBGLS','XHHJBG','XHZCXD');
DELETE t FROM wf_process_task t
JOIN wf_process_instance i ON i.id = t.processInstanceId
JOIN wf_process_define d ON d.id = i.processDefineId
WHERE d.name IN ('XHZC','XHRH','XHTH','XHPY','XHNS','XHBGLS','XHHJBG','XHZCXD');
DELETE i FROM wf_process_instance i
JOIN wf_process_define d ON d.id = i.processDefineId
WHERE d.name IN ('XHZC','XHRH','XHTH','XHPY','XHNS','XHBGLS','XHHJBG','XHZCXD');

DELETE FROM sys_user_role WHERE NULLIF(TRIM(clubId), '') IS NOT NULL;
DELETE FROM club_pay_record;
DELETE FROM club_user_apply;
DELETE FROM club_user;
DELETE FROM sys_club_evaluate;
DELETE FROM sys_club_examine_register_detailed;
DELETE FROM sys_club_examine_register;
DELETE FROM sys_club_manager;
DELETE FROM sys_club_refresh;
DELETE FROM sys_club_rule;
DELETE FROM sys_club_sponsor;
DELETE FROM sys_club_user_back_history;
DELETE FROM sys_club_user;
DELETE FROM sys_club_files;
DELETE FROM sys_club;

-- v3 社团主数据：字段不对应的值保持为空，不做兜底拼装。
INSERT INTO sys_club(
  id, clubName, clubCode, clubType, concatPerson, concatPersonMobile, concatPersonEmail,
  introduce, purpose, createTime, foundTime, dismiss, due, files, establishReport,
  rulesFile, manageFile, yearPlanFile, userId, QQGroupCode, wechatGroupCode,
  replaceReport, createdBy, createdAt, updatedBy, updatedAt, delFlag,
  afterRulesFile, schoolReplyFile, state, ruleState, reportState
)
SELECT
  s.id, s.name, s.code,
  CASE s.type WHEN 'tyjj' THEN '体育竞技' WHEN 'whys' THEN '文化艺术' ELSE NULL END,
  s.concatPerson, s.concatPersonMobile, s.concatPersonEmail,
  s.stjs, s.stzc, s.create_time, s.foundTime, COALESCE(s.isjs, 0), s.dues_standard,
  NULL, NULL, NULL, NULL, NULL, s.userid,
  CAST(s.QQGroupCode AS CHAR), CAST(s.wechatGroupCode AS CHAR), NULL,
  s.userid,
  CASE WHEN STR_TO_DATE(s.create_time, '%Y-%m-%d %H:%i:%s') IS NULL THEN NULL ELSE UNIX_TIMESTAMP(STR_TO_DATE(s.create_time, '%Y-%m-%d %H:%i:%s')) * 1000 END,
  s.userid,
  CASE WHEN STR_TO_DATE(s.create_time, '%Y-%m-%d %H:%i:%s') IS NULL THEN NULL ELSE UNIX_TIMESTAMP(STR_TO_DATE(s.create_time, '%Y-%m-%d %H:%i:%s')) * 1000 END,
  0, NULL, NULL, CAST(s.state AS CHAR), s.ruleState, NULL
FROM union_swufe.sys_club s;

INSERT INTO sys_club_sponsor(id, clubId, sponsorId, createdBy, createdAt, updatedBy, updatedAt, delFlag)
SELECT id, clubId, sponsorId, sponsorId, @migration_time, sponsorId, @migration_time, 0
FROM union_swufe.sys_club_sponsor;

-- 正式成员仅迁移 clubId 明确且 status=5 的14条；2条无社团ID和1条待审核记录不强迁。
INSERT INTO club_user(id, clubId, userId, roleCode, clubPosition, email, avatar,
                      sameTimeJoinOtherClubSituation, awardsExperience,
                      createdBy, createdAt, updatedBy, updatedAt, delFlag)
SELECT s.id, s.clubid, s.userid,
       JSON_ARRAY(CASE s.sf
         WHEN 1 THEN 'CLUB_PRESIDENT'
         WHEN 2 THEN 'CLUB_MEMBER'
         WHEN 3 THEN 'CLUB_VICE_PRESIDENT'
         WHEN 4 THEN 'CLUB_SECRETARY'
         WHEN 5 THEN 'CLUB_VICE_SECRETARY'
       END),
       s.position, NULL, NULL, NULL, NULL,
       s.userid,
       CASE WHEN STR_TO_DATE(s.join_time, '%Y-%m-%d %H:%i:%s') IS NULL THEN @migration_time ELSE UNIX_TIMESTAMP(STR_TO_DATE(s.join_time, '%Y-%m-%d %H:%i:%s')) * 1000 END,
       s.userid, @migration_time, 0
FROM union_swufe.sys_club_user s
WHERE s.clubid IS NOT NULL AND s.status = 5 AND s.sf IN (1,2,3,4,5);

-- 保留旧兼容表可明确对应的字段，供现有统计和旧查询使用。
INSERT INTO sys_club_user(
  id, clubId, userId, joinTime, roleCode, payed, giveMoney, status, exitStatus,
  state, changeRoleCode, roleCodeChangeTime, auditList, exitAuditList, isNormal,
  changeTime, position, mode, email, avatar, sameTimeJoinOtherClubSituation,
  awardsExperience, createdBy, createdAt, updatedBy, updatedAt, delFlag
)
SELECT s.id, s.clubid, s.userid, s.join_time,
       CASE s.sf WHEN 1 THEN 'CLUB_PRESIDENT' WHEN 2 THEN 'CLUB_MEMBER'
                 WHEN 3 THEN 'CLUB_VICE_PRESIDENT' WHEN 4 THEN 'CLUB_SECRETARY'
                 WHEN 5 THEN 'CLUB_VICE_SECRETARY' END,
       s.isjf, s.giveMoney, s.status, NULL, s.state,
       CASE s.changeSf WHEN 1 THEN 'CLUB_PRESIDENT' WHEN 2 THEN 'CLUB_MEMBER'
                       WHEN 3 THEN 'CLUB_VICE_PRESIDENT' WHEN 4 THEN 'CLUB_SECRETARY'
                       WHEN 5 THEN 'CLUB_VICE_SECRETARY' END,
       s.sfChangeTime, s.auditList, NULL, s.isNormal, s.changeTime, s.position,
       TRUE, NULL, NULL, NULL, NULL, s.userid,
       CASE WHEN STR_TO_DATE(s.join_time, '%Y-%m-%d %H:%i:%s') IS NULL THEN @migration_time ELSE UNIX_TIMESTAMP(STR_TO_DATE(s.join_time, '%Y-%m-%d %H:%i:%s')) * 1000 END,
       s.userid, @migration_time, 0
FROM union_swufe.sys_club_user s
WHERE s.clubid IS NOT NULL AND s.status = 5 AND s.sf IN (1,2,3,4,5);

-- 社团角色必须按 v4 角色编码重新取 roleId，禁止复制 v3 角色ID。
INSERT INTO sys_user_role(roleId, userId, clubId)
SELECT r.id, s.userid, s.clubid
FROM union_swufe.sys_club_user s
JOIN sys_role r ON r.code = CASE s.sf
  WHEN 1 THEN 'CLUB_PRESIDENT' WHEN 2 THEN 'CLUB_MEMBER'
  WHEN 3 THEN 'CLUB_VICE_PRESIDENT' WHEN 4 THEN 'CLUB_SECRETARY'
  WHEN 5 THEN 'CLUB_VICE_SECRETARY' END
WHERE s.clubid IS NOT NULL AND s.status = 5 AND s.sf IN (1,2,3,4,5);

-- 旧系统没有 v4 流程表。12个社团均为930且审核ID为空，只创建已完成注册实例，
-- 用 legacyMigration/directPassNote 明确标记迁移事实，不生成虚假的审核任务、审核人和意见。
SET @xhzc_define_id = (SELECT id FROM wf_process_define WHERE name = 'XHZC' AND state = 1 AND delFlag = 0 ORDER BY version DESC, id DESC LIMIT 1);
INSERT INTO wf_process_instance(
  parentId, processDefineId, state, parentNodeName, businessNo, operator, expireTime,
  variable, createdBy, createdAt, updatedBy, updatedAt, delFlag
)
SELECT NULL, @xhzc_define_id, 20, NULL, s.id, s.userid, NULL,
       JSON_OBJECT(
         'f_data', JSON_OBJECT('id', s.id, 'clubName', s.name, 'clubCode', s.code,
                               'clubType', CASE s.type WHEN 'tyjj' THEN '体育竞技' WHEN 'whys' THEN '文化艺术' ELSE NULL END,
                               'concatPerson', s.concatPerson, 'foundTime', s.foundTime, 'createTime', s.create_time),
         'initiator', s.userid,
         'initiatorName', COALESCE(u.username, ''),
         'initiatorAccount', COALESCE(u.loginname, ''),
         'initiatorUnitId', COALESCE(u.unitId, ''),
         'initiatorUnitName', COALESCE(unit.name, ''),
         'submitType', 0,
         'legacyMigration', TRUE,
         'legacySource', 'union_swufe',
         'legacyState', s.state,
         'legacyProcess', '社团注册',
         'directPass', TRUE,
         'directPassNote', 'v3社团状态为930且审核ID为空，仅迁移为已完成实例，未伪造审核任务和审核意见'
       ),
       s.userid,
       CASE WHEN STR_TO_DATE(s.create_time, '%Y-%m-%d %H:%i:%s') IS NULL THEN @migration_time ELSE UNIX_TIMESTAMP(STR_TO_DATE(s.create_time, '%Y-%m-%d %H:%i:%s')) * 1000 END,
       s.userid, @migration_time, 0
FROM union_swufe.sys_club s
LEFT JOIN sys_user u ON CONVERT(u.id USING utf8mb4) = CONVERT(s.userid USING utf8mb4) COLLATE utf8mb4_0900_ai_ci
LEFT JOIN sys_unit unit ON unit.id = u.unitId
WHERE s.state = 930;

-- 原 v4 社团ID被经费/文章引用。仅同名可明确匹配的3个社团转换到v3 ID；
-- 其余无同名目标的数据保留业务记录，但将clubId置空，避免形成错误社团关联。
UPDATE outlay_manage_club o
JOIN bak_sys_club_20260813_v3_club oldc ON oldc.id = o.clubId
LEFT JOIN union_swufe.sys_club newc
  ON CONVERT(newc.name USING utf8mb4) = CONVERT(oldc.clubName USING utf8mb4) COLLATE utf8mb4_0900_ai_ci
SET o.clubId = newc.id;
UPDATE article a
JOIN bak_sys_club_20260813_v3_club oldc ON oldc.id = a.clubId
LEFT JOIN union_swufe.sys_club newc
  ON CONVERT(newc.name USING utf8mb4) = CONVERT(oldc.clubName USING utf8mb4) COLLATE utf8mb4_0900_ai_ci
SET a.clubId = newc.id;
-- 原库中少量clubId本来就无法关联旧社团主表；迁移后统一置空，禁止误连到新社团。
UPDATE outlay_manage_club o
LEFT JOIN sys_club c ON c.id = o.clubId
SET o.clubId = NULL
WHERE o.clubId IS NOT NULL AND c.id IS NULL;
UPDATE article a
LEFT JOIN sys_club c ON c.id = a.clubId
SET a.clubId = NULL
WHERE a.clubId IS NOT NULL AND c.id IS NULL;

DROP TEMPORARY TABLE IF EXISTS tmp_club_final_guard;
CREATE TEMPORARY TABLE tmp_club_final_guard(name VARCHAR(64), actual BIGINT, expected BIGINT, bad INT AS (actual <> expected) STORED) ENGINE=InnoDB;
INSERT INTO tmp_club_final_guard(name, actual, expected) SELECT 'target_club_count', COUNT(*), 12 FROM sys_club;
INSERT INTO tmp_club_final_guard(name, actual, expected) SELECT 'target_member_count', COUNT(*), 14 FROM club_user;
INSERT INTO tmp_club_final_guard(name, actual, expected) SELECT 'target_legacy_member_count', COUNT(*), 14 FROM sys_club_user;
INSERT INTO tmp_club_final_guard(name, actual, expected) SELECT 'target_sponsor_count', COUNT(*), 2 FROM sys_club_sponsor;
INSERT INTO tmp_club_final_guard(name, actual, expected) SELECT 'target_club_role_count', COUNT(*), 14 FROM sys_user_role WHERE NULLIF(TRIM(clubId), '') IS NOT NULL;
INSERT INTO tmp_club_final_guard(name, actual, expected)
SELECT 'target_register_instance_count', COUNT(*), 12
FROM wf_process_instance i JOIN wf_process_define d ON d.id = i.processDefineId
WHERE d.name = 'XHZC' AND i.state = 20;
INSERT INTO tmp_club_final_guard(name, actual, expected)
SELECT 'orphan_member_club', COUNT(*), 0 FROM club_user u LEFT JOIN sys_club c ON c.id = u.clubId WHERE c.id IS NULL;
INSERT INTO tmp_club_final_guard(name, actual, expected)
SELECT 'orphan_role_club', COUNT(*), 0 FROM sys_user_role r LEFT JOIN sys_club c ON c.id = r.clubId WHERE NULLIF(TRIM(r.clubId), '') IS NOT NULL AND c.id IS NULL;
INSERT INTO tmp_club_final_guard(name, actual, expected)
SELECT 'duplicate_member', COUNT(*), 0 FROM (SELECT clubId,userId,COUNT(*) cnt FROM club_user GROUP BY clubId,userId HAVING COUNT(*) > 1) d;

DROP TEMPORARY TABLE IF EXISTS tmp_club_final_guard_fail;
CREATE TEMPORARY TABLE tmp_club_final_guard_fail(id INT NOT NULL) ENGINE=InnoDB;
INSERT INTO tmp_club_final_guard_fail SELECT NULL FROM tmp_club_final_guard WHERE bad = 1 LIMIT 1;

COMMIT;

SELECT * FROM tmp_club_guard ORDER BY name;
SELECT * FROM tmp_club_final_guard ORDER BY name;