-- t-paliad-136 Phase C rollback. Restores the v3 seed state for the
-- rows touched by 052.up.sql. Note: rollback is best-effort — if the
-- exempt-list coverage gate in migration 049 is re-evaluated post-down
-- it may complain about reply-to-cross-appeal again (049 keeps it on
-- the exempt list permanently, so it won't).

BEGIN;

-- 6b. Drop the three new spaetere-schriftsaetze leaves and their
--     junction rows (junction rows go via ON DELETE CASCADE on the
--     event_categories FK).
DELETE FROM paliad.event_categories
 WHERE slug IN (
        'ich-moechte-einreichen.spaetere-schriftsaetze.r116-eingaben',
        'ich-moechte-einreichen.spaetere-schriftsaetze.anschlussberufung-upc',
        'ich-moechte-einreichen.spaetere-schriftsaetze.reply-to-cross-appeal-upc'
       );

-- 6a. Drop the reply-to-cross-appeal | UPC_APP rows added under the
--     UPC berufungsschrift leaves.
DELETE FROM paliad.event_category_concepts
 WHERE event_category_id IN (
        SELECT id FROM paliad.event_categories
         WHERE slug IN (
            'cms-eingang.gegenseite.upc-inf.berufungsschrift',
            'cms-eingang.gegenseite.upc-rev.berufungsschrift'
         )
       )
   AND concept_id = (
        SELECT id FROM paliad.deadline_concepts
         WHERE slug = 'reply-to-cross-appeal'
       )
   AND proceeding_type_code = 'UPC_APP';

-- 5. Restore cms-eingang.gericht.anordnung →
--    request-for-discretionary-review | NULL (sort 100).
DELETE FROM paliad.event_category_concepts
 WHERE event_category_id = (
        SELECT id FROM paliad.event_categories
         WHERE slug = 'cms-eingang.gericht.anordnung'
       )
   AND concept_id = (
        SELECT id FROM paliad.deadline_concepts
         WHERE slug = 'request-for-discretionary-review'
       )
   AND proceeding_type_code = 'UPC_APP_ORDERS';

INSERT INTO paliad.event_category_concepts
        (event_category_id, concept_id, proceeding_type_code, sort_order)
SELECT  ec.id, c.id, NULL, 100
   FROM paliad.event_categories  ec
   JOIN paliad.deadline_concepts c
        ON c.slug = 'request-for-discretionary-review'
  WHERE ec.slug = 'cms-eingang.gericht.anordnung'
    ON CONFLICT (event_category_id, concept_id, proceeding_type_code)
       DO NOTHING;

-- 4. Restore ich-moechte-einreichen.berufung.upc-coa-orders →
--    application-for-leave-to-appeal | UPC_APP_ORDERS (sort 200).
DELETE FROM paliad.event_category_concepts
 WHERE event_category_id = (
        SELECT id FROM paliad.event_categories
         WHERE slug = 'ich-moechte-einreichen.berufung.upc-coa-orders'
       )
   AND concept_id = (
        SELECT id FROM paliad.deadline_concepts
         WHERE slug = 'request-for-discretionary-review'
       )
   AND proceeding_type_code = 'UPC_APP_ORDERS';

INSERT INTO paliad.event_category_concepts
        (event_category_id, concept_id, proceeding_type_code, sort_order)
SELECT  ec.id, c.id, 'UPC_APP_ORDERS', 200
   FROM paliad.event_categories  ec
   JOIN paliad.deadline_concepts c
        ON c.slug = 'application-for-leave-to-appeal'
  WHERE ec.slug = 'ich-moechte-einreichen.berufung.upc-coa-orders'
    ON CONFLICT (event_category_id, concept_id, proceeding_type_code)
       DO NOTHING;

-- 3. Restore the wrong notice-of-appeal | UPC_COST_APPEAL rows the v3
--    seed had, and drop the leave-to-appeal + notice-of-appeal pair we
--    inserted.
DELETE FROM paliad.event_category_concepts
 WHERE event_category_id IN (
        SELECT id FROM paliad.event_categories
         WHERE slug IN (
            'cms-eingang.gericht.kostenfestsetzung',
            'beschluss-entscheidung.kostenfestsetzung',
            'ich-moechte-einreichen.berufung.upc-cost'
         )
       )
   AND (
        (concept_id = (SELECT id FROM paliad.deadline_concepts
                        WHERE slug = 'application-for-leave-to-appeal')
         AND proceeding_type_code = 'UPC_COST_APPEAL')
     OR (concept_id = (SELECT id FROM paliad.deadline_concepts
                        WHERE slug = 'notice-of-appeal')
         AND proceeding_type_code = 'UPC_APP')
       );

INSERT INTO paliad.event_category_concepts
        (event_category_id, concept_id, proceeding_type_code, sort_order)
SELECT  ec.id, c.id, 'UPC_COST_APPEAL', 100
   FROM (VALUES
        ('cms-eingang.gericht.kostenfestsetzung'),
        ('beschluss-entscheidung.kostenfestsetzung'),
        ('ich-moechte-einreichen.berufung.upc-cost')
   ) AS m(leaf_slug)
   JOIN paliad.event_categories  ec ON ec.slug = m.leaf_slug
   JOIN paliad.deadline_concepts c  ON c.slug = 'notice-of-appeal'
    ON CONFLICT (event_category_id, concept_id, proceeding_type_code)
       DO NOTHING;

-- 2. Restore notice-of-defence-intention | UPC_INF on
--    cms-eingang.gegenseite.upc-inf.klageschrift.
INSERT INTO paliad.event_category_concepts
        (event_category_id, concept_id, proceeding_type_code, sort_order)
SELECT  ec.id, c.id, 'UPC_INF', 100
   FROM paliad.event_categories  ec
   JOIN paliad.deadline_concepts c
        ON c.slug = 'notice-of-defence-intention'
  WHERE ec.slug = 'cms-eingang.gegenseite.upc-inf.klageschrift'
    ON CONFLICT (event_category_id, concept_id, proceeding_type_code)
       DO NOTHING;

-- 1. Restore response-to-preliminary-opinion | DE_INF on
--    cms-eingang.gericht.hinweisbeschluss.
INSERT INTO paliad.event_category_concepts
        (event_category_id, concept_id, proceeding_type_code, sort_order)
SELECT  ec.id, c.id, 'DE_INF', 200
   FROM paliad.event_categories  ec
   JOIN paliad.deadline_concepts c
        ON c.slug = 'response-to-preliminary-opinion'
  WHERE ec.slug = 'cms-eingang.gericht.hinweisbeschluss'
    ON CONFLICT (event_category_id, concept_id, proceeding_type_code)
       DO NOTHING;

COMMIT;
