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 AceReportRow extends RowDataPacket {
  id: number;
  job_code: string | null;
  name: string;
  client_name: string | null;
  ace_total: string;
  project_total: string;
  actual_total: string;
  sale_value_total: string;
}

function contribution(saleValue: number, cost: number) {
  const siteContribution = saleValue - cost;
  const marginPct = saleValue > 0 ? (siteContribution / saleValue) * 100 : 0;
  return { siteContribution, marginPct };
}

export async function list(_req: Request, res: Response, next: NextFunction) {
  try {
    // "ACE Total" is defined consistently with the rest of the app (Projects list,
    // JCR Revised ACE) as line items only — overheads are a separate indirect-cost
    // bucket and are deliberately excluded here so this figure matches what's
    // already shown elsewhere for the same project.
    const [rows] = await pool.query<AceReportRow[]>(
      `SELECT p.id, p.job_code, p.name, c.name AS client_name,
              COALESCE((SELECT SUM(qty * ace_rate) FROM project_line_items WHERE project_id = p.id), 0) AS ace_total,
              COALESCE((SELECT SUM(qty * revised_rate) FROM project_line_items WHERE project_id = p.id AND revised_rate IS NOT NULL), 0) AS project_total,
              COALESCE((SELECT SUM(a.actual_qty * a.actual_rate) FROM project_line_item_actuals a JOIN project_line_items li ON li.id = a.line_item_id WHERE li.project_id = p.id), 0) AS actual_total,
              COALESCE((SELECT SUM(qty * sale_rate) FROM project_line_items WHERE project_id = p.id), 0) AS sale_value_total
       FROM projects p
       LEFT JOIN clients c ON c.id = p.client_id
       ORDER BY p.id DESC`,
    );

    res.json(
      rows.map((row) => {
        const mgmtAceTotal = Number(row.ace_total);
        const projectAceTotal = Number(row.project_total);
        const actualSpendTotal = Number(row.actual_total);
        const saleValue = Number(row.sale_value_total);

        const mgmt = contribution(saleValue, mgmtAceTotal);
        const project = contribution(saleValue, projectAceTotal);
        const actual = contribution(saleValue, actualSpendTotal);

        return {
          projectId: row.id,
          jobCode: row.job_code,
          name: row.name,
          clientName: row.client_name,
          mgmtAceTotal,
          projectAceTotal,
          actualSpendTotal,
          saleValue,
          siteContributionMgmt: mgmt.siteContribution,
          marginPctMgmt: mgmt.marginPct,
          siteContributionProject: project.siteContribution,
          marginPctProject: project.marginPct,
          siteContributionActual: actual.siteContribution,
          marginPctActual: actual.marginPct,
        };
      }),
    );
  } catch (err) {
    next(err);
  }
}

const NAVY = 'FF1F3864';
const LIGHT_BLUE = 'FFDCE6F1';
const GOLD = 'FFFFE699';
const GRID: Partial<ExcelJS.Border> = { style: 'thin', color: { argb: 'FF000000' } };
const DOTTED: Partial<ExcelJS.Border> = { style: 'dotted', color: { argb: 'FF000000' } };
const DASHED_BLUE: Partial<ExcelJS.Border> = { style: 'dashed', color: { argb: 'FF0000FF' } };

const SUMMARY_COLS = 5; // S.No | Description | Currency | Value (Rs) | Remarks
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 };
}

/** 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 };
}

type SummaryValue = number | '-' | ExcelJS.CellFormulaValue;

/** Excel reference to a cell on another sheet, e.g. 'Civil Works'!E7. */
function sheetRef(sheetName: string, cell: string) {
  return `'${sheetName.replace(/'/g, "''")}'!${cell}`;
}

/** Row of a detail / overheads sheet's Grand Total: header on row 1, items from row 2. */
function grandTotalRowOf(itemCount: number) {
  return itemCount + 2;
}

// S.No | Description (merged through Remarks) — a numbered section banner, e.g. "5" / "JOB CONTINGENCY".
function summarySectionRow(sheet: ExcelJS.Worksheet, sno: string, label: string) {
  const row = sheet.addRow([sno, label, '', '', '']);
  sheet.mergeCells(row.number, 2, row.number, SUMMARY_COLS);
  row.eachCell((cell) => {
    cell.font = { bold: true, italic: true };
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: LIGHT_BLUE } };
    gridBorder(cell);
  });
  row.getCell(1).alignment = { horizontal: 'center', vertical: 'middle' };
  row.height = 20;
  return row;
}

// A numbered line item, e.g. "5.1" / "Contingency" / value. `value` is a literal
// '-' for figures with no backing data source (e.g. Job Contingency).
function summaryItemRow(sheet: ExcelJS.Worksheet, sno: string, label: string, value: SummaryValue) {
  const row = sheet.addRow([sno, label, value === '-' ? '' : 'INR', value, '']);
  row.eachCell((cell) => gridBorder(cell));
  row.getCell(1).alignment = { horizontal: 'center', vertical: 'middle' };
  row.getCell(3).alignment = { horizontal: 'center', vertical: 'middle' };
  row.getCell(4).alignment = { horizontal: 'right', vertical: 'middle' };
  if (value !== '-') row.getCell(4).numFmt = '#,##0.00';
  row.height = 18;
  return row;
}

// A "SUB-TOTAL: ..." (or lettered A/B/C) row — no S.No, gold fill, bold.
function summarySubtotalRow(sheet: ExcelJS.Worksheet, label: string, value: SummaryValue) {
  const row = sheet.addRow(['', label, value === '-' ? '' : 'INR', value, '']);
  row.eachCell((cell) => {
    cell.font = { bold: true };
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: GOLD } };
    gridBorder(cell);
  });
  row.getCell(3).alignment = { horizontal: 'center', vertical: 'middle' };
  row.getCell(4).alignment = { horizontal: 'right', vertical: 'middle' };
  if (value !== '-') row.getCell(4).numFmt = '#,##0.00';
  row.height = 18;
  return row;
}

// Column widths are character-unit widths; Excel renders them at roughly
// width*7+5 px (Calibri 11 default) — used to horizontally center the logo.
function centeredLogoCol(sheet: ExcelJS.Worksheet, imageWidthPx: number) {
  const widths = sheet.columns.map((c) => (c.width ?? 10) * 7 + 5);
  const totalWidth = widths.reduce((s, w) => s + w, 0);
  let offset = Math.max(0, (totalWidth - imageWidthPx) / 2);
  let col = 0;
  for (const w of widths) {
    if (offset <= w) return col + offset / w;
    offset -= w;
    col += 1;
  }
  return col;
}

function addLogo(workbook: ExcelJS.Workbook, sheet: ExcelJS.Worksheet) {
  if (!fs.existsSync(LOGO_PATH)) return;
  const width = 220;
  const height = Math.round(width * (476 / 2200));
  const imageId = workbook.addImage({ filename: LOGO_PATH, extension: 'png' });
  const logoRow = sheet.addRow([]);
  logoRow.height = Math.max(36, height * 0.75);
  sheet.addImage(imageId, {
    tl: { col: centeredLogoCol(sheet, width), row: logoRow.number - 1 },
    ext: { width, height },
  });
  sheet.addRow([]);
}

function addSignatureBlock(sheet: ExcelJS.Worksheet) {
  sheet.addRow([]);
  sheet.addRow([]);

  const lineRow1 = sheet.addRow(['', '', '', '', '']);
  lineRow1.getCell(2).border = { bottom: DOTTED };
  lineRow1.getCell(4).border = { bottom: DOTTED };
  lineRow1.getCell(5).border = { bottom: DOTTED };

  const labelRow1 = sheet.addRow(['', 'PREPARED BY', '', 'PROJECT MANAGER', 'EXECUTIVE DIRECTOR']);
  labelRow1.eachCell((cell) => {
    cell.font = { bold: true };
    cell.alignment = { horizontal: 'center' };
  });

  sheet.addRow([]);
  sheet.addRow([]);

  const sepRow = sheet.addRow(['', '', '', '', '']);
  sepRow.eachCell((cell) => {
    cell.border = { top: DASHED_BLUE };
  });

  sheet.addRow([]);

  const lineRow2 = sheet.addRow(['', '', '', '', '']);
  lineRow2.getCell(2).border = { bottom: DOTTED };
  lineRow2.getCell(4).border = { bottom: DOTTED };

  const labelRow2 = sheet.addRow(['', 'DIRECTOR', '', 'CMD', '']);
  labelRow2.eachCell((cell) => {
    cell.font = { bold: true };
    cell.alignment = { horizontal: 'center' };
  });
}

function buildSummarySheet(
  workbook: ExcelJS.Workbook,
  project: Awaited<ReturnType<typeof fetchProjectDto>>,
) {
  if (!project) return;
  const sheet = workbook.addWorksheet('ACE Summary');
  sheet.columns = [{ width: 8 }, { width: 38 }, { width: 12 }, { width: 18 }, { width: 22 }];

  addLogo(workbook, sheet);

  const projectRow = sheet.addRow(['PROJECT:', '', project.name, '', '']);
  sheet.mergeCells(projectRow.number, 1, projectRow.number, 2);
  sheet.mergeCells(projectRow.number, 3, projectRow.number, 5);
  projectRow.getCell(1).font = { bold: true };

  const clientRow = sheet.addRow(['CLIENT:', '', project.clientName ?? '-', '', '']);
  sheet.mergeCells(clientRow.number, 1, clientRow.number, 2);
  sheet.mergeCells(clientRow.number, 3, clientRow.number, 5);
  clientRow.getCell(1).font = { bold: true };

  const jobCodeRow = sheet.addRow(['JOB CODE:', '', project.jobCode ?? '-', '', '']);
  sheet.mergeCells(jobCodeRow.number, 1, jobCodeRow.number, 2);
  sheet.mergeCells(jobCodeRow.number, 3, jobCodeRow.number, 5);
  jobCodeRow.getCell(1).font = { bold: true };
  sheet.addRow([]);

  const titleRow = sheet.addRow(['ABSTRACT OF ACCEPTED COST ESTIMATE']);
  sheet.mergeCells(titleRow.number, 1, titleRow.number, SUMMARY_COLS);
  titleRow.font = { bold: true, size: 13 };
  titleRow.alignment = { horizontal: 'center' };

  const subtitleRow = sheet.addRow(['TOP SHEET (SUPPLY, SERVICES & OVERHEADS)']);
  sheet.mergeCells(subtitleRow.number, 1, subtitleRow.number, SUMMARY_COLS);
  subtitleRow.alignment = { horizontal: 'center' };
  sheet.addRow([]);

  const headerRow = sheet.addRow(['S.No', 'Description', 'Currency', 'Value (Rs)', 'Remarks']);
  headerRow.eachCell((cell) => {
    cell.font = { bold: true, color: { argb: 'FFFFFFFF' } };
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: NAVY } };
    gridBorder(cell);
  });
  headerRow.height = 20;

  // Equipment Supply splits into Foreign/Local by the line item's `origin` field
  // (set on the Supply tab only); items with no origin recorded (legacy rows,
  // created before this field existed) are treated as Local.
  const foreignSupplyAce = project.supply
    .filter((i) => i.origin === 'foreign')
    .reduce((s, i) => s + i.qty * i.aceRate, 0);
  const localSupplyAce = project.supply
    .filter((i) => i.origin !== 'foreign')
    .reduce((s, i) => s + i.qty * i.aceRate, 0);
  const supplyAce = foreignSupplyAce + localSupplyAce;
  const civilAce = project.civil.reduce((s, i) => s + i.qty * i.aceRate, 0);
  const installationAce = project.installation.reduce((s, i) => s + i.qty * i.aceRate, 0);
  const tcAce = project.tc.reduce((s, i) => s + i.qty * i.aceRate, 0);
  const servicesAce = installationAce + tcAce;

  const overheadsByCategory = new Map<string, number>();
  for (const o of project.overheads) {
    const key = o.categoryDescription ?? 'Other';
    overheadsByCategory.set(key, (overheadsByCategory.get(key) ?? 0) + o.ctcPerMonth * o.nos * o.month);
  }
  const overheadsTotal = [...overheadsByCategory.values()].reduce((s, v) => s + v, 0);

  const mgmtAceTotal = supplyAce + civilAce + servicesAce + overheadsTotal;

  // Section values link to the detail sheets' Grand Totals (column E = ACE, H = invoiced), so the
  // summary follows any edit there; subtotals and totals are formulas on this sheet (value in column D).
  const detailTotal = (sheetName: string, items: unknown[], col: 'E' | 'H', result: number): SummaryValue =>
    items.length > 0 ? fx(sheetRef(sheetName, `${col}${grandTotalRowOf(items.length)}`), result) : result;
  const sumRows = (rows: number[], result: number) => fx(rows.length ? rows.map((n) => `D${n}`).join('+') : '0', result);

  summarySectionRow(sheet, '1', 'EQUIPMENT SUPPLY');
  // Foreign / Local is a split of the Supply sheet by origin, so these two stay as values
  const foreignRow = summaryItemRow(sheet, '1.1', 'Foreign Supply', foreignSupplyAce).number;
  const localRow = summaryItemRow(sheet, '1.2', 'Local Supply', localSupplyAce).number;
  const supplySubRow = summarySubtotalRow(sheet, 'SUB-TOTAL: SUPPLY COST', sumRows([foreignRow, localRow], supplyAce)).number;

  summarySectionRow(sheet, '2', 'CIVIL WORKS');
  const civilRow = summaryItemRow(sheet, '2.1', 'Civil Works', detailTotal('Civil Works', project.civil, 'E', civilAce)).number;
  const civilSubRow = summarySubtotalRow(sheet, 'SUB-TOTAL: CIVIL WORKS', sumRows([civilRow], civilAce)).number;

  summarySectionRow(sheet, '3', 'SERVICES');
  const installRow = summaryItemRow(sheet, '3.1', 'Installation', detailTotal('Installation', project.installation, 'E', installationAce)).number;
  const tcRow = summaryItemRow(sheet, '3.2', 'Testing & Commissioning', detailTotal('T & C', project.tc, 'E', tcAce)).number;
  const servicesSubRow = summarySubtotalRow(sheet, 'SUB-TOTAL: SERVICES', sumRows([installRow, tcRow], servicesAce)).number;

  summarySectionRow(sheet, '4', 'OVERHEADS & OTHERS');
  let overheadSno = 1;
  const overheadRows: number[] = [];
  for (const [label, value] of overheadsByCategory) {
    overheadRows.push(summaryItemRow(sheet, `4.${overheadSno}`, label, value).number);
    overheadSno += 1;
  }
  const overheadsSubRow = summarySubtotalRow(sheet, 'SUB-TOTAL: OVERHEADS', sumRows(overheadRows, overheadsTotal)).number;

  // Job Contingency has no backing data source anywhere in the schema — shown
  // as a literal '-' placeholder, matching the client's reference format.
  summarySectionRow(sheet, '5', 'JOB CONTINGENCY');
  summaryItemRow(sheet, '5.1', 'Contingency', '-');
  summarySubtotalRow(sheet, 'SUB-TOTAL: CONTINGENCY', '-');

  // A) = all cost subtotals
  const totalCostRow = summarySubtotalRow(
    sheet,
    'A) TOTAL PROJECT COST',
    sumRows([supplySubRow, civilSubRow, servicesSubRow, overheadsSubRow], mgmtAceTotal),
  ).number;
  sheet.addRow([]);

  const supplySale = project.supply.reduce((s, i) => s + i.qty * i.saleRate, 0);
  const civilSale = project.civil.reduce((s, i) => s + i.qty * i.saleRate, 0);
  const installationSale = project.installation.reduce((s, i) => s + i.qty * i.saleRate, 0);
  const tcSale = project.tc.reduce((s, i) => s + i.qty * i.saleRate, 0);
  const saleValueTotal = supplySale + civilSale + installationSale + tcSale;

  summarySectionRow(sheet, '', 'INVOICED VALUE');
  const saleRows = [
    summaryItemRow(sheet, '', 'Supply', detailTotal('Supply', project.supply, 'H', supplySale)).number,
    summaryItemRow(sheet, '', 'Civil Works', detailTotal('Civil Works', project.civil, 'H', civilSale)).number,
    summaryItemRow(sheet, '', 'Installation', detailTotal('Installation', project.installation, 'H', installationSale)).number,
    summaryItemRow(sheet, '', 'Testing and Commissioning', detailTotal('T & C', project.tc, 'H', tcSale)).number,
  ];
  // B) = all invoiced values
  const contractRow = summarySubtotalRow(sheet, 'B) PROJECT CONTRACT VALUE', sumRows(saleRows, saleValueTotal)).number;
  sheet.addRow([]);

  const siteContribution = saleValueTotal - mgmtAceTotal;
  const marginPct = saleValueTotal > 0 ? (siteContribution / saleValueTotal) * 100 : 0;
  // C) = B − A;  margin % = C ÷ B
  const contributionRow = summarySubtotalRow(
    sheet,
    'C) SITE CONTRIBUTION (B-A)',
    fx(`D${contractRow}-D${totalCostRow}`, siteContribution),
  ).number;
  const marginRow = sheet.addRow([
    '',
    'CONTRIBUTION MARGIN %',
    '',
    fx(`IF(D${contractRow}=0,0,D${contributionRow}/D${contractRow})`, marginPct / 100),
    '',
  ]);
  marginRow.eachCell((cell) => gridBorder(cell));
  marginRow.font = { bold: true };
  marginRow.getCell(4).numFmt = '0.00%';
  marginRow.getCell(4).alignment = { horizontal: 'right' };

  addSignatureBlock(sheet);
}

function buildDetailSheet(
  workbook: ExcelJS.Workbook,
  sheetName: string,
  items: { description: string; unit: string; qty: number; aceRate: number; saleRate: number }[],
) {
  if (items.length === 0) return;
  const sheet = workbook.addWorksheet(sheetName);
  sheet.columns = [
    { header: 'Description', width: 40 },
    { header: 'Unit', width: 10 },
    { header: 'ACE Qty', width: 10 },
    { header: 'ACE Rate (Rs)', width: 14 },
    { header: 'ACE Amount (Rs)', width: 16 },
    { header: 'Invoiced Qty', width: 12 },
    { header: 'Invoiced Rate (Rs)', width: 16 },
    { header: 'Invoiced Amount (Rs)', width: 18 },
    { header: 'Variance Qty', width: 12 },
    { header: 'Variance Amount (Rs)', width: 16 },
  ];
  sheet.getRow(1).eachCell((cell) => {
    cell.font = { bold: true, color: { argb: 'FFFFFFFF' } };
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: NAVY } };
  });

  let aceTotal = 0;
  let saleTotal = 0;
  // Amounts and variances are formulas on each row: E = C×D, H = F×G, I = F−C, J = H−E
  for (const item of items) {
    const aceAmount = item.qty * item.aceRate;
    const saleAmount = item.qty * item.saleRate;
    aceTotal += aceAmount;
    saleTotal += saleAmount;
    const r = sheet.rowCount + 1;
    sheet.addRow([
      item.description,
      item.unit,
      item.qty,
      item.aceRate,
      fx(`C${r}*D${r}`, aceAmount),
      item.qty,
      item.saleRate,
      fx(`F${r}*G${r}`, saleAmount),
      fx(`F${r}-C${r}`, 0),
      fx(`H${r}-E${r}`, saleAmount - aceAmount),
    ]);
  }

  const last = items.length + 1;
  const grandTotal = sheet.addRow([
    'Grand Total',
    '',
    '',
    '',
    fx(`SUM(E2:E${last})`, aceTotal),
    '',
    '',
    fx(`SUM(H2:H${last})`, saleTotal),
    '',
    fx(`SUM(J2:J${last})`, saleTotal - aceTotal),
  ]);
  grandTotal.font = { bold: true };
  [5, 8, 10].forEach((col) => {
    sheet.getColumn(col).numFmt = '#,##0.00';
  });
}

function buildOverheadsSheet(
  workbook: ExcelJS.Workbook,
  overheads: {
    categoryDescription: string | null;
    categoryCode?: string | null;
    subCategory: string | null;
    ctcPerMonth: number;
    nos: number;
    month: number;
  }[],
) {
  if (overheads.length === 0) return;
  const sheet = workbook.addWorksheet('Overheads');
  sheet.columns = [
    { header: 'Category', width: 30 },
    { header: 'Sub Category', width: 20 },
    { header: 'CTC/M (Rs)', width: 14 },
    { header: 'Nos', width: 8 },
    { header: 'Month', width: 8 },
    { header: 'Amount (Rs)', width: 16 },
  ];
  sheet.getRow(1).eachCell((cell) => {
    cell.font = { bold: true, color: { argb: 'FFFFFFFF' } };
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: { argb: NAVY } };
  });

  let total = 0;
  // Amount = CTC/M × Nos × Months (F = C×D×E)
  for (const o of overheads) {
    const amount = o.ctcPerMonth * o.nos * o.month;
    total += amount;
    const r = sheet.rowCount + 1;
    sheet.addRow([o.categoryDescription ?? '-', o.subCategory || '-', o.ctcPerMonth, o.nos, o.month, fx(`C${r}*D${r}*E${r}`, amount)]);
  }

  const grandTotal = sheet.addRow(['Grand Total', '', '', '', '', fx(`SUM(F2:F${overheads.length + 1})`, total)]);
  grandTotal.font = { bold: true };
  sheet.getColumn(6).numFmt = '#,##0.00';
}

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 workbook = new ExcelJS.Workbook();
    // Totals are formulas; have Excel recalculate them all when the file is opened
    workbook.calcProperties.fullCalcOnLoad = true;
    buildSummarySheet(workbook, project);
    buildDetailSheet(workbook, 'Supply', project.supply);
    buildDetailSheet(workbook, 'Civil Works', project.civil);
    buildDetailSheet(workbook, 'Installation', project.installation);
    buildDetailSheet(workbook, 'T & C', project.tc);
    buildOverheadsSheet(workbook, project.overheads);

    const filename = `${project.jobCode ?? project.id}_ACE.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);
  }
}