-- ============================================
-- Vocab Courses - Update für bestehende Installation
-- ============================================
-- Führe diese Queries aus, wenn die Tabellen bereits existieren
-- (z.B. wenn nur die Basis-Tabellen erstellt wurden)

-- ============================================
-- 1. chapter_id optional machen
-- ============================================
ALTER TABLE community.vocab_course_lesson
ALTER COLUMN chapter_id DROP NOT NULL;

-- ============================================
-- 2. Neue Spalten zu vocab_course_lesson hinzufügen
-- ============================================
ALTER TABLE community.vocab_course_lesson
ADD COLUMN IF NOT EXISTS week_number INTEGER,
ADD COLUMN IF NOT EXISTS day_number INTEGER,
ADD COLUMN IF NOT EXISTS lesson_type TEXT DEFAULT 'vocab',
ADD COLUMN IF NOT EXISTS audio_url TEXT,
ADD COLUMN IF NOT EXISTS cultural_notes TEXT,
ADD COLUMN IF NOT EXISTS target_minutes INTEGER,
ADD COLUMN IF NOT EXISTS target_score_percent INTEGER DEFAULT 80,
ADD COLUMN IF NOT EXISTS requires_review BOOLEAN DEFAULT false;

-- ============================================
-- 3. Neue Indizes hinzufügen
-- ============================================
CREATE INDEX IF NOT EXISTS vocab_course_lesson_week_idx
    ON community.vocab_course_lesson(course_id, week_number);
CREATE INDEX IF NOT EXISTS vocab_course_lesson_type_idx
    ON community.vocab_course_lesson(lesson_type);

-- ============================================
-- 4. Grammatik-Übungstabellen erstellen (falls noch nicht vorhanden)
-- ============================================

-- Grammatik-Übungstypen
CREATE TABLE IF NOT EXISTS community.vocab_grammar_exercise_type (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW()
);

-- Grammatik-Übungen
CREATE TABLE IF NOT EXISTS community.vocab_grammar_exercise (
    id SERIAL PRIMARY KEY,
    lesson_id INTEGER NOT NULL,
    exercise_type_id INTEGER NOT NULL,
    exercise_number INTEGER NOT NULL,
    title TEXT NOT NULL,
    instruction TEXT,
    question_data JSONB NOT NULL,
    answer_data JSONB NOT NULL,
    explanation TEXT,
    created_by_user_id INTEGER NOT NULL,
    created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW(),
    CONSTRAINT vocab_grammar_exercise_lesson_fk
        FOREIGN KEY (lesson_id)
        REFERENCES community.vocab_course_lesson(id)
        ON DELETE CASCADE,
    CONSTRAINT vocab_grammar_exercise_type_fk
        FOREIGN KEY (exercise_type_id)
        REFERENCES community.vocab_grammar_exercise_type(id)
        ON DELETE CASCADE,
    CONSTRAINT vocab_grammar_exercise_creator_fk
        FOREIGN KEY (created_by_user_id)
        REFERENCES community."user"(id)
        ON DELETE CASCADE,
    CONSTRAINT vocab_grammar_exercise_unique UNIQUE (lesson_id, exercise_number)
);

-- Fortschritt für Grammatik-Übungen
CREATE TABLE IF NOT EXISTS community.vocab_grammar_exercise_progress (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    exercise_id INTEGER NOT NULL,
    attempts INTEGER DEFAULT 0,
    correct_attempts INTEGER DEFAULT 0,
    last_attempt_at TIMESTAMP WITHOUT TIME ZONE,
    completed BOOLEAN DEFAULT false,
    completed_at TIMESTAMP WITHOUT TIME ZONE,
    CONSTRAINT vocab_grammar_exercise_progress_user_fk
        FOREIGN KEY (user_id)
        REFERENCES community."user"(id)
        ON DELETE CASCADE,
    CONSTRAINT vocab_grammar_exercise_progress_exercise_fk
        FOREIGN KEY (exercise_id)
        REFERENCES community.vocab_grammar_exercise(id)
        ON DELETE CASCADE,
    CONSTRAINT vocab_grammar_exercise_progress_unique UNIQUE (user_id, exercise_id)
);

-- Indizes für Grammatik-Übungen
CREATE INDEX IF NOT EXISTS vocab_grammar_exercise_lesson_idx
    ON community.vocab_grammar_exercise(lesson_id);
CREATE INDEX IF NOT EXISTS vocab_grammar_exercise_type_idx
    ON community.vocab_grammar_exercise(exercise_type_id);
CREATE INDEX IF NOT EXISTS vocab_grammar_exercise_progress_user_idx
    ON community.vocab_grammar_exercise_progress(user_id);
CREATE INDEX IF NOT EXISTS vocab_grammar_exercise_progress_exercise_idx
    ON community.vocab_grammar_exercise_progress(exercise_id);

-- ============================================
-- 5. Standard-Daten einfügen
-- ============================================
INSERT INTO community.vocab_grammar_exercise_type (name, description) VALUES
    ('gap_fill', 'Lückentext-Übung'),
    ('multiple_choice', 'Multiple-Choice-Fragen'),
    ('sentence_building', 'Satzbau-Übung'),
    ('transformation', 'Satzumformung'),
    ('conjugation', 'Konjugations-Übung'),
    ('declension', 'Deklinations-Übung')
ON CONFLICT (name) DO NOTHING;

-- ============================================
-- 6. Kommentare hinzufügen
-- ============================================
COMMENT ON COLUMN community.vocab_course_lesson.lesson_type IS 
    'Type: vocab, grammar, conversation, culture, review';
COMMENT ON COLUMN community.vocab_course_lesson.target_minutes IS 
    'Zielzeit in Minuten für diese Lektion';
COMMENT ON COLUMN community.vocab_course_lesson.target_score_percent IS 
    'Mindestpunktzahl in Prozent zum Abschluss (z.B. 80)';
COMMENT ON COLUMN community.vocab_course_lesson.requires_review IS 
    'Muss diese Lektion wiederholt werden, wenn Ziel nicht erreicht?';

-- ============================================
-- Fertig!
-- ============================================