/**
 * XLSX export writer — financial-statements (wave-13).
 *
 * Wraps ExcelJS to produce formula-safe, RTL-correct, Hebrew-capable XLSX
 * files. All string cell values pass through neutralizeFormula() before
 * being written.
 *
 * Reused by: specs 171 (PCN874 tax report) and 175 (annual tax summary).
 */
import ExcelJS from 'exceljs'
import { neutralizeFormula } from './sanitize'
import type { ExportColumn, ExportSheet, XlsxExportOptions } from './types'

export async function writeXlsx(opts: XlsxExportOptions): Promise<Uint8Array> {
  const wb = new ExcelJS.Workbook()
  const fontName = opts.fontName ?? 'Arial'

  for (const sheet of opts.sheets) {
    const rtl = sheet.rightToLeft !== false // default true
    const ws = wb.addWorksheet(sheet.name, {
      views: [{ rightToLeft: rtl }],
    })

    // Map columns
    ws.columns = sheet.columns.map((col) => ({
      key: col.key,
      width: 20,
    }))

    // Header row
    const headerValues: string[] = sheet.columns.map((col) =>
      neutralizeFormula(col.header),
    )
    const headerRow = ws.addRow(headerValues)
    headerRow.font = { bold: true, name: fontName }
    headerRow.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE8E8E8' },
    } as ExcelJS.Fill

    // Data rows
    for (const rowData of sheet.rows) {
      const rowValues: (string | number | null | undefined)[] = sheet.columns.map((col) => {
        const raw = rowData[col.key]
        if (raw === null || raw === undefined) return null
        if (typeof raw === 'string') return neutralizeFormula(raw)
        return raw // number — pass through
      })
      const dataRow = ws.addRow(rowValues)

      // Apply font on all cells
      dataRow.eachCell((cell: ExcelJS.Cell) => {
        cell.font = { name: fontName }
      })

      // Format currency / number columns
      sheet.columns.forEach((col, i) => {
        if (col.type === 'currency' || col.type === 'number') {
          const cell = dataRow.getCell(i + 1)
          if (typeof cell.value === 'number') {
            cell.numFmt = '#,##0.00'
          }
        }
      })
    }
  }

  const buffer = await wb.xlsx.writeBuffer()
  // ExcelJS returns an ArrayBuffer or ArrayBuffer-like; coerce to Uint8Array
  return new Uint8Array(buffer as ArrayBuffer)
}