package repository

import (
	"errors"
	"regexp"
	"testing"
	"time"

	"github.com/DATA-DOG/go-sqlmock"
	"github.com/wmms/wmms/internal/model"
	"github.com/wmms/wmms/pkg/database"
)

func TestListUsersIncludesAllAssignedRoles(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("open sql mock: %v", err)
	}
	t.Cleanup(func() { database.DB = nil; db.Close() })
	database.DB = db
	now := time.Date(2026, 9, 16, 9, 0, 0, 0, time.Local)

	mock.ExpectQuery(regexp.QuoteMeta(`SELECT COUNT(*) FROM users`)).WillReturnRows(
		sqlmock.NewRows([]string{"count"}).AddRow(1),
	)
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT id, username, password, description, created_at FROM users ORDER BY id LIMIT ? OFFSET ?`)).
		WithArgs(20, 0).
		WillReturnRows(sqlmock.NewRows([]string{"id", "username", "password", "description", "created_at"}).
			AddRow(7, "zhangsan", "hash", "测试用户", now))
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT ur.user_id, r.id, r.code, r.name, r.enabled, r.created_at, r.updated_at
		FROM user_roles ur JOIN roles r ON r.id = ur.role_id
		WHERE ur.user_id IN (?) ORDER BY ur.user_id, r.id`)).
		WithArgs(int64(7)).
		WillReturnRows(sqlmock.NewRows([]string{"user_id", "id", "code", "name", "enabled", "created_at", "updated_at"}).
			AddRow(7, 2, "warehouse_admin", "仓库管理员", true, now, now).
			AddRow(7, 3, "purchaser", "采购人员", true, now, now))

	users, total, err := NewUserRepository().List("", 20, 0)
	if err != nil {
		t.Fatalf("list users: %v", err)
	}
	if total != 1 || len(users) != 1 || len(users[0].Roles) != 2 {
		t.Fatalf("unexpected users: total=%d users=%+v", total, users)
	}
	if users[0].Roles[1].Name != "采购人员" {
		t.Fatalf("role names not loaded: %+v", users[0].Roles)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

func TestDeleteRejectsLastSuperAdmin(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("open sql mock: %v", err)
	}
	t.Cleanup(func() { database.DB = nil; db.Close() })
	database.DB = db

	mock.ExpectBegin()
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT id FROM roles WHERE code = 'super_admin' FOR UPDATE`)).
		WillReturnRows(sqlmock.NewRows([]string{"id"}).AddRow(1))
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT COUNT(*) FROM user_roles WHERE user_id = ? AND role_id = ?`)).
		WithArgs(int64(7), int64(1)).
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT COUNT(*) FROM user_roles WHERE role_id = ?`)).
		WithArgs(int64(1)).
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
	mock.ExpectRollback()

	err = NewUserRepository().Delete(7)
	if !errors.Is(err, ErrLastSuperAdmin) {
		t.Fatalf("delete error = %v, want ErrLastSuperAdmin", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

func TestUpdateRejectsRemovingRoleFromLastSuperAdmin(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("open sql mock: %v", err)
	}
	t.Cleanup(func() { database.DB = nil; db.Close() })
	database.DB = db

	mock.ExpectBegin()
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT COUNT(*) FROM roles r
		WHERE r.id IN (?)
		AND (r.enabled = TRUE OR EXISTS (
			SELECT 1 FROM user_roles ur WHERE ur.role_id = r.id AND ur.user_id = ?))`)).
		WithArgs(int64(2), int64(7)).
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT id FROM roles WHERE code = 'super_admin' FOR UPDATE`)).
		WillReturnRows(sqlmock.NewRows([]string{"id"}).AddRow(1))
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT COUNT(*) FROM user_roles WHERE user_id = ? AND role_id = ?`)).
		WithArgs(int64(7), int64(1)).
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT COUNT(*) FROM user_roles WHERE role_id = ?`)).
		WithArgs(int64(1)).
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
	mock.ExpectRollback()

	user := &model.User{ID: 7, Description: "系统管理员"}
	err = NewUserRepository().Update(user, []int64{2})
	if !errors.Is(err, ErrLastSuperAdmin) {
		t.Fatalf("update error = %v, want ErrLastSuperAdmin", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

// 编辑用户时把已被停用的角色原样回传必须能保存成功。停用只是不再授予权限，不要求
// 管理员顺手把它从用户身上摘掉；旧实现在这里返回 ErrInvalidRoles，而前端下拉里的
// 停用角色是禁用项、已选中的 tag 也关不掉，结果这个用户再也编辑不了。
func TestUpdateKeepsDisabledRoleAlreadyAssignedToUser(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("open sql mock: %v", err)
	}
	t.Cleanup(func() { database.DB = nil; db.Close() })
	database.DB = db

	mock.ExpectBegin()
	// count 命中 1：角色 3 虽然已停用，但该用户当前正持有它，EXISTS 放行。
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT COUNT(*) FROM roles r
		WHERE r.id IN (?)
		AND (r.enabled = TRUE OR EXISTS (
			SELECT 1 FROM user_roles ur WHERE ur.role_id = r.id AND ur.user_id = ?))`)).
		WithArgs(int64(3), int64(7)).
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(1))
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT id FROM roles WHERE code = 'super_admin' FOR UPDATE`)).
		WillReturnRows(sqlmock.NewRows([]string{"id"}).AddRow(1))
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT COUNT(*) FROM user_roles WHERE user_id = ? AND role_id = ?`)).
		WithArgs(int64(7), int64(1)).
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectExec(regexp.QuoteMeta(`UPDATE users SET description = ? WHERE id = ?`)).
		WithArgs("改个描述", int64(7)).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec(regexp.QuoteMeta(`DELETE FROM user_roles WHERE user_id = ?`)).
		WithArgs(int64(7)).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectExec(regexp.QuoteMeta(`INSERT INTO user_roles (user_id, role_id) VALUES (?, ?)`)).
		WithArgs(int64(7), int64(3)).
		WillReturnResult(sqlmock.NewResult(0, 1))
	mock.ExpectCommit()

	user := &model.User{ID: 7, Description: "改个描述"}
	if err := NewUserRepository().Update(user, []int64{3}); err != nil {
		t.Fatalf("update user: %v", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

// 反面：把一个该用户还没持有的停用角色塞进来仍要拒绝。EXISTS 为假，count 对不上，
// 这样"不能通过接口新分配停用角色"这条约束就没有被上面那个放行口子带松。
func TestUpdateRejectsNewlyAssignedDisabledRole(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("open sql mock: %v", err)
	}
	t.Cleanup(func() { database.DB = nil; db.Close() })
	database.DB = db

	mock.ExpectBegin()
	// count 为 0：角色 4 已停用，且不在该用户当前持有的角色里。
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT COUNT(*) FROM roles r
		WHERE r.id IN (?)
		AND (r.enabled = TRUE OR EXISTS (
			SELECT 1 FROM user_roles ur WHERE ur.role_id = r.id AND ur.user_id = ?))`)).
		WithArgs(int64(4), int64(7)).
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(0))
	mock.ExpectRollback()

	user := &model.User{ID: 7, Description: "改个描述"}
	err = NewUserRepository().Update(user, []int64{4})
	if !errors.Is(err, ErrInvalidRoles) {
		t.Fatalf("update error = %v, want ErrInvalidRoles", err)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}

func TestCreateUserAssignsMultipleEnabledRolesInTransaction(t *testing.T) {
	db, mock, err := sqlmock.New()
	if err != nil {
		t.Fatalf("open sql mock: %v", err)
	}
	t.Cleanup(func() { database.DB = nil; db.Close() })
	database.DB = db

	mock.ExpectBegin()
	mock.ExpectExec(regexp.QuoteMeta(`INSERT INTO users (username, password, description) VALUES (?, ?, ?)`)).
		WithArgs("zhangsan", "hash", "测试用户").
		WillReturnResult(sqlmock.NewResult(7, 1))
	// 新建的用户在 user_roles 里还没有记录，EXISTS 恒为假，所以这里等价于
	// "只允许启用中的角色"。
	mock.ExpectQuery(regexp.QuoteMeta(`SELECT COUNT(*) FROM roles r
		WHERE r.id IN (?, ?)
		AND (r.enabled = TRUE OR EXISTS (
			SELECT 1 FROM user_roles ur WHERE ur.role_id = r.id AND ur.user_id = ?))`)).
		WithArgs(int64(2), int64(3), int64(7)).
		WillReturnRows(sqlmock.NewRows([]string{"count"}).AddRow(2))
	mock.ExpectExec(regexp.QuoteMeta(`INSERT INTO user_roles (user_id, role_id) VALUES (?, ?), (?, ?)`)).
		WithArgs(int64(7), int64(2), int64(7), int64(3)).
		WillReturnResult(sqlmock.NewResult(0, 2))
	mock.ExpectCommit()

	user := &model.User{Username: "zhangsan", Password: "hash", Description: "测试用户"}
	if err := NewUserRepository().Create(user, []int64{2, 3}); err != nil {
		t.Fatalf("create user: %v", err)
	}
	if user.ID != 7 {
		t.Fatalf("expected user id 7, got %d", user.ID)
	}
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Fatal(err)
	}
}