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

export interface PermissionOverride {
  permission: string;
  effect: 'grant' | 'deny';
}

export async function findByEmployeeId(employeeId: string, conn?: PoolConnection): Promise<PermissionOverride[]> {
  const rows = await queryWithConn(
    'SELECT permission, effect FROM employee_permission_overrides WHERE employee_id = ? AND deleted_at IS NULL',
    [employeeId], conn,
  ) as unknown as { permission: string; effect: 'grant' | 'deny' }[];
  return rows.map((r) => ({ permission: r.permission, effect: r.effect }));
}

// Soft-deletes whatever overrides the employee currently has and inserts the
// new set, all within the caller's transaction — called from the same
// transaction as the employee create/update write so the two stay in sync.
export async function replaceForEmployee(
  employeeId: string,
  overrides: PermissionOverride[],
  actorId: string,
  conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    'UPDATE employee_permission_overrides SET deleted_at = NOW(), deleted_by = ? WHERE employee_id = ? AND deleted_at IS NULL',
    [actorId, employeeId], conn,
  );

  for (const override of overrides) {
    await createWithId('employee_permission_overrides', 'EPO', (id) => executeWithConn(
      'INSERT INTO employee_permission_overrides (id, employee_id, permission, effect, created_by) VALUES (?, ?, ?, ?, ?)',
      [id, employeeId, override.permission, override.effect, actorId],
      conn,
    ), conn);
  }
}