/**
 * Customer Statement assembler — customer-statement (wave-16, spec 183).
 *
 * Builds a full statement of account for a customer over a date range.
 * Groups rows by currency with opening/closing balances and an aging summary.
 *
 * Architecture: read-only projection — no new tables.
 * - Debit rows: invoices (source != 'credit_note'), status in TAX_ISSUED/PARTIALLY_PAID/PAID
 * - Credit-note rows: invoices (source = 'credit_note'), same status filter
 * - Payment rows: invoice_payments joined to invoices for customer/currency scoping,
 *   LEFT JOIN receipts for document label metadata
 */
import { and, eq, inArray, ne, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { invoices } from '../schema/invoices'
import { invoicePayments } from '../schema/invoice-payments'
import { receipts } from '../schema/receipts'
import { customers, customerContacts } from '../schema/customers'
import { tenantSettings } from '../schema/tenants'
import type { CustomerStatement, StatementCurrencyGroup, StatementRow } from '@zync/types'
import { computeStatementAging } from './statement-aging'
import type { OpenInvoiceForAging } from './statement-aging'

export interface BuildStatementInput {
  tenantId: string
  customerId: string
  from: string
  to: string
  currency?: string
}

const DEBIT_STATUSES = ['TAX_ISSUED', 'PARTIALLY_PAID', 'PAID'] as const
const OUTSTANDING_STATUSES_FOR_AGING = ['SENT', 'APPROVED', 'TAX_ISSUED', 'PARTIALLY_PAID'] as const

export async function buildCustomerStatement(
  db: Db,
  input: BuildStatementInput,
): Promise<CustomerStatement | null> {
  const { tenantId, customerId, from, to, currency } = input

  // Verify customer belongs to tenant
  const [customer] = await db
    .select({ id: customers.id, name: customers.name })
    .from(customers)
    .where(and(eq(customers.tenantId, tenantId), eq(customers.id, customerId)))
    .limit(1)

  if (!customer) return null

  // Get defaultPaymentTermsDays for aging calculation
  const [settings] = await db
    .select({ defaultPaymentTermsDays: tenantSettings.defaultPaymentTermsDays })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))
    .limit(1)
  const defaultPaymentTermsDays = settings?.defaultPaymentTermsDays ?? 30

  // ── Fetch invoice rows (debits + credit notes) ───────────────────────────────
  const invoiceWhere = and(
    eq(invoices.tenantId, tenantId),
    eq(invoices.customerId, customerId),
    inArray(invoices.status, [...DEBIT_STATUSES]),
    ...(currency ? [eq(invoices.currency, currency)] : []),
  )

  // Rows in date range
  const invoiceRowsInRange = await db
    .select({
      id: invoices.id,
      source: invoices.source,
      currency: invoices.currency,
      taxIssueDate: invoices.taxIssueDate,
      issueDate: invoices.issueDate,
      total: invoices.total,
      amountPaid: invoices.amountPaid,
      status: invoices.status,
      dueDate: invoices.dueDate,
      sentAt: invoices.sentAt,
      invoiceNumber: invoices.invoiceNumber,
      proformaNumber: invoices.proformaNumber,
    })
    .from(invoices)
    .where(
      and(
        invoiceWhere,
        sql`COALESCE(${invoices.taxIssueDate}, ${invoices.issueDate}) >= ${from}`,
        sql`COALESCE(${invoices.taxIssueDate}, ${invoices.issueDate}) <= ${to}`,
      ),
    )

  // Rows before from (for opening balance)
  const invoiceRowsBefore = await db
    .select({
      id: invoices.id,
      source: invoices.source,
      currency: invoices.currency,
      total: invoices.total,
      amountPaid: invoices.amountPaid,
      status: invoices.status,
      dueDate: invoices.dueDate,
      sentAt: invoices.sentAt,
      taxIssueDate: invoices.taxIssueDate,
      issueDate: invoices.issueDate,
    })
    .from(invoices)
    .where(
      and(
        invoiceWhere,
        sql`COALESCE(${invoices.taxIssueDate}, ${invoices.issueDate}) < ${from}`,
      ),
    )

  // ── Fetch payment rows ──────────────────────────────────────────────────────
  // Join invoice_payments → invoices (for customer/currency) → receipts (for doc metadata)
  const paymentRowsInRange = await db
    .select({
      id: invoicePayments.id,
      amount: invoicePayments.amount,
      currency: invoicePayments.currency,
      paidAt: invoicePayments.paidAt,
      reference: invoicePayments.reference,
      receiptId: invoicePayments.receiptId,
      invoiceId: invoicePayments.invoiceId,
      receiptNumber: receipts.receiptNumber,
      receiptDocType: receipts.docType,
    })
    .from(invoicePayments)
    .innerJoin(invoices, eq(invoicePayments.invoiceId, invoices.id))
    .leftJoin(receipts, eq(invoicePayments.receiptId, receipts.id))
    .where(
      and(
        eq(invoicePayments.tenantId, tenantId),
        eq(invoices.customerId, customerId),
        ...(currency ? [eq(invoicePayments.currency, currency)] : []),
        sql`COALESCE(${invoicePayments.paidAt}, ${invoicePayments.createdAt}) >= ${from}::timestamptz`,
        sql`COALESCE(${invoicePayments.paidAt}, ${invoicePayments.createdAt}) <= ${to}::timestamptz + interval '1 day' - interval '1 second'`,
      ),
    )

  // Payment rows before from (for opening balance)
  const paymentRowsBefore = await db
    .select({
      id: invoicePayments.id,
      amount: invoicePayments.amount,
      currency: invoicePayments.currency,
    })
    .from(invoicePayments)
    .innerJoin(invoices, eq(invoicePayments.invoiceId, invoices.id))
    .where(
      and(
        eq(invoicePayments.tenantId, tenantId),
        eq(invoices.customerId, customerId),
        ...(currency ? [eq(invoicePayments.currency, currency)] : []),
        sql`COALESCE(${invoicePayments.paidAt}, ${invoicePayments.createdAt}) < ${from}::timestamptz`,
      ),
    )

  // ── Collect all currencies ──────────────────────────────────────────────────
  const allCurrencies = new Set<string>()
  for (const r of invoiceRowsInRange) allCurrencies.add(r.currency)
  for (const r of invoiceRowsBefore) allCurrencies.add(r.currency)
  for (const r of paymentRowsInRange) allCurrencies.add(r.currency)
  for (const r of paymentRowsBefore) allCurrencies.add(r.currency)

  // ── Open invoices as of `to` for aging ─────────────────────────────────────
  const openInvoicesForAging = await db
    .select({
      status: invoices.status,
      dueDate: invoices.dueDate,
      sentAt: invoices.sentAt,
      total: invoices.total,
      amountPaid: invoices.amountPaid,
    })
    .from(invoices)
    .where(
      and(
        eq(invoices.tenantId, tenantId),
        eq(invoices.customerId, customerId),
        inArray(invoices.status, [...OUTSTANDING_STATUSES_FOR_AGING]),
        ne(invoices.source, 'credit_note'),
        sql`COALESCE(${invoices.taxIssueDate}, ${invoices.issueDate}) <= ${to}`,
        ...(currency ? [eq(invoices.currency, currency)] : []),
      ),
    )

  const agingInvoices: OpenInvoiceForAging[] = openInvoicesForAging.map((r) => ({
    status: r.status,
    dueDate: r.dueDate ?? null,
    sentAt: r.sentAt ? r.sentAt.toISOString() : null,
    total: parseFloat(r.total ?? '0'),
    totalIls: null, // no totalIls column in schema
    amountPaid: parseFloat(r.amountPaid ?? '0'),
  }))

  const aging = computeStatementAging(agingInvoices, to, defaultPaymentTermsDays)

  // ── Build per-currency groups ───────────────────────────────────────────────
  const groups: StatementCurrencyGroup[] = []

  for (const curr of allCurrencies) {
    // Opening balance: debits - credits - payments before from
    let openingBalance = 0
    for (const r of invoiceRowsBefore) {
      if (r.currency !== curr) continue
      const total = parseFloat(r.total ?? '0')
      if (r.source === 'credit_note') {
        openingBalance -= Math.abs(total)
      } else {
        openingBalance += total
      }
    }
    for (const r of paymentRowsBefore) {
      if (r.currency !== curr) continue
      openingBalance -= parseFloat(r.amount ?? '0')
    }

    // Build rows for the period
    const rows: StatementRow[] = []

    for (const r of invoiceRowsInRange) {
      if (r.currency !== curr) continue
      const date = r.taxIssueDate ?? r.issueDate ?? to
      const total = parseFloat(r.total ?? '0')
      const docNum = r.invoiceNumber ?? r.proformaNumber ?? null

      if (r.source === 'credit_note') {
        rows.push({
          date,
          kind: 'credit_note',
          documentType: 'credit_note',
          documentNumber: docNum,
          reference: null,
          sourceId: r.id,
          debit: 0,
          credit: Math.abs(total),
          runningBalance: 0, // filled in later
        })
      } else {
        rows.push({
          date,
          kind: 'invoice',
          documentType: r.invoiceNumber ? 'tax_invoice' : 'proforma',
          documentNumber: docNum,
          reference: null,
          sourceId: r.id,
          debit: total,
          credit: 0,
          runningBalance: 0,
        })
      }
    }

    for (const r of paymentRowsInRange) {
      if (r.currency !== curr) continue
      const paidAt = r.paidAt
      const dateStr = paidAt instanceof Date
        ? paidAt.toISOString().slice(0, 10)
        : to

      rows.push({
        date: dateStr,
        kind: 'payment',
        documentType: r.receiptDocType ?? 'payment',
        documentNumber: r.receiptNumber ?? null,
        reference: r.reference ?? null,
        sourceId: r.id,
        debit: 0,
        credit: parseFloat(r.amount ?? '0'),
        runningBalance: 0,
      })
    }

    // Sort: date ASC, then kind (invoice before credit_note same-day), then documentNumber
    const kindOrder: Record<StatementRow['kind'], number> = { invoice: 0, credit_note: 1, payment: 2 }
    rows.sort((a, b) => {
      const dateDiff = a.date.localeCompare(b.date)
      if (dateDiff !== 0) return dateDiff
      const kindDiff = kindOrder[a.kind] - kindOrder[b.kind]
      if (kindDiff !== 0) return kindDiff
      return (a.documentNumber ?? '').localeCompare(b.documentNumber ?? '')
    })

    // Running balance
    let balance = openingBalance
    for (const row of rows) {
      balance += row.debit - row.credit
      row.runningBalance = balance
    }

    const closingBalance = balance

    groups.push({
      currency: curr,
      openingBalance,
      rows,
      closingBalance,
    })
  }

  // Sort groups: ILS first, then alphabetically
  groups.sort((a, b) => {
    if (a.currency === 'ILS') return -1
    if (b.currency === 'ILS') return 1
    return a.currency.localeCompare(b.currency)
  })

  return {
    customerId: customer.id,
    customerName: customer.name,
    from,
    to,
    groups,
    aging,
  }
}

/**
 * Resolve billing email for a customer:
 * 1. Primary contact email (customer_contacts WHERE is_primary = true)
 * 2. customers.email fallback
 * Returns null if no email is found.
 */
export async function getCustomerBillingEmail(
  db: Db,
  tenantId: string,
  customerId: string,
): Promise<string | null> {
  const [primaryContact] = await db
    .select({ email: customerContacts.email })
    .from(customerContacts)
    .where(
      and(
        eq(customerContacts.tenantId, tenantId),
        eq(customerContacts.customerId, customerId),
        eq(customerContacts.isPrimary, true),
      ),
    )
    .limit(1)

  if (primaryContact?.email) {
    return primaryContact.email
  }

  const [customerRow] = await db
    .select({ email: customers.email })
    .from(customers)
    .where(and(eq(customers.tenantId, tenantId), eq(customers.id, customerId)))
    .limit(1)

  return customerRow?.email ?? null
}