-- =============================================================================
-- MIGRATION: Service Requests — ticketing layer on top of chat sessions
-- Run against: chat_bot (see 000_create_database.sql)
-- Convention: matches 001/002/003 in this same manual/ directory.
--
-- A ServiceRequest is a strict 1:1 wrapper around a ChatSession, created at
-- escalation time. It carries the ticket-workflow concepts the raw session
-- never had: a human-readable number, status (Open/Assigned/In Progress/
-- In Review/Closed/Reopened), priority, category, and agent assignment.
-- "One active SR per customer per application" is enforced in application
-- code (ChatService.escalate()), not by a DB constraint.
--
-- sr_number starts at 10000 to match the "SR-10024"-style numbering already
-- shown to the business as the target UX.
-- =============================================================================

BEGIN;

-- ===========================================================================
-- SECTION 1 — ENUMS
-- ===========================================================================

DO $$ BEGIN
    CREATE TYPE "chat"."ServiceRequestStatus" AS ENUM
        ('open', 'assigned', 'in_progress', 'in_review', 'closed', 'reopened');
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE "chat"."ServiceRequestPriority" AS ENUM ('low', 'medium', 'high', 'critical');
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE "chat"."ServiceRequestCategory" AS ENUM ('order', 'delivery', 'payment', 'general');
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE "chat"."ServiceRequestEventType" AS ENUM
        ('created', 'assigned', 'status_changed', 'transferred', 'note_added', 'closed', 'reopened');
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

-- ===========================================================================
-- SECTION 2 — chat.service_requests
-- ===========================================================================

CREATE SEQUENCE IF NOT EXISTS "chat"."service_requests_sr_number_seq" START WITH 10000;

CREATE TABLE IF NOT EXISTS "chat"."service_requests" (
    "id"                TEXT NOT NULL DEFAULT gen_random_uuid()::text,
    "sr_number"         INTEGER NOT NULL DEFAULT nextval('"chat"."service_requests_sr_number_seq"'),
    "applicationId"     TEXT NOT NULL,
    "sessionId"         TEXT NOT NULL,
    "externalUserId"    TEXT,
    "externalUserName"  TEXT,
    "externalUserEmail" TEXT,
    "status"            "chat"."ServiceRequestStatus" NOT NULL DEFAULT 'open',
    "priority"          "chat"."ServiceRequestPriority" NOT NULL DEFAULT 'medium',
    "category"          "chat"."ServiceRequestCategory" NOT NULL DEFAULT 'general',
    "assignedAgentId"   TEXT,
    "assignedAgentName" TEXT,
    "createdBy"         TEXT NOT NULL DEFAULT 'system',
    "createdOn"         TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedBy"         TEXT NOT NULL DEFAULT 'system',
    "updatedOn"         TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "closedAt"          TIMESTAMPTZ,

    CONSTRAINT pk_service_requests               PRIMARY KEY ("id"),
    CONSTRAINT uq_service_requests_sr_number     UNIQUE ("sr_number"),
    CONSTRAINT uq_service_requests_session       UNIQUE ("sessionId"),
    CONSTRAINT fk_service_requests_application   FOREIGN KEY ("applicationId")
        REFERENCES "chat"."applications"("id") ON DELETE CASCADE,
    CONSTRAINT fk_service_requests_session       FOREIGN KEY ("sessionId")
        REFERENCES "chat"."chat_sessions"("id") ON DELETE CASCADE
);

ALTER SEQUENCE "chat"."service_requests_sr_number_seq" OWNED BY "chat"."service_requests"."sr_number";

CREATE INDEX IF NOT EXISTS idx_service_requests_application
    ON "chat"."service_requests" ("applicationId");
CREATE INDEX IF NOT EXISTS idx_service_requests_app_external_user
    ON "chat"."service_requests" ("applicationId", "externalUserId");

COMMENT ON TABLE  "chat"."service_requests"                IS 'Ticket wrapper around a ChatSession, 1:1, created at escalation time';
COMMENT ON COLUMN "chat"."service_requests"."sr_number"    IS 'Human-readable ticket number, displayed as "SR-{sr_number}"';
COMMENT ON COLUMN "chat"."service_requests"."assignedAgentId" IS 'The JWT sub (user id) of the agent this SR is assigned to; null = unassigned/Open';

-- ===========================================================================
-- SECTION 3 — chat.service_request_events (append-only timeline)
-- ===========================================================================

CREATE TABLE IF NOT EXISTS "chat"."service_request_events" (
    "id"               TEXT NOT NULL DEFAULT gen_random_uuid()::text,
    "serviceRequestId" TEXT NOT NULL,
    "type"             "chat"."ServiceRequestEventType" NOT NULL,
    "fromValue"        TEXT,
    "toValue"          TEXT,
    "actorId"          TEXT,
    "actorName"        TEXT,
    "createdOn"        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_service_request_events PRIMARY KEY ("id"),
    CONSTRAINT fk_service_request_events_sr FOREIGN KEY ("serviceRequestId")
        REFERENCES "chat"."service_requests"("id") ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_service_request_events_sr
    ON "chat"."service_request_events" ("serviceRequestId", "createdOn");

COMMENT ON TABLE "chat"."service_request_events" IS 'Append-only audit trail rendered as the Service Request Timeline in the admin UI';

-- ===========================================================================
-- SECTION 4 — chat.service_request_notes (internal, agent-only)
-- ===========================================================================

CREATE TABLE IF NOT EXISTS "chat"."service_request_notes" (
    "id"               TEXT NOT NULL DEFAULT gen_random_uuid()::text,
    "serviceRequestId" TEXT NOT NULL,
    "authorId"         TEXT NOT NULL,
    "authorName"       TEXT NOT NULL,
    "content"          TEXT NOT NULL,
    "createdOn"        TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT pk_service_request_notes PRIMARY KEY ("id"),
    CONSTRAINT fk_service_request_notes_sr FOREIGN KEY ("serviceRequestId")
        REFERENCES "chat"."service_requests"("id") ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_service_request_notes_sr
    ON "chat"."service_request_notes" ("serviceRequestId", "createdOn");

COMMENT ON TABLE "chat"."service_request_notes" IS 'Agent-only notes, never visible to the shopper-facing widget — kept separate from chat_messages entirely';

COMMIT;