-- SQL-Skript zum Hinzufügen von Tabellen für Schulen und Jahrgänge
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(20) NOT NULL UNIQUE,
    city VARCHAR(100),
    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 years (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    description VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabelle für die Zuordnung von Jahrgängen zu Schulen
CREATE TABLE IF NOT EXISTS school_years (
    id INT AUTO_INCREMENT PRIMARY KEY,
    school_id INT NOT NULL,
    year_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (year_id) REFERENCES years(id) ON DELETE CASCADE,
    UNIQUE KEY (school_id, year_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)
);

-- Tabelle für die Zuordnung von Lehrern zu Schulen
-- (Hinweis: Lehrer haben bereits eine school_code in der teachers-Tabelle, 
-- diese Tabelle ermöglicht eine n:m-Beziehung für zukünftige Erweiterungen)
CREATE TABLE IF NOT EXISTS school_teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    school_id INT NOT NULL,
    teacher_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
    UNIQUE KEY (school_id, teacher_id)
);

-- Standarddaten einfügen
INSERT INTO schools (name, code, city) VALUES 
('München Obersendling', 'MOS', 'München'),
('Andere Schule', 'AS', 'München');

INSERT INTO years (name) VALUES 
('Vorklasse'),
('11'),
('12'),
('13');

-- MOS mit allen Jahrgängen verknüpfen
INSERT INTO school_years (school_id, year_id)
SELECT (SELECT id FROM schools WHERE code = 'MOS'), id FROM years;

-- Andere Schule mit einigen Jahrgängen verknüpfen
INSERT INTO school_years (school_id, year_id)
SELECT (SELECT id FROM schools WHERE code = 'AS'), id FROM years WHERE name IN ('11', '12', '13');

-- Alle bestehenden Fächer mit MOS verknüpfen
INSERT INTO school_subjects (school_id, subject_id)
SELECT 
    (SELECT id FROM schools WHERE code = 'MOS'),
    id
FROM subjects;

-- Bestehende Lehrer mit der entsprechenden Schule verknüpfen
INSERT INTO school_teachers (school_id, teacher_id)
SELECT 
    (SELECT id FROM schools WHERE code = teachers.school_code),
    teachers.id
FROM teachers;

-- Hinweis zur Ausführung des Skripts
-- mysql -u [USERNAME] -p [DATENBANKNAME] < add_schools_and_years_tables.sql