-- t-paliad-186 / Fristen Phase 3 Slice 5 Step F-2 — enforce
-- "fristenrechner-category only" on paliad.projects.proceeding_type_id
-- via a BEFORE INSERT/UPDATE trigger. PostgreSQL CHECK constraints
-- can't reference other tables, so a trigger is the only way to
-- evaluate the (proceeding_types.category = 'fristenrechner')
-- predicate per row.
--
-- Why trigger over deferrable-FK-to-partial-index: a partial unique
-- index on proceeding_types where category='fristenrechner' would
-- let us reference it from a separate FK column, but the existing
-- FK on projects.proceeding_type_id → proceeding_types.id is
-- broad-category. Replacing it with a narrower FK would invalidate
-- the existing schema reference in mig 027. A trigger keeps the FK
-- in place and just adds the category predicate on top.
--
-- Behaviour:
--   - INSERT/UPDATE with proceeding_type_id IS NULL: pass (NULL is allowed).
--   - INSERT/UPDATE with proceeding_type_id pointing at a
--     fristenrechner-category row: pass.
--   - INSERT/UPDATE with proceeding_type_id pointing at any other
--     category: RAISE EXCEPTION with a German + English message so the
--     handler / frontend can surface a friendly error.
--   - INSERT/UPDATE with proceeding_type_id pointing at a missing row:
--     the existing FK on the column rejects it before this trigger
--     even fires; nothing to do here.
--
-- Removed when the litigation category is fully retired (Slice 9 or
-- later). Until then this is the runtime guard for any writer that
-- bypasses the Go service-layer validation.
--
-- Idempotent: re-applying the migration drops + recreates the trigger.

CREATE OR REPLACE FUNCTION paliad.projects_proceeding_type_category_check()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_category text;
BEGIN
    IF NEW.proceeding_type_id IS NULL THEN
        RETURN NEW;
    END IF;

    SELECT category INTO v_category
      FROM paliad.proceeding_types
     WHERE id = NEW.proceeding_type_id;

    -- The FK on the column guarantees v_category is non-NULL when the
    -- id resolves — but defensive against a future FK relax-and-replace.
    IF v_category IS NULL THEN
        RAISE EXCEPTION
            'paliad.projects.proceeding_type_id = % does not resolve to a '
            'proceeding_types row — FK constraint should have caught this.',
            NEW.proceeding_type_id;
    END IF;

    IF v_category <> 'fristenrechner' THEN
        RAISE EXCEPTION
            'paliad.projects.proceeding_type_id must reference a '
            'fristenrechner-category proceeding_types row (got category=''%''). '
            'Verfahrenstyp muss ein Fristenrechner-Typ sein (Kategorie=''%''). '
            'Slice 5 (Phase 3 soft-merge per design §3.F) retires the '
            '''litigation'' category for project-binding; pick a UPC_*, '
            'DE_*, EPA_*, DPMA_* or EP_GRANT code instead.',
            v_category, v_category;
    END IF;

    RETURN NEW;
END;
$$;

COMMENT ON FUNCTION paliad.projects_proceeding_type_category_check() IS
    'BEFORE INSERT/UPDATE trigger function enforcing the Phase 3 Slice 5 '
    'invariant: paliad.projects.proceeding_type_id may only reference '
    'fristenrechner-category proceeding_types rows. NULL is allowed.';

DROP TRIGGER IF EXISTS projects_proceeding_type_category_check
    ON paliad.projects;

CREATE TRIGGER projects_proceeding_type_category_check
    BEFORE INSERT OR UPDATE OF proceeding_type_id ON paliad.projects
    FOR EACH ROW
    EXECUTE FUNCTION paliad.projects_proceeding_type_category_check();

COMMENT ON TRIGGER projects_proceeding_type_category_check ON paliad.projects IS
    'Phase 3 Slice 5 (t-paliad-186) runtime guard for the projects '
    'soft-merge — rejects any INSERT/UPDATE that would bind a project '
    'to a non-fristenrechner-category proceeding_type. The Go service '
    'layer also enforces this with a typed error; this trigger is the '
    'defence-in-depth backstop.';
