-- t-paliad-122: courts entity + per-country holiday computation.
--
-- m's design call (2026-05-05 18:51): jurisdiction lives on courts (forums),
-- not on proceeding types. Same UPC_INF can sit in München LD (DE), Paris LD
-- (FR) or Helsinki LD (FI) — three calendars, one proceeding.
--
-- Live-data finding (cronus, 2026-05-06): paliad.holidays today carries 33
-- rows with country='UPC' — the t-paliad-121 UPC summer/winter judicial
-- vacation entries. 'UPC' isn't an ISO-3166 country; it's a supranational
-- regime that applies to UPC courts regardless of which country the LD sits
-- in. The clean model is two dimensions:
--   country (ISO-3166 alpha-2, the national holiday calendar)
--   regime  (supranational layer: 'UPC' | 'EPO', NULL otherwise)
-- Each holiday row carries at least one. A UPC LD München has country='DE'
-- + regime='UPC', so its applicable holidays are German national rows + UPC
-- regime rows.
--
-- This migration:
--   1. Creates paliad.countries lookup (ISO-3166 alpha-2).
--   2. Adds paliad.holidays.regime, makes country nullable, migrates the 33
--      UPC rows from country='UPC' → country=NULL + regime='UPC'.
--   3. Replaces UNIQUE (date, name, country) with UNIQUE on the new tuple.
--   4. Adds FK paliad.holidays.country → paliad.countries.code.
--   5. Creates paliad.courts (id, code, name, country, regime, court_type,
--      parent_id, sort_order, is_active).
--   6. Seeds 41 courts from the static catalog in internal/handlers/courts.go.

-- ============================================================================
-- 1. paliad.countries
-- ============================================================================

CREATE TABLE paliad.countries (
    code       text PRIMARY KEY,         -- ISO-3166 alpha-2
    name_de    text NOT NULL,
    name_en    text NOT NULL,
    is_active  boolean NOT NULL DEFAULT true,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO paliad.countries (code, name_de, name_en) VALUES
    ('DE', 'Deutschland',              'Germany'),
    ('FR', 'Frankreich',                'France'),
    ('IT', 'Italien',                   'Italy'),
    ('NL', 'Niederlande',               'Netherlands'),
    ('BE', 'Belgien',                   'Belgium'),
    ('FI', 'Finnland',                  'Finland'),
    ('PT', 'Portugal',                  'Portugal'),
    ('AT', 'Österreich',                'Austria'),
    ('SI', 'Slowenien',                 'Slovenia'),
    ('DK', 'Dänemark',                  'Denmark'),
    ('SE', 'Schweden',                  'Sweden'),
    ('LU', 'Luxemburg',                 'Luxembourg'),
    ('GB', 'Vereinigtes Königreich',    'United Kingdom');

-- ============================================================================
-- 2. paliad.holidays: add regime, migrate UPC rows, make country nullable
-- ============================================================================

ALTER TABLE paliad.holidays ADD COLUMN regime text;
ALTER TABLE paliad.holidays ALTER COLUMN country DROP NOT NULL;
ALTER TABLE paliad.holidays ALTER COLUMN country DROP DEFAULT;

-- Migrate the t-paliad-121 UPC vacation rows: country='UPC' was a placeholder
-- for "applies to UPC proceedings regardless of country". Move to regime.
UPDATE paliad.holidays
   SET country = NULL,
       regime  = 'UPC'
 WHERE country = 'UPC';

-- Replace the old uniqueness constraint with one over the new tuple.
ALTER TABLE paliad.holidays DROP CONSTRAINT IF EXISTS holidays_date_name_country_key;
ALTER TABLE paliad.holidays
    ADD CONSTRAINT holidays_date_name_country_regime_key
    UNIQUE (date, name, country, regime);

-- Every row must carry at least one of (country, regime). country='DE'
-- public holidays have regime=NULL; UPC vacation rows have country=NULL,
-- regime='UPC'; future EPO closure rows can have country=NULL, regime='EPO'.
ALTER TABLE paliad.holidays
    ADD CONSTRAINT holidays_country_or_regime_chk
    CHECK (country IS NOT NULL OR regime IS NOT NULL);

-- FK on country only enforced when NOT NULL (Postgres default).
ALTER TABLE paliad.holidays
    ADD CONSTRAINT holidays_country_fk
    FOREIGN KEY (country) REFERENCES paliad.countries(code);

-- Constrain regime to a small known set so typos can't silently create a
-- new orphan regime ('upc' instead of 'UPC').
ALTER TABLE paliad.holidays
    ADD CONSTRAINT holidays_regime_chk
    CHECK (regime IS NULL OR regime IN ('UPC', 'EPO'));

CREATE INDEX holidays_regime_idx ON paliad.holidays(regime) WHERE regime IS NOT NULL;

-- ============================================================================
-- 3. paliad.courts
-- ============================================================================

CREATE TABLE paliad.courts (
    id          text PRIMARY KEY,                          -- kebab-case stable ID, mirrors internal/handlers/courts.go
    code        text NOT NULL,                             -- short display code, e.g. "UPC-LD-Paris"
    name_de     text NOT NULL,
    name_en     text NOT NULL,
    country     text NOT NULL REFERENCES paliad.countries(code),
    regime      text,                                      -- 'UPC' | 'EPO' | NULL
    court_type  text NOT NULL,                             -- 'UPC-LD' | 'UPC-CD' | 'UPC-CoA' | 'UPC-RD' | 'DE-LG' | 'DE-OLG' | 'DE-BGH' | 'DE-BPatG' | 'DE-DPMA' | 'EPA' | 'NAT'
    parent_id   text REFERENCES paliad.courts(id),
    sort_order  integer NOT NULL DEFAULT 0,
    is_active   boolean NOT NULL DEFAULT true,
    created_at  timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT  courts_regime_chk CHECK (regime IS NULL OR regime IN ('UPC', 'EPO'))
);

CREATE INDEX courts_country_idx     ON paliad.courts(country);
CREATE INDEX courts_court_type_idx  ON paliad.courts(court_type);
CREATE INDEX courts_regime_idx      ON paliad.courts(regime) WHERE regime IS NOT NULL;

-- Seed from the static catalog in internal/handlers/courts.go (41 entries,
-- IDs verbatim so cross-references stay diffable). UPC courts get regime
-- 'UPC' (so UPC vacation rows apply); EPA/EPO courts get regime 'EPO'
-- (reserved — no EPO regime rows seeded today). National + DE courts have
-- regime NULL.
INSERT INTO paliad.courts (id, code, name_de, name_en, country, regime, court_type, sort_order) VALUES
    -- UPC Court of Appeal
    ('upc-coa-luxembourg',  'UPC-CoA',                'UPC Berufungsgericht — Luxemburg',                          'UPC Court of Appeal — Luxembourg',                       'LU', 'UPC', 'UPC-CoA',  10),

    -- UPC Central Division
    ('upc-cd-paris',        'UPC-CD-Paris',           'UPC Zentralkammer — Sitz Paris',                            'UPC Central Division — Paris Seat',                      'FR', 'UPC', 'UPC-CD',   20),
    ('upc-cd-munich',       'UPC-CD-Munich',          'UPC Zentralkammer — Sektion München',                       'UPC Central Division — Munich Section',                  'DE', 'UPC', 'UPC-CD',   21),
    ('upc-cd-milan',        'UPC-CD-Milan',           'UPC Zentralkammer — Sektion Mailand',                       'UPC Central Division — Milan Section',                   'IT', 'UPC', 'UPC-CD',   22),

    -- UPC Local Divisions (HLC München first, then DE-tier, then international)
    ('upc-ld-muenchen',     'UPC-LD-München',         'UPC Lokalkammer München',                                   'UPC Local Division Munich',                              'DE', 'UPC', 'UPC-LD',   30),
    ('upc-ld-duesseldorf',  'UPC-LD-Düsseldorf',      'UPC Lokalkammer Düsseldorf',                                'UPC Local Division Düsseldorf',                          'DE', 'UPC', 'UPC-LD',   31),
    ('upc-ld-mannheim',     'UPC-LD-Mannheim',        'UPC Lokalkammer Mannheim',                                  'UPC Local Division Mannheim',                            'DE', 'UPC', 'UPC-LD',   32),
    ('upc-ld-hamburg',      'UPC-LD-Hamburg',         'UPC Lokalkammer Hamburg',                                   'UPC Local Division Hamburg',                             'DE', 'UPC', 'UPC-LD',   33),
    ('upc-ld-paris',        'UPC-LD-Paris',           'UPC Lokalkammer Paris',                                     'UPC Local Division Paris',                               'FR', 'UPC', 'UPC-LD',   34),
    ('upc-ld-milan',        'UPC-LD-Milan',           'UPC Lokalkammer Mailand',                                   'UPC Local Division Milan',                               'IT', 'UPC', 'UPC-LD',   35),
    ('upc-ld-denhaag',      'UPC-LD-DenHaag',         'UPC Lokalkammer Den Haag',                                  'UPC Local Division The Hague',                           'NL', 'UPC', 'UPC-LD',   36),
    ('upc-ld-brussels',     'UPC-LD-Brussels',        'UPC Lokalkammer Brüssel',                                   'UPC Local Division Brussels',                            'BE', 'UPC', 'UPC-LD',   37),
    ('upc-ld-helsinki',     'UPC-LD-Helsinki',        'UPC Lokalkammer Helsinki',                                  'UPC Local Division Helsinki',                            'FI', 'UPC', 'UPC-LD',   38),
    ('upc-ld-lisbon',       'UPC-LD-Lisbon',          'UPC Lokalkammer Lissabon',                                  'UPC Local Division Lisbon',                              'PT', 'UPC', 'UPC-LD',   39),
    ('upc-ld-wien',         'UPC-LD-Wien',            'UPC Lokalkammer Wien',                                      'UPC Local Division Vienna',                              'AT', 'UPC', 'UPC-LD',   40),
    ('upc-ld-ljubljana',    'UPC-LD-Ljubljana',       'UPC Lokalkammer Ljubljana',                                 'UPC Local Division Ljubljana',                           'SI', 'UPC', 'UPC-LD',   41),
    ('upc-ld-kopenhagen',   'UPC-LD-Kopenhagen',      'UPC Lokalkammer Kopenhagen',                                'UPC Local Division Copenhagen',                          'DK', 'UPC', 'UPC-LD',   42),

    -- UPC Regional Divisions
    ('upc-rd-nordic-baltic','UPC-RD-Nordic',          'UPC Regionalkammer Nord-Baltikum (Stockholm)',              'UPC Nordic-Baltic Regional Division (Stockholm)',        'SE', 'UPC', 'UPC-RD',   50),

    -- DE Landgerichte (regime NULL — German federal holidays only)
    ('de-lg-muenchen1',     'LG-München',             'Landgericht München I — Patentstreitkammern (7./21. Zivilkammer)',   'Regional Court Munich I — Patent Chambers (7th/21st)',          'DE', NULL,  'DE-LG',    60),
    ('de-lg-duesseldorf',   'LG-Düsseldorf',          'Landgericht Düsseldorf — Patentstreitkammern (4a/4b/4c Zivilkammer)','Regional Court Düsseldorf — Patent Chambers (4a/4b/4c)',         'DE', NULL,  'DE-LG',    61),
    ('de-lg-mannheim',      'LG-Mannheim',            'Landgericht Mannheim — Patentstreitkammern (2./7. Zivilkammer)',     'Regional Court Mannheim — Patent Chambers (2nd/7th)',           'DE', NULL,  'DE-LG',    62),
    ('de-lg-hamburg',       'LG-Hamburg',             'Landgericht Hamburg — Patentstreitkammern',                          'Regional Court Hamburg — Patent Chambers',                       'DE', NULL,  'DE-LG',    63),

    -- DE Oberlandesgerichte
    ('de-olg-duesseldorf',  'OLG-Düsseldorf',         'Oberlandesgericht Düsseldorf — 2./15. Zivilsenat',                   'Higher Regional Court Düsseldorf — 2nd/15th Civil Senate',       'DE', NULL,  'DE-OLG',   70),
    ('de-olg-muenchen',     'OLG-München',            'Oberlandesgericht München — 6. Zivilsenat (Patentberufungen)',       'Higher Regional Court Munich — 6th Civil Senate (patent appeals)','DE',NULL,  'DE-OLG',   71),
    ('de-olg-karlsruhe',    'OLG-Karlsruhe',          'Oberlandesgericht Karlsruhe — 6. Zivilsenat (Patentberufungen)',     'Higher Regional Court Karlsruhe — 6th Civil Senate (patent appeals)','DE',NULL,'DE-OLG',  72),

    -- DE Bundesgerichtshof / BPatG / DPMA
    ('de-bgh',              'BGH',                    'Bundesgerichtshof — X. Zivilsenat',                                  'Federal Court of Justice — Xth Civil Senate',                    'DE', NULL,  'DE-BGH',   80),
    ('de-bpatg',            'BPatG',                  'Bundespatentgericht (BPatG)',                                        'Federal Patent Court (BPatG)',                                   'DE', NULL,  'DE-BPatG', 81),
    ('de-dpma',             'DPMA',                   'Deutsches Patent- und Markenamt (DPMA) — Hauptsitz München',         'German Patent and Trade Mark Office (DPMA) — Munich HQ',         'DE', NULL,  'DE-DPMA',  82),

    -- EPA / EPO (regime 'EPO' reserved for future EPO-internal closure days)
    ('epa-munich',          'EPA-München',            'Europäisches Patentamt — Hauptsitz München (Isar-Gebäude)',          'European Patent Office — Munich Headquarters (Isar Building)',   'DE', 'EPO', 'EPA',      90),
    ('epa-boards-haar',     'EPA-Beschwerdekammern',  'EPA Beschwerdekammern — Haar bei München',                           'EPO Boards of Appeal — Haar (Munich)',                           'DE', 'EPO', 'EPA',      91),
    ('epa-hague',           'EPA-DenHaag',            'Europäisches Patentamt — Zweigstelle Den Haag (Rijswijk)',           'European Patent Office — Branch The Hague (Rijswijk)',           'NL', 'EPO', 'EPA',      92),

    -- National courts (NL, GB, FR, IT) — country only, no supranational regime
    ('nl-rechtbank-denhaag',  'NL-Rechtbank-DenHaag',   'Rechtbank Den Haag — Patentkamer',                                  'District Court The Hague — Patent Chamber',                      'NL', NULL, 'NAT',     100),
    ('nl-gerechtshof-denhaag','NL-Gerechtshof-DenHaag', 'Gerechtshof Den Haag — Berufungsinstanz Patentsachen',              'Court of Appeal The Hague — Patent Appeals',                     'NL', NULL, 'NAT',     101),
    ('uk-patents-court',      'UK-Patents-Court',       'UK Patents Court (High Court) — Rolls Building',                    'UK Patents Court (High Court) — Rolls Building',                 'GB', NULL, 'NAT',     110),
    ('uk-ipec',               'UK-IPEC',                'Intellectual Property Enterprise Court (IPEC)',                     'Intellectual Property Enterprise Court (IPEC)',                  'GB', NULL, 'NAT',     111),
    ('uk-ipo',                'UKIPO',                  'UK Intellectual Property Office (UKIPO)',                           'UK Intellectual Property Office (UKIPO)',                        'GB', NULL, 'NAT',     112),
    ('fr-tj-paris',           'FR-TJ-Paris',            'Tribunal judiciaire de Paris — Pôle propriété intellectuelle (3e chambre)','Paris Judicial Court — Intellectual Property Division (3rd Chamber)','FR', NULL, 'NAT', 120),
    ('fr-cour-appel-paris',   'FR-CA-Paris',            'Cour d''appel de Paris — Pôle 5 chambre 1 (propriété intellectuelle)',     'Paris Court of Appeal — Division 5 Chamber 1 (IP)',                  'FR', NULL, 'NAT', 121),
    ('fr-inpi',               'FR-INPI',                'INPI — Institut national de la propriété industrielle',            'INPI — French National Industrial Property Institute',           'FR', NULL, 'NAT',     122),
    ('it-tribunale-milano',   'IT-Tribunale-Milano',    'Tribunale di Milano — Sezioni Specializzate Impresa (14./15. Sektion)','Court of Milan — Specialised Business Sections (14th/15th)',  'IT', NULL, 'NAT',     130),
    ('it-tribunale-torino',   'IT-Tribunale-Torino',    'Tribunale di Torino — Sezione Specializzata Impresa',               'Court of Turin — Specialised Business Section',                  'IT', NULL, 'NAT',     131);
