package fixture

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

// 本文件只发 SELECT（legacy 侧），且所有金额列一律 `CAST(... AS DECIMAL(20,3))`。
//
// 为什么不直接 `SELECT balance`：
//   balance 是 float(10,3)。go-sql-driver 会把它当 float64 交给 Go，
//   于是 0.788 变成 0.78800000000000003，后面任何 Round 都在误差上做决策。
//   `CAST(x AS DECIMAL(20,3))` 让 MySQL 先把该列按「3 位小数的十进制」输出，
//   我们再按字符串精确换成分——误差在源头就被掐掉。

// extractExpense 读 1 个账户行。
func extractExpense(ctx context.Context, db *sql.DB, userID int64) (*LegacyExpense, error) {
	const q = `
SELECT userid,
       CAST(balance                 AS DECIMAL(20,3)),
       CAST(spend                   AS DECIMAL(20,3)),
       CAST(total_income            AS DECIMAL(20,3)),
       CAST(total_expenditure       AS DECIMAL(20,3)),
       CAST(total_recommended_amount AS DECIMAL(20,3)),
       CAST(special_expenses        AS DECIMAL(20,3))
  FROM t_user_expense
 WHERE userid = ?`
	row := db.QueryRowContext(ctx, q, userID)
	var out LegacyExpense
	if err := row.Scan(&out.UserID, &out.Balance, &out.Spend, &out.TotalIncome,
		&out.TotalExpenditure, &out.TotalRecommendedAmount, &out.SpecialExpenses); err != nil {
		if err == sql.ErrNoRows {
			return nil, fmt.Errorf("legacy t_user_expense 无 userid=%d 的行", userID)
		}
		return nil, err
	}
	return &out, nil
}

// extractExpenseSchools 读该用户的全部校区账户行（按 legacy id 升序，保证可复现）。
func extractExpenseSchools(ctx context.Context, db *sql.DB, userID int64) ([]LegacyExpenseSchool, error) {
	const q = `
SELECT id, userid, schoolid, cast(ifnull(campusid,0) as signed),
       CAST(balance                 AS DECIMAL(20,3)),
       CAST(spend                   AS DECIMAL(20,3)),
       CAST(total_income            AS DECIMAL(20,3)),
       CAST(total_expenditure       AS DECIMAL(20,3)),
       CAST(total_recommended_amount AS DECIMAL(20,3)),
       CAST(special_expenses        AS DECIMAL(20,3))
  FROM t_user_expense_school
 WHERE userid = ?
 ORDER BY id`
	rows, err := db.QueryContext(ctx, q, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []LegacyExpenseSchool
	for rows.Next() {
		var r LegacyExpenseSchool
		if err := rows.Scan(&r.ID, &r.UserID, &r.SchoolID, &r.CampusID,
			&r.Balance, &r.Spend, &r.TotalIncome, &r.TotalExpenditure,
			&r.TotalRecommendedAmount, &r.SpecialExpenses); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// extractExpenseLogs 读该用户的全部流水（按 legacy id 升序）。
func extractExpenseLogs(ctx context.Context, db *sql.DB, userID int64) ([]LegacyExpenseLog, error) {
	const q = `
SELECT id, userid, cast(ifnull(type,0) as signed), logid,
       CAST(money AS DECIMAL(20,3)),
       create_userid, create_time, remark
  FROM t_user_expense_log
 WHERE userid = ?
 ORDER BY id`
	rows, err := db.QueryContext(ctx, q, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()

	var out []LegacyExpenseLog
	for rows.Next() {
		var r LegacyExpenseLog
		if err := rows.Scan(&r.ID, &r.UserID, &r.Type, &r.LogID,
			&r.Money, &r.CreateUserID, &r.CreateTime, &r.Remark); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// pickLegacyUser 在未显式指定用户时，按验收要求挑一个用户：
// 有账户行 + 至少 1 个校区账户 + 至少 3 条流水 + balance 带 3 位小数（覆盖取整边界）。
//
// 只用 SELECT，且条件全部来自 information 之外的业务表本身。
func pickLegacyUser(ctx context.Context, db *sql.DB) (int64, error) {
	const q = `
SELECT e.userid
  FROM t_user_expense e
  JOIN (SELECT userid, COUNT(*) n FROM t_user_expense_school GROUP BY userid) s ON s.userid = e.userid
  JOIN (SELECT userid, COUNT(*) n FROM t_user_expense_log GROUP BY userid HAVING n >= 3) l ON l.userid = e.userid
 WHERE ABS(CAST(e.balance * 1000 AS DECIMAL(20,0))) % 100 >= 1
   AND ABS(e.balance) > 0
 ORDER BY e.userid
 LIMIT 1`
	var id int64
	if err := db.QueryRowContext(ctx, q).Scan(&id); err != nil {
		return 0, fmt.Errorf("自动挑选 legacy 用户失败（无满足条件的用户）：%w", err)
	}
	return id, nil
}

// countLegacyRows 统计该用户在 3 张 legacy 表里的行数（报告里展示「搬了几行」）。
func countLegacyRows(ctx context.Context, db *sql.DB, userID int64) (map[string]int, error) {
	out := make(map[string]int, 3)
	for _, q := range []struct {
		name string
		sql  string
	}{
		{"t_user_expense", `SELECT COUNT(*) FROM t_user_expense WHERE userid = ?`},
		{"t_user_expense_school", `SELECT COUNT(*) FROM t_user_expense_school WHERE userid = ?`},
		{"t_user_expense_log", `SELECT COUNT(*) FROM t_user_expense_log WHERE userid = ?`},
	} {
		var n int
		if err := db.QueryRowContext(ctx, q.sql, userID).Scan(&n); err != nil {
			return nil, err
		}
		out[q.name] = n
	}
	return out, nil
}