-- §11 tail (v1.2 Hierarchies): group ROUTE templates.
--
-- Until now every route was owned by exactly one app (`app_id NOT NULL`).
-- A group-owned route is a TEMPLATE: it is never served directly, but the
-- in-memory RouteTable rebuild EXPANDS it into every descendant app's slice
-- via the ancestor chain (live resolution, like vars/secrets/scripts, §11.6
-- collections, and §11 trigger templates — no per-app materialized rows). It
-- binds a group-owned handler script and runs under each firing app's
-- `app_id`.
--
-- Unlike triggers (a SQL-per-event dispatch), routes are served from an
-- in-memory cache, so the inheritance is resolved at table-rebuild time, not
-- per request. The reshape, however, is identical: a polymorphic owner column.
--
-- Reshape mirrors 0056_group_triggers exactly (RESTRICT, not CASCADE — a route
-- template references a group script; a group can't be deleted out from under
-- it):
--   * `group_id` — nullable FK→groups, ON DELETE RESTRICT.
--   * `app_id`   — made nullable; the exactly-one CHECK enforces app XOR group.
--   * the per-app unique binding index becomes per-owner partials.

ALTER TABLE routes
    ADD COLUMN group_id UUID REFERENCES groups(id) ON DELETE RESTRICT;

ALTER TABLE routes
    ALTER COLUMN app_id DROP NOT NULL;

ALTER TABLE routes
    ADD CONSTRAINT routes_owner_exactly_one
        CHECK ((group_id IS NULL) <> (app_id IS NULL));

-- Per-owner binding uniqueness (partial so each owner column only constrains
-- its own rows). Existing app rows keep the exact binding-tuple uniqueness; a
-- group can't declare two identical templates. An app declaring a binding
-- identical to an inherited group template is a deliberate SHADOW, resolved
-- at rebuild (nearest-owner-wins) — not a DB conflict.
DROP INDEX routes_unique_binding_idx;
CREATE UNIQUE INDEX routes_unique_binding_idx
    ON routes (app_id, host_kind, host, path_kind, path, COALESCE(method, ''))
    WHERE app_id IS NOT NULL;
CREATE UNIQUE INDEX routes_group_binding_idx
    ON routes (group_id, host_kind, host, path_kind, path, COALESCE(method, ''))
    WHERE group_id IS NOT NULL;

-- The expansion join matches routes by owner (`r.app_id = ac.owner_app OR
-- r.group_id = ac.owner_group`). The app side already has routes_app_id_idx;
-- add the parallel group lookup index.
CREATE INDEX routes_group_id_idx ON routes (group_id) WHERE group_id IS NOT NULL;