database.php

<?php

$dataDir = dirname(DB_PATH);
if (!is_dir($dataDir)) {
    mkdir($dataDir, 0755, true);
}

$isNew = !file_exists(DB_PATH);

$db = new PDO('sqlite:' . DB_PATH, null, null, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
]);

$db->exec('PRAGMA journal_mode=WAL');
$db->exec('PRAGMA foreign_keys=ON');

if ($isNew) {
    $db->exec("
        CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL,
            created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now'))
        );

        CREATE TABLE progress (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL,
            document TEXT NOT NULL,
            percentage REAL NOT NULL,
            progress TEXT NOT NULL,
            device TEXT NOT NULL,
            device_id TEXT NOT NULL DEFAULT '',
            metadata TEXT DEFAULT NULL,
            timestamp INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
            created_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
            updated_at INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
            UNIQUE(username, document)
        );

        CREATE INDEX idx_progress_username ON progress(username);
        CREATE INDEX idx_progress_document ON progress(document);
    ");
}

$version = $db->query("SELECT COUNT(*) FROM pragma_table_info('users') WHERE name = 'username'")->fetchColumn();
if ($version > 0) {
    $mixed = $db->query("SELECT COUNT(*) FROM users WHERE username != LOWER(username)")->fetchColumn();
    if ($mixed > 0) {
        $db->exec("
            UPDATE OR IGNORE progress SET username = LOWER(username);
            UPDATE OR IGNORE users SET username = LOWER(username);
            DELETE FROM users WHERE username != LOWER(username);
        ");
    }
}