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

function rowToStage(row: Record<string, unknown>): Stage {
  return {
    id: row.id as string,
    name: row.name as string,
    color: row.color as string,
    sort_order: row.sort_order as number,
    idle_threshold_minutes: row.idle_threshold_minutes as number,
  };
}

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

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

export async function findByName(name: string, excludeId?: string, conn?: PoolConnection): Promise<Stage | null> {
  const sql = excludeId
    ? `SELECT id, name, color, sort_order, idle_threshold_minutes FROM stages WHERE LOWER(name) = LOWER(?) AND id != ? AND deleted_at IS NULL LIMIT 1`
    : `SELECT id, name, color, sort_order, idle_threshold_minutes FROM stages 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 ? rowToStage(rows[0]!) : null;
}

export async function findMaxSortOrder(conn?: PoolConnection): Promise<number> {
  const rows = await queryWithConn(
    `SELECT MAX(sort_order) as maxOrder FROM stages WHERE deleted_at IS NULL`, undefined, conn,
  ) as unknown as { maxOrder: number | null }[];
  return rows[0]?.maxOrder ?? 0;
}

export async function insert(data: { name: string; color: string; sort_order: number; idle_threshold_minutes: number }, id: string, createdBy: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn(
    'INSERT INTO stages (id, name, color, sort_order, idle_threshold_minutes, created_by) VALUES (?, ?, ?, ?, ?, ?)',
    [id, data.name, data.color, data.sort_order, data.idle_threshold_minutes, createdBy], conn,
  );
}

export async function update(id: string, data: { name: string; color: string; idle_threshold_minutes: number }, updatedBy: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn(
    'UPDATE stages SET name = ?, color = ?, idle_threshold_minutes = ?, updated_by = ? WHERE id = ? AND deleted_at IS NULL',
    [data.name, data.color, data.idle_threshold_minutes, updatedBy, id], conn,
  );
}

export async function updateSortOrder(id: string, sortOrder: number, updatedBy: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn(
    'UPDATE stages SET sort_order = ?, updated_by = ? WHERE id = ? AND deleted_at IS NULL',
    [sortOrder, updatedBy, id], conn,
  );
}

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