package fixture

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

// 本文件做**逐列核对**，关键设计是「期望值从哪来」：
//
// ⚠ 期望值**不由本包的映射代码产生**（否则映射写错时两边一起错，核对永远绿）。
// 期望值一律由 **MySQL 服务端**算出：`CAST(x AS DECIMAL(12,2))` / `DATE_FORMAT(...)`。
// 于是：
//   · Go 侧的取整/换算只要与 DECIMAL 语义不一致，核对立刻红；
//   · 变异实验（故意改错映射）必然红——这正是它的意义。

// normalizeMoney 把服务端返回的十进制串规范成 2 位小数字符串，
// 用于「期望 vs 实际」的字符串级比较（"-0.00" → "0.00"）。
func normalizeMoney(s string) string {
	s = strings.TrimSpace(s)
	if s == "" {
		return ""
	}
	c, err := DecimalToCents(s)
	if err != nil {
		return "ERR:" + s
	}
	if c == 0 {
		return "0.00"
	}
	return CentsToDecimal(c)
}

// thousandthsOf 把十进制串精确转为「千分位」整数，用于 float 陷阱演示中的精确和。
func thousandthsOf(s string) (int64, error) {
	raw := strings.TrimSpace(s)
	neg := false
	if strings.HasPrefix(raw, "-") {
		neg = true
		raw = raw[1:]
	}
	intPart, frac := raw, ""
	if i := strings.IndexByte(raw, '.'); i >= 0 {
		intPart, frac = raw[:i], raw[i+1:]
	}
	for len(frac) < 3 {
		frac += "0"
	}
	if len(frac) > 3 {
		frac = frac[:3]
	}
	ip := int64(0)
	if intPart != "" {
		v, err := strconv.ParseInt(intPart, 10, 64)
		if err != nil {
			return 0, err
		}
		ip = v
	}
	fp, err := strconv.ParseInt(frac, 10, 64)
	if err != nil {
		return 0, err
	}
	v := ip*1000 + fp
	if neg {
		v = -v
	}
	return v, nil
}

// ---------- 期望值读取（服务端口径） ----------

type expExpense struct {
	UserID                                                    int64
	Balance, Spend, Income, Expenditure, Recommended, Special string
}

func readExpectedExpense(ctx context.Context, legacy *sql.DB, userID int64) (*expExpense, error) {
	const q = `
SELECT userid,
       CAST(balance                  AS DECIMAL(12,2)),
       CAST(spend                    AS DECIMAL(12,2)),
       CAST(total_income             AS DECIMAL(12,2)),
       CAST(total_expenditure        AS DECIMAL(12,2)),
       CAST(ifnull(total_recommended_amount,0) AS DECIMAL(12,2)),
       CAST(ifnull(special_expenses,0)         AS DECIMAL(12,2))
  FROM t_user_expense WHERE userid = ?`
	var e expExpense
	if err := legacy.QueryRowContext(ctx, q, userID).Scan(
		&e.UserID, &e.Balance, &e.Spend, &e.Income, &e.Expenditure, &e.Recommended, &e.Special); err != nil {
		return nil, err
	}
	return &e, nil
}

type expSchool struct {
	ID, SchoolID, CampusID                                    int64
	Balance, Spend, Income, Expenditure, Recommended, Special string
}

func readExpectedSchools(ctx context.Context, legacy *sql.DB, userID int64) ([]expSchool, error) {
	const q = `
SELECT id, schoolid, cast(ifnull(campusid,0) as signed),
       CAST(balance                  AS DECIMAL(12,2)),
       CAST(spend                    AS DECIMAL(12,2)),
       CAST(total_income             AS DECIMAL(12,2)),
       CAST(total_expenditure        AS DECIMAL(12,2)),
       CAST(ifnull(total_recommended_amount,0) AS DECIMAL(12,2)),
       CAST(ifnull(special_expenses,0)         AS DECIMAL(12,2))
  FROM t_user_expense_school WHERE userid = ? ORDER BY id`
	rows, err := legacy.QueryContext(ctx, q, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []expSchool
	for rows.Next() {
		var r expSchool
		if err := rows.Scan(&r.ID, &r.SchoolID, &r.CampusID,
			&r.Balance, &r.Spend, &r.Income, &r.Expenditure, &r.Recommended, &r.Special); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

type expLog struct {
	ID            int64
	Type          int32
	LogID         sql.NullString
	Money         string
	CreateUserID  sql.NullInt64
	CreateTimeStr string
	Remark        sql.NullString
}

func readExpectedLogs(ctx context.Context, legacy *sql.DB, userID int64) ([]expLog, error) {
	const q = `
SELECT id, cast(ifnull(type,0) as signed), logid,
       CAST(money AS DECIMAL(12,2)),
       create_userid,
       DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s.%f'),
       remark
  FROM t_user_expense_log WHERE userid = ? ORDER BY id`
	rows, err := legacy.QueryContext(ctx, q, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []expLog
	for rows.Next() {
		var r expLog
		if err := rows.Scan(&r.ID, &r.Type, &r.LogID, &r.Money, &r.CreateUserID, &r.CreateTimeStr, &r.Remark); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// ---------- 目标值读取 ----------

type actExpense struct {
	UserID                                                    string
	Balance, Spend, Income, Expenditure, Recommended, Special string
}

func readActualExpense(ctx context.Context, db *sql.DB, userID string) (*actExpense, error) {
	const q = `
SELECT user_id, CAST(balance AS DECIMAL(12,2)), CAST(spend AS DECIMAL(12,2)),
       CAST(total_income AS DECIMAL(12,2)), CAST(total_expenditure AS DECIMAL(12,2)),
       CAST(total_recommended_amount AS DECIMAL(12,2)), CAST(special_expenses AS DECIMAL(12,2))
  FROM t_user_expense WHERE user_id = ?`
	var a actExpense
	err := db.QueryRowContext(ctx, q, userID).Scan(&a.UserID, &a.Balance, &a.Spend,
		&a.Income, &a.Expenditure, &a.Recommended, &a.Special)
	if err != nil {
		return nil, err
	}
	return &a, nil
}

type actSchool struct {
	ID, InstitutionID, CampusID                               int64
	Balance, Spend, Income, Expenditure, Recommended, Special string
}

func readActualSchools(ctx context.Context, db *sql.DB, userID string) ([]actSchool, error) {
	const q = `
SELECT id, institution_id, campus_id,
       CAST(balance AS DECIMAL(12,2)), CAST(spend AS DECIMAL(12,2)),
       CAST(total_income AS DECIMAL(12,2)), CAST(total_expenditure AS DECIMAL(12,2)),
       CAST(total_recommended_amount AS DECIMAL(12,2)), CAST(special_expenses AS DECIMAL(12,2))
  FROM t_user_expense_school WHERE user_id = ? ORDER BY institution_id, campus_id`
	rows, err := db.QueryContext(ctx, q, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []actSchool
	for rows.Next() {
		var r actSchool
		if err := rows.Scan(&r.ID, &r.InstitutionID, &r.CampusID, &r.Balance, &r.Spend,
			&r.Income, &r.Expenditure, &r.Recommended, &r.Special); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

type actLog struct {
	ID            int64
	UserID        string
	Type          int32
	LogID         sql.NullString
	RequestID     sql.NullString
	Money         string
	BalanceAfter  sql.NullString
	CreateUserID  sql.NullString
	CreateTimeStr string
	Remark        sql.NullString
}

func readActualLogs(ctx context.Context, db *sql.DB, userID string) ([]actLog, error) {
	const q = `
SELECT id, user_id, type, log_id, request_id,
       CAST(money AS DECIMAL(12,2)),
       CAST(balance_after AS DECIMAL(12,2)),
       create_user_id,
       DATE_FORMAT(create_time, '%Y-%m-%d %H:%i:%s.%f'),
       remark
  FROM t_user_expense_log WHERE user_id = ? ORDER BY id`
	rows, err := db.QueryContext(ctx, q, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []actLog
	for rows.Next() {
		var r actLog
		if err := rows.Scan(&r.ID, &r.UserID, &r.Type, &r.LogID, &r.RequestID, &r.Money,
			&r.BalanceAfter, &r.CreateUserID, &r.CreateTimeStr, &r.Remark); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// ---------- 核对主体 ----------

// Verify 执行一轮完整核对：4 张表逐列 + AUTO_INCREMENT + 空值语义 + float 陷阱。
func Verify(ctx context.Context, cfg Config, legacyUserID int64) (*VerifyReport, error) {
	canonical := FixtureCanonicalUserID(legacyUserID)

	target, err := cfg.OpenTarget()
	if err != nil {
		return nil, err
	}
	defer target.Close()

	legacy, err := cfg.OpenLegacy()
	if err != nil {
		return nil, err
	}
	defer legacy.Close()

	rep := &VerifyReport{
		LoadedRows: map[string]int{},
		TargetRows: map[string]int{},
	}

	// ===== 1. t_user_expense（1 行 7 列）=====
	ee, err := readExpectedExpense(ctx, legacy, legacyUserID)
	if err != nil {
		return nil, fmt.Errorf("读期望 t_user_expense 失败：%w", err)
	}
	ae, err := readActualExpense(ctx, target, canonical)
	if err != nil {
		return nil, fmt.Errorf("读实际 t_user_expense 失败：%w", err)
	}
	key := "user_id=" + canonical
	rep.Checks = append(rep.Checks,
		check("t_user_expense", key, "user_id", fmt.Sprintf("%d", ee.UserID), ae.UserID,
			ae.UserID == canonical, "legacy int → canonical ULID（经 t_legacy_mapping）"),
		check("t_user_expense", key, "balance", ee.Balance, ae.Balance,
			normalizeMoney(ee.Balance) == normalizeMoney(ae.Balance), ""),
		check("t_user_expense", key, "spend", ee.Spend, ae.Spend,
			normalizeMoney(ee.Spend) == normalizeMoney(ae.Spend), ""),
		check("t_user_expense", key, "total_income", ee.Income, ae.Income,
			normalizeMoney(ee.Income) == normalizeMoney(ae.Income), ""),
		check("t_user_expense", key, "total_expenditure", ee.Expenditure, ae.Expenditure,
			normalizeMoney(ee.Expenditure) == normalizeMoney(ae.Expenditure), ""),
		check("t_user_expense", key, "total_recommended_amount", ee.Recommended, ae.Recommended,
			normalizeMoney(ee.Recommended) == normalizeMoney(ae.Recommended), ""),
		check("t_user_expense", key, "special_expenses", ee.Special, ae.Special,
			normalizeMoney(ee.Special) == normalizeMoney(ae.Special), ""),
	)
	rep.LoadedRows["t_user_expense"] = 1
	rep.TargetRows["t_user_expense"] = 1

	// ===== 2. t_user_expense_school（N 行 × 8 列）=====
	es, err := readExpectedSchools(ctx, legacy, legacyUserID)
	if err != nil {
		return nil, fmt.Errorf("读期望 t_user_expense_school 失败：%w", err)
	}
	as, err := readActualSchools(ctx, target, canonical)
	if err != nil {
		return nil, fmt.Errorf("读实际 t_user_expense_school 失败：%w", err)
	}
	rep.LoadedRows["t_user_expense_school"] = len(es)
	rep.TargetRows["t_user_expense_school"] = len(as)
	rep.Checks = append(rep.Checks, check("t_user_expense_school", "-", "行数",
		fmt.Sprintf("%d", len(es)), fmt.Sprintf("%d", len(as)), len(es) == len(as), "复合业务键 (user,institution,campus) 对齐"))

	// 按 (institution_id, campus_id) 建索引，避免依赖 legacy id（该表不保留 legacy id）。
	actualByKey := map[string]actSchool{}
	for _, r := range as {
		actualByKey[fmt.Sprintf("%d/%d", r.InstitutionID, r.CampusID)] = r
	}
	for _, e := range es {
		k := fmt.Sprintf("%d/%d", e.SchoolID, e.CampusID)
		a, ok := actualByKey[k]
		rowKey := fmt.Sprintf("inst=%d/campus=%d", e.SchoolID, e.CampusID)
		if !ok {
			rep.Checks = append(rep.Checks, ColumnCheck{
				Table: "t_user_expense_school", RowKey: rowKey, Column: "<整行>",
				Legacy: "存在", Target: "缺失", Equal: false, Note: "目标侧缺该复合键行",
			})
			continue
		}
		rep.Checks = append(rep.Checks,
			check("t_user_expense_school", rowKey, "institution_id", fmt.Sprintf("%d", e.SchoolID), fmt.Sprintf("%d", a.InstitutionID), e.SchoolID == a.InstitutionID, "schoolid→institution_id"),
			check("t_user_expense_school", rowKey, "campus_id", fmt.Sprintf("%d", e.CampusID), fmt.Sprintf("%d", a.CampusID), e.CampusID == a.CampusID, "campusid→campus_id"),
			check("t_user_expense_school", rowKey, "balance", e.Balance, a.Balance, normalizeMoney(e.Balance) == normalizeMoney(a.Balance), ""),
			check("t_user_expense_school", rowKey, "spend", e.Spend, a.Spend, normalizeMoney(e.Spend) == normalizeMoney(a.Spend), ""),
			check("t_user_expense_school", rowKey, "total_income", e.Income, a.Income, normalizeMoney(e.Income) == normalizeMoney(a.Income), ""),
			check("t_user_expense_school", rowKey, "total_expenditure", e.Expenditure, a.Expenditure, normalizeMoney(e.Expenditure) == normalizeMoney(a.Expenditure), ""),
			check("t_user_expense_school", rowKey, "total_recommended_amount", e.Recommended, a.Recommended, normalizeMoney(e.Recommended) == normalizeMoney(a.Recommended), ""),
			check("t_user_expense_school", rowKey, "special_expenses", e.Special, a.Special, normalizeMoney(e.Special) == normalizeMoney(a.Special), ""),
		)
	}

	// ===== 3. t_user_expense_log（M 行 × 10 列）=====
	el, err := readExpectedLogs(ctx, legacy, legacyUserID)
	if err != nil {
		return nil, fmt.Errorf("读期望 t_user_expense_log 失败：%w", err)
	}
	al, err := readActualLogs(ctx, target, canonical)
	if err != nil {
		return nil, fmt.Errorf("读实际 t_user_expense_log 失败：%w", err)
	}
	rep.LoadedRows["t_user_expense_log"] = len(el)
	rep.TargetRows["t_user_expense_log"] = len(al)
	rep.Checks = append(rep.Checks, check("t_user_expense_log", "-", "行数",
		fmt.Sprintf("%d", len(el)), fmt.Sprintf("%d", len(al)), len(el) == len(al), "按 id 对齐"))

	actualLogByID := map[int64]actLog{}
	for _, r := range al {
		actualLogByID[r.ID] = r
	}
	for _, e := range el {
		rowKey := fmt.Sprintf("id=%d", e.ID)
		a, ok := actualLogByID[e.ID]
		if !ok {
			rep.Checks = append(rep.Checks, ColumnCheck{
				Table: "t_user_expense_log", RowKey: rowKey, Column: "<整行>",
				Legacy: "存在", Target: "缺失", Equal: false, Note: "D1 显式插 id 未生效：目标侧无该 id",
			})
			continue
		}
		// create_user_id 期望：legacy 0/NULL → NULL；>0 → 必须是某个 ULID（由 t_legacy_mapping 解析）
		expCreateUser := "NULL"
		createUserOK := !a.CreateUserID.Valid
		if e.CreateUserID.Valid && e.CreateUserID.Int64 > 0 {
			expCreateUser = "ULID(legacy=" + strconv.FormatInt(e.CreateUserID.Int64, 10) + ")"
			createUserOK = a.CreateUserID.Valid && a.CreateUserID.String == FixtureCanonicalUserID(e.CreateUserID.Int64)
		}
		actCreateUser := "NULL"
		if a.CreateUserID.Valid {
			actCreateUser = a.CreateUserID.String
		}

		rep.Checks = append(rep.Checks,
			check("t_user_expense_log", rowKey, "id", strconv.FormatInt(e.ID, 10), strconv.FormatInt(a.ID, 10), e.ID == a.ID, "D1：与 legacy id 完全相同"),
			check("t_user_expense_log", rowKey, "user_id", strconv.FormatInt(legacyUserID, 10), a.UserID, a.UserID == canonical, "legacy userid → canonical ULID"),
			check("t_user_expense_log", rowKey, "type", strconv.FormatInt(int64(e.Type), 10), strconv.FormatInt(int64(a.Type), 10), e.Type == a.Type, ""),
			check("t_user_expense_log", rowKey, "log_id", nullStrView(e.LogID), nullStrView(a.LogID), nullStrView(e.LogID) == nullStrView(a.LogID), "logid→log_id"),
			check("t_user_expense_log", rowKey, "request_id", "NULL", nullStrView(a.RequestID), !a.RequestID.Valid, "legacy 无此列 → 必须 NULL"),
			check("t_user_expense_log", rowKey, "money", e.Money, a.Money, normalizeMoney(e.Money) == normalizeMoney(a.Money), "逐行 ROUND 到分"),
			check("t_user_expense_log", rowKey, "balance_after", "NULL", nullStrView(a.BalanceAfter), !a.BalanceAfter.Valid, "legacy 无快照 → 必须 NULL"),
			check("t_user_expense_log", rowKey, "create_user_id", expCreateUser, actCreateUser, createUserOK, "legacy 0/系统 → NULL"),
			check("t_user_expense_log", rowKey, "create_time", e.CreateTimeStr, a.CreateTimeStr, e.CreateTimeStr == a.CreateTimeStr, "秒级相同，毫秒补 .000"),
			check("t_user_expense_log", rowKey, "remark", maskRemark(e.Remark), maskRemark(a.Remark), sameRemark(e.Remark, a.Remark), "报告脱敏，比对用原文"),
		)
	}

	// ===== 4. t_user / t_legacy_mapping 链接 =====
	if err := verifyIdentity(ctx, target, legacyUserID, canonical, rep); err != nil {
		return nil, err
	}

	// ===== 4.5 create_time 未落入 DB 默认值（业务时间必须来自 legacy）=====
	rep.Assertions = append(rep.Assertions,
		createTimeAssertions(ctx, target, []string{"t_user_expense_log"})...)

	// ===== 5. 语义断言 =====
	rep.Assertions = append(rep.Assertions, Assertion{
		Name:   "create_user_id=0 → NULL（本 fixture 数据无 0 行时按空集通过）",
		Pass:   countZeroCreator(target, canonical),
		Detail: "SQL: SELECT COUNT(*) FROM t_user_expense_log WHERE user_id=? AND create_user_id='0' 必须为 0",
	})
	rep.Assertions = append(rep.Assertions, Assertion{
		Name:   "balance_after 全为 NULL",
		Pass:   countNonNullBalanceAfter(target, canonical),
		Detail: "SQL: ... WHERE user_id=? AND balance_after IS NOT NULL 必须为 0",
	})

	// ===== 6. AUTO_INCREMENT 不倒退 =====
	aiDemo, err := autoIncrementDemo(ctx, target, canonical)
	if err != nil {
		return nil, err
	}
	rep.AutoIncTest = aiDemo
	rep.Assertions = append(rep.Assertions, Assertion{
		Name: "显式插 id 后 AUTO_INCREMENT 不倒退（探针 id = 计数器，且 >= max+1；镜像表演示 max+1）",
		Pass: aiDemo.Pass && aiDemo.MirrorPass,
		Detail: fmt.Sprintf(
			"真实表：max(id)=%d, AUTO_INCREMENT=%d（=max+1? %v）, 不带 id 插入得到 id=%d；"+
				"临时表镜像：显式插 id=%d → 不带 id 插入得到 id=%d（期望 %d）",
			aiDemo.MaxIDBefore, aiDemo.AutoIncrementVal, aiDemo.EqualsMaxPlusOne, aiDemo.ProbeID,
			aiDemo.MirrorExplicitID, aiDemo.MirrorProbeID, aiDemo.MirrorExpected),
	})

	// ===== 7. float 陷阱证据 =====
	trap, err := floatTrap(ctx, legacy, legacyUserID)
	if err != nil {
		return nil, err
	}
	rep.FloatTrap = trap

	return rep, nil
}

// check 构造一条核对行。
func check(table, rowKey, column, legacyVal, targetVal string, equal bool, note string) ColumnCheck {
	return ColumnCheck{Table: table, RowKey: rowKey, Column: column,
		Legacy: legacyVal, Target: targetVal, Equal: equal, Note: note}
}

func nullStrView(v sql.NullString) string {
	if !v.Valid {
		return "NULL"
	}
	return v.String
}

// maskRemark 报告里脱敏：备注可能含真实姓名/手机号，只展示前 8 个字符 + 长度。
func maskRemark(v sql.NullString) string {
	if !v.Valid {
		return "NULL"
	}
	r := []rune(v.String)
	head := r
	if len(head) > 8 {
		head = head[:8]
	}
	return fmt.Sprintf("%s…(len=%d)", string(head), len(r))
}

// sameRemark 用原文比对（脱敏只影响展示，不影响核对）。
func sameRemark(a, b sql.NullString) bool {
	if a.Valid != b.Valid {
		return false
	}
	if !a.Valid {
		return true
	}
	return a.String == b.String
}

// verifyIdentity 核对 t_legacy_mapping 与 t_user 的链接是否正确。
func verifyIdentity(ctx context.Context, target *sql.DB, legacyUserID int64, canonical string, rep *VerifyReport) error {
	var mapped string
	err := target.QueryRowContext(ctx,
		`SELECT canonical_user_id FROM t_legacy_mapping WHERE legacy_academic_user_id = ?`, legacyUserID).Scan(&mapped)
	switch {
	case err == sql.ErrNoRows:
		rep.Checks = append(rep.Checks, ColumnCheck{Table: "t_legacy_mapping",
			RowKey: fmt.Sprintf("legacy=%d", legacyUserID), Column: "canonical_user_id",
			Legacy: "存在", Target: "缺失", Equal: false, Note: "映射行不存在"})
	case err != nil:
		return err
	default:
		rep.Checks = append(rep.Checks, check("t_legacy_mapping", fmt.Sprintf("legacy=%d", legacyUserID),
			"canonical_user_id", canonical, mapped, mapped == canonical, "legacy int → ULID 映射正确"))
	}

	var displayName sql.NullString
	err = target.QueryRowContext(ctx,
		`SELECT display_name FROM t_user WHERE canonical_user_id = ?`, canonical).Scan(&displayName)
	switch {
	case err == sql.ErrNoRows:
		rep.Checks = append(rep.Checks, ColumnCheck{Table: "t_user",
			RowKey: canonical, Column: "<整行>", Legacy: "存在", Target: "缺失", Equal: false, Note: "t_user 行不存在"})
	case err != nil:
		return err
	default:
		rep.Checks = append(rep.Checks, check("t_user", canonical, "canonical_user_id",
			canonical, canonical, true, "主键存在即可（其余列为合法默认值）"))
	}
	return nil
}

// countZeroCreator 断言「没有一行 create_user_id 落成字符串 '0'」。
func countZeroCreator(db *sql.DB, userID string) bool {
	var n int
	err := db.QueryRow(
		`SELECT COUNT(*) FROM t_user_expense_log WHERE user_id = ? AND create_user_id = '0'`, userID).Scan(&n)
	return err == nil && n == 0
}

// countNonNullBalanceAfter 断言 balance_after 全部为 NULL。
func countNonNullBalanceAfter(db *sql.DB, userID string) bool {
	var n int
	err := db.QueryRow(
		`SELECT COUNT(*) FROM t_user_expense_log WHERE user_id = ? AND balance_after IS NOT NULL`, userID).Scan(&n)
	return err == nil && n == 0
}

// readAutoIncrement 读**即时**自增计数器。
//
// 两个坑都必须躲掉，否则会得到「AUTO_INCREMENT = 1」这种完全错误的结论：
//  1. 不能读 information_schema.tables.AUTO_INCREMENT —— MySQL 8.0 默认
//     `information_schema_stats_expiry = 86400`，那是最多缓存一天的值（实测返回 1）。
//  2. 必须先 `SET SESSION information_schema_stats_expiry = 0`，且 SET 与 SELECT
//     必须在**同一条连接**上（连接池会换连接），所以这里用 db.Conn 独占一条。
func readAutoIncrement(ctx context.Context, db *sql.DB, table string) (int64, error) {
	// SHOW 语句不支持占位符（Error 1064），因此表名内联；
	// 表名来自本包常量，且此处再做一次字符白名单校验，避免拼接风险。
	if table == "" {
		return 0, fmt.Errorf("readAutoIncrement: 表名为空")
	}
	for _, r := range table {
		if !(r == '_' || (r >= 'a' && r <= 'z') || (r >= 'A' && r <= 'Z') || (r >= '0' && r <= '9')) {
			return 0, fmt.Errorf("readAutoIncrement: 非法表名 %q", table)
		}
	}
	conn, err := db.Conn(ctx)
	if err != nil {
		return 0, err
	}
	defer conn.Close()
	if _, err := conn.ExecContext(ctx, "SET SESSION information_schema_stats_expiry = 0"); err != nil {
		return 0, fmt.Errorf("关闭 information_schema 统计缓存失败：%w", err)
	}
	rows, err := conn.QueryContext(ctx, fmt.Sprintf("SHOW TABLE STATUS LIKE '%s'", table))
	if err != nil {
		return 0, err
	}
	defer rows.Close()
	cols, err := rows.Columns()
	if err != nil {
		return 0, err
	}
	if !rows.Next() {
		return 0, fmt.Errorf("SHOW TABLE STATUS 无 %s 行", table)
	}
	vals := make([]any, len(cols))
	ptrs := make([]any, len(cols))
	for i := range vals {
		ptrs[i] = &vals[i]
	}
	if err := rows.Scan(ptrs...); err != nil {
		return 0, err
	}
	for i, c := range cols {
		if !strings.EqualFold(c, "Auto_increment") {
			continue
		}
		switch v := vals[i].(type) {
		case nil:
			return 0, nil
		case []byte:
			return strconv.ParseInt(string(v), 10, 64)
		case int64:
			return v, nil
		case uint64:
			return int64(v), nil
		default:
			return 0, fmt.Errorf("Auto_increment 类型未知：%T", v)
		}
	}
	return 0, fmt.Errorf("SHOW TABLE STATUS 结果里没有 Auto_increment 列")
}

// autoIncrementDemo 验证「显式插 id 之后自增计数器不倒回」。
//
// A 段（真实表）：读 max(id) 与即时 AUTO_INCREMENT → 插一条**不带 id** 的探针行 →
// 探针 id 必须等于该计数器、且 >= max(id)+1（若被重置成 1，这里会直接撞主键或取到小数）。
// B 段（临时表镜像）：CREATE TEMPORARY TABLE ... LIKE → 显式插 id=100 →
// 不带 id 插 → 必须 101。临时表随会话结束自动消失，不污染 schema、无需 DROP。
func autoIncrementDemo(ctx context.Context, db *sql.DB, userID string) (AutoIncrementDemo, error) {
	var d AutoIncrementDemo
	if err := db.QueryRowContext(ctx,
		`SELECT IFNULL(MAX(id),0) FROM t_user_expense_log`).Scan(&d.MaxIDBefore); err != nil {
		return d, err
	}
	ai, err := readAutoIncrement(ctx, db, "t_user_expense_log")
	if err != nil {
		return d, err
	}
	d.AutoIncrementVal = ai
	d.ExpectedProbeID = ai
	d.EqualsMaxPlusOne = ai == d.MaxIDBefore+1

	res, err := db.ExecContext(ctx, `
INSERT INTO t_user_expense_log
  (user_id, type, log_id, request_id, money, balance_after, create_user_id, create_time, remark)
VALUES (?, 0, NULL, NULL, '0.00', NULL, NULL, '1970-01-01 00:00:00', 'ac15 fixture auto-inc probe')`, userID)
	if err != nil {
		return d, fmt.Errorf("AUTO_INCREMENT 探针插入失败（计数器疑似被重置）：%w", err)
	}
	probeID, err := res.LastInsertId()
	if err != nil {
		return d, err
	}
	d.ProbeID = probeID
	// 判定：探针 id 必须等于读到的计数器（证明「不带 id 的插入」就是拿计数器发号），
	// 且计数器不得小于 max(id)+1（证明显式插 id 没有把计数器倒退）。
	d.Pass = probeID == d.ExpectedProbeID && d.AutoIncrementVal >= d.MaxIDBefore+1
	if _, err := db.ExecContext(ctx,
		`DELETE FROM t_user_expense_log WHERE remark = 'ac15 fixture auto-inc probe'`); err != nil {
		return d, fmt.Errorf("AUTO_INCREMENT 探针清理失败：%w", err)
	}

	// ---- B 段：临时表镜像，可重复地演示「显式插 id=100 → 下一条 = 101」----
	if err := mirrorAutoIncrement(ctx, db, userID, &d); err != nil {
		return d, err
	}
	return d, nil
}

// mirrorAutoIncrement 用 TEMPORARY TABLE 复现「max+1」。
//
// 独立连接执行：临时表是会话级的，必须确保 CREATE / INSERT / SELECT 走同一条连接。
func mirrorAutoIncrement(ctx context.Context, db *sql.DB, userID string, d *AutoIncrementDemo) error {
	conn, err := db.Conn(ctx)
	if err != nil {
		return err
	}
	defer conn.Close()

	const tmp = "tmp_ac15_ai_probe"
	steps := []string{
		fmt.Sprintf("CREATE TEMPORARY TABLE `%s` LIKE t_user_expense_log", tmp),
		fmt.Sprintf("INSERT INTO `%s` (id, user_id, type, money, create_time, remark) VALUES (100, '%s', 0, '0.00', '1970-01-01 00:00:00', 'mirror explicit id')", tmp, userID),
	}
	for _, s := range steps {
		if _, err := conn.ExecContext(ctx, s); err != nil {
			return fmt.Errorf("临时表镜像步骤失败（%s）：%w", s, err)
		}
	}
	d.MirrorExplicitID = 100

	res, err := conn.ExecContext(ctx, fmt.Sprintf(
		"INSERT INTO `%s` (user_id, type, money, create_time, remark) VALUES ('%s', 0, '0.00', '1970-01-01 00:00:00', 'mirror auto id')",
		tmp, userID))
	if err != nil {
		return fmt.Errorf("临时表镜像第二次插入失败：%w", err)
	}
	mirrorID, err := res.LastInsertId()
	if err != nil {
		return err
	}
	d.MirrorProbeID = mirrorID
	d.MirrorExpected = d.MirrorExplicitID + 1
	d.MirrorPass = mirrorID == d.MirrorExpected
	return nil
}

// floatTrap 采集「float 直接相加」与「逐行取整到分再相加」的差异证据。
//
// 三组数字：
//
//	· Σ ROUND(x,2)  —— 逐行取整后相加（迁移口径，也是双跑对账口径）
//	· ROUND(Σ x, 2) —— 先相加再取整（旧 SP 常见口径，与上面可能差 1 分）
//	· float64 直接累加 —— 展示二进制浮点的累加误差
func floatTrap(ctx context.Context, legacy *sql.DB, userID int64) (FloatTrapDemo, error) {
	var d FloatTrapDemo

	// 逐条取值（3 位小数原文）
	rows, err := legacy.QueryContext(ctx, `
SELECT CAST(money AS DECIMAL(20,3))
  FROM t_user_expense_log WHERE userid = ? ORDER BY id`, userID)
	if err != nil {
		return d, err
	}
	var raws []string
	for rows.Next() {
		var s string
		if err := rows.Scan(&s); err != nil {
			rows.Close()
			return d, err
		}
		raws = append(raws, s)
	}
	rows.Close()
	if err := rows.Err(); err != nil {
		return d, err
	}
	d.LegacyValues = raws

	var exactThousandths int64
	var floatSum float64
	for _, raw := range raws {
		th, err := thousandthsOf(raw)
		if err != nil {
			return d, err
		}
		exactThousandths += th
		floatSum += float64Of(raw)

		c, err := DecimalToCents(raw)
		if err != nil {
			return d, err
		}
		d.PerRowRounded = append(d.PerRowRounded, CentsToDecimal(c))
		d.SumOfRoundedCents += c
	}
	d.SumRoundedStr = CentsToDecimal(d.SumOfRoundedCents)
	d.ExactSumThousandths = exactThousandths
	d.FloatSumRaw = floatSum
	d.FloatAccumErr = floatSum - float64(exactThousandths)/1000

	// 服务端口径：ROUND(Σ x, 2) —— 与 Σ ROUND(x,2) 可能差 1 分（这就是「先加后取整」的坑）
	var roundOfSum string
	if err := legacy.QueryRowContext(ctx,
		`SELECT CAST(SUM(money) AS DECIMAL(12,2)) FROM t_user_expense_log WHERE userid = ?`,
		userID).Scan(&roundOfSum); err != nil {
		return d, err
	}
	d.RoundOfSumStr = roundOfSum
	rc, err := DecimalToCents(roundOfSum)
	if err != nil {
		return d, err
	}
	d.DeltaVsPerRow = rc - d.SumOfRoundedCents
	return d, nil
}

// SortedTableNames 返回核对表出现过的表名（去重后排序），供报告分节打印。
func (r *VerifyReport) SortedTableNames() []string {
	seen := map[string]bool{}
	var out []string
	for _, c := range r.Checks {
		if !seen[c.Table] {
			seen[c.Table] = true
			out = append(out, c.Table)
		}
	}
	sort.Strings(out)
	return out
}

// FormatFloat 用 %.17g 展示 float64，暴露二进制尾差。
func FormatFloat(v float64) string {
	if math.IsNaN(v) {
		return "NaN"
	}
	return strconv.FormatFloat(v, 'g', 17, 64)
}

// NowTruncSec 仅用于报告时间戳。
func NowTruncSec() time.Time { return time.Now().Truncate(time.Second) }