CREATE TABLE IF NOT EXISTS public.public_service_routes (
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL DEFAULT now(),
    public_service_route_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    route_key text NOT NULL,
    service_key text NOT NULL,
    target_kind text NOT NULL,
    local_path_prefix text,
    target_url text,
    is_active boolean NOT NULL DEFAULT true,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    updated_at timestamptz NOT NULL DEFAULT now(),
    deleted_at timestamptz,
    CONSTRAINT public_service_routes_target_kind_chk
        CHECK (target_kind IN ('local_path', 'http_upstream')),
    CONSTRAINT public_service_routes_target_shape_chk
        CHECK (
            (target_kind = 'local_path' AND local_path_prefix IS NOT NULL AND btrim(local_path_prefix) <> '')
            OR (target_kind = 'http_upstream' AND target_url IS NOT NULL AND btrim(target_url) <> '')
        ),
    PRIMARY KEY (id)
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_public_service_routes_route_service_active
    ON public.public_service_routes (lower(route_key), lower(service_key))
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_public_service_routes_service_key_active
    ON public.public_service_routes (lower(service_key))
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_public_service_routes_is_active
    ON public.public_service_routes (is_active)
    WHERE deleted_at IS NULL;
