<?php
// app/Core/Database.php - Database Connection & Operations

class Database {
    private $conn;
    private static $instance = null;
    
    private function __construct() {
        try {
            $this->conn = mysqli_connect(
                DB_HOST,
                DB_USER,
                DB_PASS,
                DB_NAME
            );
            
            if (!$this->conn) {
                throw new Exception("Connection failed: " . mysqli_connect_error());
            }
            
            // Set charset
            if (!mysqli_set_charset($this->conn, DB_CHARSET)) {
                throw new Exception("Error setting charset: " . mysqli_error($this->conn));
            }
            
            // Set timezone
            mysqli_query($this->conn, "SET time_zone = '+01:00'");
            
        } catch (Exception $e) {
            error_log("Database Connection Error: " . $e->getMessage());
            throw $e;
        }
    }
    
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }
    
    public function getConnection() {
        return $this->conn;
    }
    
    
    // Execute query with prepared statement (INSERT, UPDATE, DELETE)
    public function execute($sql, $params = []) {
        try {
            $stmt = mysqli_prepare($this->conn, $sql);
            
            if (!$stmt) {
                throw new Exception("Prepare failed: " . mysqli_error($this->conn));
            }
            
            if (!empty($params)) {
                $types = $this->getParamTypes($params);
                mysqli_stmt_bind_param($stmt, $types, ...$params);
            }
            
            $result = mysqli_stmt_execute($stmt);
            
            if (!$result) {
                throw new Exception("Execute failed: " . mysqli_stmt_error($stmt));
            }
            
            mysqli_stmt_close($stmt);
            return $result;
            
        } catch (Exception $e) {
            error_log("Database Execute Error: " . $e->getMessage() . " | SQL: " . $sql);
            return false;
        }
    }
    
    // Fetch single row
    public function fetch($sql, $params = []) {
        try {
            $stmt = mysqli_prepare($this->conn, $sql);
            
            if (!$stmt) {
                throw new Exception("Prepare failed: " . mysqli_error($this->conn));
            }
            
            if (!empty($params)) {
                $types = $this->getParamTypes($params);
                mysqli_stmt_bind_param($stmt, $types, ...$params);
            }
            
            mysqli_stmt_execute($stmt);
            $result = mysqli_stmt_get_result($stmt);
            
            if (!$result) {
                mysqli_stmt_close($stmt);
                return null;
            }
            
            $row = mysqli_fetch_assoc($result);
            mysqli_stmt_close($stmt);
            
            return $row ?: null;
            
        } catch (Exception $e) {
            error_log("Database Fetch Error: " . $e->getMessage() . " | SQL: " . $sql);
            return null;
        }
    }
    
    // Fetch all rows
    public function fetchAll($sql, $params = []) {
        try {
            $stmt = mysqli_prepare($this->conn, $sql);
            
            if (!$stmt) {
                throw new Exception("Prepare failed: " . mysqli_error($this->conn));
            }
            
            if (!empty($params)) {
                $types = $this->getParamTypes($params);
                mysqli_stmt_bind_param($stmt, $types, ...$params);
            }
            
            mysqli_stmt_execute($stmt);
            $result = mysqli_stmt_get_result($stmt);
            
            if (!$result) {
                mysqli_stmt_close($stmt);
                return [];
            }
            
            $rows = mysqli_fetch_all($result, MYSQLI_ASSOC);
            mysqli_stmt_close($stmt);
            
            return $rows ?: [];
            
        } catch (Exception $e) {
            error_log("Database FetchAll Error: " . $e->getMessage() . " | SQL: " . $sql);
            return [];
        }
    }
    
    // Get last insert ID
    public function lastInsertId() {
        return mysqli_insert_id($this->conn);
    }
    
    // Get affected rows
    public function affectedRows() {
        return mysqli_affected_rows($this->conn);
    }
    
    // Begin transaction
    public function beginTransaction() {
        return mysqli_begin_transaction($this->conn);
    }
    
    // Commit transaction
    public function commit() {
        return mysqli_commit($this->conn);
    }
    
    // Rollback transaction
    public function rollback() {
        return mysqli_rollback($this->conn);
    }
    
    // Check if in transaction - ADDED METHOD
    public function inTransaction() {
        // MySQLi doesn't have a direct way to check transaction status
        // We'll query the session variable
        $result = mysqli_query($this->conn, "SELECT @@in_transaction as in_tx");
        if ($result) {
            $row = mysqli_fetch_assoc($result);
            return (bool)$row['in_tx'];
        }
        return false;
    }
    
    // Escape string
    public function escape($value) {
        return mysqli_real_escape_string($this->conn, $value);
    }
    
    // Get parameter types for prepared statement
    private function getParamTypes($params) {
        $types = '';
        foreach ($params as $param) {
            if (is_int($param)) {
                $types .= 'i';
            } elseif (is_float($param) || is_double($param)) {
                $types .= 'd';
            } elseif (is_string($param)) {
                $types .= 's';
            } else {
                $types .= 'b'; // blob
            }
        }
        return $types;
    }
    
    // Check if table exists
    public function tableExists($tableName) {
        $sql = "SHOW TABLES LIKE ?";
        $result = $this->fetch($sql, [$tableName]);
        return $result !== null;
    }
    
    // Count rows
    public function count($sql, $params = []) {
        $result = $this->fetch($sql, $params);
        return $result ? (int)$result['count'] : 0;
    }
    
    // Prevent cloning
    private function __clone() {}
    
    // Prevent unserialization
    public function __wakeup() {
        throw new Exception("Cannot unserialize singleton");
    }
    
    // Close connection on destruct
    public function __destruct() {
        if ($this->conn) {
            mysqli_close($this->conn);
        }
    }
}