DROP TABLE IF EXISTS `module_info`;
CREATE TABLE `module_info` (
  `module_id` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `subtitle` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `short_desc` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `long_desc` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `learning_goals` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `duration_min` int(10) unsigned DEFAULT NULL,
  `age_min` tinyint(3) unsigned DEFAULT NULL,
  `age_max` tinyint(3) unsigned DEFAULT NULL,
  `icon` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `card_image` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `page_url` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `play_url` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('aktiv','beta','geplant','archiv') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'beta',
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `short_desc_easy` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `long_desc_easy` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `learning_goals_easy` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`module_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `geo_waypoint_facts`;
CREATE TABLE `geo_waypoint_facts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `wp_key` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `kind` enum('geo','kids') COLLATE utf8mb4_unicode_ci NOT NULL,
  `text_de` varchar(500) COLLATE utf8mb4_unicode_ci NOT NULL,
  `audio_file` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `display_order` tinyint(4) DEFAULT 1,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_wp_kind` (`wp_key`,`kind`)
) ENGINE=InnoDB AUTO_INCREMENT=79 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `glossar`;
CREATE TABLE `glossar` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `key_slug` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `short` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `text` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `unit` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `animation_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `diagram_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `video_url` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `short_easy` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `text_easy` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_slug` (`key_slug`),
  KEY `category` (`category`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `glossar_examples`;
CREATE TABLE `glossar_examples` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `glossar_id` int(10) unsigned NOT NULL,
  `text` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `source_ref` tinyint(3) unsigned DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `text_easy` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `glossar_id` (`glossar_id`),
  CONSTRAINT `glossar_examples_ibfk_1` FOREIGN KEY (`glossar_id`) REFERENCES `glossar` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=236 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `glossar_links`;
CREATE TABLE `glossar_links` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `glossar_id` int(10) unsigned NOT NULL,
  `label` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `url` varchar(512) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sort_order` int(11) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `glossar_id` (`glossar_id`),
  CONSTRAINT `glossar_links_ibfk_1` FOREIGN KEY (`glossar_id`) REFERENCES `glossar` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=97 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `glossar_modules`;
CREATE TABLE `glossar_modules` (
  `glossar_id` int(10) unsigned NOT NULL,
  `module_id` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`glossar_id`,`module_id`),
  CONSTRAINT `glossar_modules_ibfk_1` FOREIGN KEY (`glossar_id`) REFERENCES `glossar` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `glossar_related`;
CREATE TABLE `glossar_related` (
  `glossar_id` int(10) unsigned NOT NULL,
  `related_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`glossar_id`,`related_id`),
  KEY `related_id` (`related_id`),
  CONSTRAINT `glossar_related_ibfk_1` FOREIGN KEY (`glossar_id`) REFERENCES `glossar` (`id`) ON DELETE CASCADE,
  CONSTRAINT `glossar_related_ibfk_2` FOREIGN KEY (`related_id`) REFERENCES `glossar` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `glossar_sources`;
CREATE TABLE `glossar_sources` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `glossar_id` int(10) unsigned NOT NULL,
  `ref_num` tinyint(3) unsigned NOT NULL,
  `label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `url` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `access_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_gl_ref` (`glossar_id`,`ref_num`),
  KEY `idx_gl` (`glossar_id`),
  CONSTRAINT `glossar_sources_ibfk_1` FOREIGN KEY (`glossar_id`) REFERENCES `glossar` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=114 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `kompetenzen`;
CREATE TABLE `kompetenzen` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `key_slug` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `title` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `category` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age_min` tinyint(3) unsigned DEFAULT NULL,
  `age_max` tinyint(3) unsigned DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `key_slug` (`key_slug`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `kompetenz_modules`;
CREATE TABLE `kompetenz_modules` (
  `kompetenz_id` int(10) unsigned NOT NULL,
  `module_id` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `level` tinyint(3) unsigned NOT NULL DEFAULT 0,
  `strength` enum('primaer','sekundaer') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'primaer',
  PRIMARY KEY (`kompetenz_id`,`module_id`,`level`),
  KEY `module_id` (`module_id`),
  CONSTRAINT `fk_km_kompetenz` FOREIGN KEY (`kompetenz_id`) REFERENCES `kompetenzen` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `lehrplan_anchors`;
CREATE TABLE `lehrplan_anchors` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `kompetenz_id` int(10) unsigned NOT NULL,
  `country` char(2) COLLATE utf8mb4_unicode_ci NOT NULL,
  `anchor_type` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'fach',
  `region` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `school_type` varchar(48) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `grade` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `subject` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference_url` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `quote` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sort_order` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `kompetenz_id` (`kompetenz_id`),
  KEY `country` (`country`,`school_type`),
  KEY `idx_anchor_type` (`anchor_type`),
  CONSTRAINT `fk_la_kompetenz` FOREIGN KEY (`kompetenz_id`) REFERENCES `kompetenzen` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=309 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
DROP TABLE IF EXISTS `lg_contracts_log`;
CREATE TABLE `lg_contracts_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `session_id` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `level_id` int(10) unsigned NOT NULL,
  `contract_code` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `assigned_vehicle` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `final_balance` int(11) DEFAULT NULL,
  `late_minutes` int(10) unsigned DEFAULT 0,
  `hint_usages` smallint(5) unsigned DEFAULT 0,
  `intermodal` tinyint(1) DEFAULT 0,
  `route_distance_km` int(10) unsigned DEFAULT NULL,
  `route_mode` varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `completed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_session` (`session_id`),
  KEY `idx_level` (`level_id`),
  KEY `idx_completed` (`completed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;