CREATE INDEX IF NOT EXISTS audit_events_recorded_at_idx
    ON gateway.audit_events (recorded_at DESC);
CREATE INDEX IF NOT EXISTS audit_events_api_key_time_idx
    ON gateway.audit_events (api_key_id, recorded_at DESC)
    WHERE api_key_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS audit_events_provider_model_time_idx
    ON gateway.audit_events (provider_code, model, recorded_at DESC);

CREATE TABLE IF NOT EXISTS gateway.usage_daily (
    usage_date date NOT NULL,
    api_key_id uuid NOT NULL REFERENCES gateway.api_keys(id),
    provider_code text NOT NULL DEFAULT '',
    model text NOT NULL DEFAULT '',
    requests bigint NOT NULL DEFAULT 0 CHECK (requests >= 0),
    failed_requests bigint NOT NULL DEFAULT 0 CHECK (failed_requests >= 0),
    prompt_tokens bigint NOT NULL DEFAULT 0 CHECK (prompt_tokens >= 0),
    completion_tokens bigint NOT NULL DEFAULT 0 CHECK (completion_tokens >= 0),
    updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (usage_date, api_key_id, provider_code, model)
);

CREATE INDEX IF NOT EXISTS usage_daily_date_idx
    ON gateway.usage_daily (usage_date DESC, provider_code, model);

COMMENT ON TABLE gateway.usage_daily IS
    'PostgreSQL daily usage aggregates produced asynchronously from gateway audit events.';