shinkan-jinkendo/backend/migrations/007_exercise_catalogs.sql

-- Migration 007: Exercise Catalogs (Admin-verwaltbare Stammdaten)
-- Erstellt: 2026-04-22
-- Beschreibung: Dynamische Kataloge statt Hard-Coding

-- Fokusbereiche (statt hard-coded)
CREATE TABLE IF NOT EXISTS focus_areas (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    abbreviation VARCHAR(20),
    description TEXT,
    color VARCHAR(20),  -- Für UI (z.B. #1D9E75)
    icon VARCHAR(50),   -- Emoji oder Icon-Name
    sort_order INT,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_focus_areas_status ON focus_areas(status);

-- Trainingsstile (NEU: Shotokan, Goju-Ryu, etc.)
CREATE TABLE IF NOT EXISTS training_styles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    abbreviation VARCHAR(20),
    description TEXT,
    parent_style_id INT REFERENCES training_styles(id),  -- z.B. Shotokan → Karate
    sort_order INT,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_training_styles_status ON training_styles(status);
CREATE INDEX IF NOT EXISTS idx_training_styles_parent ON training_styles(parent_style_id);

-- Trainingscharaktere (statt hard-coded)
CREATE TABLE IF NOT EXISTS training_characters (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    sort_order INT,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_training_characters_status ON training_characters(status);

-- Fähigkeitsbereiche (Kategorien für Skills)
CREATE TABLE IF NOT EXISTS skill_categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    parent_category_id INT REFERENCES skill_categories(id),  -- Hierarchie möglich
    sort_order INT,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_skill_categories_status ON skill_categories(status);
CREATE INDEX IF NOT EXISTS idx_skill_categories_parent ON skill_categories(parent_category_id);

-- Skills erweitern (Verknüpfung mit Kategorien)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='skills' AND column_name='category_id') THEN
        ALTER TABLE skills ADD COLUMN category_id INT REFERENCES skill_categories(id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='skills' AND column_name='parent_skill_id') THEN
        ALTER TABLE skills ADD COLUMN parent_skill_id INT REFERENCES skills(id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='skills' AND column_name='level') THEN
        ALTER TABLE skills ADD COLUMN level INT CHECK (level BETWEEN 1 AND 10);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='skills' AND column_name='sort_order') THEN
        ALTER TABLE skills ADD COLUMN sort_order INT;
    END IF;
END $$;

CREATE INDEX IF NOT EXISTS idx_skills_category ON skills(category_id);
CREATE INDEX IF NOT EXISTS idx_skills_parent ON skills(parent_skill_id);

-- Exercises erweitern
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='exercises' AND column_name='training_style_id') THEN
        ALTER TABLE exercises ADD COLUMN training_style_id INT REFERENCES training_styles(id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='exercises' AND column_name='training_character_id') THEN
        ALTER TABLE exercises ADD COLUMN training_character_id INT REFERENCES training_characters(id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='exercises' AND column_name='focus_area_id') THEN
        ALTER TABLE exercises ADD COLUMN focus_area_id INT REFERENCES focus_areas(id);
    END IF;
END $$;

-- Alte text-basierte Spalten können später deprecated werden
-- ALTER TABLE exercises DROP COLUMN focus_area;  -- später, nach Migration
-- ALTER TABLE exercises DROP COLUMN training_character;  -- später, nach Migration

CREATE INDEX IF NOT EXISTS idx_exercises_style ON exercises(training_style_id);
CREATE INDEX IF NOT EXISTS idx_exercises_character ON exercises(training_character_id);
CREATE INDEX IF NOT EXISTS idx_exercises_focus_area ON exercises(focus_area_id);

-- Trainer-Fokusbereich-Zuordnung (Welcher Trainer arbeitet in welchen Fokusbereichen?)
CREATE TABLE IF NOT EXISTS trainer_focus_areas (
    id SERIAL PRIMARY KEY,
    profile_id INT REFERENCES profiles(id) ON DELETE CASCADE,
    focus_area_id INT REFERENCES focus_areas(id) ON DELETE CASCADE,
    is_primary BOOLEAN DEFAULT false,  -- Primärer Fokusbereich des Trainers
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(profile_id, focus_area_id)
);

CREATE INDEX IF NOT EXISTS idx_trainer_focus_areas_profile ON trainer_focus_areas(profile_id);
CREATE INDEX IF NOT EXISTS idx_trainer_focus_areas_focus ON trainer_focus_areas(focus_area_id);

-- Basis-Daten einfügen
INSERT INTO focus_areas (name, abbreviation, description, color, icon, sort_order) VALUES
('Karate', 'KAR', 'Traditionelles Karate', '#1D9E75', '🥋', 1),
('Selbstverteidigung', 'SV', 'Praktische Selbstverteidigung', '#D85A30', '🛡️', 2),
('Gewaltschutz', 'GS', 'Gewaltprävention und Deeskalation', '#EF9F27', '🤝', 3)
ON CONFLICT (name) DO NOTHING;

INSERT INTO training_styles (name, abbreviation, description, sort_order) VALUES
('Shotokan', 'SHO', 'Shotokan-Karate', 1),
('Goju-Ryu', 'GJR', 'Goju-Ryu-Karate', 2),
('Wado-Ryu', 'WAD', 'Wado-Ryu-Karate', 3),
('Shito-Ryu', 'SHI', 'Shito-Ryu-Karate', 4),
('Kyokushin', 'KYO', 'Kyokushin-Karate', 5)
ON CONFLICT (name) DO NOTHING;

INSERT INTO training_characters (name, description, sort_order) VALUES
('Grundlage', 'Einführung und Basisvermittlung', 1),
('Aufbau', 'Aufbauendes Training', 2),
('Vertiefung', 'Vertiefung und Spezialisierung', 3),
('Festigung', 'Wiederholung und Festigung', 4),
('Diagnose', 'Leistungsdiagnose und Test', 5),
('Wettkampf', 'Wettkampfvorbereitung', 6)
ON CONFLICT (name) DO NOTHING;

INSERT INTO skill_categories (name, description, sort_order) VALUES
('Kihon', 'Grundschultechniken', 1),
('Kumite', 'Kampftechniken', 2),
('Kata', 'Formen', 3),
('Selbstverteidigung', 'SV-Techniken', 4),
('Fitness', 'Kondition und Athletik', 5),
('Mental', 'Mentale Fähigkeiten', 6)
ON CONFLICT (name) DO NOTHING;