/**
 * Bad-debt report routes — bad-debt-writeoff (wave-12).
 * Mounted under /api/reports in the main reports router.
 *
 * GET /api/reports/bad-debt?year=YYYY       → BadDebtReportData (JSON)
 * GET /api/reports/bad-debt/xlsx?year=YYYY  → RTL Hebrew Excel download
 */
import { Hono } from 'hono'
import type { AppEnv } from '../../types'
import { authMiddleware } from '../../middleware/auth'
import { requirePermission } from '../../middleware/guards'
import { createDb, getBadDebtReport } from '@zync/db/queries'

function safe(v: unknown): string {
  const s = String(v ?? '')
  return /^[=+\-@\t\r]/.test(s) ? `'${s}` : s
}

export const badDebtReportRoute = new Hono<AppEnv>()

badDebtReportRoute.use('*', authMiddleware)

// GET /api/reports/bad-debt/xlsx (must be before /bad-debt to avoid :year capture)
badDebtReportRoute.get(
  '/xlsx',
  requirePermission('reports:export'),
  async (c) => {
    const session = c.get('session')
    if (!session || session.type !== 'user' || !session.tid) {
      return c.json({ error: 'Unauthorized' }, 401)
    }

    const yearStr = c.req.query('year')
    const year = yearStr ? parseInt(yearStr, 10) : new Date().getFullYear()

    const db = createDb(c.env)
    const report = await getBadDebtReport(db, session.tid, year)

    // Build RTL Hebrew workbook
    const ExcelJS = (await import('exceljs')).default
    const wb = new ExcelJS.Workbook()
    const ws = wb.addWorksheet('חובות אבודים', {
      views: [{ rightToLeft: true }],
    })

    ws.columns = [
      { header: 'מס\' חשבונית',  key: 'invoice_number',       width: 18 },
      { header: 'לקוח',           key: 'customer_name',        width: 28 },
      { header: 'סכום (₪)',       key: 'amount',               width: 16 },
      { header: 'מע"מ (₪)',       key: 'vat_amount',           width: 16 },
      { header: 'תאריך מחיקה',   key: 'bad_debt_at',          width: 16 },
      { header: 'סטטוס החזר מע"מ', key: 'vat_reclaim_status', width: 22 },
      { header: 'מכתב רשום נשלח', key: 'registered_letter',   width: 20 },
    ]

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

    for (const inv of report.invoices) {
      ws.addRow({
        invoice_number: safe(inv.invoice_number),
        customer_name: safe(inv.customer_name),
        amount: inv.amount,
        vat_amount: inv.vat_amount,
        bad_debt_at: inv.bad_debt_at ? inv.bad_debt_at.slice(0, 10) : '',
        vat_reclaim_status: safe(inv.vat_reclaim_status ?? 'ללא'),
        registered_letter: inv.registered_letter_sent ? 'כן' : 'לא',
      })
    }

    // Summary row
    ws.addRow([])
    const summaryRow = ws.addRow({
      invoice_number: 'סה"כ',
      amount: report.total_written_off,
      vat_amount: report.total_vat_reclaimed,
    })
    summaryRow.font = { bold: true, name: 'Arial' }

    for (const col of ['C', 'D']) {
      ws.getColumn(col).numFmt = '#,##0.00'
    }

    const buffer = await wb.xlsx.writeBuffer()
    const filename = `bad-debt-${year}.xlsx`

    return new Response(buffer, {
      status: 200,
      headers: {
        'Content-Type': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        'Content-Disposition': `attachment; filename="${filename}"`,
      },
    })
  },
)

// GET /api/reports/bad-debt
badDebtReportRoute.get(
  '/',
  requirePermission('reports:read'),
  async (c) => {
    const session = c.get('session')
    if (!session || session.type !== 'user' || !session.tid) {
      return c.json({ error: 'Unauthorized' }, 401)
    }

    const yearStr = c.req.query('year')
    const year = yearStr ? parseInt(yearStr, 10) : new Date().getFullYear()

    const db = createDb(c.env)
    const report = await getBadDebtReport(db, session.tid, year)

    return c.json(report, 200)
  },
)