-- Theme taxonomy table (fixed, seeded once)
CREATE TABLE "theme" (
  "id"       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  "slug"     text NOT NULL UNIQUE,
  "label_en" text NOT NULL,
  "label_fr" text NOT NULL,
  "emoji"    text NOT NULL
);

-- Seed fixed taxonomy
INSERT INTO "theme" ("slug", "label_en", "label_fr", "emoji") VALUES
  ('sciences-naturelles',      'Natural sciences',         'Sciences naturelles',     '🔬'),
  ('mathematiques',            'Mathematics',              'Mathématiques',           '∑'),
  ('histoire',                 'History',                  'Histoire',                '📜'),
  ('geographie',               'Geography',                'Géographie',              '🌍'),
  ('langues-litterature',      'Languages & literature',   'Langues & littérature',   '📖'),
  ('arts-culture',             'Arts & culture',           'Arts & culture',          '🎨'),
  ('technologie-informatique', 'Technology',               'Technologie',             '💻'),
  ('philosophie-ethique',      'Philosophy & ethics',      'Philosophie & éthique',   '💭'),
  ('economie-societe',         'Economics & society',      'Économie & société',      '📊'),
  ('sante-medecine',           'Health & medicine',        'Santé & médecine',        '🏥'),
  ('sport-activite',           'Sport & activity',         'Sport & activité',        '⚡'),
  ('autre',                    'Other',                    'Autre',                   '✦');

-- Blueprint ↔ theme join
CREATE TABLE "blueprint_theme" (
  "blueprint_id" uuid NOT NULL REFERENCES "blueprint"("id") ON DELETE CASCADE,
  "theme_id"     uuid NOT NULL REFERENCES "theme"("id") ON DELETE CASCADE,
  PRIMARY KEY ("blueprint_id", "theme_id")
);