-- Active: 1772601349048@@metro.proxy.rlwy.net@13239@railway
CREATE TABLE
    IF NOT EXISTS gateway_connection_registry_realtime (
        id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
        created_at timestamptz NOT NULL DEFAULT now (),
        registry_key text NOT NULL,
        client_name text NOT NULL,
        ipv4 text NOT NULL,
        athena_url text NOT NULL,
        api_key_public_id text,
        api_key_external_id text,
        api_key_internal_id bigint,
        api_key_authenticated boolean NOT NULL DEFAULT false,
        api_key_authorized boolean NOT NULL DEFAULT false,
        last_method text,
        last_path text,
        user_agent text,
        last_request_id text,
        last_status_code integer,
        total_hits bigint NOT NULL DEFAULT 1,
        first_seen_at timestamptz NOT NULL DEFAULT now (),
        last_seen_at timestamptz NOT NULL DEFAULT now (),
        updated_at timestamptz NOT NULL DEFAULT now (),
        PRIMARY KEY (id),
        CONSTRAINT gateway_connection_registry_realtime_registry_key_unique UNIQUE (registry_key)
    );

CREATE INDEX IF NOT EXISTS idx_gateway_connection_registry_realtime_client ON gateway_connection_registry_realtime (client_name);

CREATE INDEX IF NOT EXISTS idx_gateway_connection_registry_realtime_last_seen ON gateway_connection_registry_realtime (last_seen_at DESC);

CREATE INDEX IF NOT EXISTS idx_gateway_connection_registry_realtime_ip ON gateway_connection_registry_realtime (ipv4);

CREATE INDEX IF NOT EXISTS idx_gateway_connection_registry_realtime_url ON gateway_connection_registry_realtime (athena_url);