PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS schema_migrations (
  version INTEGER PRIMARY KEY,
  applied_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS providers (
  provider_id TEXT PRIMARY KEY,
  display_name TEXT NOT NULL,
  notes TEXT NOT NULL DEFAULT '',
  lifecycle TEXT NOT NULL CHECK (lifecycle IN ('active','archived')),
  revision INTEGER NOT NULL CHECK (revision >= 1),
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  created_by TEXT NOT NULL,
  updated_by TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS egress_pools (
  egress_pool_id TEXT PRIMARY KEY,
  provider_id TEXT NOT NULL DEFAULT '',
  display_name TEXT NOT NULL,
  fixed_ips_json TEXT NOT NULL DEFAULT '[]',
  whitelist_status TEXT NOT NULL DEFAULT 'unknown',
  whitelist_checked_at TEXT,
  source TEXT NOT NULL DEFAULT 'mock',
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS cells (
  cell_id TEXT PRIMARY KEY,
  revision INTEGER NOT NULL CHECK (revision >= 1),
  config_json TEXT NOT NULL,
  cloud_instance_id TEXT NOT NULL DEFAULT '',
  instance_name TEXT NOT NULL DEFAULT '',
  region TEXT NOT NULL DEFAULT '',
  boot_id TEXT NOT NULL DEFAULT '',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  updated_by TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS trunks (
  trunk_id TEXT PRIMARY KEY,
  provider_id TEXT NOT NULL REFERENCES providers(provider_id),
  latest_revision INTEGER NOT NULL CHECK (latest_revision >= 1),
  active_revision INTEGER NOT NULL DEFAULT 0 CHECK (active_revision >= 0),
  active_status TEXT NOT NULL CHECK (active_status IN ('draft','published','disabled')),
  updated_at TEXT NOT NULL,
  updated_by TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS trunk_versions (
  trunk_id TEXT NOT NULL REFERENCES trunks(trunk_id) ON DELETE CASCADE,
  revision INTEGER NOT NULL CHECK (revision >= 1),
  state TEXT NOT NULL CHECK (state IN ('draft','publishing','published','superseded')),
  config_json TEXT NOT NULL,
  config_sha256 TEXT NOT NULL,
  created_at TEXT NOT NULL,
  created_by TEXT NOT NULL,
  PRIMARY KEY (trunk_id, revision)
);

CREATE TABLE IF NOT EXISTS admission_barriers (
  trunk_id TEXT PRIMARY KEY REFERENCES trunks(trunk_id) ON DELETE CASCADE,
  operation_id TEXT NOT NULL,
  target_revision INTEGER NOT NULL,
  reason TEXT NOT NULL,
  state TEXT NOT NULL CHECK (state IN ('active','released','pending')),
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS publications (
  trunk_id TEXT NOT NULL REFERENCES trunks(trunk_id) ON DELETE CASCADE,
  revision INTEGER NOT NULL,
  cell_id TEXT NOT NULL REFERENCES cells(cell_id),
  status TEXT NOT NULL CHECK (status IN ('pending','applied','failed')),
  error_code TEXT,
  target_digest TEXT NOT NULL,
  local_revision INTEGER NOT NULL DEFAULT 0,
  local_digest TEXT NOT NULL DEFAULT '',
  applied_at TEXT,
  operation_id TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  PRIMARY KEY (trunk_id, revision, cell_id)
);

CREATE TABLE IF NOT EXISTS operations (
  operation_id TEXT PRIMARY KEY,
  actor TEXT NOT NULL,
  action TEXT NOT NULL,
  resource_type TEXT NOT NULL,
  resource_id TEXT NOT NULL,
  request_id TEXT NOT NULL,
  request_digest TEXT NOT NULL,
  expected_revision INTEGER NOT NULL DEFAULT 0,
  state TEXT NOT NULL CHECK (state IN ('in_progress','succeeded','failed','pending','needs_reconciliation')),
  http_status INTEGER NOT NULL DEFAULT 200,
  response_json TEXT,
  error_json TEXT,
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL,
  expires_at TEXT NOT NULL,
  UNIQUE(actor, action, resource_type, resource_id, request_id)
);

CREATE TABLE IF NOT EXISTS observations (
  observation_id TEXT PRIMARY KEY,
  cell_id TEXT NOT NULL REFERENCES cells(cell_id) ON DELETE CASCADE,
  trunk_id TEXT,
  boot_id TEXT NOT NULL,
  sequence INTEGER NOT NULL,
  observed_at TEXT NOT NULL,
  received_at TEXT NOT NULL,
  source TEXT NOT NULL,
  config_revision INTEGER NOT NULL DEFAULT 0,
  states_json TEXT NOT NULL,
  occupancy_json TEXT NOT NULL DEFAULT '{}',
  sample_id TEXT NOT NULL DEFAULT ''
);

CREATE INDEX IF NOT EXISTS idx_observations_cell_trunk_received
  ON observations(cell_id, trunk_id, received_at DESC);
CREATE INDEX IF NOT EXISTS idx_observations_boot_sequence
  ON observations(cell_id, boot_id, sequence DESC);

CREATE TABLE IF NOT EXISTS attempts (
  attempt_id TEXT PRIMARY KEY,
  execution_id TEXT NOT NULL DEFAULT '',
  call_id TEXT NOT NULL DEFAULT '',
  tenant_key TEXT NOT NULL DEFAULT '',
  provider_id TEXT NOT NULL DEFAULT '',
  trunk_id TEXT NOT NULL DEFAULT '',
  cell_id TEXT NOT NULL DEFAULT '',
  egress_pool_id TEXT NOT NULL DEFAULT '',
  config_revision INTEGER NOT NULL DEFAULT 0,
  mode TEXT NOT NULL CHECK (mode IN ('mock','real')),
  dialed_number TEXT NOT NULL DEFAULT '',
  attempt_started_at TEXT,
  origin_status TEXT NOT NULL CHECK (origin_status IN ('confirmed','uncertain','not_started','rejected')),
  answered_at TEXT,
  ended_at TEXT,
  termination_reason TEXT NOT NULL DEFAULT 'unknown',
  sip_code INTEGER,
  q850 TEXT NOT NULL DEFAULT '',
  ai_status TEXT NOT NULL DEFAULT 'unknown',
  source TEXT NOT NULL DEFAULT 'mock',
  fact_version INTEGER NOT NULL DEFAULT 1,
  updated_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_attempts_started ON attempts(attempt_started_at, attempt_id);
CREATE INDEX IF NOT EXISTS idx_attempts_route ON attempts(provider_id, trunk_id, cell_id, mode);
CREATE INDEX IF NOT EXISTS idx_attempts_execution ON attempts(execution_id, call_id);

CREATE TABLE IF NOT EXISTS attempt_events (
  event_id TEXT PRIMARY KEY,
  attempt_id TEXT NOT NULL REFERENCES attempts(attempt_id) ON DELETE CASCADE,
  event_type TEXT NOT NULL,
  event_time TEXT NOT NULL,
  received_at TEXT NOT NULL,
  source TEXT NOT NULL,
  fact_version INTEGER NOT NULL DEFAULT 1,
  payload_json TEXT NOT NULL DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS trunk_verifications (
  trunk_id TEXT NOT NULL REFERENCES trunks(trunk_id) ON DELETE CASCADE,
  revision INTEGER NOT NULL,
  check_name TEXT NOT NULL,
  result TEXT NOT NULL CHECK (result IN ('confirmed','failed','unknown','not_applicable')),
  evidence_ref TEXT NOT NULL DEFAULT '',
  checked_by TEXT NOT NULL,
  checked_at TEXT NOT NULL,
  PRIMARY KEY (trunk_id, revision, check_name)
);

CREATE INDEX IF NOT EXISTS idx_verifications_revision ON trunk_verifications(trunk_id, revision, result);

CREATE TABLE IF NOT EXISTS audit_entries (
  audit_id TEXT PRIMARY KEY,
  resource_type TEXT NOT NULL,
  resource_id TEXT NOT NULL,
  action TEXT NOT NULL,
  revision INTEGER NOT NULL DEFAULT 0,
  actor TEXT NOT NULL,
  request_id TEXT,
  details_json TEXT NOT NULL,
  created_at TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_audit_resource ON audit_entries(resource_type, resource_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_request ON audit_entries(request_id);