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

export interface OrderApprovalRequest {
  id: string;
  payload: Record<string, unknown>;
  status: 'pending' | 'approved' | 'cancelled';
  requested_by_employee_id: string;
  requested_by_employee_name?: string;
  reviewed_by_employee_id?: string;
  reviewed_by_employee_name?: string;
  reviewed_at?: string;
  resulting_order_id?: string;
  created_at?: string;
}

const SELECT_WITH_NAMES = `
  SELECT r.id, r.payload, r.status, r.requested_by_employee_id, r.reviewed_by_employee_id, r.reviewed_at,
         r.resulting_order_id, r.created_at,
         req.name as requested_by_employee_name, rev.name as reviewed_by_employee_name
  FROM order_approval_requests r
  LEFT JOIN employees req ON req.id = r.requested_by_employee_id AND req.deleted_at IS NULL
  LEFT JOIN employees rev ON rev.id = r.reviewed_by_employee_id AND rev.deleted_at IS NULL
`;

function rowToRequest(row: Record<string, unknown>): OrderApprovalRequest {
  const payload = typeof row.payload === 'string' ? JSON.parse(row.payload) : row.payload;
  return {
    id: row.id as string,
    payload: payload as Record<string, unknown>,
    status: row.status as OrderApprovalRequest['status'],
    requested_by_employee_id: row.requested_by_employee_id as string,
    requested_by_employee_name: (row.requested_by_employee_name as string) ?? undefined,
    reviewed_by_employee_id: (row.reviewed_by_employee_id as string) ?? undefined,
    reviewed_by_employee_name: (row.reviewed_by_employee_name as string) ?? undefined,
    reviewed_at: row.reviewed_at instanceof Date ? row.reviewed_at.toISOString() : undefined,
    resulting_order_id: (row.resulting_order_id as string) ?? undefined,
    created_at: row.created_at instanceof Date ? row.created_at.toISOString() : undefined,
  };
}

export async function insert(
  id: string,
  payload: Record<string, unknown>,
  requestedByEmployeeId: string,
  createdBy: string,
  conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    `INSERT INTO order_approval_requests (id, payload, status, requested_by_employee_id, created_by)
     VALUES (?, ?, 'pending', ?, ?)`,
    [id, JSON.stringify(payload), requestedByEmployeeId, createdBy], conn,
  );
}

export interface FindPendingFilters {
  employeeId?: string;
  dateFrom?: string; // filters on submitted-at (r.created_at), inclusive
  dateTo?: string;
}

export async function findPending(filters: FindPendingFilters = {}, conn?: PoolConnection): Promise<OrderApprovalRequest[]> {
  const conditions = [`r.status = 'pending'`, 'r.deleted_at IS NULL'];
  const params: unknown[] = [];

  if (filters.employeeId) {
    conditions.push('r.requested_by_employee_id = ?');
    params.push(filters.employeeId);
  }
  if (filters.dateFrom) {
    conditions.push('r.created_at >= ?');
    params.push(`${filters.dateFrom} 00:00:00`);
  }
  if (filters.dateTo) {
    conditions.push('r.created_at <= ?');
    params.push(`${filters.dateTo} 23:59:59`);
  }

  const rows = await queryWithConn(
    `${SELECT_WITH_NAMES}
     WHERE ${conditions.join(' AND ')}
     ORDER BY r.created_at ASC`,
    params.length ? params : undefined, conn,
  ) as unknown as Record<string, unknown>[];
  return rows.map(rowToRequest);
}

export interface FindCancelledFilters {
  employeeId?: string;
  dateFrom?: string; // filters on reviewed-at (r.reviewed_at), inclusive
  dateTo?: string;
}

export async function findCancelled(filters: FindCancelledFilters = {}, conn?: PoolConnection): Promise<OrderApprovalRequest[]> {
  const conditions = [`r.status = 'cancelled'`, 'r.deleted_at IS NULL'];
  const params: unknown[] = [];

  if (filters.employeeId) {
    conditions.push('r.requested_by_employee_id = ?');
    params.push(filters.employeeId);
  }
  if (filters.dateFrom) {
    conditions.push('r.reviewed_at >= ?');
    params.push(`${filters.dateFrom} 00:00:00`);
  }
  if (filters.dateTo) {
    conditions.push('r.reviewed_at <= ?');
    params.push(`${filters.dateTo} 23:59:59`);
  }

  const rows = await queryWithConn(
    `${SELECT_WITH_NAMES}
     WHERE ${conditions.join(' AND ')}
     ORDER BY r.reviewed_at DESC`,
    params.length ? params : undefined, conn,
  ) as unknown as Record<string, unknown>[];
  return rows.map(rowToRequest);
}

export async function findById(id: string, conn?: PoolConnection): Promise<OrderApprovalRequest | null> {
  const rows = await queryWithConn(
    `${SELECT_WITH_NAMES}
     WHERE r.id = ? AND r.deleted_at IS NULL`,
    [id], conn,
  ) as unknown as Record<string, unknown>[];
  return rows.length ? rowToRequest(rows[0]!) : null;
}

export async function markApproved(id: string, resultingOrderId: string, reviewerId: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn(
    `UPDATE order_approval_requests
     SET status = 'approved', resulting_order_id = ?, reviewed_by_employee_id = ?, reviewed_at = NOW(), updated_by = ?
     WHERE id = ? AND deleted_at IS NULL`,
    [resultingOrderId, reviewerId, reviewerId, id], conn,
  );
}

export async function markCancelled(id: string, reviewerId: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn(
    `UPDATE order_approval_requests
     SET status = 'cancelled', reviewed_by_employee_id = ?, reviewed_at = NOW(), updated_by = ?
     WHERE id = ? AND deleted_at IS NULL`,
    [reviewerId, reviewerId, id], conn,
  );
}