package user_test

import (
	"context"
	"database/sql"
	"errors"
	"strings"
	"testing"
	"time"

	"github.com/go-sql-driver/mysql"
	"github.com/stretchr/testify/assert"
	"github.com/stretchr/testify/require"

	"perms-system-server/internal/model/user"
	"perms-system-server/internal/testutil"

	"github.com/zeromicro/go-zero/core/stores/sqlx"
)

func newTestSysUser(username string, deptId int64) *user.SysUser {
	now := time.Now().Unix()
	return &user.SysUser{
		Username:           username,
		Password:           "hashed",
		Nickname:           "nick",
		Avatar:             sql.NullString{Valid: false},
		Email:              "t@example.com",
		Phone:              "13800000000",
		Remark:             "",
		DeptId:             deptId,
		IsSuperAdmin:       2,
		MustChangePassword: 2,
		Status:             1,
		CreateTime:         now,
		UpdateTime:         now,
	}
}

func newModel(t *testing.T) (user.SysUserModel, sqlx.SqlConn) {
	t.Helper()
	conn := testutil.GetTestSqlConn()
	m := user.NewSysUserModel(conn, testutil.GetTestCacheConf(), testutil.GetTestCachePrefix())
	return m, conn
}

// TC-0333: 获取表名
func TestSysUserModel_TableName(t *testing.T) {
	m, _ := newModel(t)
	require.Equal(t, "`sys_user`", m.TableName())
}

// TC-0310: 正常插入
func TestSysUserModel_CRUD(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "crud_" + testutil.UniqueId()
	data := newTestSysUser(username, 1)

	res, err := m.Insert(ctx, data)
	require.NoError(t, err)
	id, err := res.LastInsertId()
	require.NoError(t, err)
	require.Greater(t, id, int64(0))
	defer testutil.CleanTable(ctx, conn, m.TableName(), id)

	got, err := m.FindOne(ctx, id)
	require.NoError(t, err)
	require.Equal(t, username, got.Username)
	require.Equal(t, data.Email, got.Email)

	data.Id = id
	data.Nickname = "updated_nick"
	data.UpdateTime = time.Now().Unix()
	require.NoError(t, m.Update(ctx, data))

	after, err := m.FindOne(ctx, id)
	require.NoError(t, err)
	require.Equal(t, "updated_nick", after.Nickname)

	require.NoError(t, m.Delete(ctx, id))

	_, err = m.FindOne(ctx, id)
	require.ErrorIs(t, err, user.ErrNotFound)
}

// TC-0359: FindOneByUsername
func TestSysUserModel_FindOneByUsername(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "findname_" + testutil.UniqueId()
	data := newTestSysUser(username, 1)

	res, err := m.Insert(ctx, data)
	require.NoError(t, err)
	id, err := res.LastInsertId()
	require.NoError(t, err)
	defer testutil.CleanTable(ctx, conn, m.TableName(), id)

	found, err := m.FindOneByUsername(ctx, username)
	require.NoError(t, err)
	require.Equal(t, id, found.Id)
	require.Equal(t, username, found.Username)

	_, err = m.FindOneByUsername(ctx, "no_such_"+testutil.UniqueId())
	require.ErrorIs(t, err, user.ErrNotFound)
}

// TC-0336: 多条记录(3条)
func TestSysUserModel_BatchInsert_BatchDelete(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	names := []string{
		"batch_a_" + testutil.UniqueId(),
		"batch_b_" + testutil.UniqueId(),
		"batch_c_" + testutil.UniqueId(),
	}
	list := []*user.SysUser{
		newTestSysUser(names[0], 10),
		newTestSysUser(names[1], 10),
		newTestSysUser(names[2], 10),
	}
	require.NoError(t, m.BatchInsert(ctx, list))

	var ids []int64
	for _, name := range names {
		u, err := m.FindOneByUsername(ctx, name)
		require.NoError(t, err)
		ids = append(ids, u.Id)
	}
	defer testutil.CleanTable(ctx, conn, m.TableName(), ids...)

	require.NoError(t, m.BatchDelete(ctx, ids))

	for _, name := range names {
		_, err := m.FindOneByUsername(ctx, name)
		require.ErrorIs(t, err, user.ErrNotFound)
	}
}

// TC-0345: 多条记录(3条)
func TestSysUserModel_BatchUpdate(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	u1 := "bupd1_" + testutil.UniqueId()
	u2 := "bupd2_" + testutil.UniqueId()
	d1 := newTestSysUser(u1, 20)
	d2 := newTestSysUser(u2, 20)

	r1, err := m.Insert(ctx, d1)
	require.NoError(t, err)
	id1, err := r1.LastInsertId()
	require.NoError(t, err)
	r2, err := m.Insert(ctx, d2)
	require.NoError(t, err)
	id2, err := r2.LastInsertId()
	require.NoError(t, err)
	defer testutil.CleanTable(ctx, conn, m.TableName(), id1, id2)

	now := time.Now().Unix()
	upd := []*user.SysUser{
		{Id: id1, Username: u1, Password: d1.Password, Nickname: "n1_new", Avatar: sql.NullString{}, Email: d1.Email, Phone: d1.Phone, Remark: d1.Remark, DeptId: 21, IsSuperAdmin: 2, MustChangePassword: 2, Status: 1, CreateTime: d1.CreateTime, UpdateTime: now},
		{Id: id2, Username: u2, Password: d2.Password, Nickname: "n2_new", Avatar: sql.NullString{}, Email: d2.Email, Phone: d2.Phone, Remark: d2.Remark, DeptId: 22, IsSuperAdmin: 2, MustChangePassword: 2, Status: 2, CreateTime: d2.CreateTime, UpdateTime: now},
	}
	require.NoError(t, m.BatchUpdate(ctx, upd))

	g1, err := m.FindOne(ctx, id1)
	require.NoError(t, err)
	require.Equal(t, "n1_new", g1.Nickname)
	require.Equal(t, int64(21), g1.DeptId)

	g2, err := m.FindOne(ctx, id2)
	require.NoError(t, err)
	require.Equal(t, "n2_new", g2.Nickname)
	require.Equal(t, int64(22), g2.DeptId)
	require.Equal(t, int64(2), g2.Status)
}

// TC-0331: 正常事务
func TestSysUserModel_TransactCtx_Commit(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "tx_ok_" + testutil.UniqueId()
	data := newTestSysUser(username, 3)

	var insertedID int64
	err := m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		res, err := m.InsertWithTx(c, session, data)
		if err != nil {
			return err
		}
		insertedID, err = res.LastInsertId()
		return err
	})
	require.NoError(t, err)
	require.Greater(t, insertedID, int64(0))
	defer testutil.CleanTable(ctx, conn, m.TableName(), insertedID)

	got, err := m.FindOne(ctx, insertedID)
	require.NoError(t, err)
	require.Equal(t, username, got.Username)
}

// TC-0332: fn返回错误
func TestSysUserModel_TransactCtx_Rollback(t *testing.T) {
	ctx := context.Background()
	m, _ := newModel(t)
	username := "tx_rb_" + testutil.UniqueId()
	data := newTestSysUser(username, 3)

	err := m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		if _, e := m.InsertWithTx(c, session, data); e != nil {
			return e
		}
		return errors.New("force rollback")
	})
	require.Error(t, err)
	require.Contains(t, err.Error(), "force rollback")

	_, err = m.FindOneByUsername(ctx, username)
	require.ErrorIs(t, err, user.ErrNotFound)
}

// TC-0314: 事务内插入
func TestSysUserModel_InsertWithTx_DeleteWithTx_SameTransaction(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "tx_del_" + testutil.UniqueId()
	data := newTestSysUser(username, 4)

	// DeleteWithTx 会先 FindOne；未提交事务内的插入对默认连接不可见，因此分两个 TransactCtx：
	// 先提交插入，再在独立事务中 DeleteWithTx。
	var insertedID int64
	err := m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		res, err := m.InsertWithTx(c, session, data)
		if err != nil {
			return err
		}
		insertedID, err = res.LastInsertId()
		return err
	})
	require.NoError(t, err)
	require.Greater(t, insertedID, int64(0))
	defer testutil.CleanTable(ctx, conn, m.TableName(), insertedID)

	err = m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		return m.DeleteWithTx(c, session, insertedID)
	})
	require.NoError(t, err)

	_, err = m.FindOne(ctx, insertedID)
	require.ErrorIs(t, err, user.ErrNotFound)
}

// TC-0405: 正常分页
func TestSysUserModel_FindListByPage(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)

	var cnt int64
	err := conn.QueryRowCtx(ctx, &cnt, "SELECT COUNT(*) FROM "+m.TableName())
	require.NoError(t, err)

	username := "page_" + testutil.UniqueId()
	res, err := m.Insert(ctx, newTestSysUser(username, 5))
	require.NoError(t, err)
	id, err := res.LastInsertId()
	require.NoError(t, err)
	defer testutil.CleanTable(ctx, conn, m.TableName(), id)

	list, total, err := m.FindListByPage(ctx, 1, 10)
	require.NoError(t, err)
	var cntAfter int64
	require.NoError(t, conn.QueryRowCtx(ctx, &cntAfter, "SELECT COUNT(*) FROM "+m.TableName()))
	require.Equal(t, cntAfter, total)
	require.GreaterOrEqual(t, len(list), 1)
	require.LessOrEqual(t, len(list), 10)

	list2, total2, err := m.FindListByPage(ctx, 1, 1)
	require.NoError(t, err)
	require.Equal(t, cntAfter, total2)
	require.Len(t, list2, 1)
}

// TC-0410: FindListByProductMembers 正常查询
func TestSysUserModel_FindListByProductMembers(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)

	productCode := "t_fpm_" + testutil.UniqueId()

	list, total, err := m.FindListByProductMembers(ctx, productCode, 1, 10)
	require.NoError(t, err)
	require.Nil(t, list)
	require.Equal(t, int64(0), total)

	u1 := "fpm1_" + testutil.UniqueId()
	u2 := "fpm2_" + testutil.UniqueId()
	u3 := "fpm3_" + testutil.UniqueId()

	r1, err := m.Insert(ctx, newTestSysUser(u1, 1))
	require.NoError(t, err)
	id1, _ := r1.LastInsertId()
	r2, err := m.Insert(ctx, newTestSysUser(u2, 1))
	require.NoError(t, err)
	id2, _ := r2.LastInsertId()
	r3, err := m.Insert(ctx, newTestSysUser(u3, 1))
	require.NoError(t, err)
	id3, _ := r3.LastInsertId()
	defer testutil.CleanTable(ctx, conn, m.TableName(), id1, id2, id3)

	now := time.Now().Unix()
	memberQ := "INSERT INTO `sys_product_member` (`productCode`,`userId`,`memberType`,`createTime`,`updateTime`) VALUES (?,?,?,?,?),(?,?,?,?,?)"
	res, err := conn.ExecCtx(ctx, memberQ, productCode, id1, "MEMBER", now, now, productCode, id2, "MEMBER", now, now)
	require.NoError(t, err)
	_ = res
	defer func() {
		_, _ = conn.ExecCtx(ctx, "DELETE FROM `sys_product_member` WHERE `productCode`=?", productCode)
	}()

	list, total, err = m.FindListByProductMembers(ctx, productCode, 1, 10)
	require.NoError(t, err)
	require.Equal(t, int64(2), total)
	found := map[int64]struct{}{}
	for _, u := range list {
		found[u.Id] = struct{}{}
	}
	_, ok1 := found[id1]
	_, ok2 := found[id2]
	_, ok3 := found[id3]
	require.True(t, ok1 && ok2, "expected u1 and u2 to be in product members")
	require.False(t, ok3, "u3 should not appear since not a product member")

	list2, _, err := m.FindListByProductMembers(ctx, productCode, 1, 1)
	require.NoError(t, err)
	require.Len(t, list2, 1)
}

// TC-0412: 正常批量查询
func TestSysUserModel_FindByIds(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)

	list, err := m.FindByIds(ctx, nil)
	require.NoError(t, err)
	require.Nil(t, list)

	list, err = m.FindByIds(ctx, []int64{})
	require.NoError(t, err)
	require.Nil(t, list)

	r1, err := m.Insert(ctx, newTestSysUser("fid1_"+testutil.UniqueId(), 6))
	require.NoError(t, err)
	id1, err := r1.LastInsertId()
	require.NoError(t, err)
	r2, err := m.Insert(ctx, newTestSysUser("fid2_"+testutil.UniqueId(), 6))
	require.NoError(t, err)
	id2, err := r2.LastInsertId()
	require.NoError(t, err)
	defer testutil.CleanTable(ctx, conn, m.TableName(), id1, id2)

	list, err = m.FindByIds(ctx, []int64{id1, id2})
	require.NoError(t, err)
	require.Len(t, list, 2)
	ids := map[int64]struct{}{list[0].Id: {}, list[1].Id: {}}
	_, ok1 := ids[id1]
	_, ok2 := ids[id2]
	require.True(t, ok1 && ok2)

	list, err = m.FindByIds(ctx, []int64{id1, 999999999999999})
	require.NoError(t, err)
	require.Len(t, list, 1)
	require.Equal(t, id1, list[0].Id)
}

// TC-0312: 唯一索引冲突
func TestSysUserModel_Insert_DuplicateUsername(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "dup_" + testutil.UniqueId()
	data := newTestSysUser(username, 7)

	res, err := m.Insert(ctx, data)
	require.NoError(t, err)
	id, err := res.LastInsertId()
	require.NoError(t, err)
	defer testutil.CleanTable(ctx, conn, m.TableName(), id)

	_, err = m.Insert(ctx, newTestSysUser(username, 8))
	require.Error(t, err)

	var me *mysql.MySQLError
	if errors.As(err, &me) {
		require.Equal(t, uint16(1062), me.Number)
	} else {
		require.True(t, strings.Contains(strings.ToLower(err.Error()), "duplicate"), "expected duplicate key error, got: %v", err)
	}
}

// TC-0319: 记录不存在
func TestSysUserModel_FindOne_NotFound(t *testing.T) {
	m, _ := newModel(t)
	_, err := m.FindOne(context.Background(), 999999999999)
	require.ErrorIs(t, err, user.ErrNotFound)
}

// TC-0326: 记录不存在
func TestSysUserModel_Update_NotFound(t *testing.T) {
	m, _ := newModel(t)
	err := m.Update(context.Background(), &user.SysUser{
		Id: 999999999999, Username: "ghost", Password: "x",
		Nickname: "n", Email: "e", Phone: "p",
		IsSuperAdmin: 2, MustChangePassword: 2, Status: 1,
		CreateTime: time.Now().Unix(), UpdateTime: time.Now().Unix(),
	})
	require.ErrorIs(t, err, user.ErrNotFound)
}

// TC-0329: 记录不存在
func TestSysUserModel_Delete_NotFound(t *testing.T) {
	m, _ := newModel(t)
	err := m.Delete(context.Background(), 999999999999)
	require.ErrorIs(t, err, user.ErrNotFound)
}

// TC-0334: 空列表
func TestSysUserModel_BatchInsert_Empty(t *testing.T) {
	m, _ := newModel(t)
	require.NoError(t, m.BatchInsert(context.Background(), nil))
	require.NoError(t, m.BatchInsert(context.Background(), []*user.SysUser{}))
}

// TC-0343: 空列表
func TestSysUserModel_BatchUpdate_Empty(t *testing.T) {
	m, _ := newModel(t)
	require.NoError(t, m.BatchUpdate(context.Background(), nil))
	require.NoError(t, m.BatchUpdate(context.Background(), []*user.SysUser{}))
}

// TC-0353: 空ids
func TestSysUserModel_BatchDelete_Empty(t *testing.T) {
	m, _ := newModel(t)
	require.NoError(t, m.BatchDelete(context.Background(), nil))
	require.NoError(t, m.BatchDelete(context.Background(), []int64{}))
}

// TC-0406: 第二页
func TestSysUserModel_FindListByPage_SecondPage(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)

	var ids []int64
	for i := 0; i < 3; i++ {
		res, err := m.Insert(ctx, newTestSysUser("p2_"+testutil.UniqueId(), 0))
		require.NoError(t, err)
		id, _ := res.LastInsertId()
		ids = append(ids, id)
	}
	t.Cleanup(func() { testutil.CleanTable(ctx, conn, m.TableName(), ids...) })

	_, total, err := m.FindListByPage(ctx, 1, 1)
	require.NoError(t, err)

	if total >= 2 {
		list2, _, err := m.FindListByPage(ctx, 2, 1)
		require.NoError(t, err)
		require.Len(t, list2, 1)
	}
}

// TC-0411: FindListByProductMembers productCode 不存在
func TestSysUserModel_FindListByProductMembers_NotExist(t *testing.T) {
	m, _ := newModel(t)
	list, total, err := m.FindListByProductMembers(context.Background(), "not_exist_pc_"+testutil.UniqueId(), 1, 10)
	require.NoError(t, err)
	require.Equal(t, int64(0), total)
	require.Len(t, list, 0)
}

// TC-0327: 事务内更新
func TestSysUserModel_UpdateWithTx(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "upd_tx_" + testutil.UniqueId()
	data := newTestSysUser(username, 1)

	res, err := m.Insert(ctx, data)
	require.NoError(t, err)
	id, err := res.LastInsertId()
	require.NoError(t, err)
	defer testutil.CleanTable(ctx, conn, m.TableName(), id)

	err = m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		data.Id = id
		data.Nickname = "tx_updated"
		data.UpdateTime = time.Now().Unix()
		return m.UpdateWithTx(c, session, data)
	})
	require.NoError(t, err)

	got, err := m.FindOne(ctx, id)
	require.NoError(t, err)
	require.Equal(t, "tx_updated", got.Nickname)
}

// TC-0335: 单条记录
func TestSysUserModel_BatchInsert_Single(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "bi_single_" + testutil.UniqueId()
	list := []*user.SysUser{newTestSysUser(username, 1)}
	require.NoError(t, m.BatchInsert(ctx, list))

	found, err := m.FindOneByUsername(ctx, username)
	require.NoError(t, err)
	defer testutil.CleanTable(ctx, conn, m.TableName(), found.Id)
	require.Equal(t, username, found.Username)
}

// TC-0338: 唯一索引冲突
func TestSysUserModel_BatchInsert_UniqueConflict(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "bi_dup_" + testutil.UniqueId()
	list := []*user.SysUser{
		newTestSysUser(username, 1),
		newTestSysUser(username, 2),
	}
	err := m.BatchInsert(ctx, list)
	require.Error(t, err)

	t.Cleanup(func() {
		if found, e := m.FindOneByUsername(ctx, username); e == nil {
			testutil.CleanTable(ctx, conn, m.TableName(), found.Id)
		}
	})

	var me *mysql.MySQLError
	if errors.As(err, &me) {
		require.Equal(t, uint16(1062), me.Number)
	} else {
		require.True(t, strings.Contains(strings.ToLower(err.Error()), "duplicate"), "expected duplicate key error, got: %v", err)
	}
}

// TC-0341: 正常多条
func TestSysUserModel_BatchInsertWithTx_Normal(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	u1 := "bitx_a_" + testutil.UniqueId()
	u2 := "bitx_b_" + testutil.UniqueId()
	list := []*user.SysUser{
		newTestSysUser(u1, 1),
		newTestSysUser(u2, 1),
	}

	err := m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		return m.BatchInsertWithTx(c, session, list)
	})
	require.NoError(t, err)

	f1, err := m.FindOneByUsername(ctx, u1)
	require.NoError(t, err)
	f2, err := m.FindOneByUsername(ctx, u2)
	require.NoError(t, err)
	defer testutil.CleanTable(ctx, conn, m.TableName(), f1.Id, f2.Id)

	require.Equal(t, u1, f1.Username)
	require.Equal(t, u2, f2.Username)
}

// TC-0340: 空列表
func TestSysUserModel_BatchInsertWithTx_Empty(t *testing.T) {
	ctx := context.Background()
	m, _ := newModel(t)
	err := m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		return m.BatchInsertWithTx(c, session, nil)
	})
	require.NoError(t, err)
}

// TC-0342: 事务回滚
func TestSysUserModel_BatchInsertWithTx_Rollback(t *testing.T) {
	ctx := context.Background()
	m, _ := newModel(t)
	u1 := "bitx_rb_" + testutil.UniqueId()
	u2 := "bitx_rb_" + testutil.UniqueId()
	list := []*user.SysUser{
		newTestSysUser(u1, 1),
		newTestSysUser(u2, 1),
	}

	err := m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		if e := m.BatchInsertWithTx(c, session, list); e != nil {
			return e
		}
		return errors.New("force rollback")
	})
	require.Error(t, err)

	_, err = m.FindOneByUsername(ctx, u1)
	require.ErrorIs(t, err, user.ErrNotFound)
	_, err = m.FindOneByUsername(ctx, u2)
	require.ErrorIs(t, err, user.ErrNotFound)
}

// TC-0349: 正常多条
func TestSysUserModel_BatchUpdateWithTx_Normal(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	u1 := "butx_a_" + testutil.UniqueId()
	u2 := "butx_b_" + testutil.UniqueId()
	r1, err := m.Insert(ctx, newTestSysUser(u1, 1))
	require.NoError(t, err)
	id1, _ := r1.LastInsertId()
	r2, err := m.Insert(ctx, newTestSysUser(u2, 1))
	require.NoError(t, err)
	id2, _ := r2.LastInsertId()
	defer testutil.CleanTable(ctx, conn, m.TableName(), id1, id2)

	now := time.Now().Unix()
	err = m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		return m.BatchUpdateWithTx(c, session, []*user.SysUser{
			{Id: id1, Username: u1, Password: "hashed", Nickname: "new1", Avatar: sql.NullString{}, Email: "t@example.com", Phone: "13800000000", DeptId: 1, IsSuperAdmin: 2, MustChangePassword: 2, Status: 1, CreateTime: now, UpdateTime: now},
			{Id: id2, Username: u2, Password: "hashed", Nickname: "new2", Avatar: sql.NullString{}, Email: "t@example.com", Phone: "13800000000", DeptId: 1, IsSuperAdmin: 2, MustChangePassword: 2, Status: 1, CreateTime: now, UpdateTime: now},
		})
	})
	require.NoError(t, err)

	g1, err := m.FindOne(ctx, id1)
	require.NoError(t, err)
	require.Equal(t, "new1", g1.Nickname)

	g2, err := m.FindOne(ctx, id2)
	require.NoError(t, err)
	require.Equal(t, "new2", g2.Nickname)
}

// TC-0348: 空列表
func TestSysUserModel_BatchUpdateWithTx_Empty(t *testing.T) {
	ctx := context.Background()
	m, _ := newModel(t)
	err := m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		return m.BatchUpdateWithTx(c, session, nil)
	})
	require.NoError(t, err)
}

// TC-0354: 单个id
func TestSysUserModel_BatchDelete_Single(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "bd_single_" + testutil.UniqueId()
	res, err := m.Insert(ctx, newTestSysUser(username, 1))
	require.NoError(t, err)
	id, _ := res.LastInsertId()
	defer testutil.CleanTable(ctx, conn, m.TableName(), id)

	require.NoError(t, m.BatchDelete(ctx, []int64{id}))

	_, err = m.FindOne(ctx, id)
	require.ErrorIs(t, err, user.ErrNotFound)
}

// TC-0356: 包含不存在id
func TestSysUserModel_BatchDelete_ContainsNonExist(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "bd_nonex_" + testutil.UniqueId()
	res, err := m.Insert(ctx, newTestSysUser(username, 1))
	require.NoError(t, err)
	id, _ := res.LastInsertId()
	defer testutil.CleanTable(ctx, conn, m.TableName(), id)

	require.NoError(t, m.BatchDelete(ctx, []int64{id, 999999999}))

	_, err = m.FindOne(ctx, id)
	require.ErrorIs(t, err, user.ErrNotFound)
}

// TC-0358: 正常多条
func TestSysUserModel_BatchDeleteWithTx_Normal(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	u1 := "bdtx_a_" + testutil.UniqueId()
	u2 := "bdtx_b_" + testutil.UniqueId()

	r1, err := m.Insert(ctx, newTestSysUser(u1, 1))
	require.NoError(t, err)
	id1, _ := r1.LastInsertId()
	r2, err := m.Insert(ctx, newTestSysUser(u2, 1))
	require.NoError(t, err)
	id2, _ := r2.LastInsertId()
	defer testutil.CleanTable(ctx, conn, m.TableName(), id1, id2)

	err = m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		return m.BatchDeleteWithTx(c, session, []int64{id1, id2})
	})
	require.NoError(t, err)

	_, err = m.FindOne(ctx, id1)
	require.ErrorIs(t, err, user.ErrNotFound)
	_, err = m.FindOne(ctx, id2)
	require.ErrorIs(t, err, user.ErrNotFound)
}

// TC-0357: 空ids
func TestSysUserModel_BatchDeleteWithTx_Empty(t *testing.T) {
	ctx := context.Background()
	m, _ := newModel(t)
	err := m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		return m.BatchDeleteWithTx(c, session, nil)
	})
	require.NoError(t, err)
}

// TC-0323: 事务内可见性
func TestSysUserModel_FindOneWithTx_InsertThenFind(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "fone_tx_" + testutil.UniqueId()
	data := newTestSysUser(username, 1)

	var insertedID int64
	err := m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		res, err := m.InsertWithTx(c, session, data)
		if err != nil {
			return err
		}
		insertedID, err = res.LastInsertId()
		if err != nil {
			return err
		}
		got, err := m.FindOneWithTx(c, session, insertedID)
		if err != nil {
			return err
		}
		require.Equal(t, insertedID, got.Id)
		require.Equal(t, username, got.Username)
		assert.Equal(t, data.Email, got.Email)
		assert.Equal(t, data.Phone, got.Phone)
		assert.Equal(t, data.DeptId, got.DeptId)
		return nil
	})
	require.NoError(t, err)
	defer testutil.CleanTable(ctx, conn, m.TableName(), insertedID)
}

// TC-0322: 事务内记录不存在
func TestSysUserModel_FindOneWithTx_NotFound(t *testing.T) {
	ctx := context.Background()
	m, _ := newModel(t)
	err := m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		_, err := m.FindOneWithTx(c, session, 999999999999)
		require.ErrorIs(t, err, user.ErrNotFound)
		return nil
	})
	require.NoError(t, err)
}

// TC-0361: FindOneByUsernameWithTx
func TestSysUserModel_FindOneByUsernameWithTx_InsertThenFind(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "fuser_tx_" + testutil.UniqueId()
	data := newTestSysUser(username, 1)

	var insertedID int64
	err := m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		res, err := m.InsertWithTx(c, session, data)
		if err != nil {
			return err
		}
		insertedID, err = res.LastInsertId()
		if err != nil {
			return err
		}
		got, err := m.FindOneByUsernameWithTx(c, session, username)
		if err != nil {
			return err
		}
		require.Equal(t, insertedID, got.Id)
		require.Equal(t, username, got.Username)
		assert.Equal(t, data.Email, got.Email)
		return nil
	})
	require.NoError(t, err)
	defer testutil.CleanTable(ctx, conn, m.TableName(), insertedID)
}

// TC-0362: FindOneByUsernameWithTx
func TestSysUserModel_FindOneByUsernameWithTx_NotFound(t *testing.T) {
	ctx := context.Background()
	m, _ := newModel(t)
	err := m.TransactCtx(ctx, func(c context.Context, session sqlx.Session) error {
		_, err := m.FindOneByUsernameWithTx(c, session, "no_such_"+testutil.UniqueId())
		require.ErrorIs(t, err, user.ErrNotFound)
		return nil
	})
	require.NoError(t, err)
}

// TC-0416: FindIdsByDeptId 正常返回部门下用户ID列表
func TestSysUserModel_FindIdsByDeptId_Normal(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)

	deptId := time.Now().UnixNano()%100_000_000 + 600_000_000

	u1 := "fbd1_" + testutil.UniqueId()
	u2 := "fbd2_" + testutil.UniqueId()
	r1, err := m.Insert(ctx, newTestSysUser(u1, deptId))
	require.NoError(t, err)
	id1, err := r1.LastInsertId()
	require.NoError(t, err)
	r2, err := m.Insert(ctx, newTestSysUser(u2, deptId))
	require.NoError(t, err)
	id2, err := r2.LastInsertId()
	require.NoError(t, err)
	t.Cleanup(func() { testutil.CleanTable(ctx, conn, m.TableName(), id1, id2) })

	ids, err := m.FindIdsByDeptId(ctx, deptId)
	require.NoError(t, err)
	require.Len(t, ids, 2)
	assert.ElementsMatch(t, []int64{id1, id2}, ids)
}

// TC-0417: FindIdsByDeptId 部门无用户返回空
func TestSysUserModel_FindIdsByDeptId_Empty(t *testing.T) {
	m, _ := newModel(t)
	deptId := time.Now().UnixNano()%100_000_000 + 700_000_000
	ids, err := m.FindIdsByDeptId(context.Background(), deptId)
	require.NoError(t, err)
	require.Empty(t, ids)
}

// TC-0409: FindListByPage list查询失败（DB异常）
func TestSysUserModel_FindListByPage_DBError(t *testing.T) {
	badConn := sqlx.NewMysql("root:bad@tcp(127.0.0.1:1)/bad?timeout=1s")
	m := user.NewSysUserModel(badConn, testutil.GetTestCacheConf(), testutil.GetTestCachePrefix())

	_, _, err := m.FindListByPage(context.Background(), 1, 10)
	require.Error(t, err)
}

// TC-0415: FindByIds DB异常
func TestSysUserModel_FindByIds_DBError(t *testing.T) {
	badConn := sqlx.NewMysql("root:bad@tcp(127.0.0.1:1)/bad?timeout=1s")
	m := user.NewSysUserModel(badConn, testutil.GetTestCacheConf(), testutil.GetTestCachePrefix())

	list, err := m.FindByIds(context.Background(), []int64{1, 2, 3})
	require.Error(t, err)
	require.Nil(t, list)
}

// TC-0407: FindListByPage - 空结果页
func TestSysUserModel_FindListByPage_EmptyPage(t *testing.T) {
	ctx := context.Background()
	m, _ := newModel(t)

	list, total, err := m.FindListByPage(ctx, 999999, 10)
	require.NoError(t, err)
	require.GreaterOrEqual(t, total, int64(0))
	require.Empty(t, list)
}


// TC-0311: Insert 正常插入含TokenVersion
func TestSysUserModel_Insert_WithTokenVersion(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "tv_insert_" + testutil.UniqueId()
	data := newTestSysUser(username, 0)

	res, err := m.Insert(ctx, data)
	require.NoError(t, err, "Insert should include tokenVersion in SQL parameters")

	id, err := res.LastInsertId()
	require.NoError(t, err)
	defer testutil.CleanTable(ctx, conn, m.TableName(), id)

	got, err := m.FindOne(ctx, id)
	require.NoError(t, err)
	assert.Equal(t, int64(0), got.TokenVersion, "default tokenVersion should be 0")
}

// TC-0315: InsertWithTx 事务内插入含TokenVersion
func TestSysUserModel_InsertWithTx_WithTokenVersion(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "tv_instx_" + testutil.UniqueId()
	data := newTestSysUser(username, 0)

	var insertedId int64
	err := m.TransactCtx(ctx, func(txCtx context.Context, session sqlx.Session) error {
		res, err := m.InsertWithTx(txCtx, session, data)
		if err != nil {
			return err
		}
		insertedId, _ = res.LastInsertId()
		return nil
	})
	require.NoError(t, err, "InsertWithTx should include tokenVersion in SQL parameters")
	defer testutil.CleanTable(ctx, conn, m.TableName(), insertedId)

	got, err := m.FindOne(ctx, insertedId)
	require.NoError(t, err)
	assert.Equal(t, int64(0), got.TokenVersion)
}

// TC-0325: Update 正常更新含TokenVersion
func TestSysUserModel_Update_WithTokenVersion(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "tv_update_" + testutil.UniqueId()
	data := newTestSysUser(username, 0)

	res, err := m.Insert(ctx, data)
	require.NoError(t, err)
	id, _ := res.LastInsertId()
	defer testutil.CleanTable(ctx, conn, m.TableName(), id)

	got, err := m.FindOne(ctx, id)
	require.NoError(t, err)

	got.TokenVersion = 5
	got.Nickname = "updated_nick"
	err = m.Update(ctx, got)
	require.NoError(t, err, "Update should include tokenVersion in SQL parameters")

	updated, err := m.FindOne(ctx, id)
	require.NoError(t, err)
	assert.Equal(t, int64(5), updated.TokenVersion)
	assert.Equal(t, "updated_nick", updated.Nickname)
}

// TC-0337: BatchInsert 批量插入含TokenVersion
func TestSysUserModel_BatchInsert_WithTokenVersion(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)

	dataList := make([]*user.SysUser, 3)
	for i := range dataList {
		dataList[i] = newTestSysUser("tv_batch_"+testutil.UniqueId(), 0)
	}

	err := m.BatchInsert(ctx, dataList)
	require.NoError(t, err, "BatchInsert should include tokenVersion in SQL parameters")

	for _, d := range dataList {
		got, err := m.FindOneByUsername(ctx, d.Username)
		require.NoError(t, err)
		assert.Equal(t, int64(0), got.TokenVersion)
		t.Cleanup(func() { testutil.CleanTable(ctx, conn, m.TableName(), got.Id) })
	}
}

// TC-0346: BatchUpdate 批量更新不污染数据
func TestSysUserModel_BatchUpdate_NoDataCorruption(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)

	now := time.Now().Unix()
	dataList := make([]*user.SysUser, 2)
	var ids []int64
	for i := range dataList {
		dataList[i] = newTestSysUser("tv_bupd_"+testutil.UniqueId(), 0)
		res, err := m.Insert(ctx, dataList[i])
		require.NoError(t, err)
		id, _ := res.LastInsertId()
		ids = append(ids, id)
		dataList[i].Id = id
	}
	t.Cleanup(func() { testutil.CleanTable(ctx, conn, m.TableName(), ids...) })

	dataList[0].TokenVersion = 10
	dataList[0].Nickname = "batch_updated_0"
	dataList[0].UpdateTime = now + 100
	dataList[1].TokenVersion = 20
	dataList[1].Nickname = "batch_updated_1"
	dataList[1].UpdateTime = now + 200

	err := m.BatchUpdate(ctx, dataList)
	require.NoError(t, err, "BatchUpdate should correctly assign values without offset")

	for i, id := range ids {
		got, err := m.FindOne(ctx, id)
		require.NoError(t, err)
		assert.Equal(t, dataList[i].TokenVersion, got.TokenVersion,
			"tokenVersion must not be corrupted (should not contain createTime value)")
		assert.Equal(t, dataList[i].Nickname, got.Nickname)
		assert.NotEqual(t, got.Id, got.UpdateTime,
			"updateTime must not be corrupted (should not contain Id value)")
	}
}

// TC-0418: UpdateProfile 正常更新(状态未变，不递增 tokenVersion)
func TestSysUserModel_UpdateProfile_NoStatusChange(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "up_nc_" + testutil.UniqueId()
	data := newTestSysUser(username, 1)

	res, err := m.Insert(ctx, data)
	require.NoError(t, err)
	id, _ := res.LastInsertId()
	defer testutil.CleanTable(ctx, conn, m.TableName(), id)

	orig, err := m.FindOne(ctx, id)
	require.NoError(t, err)
	origTv := orig.TokenVersion
	origStatus := orig.Status

	err = m.UpdateProfile(ctx, id, username, "new_nick", "new@example.com", "13900000000", "remark", 2, origStatus, false, orig.UpdateTime)
	require.NoError(t, err)

	got, err := m.FindOne(ctx, id)
	require.NoError(t, err)
	assert.Equal(t, "new_nick", got.Nickname)
	assert.Equal(t, "new@example.com", got.Email)
	assert.Equal(t, "13900000000", got.Phone)
	assert.Equal(t, "remark", got.Remark)
	assert.Equal(t, int64(2), got.DeptId)
	assert.Equal(t, origStatus, got.Status)
	assert.Equal(t, origTv, got.TokenVersion, "tokenVersion 未变(statusChanged=false)")
}

// TC-0419: UpdateProfile 状态改变时 tokenVersion+1
func TestSysUserModel_UpdateProfile_StatusChange_IncrementsTokenVersion(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "up_sc_" + testutil.UniqueId()
	data := newTestSysUser(username, 1)

	res, err := m.Insert(ctx, data)
	require.NoError(t, err)
	id, _ := res.LastInsertId()
	defer testutil.CleanTable(ctx, conn, m.TableName(), id)

	orig, err := m.FindOne(ctx, id)
	require.NoError(t, err)
	origTv := orig.TokenVersion

	err = m.UpdateProfile(ctx, id, username, orig.Nickname, orig.Email, orig.Phone, orig.Remark, orig.DeptId, 2, true, orig.UpdateTime)
	require.NoError(t, err)

	got, err := m.FindOne(ctx, id)
	require.NoError(t, err)
	assert.Equal(t, int64(2), got.Status)
	assert.Equal(t, origTv+1, got.TokenVersion, "statusChanged=true 时 tokenVersion 应递增")
}

// TC-0420: UpdateProfile 乐观锁冲突时返回 ErrUpdateConflict
func TestSysUserModel_UpdateProfile_OptimisticLockConflict(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "up_ol_" + testutil.UniqueId()
	data := newTestSysUser(username, 1)

	res, err := m.Insert(ctx, data)
	require.NoError(t, err)
	id, _ := res.LastInsertId()
	defer testutil.CleanTable(ctx, conn, m.TableName(), id)

	orig, err := m.FindOne(ctx, id)
	require.NoError(t, err)

	staleUpdateTime := orig.UpdateTime - 100
	err = m.UpdateProfile(ctx, id, username, "x", "x@x.com", "13900000000", "r", 1, 1, false, staleUpdateTime)
	require.ErrorIs(t, err, user.ErrUpdateConflict)
}

// TC-0421: UpdateProfile 串行两次更新: 第一次成功刷新 updateTime, 第二次基于旧 updateTime 触发 ErrUpdateConflict
// 乐观锁依赖秒级 updateTime, 两次更新之间需 >= 1 秒的间隔.
func TestSysUserModel_UpdateProfile_ConcurrentOnlyOneWins(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)
	username := "up_cc_" + testutil.UniqueId()
	data := newTestSysUser(username, 1)

	res, err := m.Insert(ctx, data)
	require.NoError(t, err)
	id, _ := res.LastInsertId()
	defer testutil.CleanTable(ctx, conn, m.TableName(), id)

	orig, err := m.FindOne(ctx, id)
	require.NoError(t, err)

	time.Sleep(1100 * time.Millisecond)

	expectedUT := orig.UpdateTime
	err1 := m.UpdateProfile(ctx, id, username, "n1", orig.Email, orig.Phone, orig.Remark, orig.DeptId, orig.Status, false, expectedUT)
	require.NoError(t, err1)
	err2 := m.UpdateProfile(ctx, id, username, "n2", orig.Email, orig.Phone, orig.Remark, orig.DeptId, orig.Status, false, expectedUT)
	require.ErrorIs(t, err2, user.ErrUpdateConflict, "基于旧 updateTime 的第二次更新应因乐观锁失败")

	got, err := m.FindOne(ctx, id)
	require.NoError(t, err)
	assert.Equal(t, "n1", got.Nickname, "仅第一次更新应生效")
}

// TC-0422: UpdateProfile userId 不存在时返回 ErrUpdateConflict
func TestSysUserModel_UpdateProfile_NotFound(t *testing.T) {
	ctx := context.Background()
	m, _ := newModel(t)
	err := m.UpdateProfile(ctx, 999999999, "nouser", "n", "n@n.com", "13900000000", "r", 1, 1, false, time.Now().Unix())
	require.ErrorIs(t, err, user.ErrUpdateConflict)
}