/**
 * unmatched_payments — payment-reconciliation (wave-12).
 *
 * Staging area for bank-received payments not yet matched to an invoice.
 * Rows enter from two sources:
 *   (a) Staff manually via RecordPaymentSheet (unmatched mode)
 *   (b) bank-statement-import (spec 167) via "Send to reconcile"
 *
 * DB conventions:
 * - UUID PK .defaultRandom()
 * - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 * - Money: NUMERIC(12,2) — matches sibling invoice_payments money columns
 * - Enums: text() + check(... IN (...)) — NEVER pgEnum
 * - Partial index (WHERE matched_to_invoice_id IS NULL) in raw SQL migration only
 */
import { pgTable, uuid, text, numeric, date, 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 unmatchedPayments = pgTable(
  'unmatched_payments',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    amount: numeric('amount', { precision: 12, scale: 2 }).notNull(),
    currency: text('currency').notNull().default('ILS'),
    paidAt: date('paid_at').notNull(),
    paymentMethod: text('payment_method').notNull().default('bank_transfer'),
    reference: text('reference'),
    notes: text('notes'),
    payerName: text('payer_name'),
    matchedToInvoiceId: uuid('matched_to_invoice_id').references(() => invoices.id, {
      onDelete: 'set null',
    }),
    matchedAt: timestamp('matched_at', { withTimezone: true }),
    matchedBy: uuid('matched_by').references(() => users.id, { onDelete: 'set null' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantPaidAtIdx: index('idx_unmatched_payments_tenant').on(t.tenantId, t.paidAt),
    amountCheck: check(
      'unmatched_payments_amount_check',
      sql`${t.amount} > 0`,
    ),
    currencyCheck: check(
      'unmatched_payments_currency_check',
      sql`${t.currency} IN ('ILS','USD','EUR')`,
    ),
    paymentMethodCheck: check(
      'unmatched_payments_payment_method_check',
      sql`${t.paymentMethod} IN ('bank_transfer','credit_card','check','cash','other')`,
    ),
  }),
)

export type UnmatchedPaymentRow = typeof unmatchedPayments.$inferSelect
export type NewUnmatchedPayment = typeof unmatchedPayments.$inferInsert

export interface UnmatchedPaymentObject {
  id: string
  tenantId: string
  amount: string
  currency: 'ILS' | 'USD' | 'EUR'
  paidAt: string
  paymentMethod: 'bank_transfer' | 'credit_card' | 'check' | 'cash' | 'other'
  reference: string | null
  notes: string | null
  payerName: string | null
  matchedToInvoiceId: string | null
  matchedAt: string | null
  matchedBy: string | null
  createdAt: string
}