/**
 * Form 6111 generator — accountant-export (wave-15).
 *
 * Generates a structured Excel workbook (RTL Hebrew) for the ITA Form 6111
 * (Annual Income Tax Return, P&L section only in v1).
 *
 * Data-driven: ex-VAT P&L amounts (mirroring deriveMovements semantics) are
 * resolved to coa_accounts via coa_mappings, then grouped by each account's
 * form6111_code (ITA field code).
 *
 * 6111 P&L amounts are ex-VAT and reconcile to the movement file.
 *
 * Reuses:
 *  - listCoaAccounts, listCoaMappings from @zync/db/queries
 *  - newRtlWorksheet / sanitizeCell from financial-export.ts
 *
 * Output: uploaded to R2 at exports/accountant/{tenantId}/{jobId}_form6111.xlsx
 */
import { eq, and, gte, lte, inArray } from '@zync/db'
import type ExcelJS from 'exceljs'
import { invoices, expenses, payoutBills } from '@zync/db'
import {
  createDb,
  accountantExportJobs,
  listCoaAccounts,
  listCoaMappings,
} from '@zync/db/queries'
import type { Db } from '@zync/db/queries'
import {
  applyHebrewFont,
  newRtlWorksheet,
  sanitizeCell,
  writeFinancialWorkbook,
} from '../lib/financial-export'
import type { Env } from '@zync/types'

/** ITA Form 6111 P&L field Hebrew labels (authoritative codes per gov.il itc6111). */
const FORM6111_LABELS: Record<string, string> = {
  '1300': 'הכנסות ממכירות ושירותים',
  '2000': 'עלות המכירות',
  '3000': 'הוצאות ייצור',
  '3500': 'הוצאות מכירה ושיווק',
  '5000': 'הוצאות הנהלה וכלליות',
  '6666': 'רווח/הפסד נטו',
}

const CATEGORY_LABELS: Record<string, string> = {
  office: 'הוצאות משרד',
  professional: 'שירותים מקצועיים',
  marketing: 'שיווק ופרסום',
  vehicle: 'רכב ונסיעות',
  equipment: 'ציוד וחומרה',
  finance: 'עלויות פיננסיות',
  welfare: 'רווחה לעובדים',
  exceptional: 'הוצאות חריגות',
  travel: 'נסיעות לחוץ לארץ',
  Other: 'הוצאות אחרות',
}

const PL_ACCOUNT_TYPES = new Set(['revenue', 'expense'])

type BreakdownEntry = { label: string; accountCode: string; amount: number }

type GroupedRow = {
  code: string
  amount: number
  breakdown: BreakdownEntry[]
}

type SourceAmount = {
  sourceKind: string
  label: string
  amount: number
}

type ExVatPl = {
  revenue: {
    gross: number
    credit_notes: number
    bad_debts: number
    net: number
  }
  expenses: {
    by_category: Record<string, number>
    inventory_stock: number
    total: number
  }
  contractor_payouts: number
  net_profit: number
}

export interface Form6111ExpenseInput {
  expenseCategory?: string | null
  amount?: string | null
  vatAmount?: string | null
  businessPercent?: number | null
  deductionPct?: number | null
  vatDeductible?: boolean | null
  sourceMetadata?: Record<string, unknown> | null
}

export interface InventoryCountRow {
  stockItemId: string
  productName: string
  locationName: string
  locationCode: string | null
  qtyOnHand: number
  avgUnitCost: number
}

type Form6111Composition = {
  groupedRows: GroupedRow[]
  mappedPlCount: number
  plAccountCount: number
  unmappedPlAccounts: Array<{ code: string; name: string }>
  unmappedWithAmounts: string[]
}

function fmtNum(n: number): string {
  return n.toFixed(2)
}

function toIls(val: string | null | undefined): number {
  return Math.abs(parseFloat(val ?? '0') || 0)
}

function round2(n: number): number {
  return Math.round(n * 100) / 100
}

function toMetadataObject(value: unknown): Record<string, unknown> | null {
  return value && typeof value === 'object' ? (value as Record<string, unknown>) : null
}

function toFiniteNumber(value: unknown): number | null {
  if (typeof value === 'number') {
    return Number.isFinite(value) ? value : null
  }
  if (typeof value === 'string' && value.trim() !== '') {
    const parsed = Number(value)
    return Number.isFinite(parsed) ? parsed : null
  }
  return null
}

function stockLineRecords(sourceMetadata: Record<string, unknown> | null | undefined): Array<Record<string, unknown>> {
  const metadata = toMetadataObject(sourceMetadata)
  if (!metadata) return []

  const nestedInventory = toMetadataObject(metadata.inventory)
  const nestedReceive = toMetadataObject(metadata.receiveIntoStock)
  const candidates = [
    metadata.stockLines,
    metadata.stock_lines,
    nestedInventory?.stockLines,
    nestedReceive?.lines,
  ]

  for (const candidate of candidates) {
    if (Array.isArray(candidate)) {
      return candidate.filter((row): row is Record<string, unknown> => !!row && typeof row === 'object')
    }
  }

  return []
}

function stockLineNetAmount(line: Record<string, unknown>): number | null {
  const directNet = toFiniteNumber(line.netAmount ?? line.totalNetAmount)
  if (directNet !== null) return directNet

  const qty = toFiniteNumber(line.qty ?? line.quantity ?? line.receivedQty)
  const directUnitNet = toFiniteNumber(line.unitNetCost ?? line.netUnitCost ?? line.unitCostNet)
  if (qty !== null && directUnitNet !== null) {
    return qty * directUnitNet
  }

  const grossAmount = toFiniteNumber(line.amount ?? line.totalAmount ?? line.grossAmount)
  if (grossAmount !== null) {
    const vatAmount = toFiniteNumber(line.vatAmount ?? line.totalVatAmount ?? line.taxAmount) ?? 0
    return grossAmount - vatAmount
  }

  const grossUnit = toFiniteNumber(line.grossUnitCost ?? line.unitGrossCost)
  if (qty !== null && grossUnit !== null) {
    const unitVat = toFiniteNumber(line.unitVatAmount ?? line.vatPerUnit) ?? 0
    return qty * (grossUnit - unitVat)
  }

  return null
}

function stockExpenseNetAmount(
  sourceMetadata: Record<string, unknown> | null | undefined,
  splitFactor: number,
  fallbackExpenseDebit: number,
): number {
  const lines = stockLineRecords(sourceMetadata)
  if (lines.length === 0) return 0

  let resolved = 0
  let resolvedCount = 0
  for (const line of lines) {
    const lineNet = stockLineNetAmount(line)
    if (lineNet === null) continue
    resolved += Math.abs(lineNet) * splitFactor
    resolvedCount += 1
  }

  if (resolvedCount === 0 && lines.length === 1) {
    return round2(fallbackExpenseDebit)
  }

  return round2(Math.min(resolved, fallbackExpenseDebit))
}

function formatBreakdown(entries: BreakdownEntry[]): string {
  return entries
    .map((b) => `${b.label} (${b.accountCode}): ${fmtNum(b.amount)}`)
    .join('; ')
}

export function partitionExpenseSourcesForForm6111(
  expensesInput: Form6111ExpenseInput[],
): ExVatPl['expenses'] {
  const byCategory: Record<string, number> = {}
  let totalExpenses = 0
  let inventoryStock = 0

  for (const exp of expensesInput) {
    const category = exp.expenseCategory ?? 'office'
    const splitFactor = (exp.businessPercent ?? 100) / 100
    const amount = round2(toIls(exp.amount) * splitFactor)
    const vatAmount = round2(toIls(exp.vatAmount) * splitFactor)
    const deductionPct = exp.deductionPct ?? 100
    const deductibleVat = exp.vatDeductible
      ? round2(vatAmount * deductionPct / 100)
      : 0
    const expenseDebit = round2(amount - deductibleVat)
    const stockNet = stockExpenseNetAmount(exp.sourceMetadata, splitFactor, expenseDebit)
    const nonStockExpense = round2(Math.max(expenseDebit - stockNet, 0))

    if (nonStockExpense > 0) {
      byCategory[category] = round2((byCategory[category] ?? 0) + nonStockExpense)
    }
    inventoryStock = round2(inventoryStock + stockNet)
    totalExpenses = round2(totalExpenses + expenseDebit)
  }

  return {
    by_category: byCategory,
    inventory_stock: inventoryStock,
    total: totalExpenses,
  }
}

/**
 * Ex-VAT P&L aggregation mirroring deriveMovements filters and amount math.
 * Revenue uses invoice subtotal; expenses net of deductible VAT; payouts gross.
 */
async function aggregateExVatPl(
  db: Db,
  tenantId: string,
  from: string,
  to: string,
): Promise<ExVatPl> {
  const [invoiceRows, expenseRows, payoutRows, badDebtRows] = await Promise.all([
    db
      .select()
      .from(invoices)
      .where(
        and(
          eq(invoices.tenantId, tenantId),
          inArray(invoices.status, ['TAX_ISSUED', 'PAID', 'PARTIALLY_PAID']),
          gte(invoices.taxIssueDate, from),
          lte(invoices.taxIssueDate, to),
        ),
      ),
    db
      .select()
      .from(expenses)
      .where(
        and(
          eq(expenses.tenantId, tenantId),
          eq(expenses.status, 'COMPLETED'),
          gte(expenses.expenseDate, from),
          lte(expenses.expenseDate, to),
        ),
      ),
    db
      .select()
      .from(payoutBills)
      .where(
        and(
          eq(payoutBills.tenantId, tenantId),
          eq(payoutBills.status, 'PAID'),
          gte(payoutBills.paidAt, new Date(from + 'T00:00:00Z')),
          lte(payoutBills.paidAt, new Date(to + 'T23:59:59Z')),
        ),
      ),
    db
      .select()
      .from(invoices)
      .where(
        and(
          eq(invoices.tenantId, tenantId),
          gte(invoices.badDebtAt, new Date(from + 'T00:00:00Z')),
          lte(invoices.badDebtAt, new Date(to + 'T23:59:59Z')),
        ),
      ),
  ])

  let grossRevenue = 0
  let creditNotes = 0
  for (const inv of invoiceRows) {
    const subtotal = toIls(inv.subtotal)
    if (inv.source === 'credit_note') {
      creditNotes += subtotal
    } else {
      grossRevenue += subtotal
    }
  }

  let badDebts = 0
  for (const inv of badDebtRows) {
    badDebts += toIls(inv.subtotal)
  }

  const netRevenue = grossRevenue - creditNotes - badDebts

  const expenseTotals = partitionExpenseSourcesForForm6111(expenseRows)

  let contractorPayouts = 0
  for (const bill of payoutRows) {
    contractorPayouts += toIls(bill.amount)
  }

  const netProfit = netRevenue - expenseTotals.total - contractorPayouts

  return {
    revenue: {
      gross: grossRevenue,
      credit_notes: creditNotes,
      bad_debts: badDebts,
      net: netRevenue,
    },
    expenses: expenseTotals,
    contractor_payouts: contractorPayouts,
    net_profit: netProfit,
  }
}

export function composeForm6111Rows(
  report: ExVatPl,
  accounts: Array<{ code: string; name: string; type: string; form6111Code?: string | null }>,
  mappings: Array<{ sourceKind: string; accountCode: string }>,
): Form6111Composition {
  const accountByCode = new Map(accounts.map((a) => [a.code, a]))
  const mappingBySource = new Map(mappings.map((m) => [m.sourceKind, m.accountCode]))
  const sources: SourceAmount[] = []

  if (report.revenue.gross !== 0) {
    sources.push({ sourceKind: 'revenue', label: 'הכנסות (לפני מע"מ)', amount: report.revenue.gross })
  }
  if (report.revenue.credit_notes > 0) {
    sources.push({ sourceKind: 'revenue', label: 'זיכויים ללקוחות', amount: -report.revenue.credit_notes })
  }
  if (report.revenue.bad_debts > 0) {
    sources.push({ sourceKind: 'revenue', label: 'חובות אבודים', amount: -report.revenue.bad_debts })
  }
  if (report.expenses.inventory_stock > 0) {
    sources.push({ sourceKind: 'inventory_stock', label: 'מלאי שנקלט מהוצאות', amount: report.expenses.inventory_stock })
  }

  for (const [cat, amt] of Object.entries(report.expenses.by_category)) {
    if (amt === 0) continue
    sources.push({ sourceKind: cat, label: CATEGORY_LABELS[cat] ?? cat, amount: amt })
  }

  if (report.contractor_payouts > 0) {
    sources.push({ sourceKind: 'professional', label: 'תשלומים לקבלנים', amount: report.contractor_payouts })
  }

  const grouped = new Map<string, GroupedRow>()
  const unmappedWithAmounts: string[] = []

  for (const src of sources) {
    const accountCode = mappingBySource.get(src.sourceKind)
    if (!accountCode) {
      unmappedWithAmounts.push(`${src.label} (${src.sourceKind}): ${fmtNum(src.amount)}`)
      continue
    }

    const account = accountByCode.get(accountCode)
    if (!account) {
      unmappedWithAmounts.push(`${src.label} (${src.sourceKind}): ${fmtNum(src.amount)}`)
      continue
    }

    const formCode = account.form6111Code?.trim()
    if (!formCode) {
      unmappedWithAmounts.push(`${account.code} — ${account.name}: ${fmtNum(src.amount)}`)
      continue
    }

    const row = grouped.get(formCode) ?? { code: formCode, amount: 0, breakdown: [] }
    row.amount = round2(row.amount + src.amount)
    row.breakdown.push({ label: src.label, accountCode: account.code, amount: src.amount })
    grouped.set(formCode, row)
  }

  const plAccounts = accounts.filter((a) => PL_ACCOUNT_TYPES.has(a.type))
  const groupedRows = [...grouped.keys()].sort().map((code) => grouped.get(code)!)
  groupedRows.push({
    code: '6666',
    amount: round2(report.net_profit),
    breakdown: [{ label: 'רווח/הפסד נטו', accountCode: '6666', amount: round2(report.net_profit) }],
  })

  return {
    groupedRows,
    mappedPlCount: plAccounts.filter((a) => (a.form6111Code ?? '').trim().length > 0).length,
    plAccountCount: plAccounts.length,
    unmappedPlAccounts: plAccounts
      .filter((a) => !(a.form6111Code ?? '').trim())
      .map((a) => ({ code: a.code, name: a.name })),
    unmappedWithAmounts,
  }
}

export async function buildInventoryCountWorkbook(
  rows: InventoryCountRow[],
  asOf: string,
): Promise<Uint8Array> {
  return writeFinancialWorkbook((wb) => {
    const ws = newRtlWorksheet(wb, 'מפקד מלאי')
    ws.columns = [
      { width: 8 },
      { width: 14 },
      { width: 18 },
      { width: 28 },
      { width: 42 },
      { width: 14 },
      { width: 14 },
      { width: 14 },
      { width: 14 },
      { width: 14 },
      { width: 24 },
    ]

    const title = ws.addRow([sanitizeCell(`מפקד מלאי ליום ${asOf}`)])
    applyHebrewFont(title.getCell(1))
    title.getCell(1).font = { ...(title.getCell(1).font ?? {}), bold: true, size: 14 }
    ws.mergeCells('A1:K1')
    ws.addRow([])

    const header = ws.addRow([
      'מס׳',
      'קוד מיקום',
      'שם מיקום',
      'מוצר',
      'מזהה מלאי',
      'כמות בספרים',
      'עלות יחידה',
      'שווי בספרים',
      'כמות שנספרה',
      'פער כמות',
      'הערות',
    ].map((value) => sanitizeCell(value)))
    header.eachCell((cell) => {
      applyHebrewFont(cell)
      cell.font = { ...(cell.font ?? {}), bold: true }
    })

    rows.forEach((row, index) => {
      const added = ws.addRow([
        index + 1,
        sanitizeCell(row.locationCode ?? ''),
        sanitizeCell(row.locationName),
        sanitizeCell(row.productName),
        sanitizeCell(row.stockItemId),
        row.qtyOnHand,
        row.avgUnitCost,
        round2(row.qtyOnHand * row.avgUnitCost),
        null,
        null,
        '',
      ])
      added.eachCell((cell) => applyHebrewFont(cell))
    })
  })
}

/**
 * Generate Form 6111 Excel workbook and upload to R2.
 * Updates accountant_export_jobs row status pending → running → done|error.
 */
export async function generateForm6111(
  env: Env,
  tenantId: string,
  periodFrom: string,
  periodTo: string,
  jobId: string,
): Promise<void> {
  const db = createDb(env)

  await db
    .update(accountantExportJobs)
    .set({ status: 'running' })
    .where(and(eq(accountantExportJobs.id, jobId), eq(accountantExportJobs.tenantId, tenantId)))

  try {
    const [report, accounts, mappings] = await Promise.all([
      aggregateExVatPl(db, tenantId, periodFrom, periodTo),
      listCoaAccounts(db, tenantId),
      listCoaMappings(db, tenantId),
    ])
    const year = periodFrom.slice(0, 4)

    const composed = composeForm6111Rows(report, accounts, mappings)

    const ExcelJS = (await import('exceljs')).default
    const wb = new ExcelJS.Workbook()
    const ws = newRtlWorksheet(wb, `טופס 6111 — ${year}`)

    ws.columns = [
      { key: 'field', width: 40 },
      { key: 'code', width: 12 },
      { key: 'amount', width: 20 },
      { key: 'breakdown', width: 60 },
    ]

    const headerRow = ws.addRow({
      field: sanitizeCell(`טופס 6111 — פירוט הכנסות והוצאות — שנת ${year}`),
      code: 'שורה',
      amount: 'סכום (₪)',
      breakdown: 'פירוט',
    })
    headerRow.font = { bold: true, name: 'Arial', size: 12 }
    headerRow.fill = {
      type: 'pattern',
      pattern: 'solid',
      fgColor: { argb: 'FFE8E8E8' },
    } as ExcelJS.Fill

    ws.addRow({})

    for (const row of composed.groupedRows.filter((entry) => entry.code !== '6666')) {
      ws.addRow({
        field: sanitizeCell(FORM6111_LABELS[row.code] ?? row.code),
        code: row.code,
        amount: fmtNum(row.amount),
        breakdown: sanitizeCell(formatBreakdown(row.breakdown)),
      })
    }

    const netProfitRow = composed.groupedRows.find((entry) => entry.code === '6666')
    ws.addRow({
      field: sanitizeCell(FORM6111_LABELS['6666']),
      code: '6666',
      amount: fmtNum(netProfitRow?.amount ?? report.net_profit),
      breakdown: sanitizeCell('רווח/הפסד נטו'),
    })

    ws.addRow({})

    ws.addRow({
      field: sanitizeCell('מיפוי קודי 6111 (חשבונות P&L)'),
      code: '',
      amount: `${composed.mappedPlCount}/${composed.plAccountCount}`,
      breakdown: sanitizeCell(
        composed.mappedPlCount < composed.plAccountCount
          ? `חשבונות ללא קוד: ${composed.unmappedPlAccounts.map((a) => `${a.code} (${a.name})`).join('; ')}`
          : 'כל חשבונות הרווח וההוצאה ממופים',
      ),
    })

    if (composed.unmappedWithAmounts.length > 0) {
      ws.addRow({
        field: sanitizeCell('סכומים שלא מופו לשדה 6111'),
        code: '',
        amount: '',
        breakdown: sanitizeCell(composed.unmappedWithAmounts.join('; ')),
      })
    }

    ws.addRow({})
    ws.addRow({
      field: sanitizeCell('התאמות לצורכי מס (להשלמת רואה החשבון)'),
      code: '',
      amount: '',
      breakdown: '',
    })
    ws.addRow({
      field: sanitizeCell(''),
      code: '',
      amount: '',
      breakdown: sanitizeCell(''),
    })

    const buffer = await wb.xlsx.writeBuffer()
    const r2Key = `exports/accountant/${tenantId}/${jobId}_form6111.xlsx`
    await (env.STORAGE as R2Bucket).put(r2Key, buffer, {
      httpMetadata: {
        contentType: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      },
    })

    const downloadExpiresAt = new Date(Date.now() + 24 * 60 * 60 * 1000)
    await db
      .update(accountantExportJobs)
      .set({
        status: 'done',
        r2Key,
        downloadExpiresAt,
      })
      .where(and(eq(accountantExportJobs.id, jobId), eq(accountantExportJobs.tenantId, tenantId)))
  } catch (err) {
    const errorMessage = err instanceof Error ? err.message : String(err)
    await db
      .update(accountantExportJobs)
      .set({ status: 'error' })
      .where(and(eq(accountantExportJobs.id, jobId), eq(accountantExportJobs.tenantId, tenantId)))
    throw new Error(`generateForm6111 failed: ${errorMessage}`)
  }
}