import type { PoolConnection } from 'mysql2/promise';
import { queryWithConn, executeWithConn } from '../../config/database.js';
import { now } from '../utils/helpers.js';
import type { Order, OrderProduct, OrderStatus } from '../types/index.js';

function rowToOrderProduct(row: Record<string, unknown>): OrderProduct {
  return {
    id: row.id as string,
    order_id: row.order_id as string,
    product_id: row.product_id as string,
    quantity: row.quantity as number,
    weight: Number(row.weight),
    size: (row.size as string) ?? undefined,
    jo_no: (row.jo_no as string) ?? undefined,
    jo_no_entered_at: row.jo_no_entered_at instanceof Date ? row.jo_no_entered_at.toISOString() : undefined,
    remark: (row.remark as string) ?? undefined,
    hallmark_grade_id: (row.hallmark_grade_id as string) ?? undefined,
    completed_stages: row.completed_stages as number,
    stage_updated_at: row.stage_updated_at instanceof Date ? row.stage_updated_at.toISOString() : undefined,
    stage_entered_at: row.stage_entered_at instanceof Date ? row.stage_entered_at.toISOString() : undefined,
    dispatched: Boolean(row.dispatched),
    dispatched_quantity: (row.dispatched_quantity as number) ?? undefined,
    dispatched_weight: row.dispatched_weight != null ? Number(row.dispatched_weight) : undefined,
    dispatch_date: row.dispatch_date instanceof Date ? row.dispatch_date.toISOString() : undefined,
    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,
    cancelled: Boolean(row.cancelled),
    cancelled_quantity: (row.cancelled_quantity as number) ?? undefined,
    cancelled_weight: row.cancelled_weight != null ? Number(row.cancelled_weight) : undefined,
    cancel_date: row.cancel_date instanceof Date ? row.cancel_date.toISOString() : undefined,
    cancelled_by_employee_id: (row.cancelled_by_employee_id as string) ?? undefined,
    cancel_reason: (row.cancel_reason as string) ?? undefined,
    created_at: row.created_at instanceof Date ? row.created_at.toISOString() : undefined,
    updated_at: row.updated_at instanceof Date ? row.updated_at.toISOString() : undefined,
    product_name: (row.product_name as string) ?? undefined,
    category_name: (row.category_name as string) ?? undefined,
    hallmark_grade_name: (row.hallmark_grade_name as string) ?? undefined,
    dispatched_by_employee_name: (row.dispatched_by_employee_name as string) ?? undefined,
    cancelled_by_employee_name: (row.cancelled_by_employee_name as string) ?? undefined,
    return_id: (row.return_id as string) ?? undefined,
    return_status: (row.return_status as OrderProduct['return_status']) ?? undefined,
  };
}

function rowToOrder(row: Record<string, unknown>): Order {
  return {
    id: row.id as string,
    order_no: row.order_no as string,
    order_date: row.order_date as string,
    client_id: row.client_id as string,
    taken_by_employee_id: row.taken_by_employee_id as string,
    received_through: row.received_through as Order['received_through'],
    delivery_date: row.delivery_date as string,
    priority: row.priority as Order['priority'],
    received_by_client_contact: (row.received_by_client_contact as string) ?? undefined,
    so_number: (row.so_number as string) ?? undefined,
    so_number_entered_at: row.so_number_entered_at instanceof Date ? row.so_number_entered_at.toISOString() : undefined,
    po_number: (row.po_number as string) ?? undefined,
    po_date: (row.po_date as string) ?? undefined,
    seal_id: (row.seal_id as string) ?? undefined,
    remarks: (row.remarks as string) ?? undefined,
    lifecycle: row.lifecycle as Order['lifecycle'],
    products: [],
    created_at: row.created_at instanceof Date ? row.created_at.toISOString() : undefined,
    client_name: (row.client_name as string) ?? undefined,
    taken_by_employee_name: (row.taken_by_employee_name as string) ?? undefined,
    approval_status: (row.approval_status as Order['approval_status']) ?? 'approved',
    requested_by_employee_id: (row.requested_by_employee_id as string) ?? undefined,
    requested_by_employee_name: (row.requested_by_employee_name as string) ?? undefined,
    approved_by_employee_id: (row.approved_by_employee_id as string) ?? undefined,
    approved_by_employee_name: (row.approved_by_employee_name as string) ?? undefined,
    approved_at: row.approved_at instanceof Date ? row.approved_at.toISOString() : undefined,
  };
}

export async function findAll(
  filters: {
    status?: string;
    clientId?: string;
    employeeId?: string;
    dateFrom?: string;
    dateTo?: string;
    search?: string;
    page?: number;
    limit?: number;
    sortBy?: string;
    sortOrder?: string;
  },
  conn?: PoolConnection,
): Promise<{ rows: Order[]; total: number }> {
  const page = filters.page ?? 1;
  const limit = filters.limit ?? 20;
  const offset = (Number(page) - 1) * Number(limit);
  const conditions = ['o.deleted_at IS NULL'];
  const params: unknown[] = [];

  if (filters.clientId) { conditions.push('o.client_id = ?'); params.push(filters.clientId); }
  if (filters.employeeId) { conditions.push('o.taken_by_employee_id = ?'); params.push(filters.employeeId); }
  if (filters.dateFrom) { conditions.push('o.order_date >= ?'); params.push(filters.dateFrom); }
  if (filters.dateTo) { conditions.push('o.order_date <= ?'); params.push(filters.dateTo); }
  if (filters.search) { conditions.push('o.order_no LIKE ?'); params.push(`%${filters.search}%`); }

  const where = `WHERE ${conditions.join(' AND ')}`;
  const allowedSorts = ['order_date', 'order_no', 'delivery_date', 'priority'];
  const sortBy = filters.sortBy && allowedSorts.includes(filters.sortBy) ? filters.sortBy : 'order_date';
  const sortOrder = filters.sortOrder === 'asc' ? 'ASC' : 'DESC';

  const countRows = await queryWithConn(`SELECT COUNT(*) as total FROM orders o ${where}`, params.length ? params : undefined, conn) as unknown as { total: number }[];
  const total = countRows[0]?.total ?? 0;

  const dataSql = `
    SELECT o.id as id, o.order_no, o.order_date,
           o.client_id as client_id, o.taken_by_employee_id as taken_by_employee_id,
           o.received_through, o.delivery_date, o.priority, o.received_by_client_contact,
           o.so_number, o.so_number_entered_at, o.po_number, o.po_date, o.seal_id as seal_id, o.remarks, o.lifecycle,
           o.created_at,
           o.approval_status, o.requested_by_employee_id, o.approved_by_employee_id, o.approved_at,
           c.name as client_name, e.name as taken_by_employee_name,
           rq.name as requested_by_employee_name, ap.name as approved_by_employee_name
    FROM orders o
    LEFT JOIN clients c ON c.id = o.client_id AND c.deleted_at IS NULL
    LEFT JOIN employees e ON e.id = o.taken_by_employee_id AND e.deleted_at IS NULL
    LEFT JOIN employees rq ON rq.id = o.requested_by_employee_id AND rq.deleted_at IS NULL
    LEFT JOIN employees ap ON ap.id = o.approved_by_employee_id AND ap.deleted_at IS NULL
    ${where}
    ORDER BY o.${sortBy} ${sortOrder}
    LIMIT ? OFFSET ?
  `;
  const dataRows = await queryWithConn(dataSql, [...params, Number(limit), Number(offset)], conn) as unknown as Record<string, unknown>[];
  const orders = dataRows.map(rowToOrder);

  for (const order of orders) {
    order.products = await findByOrderId(order.id, conn);
  }

  return { rows: orders, total };
}

export async function findById(id: string, conn?: PoolConnection): Promise<Order | null> {
  const sql = `
    SELECT o.id as id, o.order_no, o.order_date,
           o.client_id as client_id, o.taken_by_employee_id as taken_by_employee_id,
           o.received_through, o.delivery_date, o.priority, o.received_by_client_contact,
           o.so_number, o.so_number_entered_at, o.po_number, o.po_date, o.seal_id as seal_id, o.remarks, o.lifecycle,
           o.created_at,
           o.approval_status, o.requested_by_employee_id, o.approved_by_employee_id, o.approved_at,
           c.name as client_name, e.name as taken_by_employee_name,
           rq.name as requested_by_employee_name, ap.name as approved_by_employee_name
    FROM orders o
    LEFT JOIN clients c ON c.id = o.client_id AND c.deleted_at IS NULL
    LEFT JOIN employees e ON e.id = o.taken_by_employee_id AND e.deleted_at IS NULL
    LEFT JOIN employees rq ON rq.id = o.requested_by_employee_id AND rq.deleted_at IS NULL
    LEFT JOIN employees ap ON ap.id = o.approved_by_employee_id AND ap.deleted_at IS NULL
    WHERE o.id = ? AND o.deleted_at IS NULL
  `;
  const rows = await queryWithConn(sql, [id], conn) as unknown as Record<string, unknown>[];
  if (!rows.length) return null;
  const order = rowToOrder(rows[0]!);
  order.products = await findByOrderId(id, conn);
  return order;
}

export async function findByOrderId(orderId: string, conn?: PoolConnection): Promise<OrderProduct[]> {
  const sql = `
    SELECT op.id as id, op.order_id as order_id,
           op.product_id as product_id, op.quantity, op.weight, op.size, op.jo_no, op.jo_no_entered_at, op.remark, op.hallmark_grade_id,
           op.created_at,
           op.completed_stages, op.stage_updated_at, op.dispatched, op.dispatched_quantity, op.dispatched_weight, op.dispatch_date,
           op.dispatched_by_employee_id as dispatched_by_employee_id, op.dispatched_by_name as dispatched_by_name,
           op.received_by_name as received_by_name,
           op.cancelled, op.cancelled_quantity, op.cancelled_weight, op.cancel_date,
           op.cancelled_by_employee_id as cancelled_by_employee_id, op.cancel_reason, op.updated_at, op.return_id,
           -- The most recent live return raised against THIS line (by
           -- order_product_id, not op.return_id) — op.return_id is only
           -- ever set once a return is actually approved, so joining on it
           -- would miss a return that's still awaiting review. Rejected AND
           -- reverted returns are excluded so neither leaves a stale
           -- "Returned" badge behind; if an earlier return on this line
           -- already completed, that's what surfaces once the pending/
           -- reverted one is filtered out.
           (SELECT r2.status FROM order_returns r2
             WHERE r2.order_product_id = op.id AND r2.deleted_at IS NULL AND r2.status NOT IN ('rejected', 'reverted')
             ORDER BY r2.requested_at DESC LIMIT 1) as return_status,
           p.name as product_name, pc.name as category_name, hg.name as hallmark_grade_name,
           ed.name as dispatched_by_employee_name, ec.name as cancelled_by_employee_name,
           (SELECT h.entered_at FROM order_stage_history h
             WHERE h.order_product_id = op.id AND h.completed_at IS NULL
             ORDER BY h.entered_at DESC LIMIT 1) as stage_entered_at
    FROM order_products op
    LEFT JOIN products p ON p.id = op.product_id AND p.deleted_at IS NULL
    LEFT JOIN product_categories pc ON pc.id = p.category_id AND pc.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 ed ON ed.id = op.dispatched_by_employee_id AND ed.deleted_at IS NULL
    LEFT JOIN employees ec ON ec.id = op.cancelled_by_employee_id AND ec.deleted_at IS NULL
    WHERE op.order_id = ? AND op.deleted_at IS NULL
    ORDER BY op.created_at ASC
  `;
  const rows = await queryWithConn(sql, [orderId], conn) as unknown as Record<string, unknown>[];
  return rows.map(rowToOrderProduct);
}

export async function findMaxOrderNo(conn?: PoolConnection): Promise<string | null> {
  const rows = await queryWithConn('SELECT order_no FROM orders WHERE deleted_at IS NULL ORDER BY order_no DESC LIMIT 1', undefined, conn) as unknown as { order_no: string }[];
  return rows[0]?.order_no ?? null;
}

export async function insert(data: Partial<Order>, id: string, createdBy: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn(
    `INSERT INTO orders (id, order_no, order_date, client_id, taken_by_employee_id, received_through,
      delivery_date, priority, received_by_client_contact, so_number, so_number_entered_at, po_number, po_date, seal_id, remarks,
      approval_status, requested_by_employee_id, approved_by_employee_id, approved_at, created_by)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      id, data.order_no, data.order_date,
      data.client_id, data.taken_by_employee_id, data.received_through,
      data.delivery_date, data.priority ?? 'Normal',
      data.received_by_client_contact ?? null, data.so_number ?? null,
      // Stamped at insert time whenever an SO number is provided up front —
      // updateOrder()/update() below handles the "added or changed later" case.
      data.so_number ? now() : null,
      data.po_number ?? null, data.po_date ?? null,
      data.seal_id ?? null, data.remarks ?? null,
      // Direct create (by an orders.approve holder) sets approval_status =
      // 'approved' with approved_by = the creator right at insert time — no
      // separate review step for them. A replayed approval-request payload
      // (via orderApproval.service.ts's approve()) passes these explicitly
      // instead — see there.
      data.approval_status ?? 'approved',
      data.requested_by_employee_id ?? null,
      data.approved_by_employee_id ?? createdBy,
      data.approved_at ?? now(),
      createdBy,
    ], conn,
  );
}

export async function update(id: string, data: Partial<Order>, updatedBy: string, conn?: PoolConnection): Promise<void> {
  const sets: string[] = [];
  const params: unknown[] = [];

  if (data.order_date !== undefined) { sets.push('order_date = ?'); params.push(data.order_date); }
  if (data.client_id !== undefined) { sets.push('client_id = ?'); params.push(data.client_id); }
  if (data.taken_by_employee_id !== undefined) { sets.push('taken_by_employee_id = ?'); params.push(data.taken_by_employee_id); }
  if (data.received_through !== undefined) { sets.push('received_through = ?'); params.push(data.received_through); }
  if (data.delivery_date !== undefined) { sets.push('delivery_date = ?'); params.push(data.delivery_date); }
  if (data.priority !== undefined) { sets.push('priority = ?'); params.push(data.priority); }
  if (data.received_by_client_contact !== undefined) { sets.push('received_by_client_contact = ?'); params.push(data.received_by_client_contact || null); }
  if (data.so_number !== undefined) { sets.push('so_number = ?'); params.push(data.so_number || null); }
  // Only set by updateOrder() in order.service.ts, and only when the SO
  // number actually changed to a non-empty value — not part of the public
  // update payload.
  if (data.so_number_entered_at !== undefined) { sets.push('so_number_entered_at = ?'); params.push(data.so_number_entered_at); }
  if (data.po_number !== undefined) { sets.push('po_number = ?'); params.push(data.po_number || null); }
  if (data.po_date !== undefined) { sets.push('po_date = ?'); params.push(data.po_date || null); }
  if (data.seal_id !== undefined) { sets.push('seal_id = ?'); params.push(data.seal_id || null); }
  if (data.remarks !== undefined) { sets.push('remarks = ?'); params.push(data.remarks || null); }
  if (data.lifecycle !== undefined) { sets.push('lifecycle = ?'); params.push(data.lifecycle); }

  if (sets.length === 0) return;
  sets.push('updated_by = ?');
  params.push(updatedBy);
  params.push(id);
  await executeWithConn(`UPDATE orders SET ${sets.join(', ')} WHERE id = ? AND deleted_at IS NULL`, params, conn);
}

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

// Used to block deleting a client/employee that's still referenced by a live
// order — soft-deleting the parent wouldn't cascade or clean these up, so
// the order would be left pointing at a "gone" client/employee.
export async function hasOrdersForClient(clientId: string, conn?: PoolConnection): Promise<boolean> {
  const rows = await queryWithConn(
    'SELECT 1 FROM orders WHERE client_id = ? AND deleted_at IS NULL LIMIT 1',
    [clientId], conn,
  ) as unknown as unknown[];
  return rows.length > 0;
}

export async function hasOrdersForEmployee(employeeId: string, conn?: PoolConnection): Promise<boolean> {
  const rows = await queryWithConn(
    'SELECT 1 FROM orders WHERE taken_by_employee_id = ? AND deleted_at IS NULL LIMIT 1',
    [employeeId], conn,
  ) as unknown as unknown[];
  return rows.length > 0;
}

export async function getStats(conn?: PoolConnection): Promise<{ pending: number; completed: number; cancelled: number }> {
  const rows = await queryWithConn(`
    SELECT
      SUM(CASE WHEN o.lifecycle = 'cancelled' THEN 1 ELSE 0 END) as cancelled,
      SUM(CASE WHEN o.lifecycle = 'active' AND (
        SELECT COUNT(*) FROM order_products op
        WHERE op.order_id = o.id AND op.deleted_at IS NULL AND op.cancelled = FALSE AND op.dispatched = FALSE
      ) = 0 AND (
        SELECT COUNT(*) FROM order_products op
        WHERE op.order_id = o.id AND op.deleted_at IS NULL AND op.cancelled = FALSE
      ) > 0 THEN 1 ELSE 0 END) as completed,
      SUM(CASE WHEN o.lifecycle = 'active' AND (
        SELECT COUNT(*) FROM order_products op
        WHERE op.order_id = o.id AND op.deleted_at IS NULL AND op.cancelled = FALSE AND op.dispatched = FALSE
      ) > 0 THEN 1 ELSE 0 END) as pending
    FROM orders o
    WHERE o.deleted_at IS NULL
  `, undefined, conn) as unknown as [{ pending: number; completed: number; cancelled: number }];
  return rows[0] ?? { pending: 0, completed: 0, cancelled: 0 };
}