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

export interface StageTransition {
  fromStageId: string;
  toStageId: string;
}

export async function findByEmployeeId(employeeId: string, conn?: PoolConnection): Promise<StageTransition[]> {
  const rows = await queryWithConn(
    'SELECT from_stage_id, to_stage_id FROM employee_stage_transitions WHERE employee_id = ? AND deleted_at IS NULL ORDER BY created_at ASC, id ASC',
    [employeeId], conn,
  ) as unknown as { from_stage_id: string; to_stage_id: string }[];
  return rows.map((r) => ({ fromStageId: r.from_stage_id, toStageId: r.to_stage_id }));
}

// Same replace-all-in-a-transaction pattern as employeePermissionOverride.repo.ts
// — soft-deletes the employee's current rows and inserts the new set, so it
// always runs inside the caller's employee create/update transaction.
export async function replaceForEmployee(
  employeeId: string,
  transitions: StageTransition[],
  actorId: string,
  conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    'UPDATE employee_stage_transitions SET deleted_at = NOW(), deleted_by = ? WHERE employee_id = ? AND deleted_at IS NULL',
    [actorId, employeeId], conn,
  );

  for (const transition of transitions) {
    await createWithId('employee_stage_transitions', 'EST', (id) => executeWithConn(
      'INSERT INTO employee_stage_transitions (id, employee_id, from_stage_id, to_stage_id, created_by) VALUES (?, ?, ?, ?, ?)',
      [id, employeeId, transition.fromStageId, transition.toStageId, actorId],
      conn,
    ), conn);
  }
}