<?php
/**
 * ASISMA CRM - Singleton Database Wrapper using PDO
 */
class Database {
    private static $instance = null;
    private $pdo;

    private function __construct() {
        $dsn = "mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";charset=" . DB_CHARSET;
        $options = array(
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,
        );

        try {
            $this->pdo = new PDO($dsn, DB_USER, DB_PASS, $options);
            $this->pdo->exec("SET NAMES 'utf8mb4'");
            $this->pdo->exec("SET time_zone = '-04:00'");
        } catch (\PDOException $e) {
            error_log("Database connection failed: " . $e->getMessage());
            die(json_encode(array(
                'success' => false,
                'error'   => true,
                'message' => 'Error de conexión con la base de datos.'
            )));
        }
    }

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

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

    public function query($sql, $params = array()) {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt;
    }

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

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

    public function insert($table, $data) {
        $fields       = array_keys($data);
        $placeholders = array_map(function($field) { return ":$field"; }, $fields);

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

        $this->query($sql, $data);
        return $this->pdo->lastInsertId();
    }

    public function update($table, $data, $whereClause, $whereParams = array()) {
        $setParts = array();
        $params   = array();

        foreach ($data as $field => $val) {
            $setParts[] = "`{$field}` = :update_{$field}";
            $params["update_{$field}"] = $val;
        }

        foreach ($whereParams as $key => $val) {
            $params[$key] = $val;
        }

        $sql  = "UPDATE `{$table}` SET " . implode(", ", $setParts) . " WHERE {$whereClause}";
        $stmt = $this->query($sql, $params);
        return $stmt->rowCount();
    }

    public function delete($table, $whereClause, $params = array()) {
        $sql  = "DELETE FROM `{$table}` WHERE {$whereClause}";
        $stmt = $this->query($sql, $params);
        return $stmt->rowCount();
    }

    public function count($table, $whereClause = "1", $params = array()) {
        $sql = "SELECT COUNT(*) as total FROM {$table} WHERE {$whereClause}";
        $res = $this->fetchOne($sql, $params);
        return (int)(isset($res['total']) ? $res['total'] : 0);
    }

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

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

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

    public function rollBack() {
        return $this->pdo->rollBack();
    }
}
