-- SQL-Skript zum Hinzufügen von Tabellen für das Teilen von Materialien und Sichtbarkeitskontrolle
-- Erstellt am: 2024-07-05

USE klausurenweb_db;

-- Tabelle für geteilte Materialien
CREATE TABLE IF NOT EXISTS shared_materials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    material_id INT NOT NULL,
    owner_teacher_id INT NOT NULL,
    shared_with_teacher_id INT NOT NULL,
    shared_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
    FOREIGN KEY (shared_with_teacher_id) REFERENCES teachers(id) ON DELETE CASCADE,
    UNIQUE KEY (material_id, shared_with_teacher_id)
);

-- Prüfe, ob die Tabelle material_visibility existiert
CREATE TABLE IF NOT EXISTS material_visibility (
    id INT AUTO_INCREMENT PRIMARY KEY,
    material_id INT NOT NULL,
    is_visible_to_students BOOLEAN DEFAULT FALSE,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE,
    UNIQUE KEY (material_id)
);

-- Füge die neue Spalte updated_by_teacher_id zunächst als NULL-erlaubend hinzu
SET @dbname = DATABASE();
SET @tablename = "material_visibility";
SET @columnname = "updated_by_teacher_id";
SET @preparedStatement = (SELECT IF(
  (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE
      (TABLE_SCHEMA = @dbname)
      AND (TABLE_NAME = @tablename)
      AND (COLUMN_NAME = @columnname)
  ) > 0,
  "SELECT 1",
  CONCAT("ALTER TABLE ", @tablename, " ADD ", @columnname, " INT NULL")
));
PREPARE alterIfNotExists FROM @preparedStatement;
EXECUTE alterIfNotExists;
DEALLOCATE PREPARE alterIfNotExists;

-- Aktualisiere bestehende Einträge, setze den teacher_id des Materials als updated_by_teacher_id
UPDATE material_visibility mv
JOIN materials m ON mv.material_id = m.id
SET mv.updated_by_teacher_id = m.teacher_id
WHERE mv.updated_by_teacher_id IS NULL;

-- Füge den Foreign Key Constraint hinzu
ALTER TABLE material_visibility
ADD FOREIGN KEY (updated_by_teacher_id) REFERENCES teachers(id) ON DELETE CASCADE;

-- Setze die Spalte auf NOT NULL, nachdem alle Werte aktualisiert wurden
ALTER TABLE material_visibility
MODIFY updated_by_teacher_id INT NOT NULL;

-- Standardmäßig alle vorhandenen Materialien als nicht sichtbar für Schüler markieren
INSERT IGNORE INTO material_visibility (material_id, is_visible_to_students, updated_by_teacher_id)
SELECT m.id, FALSE, m.teacher_id 
FROM materials m
WHERE NOT EXISTS (
    SELECT 1 FROM material_visibility mv WHERE mv.material_id = m.id
);

-- Trigger, der sicherstellt, dass jedem neuen Material ein Eintrag in der Sichtbarkeitstabelle zugewiesen wird
DELIMITER //
DROP TRIGGER IF EXISTS after_material_insert//
CREATE TRIGGER after_material_insert
AFTER INSERT ON materials
FOR EACH ROW
BEGIN
    INSERT INTO material_visibility (material_id, is_visible_to_students, updated_by_teacher_id)
    VALUES (NEW.id, FALSE, NEW.teacher_id);
END//
DELIMITER ;

-- Hinweis: Diese Änderungen ermöglichen, dass Lehrkräfte:
-- 1. Materialien mit Kollegen teilen können (shared_materials Tabelle)
-- 2. Die Sichtbarkeit für Schüler kontrollieren können (material_visibility Tabelle)