<?php
/**
 * Database Class - PDO with prepared statements
 */
class Database {
    private static $instance = null;
    private $connection;
    private $statement;
    private $error;
    
    private function __construct() {
        $host = Config::get('db_host');
        $name = Config::get('db_name');
        $user = Config::get('db_user');
        $pass = Config::get('db_pass');
        $charset = Config::get('db_charset', 'utf8mb4');
        
        $dsn = "mysql:host=$host;dbname=$name;charset=$charset";
        $options = [
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => false,
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES $charset COLLATE utf8mb4_unicode_ci",
            PDO::MYSQL_ATTR_FOUND_ROWS => true
        ];
        
        try {
            $this->connection = new PDO($dsn, $user, $pass, $options);
        } catch (PDOException $e) {
            error_log("Database Connection Error: " . $e->getMessage());
            throw new Exception("Database connection failed. Please try again later.");
        }
    }
    
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance->connection;
    }
    
    public static function query($sql, $params = []) {
        try {
            $conn = self::getInstance();
            $stmt = $conn->prepare($sql);
            $stmt->execute($params);
            return $stmt;
        } catch (PDOException $e) {
            error_log("Query Error: " . $e->getMessage());
            error_log("SQL: " . $sql);
            error_log("Params: " . print_r($params, true));
            throw $e;
        }
    }
    
    public static function fetchAll($sql, $params = []) {
        try {
            return self::query($sql, $params)->fetchAll();
        } catch (PDOException $e) {
            error_log("fetchAll Error: " . $e->getMessage());
            return false;
        }
    }
    
    public static function fetchOne($sql, $params = []) {
        try {
            $result = self::query($sql, $params)->fetch();
            return $result ?: null;
        } catch (PDOException $e) {
            error_log("fetchOne Error: " . $e->getMessage());
            return false;
        }
    }
    
    public static function insert($table, $data) {
        try {
            $conn = self::getInstance();
            
            $columns = implode('`, `', array_keys($data));
            $placeholders = implode(', ', array_fill(0, count($data), '?'));
            
            $sql = "INSERT INTO `$table` (`$columns`) VALUES ($placeholders)";
            
            // Log for debugging
            error_log("Insert SQL: " . $sql);
            error_log("Insert data: " . print_r(array_values($data), true));
            
            $stmt = $conn->prepare($sql);
            $success = $stmt->execute(array_values($data));
            
            if ($success) {
                $lastId = $conn->lastInsertId();
                error_log("Last insert ID: " . $lastId);
                return $lastId;
            }
            
            error_log("Insert failed - execute returned false");
            return false;
            
        } catch (PDOException $e) {
            error_log("Insert Error: " . $e->getMessage());
            error_log("SQL State: " . $e->errorInfo[0]);
            error_log("Error Code: " . $e->errorInfo[1]);
            error_log("Error Message: " . $e->errorInfo[2]);
            throw $e;
        }
    }
    
    public static function update($table, $data, $where, $whereParams = []) {
        try {
            $conn = self::getInstance();
            
            $set = [];
            foreach (array_keys($data) as $column) {
                $set[] = "`$column` = ?";
            }
            $setClause = implode(', ', $set);
            
            $sql = "UPDATE `$table` SET $setClause WHERE $where";
            
            // Log for debugging
            error_log("Update SQL: " . $sql);
            error_log("Update data: " . print_r(array_merge(array_values($data), $whereParams), true));
            
            $stmt = $conn->prepare($sql);
            $stmt->execute(array_merge(array_values($data), $whereParams));
            
            return $stmt->rowCount();
            
        } catch (PDOException $e) {
            error_log("Update Error: " . $e->getMessage());
            error_log("SQL State: " . $e->errorInfo[0]);
            error_log("Error Code: " . $e->errorInfo[1]);
            error_log("Error Message: " . $e->errorInfo[2]);
            throw $e;
        }
    }
    
    public static function delete($table, $where, $params = []) {
        try {
            $conn = self::getInstance();
            
            $sql = "DELETE FROM `$table` WHERE $where";
            
            // Log for debugging
            error_log("Delete SQL: " . $sql);
            error_log("Delete params: " . print_r($params, true));
            
            $stmt = $conn->prepare($sql);
            $stmt->execute($params);
            
            return $stmt->rowCount();
            
        } catch (PDOException $e) {
            error_log("Delete Error: " . $e->getMessage());
            error_log("SQL State: " . $e->errorInfo[0]);
            error_log("Error Code: " . $e->errorInfo[1]);
            error_log("Error Message: " . $e->errorInfo[2]);
            throw $e;
        }
    }
    
    public static function beginTransaction() {
        return self::getInstance()->beginTransaction();
    }
    
    public static function commit() {
        return self::getInstance()->commit();
    }
    
    public static function rollBack() {
        return self::getInstance()->rollBack();
    }
}