<?php
/**
 * Database Class - MySQLi Wrapper
 * Hostinger Shared Hosting Compatible
 */

class Database {
    private static $instance = null;
    private $connection;
    private $lastError = '';

    private function __construct() {
        $this->connection = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);

        if ($this->connection->connect_error) {
            $this->lastError = 'Connection failed: ' . $this->connection->connect_error;
            error_log($this->lastError);
            throw new Exception($this->lastError);
        }

        $this->connection->set_charset(DB_CHARSET);
    }

    public static function getInstance() {
        if (self::$instance === null) {
            self::$instance = new self();
        }
        return self::$instance;
    }

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

    public function query($sql, $params = [], $types = '') {
        $stmt = $this->connection->prepare($sql);

        if (!$stmt) {
            $this->lastError = $this->connection->error;
            error_log('Prepare failed: ' . $this->lastError);
            return false;
        }

        if (!empty($params)) {
            if (empty($types)) {
                $types = $this->getTypes($params);
            }
            $stmt->bind_param($types, ...$params);
        }

        $stmt->execute();
        $result = $stmt->get_result();
        $stmt->close();

        return $result;
    }

    public function execute($sql, $params = [], $types = '') {
        $stmt = $this->connection->prepare($sql);

        if (!$stmt) {
            $this->lastError = $this->connection->error;
            error_log('Prepare failed: ' . $this->lastError);
            return false;
        }

        if (!empty($params)) {
            if (empty($types)) {
                $types = $this->getTypes($params);
            }
            $stmt->bind_param($types, ...$params);
        }

        $success = $stmt->execute();
        if (!$success) {
            $this->lastError = $stmt->error;
            error_log('Execute failed: ' . $this->lastError);
        }
        $insertId = $stmt->insert_id;
        $stmt->close();

        return $success ? ($insertId > 0 ? $insertId : true) : false;
    }

    public function fetch($sql, $params = [], $types = '') {
        $result = $this->query($sql, $params, $types);
        if ($result && $result->num_rows > 0) {
            return $result->fetch_assoc();
        }
        return null;
    }

    public function fetchAll($sql, $params = [], $types = '') {
        $result = $this->query($sql, $params, $types);
        $data = [];
        if ($result) {
            while ($row = $result->fetch_assoc()) {
                $data[] = $row;
            }
        }
        return $data;
    }

    public function insert($table, $data) {
        $columns = implode(', ', array_keys($data));
        $placeholders = implode(', ', array_fill(0, count($data), '?'));
        $sql = "INSERT INTO {$table} ({$columns}) VALUES ({$placeholders})";

        $values = array_values($data);
        return $this->execute($sql, $values);
    }

    public function update($table, $data, $where, $whereParams = []) {
        $set = [];
        foreach (array_keys($data) as $key) {
            $set[] = "{$key} = ?";
        }
        $setClause = implode(', ', $set);

        $sql = "UPDATE {$table} SET {$setClause} WHERE {$where}";
        $values = array_merge(array_values($data), $whereParams);

        return $this->execute($sql, $values);
    }

    public function delete($table, $where, $params = []) {
        $sql = "DELETE FROM {$table} WHERE {$where}";
        return $this->execute($sql, $params);
    }

    public function getLastError() {
        return $this->lastError;
    }

    public function escape($string) {
        return $this->connection->real_escape_string($string);
    }

    public function beginTransaction() {
        return $this->connection->begin_transaction();
    }

    public function commit() {
        return $this->connection->commit();
    }

    public function rollback() {
        return $this->connection->rollback();
    }

    private function getTypes($params) {
        $types = '';
        foreach ($params as $param) {
            if (is_int($param)) {
                $types .= 'i';
            } elseif (is_double($param) || is_float($param)) {
                $types .= 'd';
            } else {
                $types .= 's';
            }
        }
        return $types;
    }

    public function __destruct() {
        if ($this->connection) {
            $this->connection->close();
        }
    }
}