// Excel template + import helpers for the cycle-time stopwatch (KAR-704 Stoppuhr-Merge).
//
// Extracted from the legacy project stopwatch so the unified LSC stopwatch keeps
// the Excel bulk-import feature. The parsing/normalisation is pure and unit-tested;
// the workbook build + parse use a dynamic `exceljs` import so the heavy dependency
// stays out of the initial client bundle.

const XLSX_MIME =
  'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

export interface TemplateStep {
  station_name: string
}

export interface ParsedImportRow {
  /** Station name from the sheet, trimmed. Empty string when the column is blank. */
  stationName: string
  /** Cycle number from the sheet, or 0 when not provided (caller assigns a fallback). */
  cycleNum: number
  /** Cycle time in seconds, > 0, rounded to 2 decimals. */
  ct: number
  /** Optional note, or null. */
  notes: string | null
}

/**
 * Normalise one raw spreadsheet row (header → cell value) into a validated import
 * row. Accepts flexible column names and German/English decimal commas. Returns
 * null when the row has no usable cycle time (so invalid rows are skipped).
 *
 * Pure — this is the unit-tested core of the importer.
 */
export function normalizeImportRow(
  row: Record<string, unknown>
): ParsedImportRow | null {
  const stationRaw = row['Station'] ?? row['station'] ?? null
  const cycleRaw = row['Cycle #'] ?? row['cycle'] ?? row['Cycle'] ?? null
  const ctRaw =
    row['CT (seconds)'] ??
    row['CT'] ??
    row['ct'] ??
    row['cycle_time_sec'] ??
    null
  const notesRaw = row['Notes'] ?? row['notes'] ?? null

  const ct = parseFloat(String(ctRaw ?? '').replace(',', '.'))
  if (isNaN(ct) || ct <= 0) return null

  const cycleNum = parseInt(String(cycleRaw ?? '0'), 10)

  return {
    stationName: String(stationRaw ?? '').trim(),
    cycleNum: isNaN(cycleNum) ? 0 : cycleNum,
    ct: parseFloat(ct.toFixed(2)),
    notes: notesRaw ? String(notesRaw) : null,
  }
}

export interface ProcessExportRow {
  station: string
  cycle: number
  ct: number
  notes: string | null
}

/**
 * Pure: the rows for a single process's Excel export, in measurement order
 * (sorted by `cycle_number`) and renumbered #1..n. Used both by the per-process
 * download (build a workbook from these) and unit tests.
 */
export function buildProcessExportRows(
  stationName: string,
  measurements: ReadonlyArray<{
    cycle_number: number
    cycle_time_sec: number
    notes?: string | null
  }>
): ProcessExportRow[] {
  return [...measurements]
    .sort((a, b) => a.cycle_number - b.cycle_number)
    .map((m, i) => ({
      station: stationName,
      cycle: i + 1,
      ct: m.cycle_time_sec,
      notes: m.notes ?? null,
    }))
}

export interface OverwriteInsert {
  process_step_id: string
  cycle_number: number
  cycle_time_sec: number
  notes: string | null
}

/**
 * Pure: map parsed import rows to `cycle_measurements` insert rows for ONE
 * process, renumbered #1..n in the uploaded order. The caller deletes the
 * process's existing measurements first, so re-upload OVERWRITES the process.
 */
export function buildOverwriteInserts(
  processStepId: string,
  rows: ReadonlyArray<ParsedImportRow>
): OverwriteInsert[] {
  return rows.map((r, i) => ({
    process_step_id: processStepId,
    cycle_number: i + 1,
    cycle_time_sec: r.ct,
    notes: r.notes,
  }))
}

/**
 * Build a single process's measurements as an `.xlsx` blob — same column layout
 * as the import template, so a downloaded file re-imports cleanly. Rows come
 * from {@link buildProcessExportRows}.
 */
export async function buildProcessMeasurementsBlob(
  stationName: string,
  measurements: ReadonlyArray<{
    cycle_number: number
    cycle_time_sec: number
    notes?: string | null
  }>
): Promise<Blob> {
  const ExcelJS = (await import('exceljs')).default
  const wb = new ExcelJS.Workbook()
  const ws = wb.addWorksheet('Cycle Times')
  ws.columns = [
    { header: 'Station', key: 'station', width: 24 },
    { header: 'Cycle #', key: 'cycle', width: 10 },
    { header: 'CT (seconds)', key: 'ct', width: 14 },
    { header: 'Notes', key: 'notes', width: 30 },
  ]
  ws.getRow(1).font = { bold: true, color: { argb: 'FFFFFFFF' } }
  ws.getRow(1).fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FF0D4F4F' },
  }
  for (const row of buildProcessExportRows(stationName, measurements)) {
    ws.addRow(row)
  }
  const buf = await wb.xlsx.writeBuffer()
  return new Blob([buf], { type: XLSX_MIME })
}

/**
 * Build the `.xlsx` import template as a blob. Columns: Station, Cycle #,
 * CT (seconds), Notes — with three example rows seeded from the first station.
 */
export async function buildCycleTemplateBlob(
  steps: TemplateStep[]
): Promise<Blob> {
  const ExcelJS = (await import('exceljs')).default
  const wb = new ExcelJS.Workbook()
  const ws = wb.addWorksheet('Cycle Times')
  ws.columns = [
    { header: 'Station', key: 'station', width: 20 },
    { header: 'Cycle #', key: 'cycle', width: 10 },
    { header: 'CT (seconds)', key: 'ct', width: 14 },
    { header: 'Notes', key: 'notes', width: 30 },
  ]
  ws.getRow(1).font = { bold: true, color: { argb: 'FFFFFFFF' } }
  ws.getRow(1).fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FF0D4F4F' },
  }

  const sample = steps[0]?.station_name ?? 'Station Name'
  for (let i = 1; i <= 3; i++) {
    ws.addRow({ station: sample, cycle: i, ct: '', notes: '' })
  }

  const buf = await wb.xlsx.writeBuffer()
  return new Blob([buf], { type: XLSX_MIME })
}

/**
 * Parse an uploaded `.xlsx` buffer into validated cycle rows. Reads the header
 * row, maps each data row through {@link normalizeImportRow}, and prefers the
 * computed `result` for formula cells (falls back to the formula value).
 */
export async function parseCycleImportBuffer(
  buffer: ArrayBuffer
): Promise<ParsedImportRow[]> {
  const ExcelJS = (await import('exceljs')).default
  const wb = new ExcelJS.Workbook()
  await wb.xlsx.load(buffer)
  const ws = wb.worksheets[0]
  if (!ws) return []

  const headers: string[] = []
  ws.getRow(1).eachCell({ includeEmpty: true }, (cell, colNum) => {
    headers[colNum - 1] = String(cell.value ?? '')
  })

  const out: ParsedImportRow[] = []
  ws.eachRow({ includeEmpty: false }, (row, rowNum) => {
    if (rowNum === 1) return
    const obj: Record<string, unknown> = {}
    headers.forEach((h, i) => {
      const raw = row.getCell(i + 1).value
      const isFormula =
        raw !== null && typeof raw === 'object' && 'formula' in (raw as object)
      obj[h] = isFormula
        ? ((raw as { formula: unknown; result?: unknown }).result ?? null)
        : (raw ?? null)
    })
    const parsed = normalizeImportRow(obj)
    if (parsed) out.push(parsed)
  })
  return out
}