import ExcelJS from "exceljs";
import JSZip from "jszip";
import type { ProjectFile } from "../model/Project.ts";
import type { Duration } from "../model/Duration.ts";
import { TimeUnit } from "../model/types.ts";

export interface XlsxWriterOptions {
  /** Worksheet name. Default: "Schedule". */
  sheetName?: string;
}

export interface GanttBar {
  name: string;
  /** ISO date string YYYY-MM-DD. */
  start: string;
  /** ISO date string YYYY-MM-DD. */
  finish: string;
}

export interface GanttPhase {
  label: string;
  items: GanttBar[];
}

export interface GanttSpec {
  phases: GanttPhase[];
  /** Chart title. Default: "Gantt". */
  title?: string;
  /** Hex colors (no #) cycled per phase. Default: ["4472C4", "ED7D31"] (blue, orange). */
  phaseColors?: string[];
}

const DEFAULT_PHASE_COLORS = ["4472C4", "ED7D31"];

const HEADER_FILL: ExcelJS.Fill = {
  type: "pattern",
  pattern: "solid",
  fgColor: { argb: "FF4472C4" },
};
const HEADER_FONT: Partial<ExcelJS.Font> = {
  bold: true,
  color: { argb: "FFFFFFFF" },
};

/** Visual style hooks for spec-driven sheets. The semantics (e.g. muted rows
 *  placed outside SUM ranges, INTERNAL distribution warnings) are the
 *  consuming app's layout concern; the writer only renders the style. */
export type CellStyle = "header" | "input" | "derived" | "muted" | "internal";

export interface CellSpec {
  value?: unknown;
  /** Formula text without leading "=". Written without a cached result so
   *  Excel/LibreOffice recalculate on open. */
  formula?: string;
  numFmt?: string;
  /** Cell comment (parameter notes). */
  note?: string;
  style?: CellStyle;
}

export interface SheetSpec {
  name: string;
  rows: CellSpec[][];
  /** Workbook-level defined names, e.g. { name: "Rate_Install_Blended",
   *  range: "Pricing!$B$4" }. Emitters that write formulas against named
   *  ranges only survive row insertion. */
  namedRanges?: { name: string; range: string }[];
  /** 1-based inclusive row spans to group under an outline level. */
  outlineRows?: { from: number; to: number; level: number }[];
}

/**
 * Converts a Date to an Excel serial number (days since 1899-12-30),
 * avoiding timezone issues by working directly with date components.
 */
function dateToSerial(date: Date): number {
  const y = date.getFullYear();
  const m = date.getMonth(); // 0-based
  const d = date.getDate();
  const h = date.getHours();
  const min = date.getMinutes();

  // Excel epoch: day 0 = 1899-12-30 (includes the intentional 1900 leap year bug)
  const epoch = Date.UTC(1899, 11, 30);
  const dateUtc = Date.UTC(y, m, d);
  const days = (dateUtc - epoch) / 86400000;
  const timeFraction = (h * 60 + min) / 1440;
  return days + timeFraction;
}

function ymdToSerial(ymd: string): number {
  const match = /^(\d{4})-(\d{2})-(\d{2})$/.exec(ymd);
  if (!match) throw new Error(`Invalid YYYY-MM-DD date: ${ymd}`);
  const y = Number(match[1]);
  const mo = Number(match[2]);
  const d = Number(match[3]);
  const epoch = Date.UTC(1899, 11, 30);
  const utc = Date.UTC(y, mo - 1, d);
  return (utc - epoch) / 86400000;
}

function xmlEscape(text: string): string {
  return text
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    .replace(/'/g, "&apos;");
}

function durationUnitLabel(dur: Duration): string {
  switch (dur.unit) {
    case TimeUnit.Minutes:
      return '" min"';
    case TimeUnit.Hours:
      return '" hrs"';
    case TimeUnit.Days:
      return '" days"';
    case TimeUnit.Weeks:
      return '" wks"';
    case TimeUnit.Months:
      return '" mos"';
    case TimeUnit.Percent:
      return '"%"';
    default:
      return "";
  }
}

export class XlsxWriter {
  async write(
    project: ProjectFile,
    options?: XlsxWriterOptions,
  ): Promise<Uint8Array> {
    return toBuffer(this.buildWorkbook(project, options));
  }

  /**
   * Like {@link write}, but also adds a hidden `_Gantt Data` sheet and a
   * `Gantt` sheet hosting a stacked horizontal bar chart. Series 1 is the
   * transparent offset; series 2 is the visible duration, colored per phase.
   */
  async writeWithGantt(
    project: ProjectFile,
    gantt: GanttSpec,
    options?: XlsxWriterOptions,
  ): Promise<Uint8Array> {
    return this.composeGanttWorkbook(project, gantt, options);
  }

  private async composeGanttWorkbook(
    project: ProjectFile,
    gantt: GanttSpec,
    options?: XlsxWriterOptions,
    specSheets?: SheetSpec[],
  ): Promise<Uint8Array> {
    const flat: { phaseIdx: number; bar: GanttBar }[] = [];
    for (let pi = 0; pi < gantt.phases.length; pi += 1) {
      for (const bar of gantt.phases[pi]!.items) {
        flat.push({ phaseIdx: pi, bar });
      }
    }

    if (flat.length === 0) {
      const workbook = this.buildWorkbook(project, options);
      if (specSheets) addSpecSheets(workbook, specSheets);
      return toBuffer(workbook);
    }

    flat.sort((a, b) => {
      const sa = ymdToSerial(a.bar.start);
      const sb = ymdToSerial(b.bar.start);
      if (sa !== sb) return sa - sb;
      return a.bar.name.localeCompare(b.bar.name);
    });

    const colors = gantt.phaseColors ?? DEFAULT_PHASE_COLORS;
    const items = flat.map(({ phaseIdx, bar }) => {
      const offset = ymdToSerial(bar.start);
      const finish = ymdToSerial(bar.finish);
      return {
        label: `${gantt.phases[phaseIdx]!.label}: ${bar.name}`,
        offset,
        duration: Math.max(1, finish - offset),
        color: colors[phaseIdx % colors.length]!,
      };
    });

    const origin = Math.min(...items.map((i) => i.offset));
    const end = Math.max(...items.map((i) => i.offset + i.duration));

    const workbook = this.buildWorkbook(project, options);

    const dataSheetName = "_Gantt Data";
    const dataSheet = workbook.addWorksheet(dataSheetName, { state: "hidden" });
    dataSheet.addRow(["Item", "Offset", "Duration"]);
    for (const item of items) {
      dataSheet.addRow([item.label, item.offset, item.duration]);
    }

    workbook.addWorksheet("Gantt");

    // Spec sheets come after the Gantt sheet: injectGanttChart patches
    // sheet3.xml by position (Schedule, _Gantt Data, Gantt).
    if (specSheets) addSpecSheets(workbook, specSheets);

    const buffer = await workbook.xlsx.writeBuffer();
    const patched = await injectGanttChart(buffer, {
      items,
      origin,
      end,
      title: gantt.title ?? "Gantt",
      dataSheetName,
    });
    return new Uint8Array(patched);
  }

  /**
   * Writes a workbook from declarative sheet specs: literal values, formula
   * cells (no cached result), named ranges, cell notes, row outlines, and
   * the {@link CellStyle} hooks. Formula strings are opaque to the writer —
   * it writes what it is given.
   */
  async writeSheets(sheets: SheetSpec[]): Promise<Uint8Array> {
    const workbook = new ExcelJS.Workbook();
    addSpecSheets(workbook, sheets);
    return toBuffer(workbook);
  }

  /**
   * Escape hatch: composes spec sheets into a workbook that also carries the
   * existing schedule/Gantt sheets. Spec sheets are appended after the Gantt
   * sheet so the chart-injection sheet indices stay stable.
   */
  async writeWithGanttAndSheets(
    project: ProjectFile,
    gantt: GanttSpec,
    sheets: SheetSpec[],
    options?: XlsxWriterOptions,
  ): Promise<Uint8Array> {
    return this.composeGanttWorkbook(project, gantt, options, sheets);
  }

  private buildWorkbook(
    project: ProjectFile,
    options?: XlsxWriterOptions,
  ): ExcelJS.Workbook {
    const sheetName = options?.sheetName ?? "Schedule";

    const workbook = new ExcelJS.Workbook();
    const sheet = workbook.addWorksheet(sheetName, {
      properties: { outlineLevelRow: 4 },
    });

    sheet.columns = [
      { header: "ID", key: "id", width: 6 },
      { header: "Task Name", key: "name", width: 55 },
      { header: "WBS", key: "wbs", width: 10 },
      { header: "Start", key: "start", width: 18 },
      { header: "Finish", key: "finish", width: 18 },
      { header: "Duration", key: "duration", width: 14 },
    ];

    // Style header row
    const headerRow = sheet.getRow(1);
    headerRow.fill = HEADER_FILL;
    headerRow.font = HEADER_FONT;

    const dateFormat = "M/D/YYYY H:MM AM/PM";

    for (const task of project.tasks) {
      if (task.id === null) continue;

      const startSerial = task.start ? dateToSerial(task.start) : null;
      const finishSerial = task.finish ? dateToSerial(task.finish) : null;

      const row = sheet.addRow({
        id: task.id,
        name: task.name,
        wbs: task.wbs,
        start: startSerial,
        finish: finishSerial,
        duration: task.duration?.value ?? null,
      });

      if (startSerial) {
        row.getCell("start").numFmt = dateFormat;
      }
      if (finishSerial) {
        row.getCell("finish").numFmt = dateFormat;
      }

      if (task.duration) {
        const label = durationUnitLabel(task.duration);
        row.getCell("duration").numFmt = `#,##0.##${label}`;
      }

      const outlineLevel = task.outlineLevel ?? 0;
      if (outlineLevel > 0) {
        row.getCell("name").alignment = { indent: (outlineLevel - 1) * 2 };
      }

      if (task.summary) {
        row.font = { bold: true };
      }

      // Excel caps the outline level at 7.
      if (outlineLevel > 0) {
        row.outlineLevel = Math.min(outlineLevel, 7);
      }
    }

    sheet.properties.outlineLevelRow = 4;

    return workbook;
  }
}

async function toBuffer(workbook: ExcelJS.Workbook): Promise<Uint8Array> {
  return new Uint8Array(await workbook.xlsx.writeBuffer());
}

function applyCellStyle(cell: ExcelJS.Cell, style: CellStyle): void {
  switch (style) {
    case "header":
      cell.fill = HEADER_FILL;
      cell.font = HEADER_FONT;
      break;
    case "input":
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: "FFFFF2CC" },
      };
      break;
    case "derived":
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: "FFF2F2F2" },
      };
      break;
    case "muted":
      cell.font = { color: { argb: "FF808080" }, strike: true };
      break;
    case "internal":
      cell.fill = {
        type: "pattern",
        pattern: "solid",
        fgColor: { argb: "FFFCE4D6" },
      };
      break;
  }
}

function addSpecSheets(workbook: ExcelJS.Workbook, sheets: SheetSpec[]): void {
  for (const spec of sheets) {
    const sheet = workbook.addWorksheet(spec.name);

    for (let r = 0; r < spec.rows.length; r += 1) {
      const rowSpec = spec.rows[r]!;
      for (let c = 0; c < rowSpec.length; c += 1) {
        const cellSpec = rowSpec[c]!;
        const cell = sheet.getCell(r + 1, c + 1);
        if (cellSpec.formula !== undefined) {
          // No cached result — Excel/LibreOffice recalculate on open.
          cell.value = { formula: cellSpec.formula };
        } else if (cellSpec.value !== undefined) {
          cell.value = cellSpec.value as ExcelJS.CellValue;
        }
        if (cellSpec.numFmt) cell.numFmt = cellSpec.numFmt;
        if (cellSpec.note) cell.note = cellSpec.note;
        if (cellSpec.style) applyCellStyle(cell, cellSpec.style);
      }
    }

    for (const outline of spec.outlineRows ?? []) {
      for (let r = outline.from; r <= outline.to; r += 1) {
        // Excel caps the outline level at 7.
        sheet.getRow(r).outlineLevel = Math.min(outline.level, 7);
      }
    }

    for (const named of spec.namedRanges ?? []) {
      workbook.definedNames.add(named.range, named.name);
    }
  }
}

interface GanttItem {
  label: string;
  offset: number;
  duration: number;
  color: string;
}

interface InjectArgs {
  items: GanttItem[];
  origin: number;
  end: number;
  title: string;
  dataSheetName: string;
}

async function injectGanttChart(
  buffer: ArrayBuffer | Uint8Array,
  { items, origin, end, title, dataSheetName }: InjectArgs,
): Promise<ArrayBuffer> {
  const zip = await JSZip.loadAsync(buffer);

  // The Gantt sheet is the 3rd sheet (Schedule, _Gantt Data, Gantt) and gets
  // written as sheet3.xml by ExcelJS in insertion order.
  const ganttSheetPath = "xl/worksheets/sheet3.xml";
  const ganttRelsPath = "xl/worksheets/_rels/sheet3.xml.rels";
  const drawingPath = "xl/drawings/drawing1.xml";
  const drawingRelsPath = "xl/drawings/_rels/drawing1.xml.rels";
  const chartPath = "xl/charts/chart1.xml";
  const contentTypesPath = "[Content_Types].xml";

  zip.file(
    chartPath,
    buildChartXml({ items, origin, end, title, dataSheetName }),
  );
  zip.file(drawingPath, buildDrawingXml(items.length));
  zip.file(drawingRelsPath, buildDrawingRelsXml());
  zip.file(ganttRelsPath, buildSheetRelsXml());

  const sheetXml = await zip.file(ganttSheetPath)!.async("string");
  zip.file(
    ganttSheetPath,
    sheetXml.replace("</worksheet>", '<drawing r:id="rId1"/></worksheet>'),
  );

  const ctXml = await zip.file(contentTypesPath)!.async("string");
  const ctPatched = ctXml.replace(
    "</Types>",
    '<Override PartName="/xl/charts/chart1.xml" ContentType="application/vnd.openxmlformats-officedocument.drawingml.chart+xml"/>' +
      '<Override PartName="/xl/drawings/drawing1.xml" ContentType="application/vnd.openxmlformats-officedocument.drawing+xml"/>' +
      "</Types>",
  );
  zip.file(contentTypesPath, ctPatched);

  return zip.generateAsync({ type: "arraybuffer", compression: "DEFLATE" });
}

/**
 * Builds a gradient-fill `<c:spPr>` for the plot area that produces
 * alternating horizontal row bands (white / light gray), one band per
 * category. Each adjacent stop pair uses identical positions so the
 * gradient becomes a hard step transition.
 */
function buildPlotShadingXml(n: number): string {
  if (n <= 0) return "";
  const EVEN = "FFFFFF";
  const ODD = "F4F4F4";
  const stops: string[] = [];
  for (let i = 0; i < n; i += 1) {
    const startPos = Math.round((i / n) * 100000);
    const endPos = Math.round(((i + 1) / n) * 100000);
    const color = i % 2 === 0 ? EVEN : ODD;
    stops.push(`<a:gs pos="${startPos}"><a:srgbClr val="${color}"/></a:gs>`);
    stops.push(`<a:gs pos="${endPos}"><a:srgbClr val="${color}"/></a:gs>`);
  }
  return (
    "<c:spPr>" +
    '<a:gradFill rotWithShape="1">' +
    `<a:gsLst>${stops.join("")}</a:gsLst>` +
    // 5400000 = 90° in 60000ths of a degree = top-to-bottom gradient.
    // catAx uses orientation="maxMin", so band i=0 lands on the top row.
    '<a:lin ang="5400000" scaled="0"/>' +
    "</a:gradFill>" +
    "<a:ln><a:noFill/></a:ln>" +
    "</c:spPr>"
  );
}

function buildChartXml({
  items,
  origin,
  end,
  title,
  dataSheetName,
}: InjectArgs): string {
  const n = items.length;
  const lastRow = n + 1;
  const sheetRef = `'${dataSheetName.replace(/'/g, "''")}'`;

  const catCache = items
    .map((it, i) => `<c:pt idx="${i}"><c:v>${xmlEscape(it.label)}</c:v></c:pt>`)
    .join("");
  const offsetCache = items
    .map((it, i) => `<c:pt idx="${i}"><c:v>${it.offset}</c:v></c:pt>`)
    .join("");
  const durCache = items
    .map((it, i) => `<c:pt idx="${i}"><c:v>${it.duration}</c:v></c:pt>`)
    .join("");
  const dPts = items
    .map(
      (it, i) =>
        `<c:dPt><c:idx val="${i}"/><c:invertIfNegative val="0"/><c:bubble3D val="0"/>` +
        `<c:spPr><a:solidFill><a:srgbClr val="${it.color}"/></a:solidFill>` +
        `<a:ln><a:solidFill><a:srgbClr val="${it.color}"/></a:solidFill></a:ln></c:spPr></c:dPt>`,
    )
    .join("");

  return (
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
    '<c:chartSpace xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart"' +
    ' xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"' +
    ' xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">' +
    "<c:chart>" +
    "<c:title><c:tx><c:rich><a:bodyPr/><a:lstStyle/>" +
    `<a:p><a:r><a:t>${xmlEscape(title)}</a:t></a:r></a:p>` +
    '</c:rich></c:tx><c:overlay val="0"/></c:title>' +
    '<c:autoTitleDeleted val="0"/>' +
    "<c:plotArea><c:layout/>" +
    "<c:barChart>" +
    '<c:barDir val="bar"/><c:grouping val="stacked"/><c:varyColors val="0"/>' +
    // Series 0: invisible offset
    "<c:ser>" +
    '<c:idx val="0"/><c:order val="0"/>' +
    `<c:tx><c:strRef><c:f>${sheetRef}!$B$1</c:f>` +
    '<c:strCache><c:ptCount val="1"/><c:pt idx="0"><c:v>Offset</c:v></c:pt></c:strCache>' +
    "</c:strRef></c:tx>" +
    "<c:spPr><a:noFill/><a:ln><a:noFill/></a:ln></c:spPr>" +
    '<c:invertIfNegative val="0"/>' +
    `<c:cat><c:strRef><c:f>${sheetRef}!$A$2:$A$${lastRow}</c:f>` +
    `<c:strCache><c:ptCount val="${n}"/>${catCache}</c:strCache></c:strRef></c:cat>` +
    `<c:val><c:numRef><c:f>${sheetRef}!$B$2:$B$${lastRow}</c:f>` +
    `<c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="${n}"/>${offsetCache}</c:numCache>` +
    "</c:numRef></c:val>" +
    "</c:ser>" +
    // Series 1: duration, per-point colors
    "<c:ser>" +
    '<c:idx val="1"/><c:order val="1"/>' +
    `<c:tx><c:strRef><c:f>${sheetRef}!$C$1</c:f>` +
    '<c:strCache><c:ptCount val="1"/><c:pt idx="0"><c:v>Duration</c:v></c:pt></c:strCache>' +
    "</c:strRef></c:tx>" +
    '<c:invertIfNegative val="0"/>' +
    dPts +
    `<c:cat><c:strRef><c:f>${sheetRef}!$A$2:$A$${lastRow}</c:f>` +
    `<c:strCache><c:ptCount val="${n}"/>${catCache}</c:strCache></c:strRef></c:cat>` +
    `<c:val><c:numRef><c:f>${sheetRef}!$C$2:$C$${lastRow}</c:f>` +
    `<c:numCache><c:formatCode>General</c:formatCode><c:ptCount val="${n}"/>${durCache}</c:numCache>` +
    "</c:numRef></c:val>" +
    "</c:ser>" +
    '<c:overlap val="100"/>' +
    '<c:axId val="111111111"/><c:axId val="222222222"/>' +
    "</c:barChart>" +
    // Category axis (visually the vertical axis on a horizontal bar chart)
    "<c:catAx>" +
    '<c:axId val="111111111"/>' +
    '<c:scaling><c:orientation val="maxMin"/></c:scaling>' +
    '<c:delete val="0"/><c:axPos val="l"/>' +
    '<c:crossAx val="222222222"/>' +
    "</c:catAx>" +
    // Value axis (the time axis along the bottom)
    "<c:valAx>" +
    '<c:axId val="222222222"/>' +
    `<c:scaling><c:orientation val="minMax"/><c:max val="${end}"/><c:min val="${origin}"/></c:scaling>` +
    '<c:delete val="0"/><c:axPos val="b"/>' +
    '<c:numFmt formatCode="mmm-yy" sourceLinked="0"/>' +
    '<c:majorTickMark val="out"/><c:minorTickMark val="none"/>' +
    '<c:crossAx val="111111111"/>' +
    '<c:majorUnit val="30"/>' +
    "</c:valAx>" +
    buildPlotShadingXml(n) +
    "</c:plotArea>" +
    '<c:plotVisOnly val="1"/><c:dispBlanksAs val="gap"/>' +
    "</c:chart></c:chartSpace>"
  );
}

function buildDrawingXml(n: number): string {
  // Anchor B2 to col 16, row ~ scaled by bar count so the chart is roughly proportional.
  const toRow = Math.max(20, n * 2 + 5);
  return (
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
    '<xdr:wsDr xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"' +
    ' xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"' +
    ' xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"' +
    ' xmlns:c="http://schemas.openxmlformats.org/drawingml/2006/chart">' +
    "<xdr:twoCellAnchor>" +
    "<xdr:from><xdr:col>1</xdr:col><xdr:colOff>0</xdr:colOff>" +
    "<xdr:row>1</xdr:row><xdr:rowOff>0</xdr:rowOff></xdr:from>" +
    `<xdr:to><xdr:col>16</xdr:col><xdr:colOff>0</xdr:colOff>` +
    `<xdr:row>${toRow}</xdr:row><xdr:rowOff>0</xdr:rowOff></xdr:to>` +
    '<xdr:graphicFrame macro="">' +
    '<xdr:nvGraphicFramePr><xdr:cNvPr id="2" name="Chart 1"/>' +
    "<xdr:cNvGraphicFramePr/></xdr:nvGraphicFramePr>" +
    '<xdr:xfrm><a:off x="0" y="0"/><a:ext cx="0" cy="0"/></xdr:xfrm>' +
    "<a:graphic>" +
    '<a:graphicData uri="http://schemas.openxmlformats.org/drawingml/2006/chart">' +
    '<c:chart r:id="rId1"/>' +
    "</a:graphicData></a:graphic>" +
    "</xdr:graphicFrame>" +
    "<xdr:clientData/>" +
    "</xdr:twoCellAnchor>" +
    "</xdr:wsDr>"
  );
}

function buildDrawingRelsXml(): string {
  return (
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
    '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' +
    '<Relationship Id="rId1"' +
    ' Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart"' +
    ' Target="../charts/chart1.xml"/>' +
    "</Relationships>"
  );
}

function buildSheetRelsXml(): string {
  return (
    '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>' +
    '<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">' +
    '<Relationship Id="rId1"' +
    ' Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/drawing"' +
    ' Target="../drawings/drawing1.xml"/>' +
    "</Relationships>"
  );
}