DEV-MERGE-LEGACY-BACKEND-2026/internal/academic/dualrun/fixture/batch3_verify.go

package fixture

import (
	"context"
	"database/sql"
	"fmt"
	"sort"
	"strconv"
	"strings"
)

// 本文件是第三批的**核对**：逐列 + 机构集合 + 引用完整性（孤儿=0）+ R17 + bit(1)。
//
// 期望值来源与前三批一致：机构/校区字段由 **MySQL 服务端**取值（`self_support+0`、
// `CAST(...)`），不由映射代码产生；只有 `status` 的 active/disabled 是规则翻译，
// 单独作为断言说明（并在映射里对非法值报错）。

// OrgVerifyReport 是第三批核对结果。
type OrgVerifyReport struct {
	Options    OrgOptions
	Cols       []ColumnAgg
	Assertions []Assertion
	RefStats   []RefStat
	CrossOrg   []CrossOrgStat       // 跨机构三类统计（A 课程级 / B 记录级 / C 记录 vs 课程）
	Disabled   []DisabledCampusStat // 停用校区（status=0）的影响面
	TargetInst int64
	TargetCamp int64
	Warnings   []string
}

// FailCount 统计不一致列 + 失败断言。
func (r *OrgVerifyReport) FailCount() int {
	n := 0
	for _, c := range r.Cols {
		if !c.OK() {
			n++
		}
	}
	for _, a := range r.Assertions {
		if !a.Pass {
			n++
		}
	}
	return n
}

// OK 表示整轮通过。
func (r *OrgVerifyReport) OK() bool { return r.FailCount() == 0 }

// VerifyOrg 执行第三批核对。
func VerifyOrg(ctx context.Context, cfg Config, opts OrgOptions) (*OrgVerifyReport, error) {
	src, err := cfg.OpenOrgSource()
	if err != nil {
		return nil, err
	}
	defer src.Close()
	legacy, err := cfg.OpenLegacy() // finance_bk：引用完整性统计用
	if err != nil {
		return nil, err
	}
	defer legacy.Close()
	target, err := cfg.OpenTarget()
	if err != nil {
		return nil, err
	}
	defer target.Close()

	rep := &OrgVerifyReport{Options: opts}

	// ---------- 1. t_institution 逐列 ----------
	expSchools, err := extractSchools(ctx, src, opts.InstitutionIDs)
	if err != nil {
		return nil, err
	}
	cID := newCol("t_institution", "institution_id", "D1：与 t_school.id 完全相同")
	cName := newCol("t_institution", "institution_name", "t_school.name 原样")
	cCode := newCol("t_institution", "institution_code", "legacy 无此列 → 必须 NULL")
	cAddr := newCol("t_institution", "address", "legacy 无此列 → 必须 NULL")
	cPhone := newCol("t_institution", "phone", "legacy 无此列 → 必须 NULL")
	cStatus := newCol("t_institution", "status", "tinyint 1→active / 0→disabled（规则翻译）")
	cCreated := newCol("t_institution", "created_at", "legacy 无时间列 → epoch 占位")
	cUpdated := newCol("t_institution", "updated_at", "legacy 无时间列 → epoch 占位")

	var legacyIDs []int64
	for _, s := range expSchools {
		legacyIDs = append(legacyIDs, s.ID)
		rowKey := "id=" + strconv.FormatInt(s.ID, 10)
		var got struct {
			ID      int64
			Name    string
			Code    sql.NullString
			Addr    sql.NullString
			Phone   sql.NullString
			Status  string
			Created sql.NullString
			Updated sql.NullString
		}
		err := target.QueryRowContext(ctx, `
SELECT institution_id, institution_name, institution_code, address, phone, status,
       DATE_FORMAT(created_at,'%Y-%m-%d %H:%i:%s'), DATE_FORMAT(updated_at,'%Y-%m-%d %H:%i:%s')
  FROM t_institution WHERE institution_id = ?`, s.ID).Scan(
			&got.ID, &got.Name, &got.Code, &got.Addr, &got.Phone, &got.Status, &got.Created, &got.Updated)
		if err == sql.ErrNoRows {
			cID.cmp(rowKey, strconv.FormatInt(s.ID, 10), "<缺失>", false)
			continue
		}
		if err != nil {
			return nil, err
		}
		wantStatus := "active"
		if s.Status == 0 {
			wantStatus = "disabled"
		}
		cID.cmp(rowKey, strconv.FormatInt(s.ID, 10), strconv.FormatInt(got.ID, 10), got.ID == s.ID)
		cName.cmp(rowKey, s.Name, got.Name, got.Name == s.Name)
		cCode.cmp(rowKey, "NULL", nullStrOrNull(got.Code), !got.Code.Valid)
		cAddr.cmp(rowKey, "NULL", nullStrOrNull(got.Addr), !got.Addr.Valid)
		cPhone.cmp(rowKey, "NULL", nullStrOrNull(got.Phone), !got.Phone.Valid)
		cStatus.cmp(rowKey, fmt.Sprintf("%d→%s", s.Status, wantStatus), got.Status, got.Status == wantStatus)
		cCreated.cmp(rowKey, LegacyMissingTime[:10], nullStrOrNull(got.Created),
			strings.HasPrefix(nullStrOrNull(got.Created), LegacyMissingTime[:10]))
		cUpdated.cmp(rowKey, LegacyMissingTime[:10], nullStrOrNull(got.Updated),
			strings.HasPrefix(nullStrOrNull(got.Updated), LegacyMissingTime[:10]))
	}
	rep.Cols = append(rep.Cols, cID.done(), cName.done(), cCode.done(), cAddr.done(),
		cPhone.done(), cStatus.done(), cCreated.done(), cUpdated.done())

	// ---------- 2. 机构集合断言 ----------
	var gotIDs []int64
	rows, err := target.QueryContext(ctx, `SELECT institution_id FROM t_institution ORDER BY institution_id`)
	if err != nil {
		return nil, err
	}
	for rows.Next() {
		var id int64
		if err := rows.Scan(&id); err != nil {
			rows.Close()
			return nil, err
		}
		gotIDs = append(gotIDs, id)
	}
	rows.Close()
	rep.TargetInst = int64(len(gotIDs))
	want := append([]int64(nil), opts.InstitutionIDs...)
	sort.Slice(want, func(i, j int) bool { return want[i] < want[j] })
	rep.Assertions = append(rep.Assertions, Assertion{
		Name: "机构集合精确等于期望集合（不多不少）",
		Pass: int64SliceEqual(gotIDs, want),
		Detail: fmt.Sprintf("target t_institution = %v（%d 行）；期望 = %v（%d 行）",
			gotIDs, len(gotIDs), want, len(want)),
	})

	// ---------- 3. t_campus 逐列 ----------
	expCampuses, err := extractCampuses(ctx, src, opts.InstitutionIDs)
	if err != nil {
		return nil, err
	}
	if !opts.WithAllCampuses {
		refd, err := extractReferencedCampuses(ctx, legacy)
		if err != nil {
			return nil, err
		}
		var kept []LegacyCampus
		for _, c := range expCampuses {
			if refd[c.ID] {
				kept = append(kept, c)
			}
		}
		expCampuses = kept
	}

	cCampusID := newCol("t_campus", "campus_id", "D1：与 t_school_campus.id 完全相同")
	cInstID := newCol("t_campus", "institution_id", "R17：如实保留 school_id（不修正跨机构校区）")
	cCampusName := newCol("t_campus", "campus_name", "name 原样")
	cCampusCode := newCol("t_campus", "campus_code", "code → campus_code（NULL 保留）")
	cCampusAddr := newCol("t_campus", "address", "legacy 无此列 → 必须 NULL")
	cCampusPhone := newCol("t_campus", "phone", "legacy 无此列 → 必须 NULL")
	cCampusStatus := newCol("t_campus", "status", "tinyint 1→active / 0→disabled")
	cSelfSupport := newCol("t_campus", "self_support", "bit(1) → TINYINT(1)，逐行相等")
	cCampusCreated := newCol("t_campus", "created_at", "epoch 占位")
	cCampusUpdated := newCol("t_campus", "updated_at", "epoch 占位")

	campusOwner := map[int64]int64{}
	var gotCampusIDs []int64
	for _, c := range expCampuses {
		campusOwner[c.ID] = c.SchoolID
		rowKey := "id=" + strconv.FormatInt(c.ID, 10)
		var got struct {
			ID, InstID int64
			Name       string
			Code       sql.NullString
			Addr       sql.NullString
			Phone      sql.NullString
			Status     string
			SelfSup    int8
			Created    sql.NullString
			Updated    sql.NullString
		}
		err := target.QueryRowContext(ctx, `
SELECT campus_id, institution_id, campus_name, campus_code, address, phone, status,
       self_support, DATE_FORMAT(created_at,'%Y-%m-%d %H:%i:%s'), DATE_FORMAT(updated_at,'%Y-%m-%d %H:%i:%s')
  FROM t_campus WHERE campus_id = ?`, c.ID).Scan(
			&got.ID, &got.InstID, &got.Name, &got.Code, &got.Addr, &got.Phone, &got.Status,
			&got.SelfSup, &got.Created, &got.Updated)
		if err == sql.ErrNoRows {
			cCampusID.cmp(rowKey, strconv.FormatInt(c.ID, 10), "<缺失>", false)
			continue
		}
		if err != nil {
			return nil, err
		}
		gotCampusIDs = append(gotCampusIDs, got.ID)
		wantStatus := "active"
		if c.Status == 0 {
			wantStatus = "disabled"
		}
		cCampusID.cmp(rowKey, strconv.FormatInt(c.ID, 10), strconv.FormatInt(got.ID, 10), got.ID == c.ID)
		cInstID.cmp(rowKey, strconv.FormatInt(c.SchoolID, 10), strconv.FormatInt(got.InstID, 10), got.InstID == c.SchoolID)
		cCampusName.cmp(rowKey, c.Name, got.Name, got.Name == c.Name)
		cCampusCode.cmp(rowKey, nullStrOrNull(c.Code), nullStrOrNull(got.Code), nullStrOrNull(c.Code) == nullStrOrNull(got.Code))
		cCampusAddr.cmp(rowKey, "NULL", nullStrOrNull(got.Addr), !got.Addr.Valid)
		cCampusPhone.cmp(rowKey, "NULL", nullStrOrNull(got.Phone), !got.Phone.Valid)
		cCampusStatus.cmp(rowKey, fmt.Sprintf("%d→%s", c.Status, wantStatus), got.Status, got.Status == wantStatus)
		cSelfSupport.cmp(rowKey, strconv.Itoa(int(c.SelfSupport)), strconv.Itoa(int(got.SelfSup)),
			got.SelfSup == c.SelfSupport)
		cCampusCreated.cmp(rowKey, LegacyMissingTime[:10], nullStrOrNull(got.Created),
			strings.HasPrefix(nullStrOrNull(got.Created), LegacyMissingTime[:10]))
		cCampusUpdated.cmp(rowKey, LegacyMissingTime[:10], nullStrOrNull(got.Updated),
			strings.HasPrefix(nullStrOrNull(got.Updated), LegacyMissingTime[:10]))
	}
	rep.Cols = append(rep.Cols, cCampusID.done(), cInstID.done(), cCampusName.done(),
		cCampusCode.done(), cCampusAddr.done(), cCampusPhone.done(), cCampusStatus.done(),
		cSelfSupport.done(), cCampusCreated.done(), cCampusUpdated.done())
	rep.TargetCamp = int64(len(gotCampusIDs))

	// ---------- 4. campus_id = 0 不得建行 ----------
	var zeroCampus int
	if err := target.QueryRowContext(ctx, `SELECT COUNT(*) FROM t_campus WHERE campus_id = 0`).Scan(&zeroCampus); err != nil {
		return nil, err
	}
	rep.Assertions = append(rep.Assertions, Assertion{
		Name:   "t_campus 不建 campus_id=0 的行（0 是机构级约定值）",
		Pass:   zeroCampus == 0,
		Detail: fmt.Sprintf("t_campus 里 campus_id=0 的行数 = %d（期望 0）", zeroCampus),
	})

	// ---------- 5. R17 ----------
	r17OK := true
	var r17Detail []string
	for _, cid := range []int64{1, 2} {
		var inst sql.NullInt64
		err := target.QueryRowContext(ctx, `SELECT institution_id FROM t_campus WHERE campus_id = ?`, cid).Scan(&inst)
		if err != nil {
			r17OK = false
			r17Detail = append(r17Detail, fmt.Sprintf("campus %d 读不到：%v", cid, err))
			continue
		}
		ok := inst.Valid && inst.Int64 == 1
		if !ok {
			r17OK = false
		}
		r17Detail = append(r17Detail, fmt.Sprintf("campus %d institution_id=%d（期望 1）", cid, inst.Int64))
	}
	var inst1 int
	if err := target.QueryRowContext(ctx, `SELECT COUNT(*) FROM t_institution WHERE institution_id = 1`).Scan(&inst1); err != nil {
		return nil, err
	}
	rep.Assertions = append(rep.Assertions, Assertion{
		Name:   "R17：campus 1/2 的 institution_id = 1，且 t_institution(1) 存在",
		Pass:   r17OK && inst1 == 1,
		Detail: strings.Join(r17Detail, "；") + fmt.Sprintf("；t_institution(1) 行数=%d", inst1),
	})

	// ---------- 6. 引用完整性（每个来源逐个统计孤儿） ----------
	for _, s := range orgRefSources() {
		st := RefStat{Source: s.Table}
		if err := legacy.QueryRowContext(ctx, fmt.Sprintf("SELECT COUNT(*) FROM `%s`", s.Table)).Scan(&st.Rows); err != nil {
			return nil, err
		}
		if err := legacy.QueryRowContext(ctx, fmt.Sprintf(
			"SELECT COUNT(DISTINCT `%s`) FROM `%s`", s.InstCol, s.Table)).Scan(&st.DistinctInst); err != nil {
			return nil, err
		}
		// 孤儿机构
		orphanInstSQL := fmt.Sprintf(`
SELECT COUNT(*) FROM `+"`%s`"+` s
  LEFT JOIN %s.t_institution i ON i.institution_id = s.`+"`%s`"+`
 WHERE i.institution_id IS NULL`, s.Table, cfg.TargetDB, s.InstCol)
		if err := legacy.QueryRowContext(ctx, orphanInstSQL).Scan(&st.OrphanInst); err != nil {
			return nil, fmt.Errorf("%s 孤儿机构统计失败：%w", s.Table, err)
		}
		if st.OrphanInst > 0 {
			rows, err := legacy.QueryContext(ctx, fmt.Sprintf(`
SELECT DISTINCT s.`+"`%s`"+` FROM `+"`%s`"+` s
  LEFT JOIN %s.t_institution i ON i.institution_id = s.`+"`%s`"+`
 WHERE i.institution_id IS NULL ORDER BY 1`, s.InstCol, s.Table, cfg.TargetDB, s.InstCol))
			if err == nil {
				for rows.Next() {
					var v int64
					_ = rows.Scan(&v)
					st.OrphanInstList = append(st.OrphanInstList, strconv.FormatInt(v, 10))
				}
				rows.Close()
			}
		}
		if s.CampusCol != "" {
			if err := legacy.QueryRowContext(ctx, fmt.Sprintf(
				"SELECT COUNT(DISTINCT `%s`) FROM `%s` WHERE `%s` <> 0", s.CampusCol, s.Table, s.CampusCol)).Scan(&st.DistinctCampus); err != nil {
				return nil, err
			}
			if err := legacy.QueryRowContext(ctx, fmt.Sprintf(
				"SELECT COUNT(*) FROM `%s` WHERE `%s` = 0", s.Table, s.CampusCol)).Scan(&st.CampusZeroRows); err != nil {
				return nil, err
			}
			orphanCampusSQL := fmt.Sprintf(`
SELECT COUNT(*) FROM `+"`%s`"+` s
  LEFT JOIN %s.t_campus c ON c.campus_id = s.`+"`%s`"+`
 WHERE s.`+"`%s`"+` <> 0 AND c.campus_id IS NULL`, s.Table, cfg.TargetDB, s.CampusCol, s.CampusCol)
			if err := legacy.QueryRowContext(ctx, orphanCampusSQL).Scan(&st.OrphanCampus); err != nil {
				return nil, fmt.Errorf("%s 孤儿校区统计失败：%w", s.Table, err)
			}
			if st.OrphanCampus > 0 {
				rows, err := legacy.QueryContext(ctx, fmt.Sprintf(`
SELECT DISTINCT s.`+"`%s`"+` FROM `+"`%s`"+` s
  LEFT JOIN %s.t_campus c ON c.campus_id = s.`+"`%s`"+`
 WHERE s.`+"`%s`"+` <> 0 AND c.campus_id IS NULL ORDER BY 1`, s.CampusCol, s.Table, cfg.TargetDB, s.CampusCol, s.CampusCol))
				if err == nil {
					for rows.Next() {
						var v int64
						_ = rows.Scan(&v)
						st.OrphanCampusList = append(st.OrphanCampusList, strconv.FormatInt(v, 10))
					}
					rows.Close()
				}
			}
		}
		rep.RefStats = append(rep.RefStats, st)
	}

	totalOrphanInst, totalOrphanCampus := int64(0), int64(0)
	var orphanDetail []string
	for _, st := range rep.RefStats {
		totalOrphanInst += st.OrphanInst
		totalOrphanCampus += st.OrphanCampus
		if st.OrphanInst > 0 {
			orphanDetail = append(orphanDetail, fmt.Sprintf("%s 孤儿机构 %d 行 %v", st.Source, st.OrphanInst, st.OrphanInstList))
		}
		if st.OrphanCampus > 0 {
			orphanDetail = append(orphanDetail, fmt.Sprintf("%s 孤儿校区 %d 行 %v", st.Source, st.OrphanCampus, st.OrphanCampusList))
		}
	}
	detail := fmt.Sprintf("8 个来源合计：孤儿机构 %d 行、孤儿校区 %d 行（期望都为 0）", totalOrphanInst, totalOrphanCampus)
	if len(orphanDetail) > 0 {
		detail += "；" + strings.Join(orphanDetail, "；")
	}
	rep.Assertions = append(rep.Assertions, Assertion{
		Name:   "引用完整性：数据里出现过的每个 institution_id / campus_id 都能在 target 找到（孤儿 = 0）",
		Pass:   totalOrphanInst == 0 && totalOrphanCampus == 0,
		Detail: detail,
	})

	// ---------- 7. 跨机构：三个来源分别统计 ----------
	rep.CrossOrg = append(rep.CrossOrg, crossOrgCourseLevel(ctx, legacy)...)
	rep.CrossOrg = append(rep.CrossOrg, crossOrgRecordLevel(ctx, legacy)...)
	rep.CrossOrg = append(rep.CrossOrg, crossOrgRecordVsCourse(ctx, legacy)...)

	// ---------- 8. 停用校区（status=0）的影响面 + 断言 ----------
	disabled, err := disabledCampusStats(ctx, legacy, opts)
	if err != nil {
		return nil, err
	}
	rep.Disabled = disabled

	var wantDisabled []int64
	var gotDisabled []int64
	for _, d := range disabled {
		wantDisabled = append(wantDisabled, d.CampusID)
	}
	rowsD, err := target.QueryContext(ctx,
		`SELECT campus_id FROM t_campus WHERE status = 'disabled' ORDER BY campus_id`)
	if err != nil {
		return nil, err
	}
	for rowsD.Next() {
		var id int64
		if err := rowsD.Scan(&id); err != nil {
			rowsD.Close()
			return nil, err
		}
		gotDisabled = append(gotDisabled, id)
	}
	rowsD.Close()
	rep.Assertions = append(rep.Assertions, Assertion{
		Name: "停用校区数量与 id 精确匹配（status=disabled 的必须恰好是 legacy status=0 的那批）",
		Pass: int64SliceEqual(gotDisabled, wantDisabled) && len(wantDisabled) > 0,
		Detail: fmt.Sprintf("target t_campus WHERE status='disabled' → %v（%d 个）；legacy status=0 → %v（%d 个）",
			gotDisabled, len(gotDisabled), wantDisabled, len(wantDisabled)),
	})
	// 逐行再确认一次：这 8 个校区的 status 没有被"为了方便"改成 active
	badActive := 0
	for _, id := range wantDisabled {
		var st string
		if err := target.QueryRowContext(ctx, `SELECT status FROM t_campus WHERE campus_id = ?`, id).Scan(&st); err != nil {
			return nil, err
		}
		if st != "disabled" {
			badActive++
		}
	}
	rep.Assertions = append(rep.Assertions, Assertion{
		Name:   "停用校区未被改成 active（不允许为了\"让数据可见\"篡改 status）",
		Pass:   badActive == 0,
		Detail: fmt.Sprintf("%d 个 legacy status=0 的校区里，target 落成非 disabled 的个数 = %d（期望 0）", len(wantDisabled), badActive),
	})
	return rep, nil
}

// SnapshotOrg 采集 target 两张表的 (id → 名称) 快照，用于幂等比对的「id 不变」判定。
func SnapshotOrg(ctx context.Context, cfg Config) (map[int64]string, map[int64]string, error) {
	db, err := cfg.OpenTarget()
	if err != nil {
		return nil, nil, err
	}
	defer db.Close()
	inst := map[int64]string{}
	camp := map[int64]string{}
	rows, err := db.QueryContext(ctx, `SELECT institution_id, institution_name FROM t_institution`)
	if err != nil {
		return nil, nil, err
	}
	for rows.Next() {
		var id int64
		var n string
		if err := rows.Scan(&id, &n); err != nil {
			rows.Close()
			return nil, nil, err
		}
		inst[id] = n
	}
	rows.Close()
	rows2, err := db.QueryContext(ctx, `SELECT campus_id, campus_name FROM t_campus`)
	if err != nil {
		return nil, nil, err
	}
	for rows2.Next() {
		var id int64
		var n string
		if err := rows2.Scan(&id, &n); err != nil {
			rows2.Close()
			return nil, nil, err
		}
		camp[id] = n
	}
	rows2.Close()
	return inst, camp, nil
}

// ---------- 跨机构三类统计 ----------

// crossOrgCourseLevel（A）课程级：t_courses.schoolid vs 该课程 campusid 的校区真实所属。
func crossOrgCourseLevel(ctx context.Context, db *sql.DB) []CrossOrgStat {
	st := CrossOrgStat{
		Key: "A", Name: "课程级：课程挂的机构 vs 校区的真实所属",
		Source: "finance_bk.t_courses.schoolid ↔ yuansiwei_i.t_school_campus.school_id(campusid)",
		Note:   "R17 的主体：校区 1/2 属机构 1，但课程普遍挂在 63317 下",
	}
	_ = db.QueryRowContext(ctx, `
SELECT COUNT(*),
       SUM(sc.id IS NULL),
       SUM(sc.id IS NOT NULL AND sc.school_id = c.schoolid),
       SUM(sc.id IS NOT NULL AND sc.school_id <> c.schoolid)
  FROM t_courses c LEFT JOIN yuansiwei_i.t_school_campus sc ON sc.id = c.campusid`).
		Scan(&st.Total, &st.Skipped, &st.Consistent, &st.Inconsistent)
	st.Samples = topCombos(ctx, db, `
SELECT c.schoolid, c.campusid, ifnull(sc.school_id,0), COUNT(*)
  FROM t_courses c LEFT JOIN yuansiwei_i.t_school_campus sc ON sc.id = c.campusid
 WHERE sc.id IS NOT NULL AND sc.school_id <> c.schoolid
 GROUP BY 1,2,3 ORDER BY 4 DESC LIMIT 5`, "课程挂机构=%d 校区=%d(真实所属=%d) → %d 行")
	return []CrossOrgStat{st}
}

// crossOrgRecordLevel（B）记录级：t_courses_record.schoolid vs 该记录 campusid 的校区真实所属。
func crossOrgRecordLevel(ctx context.Context, db *sql.DB) []CrossOrgStat {
	st := CrossOrgStat{
		Key: "B", Name: "记录级：上课记录挂的机构 vs 校区的真实所属",
		Source: "finance_bk.t_courses_record.schoolid ↔ yuansiwei_i.t_school_campus.school_id(campusid)",
		Note:   "含反向跨机构（记录挂机构 1、校区属 63317）",
	}
	_ = db.QueryRowContext(ctx, `
SELECT COUNT(*),
       SUM(sc.id IS NULL),
       SUM(sc.id IS NOT NULL AND sc.school_id = r.schoolid),
       SUM(sc.id IS NOT NULL AND sc.school_id <> r.schoolid)
  FROM t_courses_record r LEFT JOIN yuansiwei_i.t_school_campus sc ON sc.id = r.campusid`).
		Scan(&st.Total, &st.Skipped, &st.Consistent, &st.Inconsistent)
	st.Samples = topCombos(ctx, db, `
SELECT r.schoolid, r.campusid, ifnull(sc.school_id,0), COUNT(*)
  FROM t_courses_record r LEFT JOIN yuansiwei_i.t_school_campus sc ON sc.id = r.campusid
 WHERE sc.id IS NOT NULL AND sc.school_id <> r.schoolid
 GROUP BY 1,2,3 ORDER BY 4 DESC LIMIT 5`, "记录挂机构=%d 校区=%d(真实所属=%d) → %d 行")
	return []CrossOrgStat{st}
}

// crossOrgRecordVsCourse（C）记录 vs 课程：t_courses_record.schoolid vs 其 coursesid 的 schoolid。
//
// 与 A/B 性质不同：这里两个都是"数据自己声明的机构"，不一致说明记录挂错了机构，
// 与校区归属无关。
func crossOrgRecordVsCourse(ctx context.Context, db *sql.DB) []CrossOrgStat {
	st := CrossOrgStat{
		Key: "C", Name: "记录 vs 课程：记录的机构 vs 其所属课程的机构",
		Source: "finance_bk.t_courses_record.schoolid ↔ finance_bk.t_courses.schoolid(coursesid)",
		Note:   "与校区归属无关，纯粹是记录挂错机构",
	}
	_ = db.QueryRowContext(ctx, `
SELECT COUNT(*), SUM(r.schoolid = c.schoolid), SUM(r.schoolid <> c.schoolid)
  FROM t_courses_record r JOIN t_courses c ON c.id = r.coursesid`).
		Scan(&st.Total, &st.Consistent, &st.Inconsistent)
	st.Samples = topCombos3(ctx, db, `
SELECT r.schoolid, c.schoolid, COUNT(*)
  FROM t_courses_record r JOIN t_courses c ON c.id = r.coursesid
 WHERE r.schoolid <> c.schoolid
 GROUP BY 1,2 ORDER BY 3 DESC LIMIT 5`, "记录挂机构=%d 课程属机构=%d → %d 行")
	return []CrossOrgStat{st}
}

// topCombos 执行「不一致组合 TOP N」查询并格式化成样例行。
func topCombos(ctx context.Context, db *sql.DB, q, format string) []string {
	rows, err := db.QueryContext(ctx, q)
	if err != nil {
		return []string{"<查询失败：" + err.Error() + ">"}
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var a, b, c, n int64
		if err := rows.Scan(&a, &b, &c, &n); err != nil {
			return out
		}
		out = append(out, fmt.Sprintf(format, a, b, c, n))
	}
	return out
}

// topCombos3 是 topCombos 的 3 列版本（格式串 verb 数与列数必须严格一致）。
func topCombos3(ctx context.Context, db *sql.DB, q, format string) []string {
	rows, err := db.QueryContext(ctx, q)
	if err != nil {
		return []string{"<查询失败：" + err.Error() + ">"}
	}
	defer rows.Close()
	var out []string
	for rows.Next() {
		var a, b, n int64
		if err := rows.Scan(&a, &b, &n); err != nil {
			return out
		}
		out = append(out, fmt.Sprintf(format, a, b, n))
	}
	return out
}

// ---------- 停用校区影响面 ----------

// disabledCampusStats 统计「所选机构里 status=0 的校区」各自挂了多少历史数据。
//
// 这不是为了"好看"：迁过去之后这些校区是 disabled，历史数据在"按启用校区筛选"时看不到，
// 而写入侧 assertInScope 会拒绝给停用校区新增数据（这是对的）——
// 业务侧会看到"历史数据消失"，必须让业务知情。
func disabledCampusStats(ctx context.Context, db *sql.DB, opts OrgOptions) ([]DisabledCampusStat, error) {
	ph := placeholders(len(opts.InstitutionIDs))
	args := make([]any, len(opts.InstitutionIDs))
	for i, v := range opts.InstitutionIDs {
		args[i] = v
	}
	q := fmt.Sprintf(`
SELECT sc.id, sc.name, sc.school_id,
       (SELECT COUNT(*) FROM t_courses x WHERE x.campusid = sc.id),
       (SELECT COUNT(*) FROM t_courses_record x WHERE x.campusid = sc.id),
       (SELECT COUNT(*) FROM t_user_expense_school x WHERE x.campusid = sc.id),
       (SELECT COUNT(*) FROM t_user_balance_request x WHERE x.campusid = sc.id),
       (SELECT COUNT(*) FROM t_expenditure_log x WHERE x.campusid = sc.id)
  FROM yuansiwei_i.t_school_campus sc
 WHERE sc.school_id IN (%s) AND sc.status = 0
 ORDER BY sc.id`, ph)
	rows, err := db.QueryContext(ctx, q, args...)
	if err != nil {
		return nil, fmt.Errorf("停用校区统计失败：%w", err)
	}
	defer rows.Close()
	var out []DisabledCampusStat
	for rows.Next() {
		var d DisabledCampusStat
		if err := rows.Scan(&d.CampusID, &d.Name, &d.InstitutionID, &d.Courses, &d.Records,
			&d.ExpenseSchool, &d.BalanceRequest, &d.ExpenditureLog); err != nil {
			return nil, err
		}
		out = append(out, d)
	}
	return out, rows.Err()
}