/**
 * tax-reports.ts — VAT aggregation, annual income summary, and advance tax
 * estimate queries for Israeli compliance reports.
 *
 * Spec: 2026-06-01-israeli-tax-reports (wave-14)
 *
 * All helpers are tenant-filtered. No raw tables are imported outside this
 * module (enforced by eslint no-raw-drizzle-from-routes).
 */
import { and, eq, inArray, isNull, notInArray, sql, desc } from 'drizzle-orm'
import type { Db } from '../client'
import { invoices } from '../schema/invoices'
import { expenses } from '../schema/expenses'
import { taxRates } from '../schema/tax'
import { tenantSettings } from '../schema/tenants'
import { mileageEntries } from '../schema/mileage'
import { payoutBills, contractors } from '../schema/contractors'
import { customers } from '../schema/customers'
import type {
  Pcn874Report,
  AnnualIncomeSummary,
  AnnualSummaryDetail,
  AdvanceTaxEstimate,
  TaxSettings,
  AnnualExpenseBreakdownRow,
  TaxBracket,
} from '@zync/types'

const expenseApprovalFilter = inArray(expenses.approvalStatus, ['approved', 'not_required'])

// ── VAT Aggregation (PCN874) ──────────────────────────────────────────────────

/**
 * Aggregate output VAT, credit notes, and input VAT for a PCN874 period.
 */
export async function getPcn874Report(
  db: Db,
  tenantId: string,
  periodFrom: string,
  periodTo: string,
): Promise<Pcn874Report> {
  const [settingsRow] = await db
    .select({ vatPeriod: tenantSettings.vatPeriod })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))

  const vatPeriod = (settingsRow?.vatPeriod ?? 'bimonthly') as 'monthly' | 'bimonthly'

  // ── Output VAT (non-credit invoices, excluding DRAFT/SENT/VOID/BAD_DEBT) ──
  const [outputRow] = await db
    .select({
      invoiceCount: sql<string>`COUNT(*)`,
      turnoverIls: sql<string>`COALESCE(SUM(${invoices.total}::numeric), 0)`,
      vatIls: sql<string>`COALESCE(SUM(${invoices.vatAmount}::numeric), 0)`,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.taxIssueDate} BETWEEN ${periodFrom} AND ${periodTo}`,
        notInArray(invoices.status, ['DRAFT', 'SENT', 'VOID', 'BAD_DEBT']),
        sql`${invoices.source} != 'credit_note'`,
      ),
    )

  // ── Credit notes ──
  const [creditRow] = await db
    .select({
      creditCount: sql<string>`COUNT(*)`,
      creditTurnoverIls: sql<string>`COALESCE(SUM(ABS(${invoices.total}::numeric)), 0)`,
      creditVatIls: sql<string>`COALESCE(SUM(ABS(${invoices.vatAmount}::numeric)), 0)`,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.taxIssueDate} BETWEEN ${periodFrom} AND ${periodTo}`,
        notInArray(invoices.status, ['DRAFT', 'SENT', 'VOID', 'BAD_DEBT']),
        sql`${invoices.source} = 'credit_note'`,
      ),
    )

  const inputBaseFilter = and(
    eq(expenses.tenantId, tenantId),
    isNull(expenses.deletedAt),
    expenseApprovalFilter,
    eq(expenses.vatDeductible, true),
    eq(expenses.isPerDiem, false),
    eq(expenses.status, 'COMPLETED'),
    sql`${expenses.expenseDate} BETWEEN ${periodFrom} AND ${periodTo}`,
  )

  // ── Input VAT: full-deductible rows (NULL deduction_pct treated as 100%) ──
  const [fullInputRow] = await db
    .select({
      expenseCount: sql<string>`COUNT(*)`,
      expenseTotalIls: sql<string>`COALESCE(SUM(${expenses.amount}::numeric * ${expenses.businessPercent}::numeric / 100), 0)`,
      inputVatIls: sql<string>`COALESCE(SUM(${expenses.vatAmount}::numeric * ${expenses.businessPercent}::numeric / 100), 0)`,
    })
    .from(expenses)
    .where(and(inputBaseFilter, sql`COALESCE(${expenses.deductionPct}, 100) = 100`))

  // ── Input VAT: partial-deductible rows (0 < deduction_pct < 100) ──
  const [partialInputRow] = await db
    .select({
      expenseCount: sql<string>`COUNT(*)`,
      expenseTotalIls: sql<string>`COALESCE(SUM(${expenses.amount}::numeric * ${expenses.businessPercent}::numeric / 100 * ${expenses.deductionPct}::numeric / 100), 0)`,
      inputVatIls: sql<string>`COALESCE(SUM(${expenses.vatAmount}::numeric * ${expenses.businessPercent}::numeric / 100 * ${expenses.deductionPct}::numeric / 100), 0)`,
    })
    .from(expenses)
    .where(
      and(
        inputBaseFilter,
        sql`${expenses.deductionPct} > 0`,
        sql`${expenses.deductionPct} < 100`,
      ),
    )

  const inputExpenseCount =
    parseInt(fullInputRow?.expenseCount ?? '0', 10) +
    parseInt(partialInputRow?.expenseCount ?? '0', 10)
  const inputExpenseTotalIls =
    parseFloat(fullInputRow?.expenseTotalIls ?? '0') +
    parseFloat(partialInputRow?.expenseTotalIls ?? '0')
  const inputVatIls =
    parseFloat(fullInputRow?.inputVatIls ?? '0') +
    parseFloat(partialInputRow?.inputVatIls ?? '0')

  const netOutputVat =
    parseFloat(outputRow?.vatIls ?? '0') - parseFloat(creditRow?.creditVatIls ?? '0')
  const netInputVat = inputVatIls
  const vatPayable = netOutputVat - netInputVat

  return {
    periodFrom,
    periodTo,
    vatPeriod,
    outputInvoiceCount: parseInt(outputRow?.invoiceCount ?? '0', 10),
    outputTurnoverIls: (parseFloat(outputRow?.turnoverIls ?? '0')).toFixed(2),
    outputVatIls: (parseFloat(outputRow?.vatIls ?? '0')).toFixed(2),
    creditNoteCount: parseInt(creditRow?.creditCount ?? '0', 10),
    creditNoteTurnoverIls: (parseFloat(creditRow?.creditTurnoverIls ?? '0')).toFixed(2),
    creditNoteVatIls: (parseFloat(creditRow?.creditVatIls ?? '0')).toFixed(2),
    netOutputVatIls: netOutputVat.toFixed(2),
    inputExpenseCount,
    inputExpenseTotalIls: inputExpenseTotalIls.toFixed(2),
    inputVatIls: inputVatIls.toFixed(2),
    netInputVatIls: netInputVat.toFixed(2),
    vatPayableIls: vatPayable.toFixed(2),
  }
}

// ── Annual Income Summary ─────────────────────────────────────────────────────

/**
 * Aggregate annual income, bad-debt write-offs, and deductible expenses.
 */
export async function getAnnualIncomeSummary(
  db: Db,
  tenantId: string,
  year: number,
): Promise<AnnualIncomeSummary> {
  const periodFrom = `${year}-01-01`
  const periodTo = `${year}-12-31`
  const yearStartIso = `${year}-01-01`
  const yearEndIso = `${year + 1}-01-01`

  // ── Total invoiced income (subtotal ex-VAT, incl. bad-debt invoices; excl. DRAFT/SENT/VOID + credit notes) ──
  const [incomeRow] = await db
    .select({
      totalIls: sql<string>`COALESCE(SUM(${invoices.subtotal}::numeric), 0)`,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.taxIssueDate} BETWEEN ${periodFrom} AND ${periodTo}`,
        notInArray(invoices.status, ['DRAFT', 'SENT', 'VOID']),
        sql`${invoices.source} != 'credit_note'`,
      ),
    )

  // ── Bad debt write-offs (ex-VAT subtotal; foreign-currency uses raw subtotal — IL tenants invoice in ILS) ──
  const [badDebtRow] = await db
    .select({
      totalIls: sql<string>`COALESCE(SUM(${invoices.subtotal}::numeric), 0)`,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.taxIssueDate} BETWEEN ${periodFrom} AND ${periodTo}`,
        inArray(invoices.status, ['BAD_DEBT', 'WRITTEN_OFF']),
      ),
    )

  // ── Deductible expenses by category ──
  const expenseRows = await db
    .select({
      category: sql<string>`COALESCE(${expenses.expenseCategory}, 'uncategorized')`,
      totalIls: sql<string>`COALESCE(SUM(${expenses.amount}::numeric * ${expenses.businessPercent}::numeric / 100), 0)`,
      deductibleIls: sql<string>`COALESCE(SUM(CASE WHEN ${expenses.vatDeductible} THEN ${expenses.amount}::numeric * ${expenses.businessPercent}::numeric / 100 * COALESCE(${expenses.deductionPct}, 100)::numeric / 100 ELSE 0 END), 0)`,
    })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        isNull(expenses.deletedAt),
        eq(expenses.isPerDiem, false),
        sql`${expenses.expenseDate} BETWEEN ${periodFrom} AND ${periodTo}`,
        eq(expenses.status, 'COMPLETED'),
        expenseApprovalFilter,
      ),
    )
    .groupBy(sql`COALESCE(${expenses.expenseCategory}, 'uncategorized')`)

  const breakdown: AnnualExpenseBreakdownRow[] = expenseRows.map((r) => ({
    category: r.category,
    totalIls: parseFloat(r.totalIls).toFixed(2),
    deductibleIls: parseFloat(r.deductibleIls).toFixed(2),
  }))

  const expenseDeductions = breakdown.reduce(
    (sum, r) => sum + parseFloat(r.deductibleIls),
    0,
  )

  // ── Mileage reimbursement (tenant-scoped, business trips) ──
  const [mileageRow] = await db
    .select({
      total: sql<string>`COALESCE(SUM(${mileageEntries.reimbursementAmount}::numeric), 0)`,
    })
    .from(mileageEntries)
    .where(
      and(
        eq(mileageEntries.tenantId, tenantId),
        eq(mileageEntries.isBusinessTrip, true),
        sql`${mileageEntries.date} >= ${yearStartIso}`,
        sql`${mileageEntries.date} < ${yearEndIso}`,
      ),
    )

  // ── Contractor payouts (PAID within year) ──
  const [payoutRow] = await db
    .select({
      total: sql<string>`COALESCE(SUM(${payoutBills.netAmount}::numeric), 0)`,
    })
    .from(payoutBills)
    .where(
      and(
        eq(payoutBills.tenantId, tenantId),
        eq(payoutBills.status, 'PAID'),
        sql`date_part('year', ${payoutBills.paidAt}) = ${year}`,
      ),
    )

  const mileageDeduction = parseFloat(mileageRow?.total ?? '0')
  const contractorPayouts = parseFloat(payoutRow?.total ?? '0')
  const totalDeductions = expenseDeductions + mileageDeduction + contractorPayouts

  const totalInvoiced = parseFloat(incomeRow?.totalIls ?? '0')
  const badDebt = parseFloat(badDebtRow?.totalIls ?? '0')
  const adjustedIncome = totalInvoiced - badDebt
  const netTaxable = adjustedIncome - totalDeductions

  return {
    year,
    totalInvoicedIncomeIls: totalInvoiced.toFixed(2),
    badDebtWriteOffIls: badDebt.toFixed(2),
    adjustedIncomeIls: adjustedIncome.toFixed(2),
    expenseBreakdown: breakdown,
    mileageDeductionIls: mileageDeduction.toFixed(2),
    contractorPayoutsIls: contractorPayouts.toFixed(2),
    totalDeductionsIls: totalDeductions.toFixed(2),
    netTaxableIncomeIls: netTaxable.toFixed(2),
  }
}

/**
 * Fetch supporting detail rows for annual summary xlsx export.
 */
export async function getAnnualSummaryDetail(
  db: Db,
  tenantId: string,
  year: number,
): Promise<AnnualSummaryDetail> {
  const periodFrom = `${year}-01-01`
  const periodTo = `${year}-12-31`
  const yearStartIso = `${year}-01-01`
  const yearEndIso = `${year + 1}-01-01`

  const invoiceRows = await db
    .select({
      number: sql<string>`COALESCE(${invoices.invoiceNumber}, ${invoices.proformaNumber}, '')`,
      date: invoices.taxIssueDate,
      customer: sql<string>`COALESCE(${customers.name}, '')`,
      subtotalIls: invoices.subtotal,
      vatIls: invoices.vatAmount,
      totalIls: sql<string>`${invoices.total}::numeric`,
    })
    .from(invoices)
    .leftJoin(
      customers,
      and(eq(invoices.customerId, customers.id), eq(customers.tenantId, tenantId)),
    )
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.taxIssueDate} BETWEEN ${periodFrom} AND ${periodTo}`,
        notInArray(invoices.status, ['DRAFT', 'SENT', 'VOID']),
        sql`${invoices.source} != 'credit_note'`,
      ),
    )
    .orderBy(invoices.taxIssueDate, sql`COALESCE(${invoices.invoiceNumber}, ${invoices.proformaNumber}, '')`)

  const mileageRows = await db
    .select({
      date: mileageEntries.date,
      description: mileageEntries.purpose,
      distanceKm: mileageEntries.distanceKm,
      reimbursementIls: mileageEntries.reimbursementAmount,
    })
    .from(mileageEntries)
    .where(
      and(
        eq(mileageEntries.tenantId, tenantId),
        eq(mileageEntries.isBusinessTrip, true),
        sql`${mileageEntries.date} >= ${yearStartIso}`,
        sql`${mileageEntries.date} < ${yearEndIso}`,
      ),
    )
    .orderBy(mileageEntries.date)

  const payoutRows = await db
    .select({
      contractor: contractors.name,
      datePaid: payoutBills.paidAt,
      netAmountIls: payoutBills.netAmount,
    })
    .from(payoutBills)
    .innerJoin(
      contractors,
      and(eq(payoutBills.contractorId, contractors.id), eq(contractors.tenantId, tenantId)),
    )
    .where(
      and(
        eq(payoutBills.tenantId, tenantId),
        eq(payoutBills.status, 'PAID'),
        sql`date_part('year', ${payoutBills.paidAt}) = ${year}`,
      ),
    )
    .orderBy(payoutBills.paidAt)

  return {
    invoices: invoiceRows.map((r) => ({
      number: r.number,
      date: r.date ?? '',
      customer: r.customer,
      subtotalIls: parseFloat(r.subtotalIls ?? '0').toFixed(2),
      vatIls: parseFloat(r.vatIls ?? '0').toFixed(2),
      totalIls: parseFloat(r.totalIls ?? '0').toFixed(2),
    })),
    mileage: mileageRows.map((r) => ({
      date: r.date.toISOString().slice(0, 10),
      description: r.description,
      distanceKm: parseFloat(r.distanceKm ?? '0').toFixed(2),
      reimbursementIls: parseFloat(r.reimbursementIls ?? '0').toFixed(2),
    })),
    payouts: payoutRows.map((r) => ({
      contractor: r.contractor,
      datePaid: r.datePaid ? r.datePaid.toISOString().slice(0, 10) : '',
      netAmountIls: parseFloat(r.netAmountIls ?? '0').toFixed(2),
    })),
  }
}

// ── Advance Tax Estimate ──────────────────────────────────────────────────────

/**
 * Compute marginal income-tax estimate using IL personal brackets from tax_rates.
 */
function computeMarginalTax(income: number, brackets: TaxBracket[]): number {
  let remaining = income
  let tax = 0
  let prevThreshold = 0

  for (const bracket of brackets) {
    const threshold = bracket.thresholdIls !== null ? parseFloat(bracket.thresholdIls) : Infinity
    const rate = parseFloat(bracket.rate)
    const bandSize = threshold === Infinity ? remaining : Math.min(remaining, threshold - prevThreshold)
    if (bandSize <= 0) break
    tax += bandSize * rate
    remaining -= bandSize
    prevThreshold = threshold === Infinity ? prevThreshold : threshold
    if (remaining <= 0) break
  }

  return tax
}

/**
 * Compute advance tax estimate for a given year.
 */
export async function getAdvanceTaxEstimate(
  db: Db,
  tenantId: string,
  year: number,
): Promise<AdvanceTaxEstimate> {
  const now = new Date()
  const monthsElapsed = year < now.getFullYear()
    ? 12
    : year > now.getFullYear()
    ? 0
    : now.getMonth() + 1

  const periodFrom = `${year}-01-01`
  const periodTo = year < now.getFullYear()
    ? `${year}-12-31`
    : `${year}-${String(now.getMonth() + 1).padStart(2, '0')}-${String(now.getDate()).padStart(2, '0')}`

  // ── YTD net income ──
  const [incomeRow] = await db
    .select({
      totalIls: sql<string>`COALESCE(SUM(${invoices.subtotal}::numeric), 0)`,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.taxIssueDate} BETWEEN ${periodFrom} AND ${periodTo}`,
        notInArray(invoices.status, ['DRAFT', 'SENT', 'VOID', 'BAD_DEBT', 'WRITTEN_OFF']),
        sql`${invoices.source} != 'credit_note'`,
      ),
    )

  const [expenseRow] = await db
    .select({
      totalIls: sql<string>`COALESCE(SUM(${expenses.amount}::numeric * ${expenses.businessPercent}::numeric / 100 * COALESCE(${expenses.deductionPct}, 100)::numeric / 100), 0)`,
    })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        isNull(expenses.deletedAt),
        eq(expenses.isPerDiem, false),
        sql`${expenses.expenseDate} BETWEEN ${periodFrom} AND ${periodTo}`,
        eq(expenses.status, 'COMPLETED'),
        expenseApprovalFilter,
      ),
    )

  const ytdIncome = parseFloat(incomeRow?.totalIls ?? '0')
  const ytdExpenses = parseFloat(expenseRow?.totalIls ?? '0')
  const ytdNetIncome = ytdIncome - ytdExpenses

  // ── Project annual ──
  const monthsTotal = 12
  const estimatedAnnual =
    monthsElapsed > 0 ? (ytdNetIncome / monthsElapsed) * monthsTotal : 0

  // ── Fetch IL personal income brackets (most recent effective set) ──
  const asOfDate = `${year}-12-31`
  const bracketRows = await db
    .select({
      taxType: taxRates.taxType,
      rate: taxRates.rate,
      thresholdIls: taxRates.thresholdIls,
      effectiveFrom: taxRates.effectiveFrom,
    })
    .from(taxRates)
    .where(
      and(
        eq(taxRates.countryCode, 'IL'),
        sql`${taxRates.taxType} LIKE 'personal_bracket_%'`,
        sql`${taxRates.effectiveFrom} <= ${asOfDate}`,
      ),
    )
    .orderBy(desc(taxRates.effectiveFrom), taxRates.thresholdIls)

  // Keep only the most-recent effective_from set
  const latestEffectiveFrom = bracketRows[0]?.effectiveFrom ?? null
  const activeBrackets = latestEffectiveFrom
    ? bracketRows.filter((r) => r.effectiveFrom === latestEffectiveFrom)
    : bracketRows

  const brackets: TaxBracket[] = activeBrackets.map((r) => ({
    bracketType: r.taxType,
    rate: r.rate,
    thresholdIls: r.thresholdIls ?? null,
  }))

  const estimatedTax = computeMarginalTax(Math.max(0, estimatedAnnual), brackets)

  // ── Tenant advance rate + YTD payments ──
  const [settingsRow] = await db
    .select({
      advanceTaxRatePct: tenantSettings.advanceTaxRatePct,
      advancePaymentsYtdIls: tenantSettings.advancePaymentsYtdIls,
    })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))

  const advanceTaxRatePct = settingsRow?.advanceTaxRatePct ?? null
  const advancePaymentsYtd = parseFloat(settingsRow?.advancePaymentsYtdIls ?? '0')

  // Recommended advance per remaining month
  const monthsRemaining = monthsTotal - monthsElapsed
  const remainingLiability =
    advanceTaxRatePct != null
      ? Math.max(
          0,
          estimatedTax * (parseFloat(advanceTaxRatePct) / 100) - advancePaymentsYtd,
        )
      : 0
  const recommendedPerMonth =
    monthsRemaining > 0 && advanceTaxRatePct ? remainingLiability / monthsRemaining : 0

  return {
    year,
    ytdNetIncomeIls: ytdNetIncome.toFixed(2),
    monthsElapsed,
    advanceTaxRatePct: advanceTaxRatePct,
    estimatedAnnualIncomeIls: estimatedAnnual.toFixed(2),
    taxBrackets: brackets,
    estimatedAnnualIncomeTaxIls: estimatedTax.toFixed(2),
    advancePaymentsYtdIls: advancePaymentsYtd.toFixed(2),
    recommendedAdvancePerRemainingMonthIls: recommendedPerMonth.toFixed(2),
  }
}

// ── Tax Settings ──────────────────────────────────────────────────────────────

/**
 * Fetch tenant tax settings (vat_period, advance_tax_rate_pct).
 */
export async function getTaxSettings(db: Db, tenantId: string): Promise<TaxSettings> {
  const [row] = await db
    .select({
      vatPeriod: tenantSettings.vatPeriod,
      advanceTaxRatePct: tenantSettings.advanceTaxRatePct,
      advancePaymentsYtdIls: tenantSettings.advancePaymentsYtdIls,
    })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))

  return {
    vatPeriod: (row?.vatPeriod ?? 'bimonthly') as 'monthly' | 'bimonthly',
    advanceTaxRatePct: row?.advanceTaxRatePct ?? null,
    advancePaymentsYtdIls: row?.advancePaymentsYtdIls ?? null,
  }
}

/**
 * Update tenant tax settings.
 */
export async function updateTaxSettings(
  db: Db,
  tenantId: string,
  patch: {
    vatPeriod?: 'monthly' | 'bimonthly'
    advanceTaxRatePct?: number
    advancePaymentsYtdIls?: number
  },
): Promise<TaxSettings> {
  const updates: Partial<typeof tenantSettings.$inferInsert> = {}
  if (patch.vatPeriod !== undefined) updates.vatPeriod = patch.vatPeriod
  if (patch.advanceTaxRatePct !== undefined)
    updates.advanceTaxRatePct = String(patch.advanceTaxRatePct)
  if (patch.advancePaymentsYtdIls !== undefined)
    updates.advancePaymentsYtdIls = String(patch.advancePaymentsYtdIls)

  if (Object.keys(updates).length > 0) {
    await db
      .update(tenantSettings)
      .set(updates)
      .where(eq(tenantSettings.tenantId, tenantId))
  }

  return getTaxSettings(db, tenantId)
}