shinkan-jinkendo/backend/migrations/022_skills_schema_complete.sql

-- Migration 022: Skills Schema Complete - Hauptkategorien + Fokusbereich + Level-Definitionen
-- Purpose: Erweitert Skills-Schema um vollständige Kategorisierung für Prod-Import
-- Date: 2026-04-27

-- ======================================================================
-- 1. Haupt-Kategorien (KARATE Fähigkeiten / ALLGEMEINE sportliche Fähigkeiten)
-- ======================================================================

CREATE TABLE IF NOT EXISTS skill_main_categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    sort_order INT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- ======================================================================
-- 2. Unterkategorien erweitern (Beziehung zu Hauptkategorie)
-- ======================================================================

-- Slug für URL-friendly Namen
ALTER TABLE skill_categories
    ADD COLUMN IF NOT EXISTS slug VARCHAR(50);

-- Beziehung zu Hauptkategorie
ALTER TABLE skill_categories
    ADD COLUMN IF NOT EXISTS main_category_id INT REFERENCES skill_main_categories(id);

-- Unique constraint für slug
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_constraint WHERE conname = 'skill_categories_slug_unique'
    ) THEN
        ALTER TABLE skill_categories ADD CONSTRAINT skill_categories_slug_unique UNIQUE (slug);
    END IF;
END $$;

-- ======================================================================
-- 3. Skills erweitern (Hauptkategorie + Fokusbereich)
-- ======================================================================

-- Beziehung zu Hauptkategorie (für direkte Queries)
ALTER TABLE skills
    ADD COLUMN IF NOT EXISTS main_category_id INT REFERENCES skill_main_categories(id);

-- Fokusbereich als JSONB Array (z.B. ['karate'], ['universal'], ['karate', 'selbstverteidigung'])
ALTER TABLE skills
    ADD COLUMN IF NOT EXISTS focus_areas JSONB DEFAULT '[]'::jsonb;

-- ======================================================================
-- 4. Level-Definitionen (1-5 Beschreibungen aus Matrix)
-- ======================================================================

CREATE TABLE IF NOT EXISTS skill_level_definitions (
    id SERIAL PRIMARY KEY,
    skill_id INT NOT NULL REFERENCES skills(id) ON DELETE CASCADE,
    level INT NOT NULL CHECK (level BETWEEN 1 AND 5),
    description TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    UNIQUE (skill_id, level)
);

-- Index für schnelle Abfragen
CREATE INDEX IF NOT EXISTS idx_skill_level_definitions_skill_id
    ON skill_level_definitions(skill_id);

-- ======================================================================
-- 5. Indizes für Performance
-- ======================================================================

CREATE INDEX IF NOT EXISTS idx_skills_main_category_id
    ON skills(main_category_id);

CREATE INDEX IF NOT EXISTS idx_skills_category_id
    ON skills(category_id);

CREATE INDEX IF NOT EXISTS idx_skill_categories_main_category_id
    ON skill_categories(main_category_id);

-- ======================================================================
-- 6. Kommentare für Dokumentation
-- ======================================================================

COMMENT ON TABLE skill_main_categories IS 'Hauptkategorien: KARATE Fähigkeiten / ALLGEMEINE sportliche Fähigkeiten';
COMMENT ON COLUMN skills.focus_areas IS 'JSONB Array: [''karate''] = nur Karate, [''universal''] = alle Fokussbereiche';
COMMENT ON TABLE skill_level_definitions IS 'Level 1-5 Beschreibungen aus Fähigkeitsmatrix für jede Fähigkeit';