package fixture

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

// 本文件是第二批的 legacy **只读**查询。金额列一律 `CAST(... AS DECIMAL(20,3))`，
// 浮点误差在读取源头就被掐掉（与上一批同一口径）。
//
// fixture 取样范围（可由 CLI 覆盖）：
//   分账树：root_userid = 395 的整棵子树（8 个节点：1 根 + 7 子）
//   分账明细：userid = 395 的 4 条
//   充值工单：userid = 31348 的 8 条（含 response_userid=0、3 位小数金额、NULL 时间）
//   收支记录：create_userid = 395 的 10 条（含 3 位小数 cost、字典引用 15/23）
//   收支字典：全表 45 条（解析 expenditure_id 用）

// Batch2Options 是第二批 fixture 的取样参数。
type Batch2Options struct {
	DivideRootUserID     int64 // 分账树根（取整棵子树）
	BalanceRequestUserID int64 // 充值工单所属用户
	ExpenditureLogUserID int64 // 收支记录创建人
	WithExpenditureDict  bool  // 是否同时 D1 迁移 t_expenditure 字典（解析 expenditure_id 必需）
}

// DefaultBatch2Options 是默认取样（选择依据见 README §7）。
func DefaultBatch2Options() Batch2Options {
	return Batch2Options{
		DivideRootUserID:     395,
		BalanceRequestUserID: 31348,
		ExpenditureLogUserID: 395,
		WithExpenditureDict:  true,
	}
}

func extractDivides(ctx context.Context, db *sql.DB, rootUserID int64) ([]LegacyDivideInto, error) {
	const q = `
SELECT userid, cast(ifnull(type,1) as signed),
       CAST(ifnull(quota,0)              AS DECIMAL(20,3)),
       CAST(ifnull(total_income,0)       AS DECIMAL(20,3)),
       CAST(ifnull(total_income_payed,0) AS DECIMAL(20,3)),
       root_userid, parent_userid,
       cast(ifnull(left_val,1) as signed), cast(ifnull(right_val,2) as signed),
       cast(ifnull(layer,1) as signed)
  FROM t_user_divide_into
 WHERE root_userid = ?
 ORDER BY left_val, userid`
	rows, err := db.QueryContext(ctx, q, rootUserID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []LegacyDivideInto
	for rows.Next() {
		var r LegacyDivideInto
		if err := rows.Scan(&r.UserID, &r.Type, &r.Quota, &r.TotalIncome, &r.TotalIncomePayed,
			&r.RootUserID, &r.ParentUserID, &r.LeftVal, &r.RightVal, &r.Layer); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

func extractDivideLogs(ctx context.Context, db *sql.DB, userID int64) ([]LegacyDivideIntoLog, error) {
	const q = `
SELECT id, cast(type as signed), userid, studentid, orderid,
       CAST(ifnull(money,0) AS DECIMAL(20,3)),
       create_time, remark
  FROM t_user_divide_into_log
 WHERE userid = ?
 ORDER BY id`
	rows, err := db.QueryContext(ctx, q, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []LegacyDivideIntoLog
	for rows.Next() {
		var r LegacyDivideIntoLog
		if err := rows.Scan(&r.ID, &r.Type, &r.UserID, &r.StudentID, &r.OrderNo,
			&r.Money, &r.CreateTime, &r.Remark); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

func extractBalanceRequests(ctx context.Context, db *sql.DB, userID int64) ([]LegacyBalanceRequest, error) {
	const q = `
SELECT id, type, userid, schoolid, campusid,
       CAST(money AS DECIMAL(20,3)),
       transfer_accounts_type, transfer_accounts_time, orderid,
       request_userid, request_time, response_userid, response_time,
       response_msg, aproved, item, imgs, remark
  FROM t_user_balance_request
 WHERE userid = ?
 ORDER BY request_time, id`
	rows, err := db.QueryContext(ctx, q, userID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []LegacyBalanceRequest
	for rows.Next() {
		var r LegacyBalanceRequest
		if err := rows.Scan(&r.BizNo, &r.Type, &r.UserID, &r.SchoolID, &r.CampusID, &r.Money,
			&r.TransferAccountsType, &r.TransferAccountsTime, &r.OrderNo,
			&r.RequestUserID, &r.RequestTime, &r.ResponseUserID, &r.ResponseTime,
			&r.ResponseMsg, &r.Aproved, &r.Item, &r.Imgs, &r.Remark); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

func extractExpenditureLogs(ctx context.Context, db *sql.DB, createUserID int64) ([]LegacyExpenditureLog, error) {
	const q = `
SELECT id, schoolid, campusid, expenditureid,
       CAST(cost AS DECIMAL(20,3)),
       create_userid, create_time, year, month, remark, imgs
  FROM t_expenditure_log
 WHERE create_userid = ?
 ORDER BY id`
	rows, err := db.QueryContext(ctx, q, createUserID)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []LegacyExpenditureLog
	for rows.Next() {
		var r LegacyExpenditureLog
		if err := rows.Scan(&r.BizNo, &r.SchoolID, &r.CampusID, &r.ExpenditureID, &r.Cost,
			&r.CreateUserID, &r.CreateTime, &r.Year, &r.Month, &r.Remark, &r.Imgs); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

func extractExpenditures(ctx context.Context, db *sql.DB) ([]LegacyExpenditure, error) {
	const q = `SELECT id, type, parentid, name, remark FROM t_expenditure ORDER BY id`
	rows, err := db.QueryContext(ctx, q)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var out []LegacyExpenditure
	for rows.Next() {
		var r LegacyExpenditure
		if err := rows.Scan(&r.ID, &r.Type, &r.ParentID, &r.Name, &r.Remark); err != nil {
			return nil, err
		}
		out = append(out, r)
	}
	return out, rows.Err()
}

// collectUserRefs 汇总这批数据里出现的**全部 legacy 用户引用**，
// 用于一次性补 t_legacy_mapping / t_user（否则引用列会全退化成 NULL）。
func collectUserRefs(divides []LegacyDivideInto, dlog []LegacyDivideIntoLog,
	brs []LegacyBalanceRequest, elogs []LegacyExpenditureLog) []int64 {
	set := map[int64]bool{}
	add := func(v int64) {
		if v > 0 {
			set[v] = true
		}
	}
	for _, d := range divides {
		add(d.UserID)
		if d.RootUserID.Valid {
			add(d.RootUserID.Int64)
		}
		if d.ParentUserID.Valid {
			add(d.ParentUserID.Int64)
		}
	}
	for _, l := range dlog {
		add(l.UserID)
		add(l.StudentID)
	}
	for _, r := range brs {
		add(r.UserID)
		add(r.RequestUserID)
		if r.ResponseUserID.Valid {
			add(r.ResponseUserID.Int64)
		}
	}
	for _, e := range elogs {
		if e.CreateUserID.Valid {
			add(e.CreateUserID.Int64)
		}
	}
	out := make([]int64, 0, len(set))
	for v := range set {
		out = append(out, v)
	}
	sortInt64s(out)
	return out
}

func sortInt64s(v []int64) {
	for i := 1; i < len(v); i++ {
		for j := i; j > 0 && v[j] < v[j-1]; j-- {
			v[j], v[j-1] = v[j-1], v[j]
		}
	}
}

// countBatch2Legacy 统计这批在 legacy 侧的行数。
func countBatch2Legacy(ctx context.Context, db *sql.DB, opts Batch2Options) (map[string]int, error) {
	out := map[string]int{}
	type q1 struct {
		name string
		sql  string
		arg  int64
	}
	for _, x := range []q1{
		{"t_user_divide_into", `SELECT COUNT(*) FROM t_user_divide_into WHERE root_userid = ?`, opts.DivideRootUserID},
		{"t_user_divide_into_log", `SELECT COUNT(*) FROM t_user_divide_into_log WHERE userid = ?`, opts.DivideRootUserID},
		{"t_user_balance_request", `SELECT COUNT(*) FROM t_user_balance_request WHERE userid = ?`, opts.BalanceRequestUserID},
		{"t_expenditure_log", `SELECT COUNT(*) FROM t_expenditure_log WHERE create_userid = ?`, opts.ExpenditureLogUserID},
	} {
		var n int
		if err := db.QueryRowContext(ctx, x.sql, x.arg).Scan(&n); err != nil {
			return nil, fmt.Errorf("%s：%w", x.name, err)
		}
		out[x.name] = n
	}
	var n int
	if err := db.QueryRowContext(ctx, `SELECT COUNT(*) FROM t_expenditure`).Scan(&n); err != nil {
		return nil, err
	}
	out["t_expenditure"] = n
	return out, nil
}