-- t-paliad-133 Phase A: event taxonomy schema for the Fristenrechner v3
-- decision tree (Pathway B / B1) AND a bilateral-rule flag for the
-- party-perspective selector.
--
-- Three artefacts in one migration (additive, no breaking changes):
--
--   1. paliad.event_categories — the decision-tree taxonomy. Recursive
--      tree (parent_id self-FK) of "what happened" nodes. Internal
--      nodes carry a step_question_de/en (the question to ask under
--      this node). Leaves are user-actionable events that map to one
--      or more concepts via the junction below.
--
--   2. paliad.event_category_concepts — many-to-many junction from
--      taxonomy leaves to deadline_concepts. Optional
--      proceeding_type_code narrows the card pills at this leaf to a
--      specific context (e.g. "Hinweisbeschluss" → response-to-
--      preliminary-opinion narrowed to DE_NULL only).
--
--   3. paliad.deadline_rules.is_bilateral — new bool flag for genuinely-
--      bilateral rules (Schriftsatzfristen with Anwaltszwang, joint
--      Mängelbeseitigung). When true AND primary_party='both', the rule
--      mirrors into both party columns of the columns view. Otherwise
--      'both' → the rule applies to whichever party took the action.
--
-- Seed data + bilateral backfill ship in migrations 049 / 050.
--
-- Design ref: docs/plans/unified-fristenrechner-v3.md §4.1 + §10 Q12.

-- ============================================================================
-- 1. paliad.event_categories — recursive taxonomy tree
-- ============================================================================

CREATE TABLE paliad.event_categories (
    id                uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    parent_id         uuid REFERENCES paliad.event_categories(id) ON DELETE CASCADE,
    slug              text NOT NULL UNIQUE,
    label_de          text NOT NULL,
    label_en          text NOT NULL,
    description_de    text,
    description_en    text,
    step_question_de  text,
    step_question_en  text,
    icon              text,
    sort_order        int  NOT NULL DEFAULT 100,
    is_leaf           bool NOT NULL DEFAULT false,
    is_active         bool NOT NULL DEFAULT true,
    created_at        timestamptz NOT NULL DEFAULT now(),
    updated_at        timestamptz NOT NULL DEFAULT now()
);

COMMENT ON TABLE paliad.event_categories IS
    'Decision-tree taxonomy for Fristenrechner v3 Pathway B / B1. Each '
    'row is a node in a recursive tree of "what happened" categories. '
    'The tree shape is data-driven — depth is unlimited per m''s '
    '2026-05-05 spec lock.';

COMMENT ON COLUMN paliad.event_categories.slug IS
    'Materialised path-with-dots (e.g. cms-eingang.gericht.hinweisbeschluss). '
    'Unique across the table. Drives URL bookmarks ?b1=<slug>.';

COMMENT ON COLUMN paliad.event_categories.step_question_de IS
    'The question this node''s CHILDREN answer. Rendered above the button '
    'row for the next step. NULL on leaves (terminal events that map to '
    'concepts via paliad.event_category_concepts).';

COMMENT ON COLUMN paliad.event_categories.is_leaf IS
    'True when this node has no children and produces concept outcomes '
    'via the junction. False on internal navigation nodes.';

COMMENT ON COLUMN paliad.event_categories.icon IS
    'Single emoji or icon slug rendered on the button face. Optional.';

CREATE INDEX event_categories_parent_id ON paliad.event_categories (parent_id);
CREATE INDEX event_categories_slug      ON paliad.event_categories (slug);
CREATE INDEX event_categories_active    ON paliad.event_categories (is_active);

-- updated_at is managed app-side (consistent with paliad.deadline_concepts
-- and other tables in this schema). No DB-level trigger.

-- ============================================================================
-- 2. paliad.event_category_concepts — leaf → concept junction
-- ============================================================================

CREATE TABLE paliad.event_category_concepts (
    id                  uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    event_category_id   uuid NOT NULL
                          REFERENCES paliad.event_categories(id) ON DELETE CASCADE,
    concept_id          uuid NOT NULL
                          REFERENCES paliad.deadline_concepts(id) ON DELETE CASCADE,
    proceeding_type_code text,
    sort_order          int NOT NULL DEFAULT 100,
    -- NULLS NOT DISTINCT: treat (leaf, concept, NULL) as a single tuple
    -- so we can't accidentally seed the same concept twice with no
    -- proceeding-narrowing. Requires PostgreSQL 15+ (Supabase ships ≥15).
    UNIQUE NULLS NOT DISTINCT (event_category_id, concept_id, proceeding_type_code)
);

COMMENT ON TABLE paliad.event_category_concepts IS
    'Many-to-many junction: an event_category leaf produces one or more '
    'deadline_concepts as its candidate Frist outcomes. Optional '
    'proceeding_type_code narrows the resulting card pills to that '
    'context only.';

COMMENT ON COLUMN paliad.event_category_concepts.proceeding_type_code IS
    'NULL = all contexts of the concept apply (the result card shows '
    'every pill). Set = limit the card''s pill set to this proceeding '
    '(e.g. leaf cms-eingang.gericht.hinweisbeschluss → response-to-'
    'preliminary-opinion narrowed to DE_NULL only).';

CREATE INDEX event_category_concepts_category
    ON paliad.event_category_concepts (event_category_id);
CREATE INDEX event_category_concepts_concept
    ON paliad.event_category_concepts (concept_id);
CREATE INDEX event_category_concepts_proceeding
    ON paliad.event_category_concepts (proceeding_type_code)
    WHERE proceeding_type_code IS NOT NULL;

-- ============================================================================
-- 3. paliad.deadline_rules.is_bilateral — genuinely-bilateral flag
-- ============================================================================

ALTER TABLE paliad.deadline_rules
    ADD COLUMN is_bilateral bool NOT NULL DEFAULT false;

COMMENT ON COLUMN paliad.deadline_rules.is_bilateral IS
    'When true AND primary_party=''both'', the rule mirrors into both '
    'party columns of the columns view (genuinely-bilateral rules: '
    'Schriftsatzfristen with Anwaltszwang, joint Mängelbeseitigung, '
    'mündliche Verhandlung). When false (default) AND primary_party='
    '''both'', the rule applies only to the side that took the action '
    '(determined from the parent rule''s party or, if root, from the '
    'perspective selector). Backfilled in migration 050 for the small '
    'set of genuinely-bilateral rules in the existing corpus.';

CREATE INDEX deadline_rules_is_bilateral
    ON paliad.deadline_rules (is_bilateral)
    WHERE is_bilateral = true;
