/**
 * RTL Hebrew Excel exports for revenue ledger, invoice report, payment report.
 */
import type {
  RevenueLedgerReport,
  InvoiceReport,
  PaymentReport,
} from '@zync/db/queries'
import {
  writeFinancialWorkbook,
  newRtlWorksheet,
  sanitizeCell,
  applyHebrewFont,
} from './financial-export'

const SOURCE_LABELS: Record<string, string> = {
  manual: 'ידני',
  gateway: 'שער תשלום',
  bank_transfer: 'העברה בנקאית',
  auto_billing: 'חיוב אוטומטי',
}

function styleHeaderRow(ws: import('exceljs').Worksheet, rowIndex: number): void {
  const row = ws.getRow(rowIndex)
  row.eachCell((cell) => {
    applyHebrewFont(cell)
    cell.font = { ...cell.font, bold: true }
    cell.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE8E8E8' },
    }
  })
}

function addDataRows(
  ws: import('exceljs').Worksheet,
  rows: unknown[][],
  startRow = 2,
): void {
  rows.forEach((values, i) => {
    const row = ws.addRow(values.map((v) => sanitizeCell(v)))
    row.eachCell((cell) => applyHebrewFont(cell))
    if (i === rows.length - 1 && typeof values[0] === 'string' && values[0] === 'סה"כ') {
      row.eachCell((cell) => {
        cell.font = { ...cell.font, bold: true }
      })
    }
  })
  void startRow
}

export async function buildRevenueLedgerXlsx(report: RevenueLedgerReport): Promise<Uint8Array> {
  return writeFinancialWorkbook((wb) => {
    const ws = newRtlWorksheet(wb, 'פנקס הכנסות')
    ws.columns = [
      { width: 16 },
      { width: 14 },
      { width: 28 },
      { width: 16 },
      { width: 16 },
      { width: 14 },
      { width: 16 },
      { width: 10 },
    ]
    ws.addRow([
      'מס\' חשבונית',
      'תאריך',
      'לקוח',
      'ח.פ./ע.מ.',
      'ללא מע"מ',
      'מע"מ',
      'כולל מע"מ',
      'שיעור מע"מ',
    ])
    styleHeaderRow(ws, 1)

    const dataRows = report.rows.map((r) => [
      r.invoice_number,
      r.date,
      r.customer_name,
      r.customer_tax_id ?? '',
      parseFloat(r.subtotal),
      parseFloat(r.vat_amount),
      parseFloat(r.total),
      r.vat_rate != null ? parseFloat(r.vat_rate) : '',
    ])
    dataRows.push([
      'סה"כ',
      '',
      '',
      '',
      parseFloat(report.totals.subtotal),
      parseFloat(report.totals.vat_amount),
      parseFloat(report.totals.total),
      '',
    ])
    addDataRows(ws, dataRows)

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

export async function buildInvoiceReportXlsx(report: InvoiceReport): Promise<Uint8Array> {
  return writeFinancialWorkbook((wb) => {
    const ws = newRtlWorksheet(wb, 'דוח חשבוניות')
    ws.columns = [
      { width: 16 },
      { width: 14 },
      { width: 14 },
      { width: 28 },
      { width: 14 },
      { width: 14 },
      { width: 14 },
      { width: 16 },
      { width: 8 },
      { width: 14 },
      { width: 12 },
    ]
    ws.addRow([
      'מס\' חשבונית',
      'תאריך הפקה',
      'תאריך לתשלום',
      'לקוח',
      'סטטוס',
      'ללא מע"מ',
      'מע"מ',
      'סה"כ',
      'מטבע',
      'יתרה לתשלום',
      'ימים בפיגור',
    ])
    styleHeaderRow(ws, 1)

    const dataRows = report.rows.map((r) => [
      r.invoice_number ?? '',
      r.issue_date ?? '',
      r.due_date ?? '',
      r.customer_name,
      r.status,
      parseFloat(r.subtotal),
      parseFloat(r.vat_amount),
      parseFloat(r.total),
      r.currency,
      parseFloat(r.balance_due),
      r.days_overdue,
    ])
    dataRows.push([
      'סה"כ',
      '',
      '',
      '',
      '',
      parseFloat(report.totals.subtotal),
      parseFloat(report.totals.vat_amount),
      parseFloat(report.totals.total),
      '',
      parseFloat(report.totals.balance_due),
      '',
    ])
    addDataRows(ws, dataRows)

    for (const col of ['F', 'G', 'H', 'J']) {
      ws.getColumn(col).numFmt = '#,##0.00'
    }
  })
}

export async function buildPaymentReportXlsx(report: PaymentReport): Promise<Uint8Array> {
  return writeFinancialWorkbook((wb) => {
    const ws = newRtlWorksheet(wb, 'דוח תשלומים')
    ws.columns = [
      { width: 18 },
      { width: 28 },
      { width: 16 },
      { width: 14 },
      { width: 8 },
      { width: 18 },
      { width: 20 },
      { width: 12 },
    ]
    ws.addRow([
      'תאריך תשלום',
      'לקוח',
      'מס\' חשבונית',
      'סכום',
      'מטבע',
      'אמצעי תשלום',
      'אסמכתא',
      'קבלה הונפקה',
    ])
    styleHeaderRow(ws, 1)

    const dataRows = report.rows.map((r) => [
      r.paid_at.slice(0, 10),
      r.customer_name,
      r.invoice_number ?? '',
      parseFloat(r.amount),
      r.currency,
      SOURCE_LABELS[r.source] ?? r.source,
      r.reference ?? '',
      r.receipt_issued ? 'כן' : 'לא',
    ])
    dataRows.push([
      'סה"כ',
      '',
      '',
      parseFloat(report.totals.amount),
      '',
      '',
      '',
      '',
    ])
    addDataRows(ws, dataRows)

    ws.getColumn('D').numFmt = '#,##0.00'
  })
}