/**
 * Order spine reads for the /orders surface.
 *
 * These live here rather than in queries.ts on purpose. queries.ts is ~1,500
 * lines of ledger SQL over tables Prisma models; the order spine
 * (entity_orders, entities, extracted_facts, order_feed, order_spend) is
 * written by the ingestion-engine, is NOT in prisma/schema.prisma, and never
 * will be. Mixing it in would destroy that file's "everything here is
 * Prisma-modelled" invariant. Same database, same connection, raw SQL.
 *
 * Two rules inherited from migration 010, worth restating because breaking
 * either produces a plausible wrong number rather than an error:
 *
 *   - BROWSE from order_feed. It keeps refunded/cancelled/returned rows, which
 *     belong on a history page and must never be summed.
 *   - SUM from order_spend, and only ever within a single currency. The spine
 *     holds 20 currencies including a literal '$', XLM and MANA.
 */

import { queryRaw, queryRow } from "./db";

/**
 * The spine is ONE mailbox (Siddharth's) and carries no owner column, so
 * /orders is gated by participant id rather than a row filter.
 *
 * This is an ALLOWLIST, and not because these three need protecting from each
 * other — everything here is on Siddharth's cards (145 of 145 statements) and
 * Sonu consumes the goods. It is because a participant is an ACCOUNTING
 * entity, and any participant row carrying an email is a login: auth.ts
 * matches x-forwarded-user against participants.email. Adding someone to split
 * a holiday must not silently hand them the purchase history. These three are
 * deliberate; a fourth has to be deliberate too.
 *
 * See DECISIONS.md ING-13 decision 5.
 */
export const ORDER_VIEWERS = [1, 4, 5]; // Siddharth, Sonu, Molina

export function canViewOrders(userId: number): boolean {
  return ORDER_VIEWERS.includes(userId);
}

/** Bookings, invoices and subscriptions get their own lane: they carry no
 *  goods and no delivery, so the retail columns are dead space on them. */
export type OrderLane = "retail" | "food" | "grocery" | "digital" | "transport" | "services";

export interface OrderRow {
  entity_id: number;
  entity_key: string;
  canonical_name: string | null;
  merchant_name: string | null;
  platform: string | null;
  lane: OrderLane;
  status: string;
  order_reference: string | null;
  reference_source: string | null;
  source_trust: string | null;
  ordered_at: string | null;
  eta_date: string | null;
  delivered_at: string | null;
  currency: string;
  order_total: string | null;
  refunded_amount: string | null;
  line_item_count: number;
  item_preview: string[] | null;
  /** What the classifier thought the source document was. NULL on 48% of rows
   *  (the Takeout backfill predates the interpretation index) — treat NULL as
   *  "unknown", never as "purchase". */
  content_class: string | null;
  /** COALESCE(resolved merchant, interpretation counterparty, platform slug).
   *  95% resolve to a real name; the rest honestly show the slug rather than
   *  guessing one from the sender domain — see ticket 176. */
  display_name: string;
  /** Merchant orders on a regular cadence: the subscription signal, derived
   *  not extracted. NULL unless the gaps are tight relative to their mean. */
  cadence_days: number | null;
  /** Worst sender-authentication verdict across the order's source documents.
   *  93% are 'pass' — the badge exists for the 7% that are not. Do NOT badge
   *  source_trust instead: it is 'untrusted_external' on 100% of rows, because
   *  every order here came from email. */
  auth_verdict: string | null;
  injection_flagged: boolean | null;
  /** An annual tax/donation receipt restating a year of payments already made,
   *  not a purchase (board 211). It stays on this page — the figure is the
   *  year's giving total and is worth seeing — but `order_spend` excludes it,
   *  so the amount here is deliberately NOT part of any total on the page.
   *  Badge it, or a $2,376 row reads as a single purchase. */
  summarises_period: boolean;
  txn_count: number;
  first_txn_id: number | null;
}

export interface OrderFilters {
  lane?: string;
  platforms?: string[];
  statuses?: string[];
  from?: string;
  to?: string;
  search?: string;
  currency?: string;
  has_transaction?: string;
  hide_lifecycle_only?: boolean;
  sort_by?: string;
  sort_dir?: string;
  limit?: number;
  offset?: number;
}

/**
 * Rows with no amount, no order reference and a single lifecycle event are not
 * purchases — they are a second entity minted from a mail describing an order
 * that already exists (DoorDash no-contact delivery details, shipment notices).
 * 620 of them as of 2026-08-12.
 *
 * The count moved three times that day and the history is the point:
 *   755  when this predicate was written
 *   904  after board 219 turned unstated zeros into NULLs — those 149 rows had
 *        been failing the "no amount" test on a technicality
 *   699  after board 210a retired 205 Amazon "share your experience" mails,
 *        which were never lifecycle events at all (ingest/order_noise.py)
 *   620  after board 210b joined 79 phantoms to their real parent
 *        (jobs/phantom_order_joiner.py, placebo-verified at a 1.9% error rate)
 *
 * What is left is the corpus coverage ceiling rather than a defect: a mail
 * whose parent is not in the corpus, or whose merchant is ambiguous, has
 * nothing to join to. This stays a UI workaround for those, and the toggle
 * still says so.
 *
 * This is a WORKAROUND for board 210, not a fix. The upstream defect is that a
 * lifecycle mail printing no order reference cannot join its own order, so it
 * becomes an orphan. Do not "fix" it here by deduplicating — 35 of the Amazon
 * ones are the only record their purchase happened.
 */
const LIFECYCLE_ONLY = `NOT (
  f.order_total IS NULL
  AND f.reference_source = 'message_id_fallback'
  -- TRIED AND REVERTED, 2026-08-12: exempting rows that carry line items, on
  -- the theory that naming what was bought proves a purchase. It surfaced 442
  -- rows and the very first window showed why it is wrong — the Amazon
  -- "Ordered:" mail that mints a msg- phantom carries the item list too, so
  -- Xelsluthe and YIWENTEC each rendered TWICE again, which is the exact
  -- complaint this page was fixed for. Items do not separate an orphan
  -- purchase from an item-carrying echo; only real deduplication does, and
  -- that is board 210, deliberately not attempted here.
  AND (SELECT count(*) FROM extracted_facts ef
        WHERE ef.fact_type = 'order_event'
          AND ef.payload->>'_order_entity_key' = f.entity_key) <= 1
)`;

// Never interpolate a sort column from user input.
const SORT_COLUMNS: Record<string, string> = {
  date: "f.ordered_at",
  amount: "f.order_total",
  eta: "f.eta_date",
  platform: "f.platform",
  items: "f.line_item_count",
};

function buildWhere(filters: OrderFilters) {
  const conditions: string[] = [];
  const params: unknown[] = [];
  let i = 1;

  if (filters.lane && filters.lane !== "all") {
    conditions.push(`f.lane = $${i++}`);
    params.push(filters.lane);
  }
  if (filters.platforms?.length) {
    conditions.push(`f.platform = ANY($${i++}::text[])`);
    params.push(filters.platforms);
  }
  if (filters.statuses?.length) {
    conditions.push(`f.status = ANY($${i++}::text[])`);
    params.push(filters.statuses);
  }
  if (filters.from) {
    conditions.push(`f.ordered_at >= $${i++}::date`);
    params.push(filters.from);
  }
  if (filters.to) {
    // Half-open on the upper bound: ordered_at is a timestamptz, so
    // `<= '2026-08-12'` silently drops everything after midnight that day.
    conditions.push(`f.ordered_at < ($${i++}::date + 1)`);
    params.push(filters.to);
  }
  if (filters.currency) {
    conditions.push(`f.currency = $${i++}`);
    params.push(filters.currency);
  }
  if (filters.search) {
    // One bound term, five targets. The jsonb_typeof guard is NOT optional:
    // jsonb_array_elements raises on a non-array, and details is a
    // last-write-wins merge of extractor output whose shape is not guaranteed.
    // Measured at 6,283 rows: 19 ms, sequential scan, no index warranted.
    conditions.push(`(
      f.canonical_name ILIKE $${i}
      OR m.canonical_name ILIKE $${i}
      OR f.platform ILIKE $${i}
      OR f.order_reference ILIKE $${i}
      OR EXISTS (
        SELECT 1 FROM jsonb_array_elements(
          CASE WHEN jsonb_typeof(f.details->'line_items') = 'array'
               THEN f.details->'line_items' ELSE '[]'::jsonb END) li
         WHERE li->>'description' ILIKE $${i})
    )`);
    params.push(`%${filters.search}%`);
    i++;
  }
  if (filters.hide_lifecycle_only !== false) conditions.push(LIFECYCLE_ONLY);
  if (filters.has_transaction === "yes") conditions.push(`link.txn_count > 0`);
  if (filters.has_transaction === "no") conditions.push(`link.txn_count = 0`);

  return { where: conditions.length ? `WHERE ${conditions.join(" AND ")}` : "", params, next: i };
}

/**
 * Ledger linkage — phase 2 (board 205).
 *
 * `order_transaction_links` is the source of truth: it is many-to-many, so a
 * BNPL plan is four rows against one order. `expense_metadata` remains as a
 * FALLBACK because `jobs/order_transaction_bridge.py` still writes there on
 * every hourly tick; until a bridge row is mirrored into links, dropping the
 * fallback would make freshly bridged orders read as unpaid.
 *
 * UNION, not UNION ALL, on transaction_id: after the 0030 backfill the same
 * charge is legitimately present in both stores, and counting it twice would
 * show "2 charges" on a single-payment order.
 *
 * All four readers move together — this lateral, the detail query below,
 * /api/transactions/[id]/order, and the order_ctx lateral in queries.ts.
 * Moving fewer is not a smaller change, it is an inconsistent one: once the
 * matcher inserts four links for order_ebay_14-11714-95953 that order still
 * has no expense_metadata row, so the detail page would show four instalments
 * while the list showed txn_count = 0 and the has_transaction facet put it on
 * the wrong side of both filters.
 */
const LINK_LATERAL = `
  LEFT JOIN LATERAL (
    SELECT count(*)::int AS txn_count, min(txn_id) AS first_txn_id
      FROM (
        SELECT l.transaction_id AS txn_id
          FROM order_transaction_links l
         WHERE l.entity_key = f.entity_key
        UNION
        SELECT COALESCE(em.matched_transaction_id, em.transaction_id)
          FROM expense_metadata em
         WHERE em.source = 'order-bridge'
           AND (em.source_message_id = f.entity_key
                OR em.source_message_id LIKE f.entity_key || '#f%')
           AND COALESCE(em.matched_transaction_id, em.transaction_id) IS NOT NULL
      ) both_stores
  ) link ON true`;

const FROM_CLAUSE = `
  FROM order_feed f
  LEFT JOIN entities m ON m.id = f.merchant_entity_id
  ${LINK_LATERAL}`;

export async function getOrderFeed(filters: OrderFilters) {
  const { where, params, next } = buildWhere(filters);

  const countRows = await queryRaw<{ total: number }>(
    `SELECT count(*)::int AS total ${FROM_CLAUSE} ${where}`,
    params
  );
  const total = countRows[0]?.total ?? 0;

  const sortCol = SORT_COLUMNS[filters.sort_by ?? "date"] ?? "f.ordered_at";
  const sortDir = filters.sort_dir === "asc" ? "ASC" : "DESC";
  const limit = Math.min(filters.limit ?? 50, 200);
  const offset = filters.offset ?? 0;

  const data = await queryRaw<OrderRow>(
    `SELECT f.entity_id::int   AS entity_id,
            f.entity_key, f.canonical_name, f.platform, f.lane, f.status,
            f.order_reference, f.reference_source, f.source_trust,
            f.ordered_at, f.eta_date, f.delivered_at,
            f.currency, f.order_total, f.line_item_count,
            f.content_class, f.display_name, f.cadence_days,
            f.auth_verdict, f.injection_flagged, f.summarises_period,
            m.canonical_name   AS merchant_name,
            link.txn_count, link.first_txn_id,
            (SELECT sp.refunded_amount FROM order_spend sp
              WHERE sp.entity_id = f.entity_id) AS refunded_amount,
            (SELECT jsonb_agg(t.li->>'description' ORDER BY t.n)
               FROM jsonb_array_elements(
                      CASE WHEN jsonb_typeof(f.details->'line_items') = 'array'
                           THEN f.details->'line_items' ELSE '[]'::jsonb END)
                    WITH ORDINALITY t(li, n)
              -- 12, not 3: the row shows the first few and the expansion shows
              -- the rest without a second round trip. Beyond 12 the detail page
              -- is the right surface.
              WHERE t.n <= 12) AS item_preview
     ${FROM_CLAUSE}
     ${where}
     -- NULLS LAST always: eta_date is NULL on ~65% of retail and ~95% of food,
     -- and Postgres sorts NULLs FIRST under DESC — an unguarded sort leads with
     -- thousands of blanks and reads as an empty page.
     ORDER BY ${sortCol} ${sortDir} NULLS LAST, f.entity_id DESC
     LIMIT $${next} OFFSET $${next + 1}`,
    [...params, limit, offset]
  );

  return { data, total, limit, offset };
}

export interface OrderFacets {
  /** Orders per calendar year across the WHOLE feed — deliberately not scoped
   *  to the active date range, because the strip's job is to show where the
   *  current range sits in the twenty-one years available. */
  years: { year: number; n: number }[];
  /** Corpus totals for the masthead, so a filtered count is never mistaken
   *  for the whole archive. */
  all_time: number;
  with_amount: number;
  lanes: { lane: string; n: number }[];
  platforms: { platform: string; n: number }[];
  statuses: { status: string; n: number }[];
  currencies: { currency: string; n: number }[];
}

/**
 * Facets are computed under the active date range but NOT under the active
 * lane — a lane tab showing "0" is information, a lane tab that vanished is a
 * bug report waiting to happen.
 */
export async function getOrderFacets(filters: OrderFilters): Promise<OrderFacets> {
  const base: OrderFilters = {
    from: filters.from,
    to: filters.to,
    hide_lifecycle_only: filters.hide_lifecycle_only,
  };
  const { where, params } = buildWhere(base);
  const laneScoped = buildWhere({ ...base, lane: filters.lane });

  // The year strip and the all-time totals ignore the date range on purpose.
  const spanFilters: OrderFilters = { hide_lifecycle_only: filters.hide_lifecycle_only };
  const span = buildWhere(spanFilters);

  const [years, totals, lanes, platforms, statuses, currencies] = await Promise.all([
    queryRaw<{ year: number; n: number }>(
      `SELECT extract(year from f.ordered_at)::int AS year, count(*)::int AS n
       ${FROM_CLAUSE} ${span.where}${span.where ? " AND" : " WHERE"} f.ordered_at IS NOT NULL
       GROUP BY 1 ORDER BY 1`, span.params),
    queryRaw<{ all_time: number; with_amount: number }>(
      `SELECT count(*)::int AS all_time,
              count(*) FILTER (WHERE f.order_total IS NOT NULL AND f.order_total > 0)::int AS with_amount
       ${FROM_CLAUSE} ${span.where}`, span.params),
    queryRaw<{ lane: string; n: number }>(
      `SELECT f.lane, count(*)::int AS n ${FROM_CLAUSE} ${where} GROUP BY 1 ORDER BY 2 DESC`, params),
    queryRaw<{ platform: string; n: number }>(
      `SELECT f.platform, count(*)::int AS n ${FROM_CLAUSE} ${laneScoped.where}
       GROUP BY 1 ORDER BY 2 DESC LIMIT 40`, laneScoped.params),
    queryRaw<{ status: string; n: number }>(
      `SELECT f.status, count(*)::int AS n ${FROM_CLAUSE} ${laneScoped.where}
       GROUP BY 1 ORDER BY 2 DESC`, laneScoped.params),
    queryRaw<{ currency: string; n: number }>(
      `SELECT f.currency, count(*)::int AS n ${FROM_CLAUSE} ${laneScoped.where}
       GROUP BY 1 ORDER BY 2 DESC`, laneScoped.params),
  ]);
  return {
    years,
    all_time: totals[0]?.all_time ?? 0,
    with_amount: totals[0]?.with_amount ?? 0,
    lanes, platforms, statuses, currencies,
  };
}

export interface OrderLifecycleEvent {
  fact_id: string;
  event_kind: string | null;
  effective_at: string | null;
  amount: string | null;
  currency: string | null;
  document_title: string | null;
  source_system: string | null;
}

export interface OrderSibling {
  entity_key: string;
  canonical_name: string | null;
  platform: string | null;
  order_total: string | null;
  currency: string;
  ordered_at: string | null;
  relation: "settled_by" | "settles";
}

export interface OrderLinkedTxn {
  transaction_id: number;
  transaction_date: string;
  description: string;
  amount: string;
  source_message_id: string;
  /** 'charge' | 'shipment' | 'instalment' | 'refund' | 'fee'. A plan's legs are
   *  four 'instalment' rows; a whole-order card charge is one 'charge'. */
  leg_kind: string;
  /** Human position, "2 of 4" — populated for instalments only. */
  leg_index: number | null;
  leg_count: number | null;
}

export interface OrderDetail {
  entity_key: string;
  canonical_name: string | null;
  merchant_name: string | null;
  platform: string | null;
  lane: OrderLane;
  status: string;
  direction: string;
  order_reference: string | null;
  reference_source: string | null;
  source_trust: string | null;
  ordered_at: string | null;
  eta_date: string | null;
  delivered_at: string | null;
  currency: string;
  order_total: string | null;
  gross_total: string | null;
  refunded_amount: string | null;
  tracking_url: string | null;
  tracking_carrier: string | null;
  line_items: { description?: string; quantity?: number; amount?: number }[];
  is_settled_duplicate: boolean;
  /** Board 211 — an annual receipt restating a year of payments already made.
   *  Same shape as is_settled_duplicate: browsable, excluded from spend. */
  summarises_period: boolean;
  content_class: string | null;
  display_name: string;
  cadence_days: number | null;
  order_count: number | null;
  events: OrderLifecycleEvent[];
  siblings: OrderSibling[];
  transactions: OrderLinkedTxn[];
}

export async function getOrderDetail(entityKey: string): Promise<OrderDetail | null> {
  /**
   * Read entity_orders DIRECTLY here — the one sanctioned exception to
   * "browse from order_feed". A settled rail order is excluded from the feed
   * by design, but must still be openable from the sibling link on the shop
   * order it settles. Without this, following that link 404s.
   */
  const head = await queryRow<Omit<OrderDetail, "events" | "siblings" | "transactions" | "line_items"> & {
    line_items: unknown;
  }>(
    `SELECT e.entity_key, e.canonical_name, e.source_trust,
            o.platform,
            order_lane(o.platform, p.category, e.canonical_name) AS lane,
            o.status, o.direction, o.order_reference, o.reference_source,
            o.ordered_at, o.eta_date, o.delivered_at, o.currency,
            o.order_total, o.tracking_url, o.tracking_carrier,
            m.canonical_name AS merchant_name,
            (o.settles_entity_id IS NOT NULL) AS is_settled_duplicate,
            o.summarises_period,
            COALESCE(o.details->'line_items', '[]'::jsonb) AS line_items,
            sp.order_total   AS net_total,
            sp.gross_total,
            sp.refunded_amount,
            ctx.content_class,
            COALESCE(me2.canonical_name, ctx.counterparty, o.platform) AS display_name,
            rec.cadence_days,
            -- ::int is mandatory. order_merchant_cadence.order_count is a
            -- count(*), so BIGINT, and JSON.stringify throws outright on a
            -- BigInt — "Do not know how to serialize a BigInt", a 500 with no
            -- SQL in it. It only fires on merchants that HAVE a cadence, so
            -- most orders open fine and the recurring ones die; testing one
            -- arbitrary order will not find it.
            rec.order_count::int AS order_count
       FROM entities e
       JOIN entity_orders o ON o.entity_id = e.id
       LEFT JOIN entities m ON m.id = o.merchant_entity_id
       LEFT JOIN entities me2 ON me2.id = o.merchant_entity_id
       LEFT JOIN order_platforms p ON p.slug = o.platform
       LEFT JOIN order_spend sp ON sp.entity_id = o.entity_id
       -- Cadence is keyed on the NORMALISED MERCHANT NAME, not merchant_entity_id.
       -- Migration 021 rekeyed the view (that column is NULL on ~9% of the feed
       -- and duplicate merchant entities split the same shop's history); 027 and
       -- 029 moved order_feed/order_spend onto merchant_key, and this query was
       -- missed. The result was a 42703 on EVERY order detail page — the join
       -- referenced a column the view no longer had. Keep this expression
       -- identical to the one in migration 027.
       LEFT JOIN order_merchant_cadence rec
              ON rec.merchant_key = regexp_replace(
                   lower(COALESCE(me2.canonical_name, o.platform)), '[^a-z0-9]', '', 'g')
       LEFT JOIN LATERAL (
         SELECT di.content_class, di.counterparty
           FROM extracted_facts ef
           JOIN document_interpretations di ON di.source_document_id = ef.source_document_id
          WHERE ef.fact_type = 'order_event'
            AND ef.payload->>'_order_entity_key' = e.entity_key
          ORDER BY (di.counterparty IS NULL), ef.effective_at LIMIT 1
       ) ctx ON true
      WHERE e.entity_key = $1`,
    [entityKey]
  );
  if (!head) return null;

  const events = await queryRaw<OrderLifecycleEvent>(
    // Uses the expression index on (payload->>'_order_entity_key') added in 016.
    `SELECT ef.id::text AS fact_id,
            ef.payload->>'event_kind' AS event_kind,
            ef.effective_at, ef.amount, ef.currency,
            sd.title AS document_title, sd.source_system
       FROM extracted_facts ef
       LEFT JOIN source_documents sd ON sd.id = ef.source_document_id
      WHERE ef.fact_type = 'order_event'
        AND ef.status = 'active'
        AND ef.payload->>'_order_entity_key' = $1
      ORDER BY ef.effective_at NULLS LAST, ef.id`,
    [entityKey]
  );

  const siblings = await queryRaw<OrderSibling>(
    `SELECT e2.entity_key, e2.canonical_name, o2.platform, o2.order_total,
            o2.currency, o2.ordered_at, 'settled_by'::text AS relation
       FROM entity_orders o2
       JOIN entities e2 ON e2.id = o2.entity_id
      WHERE o2.settles_entity_id = (SELECT id FROM entities WHERE entity_key = $1)
      UNION ALL
     SELECT e3.entity_key, e3.canonical_name, o3.platform, o3.order_total,
            o3.currency, o3.ordered_at, 'settles'::text AS relation
       FROM entity_orders o1
       JOIN entity_orders o3 ON o3.entity_id = o1.settles_entity_id
       JOIN entities e3 ON e3.id = o3.entity_id
      WHERE o1.entity_id = (SELECT id FROM entities WHERE entity_key = $1)`,
    [entityKey]
  );

  const transactions = await queryRaw<OrderLinkedTxn>(
    // Links first, expense_metadata as fallback, deduped on transaction_id —
    // see LINK_LATERAL above for why both stores are read. DISTINCT ON keeps
    // the LINK row when a transaction is in both, because only that row knows
    // whether the payment was one charge or leg 3 of 4.
    // The DISTINCT ON must be ordered by t.id, so the chronological sort the
    // page needs goes on the OUTER query — legs read as "1, 2, 3, 4" only if
    // they come back by date.
    `SELECT * FROM (
       SELECT DISTINCT ON (t.id)
            t.id::int AS transaction_id, t.transaction_date, t.description,
            t.amount, both_stores.source_message_id,
            both_stores.leg_kind, both_stores.leg_index, both_stores.leg_count
       FROM (
         SELECT l.transaction_id, l.leg_kind, l.leg_index::int, l.leg_count::int,
                l.entity_key AS source_message_id, 0 AS pref
           FROM order_transaction_links l
          WHERE l.entity_key = $1
         UNION ALL
         SELECT COALESCE(em.matched_transaction_id, em.transaction_id),
                'charge', NULL::int, NULL::int, em.source_message_id, 1
           FROM expense_metadata em
          WHERE em.source = 'order-bridge'
            AND (em.source_message_id = $1 OR em.source_message_id LIKE $1 || '#f%')
            AND COALESCE(em.matched_transaction_id, em.transaction_id) IS NOT NULL
       ) both_stores
       JOIN transactions t ON t.id = both_stores.transaction_id
      ORDER BY t.id, both_stores.pref
     ) deduped
     ORDER BY transaction_date, leg_index NULLS FIRST`,
    [entityKey]
  );

  const raw = head as unknown as Record<string, unknown>;
  return {
    ...(head as unknown as OrderDetail),
    line_items:
      typeof raw.line_items === "string"
        ? JSON.parse(raw.line_items as string)
        : ((raw.line_items as OrderDetail["line_items"]) ?? []),
    events,
    siblings,
    transactions,
  };
}
