// BalanceRequestRepo 单测（AC-03）：biz_no 幂等 / 审批 CAS / FOR UPDATE / 分页 / scoping。
//
// 测试栈：glebarez/sqlite 文件库（**只迁 t_user_balance_request 单表**——sqlite 索引名是库级全局，
// `uk_biz_no` 与其它单号表重名，混迁会撞名，原因见 expense_repo_test.go 注释）。
package repository

import (
	"context"
	"errors"
	"path/filepath"
	"strings"
	"testing"
	"time"

	gormsqlite "github.com/glebarez/sqlite"
	"gorm.io/gorm"

	"yuansiwei/unified-backend/internal/academic/model"
	infraerrors "yuansiwei/unified-backend/internal/infra/errors"
	appscope "yuansiwei/unified-backend/internal/infra/scope"
)

// newBalanceRequestRepoDB 工单单表文件库。
func newBalanceRequestRepoDB(t *testing.T) *gorm.DB {
	t.Helper()
	dsn := filepath.Join(t.TempDir(), "academic_balance_request_repo_test.db")
	db, err := gorm.Open(gormsqlite.Open(dsn), &gorm.Config{})
	if err != nil {
		t.Fatalf("gorm.Open(工单文件库) 失败: %v", err)
	}
	t.Cleanup(func() {
		if sqlDB, e := db.DB(); e == nil {
			_ = sqlDB.Close()
		}
	})
	if err := db.AutoMigrate(&model.UserBalanceRequest{}); err != nil {
		t.Fatalf("AutoMigrate(t_user_balance_request) 失败: %v", err)
	}
	return db
}

// TestBalanceRequestInsertAndDuplicate biz_no 幂等：撞 uk_biz_no 识别为重复单号（3003），不抛 5002。
func TestBalanceRequestInsertAndDuplicate(t *testing.T) {
	db := newBalanceRequestRepoDB(t)
	repo := NewBalanceRequestRepo(db, &appscope.Scope{InstitutionID: 1, CampusID: 2})
	runner := NewTxRunner(db)
	ctx := context.Background()

	row := &model.UserBalanceRequest{
		BizNo: "BIZ-0001", Type: model.BalanceRequestTypeRecharge,
		UserID: "01HUSER000000000000000201", Money: "100.00",
		RequestUserID: "01HUSER000000000000000201",
	}
	if err := runner.WithinTx(ctx, func(tx *gorm.DB) error { return repo.Insert(ctx, tx, row) }); err != nil {
		t.Fatalf("创建工单失败: %v", err)
	}
	if row.InstitutionID != 1 || row.CampusID != 2 {
		t.Fatalf("作用域列未补齐：inst=%d campus=%d", row.InstitutionID, row.CampusID)
	}
	if row.Approved != model.BalanceRequestApprovedPending {
		t.Fatalf("新建工单 approved = %d，期望 0（未处理）", row.Approved)
	}

	dup := &model.UserBalanceRequest{
		BizNo: "BIZ-0001", Type: model.BalanceRequestTypeRecharge,
		UserID: "01HUSER000000000000000201", Money: "100.00",
		RequestUserID: "01HUSER000000000000000201",
	}
	err := runner.WithinTx(ctx, func(tx *gorm.DB) error { return repo.Insert(ctx, tx, dup) })
	if !errors.Is(err, ErrDuplicateBizNo) {
		t.Fatalf("重复单号错误 = %v，期望 ErrDuplicateBizNo", err)
	}
	var appErr *infraerrors.AppError
	if !errors.As(err, &appErr) || appErr.Code != infraerrors.CodeUnique {
		t.Fatalf("重复单号错误码不符：%v", err)
	}
}

// TestBalanceRequestUpdateApprovedCAS 审批 CAS：0→终态只成功一次，重复调用 affected=0。
func TestBalanceRequestUpdateApprovedCAS(t *testing.T) {
	db := newBalanceRequestRepoDB(t)
	repo := NewBalanceRequestRepo(db, &appscope.Scope{InstitutionID: 1, CampusID: 2})
	runner := NewTxRunner(db)
	ctx := context.Background()

	row := &model.UserBalanceRequest{
		BizNo: "BIZ-0002", Type: model.BalanceRequestTypeRefund,
		UserID: "01HUSER000000000000000202", Money: "50.00",
		RequestUserID: "01HUSER000000000000000202",
	}
	if err := runner.WithinTx(ctx, func(tx *gorm.DB) error { return repo.Insert(ctx, tx, row) }); err != nil {
		t.Fatalf("创建工单失败: %v", err)
	}
	approver := "01HUSER000000000000000299"
	now := time.Date(2026, 2, 1, 10, 0, 0, 0, time.UTC)

	if err := runner.WithinTx(ctx, func(tx *gorm.DB) error {
		affected, e := repo.UpdateApproved(ctx, tx, row.ID, model.BalanceRequestApprovedPassed, &approver, &now)
		if e != nil {
			return e
		}
		if affected != 1 {
			t.Fatalf("首次审批 affected = %d，期望 1", affected)
		}
		return nil
	}); err != nil {
		t.Fatalf("首次审批失败: %v", err)
	}

	// 重复审批（第二次点"通过"）：affected=0（幂等，不得二次入账）
	if err := runner.WithinTx(ctx, func(tx *gorm.DB) error {
		affected, e := repo.UpdateApproved(ctx, tx, row.ID, model.BalanceRequestApprovedPassed, &approver, &now)
		if e != nil {
			return e
		}
		if affected != 0 {
			t.Fatalf("重复审批 affected = %d，期望 0（CAS 防重复入账）", affected)
		}
		return nil
	}); err != nil {
		t.Fatalf("重复审批失败: %v", err)
	}

	var stored model.UserBalanceRequest
	if err := db.Where("id = ?", row.ID).Take(&stored).Error; err != nil {
		t.Fatalf("回读工单失败: %v", err)
	}
	if stored.Approved != model.BalanceRequestApprovedPassed {
		t.Fatalf("approved = %d，期望 1", stored.Approved)
	}
	if stored.ResponseUserID == nil || *stored.ResponseUserID != approver {
		t.Fatalf("审批人未落库：%v", stored.ResponseUserID)
	}

	// 非法目标状态 → ErrBadParam
	if err := runner.WithinTx(ctx, func(tx *gorm.DB) error {
		_, e := repo.UpdateApproved(ctx, tx, row.ID, model.BalanceRequestApprovedPending, nil, nil)
		if !errors.Is(e, infraerrors.ErrBadParam) {
			t.Fatalf("非法目标状态错误 = %v，期望 ErrBadParam", e)
		}
		return nil
	}); err != nil {
		t.Fatalf("非法目标状态用例失败: %v", err)
	}
}

// TestBalanceRequestForUpdateSQL MySQL 方言：FOR UPDATE + 机构作用域同时出现在审批加锁读里。
func TestBalanceRequestForUpdateSQL(t *testing.T) {
	mdb, capture := newMySQLDryRunDB(t)
	repo := NewBalanceRequestRepo(mdb, &appscope.Scope{InstitutionID: 1, CampusID: 2})
	ctx := context.Background()

	capture.Reset()
	_, _ = repo.GetForUpdate(ctx, mdb, 7) // DryRun 必然 ErrRecordNotFound，只断言 SQL 形状
	sql := capture.Last()
	if !strings.Contains(sql, "FOR UPDATE") {
		t.Fatalf("缺少 FOR UPDATE：%q", sql)
	}
	if !strings.Contains(sql, "institution_id") {
		t.Fatalf("缺少机构作用域：%q", sql)
	}
}

// TestBalanceRequestScoping 作用域：跨机构读不到（ErrNotFound）、超管可见。
func TestBalanceRequestScoping(t *testing.T) {
	db := newBalanceRequestRepoDB(t)
	runner := NewTxRunner(db)
	ctx := context.Background()
	owner := NewBalanceRequestRepo(db, &appscope.Scope{InstitutionID: 1, CampusID: 2})

	row := &model.UserBalanceRequest{
		BizNo: "BIZ-0003", Type: model.BalanceRequestTypeBadDebt,
		UserID: "01HUSER000000000000000203", Money: "10.00",
		RequestUserID: "01HUSER000000000000000203",
	}
	if err := runner.WithinTx(ctx, func(tx *gorm.DB) error { return owner.Insert(ctx, tx, row) }); err != nil {
		t.Fatalf("创建工单失败: %v", err)
	}

	other := NewBalanceRequestRepo(db, &appscope.Scope{InstitutionID: 2, CampusID: 5})
	if err := runner.WithinTx(ctx, func(tx *gorm.DB) error {
		_, e := other.GetForUpdate(ctx, tx, row.ID)
		if !errors.Is(e, infraerrors.ErrNotFound) {
			t.Fatalf("跨机构读取错误 = %v，期望 ErrNotFound", e)
		}
		return nil
	}); err != nil {
		t.Fatalf("跨机构用例失败: %v", err)
	}
	if list, err := other.ListByUser(ctx, row.UserID, PageQuery{}); err != nil || len(list) != 0 {
		t.Fatalf("跨机构列表应查不到：len=%d err=%v", len(list), err)
	}

	admin := NewBalanceRequestRepo(db, &appscope.Scope{SuperAdmin: true, InstitutionID: 9})
	if err := runner.WithinTx(ctx, func(tx *gorm.DB) error {
		_, e := admin.GetForUpdate(ctx, tx, row.ID)
		return e
	}); err != nil {
		t.Fatalf("超管读取失败: %v", err)
	}
	if list, err := admin.ListByUser(ctx, row.UserID, PageQuery{}); err != nil || len(list) != 1 {
		t.Fatalf("超管列表应命中 1 条：len=%d err=%v", len(list), err)
	}
}

// TestBalanceRequestListByUserPaging 分页：空结果非 nil、倒序、pageSize 钳制。
func TestBalanceRequestListByUserPaging(t *testing.T) {
	db := newBalanceRequestRepoDB(t)
	repo := NewBalanceRequestRepo(db, &appscope.Scope{InstitutionID: 1, CampusID: 2})
	ctx := context.Background()
	const user = "01HUSER000000000000000204"

	empty, err := repo.ListByUser(ctx, user, PageQuery{})
	if err != nil {
		t.Fatalf("空结果查询失败: %v", err)
	}
	if empty == nil || len(empty) != 0 {
		t.Fatalf("空结果必须是非 nil 空切片：%v", empty)
	}

	for i := 0; i < 3; i++ {
		row := &model.UserBalanceRequest{
			BizNo: "BIZ-PAGE-" + string(rune('A'+i)), Type: model.BalanceRequestTypeRecharge,
			UserID: user, Money: "1.00", RequestUserID: user,
			InstitutionID: 1, CampusID: 2,
		}
		if err := db.Create(row).Error; err != nil {
			t.Fatalf("seed 工单失败: %v", err)
		}
	}
	page, err := repo.ListByUser(ctx, user, PageQuery{Page: 1, PageSize: 2})
	if err != nil {
		t.Fatalf("分页查询失败: %v", err)
	}
	if len(page) != 2 {
		t.Fatalf("第 1 页长度 = %d，期望 2", len(page))
	}
	if page[0].ID < page[1].ID {
		t.Fatalf("未按 id 倒序：%d / %d", page[0].ID, page[1].ID)
	}
	if got := (PageQuery{PageSize: 5000}).Limit(); got != MaxPageSize {
		t.Fatalf("pageSize 钳制 = %d，期望 %d", got, MaxPageSize)
	}
}

// ---------------------------------------------------------------- SP5 已用赠送额

// newBalanceRequestGiftDB 工单 + 活动报名双表库（SP5 已用赠送额需要 JOIN 两表）。
//
// 与 newBalanceRequestRepoDB 分开建库的原因：那套用例刻意只迁单表（注释说明索引名冲突理由），
// 本用例必须同时有 t_courses_activity_signed 才能验证 `biz_no = request_no` 的 JOIN 口径。
func newBalanceRequestGiftDB(t *testing.T) *gorm.DB {
	t.Helper()
	dsn := filepath.Join(t.TempDir(), "academic_balance_request_gift_test.db")
	db, err := gorm.Open(gormsqlite.Open(dsn), &gorm.Config{})
	if err != nil {
		t.Fatalf("gorm.Open(工单+报名文件库) 失败: %v", err)
	}
	t.Cleanup(func() {
		if sqlDB, e := db.DB(); e == nil {
			_ = sqlDB.Close()
		}
	})
	if err := db.AutoMigrate(&model.UserBalanceRequest{}, &model.CoursesActivitySigned{}); err != nil {
		t.Fatalf("AutoMigrate(工单+报名) 失败: %v", err)
	}
	return db
}

// seedGiftPair 落「充值工单 + 活动报名」一对行。
func seedGiftPair(t *testing.T, db *gorm.DB, bizNo, userID string, institutionID, campusID, activityID int64,
	approved int8, activityMoney, activityMoneyUsed string) {
	t.Helper()
	if err := db.Create(&model.UserBalanceRequest{
		BizNo: bizNo, Type: model.BalanceRequestTypeRecharge, UserID: userID,
		InstitutionID: institutionID, CampusID: campusID, Approved: approved,
	}).Error; err != nil {
		t.Fatalf("seed 工单(%s) 失败: %v", bizNo, err)
	}
	reqNo := bizNo
	if err := db.Create(&model.CoursesActivitySigned{
		ActivityID: activityID, UserID: userID, InstitutionID: institutionID, CampusID: campusID,
		ActivityMoney: activityMoney, ActivityMoneyUsed: activityMoneyUsed, RequestNo: &reqNo,
	}).Error; err != nil {
		t.Fatalf("seed 报名(%s) 失败: %v", bizNo, err)
	}
}

// TestBalanceRequestSumRechargeActivityUsedGift SP5 第二步的四个过滤条件 + 多行累加 + 参数校验；
// 另含**旧 dump 列名直接照抄会恒不命中**的负例（`br.id = cas.request_no` 的反向证明）。
func TestBalanceRequestSumRechargeActivityUsedGift(t *testing.T) {
	db := newBalanceRequestGiftDB(t)
	repo := NewBalanceRequestRepo(db, &appscope.Scope{InstitutionID: 1, CampusID: 2})
	ctx := context.Background()
	const user = "01HUSER000000000000000205"

	// ① 正常命中：20.00；② 第二行再 5.00 → 合计 25.00（验证 Go 侧定点累加多行）
	seedGiftPair(t, db, "BIZ-GIFT-1", user, 1, 2, 1, model.BalanceRequestApprovedPassed, "50.00", "20.00")
	seedGiftPair(t, db, "BIZ-GIFT-2", user, 1, 2, 2, model.BalanceRequestApprovedPassed, "30.00", "5.00")
	// ③ 其他校区：不参与
	seedGiftPair(t, db, "BIZ-GIFT-3", user, 1, 9, 3, model.BalanceRequestApprovedPassed, "50.00", "40.00")
	// ④ 其他机构：不参与
	seedGiftPair(t, db, "BIZ-GIFT-4", user, 2, 2, 4, model.BalanceRequestApprovedPassed, "50.00", "40.00")
	// ⑤ 未审批：不参与
	seedGiftPair(t, db, "BIZ-GIFT-5", user, 1, 2, 5, model.BalanceRequestApprovedPending, "50.00", "40.00")
	// ⑥ 赠送已用尽（used == activity_money）：不参与
	seedGiftPair(t, db, "BIZ-GIFT-6", user, 1, 2, 6, model.BalanceRequestApprovedPassed, "50.00", "50.00")
	// ⑦ 赠送额为负（抵扣，不进赠送池）：不参与
	seedGiftPair(t, db, "BIZ-GIFT-7", user, 1, 2, 7, model.BalanceRequestApprovedPassed, "-10.00", "3.00")
	// ⑧ 非充值工单（type != 100）：不参与
	if err := db.Create(&model.UserBalanceRequest{
		BizNo: "BIZ-GIFT-8", Type: model.BalanceRequestTypeRefund, UserID: user,
		InstitutionID: 1, CampusID: 2, Approved: model.BalanceRequestApprovedPassed,
	}).Error; err != nil {
		t.Fatalf("seed 退费工单失败: %v", err)
	}
	reqNo8 := "BIZ-GIFT-8"
	if err := db.Create(&model.CoursesActivitySigned{
		ActivityID: 8, UserID: user, InstitutionID: 1, CampusID: 2,
		ActivityMoney: "50.00", ActivityMoneyUsed: "45.00", RequestNo: &reqNo8,
	}).Error; err != nil {
		t.Fatalf("seed 报名(BIZ-GIFT-8) 失败: %v", err)
	}
	// ⑨ **列名映射负例**：报名单号只等于工单的自增 id（"1"），不等于 biz_no → 不得命中。
	//    照抄 dump 的 `t1.id = t2.requestid` 时会命中这一行（可退额被少扣），正确映射下不命中。
	reqNo9 := "1"
	if err := db.Create(&model.CoursesActivitySigned{
		ActivityID: 9, UserID: user, InstitutionID: 1, CampusID: 2,
		ActivityMoney: "999.00", ActivityMoneyUsed: "999.00", RequestNo: &reqNo9,
	}).Error; err != nil {
		t.Fatalf("seed 报名(按 id 伪匹配) 失败: %v", err)
	}

	got, err := repo.SumRechargeActivityUsedGift(ctx, user, 1, 2)
	if err != nil {
		t.Fatalf("SumRechargeActivityUsedGift 失败: %v", err)
	}
	if got.Cents() != 2500 {
		t.Fatalf("已用赠送额应 2500 分（20.00+5.00），实得 %d", got.Cents())
	}

	// ⑩ 无匹配行 → 0（不是 ErrNotFound）
	if v, err := repo.SumRechargeActivityUsedGift(ctx, "01HUSER000000000000000206", 1, 2); err != nil || v != 0 {
		t.Fatalf("无匹配行应返回 0 且无错，实得 (%d, %v)", v, err)
	}
	// ⑪ 参数校验：user_id 空 / institution<=0 / campus<=0 → ErrBadParam
	if _, err := repo.SumRechargeActivityUsedGift(ctx, " ", 1, 2); !errors.Is(err, infraerrors.ErrBadParam) {
		t.Fatalf("空 user_id 应 ErrBadParam，实得 %v", err)
	}
	if _, err := repo.SumRechargeActivityUsedGift(ctx, user, 0, 2); !errors.Is(err, infraerrors.ErrBadParam) {
		t.Fatalf("institution_id=0 应 ErrBadParam，实得 %v", err)
	}
	if _, err := repo.SumRechargeActivityUsedGift(ctx, user, 1, 0); !errors.Is(err, infraerrors.ErrBadParam) {
		t.Fatalf("campus_id=0 应 ErrBadParam，实得 %v", err)
	}
}

// TestBalanceRequestSumGiftSQL MySQL 方言：JOIN 键必须是 `biz_no = request_no`（不是 id），
// 且作用域过滤必须是**别名限定**列（裸列名在两表同名列下会二义，MySQL 直接报错）。
func TestBalanceRequestSumGiftSQL(t *testing.T) {
	mdb, capture := newMySQLDryRunDB(t)
	repo := NewBalanceRequestRepo(mdb, &appscope.Scope{InstitutionID: 1, CampusID: 2})
	ctx := context.Background()

	capture.Reset()
	_, _ = repo.SumRechargeActivityUsedGift(ctx, "01HUSER000000000000000207", 1, 2)
	sql := capture.Last()
	for _, want := range []string{
		"t_user_balance_request AS br",
		"t_courses_activity_signed AS cas",
		"ON br.biz_no = cas.request_no",
		"br.institution_id",
		"br.campus_id",
		"br.approved",
		"cas.activity_money > 0",
		"cas.activity_money_used < cas.activity_money",
	} {
		if !strings.Contains(sql, want) {
			t.Fatalf("SQL 缺少片段 %q：%q", want, sql)
		}
	}
	if strings.Contains(sql, "br.id = cas.request_no") {
		t.Fatalf("JOIN 不得用自增 id 对字符串单号（会恒不命中）：%q", sql)
	}
}