Jerrit Fritzsche
e4b82dd685
- aether-medical: Stationen/Belegung, Intensivstation, Blutbank, Fuhrpark, Nachrichten/Aushänge, Berichte und Textbausteine (server + NUI) - aether-admin: vollständiges Server-Admin-Panel als echtes FiveM-Resource, an aether-core gekoppelt (Rechte über aether_users.admin_group), mit WebRTC-Live-Kameras (Bildschirmfreigabe) und separatem Media-Server (SFU) - Prototypen admin/ vervollständigt - SQL-Schema aether_admin.sql - Docker-Compose mit MariaDB und idempotenter Auto-Migration (schema_migrations) Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
282 lines
15 KiB
SQL
282 lines
15 KiB
SQL
-- =====================================================================
|
|
-- Aether Framework — Datenbank-Schema (Grundlage)
|
|
-- Einspielen mit: mysql -u root -p aether < sql/aether.sql
|
|
-- =====================================================================
|
|
|
|
CREATE DATABASE IF NOT EXISTS `aether`
|
|
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE `aether`;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Benutzer (ein Eintrag pro FiveM-Account, erkannt an der Lizenz)
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_users` (
|
|
`license` VARCHAR(64) NOT NULL COMMENT 'Rockstar-Lizenz als eindeutige Kennung',
|
|
`name` VARCHAR(64) NOT NULL DEFAULT 'Unbekannt' COMMENT 'Zuletzt genutzter Spielername',
|
|
`discord` VARCHAR(64) DEFAULT NULL COMMENT 'Discord-ID, falls vorhanden',
|
|
`is_banned` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Gebannt ja/nein',
|
|
`ban_reason` VARCHAR(255) DEFAULT NULL COMMENT 'Grund des Banns',
|
|
`ban_until` DATETIME DEFAULT NULL COMMENT 'Bann-Ende (NULL = dauerhaft)',
|
|
`admin_group` VARCHAR(32) NOT NULL DEFAULT 'user' COMMENT 'Rechtegruppe: user/support/admin/owner',
|
|
`playtime` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Gesamtspielzeit in Minuten',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`last_seen` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`license`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Charaktere (mehrere pro Benutzer — Multicharacter)
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_characters` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`license` VARCHAR(64) NOT NULL COMMENT 'Besitzer des Charakters',
|
|
`firstname` VARCHAR(32) NOT NULL,
|
|
`lastname` VARCHAR(32) NOT NULL,
|
|
`dob` DATE NOT NULL COMMENT 'Geburtsdatum',
|
|
`gender` VARCHAR(16) NOT NULL DEFAULT 'm' COMMENT 'Geschlecht',
|
|
`nationality` VARCHAR(48) NOT NULL DEFAULT 'Los Santos' COMMENT 'Herkunft',
|
|
`appearance` LONGTEXT DEFAULT NULL COMMENT 'Aussehen als JSON (Charakter-Ersteller)',
|
|
-- Geld (passend zum HUD: Bargeld / Konto / Schwarzgeld)
|
|
`cash` INT NOT NULL DEFAULT 0,
|
|
`bank` INT NOT NULL DEFAULT 0,
|
|
`dirty_money` INT NOT NULL DEFAULT 0,
|
|
`iban` VARCHAR(24) DEFAULT NULL COMMENT 'Kontonummer fuer das Bank-Modul',
|
|
`bank_pin` VARCHAR(8) NOT NULL DEFAULT '1234' COMMENT 'PIN des Bankautomaten',
|
|
-- Arbeit und Zugehoerigkeit
|
|
`job` VARCHAR(32) NOT NULL DEFAULT 'unemployed',
|
|
`job_grade` INT NOT NULL DEFAULT 0,
|
|
`faction` VARCHAR(32) DEFAULT NULL COMMENT 'Fraktion/Gang, falls Mitglied',
|
|
`faction_rank` INT NOT NULL DEFAULT 0,
|
|
-- Zustand (passend zu den HUD-Statusringen)
|
|
`health` INT NOT NULL DEFAULT 200,
|
|
`hunger` INT NOT NULL DEFAULT 100,
|
|
`thirst` INT NOT NULL DEFAULT 100,
|
|
`stamina` INT NOT NULL DEFAULT 100,
|
|
`position` VARCHAR(128) DEFAULT NULL COMMENT 'Letzte Position als JSON {x,y,z,heading}',
|
|
`playtime` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Spielzeit dieses Charakters in Minuten',
|
|
`is_dead` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Liegt der Charakter verletzt am Boden?',
|
|
`deleted` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Weich geloescht (Slot freigegeben)',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`last_played` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_license` (`license`),
|
|
CONSTRAINT `fk_char_user` FOREIGN KEY (`license`)
|
|
REFERENCES `aether_users` (`license`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Inventar (ein Eintrag je Item-Stapel an einem Ort)
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_inventory` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`owner_type` VARCHAR(16) NOT NULL DEFAULT 'character' COMMENT 'character/vehicle/stash/ground',
|
|
`owner_id` VARCHAR(64) NOT NULL COMMENT 'Charakter-ID, Kennzeichen oder Lager-Name',
|
|
`slot` INT NOT NULL DEFAULT 0 COMMENT 'Platz im jeweiligen Behaelter',
|
|
`item` VARCHAR(48) NOT NULL COMMENT 'Item-Kennung aus der Registry',
|
|
`amount` INT NOT NULL DEFAULT 1,
|
|
`metadata` LONGTEXT DEFAULT NULL COMMENT 'Zusatzdaten als JSON (Seriennummer, Akku, Aufsaetze)',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_owner` (`owner_type`, `owner_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Fallengelassene Items (bleiben ueber Serverneustarts erhalten)
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_drops` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`item` VARCHAR(48) NOT NULL COMMENT 'Item-Kennung aus der Registry',
|
|
`amount` INT NOT NULL DEFAULT 1,
|
|
`metadata` LONGTEXT DEFAULT NULL COMMENT 'Zusatzdaten als JSON',
|
|
`x` FLOAT NOT NULL,
|
|
`y` FLOAT NOT NULL,
|
|
`z` FLOAT NOT NULL,
|
|
`prop` VARCHAR(48) NOT NULL COMMENT 'Modell des sichtbaren Kartons',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Fahrzeuge (Garagen-Modul)
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_vehicles` (
|
|
`plate` VARCHAR(12) NOT NULL COMMENT 'Kennzeichen als eindeutige Kennung',
|
|
`owner` INT UNSIGNED NOT NULL COMMENT 'Charakter-ID des Besitzers',
|
|
`model` VARCHAR(48) NOT NULL COMMENT 'Fahrzeugmodell (Spawnname)',
|
|
`props` LONGTEXT DEFAULT NULL COMMENT 'Tuning/Farben als JSON',
|
|
`garage` VARCHAR(48) NOT NULL DEFAULT 'zentralgarage' COMMENT 'Aktueller Standort',
|
|
`state` VARCHAR(16) NOT NULL DEFAULT 'in' COMMENT 'in = eingeparkt, out = draussen, impound = beschlagnahmt',
|
|
`fuel` FLOAT NOT NULL DEFAULT 100 COMMENT 'Tankfuellstand in Prozent',
|
|
`engine` FLOAT NOT NULL DEFAULT 1000 COMMENT 'Motorzustand',
|
|
`body` FLOAT NOT NULL DEFAULT 1000 COMMENT 'Karosseriezustand',
|
|
`mileage` INT NOT NULL DEFAULT 0 COMMENT 'Kilometerstand',
|
|
-- Letzte bekannte Position; ausgeparkte Fahrzeuge stehen nach einem
|
|
-- Serverneustart wieder genau hier.
|
|
`pos_x` FLOAT DEFAULT NULL,
|
|
`pos_y` FLOAT DEFAULT NULL,
|
|
`pos_z` FLOAT DEFAULT NULL,
|
|
`heading` FLOAT DEFAULT NULL,
|
|
PRIMARY KEY (`plate`),
|
|
KEY `idx_owner` (`owner`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Dokumente (Boerse: Ausweis, Fuehrerschein, Waffenschein ...)
|
|
-- Name und Geburtsdatum kommen live aus aether_characters, damit sie
|
|
-- nicht doppelt gepflegt werden muessen.
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_documents` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`character` INT UNSIGNED NOT NULL COMMENT 'Besitzer des Dokuments',
|
|
`type` VARCHAR(16) NOT NULL COMMENT 'perso/fs/ws/gk/vk',
|
|
`wallet` INT NOT NULL DEFAULT 0 COMMENT 'In welcher Boerse es steckt (0 = erste)',
|
|
`data` LONGTEXT DEFAULT NULL COMMENT 'Dokumentdaten als JSON (Nummer, Klassen, Waffen ...)',
|
|
`issued_by` VARCHAR(64) DEFAULT NULL COMMENT 'Ausstellende Stelle',
|
|
`issued_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`valid_until` DATE DEFAULT NULL COMMENT 'Gueltig bis (NULL = unbefristet)',
|
|
`revoked` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Entzogen ja/nein',
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_character` (`character`),
|
|
UNIQUE KEY `uniq_doc` (`character`, `type`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Handy: Geraetedaten je Charakter
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_phone` (
|
|
`character` INT UNSIGNED NOT NULL,
|
|
`number` VARCHAR(16) NOT NULL COMMENT 'Telefonnummer, z. B. 555-0182',
|
|
`pin` VARCHAR(8) DEFAULT NULL COMMENT 'Entsperrcode (NULL = Ersteinrichtung noetig)',
|
|
`faceid` TINYINT(1) NOT NULL DEFAULT 0,
|
|
`theme` VARCHAR(16) NOT NULL DEFAULT 'dark',
|
|
`wallpaper` VARCHAR(32) NOT NULL DEFAULT 'wp-aurora',
|
|
`dock` VARCHAR(128) NOT NULL DEFAULT '["phone","messages","bank","camera"]',
|
|
`battery` INT NOT NULL DEFAULT 100 COMMENT 'Akkustand in Prozent',
|
|
`unlocked` VARCHAR(255) NOT NULL DEFAULT '{}' COMMENT 'Freigeschaltete Apps als JSON',
|
|
PRIMARY KEY (`character`),
|
|
UNIQUE KEY `uniq_number` (`number`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Handy: Kontakte
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_phone_contacts` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`character` INT UNSIGNED NOT NULL COMMENT 'Besitzer der Kontaktliste',
|
|
`name` VARCHAR(48) NOT NULL,
|
|
`number` VARCHAR(16) NOT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_character` (`character`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Handy: Nachrichten (zwischen Telefonnummern, nicht Charakteren —
|
|
-- so bleiben sie auch nach einem SIM-Wechsel nachvollziehbar)
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_phone_messages` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`sender` VARCHAR(16) NOT NULL COMMENT 'Absendernummer',
|
|
`receiver` VARCHAR(16) NOT NULL COMMENT 'Empfaengernummer',
|
|
`text` VARCHAR(512) NOT NULL,
|
|
`gelesen` TINYINT(1) NOT NULL DEFAULT 0,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_paar` (`sender`, `receiver`),
|
|
KEY `idx_empfaenger` (`receiver`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Handy: Anrufverlauf
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_phone_calls` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`caller` VARCHAR(16) NOT NULL COMMENT 'Anrufernummer',
|
|
`callee` VARCHAR(16) NOT NULL COMMENT 'Angerufene Nummer',
|
|
`verpasst` TINYINT(1) NOT NULL DEFAULT 0,
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_caller` (`caller`),
|
|
KEY `idx_callee` (`callee`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Banktransaktionen (Bank- und Handy-Modul)
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_transactions` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`character` INT UNSIGNED NOT NULL COMMENT 'Betroffener Charakter',
|
|
`type` VARCHAR(16) NOT NULL COMMENT 'deposit/withdraw/transfer/salary/fine',
|
|
`amount` INT NOT NULL COMMENT 'Betrag (positiv = Eingang)',
|
|
`balance` INT NOT NULL COMMENT 'Kontostand nach der Buchung',
|
|
`label` VARCHAR(128) NOT NULL DEFAULT '' COMMENT 'Verwendungszweck',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_character` (`character`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Jobs: Level und Erfahrung je Charakter und Job
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_jobs` (
|
|
`character` INT UNSIGNED NOT NULL,
|
|
`job` VARCHAR(32) NOT NULL,
|
|
`level` INT NOT NULL DEFAULT 1,
|
|
`xp` INT NOT NULL DEFAULT 0,
|
|
`verdient` INT NOT NULL DEFAULT 0 COMMENT 'Gesamtverdienst in diesem Job',
|
|
`auftraege` INT NOT NULL DEFAULT 0 COMMENT 'Abgeschlossene Auftraege',
|
|
PRIMARY KEY (`character`, `job`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Fraktionen: Personenakten (Straftaten, Notizen, Konfisziertes)
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_records` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`character` INT UNSIGNED NOT NULL COMMENT 'Betroffene Person',
|
|
`faction` VARCHAR(32) NOT NULL COMMENT 'Welche Fraktion hat den Eintrag erstellt',
|
|
`type` VARCHAR(16) NOT NULL COMMENT 'straftat/notiz/konfisziert/behandlung',
|
|
`text` VARCHAR(512) NOT NULL,
|
|
`betrag` INT NOT NULL DEFAULT 0 COMMENT 'Strafe/Rechnung, falls zutreffend',
|
|
`haft` INT NOT NULL DEFAULT 0 COMMENT 'Haftzeit in Minuten',
|
|
`created_by` VARCHAR(64) NOT NULL DEFAULT '' COMMENT 'Name des Beamten',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_character` (`character`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Fraktionen: Fahndungen
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_wanted` (
|
|
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`character` INT UNSIGNED NOT NULL,
|
|
`faction` VARCHAR(32) NOT NULL,
|
|
`grund` VARCHAR(255) NOT NULL,
|
|
`stufe` VARCHAR(16) NOT NULL DEFAULT 'gesucht' COMMENT 'gesucht/bewaffnet/gefaehrlich',
|
|
`aktiv` TINYINT(1) NOT NULL DEFAULT 1,
|
|
`created_by` VARCHAR(64) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_character` (`character`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Gefaengnis: laufende Haftstrafen
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_jail` (
|
|
`character` INT UNSIGNED NOT NULL,
|
|
`rest` INT NOT NULL DEFAULT 0 COMMENT 'Restliche Haftzeit in Sekunden',
|
|
`grund` VARCHAR(255) NOT NULL DEFAULT '',
|
|
`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`character`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
|
|
-- ---------------------------------------------------------------------
|
|
-- Staatskasse (eine Zeile; Strafen und Rechnungen fliessen hierher)
|
|
-- ---------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `aether_treasury` (
|
|
`id` INT UNSIGNED NOT NULL DEFAULT 1,
|
|
`betrag` BIGINT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
|
INSERT IGNORE INTO `aether_treasury` (`id`, `betrag`) VALUES (1, 12480000);
|