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-0304: 获取表名
func TestSysUserModel_TableName(t *testing.T) {
	m, _ := newModel(t)
	require.Equal(t, "`sys_user`", m.TableName())
}

// TC-0281: 正常插入
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-0330: 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-0307: 多条记录(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-0316: 多条记录(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-0302: 正常事务
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-0303: 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-0285: 事务内插入
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-0376: 正常分页
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-0381: 正常查询
func TestSysUserModel_FindListByDeptIds(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)

	list, total, err := m.FindListByDeptIds(ctx, []int64{}, 1, 10)
	require.NoError(t, err)
	require.Nil(t, list)
	require.Equal(t, int64(0), total)

	deptID := time.Now().UnixNano() % 1_000_000_000
	if deptID < 0 {
		deptID = -deptID
	}
	deptID += 700_000_000

	u1 := "dept_" + testutil.UniqueId()
	u2 := "dept_" + testutil.UniqueId()
	otherDept := deptID + 99999

	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)
	r3, err := m.Insert(ctx, newTestSysUser("dept_other_"+testutil.UniqueId(), otherDept))
	require.NoError(t, err)
	id3, err := r3.LastInsertId()
	require.NoError(t, err)
	defer testutil.CleanTable(ctx, conn, m.TableName(), id1, id2, id3)

	list, total, err = m.FindListByDeptIds(ctx, []int64{deptID}, 1, 10)
	require.NoError(t, err)
	require.GreaterOrEqual(t, total, int64(2))
	found := map[int64]struct{}{}
	for _, u := range list {
		found[u.Id] = struct{}{}
	}
	_, ok1 := found[id1]
	_, ok2 := found[id2]
	require.True(t, ok1 && ok2, "expected both dept users in result set")

	list2, total, err := m.FindListByDeptIds(ctx, []int64{deptID, otherDept}, 1, 10)
	require.NoError(t, err)
	require.GreaterOrEqual(t, total, int64(3))
	require.GreaterOrEqual(t, len(list2), 3)
}

// TC-0385: 正常批量查询
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-0283: 唯一索引冲突
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-0290: 记录不存在
func TestSysUserModel_FindOne_NotFound(t *testing.T) {
	m, _ := newModel(t)
	_, err := m.FindOne(context.Background(), 999999999999)
	require.ErrorIs(t, err, user.ErrNotFound)
}

// TC-0297: 记录不存在
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-0300: 记录不存在
func TestSysUserModel_Delete_NotFound(t *testing.T) {
	m, _ := newModel(t)
	err := m.Delete(context.Background(), 999999999999)
	require.ErrorIs(t, err, user.ErrNotFound)
}

// TC-0305: 空列表
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-0314: 空列表
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-0324: 空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-0377: 第二页
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-0384: deptId不存在
func TestSysUserModel_FindListByDeptIds_NotExistDept(t *testing.T) {
	m, _ := newModel(t)
	list, total, err := m.FindListByDeptIds(context.Background(), []int64{999999999}, 1, 10)
	require.NoError(t, err)
	require.Equal(t, int64(0), total)
	require.Len(t, list, 0)
}

// TC-0298: 事务内更新
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-0306: 单条记录
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-0309: 唯一索引冲突
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-0312: 正常多条
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-0311: 空列表
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-0313: 事务回滚
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-0320: 正常多条
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-0319: 空列表
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-0325: 单个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-0327: 包含不存在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-0329: 正常多条
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-0328: 空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-0294: 事务内可见性
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-0293: 事务内记录不存在
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-0332: 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-0333: 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-0389: 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-0390: 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-0380: 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-0388: 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-0378: 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-0382: FindListByDeptIds - 空deptIds
func TestSysUserModel_FindListByDeptIds_EmptyDeptIds(t *testing.T) {
	ctx := context.Background()
	m, _ := newModel(t)

	list, total, err := m.FindListByDeptIds(ctx, []int64{}, 1, 10)
	require.NoError(t, err)
	require.Equal(t, int64(0), total)
	require.Nil(t, list)
}

// TC-0383: FindListByDeptIds - 单个deptId
func TestSysUserModel_FindListByDeptIds_SingleDeptId(t *testing.T) {
	ctx := context.Background()
	m, conn := newModel(t)

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

	username := "single_dept_" + testutil.UniqueId()
	res, err := m.Insert(ctx, newTestSysUser(username, deptId))
	require.NoError(t, err)
	id, err := res.LastInsertId()
	require.NoError(t, err)
	t.Cleanup(func() { testutil.CleanTable(ctx, conn, m.TableName(), id) })

	list, total, err := m.FindListByDeptIds(ctx, []int64{deptId}, 1, 10)
	require.NoError(t, err)
	require.GreaterOrEqual(t, total, int64(1))
	require.GreaterOrEqual(t, len(list), 1)

	found := false
	for _, u := range list {
		if u.Id == id {
			found = true
			assert.Equal(t, username, u.Username)
			assert.Equal(t, deptId, u.DeptId)
			break
		}
	}
	require.True(t, found, "expected user with id %d in result set", id)
}

// TC-0282: 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-0286: 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-0296: 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-0308: 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-0317: 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)")
	}
}