-- Athena Auth: admin audit log and Athena client registry

CREATE TABLE IF NOT EXISTS athena_clients (
    id TEXT PRIMARY KEY,
    client_name TEXT NOT NULL UNIQUE,
    description TEXT,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    pg_uri_env_var TEXT,
    metadata JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_athena_clients_client_name
    ON athena_clients (client_name);

CREATE INDEX IF NOT EXISTS idx_athena_clients_is_active
    ON athena_clients (is_active);

CREATE TABLE IF NOT EXISTS admin_audit_logs (
    id TEXT PRIMARY KEY,
    actor_user_id TEXT REFERENCES users (id) ON DELETE SET NULL,
    actor_session_token TEXT,
    action TEXT NOT NULL,
    target_type TEXT,
    target_id TEXT,
    success BOOLEAN NOT NULL DEFAULT FALSE,
    error_message TEXT,
    metadata JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_admin_audit_logs_created_at
    ON admin_audit_logs (created_at DESC);

CREATE INDEX IF NOT EXISTS idx_admin_audit_logs_action
    ON admin_audit_logs (action);

CREATE INDEX IF NOT EXISTS idx_admin_audit_logs_actor_user_id
    ON admin_audit_logs (actor_user_id);
