CREATE DATABASE IF NOT EXISTS sysadmin_game CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sysadmin_game;
DROP TABLE IF EXISTS event_log; DROP TABLE IF EXISTS services; DROP TABLE IF EXISTS systems; DROP TABLE IF EXISTS game_state;
CREATE TABLE systems (id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(50) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, type VARCHAR(50) NOT NULL, power_state ENUM('on','off') NOT NULL DEFAULT 'on', health ENUM('ok','warning','critical') NOT NULL DEFAULT 'ok', notes TEXT NULL) ENGINE=InnoDB;
CREATE TABLE services (id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(50) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, system_code VARCHAR(50) NOT NULL, status ENUM('ok','warning','critical') NOT NULL DEFAULT 'critical', manual_state ENUM('running','stopped') NOT NULL DEFAULT 'running', business_impact INT NOT NULL DEFAULT 10, detail TEXT NULL) ENGINE=InnoDB;
CREATE TABLE event_log (id INT AUTO_INCREMENT PRIMARY KEY, type ENUM('incident','action','system','hint') NOT NULL DEFAULT 'system', message TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE game_state (name VARCHAR(80) PRIMARY KEY, value TEXT NOT NULL) ENGINE=InnoDB;
INSERT INTO systems(code,name,type,power_state,health,notes) VALUES
('esx01','ESX01','VMware Host','off','critical','Power state unbekannt'),('san01','SAN01 / PROD_DS01','Storage','on','critical','Mountstatus prüfen'),('ntp01','NTP01','Zeitquelle','on','warning','Drift vermutet'),('vm-dc01','DC01','Windows VM','on','critical',''),('vm-sql01','SQL01','Database VM','on','critical',''),('vm-app01','APP01','Application VM','on','critical',''),('vm-mon01','MON01','Monitoring VM','on','critical','');
INSERT INTO services(code,name,system_code,status,manual_state,business_impact,detail) VALUES
('dc01','AD/Kerberos','vm-dc01','critical','running',80,'Host unreachable / connection timeout.'),('fileserver','Fileserver','vm-dc01','critical','running',30,'Host unreachable / connection timeout.'),('monitoring','Monitoring Core','vm-mon01','critical','running',20,'Host unreachable / connection timeout.'),('sql','SQL-Datenbank','vm-sql01','critical','stopped',180,'Host unreachable / connection timeout.'),('erp','ERP Webanwendung','vm-app01','critical','stopped',250,'Host unreachable / connection timeout.'),('backup','Backup-Scheduler','vm-sql01','critical','stopped',40,'Host unreachable / connection timeout.');
INSERT INTO event_log(type,message) VALUES
('incident','06:12 Massenausfall nach geplanter Stromwartung.'),('system','Telefonnotiz: ERP erst Loginfehler, dann 503.'),('system','Backup-Report von 05:59 fehlt.'),('hint','Nicht jeder rote Check ist eine Ursache. Suche den gemeinsamen Nenner.');
INSERT INTO game_state(name,value) VALUES ('last_random_failure','1970-01-01 00:00:00'),('last_command',''),('last_output','Tippe help für Werkzeuge. Das Dashboard zeigt Symptome, nicht die Ursache.');
