import type { PoolConnection } from 'mysql2/promise';
import { queryWithConn, executeWithConn } from '../../config/database.js';

interface RoleRow {
  id: string;
  name: string;
  is_system: boolean;
  permissions: string[] | '*';
}

function rowToRole(row: Record<string, unknown>): RoleRow {
  let permissions: string[] | '*' = [];
  if (typeof row.permissions === 'string') {
    permissions = row.permissions === '*' ? '*' : JSON.parse(row.permissions);
  } else {
    permissions = (row.permissions as string[]) ?? [];
  }
  // Legacy seed data stored the wildcard as the array ['*'] instead of the
  // bare string '*', and a since-fixed bug could carry that stray '*'
  // entry into an otherwise-normal permissions array on save (alongside the
  // real permission ids) — either way, an array containing the literal '*'
  // string means "everything", so normalize to the canonical '*' regardless
  // of what else is in the array.
  if (Array.isArray(permissions) && permissions.includes('*')) {
    permissions = '*';
  }
  return {
    id: row.id as string,
    name: row.name as string,
    is_system: Boolean(row.is_system),
    permissions,
  };
}

export async function findAll(conn?: PoolConnection): Promise<RoleRow[]> {
  const sql = `SELECT id as id, name, is_system, permissions FROM roles WHERE deleted_at IS NULL ORDER BY name ASC`;
  const rows = await queryWithConn(sql, undefined, conn) as unknown as Record<string, unknown>[];
  return rows.map(rowToRole);
}

export async function findById(id: string, conn?: PoolConnection): Promise<RoleRow | null> {
  const sql = `SELECT id as id, name, is_system, permissions FROM roles WHERE id = ? AND deleted_at IS NULL`;
  const rows = await queryWithConn(sql, [id], conn) as unknown as Record<string, unknown>[];
  return rows.length ? rowToRole(rows[0]!) : null;
}

export async function findByName(name: string, excludeId?: string, conn?: PoolConnection): Promise<RoleRow | null> {
  const sql = excludeId
    ? `SELECT id as id, name, is_system, permissions FROM roles WHERE LOWER(name) = LOWER(?) AND id != ? AND deleted_at IS NULL LIMIT 1`
    : `SELECT id as id, name, is_system, permissions FROM roles WHERE LOWER(name) = LOWER(?) AND deleted_at IS NULL LIMIT 1`;
  const params = excludeId ? [name, excludeId] : [name];
  const rows = await queryWithConn(sql, params, conn) as unknown as Record<string, unknown>[];
  return rows.length ? rowToRole(rows[0]!) : null;
}

export async function insert(data: { name: string; permissions: string[] | '*'; is_system?: boolean }, id: string, createdBy: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn(
    'INSERT INTO roles (id, name, is_system, permissions, created_by) VALUES (?, ?, ?, ?, ?)',
    [id, data.name, data.is_system ?? false, JSON.stringify(data.permissions), createdBy],
    conn,
  );
}

export async function update(id: string, data: { name?: string; permissions?: string[] | '*' }, updatedBy: string, conn?: PoolConnection): Promise<void> {
  const sets: string[] = [];
  const params: unknown[] = [];

  if (data.name !== undefined) { sets.push('name = ?'); params.push(data.name); }
  if (data.permissions !== undefined) { sets.push('permissions = ?'); params.push(JSON.stringify(data.permissions)); }

  if (sets.length === 0) return;
  sets.push('updated_by = ?');
  params.push(updatedBy);
  params.push(id);
  await executeWithConn(`UPDATE roles SET ${sets.join(', ')} WHERE id = ? AND deleted_at IS NULL`, params, conn);
}

export async function softDelete(id: string, deletedBy: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn('UPDATE roles SET deleted_at = NOW(), deleted_by = ? WHERE id = ? AND deleted_at IS NULL', [deletedBy, id], conn);
}