/**
 * Ticket SLA & Escalation query helpers — ticket-sla-escalation (P063).
 *
 * All helpers are tenant-scoped: every statement carries a tenant_id WHERE clause.
 * Routes MUST NOT import raw Drizzle tables — they call these helpers.
 *
 * Zod validation schemas are re-exported here so route files can import them
 * via @zync/db/queries (no-raw-drizzle-from-routes policy).
 */
import { and, eq, isNull, lt, notInArray, sql } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import { slaPolicies } from '../schema/ticket-sla'
import { tenantSettings } from '../schema/tenants'
import { tickets } from '../schema/support'

// ── Domain object types ───────────────────────────────────────────────────────

export interface SlaPolicyObject {
  id: string
  tenant_id: string
  priority: 'low' | 'medium' | 'high' | 'urgent'
  first_response_hours: number
  resolution_hours: number
  escalation_email: string | null
  notify_email: boolean
  notify_in_app: boolean
  created_at: string
  updated_at: string
}

/** Minimal ticket projection used by the SLA breach cron */
export interface BreachedTicketRef {
  id: string
  tenant_id: string
  assignee_id: string | null
  priority: string
}

// ── Serializers ───────────────────────────────────────────────────────────────

function serializePolicy(row: typeof slaPolicies.$inferSelect): SlaPolicyObject {
  return {
    id: row.id,
    tenant_id: row.tenantId,
    priority: row.priority as SlaPolicyObject['priority'],
    first_response_hours: row.firstResponseHours,
    resolution_hours: row.resolutionHours,
    escalation_email: row.escalationEmail ?? null,
    notify_email: row.notifyEmail,
    notify_in_app: row.notifyInApp,
    created_at: row.createdAt.toISOString(),
    updated_at: row.updatedAt.toISOString(),
  }
}

// ── listSlaPolicies ───────────────────────────────────────────────────────────

/**
 * Return all SLA policy rows for a tenant (ordered by priority severity).
 * Empty array if no policies have been seeded yet.
 */
export async function listSlaPolicies(
  db: Db,
  tenantId: string,
): Promise<SlaPolicyObject[]> {
  const rows = await db
    .select()
    .from(slaPolicies)
    .where(eq(slaPolicies.tenantId, tenantId))
    .orderBy(
      sql`CASE ${slaPolicies.priority}
        WHEN 'urgent' THEN 1
        WHEN 'high' THEN 2
        WHEN 'medium' THEN 3
        WHEN 'low' THEN 4
        ELSE 5
      END`,
    )
  return rows.map(serializePolicy)
}

// ── getSlaPolicyById ──────────────────────────────────────────────────────────

export async function getSlaPolicyById(
  db: Db,
  tenantId: string,
  policyId: string,
): Promise<SlaPolicyObject | null> {
  const [row] = await db
    .select()
    .from(slaPolicies)
    .where(and(eq(slaPolicies.id, policyId), eq(slaPolicies.tenantId, tenantId)))
    .limit(1)
  return row ? serializePolicy(row) : null
}

// ── getSlaPolicyByPriority ────────────────────────────────────────────────────

export async function getSlaPolicyByPriority(
  db: Db,
  tenantId: string,
  priority: string,
): Promise<SlaPolicyObject | null> {
  const [row] = await db
    .select()
    .from(slaPolicies)
    .where(and(eq(slaPolicies.tenantId, tenantId), eq(slaPolicies.priority, priority)))
    .limit(1)
  return row ? serializePolicy(row) : null
}

// ── updateSlaPolicy ───────────────────────────────────────────────────────────

import type { UpdateSlaPolicyInput } from '../validation/ticket-sla'

export async function updateSlaPolicy(
  db: Db,
  tenantId: string,
  policyId: string,
  patch: UpdateSlaPolicyInput,
): Promise<SlaPolicyObject> {
  const setValues: Partial<typeof slaPolicies.$inferInsert> = {
    updatedAt: new Date(),
  }

  if (patch.first_response_hours !== undefined)
    setValues.firstResponseHours = patch.first_response_hours
  if (patch.resolution_hours !== undefined)
    setValues.resolutionHours = patch.resolution_hours
  if (patch.escalation_email !== undefined)
    setValues.escalationEmail = patch.escalation_email
  if (patch.notify_email !== undefined) setValues.notifyEmail = patch.notify_email
  if (patch.notify_in_app !== undefined) setValues.notifyInApp = patch.notify_in_app

  const [row] = await db
    .update(slaPolicies)
    .set(setValues)
    .where(and(eq(slaPolicies.id, policyId), eq(slaPolicies.tenantId, tenantId)))
    .returning()
  if (!row) throw new Error('SLA policy not found')
  return serializePolicy(row)
}

// ── seedSlaPolicies ───────────────────────────────────────────────────────────

/** Default SLA targets per priority (Business+ upgrade seed). */
const DEFAULT_POLICIES: Array<{
  priority: 'low' | 'medium' | 'high' | 'urgent'
  first_response_hours: number
  resolution_hours: number
}> = [
  { priority: 'urgent', first_response_hours: 1, resolution_hours: 4 },
  { priority: 'high', first_response_hours: 4, resolution_hours: 24 },
  { priority: 'medium', first_response_hours: 8, resolution_hours: 72 },
  { priority: 'low', first_response_hours: 24, resolution_hours: 168 },
]

/**
 * Seed default SLA policies for a tenant (called on Business+ upgrade).
 * Uses INSERT ... ON CONFLICT DO NOTHING so it is idempotent.
 */
export async function seedSlaPolicies(db: Db | DbTx, tenantId: string): Promise<void> {
  await db
    .insert(slaPolicies)
    .values(
      DEFAULT_POLICIES.map((p) => ({
        tenantId,
        priority: p.priority,
        firstResponseHours: p.first_response_hours,
        resolutionHours: p.resolution_hours,
      })),
    )
    .onConflictDoNothing({ target: [slaPolicies.tenantId, slaPolicies.priority] })
}

// ── getSlaEnabled ─────────────────────────────────────────────────────────────

/** Read tenant_settings.sla_enabled (defaults false when row missing). */
export async function getSlaEnabled(db: Db | DbTx, tenantId: string): Promise<boolean> {
  const [row] = await db
    .select({ slaEnabled: tenantSettings.slaEnabled })
    .from(tenantSettings)
    .where(eq(tenantSettings.tenantId, tenantId))
    .limit(1)
  return row?.slaEnabled ?? false
}

// ── computeAndSetDueAt ────────────────────────────────────────────────────────

/**
 * Look up the SLA resolution_hours for the ticket's priority and set due_at.
 * Called after ticket create or priority change (when sla_enabled = true).
 * No-op if no policy exists for the tenant+priority.
 */
export async function computeAndSetDueAt(
  db: Db | DbTx,
  tenantId: string,
  ticketId: string,
  priority: string,
  createdAt: Date,
): Promise<void> {
  const policy = await getSlaPolicyByPriority(db as Db, tenantId, priority)
  if (!policy || policy.resolution_hours === 0) return

  const dueAt = new Date(createdAt.getTime() + policy.resolution_hours * 60 * 60 * 1000)

  await (db as Db)
    .update(tickets)
    .set({ dueAt, slaBreached: false, updatedAt: new Date() })
    .where(and(eq(tickets.id, ticketId), eq(tickets.tenantId, tenantId)))
}

// ── markFirstResponse ─────────────────────────────────────────────────────────

/**
 * Set first_response_at = now() on a ticket if not already set.
 * Called when a staff member posts the first reply.
 */
export async function markFirstResponse(
  db: Db | DbTx,
  tenantId: string,
  ticketId: string,
): Promise<void> {
  await (db as Db)
    .update(tickets)
    .set({ firstResponseAt: new Date(), updatedAt: new Date() })
    .where(
      and(
        eq(tickets.id, ticketId),
        eq(tickets.tenantId, tenantId),
        isNull(tickets.firstResponseAt),
      ),
    )
}

// ── findAndMarkBreachedTickets (cron) ─────────────────────────────────────────

/**
 * Find tickets past their resolution SLA deadline, flip sla_breached = true,
 * and return the affected rows so the cron can dispatch notifications.
 *
 * Only scans tenants that have at least one sla_policies row (i.e., Business+
 * tenants that have been seeded). Does NOT filter by tenant_settings.sla_enabled
 * here — the seed step is the gate; tenants without rows are untouched.
 */
export async function findAndMarkBreachedTickets(
  db: Db,
): Promise<BreachedTicketRef[]> {
  const now = new Date()

  const rows = await db
    .update(tickets)
    .set({ slaBreached: true, updatedAt: now })
    .where(
      and(
        notInArray(tickets.status, ['resolved', 'closed']),
        eq(tickets.slaBreached, false),
        lt(tickets.dueAt, now),
        // Only tickets that have a due_at set (i.e., SLA was computed)
        sql`${tickets.dueAt} IS NOT NULL`,
        // Only tenants with policies (Business+ gate via seed presence)
        sql`${tickets.tenantId} IN (SELECT DISTINCT tenant_id FROM sla_policies)`,
      ),
    )
    .returning({
      id: tickets.id,
      tenantId: tickets.tenantId,
      assigneeId: tickets.assigneeId,
      priority: tickets.priority,
    })

  return rows.map((r) => ({
    id: r.id,
    tenant_id: r.tenantId,
    assignee_id: r.assigneeId ?? null,
    priority: r.priority,
  }))
}

// ── Re-export validation schemas ──────────────────────────────────────────────

export {
  updateSlaPolicySchema,
  slaPrioritySchema,
} from '../validation/ticket-sla'
export type { UpdateSlaPolicyInput } from '../validation/ticket-sla'