huike-back/tmp_htyws_org_backfill.sql

-- htyws 多租户 org_id 历史数据回填（仅回填 NULL）
-- 使用方式（moicen）:
-- psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f /path/to/tmp_htyws_org_backfill.sql

BEGIN;

-- 1) 先从已确定来源回填关系链：
--    jihua_course_section <- jihua
UPDATE jihua_course_section jcs
SET org_id = j.org_id
FROM jihua j
WHERE jcs.jihua_id = j.id
  AND jcs.org_id IS NULL
  AND j.org_id IS NOT NULL;

--    daka_course_section <- daka
UPDATE daka_course_section dcs
SET org_id = d.org_id
FROM daka d
WHERE dcs.daka_id = d.id
  AND dcs.org_id IS NULL
  AND d.org_id IS NOT NULL;

-- 2) 回填 lianxi.org_id（优先 jihua，再 daka）
UPDATE lianxi l
SET org_id = jcs.org_id
FROM jihua_course_section jcs
WHERE l.jihua_course_section_id = jcs.id
  AND l.org_id IS NULL
  AND jcs.org_id IS NOT NULL;

UPDATE lianxi l
SET org_id = dcs.org_id
FROM daka_course_section dcs
WHERE l.daka_course_section_id = dcs.id
  AND l.org_id IS NULL
  AND dcs.org_id IS NOT NULL;

-- 3) 回填 piyue / piyue_info
UPDATE piyue p
SET org_id = l.org_id
FROM lianxi l
WHERE p.lianxi_id = l.id
  AND p.org_id IS NULL
  AND l.org_id IS NOT NULL;

UPDATE piyue_info pi
SET org_id = p.org_id
FROM piyue p
WHERE pi.piyue_id = p.id
  AND pi.org_id IS NULL
  AND p.org_id IS NOT NULL;

-- 4) 回填 course_section.org_id
--    4.1 course_section <- jihua_course_section
UPDATE course_section cs
SET org_id = jcs.org_id
FROM jihua_course_section jcs
WHERE jcs.course_section_id = cs.id
  AND cs.org_id IS NULL
  AND jcs.org_id IS NOT NULL;

--    4.2 course_section <- daka_course_section
UPDATE course_section cs
SET org_id = dcs.org_id
FROM daka_course_section dcs
WHERE dcs.course_section_id = cs.id
  AND cs.org_id IS NULL
  AND dcs.org_id IS NOT NULL;

--    4.3 course_section <- course
UPDATE course_section cs
SET org_id = c.org_id
FROM course c
WHERE cs.course_id = c.id
  AND cs.org_id IS NULL
  AND c.org_id IS NOT NULL;

-- 5) 回填 course.org_id（来自已回填完的 section）
WITH section_org AS (
    SELECT cs.course_id, MIN(cs.org_id) AS resolved_org_id
    FROM course_section cs
    WHERE cs.course_id IS NOT NULL
      AND cs.org_id IS NOT NULL
    GROUP BY cs.course_id
)
UPDATE course c
SET org_id = so.resolved_org_id
FROM section_org so
WHERE c.id = so.course_id
  AND c.org_id IS NULL;

-- 6) 回填 jihua / daka（来自其关系表）
WITH jihua_rel_org AS (
    SELECT jcs.jihua_id, MIN(jcs.org_id) AS resolved_org_id
    FROM jihua_course_section jcs
    WHERE jcs.org_id IS NOT NULL
    GROUP BY jcs.jihua_id
)
UPDATE jihua j
SET org_id = jro.resolved_org_id
FROM jihua_rel_org jro
WHERE j.id = jro.jihua_id
  AND j.org_id IS NULL;

WITH daka_rel_org AS (
    SELECT dcs.daka_id, MIN(dcs.org_id) AS resolved_org_id
    FROM daka_course_section dcs
    WHERE dcs.org_id IS NOT NULL
    GROUP BY dcs.daka_id
)
UPDATE daka d
SET org_id = dro.resolved_org_id
FROM daka_rel_org dro
WHERE d.id = dro.daka_id
  AND d.org_id IS NULL;

COMMIT;

-- 回填后建议检查：
-- SELECT 'course' tbl, count(*) FROM course WHERE org_id IS NULL
-- UNION ALL SELECT 'course_section', count(*) FROM course_section WHERE org_id IS NULL
-- UNION ALL SELECT 'lianxi', count(*) FROM lianxi WHERE org_id IS NULL
-- UNION ALL SELECT 'piyue', count(*) FROM piyue WHERE org_id IS NULL
-- UNION ALL SELECT 'piyue_info', count(*) FROM piyue_info WHERE org_id IS NULL
-- UNION ALL SELECT 'jihua', count(*) FROM jihua WHERE org_id IS NULL
-- UNION ALL SELECT 'daka', count(*) FROM daka WHERE org_id IS NULL
-- UNION ALL SELECT 'jihua_course_section', count(*) FROM jihua_course_section WHERE org_id IS NULL
-- UNION ALL SELECT 'daka_course_section', count(*) FROM daka_course_section WHERE org_id IS NULL;