import { describe, it, expect } from "vitest";
import { queryRaw, queryRow } from "../../lib/db";
import {
  EXCLUDE_RECONCILED_SOURCE,
  NATIVE_CURRENCY,
  AMOUNT_UNCONVERTED,
  INVESTMENT_SIGNED,
} from "../../lib/analytics-sql";

/**
 * These fragments are the ones that drifted.
 *
 * The reconciled-row exclusion lived only in `queries.ts` for months while every
 * analytics route counted the superseded manual rows as spend — 48 rows, $4,474
 * of double count, invisible because the transaction list (which did exclude
 * them) looked right. The currency expression has the same shape of risk: it is
 * read by two call sites with two different denomination conventions.
 *
 * Assertions are per-row against known fixtures rather than against aggregate
 * totals, so a change in unrelated data cannot mask a regression.
 */

async function scratchTxn(cols: string, vals: string, params: unknown[] = []) {
  const row = await queryRow<{ id: number }>(
    `INSERT INTO transactions (${cols}) VALUES (${vals}) RETURNING id`,
    params
  );
  return row!.id;
}

/** Does this row survive the predicate? */
async function passes(predicate: string, id: number): Promise<boolean> {
  const rows = await queryRaw(
    `SELECT t.id FROM transactions t
       LEFT JOIN statements s ON s.id = t.statement_id
      WHERE t.id = $1 AND (${predicate})`,
    [id]
  );
  return rows.length === 1;
}

describe("EXCLUDE_RECONCILED_SOURCE", () => {
  it("drops a manual row that a statement line has superseded", async () => {
    const survivor = await scratchTxn(
      "transaction_date, description, amount, transaction_type",
      "'2026-03-01','Analytics fixture — survivor', 10.00, 'debit'"
    );
    const superseded = await scratchTxn(
      "transaction_date, description, amount, transaction_type, reconciled_with_id",
      "'2026-03-01','Analytics fixture — superseded', 10.00, 'debit', $1",
      [survivor]
    );

    expect(await passes(EXCLUDE_RECONCILED_SOURCE, superseded)).toBe(false);
  });

  it("keeps an ordinary manual row that was never reconciled", async () => {
    const id = await scratchTxn(
      "transaction_date, description, amount, transaction_type",
      "'2026-03-01','Analytics fixture — unreconciled', 10.00, 'debit'"
    );
    expect(await passes(EXCLUDE_RECONCILED_SOURCE, id)).toBe(true);
  });

  it("keeps a credits order row — nothing ever sets reconciled_with_id on one", async () => {
    // The order slice records the card match in expense_metadata, not on the
    // transaction, and needsCardMatch() holds these out of the reconcile queue.
    // If that ever changes, this exclusion would start eating real spend.
    const id = await scratchTxn(
      "transaction_date, description, amount, transaction_type, payment_method",
      "'2026-03-01','Order - Analytics fixture', 25.00, 'debit', 'credits'"
    );
    expect(await passes(EXCLUDE_RECONCILED_SOURCE, id)).toBe(true);
  });
});

describe("NATIVE_CURRENCY", () => {
  async function currencyOf(id: number) {
    const row = await queryRow<{ ccy: string; unconverted: boolean }>(
      `SELECT ${NATIVE_CURRENCY} AS ccy, ${AMOUNT_UNCONVERTED} AS unconverted
         FROM transactions t LEFT JOIN statements s ON s.id = t.statement_id
        WHERE t.id = $1`,
      [id]
    );
    return row!;
  }

  it("reads a statement-less order row's own currency, not 'AUD'", async () => {
    // The bug this guards: sourcing currency from s.currency alone labelled
    // every foreign order row AUD, because an order has no statement.
    const id = await scratchTxn(
      "transaction_date, description, amount, transaction_type, payment_method, foreign_currency_amount, foreign_currency_code",
      "'2026-03-01','Order - Foreign fixture', 3500.00, 'debit', 'credits', 3500.00, 'LKR'"
    );
    const { ccy, unconverted } = await currencyOf(id);
    expect(ccy).toBe("LKR");
    // No amount_aud: the ingest path refuses to assert an FX rate it lacks, so
    // this row's AUD value is genuinely unknown and must be reported as such.
    expect(unconverted).toBe(true);
  });

  it("prefers the statement's currency over the foreign-charge record", async () => {
    // Opposite convention: on an AUD statement, `amount` is AUD and
    // foreign_currency_code merely notes what was originally charged. Reading
    // the foreign code here would mislabel an AUD row as USD.
    const stmt = await queryRow<{ id: number }>(
      `INSERT INTO statements (bank_name, account_number, billing_end_date, currency, filename)
       VALUES ('Analytics Fixture Bank','0000','2026-03-31','AUD','analytics-fixture.pdf') RETURNING id`
    );
    const id = await scratchTxn(
      "transaction_date, description, amount, amount_aud, transaction_type, statement_id, foreign_currency_amount, foreign_currency_code",
      "'2026-03-01','Overseas purchase fixture', 45.00, 45.00, 'debit', $1, 30.00, 'USD'",
      [stmt!.id]
    );
    const { ccy, unconverted } = await currencyOf(id);
    expect(ccy).toBe("AUD");
    expect(unconverted).toBe(false);
  });

  it("defaults a plain manual row to AUD", async () => {
    const id = await scratchTxn(
      "transaction_date, description, amount, transaction_type",
      "'2026-03-01','Plain manual fixture', 12.00, 'debit'"
    );
    expect((await currencyOf(id)).ccy).toBe("AUD");
  });
});

describe("INVESTMENT_SIGNED", () => {
  /** The signed value the investments line would attribute to this row. */
  async function signedValue(id: number): Promise<number> {
    const row = await queryRow<{ v: string }>(
      `SELECT (${INVESTMENT_SIGNED})::text AS v FROM transactions t WHERE t.id = $1`,
      [id]
    );
    return Number(row!.v);
  }

  it("counts a contribution positive", async () => {
    const id = await scratchTxn(
      "transaction_date, description, amount, transaction_type, category",
      "'2026-03-01','Investment fixture — deposit', 5000.00, 'debit', 'investment'"
    );
    expect(await signedValue(id)).toBe(5000);
  });

  it("counts a withdrawal negative so it nets against contributions", async () => {
    // The $25,000 Raiz withdrawal that made March 2026 read as a $38,615.34
    // investing month when it was net -$11,384.66.
    const id = await scratchTxn(
      "transaction_date, description, amount, transaction_type, category",
      "'2026-03-19','Investment fixture — withdrawal', 25000.00, 'credit', 'investment'"
    );
    expect(await signedValue(id)).toBe(-25000);
  });

  it("a deposit and an equal withdrawal net to zero", async () => {
    const inId = await scratchTxn(
      "transaction_date, description, amount, transaction_type, category",
      "'2026-03-01','Investment fixture — net in', 1000.00, 'debit', 'investment'"
    );
    const outId = await scratchTxn(
      "transaction_date, description, amount, transaction_type, category",
      "'2026-03-02','Investment fixture — net out', 1000.00, 'credit', 'investment'"
    );
    expect(await signedValue(inId) + await signedValue(outId)).toBe(0);
  });

  it("prefers amount_aud over the native amount", async () => {
    // The IBKR rows are USD; the line is denominated in AUD.
    const id = await scratchTxn(
      "transaction_date, description, amount, amount_aud, transaction_type, category",
      "'2026-07-25','Investment fixture — foreign', 10000.00, 14310.00, 'debit', 'investment'"
    );
    expect(await signedValue(id)).toBe(14310);
  });

  it("treats a refund like a withdrawal", async () => {
    const id = await scratchTxn(
      "transaction_date, description, amount, transaction_type, category",
      "'2026-03-01','Investment fixture — reversal', 1500.00, 'refund', 'investment'"
    );
    expect(await signedValue(id)).toBe(-1500);
  });
});
