-- t-paliad-186 / Fristen Phase 3 Slice 5 Step F-1 — remap any project
-- still pointing at a litigation-category proceeding_types row to the
-- corresponding fristenrechner-category code (per design §3.F + m's
-- Q2 ruling: "I dont even get 'litigation corpus'").
--
-- Live-data reality: 11/11 projects carry proceeding_type_id IS NULL
-- today, so this migration is effectively a no-op on the production
-- corpus. It still ships defensively for any future test / staging /
-- imported data that might land with a litigation-category id before
-- the CHECK trigger (mig 088) catches the next write.
--
-- Mapping (cross-checked against the live paliad.proceeding_types
-- catalog — 19 fristenrechner codes, 7 litigation codes):
--
--   INF        → UPC_INF       (UPC infringement, canonical reading)
--   REV        → UPC_REV       (UPC revocation)
--   APP        → UPC_APP       (UPC appeal)
--   CCR        → NULL          (no UPC_CCR in the fristenrechner catalog
--                                — flag for legal review per design §3.F)
--   APM        → NULL          (no UPC_APM — flag for legal review)
--   AMD        → NULL          (no UPC_AMD — flag for legal review)
--   ZPO_CIVIL  → NULL          (no fristenrechner analogue, design §3.F:
--                                "litigation codes stay but become unused
--                                for project-binding")
--
-- Each NULL-remap leaves a paliad.project_events row with a
-- 'proceeding_type_remap_null' event so legal review can spot the
-- project + decide whether to pick a hand-mapped fristenrechner code.
-- Today no live project hits this branch — the events table stays
-- clean — but the audit hook is there for the day a litigation-coded
-- project lands.
--
-- Idempotent: only rows still pointing at a litigation-category code
-- are touched. Re-running on a clean target is a no-op.
--
-- Hard assertion at end: no paliad.projects row points at a
-- non-fristenrechner-category proceeding_types row post-mig. RAISE
-- EXCEPTION if violated — fails the migration loudly rather than
-- relying on mig 088's runtime trigger to catch the next write.
--
-- Audit-reason wrapper: required by the mig 079 trigger when this
-- migration UPDATEs deadline_rules tangentially (it doesn't, but
-- set_config is harmless if no audited row mutates).

SELECT set_config(
    'paliad.audit_reason',
    'mig 087: remap projects.proceeding_type_id from litigation→fristenrechner per design §3.F + Q2',
    true);

-- ============================================================================
-- 1. Remap rows that point at litigation codes with a known UPC analogue.
-- ============================================================================

UPDATE paliad.projects p
   SET proceeding_type_id = pt_new.id
  FROM paliad.proceeding_types pt_old
  JOIN paliad.proceeding_types pt_new
       ON pt_new.code = CASE pt_old.code
                            WHEN 'INF' THEN 'UPC_INF'
                            WHEN 'REV' THEN 'UPC_REV'
                            WHEN 'APP' THEN 'UPC_APP'
                        END
      AND pt_new.is_active = true
      AND pt_new.category = 'fristenrechner'
 WHERE p.proceeding_type_id = pt_old.id
   AND pt_old.category      = 'litigation'
   AND pt_old.code IN ('INF', 'REV', 'APP');

-- ============================================================================
-- 2. NULL-remap rows pointing at litigation codes with no fristenrechner
--    analogue. Record a paliad.project_events row so legal review can
--    follow up.
-- ============================================================================

-- Capture the projects we're about to NULL-remap into a temp table so
-- we can both UPDATE and INSERT events from the same set (without a
-- second SELECT that might race with the UPDATE).

CREATE TEMP TABLE _mig_087_null_remaps ON COMMIT DROP AS
SELECT p.id          AS project_id,
       p.created_by  AS actor,
       pt_old.code   AS old_code
  FROM paliad.projects p
  JOIN paliad.proceeding_types pt_old ON pt_old.id = p.proceeding_type_id
 WHERE pt_old.category = 'litigation'
   AND pt_old.code IN ('CCR', 'APM', 'AMD', 'ZPO_CIVIL');

UPDATE paliad.projects p
   SET proceeding_type_id = NULL
  FROM _mig_087_null_remaps r
 WHERE p.id = r.project_id;

INSERT INTO paliad.project_events
    (id, project_id, event_type, title, description, event_date, created_by, metadata, created_at, updated_at)
SELECT gen_random_uuid(),
       r.project_id,
       'proceeding_type_remap_null',
       'Verfahrenstyp zurückgesetzt (Soft-Merge Phase 3)',
       'proceeding_type_id wurde auf NULL gesetzt — '
           || r.old_code
           || ' hat kein Fristenrechner-Pendant. Bitte manuell einen passenden Code wählen.',
       now(),
       r.actor,
       jsonb_build_object(
           'migration', '087',
           'old_code',  r.old_code,
           'reason',    'project soft-merge: no fristenrechner analogue'
       ),
       now(),
       now()
  FROM _mig_087_null_remaps r;

-- ============================================================================
-- 3. Hard assertion: every non-NULL proceeding_type_id on projects now
--    references a fristenrechner-category row.
-- ============================================================================

DO $$
DECLARE
    n_total       int;
    n_null        int;
    n_fristen     int;
    n_non_fristen int;
BEGIN
    SELECT count(*)                                          INTO n_total      FROM paliad.projects;
    SELECT count(*) FILTER (WHERE proceeding_type_id IS NULL)
      INTO n_null FROM paliad.projects;
    SELECT count(*)
      INTO n_fristen
      FROM paliad.projects p
      JOIN paliad.proceeding_types pt ON pt.id = p.proceeding_type_id
     WHERE pt.category = 'fristenrechner';
    SELECT count(*)
      INTO n_non_fristen
      FROM paliad.projects p
      JOIN paliad.proceeding_types pt ON pt.id = p.proceeding_type_id
     WHERE pt.category <> 'fristenrechner';

    RAISE NOTICE 'mig 087: projects total=%, NULL=%, fristenrechner=%, other=%',
                 n_total, n_null, n_fristen, n_non_fristen;

    IF n_non_fristen > 0 THEN
        RAISE EXCEPTION 'mig 087: % projects still point at non-fristenrechner-category '
                        'proceeding_type_ids — soft-merge incomplete. Investigate '
                        'and either extend the remap or add a hand-mapped code.',
                        n_non_fristen;
    END IF;
END $$;
