import type ExcelJS from "exceljs";
import { cellRole, GROUPS, HEADERS, isSolvedCell, SOLVE_LABEL, type CellRole } from "../grid/columns";
import { SIDES, type RowState } from "../grid/rowModel";
import type { MarketData } from "../market/sx5e";
import { isoDate } from "../pricing";

/** The grid's light-theme colours, so the sheet reads like the screen. */
const FILL: Record<CellRole, string> = {
  mark: "FFF6EBCF",
  "price-lit": "FFC9C4F3",
  "price-dark": "FFF1F0FC",
  "assump-lit": "FFA9E3CD",
  "assump-dark": "FFECF7F2",
};
const SOLVED_BORDER: Partial<ExcelJS.Border> = { style: "medium", color: { argb: "FFEF4444" } };
const NUMBER = "#,##0.00";
const PERCENT = "0.00%";

/** Layout: market block, two header rows (groups; Bid / Mark / Ask), then one row per tenor. */
const FIRST_VALUE_COL = 4; // A = Expiry, B = Expiry date, C = Solve for
const HEADER_ROW = 5;
const FIRST_DATA_ROW = HEADER_ROW + 2;

/**
 * Builds the pricer table as an Excel workbook: values as numbers (all-in as a percentage),
 * the grid's lit / solved / mark colours, and a red outline around each row's solved cells.
 */
export function buildPricerWorkbook(
  Excel: typeof ExcelJS,
  market: MarketData,
  rows: RowState[],
  exportedAt: Date = new Date(),
): ExcelJS.Workbook {
  const wb = new Excel.Workbook();
  wb.creator = "Funding Pricer";
  const ws = wb.addWorksheet("Pricer", {
    views: [{ state: "frozen", xSplit: FIRST_VALUE_COL - 1, ySplit: FIRST_DATA_ROW - 1 }],
  });

  ws.getCell("A1").value = "Funding Pricer — SX5E synthetics and TRFs";
  ws.getCell("A1").font = { bold: true, size: 13 };
  const market_: [string, ExcelJS.CellValue, string][] = [
    ["Valuation date", new Date(isoDate(market.valuationDate)), "yyyy-mm-dd"],
    ["Spot", market.spot, NUMBER],
    ["€STR (cc)", market.rate, "0.000%"],
    ["EQL spread", market.eqlSpread, "0.000%"],
    ["Exported at", exportedAt, "yyyy-mm-dd hh:mm:ss"],
  ];
  market_.forEach(([label, value, fmt], i) => {
    const head = ws.getCell(2, i + 1);
    head.value = label;
    head.font = { bold: true, color: { argb: "FF6B6A65" } };
    const cell = ws.getCell(3, i + 1);
    cell.value = value;
    cell.numFmt = fmt;
  });

  // headers
  const bold = { bold: true };
  ["Expiry", "Expiry date", "Solve for"].forEach((h, i) => {
    ws.mergeCells(HEADER_ROW, i + 1, HEADER_ROW + 1, i + 1);
    const c = ws.getCell(HEADER_ROW, i + 1);
    c.value = h;
    c.font = bold;
    c.alignment = { vertical: "bottom" };
  });
  GROUPS.forEach((g, gi) => {
    const col = FIRST_VALUE_COL + gi * SIDES.length;
    ws.mergeCells(HEADER_ROW, col, HEADER_ROW, col + SIDES.length - 1);
    const c = ws.getCell(HEADER_ROW, col);
    c.value = HEADERS[g];
    c.font = bold;
    c.alignment = { horizontal: "center" };
    SIDES.forEach((side, si) => {
      const sub = ws.getCell(HEADER_ROW + 1, col + si);
      sub.value = { bid: "Bid", mark: "Mark", ask: "Ask" }[side];
      sub.font = bold;
      sub.alignment = { horizontal: "right" };
    });
  });

  // rows
  const solved = (ri: number, gi: number) => gi >= 0 && gi < GROUPS.length && ri >= 0 && ri < rows.length && isSolvedCell(rows[ri], GROUPS[gi]);
  rows.forEach((r, ri) => {
    const rowNo = FIRST_DATA_ROW + ri;
    ws.getCell(rowNo, 1).value = r.expiry.label;
    const date = ws.getCell(rowNo, 2);
    date.value = new Date(isoDate(r.expiry.date));
    date.numFmt = "yyyy-mm-dd";
    ws.getCell(rowNo, 3).value = SOLVE_LABEL[r.dark];

    GROUPS.forEach((g, gi) => {
      SIDES.forEach((side, si) => {
        const c = ws.getCell(rowNo, FIRST_VALUE_COL + gi * SIDES.length + si);
        c.value = r[g][side];
        c.numFmt = g === "allIn" ? PERCENT : NUMBER;
        const role = cellRole(r, g, side);
        c.fill = { type: "pattern", pattern: "solid", fgColor: { argb: FILL[role] } };
        if (role === "mark") c.font = bold;
        // outline solved groups, merged across neighbouring solved cells (as on screen)
        if (solved(ri, gi)) {
          const first = si === 0;
          const last = si === SIDES.length - 1;
          c.border = {
            top: solved(ri - 1, gi) ? undefined : SOLVED_BORDER,
            bottom: solved(ri + 1, gi) ? undefined : SOLVED_BORDER,
            left: first && !solved(ri, gi - 1) ? SOLVED_BORDER : undefined,
            right: last && !solved(ri, gi + 1) ? SOLVED_BORDER : undefined,
          };
        }
      });
    });
  });

  ws.getColumn(1).width = 9;
  ws.getColumn(2).width = 12;
  ws.getColumn(3).width = 12;
  for (let c = FIRST_VALUE_COL; c < FIRST_VALUE_COL + GROUPS.length * SIDES.length; c++) ws.getColumn(c).width = 11;
  return wb;
}