iptv-stream-web/includes/Database.php

<?php
/**
 * Database Connection and Query Management Class
 * Handles SQLite database operations with prepared statements and error handling
 */

class Database
{
    private static $instance = null;
    private $pdo;
    private $dbPath;

    /**
     * Get database instance (singleton pattern)
     */
    public static function getInstance()
    {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

    /**
     * Private constructor for singleton
     */
    private function __construct()
    {
        $this->dbPath = Config::get('db.path', __DIR__ . '/../data/app.db');

        // Ensure data directory exists
        $dataDir = dirname($this->dbPath);
        if (!is_dir($dataDir)) {
            mkdir($dataDir, 0755, true);
        }

        $this->connect();
        $this->runMigrations();
    }

    /**
     * Connect to SQLite database
     */
    private function connect()
    {
        try {
            $this->pdo = new PDO("sqlite:{$this->dbPath}");
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
            $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

            // Enable WAL mode for better performance
            $this->pdo->exec('PRAGMA journal_mode = WAL');
            $this->pdo->exec('PRAGMA synchronous = NORMAL');
            $this->pdo->exec('PRAGMA cache_size = 10000');
            $this->pdo->exec('PRAGMA temp_store = MEMORY');

            if (Config::isDebug()) {
                error_log("Database connected successfully: {$this->dbPath}");
            }

        } catch (PDOException $e) {
            error_log("Database connection failed: " . $e->getMessage());
            throw new Exception("Database connection error");
        }
    }

    /**
     * Run database migrations
     */
    private function runMigrations()
    {
        $migrationDir = __DIR__ . '/../migrations';
        if (!is_dir($migrationDir)) {
            error_log("Migrations directory not found: {$migrationDir}");
            return;
        }

        $migrationsRun = [];

        // Check if migrations table exists
        try {
            $result = $this->pdo->query("SELECT name FROM sqlite_master WHERE type='table' AND name='migrations'");
            if ($result->fetch()) {
                // Get already run migrations
                $stmt = $this->pdo->query("SELECT migration_name FROM migrations");
                $migrationsRun = $stmt->fetchAll(PDO::FETCH_COLUMN);
            } else {
                // Create migrations table
                $this->pdo->exec("CREATE TABLE migrations (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    migration_name VARCHAR(255) UNIQUE NOT NULL,
                    run_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )");
            }
        } catch (PDOException $e) {
            error_log("Error checking migrations table: " . $e->getMessage());
            return;
        }

        // Get migration files
        $files = glob($migrationDir . '/*.sql');
        sort($files); // Run in order

        foreach ($files as $file) {
            $migrationName = basename($file);

            if (in_array($migrationName, $migrationsRun)) {
                continue; // Already run
            }

            try {
                $sql = file_get_contents($file);
                if (empty($sql)) {
                    error_log("Empty migration file: {$migrationName}");
                    continue;
                }

                $this->pdo->exec($sql);

                // Record migration as run
                $stmt = $this->pdo->prepare("INSERT INTO migrations (migration_name) VALUES (?)");
                $stmt->execute([$migrationName]);

                error_log("Migration completed: {$migrationName}");

            } catch (PDOException $e) {
                error_log("Migration failed {$migrationName}: " . $e->getMessage());
                // Continue with other migrations rather than stopping
            }
        }
    }

    /**
     * Execute a prepared statement
     */
    public function query($sql, $params = [])
    {
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($params);
            return $stmt;
        } catch (PDOException $e) {
            error_log("Database query error: " . $e->getMessage() . " | SQL: {$sql}");
            throw $e;
        }
    }

    /**
     * Execute a query and return all results
     */
    public function fetchAll($sql, $params = [])
    {
        return $this->query($sql, $params)->fetchAll();
    }

    /**
     * Execute a query and return single result
     */
    public function fetch($sql, $params = [])
    {
        return $this->query($sql, $params)->fetch();
    }

    /**
     * Execute a query and return single column
     */
    public function fetchColumn($sql, $params = [])
    {
        return $this->query($sql, $params)->fetchColumn();
    }

    /**
     * Insert and return last insert ID
     */
    public function insert($sql, $params = [])
    {
        $this->query($sql, $params);
        return $this->pdo->lastInsertId();
    }

    /**
     * Update records and return affected row count
     */
    public function update($sql, $params = [])
    {
        $stmt = $this->query($sql, $params);
        return $stmt->rowCount();
    }

    /**
     * Delete records and return affected row count
     */
    public function delete($sql, $params = [])
    {
        return $this->update($sql, $params);
    }

    /**
     * Begin transaction
     */
    public function beginTransaction()
    {
        $this->pdo->beginTransaction();
    }

    /**
     * Commit transaction
     */
    public function commit()
    {
        $this->pdo->commit();
    }

    /**
     * Rollback transaction
     */
    public function rollback()
    {
        $this->pdo->rollBack();
    }

    /**
     * Get PDO instance (for advanced operations)
     */
    public function getPDO()
    {
        return $this->pdo;
    }

    /**
     * Check if table exists
     */
    public function tableExists($tableName)
    {
        $result = $this->pdo->query("SELECT name FROM sqlite_master WHERE type='table' AND name='{$tableName}'");
        return $result->fetch() !== false;
    }

    /**
     * Get database statistics
     */
    public function getStats()
    {
        $stats = [];

        // Table counts
        $tables = ['users', 'chat_messages', 'active_viewers', 'banned_users'];
        foreach ($tables as $table) {
            if ($this->tableExists($table)) {
                $count = $this->fetchColumn("SELECT COUNT(*) FROM {$table}");
                $stats["{$table}_count"] = $count;
            }
        }

        // Database size
        $stats['db_size'] = filesize($this->dbPath);

        return $stats;
    }

    /**
     * Backup database
     */
    public function backup($backupPath = null)
    {
        if (!$backupPath) {
            $backupPath = $this->dbPath . '.backup.' . date('Y-m-d_H-i-s');
        }

        if (copy($this->dbPath, $backupPath)) {
            // Backup WAL file if exists
            $walFile = $this->dbPath . '-wal';
            if (file_exists($walFile)) {
                copy($walFile, $backupPath . '-wal');
            }

            return $backupPath;
        }

        return false;
    }

    /**
     * Optimize database
     */
    public function optimize()
    {
        try {
            $this->pdo->exec('VACUUM');
            $this->pdo->exec('REINDEX');
            $this->pdo->exec('ANALYZE');
            error_log("Database optimization completed");
        } catch (PDOException $e) {
            error_log("Database optimization failed: " . $e->getMessage());
        }
    }
}