-- SQL-Skript zum Hinzufügen von Schulen, Jahrgängen und deren Beziehungen
USE klausurenweb_db;

-- Tabelle für Schulen
CREATE TABLE IF NOT EXISTS schools (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(10) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabelle für Jahrgänge
CREATE TABLE IF NOT EXISTS grades (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabelle für die Zuordnung von Jahrgängen zu Schulen
CREATE TABLE IF NOT EXISTS school_grades (
    id INT AUTO_INCREMENT PRIMARY KEY,
    school_id INT NOT NULL,
    grade_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (grade_id) REFERENCES grades(id) ON DELETE CASCADE,
    UNIQUE KEY (school_id, grade_id)
);

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

-- Beispieldaten für Schulen einfügen
INSERT INTO schools (name, code, description) VALUES 
('Münchner Oberschule', 'MOS', 'Münchner Oberschule'),
('Andere Schule', 'ADS', 'Eine andere Schule');

-- Beispieldaten für Jahrgänge einfügen
INSERT INTO grades (name, description) VALUES 
('Vorklasse', 'Vorbereitungsklasse'),
('11', 'Jahrgangsstufe 11'),
('12', 'Jahrgangsstufe 12'),
('13', 'Jahrgangsstufe 13');

-- Zuordnung von Jahrgängen zu Schulen
INSERT INTO school_grades (school_id, grade_id) 
SELECT s.id, g.id 
FROM schools s, grades g 
WHERE s.code = 'MOS';

-- Zuordnung von Fächern zu Schulen
INSERT INTO school_subjects (school_id, subject_id)
SELECT s.id, sub.id
FROM schools s, subjects sub
WHERE s.code = 'MOS';

-- Überführe bestehende Lehrer in die neue Struktur
-- Aktualisiere teacher-Tabelle mit school_id
ALTER TABLE teachers ADD COLUMN school_id INT NULL;
UPDATE teachers t
JOIN schools s ON t.school_code = s.code
SET t.school_id = s.id;

ALTER TABLE teachers
ADD FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE SET NULL;

-- Bestätigungsmeldung
SELECT 'Tabellen für Schulen und Jahrgänge wurden erstellt und mit Beispieldaten gefüllt.' AS Ergebnis;