DEV-MERGE-LEGACY-BACKEND-2026/internal/migrate/verify.go

package migrate

import (
	"context"
	"fmt"
	"strings"

	"gorm.io/driver/mysql"
	"gorm.io/gorm"

	identitymodel "yuansiwei/unified-backend/internal/identity/model"
	orgmodel "yuansiwei/unified-backend/internal/org/model"
)

// Verifier 目标态一致性校验器（run 后门禁，P1-04 验收"重跑幂等 + 事务边界正确"）。
//
// 校验项：
//  1. 计数对账：t_user / t_identity / t_wechat_union / t_membership / t_legacy_mapping 行数；
//  2. 唯一键完整性：phone 撞号（同 identifier 多 canonical）/ unionid 重复 / username 重复 /
//     canonical_user_id 在 legacy_mapping 重复；
//  3. 引用完整性：t_identity / t_membership 的 canonical_user_id 必须存在于 t_user。
type Verifier struct {
	target *gorm.DB
}

// NewVerifier 构建校验器（连目标库）。
func NewVerifier(targetDSN string) (*Verifier, error) {
	if targetDSN == "" {
		return nil, fmt.Errorf("migrate: 目标库 DSN 为空")
	}
	gdb, err := gorm.Open(mysql.Open(targetDSN), &gorm.Config{
		DisableForeignKeyConstraintWhenMigrating: true,
	})
	if err != nil {
		return nil, fmt.Errorf("migrate: 目标库连接失败: %w", err)
	}
	if sqlDB, err := gdb.DB(); err == nil {
		if err := sqlDB.Ping(); err != nil {
			return nil, fmt.Errorf("migrate: 目标库 Ping 失败: %w", err)
		}
	}
	return &Verifier{target: gdb}, nil
}

// Close 关闭连接。
func (v *Verifier) Close() error {
	if v.target == nil {
		return nil
	}
	if sqlDB, err := v.target.DB(); err == nil {
		return sqlDB.Close()
	}
	return nil
}

// VerifyReport 校验报告。
type VerifyReport struct {
	// 计数
	UsersCount      int64
	IdentitiesCount int64
	WechatUnion     int64
	Memberships     int64
	LegacyMappings  int64
	Institutions    int64
	Campuses        int64

	// 唯一键完整性违规（应为 0）
	PhoneCollisions      []string // 同 identifier 多 canonical 的 phone
	UnionidDuplicates    []string // 同 unionid 多 canonical
	UsernameDuplicates   []string // 同 username 多 canonical
	CanonicalDups        []string // legacy_mapping 中同 canonical 多行
	OrphanIdentities     int64    // t_identity.canonical_user_id 不在 t_user
	OrphanMemberships    int64    // t_membership.canonical_user_id 不在 t_user
}

// OK 是否全部通过（所有违规项为 0 / 空）。
func (r *VerifyReport) OK() bool {
	return len(r.PhoneCollisions) == 0 &&
		len(r.UnionidDuplicates) == 0 &&
		len(r.UsernameDuplicates) == 0 &&
		len(r.CanonicalDups) == 0 &&
		r.OrphanIdentities == 0 &&
		r.OrphanMemberships == 0
}

// String 渲染为可读报告。
func (r *VerifyReport) String() string {
	var b strings.Builder
	b.WriteString("=== 目标态一致性校验 ===\n")
	b.WriteString(fmt.Sprintf("t_user=%d t_identity=%d t_wechat_union=%d t_membership=%d t_legacy_mapping=%d\n",
		r.UsersCount, r.IdentitiesCount, r.WechatUnion, r.Memberships, r.LegacyMappings))
	b.WriteString(fmt.Sprintf("t_institution=%d t_campus=%d\n", r.Institutions, r.Campuses))

	pass := true
	if len(r.PhoneCollisions) > 0 {
		pass = false
		b.WriteString(fmt.Sprintf("[FAIL] phone 撞号 %d 组:\n", len(r.PhoneCollisions)))
		for _, p := range r.PhoneCollisions {
			b.WriteString("  " + p + "\n")
		}
	}
	if len(r.UnionidDuplicates) > 0 {
		pass = false
		b.WriteString(fmt.Sprintf("[FAIL] unionid 重复 %d 项:\n", len(r.UnionidDuplicates)))
		for _, u := range r.UnionidDuplicates {
			b.WriteString("  " + u + "\n")
		}
	}
	if len(r.UsernameDuplicates) > 0 {
		pass = false
		b.WriteString(fmt.Sprintf("[FAIL] username 重复 %d 项:\n", len(r.UsernameDuplicates)))
		for _, u := range r.UsernameDuplicates {
			b.WriteString("  " + u + "\n")
		}
	}
	if len(r.CanonicalDups) > 0 {
		pass = false
		b.WriteString(fmt.Sprintf("[FAIL] legacy_mapping canonical 重复 %d 项:\n", len(r.CanonicalDups)))
		for _, c := range r.CanonicalDups {
			b.WriteString("  " + c + "\n")
		}
	}
	if r.OrphanIdentities > 0 {
		pass = false
		b.WriteString(fmt.Sprintf("[FAIL] t_identity 孤儿 canonical（不在 t_user）%d 条\n", r.OrphanIdentities))
	}
	if r.OrphanMemberships > 0 {
		pass = false
		b.WriteString(fmt.Sprintf("[FAIL] t_membership 孤儿 canonical（不在 t_user）%d 条\n", r.OrphanMemberships))
	}
	if pass {
		b.WriteString("[PASS] 全部唯一键/引用完整性校验通过\n")
	}
	return b.String()
}

// Verify 执行全部校验项。
func (v *Verifier) Verify(ctx context.Context) (*VerifyReport, error) {
	rep := &VerifyReport{}

	// 1. 计数
	var n int64
	_ = v.target.WithContext(ctx).Table("t_user").Count(&n).Error
	rep.UsersCount = n
	_ = v.target.WithContext(ctx).Table("t_identity").Count(&n).Error
	rep.IdentitiesCount = n
	_ = v.target.WithContext(ctx).Table("t_wechat_union").Count(&n).Error
	rep.WechatUnion = n
	_ = v.target.WithContext(ctx).Table("t_membership").Count(&n).Error
	rep.Memberships = n
	_ = v.target.WithContext(ctx).Table("t_legacy_mapping").Count(&n).Error
	rep.LegacyMappings = n
	_ = v.target.WithContext(ctx).Table("t_institution").Count(&n).Error
	rep.Institutions = n
	_ = v.target.WithContext(ctx).Table("t_campus").Count(&n).Error
	rep.Campuses = n

	// 2. phone 撞号：t_identity 中 type=phone 且同 identifier 出现 >1 个不同 canonical
	//    （phone 唯一键不含 canonical，撞号时唯一键应阻止多行；若存在 = 唯一键失效或历史脏数据）。
	//    ONLY_FULL_GROUP_BY 兼容：仅 SELECT 分组列（identifier），不 SELECT *。
	var phoneCols []string
	if err := v.target.WithContext(ctx).
		Raw("SELECT identifier FROM t_identity WHERE type = ? GROUP BY identifier HAVING COUNT(DISTINCT canonical_user_id) > 1",
			identitymodel.IdentityTypePhone).
		Scan(&phoneCols).Error; err != nil {
		return rep, fmt.Errorf("migrate: phone 撞号校验失败: %w", err)
	}
	rep.PhoneCollisions = phoneCols

	// 3. unionid 重复：t_wechat_union 同 unionid 多 canonical（uk_unionid 应阻止；存在 = 脏数据）
	var unionCols []string
	if err := v.target.WithContext(ctx).
		Raw("SELECT unionid FROM t_wechat_union GROUP BY unionid HAVING COUNT(DISTINCT canonical_user_id) > 1").
		Scan(&unionCols).Error; err != nil {
		return rep, fmt.Errorf("migrate: unionid 重复校验失败: %w", err)
	}
	rep.UnionidDuplicates = unionCols

	// 4. username 重复：t_user 同 username 多 canonical（uk_username 应阻止；存在 = 脏数据）
	var userCols []string
	if err := v.target.WithContext(ctx).
		Raw("SELECT username FROM t_user WHERE username IS NOT NULL AND username <> '' GROUP BY username HAVING COUNT(DISTINCT canonical_user_id) > 1").
		Scan(&userCols).Error; err != nil {
		return rep, fmt.Errorf("migrate: username 重复校验失败: %w", err)
	}
	rep.UsernameDuplicates = userCols

	// 5. legacy_mapping canonical 重复：同 canonical_user_id 多行（uk_canonical 应阻止）
	var mappingCols []string
	if err := v.target.WithContext(ctx).
		Raw("SELECT canonical_user_id FROM t_legacy_mapping GROUP BY canonical_user_id HAVING COUNT(*) > 1").
		Scan(&mappingCols).Error; err != nil {
		return rep, fmt.Errorf("migrate: legacy_mapping canonical 重复校验失败: %w", err)
	}
	rep.CanonicalDups = mappingCols

	// 6. 引用完整性：t_identity.canonical_user_id 不在 t_user
	if err := v.target.WithContext(ctx).
		Raw("SELECT COUNT(DISTINCT i.canonical_user_id) FROM t_identity i LEFT JOIN t_user u ON u.canonical_user_id = i.canonical_user_id WHERE u.canonical_user_id IS NULL").
		Scan(&rep.OrphanIdentities).Error; err != nil {
		return rep, fmt.Errorf("migrate: 引用完整性校验(identity)失败: %w", err)
	}
	// 7. t_membership.canonical_user_id 不在 t_user
	if err := v.target.WithContext(ctx).
		Raw("SELECT COUNT(DISTINCT m.canonical_user_id) FROM t_membership m LEFT JOIN t_user u ON u.canonical_user_id = m.canonical_user_id WHERE u.canonical_user_id IS NULL").
		Scan(&rep.OrphanMemberships).Error; err != nil {
		return rep, fmt.Errorf("migrate: 引用完整性校验(membership)失败: %w", err)
	}
	_ = orgmodel.Membership{} // 保持 orgmodel 引用（校验仅用 raw SQL，model 备用）
	return rep, nil
}