<?php
require_once __DIR__ . '/../config.php';

class DB {
    private static ?DB $instance = null;
    public mysqli $conn;

    private function __construct() {
        $this->conn = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
        if ($this->conn->connect_error) {
            http_response_code(500);
            die(json_encode(['error' => 'Database connection failed: ' . $this->conn->connect_error]));
        }
        $this->conn->set_charset('utf8mb4');
    }

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

    /** Execute a query and return the result */
    public function query(string $sql): mysqli_result|bool {
        $result = $this->conn->query($sql);
        if ($result === false) throw new Exception('DB Error: ' . $this->conn->error . ' | SQL: ' . $sql);
        return $result;
    }

    /** Prepared statement - returns rows as assoc array */
    public function select(string $sql, string $types = '', ...$params): array {
        $stmt = $this->conn->prepare($sql);
        if (!$stmt) throw new Exception('Prepare failed: ' . $this->conn->error);
        if ($types && $params) $stmt->bind_param($types, ...$params);
        $stmt->execute();
        $result = $stmt->get_result();
        $rows = [];
        while ($row = $result->fetch_assoc()) $rows[] = $row;
        $stmt->close();
        return $rows;
    }

    /** Prepared statement - returns single row */
    public function selectOne(string $sql, string $types = '', ...$params): ?array {
        $rows = $this->select($sql, $types, ...$params);
        return $rows[0] ?? null;
    }

    /** Prepared insert/update/delete - returns insert_id or affected_rows */
    public function execute(string $sql, string $types = '', ...$params): int {
        $stmt = $this->conn->prepare($sql);
        if (!$stmt) throw new Exception('Prepare failed: ' . $this->conn->error);
        if ($types && $params) $stmt->bind_param($types, ...$params);
        $stmt->execute();
        $id = $stmt->insert_id ?: $stmt->affected_rows;
        $stmt->close();
        return $id;
    }

    /** Escape for raw queries */
    public function esc(string $val): string {
        return $this->conn->real_escape_string($val);
    }
}