/**
 * People, routes, and escalation policies — who celilo can reach, how, and in
 * what order.
 *
 * The three are deliberately separate tables (design D8):
 *
 *   person  who they are, and when they may be disturbed
 *   route   a person's address ON a transport, plus the policy for using it
 *   policy  an ordered list of routes with delays
 *
 * Recipient addresses live on ROUTES, never in a transport module's config.
 * signal-cli holds exactly one credential — its own registration — while
 * recipients are just addresses. Putting them in module config would make
 * adding a person a module redeploy.
 *
 * Escalation steps reference routes rather than people, because "page Peter"
 * is ambiguous about which transport to use.
 */

import { randomUUID } from 'node:crypto';
import { and, asc, eq } from 'drizzle-orm';
import type { DbClient } from '../../db/client';
import {
  type AlertSeverity,
  type EscalationPolicy,
  type EscalationStep,
  type Person,
  type Route,
  escalationPolicies,
  escalationSteps,
  people,
  routes,
} from '../../db/schema';

// ── People ──────────────────────────────────────────────────────────────────

export interface CreatePersonInput {
  name: string;
  timezone: string;
  quietHoursStart?: string | null;
  quietHoursEnd?: string | null;
}

export function listPeople(db: DbClient): Person[] {
  return db.select().from(people).orderBy(asc(people.name)).all();
}

export function findPerson(db: DbClient, name: string): Person | undefined {
  return db.select().from(people).where(eq(people.name, name)).get();
}

export function createPerson(db: DbClient, input: CreatePersonInput): Person {
  const id = randomUUID();
  db.insert(people)
    .values({
      id,
      name: input.name,
      timezone: input.timezone,
      quietHoursStart: input.quietHoursStart ?? null,
      quietHoursEnd: input.quietHoursEnd ?? null,
    })
    .run();
  return db.select().from(people).where(eq(people.id, id)).get() as Person;
}

/**
 * Remove a person and everything addressed to them.
 *
 * Routes cascade in the schema. Escalation steps referencing those routes
 * cascade too — a policy step pointing at a deleted route would otherwise
 * silently skip at page time, which is the worst moment to discover it.
 */
export function deletePerson(db: DbClient, personId: string): void {
  db.delete(people).where(eq(people.id, personId)).run();
}

// ── Routes ──────────────────────────────────────────────────────────────────

export interface CreateRouteInput {
  personId: string;
  transportModuleId: string;
  address: string;
  severityFloor?: AlertSeverity;
  /** Whether replies can arrive back over this transport. */
  canAck?: boolean;
}

export function listRoutes(db: DbClient): Route[] {
  return db.select().from(routes).all();
}

export function listRoutesForPerson(db: DbClient, personId: string): Route[] {
  return db.select().from(routes).where(eq(routes.personId, personId)).all();
}

export function findRoute(
  db: DbClient,
  personId: string,
  transportModuleId: string,
): Route | undefined {
  return db
    .select()
    .from(routes)
    .where(and(eq(routes.personId, personId), eq(routes.transportModuleId, transportModuleId)))
    .get();
}

export function createRoute(db: DbClient, input: CreateRouteInput): Route {
  const id = randomUUID();
  db.insert(routes)
    .values({
      id,
      personId: input.personId,
      transportModuleId: input.transportModuleId,
      address: input.address,
      severityFloor: input.severityFloor ?? 'warning',
      canAck: input.canAck ?? false,
    })
    .run();
  return db.select().from(routes).where(eq(routes.id, id)).get() as Route;
}

export function deleteRoute(db: DbClient, routeId: string): void {
  db.delete(routes).where(eq(routes.id, routeId)).run();
}

// ── Escalation policies ─────────────────────────────────────────────────────

export function listPolicies(db: DbClient): EscalationPolicy[] {
  return db.select().from(escalationPolicies).orderBy(asc(escalationPolicies.name)).all();
}

export function findPolicy(db: DbClient, name: string): EscalationPolicy | undefined {
  return db.select().from(escalationPolicies).where(eq(escalationPolicies.name, name)).get();
}

export function createPolicy(db: DbClient, name: string): EscalationPolicy {
  const id = randomUUID();
  db.insert(escalationPolicies).values({ id, name }).run();
  return db
    .select()
    .from(escalationPolicies)
    .where(eq(escalationPolicies.id, id))
    .get() as EscalationPolicy;
}

/** Steps of a policy, in the order escalation walks them. */
export function listPolicySteps(db: DbClient, policyId: string): EscalationStep[] {
  return db
    .select()
    .from(escalationSteps)
    .where(eq(escalationSteps.policyId, policyId))
    .orderBy(asc(escalationSteps.stepIndex))
    .all();
}

/**
 * Append a step.
 *
 * The index is assigned rather than supplied: an operator adding steps in order
 * should not have to think about numbering, and a hand-picked index that
 * collided would violate the composite primary key at the least helpful moment.
 */
export function addPolicyStep(
  db: DbClient,
  policyId: string,
  routeId: string,
  delayMinutes: number,
): EscalationStep {
  const existing = listPolicySteps(db, policyId);
  const stepIndex = existing.length === 0 ? 0 : existing[existing.length - 1].stepIndex + 1;
  db.insert(escalationSteps).values({ policyId, stepIndex, routeId, delayMinutes }).run();
  return { policyId, stepIndex, routeId, delayMinutes };
}

export function deletePolicy(db: DbClient, policyId: string): void {
  db.delete(escalationPolicies).where(eq(escalationPolicies.id, policyId)).run();
}