nexus

includes/db.php

<?php
if (!defined('NEXUS')) exit('Forbidden');

/**
 * Database abstraction — supports SQLite3, MySQL, and MariaDB.
 * Driver is selected from DB_DRIVER constant (set by installer or config.php).
 *
 *   SQLite:  DB_DRIVER='sqlite'  (default, no credentials needed)
 *   MySQL:   DB_DRIVER='mysql'   + DB_HOST, DB_NAME, DB_USER, DB_PASS, DB_PORT
 *   MariaDB: DB_DRIVER='mysql'   (same driver as MySQL via PDO)
 */
class DB {
    private static ?PDO $pdo = null;

    /* ── Connect ──────────────────────────────────────────────── */
    public static function connect(): PDO {
        if (self::$pdo !== null) return self::$pdo;

        $driver = defined('DB_DRIVER') ? DB_DRIVER : 'sqlite';

        if ($driver === 'mysql') {
            $host    = defined('DB_HOST') ? DB_HOST : '127.0.0.1';
            $port    = defined('DB_PORT') ? DB_PORT : '3306';
            $name    = defined('DB_NAME') ? DB_NAME : 'nexus';
            $user    = defined('DB_USER') ? DB_USER : 'root';
            $pass    = defined('DB_PASS') ? DB_PASS : '';
            $charset = defined('DB_CHARSET') ? DB_CHARSET : 'utf8mb4';

            $dsn = "mysql:host={$host};port={$port};dbname={$name};charset={$charset}";
            self::$pdo = new PDO($dsn, $user, $pass, [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES   => false,
                PDO::MYSQL_ATTR_FOUND_ROWS   => true,
            ]);
            self::$pdo->exec("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'");
        } else {
            // SQLite
            $path = DATA . '/forum.db';
            if (!is_dir(DATA)) mkdir(DATA, 0750, true);
            self::$pdo = new PDO('sqlite:' . $path, null, null, [
                PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            ]);
            self::$pdo->exec('PRAGMA foreign_keys = ON');
            self::$pdo->exec('PRAGMA journal_mode = WAL');
            self::$pdo->exec('PRAGMA synchronous = NORMAL');
            self::$pdo->exec('PRAGMA temp_store = MEMORY');
            self::$pdo->exec('PRAGMA mmap_size = 268435456');
        }

        return self::$pdo;
    }

    public static function driver(): string {
        return defined('DB_DRIVER') ? DB_DRIVER : 'sqlite';
    }

    public static function isMysql(): bool {
        return self::driver() === 'mysql';
    }

    /* ── Query helpers ────────────────────────────────────────── */
    public static function run(string $sql, array $p = []): PDOStatement {
        $s = self::connect()->prepare($sql);
        $s->execute($p);
        return $s;
    }

    public static function row(string $sql, array $p = []): ?array {
        $r = self::run($sql, $p)->fetch();
        return $r ?: null;
    }

    public static function rows(string $sql, array $p = []): array {
        return self::run($sql, $p)->fetchAll();
    }

    public static function insert(string $sql, array $p = []): int {
        self::run($sql, $p);
        return (int) self::connect()->lastInsertId();
    }

    public static function val(string $sql, array $p = []): mixed {
        $row = self::run($sql, $p)->fetch(PDO::FETCH_NUM);
        return $row ? $row[0] : null;
    }

    /**
     * Cross-driver INSERT IGNORE.
     * Silently skips if the row already exists (duplicate key).
     */
    public static function insertIgnore(string $table, array $cols, array $vals): void {
        $placeholders = implode(',', array_fill(0, count($vals), '?'));
        $colList      = implode(',', array_map(fn($c) => "`$c`", $cols));
        if (self::isMysql()) {
            self::run("INSERT IGNORE INTO `{$table}` ({$colList}) VALUES ({$placeholders})", $vals);
        } else {
            self::run("INSERT OR IGNORE INTO {$table} (" . implode(',', $cols) . ") VALUES ({$placeholders})", $vals);
        }
    }

    /**
     * Cross-driver upsert (INSERT ... ON DUPLICATE KEY UPDATE for MySQL,
     * INSERT OR REPLACE for SQLite).
     * Only works for simple single-column key tables like settings(key,value).
     */
    public static function upsert(string $table, string $keyCol, string $valCol, string $key, string $val): void {
        if (self::isMysql()) {
            self::run(
                "INSERT INTO `{$table}` (`{$keyCol}`,`{$valCol}`) VALUES (?,?) ON DUPLICATE KEY UPDATE `{$valCol}`=VALUES(`{$valCol}`)",
                [$key, $val]
            );
        } else {
            self::run(
                "INSERT OR REPLACE INTO {$table} ({$keyCol},{$valCol}) VALUES (?,?)",
                [$key, $val]
            );
        }
    }

    /* ── Schema: cross-driver table creation ─────────────────── */
    public static function init(): void {
        $mysql = self::isMysql();

        /* Helper: datetime default compatible with both drivers */
        $now   = $mysql ? "DEFAULT CURRENT_TIMESTAMP" : "DEFAULT (datetime('now'))";
        $auto  = $mysql ? "INT AUTO_INCREMENT"   : "INTEGER PRIMARY KEY AUTOINCREMENT";
        $pk    = $mysql ? "PRIMARY KEY"          : "";
        $eng   = $mysql ? "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci" : "";

        $tables = [];

        /* settings */
        $tables[] = "CREATE TABLE IF NOT EXISTS settings (
            `key`   VARCHAR(100) PRIMARY KEY,
            `value` TEXT NOT NULL
        ) $eng";

        /* users */
        if ($mysql) {
            $tables[] = "CREATE TABLE IF NOT EXISTS users (
                id             INT AUTO_INCREMENT PRIMARY KEY,
                username       VARCHAR(30)  NOT NULL,
                email          VARCHAR(254) NOT NULL,
                password       VARCHAR(255) NOT NULL,
                avatar         TEXT,
                bio            TEXT,
                role           VARCHAR(20)  NOT NULL DEFAULT 'member',
                permissions    TEXT         NOT NULL DEFAULT '{}',
                post_count     INT          NOT NULL DEFAULT 0,
                topic_count    INT          NOT NULL DEFAULT 0,
                karma          INT          NOT NULL DEFAULT 0,
                suspended      TINYINT      NOT NULL DEFAULT 0,
                silenced       TINYINT      NOT NULL DEFAULT 0,
                location       VARCHAR(100) NOT NULL DEFAULT '',
                friends_hidden TINYINT      NOT NULL DEFAULT 0,
                joined_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
                last_seen      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uq_username (username),
                UNIQUE KEY uq_email (email)
            ) $eng";
        } else {
            $tables[] = "CREATE TABLE IF NOT EXISTS users (
                id             INTEGER PRIMARY KEY AUTOINCREMENT,
                username       TEXT    NOT NULL UNIQUE,
                email          TEXT    NOT NULL UNIQUE,
                password       TEXT    NOT NULL,
                avatar         TEXT,
                bio            TEXT,
                role           TEXT    NOT NULL DEFAULT 'member',
                permissions    TEXT    NOT NULL DEFAULT '{}',
                post_count     INTEGER NOT NULL DEFAULT 0,
                topic_count    INTEGER NOT NULL DEFAULT 0,
                karma          INTEGER NOT NULL DEFAULT 0,
                suspended      INTEGER NOT NULL DEFAULT 0,
                silenced       INTEGER NOT NULL DEFAULT 0,
                location       TEXT    NOT NULL DEFAULT '',
                friends_hidden INTEGER NOT NULL DEFAULT 0,
                joined_at      TEXT    NOT NULL DEFAULT (datetime('now')),
                last_seen      TEXT    NOT NULL DEFAULT (datetime('now'))
            )";
        }

        /* categories */
        if ($mysql) {
            $tables[] = "CREATE TABLE IF NOT EXISTS categories (
                id          INT AUTO_INCREMENT PRIMARY KEY,
                name        VARCHAR(100) NOT NULL,
                slug        VARCHAR(100) NOT NULL,
                description TEXT         NOT NULL DEFAULT '',
                color       VARCHAR(20)  NOT NULL DEFAULT '#3b82f6',
                icon        VARCHAR(10)  NOT NULL DEFAULT '💬',
                position    INT          NOT NULL DEFAULT 0,
                parent_id   INT,
                topic_count INT          NOT NULL DEFAULT 0,
                post_count  INT          NOT NULL DEFAULT 0,
                read_role   VARCHAR(20)  NOT NULL DEFAULT 'guest',
                post_role   VARCHAR(20)  NOT NULL DEFAULT 'member',
                reply_role  VARCHAR(20)  NOT NULL DEFAULT 'member',
                UNIQUE KEY uq_slug (slug)
            ) $eng";
        } else {
            $tables[] = "CREATE TABLE IF NOT EXISTS categories (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                name        TEXT    NOT NULL,
                slug        TEXT    NOT NULL UNIQUE,
                description TEXT    NOT NULL DEFAULT '',
                color       TEXT    NOT NULL DEFAULT '#3b82f6',
                icon        TEXT    NOT NULL DEFAULT '💬',
                position    INTEGER NOT NULL DEFAULT 0,
                parent_id   INTEGER,
                topic_count INTEGER NOT NULL DEFAULT 0,
                post_count  INTEGER NOT NULL DEFAULT 0,
                read_role   TEXT    NOT NULL DEFAULT 'guest',
                post_role   TEXT    NOT NULL DEFAULT 'member',
                reply_role  TEXT    NOT NULL DEFAULT 'member'
            )";
        }

        /* topics */
        if ($mysql) {
            $tables[] = "CREATE TABLE IF NOT EXISTS topics (
                id           INT AUTO_INCREMENT PRIMARY KEY,
                title        VARCHAR(200) NOT NULL,
                slug         VARCHAR(220) NOT NULL,
                category_id  INT          NOT NULL,
                user_id      INT          NOT NULL,
                views        INT          NOT NULL DEFAULT 0,
                reply_count  INT          NOT NULL DEFAULT 0,
                pinned       TINYINT      NOT NULL DEFAULT 0,
                closed       TINYINT      NOT NULL DEFAULT 0,
                archived     TINYINT      NOT NULL DEFAULT 0,
                last_post_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
                created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uq_slug (slug),
                KEY ix_cat (category_id),
                KEY ix_user (user_id)
            ) $eng";
        } else {
            $tables[] = "CREATE TABLE IF NOT EXISTS topics (
                id           INTEGER PRIMARY KEY AUTOINCREMENT,
                title        TEXT    NOT NULL,
                slug         TEXT    NOT NULL UNIQUE,
                category_id  INTEGER NOT NULL,
                user_id      INTEGER NOT NULL,
                views        INTEGER NOT NULL DEFAULT 0,
                reply_count  INTEGER NOT NULL DEFAULT 0,
                pinned       INTEGER NOT NULL DEFAULT 0,
                closed       INTEGER NOT NULL DEFAULT 0,
                archived     INTEGER NOT NULL DEFAULT 0,
                last_post_at TEXT    NOT NULL DEFAULT (datetime('now')),
                created_at   TEXT    NOT NULL DEFAULT (datetime('now'))
            )";
        }

        /* posts */
        if ($mysql) {
            $tables[] = "CREATE TABLE IF NOT EXISTS posts (
                id          INT AUTO_INCREMENT PRIMARY KEY,
                topic_id    INT      NOT NULL,
                user_id     INT      NOT NULL,
                content     TEXT     NOT NULL,
                post_num    INT      NOT NULL,
                reply_to    INT,
                likes       INT      NOT NULL DEFAULT 0,
                edited      TINYINT  NOT NULL DEFAULT 0,
                edit_reason VARCHAR(200),
                deleted     TINYINT  NOT NULL DEFAULT 0,
                created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                KEY ix_topic (topic_id),
                KEY ix_user  (user_id)
            ) $eng";
        } else {
            $tables[] = "CREATE TABLE IF NOT EXISTS posts (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                topic_id    INTEGER NOT NULL,
                user_id     INTEGER NOT NULL,
                content     TEXT    NOT NULL,
                post_num    INTEGER NOT NULL,
                reply_to    INTEGER,
                likes       INTEGER NOT NULL DEFAULT 0,
                edited      INTEGER NOT NULL DEFAULT 0,
                edit_reason TEXT,
                deleted     INTEGER NOT NULL DEFAULT 0,
                created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
                updated_at  TEXT    NOT NULL DEFAULT (datetime('now'))
            )";
        }

        /* likes */
        if ($mysql) {
            $tables[] = "CREATE TABLE IF NOT EXISTS likes (
                post_id INT NOT NULL,
                user_id INT NOT NULL,
                PRIMARY KEY (post_id, user_id)
            ) $eng";
        } else {
            $tables[] = "CREATE TABLE IF NOT EXISTS likes (
                post_id INTEGER NOT NULL,
                user_id INTEGER NOT NULL,
                PRIMARY KEY (post_id, user_id)
            )";
        }

        /* tags + topic_tags */
        if ($mysql) {
            $tables[] = "CREATE TABLE IF NOT EXISTS tags (
                id          INT AUTO_INCREMENT PRIMARY KEY,
                name        VARCHAR(50) NOT NULL,
                topic_count INT         NOT NULL DEFAULT 0,
                UNIQUE KEY uq_name (name)
            ) $eng";
            $tables[] = "CREATE TABLE IF NOT EXISTS topic_tags (
                topic_id INT NOT NULL,
                tag_id   INT NOT NULL,
                PRIMARY KEY (topic_id, tag_id)
            ) $eng";
        } else {
            $tables[] = "CREATE TABLE IF NOT EXISTS tags (
                id          INTEGER PRIMARY KEY AUTOINCREMENT,
                name        TEXT    NOT NULL UNIQUE,
                topic_count INTEGER NOT NULL DEFAULT 0
            )";
            $tables[] = "CREATE TABLE IF NOT EXISTS topic_tags (
                topic_id INTEGER NOT NULL,
                tag_id   INTEGER NOT NULL,
                PRIMARY KEY (topic_id, tag_id)
            )";
        }

        /* notifications */
        if ($mysql) {
            $tables[] = "CREATE TABLE IF NOT EXISTS notifications (
                id         INT AUTO_INCREMENT PRIMARY KEY,
                user_id    INT         NOT NULL,
                type       VARCHAR(50) NOT NULL,
                payload    TEXT        NOT NULL,
                `read`     TINYINT     NOT NULL DEFAULT 0,
                created_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
                KEY ix_user (user_id)
            ) $eng";
        } else {
            $tables[] = "CREATE TABLE IF NOT EXISTS notifications (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id    INTEGER NOT NULL,
                type       TEXT    NOT NULL,
                payload    TEXT    NOT NULL DEFAULT '{}',
                read       INTEGER NOT NULL DEFAULT 0,
                created_at TEXT    NOT NULL DEFAULT (datetime('now'))
            )";
        }

        /* friends */
        if ($mysql) {
            $tables[] = "CREATE TABLE IF NOT EXISTS friends (
                id         INT AUTO_INCREMENT PRIMARY KEY,
                user_id    INT         NOT NULL,
                friend_id  INT         NOT NULL,
                status     VARCHAR(20) NOT NULL DEFAULT 'pending',
                created_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uq_pair (user_id, friend_id),
                KEY ix_uid (user_id),
                KEY ix_fid (friend_id)
            ) $eng";
        } else {
            $tables[] = "CREATE TABLE IF NOT EXISTS friends (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id    INTEGER NOT NULL,
                friend_id  INTEGER NOT NULL,
                status     TEXT    NOT NULL DEFAULT 'pending',
                created_at TEXT    NOT NULL DEFAULT (datetime('now')),
                UNIQUE(user_id, friend_id)
            )";
        }

        /* messages */
        if ($mysql) {
            $tables[] = "CREATE TABLE IF NOT EXISTS messages (
                id                  INT AUTO_INCREMENT PRIMARY KEY,
                sender_id           INT          NOT NULL,
                receiver_id         INT          NOT NULL,
                subject             VARCHAR(150) NOT NULL DEFAULT '',
                body                TEXT         NOT NULL,
                is_read             TINYINT      NOT NULL DEFAULT 0,
                deleted_by_sender   TINYINT      NOT NULL DEFAULT 0,
                deleted_by_receiver TINYINT      NOT NULL DEFAULT 0,
                created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
                KEY ix_recv (receiver_id),
                KEY ix_send (sender_id)
            ) $eng";
        } else {
            $tables[] = "CREATE TABLE IF NOT EXISTS messages (
                id                  INTEGER PRIMARY KEY AUTOINCREMENT,
                sender_id           INTEGER NOT NULL,
                receiver_id         INTEGER NOT NULL,
                subject             TEXT    NOT NULL DEFAULT '',
                body                TEXT    NOT NULL,
                is_read             INTEGER NOT NULL DEFAULT 0,
                deleted_by_sender   INTEGER NOT NULL DEFAULT 0,
                deleted_by_receiver INTEGER NOT NULL DEFAULT 0,
                created_at          TEXT    NOT NULL DEFAULT (datetime('now'))
            )";
        }

        /* rate_events */
        if ($mysql) {
            $tables[] = "CREATE TABLE IF NOT EXISTS rate_events (
                id         INT AUTO_INCREMENT PRIMARY KEY,
                user_id    INT         NOT NULL,
                event_type VARCHAR(20) NOT NULL DEFAULT 'post',
                created_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
                KEY ix_rate (user_id, event_type, created_at)
            ) $eng";
        } else {
            $tables[] = "CREATE TABLE IF NOT EXISTS rate_events (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id    INTEGER NOT NULL,
                event_type TEXT    NOT NULL DEFAULT 'post',
                created_at TEXT    NOT NULL DEFAULT (datetime('now'))
            )";
        }

        /* themes */
        if ($mysql) {
            $tables[] = "CREATE TABLE IF NOT EXISTS themes (
                id         INT AUTO_INCREMENT PRIMARY KEY,
                name       VARCHAR(100) NOT NULL,
                slug       VARCHAR(100) NOT NULL,
                css        LONGTEXT     NOT NULL DEFAULT '',
                is_active  TINYINT      NOT NULL DEFAULT 0,
                created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uq_slug (slug)
            ) $eng";
        } else {
            $tables[] = "CREATE TABLE IF NOT EXISTS themes (
                id         INTEGER PRIMARY KEY AUTOINCREMENT,
                name       TEXT    NOT NULL,
                slug       TEXT    NOT NULL UNIQUE,
                css        TEXT    NOT NULL DEFAULT '',
                is_active  INTEGER NOT NULL DEFAULT 0,
                created_at TEXT    NOT NULL DEFAULT (datetime('now'))
            )";
        }

        $pdo = self::connect();
        foreach ($tables as $sql) {
            $pdo->exec($sql);
        }

        /* SQLite-only indexes */
        if (!$mysql) {
            $pdo->exec("CREATE INDEX IF NOT EXISTS ix_topics_cat  ON topics(category_id)");
            $pdo->exec("CREATE INDEX IF NOT EXISTS ix_posts_topic ON posts(topic_id)");
            $pdo->exec("CREATE INDEX IF NOT EXISTS ix_notif_user  ON notifications(user_id)");
            $pdo->exec("CREATE INDEX IF NOT EXISTS ix_rate_user   ON rate_events(user_id, event_type, created_at)");
            $pdo->exec("CREATE INDEX IF NOT EXISTS ix_friends_u   ON friends(user_id)");
            $pdo->exec("CREATE INDEX IF NOT EXISTS ix_msg_recv    ON messages(receiver_id)");
        }

        /* ── Column migrations (safe for both SQLite + MySQL upgrades) ── */
        // Add columns that were introduced in later versions.
        // Uses IF NOT EXISTS logic compatible with both drivers.
        if ($mysql) {
            // MySQL: check information_schema then ALTER TABLE if column missing
            $dbName = defined('DB_NAME') ? DB_NAME : '';
            $existingCols = array_column(self::rows(
                "SELECT COLUMN_NAME FROM information_schema.COLUMNS
                 WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'users'",
                [$dbName]
            ), 'COLUMN_NAME');
            $mysqlAdd = [
                'karma'          => "ALTER TABLE users ADD COLUMN karma INT NOT NULL DEFAULT 0",
                'permissions'    => "ALTER TABLE users ADD COLUMN permissions TEXT NOT NULL DEFAULT '{}'",
                'friends_hidden' => "ALTER TABLE users ADD COLUMN friends_hidden TINYINT NOT NULL DEFAULT 0",
                'suspended'      => "ALTER TABLE users ADD COLUMN suspended TINYINT NOT NULL DEFAULT 0",
                'silenced'       => "ALTER TABLE users ADD COLUMN silenced TINYINT NOT NULL DEFAULT 0",
                'topic_count'    => "ALTER TABLE users ADD COLUMN topic_count INT NOT NULL DEFAULT 0",
            ];
            foreach ($mysqlAdd as $col => $sql) {
                if (!in_array($col, $existingCols)) {
                    try { self::run($sql); } catch (\Throwable $e) { /* already exists */ }
                }
            }
        } else {
            // SQLite: use PRAGMA table_info
            $cols = array_column(self::rows("PRAGMA table_info(users)"), 'name');
            $sqliteAdd = [
                'karma'          => "ALTER TABLE users ADD COLUMN karma INTEGER NOT NULL DEFAULT 0",
                'permissions'    => "ALTER TABLE users ADD COLUMN permissions TEXT NOT NULL DEFAULT '{}'",
                'friends_hidden' => "ALTER TABLE users ADD COLUMN friends_hidden INTEGER NOT NULL DEFAULT 0",
                'suspended'      => "ALTER TABLE users ADD COLUMN suspended INTEGER NOT NULL DEFAULT 0",
                'silenced'       => "ALTER TABLE users ADD COLUMN silenced INTEGER NOT NULL DEFAULT 0",
                'topic_count'    => "ALTER TABLE users ADD COLUMN topic_count INTEGER NOT NULL DEFAULT 0",
            ];
            foreach ($sqliteAdd as $col => $sql) {
                if (!in_array($col, $cols)) {
                    try { self::run($sql); } catch (\Throwable $e) { /* already exists */ }
                }
            }
        }

        /* ── Categories: role permission columns ── */
        if ($mysql) {
            $dbName = defined('DB_NAME') ? DB_NAME : '';
            $catCols = array_column(self::rows(
                "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME='categories'",
                [$dbName]
            ), 'COLUMN_NAME');
            foreach (['read_role'=>"VARCHAR(20) NOT NULL DEFAULT 'guest'",'post_role'=>"VARCHAR(20) NOT NULL DEFAULT 'member'",'reply_role'=>"VARCHAR(20) NOT NULL DEFAULT 'member'"] as $col=>$def) {
                if (!in_array($col,$catCols)) { try{self::run("ALTER TABLE categories ADD COLUMN $col $def");}catch(\Throwable $e){} }
            }
        } else {
            $catCols = array_column(self::rows("PRAGMA table_info(categories)"),'name');
            foreach (['read_role'=>"TEXT NOT NULL DEFAULT 'guest'",'post_role'=>"TEXT NOT NULL DEFAULT 'member'",'reply_role'=>"TEXT NOT NULL DEFAULT 'member'"] as $col=>$def) {
                if (!in_array($col,$catCols)) { try{self::run("ALTER TABLE categories ADD COLUMN $col $def");}catch(\Throwable $e){} }
            }
        }

        /* ── Users: add location column ── */
        if ($mysql) {
            $dbName = defined('DB_NAME') ? DB_NAME : '';
            $uCols = array_column(self::rows(
                "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME='users'",
                [$dbName]
            ), 'COLUMN_NAME');
            if (!in_array('location', $uCols)) {
                try { self::run("ALTER TABLE users ADD COLUMN location VARCHAR(100) NOT NULL DEFAULT ''"); }
                catch (\Throwable $e) {}
            }
        } else {
            $uCols2 = array_column(self::rows("PRAGMA table_info(users)"), 'name');
            if (!in_array('location', $uCols2)) {
                try { self::run("ALTER TABLE users ADD COLUMN location TEXT NOT NULL DEFAULT ''"); }
                catch (\Throwable $e) {}
            }
        }

        /* ── Messages: add conversation_id for chat threading ── */
        // conversation_id = LEAST(sender_id, receiver_id) * 1000000 + GREATEST(...)
        // We store it as a VARCHAR key for easy lookup
        if ($mysql) {
            $dbName = defined('DB_NAME') ? DB_NAME : '';
            $msgCols = array_column(self::rows(
                "SELECT COLUMN_NAME FROM information_schema.COLUMNS
                 WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'messages'", [$dbName]
            ), 'COLUMN_NAME');
            if (!in_array('conversation_id', $msgCols)) {
                try {
                    self::run("ALTER TABLE messages ADD COLUMN conversation_id VARCHAR(30) NOT NULL DEFAULT ''");
                    self::run("ALTER TABLE messages ADD INDEX ix_conv (conversation_id)");
                    // Back-fill existing rows
                    self::run("UPDATE messages SET conversation_id = CONCAT(LEAST(sender_id,receiver_id),'-',GREATEST(sender_id,receiver_id))");
                } catch (\Throwable $e) { /* ignore */ }
            }
        } else {
            $msgCols = array_column(self::rows("PRAGMA table_info(messages)"), 'name');
            if (!in_array('conversation_id', $msgCols)) {
                try {
                    self::run("ALTER TABLE messages ADD COLUMN conversation_id TEXT NOT NULL DEFAULT ''");
                    self::run("UPDATE messages SET conversation_id = MIN(sender_id,receiver_id)||'-'||MAX(sender_id,receiver_id)");
                } catch (\Throwable $e) { /* ignore */ }
            }
        }
    }


    /* ── Helpers ─────────────────────────────────────────────── */
    /** Cross-driver NOW() */
    public static function now(): string {
        return self::isMysql() ? 'NOW()' : "datetime('now')";
    }

    /** Cross-driver datetime comparison for rate limiting */
    public static function sinceSeconds(int $seconds): string {
        if (self::isMysql()) {
            return "DATE_SUB(NOW(), INTERVAL {$seconds} SECOND)";
        }
        return "datetime('now','-{$seconds} seconds')";
    }
}

1 <?php
2 if (!defined('NEXUS')) exit('Forbidden');
3 
4 /**
5  * Database abstraction — supports SQLite3, MySQL, and MariaDB.
6  * Driver is selected from DB_DRIVER constant (set by installer or config.php).
7  *
8  *   SQLite:  DB_DRIVER='sqlite'  (default, no credentials needed)
9  *   MySQL:   DB_DRIVER='mysql'   + DB_HOST, DB_NAME, DB_USER, DB_PASS, DB_PORT
10  *   MariaDB: DB_DRIVER='mysql'   (same driver as MySQL via PDO)
11  */
12 class DB {
13     private static ?PDO $pdo = null;
14 
15     /* ── Connect ──────────────────────────────────────────────── */
16     public static function connect(): PDO {
17         if (self::$pdo !== null) return self::$pdo;
18 
19         $driver = defined('DB_DRIVER') ? DB_DRIVER : 'sqlite';
20 
21         if ($driver === 'mysql') {
22             $host    = defined('DB_HOST') ? DB_HOST : '127.0.0.1';
23             $port    = defined('DB_PORT') ? DB_PORT : '3306';
24             $name    = defined('DB_NAME') ? DB_NAME : 'nexus';
25             $user    = defined('DB_USER') ? DB_USER : 'root';
26             $pass    = defined('DB_PASS') ? DB_PASS : '';
27             $charset = defined('DB_CHARSET') ? DB_CHARSET : 'utf8mb4';
28 
29             $dsn = "mysql:host={$host};port={$port};dbname={$name};charset={$charset}";
30             self::$pdo = new PDO($dsn, $user, $pass, [
31                 PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
32                 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
33                 PDO::ATTR_EMULATE_PREPARES   => false,
34                 PDO::MYSQL_ATTR_FOUND_ROWS   => true,
35             ]);
36             self::$pdo->exec("SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'");
37         } else {
38             // SQLite
39             $path = DATA . '/forum.db';
40             if (!is_dir(DATA)) mkdir(DATA, 0750, true);
41             self::$pdo = new PDO('sqlite:' . $path, null, null, [
42                 PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
43                 PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
44             ]);
45             self::$pdo->exec('PRAGMA foreign_keys = ON');
46             self::$pdo->exec('PRAGMA journal_mode = WAL');
47             self::$pdo->exec('PRAGMA synchronous = NORMAL');
48             self::$pdo->exec('PRAGMA temp_store = MEMORY');
49             self::$pdo->exec('PRAGMA mmap_size = 268435456');
50         }
51 
52         return self::$pdo;
53     }
54 
55     public static function driver(): string {
56         return defined('DB_DRIVER') ? DB_DRIVER : 'sqlite';
57     }
58 
59     public static function isMysql(): bool {
60         return self::driver() === 'mysql';
61     }
62 
63     /* ── Query helpers ────────────────────────────────────────── */
64     public static function run(string $sql, array $p = []): PDOStatement {
65         $s = self::connect()->prepare($sql);
66         $s->execute($p);
67         return $s;
68     }
69 
70     public static function row(string $sql, array $p = []): ?array {
71         $r = self::run($sql, $p)->fetch();
72         return $r ?: null;
73     }
74 
75     public static function rows(string $sql, array $p = []): array {
76         return self::run($sql, $p)->fetchAll();
77     }
78 
79     public static function insert(string $sql, array $p = []): int {
80         self::run($sql, $p);
81         return (int) self::connect()->lastInsertId();
82     }
83 
84     public static function val(string $sql, array $p = []): mixed {
85         $row = self::run($sql, $p)->fetch(PDO::FETCH_NUM);
86         return $row ? $row[0] : null;
87     }
88 
89     /**
90      * Cross-driver INSERT IGNORE.
91      * Silently skips if the row already exists (duplicate key).
92      */
93     public static function insertIgnore(string $table, array $cols, array $vals): void {
94         $placeholders = implode(',', array_fill(0, count($vals), '?'));
95         $colList      = implode(',', array_map(fn($c) => "`$c`", $cols));
96         if (self::isMysql()) {
97             self::run("INSERT IGNORE INTO `{$table}` ({$colList}) VALUES ({$placeholders})", $vals);
98         } else {
99             self::run("INSERT OR IGNORE INTO {$table} (" . implode(',', $cols) . ") VALUES ({$placeholders})", $vals);
100         }
101     }
102 
103     /**
104      * Cross-driver upsert (INSERT ... ON DUPLICATE KEY UPDATE for MySQL,
105      * INSERT OR REPLACE for SQLite).
106      * Only works for simple single-column key tables like settings(key,value).
107      */
108     public static function upsert(string $table, string $keyCol, string $valCol, string $key, string $val): void {
109         if (self::isMysql()) {
110             self::run(
111                 "INSERT INTO `{$table}` (`{$keyCol}`,`{$valCol}`) VALUES (?,?) ON DUPLICATE KEY UPDATE `{$valCol}`=VALUES(`{$valCol}`)",
112                 [$key, $val]
113             );
114         } else {
115             self::run(
116                 "INSERT OR REPLACE INTO {$table} ({$keyCol},{$valCol}) VALUES (?,?)",
117                 [$key, $val]
118             );
119         }
120     }
121 
122     /* ── Schema: cross-driver table creation ─────────────────── */
123     public static function init(): void {
124         $mysql = self::isMysql();
125 
126         /* Helper: datetime default compatible with both drivers */
127         $now   = $mysql ? "DEFAULT CURRENT_TIMESTAMP" : "DEFAULT (datetime('now'))";
128         $auto  = $mysql ? "INT AUTO_INCREMENT"   : "INTEGER PRIMARY KEY AUTOINCREMENT";
129         $pk    = $mysql ? "PRIMARY KEY"          : "";
130         $eng   = $mysql ? "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci" : "";
131 
132         $tables = [];
133 
134         /* settings */
135         $tables[] = "CREATE TABLE IF NOT EXISTS settings (
136             `key`   VARCHAR(100) PRIMARY KEY,
137             `value` TEXT NOT NULL
138         ) $eng";
139 
140         /* users */
141         if ($mysql) {
142             $tables[] = "CREATE TABLE IF NOT EXISTS users (
143                 id             INT AUTO_INCREMENT PRIMARY KEY,
144                 username       VARCHAR(30)  NOT NULL,
145                 email          VARCHAR(254) NOT NULL,
146                 password       VARCHAR(255) NOT NULL,
147                 avatar         TEXT,
148                 bio            TEXT,
149                 role           VARCHAR(20)  NOT NULL DEFAULT 'member',
150                 permissions    TEXT         NOT NULL DEFAULT '{}',
151                 post_count     INT          NOT NULL DEFAULT 0,
152                 topic_count    INT          NOT NULL DEFAULT 0,
153                 karma          INT          NOT NULL DEFAULT 0,
154                 suspended      TINYINT      NOT NULL DEFAULT 0,
155                 silenced       TINYINT      NOT NULL DEFAULT 0,
156                 location       VARCHAR(100) NOT NULL DEFAULT '',
157                 friends_hidden TINYINT      NOT NULL DEFAULT 0,
158                 joined_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
159                 last_seen      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
160                 UNIQUE KEY uq_username (username),
161                 UNIQUE KEY uq_email (email)
162             ) $eng";
163         } else {
164             $tables[] = "CREATE TABLE IF NOT EXISTS users (
165                 id             INTEGER PRIMARY KEY AUTOINCREMENT,
166                 username       TEXT    NOT NULL UNIQUE,
167                 email          TEXT    NOT NULL UNIQUE,
168                 password       TEXT    NOT NULL,
169                 avatar         TEXT,
170                 bio            TEXT,
171                 role           TEXT    NOT NULL DEFAULT 'member',
172                 permissions    TEXT    NOT NULL DEFAULT '{}',
173                 post_count     INTEGER NOT NULL DEFAULT 0,
174                 topic_count    INTEGER NOT NULL DEFAULT 0,
175                 karma          INTEGER NOT NULL DEFAULT 0,
176                 suspended      INTEGER NOT NULL DEFAULT 0,
177                 silenced       INTEGER NOT NULL DEFAULT 0,
178                 location       TEXT    NOT NULL DEFAULT '',
179                 friends_hidden INTEGER NOT NULL DEFAULT 0,
180                 joined_at      TEXT    NOT NULL DEFAULT (datetime('now')),
181                 last_seen      TEXT    NOT NULL DEFAULT (datetime('now'))
182             )";
183         }
184 
185         /* categories */
186         if ($mysql) {
187             $tables[] = "CREATE TABLE IF NOT EXISTS categories (
188                 id          INT AUTO_INCREMENT PRIMARY KEY,
189                 name        VARCHAR(100) NOT NULL,
190                 slug        VARCHAR(100) NOT NULL,
191                 description TEXT         NOT NULL DEFAULT '',
192                 color       VARCHAR(20)  NOT NULL DEFAULT '#3b82f6',
193                 icon        VARCHAR(10)  NOT NULL DEFAULT '💬',
194                 position    INT          NOT NULL DEFAULT 0,
195                 parent_id   INT,
196                 topic_count INT          NOT NULL DEFAULT 0,
197                 post_count  INT          NOT NULL DEFAULT 0,
198                 read_role   VARCHAR(20)  NOT NULL DEFAULT 'guest',
199                 post_role   VARCHAR(20)  NOT NULL DEFAULT 'member',
200                 reply_role  VARCHAR(20)  NOT NULL DEFAULT 'member',
201                 UNIQUE KEY uq_slug (slug)
202             ) $eng";
203         } else {
204             $tables[] = "CREATE TABLE IF NOT EXISTS categories (
205                 id          INTEGER PRIMARY KEY AUTOINCREMENT,
206                 name        TEXT    NOT NULL,
207                 slug        TEXT    NOT NULL UNIQUE,
208                 description TEXT    NOT NULL DEFAULT '',
209                 color       TEXT    NOT NULL DEFAULT '#3b82f6',
210                 icon        TEXT    NOT NULL DEFAULT '💬',
211                 position    INTEGER NOT NULL DEFAULT 0,
212                 parent_id   INTEGER,
213                 topic_count INTEGER NOT NULL DEFAULT 0,
214                 post_count  INTEGER NOT NULL DEFAULT 0,
215                 read_role   TEXT    NOT NULL DEFAULT 'guest',
216                 post_role   TEXT    NOT NULL DEFAULT 'member',
217                 reply_role  TEXT    NOT NULL DEFAULT 'member'
218             )";
219         }
220 
221         /* topics */
222         if ($mysql) {
223             $tables[] = "CREATE TABLE IF NOT EXISTS topics (
224                 id           INT AUTO_INCREMENT PRIMARY KEY,
225                 title        VARCHAR(200) NOT NULL,
226                 slug         VARCHAR(220) NOT NULL,
227                 category_id  INT          NOT NULL,
228                 user_id      INT          NOT NULL,
229                 views        INT          NOT NULL DEFAULT 0,
230                 reply_count  INT          NOT NULL DEFAULT 0,
231                 pinned       TINYINT      NOT NULL DEFAULT 0,
232                 closed       TINYINT      NOT NULL DEFAULT 0,
233                 archived     TINYINT      NOT NULL DEFAULT 0,
234                 last_post_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
235                 created_at   DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
236                 UNIQUE KEY uq_slug (slug),
237                 KEY ix_cat (category_id),
238                 KEY ix_user (user_id)
239             ) $eng";
240         } else {
241             $tables[] = "CREATE TABLE IF NOT EXISTS topics (
242                 id           INTEGER PRIMARY KEY AUTOINCREMENT,
243                 title        TEXT    NOT NULL,
244                 slug         TEXT    NOT NULL UNIQUE,
245                 category_id  INTEGER NOT NULL,
246                 user_id      INTEGER NOT NULL,
247                 views        INTEGER NOT NULL DEFAULT 0,
248                 reply_count  INTEGER NOT NULL DEFAULT 0,
249                 pinned       INTEGER NOT NULL DEFAULT 0,
250                 closed       INTEGER NOT NULL DEFAULT 0,
251                 archived     INTEGER NOT NULL DEFAULT 0,
252                 last_post_at TEXT    NOT NULL DEFAULT (datetime('now')),
253                 created_at   TEXT    NOT NULL DEFAULT (datetime('now'))
254             )";
255         }
256 
257         /* posts */
258         if ($mysql) {
259             $tables[] = "CREATE TABLE IF NOT EXISTS posts (
260                 id          INT AUTO_INCREMENT PRIMARY KEY,
261                 topic_id    INT      NOT NULL,
262                 user_id     INT      NOT NULL,
263                 content     TEXT     NOT NULL,
264                 post_num    INT      NOT NULL,
265                 reply_to    INT,
266                 likes       INT      NOT NULL DEFAULT 0,
267                 edited      TINYINT  NOT NULL DEFAULT 0,
268                 edit_reason VARCHAR(200),
269                 deleted     TINYINT  NOT NULL DEFAULT 0,
270                 created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
271                 updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
272                 KEY ix_topic (topic_id),
273                 KEY ix_user  (user_id)
274             ) $eng";
275         } else {
276             $tables[] = "CREATE TABLE IF NOT EXISTS posts (
277                 id          INTEGER PRIMARY KEY AUTOINCREMENT,
278                 topic_id    INTEGER NOT NULL,
279                 user_id     INTEGER NOT NULL,
280                 content     TEXT    NOT NULL,
281                 post_num    INTEGER NOT NULL,
282                 reply_to    INTEGER,
283                 likes       INTEGER NOT NULL DEFAULT 0,
284                 edited      INTEGER NOT NULL DEFAULT 0,
285                 edit_reason TEXT,
286                 deleted     INTEGER NOT NULL DEFAULT 0,
287                 created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
288                 updated_at  TEXT    NOT NULL DEFAULT (datetime('now'))
289             )";
290         }
291 
292         /* likes */
293         if ($mysql) {
294             $tables[] = "CREATE TABLE IF NOT EXISTS likes (
295                 post_id INT NOT NULL,
296                 user_id INT NOT NULL,
297                 PRIMARY KEY (post_id, user_id)
298             ) $eng";
299         } else {
300             $tables[] = "CREATE TABLE IF NOT EXISTS likes (
301                 post_id INTEGER NOT NULL,
302                 user_id INTEGER NOT NULL,
303                 PRIMARY KEY (post_id, user_id)
304             )";
305         }
306 
307         /* tags + topic_tags */
308         if ($mysql) {
309             $tables[] = "CREATE TABLE IF NOT EXISTS tags (
310                 id          INT AUTO_INCREMENT PRIMARY KEY,
311                 name        VARCHAR(50) NOT NULL,
312                 topic_count INT         NOT NULL DEFAULT 0,
313                 UNIQUE KEY uq_name (name)
314             ) $eng";
315             $tables[] = "CREATE TABLE IF NOT EXISTS topic_tags (
316                 topic_id INT NOT NULL,
317                 tag_id   INT NOT NULL,
318                 PRIMARY KEY (topic_id, tag_id)
319             ) $eng";
320         } else {
321             $tables[] = "CREATE TABLE IF NOT EXISTS tags (
322                 id          INTEGER PRIMARY KEY AUTOINCREMENT,
323                 name        TEXT    NOT NULL UNIQUE,
324                 topic_count INTEGER NOT NULL DEFAULT 0
325             )";
326             $tables[] = "CREATE TABLE IF NOT EXISTS topic_tags (
327                 topic_id INTEGER NOT NULL,
328                 tag_id   INTEGER NOT NULL,
329                 PRIMARY KEY (topic_id, tag_id)
330             )";
331         }
332 
333         /* notifications */
334         if ($mysql) {
335             $tables[] = "CREATE TABLE IF NOT EXISTS notifications (
336                 id         INT AUTO_INCREMENT PRIMARY KEY,
337                 user_id    INT         NOT NULL,
338                 type       VARCHAR(50) NOT NULL,
339                 payload    TEXT        NOT NULL,
340                 `read`     TINYINT     NOT NULL DEFAULT 0,
341                 created_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
342                 KEY ix_user (user_id)
343             ) $eng";
344         } else {
345             $tables[] = "CREATE TABLE IF NOT EXISTS notifications (
346                 id         INTEGER PRIMARY KEY AUTOINCREMENT,
347                 user_id    INTEGER NOT NULL,
348                 type       TEXT    NOT NULL,
349                 payload    TEXT    NOT NULL DEFAULT '{}',
350                 read       INTEGER NOT NULL DEFAULT 0,
351                 created_at TEXT    NOT NULL DEFAULT (datetime('now'))
352             )";
353         }
354 
355         /* friends */
356         if ($mysql) {
357             $tables[] = "CREATE TABLE IF NOT EXISTS friends (
358                 id         INT AUTO_INCREMENT PRIMARY KEY,
359                 user_id    INT         NOT NULL,
360                 friend_id  INT         NOT NULL,
361                 status     VARCHAR(20) NOT NULL DEFAULT 'pending',
362                 created_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
363                 UNIQUE KEY uq_pair (user_id, friend_id),
364                 KEY ix_uid (user_id),
365                 KEY ix_fid (friend_id)
366             ) $eng";
367         } else {
368             $tables[] = "CREATE TABLE IF NOT EXISTS friends (
369                 id         INTEGER PRIMARY KEY AUTOINCREMENT,
370                 user_id    INTEGER NOT NULL,
371                 friend_id  INTEGER NOT NULL,
372                 status     TEXT    NOT NULL DEFAULT 'pending',
373                 created_at TEXT    NOT NULL DEFAULT (datetime('now')),
374                 UNIQUE(user_id, friend_id)
375             )";
376         }
377 
378         /* messages */
379         if ($mysql) {
380             $tables[] = "CREATE TABLE IF NOT EXISTS messages (
381                 id                  INT AUTO_INCREMENT PRIMARY KEY,
382                 sender_id           INT          NOT NULL,
383                 receiver_id         INT          NOT NULL,
384                 subject             VARCHAR(150) NOT NULL DEFAULT '',
385                 body                TEXT         NOT NULL,
386                 is_read             TINYINT      NOT NULL DEFAULT 0,
387                 deleted_by_sender   TINYINT      NOT NULL DEFAULT 0,
388                 deleted_by_receiver TINYINT      NOT NULL DEFAULT 0,
389                 created_at          DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
390                 KEY ix_recv (receiver_id),
391                 KEY ix_send (sender_id)
392             ) $eng";
393         } else {
394             $tables[] = "CREATE TABLE IF NOT EXISTS messages (
395                 id                  INTEGER PRIMARY KEY AUTOINCREMENT,
396                 sender_id           INTEGER NOT NULL,
397                 receiver_id         INTEGER NOT NULL,
398                 subject             TEXT    NOT NULL DEFAULT '',
399                 body                TEXT    NOT NULL,
400                 is_read             INTEGER NOT NULL DEFAULT 0,
401                 deleted_by_sender   INTEGER NOT NULL DEFAULT 0,
402                 deleted_by_receiver INTEGER NOT NULL DEFAULT 0,
403                 created_at          TEXT    NOT NULL DEFAULT (datetime('now'))
404             )";
405         }
406 
407         /* rate_events */
408         if ($mysql) {
409             $tables[] = "CREATE TABLE IF NOT EXISTS rate_events (
410                 id         INT AUTO_INCREMENT PRIMARY KEY,
411                 user_id    INT         NOT NULL,
412                 event_type VARCHAR(20) NOT NULL DEFAULT 'post',
413                 created_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
414                 KEY ix_rate (user_id, event_type, created_at)
415             ) $eng";
416         } else {
417             $tables[] = "CREATE TABLE IF NOT EXISTS rate_events (
418                 id         INTEGER PRIMARY KEY AUTOINCREMENT,
419                 user_id    INTEGER NOT NULL,
420                 event_type TEXT    NOT NULL DEFAULT 'post',
421                 created_at TEXT    NOT NULL DEFAULT (datetime('now'))
422             )";
423         }
424 
425         /* themes */
426         if ($mysql) {
427             $tables[] = "CREATE TABLE IF NOT EXISTS themes (
428                 id         INT AUTO_INCREMENT PRIMARY KEY,
429                 name       VARCHAR(100) NOT NULL,
430                 slug       VARCHAR(100) NOT NULL,
431                 css        LONGTEXT     NOT NULL DEFAULT '',
432                 is_active  TINYINT      NOT NULL DEFAULT 0,
433                 created_at DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
434                 UNIQUE KEY uq_slug (slug)
435             ) $eng";
436         } else {
437             $tables[] = "CREATE TABLE IF NOT EXISTS themes (
438                 id         INTEGER PRIMARY KEY AUTOINCREMENT,
439                 name       TEXT    NOT NULL,
440                 slug       TEXT    NOT NULL UNIQUE,
441                 css        TEXT    NOT NULL DEFAULT '',
442                 is_active  INTEGER NOT NULL DEFAULT 0,
443                 created_at TEXT    NOT NULL DEFAULT (datetime('now'))
444             )";
445         }
446 
447         $pdo = self::connect();
448         foreach ($tables as $sql) {
449             $pdo->exec($sql);
450         }
451 
452         /* SQLite-only indexes */
453         if (!$mysql) {
454             $pdo->exec("CREATE INDEX IF NOT EXISTS ix_topics_cat  ON topics(category_id)");
455             $pdo->exec("CREATE INDEX IF NOT EXISTS ix_posts_topic ON posts(topic_id)");
456             $pdo->exec("CREATE INDEX IF NOT EXISTS ix_notif_user  ON notifications(user_id)");
457             $pdo->exec("CREATE INDEX IF NOT EXISTS ix_rate_user   ON rate_events(user_id, event_type, created_at)");
458             $pdo->exec("CREATE INDEX IF NOT EXISTS ix_friends_u   ON friends(user_id)");
459             $pdo->exec("CREATE INDEX IF NOT EXISTS ix_msg_recv    ON messages(receiver_id)");
460         }
461 
462         /* ── Column migrations (safe for both SQLite + MySQL upgrades) ── */
463         // Add columns that were introduced in later versions.
464         // Uses IF NOT EXISTS logic compatible with both drivers.
465         if ($mysql) {
466             // MySQL: check information_schema then ALTER TABLE if column missing
467             $dbName = defined('DB_NAME') ? DB_NAME : '';
468             $existingCols = array_column(self::rows(
469                 "SELECT COLUMN_NAME FROM information_schema.COLUMNS
470                  WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'users'",
471                 [$dbName]
472             ), 'COLUMN_NAME');
473             $mysqlAdd = [
474                 'karma'          => "ALTER TABLE users ADD COLUMN karma INT NOT NULL DEFAULT 0",
475                 'permissions'    => "ALTER TABLE users ADD COLUMN permissions TEXT NOT NULL DEFAULT '{}'",
476                 'friends_hidden' => "ALTER TABLE users ADD COLUMN friends_hidden TINYINT NOT NULL DEFAULT 0",
477                 'suspended'      => "ALTER TABLE users ADD COLUMN suspended TINYINT NOT NULL DEFAULT 0",
478                 'silenced'       => "ALTER TABLE users ADD COLUMN silenced TINYINT NOT NULL DEFAULT 0",
479                 'topic_count'    => "ALTER TABLE users ADD COLUMN topic_count INT NOT NULL DEFAULT 0",
480             ];
481             foreach ($mysqlAdd as $col => $sql) {
482                 if (!in_array($col, $existingCols)) {
483                     try { self::run($sql); } catch (\Throwable $e) { /* already exists */ }
484                 }
485             }
486         } else {
487             // SQLite: use PRAGMA table_info
488             $cols = array_column(self::rows("PRAGMA table_info(users)"), 'name');
489             $sqliteAdd = [
490                 'karma'          => "ALTER TABLE users ADD COLUMN karma INTEGER NOT NULL DEFAULT 0",
491                 'permissions'    => "ALTER TABLE users ADD COLUMN permissions TEXT NOT NULL DEFAULT '{}'",
492                 'friends_hidden' => "ALTER TABLE users ADD COLUMN friends_hidden INTEGER NOT NULL DEFAULT 0",
493                 'suspended'      => "ALTER TABLE users ADD COLUMN suspended INTEGER NOT NULL DEFAULT 0",
494                 'silenced'       => "ALTER TABLE users ADD COLUMN silenced INTEGER NOT NULL DEFAULT 0",
495                 'topic_count'    => "ALTER TABLE users ADD COLUMN topic_count INTEGER NOT NULL DEFAULT 0",
496             ];
497             foreach ($sqliteAdd as $col => $sql) {
498                 if (!in_array($col, $cols)) {
499                     try { self::run($sql); } catch (\Throwable $e) { /* already exists */ }
500                 }
501             }
502         }
503 
504         /* ── Categories: role permission columns ── */
505         if ($mysql) {
506             $dbName = defined('DB_NAME') ? DB_NAME : '';
507             $catCols = array_column(self::rows(
508                 "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME='categories'",
509                 [$dbName]
510             ), 'COLUMN_NAME');
511             foreach (['read_role'=>"VARCHAR(20) NOT NULL DEFAULT 'guest'",'post_role'=>"VARCHAR(20) NOT NULL DEFAULT 'member'",'reply_role'=>"VARCHAR(20) NOT NULL DEFAULT 'member'"] as $col=>$def) {
512                 if (!in_array($col,$catCols)) { try{self::run("ALTER TABLE categories ADD COLUMN $col $def");}catch(\Throwable $e){} }
513             }
514         } else {
515             $catCols = array_column(self::rows("PRAGMA table_info(categories)"),'name');
516             foreach (['read_role'=>"TEXT NOT NULL DEFAULT 'guest'",'post_role'=>"TEXT NOT NULL DEFAULT 'member'",'reply_role'=>"TEXT NOT NULL DEFAULT 'member'"] as $col=>$def) {
517                 if (!in_array($col,$catCols)) { try{self::run("ALTER TABLE categories ADD COLUMN $col $def");}catch(\Throwable $e){} }
518             }
519         }
520 
521         /* ── Users: add location column ── */
522         if ($mysql) {
523             $dbName = defined('DB_NAME') ? DB_NAME : '';
524             $uCols = array_column(self::rows(
525                 "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=? AND TABLE_NAME='users'",
526                 [$dbName]
527             ), 'COLUMN_NAME');
528             if (!in_array('location', $uCols)) {
529                 try { self::run("ALTER TABLE users ADD COLUMN location VARCHAR(100) NOT NULL DEFAULT ''"); }
530                 catch (\Throwable $e) {}
531             }
532         } else {
533             $uCols2 = array_column(self::rows("PRAGMA table_info(users)"), 'name');
534             if (!in_array('location', $uCols2)) {
535                 try { self::run("ALTER TABLE users ADD COLUMN location TEXT NOT NULL DEFAULT ''"); }
536                 catch (\Throwable $e) {}
537             }
538         }
539 
540         /* ── Messages: add conversation_id for chat threading ── */
541         // conversation_id = LEAST(sender_id, receiver_id) * 1000000 + GREATEST(...)
542         // We store it as a VARCHAR key for easy lookup
543         if ($mysql) {
544             $dbName = defined('DB_NAME') ? DB_NAME : '';
545             $msgCols = array_column(self::rows(
546                 "SELECT COLUMN_NAME FROM information_schema.COLUMNS
547                  WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'messages'", [$dbName]
548             ), 'COLUMN_NAME');
549             if (!in_array('conversation_id', $msgCols)) {
550                 try {
551                     self::run("ALTER TABLE messages ADD COLUMN conversation_id VARCHAR(30) NOT NULL DEFAULT ''");
552                     self::run("ALTER TABLE messages ADD INDEX ix_conv (conversation_id)");
553                     // Back-fill existing rows
554                     self::run("UPDATE messages SET conversation_id = CONCAT(LEAST(sender_id,receiver_id),'-',GREATEST(sender_id,receiver_id))");
555                 } catch (\Throwable $e) { /* ignore */ }
556             }
557         } else {
558             $msgCols = array_column(self::rows("PRAGMA table_info(messages)"), 'name');
559             if (!in_array('conversation_id', $msgCols)) {
560                 try {
561                     self::run("ALTER TABLE messages ADD COLUMN conversation_id TEXT NOT NULL DEFAULT ''");
562                     self::run("UPDATE messages SET conversation_id = MIN(sender_id,receiver_id)||'-'||MAX(sender_id,receiver_id)");
563                 } catch (\Throwable $e) { /* ignore */ }
564             }
565         }
566     }
567 
568 
569     /* ── Helpers ─────────────────────────────────────────────── */
570     /** Cross-driver NOW() */
571     public static function now(): string {
572         return self::isMysql() ? 'NOW()' : "datetime('now')";
573     }
574 
575     /** Cross-driver datetime comparison for rate limiting */
576     public static function sinceSeconds(int $seconds): string {
577         if (self::isMysql()) {
578             return "DATE_SUB(NOW(), INTERVAL {$seconds} SECOND)";
579         }
580         return "datetime('now','-{$seconds} seconds')";
581     }
582 }