import { queryRaw } from "./db";
import { EXCLUDE_RECONCILED_SOURCE, NATIVE_CURRENCY, AMOUNT_UNCONVERTED, ACTIVE_OBLIGATION, STATEMENTS_JOIN, OWNER_SCOPE, NET_SPEND_ROWS, SPEND_SIGNED } from "./analytics-sql";

export interface RoutePointRow {
  label: string;
  time: string | null;
  address: string;
}

export interface TagRow {
  id: number;
  name: string;
  color: string;
}

export interface TransactionRow {
  id: number;
  statement_id: number | null;
  transaction_date: string;
  description: string;
  amount: number;
  amount_aud: number | null;
  transaction_type: string;
  merchant_name: string | null;
  merchant_normalized: string | null;
  location: string | null;
  foreign_currency_amount: number | null;
  foreign_currency_code: string | null;
  category: string;
  row_index: number;
  created_at: string;
  // loan repayment split — set only when the lender itemises it (migration 0014)
  principal_amount: number | null;
  interest_amount: number | null;
  // How it was paid (migration 0016). NULL = unknown, treated as reconcilable.
  // 'cash' and 'credits' are excluded from reconciliation — see needsCardMatch().
  payment_method: string | null;
  /** Uber pick-up/drop-off, when this row came from an order receipt. */
  order_route: RoutePointRow[] | null;
  /** Receipt platform ONLY — it gates the disclosure arrow, so it must stay
   *  true to "there is a receipt behind this row". */
  order_platform: "doordash" | "ubereats" | "uber" | null;
  /** Phase 2 (board 205) — set when this transaction is linked to an order.
   *  'instalment' means it is one leg of a plan; leg_index/leg_count carry
   *  "2 of 4", which is what stops one purchase reading as four. */
  order_leg_kind: string | null;
  order_leg_index: number | null;
  order_leg_count: number | null;
  order_name: string | null;
  // override fields
  category_override: string | null;
  merchant_override: string | null;
  notes: string | null;
  my_share_percent: number | null;
  effective_category: string;
  effective_merchant: string;
  // My share of this transaction, resolved server-side so the UI matches analytics.
  my_share_pct: number;
  my_amount: number;
  // statement context (null for manual transactions)
  bank_name: string;
  // The currency `amount` is denominated in; `amount_aud` is the converted
  // figure where one exists. Usually the statement's currency, but an
  // order-receipt row has no statement and carries its own — see
  // NATIVE_CURRENCY. Not simply 'AUD' for every statement-less row.
  currency: string;
  /** True when `amount` is non-AUD and no converted figure exists. */
  amount_unconverted: boolean;
  owner_id: number;
  owner_name: string;
  // tags
  tags: TagRow[];
  // splits
  splits: { participant_id: number; name: string; share_percent: number; settled: boolean }[];
  // trip
  trip_id: number | null;
  trip_name: string | null;
  trip_color: string | null;
}

export interface StatementRow {
  id: number;
  bank_name: string;
  card_name: string | null;
  account_number: string;
  account_type: string | null;
  account_holder_name: string | null;
  billing_start_date: string | null;
  billing_end_date: string | null;
  total_amount_due: number;
  minimum_amount_due: number | null;
  payment_due_date: string;
  opening_balance: number | null;
  closing_balance: number | null;
  total_credits: number | null;
  total_debits: number | null;
  interest_charged: number | null;
  fees_charged: number | null;
  credit_limit: number | null;
  currency: string;
  statement_type: string | null;
  // Loan statements only (see migration 0014)
  interest_rate: number | null;
  scheduled_repayment: number | null;
  repayment_frequency: string | null;
  redraw_available: number | null;
  loan_term_months: number | null;
  tier_used: string | null;
  owner_id: number;
  owner_name: string;
  created_at: string;
  transaction_count: number;
  // Balance assertion (see BALANCE_DELTA). Null when the statement has no
  // opening/closing balance to check against.
  expected_closing: number | null;
  balance_diff: number | null;
  /**
   * Other statements for this account billing the same days — see
   * STATEMENT_OVERLAPS. Non-empty means some of these transactions are almost
   * certainly imported twice. Empty array, never null.
   */
  overlaps: { id: number; days: number }[];
}

interface TransactionFilters {
  from?: string;
  to?: string;
  categories?: string[];
  /**
   * Categories to hide. Opt-in per caller and never defaulted here — the rules
   * preview and the bulk rule-apply path both go through getTransactions, and a
   * default exclusion would silently shrink what a rule can see and reach. The
   * transactions view sets this; nothing else does.
   */
  exclude_categories?: string[];
  bank_names?: string[];
  tag_ids?: string[];
  transaction_types?: string[];
  search?: string;
  statement_id?: string;
  sort_by?: string;
  sort_dir?: string;
  limit?: number;
  offset?: number;
  amount_min?: number;
  amount_max?: number;
  has_split?: string;
  trip_id?: string;
  /**
   * With a `trip_id` set, show every row on the trip rather than only the
   * viewer's own. A trip is all the expenses on one trip, so a participant sees
   * the whole thing — the trip total already counts every payer.
   *
   * Opt-in, and deliberately NOT implied by `trip_id` being present, because
   * `GET /api/transactions` is also the main transactions list and its trip
   * filter must keep owner scoping — otherwise filtering your own ledger by
   * "Europe 2026" would quietly fill it with someone else's rows and skew every
   * total on the page. Only the trip detail view sets this.
   */
  trip_all_rows?: boolean;
}

/**
 * `myShare` from analytics-sql.ts, in subselect form.
 *
 * Same resolution and the same ownership branch, spelled out inline because
 * this query has no `transaction_splits ts` join scoped to the viewer — it
 * already aggregates every participant's splits for display, and adding a
 * second viewer-scoped join purely to reuse the fragment would be the more
 * confusing of the two. Kept adjacent to its twin so a change to one is an
 * obvious prompt to change the other; the mirror is covered by a test that
 * asserts the two agree row for row.
 */
export const MY_SHARE_PCT = `CASE
  WHEN COALESCE(t.owner_id, s.owner_id) = $1 THEN COALESCE(
    (SELECT x.share_percent FROM transaction_splits x
      WHERE x.transaction_id = t.id AND x.participant_id = $1),
    o.my_share_percent,
    100 - COALESCE((SELECT SUM(x.share_percent) FROM transaction_splits x
                     WHERE x.transaction_id = t.id AND x.participant_id <> $1), 0)
  )
  ELSE COALESCE(
    (SELECT x.share_percent FROM transaction_splits x
      WHERE x.transaction_id = t.id AND x.participant_id = $1), 0)
END`;

export async function getTransactions(ownerId: number, filters: TransactionFilters) {
  // A real trip id, not "unassigned" — there is no trip to participate in for
  // rows that belong to none, so the owner scoping has to stand there.
  const tripAllRows = Boolean(
    filters.trip_all_rows && filters.trip_id && filters.trip_id !== "unassigned"
  );

  const conditions: string[] = [EXCLUDE_RECONCILED_SOURCE];
  if (!tripAllRows) {
    conditions.push(
      `(COALESCE(t.owner_id, s.owner_id) = $1 OR EXISTS (SELECT 1 FROM transaction_splits ts_me WHERE ts_me.transaction_id = t.id AND ts_me.participant_id = $1))`
    );
  }
  const params: unknown[] = [ownerId];
  let paramIdx = 2;

  if (filters.from) {
    conditions.push(`t.transaction_date >= $${paramIdx++}`);
    params.push(filters.from);
  }
  if (filters.to) {
    conditions.push(`t.transaction_date <= $${paramIdx++}`);
    params.push(filters.to);
  }
  if (filters.categories?.length) {
    conditions.push(`COALESCE(o.category_override, t.category) = ANY($${paramIdx++}::text[])`);
    params.push(filters.categories);
  }
  if (filters.exclude_categories?.length) {
    // Picking a category explicitly beats hiding it. Without this, selecting
    // "Transfers" while the hide-transfers default is on returns zero rows and
    // reads as "you have no transfers".
    const hidden = filters.exclude_categories.filter((c) => !filters.categories?.includes(c));
    if (hidden.length) {
      // COALESCE to '' rather than leaving it NULL: `NULL <> ALL(...)` is NULL,
      // not true, so an uncategorised row would be filtered out by a hide rule
      // that never named it. Same trap EXCLUDE_NON_SPEND documents.
      conditions.push(`COALESCE(o.category_override, t.category, '') <> ALL($${paramIdx++}::text[])`);
      params.push(hidden);
    }
  }
  if (filters.bank_names?.length) {
    // "Manual" and "Gift Card" are not banks — they are the two shapes a
    // statement-less row can take, and bankLabel() decides which. The filter
    // has to split on the same condition or the chip selects nothing.
    const hasManual = filters.bank_names.includes("Manual");
    const hasGiftCard = filters.bank_names.includes("Gift Card");
    const bankList = filters.bank_names.filter((b) => b !== "Manual" && b !== "Gift Card");
    const alternatives: string[] = [];
    if (hasManual) {
      alternatives.push(`(t.statement_id IS NULL AND t.payment_method IS DISTINCT FROM 'credits')`);
    }
    if (hasGiftCard) {
      alternatives.push(`(t.statement_id IS NULL AND t.payment_method = 'credits')`);
    }
    if (bankList.length > 0) {
      alternatives.push(`s.bank_name = ANY($${paramIdx++}::text[])`);
      params.push(bankList);
    }
    conditions.push(`(${alternatives.join(" OR ")})`);
  }
  if (filters.tag_ids?.length) {
    const noTags = filters.tag_ids.includes("untagged");
    const realTagIds = filters.tag_ids.filter((id) => id !== "untagged").map(Number);
    if (noTags) {
      conditions.push(`NOT EXISTS (SELECT 1 FROM transaction_tags tt2 WHERE tt2.transaction_id = t.id)`);
    } else if (realTagIds.length > 0) {
      conditions.push(`EXISTS (SELECT 1 FROM transaction_tags tt2 WHERE tt2.transaction_id = t.id AND tt2.tag_id = ANY($${paramIdx++}::int[]))`);
      params.push(realTagIds);
    }
  }
  if (filters.transaction_types?.length) {
    conditions.push(`t.transaction_type = ANY($${paramIdx++}::text[])`);
    params.push(filters.transaction_types);
  }
  if (filters.search) {
    conditions.push(`(t.description ILIKE $${paramIdx} OR t.merchant_name ILIKE $${paramIdx} OR COALESCE(o.merchant_normalized, t.merchant_normalized) ILIKE $${paramIdx})`);
    params.push(`%${filters.search}%`);
    paramIdx++;
  }
  if (filters.statement_id) {
    conditions.push(`t.statement_id = $${paramIdx++}`);
    params.push(Number(filters.statement_id));
  }
  if (filters.amount_min !== undefined) {
    conditions.push(`t.amount >= $${paramIdx++}`);
    params.push(filters.amount_min);
  }
  if (filters.amount_max !== undefined) {
    conditions.push(`t.amount <= $${paramIdx++}`);
    params.push(filters.amount_max);
  }
  if (filters.has_split === "yes") {
    conditions.push(`EXISTS (SELECT 1 FROM transaction_splits ts_f WHERE ts_f.transaction_id = t.id)`);
  } else if (filters.has_split === "no") {
    conditions.push(`NOT EXISTS (SELECT 1 FROM transaction_splits ts_f WHERE ts_f.transaction_id = t.id)`);
  }
  if (filters.trip_id === "unassigned") {
    conditions.push(`o.trip_id IS NULL`);
  } else if (filters.trip_id) {
    const tripParam = paramIdx++;
    conditions.push(`o.trip_id = $${tripParam}`);
    params.push(Number(filters.trip_id));
    if (tripAllRows) {
      // The gate for having dropped the owner filter above. Enforced in SQL
      // rather than trusted from the route, so passing trip_all_rows with a
      // trip you are not on returns nothing instead of everything.
      conditions.push(TRIP_PARTICIPANT(`$${tripParam}`, `$1`));
    }
  }

  const where = `WHERE ${conditions.join(" AND ")}`;

  const sortCol = filters.sort_by === "amount" ? "t.amount" : filters.sort_by === "created_at" ? "t.created_at" : "t.transaction_date";
  const sortDir = filters.sort_dir === "asc" ? "ASC" : "DESC";
  const limit = filters.limit || 50;
  const offset = filters.offset || 0;

  const countSql = `
    SELECT COUNT(*)::int as total
    FROM transactions t
    LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
    LEFT JOIN statements s ON s.id = t.statement_id
    ${where}
  `;
  const countResult = await queryRaw<{ total: number }>(countSql, params);
  const total = countResult[0]?.total || 0;

  const dataSql = `
    SELECT t.*,
      o.category_override, o.merchant_normalized as merchant_override, o.notes, o.my_share_percent,
      COALESCE(o.category_override, t.category) as effective_category,
      COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name) as effective_merchant,
      ${bankLabel()} as bank_name,
      ${NATIVE_CURRENCY} as currency,
      ${AMOUNT_UNCONVERTED} as amount_unconverted,
      -- My share, resolved the same way analytics does it (see myShare in
      -- analytics-sql.ts): on a row I paid for, explicit split row, then
      -- override, then whatever is left after everyone else; on a row someone
      -- else paid for, only an explicit split row, absent meaning 0. Computed
      -- here so the UI cannot drift from the totals it is drilling into — this
      -- list shows rows I merely hold a split on, and before MY_SPEND_SCOPE
      -- those rows read as a share of a total that excluded them entirely.
      ${MY_SHARE_PCT}::numeric(5,2) as my_share_pct,
      (COALESCE(t.amount_aud, t.amount) * ${MY_SHARE_PCT} / 100)::numeric(12,2) as my_amount,
      COALESCE(t.owner_id, s.owner_id) as owner_id,
      p.name as owner_name,
      COALESCE(src.created_at, t.created_at) as created_at,
      o.trip_id,
      tr.name as trip_name,
      tr.color as trip_color,
      txn_tags.tags,
      txn_splits.splits,
      order_ctx.route as order_route,
      -- Deliberately NOT COALESCEd with the link's platform. order_platform
      -- gates the receipt disclosure arrow on /transactions, and a BNPL leg
      -- has no receipt behind it — filling this in put an arrow on four
      -- Afterpay rows that expand to nothing, which is the exact promise the
      -- arrow exists to avoid making. The leg fields below carry the sub-line
      -- instead.
      order_ctx.platform as order_platform,
      order_link.leg_kind as order_leg_kind,
      order_link.leg_index as order_leg_index,
      order_link.leg_count as order_leg_count,
      order_link.canonical_name as order_name
    FROM transactions t
    LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
    LEFT JOIN statements s ON s.id = t.statement_id
    -- Order provenance, for the sub-line under the description. Five rows all
    -- reading "Order - Uber Trip" are indistinguishable; where they went is the
    -- only thing that tells them apart, and it was already stored.
    -- Both directions, because a card-settled order has no transaction of its
    -- own and points at the statement line instead (I5).
    -- Phase 2 (board 205): a BNPL leg has no expense_metadata row of its own,
    -- so COALESCE in the link's platform. Without it the four Afterpay debits
    -- behind the A$1,599 drone keep an empty sub-line while their order sits
    -- one join away. The route column only ever exists on the receipt side.
    -- (No backticks in here: this SQL lives in a TS template literal and a
    -- backtick ends the string — TS1005 on a line that looks like a comment.)
    LEFT JOIN LATERAL (
      SELECT em.route, em.platform
        FROM expense_metadata em
       WHERE em.transaction_id = t.id OR em.matched_transaction_id = t.id
       LIMIT 1
    ) order_ctx ON true
    LEFT JOIN LATERAL (
      SELECT o.platform, l.leg_kind, l.leg_index::int AS leg_index,
             l.leg_count::int AS leg_count, e.canonical_name
        FROM order_transaction_links l
        LEFT JOIN entities e      ON e.entity_key = l.entity_key
        LEFT JOIN entity_orders o ON o.entity_id = e.id
       WHERE l.transaction_id = t.id
       LIMIT 1
    ) order_link ON true
    LEFT JOIN participants p ON p.id = COALESCE(t.owner_id, s.owner_id)
    LEFT JOIN transactions src ON src.reconciled_with_id = t.id AND src.statement_id IS NULL
    LEFT JOIN trips tr ON tr.id = o.trip_id
    LEFT JOIN LATERAL (
      SELECT COALESCE(json_agg(json_build_object('id', tg.id, 'name', tg.name, 'color', tg.color) ORDER BY tg.name), '[]'::json) as tags
      FROM transaction_tags tt
      JOIN tags tg ON tg.id = tt.tag_id
      WHERE tt.transaction_id = t.id
    ) txn_tags ON true
    LEFT JOIN LATERAL (
      SELECT COALESCE(json_agg(json_build_object('participant_id', ts.participant_id, 'name', sp.name, 'share_percent', ts.share_percent, 'settled', ts.settled) ORDER BY sp.name), '[]'::json) as splits
      FROM transaction_splits ts
      JOIN participants sp ON sp.id = ts.participant_id
      WHERE ts.transaction_id = t.id
    ) txn_splits ON true
    ${where}
    ORDER BY ${sortCol} ${sortDir}, t.row_index ASC
    LIMIT $${paramIdx++} OFFSET $${paramIdx++}
  `;
  params.push(limit, offset);

  const raw = await queryRaw<TransactionRow & { tags: string | TagRow[]; splits: string | TransactionRow["splits"] }>(dataSql, params);
  const data = raw.map((r) => ({
    ...r,
    tags: typeof r.tags === "string" ? JSON.parse(r.tags) : (r.tags ?? []),
    splits: typeof r.splits === "string" ? JSON.parse(r.splits) : (r.splits ?? []),
  })) as TransactionRow[];

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

// A user may act on a transaction they own (directly or via the parent
// statement) or one they participate in via a split.
export async function canAccessTransactions(ownerId: number, transactionIds: number[]): Promise<boolean> {
  if (!transactionIds.length) return false;
  const rows = await queryRaw<{ n: number }>(
    `SELECT COUNT(*)::int AS n
     FROM transactions t
     LEFT JOIN statements s ON s.id = t.statement_id
     WHERE t.id = ANY($2::int[])
       AND (COALESCE(t.owner_id, s.owner_id) = $1
            OR EXISTS (SELECT 1 FROM transaction_splits ts WHERE ts.transaction_id = t.id AND ts.participant_id = $1))`,
    [ownerId, transactionIds]
  );
  return rows[0]?.n === transactionIds.length;
}

export async function getTransactionById(id: number) {
  const sql = `
    SELECT t.*,
      o.category_override, o.merchant_normalized as merchant_override, o.notes, o.my_share_percent,
      COALESCE(o.category_override, t.category) as effective_category,
      COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name) as effective_merchant,
      ${bankLabel()} as bank_name,
      COALESCE(t.owner_id, s.owner_id) as owner_id,
      p.name as owner_name
    FROM transactions t
    LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
    LEFT JOIN statements s ON s.id = t.statement_id
    LEFT JOIN participants p ON p.id = COALESCE(t.owner_id, s.owner_id)
    WHERE t.id = $1
  `;
  const rows = await queryRaw<TransactionRow>(sql, [id]);
  return rows[0] || null;
}

/**
 * Does opening_balance + the period's transactions equal closing_balance?
 *
 * The single cheapest check on extraction quality: it catches missed rows,
 * duplicates, sign errors and transactions filed against the wrong statement,
 * none of which are visible by eye. Borrowed from double-entry accounting,
 * where it is called a balance assertion.
 *
 * Sign depends on what the balance means. On a liability (credit card, loan)
 * the balance is what you OWE, so spending increases it and payments reduce it.
 * On an asset (transaction, savings, offset) the balance is what you HOLD, so
 * the signs invert.
 */
export const BALANCE_DELTA = `SUM(CASE
  WHEN s.statement_type IN ('credit_card', 'loan')
    THEN CASE WHEN t.transaction_type IN ('debit', 'fee', 'interest') THEN t.amount ELSE -t.amount END
    ELSE CASE WHEN t.transaction_type IN ('debit', 'fee', 'interest') THEN -t.amount ELSE t.amount END
END)`;

/**
 * Other statements for the same account whose billing period overlaps this one.
 *
 * An account cannot be billed twice for the same day, so an overlap means the
 * same transactions were imported twice. This is not hypothetical: ANZ
 * statements 107 and 143 overlap by 118 days and put ~$42,000 of duplicate rows
 * in the ledger, which silently inflated spend and dragged the CSV split match
 * rate down to 46.5%.
 *
 * Two details are what make it actually catch that case:
 *
 *  - The account number is compared with non-digits stripped. The duplicate got
 *    in precisely because the existing duplicate key compared raw text, and ANZ
 *    wrote the same account as `408556264` on one statement and `4085-56264` on
 *    the other.
 *  - The range is half-open `[)`. These statements are issued back-to-back with
 *    one period's end date equal to the next one's start, so inclusive bounds
 *    flag every consecutive pair — 5 hits of which 3 were false. Half-open
 *    leaves exactly the 2 real ones.
 *
 * NULL bounds are excluded rather than passed to `daterange`, where NULL means
 * unbounded and would make an undated statement overlap the entire history.
 */
const STATEMENT_OVERLAPS = `
      SELECT COALESCE(json_agg(json_build_object(
               'id', o.id,
               'days', (LEAST(s.billing_end_date, o.billing_end_date)
                        - GREATEST(s.billing_start_date, o.billing_start_date))
             ) ORDER BY o.id), '[]'::json) AS overlaps
      FROM statements o
      WHERE o.id <> s.id
        AND o.owner_id = s.owner_id
        AND regexp_replace(o.account_number, '\\D', '', 'g')
          = regexp_replace(s.account_number, '\\D', '', 'g')
        AND o.billing_start_date IS NOT NULL AND o.billing_end_date IS NOT NULL
        AND s.billing_start_date IS NOT NULL AND s.billing_end_date IS NOT NULL
        AND daterange(s.billing_start_date, s.billing_end_date, '[)')
         && daterange(o.billing_start_date, o.billing_end_date, '[)')`;

export async function getStatements(ownerId: number) {
  const sql = `
    SELECT s.*,
      (SELECT COUNT(*)::int FROM transactions t WHERE t.statement_id = s.id) as transaction_count,
      p.name as owner_name,
      recon.expected_closing,
      recon.balance_diff,
      ov.overlaps
    FROM statements s
    LEFT JOIN participants p ON p.id = s.owner_id
    LEFT JOIN LATERAL (${STATEMENT_OVERLAPS}) ov ON true
    LEFT JOIN LATERAL (
      SELECT
        (s.opening_balance + ${BALANCE_DELTA})::numeric(12,2) as expected_closing,
        (s.opening_balance + ${BALANCE_DELTA} - s.closing_balance)::numeric(12,2) as balance_diff
      FROM transactions t
      WHERE t.statement_id = s.id
        AND s.opening_balance IS NOT NULL
        AND s.closing_balance IS NOT NULL
    ) recon ON true
    WHERE s.owner_id = $1
    ORDER BY s.billing_end_date DESC NULLS LAST, s.created_at DESC
  `;
  return queryRaw<StatementRow>(sql, [ownerId]);
}

export async function getStatementById(id: number) {
  const sql = `
    SELECT s.*,
      (SELECT COUNT(*)::int FROM transactions t WHERE t.statement_id = s.id) as transaction_count,
      p.name as owner_name
    FROM statements s
    LEFT JOIN participants p ON p.id = s.owner_id
    WHERE s.id = $1
  `;
  const rows = await queryRaw<StatementRow>(sql, [id]);
  return rows[0] || null;
}

export async function getMerchantSuggestions(search: string) {
  const sql = `
    SELECT DISTINCT COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name) as merchant
    FROM transactions t
    LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
    WHERE COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name) ILIKE $1
    ORDER BY merchant
    LIMIT 20
  `;
  return queryRaw<{ merchant: string }>(sql, [`%${search}%`]);
}

export async function getBankNames() {
  const [bankRows, statementless] = await Promise.all([
    queryRaw<{ bank_name: string }>(`SELECT DISTINCT bank_name FROM statements ORDER BY bank_name`),
    queryRaw<{ label: string }>(
      `SELECT DISTINCT ${bankLabel("t", "s")} as label
         FROM transactions t LEFT JOIN statements s ON s.id = t.statement_id
        WHERE t.statement_id IS NULL`
    ),
  ]);
  const banks = bankRows.map((r) => r.bank_name);
  // Order matters for the filter chips: real banks first, then the
  // statement-less kinds, in a stable order rather than whatever the DB returns.
  for (const label of ["Manual", "Gift Card"]) {
    if (statementless.some((r) => r.label === label)) banks.push(label);
  }
  return banks;
}

export interface ParticipantBalance {
  id: number;
  name: string;
  total_owed: number;
  unsettled_count: number;
  /** Splits counted at a non-AUD figure because no converted amount exists. */
  unconverted_count: number;
}

export async function getParticipantBalances(ownerId: number, tagIds?: number[]) {
  const params: unknown[] = [ownerId];
  let tagFilter = "";
  if (tagIds?.length) {
    params.push(tagIds);
    tagFilter = `AND EXISTS (SELECT 1 FROM transaction_tags tt WHERE tt.transaction_id = t.id AND tt.tag_id = ANY($2::int[]))`;
  }

  // Payments settle the total relationship between two people, not a specific tag.
  // Only subtract payments when viewing the unfiltered total; with a tag filter
  // active, show the raw split amount for that tag context only.
  //
  // That asymmetry is a symptom, not a design: a tag is a view and has no
  // payments, so a tag-filtered balance had nothing honest to subtract. A trip
  // does have payments (`split_payments.trip_id`, migration 0022), which is why
  // the per-trip figure in getTripAnalytics can be netted and this one cannot.
  // The fix for the tag case is to stop showing a balance there, not to invent
  // one — see docs/shared-expenses-design.md.
  const paymentsJoin = tagIds?.length ? "" : `
    LEFT JOIN (
      SELECT
        CASE WHEN sp.from_participant_id != $1 THEN sp.from_participant_id ELSE sp.to_participant_id END AS pid,
        SUM(CASE WHEN sp.to_participant_id = $1 THEN sp.amount ELSE -sp.amount END) AS net_paid
      FROM split_payments sp
      WHERE sp.from_participant_id = $1 OR sp.to_participant_id = $1
      GROUP BY pid
    ) payments ON payments.pid = p.id`;
  const paymentsSelect = tagIds?.length ? "" : "- COALESCE(payments.net_paid, 0)::numeric(12,2)";
  const paymentsGroup = tagIds?.length ? "" : ", payments.net_paid";

  return queryRaw<ParticipantBalance>(`
    SELECT p.id, p.name,
      COALESCE(SUM(splits.signed_amount), 0)::numeric(12,2)
        ${paymentsSelect} AS total_owed,
      COALESCE(SUM(splits.split_count), 0)::int AS unsettled_count,
      -- Splits whose AUD value is unknown. They are still summed above (as
      -- their native figure), so a non-zero count means this balance is
      -- approximate and the UI has to say so.
      COALESCE(SUM(splits.unconverted_count), 0)::int AS unconverted_count
    FROM participants p

    LEFT JOIN (
      -- They owe me: their splits on transactions I own
      -- Settle in AUD: on a foreign-currency row the amount column is in its own
      -- currency, so splitting on it nets a USD figure against AUD ones.
      SELECT ts.participant_id AS pid,
        (CASE WHEN t.transaction_type IN ('debit', 'fee', 'interest') THEN COALESCE(t.amount_aud, t.amount) ELSE -COALESCE(t.amount_aud, t.amount) END) * ts.share_percent / 100 AS signed_amount,
        1 AS split_count,
        (CASE WHEN ${AMOUNT_UNCONVERTED} THEN 1 ELSE 0 END) AS unconverted_count
      FROM transaction_splits ts
      JOIN transactions t ON t.id = ts.transaction_id
      LEFT JOIN statements s ON s.id = t.statement_id
      WHERE COALESCE(t.owner_id, s.owner_id) = $1 AND ts.participant_id != $1
        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND ${ACTIVE_OBLIGATION}
      ${tagFilter}

      UNION ALL

      -- I owe them: my splits on transactions they own
      SELECT COALESCE(t.owner_id, s.owner_id) AS pid,
        -((CASE WHEN t.transaction_type IN ('debit', 'fee', 'interest') THEN COALESCE(t.amount_aud, t.amount) ELSE -COALESCE(t.amount_aud, t.amount) END) * ts.share_percent / 100) AS signed_amount,
        0 AS split_count,
        (CASE WHEN ${AMOUNT_UNCONVERTED} THEN 1 ELSE 0 END) AS unconverted_count
      FROM transaction_splits ts
      JOIN transactions t ON t.id = ts.transaction_id
      LEFT JOIN statements s ON s.id = t.statement_id
      WHERE ts.participant_id = $1 AND COALESCE(t.owner_id, s.owner_id) != $1
        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND ${ACTIVE_OBLIGATION}
      ${tagFilter}
    ) splits ON splits.pid = p.id
    ${paymentsJoin}

    WHERE p.id != $1
    GROUP BY p.id, p.name ${paymentsGroup}
    ORDER BY p.name
  `, params);
}

export interface SharedTransactionRow extends TransactionRow {
  splits: { participant_id: number; name: string; share_percent: number; settled: boolean }[];
}

export async function ensureTag(name: string, color: string): Promise<number> {
  const rows = await queryRaw<{ id: number }>(
    `INSERT INTO tags (name, color) VALUES ($1, $2)
     ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name
     RETURNING id`,
    [name, color]
  );
  return rows[0].id;
}

export async function batchInsertCSVTransactions(
  ownerId: number,
  rows: {
    date: string;
    description: string;
    amount: number;
    transaction_type: string;
    merchant_name?: string;
    foreign_currency_amount?: number;
    foreign_currency_code?: string;
    category?: string;
    account?: string;
    source_ref?: string;
  }[],
  tagId: number,
  source?: string
): Promise<number> {
  if (rows.length === 0) return 0;

  const baseRows = await queryRaw<{ base: number }>(
    `SELECT COALESCE(MAX(row_index), -1) as base FROM transactions WHERE owner_id = $1 AND statement_id IS NULL`,
    [ownerId]
  );
  const base = Number(baseRows[0].base);

  const valueClauses: string[] = [];
  const params: unknown[] = [ownerId];
  let p = 2;
  rows.forEach((r, i) => {
    valueClauses.push(
      `(NULL, $1, $${p++}, $${p++}, $${p++}, $${p++}, $${p++}, $${p++}, $${p++}, $${p++}, $${p++}, $${p++}, $${p++}, ${base + 1 + i})`
    );
    params.push(
      r.date, r.description, r.amount, r.transaction_type, r.merchant_name ?? null,
      r.foreign_currency_amount ?? null, r.foreign_currency_code ?? null,
      // The category the reviewer chose. It used to be accepted here and then
      // left out of the column list, so every category picked in the review
      // step was silently discarded and the trigger wrote 'other'. That is not
      // cosmetic: an uncategorised credit is admitted by NET_SPEND_ROWS and
      // negated by SPEND_SIGNED, so 62 imported transfers cancelled $74,338 of
      // spend and none of them counted as income.
      r.category ?? null,
      source ?? null,
      // source + source_ref is the only idempotency this path has. Without it a
      // second import of the same file duplicates every row, because row_index
      // is assigned MAX+1 and uq_transaction_identity can never fire.
      r.source_ref ?? null,
      r.account ?? null
    );
  });

  const txIds = await queryRaw<{ id: number }>(
    `INSERT INTO transactions (statement_id, owner_id, transaction_date, description, amount, transaction_type, merchant_name, foreign_currency_amount, foreign_currency_code, category, source, source_ref, source_account, row_index)
     VALUES ${valueClauses.join(", ")}
     ON CONFLICT (source, source_ref) WHERE source IS NOT NULL AND source_ref IS NOT NULL DO NOTHING
     RETURNING id`,
    params
  );

  if (txIds.length > 0) {
    const tagValueClauses = txIds.map((_, i) => `($${i * 2 + 1}, $${i * 2 + 2})`);
    const tagParams: unknown[] = txIds.flatMap((r) => [r.id, tagId]);
    await queryRaw(
      `INSERT INTO transaction_tags (transaction_id, tag_id) VALUES ${tagValueClauses.join(", ")} ON CONFLICT DO NOTHING`,
      tagParams
    );
  }

  return txIds.length;
}

/**
 * Excludes cash from reconciliation.
 *
 * Cash never appears on a statement, so a cash transaction would sit in the
 * queue forever being offered matches within 3 days and 1% on amount. Accepting
 * one is silently destructive: reconciled manual rows are filtered out of every
 * query, so the cash spend vanishes while the card transaction it matched
 * claims to be that same spend.
 *
 * Only cash is excluded. Bank transfers do appear on a statement now that
 * transaction accounts are imported, and NULL means unknown — both stay
 * candidates, which preserves the behaviour of every pre-existing row.
 */
/**
 * Payment methods that can still be matched against a card statement line.
 *
 * Cash never appears on one. Neither does a credits-funded delivery order: the
 * gift card already paid it, so there is no card leg coming, ever. Leaving
 * those in the queue meant 81 orders sat in "pending reconciliation" waiting
 * for a match that could not exist (user, 2026-07-27).
 */
export const needsCardMatch = (alias = "t") =>
  `(${alias}.payment_method IS NULL OR ${alias}.payment_method NOT IN ('cash', 'credits'))`;

/**
 * REMOVED 2026-08-13. There used to be an `awaitsStatementLine()` predicate
 * here, excluding account-feed rows from the pending-reconciliation queue.
 *
 * Its premise — a feed row is the account's own ledger entry, so no statement
 * line is coming — was asserted and never tested. It was false for almost every
 * account: 422 of the first 550 imported rows already had a statement twin.
 *
 * How it got in matters more than what it did. The queue jumped from 8 to 558
 * the moment the feed landed, and that jump was read as noise and filtered
 * away. The queue was right — those rows genuinely were provisional entries
 * awaiting statement lines — and hiding them removed the only mechanism that
 * would ever have collapsed them, so a transient overlap became a permanent
 * double-count and stayed invisible until a human saw one salary payment listed
 * twice in two currencies.
 *
 * An imported row that has no statement belongs in the queue. Volume is solved
 * upstream, by not importing what the statements already cover
 * (`getStatementCoverage`), never downstream by hiding what is there.
 */

/**
 * Bank label for a transaction. A row with no statement was not imported from
 * one, and the label has to say *why*: "Manual" reads as "hand-entered, still
 * awaiting a card line", which is wrong for a gift-card order — nothing is
 * awaited. `s` must be the statements alias in scope.
 */
export const bankLabel = (t = "t", s = "s") =>
  `COALESCE(${s}.bank_name, CASE WHEN ${t}.payment_method = 'credits' THEN 'Gift Card' ELSE 'Manual' END)`;

export interface PotentialMatch {
  id: number;
  transaction_date: string;
  description: string;
  amount: number;
  transaction_type: string;
  effective_merchant: string;
  effective_category: string;
  bank_name: string;
  billing_end_date: string | null;
}

export interface ManualTxWithMatches extends TransactionRow {
  matches: PotentialMatch[];
}

export async function getPendingReconciliations(ownerId: number): Promise<ManualTxWithMatches[]> {
  // Fetch all unreconciled manual transactions
  const raw = await queryRaw<TransactionRow & { tags: string | TagRow[]; splits: string | TransactionRow["splits"] }>(
    `SELECT t.*,
       o.category_override, o.merchant_normalized as merchant_override, o.notes, o.my_share_percent,
       COALESCE(o.category_override, t.category) as effective_category,
       COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name) as effective_merchant,
       'Manual' as bank_name,
       t.owner_id,
       p.name as owner_name,
       txn_tags.tags,
       txn_splits.splits
     FROM transactions t
     LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
     LEFT JOIN participants p ON p.id = t.owner_id
     LEFT JOIN LATERAL (
       SELECT COALESCE(json_agg(json_build_object('id', tg.id, 'name', tg.name, 'color', tg.color) ORDER BY tg.name), '[]'::json) as tags
       FROM transaction_tags tt JOIN tags tg ON tg.id = tt.tag_id
       WHERE tt.transaction_id = t.id
     ) txn_tags ON true
     LEFT JOIN LATERAL (
       SELECT COALESCE(json_agg(json_build_object('participant_id', ts.participant_id, 'name', sp.name, 'share_percent', ts.share_percent, 'settled', ts.settled) ORDER BY sp.name), '[]'::json) as splits
       FROM transaction_splits ts JOIN participants sp ON sp.id = ts.participant_id
       WHERE ts.transaction_id = t.id
     ) txn_splits ON true
     WHERE t.statement_id IS NULL AND t.owner_id = $1 AND t.reconciled_with_id IS NULL
       AND ${needsCardMatch("t")}
     ORDER BY t.transaction_date DESC, t.row_index ASC`,
    [ownerId]
  );

  const manualTxs = raw.map((r) => ({
    ...r,
    tags: typeof r.tags === "string" ? JSON.parse(r.tags) : (r.tags ?? []),
    splits: typeof r.splits === "string" ? JSON.parse(r.splits) : (r.splits ?? []),
  })) as TransactionRow[];

  if (manualTxs.length === 0) return [];

  // Fetch all potential matches in one query using window function
  const matchRows = await queryRaw<PotentialMatch & { manual_id: number; rn: number }>(
    `SELECT manual_id, id, transaction_date, description, amount, transaction_type,
       effective_merchant, effective_category, bank_name, billing_end_date
     FROM (
       SELECT
         m.id AS manual_id,
         t.id,
         t.transaction_date,
         t.description,
         t.amount,
         t.transaction_type,
         COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name, '') AS effective_merchant,
         COALESCE(o.category_override, t.category, '') AS effective_category,
         s.bank_name,
         s.billing_end_date,
         ROW_NUMBER() OVER (
           PARTITION BY m.id
           ORDER BY ABS(t.amount - m.amount), ABS(t.transaction_date - m.transaction_date)
         ) AS rn
       FROM transactions m
       JOIN transactions t ON t.statement_id IS NOT NULL
         AND t.transaction_date BETWEEN m.transaction_date - 3 AND m.transaction_date + 3
         AND t.amount BETWEEN m.amount * 0.99 AND m.amount * 1.01
       JOIN statements s ON s.id = t.statement_id
       LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
       WHERE m.statement_id IS NULL
         AND m.owner_id = $1
         AND m.reconciled_with_id IS NULL
         AND ${needsCardMatch("m")}
         AND COALESCE(t.owner_id, s.owner_id) = $1
         AND NOT EXISTS (
           SELECT 1 FROM transactions mt WHERE mt.reconciled_with_id = t.id
         )
     ) sq
     WHERE rn <= 5
     ORDER BY manual_id, rn`,
    [ownerId]
  );

  // Group matches by manual_id
  const matchesByManualId = new Map<number, PotentialMatch[]>();
  for (const row of matchRows) {
    const list = matchesByManualId.get(row.manual_id) ?? [];
    list.push({
      id: row.id,
      transaction_date: row.transaction_date,
      description: row.description,
      amount: row.amount,
      transaction_type: row.transaction_type,
      effective_merchant: row.effective_merchant,
      effective_category: row.effective_category,
      bank_name: row.bank_name,
      billing_end_date: row.billing_end_date,
    });
    matchesByManualId.set(row.manual_id, list);
  }

  return manualTxs.map((tx) => ({
    ...tx,
    matches: matchesByManualId.get(tx.id) ?? [],
  }));
}

export async function getTags() {
  return queryRaw<TagRow & { transaction_count: number }>(`
    SELECT tg.id, tg.name, tg.color,
      COUNT(tt.transaction_id)::int as transaction_count
    FROM tags tg
    LEFT JOIN transaction_tags tt ON tt.tag_id = tg.id
    GROUP BY tg.id
    ORDER BY tg.name
  `);
}

export async function getSharedTransactions(ownerId: number, tagIds?: number[], noTags?: boolean, participantId?: number) {
  const params: unknown[] = [ownerId];
  let tagClause = "";
  if (noTags) {
    tagClause = `AND NOT EXISTS (SELECT 1 FROM transaction_tags tt WHERE tt.transaction_id = t.id)`;
  } else if (tagIds?.length) {
    params.push(tagIds);
    tagClause = `AND EXISTS (SELECT 1 FROM transaction_tags tt WHERE tt.transaction_id = t.id AND tt.tag_id = ANY($2::int[]))`;
  }

  let participantClause = "";
  if (participantId) {
    params.push(participantId);
    participantClause = `AND EXISTS (SELECT 1 FROM transaction_splits ts_p WHERE ts_p.transaction_id = t.id AND ts_p.participant_id = $${params.length})`;
  }

  const rows = await queryRaw<TransactionRow & { split_data: string }>(`
    SELECT t.*,
      o.category_override, o.merchant_normalized as merchant_override, o.notes,
      COALESCE(o.category_override, t.category) as effective_category,
      COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name) as effective_merchant,
      ${bankLabel()} as bank_name,
      -- The table renders t.amount, which is not always AUD. Without these the
      -- rows visibly disagreed with the participant balances, which do convert.
      ${NATIVE_CURRENCY} as currency,
      ${AMOUNT_UNCONVERTED} as amount_unconverted,
      COALESCE(t.owner_id, s.owner_id) as owner_id,
      p_owner.name as owner_name,
      COALESCE(src.created_at, t.created_at) as created_at,
      -- Receipt flag, same LATERAL the transactions page uses: the shared
      -- viewer is a split participant, so the order API authorises them —
      -- without the flag here the disclosure arrow (and the item list behind
      -- it) existed only on the owner's page.
      order_ctx.platform as order_platform,
      json_agg(json_build_object(
        'split_id', ts.id,
        'participant_id', ts.participant_id,
        'name', p.name,
        'share_percent', ts.share_percent,
        'settled', ts.settled
      ) ORDER BY p.name) as split_data
    FROM transactions t
    JOIN transaction_splits ts ON ts.transaction_id = t.id
    JOIN participants p ON p.id = ts.participant_id
    LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
    LEFT JOIN statements s ON s.id = t.statement_id
    LEFT JOIN participants p_owner ON p_owner.id = COALESCE(t.owner_id, s.owner_id)
    LEFT JOIN LATERAL (
      SELECT em.platform
        FROM expense_metadata em
       WHERE em.transaction_id = t.id OR em.matched_transaction_id = t.id
       LIMIT 1
    ) order_ctx ON true
    LEFT JOIN transactions src ON src.reconciled_with_id = t.id AND src.statement_id IS NULL
    WHERE (
        (
          COALESCE(t.owner_id, s.owner_id) = $1
          AND EXISTS (SELECT 1 FROM transaction_splits ts2 WHERE ts2.transaction_id = t.id AND ts2.participant_id != $1)
        ) OR (
          COALESCE(t.owner_id, s.owner_id) != $1
          AND EXISTS (SELECT 1 FROM transaction_splits ts_me WHERE ts_me.transaction_id = t.id AND ts_me.participant_id = $1)
        )
      )
      AND ${EXCLUDE_RECONCILED_SOURCE}
    ${tagClause}
    ${participantClause}
    GROUP BY t.id, o.category_override, o.merchant_normalized, o.notes, s.bank_name, s.currency, s.owner_id, p_owner.name, src.created_at, order_ctx.platform
    ORDER BY t.transaction_date DESC
  `, params);

  return rows.map((r) => ({
    ...r,
    splits: typeof r.split_data === "string" ? JSON.parse(r.split_data) : r.split_data,
  }));
}

// ─── Trips ───────────────────────────────────────────────────────────────────

export interface TripRow {
  id: number;
  owner_id: number;
  name: string;
  description: string | null;
  start_date: string | null;
  end_date: string | null;
  color: string;
  archived: boolean;
  created_at: string;
  total_spend: number;
  transaction_count: number;
}

export interface TripAnalytics {
  trip: TripRow;
  total_spend: number;
  transaction_count: number;
  num_days: number;
  daily_average: number;
  category_breakdown: { category: string; amount: number; count: number }[];
  daily_spend: { date: string; amount: number }[];
  top_merchants: { merchant: string; amount: number; count: number }[];
  /**
   * A trip has two economies, and mixing them is what made `travel` look like an
   * uninformative 60% slab: it is the ONLY category that spans both. Measured on
   * Europe 2026, every other category is 100% on-the-ground — dining, transport,
   * entertainment, groceries and shopping are all exactly $0.00 before departure.
   *
   * So the fix is not a finer travel taxonomy (which would need a hand-maintained
   * merchant list, the trap ticket #19 already describes). It is to split by phase
   * and use the axis that carries information in each: merchant before departure,
   * where everything is a flight or a booking, and category after it, where travel
   * drops to a normal-sized slice among peers.
   *
   * `committed` is dated before `start_date`; everything else is `on_ground`. A trip
   * with no start_date has no knowable split, so it all reads as on-ground.
   */
  phases: {
    committed: number;
    committed_count: number;
    on_ground: number;
    on_ground_count: number;
  };
  /** Pre-departure spend by merchant — the bookings that make up the commitment. */
  committed_merchants: { merchant: string; amount: number; count: number }[];
  /** On-the-ground spend by category, where category is finally worth charting. */
  on_ground_categories: { category: string; amount: number; count: number }[];
  /** On-ground spend per day of the trip window. The comparable rate between trips. */
  on_ground_daily: number;
  tag_breakdown: { tag_id: number; name: string; color: string; amount: number; count: number }[];
  participant_splits: {
    participant_id: number;
    name: string;
    /** Their share of rows the VIEWER paid, net of payments scoped to this trip. */
    owed: number;
    /** That same figure before payments, so the UI can say "settled" rather than just "0.00". */
    owed_gross: number;
    /** Payments from them to the viewer, scoped to this trip. */
    paid_to_me: number;
    /** The VIEWER's share of rows THIS PARTICIPANT paid, net of the viewer's payments to them. */
    i_owe: number;
    /** Ditto, before payments. */
    i_owe_gross: number;
    /** Payments from the viewer to them, scoped to this trip. */
    paid_by_me: number;
    /** Splits counted at a non-AUD figure because no converted amount exists. */
    unconverted_count: number;
    i_owe_unconverted_count: number;
  }[];
  /** True when the viewer is a participant but not the trip's owner. */
  viewer_is_owner: boolean;
}

/**
 * Who counts as a participant on a trip. Derived, never stored.
 *
 * A trip is *all the expenses on one trip*, so being a participant is a fact
 * about those expenses: you hold a split on one, you paid for one, or a payment
 * of yours is scoped to the trip. Storing it as a membership list would be a
 * second record of the same fact, and two records of one fact drift — the same
 * reason `CLAUDE.md` insists sharing is a real split rather than a flag, and the
 * reason the 2026-07-27 settlement design concluded an extra party on a trip
 * "needs no schema at all".
 *
 * It also gets the exclusions right for free. Singapore + Bangkok 2026 has no
 * Sonu split and no Sonu payment, so she is not a participant and never sees it,
 * with no backfill to keep in sync as transactions are assigned and unassigned.
 *
 * Aliased `p*` throughout so it can be inlined anywhere without colliding with
 * the `t`/`s`/`o` aliases the shared fragments assume.
 */
const TRIP_PARTICIPANT = (tripIdExpr: string, participantExpr: string) => `(
    EXISTS (
      SELECT 1 FROM transaction_overrides po
      JOIN transactions pt ON pt.id = po.transaction_id
      LEFT JOIN statements ps ON ps.id = pt.statement_id
      LEFT JOIN transaction_splits pts ON pts.transaction_id = pt.id
      WHERE po.trip_id = ${tripIdExpr}
        AND (COALESCE(pt.owner_id, ps.owner_id) = ${participantExpr}
             OR pts.participant_id = ${participantExpr})
    )
    OR EXISTS (
      SELECT 1 FROM split_payments psp
      WHERE psp.trip_id = ${tripIdExpr}
        AND (psp.from_participant_id = ${participantExpr}
             OR psp.to_participant_id = ${participantExpr})
    )
  )`;

/** Exported for the routes that gate a write on participation. */
export async function isTripParticipant(tripId: number, participantId: number): Promise<boolean> {
  const rows = await queryRaw<{ ok: boolean }>(`
    SELECT (tr.owner_id = $2 OR ${TRIP_PARTICIPANT('tr.id', '$2')}) AS ok
    FROM trips tr WHERE tr.id = $1
  `, [tripId, participantId]);
  return rows[0]?.ok === true;
}

// `total_spend` is the headline figure on the trips list and the trip header,
// and it nets refunds for the same reason getTripAnalytics does — see the note
// there. Trips are aliased `tr` so that `t` can be `transactions`, which is the
// alias the shared fragments assume.
const TRIP_TOTAL_SPEND = `COALESCE(SUM(
        CASE WHEN ${NET_SPEND_ROWS}
             AND ${EXCLUDE_RECONCILED_SOURCE}
             AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
        THEN ${SPEND_SIGNED} ELSE 0 END
      ), 0)::float AS total_spend`;

// A trip is visible to its owner and to anyone who participates in it. Scoping
// visibility to `owner_id` alone meant Sonu could not see a single trip despite
// paying for 104 of the tagged rows herself — her own spending was invisible on
// the only page organised around it.
export async function getTrips(viewerId: number): Promise<TripRow[]> {
  return queryRaw<TripRow>(`
    SELECT
      tr.*,
      ${TRIP_TOTAL_SPEND},
      COUNT(o.transaction_id)::int AS transaction_count
    FROM trips tr
    LEFT JOIN transaction_overrides o ON o.trip_id = tr.id
    LEFT JOIN transactions t ON t.id = o.transaction_id
    WHERE tr.owner_id = $1 OR ${TRIP_PARTICIPANT('tr.id', '$1')}
    GROUP BY tr.id
    ORDER BY tr.created_at DESC
  `, [viewerId]);
}

export async function getTripById(id: number, viewerId: number): Promise<TripRow | null> {
  const rows = await queryRaw<TripRow>(`
    SELECT
      tr.*,
      ${TRIP_TOTAL_SPEND},
      COUNT(o.transaction_id)::int AS transaction_count
    FROM trips tr
    LEFT JOIN transaction_overrides o ON o.trip_id = tr.id
    LEFT JOIN transactions t ON t.id = o.transaction_id
    WHERE tr.id = $1 AND (tr.owner_id = $2 OR ${TRIP_PARTICIPANT('tr.id', '$2')})
    GROUP BY tr.id
  `, [id, viewerId]);
  return rows[0] ?? null;
}

export async function getTripAnalytics(tripId: number, viewerId: number): Promise<TripAnalytics> {
  const trip = await getTripById(tripId, viewerId);
  if (!trip) throw new Error("Trip not found");

  // What the trip cost, with refunds subtracted.
  //
  // These four queries filtered on `transaction_type IN ('debit','fee','interest')`,
  // which drops every refund and credit — so money that came back was still
  // counted as trip spend. A partly-refunded booking read at its full price and
  // a fully-refunded one read as pure cost.
  //
  // NET_SPEND_ROWS admits the refunds and SPEND_SIGNED carries their direction,
  // the same pair the general analytics adopted after a refunded Expedia
  // purchase read as $2,888.92 of spend. `transactions` is aliased `t` because
  // the fragments assume that alias.
  //
  // A cancelled booking is a different case and is NOT handled here: both its
  // legs are untagged from the trip by hand, because a trip the booking was
  // cancelled from never incurred that cost at all. This nets the partial
  // refunds — a price adjustment on a booking that did happen.
  //
  // COUNT(*) deliberately still counts refund rows: a refund is a transaction
  // that occurred on the trip, even though it subtracts from the total.
  const [
    categoryRows, dailyRows, merchantRows, tagRows, splitRows,
    phaseRows, committedMerchantRows, onGroundCategoryRows,
  ] = await Promise.all([
    queryRaw<{ category: string; amount: number; count: number }>(`
      SELECT
        COALESCE(o.category_override, t.category, 'other') AS category,
        SUM(${SPEND_SIGNED})::float AS amount,
        COUNT(*)::int AS count
      FROM transaction_overrides o
      JOIN transactions t ON t.id = o.transaction_id
      WHERE o.trip_id = $1
        AND ${NET_SPEND_ROWS}
        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
      GROUP BY 1
      ORDER BY 2 DESC
    `, [tripId]),

    queryRaw<{ date: string; amount: number }>(`
      SELECT
        t.transaction_date::text AS date,
        SUM(${SPEND_SIGNED})::float AS amount
      FROM transaction_overrides o
      JOIN transactions t ON t.id = o.transaction_id
      WHERE o.trip_id = $1
        AND ${NET_SPEND_ROWS}
        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
      GROUP BY 1
      ORDER BY 1
    `, [tripId]),

    queryRaw<{ merchant: string; amount: number; count: number }>(`
      SELECT
        COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name, t.description) AS merchant,
        SUM(${SPEND_SIGNED})::float AS amount,
        COUNT(*)::int AS count
      FROM transaction_overrides o
      JOIN transactions t ON t.id = o.transaction_id
      WHERE o.trip_id = $1
        AND ${NET_SPEND_ROWS}
        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
      GROUP BY 1
      ORDER BY 2 DESC
      LIMIT 10
    `, [tripId]),

    queryRaw<{ tag_id: number; name: string; color: string; amount: number; count: number }>(`
      SELECT
        tg.id AS tag_id, tg.name, tg.color,
        SUM(${SPEND_SIGNED})::float AS amount,
        COUNT(DISTINCT t.id)::int AS count
      FROM transaction_overrides o
      JOIN transactions t ON t.id = o.transaction_id
      JOIN transaction_tags tt ON tt.transaction_id = t.id
      JOIN tags tg ON tg.id = tt.tag_id
      WHERE o.trip_id = $1
        AND ${NET_SPEND_ROWS}
        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
      GROUP BY tg.id
      ORDER BY 4 DESC
    `, [tripId]),

    // Owed per participant for THIS trip, net of payments made against it.
    //
    // This was gross splits, with a comment explaining why it could not be
    // anything better: `split_payments` carried no trip attribution, so a
    // payment could not be assigned to a trip and every trip reported 100%
    // unsettled including trips paid in full. `split_payments.trip_id`
    // (migration 0022) closes that, so the figure is now real.
    //
    // Three exclusions, all load-bearing:
    //  - ACTIVE_OBLIGATION drops settled splits, so a closed trip reads zero
    //    rather than its original gross.
    //  - EXCLUDE_RECONCILED_SOURCE drops the manual row a statement line has
    //    superseded. The trip queries never applied it, so a reconciled trip
    //    expense was counted twice here.
    //  - AMOUNT_UNCONVERTED counts rows whose AUD value is unknown, the same
    //    way getParticipantBalances does. They are still summed (at their
    //    native figure), so a non-zero count means this total is approximate
    //    and the UI has to say so. A trip is where foreign rows actually live,
    //    so netting a EUR figure against AUD ones silently is most likely to
    //    bite exactly here.
    //
    // A fourth, and it is what "owed" actually means: only rows THIS viewer paid
    // for. Without ${OWNER_SCOPE} the figure sums every split on every trip
    // transaction regardless of who paid, so it silently mixes debts owed to
    // different people. On Europe 2026 that put $1,605.49 of Molina's share of
    // Sonu-paid rows into a number labelled as owed to the owner — a debt that
    // is real, but between the other two participants, and which they settled
    // directly (split_payments id 5, Molina -> Sonu, exactly $1,605.49).
    // A participant's own share of a row they paid for was in there too, which
    // is nobody's debt at all.
    //
    // `transactions` is aliased `t` so the shared fragments apply directly —
    // they assume that alias, and hand-inlining a copy is what let the
    // reconciled-row exclusion drift out of the analytics routes to begin with.
    //
    // ── Both directions, and deliberately NOT netted ──
    //
    // `owed` is unchanged: their share of rows the viewer paid. `i_owe` is the
    // mirror: the viewer's share of rows THAT participant paid. Rendering the
    // pair from the viewer's side is the whole fix — a non-owner used to get a
    // page where their own obligation could not appear, so Sonu's Europe 2026
    // read "you are owed $2,408.24" while omitting the $8,004.04 she owed.
    //
    // Collapsing the two into one signed net was the obvious next step and is
    // WRONG. The grouped-payment allocation (memory case `allocate_grouped_payments`)
    // cleared Sonu's transfers against the trip debts chronologically, Europe
    // first, remainder to household — and the debt it cleared was this
    // one-directional gross. Netting redefines Europe's debt as $7,201.30 after
    // the fact, which turns the $8,004.04 already allocated into an $802.75
    // over-allocation and leaves household understated by the same amount. The
    // total stays right and the split between scopes silently stops being. So
    // both halves are returned whole, with their gross and payments, and the UI
    // states them separately.
    queryRaw<{
      participant_id: number; name: string;
      owed: number; owed_gross: number; paid_to_me: number;
      i_owe: number; i_owe_gross: number; paid_by_me: number;
      unconverted_count: number; i_owe_unconverted_count: number;
    }>(`
      WITH scoped AS (
        SELECT ts.participant_id AS split_pid,
          ${OWNER_SCOPE} AS payer_pid,
          ts.share_percent / 100.0 * COALESCE(t.amount_aud, t.amount) AS amt,
          CASE WHEN ${AMOUNT_UNCONVERTED} THEN 1 ELSE 0 END AS unconverted
        FROM transaction_overrides o
        JOIN transactions t ON t.id = o.transaction_id
        ${STATEMENTS_JOIN}
        JOIN transaction_splits ts ON ts.transaction_id = t.id
        WHERE o.trip_id = $1
          AND t.transaction_type IN ('debit','fee','interest')
          AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
          AND ${ACTIVE_OBLIGATION}
          AND ${EXCLUDE_RECONCILED_SOURCE}
      ),
      owed AS (
        SELECT split_pid AS pid, SUM(amt) AS gross, SUM(unconverted) AS unconverted
        FROM scoped WHERE payer_pid = $2 AND split_pid <> $2 GROUP BY 1
      ),
      mine AS (
        SELECT payer_pid AS pid, SUM(amt) AS gross, SUM(unconverted) AS unconverted
        FROM scoped WHERE split_pid = $2 AND payer_pid <> $2 GROUP BY 1
      ),
      -- Only payments made TO the viewer. Payment 5 is Molina -> Sonu: a real
      -- settlement, but of a debt between those two, so it must not reduce what
      -- Molina owes the viewer. Symmetrical with the payer scoping above.
      paid_to_me AS (
        SELECT sp.from_participant_id AS pid, SUM(sp.amount) AS amt
        FROM split_payments sp
        WHERE sp.trip_id = $1 AND sp.to_participant_id = $2
        GROUP BY 1
      ),
      paid_by_me AS (
        SELECT sp.to_participant_id AS pid, SUM(sp.amount) AS amt
        FROM split_payments sp
        WHERE sp.trip_id = $1 AND sp.from_participant_id = $2
        GROUP BY 1
      )
      SELECT p.id AS participant_id, p.name,
        (COALESCE(owed.gross, 0) - COALESCE(paid_to_me.amt, 0))::float AS owed,
        COALESCE(owed.gross, 0)::float AS owed_gross,
        COALESCE(paid_to_me.amt, 0)::float AS paid_to_me,
        (COALESCE(mine.gross, 0) - COALESCE(paid_by_me.amt, 0))::float AS i_owe,
        COALESCE(mine.gross, 0)::float AS i_owe_gross,
        COALESCE(paid_by_me.amt, 0)::float AS paid_by_me,
        COALESCE(owed.unconverted, 0)::int AS unconverted_count,
        COALESCE(mine.unconverted, 0)::int AS i_owe_unconverted_count
      FROM participants p
      LEFT JOIN owed       ON owed.pid = p.id
      LEFT JOIN mine       ON mine.pid = p.id
      LEFT JOIN paid_to_me ON paid_to_me.pid = p.id
      LEFT JOIN paid_by_me ON paid_by_me.pid = p.id
      WHERE owed.pid IS NOT NULL OR mine.pid IS NOT NULL
         OR paid_to_me.pid IS NOT NULL OR paid_by_me.pid IS NOT NULL
      ORDER BY 3 DESC
    `, [tripId, viewerId]),

    // ── The phase split, and the right axis on each side of it ──
    //
    // $3 is the trip's start_date. NULL makes every comparison NULL, so a trip with
    // no dates collapses to all-on-ground rather than erroring or silently
    // reporting everything as committed.
    queryRaw<{ committed: number; committed_count: number; on_ground: number; on_ground_count: number }>(`
      SELECT
        COALESCE(SUM(CASE WHEN t.transaction_date < $2::date THEN ${SPEND_SIGNED} END), 0)::float AS committed,
        COUNT(*) FILTER (WHERE t.transaction_date < $2::date)::int AS committed_count,
        COALESCE(SUM(CASE WHEN t.transaction_date >= $2::date OR $2 IS NULL THEN ${SPEND_SIGNED} END), 0)::float AS on_ground,
        COUNT(*) FILTER (WHERE t.transaction_date >= $2::date OR $2 IS NULL)::int AS on_ground_count
      FROM transaction_overrides o
      JOIN transactions t ON t.id = o.transaction_id
      WHERE o.trip_id = $1
        AND ${NET_SPEND_ROWS}
        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
    `, [tripId, trip.start_date]),

    queryRaw<{ merchant: string; amount: number; count: number }>(`
      SELECT
        COALESCE(o.merchant_normalized, t.merchant_normalized, t.merchant_name, t.description) AS merchant,
        SUM(${SPEND_SIGNED})::float AS amount,
        COUNT(*)::int AS count
      FROM transaction_overrides o
      JOIN transactions t ON t.id = o.transaction_id
      WHERE o.trip_id = $1
        AND t.transaction_date < $2::date
        AND ${NET_SPEND_ROWS}
        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
      GROUP BY 1
      ORDER BY 2 DESC
      LIMIT 12
    `, [tripId, trip.start_date]),

    queryRaw<{ category: string; amount: number; count: number }>(`
      SELECT
        COALESCE(o.category_override, t.category, 'other') AS category,
        SUM(${SPEND_SIGNED})::float AS amount,
        COUNT(*)::int AS count
      FROM transaction_overrides o
      JOIN transactions t ON t.id = o.transaction_id
      WHERE o.trip_id = $1
        AND (t.transaction_date >= $2::date OR $2 IS NULL)
        AND ${NET_SPEND_ROWS}
        AND ${EXCLUDE_RECONCILED_SOURCE}
        AND COALESCE(o.category_override, t.category, 'other') NOT IN ('transfers', 'investment')
      GROUP BY 1
      ORDER BY 2 DESC
    `, [tripId, trip.start_date]),
  ]);

  const num_days = (trip.start_date && trip.end_date)
    ? Math.max(1, Math.round((new Date(trip.end_date).getTime() - new Date(trip.start_date).getTime()) / 86400000) + 1)
    : Math.max(dailyRows.length, 1);

  return {
    trip,
    total_spend: trip.total_spend,
    transaction_count: trip.transaction_count,
    num_days,
    daily_average: trip.total_spend / num_days,
    category_breakdown: categoryRows,
    daily_spend: dailyRows,
    top_merchants: merchantRows,
    tag_breakdown: tagRows,
    participant_splits: splitRows,
    viewer_is_owner: trip.owner_id === viewerId,
    phases: phaseRows[0] ?? { committed: 0, committed_count: 0, on_ground: 0, on_ground_count: 0 },
    committed_merchants: committedMerchantRows,
    on_ground_categories: onGroundCategoryRows,
    // Per day of the trip window, not per day of the whole span — the commitment
    // was made over months and dividing it by trip length would be meaningless.
    on_ground_daily: (phaseRows[0]?.on_ground ?? 0) / num_days,
  };
}

export async function createTrip(
  ownerId: number,
  data: { name: string; description?: string | null; start_date?: string | null; end_date?: string | null; color?: string }
): Promise<TripRow> {
  const rows = await queryRaw<TripRow>(`
    INSERT INTO trips (owner_id, name, description, start_date, end_date, color)
    VALUES ($1, $2, $3, $4, $5, $6)
    RETURNING *, 0::float AS total_spend, 0::int AS transaction_count
  `, [ownerId, data.name, data.description ?? null, data.start_date ?? null, data.end_date ?? null, data.color ?? '#6366f1']);
  return rows[0];
}

// Editable by any participant: a trip is a shared record of a shared journey,
// and dates or a colour are not the owner's private property.
export async function updateTrip(
  id: number,
  viewerId: number,
  data: Partial<{ name: string; description: string | null; start_date: string | null; end_date: string | null; color: string; archived: boolean }>
): Promise<TripRow | null> {
  const setClauses: string[] = [];
  const params: unknown[] = [];
  let idx = 1;
  if (data.name !== undefined) { setClauses.push(`name = $${idx++}`); params.push(data.name); }
  if ('description' in data) { setClauses.push(`description = $${idx++}`); params.push(data.description ?? null); }
  if ('start_date' in data) { setClauses.push(`start_date = $${idx++}`); params.push(data.start_date ?? null); }
  if ('end_date' in data) { setClauses.push(`end_date = $${idx++}`); params.push(data.end_date ?? null); }
  if (data.color !== undefined) { setClauses.push(`color = $${idx++}`); params.push(data.color); }
  if (data.archived !== undefined) { setClauses.push(`archived = $${idx++}`); params.push(data.archived); }
  if (!setClauses.length) return getTripById(id, viewerId);
  const idParam = idx++;
  const viewerParam = idx;
  params.push(id, viewerId);
  const rows = await queryRaw<TripRow>(`
    UPDATE trips SET ${setClauses.join(', ')}
    WHERE id = $${idParam}
      AND (owner_id = $${viewerParam} OR ${TRIP_PARTICIPANT(`$${idParam}`, `$${viewerParam}`)})
    RETURNING *, 0::float AS total_spend, 0::int AS transaction_count
  `, params);
  return rows[0] ?? null;
}

/**
 * Delete stays OWNER-ONLY, deliberately, even though everything else about a
 * trip is now shared.
 *
 * Both trip foreign keys are ON DELETE SET NULL, so deleting Europe 2026 untags
 * 210 transactions *and* NULLs the trip scope on 6 payments. That scope is where
 * the Europe-first allocation of Sonu's grouped transfers lives, and it was
 * derived by hand — nothing in the app recomputes it. Handing that to any
 * participant makes an unrecoverable loss one click away.
 */
export async function deleteTrip(id: number, ownerId: number): Promise<void> {
  await queryRaw(`DELETE FROM trips WHERE id = $1 AND owner_id = $2`, [id, ownerId]);
}

/**
 * Assign transactions to a trip, or to none when `tripId` is null.
 *
 * Both the scoping clauses here are new and both closed a live hole: this took
 * no viewer at all and checked nothing, so `PATCH /api/trips/[id]/transactions`
 * let any authenticated participant move any transaction id into any trip id.
 * Not being able to *see* a trip was no obstacle, because the write path never
 * read one.
 *
 *  - Only rows the viewer can already see may be moved (`owner OR split`, the
 *    same test getTransactions applies).
 *  - A non-null destination must be a trip the viewer participates in. Checked
 *    here rather than only in the route so the guarantee cannot be bypassed by
 *    the other caller (`POST /api/transactions/bulk`, `assign_trip`).
 *
 * Returns the number of rows actually moved, which is how a caller detects that
 * some ids were silently out of reach.
 */
export async function assignTransactionsToTrip(
  tripId: number | null,
  transactionIds: number[],
  viewerId: number
): Promise<number> {
  if (!transactionIds.length) return 0;
  if (tripId !== null && !(await isTripParticipant(tripId, viewerId))) {
    throw new Error("Not a participant on that trip");
  }
  const rows = await queryRaw<{ transaction_id: number }>(`
    INSERT INTO transaction_overrides (transaction_id, trip_id)
    SELECT t.id, $2
    FROM transactions t
    LEFT JOIN statements s ON s.id = t.statement_id
    WHERE t.id = ANY($1::int[])
      AND (COALESCE(t.owner_id, s.owner_id) = $3
           OR EXISTS (SELECT 1 FROM transaction_splits ts
                      WHERE ts.transaction_id = t.id AND ts.participant_id = $3))
    ON CONFLICT (transaction_id)
    DO UPDATE SET trip_id = EXCLUDED.trip_id
    RETURNING transaction_id
  `, [transactionIds, tripId, viewerId]);
  return rows.length;
}

export async function getTagTransactionIds(tagId: number): Promise<number[]> {
  const rows = await queryRaw<{ transaction_id: number }>(
    `SELECT transaction_id FROM transaction_tags WHERE tag_id = $1`,
    [tagId]
  );
  return rows.map((r) => r.transaction_id);
}

/**
 * The last date each account's statements cover, keyed by the account's last
 * four digits.
 *
 * This is the coverage test that matters, and it is not the one that was tried
 * first. The first attempt asked whether a row's date fell inside a statement's
 * min-max *window*, which for accounts whose statements span 182 to 460 days
 * swallows a year and answers nothing — 422 duplicates out of 550 rows got in
 * that way. `billing_end_date` asks a question that has an answer: up to what
 * date is this account complete?
 *
 * Amount matching cannot substitute for it. Two sources decompose the same
 * event differently — an aggregator bundles the Wise transfer fee into the
 * transfer (10001.13) where the statement itemises it (10000.00 + 1.13) — so
 * the rows are the same money with different numbers.
 *
 * Keyed on last4 because account numbers are written differently everywhere
 * ("235242176", "xxxxxxxxxxxx2176", "4085-56264"). Where two banks share a
 * last4 the later date wins, which errs towards excluding a row rather than
 * duplicating one; the caller shows every watermark so a wrong one is visible.
 */
export async function getStatementCoverage(): Promise<{ last4: string; coveredTo: string }[]> {
  const rows = await queryRaw<{ last4: string; covered_to: string }>(
    `SELECT right(regexp_replace(account_number, '[^0-9]', '', 'g'), 4) AS last4,
            MAX(billing_end_date)::text AS covered_to
       FROM statements
      WHERE account_number IS NOT NULL
      GROUP BY 1`,
    []
  );
  return rows
    .filter((r) => r.last4 && r.last4.length === 4)
    .map((r) => ({ last4: r.last4, coveredTo: r.covered_to }))
    .sort((a, b) => a.last4.localeCompare(b.last4));
}
