<?php
// src/Database.php - Database Connection and Management

class Database {
    private static $instance = null;
    private $connection;
    
    private $host = DB_HOST;
    private $db_name = DB_NAME;
    private $username = DB_USER;
    private $password = DB_PASSWORD;

    /**
     * Private constructor to prevent direct instantiation
     */
    private function __construct() {
        $this->connect();
    }

    /**
     * Establish database connection using PDO
     */
    private function connect() {
        $this->connection = null;

        try {
            $dsn = "mysql:host=" . $this->host . ";dbname=" . $this->db_name . ";charset=utf8mb4";
            
            $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_PERSISTENT => false,
                PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"
            ];

            $this->connection = new PDO($dsn, $this->username, $this->password, $options);
            
            if (DEBUG_MODE) {
                error_log('Database connection established successfully');
            }
            
        } catch (PDOException $e) {
            error_log('Database Connection Error: ' . $e->getMessage());
            
            if (DEBUG_MODE) {
                die('Database Connection Failed: ' . $e->getMessage());
            } else {
                die('Database Connection Failed. Please check configuration.');
            }
        }

        return $this->connection;
    }

    /**
     * Get singleton instance of Database
     */
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance->connection;
    }

    /**
     * Prepare and execute a query
     */
    public static function query($sql, $params = []) {
        try {
            $db = self::getInstance();
            $stmt = $db->prepare($sql);
            $stmt->execute($params);
            return $stmt;
        } catch (PDOException $e) {
            error_log('Database Query Error: ' . $e->getMessage());
            error_log('SQL: ' . $sql);
            throw $e;
        }
    }

    /**
     * Fetch single row
     */
    public static function fetchOne($sql, $params = []) {
        $stmt = self::query($sql, $params);
        return $stmt->fetch();
    }

    /**
     * Fetch all rows
     */
    public static function fetchAll($sql, $params = []) {
        $stmt = self::query($sql, $params);
        return $stmt->fetchAll();
    }

    /**
     * Insert data and return last inserted ID
     */
    public static function insert($table, $data) {
        $fields = array_keys($data);
        $placeholders = array_map(function($field) { return ':' . $field; }, $fields);
        
        $sql = "INSERT INTO {$table} (" . implode(', ', $fields) . ") VALUES (" . implode(', ', $placeholders) . ")";
        
        $params = [];
        foreach ($data as $field => $value) {
            $params[':' . $field] = $value;
        }
        
        $stmt = self::query($sql, $params);
        return self::getInstance()->lastInsertId();
    }

    /**
     * Update data
     */
    public static function update($table, $data, $where, $whereParams = []) {
        $fields = [];
        foreach (array_keys($data) as $field) {
            $fields[] = "{$field} = :{$field}";
        }
        
        $sql = "UPDATE {$table} SET " . implode(', ', $fields) . " WHERE {$where}";
        
        $params = [];
        foreach ($data as $field => $value) {
            $params[':' . $field] = $value;
        }
        $params = array_merge($params, $whereParams);
        
        return self::query($sql, $params);
    }

    /**
     * Delete data
     */
    public static function delete($table, $where, $params = []) {
        $sql = "DELETE FROM {$table} WHERE {$where}";
        return self::query($sql, $params);
    }

    /**
     * Begin transaction
     */
    public static function beginTransaction() {
        return self::getInstance()->beginTransaction();
    }

    /**
     * Commit transaction
     */
    public static function commit() {
        return self::getInstance()->commit();
    }

    /**
     * Rollback transaction
     */
    public static function rollback() {
        return self::getInstance()->rollBack();
    }

    /**
     * Check if table exists
     */
    public static function tableExists($table) {
        try {
            $sql = "SHOW TABLES LIKE :table";
            $stmt = self::query($sql, [':table' => $table]);
            return $stmt->rowCount() > 0;
        } catch (PDOException $e) {
            return false;
        }
    }

    /**
     * Get connection instance (for raw queries)
     */
    public static function getConnection() {
        return self::getInstance();
    }

    /**
     * Close connection
     */
    public static function closeConnection() {
        self::$instance = null;
    }
}

?>