-- =====================================================================
-- Real-time customer support chat — PostgreSQL schema
-- Idempotent: safe to run repeatedly (used by `npm run db:migrate`).
-- =====================================================================

CREATE EXTENSION IF NOT EXISTS "pgcrypto";   -- gen_random_uuid()
CREATE EXTENSION IF NOT EXISTS "citext";     -- case-insensitive email

-- ---------- enums -----------------------------------------------------
DO $$ BEGIN
  CREATE TYPE agent_role       AS ENUM ('agent', 'admin');
  CREATE TYPE agent_presence   AS ENUM ('online', 'away', 'busy', 'offline');
  CREATE TYPE conversation_status AS ENUM ('queued', 'active', 'closed');
  CREATE TYPE sender_type      AS ENUM ('customer', 'agent', 'system');
  CREATE TYPE message_type     AS ENUM ('text', 'file', 'system');
  CREATE TYPE attachment_status AS ENUM ('pending', 'uploaded', 'failed');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- ---------- agents ----------------------------------------------------
CREATE TABLE IF NOT EXISTS agents (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email                 CITEXT UNIQUE NOT NULL,
  password_hash         TEXT NOT NULL,
  name                  TEXT NOT NULL,
  avatar_url            TEXT,
  role                  agent_role NOT NULL DEFAULT 'agent',
  presence              agent_presence NOT NULL DEFAULT 'offline',
  max_concurrent_chats  INT NOT NULL DEFAULT 5 CHECK (max_concurrent_chats > 0),
  last_seen_at          TIMESTAMPTZ,
  created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at            TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- customers -------------------------------------------------
-- One row per human. Re-identified across visits by email (fallback: phone),
-- which is what powers the "previous chats" list in the agent profile panel.
CREATE TABLE IF NOT EXISTS customers (
  id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name         TEXT NOT NULL,
  email        CITEXT,
  phone        TEXT,
  external_id  TEXT,                       -- host app's own user id, if provided
  created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS customers_email_key ON customers (email) WHERE email IS NOT NULL;
CREATE INDEX IF NOT EXISTS customers_phone_idx ON customers (phone) WHERE phone IS NOT NULL;

-- ---------- conversations --------------------------------------------
CREATE TABLE IF NOT EXISTS conversations (
  id                   UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id          UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  agent_id             UUID REFERENCES agents(id) ON DELETE SET NULL,
  status               conversation_status NOT NULL DEFAULT 'queued',
  -- 0 = normal, 1 = high, 2 = urgent. Drives queue sorting in the dashboard.
  priority             SMALLINT NOT NULL DEFAULT 0 CHECK (priority BETWEEN 0 AND 2),
  subject              TEXT,                -- "issue" text from the pre-chat form
  -- Read cursors: highest message.seq each side has seen (powers read receipts
  -- and unread badges without a row per message per reader).
  customer_last_read_seq BIGINT NOT NULL DEFAULT 0,
  agent_last_read_seq    BIGINT NOT NULL DEFAULT 0,
  last_message_at      TIMESTAMPTZ,
  queued_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
  accepted_at          TIMESTAMPTZ,
  closed_at            TIMESTAMPTZ,
  close_reason         TEXT,
  meta                 JSONB NOT NULL DEFAULT '{}'::jsonb,  -- page url, referrer, tags…
  created_at           TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at           TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS conversations_status_idx    ON conversations (status);
CREATE INDEX IF NOT EXISTS conversations_agent_idx     ON conversations (agent_id, status);
CREATE INDEX IF NOT EXISTS conversations_customer_idx  ON conversations (customer_id, created_at DESC);
-- Queue ordering: urgent first, then oldest-waiting first.
CREATE INDEX IF NOT EXISTS conversations_queue_idx     ON conversations (priority DESC, queued_at ASC) WHERE status = 'queued';

-- ---------- customer sessions (enrichment snapshot) -------------------
-- Captured once per chat, at pre-chat-form submit time. Kept separate from
-- `customers` because IP / device / location are per-visit facts.
CREATE TABLE IF NOT EXISTS customer_sessions (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  customer_id    UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  ip             TEXT,
  user_agent     TEXT,
  device         JSONB NOT NULL DEFAULT '{}'::jsonb,  -- {browser, os, device, type}
  geo            JSONB NOT NULL DEFAULT '{}'::jsonb,  -- {city, region, country, lat, lon, tz, isp}
  locale         TEXT,
  timezone       TEXT,
  screen         JSONB,                                -- {w, h, dpr}
  page_url       TEXT,
  referrer       TEXT,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS customer_sessions_conv_key ON customer_sessions (conversation_id);

-- ---------- messages --------------------------------------------------
-- `seq` is a global monotonic bigint. Ordering *within* a conversation is what
-- matters, and a global sequence makes reconnect-resync a single indexed
-- range scan: WHERE conversation_id = $1 AND seq > $cursor.
CREATE TABLE IF NOT EXISTS messages (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  seq             BIGSERIAL UNIQUE NOT NULL,
  conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  sender_type     sender_type NOT NULL,
  sender_id       UUID,                    -- agents.id or customers.id; NULL for system
  type            message_type NOT NULL DEFAULT 'text',
  body            TEXT NOT NULL DEFAULT '',
  attachment_id   UUID,                    -- FK added after attachments table
  -- Client-generated id: makes message sending idempotent, so a retry after a
  -- flaky reconnect can never duplicate a message.
  client_msg_id   TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS messages_conv_seq_idx ON messages (conversation_id, seq);
CREATE UNIQUE INDEX IF NOT EXISTS messages_client_msg_key
  ON messages (conversation_id, client_msg_id) WHERE client_msg_id IS NOT NULL;

-- ---------- attachments ----------------------------------------------
CREATE TABLE IF NOT EXISTS attachments (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  uploader_type   sender_type NOT NULL,
  uploader_id     UUID,
  storage_key     TEXT NOT NULL,           -- driver-relative key, e.g. 2026/08/<uuid>.png
  original_name   TEXT NOT NULL,
  mime_type       TEXT NOT NULL,
  size_bytes      BIGINT NOT NULL DEFAULT 0,
  status          attachment_status NOT NULL DEFAULT 'pending',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS attachments_conv_idx ON attachments (conversation_id);

DO $$ BEGIN
  ALTER TABLE messages
    ADD CONSTRAINT messages_attachment_fk
    FOREIGN KEY (attachment_id) REFERENCES attachments(id) ON DELETE SET NULL;
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

-- ---------- internal notes (never sent to the customer) ---------------
CREATE TABLE IF NOT EXISTS internal_notes (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  agent_id        UUID REFERENCES agents(id) ON DELETE SET NULL,
  body            TEXT NOT NULL,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS internal_notes_conv_idx ON internal_notes (conversation_id, created_at DESC);

-- ---------- canned responses -----------------------------------------
-- agent_id NULL = shared across the whole team.
CREATE TABLE IF NOT EXISTS canned_responses (
  id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  agent_id   UUID REFERENCES agents(id) ON DELETE CASCADE,
  title      TEXT NOT NULL,
  shortcut   TEXT,                          -- typed as "/refund" in the composer
  body       TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS canned_agent_idx ON canned_responses (agent_id);

-- ---------- transfers -------------------------------------------------
CREATE TABLE IF NOT EXISTS conversation_transfers (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id UUID NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  from_agent_id   UUID REFERENCES agents(id) ON DELETE SET NULL,
  to_agent_id     UUID REFERENCES agents(id) ON DELETE SET NULL,
  note            TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS transfers_conv_idx ON conversation_transfers (conversation_id, created_at DESC);

-- ---------- CSAT ------------------------------------------------------
CREATE TABLE IF NOT EXISTS csat_ratings (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  conversation_id UUID NOT NULL UNIQUE REFERENCES conversations(id) ON DELETE CASCADE,
  agent_id        UUID REFERENCES agents(id) ON DELETE SET NULL,
  rating          SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
  comment         TEXT,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ---------- updated_at triggers --------------------------------------
CREATE OR REPLACE FUNCTION set_updated_at() RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE t TEXT;
BEGIN
  FOREACH t IN ARRAY ARRAY['agents','customers','conversations'] LOOP
    EXECUTE format('DROP TRIGGER IF EXISTS %I_set_updated_at ON %I', t, t);
    EXECUTE format(
      'CREATE TRIGGER %I_set_updated_at BEFORE UPDATE ON %I
       FOR EACH ROW EXECUTE FUNCTION set_updated_at()', t, t);
  END LOOP;
END $$;