scan-receipts/tests/e2e/export_localization.test.ts

/**
 * Export-Lokalisierung und Trinkgeld in den KPI-Kacheln.
 *
 * Der deutsche Export ist der Bestand — Blattnamen und Kopfzeilen dürfen sich
 * nicht verändern, nur weil eine englische Variante dazugekommen ist.
 */

import ExcelJS from "exceljs";
import { describe, test, expect } from "./runner";
import { generateDualSheetExcel } from "../../src/lib/export/excelGenerator";
import { generateAccountingCsv } from "../../src/lib/export/csvGenerator";
import { calculateDashboardKPIs } from "../../src/components/dashboard/KPICards";
import { ProcessedReceipt } from "../../src/lib/schema/receipt";

function receipt(overrides: Partial<ProcessedReceipt> = {}): ProcessedReceipt {
  return {
    merchant: { name: "REWE", address: null, taxId: null, confidence: 0.97 },
    date: { isoDate: "2026-08-12", time: null, confidence: 0.95 },
    documentType: "KASSENBON",
    receiptNumber: "1",
    currency: "EUR",
    totalAmount: { value: 31.8, confidence: 0.98 },
    netAmount: 26.72,
    tipAmount: null,
    taxBreakdown: [{ ratePercent: 19, taxAmount: 5.08, netAmount: 26.72 }],
    lineItems: [],
    suggestedCategory: "Sonstiges",
    validation: {
      isMathValid: true,
      isDuplicateSuspected: false,
      needsUserReview: false,
      reviewField: "none",
      reviewReason: null,
    },
    id: "r1",
    imageHash: "h1",
    originalFileName: "r1.jpg",
    fileSizeBytes: 1024,
    createdAt: "2026-08-12T10:00:00.000Z",
    updatedAt: "2026-08-12T10:00:00.000Z",
    status: "ready",
    ...overrides,
  } as ProcessedReceipt;
}

async function headersOf(receipts: ProcessedReceipt[], locale?: "de" | "en") {
  const buf = await generateDualSheetExcel(receipts, locale ? { locale } : {});
  const wb = new ExcelJS.Workbook();
  await wb.xlsx.load(buf as unknown as ArrayBuffer);
  const out: string[] = [];
  wb.worksheets[0].getRow(1).eachCell((c) => out.push(String(c.value ?? "")));
  return { wb, headers: out };
}

describe("Export-Lokalisierung: Excel", () => {
  test("L-1: Standard ohne Option bleibt Deutsch", async () => {
    const { wb, headers } = await headersOf([receipt()]);
    expect(wb.worksheets[0].name).toBe("Belegübersicht");
    expect(wb.worksheets[1].name).toBe("Einzelpositionen Detail");
    expect(headers[2]).toBe("Händler / Aussteller");
  });

  test("L-2: locale 'en' übersetzt Blattnamen und Kopfzeile", async () => {
    const { wb, headers } = await headersOf([receipt()], "en");
    expect(wb.worksheets[0].name).toBe("Receipts");
    expect(wb.worksheets[1].name).toBe("Line items");
    expect(headers[2]).toBe("Merchant / Issuer");
    expect(headers.some((h) => h.startsWith("Gross total"))).toBe(true);
  });

  test("L-3: im englischen Export bleibt kein deutsches Label stehen", async () => {
    const { headers } = await headersOf([receipt({ tipAmount: 5 })], "en");
    const german = ["Netto", "Brutto", "Währung", "MwSt", "Händler", "Trinkgeld", "Plausibilität"];
    const leftovers = headers.filter((h) => german.some((g) => h.includes(g)));
    expect(leftovers).toEqual([]);
  });

  test("L-4: Datumsformat ist sprachabhängig, Geldformat nicht", async () => {
    const de = await generateDualSheetExcel([receipt()], { locale: "de" });
    const en = await generateDualSheetExcel([receipt()], { locale: "en" });
    const read = async (buf: Buffer) => {
      const wb = new ExcelJS.Workbook();
      await wb.xlsx.load(buf as unknown as ArrayBuffer);
      const row = wb.worksheets[0].getRow(2);
      return { date: row.getCell(2).numFmt, money: row.getCell(7).numFmt };
    };
    const a = await read(de);
    const b = await read(en);
    expect(a.date).toBe("DD.MM.YYYY");
    expect(b.date).toBe("YYYY-MM-DD");
    expect(a.money).toBe(b.money);
  });

  test("L-5: unbekannte Sprache fällt auf Deutsch zurück", async () => {
    const { wb } = await headersOf([receipt()], "fr" as unknown as "de");
    expect(wb.worksheets[0].name).toBe("Belegübersicht");
  });

  test("L-6: negative Beträge bekommen ein Rot-Format", async () => {
    const { wb } = await headersOf([receipt()]);
    expect(wb.worksheets[0].getRow(2).getCell(7).numFmt.includes("[Red]")).toBe(true);
  });

  test("L-7: Statusspalte ist farblich hinterlegt", async () => {
    const buf = await generateDualSheetExcel(
      [
        receipt(),
        receipt({
          id: "r2",
          validation: {
            isMathValid: false,
            isDuplicateSuspected: false,
            needsUserReview: true,
            reviewField: "totalAmount",
            reviewReason: "x",
            issues: [{ field: "totalAmount", severity: "error", message: "x" }],
          },
        }),
      ],
      { locale: "de" }
    );
    const wb = new ExcelJS.Workbook();
    await wb.xlsx.load(buf as unknown as ArrayBuffer);
    const sheet = wb.worksheets[0];
    const headers: string[] = [];
    sheet.getRow(1).eachCell((c) => headers.push(String(c.value ?? "")));
    const col = headers.indexOf("Plausibilität") + 1;
    const okFill = sheet.getRow(2).getCell(col).fill as { fgColor?: { argb?: string } };
    const warnFill = sheet.getRow(3).getCell(col).fill as { fgColor?: { argb?: string } };
    expect(okFill.fgColor?.argb).toBe("FFDCFCE7");
    expect(warnFill.fgColor?.argb).toBe("FFFEF3C7");
  });

  test("L-8: Kopfzeile und erste Spalten sind fixiert", async () => {
    const { wb } = await headersOf([receipt()]);
    const view = wb.worksheets[0].views[0] as { xSplit?: number; ySplit?: number };
    expect(view.ySplit).toBe(1);
    expect(view.xSplit).toBe(3);
  });
});

describe("Export-Lokalisierung: CSV", () => {
  test("L-9: Standard bleibt Deutsch", () => {
    const head = generateAccountingCsv([receipt()]).replace(/^/, "").split("\r\n")[0];
    expect(head.includes("Händler / Aussteller")).toBe(true);
    expect(head.includes("Umsatz Brutto")).toBe(true);
  });

  test("L-10: locale 'en' übersetzt die Kopfzeile", () => {
    const head = generateAccountingCsv([receipt({ tipAmount: 5 })], { locale: "en" })
      .replace(/^/, "")
      .split("\r\n")[0];
    expect(head.includes("Merchant / Issuer")).toBe(true);
    expect(head.includes("Total paid")).toBe(true);
    // Steuersatz englisch ohne Leerzeichen, wie in der Excel-Mappe.
    expect(head.includes("VAT 19%")).toBe(true);
    expect(head.includes("MwSt")).toBe(false);
  });

  test("L-11: Statuswerte sind übersetzt", () => {
    const rows = generateAccountingCsv([receipt()], { locale: "en" }).split("\r\n");
    expect(rows[1].includes("Valid")).toBe(true);
    expect(rows[1].includes("Valide")).toBe(false);
  });
});

describe("Kleinbetragsrechnung (§ 33 UStDV)", () => {
  async function cellFor(r: ProcessedReceipt, locale: "de" | "en" = "de") {
    const buf = await generateDualSheetExcel([r], { locale });
    const wb = new ExcelJS.Workbook();
    await wb.xlsx.load(buf as unknown as ArrayBuffer);
    const sheet = wb.worksheets[0];
    const headers: string[] = [];
    sheet.getRow(1).eachCell((c) => headers.push(String(c.value ?? "")));
    const col = headers.findIndex((h) => h.includes("§ 33")) + 1;
    return { value: String(sheet.getRow(2).getCell(col).value ?? ""), col, sheet };
  }

  const gross = (value: number, currency = "EUR") =>
    receipt({ totalAmount: { value, confidence: 0.98 }, currency, netAmount: null, taxBreakdown: [] });

  test("K-1: 250,00 € ist noch Kleinbetrag (Grenze inklusive)", async () => {
    expect((await cellFor(gross(250))).value).toBe("Ja");
  });

  test("K-2: 250,01 € ist keiner mehr", async () => {
    expect((await cellFor(gross(250.01))).value).toBe("Nein");
  });

  test("K-3: typischer Kassenbon ist Kleinbetrag", async () => {
    expect((await cellFor(gross(9.06))).value).toBe("Ja");
  });

  test("K-4: Trinkgeld zählt nicht in die Grenze", async () => {
    // Rechnungsbetrag 248 € + 10 € Tip = 258 € gezahlt, aber die Rechnung
    // selbst bleibt eine Kleinbetragsrechnung.
    const r = receipt({
      totalAmount: { value: 248, confidence: 0.98 },
      tipAmount: 10,
      netAmount: null,
      taxBreakdown: [],
    });
    expect((await cellFor(r)).value).toBe("Ja");
  });

  test("K-5: Gutschrift wird über den Betrag ohne Vorzeichen bewertet", async () => {
    expect((await cellFor(gross(-300))).value).toBe("Nein");
    expect((await cellFor(gross(-12.5))).value).toBe("Ja");
  });

  test("K-6: Fremdwährung bleibt leer statt geraten", async () => {
    expect((await cellFor(gross(100, "CHF"))).value).toBe("");
    expect((await cellFor(gross(100, "USD"))).value).toBe("");
  });

  test("K-7: englischer Export nutzt Yes/No", async () => {
    expect((await cellFor(gross(9.06), "en")).value).toBe("Yes");
    expect((await cellFor(gross(999), "en")).value).toBe("No");
  });

  test("K-8: Rechtsgrundlage hängt als Kommentar an der Überschrift", async () => {
    const { sheet, col } = await cellFor(gross(9.06));
    const note = sheet.getRow(1).getCell(col).note;
    const noteText = typeof note === "string" ? note : (note?.texts ?? []).map((t) => t.text).join("");
    expect(noteText.includes("§ 33 UStDV")).toBe(true);
    expect(noteText.includes("250")).toBe(true);
  });

  test("K-9: CSV führt dieselbe Spalte", () => {
    const csv = generateAccountingCsv([gross(9.06), gross(999), gross(50, "CHF")]);
    const rows = csv.replace(/^/, "").split("\r\n");
    const idx = rows[0].split(";").findIndex((h) => h.includes("§ 33"));
    expect(idx).toBeGreaterThan(-1);
    const valueAt = (row: string) => row.split(";")[idx].replace(/"/g, "");
    expect(valueAt(rows[1])).toBe("Ja");
    expect(valueAt(rows[2])).toBe("Nein");
    expect(valueAt(rows[3])).toBe("");
  });

  test("K-10: die Spalte verschiebt Steuernummer und Status nicht durcheinander", async () => {
    const { sheet, col } = await cellFor(gross(9.06));
    const headers: string[] = [];
    sheet.getRow(1).eachCell((c) => headers.push(String(c.value ?? "")));
    expect(headers[col]).toBe("Steuernummer / USt-IdNr.");
    expect(headers[col + 1]).toBe("Plausibilität");
  });
});

describe("KPI-Kacheln: Trinkgeld", () => {
  const withTips = [
    receipt({ id: "a", tipAmount: 5, totalAmount: { value: 31.8, confidence: 0.98 } }),
    receipt({ id: "b", tipAmount: 2.5, totalAmount: { value: 20, confidence: 0.98 } }),
    receipt({ id: "c", tipAmount: null, totalAmount: { value: 10, confidence: 0.98 } }),
  ];

  test("L-12: totalGross bleibt ohne Trinkgeld (MwSt-tragend)", () => {
    const k = calculateDashboardKPIs(withTips);
    expect(Number(k.totalGross.toFixed(2))).toBe(61.8);
  });

  test("L-13: totalTips summiert alle Trinkgelder", () => {
    expect(Number(calculateDashboardKPIs(withTips).totalTips.toFixed(2))).toBe(7.5);
  });

  test("L-14: totalPaid ist Brutto plus Trinkgeld", () => {
    expect(Number(calculateDashboardKPIs(withTips).totalPaid.toFixed(2))).toBe(69.3);
  });

  test("L-15: Monatsausgaben enthalten das Trinkgeld", () => {
    const k = calculateDashboardKPIs(withTips);
    expect(Number(k.monthlySpendPaid.toFixed(2))).toBe(
      Number((k.monthlySpend + k.monthlySpendTips).toFixed(2))
    );
    expect(k.monthlySpendPaid).toBeGreaterThan(k.monthlySpend);
  });

  test("L-16: ohne Trinkgeld bleiben die Kennzahlen unverändert", () => {
    const k = calculateDashboardKPIs([receipt({ tipAmount: null })]);
    expect(k.totalTips).toBe(0);
    expect(k.totalPaid).toBe(k.totalGross);
    expect(k.monthlySpendPaid).toBe(k.monthlySpend);
  });

  test("L-17: leerer Datensatz erzeugt keine NaN", () => {
    const k = calculateDashboardKPIs([]);
    expect(k.totalTips).toBe(0);
    expect(k.totalPaid).toBe(0);
    expect(k.monthlySpendPaid).toBe(0);
  });
});