import { inflateRawSync } from 'node:zlib';
import type { AlertItem, BudgetYear, MonthsReport, Overview, Subscription, SubscriptionDetail } from '@spendlight/shared';
import { afterEach, beforeEach, describe, expect, it } from 'vitest';
import { today } from '../src/dates';
import { buildCsv } from '../src/export/xlsx';
import { fitCadence } from '../src/subscriptions/engine';
import { makeApp, setUpAdmin, type Client, type TestApp } from './helpers';
import { chaseCsv, checkingCsv, discoverCsv } from './synthetic';

// Milestone 2: budgets, reports, subscriptions, alerts, management and export,
// all on made-up statements (see synthetic.ts).

let t: TestApp;
let admin: Client;
let ids: { checking: number; discover: number; chase: number };
let imports: { discover: number; chase: number; checking: number };

const groups = async () => (await admin.get('/api/v1/categories?includeArchived=true')).json as { id: number; name: string; categories: { id: number; name: string; archived: boolean }[] }[];
const category = async (name: string) => (await groups()).flatMap((g) => g.categories).find((c) => c.name === name)!.id;
const subs = async () => (await admin.get('/api/v1/subscriptions')).json as Subscription[];
const sub = async (name: string) => (await subs()).find((s) => s.name === name)!;
const alerts = async (area?: string) => (await admin.get(`/api/v1/alerts${area ? `?area=${area}` : ''}`)).json as AlertItem[];

async function importText(name: string, text: string, accountId: number) {
  const preview = await admin.upload('/api/v1/imports/files', name, text);
  expect(preview.status).toBe(200);
  const done = await admin.post('/api/v1/imports', { stagingId: preview.json.stagingId, accountId });
  expect(done.status).toBe(200);
  return done.json.id as number;
}

beforeEach(async () => {
  t = await makeApp();
  admin = await setUpAdmin(t);
  const add = async (body: Record<string, unknown>) => (await admin.post('/api/v1/accounts', body)).json.id as number;
  ids = {
    checking: await add({ name: 'Checking', kind: 'checking', institution: 'Bank of America', last4: '1234' }),
    discover: await add({ name: 'Discover', kind: 'credit_card', institution: 'Discover' }),
    chase: await add({ name: 'Prime Visa', kind: 'credit_card', institution: 'Chase', last4: '9876' }),
  };
  // Filed by hand once, so the big Best Buy purchase has a typical Electronics purchase to compare with.
  await admin.post('/api/v1/rules', { merchant: 'Micro Center', categoryId: await category('Electronics') });
  imports = {
    discover: await importText('Discover-2026.csv', discoverCsv(), ids.discover),
    chase: await importText('Chase9876_Activity.csv', chaseCsv(), ids.chase),
    checking: await importText('stmt.csv', checkingCsv(), ids.checking),
  };
});
afterEach(() => t.cleanup());

describe('budgets', () => {
  it('sets a monthly amount, a one-month exception, and "from this month on"', async () => {
    const gifts = await category('Gifts');
    let year = (await admin.put('/api/v1/budgets', { categoryId: gifts, year: 2026, month: 1, amountCents: 10000, scope: 'year' })).json as BudgetYear;
    expect(year.items).toEqual([{ categoryId: gifts, monthly: 10000, months: Array(12).fill(null) }]);

    year = (await admin.put('/api/v1/budgets', { categoryId: gifts, year: 2026, month: 12, amountCents: 80000, scope: 'month' })).json;
    expect(year.items[0]!.months[11]).toBe(80000);

    // October onward becomes $150; January to September keep $100. December's own amount gives way.
    year = (await admin.put('/api/v1/budgets', { categoryId: gifts, year: 2026, month: 10, amountCents: 15000, scope: 'rest' })).json;
    const line = year.items[0]!;
    const effective = line.months.map((m) => m ?? line.monthly);
    expect(effective).toEqual([...Array(9).fill(10000), 15000, 15000, 15000]);

    // Setting a month to the yearly amount leaves no needless exception behind.
    year = (await admin.put('/api/v1/budgets', { categoryId: gifts, year: 2026, month: 3, amountCents: 15000, scope: 'month' })).json;
    expect(year.items[0]!.months[2]).toBeNull();

    // Zero for every month removes the budget.
    year = (await admin.put('/api/v1/budgets', { categoryId: gifts, year: 2026, month: 1, amountCents: 0, scope: 'year' })).json;
    expect(year.items).toEqual([]);
  });

  it('fills empty categories from a 3-month average and copies a year forward', async () => {
    const groceries = await category('Groceries');
    const mine = await category('Dining Out');
    await admin.put('/api/v1/budgets', { categoryId: mine, year: 2026, month: 1, amountCents: 12345, scope: 'year' });
    const filled = await admin.post('/api/v1/budgets/fill', { month: '2026-10', source: 'average3' });
    expect(filled.status).toBe(200);
    expect(filled.json.filled).toBeGreaterThan(3);
    const lines = new Map((filled.json.budgets as BudgetYear).items.map((l) => [l.categoryId, l]));
    // Categories already budgeted are left alone; the rest round to $5 steps.
    expect(lines.get(mine)!.monthly).toBe(12345);
    const report = (await admin.get('/api/v1/reports/months?from=2026-07&to=2026-09')).json as MonthsReport;
    const actual = report.categories.find((c) => c.categoryId === groceries)!.actual;
    const average = actual.reduce((a, b) => a + b, 0) / 3;
    expect(lines.get(groceries)!.monthly).toBe(Math.ceil(average / 500) * 500);
    // Transfers never get a budget.
    expect(lines.has(await category('Credit Card Payment'))).toBe(false);

    const copied = await admin.post('/api/v1/budgets/copy-year', { from: 2026, to: 2027 });
    expect(copied.json.copied).toBe(filled.json.filled + 1);
    expect((await admin.put('/api/v1/budgets', { categoryId: await category('Credit Card Payment'), year: 2026, month: 1, amountCents: 100, scope: 'year' })).status).toBe(400);
  });
});

describe('reports', () => {
  it('totals each month by category: refunds reduce spending, transfers never count, splits count by part', async () => {
    const report = (await admin.get('/api/v1/reports/months?from=2026-01&to=2026-09')).json as MonthsReport;
    expect(report.months).toHaveLength(9);
    expect(report.dataThrough).toBe('2026-09-29');
    // Income: 19 or 20 paychecks of $2,450 across nine months.
    const income = report.totals.income.reduce((a, b) => a + b, 0);
    expect(income % 245000).toBe(0);
    // Card payments are paired transfers, so no month counts them as spending.
    const spentSep = report.totals.spent[8]!;
    const page = (await admin.get('/api/v1/transactions?from=2026-09-01&to=2026-09-30&limit=500')).json;
    expect(spentSep).toBe(-page.sums.outCents);

    // Split a September Kroger trip: $20 of it was Household Supplies.
    const kroger = page.items.find((x: { merchant: string }) => x.merchant === 'Kroger');
    const groceries = await category('Groceries');
    const household = await category('Household Supplies');
    const before = report.categories.find((c) => c.categoryId === groceries)!.actual[8]!;
    await admin.call('PUT', `/api/v1/transactions/${kroger.id}/split`, {
      parts: [
        { categoryId: groceries, amountCents: kroger.amountCents + 2000 },
        { categoryId: household, amountCents: -2000 },
      ],
    });
    const after = (await admin.get('/api/v1/reports/months?from=2026-09&to=2026-09')).json as MonthsReport;
    expect(after.categories.find((c) => c.categoryId === groceries)!.actual[0]).toBe(before - 2000);
    expect(after.categories.find((c) => c.categoryId === household)!.actual[0]).toBe(2000);
    expect(after.totals.spent[0]).toBe(spentSep);
  });

  it('shows the latest month with data on Home while statements catch up, and paces the year', async () => {
    const gifts = await category('Groceries');
    await admin.put('/api/v1/budgets', { categoryId: gifts, year: 2026, month: 1, amountCents: 30000, scope: 'year' });
    const o = (await admin.get('/api/v1/reports/overview')).json as Overview;
    if (today() > '2026-09-30') {
      expect(o.month).toBe('2026-09');
      expect(o.isCurrent).toBe(false);
    }
    expect(o.hasBudgets).toBe(true);
    expect(o.budgetSpend).toBe(30000);
    expect(o.year.budgetYear).toBe(360000);
    expect(o.year.budgetSoFar).toBe(270000);
    // Spending is far above this one small budget, so the projection runs over it.
    expect(o.year.projected).toBeGreaterThan(o.year.budgetYear);
    expect(o.categories[0]!.spent).toBeGreaterThanOrEqual(o.categories.at(-1)!.spent);
  });

  it('lists merchants, balances and tags', async () => {
    const merchants = (await admin.get('/api/v1/reports/merchants?from=2026-01-01&to=2026-09-30')).json as { merchant: string; spent: number; count: number }[];
    expect(merchants[0]!.merchant).toBe('Rocket Mortgage');
    expect(merchants.find((m) => m.merchant === 'Starbucks')!.count).toBeGreaterThan(60);
    expect(merchants.some((m) => /payment/i.test(m.merchant))).toBe(false);

    const balances = (await admin.get('/api/v1/reports/balances?months=12')).json as { months: string[]; accounts: { accountId: number; balances: (number | null)[] }[] };
    const checking = balances.accounts.find((a) => a.accountId === ids.checking)!;
    const sep = balances.months.indexOf('2026-09');
    expect(sep).toBeGreaterThan(-1);
    // The statement's running balance anchors checking; the cards have no known balance yet.
    expect(checking.balances[sep]).toBeTypeOf('number');
    expect(balances.accounts.find((a) => a.accountId === ids.discover)!.balances.every((b) => b === null)).toBe(true);

    const page = (await admin.get('/api/v1/transactions?q=Joe&limit=3')).json;
    for (const x of page.items) await admin.patch(`/api/v1/transactions/${x.id}`, { tags: ['Birthday'] });
    const tags = (await admin.get('/api/v1/reports/tags')).json as { tag: string; spent: number; count: number }[];
    expect(tags[0]).toMatchObject({ tag: 'Birthday', count: 3 });
    expect(tags[0]!.spent).toBe(-page.items.reduce((a: number, x: { amountCents: number }) => a + x.amountCents, 0));
  });
});

describe('subscriptions', () => {
  it('finds subscriptions and bills, one entry per service even across cards', async () => {
    const list = await subs();
    const byName = new Map(list.map((s) => [s.name, s]));
    expect([...byName.keys()].sort()).toEqual(['Costco membership', 'Disney+', 'Duke Energy', 'Hulu', 'Netflix', 'Rocket Mortgage', 'Spotify']);
    expect(byName.get('Netflix')).toMatchObject({ kind: 'subscription', cadence: 'monthly', expectedCents: 1799, chargeCount: 10, status: 'suggested', confidence: 'confident' });
    expect(byName.get('Costco membership')).toMatchObject({ cadence: 'yearly', expectedCents: 6500, chargeCount: 1, monthlyCents: 542 });
    expect(byName.get('Duke Energy')).toMatchObject({ kind: 'bill', chargeCount: 9 });
    expect(byName.get('Rocket Mortgage')).toMatchObject({ kind: 'bill', expectedCents: 185000 });
    expect(byName.get('Disney+')).toMatchObject({ confidence: 'likely', chargeCount: 2, manageUrl: 'https://www.disneyplus.com/account' });

    const netflix = (await admin.get(`/api/v1/subscriptions/${byName.get('Netflix')!.id}`)).json as SubscriptionDetail;
    expect(netflix.priceHistory).toEqual([
      { from: '2026-01-12', amountCents: 1549 },
      { from: '2026-07-12', amountCents: 1799 },
    ]);
    expect(new Set(netflix.charges.map((c) => c.accountId))).toEqual(new Set([ids.discover, ids.chase]));
    // Each find asks once.
    expect((await alerts('subscriptions')).filter((a) => a.kind === 'sub_found')).toHaveLength(7);
  });

  it('raises alerts once confirmed: price change, two cards, charged after cancelling', async () => {
    const netflix = await sub('Netflix');
    await admin.patch(`/api/v1/subscriptions/${netflix.id}`, { status: 'active' });
    const hulu = await sub('Hulu');
    await admin.patch(`/api/v1/subscriptions/${hulu.id}`, { status: 'cancelled', cancelledOn: '2026-08-25' });

    const list = await alerts('subscriptions');
    const kinds = list.map((a) => a.kind);
    expect(kinds).toContain('sub_two_cards');
    expect(kinds).toContain('sub_after_cancel');
    expect(list.find((a) => a.kind === 'sub_after_cancel')!.title).toBe('Hulu charged Discover after you marked it cancelled');
    // The most urgent comes first.
    expect(list[0]!.kind).toBe('sub_after_cancel');
    // The July price rise is old news by September; recent rises raise an alert.
    expect(kinds).not.toContain('sub_price');

    const counts = (await admin.get('/api/v1/alerts/counts')).json;
    expect(counts.subscriptions).toBe(list.length);
    await admin.post('/api/v1/alerts/read', { area: 'subscriptions' });
    expect((await admin.get('/api/v1/alerts/counts')).json.subscriptions).toBe(0);

    const dismissed = list.find((a) => a.kind === 'sub_two_cards')!;
    await admin.post(`/api/v1/alerts/${dismissed.id}/dismiss`);
    expect((await alerts('subscriptions')).some((a) => a.id === dismissed.id)).toBe(false);
  });

  it('remembers "not a subscription", adds by hand, merges, and follows an undone import', async () => {
    const disney = await sub('Disney+');
    await admin.patch(`/api/v1/subscriptions/${disney.id}`, { status: 'dismissed' });
    await admin.post('/api/v1/subscriptions/scan');
    expect((await subs()).some((s) => s.name === 'Disney+')).toBe(false);

    const added = await admin.post('/api/v1/subscriptions', { name: 'Car wash club', cadence: 'monthly', expectedCents: 2999, nextExpectedOn: '2026-10-15' });
    expect(added.status).toBe(200);
    expect(added.json).toMatchObject({ source: 'manual', status: 'active', chargeCount: 0 });
    const upcoming = (await admin.get('/api/v1/subscriptions/upcoming?days=120')).json as { name: string }[];
    expect(upcoming.some((u) => u.name === 'Car wash club')).toBe(true);

    const spotify = await sub('Spotify');
    const merged = await admin.post(`/api/v1/subscriptions/${added.json.id}/merge`, { intoId: spotify.id });
    expect(merged.json.aliases).toEqual(['Car wash club']);
    expect((await subs()).some((s) => s.name === 'Car wash club')).toBe(false);

    // Undo the Chase import: Disney+'s charges go with it and the second Netflix card disappears.
    await admin.del(`/api/v1/imports/${imports.chase}`);
    const netflix = (await admin.get(`/api/v1/subscriptions/${(await sub('Netflix')).id}`)).json as SubscriptionDetail;
    expect(netflix.charges.every((c) => c.accountId === ids.discover)).toBe(true);
    expect(netflix.chargeCount).toBe(9);
  });

  it('files every charge when a subscription’s category changes', async () => {
    const spotify = await sub('Spotify');
    const streaming = await category('Streaming and Subscriptions');
    await admin.patch(`/api/v1/subscriptions/${spotify.id}`, { status: 'active', categoryId: streaming });
    const charges = (await admin.get(`/api/v1/transactions?subscriptionId=${spotify.id}&limit=50`)).json;
    expect(charges.total).toBe(9);
    expect(charges.items.every((x: { categoryId: number; needsReview: boolean }) => x.categoryId === streaming && !x.needsReview)).toBe(true);
  });

  it('reads rhythms, allowing a skipped month', () => {
    expect(fitCadence(['2026-01-05', '2026-02-05', '2026-03-06', '2026-05-05'])).toBe('monthly');
    expect(fitCadence(['2025-03-10', '2026-03-09'])).toBe('yearly');
    expect(fitCadence(['2026-01-05', '2026-01-09', '2026-01-20'])).toBeNull();
  });
});

describe('money alerts', () => {
  it('flags an unusual purchase and a month over budget, set against the year', async () => {
    const list = await alerts('money');
    const unusual = list.find((a) => a.kind === 'unusual')!;
    expect(unusual.title).toBe('Unusual: $480.00 at Best Buy');
    expect(unusual.body).toMatch(/times your typical Electronics purchase/);

    const dining = await category('Dining Out');
    await admin.put('/api/v1/budgets', { categoryId: dining, year: 2026, month: 1, amountCents: 1000, scope: 'year' });
    const after = await alerts('money');
    const over = after.find((a) => a.kind === 'month_category_over');
    if (today() > '2026-09-30') {
      expect(over!.title).toMatch(/^Dining Out ran \$\d+(\.\d\d)? over in September$/);
      expect(over!.body).toMatch(/over budget for the year/);
      expect(after.find((a) => a.kind === 'year_pace')!.title).toMatch(/^On pace to finish 2026 .* over budget$/);
    }
  });

  it('warns while a month is running when a category nears its budget', async () => {
    const now = today();
    const coffee = await category('Coffee and Snacks');
    const add = await admin.post('/api/v1/transactions', { accountId: ids.checking, date: now, amountCents: -9000, merchant: 'Corner Cafe', categoryId: coffee });
    expect(add.status).toBe(200);
    await admin.put('/api/v1/budgets', { categoryId: coffee, year: Number(now.slice(0, 4)), month: Number(now.slice(5, 7)), amountCents: 10000, scope: 'month' });
    const list = await alerts('money');
    const left = new Date(Number(now.slice(0, 4)), Number(now.slice(5, 7)), 0).getDate() - Number(now.slice(8, 10));
    const close = list.find((a) => a.kind === 'budget_close' || a.kind === 'budget_over');
    if (left > 0) expect(close?.title).toMatch(/^Coffee and Snacks is at \d+% of budget with \d+ days? left$/);
  });
});

describe('managing categories, rules and tags', () => {
  it('adds, renames, archives and merges categories, carrying budgets along', async () => {
    const home = (await groups()).find((g) => g.name === 'Home')!;
    const added = await admin.post('/api/v1/categories', { groupId: home.id, name: 'Lawn Care', icon: 'wrench', color: 'emerald' });
    expect(added.json).toMatchObject({ name: 'Lawn Care', kind: 'expense', groupName: 'Home' });
    expect((await admin.post('/api/v1/categories', { groupId: home.id, name: 'lawn care' })).status).toBe(400);

    const repairs = await category('Repairs and Maintenance');
    await admin.put('/api/v1/budgets', { categoryId: added.json.id, year: 2026, month: 1, amountCents: 5000, scope: 'year' });
    await admin.put('/api/v1/budgets', { categoryId: repairs, year: 2026, month: 1, amountCents: 10000, scope: 'year' });
    const page = (await admin.get('/api/v1/transactions?q=Joe&limit=1')).json;
    await admin.patch(`/api/v1/transactions/${page.items[0].id}`, { categoryId: added.json.id });

    const merged = await admin.post(`/api/v1/categories/${added.json.id}/merge`, { intoId: repairs });
    expect(merged.json.moved).toBe(1);
    const budgets = (await admin.get('/api/v1/budgets?year=2026')).json as BudgetYear;
    expect(budgets.items.find((l) => l.categoryId === repairs)!.monthly).toBe(15000);
    expect((await admin.get(`/api/v1/transactions/${page.items[0].id}`)).json.categoryId).toBe(repairs);
    // Merging spending into income makes no sense.
    expect((await admin.post(`/api/v1/categories/${repairs}/merge`, { intoId: await category('Paycheck') })).status).toBe(400);

    await admin.patch(`/api/v1/categories/${repairs}`, { name: 'Home Repairs', archived: true });
    const active = (await admin.get('/api/v1/categories')).json as { categories: { name: string }[] }[];
    expect(active.flatMap((g) => g.categories).some((c) => c.name === 'Home Repairs')).toBe(false);
    expect((await groups()).flatMap((g) => g.categories).find((c) => c.id === repairs)).toMatchObject({ name: 'Home Repairs', archived: true });

    expect((await admin.del(`/api/v1/category-groups/${home.id}`)).status).toBe(400);
    const empty = await admin.post('/api/v1/category-groups', { name: 'Side business' });
    expect((await admin.del(`/api/v1/category-groups/${empty.json.id}`)).status).toBe(200);
  });

  it('counts rule use on import, edits rules, and merges tags on rename', async () => {
    const rules = (await admin.get('/api/v1/rules')).json as { id: number; merchant: string; hits: number; lastUsedAt: string | null }[];
    const micro = rules.find((r) => r.merchant === 'Micro Center')!;
    expect(micro.hits).toBe(1);
    expect(micro.lastUsedAt).not.toBeNull();
    const edited = await admin.patch(`/api/v1/rules/${micro.id}`, { renameTo: 'Micro Center Electronics' });
    expect(edited.json.renameTo).toBe('Micro Center Electronics');
    expect((await admin.patch(`/api/v1/rules/${micro.id}`, { categoryId: null, renameTo: null })).status).toBe(400);

    const page = (await admin.get('/api/v1/transactions?q=Kroger&limit=2')).json;
    await admin.patch(`/api/v1/transactions/${page.items[0].id}`, { tags: ['Party'] });
    await admin.patch(`/api/v1/transactions/${page.items[1].id}`, { tags: ['Parties'] });
    let tags = (await admin.get('/api/v1/tags')).json as { id: number; name: string; count: number }[];
    const party = tags.find((x) => x.name === 'Parties')!;
    await admin.patch(`/api/v1/tags/${party.id}`, { name: 'party' });
    tags = (await admin.get('/api/v1/tags')).json;
    expect(tags).toEqual([{ id: expect.any(Number), name: 'Party', count: 2 }]);
  });
});

describe('tags on split parts', () => {
  it('finds, totals and exports a tag on one part of a purchase', async () => {
    const page = (await admin.get('/api/v1/transactions?q=Kroger&limit=1')).json;
    const kroger = page.items[0];
    const groceries = await category('Groceries');
    const household = await category('Household Supplies');
    const split = await admin.call('PUT', `/api/v1/transactions/${kroger.id}/split`, {
      parts: [
        { categoryId: groceries, amountCents: kroger.amountCents + 1500 },
        { categoryId: household, amountCents: -1500, tags: ['Reimbursable'] },
      ],
    });
    expect(split.json.splits.map((x: { tags: string[] }) => x.tags)).toEqual([[], ['Reimbursable']]);

    const found = (await admin.get('/api/v1/transactions?tag=reimbursable')).json;
    expect(found.items.map((x: { id: number }) => x.id)).toEqual([kroger.id]);
    const report = (await admin.get('/api/v1/reports/tags')).json as { tag: string; spent: number; count: number; byCategory: { categoryId: number }[] }[];
    expect(report.find((r) => r.tag === 'Reimbursable')).toMatchObject({ spent: 1500, count: 1, byCategory: [{ categoryId: household, cents: 1500 }] });
    expect((await admin.get('/api/v1/tags')).json).toEqual([{ id: expect.any(Number), name: 'Reimbursable', count: 1 }]);

    // Tagging the whole purchase too doesn't count the tagged part twice.
    await admin.patch(`/api/v1/transactions/${kroger.id}`, { tags: ['Reimbursable'] });
    const both = (await admin.get('/api/v1/reports/tags')).json as { tag: string; spent: number }[];
    expect(both.find((r) => r.tag === 'Reimbursable')!.spent).toBe(-kroger.amountCents);

    const res = await t.app.request(`http://localhost/api/v1/export/transactions.csv?tag=Reimbursable`, { headers: { Cookie: `sl_session=${admin.cookie('sl_session')}` } });
    const lines = Buffer.from(await res.arrayBuffer()).subarray(3).toString('utf8').trim().split('\r\n');
    expect(lines).toHaveLength(3);
    expect(lines.every((l, i) => i === 0 || l.includes('Reimbursable'))).toBe(true);
  });
});

describe('export', () => {
  it('writes CSV that defuses formulas and splits rows by part', async () => {
    expect(buildCsv(['A', 'B'], [['=HYPERLINK("x")', { money: -1234 }], ['a,b', 5]])).toBe('\uFEFFA,B\r\n"\'=HYPERLINK(""x"")",-12.34\r\n"a,b",5\r\n');
    const res = await t.app.request('http://localhost/api/v1/export/transactions.csv?q=Netflix', { headers: { Cookie: `sl_session=${admin.cookie('sl_session')}` } });
    expect(res.status).toBe(200);
    expect(res.headers.get('content-disposition')).toMatch(/attachment; filename="spendlight-transactions-\d{4}-\d\d-\d\d\.csv"/);
    const bytes = Buffer.from(await res.arrayBuffer());
    // Excel needs the byte-order mark to read accents and symbols correctly.
    expect([...bytes.subarray(0, 3)]).toEqual([0xef, 0xbb, 0xbf]);
    const lines = bytes.subarray(3).toString('utf8').trim().split('\r\n');
    expect(lines[0]).toBe('Date,Account,Merchant,Category,Group,Amount,Split of,Transfer with,Tags,Note,Subscription,Status,Bank description');
    expect(lines).toHaveLength(11);
  });

  it('writes a real Excel workbook with every part Excel needs', async () => {
    const res = await t.app.request('http://localhost/api/v1/export/everything.xlsx', { headers: { Cookie: `sl_session=${admin.cookie('sl_session')}` } });
    expect(res.status).toBe(200);
    const zip = Buffer.from(await res.arrayBuffer());
    const files = unzip(zip);
    expect([...files.keys()].sort()).toEqual([
      '[Content_Types].xml',
      '_rels/.rels',
      'docProps/app.xml',
      'docProps/core.xml',
      'xl/_rels/workbook.xml.rels',
      'xl/styles.xml',
      'xl/workbook.xml',
      'xl/worksheets/sheet1.xml',
      'xl/worksheets/sheet2.xml',
      'xl/worksheets/sheet3.xml',
      'xl/worksheets/sheet4.xml',
      'xl/worksheets/sheet5.xml',
      'xl/worksheets/sheet6.xml',
    ]);
    expect(files.get('xl/workbook.xml')).toContain('<sheet name="Subscriptions" sheetId="5" r:id="rId5"/>');
    const sheet = files.get('xl/worksheets/sheet1.xml')!;
    expect(sheet).toContain('<pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/>');
    expect(sheet).toMatch(/<c r="F2" s="2"><v>-?\d+\.\d\d<\/v><\/c>/);
    expect(sheet).toMatch(/<c r="A2" s="3"><v>46\d\d\d<\/v><\/c>/);
  });
});

describe('roles and AI', () => {
  it('keeps budgets read-only for read-only people and AI off without a provider', async () => {
    await admin.post('/api/v1/people', { username: 'reader', displayName: 'Reader', access: 'read', isAdmin: false, password: 'reader-password-1' });
    const { Client } = await import('./helpers');
    const reader = new Client(t);
    await reader.post('/api/v1/auth/login', { username: 'reader', password: 'reader-password-1' });
    expect((await reader.get('/api/v1/reports/overview')).status).toBe(200);
    expect((await reader.put('/api/v1/budgets', { categoryId: 1, year: 2026, month: 1, amountCents: 100, scope: 'year' })).status).toBe(403);
    expect((await reader.get('/api/v1/ai')).status).toBe(403);

    const status = (await admin.get('/api/v1/ai')).json;
    expect(status.providerConfigured).toBe(false);
    expect(status.features.every((f: { enabled: boolean }) => !f.enabled)).toBe(true);
    const refused = await admin.patch('/api/v1/ai', { feature: 'categorize', enabled: true });
    expect(refused.status).toBe(400);
    expect(refused.json.error.code).toBe('no_provider');
  });
});

/** Read a zip's entries, enough to check what buildXlsx wrote. */
function unzip(buf: Buffer): Map<string, string> {
  const out = new Map<string, string>();
  const end = buf.lastIndexOf(Buffer.from([0x50, 0x4b, 0x05, 0x06]));
  const entries = buf.readUInt16LE(end + 10);
  let p = buf.readUInt32LE(end + 16);
  for (let i = 0; i < entries; i++) {
    const nameLength = buf.readUInt16LE(p + 28);
    const extra = buf.readUInt16LE(p + 30);
    const comment = buf.readUInt16LE(p + 32);
    const offset = buf.readUInt32LE(p + 42);
    const name = buf.subarray(p + 46, p + 46 + nameLength).toString('utf8');
    const size = buf.readUInt32LE(p + 20);
    const local = offset + 30 + buf.readUInt16LE(offset + 26) + buf.readUInt16LE(offset + 28);
    out.set(name, inflateRawSync(buf.subarray(local, local + size)).toString('utf8'));
    p += 46 + nameLength + extra + comment;
  }
  return out;
}