ALTER TABLE gateway.api_keys
    ADD COLUMN IF NOT EXISTS application_id uuid REFERENCES gateway.applications(id) ON DELETE SET NULL;

CREATE INDEX IF NOT EXISTS api_keys_application_idx
    ON gateway.api_keys (application_id, enabled)
    WHERE application_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS gateway.application_runtime_credentials (
    application_id uuid NOT NULL REFERENCES gateway.applications(id) ON DELETE CASCADE,
    department_id uuid REFERENCES gateway.departments(id) ON DELETE CASCADE,
    api_key_id uuid NOT NULL UNIQUE REFERENCES gateway.api_keys(id) ON DELETE CASCADE,
    encrypted_key bytea NOT NULL,
    key_kek_version integer NOT NULL,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    UNIQUE NULLS NOT DISTINCT (application_id, department_id)
);

CREATE TABLE IF NOT EXISTS gateway.portal_conversations (
    id uuid PRIMARY KEY,
    portal_user_id uuid NOT NULL REFERENCES gateway.portal_users(id) ON DELETE CASCADE,
    application_id uuid NOT NULL REFERENCES gateway.applications(id) ON DELETE CASCADE,
    title text NOT NULL DEFAULT '新会话' CHECK (length(title) BETWEEN 1 AND 160),
    status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')),
    busy boolean NOT NULL DEFAULT false,
    busy_token uuid,
    busy_since timestamptz,
    next_sequence integer NOT NULL DEFAULT 1 CHECK (next_sequence BETWEEN 1 AND 202),
    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    updated_at timestamptz NOT NULL DEFAULT clock_timestamp()
);

CREATE INDEX IF NOT EXISTS portal_conversations_user_time_idx
    ON gateway.portal_conversations (portal_user_id, updated_at DESC);

CREATE TABLE IF NOT EXISTS gateway.portal_conversation_messages (
    id uuid PRIMARY KEY,
    conversation_id uuid NOT NULL REFERENCES gateway.portal_conversations(id) ON DELETE CASCADE,
    sequence integer NOT NULL CHECK (sequence BETWEEN 1 AND 200),
    role text NOT NULL CHECK (role IN ('user','assistant')),
    content text NOT NULL CHECK (length(content) BETWEEN 1 AND 100000),
    previous_hash char(64) NOT NULL,
    message_hash char(64) NOT NULL,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    UNIQUE (conversation_id, sequence)
);

COMMENT ON TABLE gateway.portal_conversation_messages IS
    'Server-managed immutable conversation history. A SHA-256 hash chain detects database-side sequence/content corruption before replay.';