-- Gateway request log table stores HTTP metadata for each proxied request.
CREATE TABLE IF NOT EXISTS gateway_request_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    request_id text NOT NULL,
    client text,
    method text,
    path text,
    query_string text,
    status_code integer,
    ipv4 text,
    user_agent text,
    headers jsonb,
    body jsonb,
    user_id text,
    company_id text,
    organization_id text,
    api_key_id text,
    presented_api_key_public_id text,
    presented_api_key_hash text,
    presented_api_key_salt text,
    api_key_authenticated boolean DEFAULT false,
    api_key_authorized boolean DEFAULT false,
    api_key_enforced boolean DEFAULT false,
    api_key_auth_reason text,
    host text,
    cached boolean,
    time numeric NOT NULL,
    PRIMARY KEY (id)
);

-- Gateway operation log captures insert/fetch/update/delete/query/pipeline events.
CREATE TABLE IF NOT EXISTS gateway_operation_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    request_id text NOT NULL,
    operation text,
    table_name text,
    client text,
    method text,
    path text,
    status_code integer,
    duration_ms bigint,
    details jsonb,
    time numeric NOT NULL,
    error boolean DEFAULT false,
    message text,
    cache_key text,
    PRIMARY KEY (id)
);

-- API key auth attempts capture both successful and failed gateway authorization decisions.
CREATE TABLE IF NOT EXISTS api_key_auth_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    request_id text NOT NULL,
    api_key_id text,
    api_key_public_id text,
    client_name text,
    method text,
    path text,
    presented_key_hash text,
    presented_key_salt text,
    required_rights jsonb,
    granted_rights jsonb,
    authenticated boolean NOT NULL DEFAULT false,
    authorized boolean NOT NULL DEFAULT false,
    enforced boolean NOT NULL DEFAULT false,
    failure_reason text,
    remote_addr text,
    user_agent text,
    time numeric NOT NULL,
    PRIMARY KEY (id)
);
