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

export interface SearchResult {
  order_id: string;
  order_no: string;
  match_type: 'order_no' | 'so_number' | 'jo_no' | 'client';
  match_value: string;
  order_product_id?: string;
}

export interface DeliveredLineSearchResult {
  order_id: string;
  order_no: string;
  so_number?: string;
  client_name: string;
  order_product_id: string;
  jo_no?: string;
  product_name: string;
  size?: string;
  dispatched_quantity: number;
  already_returned_quantity: number;
  unit_weight: number;
}

// A single LIKE-based lookup across the fields users actually search by
// (order no, SO no, JO no, client name) — no full-text index needed at this
// scale. Each row already carries enough to know which field matched, so
// the caller (search.service.ts) doesn't have to re-derive it.
//
// relevantStageIds narrows results for a stage-restricted employee (see
// search.service.ts) — null means unrestricted, same convention used
// everywhere else this pattern appears. A line's *real* current stage is
// read from its open order_stage_history row (completed_at IS NULL), the
// same reliable, identity-based source the Stage Idle card uses — never
// the completed_stages position counter, which can drift from what a
// stage's name actually is after a stage reorder (see dashboard.repo.ts's
// getStageBreakdown for the full story on why that matters).
export async function search(term: string, limit = 10, relevantStageIds: string[] | null = null): Promise<SearchResult[]> {
  const like = `%${term}%`;

  // A stage-restricted match is either: the specific product line matched
  // by JO number is currently in one of their stages, or — for an order
  // no/SO no/client match, which isn't tied to one specific line — the
  // order has *some* still-pending line currently in one of their stages.
  let stageFilterSql = '';
  const stageParams: string[] = [];
  if (relevantStageIds && relevantStageIds.length > 0) {
    const placeholders = relevantStageIds.map(() => '?').join(', ');
    stageFilterSql = `
      AND (
        (op.id IS NOT NULL AND COALESCE(h.stage_id, 'not_started') IN (${placeholders}))
        OR EXISTS (
          SELECT 1 FROM order_products op2
          LEFT JOIN order_stage_history h2 ON h2.order_product_id = op2.id AND h2.completed_at IS NULL
          WHERE op2.order_id = o.id AND op2.deleted_at IS NULL AND op2.cancelled = FALSE AND op2.dispatched = FALSE
            AND COALESCE(h2.stage_id, 'not_started') IN (${placeholders})
        )
      )`;
    stageParams.push(...relevantStageIds, ...relevantStageIds);
  }

  const rows = await query(
    `SELECT o.id as order_id, o.order_no as order_no, o.so_number as so_number,
            op.id as order_product_id, op.jo_no as jo_no,
            c.name as client_name
     FROM orders o
     LEFT JOIN order_products op ON op.order_id = o.id AND op.deleted_at IS NULL AND op.jo_no LIKE ?
     LEFT JOIN order_stage_history h ON h.order_product_id = op.id AND h.completed_at IS NULL
     LEFT JOIN clients c ON c.id = o.client_id AND c.deleted_at IS NULL
     WHERE o.deleted_at IS NULL AND (
       o.order_no LIKE ? OR o.so_number LIKE ? OR op.jo_no LIKE ? OR c.name LIKE ?
     )
     ${stageFilterSql}
     ORDER BY o.created_at DESC
     LIMIT ?`,
    [like, like, like, like, like, ...stageParams, limit],
  ) as unknown as Record<string, unknown>[];

  const lowerTerm = term.toLowerCase();
  return rows.map((r) => {
    const orderNo = r.order_no as string;
    const soNumber = (r.so_number as string) ?? '';
    const joNo = (r.jo_no as string) ?? '';
    const clientName = (r.client_name as string) ?? '';

    let matchType: SearchResult['match_type'] = 'client';
    let matchValue = clientName;
    if (joNo.toLowerCase().includes(lowerTerm)) {
      matchType = 'jo_no';
      matchValue = joNo;
    } else if (orderNo.toLowerCase().includes(lowerTerm)) {
      matchType = 'order_no';
      matchValue = orderNo;
    } else if (soNumber.toLowerCase().includes(lowerTerm)) {
      matchType = 'so_number';
      matchValue = soNumber;
    }

    return {
      order_id: r.order_id as string,
      order_no: orderNo,
      match_type: matchType,
      match_value: matchValue,
      order_product_id: matchType === 'jo_no' ? (r.order_product_id as string) : undefined,
    };
  });
}

// Powers the Returned Orders request search — scoped to lines that are
// actually returnable: some quantity currently delivered (dispatched_quantity
// > 0) and not cancelled. Deliberately checks the quantity counter, not the
// `dispatched` boolean — a line with one return already approved has
// `dispatched` flipped back to false even though part of it may still be
// legitimately out with the customer and returnable again. Distinct from
// search() above (which matches order-level fields against every order
// line) since a return is always raised against one specific delivered
// line, never a whole order.
export async function searchDeliveredLines(term: string, limit = 10): Promise<DeliveredLineSearchResult[]> {
  const like = `%${term}%`;

  const rows = await query(
    `SELECT o.id as order_id, o.order_no as order_no, o.so_number as so_number, c.name as client_name,
            op.id as order_product_id, op.jo_no as jo_no, p.name as product_name, op.size as size,
            op.dispatched_quantity as dispatched_quantity, op.weight as weight, op.dispatched_weight as dispatched_weight,
            -- Already-claimed quantity across every still-live return
            -- against this same line, so the frontend can show the
            -- remaining-returnable amount before the requester submits.
            -- Excludes 'rejected' (never applied) AND 'reverted' (applied,
            -- then undone — see orderReturn.service.ts's revert()) so a
            -- reverted return's quantity becomes returnable again here,
            -- same as sumOpenReturnedQtyForLine() in orderReturn.repo.ts.
            (SELECT COALESCE(SUM(r.returned_quantity), 0) FROM order_returns r
              WHERE r.order_product_id = op.id AND r.deleted_at IS NULL AND r.status NOT IN ('rejected', 'reverted')) as already_returned_qty
     FROM order_products op
     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
     WHERE op.deleted_at IS NULL AND op.dispatched_quantity > 0 AND op.cancelled = FALSE
       AND (o.order_no LIKE ? OR o.so_number LIKE ? OR op.jo_no LIKE ? OR c.name LIKE ?)
     ORDER BY o.created_at DESC
     LIMIT ?`,
    [like, like, like, like, limit],
  ) as unknown as Record<string, unknown>[];

  return rows.map((r) => ({
    order_id: r.order_id as string,
    order_no: r.order_no as string,
    so_number: (r.so_number as string) ?? undefined,
    client_name: (r.client_name as string) ?? 'Unknown customer',
    order_product_id: r.order_product_id as string,
    jo_no: (r.jo_no as string) ?? undefined,
    product_name: (r.product_name as string) ?? 'Unknown product',
    size: (r.size as string) ?? undefined,
    dispatched_quantity: (r.dispatched_quantity as number) ?? 0,
    already_returned_quantity: Number(r.already_returned_qty ?? 0),
    // Prefer the average per-unit weight actually delivered (dispatched
    // weight can differ slightly from quantity * op.weight if a partial
    // dispatch recorded a custom weight); fall back to the line's recorded
    // per-unit weight when nothing has been dispatched yet (shouldn't
    // happen here since this query is already scoped to dispatched_quantity
    // > 0, but keeps the value sane rather than NaN/Infinity).
    unit_weight: Number(r.dispatched_quantity) > 0
      ? Number(r.dispatched_weight ?? 0) / Number(r.dispatched_quantity)
      : Number(r.weight ?? 0),
  }));
}