AiDOPWarehouse/server/Admin.NET.Web.Entry/UpdateScripts/1.0.412.sql

-- ===========================================================================
-- S0-LINESKILL-TENANT-UK-CLOSURE-1 —— 产线岗位（line-post）
--
-- 本脚本做两件互相独立的事：
--
-- 【1】MULTI-TENANT SCHEMA CORRECTNESS（主要目的）
--     LineSkillMaster / LineSkillDetail 的 UNIQUE 索引缺少 tenant_id：
--        IX_LineSkillMaster : Domain + Site + ProdLine + JOBNo
--        IX_LineSkillDetail : Domain + Site + ProdLine + JOBNo + SkillNo
--     两者都是「跨租户唯一」，一旦 Site 被启用（非 NULL），两个租户持有相同
--     (Domain, Site, ProdLine, JOBNo) 就会互相顶撞。这属于多租户索引缺陷，
--     必须补 tenant_id。
--
--     ⚠️ 这不是为了「让下面 22 行插得进去」。已实测证明：当前 Site 全为 NULL，
--     MySQL 的 UNIQUE 索引不对 NULL 做相等判定，因此即使不改索引，
--     11 master + 11 detail 也能插入成功（事务内实测 11/11 + 11/11，随后回滚）。
--     索引修复的唯一目的是 schema 正确性。
--
-- 【2】line-post 演示数据（22 行）
--     来源 AIDOP 797：仅取 ProdLine 命中目标租户 LineMaster 的 11 条产线；
--     其余 29 条因产线不存在于目标租户而丢弃，不为凑数复制。
--
-- Site 处理：保持 NULL。
--     族级证据显示 Site = 工作组/工作中心类历史列（EmpSkills.Site 注释「工作组」，
--     AIDOPB 实测取值为 数控组/冲压组 等；ProdLineDetail.Site 注释「工作中心编码」），
--     且 LineSkill 全库 296 行 Site 100% 为 NULL、无 UI、无校验。
--     因此新行沿用 NULL —— 不得写入租户/工厂/组织编码。
--
-- ⚠️ 事务性说明：MySQL 的 DDL 会隐式提交，且 AutoVersionUpdate 的
--     SqlScriptSplitter.Execute 不开启显式事务。因此本脚本**不是原子的**。
--     为此所有语句均写成可安全重入：
--       - DDL 以「索引是否已包含 tenant_id」为条件，已修则整段跳过；
--       - 数据以业务键 NOT EXISTS 兜底，重跑影响行数为 0。
-- ===========================================================================

SET @src := (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id = t.OrgId WHERE o.Code = 'AIDOP'       AND t.Status = 1 LIMIT 1);
SET @dst := (SELECT t.Id FROM SysTenant t JOIN SysOrg o ON o.Id = t.OrgId WHERE o.Code = 'UATTEST_CHL' AND t.Status = 1 LIMIT 1);


-- ---------------------------------------------------------------------------
-- 01 · 前置守卫：新 UK 定义下不得存在重复（否则不能建唯一索引）
--      重复则抛错中止，不带病改索引。
-- ---------------------------------------------------------------------------
SET @dup_m := (SELECT COUNT(*) FROM (
    SELECT tenant_id, Domain, Site, ProdLine, JOBNo
    FROM LineSkillMaster GROUP BY tenant_id, Domain, Site, ProdLine, JOBNo HAVING COUNT(*) > 1) x);

SET @dup_d := (SELECT COUNT(*) FROM (
    SELECT tenant_id, Domain, Site, ProdLine, JOBNo, SkillNo
    FROM LineSkillDetail GROUP BY tenant_id, Domain, Site, ProdLine, JOBNo, SkillNo HAVING COUNT(*) > 1) x);

SET @g := IF(@dup_m = 0 AND @dup_d = 0, 'DO 0',
    'SELECT RAISE_LINESKILL_UK_DUPLICATE_FOUND');
PREPARE gstmt FROM @g;
EXECUTE gstmt;
DEALLOCATE PREPARE gstmt;


-- ---------------------------------------------------------------------------
-- 02 · IX_LineSkillMaster → 补 tenant_id
--      条件：该索引存在且**不含** tenant_id 才重建；已含则整段 no-op（可重入）。
-- ---------------------------------------------------------------------------
SET @m_fixed := (SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillMaster'
      AND INDEX_NAME = 'IX_LineSkillMaster' AND COLUMN_NAME = 'tenant_id');

SET @m_exists := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillMaster'
      AND INDEX_NAME = 'IX_LineSkillMaster');

SET @s := IF(@m_fixed = 0 AND @m_exists > 0,
    'DROP INDEX IX_LineSkillMaster ON LineSkillMaster', 'DO 0');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @m_now := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillMaster'
      AND INDEX_NAME = 'IX_LineSkillMaster');

SET @s := IF(@m_now = 0,
    'CREATE UNIQUE INDEX IX_LineSkillMaster ON LineSkillMaster (tenant_id, Domain, Site, ProdLine, JOBNo)',
    'DO 0');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


-- ---------------------------------------------------------------------------
-- 03 · IX_LineSkillDetail → 补 tenant_id（同样条件化、可重入）
-- ---------------------------------------------------------------------------
SET @d_fixed := (SELECT COUNT(*) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillDetail'
      AND INDEX_NAME = 'IX_LineSkillDetail' AND COLUMN_NAME = 'tenant_id');

SET @d_exists := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillDetail'
      AND INDEX_NAME = 'IX_LineSkillDetail');

SET @s := IF(@d_fixed = 0 AND @d_exists > 0,
    'DROP INDEX IX_LineSkillDetail ON LineSkillDetail', 'DO 0');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @d_now := (SELECT COUNT(DISTINCT INDEX_NAME) FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'LineSkillDetail'
      AND INDEX_NAME = 'IX_LineSkillDetail');

SET @s := IF(@d_now = 0,
    'CREATE UNIQUE INDEX IX_LineSkillDetail ON LineSkillDetail (tenant_id, Domain, Site, ProdLine, JOBNo, SkillNo)',
    'DO 0');
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


-- ---------------------------------------------------------------------------
-- 04 · LineSkillMaster COPY 11
--      只取 ProdLine 命中目标租户 LineMaster 的产线（JOIN 即门禁）；
--      Site 保持 NULL；RecID 为 auto_increment，不赋值、不复制源主键。
-- ---------------------------------------------------------------------------
INSERT INTO LineSkillMaster
    (tenant_id, Domain, Site, ProdLine, JOBNo, JOBDescr, Orderby, WType, RunCrew, CreateTime, UpdateTime)
SELECT @dst, s.Domain, NULL, s.ProdLine, s.JOBNo, s.JOBDescr, s.Orderby, s.WType, s.RunCrew, NOW(), NOW()
FROM LineSkillMaster s
JOIN LineMaster l ON l.tenant_id = @dst AND l.Line = s.ProdLine
WHERE s.tenant_id = @src
  AND @src IS NOT NULL AND @dst IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM LineSkillMaster x
      WHERE x.tenant_id = @dst AND x.Domain = s.Domain
        AND x.ProdLine = s.ProdLine AND x.JOBNo = s.JOBNo AND x.Site IS NULL);


-- ---------------------------------------------------------------------------
-- 05 · LineSkillDetail COPY 11
--      ★ 内部 ID remap（禁止复制源主键 / 源 PersonSkillId）：
--        LineSkillRecID ← 目标租户刚插入的 master RecID（按业务键回查）
--        PersonSkillId  ← 目标租户 PersonSkill.Code = 源 SkillNo 的 Id
--        （源库该列指向的是他租户的 PersonSkill，属源侧既有缺陷，不得沿用）
-- ---------------------------------------------------------------------------
INSERT INTO LineSkillDetail
    (tenant_id, Domain, Site, ProdLine, JOBNo, SkillNo, LineSkillRecID, PersonSkillId,
     required_level, is_enabled, CreateTime, UpdateTime)
SELECT @dst, m.Domain, NULL, m.ProdLine, m.JOBNo, sd.SkillNo, m.RecID, p.Id,
       sd.required_level, 1, NOW(), NOW()
FROM LineSkillMaster m
JOIN LineSkillMaster sm
      ON sm.tenant_id = @src AND sm.Domain = m.Domain
     AND sm.ProdLine = m.ProdLine AND sm.JOBNo = m.JOBNo AND sm.Site IS NULL
JOIN LineSkillDetail sd
      ON sd.tenant_id = @src AND sd.LineSkillRecID = sm.RecID
JOIN PersonSkill p
      ON p.tenant_id = @dst AND p.Code = sd.SkillNo
WHERE m.tenant_id = @dst AND m.Site IS NULL
  AND @src IS NOT NULL AND @dst IS NOT NULL
  AND NOT EXISTS (
      SELECT 1 FROM LineSkillDetail d
      WHERE d.tenant_id = @dst AND d.LineSkillRecID = m.RecID AND d.PersonSkillId = p.Id);