/**
 * annual-summary-xlsx.ts — RTL Hebrew Excel writer for Annual Income Summary.
 *
 * Builds an xlsx workbook with:
 *   - "Income Summary" sheet: invoiced income, bad debts, adjusted income
 *   - "Expense Breakdown" sheet: deductible expenses by category
 *   - "Net Taxable" sheet: total deductions and net taxable income
 *   - "Invoices" sheet: issued invoice list
 *   - "Mileage Log" sheet: business-trip mileage rows
 *   - "Contractor Payouts" sheet: PAID payout bills
 *
 * All sheets are RTL with Hebrew-capable font (Arial).
 * Formula injection is guarded via sanitizeCell.
 *
 * Spec: 2026-06-01-israeli-tax-reports (wave-14)
 */
import type ExcelJS from 'exceljs'
import {
  writeFinancialWorkbook,
  newRtlWorksheet,
  applyHebrewFont,
  sanitizeCell,
} from './financial-export'
import type { AnnualIncomeSummary, AnnualSummaryDetail } from '@zync/types'

function addHeaderRow(ws: ExcelJS.Worksheet, headers: string[]): void {
  const row = ws.addRow(headers.map((h) => sanitizeCell(h)))
  row.eachCell((cell) => {
    cell.font = { name: 'Arial', bold: true }
    cell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE0E0E0' },
    }
  })
}

function addDataRow(ws: ExcelJS.Worksheet, values: (string | number)[]): void {
  const row = ws.addRow(values.map((v) => sanitizeCell(typeof v === 'number' ? String(v) : v)))
  row.eachCell((cell) => {
    applyHebrewFont(cell)
  })
}

/**
 * Build the annual summary xlsx workbook and return raw bytes.
 */
export async function buildAnnualSummaryXlsx(
  summary: AnnualIncomeSummary,
  supporting: AnnualSummaryDetail,
): Promise<Uint8Array> {
  return writeFinancialWorkbook((wb) => {
    // ── Sheet 1: Income Summary ──
    const incomeWs = newRtlWorksheet(wb, `Income Summary ${summary.year}`)
    incomeWs.columns = [
      { key: 'item', width: 40 },
      { key: 'amount', width: 20 },
    ]
    addHeaderRow(incomeWs, ['Item', 'Amount (ILS)'])
    addDataRow(incomeWs, ['Total Invoiced Income (excl. VAT)', summary.totalInvoicedIncomeIls])
    addDataRow(incomeWs, ['Bad Debt Write-offs', `-${summary.badDebtWriteOffIls}`])
    addDataRow(incomeWs, ['Adjusted Income', summary.adjustedIncomeIls])

    // ── Sheet 2: Expense Breakdown ──
    const expWs = newRtlWorksheet(wb, 'Expense Breakdown')
    expWs.columns = [
      { key: 'category', width: 30 },
      { key: 'total', width: 20 },
      { key: 'deductible', width: 20 },
    ]
    addHeaderRow(expWs, ['Category', 'Total (ILS)', 'Deductible (ILS)'])
    for (const row of summary.expenseBreakdown) {
      addDataRow(expWs, [row.category, row.totalIls, row.deductibleIls])
    }
    addDataRow(expWs, ['TOTAL', '', summary.totalDeductionsIls])

    // ── Sheet 3: Net Taxable Income ──
    const netWs = newRtlWorksheet(wb, 'Net Taxable Income')
    netWs.columns = [
      { key: 'item', width: 40 },
      { key: 'amount', width: 20 },
    ]
    addHeaderRow(netWs, ['Item', 'Amount (ILS)'])
    addDataRow(netWs, ['Adjusted Income', summary.adjustedIncomeIls])
    addDataRow(netWs, ['Total Deductions', `-${summary.totalDeductionsIls}`])
    addDataRow(netWs, ['Net Taxable Income', summary.netTaxableIncomeIls])

    // ── Sheet 4: Invoices ──
    const invWs = newRtlWorksheet(wb, 'Invoices')
    invWs.columns = [
      { key: 'number', width: 16 },
      { key: 'date', width: 14 },
      { key: 'customer', width: 30 },
      { key: 'subtotal', width: 18 },
      { key: 'vat', width: 14 },
      { key: 'total', width: 18 },
    ]
    addHeaderRow(invWs, ['Number', 'Date', 'Customer', 'Subtotal (ILS)', 'VAT (ILS)', 'Total (ILS)'])
    for (const row of supporting.invoices) {
      addDataRow(invWs, [
        row.number,
        row.date,
        row.customer,
        row.subtotalIls,
        row.vatIls,
        row.totalIls,
      ])
    }

    // ── Sheet 5: Mileage Log ──
    const milWs = newRtlWorksheet(wb, 'Mileage Log')
    milWs.columns = [
      { key: 'date', width: 14 },
      { key: 'description', width: 40 },
      { key: 'distance', width: 16 },
      { key: 'reimbursement', width: 20 },
    ]
    addHeaderRow(milWs, ['Date', 'Description', 'Distance (km)', 'Reimbursement (ILS)'])
    for (const row of supporting.mileage) {
      addDataRow(milWs, [row.date, row.description, row.distanceKm, row.reimbursementIls])
    }

    // ── Sheet 6: Contractor Payouts ──
    const payWs = newRtlWorksheet(wb, 'Contractor Payouts')
    payWs.columns = [
      { key: 'contractor', width: 30 },
      { key: 'datePaid', width: 14 },
      { key: 'netAmount', width: 20 },
    ]
    addHeaderRow(payWs, ['Contractor', 'Date Paid', 'Net Amount (ILS)'])
    for (const row of supporting.payouts) {
      addDataRow(payWs, [row.contractor, row.datePaid, row.netAmountIls])
    }
  })
}