-- 001_init.sql — Helpdesk Postgres schema (migration from the in-memory JSON Store).
-- Idempotent / re-runnable: only CREATE ... IF NOT EXISTS (no data-changing statements here;
-- data import lives in the separate idempotent importer, db/import_json.rb).
-- This is the schema of record: the record shapes here mirror the JSON Store in lib/helpdesk/domain.rb.
-- Enums are TEXT (the app uses string values); timestamps timestamptz; IDs preserved as bigint,
-- generated via standalone sequences (PgStore.next_id -> nextval), set past max on import.

-- ---- id sequences (PgStore.next_id(kind) -> nextval("<kind>_id_seq"); ledger -> ledger_seq) ----
CREATE SEQUENCE IF NOT EXISTS org_id_seq;
CREATE SEQUENCE IF NOT EXISTS campaign_id_seq;
CREATE SEQUENCE IF NOT EXISTS person_id_seq;
CREATE SEQUENCE IF NOT EXISTS event_id_seq;
CREATE SEQUENCE IF NOT EXISTS ledger_seq;

-- ---- orgs (legacy: never written by current code; present in seed data) ----
CREATE TABLE IF NOT EXISTS orgs (
  id       bigint PRIMARY KEY,
  name     text NOT NULL,
  operator text NOT NULL
);

-- ---- campaigns ----
CREATE TABLE IF NOT EXISTS campaigns (
  id       bigint PRIMARY KEY,
  name     text NOT NULL,
  operator text NOT NULL
);

-- ---- dids (a number WE own) ----
CREATE TABLE IF NOT EXISTS dids (
  e164        text PRIMARY KEY,
  operator    text NOT NULL,
  campaign_id bigint REFERENCES campaigns(id)
);

-- ---- people (+ per-customer AI memory as first-class jsonb columns) ----
CREATE TABLE IF NOT EXISTS people (
  id                         bigint PRIMARY KEY,
  name                       text NOT NULL,
  operator                   text NOT NULL,
  email                      text,
  context                    text,
  position                   text,
  org_id                     bigint,               -- legacy, nullable, no FK (no reader/writer)
  source                     text,                 -- "phone" = synced from the Pixel address book
  notes_rev                  int DEFAULT 0,
  ai_context                 jsonb,                -- {"summary":.., "other_context":[..]} (string-keyed)
  ai_context_rev             int DEFAULT 0,        -- optimistic-lock revision (CAS)
  ai_context_pinned_sections jsonb DEFAULT '{}'::jsonb,
  ai_context_edited          boolean DEFAULT false,
  ai_context_built_from      jsonb,                -- {"last_event_id":.., "model":.., "at":.., ...}
  resolutions_ledger         jsonb DEFAULT '[]'::jsonb,
  ledger_applied_events      jsonb DEFAULT '[]'::jsonb
);

-- ---- phone_numbers (e164 is the store PK; a person has many) ----
CREATE TABLE IF NOT EXISTS phone_numbers (
  e164      text PRIMARY KEY,
  person_id bigint REFERENCES people(id) ON DELETE CASCADE,  -- delete_person deletes the numbers
  raw_input text,
  label     text
);

-- ---- events (one row per call; scalars=columns, nested=jsonb) ----
CREATE TABLE IF NOT EXISTS events (
  id                 bigint PRIMARY KEY,
  call_id            text NOT NULL UNIQUE,          -- device/server-minted; hottest lookup + idempotency
  person_id          bigint,                        -- NO FK: may dangle after delete_person (verify at import)
  campaign_id        bigint,
  did_id             text,
  recording_uuid     text,
  recording_url      text,
  started_at         timestamptz,
  answered_at        timestamptz,
  ended_at           timestamptz,
  duration_s         int,
  direction          text,                          -- inbound | outbound
  operator_context   text,
  presentation       text,                          -- allowed | restricted | ...
  state              text NOT NULL,                 -- ringing/answered/ended/recording_uploaded/transcribed/summarised/failed/no_speech/recording_missing
  notes              text,
  transcript         text,
  ai_summary         text,
  action_items       jsonb DEFAULT '[]'::jsonb,     -- [{"text":..,"kind":..,"target":..}] (string-keyed)
  resolution         text,                          -- solved | partial | unsolved | null
  prior_event_id     bigint,                        -- self-ref (repeat thread); no FK (import order)
  pomoc_ticket_id    text,
  device_id          text,
  flags              jsonb DEFAULT '[]'::jsonb,     -- ["timeline_incomplete", ...]
  muted              boolean DEFAULT false,
  on_hold            boolean DEFAULT false,
  audio_route        text,
  dtmf_log           jsonb DEFAULT '[]'::jsonb,
  disconnect_cause   text,
  number             text,
  dialed_did         text,
  audio_path         text,
  context_digest     text,
  caller_facts       jsonb DEFAULT '[]'::jsonb,     -- ["fact", ...]
  ledger_deltas      jsonb DEFAULT '[]'::jsonb,     -- [{"entry_id":..,"question":..,"decision":..,"quote":..}]
  failure_reason     text,
  failure_retryable  boolean,
  suggested_position text
);
CREATE INDEX IF NOT EXISTS events_person_id_idx ON events(person_id);

-- ---- quarantine (recording uploaded before its /incoming; reattached later) ----
CREATE TABLE IF NOT EXISTS quarantine (
  recording_uuid text PRIMARY KEY,
  call_id        text,
  url            text,
  audio_path     text,
  at             timestamptz
);