-- t-paliad-200 / Fristen Phase 3 Slice 9 follow-up B — retire the
-- 'litigation' category from the rule corpus.
--
-- Lorenz's Slice 9 (t-paliad-195) deferred this drop because 40 active
-- paliad.deadline_rules still pointed at the 7 litigation-category
-- proceeding_types (INF, REV, CCR, APM, APP, AMD, ZPO_CIVIL). Phase 3
-- Slice 5 retired litigation codes from project-binding (mig 087/088);
-- this migration retires them from the rule corpus.
--
-- Plan choice (audit-gated, paliadin-approved): archive-all-40 rather
-- than the original re-parent plan. The audit found:
--
--   * 23 of 40 Pipeline-A litigation rules share their `code` with an
--     existing fristenrechner rule on the proposed re-parent target
--     (e.g. `inf.oral` exists on both INF and UPC_INF). Re-parenting
--     would leave two rules with identical (proceeding_type_id, code),
--     breaking the implicit per-proceeding rule_code identity contract
--     keyed off by projection / search / rule_editor.
--   * The fristenrechner-category rules are the production version:
--     proper German names, legal_source pinned (UPC.RoP citations),
--     full bilateral chains, intra-proceeding counterclaim handling
--     via inf.def_to_ccr / rev.cc_inf / etc. The Pipeline-A rules are
--     stubs: English-only, mostly NULL legal_source, duration_value=0
--     for 28 of 40, no spawn_proceeding_type_id wiring.
--   * 1 live deadline ("Lecker Frist", status=completed) points at
--     Pipeline-A inf.rejoin/INF via paliad.deadlines.rule_id. Archive-
--     not-delete preserves the FK.
--   * 30 intra-litigation parent_id chains would be silently broken by
--     piecemeal re-parenting. Archive-all preserves them.
--   * FK on deadline_rules.proceeding_type_id is ON DELETE CASCADE →
--     proceeding_types(id). A naive DELETE of the 7 litigation rows
--     would cascade-delete all 40 rules AND break the live deadline's
--     rule_id FK. Rules must be moved off the litigation pt ids before
--     the litigation rows are dropped.
--
-- Surfaced for legal review at merge (commit body lists these so they
-- don't get lost as the four open coverage questions Phase 3 leaves
-- behind):
--
--   1. inf.prelim (Preliminary Objection, RoP 19, 1 month) — not
--      present on UPC_INF. Possible coverage gap for the fristenrechner
--      ruleset; legal review to decide whether to add it.
--   2. inf.appeal / rev.appeal / ccr.appeal as cross-proceeding spawns
--      into UPC_APP (2 months, UPC.RoP.220.1) — fristenrechner UPC_APP
--      currently starts standalone with no spawn from UPC_INF/UPC_REV.
--      Possible UX gap; the Pipeline-A versions had
--      spawn_proceeding_type_id=NULL so they weren't functional
--      spawns either.
--   3. ccr.amend / rev.amend (spawn rules) — superseded by
--      inf.app_to_amend / rev.app_to_amend on UPC_INF / UPC_REV. Safe
--      to drop.
--   4. zpo.klage / zpo.vertanz / zpo.klageerw / zpo.berufung — no UPC
--      analogue; redundant with DE_INF / DE_INF_OLG / DE_INF_BGH and
--      DE_NULL / DE_NULL_BGH. Safe to drop.
--
-- Sequencing — every step required for the drop to be safe:
--
--   1. Snapshot paliad.proceeding_types and the 40 affected
--      paliad.deadline_rules into _pre_093 audit tables.
--   2. Create a holding proceeding_type `_archived_litigation`
--      (category='archived', is_active=false, jurisdiction='UPC') to
--      home the archived rules and preserve their intra-set parent_id
--      chains across the drop.
--   3. UPDATE all 40 rules: proceeding_type_id = archived_id,
--      lifecycle_state='archived', is_active=false. The mig 079
--      trigger captures every row in paliad.deadline_rule_audit.
--   4. DELETE the 7 litigation rows from paliad.proceeding_types
--      (now safe — nothing references them).
--   5. Hard assertions: zero rules on litigation ids, zero litigation
--      rows surviving, exactly 40 rules on the archive id.
--
-- Idempotent: re-applying is a no-op (snapshots use CREATE TABLE IF
-- NOT EXISTS; the archive pt INSERT uses ON CONFLICT DO NOTHING; the
-- UPDATEs are guarded by lifecycle_state='archived' so they only fire
-- once; the DELETE targets category='litigation' which becomes empty
-- after first run).
--
-- audit_reason wrapper at top — the mig 079 trigger on
-- paliad.deadline_rules logs every row-level edit. The UPDATE on all
-- 40 rules fires through that trigger, so the reason persists in
-- paliad.deadline_rule_audit for forever-grade audit.

SELECT set_config(
    'paliad.audit_reason',
    'mig 093: retire litigation category from rule corpus — archive 40 Pipeline-A rules under _archived_litigation pt, drop 7 litigation proceeding_types rows (t-paliad-200, Slice 9 follow-up B)',
    true);

-- =============================================================================
-- 1. Backup snapshots. CREATE TABLE IF NOT EXISTS keeps the migration
--    idempotent across reapplications. Snapshots persist post-drop as
--    the permanent audit anchor; the down migration restores from them.
-- =============================================================================

CREATE TABLE IF NOT EXISTS paliad.proceeding_types_pre_093 AS
SELECT *, now() AS snapshotted_at
  FROM paliad.proceeding_types
 WHERE category = 'litigation';

COMMENT ON TABLE paliad.proceeding_types_pre_093 IS
    'Snapshot of the 7 litigation-category paliad.proceeding_types rows '
    '(INF, REV, CCR, APM, APP, AMD, ZPO_CIVIL) before mig 093 dropped '
    'them. Source-of-truth for the down migration; persists post-drop '
    'as the permanent audit record of the Pipeline-A proceeding '
    'inventory. Drop with a focused follow-up after the Phase 3 cleanup '
    'is verified in prod.';

CREATE TABLE IF NOT EXISTS paliad.deadline_rules_pre_093 AS
SELECT dr.*, now() AS snapshotted_at
  FROM paliad.deadline_rules dr
  JOIN paliad.proceeding_types pt ON pt.id = dr.proceeding_type_id
 WHERE pt.category = 'litigation';

COMMENT ON TABLE paliad.deadline_rules_pre_093 IS
    'Snapshot of the 40 paliad.deadline_rules rows that pointed at '
    'litigation-category proceeding_types before mig 093 re-homed '
    'them under the _archived_litigation pt. Source-of-truth for the '
    'down migration; persists post-drop as the permanent audit record '
    'of the Pipeline-A rule corpus.';

-- =============================================================================
-- 2. Create the holding proceeding_type `_archived_litigation`. Category
--    is the new 'archived' bucket (non-fristenrechner, so it cannot be
--    selected from any UI that filters category='fristenrechner', and
--    the mig 088 trigger continues to reject project-binding to it).
--    is_active=false so it doesn't appear in admin lists.
--
--    sort_order = 9999 to sit at the tail of any category sort. The
--    INSERT is idempotent via ON CONFLICT (code) DO NOTHING.
-- =============================================================================

INSERT INTO paliad.proceeding_types
    (code, name, name_en, description, jurisdiction, category,
     default_color, sort_order, display_order, is_active)
VALUES
    ('_archived_litigation',
     'Archivierte Litigation-Regeln (Pipeline A)',
     'Archived litigation rules (Pipeline A)',
     'Holding proceeding_type for the 40 Pipeline-A litigation-category '
     'rules retired by mig 093 (t-paliad-200, Slice 9 follow-up B). Not '
     'selectable from any UI; preserves the rules + their 30 intra-set '
     'parent_id chains for audit, and keeps the FK valid for the one '
     'live deadline that still references inf.rejoin/INF.',
     'UPC',
     'archived',
     '#94a3b8',
     9999,
     9999,
     false)
ON CONFLICT (code) DO NOTHING;

-- =============================================================================
-- 3. Re-home all 40 rules to the archive pt and mark them archived.
--    The mig 079 trigger requires a non-empty audit_reason for UPDATE;
--    set_config above provides it. lifecycle_state='archived' +
--    is_active=false means projection_service / fristenrechner /
--    rule_editor filter them out by default. The intra-set parent_id
--    chains (30 of them) are preserved verbatim — parent_id values
--    point at the rule UUIDs which don't change.
--
--    Guard the UPDATE on lifecycle_state <> 'archived' so a second
--    application of the migration is a no-op (the rules are already
--    archived on the second run).
-- =============================================================================

UPDATE paliad.deadline_rules dr
   SET proceeding_type_id = (SELECT id FROM paliad.proceeding_types
                              WHERE code = '_archived_litigation'),
       lifecycle_state    = 'archived',
       is_active          = false,
       updated_at         = now()
  FROM paliad.proceeding_types pt
 WHERE pt.id = dr.proceeding_type_id
   AND pt.category = 'litigation'
   AND dr.lifecycle_state <> 'archived';

-- =============================================================================
-- 4. Drop the 7 litigation rows from paliad.proceeding_types. Nothing
--    references them now: step 3 moved all 40 rules off; mig 087 moved
--    every project off; the audit confirmed zero cross-category spawn /
--    parent references. The FK is ON DELETE CASCADE but cascades zero
--    rows at this point.
-- =============================================================================

DELETE FROM paliad.proceeding_types
 WHERE category = 'litigation';

-- =============================================================================
-- 5. Hard assertions. Raise loudly if anything didn't land — this
--    migration is not safe to leave half-applied because the litigation
--    pt rows are gone and the rule corpus needs to be coherent.
-- =============================================================================

DO $$
DECLARE
    v_orphan_rules  integer;
    v_lit_rows      integer;
    v_archived      integer;
    v_archive_id    integer;
BEGIN
    SELECT id INTO v_archive_id
      FROM paliad.proceeding_types
     WHERE code = '_archived_litigation';

    IF v_archive_id IS NULL THEN
        RAISE EXCEPTION
            'mig 093: _archived_litigation proceeding_type missing after step 2';
    END IF;

    -- No deadline_rules row still points at a litigation pt id (the
    -- pt rows themselves are gone, so the proper check is "no rule
    -- points at a row outside the surviving proceeding_types set").
    -- This collapses to: no rule has a NULL proceeding_type from the
    -- DELETE (the FK on rules → pt(id) is ON DELETE CASCADE; if we
    -- missed a rule it would have been cascade-deleted in step 4).
    -- Cross-check by counting rules that used to be on litigation pts:
    SELECT count(*) INTO v_lit_rows
      FROM paliad.proceeding_types
     WHERE category = 'litigation';
    IF v_lit_rows <> 0 THEN
        RAISE EXCEPTION
            'mig 093: % litigation proceeding_types rows survived the DELETE',
            v_lit_rows;
    END IF;

    SELECT count(*) INTO v_archived
      FROM paliad.deadline_rules
     WHERE proceeding_type_id = v_archive_id;
    IF v_archived <> 40 THEN
        RAISE EXCEPTION
            'mig 093: expected 40 rules on _archived_litigation, got %',
            v_archived;
    END IF;

    -- Belt-and-braces: every snapshot row matches a surviving rule on
    -- the archive pt by id. If any rule was cascade-deleted by a
    -- missed step, this raises.
    SELECT count(*) INTO v_orphan_rules
      FROM paliad.deadline_rules_pre_093 snap
      LEFT JOIN paliad.deadline_rules dr ON dr.id = snap.id
     WHERE dr.id IS NULL;
    IF v_orphan_rules <> 0 THEN
        RAISE EXCEPTION
            'mig 093: % rules from the pre-snapshot are missing from '
            'paliad.deadline_rules — cascade-delete leak',
            v_orphan_rules;
    END IF;
END $$;
