/**
 * invoice_payments — partial-payment-recording (wave-11).
 *
 * Records individual payment entries against an invoice.
 * The invoices.amount_paid and invoices.overpayment_amount denorm columns
 * are kept in sync transactionally by recordInvoicePayment / reverseInvoicePayment.
 *
 * DB conventions:
 * - UUID PK .defaultRandom()
 * - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 * - Money: NUMERIC(12,2) — matches existing invoices money columns
 * - Enums: text() + check(... IN (...)) — NEVER pgEnum
 * - Plain btree indexes via drizzle index()
 */
import { pgTable, uuid, text, numeric, timestamp, index, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { invoices } from './invoices'
import { users } from './users'

export const invoicePayments = pgTable(
  'invoice_payments',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    invoiceId: uuid('invoice_id')
      .notNull()
      .references(() => invoices.id, { onDelete: 'cascade' }),
    amount: numeric('amount', { precision: 12, scale: 2 }).notNull(),
    currency: text('currency').notNull().default('ILS'),
    paidAt: timestamp('paid_at', { withTimezone: true }).notNull(),
    source: text('source').notNull().default('manual'),
    reference: text('reference'),
    recordedBy: uuid('recorded_by').references(() => users.id, { onDelete: 'set null' }),
    note: text('note'),
    // FK invoice_payments_receipt_id_fkey added in migration 0026 (spec 179).
    // Circular reference receipts↔invoice_payments prevents .references() here;
    // constraint lives in raw migration SQL only.
    receiptId: uuid('receipt_id'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    invoiceIdx: index('idx_invoice_payments_invoice').on(t.invoiceId),
    tenantPaidAtIdx: index('idx_invoice_payments_tenant').on(t.tenantId, t.paidAt.desc()),
    // financial-statements (wave-13): covering index for Cash Flow report queries
    reportIdx: index('idx_invoice_payments_rpt').on(t.tenantId, t.paidAt),
    amountCheck: check(
      'invoice_payments_amount_check',
      sql`${t.amount} > 0`,
    ),
    sourceCheck: check(
      'invoice_payments_source_check',
      sql`${t.source} IN ('manual','gateway','bank_transfer','auto_billing')`,
    ),
  }),
)

export type InvoicePaymentRow = typeof invoicePayments.$inferSelect
export type NewInvoicePayment = typeof invoicePayments.$inferInsert