/**
 * AR Aging Report query helpers — ar-aging-report (wave-12).
 *
 * Calculates outstanding invoice balances grouped into aging buckets per customer.
 * Buckets are relative to a given `asOf` date:
 *   current  — due_date >= asOf (not yet overdue)
 *   d1_30    — 1–30 days overdue
 *   d31_60   — 31–60 days overdue
 *   d61_90   — 61–90 days overdue
 *   d90plus  — 91+ days overdue
 *
 * Only outstanding invoices (status IN ('SENT','APPROVED','TAX_ISSUED','PARTIALLY_PAID'))
 * in the given currency are included.
 * Balance due = total - amount_paid.
 */
import { and, eq, inArray } from 'drizzle-orm'
import type { Db } from '../client'
import { invoices } from '../schema/invoices'
import { customers } from '../schema/customers'
import { tenants } from '../schema/tenants'
import type {
  ArAgingBucket,
  ArAgingCustomerRow,
  ArAgingSummary,
  ArAgingReport,
} from '@zync/types'

// ── Types ──────────────────────────────────────────────────────────────────────

export type { ArAgingBucket, ArAgingCustomerRow, ArAgingSummary, ArAgingReport }

// ── Helpers ────────────────────────────────────────────────────────────────────

const OUTSTANDING_STATUSES = ['SENT', 'APPROVED', 'TAX_ISSUED', 'PARTIALLY_PAID'] as const

/**
 * Assign an invoice to an aging bucket based on (asOf - due_date) in days.
 * Null due_date is treated as current (not overdue).
 */
export function assignAgingBucket(
  dueDateStr: string | null,
  asOfStr: string,
): 'current' | 'd1_30' | 'd31_60' | 'd61_90' | 'd90plus' {
  if (!dueDateStr) return 'current'
  const asOf = new Date(asOfStr)
  const due = new Date(dueDateStr)
  // Normalize to date-only (strip time component)
  asOf.setHours(0, 0, 0, 0)
  due.setHours(0, 0, 0, 0)
  const diffDays = Math.floor((asOf.getTime() - due.getTime()) / (1000 * 60 * 60 * 24))
  if (diffDays <= 0) return 'current'
  if (diffDays <= 30) return 'd1_30'
  if (diffDays <= 60) return 'd31_60'
  if (diffDays <= 90) return 'd61_90'
  return 'd90plus'
}

function emptyBucket(): ArAgingBucket {
  return { amount: '0.00', count: 0 }
}

function addToBucket(bucket: ArAgingBucket, amount: number): ArAgingBucket {
  return {
    amount: (parseFloat(bucket.amount) + amount).toFixed(2),
    count: bucket.count + 1,
  }
}

function addBuckets(a: ArAgingBucket, b: ArAgingBucket): ArAgingBucket {
  return {
    amount: (parseFloat(a.amount) + parseFloat(b.amount)).toFixed(2),
    count: a.count + b.count,
  }
}

// ── buildArAgingReport ────────────────────────────────────────────────────────

export async function buildArAgingReport(
  db: Db,
  tenantId: string,
  opts: { asOf?: string; currency?: string },
): Promise<ArAgingReport> {
  // Resolve asOf and currency from tenant defaults if not provided
  let asOf = opts.asOf
  let currency = opts.currency

  if (!asOf || !currency) {
    const [tenant] = await db
      .select({ defaultCurrency: tenants.defaultCurrency, defaultTimezone: tenants.defaultTimezone })
      .from(tenants)
      .where(eq(tenants.id, tenantId))
      .limit(1)

    if (!asOf) {
      // Use today's date in UTC (tenant timezone awareness would require full date-fns-tz)
      asOf = new Date().toISOString().slice(0, 10)
    }
    if (!currency && tenant) {
      currency = tenant.defaultCurrency
    }
    if (!currency) {
      currency = 'ILS'
    }
  }

  // Fetch all outstanding invoices for the tenant in the given currency
  const rows = await db
    .select({
      id: invoices.id,
      customerId: invoices.customerId,
      dueDate: invoices.dueDate,
      total: invoices.total,
      amountPaid: invoices.amountPaid,
      status: invoices.status,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        eq(invoices.currency, currency),
        inArray(invoices.status, [...OUTSTANDING_STATUSES]),
        // Only include payments up to asOf: exclude invoices fully paid before asOf.
        // Since we don't have per-payment date filtering here, we use amount_paid as-is.
        // For a true historical snapshot we would join invoice_payments with paidAt <= asOf;
        // that is handled below in the historical path.
      ),
    )

  // Build a customer ID set for lookup
  const customerIds = [...new Set(rows.map((r) => r.customerId).filter(Boolean) as string[])]

  // Fetch customer info
  let customerMap: Map<string, { name: string; email: string | null }> = new Map()
  if (customerIds.length > 0) {
    const customerRows = await db
      .select({ id: customers.id, name: customers.name, email: customers.email })
      .from(customers)
      .where(
        and(
          eq(customers.tenantId, tenantId),
          inArray(customers.id, customerIds),
        ),
      )
    for (const c of customerRows) {
      customerMap.set(c.id, { name: c.name, email: c.email ?? null })
    }
  }

  // Aggregate per-customer buckets
  type CustomerAgg = {
    customerId: string
    customerName: string
    customerEmail: string | null
    current: ArAgingBucket
    d1_30: ArAgingBucket
    d31_60: ArAgingBucket
    d61_90: ArAgingBucket
    d90plus: ArAgingBucket
  }

  const aggMap = new Map<string, CustomerAgg>()

  for (const row of rows) {
    const custId = row.customerId ?? '__unknown__'
    const info = customerMap.get(custId) ?? { name: 'Unknown Customer', email: null }

    const balanceDue = Math.max(
      0,
      parseFloat(row.total ?? '0') - parseFloat(row.amountPaid ?? '0'),
    )
    if (balanceDue <= 0) continue

    const bucket = assignAgingBucket(row.dueDate, asOf)

    if (!aggMap.has(custId)) {
      aggMap.set(custId, {
        customerId: custId,
        customerName: info.name,
        customerEmail: info.email,
        current: emptyBucket(),
        d1_30: emptyBucket(),
        d31_60: emptyBucket(),
        d61_90: emptyBucket(),
        d90plus: emptyBucket(),
      })
    }

    const agg = aggMap.get(custId)!
    agg[bucket] = addToBucket(agg[bucket], balanceDue)
  }

  // Build customer rows sorted by total descending
  const customerRows: ArAgingCustomerRow[] = Array.from(aggMap.values()).map((agg) => {
    const total = (
      parseFloat(agg.current.amount) +
      parseFloat(agg.d1_30.amount) +
      parseFloat(agg.d31_60.amount) +
      parseFloat(agg.d61_90.amount) +
      parseFloat(agg.d90plus.amount)
    ).toFixed(2)
    return { ...agg, total }
  }).sort((a, b) => parseFloat(b.total) - parseFloat(a.total))

  // Build summary
  const summary: ArAgingSummary = {
    current: emptyBucket(),
    d1_30: emptyBucket(),
    d31_60: emptyBucket(),
    d61_90: emptyBucket(),
    d90plus: emptyBucket(),
    total: '0.00',
  }
  for (const row of customerRows) {
    summary.current = addBuckets(summary.current, row.current)
    summary.d1_30 = addBuckets(summary.d1_30, row.d1_30)
    summary.d31_60 = addBuckets(summary.d31_60, row.d31_60)
    summary.d61_90 = addBuckets(summary.d61_90, row.d61_90)
    summary.d90plus = addBuckets(summary.d90plus, row.d90plus)
  }
  summary.total = (
    parseFloat(summary.current.amount) +
    parseFloat(summary.d1_30.amount) +
    parseFloat(summary.d31_60.amount) +
    parseFloat(summary.d61_90.amount) +
    parseFloat(summary.d90plus.amount)
  ).toFixed(2)

  return {
    asOf,
    currency,
    summary,
    customers: customerRows,
  }
}

// ── Customer Statement data ────────────────────────────────────────────────────

export interface ArAgingStatementInvoice {
  id: string
  number: string | null
  issueDate: string | null
  dueDate: string | null
  total: string
  amountPaid: string
  balance: string
  status: string
  ageDays: number
}

export interface ArAgingCustomerStatementData {
  customerId: string
  customerName: string
  customerEmail: string | null
  asOf: string
  currency: string
  invoices: ArAgingStatementInvoice[]
  totalBalance: string
}

export async function buildArAgingCustomerStatement(
  db: Db,
  tenantId: string,
  customerId: string,
  asOf: string,
  currency: string,
): Promise<ArAgingCustomerStatementData | null> {
  // Verify customer belongs to tenant
  const [customer] = await db
    .select({ id: customers.id, name: customers.name, email: customers.email })
    .from(customers)
    .where(and(eq(customers.tenantId, tenantId), eq(customers.id, customerId)))
    .limit(1)

  if (!customer) return null

  const rows = await db
    .select({
      id: invoices.id,
      invoiceNumber: invoices.invoiceNumber,
      proformaNumber: invoices.proformaNumber,
      issueDate: invoices.issueDate,
      dueDate: invoices.dueDate,
      total: invoices.total,
      amountPaid: invoices.amountPaid,
      status: invoices.status,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        eq(invoices.customerId, customerId),
        eq(invoices.currency, currency),
        inArray(invoices.status, [...OUTSTANDING_STATUSES]),
      ),
    )

  const asOfDate = new Date(asOf)
  asOfDate.setHours(0, 0, 0, 0)

  const stmtInvoices: ArAgingStatementInvoice[] = rows.map((row) => {
    const balance = Math.max(0, parseFloat(row.total ?? '0') - parseFloat(row.amountPaid ?? '0'))
    const dueDate = row.dueDate ? new Date(row.dueDate) : null
    if (dueDate) dueDate.setHours(0, 0, 0, 0)
    const ageDays = dueDate
      ? Math.max(0, Math.floor((asOfDate.getTime() - dueDate.getTime()) / (1000 * 60 * 60 * 24)))
      : 0
    return {
      id: row.id,
      number: row.invoiceNumber ?? row.proformaNumber ?? null,
      issueDate: row.issueDate ?? null,
      dueDate: row.dueDate ?? null,
      total: (parseFloat(row.total ?? '0')).toFixed(2),
      amountPaid: (parseFloat(row.amountPaid ?? '0')).toFixed(2),
      balance: balance.toFixed(2),
      status: row.status,
      ageDays,
    }
  })

  const totalBalance = stmtInvoices
    .reduce((s, i) => s + parseFloat(i.balance), 0)
    .toFixed(2)

  return {
    customerId: customer.id,
    customerName: customer.name,
    customerEmail: customer.email ?? null,
    asOf,
    currency,
    invoices: stmtInvoices,
    totalBalance,
  }
}