import { NextFunction, Request, Response } from 'express';
import ExcelJS from 'exceljs';
import { RowDataPacket } from 'mysql2/promise';
import { pool } from '../config/db.js';
import { AuthRequest } from '../middleware/auth.middleware.js';
import { hasPermission } from '../services/permissionService.js';

/* ───────────────────────── types ───────────────────────── */

interface InvoiceRow extends RowDataPacket {
  id: number;
  project_id: number;
  category: string | null;
  invoice_no: string;
  invoice_date: string;
  invoice_value: string;
  gst_amount: string;
  tds_amount: string;
  retention_amount: string;
  advance_amount: string;
  hold_amount: string;
  penalty_amount: string;
  remarks: string | null;
}

interface PaymentRow extends RowDataPacket {
  id: number;
  project_id: number;
  invoice_id: number | null;
  invoice_no: string | null;
  payment_date: string;
  amount: string;
  reference: string | null;
  remarks: string | null;
}

interface ProjectHeaderRow extends RowDataPacket {
  id: number;
  job_code: string | null;
  name: string;
  client_name: string | null;
  location: string | null;
  customer_po_no: string | null;
  po_date: string | null;
  approval_status: string;
  gst_pct: string;
  tds_pct: string;
  retention_pct: string;
  advance_pct: string;
}

/* ───────────────────────── helpers ───────────────────────── */

const n = (v: unknown) => Number(v ?? 0) || 0;

/** Receivable = invoice value + GST − TDS − retention − advance recovery − hold − penalty. */
function invoiceDto(r: InvoiceRow, receivedAgainst = 0) {
  const invoiceValue = n(r.invoice_value);
  const gst = n(r.gst_amount);
  const total = invoiceValue + gst;
  const tds = n(r.tds_amount);
  const retention = n(r.retention_amount);
  const advance = n(r.advance_amount);
  const hold = n(r.hold_amount);
  const penalty = n(r.penalty_amount);
  const receivable = total - tds - retention - advance - hold - penalty;
  return {
    id: r.id,
    projectId: r.project_id,
    category: r.category,
    invoiceNo: r.invoice_no,
    invoiceDate: r.invoice_date,
    invoiceValue,
    gstAmount: gst,
    totalValue: total,
    tdsAmount: tds,
    retentionAmount: retention,
    advanceAmount: advance,
    holdAmount: hold,
    penaltyAmount: penalty,
    receivable,
    received: receivedAgainst,
    yetToReceive: receivable - receivedAgainst,
    remarks: r.remarks,
  };
}

function paymentDto(r: PaymentRow) {
  return {
    id: r.id,
    projectId: r.project_id,
    invoiceId: r.invoice_id,
    invoiceNo: r.invoice_no,
    paymentDate: r.payment_date,
    amount: n(r.amount),
    reference: r.reference,
    remarks: r.remarks,
  };
}

export interface ProjectInvoiceTotals {
  invoiceCount: number;
  invoiced: number; // excl. GST
  gst: number;
  totalWithGst: number;
  retention: number;
  receivable: number;
  collected: number;
  yetToReceive: number;
}

const EMPTY_TOTALS: ProjectInvoiceTotals = {
  invoiceCount: 0, invoiced: 0, gst: 0, totalWithGst: 0, retention: 0, receivable: 0, collected: 0, yetToReceive: 0,
};

/**
 * Cumulative invoice and collection figures per project, used by the Invoices list, the JCR Report
 * (Total Invoice / Total Collection), the S Curve cards and the project view.
 */
export async function invoiceTotalsByProject(projectIds?: number[]): Promise<Map<number, ProjectInvoiceTotals>> {
  const map = new Map<number, ProjectInvoiceTotals>();
  if (projectIds && projectIds.length === 0) return map;
  const scope = projectIds ? 'WHERE project_id IN (?)' : '';
  const params = projectIds ? [projectIds] : [];
  const [inv] = await pool.query<RowDataPacket[]>(
    `SELECT project_id, COUNT(*) AS cnt, SUM(invoice_value) AS invoiced, SUM(gst_amount) AS gst,
            SUM(retention_amount) AS retention,
            SUM(invoice_value + gst_amount - tds_amount - retention_amount - advance_amount - hold_amount - penalty_amount) AS receivable
     FROM project_invoices ${scope} GROUP BY project_id`,
    params,
  );
  const [pay] = await pool.query<RowDataPacket[]>(
    `SELECT project_id, SUM(amount) AS collected FROM project_payments ${scope} GROUP BY project_id`,
    params,
  );
  const get = (id: number) => {
    let t = map.get(id);
    if (!t) {
      t = { ...EMPTY_TOTALS };
      map.set(id, t);
    }
    return t;
  };
  for (const r of inv) {
    const t = get(r.project_id);
    t.invoiceCount = n(r.cnt);
    t.invoiced = n(r.invoiced);
    t.gst = n(r.gst);
    t.totalWithGst = t.invoiced + t.gst;
    t.retention = n(r.retention);
    t.receivable = n(r.receivable);
  }
  for (const r of pay) get(r.project_id).collected = n(r.collected);
  for (const t of map.values()) t.yetToReceive = t.receivable - t.collected;
  return map;
}

export function emptyInvoiceTotals(): ProjectInvoiceTotals {
  return { ...EMPTY_TOTALS };
}

async function loadProject(projectId: number) {
  const [rows] = await pool.query<ProjectHeaderRow[]>(
    `SELECT p.id, p.job_code, p.name, c.name AS client_name, p.location, p.customer_po_no, p.po_date, p.approval_status,
            p.gst_pct, p.tds_pct, p.retention_pct, p.advance_pct
     FROM projects p LEFT JOIN clients c ON c.id = p.client_id WHERE p.id = ?`,
    [projectId],
  );
  return rows[0];
}

async function loadRegister(projectId: number) {
  const [invoices] = await pool.query<InvoiceRow[]>(
    'SELECT * FROM project_invoices WHERE project_id = ? ORDER BY invoice_date ASC, id ASC',
    [projectId],
  );
  const [payments] = await pool.query<PaymentRow[]>(
    `SELECT pp.*, pi.invoice_no FROM project_payments pp
     LEFT JOIN project_invoices pi ON pi.id = pp.invoice_id
     WHERE pp.project_id = ? ORDER BY pp.payment_date ASC, pp.id ASC`,
    [projectId],
  );
  const receivedByInvoice = new Map<number, number>();
  for (const p of payments) {
    if (p.invoice_id) receivedByInvoice.set(p.invoice_id, (receivedByInvoice.get(p.invoice_id) ?? 0) + n(p.amount));
  }
  return {
    invoices: invoices.map((r) => invoiceDto(r, receivedByInvoice.get(r.id) ?? 0)),
    payments: payments.map(paymentDto),
  };
}

function projectHeaderDto(p: ProjectHeaderRow) {
  return {
    id: p.id,
    jobCode: p.job_code,
    name: p.name,
    clientName: p.client_name,
    location: p.location,
    customerPoNo: p.customer_po_no,
    poDate: p.po_date,
    rates: { gstPct: n(p.gst_pct), tdsPct: n(p.tds_pct), retentionPct: n(p.retention_pct), advancePct: n(p.advance_pct) },
  };
}

function badRequest(res: Response, message: string) {
  return res.status(400).json({ message });
}

const isDate = (v: unknown) => typeof v === 'string' && /^\d{4}-\d{2}-\d{2}$/.test(v);
const isAmount = (v: unknown) => v !== '' && v !== null && v !== undefined && Number.isFinite(Number(v)) && Number(v) >= 0;

/* ───────────────────────── list & register ───────────────────────── */

/** Approved projects with their cumulative invoice / collection totals. */
export async function list(_req: Request, res: Response, next: NextFunction) {
  try {
    const [projects] = await pool.query<RowDataPacket[]>(
      `SELECT p.id, p.job_code, p.name, c.name AS client_name, p.customer_po_no, p.po_date,
              (SELECT COALESCE(SUM(qty * sale_rate), 0) FROM project_line_items li WHERE li.project_id = p.id) AS sale_value
       FROM projects p LEFT JOIN clients c ON c.id = p.client_id
       WHERE p.approval_status = 'approved' ORDER BY p.id DESC`,
    );
    const totals = await invoiceTotalsByProject(projects.map((p) => p.id));
    res.json(
      projects.map((p) => ({
        projectId: p.id,
        jobCode: p.job_code,
        name: p.name,
        clientName: p.client_name,
        customerPoNo: p.customer_po_no,
        poDate: p.po_date,
        saleValue: n(p.sale_value),
        ...(totals.get(p.id) ?? emptyInvoiceTotals()),
      })),
    );
  } catch (err) {
    next(err);
  }
}

export async function getRegister(req: Request, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  try {
    const project = await loadProject(projectId);
    if (!project || project.approval_status !== 'approved') return res.status(404).json({ message: 'Project not found' });
    const register = await loadRegister(projectId);
    const totals = (await invoiceTotalsByProject([projectId])).get(projectId) ?? emptyInvoiceTotals();
    res.json({ project: projectHeaderDto(project), ...register, totals });
  } catch (err) {
    next(err);
  }
}

export async function updateRates(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const { gstPct, tdsPct, retentionPct, advancePct } = req.body ?? {};
  try {
    if (!(await hasPermission(req.userId!, 'invoices', 'canUpdate'))) {
      return res.status(403).json({ message: 'You do not have permission to change invoice rates.' });
    }
    const values = [gstPct, tdsPct, retentionPct, advancePct];
    if (!values.every((v) => isAmount(v) && Number(v) <= 100)) return badRequest(res, 'Each rate must be between 0 and 100.');
    await pool.query('UPDATE projects SET gst_pct = ?, tds_pct = ?, retention_pct = ?, advance_pct = ? WHERE id = ?', [...values.map(Number), projectId]);
    res.json({ ok: true });
  } catch (err) {
    next(err);
  }
}

/* ───────────────────────── invoices ───────────────────────── */

function invoiceInput(body: Record<string, unknown>) {
  const amounts = ['invoiceValue', 'gstAmount', 'tdsAmount', 'retentionAmount', 'advanceAmount', 'holdAmount', 'penaltyAmount'] as const;
  const invoiceNo = String(body.invoiceNo ?? '').trim();
  if (!invoiceNo) return { error: 'Invoice number is required.' };
  if (!isDate(body.invoiceDate)) return { error: 'Invoice date is required.' };
  for (const k of amounts) {
    const v = body[k] ?? 0;
    if (!isAmount(v)) return { error: 'Amounts must be zero or more.' };
  }
  if (!(Number(body.invoiceValue) > 0)) return { error: 'Invoice value must be more than zero.' };
  return {
    values: [
      String(body.category ?? '').trim() || null,
      invoiceNo,
      body.invoiceDate as string,
      ...amounts.map((k) => Number(body[k] ?? 0)),
      String(body.remarks ?? '').trim() || null,
    ],
  };
}

function duplicateInvoice(err: unknown) {
  return (err as { code?: string })?.code === 'ER_DUP_ENTRY';
}

export async function createInvoice(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  try {
    if (!(await hasPermission(req.userId!, 'invoices', 'canCreate'))) {
      return res.status(403).json({ message: 'You do not have permission to add invoices.' });
    }
    const project = await loadProject(projectId);
    if (!project || project.approval_status !== 'approved') return res.status(404).json({ message: 'Project not found' });
    const input = invoiceInput(req.body ?? {});
    if ('error' in input) return badRequest(res, input.error!);
    await pool.query(
      `INSERT INTO project_invoices
         (category, invoice_no, invoice_date, invoice_value, gst_amount, tds_amount, retention_amount, advance_amount,
          hold_amount, penalty_amount, remarks, project_id, created_by)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      [...input.values!, projectId, req.userId],
    );
    res.status(201).json({ ok: true });
  } catch (err) {
    if (duplicateInvoice(err)) return badRequest(res, 'This invoice number already exists for this project.');
    next(err);
  }
}

export async function updateInvoice(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const invoiceId = Number(req.params.invoiceId);
  try {
    if (!(await hasPermission(req.userId!, 'invoices', 'canUpdate'))) {
      return res.status(403).json({ message: 'You do not have permission to edit invoices.' });
    }
    const input = invoiceInput(req.body ?? {});
    if ('error' in input) return badRequest(res, input.error!);
    const [result] = await pool.query<RowDataPacket[] & { affectedRows?: number }>(
      `UPDATE project_invoices SET category = ?, invoice_no = ?, invoice_date = ?, invoice_value = ?, gst_amount = ?,
         tds_amount = ?, retention_amount = ?, advance_amount = ?, hold_amount = ?, penalty_amount = ?, remarks = ?
       WHERE project_id = ? AND id = ?`,
      [...input.values!, projectId, invoiceId],
    );
    if (!(result as unknown as { affectedRows: number }).affectedRows) return res.status(404).json({ message: 'Invoice not found' });
    res.json({ ok: true });
  } catch (err) {
    if (duplicateInvoice(err)) return badRequest(res, 'This invoice number already exists for this project.');
    next(err);
  }
}

export async function deleteInvoice(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const invoiceId = Number(req.params.invoiceId);
  try {
    if (!(await hasPermission(req.userId!, 'invoices', 'canDelete'))) {
      return res.status(403).json({ message: 'You do not have permission to delete invoices.' });
    }
    // Payments tagged to this invoice stay on the project (their link is cleared by the foreign key).
    await pool.query('DELETE FROM project_invoices WHERE project_id = ? AND id = ?', [projectId, invoiceId]);
    res.json({ ok: true });
  } catch (err) {
    next(err);
  }
}

/* ───────────────────────── payments ───────────────────────── */

async function paymentInput(projectId: number, body: Record<string, unknown>) {
  if (!isDate(body.paymentDate)) return { error: 'Payment date is required.' };
  if (!(Number(body.amount) > 0)) return { error: 'Payment amount must be more than zero.' };
  const invoiceId = body.invoiceId === '' || body.invoiceId === null || body.invoiceId === undefined ? null : Number(body.invoiceId);
  if (invoiceId !== null) {
    const [rows] = await pool.query<RowDataPacket[]>('SELECT id FROM project_invoices WHERE id = ? AND project_id = ?', [invoiceId, projectId]);
    if (!rows[0]) return { error: 'The selected invoice does not belong to this project.' };
  }
  return {
    values: [
      invoiceId,
      body.paymentDate as string,
      Number(body.amount),
      String(body.reference ?? '').trim() || null,
      String(body.remarks ?? '').trim() || null,
    ],
  };
}

export async function createPayment(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  try {
    if (!(await hasPermission(req.userId!, 'invoices', 'canCreate'))) {
      return res.status(403).json({ message: 'You do not have permission to record payments.' });
    }
    const project = await loadProject(projectId);
    if (!project || project.approval_status !== 'approved') return res.status(404).json({ message: 'Project not found' });
    const input = await paymentInput(projectId, req.body ?? {});
    if ('error' in input) return badRequest(res, input.error!);
    await pool.query(
      `INSERT INTO project_payments (invoice_id, payment_date, amount, reference, remarks, project_id, created_by)
       VALUES (?, ?, ?, ?, ?, ?, ?)`,
      [...input.values!, projectId, req.userId],
    );
    res.status(201).json({ ok: true });
  } catch (err) {
    next(err);
  }
}

export async function updatePayment(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const paymentId = Number(req.params.paymentId);
  try {
    if (!(await hasPermission(req.userId!, 'invoices', 'canUpdate'))) {
      return res.status(403).json({ message: 'You do not have permission to edit payments.' });
    }
    const input = await paymentInput(projectId, req.body ?? {});
    if ('error' in input) return badRequest(res, input.error!);
    const [result] = await pool.query(
      `UPDATE project_payments SET invoice_id = ?, payment_date = ?, amount = ?, reference = ?, remarks = ?
       WHERE project_id = ? AND id = ?`,
      [...input.values!, projectId, paymentId],
    );
    if (!(result as { affectedRows: number }).affectedRows) return res.status(404).json({ message: 'Payment not found' });
    res.json({ ok: true });
  } catch (err) {
    next(err);
  }
}

export async function deletePayment(req: AuthRequest, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  const paymentId = Number(req.params.paymentId);
  try {
    if (!(await hasPermission(req.userId!, 'invoices', 'canDelete'))) {
      return res.status(403).json({ message: 'You do not have permission to delete payments.' });
    }
    await pool.query('DELETE FROM project_payments WHERE project_id = ? AND id = ?', [projectId, paymentId]);
    res.json({ ok: true });
  } catch (err) {
    next(err);
  }
}

/* ───────────────────────── reports ───────────────────────── */

const PROJECT_JOIN = `JOIN projects p ON p.id = x.project_id LEFT JOIN clients c ON c.id = p.client_id`;

/** Every invoice across approved projects (Invoice Report and Retention Report). */
export async function invoiceReport(_req: Request, res: Response, next: NextFunction) {
  try {
    const [rows] = await pool.query<(InvoiceRow & { job_code: string | null; project_name: string; client_name: string | null })[]>(
      `SELECT x.*, p.job_code, p.name AS project_name, c.name AS client_name
       FROM project_invoices x ${PROJECT_JOIN}
       WHERE p.approval_status = 'approved' ORDER BY x.invoice_date DESC, x.id DESC`,
    );
    const [received] = await pool.query<RowDataPacket[]>(
      'SELECT invoice_id, SUM(amount) AS received FROM project_payments WHERE invoice_id IS NOT NULL GROUP BY invoice_id',
    );
    const receivedBy = new Map(received.map((r) => [r.invoice_id as number, n(r.received)]));
    res.json(
      rows.map((r) => ({
        ...invoiceDto(r, receivedBy.get(r.id) ?? 0),
        jobCode: r.job_code,
        projectName: r.project_name,
        clientName: r.client_name,
      })),
    );
  } catch (err) {
    next(err);
  }
}

/** Every payment across approved projects (Collection Report). */
export async function collectionReport(_req: Request, res: Response, next: NextFunction) {
  try {
    const [rows] = await pool.query<(PaymentRow & { job_code: string | null; project_name: string; client_name: string | null })[]>(
      `SELECT x.*, pi.invoice_no, p.job_code, p.name AS project_name, c.name AS client_name
       FROM project_payments x ${PROJECT_JOIN}
       LEFT JOIN project_invoices pi ON pi.id = x.invoice_id
       WHERE p.approval_status = 'approved' ORDER BY x.payment_date DESC, x.id DESC`,
    );
    res.json(rows.map((r) => ({ ...paymentDto(r), jobCode: r.job_code, projectName: r.project_name, clientName: r.client_name })));
  } catch (err) {
    next(err);
  }
}

/* ───────────────────────── Excel export (register, laid out like the template) ───────────────────────── */

const NAVY = 'FF1F3864';
const GOLD = 'FFFFE699';
const GRID: Partial<ExcelJS.Border> = { style: 'thin', color: { argb: 'FF000000' } };
const MONEY = '#,##0.00;(#,##0.00);"-"';

function ddmmyyyy(iso: string | null) {
  const m = /^(\d{4})-(\d{2})-(\d{2})/.exec(iso ?? '');
  return m ? `${m[3]}-${m[2]}-${m[1]}` : '';
}

export async function exportRegister(req: Request, res: Response, next: NextFunction) {
  const projectId = Number(req.params.projectId);
  try {
    const project = await loadProject(projectId);
    if (!project) return res.status(404).json({ message: 'Project not found' });
    const { invoices, payments } = await loadRegister(projectId);

    const wb = new ExcelJS.Workbook();
    wb.calcProperties.fullCalcOnLoad = true;
    const sheet = wb.addWorksheet((project.client_name ?? 'Register').slice(0, 30), {
      views: [{ state: 'frozen', ySplit: 3 }],
    });
    const headers = [
      'S. No', 'Category', 'Invoice Number', 'Date', 'Invoice Value', 'GST', 'Invoice Value (Total)', 'TDS',
      'Retention', 'Advance', 'Hold', 'Penalty', 'Receivable value', 'Payment Received Date', 'Payment Received Amount',
      'Yet To Receive Value',
    ];
    sheet.columns = [6, 16, 20, 12, 16, 14, 18, 13, 14, 14, 12, 12, 17, 15, 17, 17].map((width) => ({ width }));

    const title = [project.client_name, project.location, project.customer_po_no ? `PO ${project.customer_po_no}` : null, project.po_date ? `Dt: ${ddmmyyyy(project.po_date)}` : null]
      .filter(Boolean)
      .join(' - ');
    sheet.mergeCells(2, 1, 2, headers.length);
    const t = sheet.getCell(2, 1);
    t.value = `${title || project.name}  (${project.job_code ?? ''} ${project.name})`;
    t.font = { bold: true, size: 13 };
    t.alignment = { horizontal: 'center' };

    const head = sheet.getRow(3);
    head.values = headers;
    head.eachCell((cell) => {
      cell.font = { bold: true, color: { argb: 'FFFFFFFF' } };
      cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: NAVY } };
      cell.alignment = { horizontal: 'center', vertical: 'middle', wrapText: true };
      cell.border = { top: GRID, left: GRID, bottom: GRID, right: GRID };
    });
    head.height = 32;

    // One row per invoice, then the payments tagged to it; untagged payments at the end.
    let sno = 0;
    let r = 4;
    const firstRow = r;
    const writeRow = (vals: (string | number | ExcelJS.CellFormulaValue | null)[]) => {
      const row = sheet.getRow(r);
      row.values = vals as ExcelJS.CellValue[];
      for (let c = 1; c <= headers.length; c++) {
        const cell = row.getCell(c);
        cell.border = { top: GRID, left: GRID, bottom: GRID, right: GRID };
        if (c >= 5 && c !== 14) cell.numFmt = MONEY;
      }
      r++;
      return row;
    };
    // A payment tagged to an invoice is netted off on that invoice's row; an untagged payment shows as (amount).
    const paymentRow = (p: ReturnType<typeof paymentDto>, tagged: boolean) => {
      sno++;
      const yet = tagged ? null : { formula: `-O${r}`, result: -p.amount };
      writeRow([pad(sno), '', p.reference ?? '', '', null, null, null, null, null, null, null, null, null, ddmmyyyy(p.paymentDate), p.amount, yet]);
    };
    for (const inv of invoices) {
      sno++;
      const row = r;
      writeRow([
        pad(sno), inv.category ?? '', inv.invoiceNo, ddmmyyyy(inv.invoiceDate), inv.invoiceValue, inv.gstAmount,
        { formula: `E${row}+F${row}`, result: inv.totalValue }, inv.tdsAmount, inv.retentionAmount, inv.advanceAmount,
        inv.holdAmount, inv.penaltyAmount,
        { formula: `G${row}-H${row}-I${row}-J${row}-K${row}-L${row}`, result: inv.receivable },
        null, null, null,
      ]);
      const tagged = payments.filter((p) => p.invoiceId === inv.id);
      // Yet to receive on the invoice row = receivable − everything received against it
      const receivedRange = tagged.length ? `SUM(O${row + 1}:O${row + tagged.length})` : '0';
      sheet.getCell(`P${row}`).value = { formula: `M${row}-${receivedRange}`, result: inv.yetToReceive };
      for (const p of tagged) paymentRow(p, true);
    }
    for (const p of payments.filter((p) => !p.invoiceId)) paymentRow(p, false);
    const lastRow = r - 1;

    const totals = writeRow([
      '', 'TOTAL', '', '',
      ...['E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M'].map((col) => ({ formula: lastRow >= firstRow ? `SUM(${col}${firstRow}:${col}${lastRow})` : '0', result: 0 })),
      '', { formula: lastRow >= firstRow ? `SUM(O${firstRow}:O${lastRow})` : '0', result: 0 },
      { formula: `M${r}-O${r}`, result: 0 },
    ]);
    totals.font = { bold: true };
    totals.eachCell((cell) => (cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: GOLD } }));
    sheet.autoFilter = { from: { row: 3, column: 1 }, to: { row: 3, column: headers.length } };

    const safe = `${project.job_code ?? project.id}_${project.name}`.replace(/[\\/:*?"<>|]/g, '_');
    res.setHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    res.setHeader('Content-Disposition', `attachment; filename="${safe}_Invoice_Collection.xlsx"`);
    await wb.xlsx.write(res);
    res.end();
  } catch (err) {
    next(err);
  }
}

function pad(v: number) {
  return v < 10 ? `0${v}` : String(v);
}
