-- Migration 028: exercise_media (Upload/Embed laut Spec) + exercise_skills Typen für API v2
-- Datum: 2026-04-27

-- exercise_media: Metadaten + Embed
ALTER TABLE exercise_media ADD COLUMN IF NOT EXISTS file_size INT;
ALTER TABLE exercise_media ADD COLUMN IF NOT EXISTS mime_type VARCHAR(100);
ALTER TABLE exercise_media ADD COLUMN IF NOT EXISTS original_filename VARCHAR(300);
ALTER TABLE exercise_media ADD COLUMN IF NOT EXISTS embed_url TEXT;
ALTER TABLE exercise_media ADD COLUMN IF NOT EXISTS embed_platform VARCHAR(50);
ALTER TABLE exercise_media ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT NOW();

CREATE INDEX IF NOT EXISTS idx_exercise_media_exercise_sort ON exercise_media(exercise_id, sort_order);

-- exercise_skills: KI-Flag + benannte Stufen / Intensität (EXERCISES_API_SPEC)
ALTER TABLE exercise_skills ADD COLUMN IF NOT EXISTS ai_suggested BOOLEAN DEFAULT FALSE;

DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns c
    WHERE c.table_schema = 'public' AND c.table_name = 'exercise_skills'
      AND c.column_name = 'intensity' AND c.data_type IN ('integer', 'bigint', 'smallint')
  ) THEN
    ALTER TABLE exercise_skills DROP CONSTRAINT IF EXISTS exercise_skills_intensity_check;
    ALTER TABLE exercise_skills ALTER COLUMN intensity DROP DEFAULT;
    ALTER TABLE exercise_skills ALTER COLUMN intensity TYPE VARCHAR(10) USING
      CASE
        WHEN intensity IS NULL THEN NULL
        WHEN intensity::integer <= 2 THEN 'niedrig'
        WHEN intensity::integer = 3 THEN 'mittel'
        ELSE 'hoch'
      END;
  END IF;
END $$;

DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns c
    WHERE c.table_schema = 'public' AND c.table_name = 'exercise_skills'
      AND c.column_name = 'required_level' AND c.data_type IN ('integer', 'bigint', 'smallint')
  ) THEN
    ALTER TABLE exercise_skills ALTER COLUMN required_level TYPE VARCHAR(20) USING
      CASE WHEN required_level IS NULL THEN NULL ELSE required_level::text END;
  END IF;
END $$;

DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns c
    WHERE c.table_schema = 'public' AND c.table_name = 'exercise_skills'
      AND c.column_name = 'target_level' AND c.data_type IN ('integer', 'bigint', 'smallint')
  ) THEN
    ALTER TABLE exercise_skills ALTER COLUMN target_level TYPE VARCHAR(20) USING
      CASE WHEN target_level IS NULL THEN NULL ELSE target_level::text END;
  END IF;
END $$;

-- Volltext: Ziel einbeziehen (Wiki-Import nutzt oft nur goal)
CREATE OR REPLACE FUNCTION update_exercises_search_vector()
RETURNS trigger AS $func$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('german', COALESCE(NEW.title, '')), 'A') ||
        setweight(to_tsvector('german', COALESCE(NEW.summary, '')), 'B') ||
        setweight(to_tsvector('german', COALESCE(NEW.goal, '')), 'B') ||
        setweight(to_tsvector('german', COALESCE(NEW.execution, '')), 'C') ||
        setweight(to_tsvector('german', COALESCE(NEW.trainer_notes, '')), 'D');
    RETURN NEW;
END;
$func$ LANGUAGE plpgsql;