-- SQL-Skript für die Schuladministration
-- Erstelle Tabelle für Schulen
CREATE TABLE IF NOT EXISTS schools (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    code VARCHAR(50) NOT NULL UNIQUE,
    address TEXT,
    contact_email VARCHAR(255),
    contact_phone VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Erstelle Tabelle für Schuladministratoren
CREATE TABLE IF NOT EXISTS school_admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    school_id INT NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE
);

-- Erstelle Tabelle für Passwort-Reset-Tokens
CREATE TABLE IF NOT EXISTS school_admin_reset_tokens (
    id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT NOT NULL,
    token VARCHAR(255) NOT NULL UNIQUE,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES school_admins(id) ON DELETE CASCADE
);

-- Erstelle Tabelle für Klassen/Jahrgangsstufen
CREATE TABLE IF NOT EXISTS grades (
    id INT PRIMARY KEY AUTO_INCREMENT,
    school_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (school_id) REFERENCES schools(id) ON DELETE CASCADE,
    UNIQUE KEY school_grade_unique (school_id, name)
);

-- Erstelle Tabelle für die Zuordnung von Fächern zu Schulen
CREATE TABLE IF NOT EXISTS school_subjects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    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_subject_unique (school_id, subject_id)
);

-- Vorhandene Tabellen modifizieren
-- Füge school_id zu teachers hinzu, falls noch nicht vorhanden
ALTER TABLE teachers ADD COLUMN IF NOT EXISTS school_id INT;
ALTER TABLE teachers ADD FOREIGN KEY IF NOT EXISTS (school_id) REFERENCES schools(id);

-- Beispieldaten für Tests einfügen
INSERT INTO schools (name, code, address, contact_email, contact_phone) 
VALUES ('Münchner Oberschule', 'MOS', 'Musterstraße 123, 80333 München', 'kontakt@musterschule.de', '+49 89 12345678');

-- Beispiel-Schuladministrator erstellen (Passwort: 'password')
INSERT INTO school_admins (username, email, password_hash, school_id) 
VALUES ('schuladmin', 'admin@musterschule.de', '$2b$12$XoOaMKf6jLkj0FV4xgBdDOzJUmV2Vss/d3TWWjSLNi192yh65YYxa', LAST_INSERT_ID());

-- Beispiel-Jahrgangsstufen erstellen
INSERT INTO grades (school_id, name, description) 
VALUES 
  (LAST_INSERT_ID(), '5a', 'Klasse 5a - Schuljahr 2023/24'),
  (LAST_INSERT_ID(), '5b', 'Klasse 5b - Schuljahr 2023/24'),
  (LAST_INSERT_ID(), '6a', 'Klasse 6a - Schuljahr 2023/24');