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

const SELECT_WITH_JOINS = `
  SELECT r.id, r.order_id, r.order_product_id, r.returned_quantity, r.returned_weight, r.reason,
         r.target_stage_id, r.status, r.requested_by_employee_id, r.requested_at,
         r.approved_by_employee_id, r.approved_at, r.rejected_by_employee_id, r.rejected_at, r.rejection_reason,
         r.returned_date, r.next_delivery_date, r.created_at, r.updated_at,
         o.order_no as order_no, o.so_number as so_number,
         op.jo_no as jo_no, p.name as product_name,
         c.name as client_name,
         s.name as target_stage_name,
         req.name as requested_by_employee_name, apr.name as approved_by_employee_name, rej.name as rejected_by_employee_name
  FROM order_returns r
  JOIN orders o ON o.id = r.order_id
  JOIN order_products op ON op.id = r.order_product_id
  LEFT JOIN products p ON p.id = op.product_id AND p.deleted_at IS NULL
  LEFT JOIN clients c ON c.id = o.client_id AND c.deleted_at IS NULL
  LEFT JOIN stages s ON s.id = r.target_stage_id
  LEFT JOIN employees req ON req.id = r.requested_by_employee_id AND req.deleted_at IS NULL
  LEFT JOIN employees apr ON apr.id = r.approved_by_employee_id AND apr.deleted_at IS NULL
  LEFT JOIN employees rej ON rej.id = r.rejected_by_employee_id AND rej.deleted_at IS NULL
`;

function rowToReturn(row: Record<string, unknown>): OrderReturn {
  return {
    id: row.id as string,
    order_id: row.order_id as string,
    order_product_id: row.order_product_id as string,
    returned_quantity: row.returned_quantity as number,
    returned_weight: Number(row.returned_weight),
    reason: row.reason as string,
    target_stage_id: row.target_stage_id as string,
    status: row.status as OrderReturn['status'],
    requested_by_employee_id: row.requested_by_employee_id as string,
    requested_at: row.requested_at instanceof Date ? row.requested_at.toISOString() : undefined,
    approved_by_employee_id: (row.approved_by_employee_id as string) ?? undefined,
    approved_at: row.approved_at instanceof Date ? row.approved_at.toISOString() : undefined,
    rejected_by_employee_id: (row.rejected_by_employee_id as string) ?? undefined,
    rejected_at: row.rejected_at instanceof Date ? row.rejected_at.toISOString() : undefined,
    rejection_reason: (row.rejection_reason as string) ?? undefined,
    returned_date: row.returned_date instanceof Date ? row.returned_date.toISOString() : (row.returned_date as string),
    next_delivery_date: (row.next_delivery_date 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,
    order_no: (row.order_no as string) ?? undefined,
    jo_no: (row.jo_no as string) ?? undefined,
    so_number: (row.so_number as string) ?? undefined,
    client_name: (row.client_name as string) ?? undefined,
    product_name: (row.product_name as string) ?? undefined,
    target_stage_name: (row.target_stage_name as string) ?? undefined,
    requested_by_employee_name: (row.requested_by_employee_name as string) ?? undefined,
    approved_by_employee_name: (row.approved_by_employee_name as string) ?? undefined,
    rejected_by_employee_name: (row.rejected_by_employee_name as string) ?? undefined,
  };
}

export interface InsertOrderReturnData {
  order_id: string;
  order_product_id: string;
  returned_quantity: number;
  returned_weight: number;
  reason: string;
  target_stage_id: string;
  status: 'pending' | 'active';
  requested_by_employee_id: string;
  returned_date: string;
  next_delivery_date?: string | null;
  // Only set when inserted directly as 'active' (Admin direct-create).
  approved_by_employee_id?: string | null;
  approved_at?: string | null;
}

export async function insert(
  id: string,
  data: InsertOrderReturnData,
  createdBy: string,
  conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    `INSERT INTO order_returns
      (id, order_id, order_product_id, returned_quantity, returned_weight, reason, target_stage_id, status,
       requested_by_employee_id, returned_date, next_delivery_date,
       approved_by_employee_id, approved_at, created_by)
     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
    [
      id, data.order_id, data.order_product_id, data.returned_quantity, data.returned_weight, data.reason,
      data.target_stage_id, data.status, data.requested_by_employee_id,
      // mysql2 rejects a raw ISO string for a DATETIME column — it only
      // auto-formats a JS Date object correctly (same convention as
      // markDispatched()/markCancelled() elsewhere in this codebase).
      new Date(data.returned_date),
      data.next_delivery_date ?? null, data.approved_by_employee_id ?? null, data.approved_at ?? null,
      createdBy,
    ],
    conn,
  );
}

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

// Locks the row for the approval transaction — re-validated under this lock
// so two concurrent approvals against the same original line can't both
// pass the "remaining returnable quantity" check (see
// sumOpenReturnedQtyForLine below).
export async function findByIdForUpdate(id: string, conn: PoolConnection): Promise<OrderReturn | null> {
  const rows = await queryWithConn(
    `SELECT id, order_id, order_product_id, returned_quantity, returned_weight, reason, target_stage_id, status,
            requested_by_employee_id, requested_at, approved_by_employee_id, approved_at,
            rejected_by_employee_id, rejected_at, rejection_reason, returned_date, next_delivery_date,
            created_at, updated_at
     FROM order_returns WHERE id = ? AND deleted_at IS NULL FOR UPDATE`,
    [id], conn,
  ) as unknown as Record<string, unknown>[];
  return rows.length ? rowToReturn(rows[0]!) : null;
}

export interface FindReturnsFilters {
  status?: string;
  employeeId?: string;
  dateFrom?: string;
  dateTo?: string;
}

export async function findPending(filters: FindReturnsFilters = {}, conn?: PoolConnection): Promise<OrderReturn[]> {
  return findAll({ ...filters, status: 'pending' }, conn);
}

export async function findAll(filters: FindReturnsFilters = {}, conn?: PoolConnection): Promise<OrderReturn[]> {
  const conditions = ['r.deleted_at IS NULL'];
  const params: unknown[] = [];

  if (filters.status) { conditions.push('r.status = ?'); params.push(filters.status); }
  if (filters.employeeId) { conditions.push('r.requested_by_employee_id = ?'); params.push(filters.employeeId); }
  if (filters.dateFrom) { conditions.push('r.returned_date >= ?'); params.push(filters.dateFrom); }
  if (filters.dateTo) { conditions.push('r.returned_date <= ?'); params.push(filters.dateTo); }

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

// "Returned Items" KPI: open (not yet completed/rejected) returns.
export async function findOpenCountForKpi(conn?: PoolConnection): Promise<number> {
  const rows = await queryWithConn(
    `SELECT COUNT(*) as cnt FROM order_returns WHERE deleted_at IS NULL AND status IN ('pending', 'active')`,
    undefined, conn,
  ) as unknown as { cnt: number }[];
  return Number(rows[0]?.cnt ?? 0);
}

export async function findOpen(conn?: PoolConnection): Promise<OrderReturn[]> {
  const rows = await queryWithConn(
    `${SELECT_WITH_JOINS} WHERE r.deleted_at IS NULL AND r.status IN ('pending', 'active') ORDER BY r.requested_at DESC`,
    undefined, conn,
  ) as unknown as Record<string, unknown>[];
  return rows.map(rowToReturn);
}

// Double-booking guard: total returned_quantity already claimed by every
// non-rejected return (pending/active/completed) against the same original
// line — request-time and approval-time both validate the new/edited amount
// doesn't push this past the line's dispatched_quantity.
export async function sumOpenReturnedQtyForLine(orderProductId: string, conn?: PoolConnection): Promise<number> {
  const rows = await queryWithConn(
    `SELECT COALESCE(SUM(returned_quantity), 0) as total FROM order_returns
     WHERE order_product_id = ? AND deleted_at IS NULL AND status NOT IN ('rejected', 'reverted')`,
    [orderProductId], conn,
  ) as unknown as { total: number }[];
  return Number(rows[0]?.total ?? 0);
}

export async function markActive(
  id: string,
  approvedByEmployeeId: string,
  conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    `UPDATE order_returns
     SET status = 'active', approved_by_employee_id = ?, approved_at = NOW(), updated_by = ?
     WHERE id = ? AND deleted_at IS NULL`,
    [approvedByEmployeeId, approvedByEmployeeId, id], conn,
  );
}

export async function markRejected(
  id: string,
  rejectedByEmployeeId: string,
  reason: string,
  conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    `UPDATE order_returns
     SET status = 'rejected', rejected_by_employee_id = ?, rejected_at = NOW(), rejection_reason = ?, updated_by = ?
     WHERE id = ? AND deleted_at IS NULL`,
    [rejectedByEmployeeId, reason, rejectedByEmployeeId, id], conn,
  );
}

export async function markCompleted(id: string, updatedBy: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn(
    `UPDATE order_returns SET status = 'completed', updated_by = ? WHERE id = ? AND status = 'active' AND deleted_at IS NULL`,
    [updatedBy, id], conn,
  );
}

// Reverts a return from 'completed' back to 'active' — mirrors
// order.service.ts's own dispatch-revert flow (undoing/editing a dispatch
// entry can walk a line back from fully-dispatched to partially-dispatched).
export async function revertToActive(id: string, updatedBy: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn(
    `UPDATE order_returns SET status = 'active', updated_by = ? WHERE id = ? AND status = 'completed' AND deleted_at IS NULL`,
    [updatedBy, id], conn,
  );
}

// Undoes an 'active' (approved, not yet redelivered) return entirely — see
// orderReturn.service.ts's revert(), which is the RBAC-gated action this
// backs. Not to be confused with revertToActive() above, which is a much
// narrower internal bookkeeping step (completed -> active) triggered
// automatically by an edited/reverted dispatch-history entry.
export async function markReverted(id: string, revertedByEmployeeId: string, conn?: PoolConnection): Promise<void> {
  await executeWithConn(
    `UPDATE order_returns SET status = 'reverted', updated_by = ? WHERE id = ? AND status = 'active' AND deleted_at IS NULL`,
    [revertedByEmployeeId, id], conn,
  );
}

// Editable only while a return is still 'pending' (see orderReturn.service.ts's
// updateRequest()) — the requester's own submitted details, not the full
// order. order_id/order_product_id (which line it targets) are deliberately
// not editable here; if that's wrong, the requester rejects... actually
// there's no self-cancel yet, so for now they resubmit under a fresh
// request if the wrong line was picked.
export async function update(
  id: string,
  data: {
    returned_quantity: number; returned_weight: number; reason: string; target_stage_id: string;
    returned_date: string; next_delivery_date?: string | null;
  },
  updatedBy: string,
  conn?: PoolConnection,
): Promise<void> {
  await executeWithConn(
    `UPDATE order_returns
     SET returned_quantity = ?, returned_weight = ?, reason = ?, target_stage_id = ?, returned_date = ?, next_delivery_date = ?, updated_by = ?
     WHERE id = ? AND status = 'pending' AND deleted_at IS NULL`,
    [
      data.returned_quantity, data.returned_weight, data.reason, data.target_stage_id,
      new Date(data.returned_date), data.next_delivery_date ?? null, updatedBy, id,
    ],
    conn,
  );
}