import { describe, it, expect, beforeAll } from "vitest";
import { queryRaw, queryRow } from "../../lib/db";
import {
  EXCLUDE_RECONCILED_SOURCE,
  NATIVE_CURRENCY,
  AMOUNT_UNCONVERTED,
  INVESTMENT_SIGNED,
  MY_SPEND_SCOPE,
  NET_SPEND_ROWS,
  SPEND_SIGNED,
  myShare,
  mySplitOf,
} from "../../lib/analytics-sql";
import { MY_SHARE_PCT } from "../../lib/queries";

/**
 * 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.
    // owner_id is NOT NULL DEFAULT 1 with an FK to participants, so this insert
    // relied on a participant with id 1 already existing — true only when a
    // sibling test file had seeded one and left it behind. On a freshly reset
    // database it failed the FK. Own the fixture instead of inheriting it.
    const owner = await queryRow<{ id: number }>(
      `INSERT INTO participants (name) VALUES ('Analytics fixture — owner') RETURNING id`
    );
    const stmt = await queryRow<{ id: number }>(
      `INSERT INTO statements (bank_name, account_number, billing_end_date, currency, filename, owner_id)
       VALUES ('Analytics Fixture Bank','0000','2026-03-31','AUD','analytics-fixture.pdf', $1) RETURNING id`,
      [owner!.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);
  });
});

/**
 * Whose expense is it — the gate that decides, and the share that scales it.
 *
 * Both fragments changed together on 2026-08-15 and neither is safe alone.
 * `OWNER_SCOPE = $1` was the gate on every spend analytic, with `mySplitOf`
 * scaling within it, so a row someone else paid for contributed nothing to my
 * spend no matter what share I held — 167 rows / $3,210.91 of my own share
 * missing from Jan–Jul 2026, while the balance queries booked the debt
 * correctly. Widening the gate without also fixing `myShare` is worse than
 * leaving it: the old remainder fallback hands the viewer the PAYER's share on
 * a row with no split of theirs, which on an unsplit row is the full amount.
 *
 * So the discriminating cases here are all ones where I hold NO split row on
 * someone else's transaction. That is where old and new disagree, and it is the
 * only place the widened gate could do damage.
 */
describe("MY_SPEND_SCOPE / myShare", () => {
  let me = 0;
  let them = 0;
  let third = 0;

  beforeAll(async () => {
    const mk = async (name: string) =>
      (await queryRow<{ id: number }>(
        `INSERT INTO participants (name) VALUES ($1) RETURNING id`,
        [name]
      ))!.id;
    me = await mk("Share fixture — viewer");
    them = await mk("Share fixture — payer");
    third = await mk("Share fixture — third party");
  });

  /** A transaction owned by `owner`, optionally split, optionally overridden. */
  async function fixture(
    label: string,
    owner: number,
    splits: [number, number][] = [],
    myShareOverride?: number
  ) {
    const id = await scratchTxn(
      "transaction_date, description, amount, transaction_type, category, owner_id",
      `'2026-06-01', $1, 100.00, 'debit', 'groceries', $2`,
      [`Share fixture — ${label}`, owner]
    );
    for (const [pid, pct] of splits) {
      await queryRaw(
        `INSERT INTO transaction_splits (transaction_id, participant_id, share_percent)
         VALUES ($1, $2, $3)`,
        [id, pid, pct]
      );
    }
    if (myShareOverride !== undefined) {
      await queryRaw(
        `INSERT INTO transaction_overrides (transaction_id, my_share_percent) VALUES ($1, $2)`,
        [id, myShareOverride]
      );
    }
    return id;
  }

  const JOINS = `
    FROM transactions t
    LEFT JOIN statements s ON s.id = t.statement_id
    LEFT JOIN transaction_overrides o ON o.transaction_id = t.id
    LEFT JOIN transaction_splits ts ON ts.transaction_id = t.id AND ts.participant_id = $1`;

  async function inScope(id: number, viewer: number): Promise<boolean> {
    const rows = await queryRaw(
      `SELECT t.id ${JOINS} WHERE t.id = $2 AND ${MY_SPEND_SCOPE()}`,
      [viewer, id]
    );
    return rows.length === 1;
  }

  async function share(id: number, viewer: number): Promise<number> {
    const row = await queryRow<{ v: string }>(
      `SELECT (${myShare()})::numeric AS v ${JOINS} WHERE t.id = $2`,
      [viewer, id]
    );
    return Number(row!.v);
  }

  /** What this row contributes to the viewer's net spend. */
  async function spend(id: number, viewer: number): Promise<number> {
    const row = await queryRow<{ v: string | null }>(
      `SELECT SUM(${mySplitOf(SPEND_SIGNED)})::numeric(14,4) AS v ${JOINS}
        WHERE t.id = $2 AND ${MY_SPEND_SCOPE()} AND ${NET_SPEND_ROWS}`,
      [viewer, id]
    );
    return Number(row?.v ?? 0);
  }

  describe("the gate", () => {
    it("admits a row someone else paid for that I hold a split on", async () => {
      const id = await fixture("their shop, my half", them, [[me, 50], [them, 50]]);
      expect(await inScope(id, me)).toBe(true);
    });

    it("still admits a row I paid for with no splits at all", async () => {
      const id = await fixture("mine alone", me);
      expect(await inScope(id, me)).toBe(true);
    });

    it("rejects a row someone else paid for that I have no split on", async () => {
      const id = await fixture("not mine", them, [[them, 60], [third, 40]]);
      expect(await inScope(id, me)).toBe(false);
    });
  });

  describe("my share of someone else's row", () => {
    it("is my split percent", async () => {
      const id = await fixture("their shop, my quarter", them, [[me, 25], [them, 75]]);
      expect(await share(id, me)).toBe(25);
    });

    it("is 0 with no split of mine — NOT the payer's remainder", async () => {
      // The load-bearing case. An unsplit row someone else paid for has no
      // `ts.share_percent`, so the old `100 - everyone else` fallback returned
      // 100 and would have moved their entire grocery bill onto my spend the
      // moment the gate widened.
      const id = await fixture("entirely theirs", them);
      expect(await share(id, me)).toBe(0);
      expect(await spend(id, me)).toBe(0);
    });

    it("ignores my_share_percent, which can only mean the owner's share", async () => {
      // One unscoped column per transaction, writable by anyone who can see the
      // row. Reading it as the viewer's share on someone else's transaction
      // would be a guess.
      const id = await fixture("their row, their override", them, [], 30);
      expect(await share(id, me)).toBe(0);
    });
  });

  describe("my share of my own row is unchanged", () => {
    it("prefers an explicit split row for me", async () => {
      const id = await fixture("mine, split explicitly", me, [[me, 35], [them, 65]]);
      expect(await share(id, me)).toBe(35);
    });

    it("falls back to the my_share_percent override", async () => {
      const id = await fixture("mine, overridden", me, [], 80);
      expect(await share(id, me)).toBe(80);
    });

    it("falls back to whatever is left after everyone else", async () => {
      const id = await fixture("mine, remainder", me, [[them, 40]]);
      expect(await share(id, me)).toBe(60);
    });

    it("is 0 when a row I paid for is allocated entirely to someone else", async () => {
      const id = await fixture("mine, all theirs", me, [[them, 100]]);
      expect(await share(id, me)).toBe(0);
    });
  });

  describe("the regression it was written for", () => {
    it("counts my half of a shop the other person paid for", async () => {
      const id = await fixture("the grocery shop", them, [[me, 50], [them, 50]]);
      expect(await spend(id, me)).toBe(50);
    });

    it("still counts my half of the same shop when I paid", async () => {
      const id = await fixture("the same shop, my card", me, [[me, 50], [them, 50]]);
      expect(await spend(id, me)).toBe(50);
    });

    it("counts my share whether or not the split is settled", async () => {
      // `myShare` must not filter on `settled` — half a shop was my expense
      // whether or not the other half was repaid. Same rule, payer-side.
      const id = await fixture("settled shop", them, [[me, 50], [them, 50]]);
      await queryRaw(`UPDATE transaction_splits SET settled = true WHERE transaction_id = $1`, [id]);
      expect(await spend(id, me)).toBe(50);
    });

    it("never counts one row for both people at full value", async () => {
      const id = await fixture("one shop, two people", them, [[me, 50], [them, 50]]);
      expect(await spend(id, me) + await spend(id, them)).toBe(100);
    });
  });

  describe("the transactions-list mirror agrees", () => {
    it("MY_SHARE_PCT matches myShare on every fixture shape", async () => {
      const shapes: [string, number, [number, number][], number | undefined][] = [
        ["mirror A", them, [[me, 25], [them, 75]], undefined],
        ["mirror B", them, [], undefined],
        ["mirror C", them, [], 30],
        ["mirror D", me, [[me, 35], [them, 65]], undefined],
        ["mirror E", me, [], 80],
        ["mirror F", me, [[them, 40]], undefined],
        ["mirror G", me, [], undefined],
      ];
      for (const [label, owner, splits, override] of shapes) {
        const id = await fixture(label, owner, splits, override);
        const mirrored = await queryRow<{ v: string }>(
          `SELECT (${MY_SHARE_PCT})::numeric AS v ${JOINS} WHERE t.id = $2`,
          [me, id]
        );
        expect([label, Number(mirrored!.v)]).toEqual([label, await share(id, me)]);
      }
    });
  });
});
