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   []string // R17：数据里 (schoolid, campusid) 与校区真实所属机构不一致的组合
	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. cross-org-campus 标注（只报告，不判失败） ----------
	for _, s := range orgRefSources() {
		if s.CampusCol == "" {
			continue
		}
		rows, err := legacy.QueryContext(ctx, fmt.Sprintf(
			"SELECT DISTINCT `%s`, `%s`, COUNT(*) FROM `%s` WHERE `%s` <> 0 GROUP BY 1,2 ORDER BY 1,2",
			s.InstCol, s.CampusCol, s.Table, s.CampusCol))
		if err != nil {
			return nil, err
		}
		for rows.Next() {
			var inst, camp, n int64
			if err := rows.Scan(&inst, &camp, &n); err != nil {
				rows.Close()
				return nil, err
			}
			if IsCrossOrgCampus(inst, camp, campusOwner) {
				rep.CrossOrg = append(rep.CrossOrg, fmt.Sprintf(
					"%s: schoolid=%d campusid=%d（校区真实所属=%d）%d 行",
					s.Table, inst, camp, campusOwner[camp], n))
			}
		}
		rows.Close()
	}
	sort.Strings(rep.CrossOrg)
	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
}