// Excel-template helpers shared by all "verformelte" export templates
// (OEE, Stopwatch, VSM, Planning Reports, Assessment). Pattern was first
// developed for the OEE template in components/oee/oee-template-download-button.tsx
// and extracted here so every export template can ship with:
//   - role-based header fills (mandatory / mandatory-basis / optional / reference / computed)
//   - a yellow BEISPIEL row that is ignored on re-import
//   - pre-filled formulas across 100 data rows
//   - a paired "Anleitung" sheet built via a fluent helper
//
// Framework-agnostic: no React, no Supabase, no DOM. Callers dynamic-import
// `exceljs` in the browser; this module only types against ExcelJS.

import type ExcelJS from 'exceljs'

// ─── ARGB color tokens (ExcelJS fills expect { argb }) ────────────────────────
// ExcelJS xlsx cell fills are exempt from the app-side check:hardcoded-colors
// gate — these are spreadsheet colors, not CSS.
export const TEMPLATE_FILL = {
  BRAND_PETROL:  { argb: 'FF037493' },
  BASIS_PETROL:  { argb: 'FF025F78' },
  OPTIONAL_TEAL: { argb: 'FF5BA3B0' },
  COMPUTED_TEAL: { argb: 'FF1A5276' },
  EXAMPLE_AMBER: { argb: 'FFFFF3CD' },
  SECTION_LIGHT: { argb: 'FFE0F2FF' },
  TEXT_DARK:     { argb: 'FF353A41' },
  WHITE:         { argb: 'FFFFFFFF' },
  GREY:          { argb: 'FFAAAAAA' },
} as const

// ─── Column letter helper (1-based → A, Z, AA, …) ─────────────────────────────
export function colLetter(n: number): string {
  let s = ''
  while (n > 0) {
    const r = (n - 1) % 26
    s = String.fromCharCode(65 + r) + s
    n = Math.floor((n - 1) / 26)
  }
  return s
}

// ─── Column-role classification ───────────────────────────────────────────────
export type ColumnRole =
  | 'mandatory'        // * suffix, petrol fill
  | 'mandatory-basis'  // ** suffix, darker petrol — central calculation driver
  | 'optional'         // no suffix, muted teal fill
  | 'reference'        // "(Referenz)" suffix, muted teal — does not feed the calc
  | 'computed'         // no suffix, darker teal — formula cell, not user-editable

export interface ColumnSpec {
  /** Internal column name written into the header (machine-readable). */
  name: string
  /** Role drives header suffix + fill color. */
  role: ColumnRole
  /** Excel column width. Defaults to 16 when omitted. */
  width?: number
}

const ROLE_SUFFIX: Record<ColumnRole, string> = {
  'mandatory':       ' *',
  'mandatory-basis': ' **',
  'optional':        '',
  'reference':       ' (Referenz)',
  'computed':        '',
}

const ROLE_FILL: Record<ColumnRole, { argb: string }> = {
  'mandatory':       TEMPLATE_FILL.BRAND_PETROL,
  'mandatory-basis': TEMPLATE_FILL.BASIS_PETROL,
  'optional':        TEMPLATE_FILL.OPTIONAL_TEAL,
  'reference':       TEMPLATE_FILL.OPTIONAL_TEAL,
  'computed':        TEMPLATE_FILL.COMPUTED_TEAL,
}

export function headerLabel(spec: ColumnSpec): string {
  return spec.name + ROLE_SUFFIX[spec.role]
}

export function headerFill(spec: ColumnSpec): { argb: string } {
  return ROLE_FILL[spec.role]
}

// ─── Header writer ────────────────────────────────────────────────────────────
export interface HeaderOptions {
  /** Optional italic-grey hint row merged across all columns above the header. */
  noteText?: string
  /** Header row height. Default 30. */
  headerHeight?: number
  /** Optional note row height. Default 32. */
  noteHeight?: number
  /** Freeze rows above the data area. Default true. */
  freezePanes?: boolean
}

/**
 * Writes the header (and optional hint note) and sets per-column widths.
 * Returns the 1-based row number where the first data row should be placed.
 *
 *   no note: header at row 1, data starts at row 2
 *   with note: note at row 1, header at row 2, data starts at row 3
 */
export function writeHeader(
  ws: ExcelJS.Worksheet,
  cols: ColumnSpec[],
  opts: HeaderOptions = {},
): number {
  const totalCols = cols.length
  let headerRowIdx = 1

  if (opts.noteText) {
    ws.addRow([opts.noteText])
    ws.mergeCells(`A1:${colLetter(totalCols)}1`)
    const noteRow = ws.getRow(1)
    noteRow.getCell(1).font = { italic: true, size: 10, name: 'Calibri', color: TEMPLATE_FILL.GREY }
    noteRow.getCell(1).alignment = { wrapText: true }
    noteRow.height = opts.noteHeight ?? 32
    headerRowIdx = 2
  }

  const headerRow = ws.addRow(cols.map((c) => headerLabel(c)))
  headerRow.eachCell((cell, colIdx) => {
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: headerFill(cols[colIdx - 1]) }
    cell.font = { color: TEMPLATE_FILL.WHITE, bold: true, size: 10, name: 'Calibri' }
    cell.alignment = { vertical: 'middle', horizontal: 'center', wrapText: true }
  })
  headerRow.height = opts.headerHeight ?? 30

  cols.forEach((c, i) => {
    ws.getColumn(i + 1).width = c.width ?? 16
  })

  if (opts.freezePanes !== false) {
    ws.views = [{ state: 'frozen', ySplit: headerRowIdx }]
  }

  return headerRowIdx + 1
}

// ─── Example row writer ───────────────────────────────────────────────────────
export interface ExampleCell {
  /** Raw value OR an ExcelJS `{ formula: string }` object. */
  value: number | string | boolean | null | { formula: string }
  /** Optional Excel number format, e.g. '0.00%'. */
  numFmt?: string
}

/**
 * Writes the yellow BEISPIEL row (italic grey on amber) and returns its row
 * number. The sentinel value in the first cell typically reads
 * "BEISPIEL (nicht löschen – wird beim Import ignoriert)" so the import path
 * can detect and skip it.
 */
export function writeExampleRow(ws: ExcelJS.Worksheet, cells: ExampleCell[]): number {
  const values = cells.map((c) => c.value)
  const row = ws.addRow(values)
  row.eachCell({ includeEmpty: true }, (cell, colIdx) => {
    cell.fill = { type: 'pattern', pattern: 'solid', fgColor: TEMPLATE_FILL.EXAMPLE_AMBER }
    cell.font = { italic: true, size: 10, name: 'Calibri', color: TEMPLATE_FILL.GREY }
    const numFmt = cells[colIdx - 1]?.numFmt
    if (numFmt) cell.numFmt = numFmt
  })
  return row.number
}

// ─── Formula pre-fill over data rows ──────────────────────────────────────────
export interface FormulaColumnSpec {
  /** 1-based Excel column number. */
  colIndex: number
  /** Returns the formula string for a given Excel data row number. */
  build: (row: number) => string
  /** Optional cell number format (e.g. '0.00%', '0.0'). */
  numFmt?: string
}

/**
 * Pre-fills `dataRows` Excel rows starting at `startRow` with formulas for
 * each column. Each cell becomes `{ formula: build(row) }` so it recalculates
 * live once the user fills the row's input columns.
 */
export function prefillFormulas(
  ws: ExcelJS.Worksheet,
  formulaCols: FormulaColumnSpec[],
  startRow: number,
  dataRows: number,
): void {
  for (let r = startRow; r < startRow + dataRows; r++) {
    const dataRow = ws.getRow(r)
    for (const fc of formulaCols) {
      dataRow.getCell(fc.colIndex).value = { formula: fc.build(r) }
      if (fc.numFmt) dataRow.getCell(fc.colIndex).numFmt = fc.numFmt
    }
  }
}

// ─── Anleitung-Sheet builder (fluent) ─────────────────────────────────────────
export class AnleitungSheet {
  private ws: ExcelJS.Worksheet

  constructor(wb: ExcelJS.Workbook, name = 'Anleitung', columnWidth = 90) {
    this.ws = wb.addWorksheet(name)
    this.ws.getColumn(1).width = columnWidth
    this.ws.views = [{ state: 'normal' }]
  }

  title(text: string): this {
    const row = this.ws.addRow([text])
    row.getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: TEMPLATE_FILL.BRAND_PETROL }
    row.getCell(1).font = { color: TEMPLATE_FILL.WHITE, bold: true, size: 12, name: 'Calibri' }
    row.getCell(1).alignment = { vertical: 'middle' }
    row.height = 24
    return this
  }

  section(text: string): this {
    const row = this.ws.addRow([text])
    row.getCell(1).fill = { type: 'pattern', pattern: 'solid', fgColor: TEMPLATE_FILL.SECTION_LIGHT }
    row.getCell(1).font = { bold: true, size: 10, name: 'Calibri', color: TEMPLATE_FILL.TEXT_DARK }
    row.height = 18
    return this
  }

  line(text: string): this {
    const row = this.ws.addRow([text])
    row.getCell(1).font = { size: 10, name: 'Calibri', color: TEMPLATE_FILL.TEXT_DARK }
    row.getCell(1).alignment = { wrapText: true }
    row.height = 16
    return this
  }

  blank(): this {
    this.ws.addRow([''])
    return this
  }

  /** `[label, description]` rows with the label left-padded to a fixed width. */
  columnDescriptions(rows: [string, string][], labelWidth = 32): this {
    for (const [label, desc] of rows) {
      this.line(`  ${label.padEnd(labelWidth, ' ')}${desc}`)
    }
    return this
  }

  worksheet(): ExcelJS.Worksheet {
    return this.ws
  }
}

// ─── Constants ────────────────────────────────────────────────────────────────

/** Standard legend tail for the hint note. Use as `'<context>   |   ' + LEGEND`. */
export const STANDARD_NOTE_LEGEND =
  '* = Pflichtfeld   ** = Berechnungs-Basis (Pflicht)   (Referenz) = optional, nur Dokumentation'

/** Sentinel value placed in the example row's first cell. Import paths match on
 *  the prefix "BEISPIEL" to detect and skip the row. */
export const EXAMPLE_ROW_SENTINEL = 'BEISPIEL (nicht löschen – wird beim Import ignoriert)'

/** Default number of data rows to pre-fill with formulas after the example. */
export const DEFAULT_DATA_ROWS = 100