Adminator
1e51ef7def
- Konzept/, didaktik_geografie/, didaktik_simulation/, v2-modules/, v2-platform/ - 12 code-workspace-Files - STATUS-*.md - viele M/D/R-Änderungen an bereits getrackten Files - .gitignore verstärkt: **/.humaninput/, **/secret_keys.txt Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>
198 lines
7.9 KiB
SQL
198 lines
7.9 KiB
SQL
-- GeoGraSim — Deploy-Migration 2026-04-23
|
|
-- WICHTIG: Nur ADDITIV. Keine DROPs/TRUNCATEs auf Tabellen mit User-Daten.
|
|
-- Schutz: teachers, students, classes, licenses, admin_users, student_sessions,
|
|
-- activity_log, class_modules, student_modules, class_assignments,
|
|
-- assessments, assessment_answers, player_progress, student_results,
|
|
-- game_saves
|
|
|
|
-- ============================================================
|
|
-- 1. NEUE TABELLEN (IF NOT EXISTS — idempotent)
|
|
-- ============================================================
|
|
|
|
-- Modul-Metadaten (Titel, Icon, Beschreibungen)
|
|
CREATE TABLE IF NOT EXISTS module_info (
|
|
module_id VARCHAR(16) NOT NULL PRIMARY KEY,
|
|
title VARCHAR(128) NOT NULL,
|
|
subtitle VARCHAR(255),
|
|
short_desc TEXT,
|
|
long_desc MEDIUMTEXT,
|
|
learning_goals MEDIUMTEXT,
|
|
duration_min INT UNSIGNED,
|
|
age_min TINYINT UNSIGNED,
|
|
age_max TINYINT UNSIGNED,
|
|
icon VARCHAR(16),
|
|
card_image VARCHAR(255),
|
|
page_url VARCHAR(128),
|
|
play_url VARCHAR(128),
|
|
status ENUM('aktiv','beta','geplant','archiv') DEFAULT 'geplant',
|
|
sort_order INT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
short_desc_easy TEXT,
|
|
long_desc_easy MEDIUMTEXT,
|
|
learning_goals_easy MEDIUMTEXT
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Waypoint-Facts für Heli (geo/kids)
|
|
CREATE TABLE IF NOT EXISTS geo_waypoint_facts (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
wp_key VARCHAR(32) NOT NULL,
|
|
kind ENUM('geo','kids') NOT NULL,
|
|
text_de TEXT NOT NULL,
|
|
display_order TINYINT UNSIGNED DEFAULT 0,
|
|
INDEX idx_wp (wp_key, kind)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Glossar-Haupttabelle
|
|
CREATE TABLE IF NOT EXISTS glossar (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
slug VARCHAR(64) NOT NULL UNIQUE,
|
|
term VARCHAR(128) NOT NULL,
|
|
short_def TEXT,
|
|
long_def MEDIUMTEXT,
|
|
short_def_easy TEXT,
|
|
long_def_easy MEDIUMTEXT,
|
|
image_url VARCHAR(255),
|
|
infographic_url VARCHAR(255),
|
|
category VARCHAR(32),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS glossar_examples (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
glossar_id INT UNSIGNED NOT NULL,
|
|
example_text TEXT NOT NULL,
|
|
display_order TINYINT UNSIGNED DEFAULT 0,
|
|
INDEX idx_glossar (glossar_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS glossar_links (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
glossar_id INT UNSIGNED NOT NULL,
|
|
url VARCHAR(512) NOT NULL,
|
|
label VARCHAR(255),
|
|
display_order TINYINT UNSIGNED DEFAULT 0,
|
|
INDEX idx_glossar (glossar_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS glossar_modules (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
glossar_id INT UNSIGNED NOT NULL,
|
|
module_id VARCHAR(16) NOT NULL,
|
|
INDEX idx_glossar (glossar_id),
|
|
INDEX idx_module (module_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS glossar_related (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
glossar_id INT UNSIGNED NOT NULL,
|
|
related_slug VARCHAR(64) NOT NULL,
|
|
INDEX idx_glossar (glossar_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS glossar_sources (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
glossar_id INT UNSIGNED NOT NULL,
|
|
title VARCHAR(255),
|
|
url VARCHAR(512),
|
|
display_order TINYINT UNSIGNED DEFAULT 0,
|
|
INDEX idx_glossar (glossar_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Lehrplan-Anker
|
|
CREATE TABLE IF NOT EXISTS lehrplan_anchors (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
country CHAR(2) NOT NULL,
|
|
anchor_code VARCHAR(32) NOT NULL,
|
|
description TEXT,
|
|
grade VARCHAR(16),
|
|
subject VARCHAR(64),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_country (country),
|
|
INDEX idx_code (anchor_code)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS kompetenzen (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
slug VARCHAR(64) NOT NULL UNIQUE,
|
|
name VARCHAR(128) NOT NULL,
|
|
description TEXT
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS kompetenz_modules (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
kompetenz_id INT UNSIGNED NOT NULL,
|
|
module_id VARCHAR(16) NOT NULL,
|
|
INDEX idx_kompetenz (kompetenz_id),
|
|
INDEX idx_module (module_id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- Logistik-Analytics
|
|
CREATE TABLE IF NOT EXISTS lg_contracts_log (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
session_id CHAR(36) NOT NULL,
|
|
level_id INT UNSIGNED NOT NULL,
|
|
contract_code VARCHAR(32) NOT NULL,
|
|
assigned_vehicle VARCHAR(32) NULL,
|
|
state VARCHAR(16) NOT NULL,
|
|
final_balance INT NULL,
|
|
late_minutes INT UNSIGNED DEFAULT 0,
|
|
hint_usages SMALLINT UNSIGNED DEFAULT 0,
|
|
intermodal BOOLEAN DEFAULT FALSE,
|
|
route_distance_km INT UNSIGNED NULL,
|
|
route_mode VARCHAR(16) NULL,
|
|
completed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
INDEX idx_session (session_id),
|
|
INDEX idx_level (level_id),
|
|
INDEX idx_completed (completed_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
-- ============================================================
|
|
-- 2. MODULE_INFO — NEUE ZEILEN (INSERT IGNORE = nur wenn nicht da)
|
|
-- ============================================================
|
|
-- Die lokale DB hat 9 module_info-Einträge; diese jetzt auf Prod
|
|
-- übertragen. INSERT IGNORE schützt bestehende Daten.
|
|
-- (Content-Import erfolgt separat aus Seed-File, siehe Deploy-Script)
|
|
|
|
-- ============================================================
|
|
-- 3. GAME_LEVELS — LOGISTIK-EINTRÄGE (INSERT IGNORE gegen game_id+sort_order)
|
|
-- ============================================================
|
|
INSERT IGNORE INTO game_levels (game_id, level_name, scenario, params, sort_order) VALUES
|
|
('logistik', 'Lernen', 'logistik_level_1', JSON_OBJECT(
|
|
'startBudget', 8000, 'maxActiveContracts', 1,
|
|
'availableVehicleTypes', JSON_ARRAY('TRUCK_SMALL'), 'vehicleCount', 1,
|
|
'deadlineMultiplier', 1.5, 'defaultHintMode', 'BLINK_EXACT',
|
|
'eventProbabilityMultiplier', 0, 'latePenaltyOverride', 0.10,
|
|
'rentalCostPerDay', 0, 'idleCostPerHour', 0,
|
|
'containerStandCostPerHour', 0, 'minTargetEarnings', 3000,
|
|
'timeLimitHours', 24, 'initialTimeScale', 1,
|
|
'railEnabled', FALSE, 'portsEnabled', FALSE,
|
|
'intermodalEnabled', FALSE, 'minigamesEnabled', FALSE,
|
|
'visibleLocationLevel', 1, 'levelNameEasy', 'Erster Auftrag'
|
|
), 1),
|
|
('logistik', 'Übung', 'logistik_level_2', JSON_OBJECT(
|
|
'startBudget', 5000, 'maxActiveContracts', 3,
|
|
'availableVehicleTypes', JSON_ARRAY('TRUCK_SMALL','TRUCK_LARGE','TRAIN'), 'vehicleCount', 3,
|
|
'deadlineMultiplier', 1.2, 'defaultHintMode', 'SHOW_REGION',
|
|
'eventProbabilityMultiplier', 0.5, 'latePenaltyOverride', 0.20,
|
|
'rentalCostPerDay', 200, 'idleCostPerHour', 5,
|
|
'containerStandCostPerHour', 0, 'minTargetEarnings', 10000,
|
|
'timeLimitHours', 48, 'initialTimeScale', 1,
|
|
'railEnabled', FALSE, 'portsEnabled', FALSE,
|
|
'intermodalEnabled', FALSE, 'minigamesEnabled', TRUE,
|
|
'visibleLocationLevel', 2, 'levelNameEasy', 'Drei Aufträge'
|
|
), 2),
|
|
('logistik', 'Profi', 'logistik_level_3', JSON_OBJECT(
|
|
'startBudget', 2500, 'maxActiveContracts', 6,
|
|
'availableVehicleTypes', JSON_ARRAY('TRUCK_SMALL','TRUCK_LARGE','TRAIN'), 'vehicleCount', 5,
|
|
'deadlineMultiplier', 1.0, 'defaultHintMode', 'NONE',
|
|
'eventProbabilityMultiplier', 1.0, 'latePenaltyOverride', 0.30,
|
|
'rentalCostPerDay', 500, 'idleCostPerHour', 5,
|
|
'containerStandCostPerHour', 10, 'minTargetEarnings', 20000,
|
|
'timeLimitHours', 72, 'initialTimeScale', 1,
|
|
'railEnabled', TRUE, 'portsEnabled', TRUE,
|
|
'intermodalEnabled', TRUE, 'minigamesEnabled', TRUE,
|
|
'visibleLocationLevel', 3, 'levelNameEasy', 'Profi-Disposition'
|
|
), 3);
|