/**
 * Receipts schema — invoice-receipt-document (wave-12, spec 179).
 *
 * Tables:
 * - receipts:              issued receipt / combined invoice-receipt documents
 * - receipt_payment_lines: payment method breakdown for each receipt
 * - receipt_sequences:     gapless sequential number state per (tenant, doc_type)
 *
 * Israeli tax law compliance:
 *   קבלה (receipt)                   → standalone receipt doc_type='receipt'
 *   חשבונית מס/קבלה (invoice_receipt) → combined invoice+receipt doc_type='invoice_receipt'
 * Both number sequences must be gap-free, sequential, per-tenant.
 *
 * 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
 * - Expression/GIN/partial indexes → migration raw SQL only
 */
import { pgTable, uuid, text, numeric, integer, timestamp, date, check, index, primaryKey } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { customers } from './customers'
import { users } from './users'
import { invoices } from './invoices'
import { invoicePayments } from './invoice-payments'

// ── receipts ──────────────────────────────────────────────────────────────────

export const receipts = pgTable(
  'receipts',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    customerId: uuid('customer_id')
      .notNull()
      .references(() => customers.id, { onDelete: 'restrict' }),
    docType: text('doc_type').notNull(),
    receiptNumber: text('receipt_number'),
    status: text('status').notNull().default('DRAFT'),
    invoiceId: uuid('invoice_id').references(() => invoices.id, { onDelete: 'restrict' }),
    currency: text('currency').notNull().default('ILS'),
    amount: numeric('amount', { precision: 12, scale: 2 }).notNull(),
    ilsExchangeRate: numeric('ils_exchange_rate', { precision: 10, scale: 4 }),
    amountIls: numeric('amount_ils', { precision: 12, scale: 2 }),
    issuedAt: timestamp('issued_at', { withTimezone: true }),
    issuedBy: uuid('issued_by').references(() => users.id, { onDelete: 'set null' }),
    pdfR2Key: text('pdf_r2_key'),
    voidReason: text('void_reason'),
    voidedAt: timestamp('voided_at', { withTimezone: true }),
    voidedBy: uuid('voided_by').references(() => users.id, { onDelete: 'set null' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantStatusIdx: index('idx_receipts_tenant').on(t.tenantId, t.status, t.issuedAt.desc()),
    invoiceIdx: index('idx_receipts_invoice').on(t.invoiceId),
    docTypeCheck: check(
      'receipts_doc_type_check',
      sql`${t.docType} IN ('receipt','invoice_receipt')`,
    ),
    statusCheck: check(
      'receipts_status_check',
      sql`${t.status} IN ('DRAFT','ISSUED','VOIDED')`,
    ),
  }),
)

export type ReceiptRow = typeof receipts.$inferSelect
export type NewReceipt = typeof receipts.$inferInsert

// ── receipt_payment_lines ─────────────────────────────────────────────────────

export const receiptPaymentLines = pgTable(
  'receipt_payment_lines',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    receiptId: uuid('receipt_id')
      .notNull()
      .references(() => receipts.id, { onDelete: 'cascade' }),
    method: text('method').notNull(),
    amount: numeric('amount', { precision: 12, scale: 2 }).notNull(),
    chequeNumber: text('cheque_number'),
    chequeBank: text('cheque_bank'),
    chequeBranch: text('cheque_branch'),
    chequeAccount: text('cheque_account'),
    chequeDueDate: date('cheque_due_date'),
    cardLastFour: text('card_last_four'),
    cardBrand: text('card_brand'),
    reference: text('reference'),
    invoicePaymentId: uuid('invoice_payment_id').references(() => invoicePayments.id, { onDelete: 'set null' }),
  },
  (t) => ({
    methodCheck: check(
      'receipt_payment_lines_method_check',
      sql`${t.method} IN ('cash','bank_transfer','cheque','credit_card','other')`,
    ),
  }),
)

export type ReceiptPaymentLineRow = typeof receiptPaymentLines.$inferSelect
export type NewReceiptPaymentLine = typeof receiptPaymentLines.$inferInsert

// ── receipt_sequences ─────────────────────────────────────────────────────────

export const receiptSequences = pgTable(
  'receipt_sequences',
  {
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    docType: text('doc_type').notNull(),
    prefix: text('prefix').notNull().default(''),
    nextNumber: integer('next_number').notNull().default(1),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.tenantId, t.docType] }),
    docTypeCheck: check(
      'receipt_sequences_doc_type_check',
      sql`${t.docType} IN ('receipt','invoice_receipt')`,
    ),
  }),
)

export type ReceiptSequenceRow = typeof receiptSequences.$inferSelect
export type NewReceiptSequence = typeof receiptSequences.$inferInsert