/**
 * Webhook schema — webhook-endpoint-detail (wave-11 leaf-D).
 *
 * Tables:
 * - webhook_endpoints:      per-tenant outbound webhook endpoint definitions
 * - webhook_deliveries:     per-attempt delivery log (OUTBOUND)
 * - webhook_delivery_log:   inbound external webhook forensic log (INBOUND)
 * - tenant_api_keys:        per-tenant API keys for the public API (white-label-api)
 *
 * GIN and composite indexes are created via raw SQL migration; drizzle-kit
 * snapshot serializer truncates expression indexes, so they live in
 * migrations/0014_webhook_endpoint_detail.sql only.
 *
 * DB conventions:
 * - UUID PK .defaultRandom()
 * - TIMESTAMPTZ via timestamp(col, { withTimezone: true })
 * - Enums: text() + check(... IN (...)) — NEVER pgEnum
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  jsonb,
  timestamp,
  integer,
  index,
  check,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

// ── webhook_endpoints ─────────────────────────────────────────────────────────

export const webhookEndpoints = pgTable(
  'webhook_endpoints',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    url: text('url').notNull(),
    // JSONB array of event type strings; [] means all events
    events: jsonb('events').notNull().default(sql`'[]'::jsonb`),
    // AES-256-GCM encrypted HMAC secret (ciphertext JSON)
    secretEncrypted: text('secret_encrypted').notNull(),
    isActive: boolean('is_active').notNull().default(true),
    description: text('description'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantIdx: index('idx_webhook_endpoints_tenant').on(t.tenantId, t.createdAt.desc()),
    // GIN index on events for contains-filter lives in raw SQL migration
  }),
)

export type WebhookEndpointRow = typeof webhookEndpoints.$inferSelect
export type NewWebhookEndpoint = typeof webhookEndpoints.$inferInsert

// ── webhook_deliveries ────────────────────────────────────────────────────────
// OUTBOUND delivery log — one row per HTTP delivery attempt.
// status: 4-state as per white-label-api spec 27.

export const webhookDeliveries = pgTable(
  'webhook_deliveries',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    endpointId: uuid('endpoint_id')
      .notNull()
      .references(() => webhookEndpoints.id, { onDelete: 'cascade' }),
    eventType: text('event_type').notNull(),
    // pending | delivered | failed | test
    status: text('status').notNull().default('pending'),
    requestBody: text('request_body'),
    responseStatus: integer('response_status'),
    responseBody: text('response_body'),
    latencyMs: integer('latency_ms'),
    attempt: integer('attempt').notNull().default(1),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantIdx: index('idx_webhook_deliveries_tenant').on(t.tenantId, t.createdAt.desc()),
    // composite (endpoint_id, created_at DESC) lives in raw SQL migration
    statusCheck: check(
      'webhook_deliveries_status_check',
      sql`${t.status} IN ('pending','delivered','failed','test')`,
    ),
  }),
)

export type WebhookDeliveryRow = typeof webhookDeliveries.$inferSelect
export type NewWebhookDelivery = typeof webhookDeliveries.$inferInsert

// ── webhook_delivery_log ──────────────────────────────────────────────────────
// INBOUND external webhook forensic log (spec 105 §Inbound Webhook Security).
// Distinct from the OUTBOUND webhook_deliveries table.

export const webhookDeliveryLog = pgTable(
  'webhook_delivery_log',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    // nullable: pre-auth inbound may not resolve a tenant
    tenantId: uuid('tenant_id').references(() => tenants.id, { onDelete: 'cascade' }),
    // nullable: inbound may not map to a known endpoint
    endpointId: uuid('endpoint_id').references(() => webhookEndpoints.id, {
      onDelete: 'set null',
    }),
    // inbound source identifier (e.g. 'payment-provider', path segment)
    source: text('source').notNull(),
    sourceIp: text('source_ip'),
    eventType: text('event_type'),
    status: text('status').notNull(),
    signatureValid: boolean('signature_valid').notNull().default(false),
    // truncated to first 10 KB regardless of signature outcome
    rawBody: text('raw_body'),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantCreatedIdx: index('idx_wdl_tenant_created').on(t.tenantId, t.createdAt.desc()),
    statusCheck: check(
      'webhook_delivery_log_status_check',
      sql`${t.status} IN ('accepted','rejected_invalid_signature','rejected_rate_limited')`,
    ),
  }),
)

export type WebhookDeliveryLogRow = typeof webhookDeliveryLog.$inferSelect
export type NewWebhookDeliveryLog = typeof webhookDeliveryLog.$inferInsert

// ── tenant_api_keys ───────────────────────────────────────────────────────────
// Per-tenant API keys for the public API.
// Owned by white-label-api spec; created here as the canonical definition.

export const tenantApiKeys = pgTable(
  'tenant_api_keys',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    // First 8 chars of the plaintext key for display (e.g. "zync_abc")
    keyPrefix: text('key_prefix').notNull(),
    // SHA-256(plaintext_key) — used for lookup; plaintext never stored
    keyHash: text('key_hash').notNull().unique(),
    // Array of scope strings (e.g. ['customers:read', 'invoices:read'])
    scopes: jsonb('scopes').notNull().default(sql`'[]'::jsonb`),
    expiresAt: timestamp('expires_at', { withTimezone: true }),
    lastUsedAt: timestamp('last_used_at', { withTimezone: true }),
    // User who created the key (reporter_id for API-created tasks)
    createdBy: uuid('created_by')
      .notNull()
      .references(() => users.id, { onDelete: 'restrict' }),
    revokedAt: timestamp('revoked_at', { withTimezone: true }),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    // api-usage-quota-ui (wave-12): monthly request quota; NULL = unlimited
    monthlyQuota: integer('monthly_quota'),
  },
  (t) => ({
    tenantIdx: index('idx_tenant_api_keys_tenant').on(t.tenantId, t.createdAt.desc()),
    hashIdx: index('idx_tenant_api_keys_hash').on(t.keyHash),
    monthlyQuotaCheck: check(
      'tenant_api_keys_monthly_quota_nonneg',
      sql`${t.monthlyQuota} IS NULL OR ${t.monthlyQuota} >= 0`,
    ),
  }),
)

export type TenantApiKeyRow = typeof tenantApiKeys.$inferSelect
export type NewTenantApiKey = typeof tenantApiKeys.$inferInsert