/**
 * Contracts schema — contracts-esignature (wave-8 leaf 4).
 * wave-11 leaf C additions (contract-signing-page):
 *  - contracts.signingDeadline: deadline for signatories to sign
 *  - contracts.signedPdfR2Key: R2 key for the completed signed PDF
 *  - contract_signatories: per-signatory signing state, token, audit trail
 *  - contract_audit_log: immutable event log for contract lifecycle
 * wave-12: multi-signatory-coordination additions:
 *  - contract_signatories."order": integer signing order (1=first; all 1=simultaneous)
 *  - contract_signatories.reminder_sent_at: last reminder timestamp (nullable)
 *  - contract_signatories.reminder_count: cumulative reminder count
 *  - contract_audit_log: added link_resent, signatory_replaced events
 * wave-12: contract-renewal-amendment additions:
 *  - contracts.renewedFromId: FK to origin contract (renewal linkage)
 *  - contracts.amendedFromId: FK to origin contract (amendment linkage)
 *  - contracts.effectiveDate: optional contract start date (DATE)
 *  - contracts.expiryDate: optional contract end date (DATE)
 *  Partial index on (tenant_id, expiry_date) WHERE status='signed' AND expiry_date IS NOT NULL
 *  lives in the raw SQL migration (GIN/expression/partial indexes must NOT use drizzle index builder).
 * wave-13: leads-detail-view additions:
 *  - contracts.leadId: nullable FK to leads(id) for reverse-lookup from lead detail
 *
 * DB conventions:
 *   UUID PK .defaultRandom()
 *   TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 *   Enums: text() + check(... IN (...)) — NEVER pgEnum
 *   Indexes via index() builder
 */
import { pgTable, uuid, text, jsonb, timestamp, date, integer, index, check, uniqueIndex, type AnyPgColumn } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { leads } from './marketing'

// ── contracts ─────────────────────────────────────────────────────────────────

export const contracts = pgTable(
  'contracts',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    customerId: uuid('customer_id'),
    title: text('title').notNull(),
    content: text('content').notNull(), // rich text / markdown
    status: text('status').notNull().default('draft'), // draft|sent|signed|voided
    signerEmail: text('signer_email'),
    signerName: text('signer_name'),
    sentAt: timestamp('sent_at', { withTimezone: true }),
    signedAt: timestamp('signed_at', { withTimezone: true }),
    voidedAt: timestamp('voided_at', { withTimezone: true }),
    signatureData: jsonb('signature_data'), // base64 signature image
    metadata: jsonb('metadata').default({}),
    // wave-11: signing deadline for all signatories
    signingDeadline: timestamp('signing_deadline', { withTimezone: true }),
    // wave-11: R2 key for completed signed PDF (null until generated)
    signedPdfR2Key: text('signed_pdf_r2_key'),
    // wave-12: renewal/amendment linkage (self-referential FKs, nullable)
    renewedFromId: uuid('renewed_from_id').references((): AnyPgColumn => contracts.id),
    amendedFromId: uuid('amended_from_id').references((): AnyPgColumn => contracts.id),
    // wave-12: optional contract term dates (DATE, not TIMESTAMPTZ)
    effectiveDate: date('effective_date'),
    expiryDate: date('expiry_date'),
    // wave-13: reverse-lookup FK from lead detail (nullable — existing rows unaffected)
    leadId: uuid('lead_id').references(() => leads.id, { onDelete: 'set null' }),
    // wave-13: settings-contracts — signing order for this contract's signatories
    signingOrder: text('signing_order').notNull().default('parallel'),
    createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
  },
  (t) => ({
    tenantStatusIdx: index('idx_contracts_tenant_status').on(t.tenantId, t.status),
    statusCheck: check(
      'contracts_status_check',
      sql`${t.status} IN ('draft','sent','signed','voided')`,
    ),
  }),
)

export type ContractRow = typeof contracts.$inferSelect
export type NewContract = typeof contracts.$inferInsert

// ── contract_signatories ──────────────────────────────────────────────────────

export const contractSignatories = pgTable(
  'contract_signatories',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    contractId: uuid('contract_id')
      .notNull()
      .references(() => contracts.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    email: text('email').notNull(),
    name: text('name'),
    // Opaque plaintext signing token (unique per signatory)
    token: text('token').notNull().unique(),
    tokenExpiresAt: timestamp('token_expires_at', { withTimezone: true }),
    signedAt: timestamp('signed_at', { withTimezone: true }),
    declinedAt: timestamp('declined_at', { withTimezone: true }),
    signatureData: jsonb('signature_data'), // drawn/typed signature
    signatureType: text('signature_type'), // 'drawn' | 'typed'
    // Signer IP and user agent captured at signing
    signerIp: text('signer_ip'),
    signerUserAgent: text('signer_user_agent'),
    // For multi-signatory ordering (NULL = any order)
    signingOrder: text('signing_order'),
    // wave-12: multi-signatory-coordination — integer signing order (1=first; all 1=simultaneous)
    // "order" is a reserved SQL word; drizzle quotes it automatically via integer('order')
    order: integer('order').notNull().default(1),
    // wave-12: reminder tracking
    reminderSentAt: timestamp('reminder_sent_at', { withTimezone: true }),
    reminderCount: integer('reminder_count').notNull().default(0),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    contractIdx: index('idx_signatories_contract').on(t.contractId),
    tokenIdx: uniqueIndex('idx_signatories_token').on(t.token),
    signatureTypeCheck: check(
      'contract_signatories_signature_type_check',
      sql`${t.signatureType} IS NULL OR ${t.signatureType} IN ('drawn','typed')`,
    ),
  }),
)

export type ContractSignatoryRow = typeof contractSignatories.$inferSelect
export type NewContractSignatory = typeof contractSignatories.$inferInsert

// ── contract_audit_log ────────────────────────────────────────────────────────

export const contractAuditLog = pgTable(
  'contract_audit_log',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    contractId: uuid('contract_id')
      .notNull()
      .references(() => contracts.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // 'sent' | 'viewed' | 'signed' | 'declined' | 'voided' | 'downloaded' | 'reminder_sent' | 'link_resent' | 'signatory_replaced'
    event: text('event').notNull(),
    actorType: text('actor_type').notNull(), // 'staff' | 'signatory' | 'system'
    actorId: text('actor_id'), // userId or signatory email
    ipAddress: text('ip_address'),
    userAgent: text('user_agent'),
    metadata: jsonb('metadata').default({}),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    contractIdx: index('idx_contract_audit_log_contract').on(t.contractId, t.createdAt),
    eventCheck: check(
      'contract_audit_log_event_check',
      sql`${t.event} IN ('sent','viewed','signed','declined','voided','downloaded','reminder_sent','link_resent','signatory_replaced')`,
    ),
    actorTypeCheck: check(
      'contract_audit_log_actor_type_check',
      sql`${t.actorType} IN ('staff','signatory','system')`,
    ),
  }),
)

export type ContractAuditLogRow = typeof contractAuditLog.$inferSelect
export type NewContractAuditLog = typeof contractAuditLog.$inferInsert