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

export interface DispatchHistoryEntry {
  id: string;
  order_product_id: string;
  quantity: number;
  weight: number;
  dispatch_date: string;
  dispatched_by_employee_id?: string;
  dispatched_by_name?: string;
  received_by_name?: string;
  employee_name?: string;
  created_at: string;
}

function rowToEntry(row: Record<string, unknown>): DispatchHistoryEntry {
  return {
    id: row.id as string,
    order_product_id: row.order_product_id as string,
    quantity: row.quantity as number,
    weight: Number(row.weight),
    dispatch_date: (row.dispatch_date as Date).toISOString(),
    dispatched_by_employee_id: (row.dispatched_by_employee_id as string) ?? undefined,
    dispatched_by_name: (row.dispatched_by_name as string) ?? undefined,
    received_by_name: (row.received_by_name as string) ?? undefined,
    employee_name: (row.employee_name as string) ?? undefined,
    created_at: (row.created_at as Date).toISOString(),
  };
}

export async function insert(
  data: {
    order_product_id: string; quantity: number; weight: number; dispatch_date: string;
    dispatched_by_employee_id?: string | null; dispatched_by_name?: string | null; received_by_name?: string | null;
  },
  id: string,
  conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    `INSERT INTO order_dispatch_history (id, order_product_id, quantity, weight, dispatch_date, dispatched_by_employee_id, dispatched_by_name, received_by_name)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?)`,
    // mysql2 rejects a raw ISO string ("...T...Z") for a DATETIME column —
    // it only auto-formats a JS Date object correctly.
    [id, data.order_product_id, data.quantity, data.weight, new Date(data.dispatch_date), data.dispatched_by_employee_id ?? null, data.dispatched_by_name ?? null, data.received_by_name ?? null],
    conn,
  );
}

export async function findById(id: string, conn?: PoolConnection): Promise<DispatchHistoryEntry | null> {
  const sql = `
    SELECT h.id as id, h.order_product_id as order_product_id, h.quantity, h.weight,
           h.dispatch_date, h.dispatched_by_employee_id as dispatched_by_employee_id, h.dispatched_by_name as dispatched_by_name,
           h.received_by_name as received_by_name,
           h.created_at, e.name as employee_name
    FROM order_dispatch_history h
    LEFT JOIN employees e ON e.id = h.dispatched_by_employee_id
    WHERE h.id = ?
  `;
  const rows = await queryWithConn(sql, [id], conn) as unknown as Record<string, unknown>[];
  return rows[0] ? rowToEntry(rows[0]) : null;
}

export async function update(
  id: string,
  data: { quantity: number; weight: number; dispatch_date: string; dispatched_by_employee_id?: string | null; dispatched_by_name?: string | null; received_by_name?: string | null },
  conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    `UPDATE order_dispatch_history
     SET quantity = ?, weight = ?, dispatch_date = ?, dispatched_by_employee_id = ?, dispatched_by_name = ?, received_by_name = ?
     WHERE id = ?`,
    [data.quantity, data.weight, new Date(data.dispatch_date), data.dispatched_by_employee_id ?? null, data.dispatched_by_name ?? null, data.received_by_name ?? null, id],
    conn,
  );
}

// Used by revertDispatchHistory() in order.service.ts — a full undo removes
// the record entirely (per product decision: no "reverted" audit row is
// kept), and the dispatched quantity/weight on the line is recalculated
// from whatever history rows remain.
export async function remove(id: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn('DELETE FROM order_dispatch_history WHERE id = ?', [id], conn);
}

export interface DispatchHistoryReportRow {
  id: string;
  order_no: string;
  order_date: string;
  client_id: string;
  client_name: string;
  product_name: string;
  hallmark_grade_name?: string;
  jo_no?: string;
  quantity: number;
  weight: number;
  size?: string;
  dispatch_date: string;
  dispatched_by_name: string;
  received_by_name?: string;
}

// One row per actual dispatch batch (never per order_product), with every
// name the Dispatch Report needs already joined in. Deliberately reads
// order_dispatch_history — the append-only ledger — rather than deriving
// rows from order_products' live dispatched_quantity/dispatch_date columns:
// those live columns represent *current* state (and get decremented by an
// approved return, see orderReturn.service.ts's approve()), so a report
// built from them would retroactively show a smaller quantity for a past
// dispatch date once a return later reduces the running total. This table
// is never touched by a return, so every historical batch — and its real
// date/quantity/weight — stays exactly as it was when it happened.
export async function findAllForReport(): Promise<DispatchHistoryReportRow[]> {
  const sql = `
    SELECT dh.id as id, dh.quantity, dh.weight, dh.dispatch_date,
           dh.dispatched_by_name as dispatched_by_name_raw, dh.received_by_name,
           op.jo_no, op.size,
           o.order_no, o.order_date, o.client_id,
           c.name as client_name,
           p.name as product_name,
           hg.name as hallmark_grade_name,
           e.name as employee_name
    FROM order_dispatch_history dh
    JOIN order_products op ON op.id = dh.order_product_id AND op.deleted_at IS NULL
    JOIN orders o ON o.id = op.order_id AND o.deleted_at IS NULL
    LEFT JOIN clients c ON c.id = o.client_id AND c.deleted_at IS NULL
    LEFT JOIN products p ON p.id = op.product_id AND p.deleted_at IS NULL
    LEFT JOIN hallmark_grades hg ON hg.id = op.hallmark_grade_id AND hg.deleted_at IS NULL
    LEFT JOIN employees e ON e.id = dh.dispatched_by_employee_id AND e.deleted_at IS NULL
    ORDER BY dh.dispatch_date ASC
  `;
  const rows = await queryWithConn(sql, undefined, undefined) as unknown as Record<string, unknown>[];
  return rows.map((r) => ({
    id: r.id as string,
    order_no: r.order_no as string,
    // orders.order_date is DATEONLY — mysql2 returns that as a plain
    // "YYYY-MM-DD" string, not a Date object, unlike the DATETIME columns
    // elsewhere in this row (dispatch_date etc.), which do come back as Date.
    order_date: r.order_date instanceof Date ? r.order_date.toISOString() : (r.order_date as string),
    client_id: r.client_id as string,
    client_name: (r.client_name as string) ?? 'Unknown customer',
    product_name: (r.product_name as string) ?? 'Unknown product',
    hallmark_grade_name: (r.hallmark_grade_name as string) ?? undefined,
    jo_no: (r.jo_no as string) ?? undefined,
    quantity: r.quantity as number,
    weight: Number(r.weight),
    size: (r.size as string) ?? undefined,
    dispatch_date: (r.dispatch_date as Date).toISOString(),
    // Same fallback order OrderProductRow.tsx already uses: the registered
    // employee's name when dispatched_by_employee_id was set, else whoever
    // was typed in free-text at dispatch time.
    dispatched_by_name: (r.employee_name as string) ?? (r.dispatched_by_name_raw as string) ?? '—',
    received_by_name: (r.received_by_name as string) ?? undefined,
  }));
}

export async function findByOrderProductId(orderProductId: string, conn?: PoolConnection): Promise<DispatchHistoryEntry[]> {
  const sql = `
    SELECT h.id as id, h.order_product_id as order_product_id, h.quantity, h.weight,
           h.dispatch_date, h.dispatched_by_employee_id as dispatched_by_employee_id, h.dispatched_by_name as dispatched_by_name,
           h.received_by_name as received_by_name,
           h.created_at, e.name as employee_name
    FROM order_dispatch_history h
    LEFT JOIN employees e ON e.id = h.dispatched_by_employee_id
    WHERE h.order_product_id = ?
    ORDER BY h.dispatch_date ASC
  `;
  const rows = await queryWithConn(sql, [orderProductId], conn) as unknown as Record<string, unknown>[];
  return rows.map(rowToEntry);
}