-- Custom routes bind a script to (host, method, path) tuples. The
-- orchestrator's in-memory matcher dispatches user-defined URLs to the
-- right script; the internal /api/v1/execute/{id} endpoint stays as a
-- bypass that always works.
--
-- host_kind / path_kind drive matching and conflict-detection semantics;
-- the orchestrator parses the stored TEXT into its internal pattern
-- representation on table load. See docs/versioning.md and the
-- orchestrator_core::routing module for the matching rules.
--
-- host_param_name is reserved for the future `{subdomain}.example.com`
-- syntax; the current parser only accepts `*.example.com` (wildcard,
-- no capture).
--
-- method = NULL means "any HTTP method"; NULL handling in the unique
-- index uses COALESCE so two "any" rows still conflict.

CREATE TABLE routes (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    script_id       UUID NOT NULL REFERENCES scripts(id) ON DELETE CASCADE,

    host_kind       TEXT NOT NULL CHECK (host_kind IN ('any', 'strict', 'wildcard')),
    host            TEXT NOT NULL DEFAULT '',
    host_param_name TEXT,

    path_kind       TEXT NOT NULL CHECK (path_kind IN ('exact', 'prefix', 'param')),
    path            TEXT NOT NULL,

    method          TEXT,

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Catch the easy case of two literally identical routes at the DB level.
-- Cross-pattern overlap (param vs exact for the same request shape) is
-- enforced in code by the conflict detector.
CREATE UNIQUE INDEX routes_unique_binding_idx
    ON routes (host_kind, host, path_kind, path, COALESCE(method, ''));

CREATE INDEX routes_lookup_idx ON routes (host_kind, host);
CREATE INDEX routes_script_id_idx ON routes (script_id);