-- =============================================================================
-- MIGRATION: split ChatSession.status (lifecycle) from a new
-- ChatSession.mode (who's currently answering)
--
-- Previously "escalated" was both "a human is answering" AND, in practice,
-- permanent — chat.service.ts never set a session back to 'open', and
-- closing the linked ServiceRequest never touched the session either, so a
-- customer messaging a "closed" ticket was silently absorbed forever. This
-- splits the two: status is now just open|closed (lifecycle — closed means
-- the thread is over, start a new one), mode is bot|agent (freely
-- switchable by the customer while the session is open).
--
-- 'escalated' remains a legal label in the existing Postgres
-- ChatSessionStatus enum — Postgres has no DROP VALUE, so removing it
-- would mean recreating the type. Not worth the risk here: the backfill
-- below moves every row off it, and the Prisma schema (source of truth for
-- application code) only declares open|closed going forward, so nothing
-- will ever write 'escalated' again.
--
-- Run: psql "$CHAT_DATABASE_URL" -f chat-service/prisma/migrations/manual/005_chat_session_mode.sql
-- =============================================================================

BEGIN;

DO $$ BEGIN
    CREATE TYPE "chat"."ChatSessionMode" AS ENUM ('bot', 'agent');
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

ALTER TABLE "chat"."chat_sessions"
  ADD COLUMN IF NOT EXISTS "mode" "chat"."ChatSessionMode" NOT NULL DEFAULT 'bot';

-- Sessions whose ticket was already closed before this migration: the new
-- code enforces "ServiceRequest closed => ChatSession closed" going
-- forward (see ServiceRequestService.updateStatus's cascade) — backfill
-- existing rows to the same invariant rather than leaving them reachable
-- (status='open') with no ticket to show for it.
UPDATE "chat"."chat_sessions" cs
SET "mode" = 'agent',
    "status" = 'closed',
    "closedAt" = COALESCE(cs."closedAt", sr."closedAt", NOW())
FROM "chat"."service_requests" sr
WHERE cs."status" = 'escalated'
  AND sr."sessionId" = cs."id"
  AND sr."status" = 'closed';

-- Everything else that was escalated (ticket still active, or no ticket at
-- all) goes back to a normal open lifecycle, agent mode.
UPDATE "chat"."chat_sessions"
SET "mode" = 'agent',
    "status" = 'open'
WHERE "status" = 'escalated';

COMMIT;

-- Verify — should return zero rows: nothing left on the dead 'escalated' label.
SELECT id, status, mode FROM "chat"."chat_sessions" WHERE status = 'escalated';