-- GeoGraSim — MySQL Schema
-- Ausfuehren: mysql -u root geograsim < schema.sql

CREATE DATABASE IF NOT EXISTS geograsim CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE geograsim;

-- Lehrkraefte
CREATE TABLE IF NOT EXISTS teachers (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username     VARCHAR(64)  NOT NULL UNIQUE,
  password     VARCHAR(255) NOT NULL,
  display_name VARCHAR(128),
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Klassen
CREATE TABLE IF NOT EXISTS classes (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  teacher_id   INT UNSIGNED NOT NULL,
  name         VARCHAR(64) NOT NULL,
  school_year  VARCHAR(10),
  join_code    CHAR(6) NOT NULL UNIQUE,
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (teacher_id) REFERENCES teachers(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Schueler-Sessions (anonym, UUID-Cookie)
CREATE TABLE IF NOT EXISTS student_sessions (
  id           CHAR(36) PRIMARY KEY,
  class_id     INT UNSIGNED,
  display_name VARCHAR(64),
  created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  last_seen    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Spielstaende (ersetzt localStorage)
CREATE TABLE IF NOT EXISTS game_saves (
  id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id   CHAR(36) NOT NULL,
  save_key     VARCHAR(64) NOT NULL,
  save_data    MEDIUMTEXT NOT NULL,
  save_version TINYINT UNSIGNED DEFAULT 2,
  updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_session_key (session_id, save_key),
  FOREIGN KEY (session_id) REFERENCES student_sessions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Assessment-Daten (eine Zeile pro Simulationsabschluss)
CREATE TABLE IF NOT EXISTS assessments (
  id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  session_id       CHAR(36) NOT NULL,
  sim_id           VARCHAR(16) NOT NULL,
  class_id         INT UNSIGNED,
  process_log      JSON,
  predictions      JSON,
  results          JSON,
  reflections      JSON,
  duration_ms      INT UNSIGNED,
  completed_phases JSON,
  submitted_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_class_sim (class_id, sim_id),
  INDEX idx_session (session_id),
  FOREIGN KEY (session_id) REFERENCES student_sessions(id) ON DELETE CASCADE,
  FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Modulfreigabe pro Klasse
CREATE TABLE IF NOT EXISTS class_modules (
  class_id   INT UNSIGNED NOT NULL,
  module_id  VARCHAR(16) NOT NULL,
  enabled    BOOLEAN DEFAULT TRUE,
  PRIMARY KEY (class_id, module_id),
  FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Demo-Lehrer anlegen (Passwort: "demo2026")
INSERT IGNORE INTO teachers (id, username, password, display_name)
VALUES (1, 'demo', '$2y$10$YQ8kXE5Bq3Dq1Z5VxGf1/.J9Cxj5xEqL8RyE3.zDZ1YCbYdLiK2Vy', 'Demo-Lehrkraft');

-- Demo-Klasse
INSERT IGNORE INTO classes (id, teacher_id, name, school_year, join_code)
VALUES (1, 1, '1A', '2025/26', 'GEO1A');