/**
 * bituach-leumi-xlsx.ts — RTL Hebrew Excel writer for Bituach Leumi report.
 *
 * Builds an xlsx workbook with 5 sheets:
 *   1. Summary       — main report figures
 *   2. Invoices      — qualifying invoices (net of VAT)
 *   3. Expenses      — deductible expenses (net of VAT)
 *   4. Payouts       — contractor payouts (paid)
 *   5. NII Advances  — recorded NII advance payments
 *
 * All sheets are RTL with Hebrew-capable font.
 * Formula injection guarded via sanitizeCell.
 *
 * Spec: 2026-06-01-bituach-leumi (spec 175, wave-15)
 */
import type ExcelJS from 'exceljs'
import {
  writeFinancialWorkbook,
  newRtlWorksheet,
  applyHebrewFont,
  sanitizeCell,
} from './financial-export'
import type { BituachLeumiReport } from '@zync/types'
import type { Db } from '@zync/db/queries'
import { invoices, expenses, payoutBills, niiAdvancePayments } from '@zync/db/schema'
import { and, eq, sql } from '@zync/db'

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 | null)[]): void {
  const row = ws.addRow(
    values.map((v) => sanitizeCell(v === null ? '' : typeof v === 'number' ? String(v) : v)),
  )
  row.eachCell((cell) => applyHebrewFont(cell))
}

/**
 * Build the bituach-leumi xlsx workbook and return raw bytes.
 * Requires a DB instance to query detail rows for the export sheets.
 */
export async function buildBituachLeumiXlsx(
  report: BituachLeumiReport,
  db: Db,
  tenantId: string,
  userId: string,
): Promise<Uint8Array> {
  const year        = report.year
  const periodFrom  = `${year}-01-01`
  const periodTo    = `${year}-12-31`

  // ── Fetch detail rows in parallel ─────────────────────────────────────────
  const [invoiceRows, expenseRows, payoutRows, advanceRows] = await Promise.all([
    db
      .select({
        invoiceNumber: invoices.invoiceNumber,
        taxIssueDate: invoices.taxIssueDate,
        total: invoices.total,
        vatAmount: invoices.vatAmount,
      })
      .from(invoices)
      .where(
        and(
          eq(invoices.tenantId, tenantId),
          sql`${invoices.taxIssueDate} BETWEEN ${periodFrom} AND ${periodTo}`,
          sql`${invoices.status} NOT IN ('DRAFT', 'SENT', 'VOID')`,
          sql`${invoices.source} != 'credit_note'`,
        ),
      )
      .orderBy(invoices.taxIssueDate),

    db
      .select({
        expenseDate: expenses.expenseDate,
        vendorName: expenses.vendorName,
        expenseCategory: expenses.expenseCategory,
        amount: sql<string>`${expenses.amount}::numeric * ${expenses.businessPercent}::numeric / 100`,
        vatAmount: sql<string>`COALESCE(${expenses.vatAmount}::numeric, 0) * ${expenses.businessPercent}::numeric / 100`,
      })
      .from(expenses)
      .where(
        and(
          eq(expenses.tenantId, tenantId),
          sql`${expenses.expenseDate} BETWEEN ${periodFrom} AND ${periodTo}`,
          eq(expenses.status, 'COMPLETED'),
          eq(expenses.vatDeductible, true),
        ),
      )
      .orderBy(expenses.expenseDate),

    db
      .select({
        periodStart: payoutBills.periodStart,
        periodEnd: payoutBills.periodEnd,
        netAmount: payoutBills.netAmount,
        paidAt: payoutBills.paidAt,
      })
      .from(payoutBills)
      .where(
        and(
          eq(payoutBills.tenantId, tenantId),
          eq(payoutBills.status, 'PAID'),
          sql`date_part('year', ${payoutBills.paidAt}) = ${year}`,
        ),
      )
      .orderBy(payoutBills.paidAt),

    db
      .select()
      .from(niiAdvancePayments)
      .where(
        and(
          eq(niiAdvancePayments.tenantId, tenantId),
          eq(niiAdvancePayments.userId, userId),
          eq(niiAdvancePayments.year, year),
        ),
      )
      .orderBy(niiAdvancePayments.month),
  ])

  return writeFinancialWorkbook((wb) => {
    // ── Sheet 1: Summary ──
    const summaryWs = newRtlWorksheet(wb, `Summary ${year}`)
    summaryWs.columns = [
      { header: 'Item', key: 'item', width: 40 },
      { header: 'Amount (ILS)', key: 'amount', width: 20 },
    ]
    addHeaderRow(summaryWs, ['Item', 'Amount (ILS)'])
    addDataRow(summaryWs, ['Gross revenue (excl. VAT)', report.gross_revenue_net_vat])
    addDataRow(summaryWs, ['Less: Deductible expenses', `-${report.deductible_expenses}`])
    addDataRow(summaryWs, ['Less: Contractor payouts', `-${report.contractor_payouts}`])
    addDataRow(summaryWs, ['Less: Depreciation', `-${report.depreciation_deduction}`])
    addDataRow(summaryWs, ['Net income for NII', report.net_income])
    addDataRow(summaryWs, ['Monthly average', report.monthly_average])
    summaryWs.addRow([])
    addHeaderRow(summaryWs, ['NII Contributions (Estimate)', ''])
    addDataRow(summaryWs, ['National insurance', String(report.nii_contributions.national_insurance)])
    addDataRow(summaryWs, ['Health insurance', String(report.nii_contributions.health_insurance)])
    addDataRow(summaryWs, ['Total NII contributions', String(report.nii_contributions.total)])
    summaryWs.addRow([])
    addDataRow(summaryWs, ['NII advances paid (total)', String(report.advances_paid.total)])

    // ── Sheet 2: Invoices ──
    const invWs = newRtlWorksheet(wb, 'Invoices')
    invWs.columns = [
      { header: 'Invoice Number', key: 'num', width: 20 },
      { header: 'Date', key: 'date', width: 15 },
      { header: 'Total (ILS)', key: 'total', width: 16 },
      { header: 'VAT (ILS)', key: 'vat', width: 16 },
      { header: 'Net of VAT (ILS)', key: 'net', width: 18 },
    ]
    addHeaderRow(invWs, ['Invoice Number', 'Date', 'Total (ILS)', 'VAT (ILS)', 'Net of VAT (ILS)'])
    for (const r of invoiceRows) {
      const total = parseFloat(r.total ?? '0')
      const vat   = parseFloat(r.vatAmount ?? '0')
      addDataRow(invWs, [
        r.invoiceNumber ?? '',
        r.taxIssueDate ?? '',
        total.toFixed(2),
        vat.toFixed(2),
        (total - vat).toFixed(2),
      ])
    }

    // ── Sheet 3: Expenses ──
    const expWs = newRtlWorksheet(wb, 'Expenses')
    expWs.columns = [
      { header: 'Date', key: 'date', width: 15 },
      { header: 'Vendor', key: 'vendor', width: 30 },
      { header: 'Category', key: 'category', width: 20 },
      { header: 'Amount (ILS)', key: 'amount', width: 16 },
      { header: 'VAT (ILS)', key: 'vat', width: 16 },
      { header: 'Net of VAT (ILS)', key: 'net', width: 18 },
    ]
    addHeaderRow(expWs, ['Date', 'Vendor', 'Category', 'Amount (ILS)', 'VAT (ILS)', 'Net of VAT (ILS)'])
    for (const r of expenseRows) {
      const amount = parseFloat(r.amount ?? '0')
      const vat    = parseFloat(r.vatAmount ?? '0')
      addDataRow(expWs, [
        r.expenseDate ?? '',
        r.vendorName ?? '',
        r.expenseCategory ?? '',
        amount.toFixed(2),
        vat.toFixed(2),
        (amount - vat).toFixed(2),
      ])
    }

    // ── Sheet 4: Payouts ──
    const payWs = newRtlWorksheet(wb, 'Payouts')
    payWs.columns = [
      { header: 'Period Start', key: 'start', width: 15 },
      { header: 'Period End', key: 'end', width: 15 },
      { header: 'Paid At', key: 'paidAt', width: 20 },
      { header: 'Net Amount (ILS)', key: 'net', width: 18 },
    ]
    addHeaderRow(payWs, ['Period Start', 'Period End', 'Paid At', 'Net Amount (ILS)'])
    for (const r of payoutRows) {
      addDataRow(payWs, [
        r.periodStart ?? '',
        r.periodEnd ?? '',
        r.paidAt ? r.paidAt.toISOString().slice(0, 10) : '',
        parseFloat(r.netAmount ?? '0').toFixed(2),
      ])
    }

    // ── Sheet 5: NII Advances ──
    const advWs = newRtlWorksheet(wb, 'NII Advances')
    advWs.columns = [
      { header: 'Month', key: 'month', width: 10 },
      { header: 'Amount (ILS)', key: 'amount', width: 16 },
      { header: 'Paid On', key: 'paidAt', width: 15 },
      { header: 'Notes', key: 'notes', width: 30 },
    ]
    addHeaderRow(advWs, ['Month', 'Amount (ILS)', 'Paid On', 'Notes'])
    for (const r of advanceRows) {
      addDataRow(advWs, [
        r.month,
        parseFloat(r.amount).toFixed(2),
        r.paidAt ?? '',
        r.notes ?? '',
      ])
    }
  })
}