CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE SCHEMA IF NOT EXISTS athena;

CREATE TABLE IF NOT EXISTS athena.chat_session_auth_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    chat_session_auth_log_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    created_at timestamptz NOT NULL DEFAULT now(),
    request_id text NOT NULL,
    client_name text,
    transport text NOT NULL,
    method text NOT NULL,
    path text NOT NULL,
    auth_source text,
    outcome text NOT NULL,
    failure_reason text,
    user_id text,
    session_id text,
    organization_id text,
    remote_addr text,
    user_agent text,
    details jsonb NOT NULL DEFAULT '{}'::jsonb,
    CONSTRAINT chat_session_auth_log_transport_check
        CHECK (transport IN ('http', 'wss')),
    CONSTRAINT chat_session_auth_log_outcome_check
        CHECK (
            outcome IN (
                'authenticated',
                'missing_session_token',
                'invalid_session',
                'missing_organization',
                'auth_store_unavailable'
            )
        ),
    CONSTRAINT chat_session_auth_log_auth_source_check
        CHECK (
            auth_source IS NULL
            OR auth_source IN ('authorization_bearer', 'cookie')
        ),
    PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS idx_chat_session_auth_log_created_at
    ON athena.chat_session_auth_log (created_at DESC);

CREATE INDEX IF NOT EXISTS idx_chat_session_auth_log_request_id
    ON athena.chat_session_auth_log (request_id);

CREATE INDEX IF NOT EXISTS idx_chat_session_auth_log_client_created_at
    ON athena.chat_session_auth_log (client_name, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_chat_session_auth_log_outcome_created_at
    ON athena.chat_session_auth_log (outcome, created_at DESC);
