/**
 * Expense query helpers — expenses-module.
 * All queries are tenant-scoped. Route files import from '@zync/db/queries'.
 */
import { and, eq, isNull, lte, gte, desc, asc, count, sql, inArray, notInArray } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import { expenses, expenseCorrections } from '../schema/expenses'
import type { ExpenseRow, NewExpense, ExpenseCorrectionRow } from '../schema/expenses'
import { tenantSettings } from '../schema/tenants'
import { invoices } from '../schema/invoices'
import { users } from '../schema/users'
import { vendors } from '../schema/vendors'
import { auditLog } from './_audit-forward'
import { captureEntityChange } from './entity-history'

/** Optional actor context for operational-audit-trail diff capture. */
export interface ExpenseActorContext {
  actorName?: string | null
  actorEmail?: string | null
  ipAddress?: string | null
}

import type {
  Expense,
  ExpenseCorrection,
  ExpenseBillingStatus,
  ExpenseListResponse,
  ExpenseStatus,
  ExpenseSource,
  ExpenseSettings,
} from '@zync/types'
import type { ExpenseCategoryId } from '@zync/types'
import { computeSplit, mergeExpenseSettings } from '@zync/types'

// ── Cursor helpers ─────────────────────────────────────────────────────────────

function encodeCursor(createdAt: Date, id: string): string {
  const payload = JSON.stringify({ createdAt: createdAt.toISOString(), id })
  return Buffer.from(payload).toString('base64url')
}

function decodeCursor(cursor: string): { createdAt: string; id: string } | null {
  try {
    const raw = Buffer.from(cursor, 'base64url').toString('utf8')
    const parsed = JSON.parse(raw) as { createdAt: string; id: string }
    if (typeof parsed.createdAt !== 'string' || typeof parsed.id !== 'string') return null
    return parsed
  } catch {
    return null
  }
}

// ── Serializers ────────────────────────────────────────────────────────────────

export function serializeExpenseRow(row: ExpenseRow): Expense {
  const splitBaseAmount = row.invoiceTotal ?? row.amount ?? null
  const split =
    splitBaseAmount !== null
      ? computeSplit(Number.parseFloat(splitBaseAmount), row.businessPercent)
      : null

  const billingStatus: ExpenseBillingStatus =
    row.billedAt
      ? 'billed'
      : row.projectId
        ? 'unbilled'
        : 'no_project'

  return {
    id: row.id,
    tenantId: row.tenantId,
    projectId: row.projectId ?? null,
    createdBy: row.createdBy,
    r2Key: row.r2Key,
    fileName: row.fileName,
    fileType: row.fileType as Expense['fileType'],
    fileSizeBytes: row.fileSizeBytes,
    vendorName: row.vendorName ?? null,
    vendorTaxId: row.vendorTaxId ?? null,
    invoiceNumber: row.invoiceNumber ?? null,
    invoiceTotal: row.invoiceTotal ?? null,
    vatAmount: row.vatAmount ?? null,
    currency: row.currency,
    allocationNumber: row.allocationNumber ?? null,
    rawOcrText: row.rawOcrText ?? null,
    expenseDate: row.expenseDate ?? null,
    amount: row.amount ?? null,
    businessAmount: split ? split.businessAmount.toFixed(2) : null,
    personalAmount: split ? split.personalAmount.toFixed(2) : null,
    vatDeductible: row.vatDeductible,
    status: row.status as ExpenseStatus,
    ocrConfidence: row.ocrConfidence ?? null,
    processingStartedAt: row.processingStartedAt?.toISOString() ?? null,
    processedAt: row.processedAt?.toISOString() ?? null,
    processingError: row.processingError ?? null,
    expenseCategory: (row.expenseCategory ?? null) as ExpenseCategoryId | null,
    deductionPct: row.deductionPct ?? null,
    deductionConfidence: row.deductionConfidence ?? null,
    deductionReasoningHe: row.deductionReasoningHe ?? null,
    deductionReasoningEn: row.deductionReasoningEn ?? null,
    evaluatedAt: row.evaluatedAt?.toISOString() ?? null,
    isPerDiem: row.isPerDiem,
    perDiemDays: row.perDiemDays ?? null,
    perDiemRateIls: row.perDiemRateIls ?? null,
    source: row.source as ExpenseSource,
    sourceMetadata: (row.sourceMetadata as Record<string, unknown>) ?? null,
    notes: row.notes ?? null,
    businessPercent: row.businessPercent,
    billedAt: row.billedAt?.toISOString() ?? null,
    invoiceId: row.invoiceId ?? null,
    billingStatus,
    // OCR correction UX fields
    correctionNote: row.correctionNote ?? null,
    voidedAt: row.voidedAt?.toISOString() ?? null,
    voidedReason: row.voidedReason ?? null,
    // Approval workflow fields
    approvalStatus: row.approvalStatus,
    approvedBy: row.approvedBy ?? null,
    approvedAt: row.approvedAt?.toISOString() ?? null,
    approvalNote: row.approvalNote ?? null,
    createdAt: row.createdAt.toISOString(),
    updatedAt: row.updatedAt.toISOString(),
  }
}

export function serializeCorrectionRow(row: ExpenseCorrectionRow): ExpenseCorrection {
  return {
    id: row.id,
    expenseId: row.expenseId,
    userId: row.userId,
    fieldName: row.fieldName,
    originalValue: row.originalValue ?? null,
    correctedValue: row.correctedValue,
    correctionSource: (row.correctionSource ?? 'ocr') as import('@zync/types').CorrectionSource,
    createdAt: row.createdAt.toISOString(),
  }
}

// ── Filters ────────────────────────────────────────────────────────────────────

export interface ExpenseFilters {
  cursor?: string
  limit?: number
  dateFrom?: string
  dateTo?: string
  category?: ExpenseCategoryId
  deductionPct?: number
  status?: ExpenseStatus
  source?: ExpenseSource
  projectId?: string
  tab?: 'all' | 'needs_review' | 'recurring'
  billable?: boolean
}

// ── List expenses ──────────────────────────────────────────────────────────────

export async function listExpenses(
  db: Db,
  tenantId: string,
  f: ExpenseFilters,
): Promise<ExpenseListResponse> {
  const limit = Math.min(f.limit ?? 50, 100)
  const cursor = f.cursor ? decodeCursor(f.cursor) : null

  const buildConditions = () => {
    const conds = [eq(expenses.tenantId, tenantId), isNull(expenses.deletedAt)]

    if (f.tab === 'needs_review') {
      conds.push(eq(expenses.status, 'NEEDS_REVIEW'))
    } else if (f.tab === 'recurring') {
      conds.push(sql`${expenses.sourceMetadata}->>'recurring' IS NOT NULL`)
    }

    if (f.status && f.tab === undefined) {
      conds.push(eq(expenses.status, f.status))
    }
    if (f.dateFrom) conds.push(gte(expenses.expenseDate, f.dateFrom))
    if (f.dateTo) conds.push(lte(expenses.expenseDate, f.dateTo))
    if (f.category) conds.push(eq(expenses.expenseCategory, f.category))
    if (f.deductionPct !== undefined) conds.push(eq(expenses.deductionPct, f.deductionPct))
    if (f.source) conds.push(eq(expenses.source, f.source))
    if (f.projectId) conds.push(eq(expenses.projectId, f.projectId))
    if (f.billable) {
      conds.push(eq(expenses.status, 'COMPLETED'))
      conds.push(isNull(expenses.billedAt))
      conds.push(sql`${expenses.projectId} IS NOT NULL`)
    }

    return conds
  }

  const baseConds = buildConditions()
  const countResult = await db
    .select({ value: count() })
    .from(expenses)
    .where(and(...baseConds))
  const total = countResult[0]?.value ?? 0

  const pageConds = [...baseConds]
  if (cursor) {
    pageConds.push(
      sql`(${expenses.createdAt}, ${expenses.id}) < (${new Date(cursor.createdAt)}, ${cursor.id})`,
    )
  }

  const rows = await db
    .select()
    .from(expenses)
    .where(and(...pageConds))
    .orderBy(desc(expenses.createdAt), desc(expenses.id))
    .limit(limit + 1)

  const hasMore = rows.length > limit
  const pageRows = hasMore ? rows.slice(0, limit) : rows
  const lastRow = pageRows.at(-1)
  const nextCursor = hasMore && lastRow ? encodeCursor(lastRow.createdAt, lastRow.id) : null

  return {
    items: pageRows.map(serializeExpenseRow),
    nextCursor,
    total,
  }
}

// ── Get expense ────────────────────────────────────────────────────────────────

export async function getExpense(
  db: Db,
  tenantId: string,
  id: string,
): Promise<{ expense: Expense; corrections: ExpenseCorrection[] } | null> {
  const [row] = await db
    .select()
    .from(expenses)
    .where(and(eq(expenses.id, id), eq(expenses.tenantId, tenantId), isNull(expenses.deletedAt)))

  if (!row) return null

  const correctionRows = await db
    .select()
    .from(expenseCorrections)
    .where(eq(expenseCorrections.expenseId, id))
    .orderBy(asc(expenseCorrections.createdAt))

  return {
    expense: serializeExpenseRow(row),
    corrections: correctionRows.map(serializeCorrectionRow),
  }
}

// ── Get by ID (raw row) ────────────────────────────────────────────────────────

export async function getExpenseById(
  db: Db | DbTx,
  tenantId: string,
  id: string,
): Promise<ExpenseRow | null> {
  const [row] = await db
    .select()
    .from(expenses)
    .where(and(eq(expenses.id, id), eq(expenses.tenantId, tenantId)))
  return row ?? null
}

// ── EditableExpenseFields ──────────────────────────────────────────────────────

export interface EditableExpenseFields {
  vendorName?: string | null
  vendorTaxId?: string | null
  invoiceNumber?: string | null
  invoiceTotal?: string | null
  vatAmount?: string | null
  currency?: string
  allocationNumber?: string | null
  expenseDate?: string | null
  amount?: string | null
  vatDeductible?: boolean
  expenseCategory?: ExpenseCategoryId | null
  deductionPct?: number | null
  notes?: string | null
  businessPercent?: number
}

export interface NewExpenseInput {
  tenantId: string
  projectId?: string | null
  createdBy: string
  r2Key: string
  fileName: string
  fileType: 'pdf' | 'jpg' | 'png' | 'heic'
  fileSizeBytes: number
  source?: ExpenseSource
  sourceMetadata?: Record<string, unknown> | null
  isPerDiem?: boolean
  perDiemDays?: string | null
  perDiemRateIls?: string | null
  expenseCategory?: string | null
  vatAmount?: string | null
  vatDeductible?: boolean
  amount?: string | null
  expenseDate?: string | null
  notes?: string | null
  status?: ExpenseStatus
  approvalStatus?: string
}

// ── Create expense ─────────────────────────────────────────────────────────────

export async function createExpense(
  db: Db,
  tenantId: string,
  input: NewExpenseInput,
): Promise<Expense> {
  const [row] = await db
    .insert(expenses)
    .values({
      tenantId,
      projectId: input.projectId ?? null,
      createdBy: input.createdBy,
      r2Key: input.r2Key,
      fileName: input.fileName,
      fileType: input.fileType,
      fileSizeBytes: input.fileSizeBytes,
      source: input.source ?? 'upload',
      sourceMetadata: input.sourceMetadata ?? null,
      isPerDiem: input.isPerDiem ?? false,
      perDiemDays: input.perDiemDays ?? null,
      perDiemRateIls: input.perDiemRateIls ?? null,
      expenseCategory: input.expenseCategory ?? null,
      vatAmount: input.vatAmount ?? null,
      vatDeductible: input.vatDeductible ?? true,
      amount: input.amount ?? null,
      expenseDate: input.expenseDate ?? null,
      notes: input.notes ?? null,
      status: input.status ?? 'PENDING',
      approvalStatus: input.approvalStatus ?? 'not_required',
    })
    .returning()

  if (!row) throw new Error('Failed to create expense')
  return serializeExpenseRow(row)
}

// ── Update expense (with corrections) ─────────────────────────────────────────

export async function updateExpense(
  db: Db,
  tenantId: string,
  userId: string,
  id: string,
  patch: Partial<EditableExpenseFields>,
  actorCtx?: ExpenseActorContext,
): Promise<Expense> {
  return db.transaction(async (tx) => {
    const [existing] = await tx
      .select()
      .from(expenses)
      .where(and(eq(expenses.id, id), eq(expenses.tenantId, tenantId), isNull(expenses.deletedAt)))

    if (!existing) throw new Error('Expense not found')

    if (existing.status !== 'COMPLETED' && existing.status !== 'NEEDS_REVIEW') {
      throw new Error('Expense is not editable in current status')
    }

    const correctionInserts: Array<{
      expenseId: string
      userId: string
      fieldName: string
      originalValue: string | null
      correctedValue: string
    }> = []

    for (const field of Object.keys(patch) as (keyof EditableExpenseFields)[]) {
      const oldVal = existing[field as keyof ExpenseRow]
      const newVal = patch[field]
      const oldStr = oldVal == null ? null : String(oldVal)
      const newStr = newVal == null ? null : String(newVal)
      if (oldStr !== newStr && newStr !== null) {
        correctionInserts.push({
          expenseId: id,
          userId,
          fieldName: field,
          originalValue: oldStr,
          correctedValue: newStr,
        })
      }
    }

    if (correctionInserts.length > 0) {
      await tx.insert(expenseCorrections).values(correctionInserts)
    }

    const updatePayload: Partial<NewExpense> = { updatedAt: new Date() }
    if (patch.vendorName !== undefined) updatePayload.vendorName = patch.vendorName
    if (patch.vendorTaxId !== undefined) updatePayload.vendorTaxId = patch.vendorTaxId
    if (patch.invoiceNumber !== undefined) updatePayload.invoiceNumber = patch.invoiceNumber
    if (patch.invoiceTotal !== undefined) updatePayload.invoiceTotal = patch.invoiceTotal
    if (patch.vatAmount !== undefined) updatePayload.vatAmount = patch.vatAmount
    if (patch.currency !== undefined) updatePayload.currency = patch.currency
    if (patch.allocationNumber !== undefined) updatePayload.allocationNumber = patch.allocationNumber
    if (patch.expenseDate !== undefined) updatePayload.expenseDate = patch.expenseDate
    if (patch.amount !== undefined) updatePayload.amount = patch.amount
    if (patch.vatDeductible !== undefined) updatePayload.vatDeductible = patch.vatDeductible
    if (patch.expenseCategory !== undefined) updatePayload.expenseCategory = patch.expenseCategory
    if (patch.deductionPct !== undefined) updatePayload.deductionPct = patch.deductionPct
    if (patch.notes !== undefined) updatePayload.notes = patch.notes
    if (patch.businessPercent !== undefined) updatePayload.businessPercent = patch.businessPercent

    const [updated] = await tx
      .update(expenses)
      .set(updatePayload)
      .where(and(eq(expenses.id, id), eq(expenses.tenantId, tenantId)))
      .returning()

    if (!updated) throw new Error('Expense update failed')

    await tx.insert(auditLog).values({
      tenantId,
      actorId: userId,
      actorType: 'user',
      entityType: 'expense',
      entityId: id,
      action: 'expense.updated',
    })

    // operational-audit-trail: field diff
    const beforeRecord: Record<string, unknown> = {}
    const afterRecord: Record<string, unknown> = {}
    for (const field of Object.keys(patch) as (keyof typeof patch)[]) {
      const oldVal = existing[field as keyof ExpenseRow]
      const newVal = patch[field]
      if (JSON.stringify(oldVal) !== JSON.stringify(newVal)) {
        beforeRecord[field] = oldVal
        afterRecord[field] = newVal
      }
    }
    if (Object.keys(afterRecord).length > 0) {
      await captureEntityChange({
        tx, tenantId, userId,
        actorName: actorCtx?.actorName ?? null,
        actorEmail: actorCtx?.actorEmail ?? null,
        eventType: 'expense.field_updated',
        entityType: 'expense', entityId: id,
        entityLabel: updated!.vendorName ?? id,
        beforeState: beforeRecord,
        afterState: afterRecord,
        ipAddress: actorCtx?.ipAddress ?? null,
      })
    }

    return serializeExpenseRow(updated)
  })
}

// ── Bulk helpers ─────────────────────────────────────────────────────────────

export async function listExpensesByIds(
  db: Db,
  tenantId: string,
  ids: string[],
): Promise<ExpenseReportRow[]> {
  if (ids.length === 0) return []
  const rows = await db
    .select()
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        isNull(expenses.deletedAt),
        inArray(expenses.id, ids),
      ),
    )
    .orderBy(desc(expenses.expenseDate), desc(expenses.createdAt))

  return rows.map((r) => {
    const totalRaw = r.amount ?? null
    const total =
      totalRaw != null
        ? String(parseFloat(totalRaw) * r.businessPercent / 100)
        : null
    const vatRaw = r.vatAmount ?? null
    const vat =
      vatRaw != null && totalRaw != null
        ? String(parseFloat(vatRaw) * r.businessPercent / 100)
        : vatRaw
    let net: string | null = null
    if (total !== null && vat !== null) {
      net = String(parseFloat(total) - parseFloat(vat))
    }
    return {
      date: r.expenseDate ?? null,
      vendor: r.vendorName ?? null,
      invoiceNumber: r.invoiceNumber ?? null,
      vendorTaxId: r.vendorTaxId ?? null,
      total,
      vat,
      net,
      category: (r.expenseCategory ?? null) as ExpenseCategoryId | null,
      deductionPct: r.deductionPct ?? null,
      allocationNumber: r.allocationNumber ?? null,
      notes: r.notes ?? null,
    }
  })
}

export async function listNeedsReviewExpenseIds(
  db: Db,
  tenantId: string,
): Promise<string[]> {
  const rows = await db
    .select({ id: expenses.id })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        isNull(expenses.deletedAt),
        eq(expenses.status, 'NEEDS_REVIEW'),
      ),
    )
  return rows.map((r) => r.id)
}

// ── Soft delete ────────────────────────────────────────────────────────────────

export async function softDeleteExpense(db: Db, tenantId: string, id: string): Promise<void> {
  await db
    .update(expenses)
    .set({ deletedAt: new Date(), updatedAt: new Date() })
    .where(and(eq(expenses.id, id), eq(expenses.tenantId, tenantId), isNull(expenses.deletedAt)))
}

// ── Internal status update ─────────────────────────────────────────────────────

export async function setExpenseStatus(
  db: Db | DbTx,
  tenantId: string,
  id: string,
  status: ExpenseStatus,
  extra?: Partial<NewExpense>,
): Promise<void> {
  await db
    .update(expenses)
    .set({ status, ...extra, updatedAt: new Date() })
    .where(and(eq(expenses.id, id), eq(expenses.tenantId, tenantId)))
}

// ── Expense Settings ───────────────────────────────────────────────────────────

export async function getExpenseSettings(db: Db, tenantId: string): Promise<ExpenseSettings> {
  const [row] = await db
    .select()
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))

  if (!row) return mergeExpenseSettings(null)

  return mergeExpenseSettings({
    filing_cadence: row.filingCadence as ExpenseSettings['filing_cadence'],
    tax_basis: row.taxBasis as ExpenseSettings['tax_basis'],
    business_category: row.businessCategory ?? null,
    per_diem_rates: row.perDiemRates ?? undefined,
    expense_default_category: row.expenseDefaultCategory,
    expense_auto_approve_threshold_ils: row.expenseAutoApproveThresholdIls,
    expense_receipt_reminder_enabled: row.expenseReceiptReminderEnabled,
    expense_receipt_reminder_days: row.expenseReceiptReminderDays,
    expense_approval_threshold_ils: row.expenseApprovalThresholdIls,
    expense_approver_role: row.expenseApproverRole,
  })
}

export async function updateExpenseSettings(
  db: Db,
  tenantId: string,
  patch: Partial<ExpenseSettings>,
): Promise<ExpenseSettings> {
  const updatePayload: Record<string, unknown> = { updatedAt: new Date() }

  if (patch.filing_cadence !== undefined) updatePayload['filingCadence'] = patch.filing_cadence
  if (patch.tax_basis !== undefined) updatePayload['taxBasis'] = patch.tax_basis
  if (patch.business_category !== undefined) updatePayload['businessCategory'] = patch.business_category
  if (patch.per_diem_rates !== undefined) updatePayload['perDiemRates'] = patch.per_diem_rates
  if (patch.expense_default_category !== undefined) updatePayload['expenseDefaultCategory'] = patch.expense_default_category
  if (patch.expense_auto_approve_threshold_ils !== undefined) updatePayload['expenseAutoApproveThresholdIls'] = patch.expense_auto_approve_threshold_ils
  if (patch.expense_receipt_reminder_enabled !== undefined) updatePayload['expenseReceiptReminderEnabled'] = patch.expense_receipt_reminder_enabled
  if (patch.expense_receipt_reminder_days !== undefined) updatePayload['expenseReceiptReminderDays'] = patch.expense_receipt_reminder_days
  if (patch.expense_approval_threshold_ils !== undefined) updatePayload['expenseApprovalThresholdIls'] = patch.expense_approval_threshold_ils
  if (patch.expense_approver_role !== undefined) updatePayload['expenseApproverRole'] = patch.expense_approver_role

  await db
    .update(tenantSettings)
    .set(updatePayload)
    .where(eq(tenantSettings.tenantId, tenantId))

  return getExpenseSettings(db, tenantId)
}

// ── Reports ────────────────────────────────────────────────────────────────────

export interface ReportFilters {
  dateFrom?: string
  dateTo?: string
  vendor?: string
  category?: ExpenseCategoryId
  status?: ExpenseStatus
}

export interface ExpenseReportRow {
  date: string | null
  vendor: string | null
  invoiceNumber: string | null
  vendorTaxId: string | null
  total: string | null
  vat: string | null
  net: string | null
  category: ExpenseCategoryId | null
  deductionPct: number | null
  allocationNumber: string | null
  notes: string | null
}

export interface VatSummary {
  period: string
  inputVat: string
  partialInputVat: string
  outputVat: string
  netVatDue: string
}

export interface VendorAnalysisRow {
  vendor: string
  vendorId: string | null
  totalAmount: string
  totalVat: string
}

export interface ExpenseReportsUiFilters {
  from: string
  to: string
  category?: ExpenseCategoryId
  projectId?: string
  userId?: string
}

export interface ExpenseReportDetailRow {
  id: string
  expenseDate: string
  vendorName: string | null
  expenseCategory: ExpenseCategoryId | null
  amount: number
  deductionPct: number | null
  vatAmount: number | null
  vatDeductible: boolean
  isPerDiem: boolean
  createdBy: string
  createdByName: string
}

export interface ExpenseReportDetail {
  rows: ExpenseReportDetailRow[]
  totalAmount: number
  totalVat: number
  deductibleTotal: number
}

export interface VatSummaryUiLine {
  code: '220' | '225' | '320'
  descriptionHe: string
  amount: number
  vat: number
}

export interface VatSummaryUi {
  periodLabel: string
  filingCadence: 'monthly' | 'bimonthly'
  lines: VatSummaryUiLine[]
  vatToPay: number
}

export interface VendorAnalysisUiRow {
  vendorId: string | null
  vendorName: string
  count: number
  total: number
  avgDeductionPct: number
  vatTotal: number
}

export async function expenseReport(
  db: Db,
  tenantId: string,
  f: ReportFilters,
): Promise<ExpenseReportRow[]> {
  const conds = [
    eq(expenses.tenantId, tenantId),
    isNull(expenses.deletedAt),
    sql`${expenses.approvalStatus} != 'rejected'`,
  ]

  if (f.dateFrom) conds.push(gte(expenses.expenseDate, f.dateFrom))
  if (f.dateTo) conds.push(lte(expenses.expenseDate, f.dateTo))
  if (f.category) conds.push(eq(expenses.expenseCategory, f.category))
  if (f.status) conds.push(eq(expenses.status, f.status))
  if (f.vendor) {
    conds.push(sql`lower(${expenses.vendorName}) LIKE ${'%' + f.vendor.toLowerCase() + '%'}`)
  }

  const rows = await db
    .select()
    .from(expenses)
    .where(and(...conds))
    .orderBy(desc(expenses.expenseDate), desc(expenses.createdAt))

  return rows.map((r) => {
    const totalRaw = r.amount ?? null
    const total =
      totalRaw != null
        ? String(parseFloat(totalRaw) * r.businessPercent / 100)
        : null
    const vatRaw = r.vatAmount ?? null
    const vat =
      vatRaw != null && totalRaw != null
        ? String(parseFloat(vatRaw) * r.businessPercent / 100)
        : vatRaw
    let net: string | null = null
    if (total !== null && vat !== null) {
      net = String(parseFloat(total) - parseFloat(vat))
    }
    return {
      date: r.expenseDate ?? null,
      vendor: r.vendorName ?? null,
      invoiceNumber: r.invoiceNumber ?? null,
      vendorTaxId: r.vendorTaxId ?? null,
      total,
      vat,
      net,
      category: (r.expenseCategory ?? null) as ExpenseCategoryId | null,
      deductionPct: r.deductionPct ?? null,
      allocationNumber: r.allocationNumber ?? null,
      notes: r.notes ?? null,
    }
  })
}

export async function vatSummaryPcn874(
  db: Db,
  tenantId: string,
  period: { from: string; to: string },
): Promise<VatSummary> {
  const approvalFilter = inArray(expenses.approvalStatus, ['approved', 'not_required'])

  // NULL deduction_pct treated as 100% deductible (matches income-tax COALESCE convention)
  const [inputVatResult] = await db
    .select({
      sum: sql<string>`COALESCE(SUM(${expenses.vatAmount}::numeric * ${expenses.businessPercent}::numeric / 100), 0)`,
    })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        isNull(expenses.deletedAt),
        approvalFilter,
        eq(expenses.vatDeductible, true),
        sql`COALESCE(${expenses.deductionPct}, 100) = 100`,
        eq(expenses.isPerDiem, false),
        eq(expenses.status, 'COMPLETED'),
        gte(expenses.expenseDate, period.from),
        lte(expenses.expenseDate, period.to),
      ),
    )

  const [partialVatResult] = await db
    .select({
      sum: sql<string>`COALESCE(SUM(${expenses.vatAmount}::numeric * ${expenses.businessPercent}::numeric / 100 * ${expenses.deductionPct}::numeric / 100), 0)`,
    })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        isNull(expenses.deletedAt),
        approvalFilter,
        eq(expenses.vatDeductible, true),
        sql`${expenses.deductionPct} > 0`,
        sql`${expenses.deductionPct} < 100`,
        eq(expenses.isPerDiem, false),
        eq(expenses.status, 'COMPLETED'),
        gte(expenses.expenseDate, period.from),
        lte(expenses.expenseDate, period.to),
      ),
    )

  const [outputVatResult] = await db
    .select({
      sum: sql<string>`COALESCE(SUM(${invoices.vatAmount}::numeric), 0)`,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.taxIssueDate} BETWEEN ${period.from} AND ${period.to}`,
        notInArray(invoices.status, ['DRAFT', 'SENT', 'VOID', 'BAD_DEBT']),
        sql`${invoices.source} != 'credit_note'`,
      ),
    )

  const outputVat = outputVatResult?.sum ?? '0'
  const inputVat = inputVatResult?.sum ?? '0'
  const partialInputVat = partialVatResult?.sum ?? '0'
  const netVatDue = String(
    parseFloat(outputVat) - parseFloat(inputVat) - parseFloat(partialInputVat),
  )

  return {
    period: `${period.from}–${period.to}`,
    inputVat: String(parseFloat(inputVat)),
    partialInputVat: String(parseFloat(partialInputVat)),
    outputVat,
    netVatDue,
  }
}

export async function vendorAnalysis(
  db: Db,
  tenantId: string,
  range: { from: string; to: string },
): Promise<VendorAnalysisRow[]> {
  const rows = await db
    .select({
      vendor: sql<string>`COALESCE(lower(trim(${expenses.vendorName})), 'unknown')`,
      vendorId: sql<string | null>`null::text`,
      totalAmount: sql<string>`COALESCE(SUM(${expenses.amount}::numeric * ${expenses.businessPercent}::numeric / 100), 0)::text`,
      totalVat: sql<string>`COALESCE(SUM(${expenses.vatAmount}::numeric * ${expenses.businessPercent}::numeric / 100), 0)::text`,
    })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        isNull(expenses.deletedAt),
        gte(expenses.expenseDate, range.from),
        lte(expenses.expenseDate, range.to),
      ),
    )
    .groupBy(sql`COALESCE(lower(trim(${expenses.vendorName})), 'unknown')`)
    .orderBy(desc(sql`SUM(${expenses.amount}::numeric * ${expenses.businessPercent}::numeric / 100)`))

  return rows.map((r) => ({
    vendor: r.vendor,
    vendorId: r.vendorId,
    totalAmount: r.totalAmount,
    totalVat: r.totalVat,
  }))
}

export async function expenseReportDetail(
  db: Db,
  tenantId: string,
  filters: ExpenseReportsUiFilters,
): Promise<ExpenseReportDetail> {
  const conds = [
    eq(expenses.tenantId, tenantId),
    isNull(expenses.deletedAt),
    sql`${expenses.approvalStatus} != 'rejected'`,
    gte(expenses.expenseDate, filters.from),
    lte(expenses.expenseDate, filters.to),
  ]

  if (filters.category) conds.push(eq(expenses.expenseCategory, filters.category))
  if (filters.projectId) conds.push(eq(expenses.projectId, filters.projectId))
  if (filters.userId) conds.push(eq(expenses.createdBy, filters.userId))

  const rows = await db
    .select({
      id: expenses.id,
      expenseDate: expenses.expenseDate,
      rawVendorName: expenses.vendorName,
      canonicalVendorName: vendors.name,
      expenseCategory: expenses.expenseCategory,
      amount: expenses.amount,
      deductionPct: expenses.deductionPct,
      vatAmount: expenses.vatAmount,
      vatDeductible: expenses.vatDeductible,
      isPerDiem: expenses.isPerDiem,
      createdBy: expenses.createdBy,
      createdByName: users.name,
      createdByEmail: users.email,
    })
    .from(expenses)
    .leftJoin(users, eq(users.id, expenses.createdBy))
    .leftJoin(vendors, eq(vendors.id, expenses.vendorId))
    .where(and(...conds))
    .orderBy(desc(expenses.expenseDate), desc(expenses.createdAt))

  const normalizedRows: ExpenseReportDetailRow[] = rows
    .filter((row): row is typeof row & { expenseDate: string } => row.expenseDate !== null)
    .map((row) => {
      const amount = row.amount != null ? Number(row.amount) : 0
      const deductionPct = row.deductionPct ?? null
      const vatAmount = row.vatAmount != null ? Number(row.vatAmount) : null
      return {
        id: row.id,
        expenseDate: row.expenseDate,
        vendorName: row.canonicalVendorName ?? row.rawVendorName ?? null,
        expenseCategory: (row.expenseCategory ?? null) as ExpenseCategoryId | null,
        amount,
        deductionPct,
        vatAmount,
        vatDeductible: row.vatDeductible,
        isPerDiem: row.isPerDiem,
        createdBy: row.createdBy,
        createdByName: row.createdByName ?? row.createdByEmail ?? row.createdBy,
      }
    })

  const totalAmount = normalizedRows.reduce((sum, row) => sum + row.amount, 0)
  const totalVat = normalizedRows.reduce((sum, row) => sum + (row.vatAmount ?? 0), 0)
  const deductibleTotal = normalizedRows.reduce(
    (sum, row) => sum + row.amount * ((row.deductionPct ?? 0) / 100),
    0,
  )

  return { rows: normalizedRows, totalAmount, totalVat, deductibleTotal }
}

export async function vatSummaryPcn874Ui(
  db: Db,
  tenantId: string,
  period: { from: string; to: string },
): Promise<VatSummaryUi> {
  const [settingsRow] = await db
    .select({ vatPeriod: tenantSettings.vatPeriod })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))
    .limit(1)

  const [salesResult] = await db
    .select({
      amount: sql<string>`COALESCE(SUM(${invoices.subtotal}), 0)::text`,
      vat: sql<string>`COALESCE(SUM(${invoices.vatAmount}), 0)::text`,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        sql`${invoices.taxIssueDate} BETWEEN ${period.from} AND ${period.to}`,
        notInArray(invoices.status, ['DRAFT', 'SENT', 'VOID', 'BAD_DEBT']),
        sql`${invoices.source} != 'credit_note'`,
      ),
    )

  const [inputResult] = await db
    .select({
      amount: sql<string>`COALESCE(SUM(CASE WHEN ${expenses.deductionPct} IS NULL THEN ${expenses.amount} ELSE ${expenses.amount} * ${expenses.deductionPct}::numeric / 100 END), 0)::text`,
      vat: sql<string>`COALESCE(SUM(CASE WHEN ${expenses.deductionPct} IS NULL THEN ${expenses.vatAmount} ELSE ${expenses.vatAmount} * ${expenses.deductionPct}::numeric / 100 END), 0)::text`,
    })
    .from(expenses)
    .where(
      and(
        eq(expenses.tenantId, tenantId),
        isNull(expenses.deletedAt),
        inArray(expenses.approvalStatus, ['approved', 'not_required']),
        eq(expenses.vatDeductible, true),
        eq(expenses.isPerDiem, false),
        eq(expenses.status, 'COMPLETED'),
        gte(expenses.expenseDate, period.from),
        lte(expenses.expenseDate, period.to),
      ),
    )

  const line220Amount = Number(salesResult?.amount ?? '0')
  const line220Vat = Number(salesResult?.vat ?? '0')
  const line320Amount = Number(inputResult?.amount ?? '0')
  const line320Vat = Number(inputResult?.vat ?? '0')

  return {
    periodLabel: `${period.from}–${period.to}`,
    filingCadence: (settingsRow?.vatPeriod ?? 'bimonthly') as 'monthly' | 'bimonthly',
    lines: [
      { code: '220', descriptionHe: 'עסקאות חייבות במס', amount: line220Amount, vat: line220Vat },
      { code: '225', descriptionHe: 'עסקאות בשיעור אפס', amount: 0, vat: 0 },
      { code: '320', descriptionHe: 'תשומות חייבות במס', amount: line320Amount, vat: line320Vat },
    ],
    vatToPay: line220Vat - line320Vat,
  }
}

export async function vendorAnalysisUi(
  db: Db,
  tenantId: string,
  filters: ExpenseReportsUiFilters,
): Promise<VendorAnalysisUiRow[]> {
  const conds = [
    eq(expenses.tenantId, tenantId),
    isNull(expenses.deletedAt),
    gte(expenses.expenseDate, filters.from),
    lte(expenses.expenseDate, filters.to),
  ]

  if (filters.category) conds.push(eq(expenses.expenseCategory, filters.category))
  if (filters.projectId) conds.push(eq(expenses.projectId, filters.projectId))
  if (filters.userId) conds.push(eq(expenses.createdBy, filters.userId))

  const rows = await db
    .select({
      vendorId: expenses.vendorId,
      vendorName: sql<string>`COALESCE(${vendors.name}, NULLIF(trim(${expenses.vendorName}), ''), 'Unknown vendor')`,
      count: sql<number>`COUNT(*)::int`,
      total: sql<string>`COALESCE(SUM(${expenses.amount}), 0)::text`,
      avgDeductionPct: sql<string>`COALESCE(AVG(COALESCE(${expenses.deductionPct}, 100)), 0)::text`,
      vatTotal: sql<string>`COALESCE(SUM(${expenses.vatAmount}), 0)::text`,
    })
    .from(expenses)
    .leftJoin(vendors, eq(vendors.id, expenses.vendorId))
    .where(and(...conds))
    .groupBy(expenses.vendorId, vendors.name, expenses.vendorName)
    .orderBy(desc(sql`SUM(${expenses.amount})`))

  return rows.map((row) => ({
    vendorId: row.vendorId,
    vendorName: row.vendorName,
    count: row.count,
    total: Number(row.total),
    avgDeductionPct: Number(row.avgDeductionPct),
    vatTotal: Number(row.vatTotal),
  }))
}