/**
 * RTL Hebrew Excel export — expenses-module.
 * Uses exceljs to produce RTL spreadsheets with Hebrew column headers.
 */
import ExcelJS from 'exceljs'
import { neutralizeFormula } from '@zync/exporters'
import type { ExpenseReportRow, VatSummary } from './reports'

// ── Expense Report XLSX ────────────────────────────────────────────────────────

export async function buildExpenseReportXlsx(rows: ExpenseReportRow[]): Promise<ArrayBuffer> {
  const wb = new ExcelJS.Workbook()
  const ws = wb.addWorksheet('דוח הוצאות', {
    views: [{ rightToLeft: true }],
  })

  // Hebrew column headers
  ws.columns = [
    { header: 'תאריך',           key: 'date',            width: 14 },
    { header: 'ספק',             key: 'vendor',          width: 24 },
    { header: 'מס\' חשבונית',   key: 'invoiceNumber',   width: 18 },
    { header: 'ח.פ. ספק',       key: 'vendorTaxId',     width: 16 },
    { header: 'סה"כ (₪)',        key: 'total',           width: 14 },
    { header: 'מע"מ (₪)',        key: 'vat',             width: 14 },
    { header: 'נטו (₪)',         key: 'net',             width: 14 },
    { header: 'קטגוריה',         key: 'category',        width: 18 },
    { header: '% ניכוי',         key: 'deductionPct',    width: 12 },
    { header: 'מס\' הקצאה',     key: 'allocationNumber', width: 16 },
    { header: 'הערות',           key: 'notes',           width: 24 },
  ]

  // Style header row
  const headerRow = ws.getRow(1)
  headerRow.font = { bold: true }
  headerRow.fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFE8E8E8' },
  }

  for (const row of rows) {
    ws.addRow({
      date: row.date ?? '',
      vendor: neutralizeFormula(row.vendor ?? ''),
      invoiceNumber: neutralizeFormula(row.invoiceNumber ?? ''),
      vendorTaxId: neutralizeFormula(row.vendorTaxId ?? ''),
      total: row.total ? parseFloat(row.total) : '',
      vat: row.vat ? parseFloat(row.vat) : '',
      net: row.net ? parseFloat(row.net) : '',
      category: neutralizeFormula(row.category ?? ''),
      deductionPct: row.deductionPct ?? '',
      allocationNumber: neutralizeFormula(row.allocationNumber ?? ''),
      notes: neutralizeFormula(row.notes ?? ''),
    })
  }

  // Format numeric columns
  for (const col of ['E', 'F', 'G']) {
    ws.getColumn(col).numFmt = '#,##0.00'
  }

  const buffer = await wb.xlsx.writeBuffer()
  return buffer
}

// ── VAT (PCN874) XLSX ──────────────────────────────────────────────────────────

export async function buildVatXlsx(summary: VatSummary): Promise<ArrayBuffer> {
  const wb = new ExcelJS.Workbook()
  const ws = wb.addWorksheet('PCN874 - סיכום מע"מ', {
    views: [{ rightToLeft: true }],
  })

  ws.columns = [
    { header: 'שדה',   key: 'field', width: 32 },
    { header: 'סכום',  key: 'value', width: 20 },
  ]

  const headerRow = ws.getRow(1)
  headerRow.font = { bold: true }
  headerRow.fill = {
    type: 'pattern',
    pattern: 'solid',
    fgColor: { argb: 'FFE8E8E8' },
  }

  ws.addRow({ field: 'תקופת דיווח',            value: summary.period })
  ws.addRow({ field: 'מע"מ תשומות (מלא)',       value: parseFloat(summary.inputVat) })
  ws.addRow({ field: 'מע"מ תשומות (חלקי)',      value: parseFloat(summary.partialInputVat) })
  ws.addRow({ field: 'מע"מ עסקאות (תשומות)',    value: parseFloat(summary.outputVat) })
  ws.addRow({ field: 'מע"מ לתשלום / להחזר',    value: parseFloat(summary.netVatDue) })

  ws.getColumn('B').numFmt = '#,##0.00'

  const buffer = await wb.xlsx.writeBuffer()
  return buffer
}