import { applyHebrewFont, newRtlWorksheet, sanitizeCell, writeFinancialWorkbook } from './financial-export'

type WithholdingContractorRow = {
  payee_kind?: 'contractor'
  contractor_id: string
  name: string | null
  tax_id: string | null
  gross_paid: string
  withholding_rate: string
  withheld_amount: string
  certificate_number: string | null
  certificate_expiry: string | null
}

type WithholdingPayeeRow = {
  payee_kind: 'contractor' | 'vendor'
  payee_id: string
  name: string | null
  tax_id: string | null
  gross_paid: string
  withholding_rate: string
  withheld_amount: string
  certificate_number: string | null
  certificate_expiry: string | null
}

type WithholdingReportShape = {
  year: number
  total_gross: string
  total_withheld: string
  contractors?: WithholdingContractorRow[]
  payees?: WithholdingPayeeRow[]
}

type PayoutLedgerItem = {
  bill: {
    id: string
    contractorId: string
    periodStart: string
    periodEnd: string
    status: string
    amount: string
    currency: string
    withholdingAmount: string
    netAmount: string | null
    totalHours: string | null
    paidAt: string | null
  }
  contractorName: string | null
  contractorTaxId: string | null
}

type PayoutLedgerShape = {
  items: PayoutLedgerItem[]
  totalDue: string
}

function autosize(columns: unknown[][], ws: import('exceljs').Worksheet) {
  columns.forEach((values, index) => {
    const width = Math.max(
      14,
      ...values.map((value) => String(value ?? '').length + 2),
    )
    ws.getColumn(index + 1).width = width
  })
}

export async function buildWithholdingReportXlsx(report: WithholdingReportShape): Promise<Uint8Array> {
  return writeFinancialWorkbook(async (wb) => {
    const ws = newRtlWorksheet(wb, 'טופס 856')
    const payees = report.payees ?? report.contractors?.map((row) => ({
      payee_kind: 'contractor' as const,
      payee_id: row.contractor_id,
      name: row.name,
      tax_id: row.tax_id,
      gross_paid: row.gross_paid,
      withholding_rate: row.withholding_rate,
      withheld_amount: row.withheld_amount,
      certificate_number: row.certificate_number,
      certificate_expiry: row.certificate_expiry,
    })) ?? []
    const header = ['סוג', 'מספר מזהה', 'שם', 'ח.פ./ת.ז.', 'סה"כ ברוטו', 'שיעור ניכוי', 'סכום מנוכה', 'מספר אישור', 'תוקף אישור']
    const rows = payees.map((row) => [
      row.payee_kind === 'vendor' ? 'ספק' : 'קבלן',
      row.payee_id,
      row.name ?? '',
      row.tax_id ?? '',
      row.gross_paid,
      row.withholding_rate,
      row.withheld_amount,
      row.certificate_number ?? '',
      row.certificate_expiry ?? '',
    ])

    ws.addRow([`דו"ח ניכוי מס במקור ${report.year}`])
    ws.mergeCells('A1:I1')
    applyHebrewFont(ws.getCell('A1'))
    ws.getCell('A1').font = { ...ws.getCell('A1').font, bold: true, size: 14 }

    ws.addRow(header.map((value) => sanitizeCell(value)))
    header.forEach((_, index) => {
      const cell = ws.getRow(2).getCell(index + 1)
      applyHebrewFont(cell)
      cell.font = { ...cell.font, bold: true }
    })

    for (const row of rows) {
      const added = ws.addRow(row.map((value) => sanitizeCell(value)))
      added.eachCell((cell) => applyHebrewFont(cell))
    }

    ws.addRow([])
    const totals = ws.addRow(['', 'סה"כ', '', report.total_gross, '', report.total_withheld, '', ''])
    totals.eachCell((cell) => {
      applyHebrewFont(cell)
      cell.font = { ...cell.font, bold: true }
    })

    autosize(
      header.map((_, index) => [header[index], ...rows.map((row) => row[index] ?? '')]),
      ws,
    )
  })
}

export async function buildPayoutLedgerXlsx(report: PayoutLedgerShape): Promise<Uint8Array> {
  return writeFinancialWorkbook(async (wb) => {
    const ws = newRtlWorksheet(wb, 'יומן תשלומי קבלנים')
    const header = ['קבלן', 'ח.פ./ת.ז.', 'מתאריך', 'עד תאריך', 'סטטוס', 'שעות', 'ברוטו', 'ניכוי', 'נטו', 'שולם בתאריך']
    const rows = report.items.map((item) => [
      item.contractorName ?? item.bill.contractorId,
      item.contractorTaxId ?? '',
      item.bill.periodStart,
      item.bill.periodEnd,
      item.bill.status,
      item.bill.totalHours ?? '',
      item.bill.amount,
      item.bill.withholdingAmount,
      item.bill.netAmount ?? item.bill.amount,
      item.bill.paidAt ? item.bill.paidAt.slice(0, 10) : '',
    ])

    ws.addRow(['יומן תשלומי קבלנים'])
    ws.mergeCells('A1:J1')
    applyHebrewFont(ws.getCell('A1'))
    ws.getCell('A1').font = { ...ws.getCell('A1').font, bold: true, size: 14 }

    ws.addRow(header.map((value) => sanitizeCell(value)))
    header.forEach((_, index) => {
      const cell = ws.getRow(2).getCell(index + 1)
      applyHebrewFont(cell)
      cell.font = { ...cell.font, bold: true }
    })

    for (const row of rows) {
      const added = ws.addRow(row.map((value) => sanitizeCell(value)))
      added.eachCell((cell) => applyHebrewFont(cell))
    }

    ws.addRow([])
    const totals = ws.addRow(['', '', '', '', 'סה"כ פתוח', '', '', '', report.totalDue, ''])
    totals.eachCell((cell) => {
      applyHebrewFont(cell)
      cell.font = { ...cell.font, bold: true }
    })

    autosize(
      header.map((_, index) => [header[index], ...rows.map((row) => row[index] ?? '')]),
      ws,
    )
  })
}

export async function buildForm857Xlsx(
  report: WithholdingReportShape,
  payeeId: string,
): Promise<Uint8Array> {
  const payees = report.payees ?? report.contractors?.map((item) => ({
    payee_kind: 'contractor' as const,
    payee_id: item.contractor_id,
    name: item.name,
    tax_id: item.tax_id,
    gross_paid: item.gross_paid,
    withholding_rate: item.withholding_rate,
    withheld_amount: item.withheld_amount,
    certificate_number: item.certificate_number,
    certificate_expiry: item.certificate_expiry,
  })) ?? []
  const row = payees.find((item) => item.payee_id === payeeId)
  if (!row) {
    throw new Error('Payee not found in withholding report')
  }

  return writeFinancialWorkbook(async (wb) => {
    const ws = newRtlWorksheet(wb, 'טופס 857')
    const lines: Array<[string, string]> = [
      ['שנה', String(report.year)],
      ['סוג', row.payee_kind === 'vendor' ? 'ספק' : 'קבלן'],
      ['שם', row.name ?? row.payee_id],
      ['ח.פ./ת.ז.', row.tax_id ?? ''],
      ['ברוטו ששולם', row.gross_paid],
      ['שיעור ניכוי', row.withholding_rate],
      ['סכום שנוכה', row.withheld_amount],
      ['מספר אישור', row.certificate_number ?? ''],
      ['תוקף אישור', row.certificate_expiry ?? ''],
    ]

    ws.addRow(['אישור שנתי על ניכוי מס במקור (טופס 857)'])
    ws.mergeCells('A1:B1')
    applyHebrewFont(ws.getCell('A1'))
    ws.getCell('A1').font = { ...ws.getCell('A1').font, bold: true, size: 14 }

    for (const [label, value] of lines) {
      const added = ws.addRow([sanitizeCell(label), sanitizeCell(value)])
      added.eachCell((cell) => applyHebrewFont(cell))
    }

    autosize(
      [
        lines.map(([label]) => label),
        lines.map(([, value]) => value),
      ],
      ws,
    )
  })
}