import fs from 'fs';
import path from 'path';
import { NextFunction, Request, Response } from 'express';
import ExcelJS from 'exceljs';
import { RowDataPacket } from 'mysql2/promise';
import { pool } from '../config/db.js';
import { fetchProjectDto } from './project.controller.js';

interface ProjectRow extends RowDataPacket {
  id: number;
  job_code: string | null;
  name: string;
}

interface LineItemRow extends RowDataPacket {
  id: number;
  qty: string;
  revised_rate: string | null;
}

interface OverheadRow extends RowDataPacket {
  id: number;
  revised_ctc_per_month: string | null;
  nos: number;
  month: string;
}

interface CumulativeLineItemRow extends RowDataPacket {
  key_id: number;
  cumulative_qty: string;
  cumulative_amount: string;
}

interface CumulativeOverheadRow extends RowDataPacket {
  key_id: number;
  cumulative_amount: string;
}

interface LatestEtcRow extends RowDataPacket {
  key_id: number;
  etc_qty: string | null;
  etc_amount: string;
}

interface RemarksRow extends RowDataPacket {
  key_id: number;
  period: string;
  remarks: string;
}

async function remarksNotesByLineItem(ids: number[]): Promise<Map<number, string>> {
  const [rows] = await pool.query<RemarksRow[]>(
    `SELECT line_item_id AS key_id, period, remarks FROM project_line_item_actuals
     WHERE line_item_id IN (?) AND remarks IS NOT NULL AND remarks <> ''
     ORDER BY period ASC`,
    [ids],
  );
  const byItem = new Map<number, string[]>();
  for (const r of rows) {
    const label = new Date(`${r.period}T00:00:00`).toLocaleDateString('en-IN', { month: 'short', year: '2-digit' });
    const bucket = byItem.get(r.key_id) ?? [];
    bucket.push(`${label}: ${r.remarks}`);
    byItem.set(r.key_id, bucket);
  }
  return new Map([...byItem.entries()].map(([id, lines]) => [id, lines.join('\n')]));
}

/** One row per line item / overhead. `qty`/`etcQty` are null for overheads, which
 * have no quantity concept (Actuals & ETC tracks overhead progress as amount-only,
 * per the earlier "track actual monthly spend directly" decision). `aceAmount` is
 * already the full revised amount for both kinds (qty × rate for line items,
 * revisedCtc × nos × month for overheads). `etcAmount` is the *latest* submitted
 * ETC (a forward re-forecast, not cumulative) — same convention as the Enter
 * Actuals & ETC pages. */
interface ItemFigures {
  id: number;
  qty: number | null;
  aceAmount: number;
  actualQty: number | null;
  actualAmount: number;
  etcQty: number | null;
  etcAmount: number;
  remarksNote: string | null;
}

async function lineItemFigures(projectId: number): Promise<ItemFigures[]> {
  const [items] = await pool.query<LineItemRow[]>(
    'SELECT id, qty, revised_rate FROM project_line_items WHERE project_id = ?',
    [projectId],
  );
  if (items.length === 0) return [];
  const ids = items.map((i) => i.id);

  const [cumRows] = await pool.query<CumulativeLineItemRow[]>(
    `SELECT line_item_id AS key_id, SUM(actual_qty) AS cumulative_qty, SUM(actual_qty * actual_rate) AS cumulative_amount
     FROM project_line_item_actuals WHERE line_item_id IN (?) GROUP BY line_item_id`,
    [ids],
  );
  const [latestRows] = await pool.query<LatestEtcRow[]>(
    `SELECT a.line_item_id AS key_id, a.etc_qty, a.etc_qty * a.etc_rate AS etc_amount
     FROM project_line_item_actuals a
     INNER JOIN (SELECT line_item_id, MAX(id) AS max_id FROM project_line_item_actuals GROUP BY line_item_id) latest
       ON latest.line_item_id = a.line_item_id AND latest.max_id = a.id
     WHERE a.line_item_id IN (?)`,
    [ids],
  );
  const cumById = new Map(cumRows.map((r) => [r.key_id, { qty: Number(r.cumulative_qty), amt: Number(r.cumulative_amount) }]));
  const etcById = new Map(latestRows.map((r) => [r.key_id, { qty: Number(r.etc_qty ?? 0), amt: Number(r.etc_amount) }]));
  const remarksById = await remarksNotesByLineItem(ids);

  return items.map((i) => {
    const revisedRate = i.revised_rate === null ? null : Number(i.revised_rate);
    const cum = cumById.get(i.id);
    const etc = etcById.get(i.id);
    return {
      id: i.id,
      qty: Number(i.qty),
      aceAmount: revisedRate === null ? 0 : Number(i.qty) * revisedRate,
      actualQty: cum?.qty ?? 0,
      actualAmount: cum?.amt ?? 0,
      etcQty: etc?.qty ?? 0,
      etcAmount: etc?.amt ?? 0,
      remarksNote: remarksById.get(i.id) ?? null,
    };
  });
}

async function overheadFigures(projectId: number): Promise<ItemFigures[]> {
  const [items] = await pool.query<OverheadRow[]>(
    'SELECT id, revised_ctc_per_month, nos, month FROM project_overheads WHERE project_id = ?',
    [projectId],
  );
  if (items.length === 0) return [];
  const ids = items.map((i) => i.id);

  const [cumRows] = await pool.query<CumulativeOverheadRow[]>(
    `SELECT overhead_id AS key_id, SUM(actual_amount) AS cumulative_amount
     FROM project_overhead_actuals WHERE overhead_id IN (?) GROUP BY overhead_id`,
    [ids],
  );
  const [latestRows] = await pool.query<LatestEtcRow[]>(
    `SELECT a.overhead_id AS key_id, NULL AS etc_qty, a.etc_amount
     FROM project_overhead_actuals a
     INNER JOIN (SELECT overhead_id, MAX(id) AS max_id FROM project_overhead_actuals GROUP BY overhead_id) latest
       ON latest.overhead_id = a.overhead_id AND latest.max_id = a.id
     WHERE a.overhead_id IN (?)`,
    [ids],
  );
  const cumById = new Map(cumRows.map((r) => [r.key_id, Number(r.cumulative_amount)]));
  const etcById = new Map(latestRows.map((r) => [r.key_id, Number(r.etc_amount)]));

  return items.map((i) => ({
    id: i.id,
    qty: null,
    aceAmount: i.revised_ctc_per_month === null ? 0 : Number(i.revised_ctc_per_month) * i.nos * Number(i.month),
    actualQty: null,
    actualAmount: cumById.get(i.id) ?? 0,
    etcQty: null,
    etcAmount: etcById.get(i.id) ?? 0,
    // Overheads only appear as aggregated category totals in this report — no
    // single-row anchor to hang a per-entry remark note on.
    remarksNote: null,
  }));
}

function rollupOf(items: ItemFigures[]) {
  const ace = items.reduce((s, i) => s + i.aceAmount, 0);
  const actual = items.reduce((s, i) => s + i.actualAmount, 0);
  const etc = items.reduce((s, i) => s + i.etcAmount, 0);
  const balance = ace - actual;
  const bac = actual + etc;
  const vac = ace - bac;
  const vacPct = ace > 0 ? (vac / ace) * 100 : 0;
  return { ace, actual, balance, etc, bac, vac, vacPct };
}

export async function computeProjectRollup(projectId: number) {
  const [lineItems, overheads] = await Promise.all([lineItemFigures(projectId), overheadFigures(projectId)]);
  const r = rollupOf([...lineItems, ...overheads]);
  return {
    aceRevised: r.ace,
    actualTotal: r.actual,
    balance: r.balance,
    etcTotal: r.etc,
    bac: r.bac,
    vac: r.vac,
    vacPct: r.vacPct,
  };
}

export async function list(_req: Request, res: Response, next: NextFunction) {
  try {
    const [projects] = await pool.query<ProjectRow[]>(
      `SELECT id, job_code, name FROM projects WHERE approval_status = 'approved' ORDER BY id DESC`,
    );

    // Invoice (sale) value per project = Σ qty × sale rate over its line items
    const [saleRows] = await pool.query<(RowDataPacket & { project_id: number; total: string | number })[]>(
      `SELECT li.project_id, SUM(li.qty * li.sale_rate) AS total
       FROM project_line_items li JOIN projects p ON p.id = li.project_id
       WHERE p.approval_status = 'approved' GROUP BY li.project_id`,
    );
    const saleByProject = new Map(saleRows.map((r) => [r.project_id, Number(r.total)]));

    const rows = await Promise.all(
      projects.map(async (p) => {
        const rollup = await computeProjectRollup(p.id);
        return { projectId: p.id, jobCode: p.job_code, name: p.name, ...rollup, saleValue: saleByProject.get(p.id) ?? 0 };
      }),
    );

    res.json(rows);
  } catch (err) {
    next(err);
  }
}

const NAVY = 'FF1F3864';
const GOLD = 'FFFFE699';
const GREEN_FILL = 'FFD9F2D9';
const RED_FONT = 'FFCC0000';
const GRID: Partial<ExcelJS.Border> = { style: 'thin', color: { argb: 'FF000000' } };

const JCR_COLS = 20;
const LOGO_PATH = path.join(process.cwd(), 'src', 'assets', 'logo', 'vepl.png');

function gridBorder(cell: ExcelJS.Cell) {
  cell.border = { top: GRID, left: GRID, bottom: GRID, right: GRID };
}

function blankRow(n: number) {
  return Array(n).fill('');
}

/** A formula cell that also carries its computed result, so the sheet shows values before recalculation. */
function fx(formula: string, result: number): ExcelJS.CellFormulaValue {
  return { formula, result };
}

function addLogo(workbook: ExcelJS.Workbook, sheet: ExcelJS.Worksheet, topLeftCol: number) {
  if (!fs.existsSync(LOGO_PATH)) return;
  const width = 180;
  const height = Math.round(width * (476 / 2200));
  const imageId = workbook.addImage({ filename: LOGO_PATH, extension: 'png' });
  sheet.addImage(imageId, { tl: { col: topLeftCol, row: 0 }, ext: { width, height } });
}

// Two-row grouped header: Description/Unit/VAC/Earned Value/Invoiced Value span both
// rows; the five figure groups (a..e) merge across their Qty/Rate/Amount
// sub-columns on row 1, with the sub-labels on row 2.
function addColumnHeaders(sheet: ExcelJS.Worksheet) {
  const row1 = sheet.addRow([
    'Description',
    'Unit',
    'Accepted Cost Estimate',
    '',
    '',
    'Actual',
    '',
    '',
    'Balance',
    '',
    '',
    'Estimate At Completion',
    '',
    '',
    'Budget At Completion',
    '',
    '',
    'VAC',
    'Earned Value',
    'Invoiced Value',
  ]);
  const row2 = sheet.addRow([
    '',
    '',
    'Qty',
    'Rate',
    'Amount (Rs)',
    'Qty',
    'Rate',
    'Amount (Rs)',
    'Qty',
    'Rate',
    'Amount (Rs)',
    'Qty',
    'Rate',
    'Amount (Rs)',
    'Qty',
    'Rate',
    'Amount (Rs)',
    '',
    '',
    '',
  ]);

  const r1 = row1.number;
  const r2 = row2.number;
  sheet.mergeCells(r1, 1, r2, 1);
  sheet.mergeCells(r1, 2, r2, 2);
  sheet.mergeCells(r1, 3, r1, 5);
  sheet.mergeCells(r1, 6, r1, 8);
  sheet.mergeCells(r1, 9, r1, 11);
  sheet.mergeCells(r1, 12, r1, 14);
  sheet.mergeCells(r1, 15, r1, 17);
  sheet.mergeCells(r1, 18, r2, 18);
  sheet.mergeCells(r1, 19, r2, 19);
  sheet.mergeCells(r1, 20, r2, 20);

  for (const row of [row1, row2]) {
    row.eachCell({ includeEmpty: true }, (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 };
      gridBorder(cell);
    });
  }
  row1.height = 20;
  row2.height = 24;
}

function addReportHeader(
  workbook: ExcelJS.Workbook,
  sheet: ExcelJS.Worksheet,
  project: Awaited<ReturnType<typeof fetchProjectDto>>,
) {
  if (!project) return;

  const titleRow = sheet.addRow(['JOB COST REPORT']);
  sheet.mergeCells(titleRow.number, 1, titleRow.number, JCR_COLS);
  titleRow.font = { bold: true, size: 14 };
  titleRow.alignment = { horizontal: 'center' };
  titleRow.height = 22;
  addLogo(workbook, sheet, 16);

  const DAY_MS = 24 * 60 * 60 * 1000;
  const today = new Date();
  const start = new Date(project.startDate);
  const finish = new Date(project.finishDate);
  const daysElapsed = Math.floor((today.getTime() - start.getTime()) / DAY_MS);
  const duration = Math.floor((finish.getTime() - start.getTime()) / DAY_MS);
  const remainingDays = Math.floor((finish.getTime() - today.getTime()) / DAY_MS);
  const monthLabel = `${today.toLocaleDateString('en-US', { month: 'short' })}'${String(today.getFullYear()).slice(-2)}`;

  // Every row below is deliberately laid out to sum to exactly JCR_COLS (20)
  // cells, with each value sitting in the first cell of whatever merge range
  // it belongs to — a merge silently drops the value of any other cell in its
  // range, which bit the "as on" date here once already.

  // 1-2 label | 3-11 value(9) | 12-13 label | 14 date | 15-16 blank(2) | 17-18 label | 19-20 date(2)
  const row1 = sheet.addRow([
    'Project Name:', '',
    project.name, '', '', '', '', '', '', '', '',
    'Start:', '',
    start,
    '', '',
    'as on:', '',
    today, '',
  ]);
  sheet.mergeCells(row1.number, 1, row1.number, 2);
  sheet.mergeCells(row1.number, 3, row1.number, 11);
  sheet.mergeCells(row1.number, 12, row1.number, 13);
  sheet.mergeCells(row1.number, 17, row1.number, 18);
  sheet.mergeCells(row1.number, 19, row1.number, 20);
  row1.getCell(1).font = { bold: true };
  row1.getCell(12).font = { bold: true, italic: true };
  row1.getCell(14).numFmt = 'dd-mmm-yyyy';
  row1.getCell(17).font = { bold: true, italic: true };
  row1.getCell(19).numFmt = 'dd-mmm-yyyy';

  // 1-2 label | 3-11 value(9) | 12-13 label | 14 value | 15-16 label | 17 date | 18 label | 19-20 value(2)
  const row2 = sheet.addRow([
    'Client:', '',
    project.clientName ?? '-', '', '', '', '', '', '', '', '',
    'Days Elapsed:', '',
    daysElapsed,
    'Finish:', '',
    finish,
    'Duration (days):',
    duration, '',
  ]);
  sheet.mergeCells(row2.number, 1, row2.number, 2);
  sheet.mergeCells(row2.number, 3, row2.number, 11);
  sheet.mergeCells(row2.number, 12, row2.number, 13);
  sheet.mergeCells(row2.number, 15, row2.number, 16);
  sheet.mergeCells(row2.number, 19, row2.number, 20);
  row2.getCell(1).font = { bold: true };
  row2.getCell(12).font = { bold: true, italic: true };
  row2.getCell(14).font = { bold: true, color: { argb: RED_FONT } };
  row2.getCell(15).font = { bold: true, italic: true };
  row2.getCell(17).numFmt = 'dd-mmm-yyyy';
  row2.getCell(18).font = { bold: true, italic: true };

  // 1-11 blank(11) | 12-13 label | 14 value | 15-16 blank(2) | 17-20 label(4)
  const row3 = sheet.addRow([
    '', '', '', '', '', '', '', '', '', '', '',
    'Remaining Days:', '',
    remainingDays,
    '', '',
    `JCR for the Month of ${monthLabel}`, '', '', '',
  ]);
  sheet.mergeCells(row3.number, 12, row3.number, 13);
  sheet.mergeCells(row3.number, 17, row3.number, 20);
  row3.getCell(12).font = { bold: true, italic: true };
  row3.getCell(14).font = { bold: true, color: { argb: RED_FONT } };
  row3.getCell(17).font = { bold: true };
  row3.getCell(17).alignment = { horizontal: 'right' };

  sheet.addRow([]);
}

function directItemRow(
  sheet: ExcelJS.Worksheet,
  description: string,
  unit: string,
  aceRate: number | null,
  item: ItemFigures,
  saleAmount: number,
) {
  const aQty = item.qty;
  const aAmt = item.aceAmount;
  const bQty = item.actualQty;
  const bAmt = item.actualAmount;
  const bRate = bQty && bQty > 0 ? bAmt / bQty : null;
  const cQty = aQty !== null && bQty !== null ? aQty - bQty : null;
  const cAmt = aAmt - bAmt;
  const dAmt = bAmt + cAmt;
  // Budget At Completion = Estimate At Completion (d) + Actual (b); VAC = ACE (a) - BAC (e).
  const eAmt = dAmt + bAmt;
  const vac = aAmt - eAmt;

  // Derived columns are written as formulas on this row: K = E − H, N = H + K, Q = N + H, R = E − Q.
  const r = sheet.rowCount + 1;
  const row = sheet.addRow([
    description,
    unit,
    aQty ?? '-',
    aceRate ?? '-',
    aAmt,
    bQty ?? '-',
    bRate ?? '-',
    bAmt,
    cQty ?? '-',
    '',
    fx(`E${r}-H${r}`, cAmt),
    aQty ?? '-',
    aceRate ?? '-',
    fx(`H${r}+K${r}`, dAmt),
    '',
    '',
    fx(`N${r}+H${r}`, eAmt),
    fx(`E${r}-Q${r}`, vac),
    '',
    saleAmount,
  ]);
  row.eachCell({ includeEmpty: true }, (cell) => gridBorder(cell));
  [5, 8, 11, 14, 17, 18, 20].forEach((c) => (row.getCell(c).numFmt = '#,##0.00'));
  row.getCell(1).alignment = { wrapText: true, vertical: 'middle' };
  if (item.remarksNote) {
    const descCell = row.getCell(1);
    descCell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: GOLD } };
    descCell.note = item.remarksNote;
  }
  return row;
}

function jcrSectionBanner(sheet: ExcelJS.Worksheet, label: string) {
  const row = sheet.addRow([label, ...blankRow(JCR_COLS - 1)]);
  sheet.mergeCells(row.number, 1, row.number, JCR_COLS);
  row.font = { bold: true, italic: true };
  row.eachCell({ includeEmpty: true }, (cell) => gridBorder(cell));
  return row;
}

function jcrSectionTotalRow(
  sheet: ExcelJS.Worksheet,
  label: string,
  rollup: { ace: number; actual: number; balance: number },
  salesTotal: number,
  firstRow: number,
  lastRow: number,
) {
  const dAmt = rollup.actual + rollup.balance;
  const eAmt = dAmt + rollup.actual;
  const vac = rollup.ace - eAmt;
  const sum = (col: string, result: number) => fx(`SUM(${col}${firstRow}:${col}${lastRow})`, result);
  const row = sheet.addRow([
    label,
    '',
    '',
    '',
    sum('E', rollup.ace),
    '',
    '',
    sum('H', rollup.actual),
    '',
    '',
    sum('K', rollup.balance),
    '',
    '',
    sum('N', dAmt),
    '',
    '',
    sum('Q', eAmt),
    sum('R', vac),
    '',
    sum('T', salesTotal),
  ]);
  row.font = { bold: true };
  row.eachCell({ includeEmpty: true }, (cell) => {
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: GOLD } };
    gridBorder(cell);
  });
  [5, 8, 11, 14, 17, 18, 20].forEach((c) => (row.getCell(c).numFmt = '#,##0.00'));
  return row;
}

function jcrCategoryRow(sheet: ExcelJS.Worksheet, label: string, amount: number) {
  const row = sheet.addRow([label, '', '', '', amount, ...blankRow(JCR_COLS - 5)]);
  row.eachCell({ includeEmpty: true }, (cell) => gridBorder(cell));
  row.getCell(5).numFmt = '#,##0.00';
  return row;
}

function jcrHighlightRow(
  sheet: ExcelJS.Worksheet,
  label: string,
  value: number | '-' | ExcelJS.CellFormulaValue,
  fillColor: string | null,
  numFmt: string,
) {
  const row = sheet.addRow([label, '', '', '', value, ...blankRow(JCR_COLS - 5)]);
  row.font = { bold: true };
  row.eachCell({ includeEmpty: true }, (cell) => {
    if (fillColor) cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: fillColor } };
    gridBorder(cell);
  });
  if (value !== '-') row.getCell(5).numFmt = numFmt;
  return row;
}

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

    const [lineItems, overheads] = await Promise.all([lineItemFigures(projectId), overheadFigures(projectId)]);
    const lineItemById = new Map(lineItems.map((i) => [i.id, i]));

    const workbook = new ExcelJS.Workbook();
    // Totals are formulas; have Excel recalculate them all when the file is opened
    workbook.calcProperties.fullCalcOnLoad = true;
    const sheet = workbook.addWorksheet('JCR Report');
    sheet.columns = [
      { width: 34 }, // Description
      { width: 8 }, // Unit
      { width: 8 }, // (a) Qty
      { width: 12 }, // (a) Rate
      { width: 14 }, // (a) Amount
      { width: 8 }, // (b) Qty
      { width: 12 }, // (b) Rate
      { width: 14 }, // (b) Amount
      { width: 8 }, // (c) Qty
      { width: 10 }, // (c) Rate
      { width: 14 }, // (c) Amount
      { width: 8 }, // (d) Qty
      { width: 12 }, // (d) Rate
      { width: 14 }, // (d) Amount
      { width: 8 }, // (e) Qty
      { width: 12 }, // (e) Rate
      { width: 14 }, // (e) Amount
      { width: 12 }, // VAC
      { width: 12 }, // Earned Value
      { width: 14 }, // Invoiced Value
    ];

    addReportHeader(workbook, sheet, project);
    addColumnHeaders(sheet);

    jcrSectionBanner(sheet, 'DIRECT ITEMS');

    function directSection(
      label: string,
      section: { id?: number; description: string; unit: string; qty: number; saleRate: number; revisedRate: number | null }[],
    ) {
      const items = section.filter((s) => s.id !== undefined && lineItemById.has(s.id));
      if (items.length === 0) return;
      jcrSectionBanner(sheet, label);
      let saleTotalForSection = 0;
      const firstRow = sheet.rowCount + 1;
      for (const s of items) {
        const figures = lineItemById.get(s.id!)!;
        const saleAmount = s.qty * s.saleRate;
        saleTotalForSection += saleAmount;
        directItemRow(sheet, s.description, s.unit, s.revisedRate, figures, saleAmount);
      }
      const lastRow = sheet.rowCount;
      const rollup = rollupOf(items.map((s) => lineItemById.get(s.id!)!));
      const totalRow = jcrSectionTotalRow(sheet, `${label} Total`, rollup, saleTotalForSection, firstRow, lastRow);
      sectionTotalRows.push(totalRow.number);
    }

    // Row numbers of the section totals, so the grand totals can reference them
    const sectionTotalRows: number[] = [];

    directSection('Supply', project.supply);
    directSection('Civil Works', project.civil);
    directSection('Installation', project.installation);
    directSection('T & C', project.tc);

    jcrSectionBanner(sheet, 'INDIRECT ITEMS');
    const overheadsByCategory = new Map<string, number>();
    for (const o of project.overheads) {
      if (o.id === undefined) continue;
      const figures = overheads.find((f) => f.id === o.id);
      if (!figures) continue;
      const key = o.categoryDescription ?? 'Other';
      overheadsByCategory.set(key, (overheadsByCategory.get(key) ?? 0) + figures.aceAmount);
    }
    const firstIndirectRow = sheet.rowCount + 1;
    for (const [label, amount] of overheadsByCategory) {
      jcrCategoryRow(sheet, label, amount);
    }
    const lastIndirectRow = sheet.rowCount;
    const indirectTotal = [...overheadsByCategory.values()].reduce((s, v) => s + v, 0);
    const indirectTotalRow = jcrHighlightRow(
      sheet,
      'Indirect items - Total (Rs)',
      overheadsByCategory.size > 0 ? fx(`SUM(E${firstIndirectRow}:E${lastIndirectRow})`, indirectTotal) : 0,
      null,
      '#,##0.00',
    ).number;

    // CEIG Government fees has no backing data anywhere in the schema — shown as
    // a literal placeholder, same treatment as Job Contingency in the ACE Summary.
    jcrHighlightRow(sheet, 'CEIG Government fees', '-', null, '#,##0.00');

    const directRollup = rollupOf(lineItems);
    const grandTotalCost = directRollup.ace + indirectTotal;
    // (A) Grand total cost = every section's ACE total + indirect total
    const costRefs = [...sectionTotalRows.map((n) => `E${n}`), `E${indirectTotalRow}`].join('+');
    const grandTotalRow = jcrHighlightRow(sheet, 'Grand Total_COST (Rs)', fx(costRefs, grandTotalCost), GOLD, '#,##0.00').number;

    const saleValueTotal = [...project.supply, ...project.civil, ...project.installation, ...project.tc].reduce(
      (s, i) => s + i.qty * i.saleRate,
      0,
    );
    // (B) Total invoiced value = every section's invoiced total
    const invoicedRefs = sectionTotalRows.map((n) => `T${n}`).join('+') || '0';
    const invoicedRow = jcrHighlightRow(
      sheet,
      'Total Invoiced (Turn over) Value (Rs)',
      fx(invoicedRefs, saleValueTotal),
      GREEN_FILL,
      '#,##0.00',
    ).number;

    const jcrMarginRs = saleValueTotal - grandTotalCost;
    const jcrMarginPct = saleValueTotal > 0 ? (jcrMarginRs / saleValueTotal) * 100 : 0;
    // (C) = B − A;  % = C ÷ B
    const marginRow = jcrHighlightRow(
      sheet,
      'JCR Margin (Rs) = (B -A)',
      fx(`E${invoicedRow}-E${grandTotalRow}`, jcrMarginRs),
      null,
      '#,##0.00',
    ).number;
    jcrHighlightRow(
      sheet,
      "JCR Margin (%) = '(C/B)*100",
      fx(`IF(E${invoicedRow}=0,0,E${marginRow}/E${invoicedRow})`, jcrMarginPct / 100),
      null,
      '0.00%',
    );

    sheet.addRow([]);
    sheet.addRow([]);
    const signRow = sheet.addRow(['', '(Project Manager)', ...blankRow(JCR_COLS - 3), '(Director)']);
    sheet.mergeCells(signRow.number, 2, signRow.number, 4);
    signRow.getCell(2).alignment = { horizontal: 'center' };
    signRow.getCell(JCR_COLS).alignment = { horizontal: 'center' };

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