Ecobuddy/Models/Database.php

<?php
/**
 * Database connection handler using Singleton pattern
 */
class Database {
    /**
     * @var Database|null The singleton instance
     */
    protected static $_dbInstance = null;

    /**
     * @var PDO The database connection handle
     */
    protected $_dbHandle;

    /**
     * Gets the database connection handle
     * @return PDO The database connection
     */
    public function getDbConnection(): PDO
    {
        return $this->_dbHandle;
    }

    /**
     * Gets the singleton instance of the Database class
     * @return Database The database instance
     */
    public static function getInstance(): ?Database
    {
        if(self::$_dbInstance == null) {
            self::$_dbInstance = new self();
        }
        return self::$_dbInstance;
    }

    /**
     * Private constructor to prevent direct instantiation
     * Initialises the database connection
     * @throws PDOException if connection fails
     */
    private function __construct() {
        try {
            // Create PDO connection with error handling
            $this->_dbHandle = new PDO("sqlite:Databases/ecobuddy.sqlite");
            
            // Configure PDO for better error handling and performance 
            $this->_dbHandle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);
            $this->_dbHandle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            
            // SQLite3 sometimes just forgets foreign keys exist i guess (https://stackoverflow.com/questions/15301643/sqlite3-forgets-to-use-foreign-keys)
            $this->_dbHandle->exec('PRAGMA foreign_keys = ON;');
            
            // Set transaction timeout to 5 seconds, just stops the app from hanging when the db is busy
            $this->_dbHandle->exec('PRAGMA busy_timeout = 5000;');
        }
        catch (PDOException $e) {
            // Log the error and rethrow
            error_log("Database connection error: " . $e->getMessage());
            throw $e;
        }
    }

    /**
     * Destructor to clean up database connection
     */
    public function __destruct() {
        $this->_dbHandle = null;
    }
}