-- =============================================================================
-- MIGRATION: Chat platform — initial schema
-- Run against: chat_bot (see 000_create_database.sql) — NOT the cart platform DB
-- Convention: matches prisma/migrations/manual/* in the main platform —
--   createdAt/updatedAt map to createdOn/updatedOn, ids are TEXT defaulting to
--   gen_random_uuid()::text, soft delete via deletedAt/deletedBy, explicit
--   constraint/index names, idempotent (safe to re-run).
-- Tenancy: every table (except chat_messages, which hangs off chat_sessions)
--   carries "applicationId" — the equivalent of the cart platform's "storeId".
--   No table here ever joins across two different applicationIds.
-- =============================================================================

BEGIN;

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

CREATE SCHEMA IF NOT EXISTS "chat";

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

DO $$ BEGIN
    CREATE TYPE "chat"."ApplicationStatus" AS ENUM ('active', 'suspended');
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE "chat"."ChatSessionStatus" AS ENUM ('open', 'closed', 'escalated');
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE "chat"."ChatMessageRole" AS ENUM ('user', 'assistant', 'system');
EXCEPTION
    WHEN duplicate_object THEN NULL;
END $$;

-- ===========================================================================
-- SECTION 2 — chat.applications
-- One row per product embedding the widget (storefront, LMS, timesheet app…)
-- ===========================================================================

CREATE TABLE IF NOT EXISTS "chat"."applications" (
    "id"             TEXT NOT NULL DEFAULT gen_random_uuid()::text,
    "slug"           TEXT NOT NULL,
    "name"           TEXT NOT NULL,
    "status"         "chat"."ApplicationStatus" NOT NULL DEFAULT 'active',
    "allowedOrigins" TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
    "settings"       JSONB NOT NULL DEFAULT '{}'::jsonb,
    "isActive"       BOOLEAN NOT NULL DEFAULT TRUE,
    "sortOrder"      INTEGER NOT NULL DEFAULT 0,
    "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,
    "deletedAt"      TIMESTAMPTZ,
    "deletedBy"      TEXT,

    CONSTRAINT pk_applications     PRIMARY KEY ("id"),
    CONSTRAINT uq_applications_slug UNIQUE ("slug")
);

COMMENT ON TABLE  "chat"."applications"           IS 'A product embedding the chat widget — the tenancy root for this DB';
COMMENT ON COLUMN "chat"."applications"."slug"    IS 'The appId passed to ChatWidget.init() — e.g. "storefront", "lms", "timesheet"';
COMMENT ON COLUMN "chat"."applications"."allowedOrigins" IS 'Origins allowed to embed this application''s widget; checked against postMessage origin';
COMMENT ON COLUMN "chat"."applications"."settings"       IS 'Per-application config: greeting, theme, active responder, n8n webhook URL, etc.';

-- ===========================================================================
-- SECTION 3 — chat.api_keys
-- Separate from applications so keys rotate/revoke independently
-- ===========================================================================

CREATE TABLE IF NOT EXISTS "chat"."api_keys" (
    "id"            TEXT NOT NULL DEFAULT gen_random_uuid()::text,
    "applicationId" TEXT NOT NULL,
    "keyHash"       TEXT NOT NULL,
    "label"         TEXT NOT NULL,
    "lastUsedAt"    TIMESTAMPTZ,
    "revokedAt"     TIMESTAMPTZ,
    "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,

    CONSTRAINT pk_api_keys              PRIMARY KEY ("id"),
    CONSTRAINT uq_api_keys_key_hash     UNIQUE ("keyHash"),
    CONSTRAINT fk_api_keys_application  FOREIGN KEY ("applicationId")
        REFERENCES "chat"."applications"("id") ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_api_keys_application_id ON "chat"."api_keys" ("applicationId");

COMMENT ON TABLE  "chat"."api_keys"           IS 'Hashed API keys, one application can have many (rotate/revoke independently)';
COMMENT ON COLUMN "chat"."api_keys"."keyHash" IS 'Hash only — the raw key is shown once at creation and never stored/retrievable';

-- ===========================================================================
-- SECTION 4 — chat.chat_sessions
-- One row per conversation. Guest-friendly: external* columns are all nullable.
-- ===========================================================================

CREATE TABLE IF NOT EXISTS "chat"."chat_sessions" (
    "id"                    TEXT NOT NULL DEFAULT gen_random_uuid()::text,
    "applicationId"         TEXT NOT NULL,
    "externalUserId"        TEXT,
    "externalUserName"      TEXT,
    "externalUserEmail"     TEXT,
    "externalIdentityGroup" TEXT,
    "status"                "chat"."ChatSessionStatus" NOT NULL DEFAULT 'open',
    "responderKey"          TEXT,
    "startedAt"             TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "lastMessageAt"         TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "closedAt"              TIMESTAMPTZ,
    "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,

    CONSTRAINT pk_chat_sessions             PRIMARY KEY ("id"),
    CONSTRAINT fk_chat_sessions_application FOREIGN KEY ("applicationId")
        REFERENCES "chat"."applications"("id") ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_chat_sessions_app_last_message
    ON "chat"."chat_sessions" ("applicationId", "lastMessageAt");
CREATE INDEX IF NOT EXISTS idx_chat_sessions_app_external_user
    ON "chat"."chat_sessions" ("applicationId", "externalUserId");

COMMENT ON TABLE  "chat"."chat_sessions"                    IS 'One conversation. externalUserId/Name/Email are only ever populated by identify() from the host app — never joined across applications';
COMMENT ON COLUMN "chat"."chat_sessions"."externalIdentityGroup" IS 'Nullable, unused by default — only set if a product explicitly opts into cross-product identity later';
COMMENT ON COLUMN "chat"."chat_sessions"."responderKey"          IS 'Which responder answered: "scripted" | "llm" | "n8n"';
COMMENT ON COLUMN "chat"."chat_sessions"."status"                IS 'escalated = handed to a human; no agent inbox built yet, but the state exists';

-- ===========================================================================
-- SECTION 5 — chat.chat_messages
-- ===========================================================================

CREATE TABLE IF NOT EXISTS "chat"."chat_messages" (
    "id"        TEXT NOT NULL DEFAULT gen_random_uuid()::text,
    "sessionId" TEXT NOT NULL,
    "role"      "chat"."ChatMessageRole" NOT NULL,
    "content"   TEXT NOT NULL,
    "metadata"  JSONB,
    "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,

    CONSTRAINT pk_chat_messages         PRIMARY KEY ("id"),
    CONSTRAINT fk_chat_messages_session FOREIGN KEY ("sessionId")
        REFERENCES "chat"."chat_sessions"("id") ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_chat_messages_session_created
    ON "chat"."chat_messages" ("sessionId", "createdOn");

COMMENT ON TABLE  "chat"."chat_messages"            IS 'Individual messages within a session, ordered by createdOn';
COMMENT ON COLUMN "chat"."chat_messages"."role"     IS '"system" is reserved for context/tool-call metadata — never rendered in the widget UI';
COMMENT ON COLUMN "chat"."chat_messages"."metadata" IS 'Flexible bucket: matched FAQ id, LLM token usage, n8n execution id, confidence score, etc.';

-- ===========================================================================
-- SECTION 6 — chat.knowledge_entries
-- FAQ/keyword content the v1 ScriptedChatResponder searches, per application
-- ===========================================================================

CREATE TABLE IF NOT EXISTS "chat"."knowledge_entries" (
    "id"            TEXT NOT NULL DEFAULT gen_random_uuid()::text,
    "applicationId" TEXT NOT NULL,
    "question"      TEXT NOT NULL,
    "answer"        TEXT NOT NULL,
    "keywords"      TEXT[] NOT NULL DEFAULT ARRAY[]::TEXT[],
    "isActive"      BOOLEAN NOT NULL DEFAULT TRUE,
    "sortOrder"     INTEGER NOT NULL DEFAULT 0,
    "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,

    CONSTRAINT pk_knowledge_entries             PRIMARY KEY ("id"),
    CONSTRAINT fk_knowledge_entries_application FOREIGN KEY ("applicationId")
        REFERENCES "chat"."applications"("id") ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_knowledge_entries_application ON "chat"."knowledge_entries" ("applicationId");

COMMENT ON TABLE "chat"."knowledge_entries" IS 'Per-application FAQ/keyword content; doubles as seed content for a future RAG-style responder';

COMMIT;