import { queryWithConn } from '../../config/database.js';

export interface StageHistoryContextRow {
  id: string;
  order_product_id: string;
  stage_id: string;
  entered_at: Date;
  employee_id: string | null;
  jo_no: string | null;
  quantity: number;
  dispatched_quantity: number;
  cancelled_quantity: number;
  order_id: string;
  order_no: string;
  priority: string;
  customer_name: string;
  item_name: string;
  category_name: string | null;
  metal_name: string | null;
  purity_name: string | null;
  employee_name: string | null;
  remarks: string | null;
}

// One row per stage this line ever entered, with everything the Logs page
// needs already resolved — grouping into from/to transitions happens in the
// service layer since that's plain JS logic, not something SQL does cleanly.
export async function findAllStageHistory(): Promise<StageHistoryContextRow[]> {
  const sql = `
    SELECT
      sh.id as id, sh.order_product_id as order_product_id, sh.stage_id, sh.entered_at, sh.remarks,
      sh.employee_id as employee_id,
      op.jo_no, op.quantity, op.dispatched_quantity, op.cancelled_quantity,
      o.id as order_id, o.order_no, o.priority,
      c.name as customer_name,
      p.name as item_name,
      cat.name as category_name, mt.name as metal_name, pur.name as purity_name,
      e.name as employee_name
    FROM order_stage_history sh
    JOIN order_products op ON op.id = sh.order_product_id
    JOIN orders o ON o.id = op.order_id
    JOIN clients c ON c.id = o.client_id
    JOIN products p ON p.id = op.product_id
    LEFT JOIN product_categories cat ON cat.id = p.category_id
    LEFT JOIN metal_types mt ON mt.id = p.metal_type_id
    LEFT JOIN purities pur ON pur.id = p.purity_id
    LEFT JOIN employees e ON e.id = sh.employee_id
    WHERE op.deleted_at IS NULL AND o.deleted_at IS NULL
    ORDER BY sh.order_product_id ASC, sh.entered_at ASC
  `;
  return queryWithConn(sql, undefined, undefined) as unknown as Promise<StageHistoryContextRow[]>;
}

export interface DispatchEventContextRow {
  id: string;
  order_product_id: string;
  quantity: number;
  dispatch_date: Date;
  employee_id: string | null;
  jo_no: string | null;
  line_quantity: number;
  line_dispatched_quantity: number;
  line_cancelled_quantity: number;
  order_id: string;
  order_no: string;
  priority: string;
  customer_name: string;
  item_name: string;
  category_name: string | null;
  metal_name: string | null;
  purity_name: string | null;
  employee_name: string | null;
}

// One row per dispatch event (a line can be dispatched in several partial
// batches over time, each its own row here) — same context columns as
// findAllStageHistory so the service layer can merge both into one feed.
export async function findAllDispatchEvents(): Promise<DispatchEventContextRow[]> {
  const sql = `
    SELECT
      dh.id as id, dh.order_product_id as order_product_id, dh.quantity, dh.dispatch_date,
      dh.dispatched_by_employee_id as employee_id,
      op.jo_no, op.quantity as line_quantity, op.dispatched_quantity as line_dispatched_quantity,
      op.cancelled_quantity as line_cancelled_quantity,
      o.id as order_id, o.order_no, o.priority,
      c.name as customer_name,
      p.name as item_name,
      cat.name as category_name, mt.name as metal_name, pur.name as purity_name,
      e.name as employee_name
    FROM order_dispatch_history dh
    JOIN order_products op ON op.id = dh.order_product_id
    JOIN orders o ON o.id = op.order_id
    JOIN clients c ON c.id = o.client_id
    JOIN products p ON p.id = op.product_id
    LEFT JOIN product_categories cat ON cat.id = p.category_id
    LEFT JOIN metal_types mt ON mt.id = p.metal_type_id
    LEFT JOIN purities pur ON pur.id = p.purity_id
    LEFT JOIN employees e ON e.id = dh.dispatched_by_employee_id
    WHERE op.deleted_at IS NULL AND o.deleted_at IS NULL
  `;
  return queryWithConn(sql, undefined, undefined) as unknown as Promise<DispatchEventContextRow[]>;
}

export interface CancelEventContextRow {
  order_product_id: string;
  cancelled: boolean;
  cancelled_quantity: number;
  cancel_date: Date;
  updated_at: Date;
  employee_id: string | null;
  jo_no: string | null;
  line_quantity: number;
  line_dispatched_quantity: number;
  order_id: string;
  order_no: string;
  priority: string;
  customer_name: string;
  item_name: string;
  category_name: string | null;
  metal_name: string | null;
  purity_name: string | null;
  employee_name: string | null;
}

// A line can be partially cancelled more than once before it's fully
// cancelled, but there's no per-cancellation history table (unlike dispatch)
// — cancelled_quantity/cancel_date/updated_at are just the line's current
// cumulative state. So this surfaces one row per line with any cancelled
// quantity at all (partial or full); the service layer labels it
// 'cancelled' vs 'partially_cancelled' from the `cancelled` flag.
export async function findAllCancelEvents(): Promise<CancelEventContextRow[]> {
  const sql = `
    SELECT
      op.id as order_product_id, op.cancelled, op.cancelled_quantity, op.cancel_date, op.updated_at,
      op.cancelled_by_employee_id as employee_id,
      op.jo_no, op.quantity as line_quantity, op.dispatched_quantity as line_dispatched_quantity,
      o.id as order_id, o.order_no, o.priority,
      c.name as customer_name,
      p.name as item_name,
      cat.name as category_name, mt.name as metal_name, pur.name as purity_name,
      e.name as employee_name
    FROM order_products op
    JOIN orders o ON o.id = op.order_id
    JOIN clients c ON c.id = o.client_id
    JOIN products p ON p.id = op.product_id
    LEFT JOIN product_categories cat ON cat.id = p.category_id
    LEFT JOIN metal_types mt ON mt.id = p.metal_type_id
    LEFT JOIN purities pur ON pur.id = p.purity_id
    LEFT JOIN employees e ON e.id = op.cancelled_by_employee_id
    WHERE op.deleted_at IS NULL AND o.deleted_at IS NULL AND op.cancelled_quantity > 0
  `;
  return queryWithConn(sql, undefined, undefined) as unknown as Promise<CancelEventContextRow[]>;
}

export interface OrderCompletionContextRow {
  order_id: string;
  order_no: string;
  priority: string;
  customer_name: string;
  quantity: number;
  cancelled_quantity: number;
  dispatched: boolean;
  dispatch_date: Date | null;
  cancel_date: Date | null;
  updated_at: Date;
  dispatched_by_employee_name: string | null;
  cancelled_by_employee_name: string | null;
}

// Every active line of every active order — the service layer uses this to
// work out which orders are now fully resolved (every line dispatched or
// cancelled) and, for those, when the last line settled.
export async function findAllOrderProductsForCompletion(): Promise<OrderCompletionContextRow[]> {
  const sql = `
    SELECT
      o.id as order_id, o.order_no, o.priority, c.name as customer_name,
      op.quantity, op.cancelled_quantity, op.dispatched, op.dispatch_date, op.cancel_date, op.updated_at,
      ed.name as dispatched_by_employee_name, ec.name as cancelled_by_employee_name
    FROM order_products op
    JOIN orders o ON o.id = op.order_id
    JOIN clients c ON c.id = o.client_id
    LEFT JOIN employees ed ON ed.id = op.dispatched_by_employee_id
    LEFT JOIN employees ec ON ec.id = op.cancelled_by_employee_id
    WHERE op.deleted_at IS NULL AND o.deleted_at IS NULL AND o.lifecycle = 'active'
  `;
  return queryWithConn(sql, undefined, undefined) as unknown as Promise<OrderCompletionContextRow[]>;
}

export interface OrderCreationContextRow {
  order_id: string;
  order_no: string;
  priority: string;
  customer_name: string;
  created_at: Date;
  taken_by_employee_name: string | null;
  total_quantity: number;
}

// One row per order, however many lines it has — the order-creation event
// is order-level, not per-line, so quantities are summed across all of an
// order's active lines rather than listed separately.
export async function findAllOrderCreationEvents(): Promise<OrderCreationContextRow[]> {
  const sql = `
    SELECT
      o.id as order_id, o.order_no, o.priority, c.name as customer_name,
      o.created_at, e.name as taken_by_employee_name,
      COALESCE(SUM(op.quantity), 0) as total_quantity
    FROM orders o
    JOIN clients c ON c.id = o.client_id
    LEFT JOIN employees e ON e.id = o.taken_by_employee_id
    LEFT JOIN order_products op ON op.order_id = o.id AND op.deleted_at IS NULL
    WHERE o.deleted_at IS NULL
    GROUP BY o.id, o.order_no, o.priority, c.name, o.created_at, e.name
  `;
  return queryWithConn(sql, undefined, undefined) as unknown as Promise<OrderCreationContextRow[]>;
}