-- lq_md_target 归属字段：将误存的组织「名称」统一回填为 base_organize.F_Id
-- 背景：规范要求 F_BusinessUnit / F_TechDepartment / F_EducationDepartment / F_MajorProjectDepartment 存组织ID
-- 混存原因见脚本末尾说明

-- ============================================================
-- 1. 修复前检查（先看再改）
-- ============================================================

-- 1.1 各字段「名称」vs「ID」数量
SELECT
  SUM(CASE WHEN F_BusinessUnit REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS business_unit_id_cnt,
  SUM(CASE WHEN F_BusinessUnit IS NOT NULL AND F_BusinessUnit != '' AND F_BusinessUnit NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS business_unit_name_cnt,
  SUM(CASE WHEN F_TechDepartment REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS tech_id_cnt,
  SUM(CASE WHEN F_TechDepartment IS NOT NULL AND F_TechDepartment != '' AND F_TechDepartment NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS tech_name_cnt,
  SUM(CASE WHEN F_EducationDepartment REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS edu_id_cnt,
  SUM(CASE WHEN F_EducationDepartment IS NOT NULL AND F_EducationDepartment != '' AND F_EducationDepartment NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS edu_name_cnt,
  SUM(CASE WHEN F_MajorProjectDepartment REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS major_id_cnt,
  SUM(CASE WHEN F_MajorProjectDepartment IS NOT NULL AND F_MajorProjectDepartment != '' AND F_MajorProjectDepartment NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS major_name_cnt
FROM lq_md_target;

-- 1.2 按月份查看（dev 库 202608 曾出现 28 条名称）
SELECT
  F_Month,
  SUM(CASE WHEN F_BusinessUnit IS NOT NULL AND F_BusinessUnit != '' AND F_BusinessUnit NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS business_unit_name_cnt
FROM lq_md_target
GROUP BY F_Month
HAVING business_unit_name_cnt > 0
ORDER BY F_Month DESC;

-- 1.3 无法匹配到组织的名称（应为 0 行；有结果则先人工处理）
SELECT DISTINCT t.F_BusinessUnit AS val, 'F_BusinessUnit' AS field
FROM lq_md_target t
WHERE t.F_BusinessUnit IS NOT NULL AND t.F_BusinessUnit != ''
  AND t.F_BusinessUnit NOT REGEXP '^[0-9]{15,}$'
  AND NOT EXISTS (
    SELECT 1 FROM base_organize o
    WHERE o.F_FullName = t.F_BusinessUnit
      AND o.F_Category = 'department'
      AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
  )
UNION ALL
SELECT DISTINCT t.F_TechDepartment, 'F_TechDepartment'
FROM lq_md_target t
WHERE t.F_TechDepartment IS NOT NULL AND t.F_TechDepartment != ''
  AND t.F_TechDepartment NOT REGEXP '^[0-9]{15,}$'
  AND NOT EXISTS (
    SELECT 1 FROM base_organize o
    WHERE o.F_FullName = t.F_TechDepartment
      AND o.F_Category = 'department'
      AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
  )
UNION ALL
SELECT DISTINCT t.F_EducationDepartment, 'F_EducationDepartment'
FROM lq_md_target t
WHERE t.F_EducationDepartment IS NOT NULL AND t.F_EducationDepartment != ''
  AND t.F_EducationDepartment NOT REGEXP '^[0-9]{15,}$'
  AND NOT EXISTS (
    SELECT 1 FROM base_organize o
    WHERE o.F_FullName = t.F_EducationDepartment
      AND o.F_Category = 'department'
      AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
  )
UNION ALL
SELECT DISTINCT t.F_MajorProjectDepartment, 'F_MajorProjectDepartment'
FROM lq_md_target t
WHERE t.F_MajorProjectDepartment IS NOT NULL AND t.F_MajorProjectDepartment != ''
  AND t.F_MajorProjectDepartment NOT REGEXP '^[0-9]{15,}$'
  AND NOT EXISTS (
    SELECT 1 FROM base_organize o
    WHERE o.F_FullName = t.F_MajorProjectDepartment
      AND o.F_Category = 'department'
      AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
  );

-- 1.4 预览：名称 -> 将回填的 ID
SELECT
  t.F_Id,
  t.F_Month,
  t.F_StoreId,
  t.F_BusinessUnit AS old_business_unit,
  ob.F_Id AS new_business_unit,
  t.F_TechDepartment AS old_tech,
  ot.F_Id AS new_tech,
  t.F_EducationDepartment AS old_edu,
  oe.F_Id AS new_edu,
  t.F_MajorProjectDepartment AS old_major,
  om.F_Id AS new_major
FROM lq_md_target t
LEFT JOIN base_organize ob ON ob.F_FullName = t.F_BusinessUnit AND ob.F_Category = 'department' AND (ob.F_DeleteMark IS NULL OR ob.F_DeleteMark != 1)
LEFT JOIN base_organize ot ON ot.F_FullName = t.F_TechDepartment AND ot.F_Category = 'department' AND (ot.F_DeleteMark IS NULL OR ot.F_DeleteMark != 1)
LEFT JOIN base_organize oe ON oe.F_FullName = t.F_EducationDepartment AND oe.F_Category = 'department' AND (oe.F_DeleteMark IS NULL OR oe.F_DeleteMark != 1)
LEFT JOIN base_organize om ON om.F_FullName = t.F_MajorProjectDepartment AND om.F_Category = 'department' AND (om.F_DeleteMark IS NULL OR om.F_DeleteMark != 1)
WHERE (
  (t.F_BusinessUnit IS NOT NULL AND t.F_BusinessUnit != '' AND t.F_BusinessUnit NOT REGEXP '^[0-9]{15,}$')
  OR (t.F_TechDepartment IS NOT NULL AND t.F_TechDepartment != '' AND t.F_TechDepartment NOT REGEXP '^[0-9]{15,}$')
  OR (t.F_EducationDepartment IS NOT NULL AND t.F_EducationDepartment != '' AND t.F_EducationDepartment NOT REGEXP '^[0-9]{15,}$')
  OR (t.F_MajorProjectDepartment IS NOT NULL AND t.F_MajorProjectDepartment != '' AND t.F_MajorProjectDepartment NOT REGEXP '^[0-9]{15,}$')
)
ORDER BY t.F_Month DESC, t.F_StoreId;

-- ============================================================
-- 2. 执行回填（确认预览无误后再执行）
-- ============================================================

-- 2.1 事业部
UPDATE lq_md_target t
INNER JOIN base_organize o
  ON t.F_BusinessUnit = o.F_FullName
 AND o.F_Category = 'department'
 AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
SET t.F_BusinessUnit = o.F_Id
WHERE t.F_BusinessUnit IS NOT NULL
  AND t.F_BusinessUnit != ''
  AND t.F_BusinessUnit NOT REGEXP '^[0-9]{15,}$';

-- 2.2 科技部
UPDATE lq_md_target t
INNER JOIN base_organize o
  ON t.F_TechDepartment = o.F_FullName
 AND o.F_Category = 'department'
 AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
SET t.F_TechDepartment = o.F_Id
WHERE t.F_TechDepartment IS NOT NULL
  AND t.F_TechDepartment != ''
  AND t.F_TechDepartment NOT REGEXP '^[0-9]{15,}$';

-- 2.3 教育部
UPDATE lq_md_target t
INNER JOIN base_organize o
  ON t.F_EducationDepartment = o.F_FullName
 AND o.F_Category = 'department'
 AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
SET t.F_EducationDepartment = o.F_Id
WHERE t.F_EducationDepartment IS NOT NULL
  AND t.F_EducationDepartment != ''
  AND t.F_EducationDepartment NOT REGEXP '^[0-9]{15,}$';

-- 2.4 大项目部
UPDATE lq_md_target t
INNER JOIN base_organize o
  ON t.F_MajorProjectDepartment = o.F_FullName
 AND o.F_Category = 'department'
 AND (o.F_DeleteMark IS NULL OR o.F_DeleteMark != 1)
SET t.F_MajorProjectDepartment = o.F_Id
WHERE t.F_MajorProjectDepartment IS NOT NULL
  AND t.F_MajorProjectDepartment != ''
  AND t.F_MajorProjectDepartment NOT REGEXP '^[0-9]{15,}$';

-- ============================================================
-- 3. 修复后验证（名称应为 0）
-- ============================================================
SELECT
  SUM(CASE WHEN F_BusinessUnit IS NOT NULL AND F_BusinessUnit != '' AND F_BusinessUnit NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS business_unit_name_cnt,
  SUM(CASE WHEN F_TechDepartment IS NOT NULL AND F_TechDepartment != '' AND F_TechDepartment NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS tech_name_cnt,
  SUM(CASE WHEN F_EducationDepartment IS NOT NULL AND F_EducationDepartment != '' AND F_EducationDepartment NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS edu_name_cnt,
  SUM(CASE WHEN F_MajorProjectDepartment IS NOT NULL AND F_MajorProjectDepartment != '' AND F_MajorProjectDepartment NOT REGEXP '^[0-9]{15,}$' THEN 1 ELSE 0 END) AS major_name_cnt
FROM lq_md_target;

-- ============================================================
-- 混存原因说明（排查结论）
-- ============================================================
-- 1. 规范：归属字段应存 base_organize.F_Id（项目文档、日报/驾驶舱 SQL 均按 ID 关联）
-- 2. 历史数据：202601~202607 基本全是 ID；仅 202608 出现 28 条名称（dev 库实测）
-- 3. 直接原因：列表接口 EnrichExportNamesAsync 把 ID 转成名称返回给前端；
--    管理后台/门店 PC 编辑时直接用列表行回填表单，保存时把「事业一部」等名称写回库
-- 4. 后端 Create/Update 未做 ID 归一化，字符串原样入库
-- 5. 「同步上月」会原样复制上月值，若上月已是名称会继续扩散
--
-- 组织名称与 ID 对照（base_organize，department）：
-- 事业一部 734725158358484229 | 事业二部 734725192445592837 | 事业三部 734725229380633861
-- 事业四部 734725299018663173 | 事业五部 734725363116016901 | 事业六部 735107948883215621
-- 科技一部 734725579919590661 | 科技二部 734725628560934149
-- 教育一部 734725416912160005 | 教育二部 734725478136415493
-- 大项目一部 734725667203056901 | 大项目二部 734725746831918341