-- 153_proceeding_types_kind — t-paliad-325 / m/paliad#147
--
-- Purpose: tag every paliad.proceeding_types row with a structural
-- classification so the Mode B R3 wizard (Fristenrechner overhaul,
-- m/paliad#146), the projects.proceeding_type_id binding, and the
-- pkg/litigationplanner snapshot can filter to primary proceedings
-- only — separating self-contained matters from CFI phases,
-- in-proceeding side-actions, and cross-cutting RoP/admin rows.
--
-- Design: docs/design-proceeding-types-taxonomy-2026-05-26.md
-- §0–§10 (m ratified 2026-05-27 09:52 via 11-question AskUserQuestion
-- batch; "proceed, sure" greenlight at 09:57).
--
-- This mig is purely additive: ALTER TABLE adds the kind column with
-- a safe DEFAULT, UPDATEs reclassify the 23 non-primary rows, and a
-- BEFORE INSERT/UPDATE trigger backstops the new
-- "projects.proceeding_type_id must point at kind='proceeding'"
-- invariant. The 23 rows being reclassified have zero downstream
-- consumers today (0 active sequencing_rules anchor, 0 spawn, 0
-- projects bind, 0 event_category_concepts reference) so no FK
-- reparenting is needed — verified via Supabase MCP 2026-05-27
-- before write.
--
-- Hard constraints honoured (mirrors precedent migs 091/093/095/098/
-- 140/151/152):
--   * No deletions. Non-primary rows flip is_active=false but stay in
--     the table for audit + future re-activation.
--   * Snapshot the affected proceeding_types into
--     paliad.proceeding_types_pre_153 in the same TX.
--   * set_config('paliad.audit_reason') is defensively called even
--     though no audit trigger fires on proceeding_types today; a
--     future audit trigger would inherit the reason automatically.
--   * Idempotent on re-apply — the ADD COLUMN uses IF NOT EXISTS
--     semantics through golang-migrate's tracker (mig only fires
--     once); the UPDATEs only touch rows that match the explicit ID
--     list from the ratified design §3.2 / §10.2.

BEGIN;

SELECT set_config(
    'paliad.audit_reason',
    'mig 153: proceeding_types kind discriminator (m/paliad#147)',
    true
);

-- ----------------------------------------------------------------
-- 1. Snapshot the pre-mig state for audit + rollback safety.
--    Mirrors precedent: sequencing_rules_pre_151/_pre_152,
--    procedural_events_pre_151.
-- ----------------------------------------------------------------

CREATE TABLE paliad.proceeding_types_pre_153 AS
SELECT * FROM paliad.proceeding_types;

COMMENT ON TABLE paliad.proceeding_types_pre_153 IS
    'Snapshot of paliad.proceeding_types taken in the same TX as '
    'mig 153 (kind discriminator). Audit + rollback safety per the '
    'precedent set by migs 091/093/095/098/140/151/152. Drop only '
    'when the kind taxonomy has held in prod for at least one '
    'release cycle and no rollback is anticipated.';

-- ----------------------------------------------------------------
-- 2. Add the kind column.
-- ----------------------------------------------------------------

ALTER TABLE paliad.proceeding_types
    ADD COLUMN kind text NOT NULL DEFAULT 'proceeding'
        CHECK (kind IN ('proceeding', 'phase', 'side_action', 'meta'));

COMMENT ON COLUMN paliad.proceeding_types.kind IS
    'Structural classification — see docs/design-proceeding-types-taxonomy-2026-05-26.md §1. '
    'proceeding = self-contained matter (own filing + deadline tree); '
    'phase = stage inside a primary CFI proceeding; '
    'side_action = application/order inside a proceeding; '
    'meta = RoP mechanics, court admin, cross-cutting remedies.';

CREATE INDEX proceeding_types_kind_active_idx
    ON paliad.proceeding_types(kind, is_active)
    WHERE is_active = true;

-- ----------------------------------------------------------------
-- 3. Reclassify the 23 non-primary rows.
--    IDs per ratified design §3.2 / §10.2. m's Q2 carve-out keeps
--    upc.costs.cfi (176) as kind='proceeding' (defaults to that);
--    Q3.b keeps upc.pl.cfi (188) as kind='proceeding' (defaults).
-- ----------------------------------------------------------------

-- 3.1 Phases: 4 rows (Q2 carve-out drops upc.costs.cfi from the original 5).
UPDATE paliad.proceeding_types
    SET kind = 'phase'
    WHERE id IN (173, 174, 175, 185);

-- 3.2 Side-actions: 10 rows (§0.4 Group C).
UPDATE paliad.proceeding_types
    SET kind = 'side_action'
    WHERE id IN (178, 182, 177, 184, 165, 170, 180, 181, 187, 183);

-- 3.3 Meta / cross-cutting: 9 rows (§0.4 Group D incl. upc.reestablishment.rop).
UPDATE paliad.proceeding_types
    SET kind = 'meta'
    WHERE id IN (162, 161, 163, 168, 164, 166, 167, 186, 169);

-- 3.4 Defensive integrity check — every reclassified ID must have been
--     reached. If the live table drifted between design (2026-05-26)
--     and apply, this raises before the trigger ships.
DO $$
DECLARE
    expected int := 23;
    actual int;
BEGIN
    SELECT COUNT(*) INTO actual
      FROM paliad.proceeding_types
     WHERE kind <> 'proceeding';
    IF actual <> expected THEN
        RAISE EXCEPTION
            '[mig 153] expected % rows reclassified to non-proceeding kind, found % — '
            'live IDs drifted from the design. Abort.',
            expected, actual;
    END IF;
    RAISE NOTICE '[mig 153] reclassified % rows: 4 phase + 10 side_action + 9 meta', actual;
END $$;

-- ----------------------------------------------------------------
-- 4. Per m's Q9: deactivate the non-primary rows so the admin list
--    surfaces only primaries. The kind column carries the semantic
--    info; is_active controls UI visibility. Reversible — flip
--    is_active back on if a row gains corpus.
-- ----------------------------------------------------------------

UPDATE paliad.proceeding_types
    SET is_active = false
    WHERE kind IN ('phase', 'side_action', 'meta');

-- ----------------------------------------------------------------
-- 5. Backstop trigger on projects.proceeding_type_id (§3.3 + Q8).
--    Complements mig 088's category check; rejects any
--    INSERT/UPDATE that would bind a project to a non-proceeding
--    kind. Independent from the category trigger so each invariant
--    can be dropped in isolation.
-- ----------------------------------------------------------------

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

    SELECT kind INTO v_kind
      FROM paliad.proceeding_types
     WHERE id = NEW.proceeding_type_id;

    IF v_kind IS NULL THEN
        -- FK should have caught this; defensive for any future FK relax.
        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_kind <> 'proceeding' THEN
        RAISE EXCEPTION
            'paliad.projects.proceeding_type_id must reference a kind=''proceeding'' '
            'proceeding_types row (got kind=''%''). '
            'Verfahrenstyp muss ein primäres Verfahren sein (kind=''%''). '
            'Phasen, Nebenanträge und RoP-Querschnittsregeln sind keine '
            'wählbaren Projekt-Verfahrenstypen.',
            v_kind, v_kind
        USING ERRCODE = '23514';
    END IF;

    RETURN NEW;
END;
$$;

COMMENT ON FUNCTION paliad.projects_proceeding_type_kind_check() IS
    'BEFORE INSERT/UPDATE trigger function enforcing the mig 153 '
    'invariant: paliad.projects.proceeding_type_id may only '
    'reference kind=''proceeding'' proceeding_types rows. NULL is '
    'allowed. Complements mig 088''s category check.';

DROP TRIGGER IF EXISTS projects_proceeding_type_kind_check
    ON paliad.projects;

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

COMMENT ON TRIGGER projects_proceeding_type_kind_check ON paliad.projects IS
    'mig 153 (t-paliad-325 / m/paliad#147) runtime guard — rejects '
    'any INSERT/UPDATE that would bind a project to a phase/'
    'side_action/meta proceeding_types row. The Go service layer '
    'also enforces this with a typed error; this trigger is the '
    'defence-in-depth backstop.';

COMMIT;
