package fixture

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

// 本文件是第二批的写入层，只写 5 张表：
//   t_expenditure（字典，D1）/ t_user_divide_into / t_user_divide_into_log /
//   t_user_balance_request / t_expenditure_log
//
// 两种主键口径各自的幂等写法，是本批的核心：
//   · D1（显式插 id）：主键就是 legacy id，天然幂等 → `ON DUPLICATE KEY UPDATE` 命中 PK
//   · biz_no：target 的 id 由自增发号，幂等键是 `uk_biz_no` 唯一键 → `ON DUPLICATE KEY UPDATE`
//     命中唯一键时 **MySQL 只更新指定列，不会改写 id**，所以重跑 id 不变。
//     切记不能写成纯 `INSERT`——那会在第二次跑报 1062，或（若无唯一键）插出重复行。

// upsertExpenditureDict 写收支类型字典（D1）。
func upsertExpenditureDict(ctx context.Context, db *sql.DB, r TargetExpenditure) error {
	const q = `
INSERT INTO t_expenditure (id, type, parent_id, name, remark, institution_id)
VALUES (?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  type           = VALUES(type),
  parent_id      = VALUES(parent_id),
  name           = VALUES(name),
  remark         = VALUES(remark),
  institution_id = VALUES(institution_id)`
	if _, err := db.ExecContext(ctx, q, r.ID, r.Type, r.ParentID, r.Name,
		nullStr(r.Remark), r.InstitutionID); err != nil {
		return fmt.Errorf("写 t_expenditure(id=%d) 失败：%w", r.ID, err)
	}
	return nil
}

// upsertDivideInto 写分账树节点（自然键 user_id）。
func upsertDivideInto(ctx context.Context, db *sql.DB, r TargetDivideInto) error {
	const q = `
INSERT INTO t_user_divide_into
  (user_id, type, quota, total_income, total_income_payed,
   root_user_id, parent_user_id, left_val, right_val, layer, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  type               = VALUES(type),
  quota              = VALUES(quota),
  total_income       = VALUES(total_income),
  total_income_payed = VALUES(total_income_payed),
  root_user_id       = VALUES(root_user_id),
  parent_user_id     = VALUES(parent_user_id),
  left_val           = VALUES(left_val),
  right_val          = VALUES(right_val),
  layer              = VALUES(layer)`
	_, err := db.ExecContext(ctx, q, r.UserID, r.Type,
		CentsToDecimal(r.QuotaCents), CentsToDecimal(r.TotalIncomeCents),
		CentsToDecimal(r.TotalIncomePayedCents),
		nullStr(r.RootUserID), nullStr(r.ParentUserID),
		r.LeftVal, r.RightVal, r.Layer,
		r.CreatedAt.Format("2006-01-02 15:04:05"), r.UpdatedAt.Format("2006-01-02 15:04:05"))
	if err != nil {
		return fmt.Errorf("写 t_user_divide_into(user=%s) 失败：%w", r.UserID, err)
	}
	return nil
}

// upsertDivideIntoLog 写分账明细（D1：显式插 legacy id）。
func upsertDivideIntoLog(ctx context.Context, db *sql.DB, r TargetDivideIntoLog) error {
	const q = `
INSERT INTO t_user_divide_into_log
  (id, type, user_id, student_id, order_no, money, create_time, remark)
VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  type        = VALUES(type),
  user_id     = VALUES(user_id),
  student_id  = VALUES(student_id),
  order_no    = VALUES(order_no),
  money       = VALUES(money),
  create_time = VALUES(create_time),
  remark      = VALUES(remark)`
	_, err := db.ExecContext(ctx, q, r.ID, r.Type, r.UserID, r.StudentID,
		nullStr(r.OrderNo), CentsToDecimal(r.MoneyCents),
		r.CreateTime.Format("2006-01-02 15:04:05"), nullStr(r.Remark))
	if err != nil {
		return fmt.Errorf("写 t_user_divide_into_log(id=%d) 失败：%w", r.ID, err)
	}
	return nil
}

// upsertBalanceRequest 写充值/退费工单（**biz_no 口径**）。
//
// biz_no 是唯一键 uk_biz_no；命中时只更新业务列，**id 保持首次分配值**。
func upsertBalanceRequest(ctx context.Context, db *sql.DB, r TargetBalanceRequest) error {
	const q = `
INSERT INTO t_user_balance_request
  (biz_no, type, user_id, institution_id, campus_id, to_institution_id, to_campus_id,
   money, transfer_accounts_type, transfer_accounts_time, order_no,
   request_user_id, request_time, response_user_id, response_time,
   response_msg, approved, item, imgs, remark, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  type                    = VALUES(type),
  user_id                 = VALUES(user_id),
  institution_id          = VALUES(institution_id),
  campus_id               = VALUES(campus_id),
  to_institution_id       = VALUES(to_institution_id),
  to_campus_id            = VALUES(to_campus_id),
  money                   = VALUES(money),
  transfer_accounts_type  = VALUES(transfer_accounts_type),
  transfer_accounts_time  = VALUES(transfer_accounts_time),
  order_no                = VALUES(order_no),
  request_user_id         = VALUES(request_user_id),
  request_time            = VALUES(request_time),
  response_user_id        = VALUES(response_user_id),
  response_time           = VALUES(response_time),
  response_msg            = VALUES(response_msg),
  approved                = VALUES(approved),
  item                    = VALUES(item),
  imgs                    = VALUES(imgs),
  remark                  = VALUES(remark)`
	_, err := db.ExecContext(ctx, q,
		r.BizNo, r.Type, r.UserID, r.InstitutionID, r.CampusID,
		nullInt(r.ToInstitutionID), nullInt(r.ToCampusID),
		CentsToDecimal(r.MoneyCents), r.TransferAccountsType,
		nullTime(r.TransferAccountsTime), nullStr(r.OrderNo),
		r.RequestUserID, r.RequestTime.Format("2006-01-02 15:04:05"),
		nullStr(r.ResponseUserID), nullTime(r.ResponseTime),
		nullStr(r.ResponseMsg), r.Approved, nullStr(r.Item), nullStr(r.Imgs), nullStr(r.Remark),
		r.CreatedAt.Format("2006-01-02 15:04:05"), r.UpdatedAt.Format("2006-01-02 15:04:05"))
	if err != nil {
		return fmt.Errorf("写 t_user_balance_request(biz_no=%s) 失败：%w", r.BizNo, err)
	}
	return nil
}

// upsertExpenditureLog 写收支记录（**biz_no 口径**）。
func upsertExpenditureLog(ctx context.Context, db *sql.DB, r TargetExpenditureLog) error {
	const q = `
INSERT INTO t_expenditure_log
  (biz_no, institution_id, campus_id, expenditure_id, cost,
   create_user_id, create_time, year, month, remark, imgs)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
  institution_id  = VALUES(institution_id),
  campus_id       = VALUES(campus_id),
  expenditure_id  = VALUES(expenditure_id),
  cost            = VALUES(cost),
  create_user_id  = VALUES(create_user_id),
  create_time     = VALUES(create_time),
  year            = VALUES(year),
  month           = VALUES(month),
  remark          = VALUES(remark),
  imgs            = VALUES(imgs)`
	_, err := db.ExecContext(ctx, q, r.BizNo, r.InstitutionID, r.CampusID, r.ExpenditureID,
		CentsToDecimal(r.CostCents), nullStr(r.CreateUserID),
		r.CreateTime.Format("2006-01-02 15:04:05"),
		nullInt(r.Year), nullInt(r.Month), nullStr(r.Remark), nullStr(r.Imgs))
	if err != nil {
		return fmt.Errorf("写 t_expenditure_log(biz_no=%s) 失败：%w", r.BizNo, err)
	}
	return nil
}

func nullInt(v sql.NullInt64) any {
	if !v.Valid {
		return nil
	}
	return v.Int64
}

func nullTime(v sql.NullTime) any {
	if !v.Valid {
		return nil
	}
	return v.Time.Format("2006-01-02 15:04:05")
}