<?php
declare(strict_types=1);

define('BASE_PATH',    __DIR__);
define('DATA_PATH',    __DIR__ . '/data');
define('UPLOAD_PATH',  __DIR__ . '/data/uploads');
define('DB_PATH',      __DIR__ . '/data/database.sqlite');
define('APP_VERSION',  '1.20.2');

// Auto-detect subfolder deployment (e.g. /pm, /tools/pm, or /)
$_scriptDir = dirname($_SERVER['SCRIPT_NAME'] ?? '/index.php');
define('APP_BASE', $_scriptDir === '/' || $_scriptDir === '\\' ? '' : rtrim($_scriptDir, '/'));

// Composer autoloader
if (!file_exists(BASE_PATH . '/vendor/autoload.php')) {
    http_response_code(503);
    die('<h1 style="font-family:sans-serif">Setup benötigt</h1><p>Bitte ausführen: <code>composer install</code></p>');
}
require_once BASE_PATH . '/vendor/autoload.php';

// Core source files
foreach (['Crypto', 'Database', 'Migrator', 'Auth', 'Lang', 'Helper', 'Mailer', 'TOTP', 'AI', 'PluginManager', 'Updater', 'PasswordPolicy', 'SecurityLog', 'HealthCheck', 'Backup'] as $class) {
    require_once BASE_PATH . '/src/' . $class . '.php';
}

// Check installation
$installed = file_exists(DB_PATH);

// Start session
$_isHttps = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off')
         || ($_SERVER['HTTP_X_FORWARDED_PROTO'] ?? '') === 'https';
session_set_cookie_params(['httponly' => true, 'samesite' => 'Lax', 'secure' => $_isHttps]);
unset($_isHttps);
session_start();

// ── CSP nonce (per request, used in <script nonce="..."> tags) ───────────────
$GLOBALS['csp_nonce'] = base64_encode(random_bytes(16));
function csp_nonce(): string {
    return $GLOBALS['csp_nonce'];
}
// Enforced strict CSP. script-src has no 'unsafe-inline': every script must
// carry the per-request nonce or be loaded by a script that does, so a
// successful HTML injection cannot smuggle in executable JavaScript.
// style-src keeps 'unsafe-inline' because the views still ship inline
// style="…" attributes, which CSP does not allow nonces to whitelist.
header(
    "Content-Security-Policy: "
    . "default-src 'self'; "
    . "script-src 'self' 'nonce-" . $GLOBALS['csp_nonce'] . "' 'strict-dynamic'; "
    . "style-src 'self' 'unsafe-inline'; "
    . "img-src 'self' data:; "
    . "font-src 'self'; "
    . "connect-src 'self'; "
    . "frame-ancestors 'none'; "
    . "object-src 'none'; "
    . "base-uri 'self';"
);
// Prevent token-bearing URLs (e.g. /ext/{64-hex} for project externals,
// password-reset and email-verify links) from leaking via the Referer
// header to off-site resources. 'same-origin' still sends the referer for
// in-app navigation so analytics on our own pages keep working.
header('Referrer-Policy: same-origin');

// Initialize language: session override > Accept-Language > admin default > fallback
if ($installed) {
    if (isset($_SESSION['lang']) && Lang::isSupported($_SESSION['lang'])) {
        Lang::load($_SESSION['lang']);
    } else {
        $browserLang = Lang::fromAcceptHeader($_SERVER['HTTP_ACCEPT_LANGUAGE'] ?? null);
        if ($browserLang === Lang::FALLBACK) {
            // No browser preference matched a supported language → use admin-configured default
            try {
                $row = Database::getInstance()->fetch("SELECT value FROM settings WHERE key='app_default_language'");
                $adminDefault = $row['value'] ?? '';
                if ($adminDefault && Lang::isSupported($adminDefault)) $browserLang = $adminDefault;
            } catch (\Throwable) { /* DB not yet ready */ }
        }
        Lang::load($browserLang);
    }
} else {
    Lang::load('de');
}

// Set the PHP-default timezone for date()/strtotime() calls. Per-call code can
// still override, but this is a sane base. Order: logged-in user > admin default > UTC.
if ($installed) {
    try {
        $tz = 'UTC';
        if (Auth::check()) {
            $u = Auth::user();
            if (!empty($u['timezone']) && Helper::isValidTimezone($u['timezone'])) $tz = $u['timezone'];
        }
        if ($tz === 'UTC') {
            $adminTz = Helper::getSetting('app_default_timezone', 'UTC');
            if (Helper::isValidTimezone($adminTz)) $tz = $adminTz;
        }
        date_default_timezone_set($tz);
    } catch (\Throwable) { /* keep PHP default */ }
}

// Parse URI – strip subfolder prefix so routing works from any deployment path
$uri = $_SERVER['REQUEST_URI'] ?? '/';
$uri = parse_url($uri, PHP_URL_PATH);
if (APP_BASE !== '' && str_starts_with($uri, APP_BASE)) {
    $uri = substr($uri, strlen(APP_BASE));
}
$uri = rtrim($uri, '/') ?: '/';

// ── URL helper – prepends APP_BASE to all internal paths ─────────────────────
function url(string $path = '/'): string
{
    return APP_BASE . '/' . ltrim($path, '/');
}

// ── View helper ──────────────────────────────────────────────────────────────
function view(string $template, array $data = [], string $layout = 'base'): void
{
    extract($data, EXTR_SKIP);
    ob_start();
    $tplFile = BASE_PATH . '/views/' . $template . '.php';
    if (!file_exists($tplFile)) {
        ob_end_clean();
        die('View not found: ' . htmlspecialchars($template));
    }
    include $tplFile;
    $content = ob_get_clean();

    $layoutFile = BASE_PATH . '/views/layout/' . $layout . '.php';
    if ($layout && file_exists($layoutFile)) {
        include $layoutFile;
    } else {
        echo $content;
    }
}

// ── Install redirect ──────────────────────────────────────────────────────────
if (!$installed && $uri !== '/install') {
    Helper::redirect('/install');
}

// ── Load all controllers ──────────────────────────────────────────────────────
foreach (glob(BASE_PATH . '/controllers/*.php') as $f) {
    require_once $f;
}

// ── Drain bundled-plugins staging (post-update bootstrap) ────────────────────
// Older auto-updaters skipped every `plugins/` path in the release ZIP, so
// official plugins like LDAP cannot reach those installations through the
// updater alone. Each release ZIP also carries a copy under `_bundled-plugins/`
// (a path the legacy updater does not skip); the installer drains that
// directory into `plugins/` exactly once, then removes it.
require_once BASE_PATH . '/src/BundledPluginInstaller.php';
BundledPluginInstaller::run();

// ── Load plugins ──────────────────────────────────────────────────────────────
PluginManager::load();

// ── DB migrations (idempotent ALTER TABLE for existing installs) ──────────────
if ($installed) {
    $db = Database::getInstance();
    $cols = array_column($db->fetchAll('PRAGMA table_info(users)'), 'name');
    if (!in_array('reset_token', $cols))           $db->execute('ALTER TABLE users ADD COLUMN reset_token TEXT');
    if (!in_array('reset_token_expires', $cols))   $db->execute('ALTER TABLE users ADD COLUMN reset_token_expires INTEGER');
    if (!in_array('email_verify_token', $cols))    $db->execute('ALTER TABLE users ADD COLUMN email_verify_token TEXT');
    if (!in_array('email_verify_expires', $cols))  $db->execute('ALTER TABLE users ADD COLUMN email_verify_expires INTEGER');
    // Tracks the latest password change so other live sessions can be invalidated (security-audit #14)
    if (!in_array('password_changed_at', $cols))   $db->execute('ALTER TABLE users ADD COLUMN password_changed_at INTEGER');
    // Pending email change with verification token (security-audit #2)
    if (!in_array('pending_email', $cols))         $db->execute('ALTER TABLE users ADD COLUMN pending_email TEXT');
    if (!in_array('pending_email_token', $cols))   $db->execute('ALTER TABLE users ADD COLUMN pending_email_token TEXT');
    if (!in_array('pending_email_expires', $cols)) $db->execute('ALTER TABLE users ADD COLUMN pending_email_expires INTEGER');
    $db->execute('CREATE TABLE IF NOT EXISTS login_attempts (
        ip TEXT NOT NULL,
        attempted_at INTEGER NOT NULL
    )');
    $db->execute('CREATE INDEX IF NOT EXISTS idx_login_attempts_ip ON login_attempts(ip, attempted_at)');
    $db->execute('CREATE TABLE IF NOT EXISTS project_notification_settings (
        project_id                      INTEGER PRIMARY KEY,
        notify_assignee_on_assign       INTEGER NOT NULL DEFAULT 1,
        notify_assignee_reminder        INTEGER NOT NULL DEFAULT 1,
        notify_assignee_predecessors    INTEGER NOT NULL DEFAULT 1,
        notify_manager_any_activity     INTEGER NOT NULL DEFAULT 0,
        notify_manager_task_done        INTEGER NOT NULL DEFAULT 1,
        FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
    )');

    // project_externals table
    $db->execute('CREATE TABLE IF NOT EXISTS project_externals (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        project_id INTEGER NOT NULL,
        name       TEXT NOT NULL,
        email      TEXT,
        token      TEXT UNIQUE,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
        UNIQUE(project_id, email)
    )');

    // project_externals: make email and token nullable (migration for existing installs)
    $peColInfo = $db->fetchAll('PRAGMA table_info(project_externals)');
    $peEmailNotNull = false;
    foreach ($peColInfo as $c) {
        if ($c['name'] === 'email' && (int)$c['notnull'] === 1) { $peEmailNotNull = true; break; }
    }
    if ($peEmailNotNull) {
        $pdo2 = $db->getPdo();
        $pdo2->exec('PRAGMA foreign_keys=OFF');
        $pdo2->beginTransaction();
        try {
            $pdo2->exec('DROP TABLE IF EXISTS project_externals_v2');
            $pdo2->exec('CREATE TABLE project_externals_v2 (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                project_id INTEGER NOT NULL,
                name       TEXT NOT NULL,
                email      TEXT,
                token      TEXT UNIQUE,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
                UNIQUE(project_id, email)
            )');
            $pdo2->exec('INSERT INTO project_externals_v2 SELECT * FROM project_externals');
            $pdo2->exec('DROP TABLE project_externals');
            $pdo2->exec('ALTER TABLE project_externals_v2 RENAME TO project_externals');
            $pdo2->commit();
        } catch (\Throwable $e) {
            $pdo2->rollBack();
            error_log('project_externals migration failed: ' . $e->getMessage());
        }
        $pdo2->exec('PRAGMA foreign_keys=ON');
        unset($pdo2);
    }
    unset($peColInfo, $peEmailNotNull);

    // task_assignees.external_id
    $taCols = array_column($db->fetchAll('PRAGMA table_info(task_assignees)'), 'name');
    if (!in_array('external_id', $taCols)) {
        $db->execute('ALTER TABLE task_assignees ADD COLUMN external_id INTEGER REFERENCES project_externals(id) ON DELETE CASCADE');
    }

    // comments: make user_id nullable + add external_id / external_name
    $commentColInfo = $db->fetchAll('PRAGMA table_info(comments)');
    $commentColNames = array_column($commentColInfo, 'name');
    $userIdNotNull = false;
    foreach ($commentColInfo as $c) {
        if ($c['name'] === 'user_id' && (int)$c['notnull'] === 1) { $userIdNotNull = true; break; }
    }
    if ($userIdNotNull || !in_array('external_id', $commentColNames)) {
        $pdo = $db->getPdo();
        $pdo->exec('PRAGMA foreign_keys=OFF');
        $pdo->beginTransaction();
        try {
            $pdo->exec('DROP TABLE IF EXISTS comments_v2');
            $pdo->exec('CREATE TABLE comments_v2 (
                id            INTEGER PRIMARY KEY AUTOINCREMENT,
                task_id       INTEGER NOT NULL,
                user_id       INTEGER,
                external_id   INTEGER,
                external_name TEXT,
                content       TEXT NOT NULL,
                is_edited     INTEGER DEFAULT 0,
                created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
                updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (task_id)     REFERENCES tasks(id)             ON DELETE CASCADE,
                FOREIGN KEY (user_id)     REFERENCES users(id)             ON DELETE SET NULL,
                FOREIGN KEY (external_id) REFERENCES project_externals(id) ON DELETE SET NULL
            )');
            $pdo->exec('INSERT INTO comments_v2 (id, task_id, user_id, content, is_edited, created_at, updated_at)
                        SELECT id, task_id, user_id, content, is_edited, created_at, updated_at FROM comments');
            $pdo->exec('DROP TABLE comments');
            $pdo->exec('ALTER TABLE comments_v2 RENAME TO comments');
            $pdo->commit();
        } catch (\Throwable $e) {
            $pdo->rollBack();
            error_log('Comments migration failed: ' . $e->getMessage());
        }
        $pdo->exec('PRAGMA foreign_keys=ON');
        unset($pdo);
    }

    // activity_log: make user_id nullable (external users have no user_id)
    $alColInfo = $db->fetchAll('PRAGMA table_info(activity_log)');
    $alUserNotNull = false;
    foreach ($alColInfo as $c) {
        if ($c['name'] === 'user_id' && (int)$c['notnull'] === 1) { $alUserNotNull = true; break; }
    }
    if ($alUserNotNull) {
        $pdo3 = new \PDO('sqlite:' . DB_PATH);
        $pdo3->exec('PRAGMA foreign_keys=OFF');
        $pdo3->beginTransaction();
        try {
            $pdo3->exec('
                CREATE TABLE activity_log_new (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    project_id INTEGER,
                    task_id INTEGER,
                    user_id INTEGER,
                    action TEXT NOT NULL,
                    description TEXT,
                    old_value TEXT,
                    new_value TEXT,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users(id)
                )
            ');
            $pdo3->exec('INSERT INTO activity_log_new SELECT * FROM activity_log');
            $pdo3->exec('DROP TABLE activity_log');
            $pdo3->exec('ALTER TABLE activity_log_new RENAME TO activity_log');
            $pdo3->commit();
        } catch (\Throwable $e) {
            $pdo3->rollBack();
            error_log('activity_log migration failed: ' . $e->getMessage());
        }
        $pdo3->exec('PRAGMA foreign_keys=ON');
        unset($pdo3);
    }

    // groups table + user group columns
    $db->execute('CREATE TABLE IF NOT EXISTS groups (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        name        TEXT NOT NULL UNIQUE,
        description TEXT,
        created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
    )');
    $userCols = array_column($db->fetchAll('PRAGMA table_info(users)'), 'name');
    if (!in_array('group_id',   $userCols)) $db->execute('ALTER TABLE users ADD COLUMN group_id   INTEGER');
    if (!in_array('group_role', $userCols)) $db->execute('ALTER TABLE users ADD COLUMN group_role TEXT');
    $groupCols = array_column($db->fetchAll('PRAGMA table_info(groups)'), 'name');
    if (!in_array('ai_disabled', $groupCols)) $db->execute('ALTER TABLE groups ADD COLUMN ai_disabled INTEGER NOT NULL DEFAULT 0');

    // Remove UNIQUE constraint from groups.name (allow duplicate group names)
    $groupIdxList = $db->fetchAll('PRAGMA index_list(groups)');
    $groupNameUnique = false;
    foreach ($groupIdxList as $idx) {
        if ((int)$idx['unique'] === 1) {
            $idxCols = $db->fetchAll('PRAGMA index_info(' . $idx['name'] . ')');
            foreach ($idxCols as $c) {
                if ($c['name'] === 'name') { $groupNameUnique = true; break 2; }
            }
        }
    }
    if ($groupNameUnique) {
        $pdo5 = $db->getPdo();
        $pdo5->exec('PRAGMA foreign_keys=OFF');
        $pdo5->beginTransaction();
        try {
            $pdo5->exec('CREATE TABLE groups_v2 (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                name        TEXT NOT NULL,
                description TEXT,
                ai_disabled INTEGER NOT NULL DEFAULT 0,
                created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
            )');
            $pdo5->exec('INSERT INTO groups_v2 SELECT id,name,description,ai_disabled,created_at FROM groups');
            $pdo5->exec('DROP TABLE groups');
            $pdo5->exec('ALTER TABLE groups_v2 RENAME TO groups');
            $pdo5->commit();
        } catch (\Throwable $e) {
            $pdo5->rollBack();
            error_log('groups name-unique migration failed: ' . $e->getMessage());
        }
        $pdo5->exec('PRAGMA foreign_keys=ON');
        unset($pdo5);
    }
    unset($groupIdxList, $groupNameUnique);

    // group_members junction table (n:m users ↔ groups)
    $db->execute('CREATE TABLE IF NOT EXISTS group_members (
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
        group_id INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
        user_id  INTEGER NOT NULL REFERENCES users(id)  ON DELETE CASCADE,
        role     TEXT NOT NULL DEFAULT \'member\',
        UNIQUE(group_id, user_id)
    )');
    // Migrate legacy single-group assignments from users.group_id / users.group_role
    $legacy = $db->fetchAll('SELECT id, group_id, group_role FROM users WHERE group_id IS NOT NULL');
    foreach ($legacy as $row) {
        $role = $row['group_role'] === 'admin' ? 'admin' : 'member';
        $db->execute('INSERT OR IGNORE INTO group_members (group_id, user_id, role) VALUES (?,?,?)',
            [$row['group_id'], $row['id'], $role]);
    }
    // Clear legacy columns after migration
    if (!empty($legacy)) {
        $db->execute('UPDATE users SET group_id = NULL, group_role = NULL WHERE group_id IS NOT NULL');
    }
    unset($legacy);

    // invitations table
    $db->execute('CREATE TABLE IF NOT EXISTS invitations (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        token      TEXT NOT NULL UNIQUE,
        email      TEXT NOT NULL,
        group_id   INTEGER NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
        group_role TEXT NOT NULL DEFAULT \'member\',
        invited_by INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        expires_at INTEGER NOT NULL,
        used_at    DATETIME
    )');

    // email MFA
    $userCols2 = array_column($db->fetchAll('PRAGMA table_info(users)'), 'name');
    if (!in_array('email_mfa_enabled', $userCols2)) {
        $db->execute('ALTER TABLE users ADD COLUMN email_mfa_enabled INTEGER NOT NULL DEFAULT 0');
    }
    $db->execute('CREATE TABLE IF NOT EXISTS email_mfa_codes (
        id         INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        code_hash  TEXT NOT NULL,
        expires_at INTEGER NOT NULL,
        used_at    INTEGER
    )');
    // Brute-force counter per code (defence-in-depth alongside the per-session limit).
    try { $db->execute('ALTER TABLE email_mfa_codes ADD COLUMN attempts INTEGER NOT NULL DEFAULT 0'); } catch (\Throwable) {}
    $db->execute('CREATE TABLE IF NOT EXISTS external_mfa_codes (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        external_id INTEGER NOT NULL REFERENCES project_externals(id) ON DELETE CASCADE,
        code_hash   TEXT NOT NULL,
        expires_at  INTEGER NOT NULL,
        used_at     INTEGER
    )');
    try { $db->execute('ALTER TABLE external_mfa_codes ADD COLUMN attempts INTEGER NOT NULL DEFAULT 0'); } catch (\Throwable) {}
    $db->execute('CREATE TABLE IF NOT EXISTS webauthn_credentials (
        id            INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id       INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        credential_id TEXT NOT NULL UNIQUE,
        public_key    TEXT NOT NULL,
        sign_count    INTEGER NOT NULL DEFAULT 0,
        name          TEXT NOT NULL DEFAULT \'Security Key\',
        created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
        last_used_at  DATETIME
    )');

    // Remove UNIQUE constraint from username (email is the sole unique identifier)
    $idxList = $db->fetchAll('PRAGMA index_list(users)');
    $usernameUnique = false;
    foreach ($idxList as $idx) {
        if ((int)$idx['unique'] === 1) {
            $idxCols = $db->fetchAll('PRAGMA index_info(' . $idx['name'] . ')');
            foreach ($idxCols as $c) {
                if ($c['name'] === 'username') { $usernameUnique = true; break 2; }
            }
        }
    }
    if ($usernameUnique) {
        try {
            $pdo4 = $db->getPdo();
            $pdo4->exec('PRAGMA foreign_keys = OFF');
            $pdo4->exec('BEGIN');
            $pdo4->exec('CREATE TABLE users_v2 (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL,
                email TEXT NOT NULL UNIQUE,
                password TEXT NOT NULL,
                role TEXT DEFAULT \'user\',
                language TEXT DEFAULT \'de\',
                theme TEXT DEFAULT \'light\',
                avatar TEXT,
                is_active INTEGER DEFAULT 1,
                totp_secret TEXT,
                totp_enabled INTEGER NOT NULL DEFAULT 0,
                reset_token TEXT,
                reset_token_expires INTEGER,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                last_login DATETIME,
                group_id INTEGER,
                group_role TEXT,
                email_mfa_enabled INTEGER NOT NULL DEFAULT 0
            )');
            $pdo4->exec('INSERT INTO users_v2 SELECT id,username,email,password,role,language,theme,avatar,is_active,totp_secret,totp_enabled,reset_token,reset_token_expires,created_at,last_login,group_id,group_role,email_mfa_enabled FROM users');
            $pdo4->exec('DROP TABLE users');
            $pdo4->exec('ALTER TABLE users_v2 RENAME TO users');
            $pdo4->exec('COMMIT');
            $pdo4->exec('PRAGMA foreign_keys = ON');
        } catch (\Throwable $e) {
            error_log('users username-unique migration failed: ' . $e->getMessage());
        }
        unset($pdo4);
    }
    unset($idxList, $usernameUnique);

    // can_create_groups + group_creation_requests
    $userColsCgr = array_column($db->fetchAll('PRAGMA table_info(users)'), 'name');
    if (!in_array('can_create_groups', $userColsCgr)) {
        $db->execute('ALTER TABLE users ADD COLUMN can_create_groups INTEGER NOT NULL DEFAULT 0');
    }
    unset($userColsCgr);
    $db->execute('CREATE TABLE IF NOT EXISTS group_creation_requests (
        id          INTEGER PRIMARY KEY AUTOINCREMENT,
        user_id     INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
        status      TEXT NOT NULL DEFAULT \'pending\',
        created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
        reviewed_at DATETIME,
        reviewed_by INTEGER REFERENCES users(id)
    )');

    // preferred_view column
    $userColsPv = array_column($db->fetchAll('PRAGMA table_info(users)'), 'name');
    if (!in_array('preferred_view', $userColsPv)) {
        $db->execute("ALTER TABLE users ADD COLUMN preferred_view TEXT NOT NULL DEFAULT 'kanban'");
    }
    unset($userColsPv);

    // Account lockout columns (per-account brute-force protection)
    $userColsLk = array_column($db->fetchAll('PRAGMA table_info(users)'), 'name');
    if (!in_array('failed_login_count', $userColsLk)) {
        $db->execute('ALTER TABLE users ADD COLUMN failed_login_count INTEGER NOT NULL DEFAULT 0');
    }
    if (!in_array('locked_until', $userColsLk)) {
        $db->execute('ALTER TABLE users ADD COLUMN locked_until INTEGER');
    }
    unset($userColsLk);

    // One-time invalidation of plaintext password-reset / email-verify tokens.
    // After this, tokens are stored as SHA-256 hashes; old plaintext tokens
    // would never match the new hash-based lookup, so we clear them now.
    if (Helper::getSetting('migration_token_hash_v1', '0') !== '1') {
        $db->execute('UPDATE users SET reset_token=NULL, reset_token_expires=NULL WHERE reset_token IS NOT NULL');
        $db->execute('UPDATE users SET email_verify_token=NULL, email_verify_expires=NULL WHERE email_verify_token IS NOT NULL');
        Helper::setSetting('migration_token_hash_v1', '1');
    }

    unset($db, $cols, $taCols, $commentColInfo, $commentColNames, $userIdNotNull, $alColInfo, $alUserNotNull, $userCols, $userCols2);

    // ── Session timeouts (admin-configurable) ────────────────────────────────
    // Idle timeout: logout after N minutes without activity.
    // Absolute timeout: logout after N hours since login regardless of activity.
    // 0 = disabled. Defaults: 30 min idle, 8 h absolute.
    if (!empty($_SESSION['user_id'])) {
        $idleMin   = (int)Helper::getSetting('session_idle_timeout_minutes', '30');
        $absHours  = (int)Helper::getSetting('session_absolute_lifetime_hours', '8');
        $now       = time();
        $expired   = false;
        $expiredReason = 'session_expired';

        if ($idleMin > 0 && isset($_SESSION['_last_activity'])
            && ($now - (int)$_SESSION['_last_activity']) > $idleMin * 60) {
            $expired = true;
        }
        if (!$expired && $absHours > 0 && isset($_SESSION['_login_time'])
            && ($now - (int)$_SESSION['_login_time']) > $absHours * 3600) {
            $expired = true;
        }

        // Password-change session invalidation: if the user (or someone using
        // their account) changed the password after this session was minted,
        // every other still-live session is forcibly logged out on its next
        // request. The session that performed the change re-stamps _login_time
        // in change_password and is therefore unaffected. (security-audit #14)
        if (!$expired) {
            try {
                $u = Database::getInstance()->fetch(
                    'SELECT password_changed_at FROM users WHERE id = ?',
                    [(int)$_SESSION['user_id']]
                );
                $pwChanged = (int)($u['password_changed_at'] ?? 0);
                if ($pwChanged > 0
                    && isset($_SESSION['_login_time'])
                    && (int)$_SESSION['_login_time'] < $pwChanged) {
                    $expired       = true;
                    $expiredReason = 'session_invalidated_password_changed';
                }
            } catch (\Throwable) { /* column may be missing on very old installs */ }
        }

        if ($expired) {
            Auth::logout();
            $_SESSION['flash']['error'] = Lang::get($expiredReason);
            header('Location: ' . APP_BASE . '/login');
            exit;
        }
        $_SESSION['_last_activity'] = $now;
    }
}

// ── Versioned migrations (idempotent; runs only unapplied ones) ───────────────
if ($installed) {
    Migrator::run();
}

// ── Forgot / Reset password + Register (public – no auth needed) ─────────────
if ($uri === '/forgot-password') { AuthController::forgotPassword(); exit; }
if ($uri === '/reset-password')  { AuthController::resetPassword();  exit; }
if ($uri === '/register')        { AuthController::register();       exit; }
if ($uri === '/verify-email')    { AuthController::verifyEmail();    exit; }
if ($uri === '/verify-email-change') { AuthController::verifyEmailChange(); exit; }

// ── Plugin public dynamic routes (no auth needed, e.g. invite acceptance) ────
foreach (PluginManager::getPublicDynamicRoutes() as $_pattern => $_handler) {
    if (preg_match($_pattern, $uri, $_im)) {
        $_handler($_im); exit;
    }
}
unset($_pattern, $_handler, $_im);

// ── External token access (public – no auth needed) ───────────────────────────
if (preg_match('#^/ext/([a-f0-9]{64})/verify$#', $uri, $_em)) {
    ExternalController::verify($_em[1]); exit;
}
if (preg_match('#^/ext/([a-f0-9]{64})$#', $uri, $_em)) {
    ExternalController::overview($_em[1]); exit;
}
if (preg_match('#^/ext/([a-f0-9]{64})/task/(\d+)$#', $uri, $_em)) {
    ExternalController::task($_em[1], (int)$_em[2]); exit;
}

// ── Avatar (auth required to prevent user enumeration) ──────────────────────
if (preg_match('#^/api/avatar/(\d+)$#', $uri, $m)) {
    if (!Auth::check()) {
        http_response_code(404);
        exit;
    }
    $userId = (int)$m[1];
    $row    = Database::getInstance()->fetch('SELECT avatar FROM users WHERE id = ?', [$userId]);
    $allowedExts = ['jpg','jpeg','png','gif','webp'];
    if ($row && !empty($row['avatar'])) {
        $ext  = strtolower(pathinfo($row['avatar'], PATHINFO_EXTENSION));
        $path = DATA_PATH . '/avatars/' . basename($row['avatar']);
        if (in_array($ext, $allowedExts, true) && file_exists($path)) {
            $mime = (new \finfo(FILEINFO_MIME_TYPE))->file($path);
            header('Content-Type: ' . $mime);
            header('X-Content-Type-Options: nosniff');
            header('Content-Disposition: inline');
            header('Cache-Control: private, max-age=86400');
            readfile($path);
        } else {
            http_response_code(404);
        }
    } else {
        http_response_code(404);
    }
    exit;
}

// ── Public routes (no auth needed) ───────────────────────────────────────────
$public = ['/', '/login', '/install', '/totp-verify', '/totp-setup-forced', '/mfa-choice', '/mfa-setup', '/email-mfa-verify', '/forgot-password', '/reset-password', '/register', '/verify-email', '/verify-email-change', '/help', '/help/group-admin', '/help/project-manager', '/help/task-worker', '/help/external', '/help/notifications', '/update/manifest', '/downloads', '/contact', '/LICENSE', '/robots.txt', '/sitemap.xml', '/webauthn/login-challenge', '/webauthn/login-verify'];
// /ext/... and /invite/... are already handled above before the auth check

if ($installed && !Auth::check() && !in_array($uri, $public)) {
    header('Location: /');
    exit;
}

// ── Routing ───────────────────────────────────────────────────────────────────
// Regex-based dynamic routing
$routes = [
    // Static
    '/'                      => fn() => Auth::check() ? Helper::redirect('/dashboard') : (file_exists(BASE_PATH . '/views/landing/index.php') ? view('landing/index', [], 'landing') : Helper::redirect('/login')),
    '/downloads'             => fn() => file_exists(BASE_PATH . '/views/landing/downloads.php') ? view('landing/downloads', [], 'landing') : Helper::redirect('/'),
    '/robots.txt'            => function() {
        $scheme = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off')
               || (($_SERVER['HTTP_X_FORWARDED_PROTO'] ?? '') === 'https') ? 'https' : 'http';
        $base   = $scheme . '://' . ($_SERVER['HTTP_HOST'] ?? 'localhost') . APP_BASE;
        header('Content-Type: text/plain; charset=utf-8');
        header('Cache-Control: public, max-age=3600');
        echo "User-agent: *\n";
        echo "Allow: /\n";
        echo "Allow: /downloads\n";
        echo "Disallow: /admin/\n";
        echo "Disallow: /api/\n";
        echo "Disallow: /dashboard\n";
        echo "Disallow: /project/\n";
        echo "Disallow: /task/\n";
        echo "Disallow: /my-tasks\n";
        echo "Disallow: /settings\n";
        echo "Disallow: /ext/\n";
        echo "Disallow: /invite/\n";
        echo "Disallow: /login\n";
        echo "Disallow: /register\n";
        echo "Disallow: /reset-password\n";
        echo "Disallow: /forgot-password\n";
        echo "Disallow: /verify-email\n";
        echo "Disallow: /verify-email-change\n";
        echo "Disallow: /install\n";
        echo "\nSitemap: " . $base . "/sitemap.xml\n";
        exit;
    },
    '/sitemap.xml'           => function() {
        $scheme = (!empty($_SERVER['HTTPS']) && $_SERVER['HTTPS'] !== 'off')
               || (($_SERVER['HTTP_X_FORWARDED_PROTO'] ?? '') === 'https') ? 'https' : 'http';
        $base   = $scheme . '://' . ($_SERVER['HTTP_HOST'] ?? 'localhost') . APP_BASE;
        $today  = date('Y-m-d');
        $pages  = [
            ['loc' => '/',          'changefreq' => 'weekly',  'priority' => '1.0'],
            ['loc' => '/downloads', 'changefreq' => 'weekly',  'priority' => '0.9'],
        ];
        header('Content-Type: application/xml; charset=utf-8');
        header('Cache-Control: public, max-age=3600');
        echo '<?xml version="1.0" encoding="UTF-8"?>' . "\n";
        echo '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9" xmlns:xhtml="http://www.w3.org/1999/xhtml">' . "\n";
        foreach ($pages as $p) {
            echo "  <url>\n";
            echo "    <loc>" . htmlspecialchars($base . $p['loc']) . "</loc>\n";
            echo "    <lastmod>" . $today . "</lastmod>\n";
            echo "    <changefreq>" . $p['changefreq'] . "</changefreq>\n";
            echo "    <priority>" . $p['priority'] . "</priority>\n";
            echo "    <xhtml:link rel=\"alternate\" hreflang=\"de\" href=\"" . htmlspecialchars($base . $p['loc']) . "\"/>\n";
            echo "    <xhtml:link rel=\"alternate\" hreflang=\"en\" href=\"" . htmlspecialchars($base . $p['loc']) . "\"/>\n";
            echo "    <xhtml:link rel=\"alternate\" hreflang=\"x-default\" href=\"" . htmlspecialchars($base . $p['loc']) . "\"/>\n";
            echo "  </url>\n";
        }
        echo '</urlset>' . "\n";
        exit;
    },
    '/contact'               => fn() => ContactController::submit(),
    '/LICENSE'               => function () { header('Content-Type: text/plain; charset=utf-8'); readfile(BASE_PATH . '/LICENSE'); exit; },
    '/login'                 => fn() => AuthController::login(),
    '/logout'                => fn() => AuthController::logout(),
    '/install'               => fn() => AuthController::install(),
    '/dashboard'             => fn() => DashboardController::index(),
    '/my-tasks'              => fn() => MyTasksController::index(),
    '/projects'              => fn() => ProjectController::index(),
    '/project/create'        => fn() => ProjectController::create(),
    '/settings'              => fn() => SettingsController::profile(),
    '/settings/totp'              => fn() => SettingsController::totp(),
    '/webauthn/register-challenge'=> fn() => WebAuthnController::registerChallenge(),
    '/webauthn/register-verify'   => fn() => WebAuthnController::registerVerify(),
    '/webauthn/login-challenge'   => fn() => WebAuthnController::loginChallenge(),
    '/webauthn/login-verify'      => fn() => WebAuthnController::loginVerify(),
    '/webauthn/delete'            => fn() => WebAuthnController::delete(),
    '/settings/preferences'  => fn() => SettingsController::preferences(),
    '/settings/notifications'=> fn() => SettingsController::notifications(),
    '/totp-verify'           => fn() => AuthController::totpVerify(),
    '/totp-setup-forced'     => fn() => AuthController::totpSetupForced(),
    '/mfa-choice'            => fn() => AuthController::mfaChoice(),
    '/mfa-setup'             => fn() => AuthController::mfaSetup(),
    '/email-mfa-verify'      => fn() => AuthController::emailMfaVerify(),
    '/admin/update'          => fn() => Helper::redirect('/admin/settings'),
    '/admin/update/run'     => fn() => AdminController::updateRun(),
    '/admin/update/perform' => fn() => AdminController::updatePerform(),
    '/update/manifest'       => function() {
        $file = BASE_PATH . '/downloads/version.json';
        if (!file_exists($file)) { http_response_code(404); exit; }
        header('Content-Type: application/json');
        header('Cache-Control: no-store');
        readfile($file);
        exit;
    },
    '/admin/users'           => fn() => AdminController::users(),
    '/admin/settings'        => fn() => AdminController::settings(),
    '/admin/backup/download'    => fn() => AdminController::backupDownload(),
    '/admin/backup/redownload'  => fn() => AdminController::backupRedownload(),
    '/admin/backup/delete'      => fn() => AdminController::backupDeletePersisted(),
    '/admin/backup/restore'     => fn() => AdminController::backupRestore(),
    '/admin/plugins'         => fn() => AdminController::plugins(),
    '/admin/ai-models'       => fn() => AdminController::aiModels(),
    '/admin/health-check'    => fn() => AdminController::healthCheck(),
    '/admin/security-log'    => fn() => AdminController::securityLog(),
    '/help'                  => fn() => HelpController::index(),
    '/help/group-admin'      => fn() => HelpController::groupAdmin(),
    '/help/project-manager'  => fn() => HelpController::projectManager(),
    '/help/task-worker'      => fn() => HelpController::taskWorker(),
    '/help/external'         => fn() => HelpController::external(),
    '/help/notifications'    => fn() => HelpController::notifications(),

    // Dynamic – matched via regex below
];

// Merge plugin routes
$routes = array_merge($routes, PluginManager::getRoutes());

// Try static routes first
if (isset($routes[$uri])) {
    $routes[$uri]();
    exit;
}

// Dynamic routes
$dynamicRoutes = [
    '#^/project/(\d+)$#'           => fn($m) => ProjectController::show((int)$m[1]),
    '#^/project/(\d+)/kanban$#'     => fn($m) => ProjectController::show((int)$m[1]),
    '#^/project/(\d+)/list$#'       => fn($m) => ProjectController::show((int)$m[1]),
    '#^/project/(\d+)/gantt$#'      => fn($m) => ProjectController::show((int)$m[1]),
    '#^/project/(\d+)/settings$#'   => fn($m) => ProjectController::settings((int)$m[1]),
    '#^/project/(\d+)/members$#'    => fn($m) => ProjectController::members((int)$m[1]),
    '#^/project/(\d+)/ai$#'         => fn($m) => AiController::chat((int)$m[1]),
    '#^/project/(\d+)/ai/send$#'    => fn($m) => AiController::send((int)$m[1]),
    '#^/project/(\d+)/ai/clear$#'   => fn($m) => AiController::clearHistory((int)$m[1]),
    '#^/project/(\d+)/ai/tasks$#'   => fn($m) => AiController::createTasks((int)$m[1]),
    '#^/task/create/(\d+)$#'        => fn($m) => TaskController::create((int)$m[1]),
    '#^/task/(\d+)$#'               => fn($m) => TaskController::show((int)$m[1]),
    '#^/task/(\d+)/edit$#'          => fn($m) => TaskController::edit((int)$m[1]),
    '#^/task/(\d+)/comment$#'       => fn($m) => TaskController::comment((int)$m[1]),
    '#^/attachment/(\d+)/download$#'=> fn($m) => TaskController::download((int)$m[1]),
    '#^/attachment/(\d+)/delete$#'  => fn($m) => TaskController::deleteAttachment((int)$m[1]),
    '#^/users/(\d+)/managed-tasks$#'    => fn($m) => ManagedTasksController::forUser((int)$m[1]),
    '#^/externals/(\d+)/managed-tasks$#'=> fn($m) => ManagedTasksController::forExternal((int)$m[1]),
    '#^/api/(.+)$#'                 => fn($m) => ApiController::handle($m[1]),
];

$dynamicRoutes = array_merge($dynamicRoutes, PluginManager::getDynamicRoutes());

foreach ($dynamicRoutes as $pattern => $handler) {
    if (preg_match($pattern, $uri, $matches)) {
        $handler($matches);
        exit;
    }
}


// 404
http_response_code(404);
view('errors/404', [], 'base');
