/**
 * bad_debt_vat_reclaims — bad-debt-writeoff (wave-12).
 *
 * Tracks VAT reclaim progress for written-off invoices (חוב אבוד).
 * Under Israeli law, a creditor may reclaim the VAT remitted to the ITA
 * after formally notifying the debtor and the ITA.
 *
 * DB conventions:
 * - UUID PK .defaultRandom()
 * - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 * - Money: NUMERIC(12,2)
 * - Enums: text() + check(... IN (...)) — NEVER pgEnum
 * - Expression/GIN/partial indexes → raw SQL migration ONLY (never drizzle index())
 */
import { pgTable, uuid, text, numeric, date, timestamp, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { invoices } from './invoices'

export const badDebtVatReclaims = pgTable(
  'bad_debt_vat_reclaims',
  {
    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' }),
    // VAT amount reclaimable = vat_amount * (total - amount_paid) / total
    vatAmount: numeric('vat_amount', { precision: 12, scale: 2 }).notNull(),
    status: text('status').notNull().default('pending'),
    registeredLetterSentAt: date('registered_letter_sent_at'),
    itaSubmissionDate: date('ita_submission_date'),
    itaReference: text('ita_reference'),
    resolvedAt: timestamp('resolved_at', { withTimezone: true }),
    reversedAt: timestamp('reversed_at', { withTimezone: true }),
    notes: text('notes'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    // GIN/expression/partial indexes are in raw SQL migration — see 0017_bad_debt_writeoff.sql
    statusCheck: check(
      'bad_debt_vat_reclaims_status_check',
      sql`${t.status} IN ('pending','submitted','approved','rejected','reversed','cancelled')`,
    ),
  }),
)

export type BadDebtVatReclaimRow = typeof badDebtVatReclaims.$inferSelect
export type NewBadDebtVatReclaim = typeof badDebtVatReclaims.$inferInsert