/**
 * Invoice email events — invoice-email-history (wave-13).
 *
 * Tracks per-recipient email delivery events for invoices.
 * Events written by:
 *   sent / failed   → synchronously on POST /api/invoices/:id/send
 *   delivered / opened / clicked / bounced → async webhook from Resend
 *
 * DB conventions:
 * - UUID PK .defaultRandom()
 * - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 * - Enums: text() + check(... IN (...)) — NEVER pgEnum
 */
import { pgTable, uuid, text, jsonb, timestamp, index, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'
import { invoices } from './invoices'

export const invoiceEmailEvents = pgTable(
  'invoice_email_events',
  {
    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' }),
    sentBy: uuid('sent_by')
      .notNull()
      .references(() => users.id),
    toAddress: text('to_address').notNull(),
    eventType: text('event_type').notNull(),
    metadata: jsonb('metadata'),
    occurredAt: timestamp('occurred_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    eventTypeCheck: check(
      'invoice_email_events_event_type_check',
      sql`${t.eventType} IN ('sent', 'delivered', 'opened', 'clicked', 'bounced', 'failed')`,
    ),
    invoiceOccurredIdx: index('idx_invoice_email_events_invoice').on(
      t.invoiceId,
      t.occurredAt,
    ),
    recipientIdx: index('idx_invoice_email_events_recipient').on(
      t.invoiceId,
      t.toAddress,
      t.eventType,
      t.occurredAt,
    ),
  }),
)

export type InvoiceEmailEvent = typeof invoiceEmailEvents.$inferSelect
export type NewInvoiceEmailEvent = typeof invoiceEmailEvents.$inferInsert