-- t-paliad-134 add-on (m 2026-05-05): pill ordering by real-world
-- frequency + name standardisation + legal-source fix for rev.defence.
--
-- Three changes on paliad.proceeding_types and paliad.deadline_rules:
--
-- 1. New column display_order: secondary sort key for proceeding-pill
--    ordering inside concept cards. sort_order is per-jurisdiction
--    grouping (used in the wizard); display_order reflects pill
--    likelihood (most common first). m's spec values, lower = more
--    common; gap-numbered so future additions slot in.
--
-- 2. name standardisation — m wants -verfahren (process) consistently.
--    'Nichtigkeitsklage' → 'Nichtigkeitsverfahren' is the explicit
--    example; Berufung/Beschwerde/Einspruch get the same treatment
--    where the row describes the multi-step process rather than a
--    single submission.
--
-- 3. legal_source for the rev.defence rule (UPC_REV statement-of-
--    defence): was NULL, which leaked the internal local_code 'rev.
--    defence' to the UI as the citation. Should be 'UPC.RoP.49.1'.
--    rule_code mirrors that as 'RoP.49.1' for trigram search hits.
--
-- Validation gates use RAISE WARNING (not EXCEPTION) per the 049 outage
-- lesson — partial-migration recovery beats whole-transaction failure.

BEGIN;

-- ---------------------------------------------------------------------
-- 1. display_order column + backfill
-- ---------------------------------------------------------------------
ALTER TABLE paliad.proceeding_types
    ADD COLUMN IF NOT EXISTS display_order int NOT NULL DEFAULT 999;

COMMENT ON COLUMN paliad.proceeding_types.display_order IS
    'Pill-ordering rank by real-world frequency (lower = shown first). '
    'Independent of sort_order, which groups by jurisdiction for the '
    'Verfahrensablauf wizard. Default 999 = appears at the end.';

UPDATE paliad.proceeding_types SET display_order = CASE code
    -- Common, listed first
    WHEN 'UPC_INF'                THEN 10
    WHEN 'DE_INF'                 THEN 20
    WHEN 'UPC_REV'                THEN 30
    WHEN 'DE_NULL'                THEN 40
    WHEN 'EPA_OPP'                THEN 50
    WHEN 'EPA_APP'                THEN 60
    WHEN 'DPMA_OPP'               THEN 70
    WHEN 'UPC_APP'                THEN 80
    WHEN 'DE_INF_OLG'             THEN 90
    WHEN 'DE_INF_BGH'             THEN 100
    WHEN 'DE_NULL_BGH'            THEN 110
    -- Less common, pushed back
    WHEN 'EP_GRANT'               THEN 200
    WHEN 'DPMA_BPATG_BESCHWERDE'  THEN 210
    WHEN 'DPMA_BGH_RB'            THEN 220
    WHEN 'UPC_APP_ORDERS'         THEN 230
    -- Rare, last block
    WHEN 'UPC_DAMAGES'            THEN 900
    WHEN 'UPC_DISCOVERY'          THEN 910
    WHEN 'UPC_PI'                 THEN 920
    WHEN 'UPC_COST_APPEAL'        THEN 930
    -- Legacy v0 codes (INF/REV/CCR/APM/APP/AMD/ZPO_CIVIL): keep at 999.
    ELSE display_order
END;

-- Soft check: warn (don't fail) if any active proceeding still has the
-- default. m wants visibility, not transactional gate-keeping.
DO $$
DECLARE
    n_default int;
BEGIN
    SELECT count(*)
      INTO n_default
      FROM paliad.proceeding_types
     WHERE is_active = true
       AND display_order = 999
       AND code IN ('UPC_INF','UPC_REV','UPC_PI','UPC_APP','UPC_DAMAGES',
                    'UPC_DISCOVERY','UPC_COST_APPEAL','UPC_APP_ORDERS',
                    'DE_INF','DE_NULL','DE_INF_OLG','DE_INF_BGH','DE_NULL_BGH',
                    'EPA_OPP','EPA_APP','EP_GRANT',
                    'DPMA_OPP','DPMA_BPATG_BESCHWERDE','DPMA_BGH_RB');
    IF n_default > 0 THEN
        RAISE WARNING 't-paliad-134: % active proceeding_types in the v3 set still have default display_order=999. Frequency-sort will push them to the end.', n_default;
    END IF;
END$$;

-- ---------------------------------------------------------------------
-- 2. -klage / -verfahren standardisation in name (DE)
-- ---------------------------------------------------------------------
-- Each row keyed by code so renames are idempotent across re-runs.
UPDATE paliad.proceeding_types SET name = 'Nichtigkeitsverfahren'                      WHERE code = 'UPC_REV';
UPDATE paliad.proceeding_types SET name = 'Berufungsverfahren'                         WHERE code = 'UPC_APP';
UPDATE paliad.proceeding_types SET name = 'Verletzungsverfahren (LG)'                  WHERE code = 'DE_INF';
UPDATE paliad.proceeding_types SET name = 'Berufungsverfahren OLG (Verletzung)'        WHERE code = 'DE_INF_OLG';
UPDATE paliad.proceeding_types SET name = 'Berufungsverfahren BGH (Nichtigkeit)'       WHERE code = 'DE_NULL_BGH';
UPDATE paliad.proceeding_types SET name = 'Beschwerdeverfahren BPatG (DPMA)'           WHERE code = 'DPMA_BPATG_BESCHWERDE';
UPDATE paliad.proceeding_types SET name = 'Einspruchsverfahren DPMA'                   WHERE code = 'DPMA_OPP';
UPDATE paliad.proceeding_types SET name = 'Berufungsverfahren Kosten'                  WHERE code = 'UPC_COST_APPEAL';
UPDATE paliad.proceeding_types SET name = 'Berufungsverfahren Anordnungen'             WHERE code = 'UPC_APP_ORDERS';
UPDATE paliad.proceeding_types SET name = 'Rechtsbeschwerdeverfahren BGH'              WHERE code = 'DPMA_BGH_RB';
UPDATE paliad.proceeding_types SET name = 'Revisions-/NZB-Verfahren BGH (Verletzung)'  WHERE code = 'DE_INF_BGH';

-- ---------------------------------------------------------------------
-- 3. legal_source for the rev.defence rule (UPC_REV)
-- ---------------------------------------------------------------------
UPDATE paliad.deadline_rules dr
   SET legal_source = 'UPC.RoP.49.1',
       rule_code    = 'RoP.49.1'
  FROM paliad.proceeding_types p
 WHERE dr.proceeding_type_id = p.id
   AND p.code = 'UPC_REV'
   AND dr.code = 'rev.defence'
   AND dr.legal_source IS NULL;

DO $$
DECLARE
    n_unfixed int;
BEGIN
    SELECT count(*)
      INTO n_unfixed
      FROM paliad.deadline_rules dr
      JOIN paliad.proceeding_types p ON p.id = dr.proceeding_type_id
     WHERE p.code = 'UPC_REV' AND dr.code = 'rev.defence' AND dr.legal_source IS NULL;
    IF n_unfixed > 0 THEN
        RAISE WARNING 't-paliad-134: rev.defence × UPC_REV still has NULL legal_source after backfill (% rows).', n_unfixed;
    END IF;
END$$;

-- ---------------------------------------------------------------------
-- 4. Matview rebuild — surface proceeding_display_order so the search
--    service can ORDER BY it directly. Drop + recreate keeps the
--    column list explicit; existing query patterns and all indexes
--    are reproduced verbatim with the one new column added.
-- ---------------------------------------------------------------------
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.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,
    NULL::text,
    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
 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);

-- The fresh matview is populated by the implicit initial scan above. The
-- next RefreshSearchView() (called from cmd/server/main.go after this
-- migration runner) is a no-op refresh and stays cheap.

COMMIT;
