-- t-paliad-266 / m/paliad#97 — make cross-cutting trigger pills filter
-- by court system in the event-type / Fristen search modal.
--
-- Two things land here:
--
--   1. DATA — backfill the missing deadline_rules row for trigger 207
--      (Wegfall des Hindernisses, UPC R.320). Mig 063 added the
--      trigger_event but never seeded its event_deadlines counterpart;
--      mig 092 then dropped event_deadlines after copying the four
--      sibling Wiedereinsetzungen (ids 200..203) into deadline_rules,
--      so trigger 207 stayed orphaned with no duration / legal_source.
--      Adding the row makes UPC R.320 Wiedereinsetzung calculable on
--      par with the four siblings (2 months from removal of obstacle,
--      legal_source = 'UPC.RoP.320', party = 'both') and gives the
--      matview a legal_source to surface for the UPC trigger pill.
--      Pattern mirrors the four sibling rows mig 085 inserted.
--
--   2. MATVIEW — rebuild paliad.deadline_search with a LEFT JOIN on
--      paliad.deadline_rules for trigger pills, exposing the trigger's
--      legal_source on the row. The cross-cutting concept card pills
--      then carry a structured citation prefix (UPC.* / DE.ZPO.* /
--      DE.PatG.* / EU.EPC* / EU.EPÜ.*) that the search service can
--      match against the active forum-bucket filter — see
--      DeadlineSearchService.translateForums + ForumToLegalSourcePrefixes
--      (added in this same change). Without the matview surfacing
--      legal_source for trigger rows, every cross-cutting sub-row
--      ignored the court-system chip selection (the bug m reported).
--
-- The materialised view paliad.deadline_search refreshes on the next
-- server boot via services.RefreshSearchView (cmd/server/main.go), so
-- the new legal_source column for triggers becomes searchable as soon
-- as the deploy restarts the process. No matview refresh from the
-- migration itself.

SELECT set_config(
    'paliad.audit_reason',
    'mig 125: t-paliad-266 — backfill missing deadline_rules row for trigger 207 (UPC R.320 Wiedereinsetzung) and rebuild deadline_search matview so trigger pills carry legal_source (cross-cutting court-system filter, m/paliad#97).',
    true);

-- =============================================================================
-- 1. Backfill: deadline_rules row for trigger 207.
--
-- Idempotency: gated on NOT EXISTS by (trigger_event_id, name). Mirrors
-- mig 085's guard so re-runs are no-ops once the row is present.
-- =============================================================================

INSERT INTO paliad.deadline_rules (
    id,
    proceeding_type_id,
    parent_id,
    trigger_event_id,
    spawn_proceeding_type_id,
    submission_code,
    name,
    name_en,
    primary_party,
    event_type,
    is_court_set,
    is_spawn,
    duration_value,
    duration_unit,
    timing,
    alt_duration_value,
    alt_duration_unit,
    combine_op,
    rule_code,
    deadline_notes,
    deadline_notes_en,
    legal_source,
    condition_expr,
    sequence_order,
    is_active,
    priority,
    lifecycle_state,
    draft_of,
    published_at,
    concept_id
)
SELECT
    gen_random_uuid(),
    NULL::integer,
    NULL::uuid,
    207,
    NULL::integer,
    NULL::text,
    'Wiedereinsetzungsantrag (UPC R.320)',
    'Petition for re-establishment of rights (UPC R.320)',
    NULL::text,
    NULL::text,
    false,
    false,
    2,
    'months',
    'after',
    NULL::integer,
    NULL::text,
    NULL::text,
    NULL::text,
    'Frist beträgt 2 Monate ab Wegfall des Hindernisses (R.320 RoP). Spätestens 12 Monate nach Ablauf der versäumten Frist.',
    'Period is 2 months from removal of the obstacle (UPC R.320 RoP). Latest 12 months after expiry of the missed deadline.',
    'UPC.RoP.320',
    NULL::jsonb,
    1207,
    true,
    'mandatory',
    'published',
    NULL::uuid,
    now(),
    (SELECT id FROM paliad.deadline_concepts WHERE slug = 'wiedereinsetzung')
WHERE NOT EXISTS (
    SELECT 1
      FROM paliad.deadline_rules dr
     WHERE dr.trigger_event_id = 207
);

-- =============================================================================
-- 2. Matview rebuild — LEFT JOIN deadline_rules on trigger_event_id so
--    cross-cutting trigger pills carry legal_source. Indexes reproduced
--    verbatim from mig 098 §5.
--
-- The trigger-row JOIN matches the Pipeline-C convention (mig 085 §2.5 /
-- mig 092 §2): each cross-cutting trigger has a single deadline_rules
-- row with proceeding_type_id IS NULL. A trigger event without that
-- row leaves legal_source NULL and the trigger pill keeps its current
-- "no jurisdiction filter match" semantics — same shape as before this
-- migration, just structurally surfaceable.
-- =============================================================================

DROP MATERIALIZED VIEW IF EXISTS paliad.deadline_search;

CREATE MATERIALIZED VIEW paliad.deadline_search AS
SELECT
    'rule'::text                         AS kind,
    'r:' || dr.id::text                  AS row_key,
    dc.id                                AS concept_id,
    dc.slug                              AS concept_slug,
    dc.name_de                           AS concept_name_de,
    dc.name_en                           AS concept_name_en,
    dc.description                       AS concept_description,
    dc.aliases                           AS concept_aliases,
    dc.party                             AS concept_party,
    dc.category                          AS concept_category,
    dc.sort_order                        AS concept_sort_order,
    dr.id                                AS rule_id,
    NULL::bigint                         AS trigger_event_id,
    pt.code                              AS proceeding_code,
    pt.name                              AS proceeding_name_de,
    pt.name_en                           AS proceeding_name_en,
    pt.jurisdiction                      AS jurisdiction,
    pt.display_order                     AS proceeding_display_order,
    dr.submission_code                   AS rule_local_code,
    dr.name                              AS rule_name_de,
    dr.name_en                           AS rule_name_en,
    dr.legal_source                      AS legal_source,
    dr.rule_code                         AS rule_code,
    dr.duration_value,
    dr.duration_unit,
    dr.timing,
    COALESCE(dr.primary_party, dc.party) AS effective_party
  FROM paliad.deadline_rules dr
  JOIN paliad.proceeding_types pt  ON pt.id = dr.proceeding_type_id
  JOIN paliad.deadline_concepts dc ON dc.id = dr.concept_id
 WHERE dr.is_active
   AND pt.is_active
   AND pt.category = 'fristenrechner'

UNION ALL

SELECT
    'trigger'::text,
    't:' || te.id::text,
    dc.id,
    dc.slug,
    dc.name_de,
    dc.name_en,
    dc.description,
    dc.aliases,
    dc.party,
    dc.category,
    dc.sort_order,
    NULL::uuid,
    te.id,
    NULL::text,
    NULL::text,
    NULL::text,
    'cross-cutting'::text,
    9999::int                            AS proceeding_display_order,
    te.code,
    te.name_de,
    te.name,
    dr_trig.legal_source                 AS legal_source,
    NULL::text,
    NULL::int,
    NULL::text,
    NULL::text,
    dc.party
  FROM paliad.trigger_events te
  JOIN paliad.deadline_concepts dc ON dc.slug = te.concept_id
  LEFT JOIN paliad.deadline_rules dr_trig
         ON dr_trig.trigger_event_id = te.id
        AND dr_trig.proceeding_type_id IS NULL
        AND dr_trig.is_active
        AND dr_trig.lifecycle_state = 'published'
 WHERE te.is_active;

CREATE UNIQUE INDEX deadline_search_row_key       ON paliad.deadline_search (row_key);
CREATE INDEX deadline_search_concept_id           ON paliad.deadline_search (concept_id);
CREATE INDEX deadline_search_proc_code            ON paliad.deadline_search (proceeding_code);
CREATE INDEX deadline_search_legal_source         ON paliad.deadline_search (legal_source);
CREATE INDEX deadline_search_effective_party      ON paliad.deadline_search (effective_party);
CREATE INDEX deadline_search_legal_source_trgm    ON paliad.deadline_search USING gin (legal_source gin_trgm_ops);
CREATE INDEX deadline_search_concept_de_trgm      ON paliad.deadline_search USING gin (concept_name_de gin_trgm_ops);
CREATE INDEX deadline_search_concept_en_trgm      ON paliad.deadline_search USING gin (concept_name_en gin_trgm_ops);
CREATE INDEX deadline_search_rule_de_trgm         ON paliad.deadline_search USING gin (rule_name_de gin_trgm_ops);
CREATE INDEX deadline_search_rule_en_trgm         ON paliad.deadline_search USING gin (rule_name_en gin_trgm_ops);
CREATE INDEX deadline_search_rule_code_trgm       ON paliad.deadline_search USING gin (rule_code gin_trgm_ops);
