-- MySQL-Datenbank für Lehrerkonten
-- Ausführen mit: mysql -u username -p < mysql_setup.sql

-- Datenbank erstellen (falls nicht vorhanden)
CREATE DATABASE IF NOT EXISTS klausurenweb_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE klausurenweb_db;

-- Tabelle für Lehrerkonten
CREATE TABLE IF NOT EXISTS teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    school_code VARCHAR(10) NOT NULL,
    teacher_code VARCHAR(10) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabelle für Passwort-Reset-Tokens
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    token VARCHAR(100) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE
);

-- Neue Tabelle für Fächer
CREATE TABLE IF NOT EXISTS subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Neue Tabelle für Aufgabentypen
CREATE TABLE IF NOT EXISTS assignment_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Neue Tabelle für die Zuordnung von Lehrern zu Fächern
CREATE TABLE IF NOT EXISTS teacher_subjects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    subject_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
    FOREIGN KEY (subject_id) REFERENCES subjects(id) ON DELETE CASCADE,
    UNIQUE KEY (teacher_id, subject_id)
);

-- Neue Tabelle für die Zuordnung von Aufgabentypen zu Fächern und Lehrern
CREATE TABLE IF NOT EXISTS teacher_subject_assignment_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    subject_id INT NOT NULL,
    assignment_type_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT 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,
    UNIQUE KEY (teacher_id, subject_id, assignment_type_id)
);

-- Neue Tabelle für Materialien
CREATE TABLE IF NOT EXISTS 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
);

-- Neue Tabelle für die Inhalte der Materialien
CREATE TABLE IF NOT EXISTS material_contents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    material_id INT NOT NULL,
    content_type ENUM('prompt', 'criteria', 'task', 'original_text') 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)
);

-- Beispieldaten einfügen (Passwort: demo123)
INSERT INTO teachers (username, email, password_hash, school_code, teacher_code)
VALUES 
('mosend', 's.endlicher@mos-muenchen.de', '$2b$12$8NJh3ftoEJ36FQBXo7A/6.H1rnUXo2xJOrnzh.GtUC2RVw.X5H4Hy', 'mos', 'end'),
('mosdol', 'lehrer2@schule.de', '$2b$12$8NJh3ftoEJ36FQBXo7A/6.H1rnUXo2xJOrnzh.GtUC2RVw.X5H4Hy', 'mos', 'dol');

-- Einige Standardfächer einfügen
INSERT INTO subjects (name) VALUES 
('Deutsch'),
('Mathematik'),
('Englisch'),
('GPuG'),
('Biologie'),
('Physik'),
('Chemie'),
('Geschichte'),
('Informatik');

-- Einige Standard-Aufgabentypen einfügen
INSERT INTO assignment_types (name, description) VALUES 
('Inhaltsangabe', 'Zusammenfassung eines Textes'),
('Gedichtsanalyse', 'Analyse eines Gedichts'),
('Charakterisierung', 'Charakterisierung einer literarischen Figur'),
('Erörterung', 'Diskussion eines Themas'),
('Karikaturanalyse', 'Analyse einer politischen Karikatur'),
('lb3', 'Leistungsbeurteilung 3'),
('Essay', 'Persönliche Stellungnahme zu einem Thema'),
('Analysis', 'Mathematische Analyse und Funktionsuntersuchung');

-- Berechtigungen für den Anwendungsbenutzer
--CREATE USER IF NOT EXISTS 'klausurenweb_db'@'localhost' IDENTIFIED BY 'sicheres_passwort_hier_einsetzen';
--GRANT SELECT, INSERT, UPDATE, DELETE ON klausurenweb_db.* TO 'klausurenweb_db'@'localhost';
FLUSH PRIVILEGES;

-- Neue Tabelle für Schülerlösungen
CREATE TABLE IF NOT EXISTS student_solutions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT NOT NULL,
    material_id INT NOT NULL,
    original_text_id INT,
    title VARCHAR(255),
    file_path VARCHAR(255),
    file_type VARCHAR(50) NOT NULL,
    display_name VARCHAR(255) NOT NULL,
    corrected BOOLEAN DEFAULT FALSE,
    correction_text TEXT,
    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 (material_id) REFERENCES materials(id) ON DELETE CASCADE,
    FOREIGN KEY (original_text_id) REFERENCES material_contents(id) ON DELETE SET NULL
);