package fixture

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

// 本文件是**唯一**往 yuansiwei_2026 写数据的地方，且只写 5 张表：
//   t_user / t_legacy_mapping / t_user_expense / t_user_expense_school / t_user_expense_log
//
// 所有写入都用幂等写法（ON DUPLICATE KEY UPDATE），理由：
//   · map/verify/clean 是 3 个独立命令，中途失败要能重跑；
//   · 变异实验要跑多轮「红→绿」，每轮都从同一起点开始。

// upsertUser 插入 fixture 用的 t_user 行（最小列集）。
//
// t_user 的 NOT NULL 列只有 canonical_user_id / person_type / status / created_at / updated_at，
// 后三者都有默认值，因此最少只需给 canonical_user_id（+ 便于识别的 display_name）。
func upsertUser(ctx context.Context, db *sql.DB, canonicalUserID, displayName string) error {
	const q = `
INSERT INTO t_user (canonical_user_id, username, display_name, person_type, status)
VALUES (?, NULL, ?, 'student', 'active')
ON DUPLICATE KEY UPDATE display_name = VALUES(display_name)`
	if _, err := db.ExecContext(ctx, q, canonicalUserID, displayName); err != nil {
		return fmt.Errorf("写 t_user(%s) 失败：%w", canonicalUserID, err)
	}
	return nil
}

// upsertMapping 插入 t_legacy_mapping 行（legacy int id → ULID）。
//
// 唯一键是 uk_canonical(canonical_user_id)，因此重复执行只会更新同一行。
func upsertMapping(ctx context.Context, db *sql.DB, m MappingEntry) error {
	const q = `
INSERT INTO t_legacy_mapping
  (canonical_user_id, legacy_academic_user_id, institution_id, campus_id,
   role, match_source, match_confidence, merged_at)
VALUES (?, ?, ?, ?, ?, ?, ?, NOW(3))
ON DUPLICATE KEY UPDATE
  legacy_academic_user_id = VALUES(legacy_academic_user_id),
  institution_id          = VALUES(institution_id),
  campus_id               = VALUES(campus_id),
  role                    = VALUES(role),
  match_source            = VALUES(match_source),
  match_confidence        = VALUES(match_confidence)`
	if _, err := db.ExecContext(ctx, q, m.CanonicalUserID, m.LegacyAcademicUserID,
		m.InstitutionID, m.CampusID, nullIfEmpty(m.Role), m.MatchSource, m.MatchConfidence); err != nil {
		return fmt.Errorf("写 t_legacy_mapping(legacy=%d) 失败：%w", m.LegacyAcademicUserID, err)
	}
	return nil
}

// upsertExpense 写全局账户行（P）：金额以十进制字符串写入 DECIMAL(12,2)。
func upsertExpense(ctx context.Context, db *sql.DB, r TargetExpense) error {
	const q = `
INSERT INTO t_user_expense
  (user_id, balance, spend, total_income, total_expenditure,
   total_recommended_amount, special_expenses)
VALUES (?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  balance                  = VALUES(balance),
  spend                    = VALUES(spend),
  total_income             = VALUES(total_income),
  total_expenditure        = VALUES(total_expenditure),
  total_recommended_amount = VALUES(total_recommended_amount),
  special_expenses         = VALUES(special_expenses)`
	_, err := db.ExecContext(ctx, q, r.UserID,
		CentsToDecimal(r.BalanceCents),
		CentsToDecimal(r.SpendCents),
		CentsToDecimal(r.TotalIncomeCents),
		CentsToDecimal(r.TotalExpenditureCents),
		CentsToDecimal(r.TotalRecommendedCents),
		CentsToDecimal(r.SpecialExpensesCents))
	if err != nil {
		return fmt.Errorf("写 t_user_expense(user=%s) 失败：%w", r.UserID, err)
	}
	return nil
}

// upsertExpenseSchool 写校区账户行。
//
// id 不显式指定：该表的对齐键是 uk_user_inst_campus，靠唯一键做幂等。
func upsertExpenseSchool(ctx context.Context, db *sql.DB, r TargetExpenseSchool) error {
	const q = `
INSERT INTO t_user_expense_school
  (user_id, institution_id, campus_id, balance, spend, total_income,
   total_expenditure, total_recommended_amount, special_expenses)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  balance                  = VALUES(balance),
  spend                    = VALUES(spend),
  total_income             = VALUES(total_income),
  total_expenditure        = VALUES(total_expenditure),
  total_recommended_amount = VALUES(total_recommended_amount),
  special_expenses         = VALUES(special_expenses)`
	_, err := db.ExecContext(ctx, q, r.UserID, r.InstitutionID, r.CampusID,
		CentsToDecimal(r.BalanceCents),
		CentsToDecimal(r.SpendCents),
		CentsToDecimal(r.TotalIncomeCents),
		CentsToDecimal(r.TotalExpenditureCents),
		CentsToDecimal(r.TotalRecommendedCents),
		CentsToDecimal(r.SpecialExpensesCents))
	if err != nil {
		return fmt.Errorf("写 t_user_expense_school(user=%s,campus=%d) 失败：%w", r.UserID, r.CampusID, err)
	}
	return nil
}

// insertExpenseLog 写流水行（D1：**显式插入 legacy id**）。
//
// 幂等键是 PRIMARY KEY(id)=legacy id，天然可重跑。
// 注意：这里刻意**不**依赖 AUTO_INCREMENT 生成 id——id 由 legacy 决定。
func insertExpenseLog(ctx context.Context, db *sql.DB, r TargetExpenseLog) error {
	const q = `
INSERT INTO t_user_expense_log
  (id, user_id, type, log_id, request_id, money, balance_after, create_user_id, create_time, remark)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  user_id        = VALUES(user_id),
  type           = VALUES(type),
  log_id         = VALUES(log_id),
  request_id     = VALUES(request_id),
  money          = VALUES(money),
  balance_after  = VALUES(balance_after),
  create_user_id = VALUES(create_user_id),
  create_time    = VALUES(create_time),
  remark         = VALUES(remark)`
	_, err := db.ExecContext(ctx, q,
		r.ID, r.UserID, r.Type,
		nullStr(r.LogID), nullStr(r.RequestID),
		CentsToDecimal(r.MoneyCents),
		nullStr(r.BalanceAfter), nullStr(r.CreateUserID),
		r.CreateTime.Format("2006-01-02 15:04:05"),
		nullStr(r.Remark))
	if err != nil {
		return fmt.Errorf("写 t_user_expense_log(id=%d) 失败：%w", r.ID, err)
	}
	return nil
}

// nullIfEmpty 把空串转 NULL（role 之类的可选列用）。
func nullIfEmpty(s string) any {
	if strings.TrimSpace(s) == "" {
		return nil
	}
	return s
}

// nullStr 把 sql.NullString 转成可交给驱动的 any（NULL 或 string）。
func nullStr(v sql.NullString) any {
	if !v.Valid {
		return nil
	}
	return v.String
}