-- 136_procedural_events_additive — Slice B.1, t-paliad-273 / m/paliad#93
--
-- ADDITIVE ONLY. Creates the three new tables that split today's
-- paliad.deadline_rules into its three latent concepts (per the
-- 2026-05-25 inventor design + 2026-05-26 B.0 re-validation):
--
--   1. paliad.legal_sources        — the source-of-law citations
--                                    (DE.PatG.102, UPC.RoP.220.1, …)
--   2. paliad.procedural_events    — the procedural-event templates
--                                    (Rechtsbeschwerdebegründung, etc.;
--                                    successor of `submission_code`)
--   3. paliad.sequencing_rules     — the timing + trigger + condition
--                                    mechanics (today's per-row data)
--
-- and adds two nullable link columns on paliad.deadlines so B.2's
-- dual-write phase has somewhere to point.
--
-- The migration does NOT touch paliad.deadline_rules. The legacy table
-- stays intact and authoritative for reads until B.3 flips the cutover.
-- deadlines.rule_id stays in place (read by the calculator + projection
-- service). No app code is changed by this migration; B.2 introduces
-- the dual-write that wires services to the new tables.
--
-- Backfill plan (cf. design §5.1 + B.0 findings §7):
--   * legal_sources <- DISTINCT legal_source FROM deadline_rules WHERE
--                       legal_source IS NOT NULL. pretty_de/pretty_en
--                       LEFT NULL for now (legalSourcePretty() in Go
--                       continues to materialise them on read; a future
--                       slice backfills them via a Go shim).
--   * procedural_events <-
--       (a) DISTINCT ON (submission_code) FROM deadline_rules WHERE
--           submission_code IS NOT NULL — picks the lowest-id rule per
--           code as the procedural-event identity source.
--       (b) one synthetic procedural_event per NULL-submission_code
--           rule, code = 'null.' || substring(replace(id::text,'-',''),1,8).
--           m's pick (paliadin instruction 2026-05-26): mint synthetic
--           codes so every deadline_rules row ends up with a
--           procedural_events row, preserving the 1:1 sequencing-rule
--           backfill and keeping the NOT NULL FK on
--           sequencing_rules.procedural_event_id intact.
--   * sequencing_rules <- 1:1 from deadline_rules. The new row inherits
--           the source row's id so that any existing
--           paliad.deadlines.rule_id FK target stays resolvable through
--           the dual-write window (design §5.1 step 4).
--   * deadlines.procedural_event_id + sequencing_rule_id <- joined from
--           sequencing_rules on the inherited id.
--
-- Design deviations (intentional, documented):
--   - procedural_events.event_kind is NULLABLE (design proposed NOT NULL
--     with 'other' fallback). Today 89 deadline_rules rows have NULL
--     event_type — these are "structural / parent-only rows in the
--     proceeding tree" per B.0 §1. Forcing them to 'other' would lose
--     semantics. A later slice can tighten this to NOT NULL after the
--     78+11 NULLs are reclassified.
--   - legal_sources.pretty_de / pretty_en are NULLABLE (design proposed
--     NOT NULL). Materialising them requires the Go-side
--     legalSourcePretty() function — out of scope for a SQL migration.
--     The Go read path continues to compute them on the fly from
--     legal_source / citation; a future slice (Go shim driven from
--     internal/services/submission_vars.go:619) backfills them.
--   - submission_drafts is NOT modified. The design proposes adding
--     procedural_event_id there too (§4.1 §5.1 step 6) but the B.1
--     instruction scope is explicit: tables + deadlines columns only.
--     submission_drafts continues to key off submission_code text.
--
-- Audit pattern follows mig 135 (Slice B3): PRE-pass counts what we
-- expect to write, BACKFILL runs the SELECT-INSERTs, POST-pass verifies
-- row counts and FK integrity. Any mismatch RAISE EXCEPTIONs and the
-- transaction rolls back — operator sees the NOTICE lines and the
-- failed assertion message.
--
-- See: docs/design-procedural-events-model-2026-05-25.md §4 + §5
--      docs/design-procedural-events-b0-findings-2026-05-26.md §7

-- ---------------------------------------------------------------
-- 0. PRE pass — snapshot what we're about to backfill
-- ---------------------------------------------------------------

DO $$
DECLARE
    v_rules           int;
    v_codes_nn        int;
    v_codes_distinct  int;
    v_codes_null      int;
    v_legal_distinct  int;
    v_concept_linked  int;
    v_dups            int;
BEGIN
    SELECT COUNT(*) INTO v_rules            FROM paliad.deadline_rules;
    SELECT COUNT(*) INTO v_codes_nn         FROM paliad.deadline_rules WHERE submission_code IS NOT NULL;
    SELECT COUNT(DISTINCT submission_code) INTO v_codes_distinct
                                            FROM paliad.deadline_rules WHERE submission_code IS NOT NULL;
    SELECT COUNT(*) INTO v_codes_null       FROM paliad.deadline_rules WHERE submission_code IS NULL;
    SELECT COUNT(DISTINCT legal_source) INTO v_legal_distinct
                                            FROM paliad.deadline_rules WHERE legal_source IS NOT NULL;
    SELECT COUNT(*) INTO v_concept_linked   FROM paliad.deadline_rules WHERE concept_id IS NOT NULL;

    RAISE NOTICE '[mig 136] PRE: deadline_rules=%, with_submission_code=%, distinct_codes=%, null_codes=%, distinct_legal_sources=%, concept_linked=%',
        v_rules, v_codes_nn, v_codes_distinct, v_codes_null, v_legal_distinct, v_concept_linked;

    -- Defensive: refuse to run if multi-row submission_codes have crept
    -- back in. B.0 (2026-05-26) found zero; mig 134 + 135 do not add
    -- any. If this CHECK ever fires the backfill arithmetic below
    -- breaks silently (one PE per code becomes ambiguous), so abort.
    SELECT COUNT(*) INTO v_dups FROM (
        SELECT submission_code
          FROM paliad.deadline_rules
         WHERE submission_code IS NOT NULL
         GROUP BY submission_code
        HAVING COUNT(*) > 1
    ) d;
    IF v_dups > 0 THEN
        RAISE EXCEPTION '[mig 136] FAILED PRE: % submission_code value(s) appear on >1 deadline_rules row. '
            'The B.0 audit (2026-05-26) found zero. If you are seeing this, a rule was added that '
            'duplicates an existing submission_code (or the _archived_litigation.* rows returned). '
            'Decide whether the new schema collapses them (multiple sequencing rules → one '
            'procedural event) or whether each row gets its own code, then update this migration '
            'or the offending data before re-running.', v_dups;
    END IF;
END $$;

-- ---------------------------------------------------------------
-- 1. CREATE TABLE paliad.legal_sources
-- ---------------------------------------------------------------

CREATE TABLE paliad.legal_sources (
    id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    citation     text NOT NULL UNIQUE,
    jurisdiction text NOT NULL,
    pretty_de    text,
    pretty_en    text,
    notes        text,
    created_at   timestamptz NOT NULL DEFAULT now(),
    updated_at   timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE paliad.legal_sources IS
    'Source-of-law citations (DE.PatG.102, UPC.RoP.220.1, …). One row per '
    'distinct citation shorthand. pretty_de/pretty_en backfilled by a '
    'future Go-driven slice; until then NULL and the Go service ('
    'internal/services/submission_vars.go:619 legalSourcePretty) computes '
    'the human-readable form on read from the citation. Slice B.1 t-paliad-273.';

CREATE INDEX legal_sources_jurisdiction_idx ON paliad.legal_sources(jurisdiction);

-- ---------------------------------------------------------------
-- 2. CREATE TABLE paliad.procedural_events
-- ---------------------------------------------------------------

CREATE TABLE paliad.procedural_events (
    id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    code                  text NOT NULL UNIQUE,
    name                  text NOT NULL,
    name_en               text NOT NULL DEFAULT '',
    description           text,
    event_kind            text,
    primary_party_default text,
    legal_source_id       uuid REFERENCES paliad.legal_sources(id),
    concept_id            uuid REFERENCES paliad.deadline_concepts(id),
    lifecycle_state       text NOT NULL DEFAULT 'published',
    draft_of              uuid REFERENCES paliad.procedural_events(id),
    published_at          timestamptz,
    is_active             boolean NOT NULL DEFAULT true,
    created_at            timestamptz NOT NULL DEFAULT now(),
    updated_at            timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE paliad.procedural_events IS
    'Procedural-event templates — the "what kind of step is this in the '
    'proceeding" hat of the legacy paliad.deadline_rules row. One row per '
    'unique submission_code, plus one synthetic row per NULL-submission_code '
    'rule (code prefix "null."). Slice B.1 t-paliad-273.';

COMMENT ON COLUMN paliad.procedural_events.event_kind IS
    'filing|reply|hearing|decision|order|other. NULLABLE for now — 89 '
    'rules in the live corpus have NULL event_type (structural / parent-only '
    'rows in the proceeding tree). A future slice can tighten to NOT NULL '
    'after these are reclassified.';

COMMENT ON COLUMN paliad.procedural_events.concept_id IS
    'Optional reference to a deadline_concepts row. N:1 — one concept may '
    'be shared by many procedural events (e.g. "Berufungsfrist" attaches to '
    'all four court-specific Berufung procedural events). Do NOT add UNIQUE.';

CREATE INDEX procedural_events_concept_id_idx     ON paliad.procedural_events(concept_id);
CREATE INDEX procedural_events_event_kind_idx     ON paliad.procedural_events(event_kind);
CREATE INDEX procedural_events_lifecycle_idx      ON paliad.procedural_events(lifecycle_state);
CREATE INDEX procedural_events_legal_source_idx   ON paliad.procedural_events(legal_source_id);

-- ---------------------------------------------------------------
-- 3. CREATE TABLE paliad.sequencing_rules
-- ---------------------------------------------------------------

CREATE TABLE paliad.sequencing_rules (
    id                       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    procedural_event_id      uuid NOT NULL REFERENCES paliad.procedural_events(id),
    proceeding_type_id       integer REFERENCES paliad.proceeding_types(id),
    parent_id                uuid REFERENCES paliad.sequencing_rules(id),
    trigger_event_id         bigint REFERENCES paliad.trigger_events(id),
    duration_value           integer NOT NULL DEFAULT 0,
    duration_unit            text NOT NULL DEFAULT 'months',
    timing                   text DEFAULT 'after',
    alt_duration_value       integer,
    alt_duration_unit        text,
    alt_rule_code            text,
    anchor_alt               text,
    combine_op               text,
    condition_expr           jsonb,
    primary_party            text,
    sequence_order           integer NOT NULL DEFAULT 0,
    is_spawn                 boolean NOT NULL DEFAULT false,
    spawn_label              text,
    spawn_proceeding_type_id integer REFERENCES paliad.proceeding_types(id),
    is_bilateral             boolean NOT NULL DEFAULT false,
    is_court_set             boolean NOT NULL DEFAULT false,
    priority                 text NOT NULL DEFAULT 'mandatory',
    rule_code                text,
    rule_codes               text[],
    deadline_notes           text,
    deadline_notes_en        text,
    choices_offered          jsonb,
    applies_to_target        text[],
    lifecycle_state          text NOT NULL DEFAULT 'published',
    draft_of                 uuid REFERENCES paliad.sequencing_rules(id),
    published_at             timestamptz,
    is_active                boolean NOT NULL DEFAULT true,
    created_at               timestamptz NOT NULL DEFAULT now(),
    updated_at               timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE paliad.sequencing_rules IS
    'Sequencing-rule mechanics — the "how and when does this fire" hat of '
    'the legacy paliad.deadline_rules row. 1:1 with deadline_rules during '
    'the dual-write window; the id is inherited from deadline_rules.id so '
    'paliad.deadlines.rule_id FKs continue to resolve transitively. '
    'Slice B.1 t-paliad-273.';

COMMENT ON COLUMN paliad.sequencing_rules.primary_party IS
    'Per-rule override of procedural_events.primary_party_default. Same '
    'four-value vocab as deadline_rules.primary_party (mig 135 CHECK). '
    'NULL = use procedural-event default. A future slice can add the '
    'same CHECK here.';

CREATE INDEX sequencing_rules_pe_proc_lifecycle_idx
    ON paliad.sequencing_rules(procedural_event_id, proceeding_type_id, lifecycle_state);
CREATE INDEX sequencing_rules_parent_id_idx       ON paliad.sequencing_rules(parent_id);
CREATE INDEX sequencing_rules_trigger_event_idx   ON paliad.sequencing_rules(trigger_event_id);
CREATE INDEX sequencing_rules_proceeding_type_idx ON paliad.sequencing_rules(proceeding_type_id);

-- ---------------------------------------------------------------
-- 4. ALTER paliad.deadlines — add link columns
-- ---------------------------------------------------------------

ALTER TABLE paliad.deadlines
    ADD COLUMN procedural_event_id uuid REFERENCES paliad.procedural_events(id),
    ADD COLUMN sequencing_rule_id  uuid REFERENCES paliad.sequencing_rules(id);

COMMENT ON COLUMN paliad.deadlines.procedural_event_id IS
    'NULLABLE link to the procedural event this deadline instantiates. '
    'Added Slice B.1 (mig 136). B.2 dual-write populates it on every new '
    'deadline; B.3 cutover flips reads to use this instead of rule_id. '
    'rule_id stays in place until B.4 destructive drop.';
COMMENT ON COLUMN paliad.deadlines.sequencing_rule_id IS
    'NULLABLE link to the sequencing rule. Same lifecycle as '
    'procedural_event_id — added Slice B.1, dual-written B.2, read in B.3, '
    'rule_id dropped in B.4.';

CREATE INDEX deadlines_procedural_event_id_idx ON paliad.deadlines(procedural_event_id);
CREATE INDEX deadlines_sequencing_rule_id_idx  ON paliad.deadlines(sequencing_rule_id);

-- ---------------------------------------------------------------
-- 5. BACKFILL — legal_sources
-- ---------------------------------------------------------------

INSERT INTO paliad.legal_sources (citation, jurisdiction)
SELECT DISTINCT
    legal_source AS citation,
    COALESCE(NULLIF(split_part(legal_source, '.', 1), ''), 'other') AS jurisdiction
  FROM paliad.deadline_rules
 WHERE legal_source IS NOT NULL;

-- ---------------------------------------------------------------
-- 6. BACKFILL — procedural_events
--    (a) codes-bearing branch: DISTINCT ON (submission_code) picks the
--        lowest-id (tie-break sequence_order) deadline_rules row as the
--        identity source per the design's §5.1 step 3.
--    (b) NULL-code branch: one synthetic row per rule, code minted from
--        the rule id's first 8 hex chars (sans dashes) — m's pick
--        2026-05-26 (paliadin instruction).
-- ---------------------------------------------------------------

-- (a) codes-bearing rules → one procedural_events row per distinct code
INSERT INTO paliad.procedural_events
    (code, name, name_en, description, event_kind, primary_party_default,
     legal_source_id, concept_id, lifecycle_state, published_at, is_active)
SELECT
    src.submission_code,
    src.name,
    src.name_en,
    src.description,
    src.event_type,
    src.primary_party,
    ls.id,
    src.concept_id,
    src.lifecycle_state,
    src.published_at,
    src.is_active
FROM (
    SELECT DISTINCT ON (submission_code)
        submission_code, name, name_en, description, event_type,
        primary_party, concept_id, legal_source, lifecycle_state,
        published_at, is_active
      FROM paliad.deadline_rules
     WHERE submission_code IS NOT NULL
     ORDER BY submission_code, id, sequence_order
) src
LEFT JOIN paliad.legal_sources ls ON ls.citation = src.legal_source;

-- (b) NULL-code rules → one synthetic procedural_events row each
INSERT INTO paliad.procedural_events
    (code, name, name_en, description, event_kind, primary_party_default,
     legal_source_id, concept_id, lifecycle_state, published_at, is_active)
SELECT
    'null.' || substring(replace(dr.id::text, '-', ''), 1, 8) AS code,
    dr.name,
    dr.name_en,
    dr.description,
    dr.event_type,
    dr.primary_party,
    ls.id,
    dr.concept_id,
    dr.lifecycle_state,
    dr.published_at,
    dr.is_active
  FROM paliad.deadline_rules dr
  LEFT JOIN paliad.legal_sources ls ON ls.citation = dr.legal_source
 WHERE dr.submission_code IS NULL;

-- ---------------------------------------------------------------
-- 7. BACKFILL — sequencing_rules
--    1:1 with deadline_rules. id inherited so deadlines.rule_id FKs
--    continue to resolve through the dual-write window (design §5.1
--    step 4). procedural_event_id resolved by JOIN on the (real or
--    synthetic) code.
-- ---------------------------------------------------------------

INSERT INTO paliad.sequencing_rules
    (id, procedural_event_id, proceeding_type_id, parent_id, trigger_event_id,
     duration_value, duration_unit, timing,
     alt_duration_value, alt_duration_unit, alt_rule_code, anchor_alt,
     combine_op, condition_expr, primary_party, sequence_order,
     is_spawn, spawn_label, spawn_proceeding_type_id,
     is_bilateral, is_court_set, priority,
     rule_code, rule_codes, deadline_notes, deadline_notes_en,
     choices_offered, applies_to_target,
     lifecycle_state, draft_of, published_at, is_active,
     created_at, updated_at)
SELECT
    dr.id,
    pe.id,
    dr.proceeding_type_id,
    dr.parent_id,
    dr.trigger_event_id,
    dr.duration_value, dr.duration_unit, dr.timing,
    dr.alt_duration_value, dr.alt_duration_unit, dr.alt_rule_code, dr.anchor_alt,
    dr.combine_op, dr.condition_expr, dr.primary_party, dr.sequence_order,
    dr.is_spawn, dr.spawn_label, dr.spawn_proceeding_type_id,
    dr.is_bilateral, dr.is_court_set, dr.priority,
    dr.rule_code, dr.rule_codes, dr.deadline_notes, dr.deadline_notes_en,
    dr.choices_offered, dr.applies_to_target,
    dr.lifecycle_state,
    -- draft_of is a self-FK on deadline_rules; preserve as a self-FK on
    -- sequencing_rules since the inherited ids are stable across both.
    dr.draft_of,
    dr.published_at, dr.is_active,
    dr.created_at, dr.updated_at
  FROM paliad.deadline_rules dr
  JOIN paliad.procedural_events pe
    ON pe.code = COALESCE(
         dr.submission_code,
         'null.' || substring(replace(dr.id::text, '-', ''), 1, 8)
       );

-- ---------------------------------------------------------------
-- 8. BACKFILL — paliad.deadlines link columns
-- ---------------------------------------------------------------

UPDATE paliad.deadlines d
   SET procedural_event_id = sr.procedural_event_id,
       sequencing_rule_id  = sr.id
  FROM paliad.sequencing_rules sr
 WHERE d.rule_id = sr.id;

-- ---------------------------------------------------------------
-- 9. POST pass — integrity assertions
-- ---------------------------------------------------------------

DO $$
DECLARE
    v_dr_total           int;
    v_dr_codes_distinct  int;
    v_dr_codes_null      int;
    v_dr_legal_distinct  int;
    v_pe_total           int;
    v_sr_total           int;
    v_ls_total           int;
    v_orphan_pe          int;
    v_dup_synthetic      int;
    v_deadlines_linked   int;
    v_deadlines_total    int;
    v_pe_missing_ls      int;
BEGIN
    SELECT COUNT(*)                    INTO v_dr_total          FROM paliad.deadline_rules;
    SELECT COUNT(DISTINCT submission_code)
                                       INTO v_dr_codes_distinct FROM paliad.deadline_rules WHERE submission_code IS NOT NULL;
    SELECT COUNT(*)                    INTO v_dr_codes_null     FROM paliad.deadline_rules WHERE submission_code IS NULL;
    SELECT COUNT(DISTINCT legal_source)
                                       INTO v_dr_legal_distinct FROM paliad.deadline_rules WHERE legal_source IS NOT NULL;
    SELECT COUNT(*)                    INTO v_pe_total          FROM paliad.procedural_events;
    SELECT COUNT(*)                    INTO v_sr_total          FROM paliad.sequencing_rules;
    SELECT COUNT(*)                    INTO v_ls_total          FROM paliad.legal_sources;
    SELECT COUNT(*)                    INTO v_deadlines_total   FROM paliad.deadlines;
    SELECT COUNT(*)                    INTO v_deadlines_linked  FROM paliad.deadlines WHERE procedural_event_id IS NOT NULL;

    -- a. procedural_events row count = distinct_codes + null_codes
    IF v_pe_total <> v_dr_codes_distinct + v_dr_codes_null THEN
        RAISE EXCEPTION '[mig 136] FAILED POST: procedural_events count mismatch — got %, expected % (% distinct codes + % null-code rules)',
            v_pe_total, v_dr_codes_distinct + v_dr_codes_null, v_dr_codes_distinct, v_dr_codes_null;
    END IF;

    -- b. sequencing_rules row count = deadline_rules row count (1:1)
    IF v_sr_total <> v_dr_total THEN
        RAISE EXCEPTION '[mig 136] FAILED POST: sequencing_rules count mismatch — got %, expected % (1:1 with deadline_rules)',
            v_sr_total, v_dr_total;
    END IF;

    -- c. legal_sources row count = distinct legal_source in deadline_rules
    IF v_ls_total <> v_dr_legal_distinct THEN
        RAISE EXCEPTION '[mig 136] FAILED POST: legal_sources count mismatch — got %, expected % (distinct legal_source)',
            v_ls_total, v_dr_legal_distinct;
    END IF;

    -- d. every sequencing_rules row's procedural_event_id resolves
    SELECT COUNT(*)
      INTO v_orphan_pe
      FROM paliad.sequencing_rules sr
      LEFT JOIN paliad.procedural_events pe ON pe.id = sr.procedural_event_id
     WHERE pe.id IS NULL;
    IF v_orphan_pe > 0 THEN
        RAISE EXCEPTION '[mig 136] FAILED POST: % sequencing_rules row(s) have no resolving procedural_event_id', v_orphan_pe;
    END IF;

    -- e. no two synthetic codes collide (would have crashed the INSERT
    --    via UNIQUE, but assert again for clarity — collision among 78
    --    UUIDs at 8 hex chars is ~6e-7 probability)
    SELECT COUNT(*)
      INTO v_dup_synthetic
      FROM (
        SELECT code, COUNT(*) AS n
          FROM paliad.procedural_events
         WHERE code LIKE 'null.%'
         GROUP BY code
        HAVING COUNT(*) > 1
      ) d;
    IF v_dup_synthetic > 0 THEN
        RAISE EXCEPTION '[mig 136] FAILED POST: % synthetic codes collided. '
            'Re-run with a longer substring (16 hex chars instead of 8) '
            'or full uuid in the code-mint expression.', v_dup_synthetic;
    END IF;

    -- f. every procedural_events.legal_source_id either resolves or is
    --    NULL (NULL is fine — 119 of 231 rules have NULL legal_source)
    SELECT COUNT(*)
      INTO v_pe_missing_ls
      FROM paliad.procedural_events pe
      LEFT JOIN paliad.legal_sources ls ON ls.id = pe.legal_source_id
     WHERE pe.legal_source_id IS NOT NULL
       AND ls.id IS NULL;
    IF v_pe_missing_ls > 0 THEN
        RAISE EXCEPTION '[mig 136] FAILED POST: % procedural_events row(s) reference a missing legal_sources id', v_pe_missing_ls;
    END IF;

    RAISE NOTICE '[mig 136] POST: legal_sources=%, procedural_events=%, sequencing_rules=%, deadlines=% (% linked)',
        v_ls_total, v_pe_total, v_sr_total, v_deadlines_total, v_deadlines_linked;
    RAISE NOTICE '[mig 136] integrity OK — backfill complete. '
                 'deadline_rules untouched (1:1 with sequencing_rules; '
                 'ready for B.2 dual-write).';
END $$;
