CREATE SCHEMA IF NOT EXISTS gateway;

CREATE TABLE IF NOT EXISTS gateway.outbox_events (
    event_id uuid PRIMARY KEY,
    event_type text NOT NULL,
    event_version integer NOT NULL CHECK (event_version > 0),
    tenant_id uuid,
    aggregate_type text NOT NULL,
    aggregate_id text NOT NULL,
    payload jsonb NOT NULL,
    trace_context jsonb NOT NULL DEFAULT '{}'::jsonb,
    occurred_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    available_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    attempts integer NOT NULL DEFAULT 0 CHECK (attempts >= 0),
    locked_at timestamptz,
    locked_by text,
    processed_at timestamptz,
    last_error text
);

CREATE INDEX IF NOT EXISTS outbox_events_pending_idx
    ON gateway.outbox_events (available_at, occurred_at)
    WHERE processed_at IS NULL;

CREATE TABLE IF NOT EXISTS gateway.event_consumptions (
    subscriber text NOT NULL,
    event_id uuid NOT NULL,
    consumed_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (subscriber, event_id)
);

CREATE TABLE IF NOT EXISTS gateway.api_keys (
    id uuid PRIMARY KEY,
    tenant_id uuid,
    name text NOT NULL,
    key_prefix varchar(16) NOT NULL,
    key_hash bytea NOT NULL,
    scopes text[] NOT NULL DEFAULT '{}',
    enabled boolean NOT NULL DEFAULT true,
    expires_at timestamptz,
    last_used_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    UNIQUE (key_prefix, key_hash)
);

CREATE INDEX IF NOT EXISTS api_keys_tenant_idx ON gateway.api_keys (tenant_id, enabled);

CREATE TABLE IF NOT EXISTS gateway.providers (
    id uuid PRIMARY KEY,
    tenant_id uuid,
    code text NOT NULL,
    adapter text NOT NULL,
    base_url text NOT NULL,
    encrypted_credentials bytea NOT NULL,
    credential_kek_version integer NOT NULL,
    capabilities text[] NOT NULL DEFAULT '{}',
    config jsonb NOT NULL DEFAULT '{}'::jsonb,
    enabled boolean NOT NULL DEFAULT true,
    revision bigint NOT NULL DEFAULT 1,
    created_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    updated_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    UNIQUE NULLS NOT DISTINCT (tenant_id, code)
);

CREATE TABLE IF NOT EXISTS gateway.audit_events (
    id uuid NOT NULL,
    tenant_id uuid,
    request_id text NOT NULL,
    actor_id uuid,
    api_key_id uuid,
    provider_code text,
    model text,
    protocol text NOT NULL,
    status_code integer,
    prompt_tokens bigint CHECK (prompt_tokens IS NULL OR prompt_tokens >= 0),
    completion_tokens bigint CHECK (completion_tokens IS NULL OR completion_tokens >= 0),
    cost_microunits bigint CHECK (cost_microunits IS NULL OR cost_microunits >= 0),
    latency_ms integer CHECK (latency_ms IS NULL OR latency_ms >= 0),
    request_preview text CHECK (request_preview IS NULL OR octet_length(request_preview) <= 65536),
    response_preview text CHECK (response_preview IS NULL OR octet_length(response_preview) <= 65536),
    labels jsonb NOT NULL DEFAULT '{}'::jsonb,
    recorded_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    PRIMARY KEY (id, recorded_at)
) PARTITION BY RANGE (recorded_at);

CREATE TABLE IF NOT EXISTS gateway.audit_events_default
    PARTITION OF gateway.audit_events DEFAULT;

CREATE INDEX IF NOT EXISTS audit_events_default_tenant_time_idx
    ON gateway.audit_events_default (tenant_id, recorded_at DESC);
CREATE INDEX IF NOT EXISTS audit_events_default_request_idx
    ON gateway.audit_events_default (request_id);

COMMENT ON TABLE gateway.audit_events IS
    'PostgreSQL baseline audit storage. Full unbounded bodies are deliberately not retained.';