-- Route-level request logging for wildcard host routing and public route dispatch.
CREATE TABLE IF NOT EXISTS public.gateway_route_request_log (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    request_id text NOT NULL,
    source text NOT NULL,
    outcome text NOT NULL,
    method text,
    path text,
    host text,
    wildcard_host_pattern text,
    wildcard_prefix text,
    route_key text,
    public_op text,
    resolved_client text,
    status_code integer,
    duration_ms bigint,
    error_message text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    time numeric NOT NULL,
    PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS idx_gateway_route_request_log_created_at
    ON public.gateway_route_request_log (created_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_route_request_log_request_id
    ON public.gateway_route_request_log (request_id);

CREATE INDEX IF NOT EXISTS idx_gateway_route_request_log_source_outcome
    ON public.gateway_route_request_log (source, outcome, created_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_route_request_log_route_key_created_at
    ON public.gateway_route_request_log (route_key, created_at DESC)
    WHERE route_key IS NOT NULL;

CREATE INDEX IF NOT EXISTS idx_gateway_route_request_log_host_created_at
    ON public.gateway_route_request_log (host, created_at DESC)
    WHERE host IS NOT NULL;
