<?php
class Database {
    private $host = 'localhost';
    private $db_name = 'iplztpse_final';
    private $username = 'root';
    private $password = '';
    private static $instance = null;
    private $conn;

    private function __construct() {
        try {
            $this->conn = new PDO(
                'mysql:host=' . $this->host . ';dbname=' . $this->db_name . ';charset=utf8',
                $this->username,
                $this->password
            );
            $this->conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->conn->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
        } catch(PDOException $exception) {
            die('❌ Database Connection Error: ' . $exception->getMessage());
        }
    }

    // ✅ FIXED: return Database object, not raw PDO
    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance; 
    }

    public function getConnection() {
        return $this->conn;
    }

    // Query wrapper
    public function query($sql, $params = []) {
        try {
            $stmt = $this->conn->prepare($sql);
            $stmt->execute($params);
            return $stmt;
        } catch(PDOException $e) {
            error_log('Database query error: ' . $e->getMessage());
            return false;
        }
    }

    // Fetch single record
    public function fetch($sql, $params = []) {
        $stmt = $this->query($sql, $params);
        return $stmt ? $stmt->fetch() : false;
    }

    // Fetch all records
    public function fetchAll($sql, $params = []) {
        $stmt = $this->query($sql, $params);
        return $stmt ? $stmt->fetchAll() : [];
    }

    // Insert record
    public function insert($table, $data) {
        $columns = array_keys($data);
        $placeholders = array_map(fn($col) => ':' . $col, $columns);

        $sql = "INSERT INTO {$table} (" . implode(', ', $columns) . 
               ") VALUES (" . implode(', ', $placeholders) . ")";

        $stmt = $this->query($sql, $data);
        return $stmt ? $this->conn->lastInsertId() : false;
    }

    // Update record
    public function update($table, $data, $where, $whereParams = []) {
        $set = [];
        $params = [];
        $paramCounter = 0;
        
        // Build SET clause with unique parameter names (p0, p1, p2, etc.)
        foreach ($data as $key => $value) {
            $paramName = 'p' . ($paramCounter++);
            $set[] = "{$key} = :{$paramName}";
            $params[$paramName] = $value;
        }

        // Build WHERE clause - handle both named and positional parameters
        $whereSQL = $where;
        
        // If whereParams has keys (named parameters), merge them directly
        if (!empty($whereParams) && is_array($whereParams)) {
            if (array_key_exists(0, $whereParams)) {
                // Positional parameters - replace ? with named params
                foreach ($whereParams as $value) {
                    $paramName = 'p' . ($paramCounter++);
                    $whereSQL = preg_replace('/\?/', ':' . $paramName, $whereSQL, 1);
                    $params[$paramName] = $value;
                }
            } else {
                // Named parameters - merge directly
                $params = array_merge($params, $whereParams);
            }
        }

        $sql = "UPDATE {$table} SET " . implode(', ', $set) . " WHERE " . $whereSQL;
        
        try {
            $stmt = $this->conn->prepare($sql);
            $stmt->execute($params);
            return $stmt ? $stmt->rowCount() : false;
        } catch(PDOException $e) {
            error_log('Database update error: ' . $e->getMessage());
            return false;
        }
    }

    // Delete record
    public function delete($table, $where, $params = []) {
        $sql = "DELETE FROM {$table} WHERE {$where}";
        $stmt = $this->query($sql, $params);
        return $stmt ? $stmt->rowCount() : false;
    }
}
?>
