package userrole

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

	"perms-system-server/internal/consts"

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

var _ SysUserRoleModel = (*customSysUserRoleModel)(nil)

type (
	SysUserRoleModel interface {
		sysUserRoleModel
		FindRoleIdsByUserId(ctx context.Context, userId int64) ([]int64, error)
		FindRoleIdsByUserIdForProduct(ctx context.Context, userId int64, productCode string) ([]int64, error)
		// FindRoleIdsByUserIdForProductTx 是 FindRoleIdsByUserIdForProduct 的事务内变体：
		// 上游 BindRoles 在 FindOneForUpdateTx(member) 之后，需要在同一事务里读取 existing
		// roleIds 再做 diff，避免"existing 读-外部 / diff 写-内部"的窗口产生第三态
		// （见审计 M-R10-2）。此处不写库，不需要 FOR UPDATE；member 行锁已经负责并发串行化。
		FindRoleIdsByUserIdForProductTx(ctx context.Context, session sqlx.Session, userId int64, productCode string) ([]int64, error)
		FindUserIdsByRoleId(ctx context.Context, roleId int64) ([]int64, error)
		FindUserIdsByRoleIdForUpdateTx(ctx context.Context, session sqlx.Session, roleId int64) ([]int64, error)
		DeleteByRoleIdTx(ctx context.Context, session sqlx.Session, roleId int64) error
		DeleteByUserIdForProductTx(ctx context.Context, session sqlx.Session, userId int64, productCode string) error
		DeleteByUserIdAndRoleIdsTx(ctx context.Context, session sqlx.Session, userId int64, roleIds []int64) error
	}

	customSysUserRoleModel struct {
		*defaultSysUserRoleModel
	}
)

func NewSysUserRoleModel(conn sqlx.SqlConn, c cache.CacheConf, cachePrefix string, opts ...cache.Option) SysUserRoleModel {
	return &customSysUserRoleModel{
		defaultSysUserRoleModel: newSysUserRoleModel(conn, c, cachePrefix, opts...),
	}
}

// FindRoleIdsByUserId 查询用户关联的所有角色 ID（跨全部产品聚合）。
// 仅在超管未带产品上下文时通过 UserDetail 调用，返回结果不区分产品归属。
func (m *customSysUserRoleModel) FindRoleIdsByUserId(ctx context.Context, userId int64) ([]int64, error) {
	var ids []int64
	query := fmt.Sprintf("SELECT `roleId` FROM %s WHERE `userId` = ?", m.table)
	if err := m.QueryRowsNoCacheCtx(ctx, &ids, query, userId); err != nil {
		return nil, err
	}
	return ids, nil
}

func (m *customSysUserRoleModel) FindRoleIdsByUserIdForProduct(ctx context.Context, userId int64, productCode string) ([]int64, error) {
	var ids []int64
	// status 走占位参数 + consts.StatusEnabled，避免把"启用"语义钉死成字面量 1；未来新增
	// status=3/已归档 之类时只要改 consts 就行，这里不会漏掉（见审计 L-4）。
	query := fmt.Sprintf("SELECT ur.`roleId` FROM %s ur INNER JOIN `sys_role` r ON ur.`roleId` = r.`id` WHERE ur.`userId` = ? AND r.`productCode` = ? AND r.`status` = ?", m.table)
	if err := m.QueryRowsNoCacheCtx(ctx, &ids, query, userId, productCode, consts.StatusEnabled); err != nil {
		return nil, err
	}
	return ids, nil
}

func (m *customSysUserRoleModel) FindRoleIdsByUserIdForProductTx(ctx context.Context, session sqlx.Session, userId int64, productCode string) ([]int64, error) {
	var ids []int64
	query := fmt.Sprintf("SELECT ur.`roleId` FROM %s ur INNER JOIN `sys_role` r ON ur.`roleId` = r.`id` WHERE ur.`userId` = ? AND r.`productCode` = ? AND r.`status` = ?", m.table)
	if err := session.QueryRowsCtx(ctx, &ids, query, userId, productCode, consts.StatusEnabled); err != nil {
		return nil, err
	}
	return ids, nil
}

func (m *customSysUserRoleModel) FindUserIdsByRoleId(ctx context.Context, roleId int64) ([]int64, error) {
	var ids []int64
	query := fmt.Sprintf("SELECT `userId` FROM %s WHERE `roleId` = ?", m.table)
	if err := m.QueryRowsNoCacheCtx(ctx, &ids, query, roleId); err != nil {
		return nil, err
	}
	return ids, nil
}

func (m *customSysUserRoleModel) FindUserIdsByRoleIdForUpdateTx(ctx context.Context, session sqlx.Session, roleId int64) ([]int64, error) {
	var ids []int64
	query := fmt.Sprintf("SELECT `userId` FROM %s WHERE `roleId` = ? FOR UPDATE", m.table)
	if err := session.QueryRowsCtx(ctx, &ids, query, roleId); err != nil {
		return nil, err
	}
	return ids, nil
}

// userRoleKey 仅含 buildCacheKeys 真正需要的三列（审计 L-R11-2）：以前 Delete 族 SELECT 整行
// 只是为了拿 id / userId / roleId 构造缓存键，却把 createTime/updateTime 等业务字段一并搬运回
// 应用层再丢弃。在"删除一个角色的所有绑定关系"这类关联行 O(关联用户数) 的场景下，SELECT 只读
// 必要列能显著减少 goroutine 临时内存与网络 I/O。
type userRoleKey struct {
	Id     int64 `db:"id"`
	UserId int64 `db:"userId"`
	RoleId int64 `db:"roleId"`
}

func (m *customSysUserRoleModel) buildCacheKeysFromKeys(list []userRoleKey) []string {
	keys := make([]string, 0, len(list)*2)
	for _, data := range list {
		keys = append(keys,
			fmt.Sprintf("%s%v", cacheSysUserRoleIdPrefix, data.Id),
			fmt.Sprintf("%s%v:%v", cacheSysUserRoleUserIdRoleIdPrefix, data.UserId, data.RoleId),
		)
	}
	return keys
}

func (m *customSysUserRoleModel) DeleteByRoleIdTx(ctx context.Context, session sqlx.Session, roleId int64) error {
	var list []userRoleKey
	findQuery := fmt.Sprintf("SELECT `id`, `userId`, `roleId` FROM %s WHERE `roleId` = ? FOR UPDATE", m.table)
	if err := session.QueryRowsCtx(ctx, &list, findQuery, roleId); err != nil {
		return err
	}
	if len(list) == 0 {
		return nil
	}
	keys := m.buildCacheKeysFromKeys(list)
	_, err := m.ExecCtx(ctx, func(ctx context.Context, conn sqlx.SqlConn) (sql.Result, error) {
		query := fmt.Sprintf("DELETE FROM %s WHERE `roleId` = ?", m.table)
		return session.ExecCtx(ctx, query, roleId)
	}, keys...)
	return err
}

func (m *customSysUserRoleModel) DeleteByUserIdForProductTx(ctx context.Context, session sqlx.Session, userId int64, productCode string) error {
	var list []userRoleKey
	findQuery := fmt.Sprintf("SELECT `id`, `userId`, `roleId` FROM %s WHERE `userId` = ? AND `roleId` IN (SELECT `id` FROM `sys_role` WHERE `productCode` = ?) FOR UPDATE", m.table)
	if err := session.QueryRowsCtx(ctx, &list, findQuery, userId, productCode); err != nil {
		return err
	}
	if len(list) == 0 {
		return nil
	}
	keys := m.buildCacheKeysFromKeys(list)
	_, err := m.ExecCtx(ctx, func(ctx context.Context, conn sqlx.SqlConn) (sql.Result, error) {
		query := fmt.Sprintf("DELETE FROM %s WHERE `userId` = ? AND `roleId` IN (SELECT `id` FROM `sys_role` WHERE `productCode` = ?)", m.table)
		return session.ExecCtx(ctx, query, userId, productCode)
	}, keys...)
	return err
}

func (m *customSysUserRoleModel) DeleteByUserIdAndRoleIdsTx(ctx context.Context, session sqlx.Session, userId int64, roleIds []int64) error {
	if len(roleIds) == 0 {
		return nil
	}
	placeholders := make([]string, len(roleIds))
	args := make([]interface{}, 0, len(roleIds)+1)
	args = append(args, userId)
	for i, id := range roleIds {
		placeholders[i] = "?"
		args = append(args, id)
	}
	inClause := strings.Join(placeholders, ",")

	var list []userRoleKey
	findQuery := fmt.Sprintf("SELECT `id`, `userId`, `roleId` FROM %s WHERE `userId` = ? AND `roleId` IN (%s) FOR UPDATE", m.table, inClause)
	if err := session.QueryRowsCtx(ctx, &list, findQuery, args...); err != nil {
		return err
	}
	if len(list) == 0 {
		return nil
	}
	keys := m.buildCacheKeysFromKeys(list)
	_, err := m.ExecCtx(ctx, func(ctx context.Context, conn sqlx.SqlConn) (sql.Result, error) {
		query := fmt.Sprintf("DELETE FROM %s WHERE `userId` = ? AND `roleId` IN (%s)", m.table, inClause)
		return session.ExecCtx(ctx, query, args...)
	}, keys...)
	return err
}