CREATE TABLE IF NOT EXISTS public_gateway_routes (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    public_gateway_route_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    route_key text NOT NULL,
    client_name text NOT NULL,
    allowed_ops text[] NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_public_gateway_routes_route_key_active
    ON public_gateway_routes (lower(route_key))
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_public_gateway_routes_client_name_active
    ON public_gateway_routes (lower(client_name))
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_public_gateway_routes_is_active
    ON public_gateway_routes (is_active)
    WHERE deleted_at IS NULL;
