-- SQL-Skript zum Hinzufügen der Materialien-Tabellen zu einer existierenden Datenbank
USE klausurenweb_db;

-- Überprüfen, ob die Tabellen bereits existieren
SET @table_exists := (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'klausurenweb_db' AND table_name = 'materials');

-- Nur erstellen, wenn die Tabelle noch nicht existiert
SET @sql := IF(@table_exists = 0, 
  'CREATE TABLE materials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    subject_id INT NOT NULL,
    assignment_type_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    year VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    FOREIGN KEY (assignment_type_id) REFERENCES assignment_types(id) ON DELETE CASCADE
  )',
  'SELECT "Tabelle materials existiert bereits." AS Hinweis'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Überprüfen, ob die Tabelle material_contents bereits existiert
SET @table_exists := (SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'klausurenweb_db' AND table_name = 'material_contents');

-- Nur erstellen, wenn die Tabelle noch nicht existiert
SET @sql := IF(@table_exists = 0, 
  'CREATE TABLE material_contents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    material_id INT NOT NULL,
    content_type ENUM("prompt", "criteria", "task", "original_text", "expectation") NOT NULL,
    title VARCHAR(255),
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE,
    UNIQUE KEY (material_id, content_type, title)
  )',
  'SELECT "Tabelle material_contents existiert bereits." AS Hinweis'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Bestätigung
SELECT 'Materialtabellen wurden erstellt (falls sie nicht bereits existierten).' AS Ergebnis;