/**
 * Ticket SLA & Escalation schema — ticket-sla-escalation (P063).
 * Postgres / Neon via Hyperdrive.
 *
 * Tables:
 *  - sla_policies: per-tenant, per-priority SLA configuration
 *
 * One row per (tenant_id, priority) — seeded at Business+ upgrade.
 * UNIQUE constraint via drizzle unique() (standard, no collision risk).
 *
 * Partial/expression indexes → not needed here; queries filter by
 * (tenant_id, priority) which is covered by the unique index.
 */
import { pgTable, uuid, text, integer, boolean, timestamp, unique, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'

// ── sla_policies ──────────────────────────────────────────────────────────────

export const slaPolicies = pgTable(
  'sla_policies',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    // 'low' | 'medium' | 'high' | 'urgent' — matches ticket.priority
    priority: text('priority').notNull(),
    // SLA target hours (0 = no target enforced)
    firstResponseHours: integer('first_response_hours').notNull(),
    resolutionHours: integer('resolution_hours').notNull(),
    // Per-priority escalation recipient; NULL = no email escalation
    escalationEmail: text('escalation_email'),
    // Whether to send escalation email on breach (only if escalation_email is set)
    notifyEmail: boolean('notify_email').notNull().default(true),
    // Whether to emit in-app notification to assignee + OWNER/ADMIN on breach
    notifyInApp: boolean('notify_in_app').notNull().default(true),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
    updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    priorityCheck: check(
      'sla_policies_priority_check',
      sql`${t.priority} IN ('low','medium','high','urgent')`,
    ),
    // One policy row per (tenant, priority)
    tenantPriorityUniq: unique('sla_policies_tenant_priority_uniq').on(t.tenantId, t.priority),
  }),
)

export type SlaPolicyRow = typeof slaPolicies.$inferSelect
export type NewSlaPolicy = typeof slaPolicies.$inferInsert