/**
 * Invoices module schema — invoices-core.
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 * - invoices:          core invoice records (two-stage: proforma → tax invoice)
 * - invoice_lines:     line items per invoice
 * - invoice_sequences: gapless sequential number state per (tenant, type)
 *
 * Israeli tax law compliance:
 *   חשבונית עסקה (proforma) → sent to customer at SENT state (proforma_number assigned)
 *   חשבונית מס  (tax invoice) → issued at TAX_ISSUED state (invoice_number assigned)
 * Both numbers must be gap-free, sequential, per-tenant.
 *
 * DB conventions:
 * - UUID PK .defaultRandom()
 * - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 * - Money: NUMERIC
 * - Enums: text() + check(... IN (...)) — NEVER pgEnum
 * - Expression/GIN/partial indexes → manifest.raw_ddl only
 * - FKs to tenants/users/customers/projects via .references()
 * - Self-referencing FK uses AnyPgColumn to avoid circular inference
 */
import {
  pgTable,
  uuid,
  text,
  numeric,
  integer,
  boolean,
  timestamp,
  date,
  index,
  check,
  uniqueIndex,
  primaryKey,
  type AnyPgColumn,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'
import { customers } from './customers'
import { projects } from './projects'
import { expenses } from './expenses'
import { recurringInvoiceTemplates } from './recurring-invoices'
import { leads } from './marketing'

// ── invoices ──────────────────────────────────────────────────────────────────

/**
 * Canonical InvoiceStatus values (10 states):
 * DRAFT | SENT | APPROVED | REJECTED | TAX_ISSUED | PAID | PARTIALLY_PAID | VOID | WRITTEN_OFF | BAD_DEBT
 *
 * See packages/types/src/enums.ts — InvoiceStatus = string (refined by invoices-core)
 * The CHECK below is the runtime source of truth. The integrator must also
 * narrow the packages/types stub per manifest.notes.
 */
export const invoices = pgTable(
  'invoices',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    customerId: uuid('customer_id')
      .references(() => customers.id, { onDelete: 'restrict' }),
    projectId: uuid('project_id').references(() => projects.id, { onDelete: 'set null' }),

    // Sequential numbers — NULL until assigned at state transition
    invoiceNumber: text('invoice_number'), // assigned at TAX_ISSUED
    proformaNumber: text('proforma_number'), // assigned at SENT

    status: text('status').notNull().default('DRAFT'),

    currency: text('currency').notNull().default('ILS'),

    // Dates
    issueDate: date('issue_date'), // date proforma was sent
    taxIssueDate: date('tax_issue_date'), // date tax invoice was issued
    dueDate: date('due_date'),

    // VAT — stored at issue time (rate at that date, immutable after issue)
    vatRate: numeric('vat_rate', { precision: 5, scale: 4 }),

    // Totals
    subtotal: numeric('subtotal', { precision: 12, scale: 2 }).notNull().default('0'),
    vatAmount: numeric('vat_amount', { precision: 12, scale: 2 }).notNull().default('0'),
    total: numeric('total', { precision: 12, scale: 2 }).notNull().default('0'),

    // Payment denormalization (set by partial-payment-recording spec 80)
    amountPaid: numeric('amount_paid', { precision: 12, scale: 2 }).notNull().default('0'),
    // Overpayment denorm (partial-payment-recording wave-11)
    overpaymentAmount: numeric('overpayment_amount', { precision: 12, scale: 2 }).notNull().default('0'),

    notes: text('notes'),

    // Idempotent auto-generation key (automation only; partial unique index in raw DDL)
    dedupKey: text('dedup_key'),

    // Invoice source — how was this invoice created?
    source: text('source').notNull().default('manual'),

    // State transition timestamps
    sentAt: timestamp('sent_at', { withTimezone: true }),
    approvedAt: timestamp('approved_at', { withTimezone: true }),
    taxIssuedAt: timestamp('tax_issued_at', { withTimezone: true }),
    paidAt: timestamp('paid_at', { withTimezone: true }),

    // External adapter integration (Morning, iCount, Rivhit, etc.)
    externalId: text('external_id'),
    externalProvider: text('external_provider'),

    // VOID support
    voidReason: text('void_reason'),
    voidedAt: timestamp('voided_at', { withTimezone: true }),
    voidedBy: uuid('voided_by').references(() => users.id, { onDelete: 'set null' }),

    // Credit note (חשבונית זיכוי) linkage — self-referencing FK
    // source='credit_note' + negative totals + parent_invoice_id → the invoice being credited
    parentInvoiceId: uuid('parent_invoice_id').references((): AnyPgColumn => invoices.id, {
      onDelete: 'set null',
    }),

    // R2 snapshot — URL of immutable HTML render stored at TAX_ISSUED
    htmlSnapshotUrl: text('html_snapshot_url'),

    // invoice-approval-workflow (P054) — wave-7 integrator columns
    approvedBy: uuid('approved_by').references(() => users.id, { onDelete: 'set null' }),
    approvalNote: text('approval_note'),
    rejectionReason: text('rejection_reason'),
    rejectionNotifyCustomer: boolean('rejection_notify_customer').notNull().default(false),

    // invoice-draft-library (P055) — wave-7 integrator columns
    isTemplate: boolean('is_template').notNull().default(false),

    // recurring-invoices (P061) — wave-7 integrator column
    recurringTemplateId: uuid('recurring_template_id').references(
      () => recurringInvoiceTemplates.id,
      { onDelete: 'set null' },
    ),
    // recurring-invoices: billing period the invoice was generated for (next_generation_date at generation time)
    recurringPeriodStart: date('recurring_period_start'),

    // proposal-to-invoice-direct (wave-11) — set when invoice is created from a proposal
    proposalId: uuid('proposal_id'),

    // bad-debt-writeoff (wave-12) — three nullable columns; status already covers BAD_DEBT/WRITTEN_OFF
    badDebtAt: timestamp('bad_debt_at', { withTimezone: true }),
    badDebtReason: text('bad_debt_reason'),
    badDebtNote: text('bad_debt_note'),

    // invoice-payment-link-generation (wave-12) — timestamp of last payment link email send (audit trail)
    paymentLinkSentAt: timestamp('payment_link_sent_at', { withTimezone: true }),

    // invoice-payment-reminders (wave-13): reminder state columns
    reminderLastSentAt: timestamp('reminder_last_sent_at', { withTimezone: true }),
    reminderCount: integer('reminder_count').notNull().default(0),
    remindersDisabled: boolean('reminders_disabled').notNull().default(false),
    nextReminderAt: timestamp('next_reminder_at', { withTimezone: true }),
    // offset_days of the stage next_reminder_at points to; <=0 = pre/at-due, >0 = post-due
    nextReminderOffset: integer('next_reminder_offset'),

    // wave-13: leads-detail-view — reverse-lookup FK from lead detail (nullable)
    leadId: uuid('lead_id').references(() => leads.id, { onDelete: 'set null' }),

    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    // btree indexes — safe in drizzle builder
    tenantStatusIdx: index('idx_invoices_tenant_status').on(t.tenantId, t.status, t.createdAt.desc()),
    // financial-statements (wave-13): covering index for P&L report queries
    reportIdx: index('idx_invoices_report').on(t.tenantId, t.status, t.taxIssueDate),
    // reports-analytics (wave B): invoice report filters by tenant + issue_date
    tenantIssueDateIdx: index('idx_invoices_tenant_issue_date').on(t.tenantId, t.issueDate),
    tenantCustomerIdx: index('idx_invoices_tenant_customer').on(t.tenantId, t.customerId),
    tenantProjectIdx: index('idx_invoices_tenant_project').on(t.tenantId, t.projectId),

    // Unique: issued invoice numbers must be gap-free and unique per tenant.
    // NULLs (DRAFT/SENT) are distinct in Postgres — no false conflicts.
    invoiceNumberUniq: uniqueIndex('idx_invoices_invoice_number_uniq').on(
      t.tenantId,
      t.invoiceNumber,
    ),
    proformaNumberUniq: uniqueIndex('idx_invoices_proforma_number_uniq').on(
      t.tenantId,
      t.proformaNumber,
    ),

    // Status CHECK — canonical 10 values, must match InvoiceStatus union
    statusCheck: check(
      'invoices_status_check',
      sql`${t.status} IN ('DRAFT','SENT','APPROVED','REJECTED','TAX_ISSUED','PAID','PARTIALLY_PAID','VOID','WRITTEN_OFF','BAD_DEBT')`,
    ),
    // Source CHECK
    sourceCheck: check(
      'invoices_source_check',
      sql`${t.source} IN ('manual','retainer','hourly_auto','fixed_deposit','credit_note','auto_charge')`,
    ),
    // invoice-draft-library (P055): templates don't need a customer
    requireCustomerCheck: check(
      'chk_invoice_requires_customer',
      sql`${t.isTemplate} = true OR ${t.customerId} IS NOT NULL`,
    ),
    // bad-debt-writeoff (wave-12): constrain reason values
    badDebtReasonCheck: check(
      'invoices_bad_debt_reason_check',
      sql`${t.badDebtReason} IS NULL OR ${t.badDebtReason} IN ('bankruptcy','collection_failed','other')`,
    ),
  }),
)

export type InvoiceRow = typeof invoices.$inferSelect
export type NewInvoice = typeof invoices.$inferInsert

// ── invoice_lines ─────────────────────────────────────────────────────────────

export const invoiceLines = pgTable(
  'invoice_lines',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    invoiceId: uuid('invoice_id')
      .notNull()
      .references(() => invoices.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    description: text('description').notNull(),
    quantity: numeric('quantity', { precision: 10, scale: 3 }).notNull().default('1'),
    unitPrice: numeric('unit_price', { precision: 12, scale: 2 }).notNull(),
    discountPct: numeric('discount_pct', { precision: 5, scale: 2 }).notNull().default('0'),
    // Denormalized: quantity * unit_price * (1 - discount_pct/100)
    lineTotal: numeric('line_total', { precision: 12, scale: 2 }).notNull(),
    taxable: boolean('taxable').notNull().default(true),
    position: integer('position').notNull(),
    // expense-to-invoice-line (P052) — wave-7 integrator column
    expenseId: uuid('expense_id').references(() => expenses.id, { onDelete: 'set null' }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    invoiceIdx: index('idx_invoice_lines_invoice').on(t.invoiceId, t.position),
    tenantIdx: index('idx_invoice_lines_tenant').on(t.tenantId),
  }),
)

export type InvoiceLineRow = typeof invoiceLines.$inferSelect
export type NewInvoiceLine = typeof invoiceLines.$inferInsert

// ── invoice_sequences ─────────────────────────────────────────────────────────

/**
 * Gapless sequence state per (tenant, type).
 * type: 'invoice' | 'proforma'
 * Number assigned atomically via INSERT … ON CONFLICT DO UPDATE RETURNING.
 * Israeli law requires gap-free, sequential numbering — any gap invalidates compliance.
 */
export const invoiceSequences = pgTable(
  'invoice_sequences',
  {
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    type: text('type').notNull(), // 'invoice' | 'proforma' | 'credit_note'
    lastNumber: integer('last_number').notNull().default(0),
    prefix: text('prefix').notNull().default(''),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.tenantId, t.type] }),
    typeCheck: check(
      'invoice_sequences_type_check',
      sql`${t.type} IN ('invoice','proforma','credit_note')`,
    ),
  }),
)

export type InvoiceSequenceRow = typeof invoiceSequences.$inferSelect
export type NewInvoiceSequence = typeof invoiceSequences.$inferInsert