/**
 * System status page schema — system-status-page.
 *
 * Three tables:
 *  - system_incidents       — an outage / degradation event posted by SUPER_ADMIN
 *  - system_incident_updates — timeline entries attached to an incident
 *  - status_subscribers     — email opt-in list for status notifications
 *
 * IMPORTANT: `created_by` is TEXT (SUPER_ADMIN email), NOT a UUID FK, because
 * admin staff live in `admin_users` which is intentionally decoupled from the
 * tenant `users` table.
 *
 * `unsubscribe_token` uses `encode(gen_random_bytes(16),'hex')` which requires
 * the `pgcrypto` extension — the migration ensures
 * `CREATE EXTENSION IF NOT EXISTS pgcrypto` before these tables.
 */
import { pgTable, uuid, text, timestamp, check, index } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'

// ── system_incidents ──────────────────────────────────────────────────────────

export const systemIncidents = pgTable(
  'system_incidents',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    title: text('title').notNull(),
    // status: 'investigating' | 'identified' | 'monitoring' | 'resolved'
    status: text('status').notNull(),
    // impact: 'minor' | 'major' | 'critical'
    impact: text('impact').notNull(),
    // affected_services TEXT[] — service ids from STATUS_SERVICES
    affectedServices: text('affected_services')
      .array()
      .notNull()
      .default(sql`'{}'::text[]`),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    resolvedAt: timestamp('resolved_at', { withTimezone: true }),
    // SUPER_ADMIN email — admin staff are NOT in the tenant users table
    createdBy: text('created_by').notNull(),
  },
  (t) => ({
    statusCheck: check(
      'system_incidents_status_check',
      sql`${t.status} IN ('investigating','identified','monitoring','resolved')`,
    ),
    impactCheck: check(
      'system_incidents_impact_check',
      sql`${t.impact} IN ('minor','major','critical')`,
    ),
    // Plain btree index for "active incidents" lookup (WHERE resolved_at IS NULL)
    resolvedAtIdx: index('idx_system_incidents_resolved_at').on(t.resolvedAt),
  }),
)

export type SystemIncidentRow = typeof systemIncidents.$inferSelect
export type NewSystemIncident = typeof systemIncidents.$inferInsert

// ── system_incident_updates ───────────────────────────────────────────────────

export const systemIncidentUpdates = pgTable(
  'system_incident_updates',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    incidentId: uuid('incident_id')
      .notNull()
      .references(() => systemIncidents.id, { onDelete: 'cascade' }),
    body: text('body').notNull(),
    // status mirrors the parent incident status at time of this update
    status: text('status').notNull(),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    createdBy: text('created_by').notNull(),
  },
  (t) => ({
    statusCheck: check(
      'system_incident_updates_status_check',
      sql`${t.status} IN ('investigating','identified','monitoring','resolved')`,
    ),
    // Plain btree index for ordered update fetch by incident
    incidentCreatedAtIdx: index('idx_system_incident_updates_incident').on(
      t.incidentId,
      t.createdAt,
    ),
  }),
)

export type SystemIncidentUpdateRow = typeof systemIncidentUpdates.$inferSelect
export type NewSystemIncidentUpdate = typeof systemIncidentUpdates.$inferInsert

// ── status_subscribers ────────────────────────────────────────────────────────

export const statusSubscribers = pgTable(
  'status_subscribers',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    email: text('email').notNull().unique(),
    subscribedAt: timestamp('subscribed_at', { withTimezone: true }).notNull().defaultNow(),
    // Populated by pgcrypto: encode(gen_random_bytes(16),'hex')
    // The DEFAULT is in raw SQL only — drizzle cannot model gen_random_bytes.
    // We return the token from the INSERT RETURNING clause.
    unsubscribeToken: text('unsubscribe_token').notNull().unique(),
  },
)

export type StatusSubscriberRow = typeof statusSubscribers.$inferSelect
export type NewStatusSubscriber = typeof statusSubscribers.$inferInsert