/**
 * Dunning schema — payment-retry-dunning (wave-11).
 *
 * Tables:
 * - dunning_schedules: per-tenant dunning step definitions
 * - dunning_log:       audit trail of dunning actions taken against invoices
 *
 * DB conventions:
 * - UUID PK .defaultRandom()
 * - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 * - Enums: text() + check(... IN (...)) — NEVER pgEnum
 * - Plain btree indexes via drizzle index()
 */
import { pgTable, uuid, text, integer, timestamp, index, check, unique } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { invoices } from './invoices'

export const dunningSchedules = pgTable(
  'dunning_schedules',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // days after invoice due_date to trigger
    offsetDays: integer('offset_days').notNull(),
    action: text('action').notNull(),
    // optional custom email template (soft ref, intentionally no FK)
    emailTemplateId: uuid('email_template_id'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantOffsetUniq: unique('dunning_schedules_tenant_offset_uniq').on(t.tenantId, t.offsetDays),
    actionCheck: check(
      'dunning_schedules_action_check',
      sql`${t.action} IN ('email_reminder','suspend_access','flag_for_review')`,
    ),
  }),
)

export type DunningScheduleRow = typeof dunningSchedules.$inferSelect
export type NewDunningSchedule = typeof dunningSchedules.$inferInsert

export const dunningLog = pgTable(
  'dunning_log',
  {
    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' }),
    // schedule offset this row records; -1 = manual reminder
    offsetDays: integer('offset_days').notNull(),
    action: text('action').notNull(),
    sentAt: timestamp('sent_at', { withTimezone: true }).notNull().defaultNow(),
    result: text('result').notNull(),
    errorMsg: text('error_msg'),
  },
  (t) => ({
    invoiceIdx: index('idx_dunning_log_invoice').on(t.invoiceId),
    tenantIdx: index('idx_dunning_log_tenant').on(t.tenantId),
    resultCheck: check(
      'dunning_log_result_check',
      sql`${t.result} IN ('sent','skipped','error')`,
    ),
  }),
)

export type DunningLogRow = typeof dunningLog.$inferSelect
export type NewDunningLog = typeof dunningLog.$inferInsert