black-sheep-bluff/app/Database/migrations.php
2026-09-18 06:46:56 +02:00

385 lines
18 KiB
PHP
Raw Permalink Blame History

This file contains ambiguous Unicode characters

This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

<?php
/**
* Automatische DB-Migrationen.
* Wird von api.php und auth.php eingebunden.
* Führt nur fehlende Versionen aus — ein SELECT pro Request nach dem Setup.
*/
declare(strict_types=1);
const LATEST_MIGRATION = 13;
function runMigrations(PDO $pdo, string $dbname): void
{
// Migrations-Tabelle anlegen (immer sicher)
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_migrations` (
`version` INT UNSIGNED NOT NULL,
`applied_at` INT UNSIGNED NOT NULL,
PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
$current = (int) $pdo->query("SELECT COALESCE(MAX(version),0) FROM gtl_migrations")->fetchColumn();
if ($current >= LATEST_MIGRATION) return;
// ── V1: Basis-Tabellen ────────────────────────────────────
if ($current < 1) {
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_rooms` (
`code` VARCHAR(12) NOT NULL,
`pass_hash` VARCHAR(255) NOT NULL,
`revealed` TINYINT(1) NOT NULL DEFAULT 0,
`created_at` INT UNSIGNED NOT NULL,
`updated_at` INT UNSIGNED NOT NULL,
PRIMARY KEY (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_players` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`room_code` VARCHAR(12) NOT NULL,
`pkey` VARCHAR(40) NOT NULL,
`name` VARCHAR(40) NOT NULL,
`question` TEXT NULL,
`answer` TEXT NULL,
`answered` TINYINT(1) NOT NULL DEFAULT 0,
`score` INT NOT NULL DEFAULT 0,
`updated_at`INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_room_player` (`room_code`,`pkey`),
CONSTRAINT `fk_player_room` FOREIGN KEY (`room_code`)
REFERENCES `gtl_rooms`(`code`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
markMigration($pdo, 1);
}
// ── V2: Neue Spalten + User/Statistik/Verlauf-Tabellen ───
if ($current < 2) {
// gtl_rooms
addColumnIfMissing($pdo, $dbname, 'gtl_rooms', 'liar_keys', 'TEXT NULL AFTER revealed');
addColumnIfMissing($pdo, $dbname, 'gtl_rooms', 'normal_question', 'TEXT NULL AFTER liar_keys');
addColumnIfMissing($pdo, $dbname, 'gtl_rooms', 'imposter_question', 'TEXT NULL AFTER normal_question');
addColumnIfMissing($pdo, $dbname, 'gtl_rooms', 'round_num', 'INT UNSIGNED NOT NULL DEFAULT 1 AFTER imposter_question');
addColumnIfMissing($pdo, $dbname, 'gtl_rooms', 'voted_out_keys', 'TEXT NULL AFTER round_num');
addColumnIfMissing($pdo, $dbname, 'gtl_rooms', 'round_outcome', 'VARCHAR(20) NULL AFTER voted_out_keys');
dropColumnIfExists($pdo, $dbname, 'gtl_rooms', 'liar_key'); // legacy
// gtl_players
addColumnIfMissing($pdo, $dbname, 'gtl_players', 'user_id', 'INT UNSIGNED NULL AFTER name');
addColumnIfMissing($pdo, $dbname, 'gtl_players', 'vote_for', 'VARCHAR(40) NULL AFTER score');
// gtl_users
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_users` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`username` VARCHAR(40) NOT NULL,
`credential_hash` VARCHAR(255) NULL,
`avatar` VARCHAR(255) NULL,
`role` ENUM('admin','user') NOT NULL DEFAULT 'user',
`created_at` INT UNSIGNED NOT NULL,
`last_login` INT UNSIGNED NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
// gtl_user_stats
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_user_stats` (
`user_id` INT UNSIGNED NOT NULL,
`games_played` INT UNSIGNED NOT NULL DEFAULT 0,
`games_won` INT UNSIGNED NOT NULL DEFAULT 0,
`games_lost` INT UNSIGNED NOT NULL DEFAULT 0,
`times_imposter` INT UNSIGNED NOT NULL DEFAULT 0,
`imposter_wins` INT UNSIGNED NOT NULL DEFAULT 0,
`imposter_losses` INT UNSIGNED NOT NULL DEFAULT 0,
`innocent_wins` INT UNSIGNED NOT NULL DEFAULT 0,
`innocent_losses` INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`user_id`),
CONSTRAINT `fk_stats_user` FOREIGN KEY (`user_id`) REFERENCES `gtl_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
// gtl_game_rounds
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_game_rounds` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`room_code` VARCHAR(12) NOT NULL,
`round_num` INT UNSIGNED NOT NULL DEFAULT 1,
`played_at` INT UNSIGNED NOT NULL,
`outcome` VARCHAR(20) NULL,
PRIMARY KEY (`id`),
KEY `idx_room` (`room_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
// gtl_round_players
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_round_players` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`round_id` INT UNSIGNED NOT NULL,
`user_id` INT UNSIGNED NULL,
`player_name` VARCHAR(40) NOT NULL,
`pkey` VARCHAR(40) NOT NULL,
`was_imposter`TINYINT(1) NOT NULL DEFAULT 0,
`voted_out` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_round_player` (`round_id`,`pkey`),
CONSTRAINT `fk_rp_round` FOREIGN KEY (`round_id`) REFERENCES `gtl_game_rounds`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
markMigration($pdo, 2);
}
// ── V3: Antworten + Fragen im Verlauf speichern ──────────
if ($current < 3) {
addColumnIfMissing($pdo, $dbname, 'gtl_round_players', 'question', 'TEXT NULL AFTER voted_out');
addColumnIfMissing($pdo, $dbname, 'gtl_round_players', 'answer', 'TEXT NULL AFTER question');
markMigration($pdo, 3);
}
// ── V4: uploads-Verzeichnis anlegen ──────────────────────
if ($current < 4) {
$dir = (defined('BSB_UPLOADS') ? BSB_UPLOADS : dirname(__DIR__, 2) . '/public/uploads') . '/avatars';
if (!is_dir($dir)) {
mkdir($dir, 0755, true);
}
markMigration($pdo, 4);
}
// ── V5: Voting-Steuerung ──────────────────────────────────
if ($current < 5) {
addColumnIfMissing($pdo, $dbname, 'gtl_rooms', 'voting_open',
'TINYINT(1) NOT NULL DEFAULT 0 AFTER round_outcome');
markMigration($pdo, 5);
}
// ── V6: Live-Emoji-Reaktionen ──────────────────────────────
if ($current < 6) {
addColumnIfMissing($pdo, $dbname, 'gtl_rooms', 'active_emojis',
'TEXT NULL DEFAULT NULL AFTER voting_open');
markMigration($pdo, 6);
}
// ── V7: Schrittweises Aufdecken, Imposter-Gewichte, Vote-Lock, Vote-Request ──
if ($current < 7) {
addColumnIfMissing($pdo, $dbname, 'gtl_rooms', 'reveal_index',
'INT NOT NULL DEFAULT -1 AFTER active_emojis');
addColumnIfMissing($pdo, $dbname, 'gtl_rooms', 'imposter_weights',
'VARCHAR(500) NULL DEFAULT NULL AFTER reveal_index');
addColumnIfMissing($pdo, $dbname, 'gtl_players', 'vote_locked',
'TINYINT(1) NOT NULL DEFAULT 0');
addColumnIfMissing($pdo, $dbname, 'gtl_players', 'vote_request',
'TINYINT(1) NOT NULL DEFAULT 0');
markMigration($pdo, 7);
}
// ── V8: Gezieltes Aufdecken (revealed_keys), Lobby-Chat ──────
if ($current < 8) {
addColumnIfMissing($pdo, $dbname, 'gtl_rooms', 'revealed_keys',
'TEXT NULL DEFAULT NULL AFTER reveal_index');
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_chat_messages` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`room_code` VARCHAR(12) NOT NULL,
`player_name` VARCHAR(40) NOT NULL,
`message` VARCHAR(500) NOT NULL,
`created_at` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_room` (`room_code`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
markMigration($pdo, 8);
}
// ── V9: Spieler-Namensfarbe ───────────────────────────────────
if ($current < 9) {
addColumnIfMissing($pdo, $dbname, 'gtl_users', 'color',
'VARCHAR(7) NULL DEFAULT NULL');
markMigration($pdo, 9);
}
// ── V10: Echte Frage für Imposter aufdecken (Leiter-Knopf) ────
if ($current < 10) {
addColumnIfMissing($pdo, $dbname, 'gtl_rooms', 'questions_revealed',
'TINYINT(1) NOT NULL DEFAULT 0');
markMigration($pdo, 10);
}
// ── V11: Mid-Game-Join, Custom-Sticker, Gruppen, Fake-Ads, Foreign Keys ──
if ($current < 11) {
// Nachzügler-Flag (rundenbezogen): Spieler, die mitten in einer Runde joinen
addColumnIfMissing($pdo, $dbname, 'gtl_players', 'late_join',
'TINYINT(1) NOT NULL DEFAULT 0 AFTER vote_request');
// Custom-Profil-Sticker (Dateiname in uploads/sticker/)
addColumnIfMissing($pdo, $dbname, 'gtl_users', 'sticker',
'VARCHAR(255) NULL DEFAULT NULL AFTER avatar');
// Herden / Gang-System
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_groups` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`group_name` VARCHAR(60) NOT NULL,
`created_by` INT UNSIGNED NULL,
`created_at` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_creator` (`created_by`),
CONSTRAINT `fk_group_creator` FOREIGN KEY (`created_by`)
REFERENCES `gtl_users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
// Mitgliedschaften (Mapping User ↔ Gruppe) – nötig für Online-/Lobby-Übersicht
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_group_members` (
`group_id` INT UNSIGNED NOT NULL,
`user_id` INT UNSIGNED NOT NULL,
`joined_at`INT UNSIGNED NOT NULL,
PRIMARY KEY (`group_id`,`user_id`),
KEY `idx_member_user` (`user_id`),
CONSTRAINT `fk_gm_group` FOREIGN KEY (`group_id`) REFERENCES `gtl_groups`(`id`) ON DELETE CASCADE,
CONSTRAINT `fk_gm_user` FOREIGN KEY (`user_id`) REFERENCES `gtl_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
// Fake-Werbung (Admin-Joke)
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_fake_ads` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`image_url` VARCHAR(500) NOT NULL,
`target_url` VARCHAR(500) NULL,
`is_active` TINYINT(1) NOT NULL DEFAULT 1,
`created_at` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
// Re-Engineering auf echte Foreign Keys (user_id → gtl_users.id, ON DELETE CASCADE).
// Defensiv: erst verwaiste Verweise auf NULL setzen, dann FK anlegen.
addForeignKeyIfMissing($pdo, $dbname, 'gtl_players', 'fk_player_user',
'user_id', 'gtl_users', 'id', 'ON DELETE CASCADE');
addForeignKeyIfMissing($pdo, $dbname, 'gtl_round_players', 'fk_rp_user',
'user_id', 'gtl_users', 'id', 'ON DELETE CASCADE');
// Online-Tracking für die Herden-Übersicht
addColumnIfMissing($pdo, $dbname, 'gtl_users', 'last_seen',
'INT UNSIGNED NULL DEFAULT NULL AFTER last_login');
addColumnIfMissing($pdo, $dbname, 'gtl_users', 'current_room',
'VARCHAR(12) NULL DEFAULT NULL AFTER last_seen');
// Sticker-Upload-Verzeichnis
$sdir = (defined('BSB_UPLOADS') ? BSB_UPLOADS : dirname(__DIR__, 2) . '/public/uploads') . '/sticker';
if (!is_dir($sdir)) { @mkdir($sdir, 0755, true); }
markMigration($pdo, 11);
}
// ── V12: Globale, admin-verwaltete Sounds / Reaktionen / Sticker ──
if ($current < 12) {
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_sounds` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`label` VARCHAR(60) NOT NULL,
`file` VARCHAR(255) NOT NULL,
`created_at` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
// Reaktion = Emoji-Zeichen ODER hochgeladenes Bild (genau eines gesetzt)
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_reactions` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`emoji` VARCHAR(16) NULL DEFAULT NULL,
`image` VARCHAR(255) NULL DEFAULT NULL,
`created_at` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
$pdo->exec("CREATE TABLE IF NOT EXISTS `gtl_stickers` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`label` VARCHAR(60) NOT NULL,
`file` VARCHAR(255) NOT NULL,
`created_at` INT UNSIGNED NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
// Bisherige Hardcode-Defaults übernehmen (nur wenn Tabellen leer)
$now = time();
if ((int) $pdo->query("SELECT COUNT(*) FROM gtl_reactions")->fetchColumn() === 0) {
$rs = $pdo->prepare("INSERT INTO gtl_reactions (emoji, created_at) VALUES (?, ?)");
foreach (['🐑', '🔥', '😂', '👍', '🐱'] as $e) { $rs->execute([$e, $now]); }
}
if ((int) $pdo->query("SELECT COUNT(*) FROM gtl_sounds")->fetchColumn() === 0) {
$ss = $pdo->prepare("INSERT INTO gtl_sounds (label, file, created_at) VALUES (?, ?, ?)");
$ss->execute(['🐑 Määh', '/sounds/maeh.mp3', $now]);
$ss->execute(['🔴 Fail-Buzzer', '/sounds/buzzer.mp3', $now]);
$ss->execute(['🥁 Trommelwirbel','/sounds/drumroll.mp3', $now]);
$ss->execute(['📢 Airhorn', '/sounds/airhorn.mp3', $now]);
}
// Upload-Verzeichnisse für die neuen Asset-Typen
$base = defined('BSB_UPLOADS') ? BSB_UPLOADS : dirname(__DIR__, 2) . '/public/uploads';
foreach (['sounds', 'reactions', 'stickers'] as $sub) {
if (!is_dir($base . '/' . $sub)) { @mkdir($base . '/' . $sub, 0755, true); }
}
markMigration($pdo, 12);
}
// ── V13: Name-Styling (Verlauf/Regenbogen/Font) + Theme-Präferenz ──
if ($current < 13) {
addColumnIfMissing($pdo, $dbname, 'gtl_users', 'name_style',
"VARCHAR(16) NOT NULL DEFAULT 'solid' AFTER color"); // solid | gradient | rainbow
addColumnIfMissing($pdo, $dbname, 'gtl_users', 'color2',
'VARCHAR(7) NULL DEFAULT NULL AFTER name_style'); // Verlauf-Endfarbe
addColumnIfMissing($pdo, $dbname, 'gtl_users', 'name_font',
'VARCHAR(40) NULL DEFAULT NULL AFTER color2'); // Font-Key
addColumnIfMissing($pdo, $dbname, 'gtl_users', 'theme',
"VARCHAR(10) NOT NULL DEFAULT 'system'"); // light | dark | system
markMigration($pdo, 13);
}
}
// ── Hilfsfunktionen ──────────────────────────────────────────
function columnExists(PDO $pdo, string $db, string $table, string $col): bool
{
$stmt = $pdo->prepare(
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA=? AND TABLE_NAME=? AND COLUMN_NAME=?"
);
$stmt->execute([$db, $table, $col]);
return (int) $stmt->fetchColumn() > 0;
}
function addColumnIfMissing(PDO $pdo, string $db, string $table, string $col, string $def): void
{
if (!columnExists($pdo, $db, $table, $col)) {
$pdo->exec("ALTER TABLE `$table` ADD COLUMN `$col` $def");
}
}
function foreignKeyExists(PDO $pdo, string $db, string $table, string $constraint): bool
{
$stmt = $pdo->prepare(
"SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA=? AND TABLE_NAME=? AND CONSTRAINT_NAME=? AND CONSTRAINT_TYPE='FOREIGN KEY'"
);
$stmt->execute([$db, $table, $constraint]);
return (int) $stmt->fetchColumn() > 0;
}
/**
* Legt einen Foreign Key an, falls noch nicht vorhanden.
* Räumt vorher verwaiste Verweise auf (→ NULL) und kapselt Fehler,
* damit eine fehlschlagende FK-Anlage die Migrationen niemals blockiert.
*/
function addForeignKeyIfMissing(PDO $pdo, string $db, string $table, string $constraint,
string $col, string $refTable, string $refCol, string $onDelete): void
{
if (foreignKeyExists($pdo, $db, $table, $constraint)) return;
if (!columnExists($pdo, $db, $table, $col)) return;
try {
$pdo->exec("UPDATE `$table` SET `$col`=NULL
WHERE `$col` IS NOT NULL AND `$col` NOT IN (SELECT `$refCol` FROM `$refTable`)");
$pdo->exec("ALTER TABLE `$table` ADD CONSTRAINT `$constraint`
FOREIGN KEY (`$col`) REFERENCES `$refTable`(`$refCol`) $onDelete");
} catch (Throwable $e) {
// Daten-Inkonsistenz o. ä. → FK nicht erzwingen, Migration trotzdem abschließen
}
}
function dropColumnIfExists(PDO $pdo, string $db, string $table, string $col): void
{
if (columnExists($pdo, $db, $table, $col)) {
$pdo->exec("ALTER TABLE `$table` DROP COLUMN `$col`");
}
}
function markMigration(PDO $pdo, int $version): void
{
$pdo->prepare("INSERT IGNORE INTO gtl_migrations (version, applied_at) VALUES (?,?)")
->execute([$version, time()]);
}