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

function rowToHistory(row: Record<string, unknown>): OrderStageHistory {
  const entered = row.entered_at as Date;
  const completed = row.completed_at as Date | null;
  return {
    id: row.id as string,
    order_product_id: row.order_product_id as string,
    stage_id: row.stage_id as string,
    entered_at: entered && !isNaN(entered.getTime()) ? entered.toISOString() : new Date().toISOString(),
    completed_at: completed && !isNaN(completed.getTime()) ? completed.toISOString() : undefined,
    employee_id: (row.employee_id as string) ?? undefined,
    remarks: (row.remarks as string) ?? undefined,
  };
}

export async function findByOrderProductId(orderProductId: string, conn?: PoolConnection): Promise<OrderStageHistory[]> {
  const sql = `
    SELECT id as id, order_product_id as order_product_id,
           stage_id, entered_at, completed_at, employee_id as employee_id, remarks
    FROM order_stage_history
    WHERE order_product_id = ?
    ORDER BY entered_at ASC
  `;
  const rows = await queryWithConn(sql, [orderProductId], conn) as unknown as Record<string, unknown>[];
  return rows.map(rowToHistory);
}

export async function insert(
  data: { order_product_id: string; stage_id: string; entered_at: string; employee_id?: string; remarks?: string },
  id: string,
  conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    `INSERT INTO order_stage_history (id, order_product_id, stage_id, entered_at, employee_id, remarks)
     VALUES (?, ?, ?, ?, ?, ?)`,
    [id, data.order_product_id, data.stage_id, data.entered_at,
     data.employee_id ?? null, data.remarks ?? null],
    conn,
  );
}

export async function completeStage(
  orderProductId: string,
  stageId: string,
  completedAt: string,
  conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    'UPDATE order_stage_history SET completed_at = ? WHERE order_product_id = ? AND stage_id = ? AND completed_at IS NULL',
    [completedAt, orderProductId, stageId],
    conn,
  );
}

export async function getCurrentStage(orderProductId: string, conn?: PoolConnection): Promise<OrderStageHistory | null> {
  const sql = `
    SELECT id as id, order_product_id as order_product_id,
           stage_id, entered_at, completed_at, employee_id as employee_id, remarks
    FROM order_stage_history
    WHERE order_product_id = ? AND completed_at IS NULL
    ORDER BY entered_at DESC
    LIMIT 1
  `;
  const rows = await queryWithConn(sql, [orderProductId], conn) as unknown as Record<string, unknown>[];
  return rows.length ? rowToHistory(rows[0]!) : null;
}