<?php

declare(strict_types=1);

namespace App\Lib;

use PDO;
use PDOException;

class Database
{
    protected string $dbHost;
    protected string $dbUser;
    protected string $dbPass;
    protected string $dbName;

    private static ?Database $instance = null;
    private $db; // Store the Connection object here

    private function __construct()
    {
        $this->dbHost = $_ENV['DB_HOST'];
        $this->dbUser = $_ENV['DB_USER'];
        $this->dbPass = $_ENV['DB_PASS'];
        $this->dbName = $_ENV['DB_NAME'];

        $dsn = "mysql:host={$this->dbHost};dbname={$this->dbName}";

        try {
            $this->db = new \PDO($dsn, $this->dbUser, $this->dbPass);
            $this->db->setAttribute(PDO::ATTR_PERSISTENT, false);
            $this->db->setAttribute(PDO::MYSQL_ATTR_COMPRESS, true);
            $this->db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
            $this->db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, 'SET NAMES "UTF8"');
            $this->db->setAttribute(PDO::MYSQL_ATTR_FOUND_ROWS, true);
            $this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // Disable emulation of prepared statements
            $this->db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ); // Fetch results as objects
            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            die("Connection failed: " . $e->getMessage());
        }
    }

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

        return self::$instance;
    }

    public function query($sql, ...$params)
    {
        // Check if the query has WHERE conditions with an associative array
        if (strpos($sql, 'WHERE') !== false && is_array($params[0] ?? null)) {
            $whereConditions = [];
            foreach ($params[0] as $key => $value) {
                if (is_array($value)) {
                    // For arrays, handle enumeration (IN) or NOT IN conditions
                    $whereInValues = implode(', ', array_fill(0, count($value), '?'));
                    $whereConditions[] = "$key IN ($whereInValues)";
                } else {
                    // Handle other conditions
                    $whereConditions[] = "$key = ?";
                }
            }
            $whereClause = implode(' AND ', $whereConditions);

            $sql = str_replace('WHERE', "WHERE $whereClause", $sql);
            $params = array_values($params[0]);
        }

        // Check if the query has += or -= operations
        if (strpos($sql, '+=') !== false || strpos($sql, '-=') !== false) {
            $setClause = '';
            foreach ($params[0] as $column => $value) {
                if (strpos($column, '+=') !== false) {
                    $column = str_replace('+=', '', $column);
                    $setClause .= "$column = $column + ?, ";
                } elseif (strpos($column, '-=') !== false) {
                    $column = str_replace('-=', '', $column);
                    $setClause .= "$column = $column - ?, ";
                }
            }

            $setClause = rtrim($setClause, ', ');

            $sql = str_replace('SET', "SET $setClause", $sql);
            $params = array_values($params[0]);
        }

        // Check if the query is an INSERT with an associative array
        if (strpos($sql, 'INSERT') !== false && is_array($params[0])) {
            $columns = implode(', ', array_keys($params[0][0] ?? $params[0])); // Assuming all rows have the same columns
            $values = [];
            $paramsFlat = [];
    
            foreach ($params as $row) {
                // \var_dump($row);
                // exit;
                $placeholders = rtrim(str_repeat('?, ', count($row)), ', ');
                $values[] = "($placeholders)";
                $paramsFlat = array_merge($paramsFlat, array_values($row));
            }
    
            $valuesStr = implode(', ', $values);
            $sql = "$sql ($columns) VALUES $valuesStr";
            $params = $paramsFlat;
        }

        
        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }

    public function fetch($sql, ...$params)
    {
        $stmt = $this->query($sql, ...$params);
        return $stmt->fetch();
    }

    public function fetchAll($sql, ...$params)
    {
        $stmt = $this->query($sql, ...$params);
        return $stmt->fetchAll();
    }

    public function getInsertId()
    {
        return $this->db->lastInsertId();
    }

    public function quote($value)
    {
        $escapedText =  $this->db->quote($value);
        $escapedValue = substr($escapedText, 1, -1);

        return $escapedValue;
    }

    public function transaction($callback)
    {
        try {
            $this->db->beginTransaction();
            $result = $callback($this);
            $this->db->commit();
            return $result;
        } catch (PDOException $e) {
            $this->db->rollBack();
            throw $e;
        }
    }

    public function closeConnection()
    {
        $this->db = null;
    }
}
