setup.php

<?php
declare(strict_types=1);

error_reporting(E_ALL);
ini_set('display_errors', 'On');

require_once __DIR__ . '/bootstrap.php';

if (app_is_installed()) {
    set_flash('success', 'Setup ist bereits abgeschlossen.');
    redirect('index.php');
}

$errors = [];

if (request_is_post()) {
    verify_csrf_or_fail();

    $dbHost = old_input('db_host', '127.0.0.1');
    $dbPort = (int) old_input('db_port', '3306');
    $dbName = old_input('db_name', 'vereinsapp');
    $dbUser = old_input('db_user', 'root');
    $dbPass = (string) ($_POST['db_pass'] ?? '');

    $clubName = old_input('club_name');
    $officePassword = (string) ($_POST['office_password'] ?? '');
    $officeFirst = old_input('office_first_name');
    $officeLast = old_input('office_last_name');
    $officeBirthdate = old_input('office_birthdate');

    if ($clubName === '') {
        $errors[] = 'Vereinsname fehlt.';
    }
    if (!preg_match('/^[A-Za-z0-9_]+$/', $dbName)) {
        $errors[] = 'DB Name darf nur Buchstaben, Zahlen und _ enthalten.';
    }
    if ($dbPort < 1 || $dbPort > 65535) {
        $errors[] = 'DB Port ist ungültig.';
    }
    if ($officePassword === '' || strlen($officePassword) < 8) {
        $errors[] = 'Passwort für Geschäftsstelle muss mindestens 8 Zeichen haben.';
    }
    if ($officeFirst === '' || $officeLast === '' || $officeBirthdate === '') {
        $errors[] = 'Vorname, Nachname und Geburtsdatum der Geschäftsstelle sind Pflicht.';
    }

    if ($errors === []) {
        try {
            $serverDsn = sprintf('mysql:host=%s;port=%d;charset=utf8mb4', $dbHost, $dbPort);
            $serverPdo = new PDO($serverDsn, $dbUser, $dbPass, [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
            ]);

            $serverPdo->exec(sprintf('CREATE DATABASE IF NOT EXISTS `%s` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci', $dbName));
            $serverPdo->exec(sprintf('USE `%s`', $dbName));

            $schemaSql = [
                'CREATE TABLE IF NOT EXISTS settings (
                    id TINYINT UNSIGNED PRIMARY KEY,
                    club_name VARCHAR(255) NOT NULL,
                    log_retention_days INT NOT NULL DEFAULT 90,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',

                'CREATE TABLE IF NOT EXISTS offices (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    password_hash VARCHAR(255) NOT NULL,
                    must_change_password TINYINT(1) NOT NULL DEFAULT 0,
                    first_name VARCHAR(120) NOT NULL,
                    last_name VARCHAR(120) NOT NULL,
                    birthdate DATE NOT NULL,
                    street VARCHAR(190) NULL,
                    zip VARCHAR(20) NULL,
                    city VARCHAR(120) NULL,
                    mobile VARCHAR(50) NULL,
                    email VARCHAR(190) NULL,
                    notes TEXT NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',

                'CREATE TABLE IF NOT EXISTS coaches (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    club_member_id VARCHAR(64) NULL,
                    password_hash VARCHAR(255) NOT NULL,
                    must_change_password TINYINT(1) NOT NULL DEFAULT 1,
                    first_name VARCHAR(120) NOT NULL,
                    last_name VARCHAR(120) NOT NULL,
                    birthdate DATE NOT NULL,
                    street VARCHAR(190) NULL,
                    zip VARCHAR(20) NULL,
                    city VARCHAR(120) NULL,
                    mobile VARCHAR(50) NULL,
                    email VARCHAR(190) NULL,
                    notes TEXT NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',

                'CREATE TABLE IF NOT EXISTS assistants (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    club_member_id VARCHAR(64) NULL,
                    password_hash VARCHAR(255) NOT NULL,
                    must_change_password TINYINT(1) NOT NULL DEFAULT 1,
                    first_name VARCHAR(120) NOT NULL,
                    last_name VARCHAR(120) NOT NULL,
                    birthdate DATE NOT NULL,
                    street VARCHAR(190) NULL,
                    zip VARCHAR(20) NULL,
                    city VARCHAR(120) NULL,
                    mobile VARCHAR(50) NULL,
                    email VARCHAR(190) NULL,
                    notes TEXT NULL,
                    mother_name VARCHAR(120) NULL,
                    mother_mobile VARCHAR(50) NULL,
                    father_name VARCHAR(120) NULL,
                    father_mobile VARCHAR(50) NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',

                'CREATE TABLE IF NOT EXISTS athletes (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    turn_id VARCHAR(16) NULL UNIQUE,
                    club_member_id VARCHAR(64) NULL,
                    password_hash VARCHAR(255) NOT NULL,
                    must_change_password TINYINT(1) NOT NULL DEFAULT 1,
                    first_name VARCHAR(120) NOT NULL,
                    last_name VARCHAR(120) NOT NULL,
                    birthdate DATE NOT NULL,
                    street VARCHAR(190) NULL,
                    zip VARCHAR(20) NULL,
                    city VARCHAR(120) NULL,
                    mobile VARCHAR(50) NULL,
                    email VARCHAR(190) NULL,
                    notes TEXT NULL,
                    mother_name VARCHAR(120) NULL,
                    mother_mobile VARCHAR(50) NULL,
                    father_name VARCHAR(120) NULL,
                    father_mobile VARCHAR(50) NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    INDEX idx_athlete_name_birth (last_name, first_name, birthdate)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',

                'CREATE TABLE IF NOT EXISTS training_groups (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(180) NOT NULL UNIQUE,
                    description TEXT NULL,
                    whatsapp_join_url VARCHAR(1024) NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',

                'CREATE TABLE IF NOT EXISTS group_coaches (
                    group_id INT UNSIGNED NOT NULL,
                    coach_id INT UNSIGNED NOT NULL,
                    PRIMARY KEY (group_id, coach_id),
                    CONSTRAINT fk_group_coaches_group FOREIGN KEY (group_id) REFERENCES training_groups(id) ON DELETE CASCADE,
                    CONSTRAINT fk_group_coaches_coach FOREIGN KEY (coach_id) REFERENCES coaches(id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',

                'CREATE TABLE IF NOT EXISTS group_assistants (
                    group_id INT UNSIGNED NOT NULL,
                    assistant_id INT UNSIGNED NOT NULL,
                    PRIMARY KEY (group_id, assistant_id),
                    CONSTRAINT fk_group_assistants_group FOREIGN KEY (group_id) REFERENCES training_groups(id) ON DELETE CASCADE,
                    CONSTRAINT fk_group_assistants_assistant FOREIGN KEY (assistant_id) REFERENCES assistants(id) ON DELETE CASCADE
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',

                'CREATE TABLE IF NOT EXISTS athlete_group_memberships (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    athlete_id INT UNSIGNED NOT NULL,
                    group_id INT UNSIGNED NOT NULL,
                    joined_at DATE NOT NULL,
                    left_at DATE NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    CONSTRAINT fk_agm_athlete FOREIGN KEY (athlete_id) REFERENCES athletes(id) ON DELETE CASCADE,
                    CONSTRAINT fk_agm_group FOREIGN KEY (group_id) REFERENCES training_groups(id) ON DELETE CASCADE,
                    INDEX idx_agm_active (athlete_id, group_id, left_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',

                'CREATE TABLE IF NOT EXISTS invitations (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    group_id INT UNSIGNED NOT NULL,
                    token VARCHAR(64) NOT NULL UNIQUE,
                    expires_at DATETIME NOT NULL,
                    group_join_url VARCHAR(1024) NULL,
                    multi_use TINYINT(1) NOT NULL DEFAULT 1,
                    usage_count INT UNSIGNED NOT NULL DEFAULT 0,
                    created_by_role ENUM(\'geschaeftsstelle\',\'uebungsleiter\',\'assistent\') NOT NULL,
                    created_by_id INT UNSIGNED NOT NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    CONSTRAINT fk_invitation_group FOREIGN KEY (group_id) REFERENCES training_groups(id) ON DELETE CASCADE,
                    INDEX idx_invitation_expiry (expires_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',

                'CREATE TABLE IF NOT EXISTS audit_logs (
                    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
                    actor_role VARCHAR(40) NOT NULL,
                    actor_id INT UNSIGNED NULL,
                    action VARCHAR(80) NOT NULL,
                    entity_type VARCHAR(80) NOT NULL,
                    entity_id INT UNSIGNED NULL,
                    payload LONGTEXT NULL,
                    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                    INDEX idx_audit_created_at (created_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',

                'CREATE TABLE IF NOT EXISTS login_throttles (
                    throttle_key CHAR(64) PRIMARY KEY,
                    scope VARCHAR(20) NOT NULL,
                    role VARCHAR(40) NULL,
                    first_name_norm VARCHAR(120) NULL,
                    last_name_norm VARCHAR(120) NULL,
                    ip_address VARCHAR(64) NOT NULL,
                    failed_count INT NOT NULL DEFAULT 0,
                    window_started_at DATETIME NOT NULL,
                    locked_until DATETIME NULL,
                    last_failed_at DATETIME NULL,
                    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                    INDEX idx_login_throttles_locked_until (locked_until),
                    INDEX idx_login_throttles_updated_at (updated_at)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4',
            ];

            foreach ($schemaSql as $sql) {
                $serverPdo->exec($sql);
            }

            $settingsStmt = $serverPdo->prepare(
                'INSERT INTO settings (id, club_name, log_retention_days)
                 VALUES (1, :club_name, 90)
                 ON DUPLICATE KEY UPDATE club_name = VALUES(club_name), updated_at = NOW()'
            );
            $settingsStmt->execute(['club_name' => $clubName]);

            $existingOfficeStmt = $serverPdo->query('SELECT id FROM offices LIMIT 1');
            $hasOffice = (bool) $existingOfficeStmt->fetchColumn();

            if (!$hasOffice) {
                $officeStmt = $serverPdo->prepare(
                    'INSERT INTO offices (password_hash, first_name, last_name, birthdate)
                     VALUES (:password_hash, :first_name, :last_name, :birthdate)'
                );
                $officeStmt->execute([
                    'password_hash' => password_hash($officePassword, PASSWORD_DEFAULT),
                    'first_name' => $officeFirst,
                    'last_name' => $officeLast,
                    'birthdate' => $officeBirthdate,
                ]);
            }

            $configContent = "<?php\n\ndeclare(strict_types=1);\n\nreturn [\n"
                . "    'db_host' => '" . addslashes($dbHost) . "',\n"
                . "    'db_port' => " . $dbPort . ",\n"
                . "    'db_name' => '" . addslashes($dbName) . "',\n"
                . "    'db_user' => '" . addslashes($dbUser) . "',\n"
                . "    'db_pass' => '" . addslashes($dbPass) . "',\n"
                . "];\n";

            if (!is_dir(__DIR__ . '/config')) {
                mkdir(__DIR__ . '/config', 0775, true);
            }

            $written = file_put_contents(__DIR__ . '/config/config.php', $configContent, LOCK_EX);
            if ($written === false) {
                throw new RuntimeException('Konnte config/config.php nicht schreiben.');
            }

            set_flash('success', 'Setup erfolgreich abgeschlossen. Bitte jetzt anmelden.');
            redirect('index.php');
        } catch (Throwable $e) {
            $errors[] = 'Setup fehlgeschlagen: ' . $e->getMessage();
        }
    }
}

?><!DOCTYPE html>
<html lang="de">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Setup - Turn-App</title>
    <link rel="stylesheet" href="assets/css/app.css">
</head>
<body>
<div class="auth-wrap">
    <div class="form-card">
        <h1>Setup Turn-App</h1>
        <p class="muted">Einmalige Ersteinrichtung für Datenbank und Geschäftsstelle.</p>

        <?php foreach ($errors as $error): ?>
            <div class="flash flash-error"><?php echo e($error); ?></div>
        <?php endforeach; ?>

        <form method="post">
            <?php echo csrf_field(); ?>

            <h3>Datenbank</h3>
            <div class="two-col">
                <div>
                    <label for="db_host">DB Host</label>
                    <input id="db_host" name="db_host" value="<?php echo e(old_input('db_host', '127.0.0.1')); ?>" required>
                </div>
                <div>
                    <label for="db_port">DB Port</label>
                    <input id="db_port" name="db_port" type="number" value="<?php echo e(old_input('db_port', '3306')); ?>" required>
                </div>
                <div>
                    <label for="db_name">DB Name</label>
                    <input id="db_name" name="db_name" value="<?php echo e(old_input('db_name', 'vereinsapp')); ?>" required>
                </div>
                <div>
                    <label for="db_user">DB Benutzer</label>
                    <input id="db_user" name="db_user" value="<?php echo e(old_input('db_user', 'root')); ?>" required>
                </div>
            </div>
            <div>
                <label for="db_pass">DB Passwort</label>
                <input id="db_pass" name="db_pass" type="password" value="<?php echo e((string) ($_POST['db_pass'] ?? '')); ?>">
            </div>

            <h3>Verein</h3>
            <div>
                <label for="club_name">Vereinsname</label>
                <input id="club_name" name="club_name" value="<?php echo e(old_input('club_name')); ?>" required>
            </div>

            <h3>Erste Geschäftsstelle</h3>
            <div class="two-col">
                <div>
                    <label for="office_password">Passwort</label>
                    <input id="office_password" name="office_password" type="password" required>
                </div>
                <div>
                    <label for="office_first_name">Vorname</label>
                    <input id="office_first_name" name="office_first_name" value="<?php echo e(old_input('office_first_name')); ?>" required>
                </div>
                <div>
                    <label for="office_last_name">Nachname</label>
                    <input id="office_last_name" name="office_last_name" value="<?php echo e(old_input('office_last_name')); ?>" required>
                </div>
                <div>
                    <label for="office_birthdate">Geburtsdatum</label>
                    <input id="office_birthdate" name="office_birthdate" type="date" value="<?php echo e(old_input('office_birthdate')); ?>" required>
                </div>
            </div>

            <button type="submit">Setup starten</button>
        </form>
    </div>
</div>
</body>
</html>