<?php

if (!function_exists('isSessionExpiredMiddleware')) {
    function isSessionExpiredMiddleware()
    {
        if (session_status() === PHP_SESSION_NONE) session_start();

        // for debuging the blank screen issue
        // if (isset($_COOKIE['remember_me'])) {
        //     $encryptedSession = $_COOKIE['remember_me'];
        //     $sessionData = json_decode(base64_decode($encryptedSession), true);
        //     if (!empty($sessionData)) {
        //         $_SESSION = $sessionData;
        //         return false;
        //     }
        // }

        if (empty($_SESSION["loginTime"]) || empty($_SESSION["logout_duration"])) {
            return false;
        }
        $currentTime = new DateTime();
        $difference = ($currentTime->getTimestamp() - $_SESSION["loginTime"]) * 1000;
        if ($difference > $_SESSION['logout_duration']) {
            if (!empty($_SERVER['HTTP_X_REQUESTED_WITH'])) {
                sendJsonResponse(401, ["error" => "Timeout logout, Unauthorized"]);
                exit;
            } else {
                if (!headers_sent()) {
                    header("location: ../../login.php?error=Timeout-logout");
                }
                exit();
            }
        }
        return false;
    }
}

// require realpath(dirname(__FILE__) . '../../db.php');
global $pdo;
/**
 * Fetch data with a specific ID from a table.
 * @param string $table The name of the table to query.
 * @param int $id The ID of the record to fetch.
 * @return array|false The fetched record as an associative array, or false on failure.
 */
if (!function_exists('fetchDataWithId')) {
    function fetchDataWithId($table, $id)
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();
            $sql = "SELECT * FROM $table WHERE id = :id";
            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }
            $stmt = $pdo->prepare($sql);
            $stmt->bindParam(':id', $id, PDO::PARAM_INT);
            $stmt->execute();
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            return $result;
        } catch (PDOException $e) {
            return  $e->getMessage();
        }
    }
}

/**
 * Fetch data with a specific ID from a table.
 *
 * @param string $table The name of the table to query.
 * @param array $conditions params for fetching data
 * @return array|false The fetched record as an associative array, or false on failure.
 */
if (!function_exists('fetchData')) {
    function fetchData($table, $conditions = [])
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();
            $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $table);
            $whereClause = '';
            if (!empty($conditions)) {
                $whereParts = [];
                foreach ($conditions as $column => $value) {
                    if (is_null($value)) {
                        $whereParts[] = "$column IS NULL";
                    } elseif (is_array($value) && isset($value[0]) && strtoupper($value[0]) === 'IS NOT NULL') {
                        $whereParts[] = "$column IS NOT NULL";
                    } else {
                        $whereParts[] = "$column = :$column";
                    }
                }
                $whereClause = 'WHERE ' . implode(' AND ', $whereParts);
            }

            // Prepare the SQL statement
            $sql = "SELECT * FROM $table $whereClause";
            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }
            $stmt = $pdo->prepare($sql);

            // Bind the parameters
            foreach ($conditions as $column => $value) {
                if (!is_null($value) && !(is_array($value) && strtoupper($value[0]) === 'IS NOT NULL')) {
                    $stmt->bindValue(":$column", $value);
                }
            }

            // Execute the statement
            $stmt->execute();

            // Fetch the results
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            return $result;
        } catch (PDOException $e) {
            throw new Exception("Error inserting data: " . $e->getMessage());
        }
    }
}

/**
 * Fetch data as a single object with a specific ID from a table.
 *
 * @param string $table The name of the table to query.
 * @param array $conditions params for fetching data
 * @return array|false The fetched record as a single object, or false on failure.
 */
if (!function_exists('fetchDataSingle')) {
    function fetchDataSingle($table, $conditions = [])
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();
            $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $table);
            $whereClause = '';
            if (!empty($conditions)) {
                $whereParts = [];
                foreach ($conditions as $column => $value) {
                    if (is_null($value)) {
                        $whereParts[] = "$column IS NULL";
                    } elseif (is_array($value) && isset($value[0]) && strtoupper($value[0]) === 'IS NOT NULL') {
                        $whereParts[] = "$column IS NOT NULL";
                    } else {
                        $whereParts[] = "$column = :$column";
                    }
                }
                $whereClause = 'WHERE ' . implode(' AND ', $whereParts);
            }

            // Prepare the SQL statement
            $sql = "SELECT * FROM $table $whereClause";
            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }
            $stmt = $pdo->prepare($sql);

            // Bind the parameters
            foreach ($conditions as $column => $value) {
                if (!is_null($value) && !(is_array($value) && strtoupper($value[0]) === 'IS NOT NULL')) {
                    $stmt->bindValue(":$column", $value);
                }
            }

            // Execute the statement
            $stmt->execute();

            // Fetch the results
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            return $result;
        } catch (PDOException $e) {
            throw new Exception("Error inserting data: " . $e->getMessage());
        }
    }
}

if (!function_exists('fetchDataSingleDesc')) {
    function fetchDataSingleDesc($table, $conditions = [])
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();
            $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $table);
            $whereClause = '';
            if (!empty($conditions)) {
                $whereParts = [];
                foreach ($conditions as $column => $value) {
                    if (is_null($value)) {
                        $whereParts[] = "$column IS NULL";
                    } elseif (is_array($value) && isset($value[0]) && strtoupper($value[0]) === 'IS NOT NULL') {
                        $whereParts[] = "$column IS NOT NULL";
                    } else {
                        $whereParts[] = "$column = :$column";
                    }
                }
                $whereClause = 'WHERE ' . implode(' AND ', $whereParts);
            }

            $sql = "SELECT * FROM $table $whereClause ORDER BY id DESC LIMIT 1";

            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }
            $stmt = $pdo->prepare($sql);

            // Bind the parameters
            foreach ($conditions as $column => $value) {
                if (!is_null($value) && !(is_array($value) && strtoupper($value[0]) === 'IS NOT NULL')) {
                    $stmt->bindValue(":$column", $value);
                }
            }

            $stmt->execute();
            return $stmt->fetch(PDO::FETCH_ASSOC);

        } catch (PDOException $e) {
            throw new Exception("Error fetching data: " . $e->getMessage());
        }
    }
}


if (!function_exists('fetchOrDataSingle')) {
    function fetchOrDataSingle($table, $conditions = [])
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();
            $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $table);
            $whereClause = '';
            if (!empty($conditions)) {
                $whereParts = [];
                foreach ($conditions as $column => $value) {
                    if (is_null($value)) {
                        $whereParts[] = "$column IS NULL";
                    } elseif (is_array($value) && isset($value[0]) && strtoupper($value[0]) === 'IS NOT NULL') {
                        $whereParts[] = "$column IS NOT NULL";
                    } else {
                        $whereParts[] = "$column = :$column";
                    }
                }
                $whereClause = 'WHERE ' . implode(' OR ', $whereParts);
            }

            // Prepare the SQL statement
            $sql = "SELECT * FROM $table $whereClause";
            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }
            $stmt = $pdo->prepare($sql);

            // Bind the parameters
            foreach ($conditions as $column => $value) {
                if (!is_null($value) && !(is_array($value) && strtoupper($value[0]) === 'IS NOT NULL')) {
                    $stmt->bindValue(":$column", $value);
                }
            }

            // Execute the statement
            $stmt->execute();

            // Fetch the results
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            return $result;
        } catch (PDOException $e) {
            throw new Exception("Error inserting data: " . $e->getMessage());
        }
    }
}

if (!function_exists('fetchAllDataByOperator')) {
    function fetchAllDataByOperator($table, $conditions = [])
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();
            $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $table);
            $whereClause = '';
            if (!empty($conditions)) {
                $whereParts = [];
                foreach ($conditions as $column => $value) {
                    // Check if column contains an operator (like <= or >=)
                    if (preg_match('/\s*(<=|>=|<|>|!=)\s*$/', $column, $matches)) {
                        $operator = $matches[1];
                        $column = trim(str_replace($operator, '', $column));
                        $whereParts[] = "$column $operator :$column";
                    } elseif (is_null($value)) {
                        $whereParts[] = "$column IS NULL";
                    } elseif (is_array($value) && isset($value[0]) && strtoupper($value[0]) === 'IS NOT NULL') {
                        $whereParts[] = "$column IS NOT NULL";
                    } else {
                        $whereParts[] = "$column = :$column";
                    }
                }
                $whereClause = 'WHERE ' . implode(' AND ', $whereParts);
            }

            // Prepare the SQL statement
            $sql = "SELECT * FROM $table $whereClause";
            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }
            $stmt = $pdo->prepare($sql);

            // Bind the parameters
            foreach ($conditions as $column => $value) {
                if (preg_match('/\s*(<=|>=|<|>|!=)\s*$/', $column)) {
                    $column = trim(preg_replace('/\s*(<=|>=|<|>|!=)\s*$/', '', $column));
                }
                if (!is_null($value) && !(is_array($value) && strtoupper($value[0]) === 'IS NOT NULL')) {
                    $stmt->bindValue(":$column", $value);
                }
            }

            // Execute the statement
            $stmt->execute();

            // Fetch the results
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            return $result;
        } catch (PDOException $e) {
            throw new Exception("Error fetching data: " . $e->getMessage());
        }
    }
}

if (!function_exists('fetchDataByOperatorSingle')) {
    function fetchDataByOperatorSingle($table, $conditions = [])
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();
            $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $table);
            $whereClause = '';
            if (!empty($conditions)) {
                $whereParts = [];
                foreach ($conditions as $column => $value) {
                    // Check if column contains an operator (like <= or >=)
                    if (preg_match('/\s*(<=|>=|<|>|!=)\s*$/', $column, $matches)) {
                        $operator = $matches[1];
                        $column = trim(str_replace($operator, '', $column));
                        $whereParts[] = "$column $operator :$column";
                    } elseif (is_null($value)) {
                        $whereParts[] = "$column IS NULL";
                    } elseif (is_array($value) && isset($value[0]) && strtoupper($value[0]) === 'IS NOT NULL') {
                        $whereParts[] = "$column IS NOT NULL";
                    } else {
                        $whereParts[] = "$column = :$column";
                    }
                }
                $whereClause = 'WHERE ' . implode(' AND ', $whereParts);
            }

            $sql = "SELECT * FROM $table $whereClause ORDER BY id DESC";

            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }
            $stmt = $pdo->prepare($sql);

            // Bind the parameters
            foreach ($conditions as $column => $value) {
                if (preg_match('/\s*(<=|>=|<|>|!=)\s*$/', $column)) {
                    $column = trim(preg_replace('/\s*(<=|>=|<|>|!=)\s*$/', '', $column));
                }
                if (!is_null($value) && !(is_array($value) && strtoupper($value[0]) === 'IS NOT NULL')) {
                    $stmt->bindValue(":$column", $value);
                }
            }

            $stmt->execute();

            // Right now you are fetching **only one row**
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            return $result;

        } catch (PDOException $e) {
            throw new Exception("Error fetching data: " . $e->getMessage());
        }
    }
}

if (!function_exists('fetchDataByOperator')) {
    function fetchDataByOperator($table, $conditions = [])
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();
            $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $table);
            $whereClause = '';
            if (!empty($conditions)) {
                $whereParts = [];
                foreach ($conditions as $column => $value) {
                    // Check if column contains an operator (like <= or >=)
                    if (preg_match('/\s*(<=|>=|<|>|!=)\s*$/', $column, $matches)) {
                        $operator = $matches[1];
                        $column = trim(str_replace($operator, '', $column));
                        $whereParts[] = "$column $operator :$column";
                    } elseif (is_null($value)) {
                        $whereParts[] = "$column IS NULL";
                    } elseif (is_array($value) && isset($value[0]) && strtoupper($value[0]) === 'IS NOT NULL') {
                        $whereParts[] = "$column IS NOT NULL";
                    } else {
                        $whereParts[] = "$column = :$column";
                    }
                }
                $whereClause = 'WHERE ' . implode(' AND ', $whereParts);
            }

            // Prepare the SQL statement
            $sql = "SELECT * FROM $table $whereClause";
            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }
            $stmt = $pdo->prepare($sql);

            // Bind the parameters
            foreach ($conditions as $column => $value) {
                if (preg_match('/\s*(<=|>=|<|>|!=)\s*$/', $column)) {
                    $column = trim(preg_replace('/\s*(<=|>=|<|>|!=)\s*$/', '', $column));
                }
                if (!is_null($value) && !(is_array($value) && strtoupper($value[0]) === 'IS NOT NULL')) {
                    $stmt->bindValue(":$column", $value);
                }
            }

            // Execute the statement
            $stmt->execute();

            // Fetch the results
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            return $result;
        } catch (PDOException $e) {
            throw new Exception("Error fetching data: " . $e->getMessage());
        }
    }
}

if (!function_exists('fetchDataByOperatorAll')) {
    function fetchDataByOperatorAll($table, $conditions = [])
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();

            // sanitize table name
            $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $table);
            $whereClause = '';
            $params = [];

            if (!empty($conditions)) {
                $whereParts = [];
                foreach ($conditions as $column => $value) {
                    if (preg_match('/\s*(<=|>=|<|>|!=)\s*$/', $column, $matches)) {
                        $operator = $matches[1];
                        $column   = trim(str_replace($operator, '', $column));

                        // create a unique param name (e.g. created_at_gte, created_at_lte)
                        $suffix = match ($operator) {
                            '>=' => 'gte',
                            '<=' => 'lte',
                            '>'  => 'gt',
                            '<'  => 'lt',
                            '!=' => 'neq',
                            default => 'val'
                        };
                        $paramName = $column . '_' . $suffix;

                        $whereParts[] = "$column $operator :$paramName";
                        $params[$paramName] = $value;

                    } elseif (is_null($value)) {
                        $whereParts[] = "$column IS NULL";
                    } elseif (is_array($value) && isset($value[0]) && strtoupper($value[0]) === 'IS NOT NULL') {
                        $whereParts[] = "$column IS NOT NULL";
                    } else {
                        $whereParts[] = "$column = :$column";
                        $params[$column] = $value;
                    }
                }
                $whereClause = 'WHERE ' . implode(' AND ', $whereParts);
            }

            // final SQL
            $sql = "SELECT * FROM $table $whereClause ORDER BY id DESC";

            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }

            $stmt = $pdo->prepare($sql);

            // bind all params
            foreach ($params as $key => $val) {
                $stmt->bindValue(":$key", $val);
            }

            $stmt->execute();
            return $stmt->fetchAll(PDO::FETCH_ASSOC);

        } catch (PDOException $e) {
            throw new Exception("Error fetching data: " . $e->getMessage());
        }
    }
}


//

/**
 * Update a row in a given table.
 *
 * @param PDO $pdo The PDO connection object.
 * @param string $table The name of the table to update.
 * @param array $data An associative array of columns and their values to update.
 * @param string $where A string representing the WHERE clause (without the 'WHERE' keyword).
 * @param array $params An associative array of parameters for the WHERE clause.
 * @return bool Returns true on success, false on failure.
 */
if (!function_exists('updateTable')) {
    function updateTable($table, array $data, $where, array $params = [])
    {
        global $pdo;
        try {
            // Build the SET part of the SQL query
            isSessionExpiredMiddleware();
            $setPart = [];
            foreach ($data as $column => $value) {
                $setPart[] = "$column = :$column";
            }
            $setPart = implode(', ', $setPart);

            // Prepare the SQL statement
            $sql = "UPDATE $table SET $setPart WHERE $where";
            if (!$pdo) {
                require realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }
            $stmt = $pdo->prepare($sql);

            // Bind the values to the statement
            foreach ($data as $column => &$value) {
                $stmt->bindParam(":$column", $value);
            }

            // Bind the parameters for the WHERE clause
            foreach ($params as $param => &$value) {
                $stmt->bindParam(":$param", $value);
            }

            // Execute the statement
            return $stmt->execute();
        } catch (PDOException $e) {
            throw new Exception("Error inserting data: " . $e->getMessage());
        }
    }
}
if (!function_exists('insertData')) {
    function insertData($table, $data)
    {
        global $pdo;
        try {
            // Sanitize the table name to prevent SQL injection
            isSessionExpiredMiddleware();
            $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $table);

            // Prepare columns and values for the SQL statement
            $columns = implode(", ", array_keys($data));
            $placeholders = implode(", ", array_map(fn($key) => ":$key", array_keys($data)));

            // Prepare the SQL statement
            $sql = "INSERT INTO $table ($columns) VALUES ($placeholders)";
            if (!$pdo) {
                require realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }
            $stmt = $pdo->prepare($sql);

            // Bind the parameters
            foreach ($data as $key => $value) {
                // Sanitize input data if necessary, handle null values
                $sanitizedValue = is_null($value) ? null : htmlspecialchars($value, ENT_QUOTES, 'UTF-8');
                $stmt->bindValue(":$key", $sanitizedValue);
            }

            // Execute the statement
            if ($stmt->execute()) {
                $data['id'] = $pdo->lastInsertId();
                return $data;
            } else {
                return false;
            }
        } catch (\Throwable $th) {
            // Handle the exception
            throw new Exception("Error inserting data: " . $th->getMessage());
        }
    }
}

if (!function_exists('getLastInsertId')) {
    function getLastInsertId()
    {
        global $pdo;
        try {
            
            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }

            return $pdo->lastInsertId();
        } catch (PDOException $e) {
            throw new Exception("Error getting last insert ID: " . $e->getMessage());
        }
    }
}


/**
 * Delete a row in a given table.
 *
 * @param string $table The name of the table to update.
 * @param string $id Id for deleting
 * @return bool Returns true on success, Exception on failure.
 */

if (!function_exists('deleteData')) {
    function deleteData($table, $column, $value)
    {
        global $pdo;
        try {
            // Sanitize the table and column names to prevent SQL injection
            isSessionExpiredMiddleware();
            $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $table);
            $column = preg_replace('/[^a-zA-Z0-9_]+/', '', $column);

            // Prepare the SQL statement
            $sql = "DELETE FROM $table WHERE $column = :value";

            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }

            $stmt = $pdo->prepare($sql);

            // Bind the value parameter
            $stmt->bindParam(':value', $value, PDO::PARAM_STR);

            $stmt->execute();
            return $stmt->rowCount() > 0; // Returns true if the delete was successful
        } catch (PDOException $e) {
            throw new Exception("Error deleting data: " . $e->getMessage());
        }
    }
}

if (!function_exists('deleteDataAdvanced')) {
    function deleteDataAdvanced($table, $conditions)
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();

            $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $table);
            $whereParts = [];
            foreach ($conditions as $column => $value) {
                $column = preg_replace('/[^a-zA-Z0-9_]+/', '', $column);
                $whereParts[] = "$column = :$column";
            }

            $whereClause = implode(' AND ', $whereParts);
            $sql = "DELETE FROM $table WHERE $whereClause";

            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }

            $stmt = $pdo->prepare($sql);
            foreach ($conditions as $column => $value) {
                $stmt->bindValue(":$column", $value);
            }

            $stmt->execute();
            return $stmt->rowCount() > 0;
        } catch (PDOException $e) {
            throw new Exception("Error deleting data: " . $e->getMessage());
        }
    }
}

/**
 * Fetch data with a specific ID from a table.
 * @param string $table The name of the table to query.
 * @param int $id The ID of the record to fetch.
 * @return array|false The fetched record as an associative array, or false on failure.
 */
if (!function_exists('fetchDataWithJoins')) {
    function fetchDataWithJoins($sql, $params = [])
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();
            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }
            $stmt = $pdo->prepare($sql);

            // Bind the parameters
            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value);
            }

            // Execute the statement
            $stmt->execute();

            // Fetch the results
            $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
            return $result;
        } catch (PDOException $e) {
            throw new Exception("Error executing query: " . $e->getMessage());
        }
    }
}

/**
 * Fetch data with a specific ID from a table.
 * @param string $table The name of the table to query.
 * @param int $id The ID of the record to fetch.
 * @return Object|false The fetched record as an Object, or false on failure.
 */
if (!function_exists('fetchSingleDataWithJoins')) {
    function fetchSingleDataWithJoins($sql, $params = [])
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();
            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }
            $stmt = $pdo->prepare($sql);

            // Bind the parameters
            foreach ($params as $key => $value) {
                $stmt->bindValue($key, $value);
            }

            // Execute the statement
            $stmt->execute();

            // Fetch the results
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            return $result;
        } catch (PDOException $e) {
            throw new Exception("Error executing query: " . $e->getMessage());
        }
    }
}
// moved to auth_functions
// if (!function_exists('softDeleteUser')) {

//     function softDeleteUser($userid, $property_id)
//     {
//         global $pdo;
//         try {
//             isSessionExpiredMiddleware();
//             $tables = [
//                 ['table' => 'audit_trail_login', 'column' => 'property_user_id'],
//                 ['table' => 'property_users_data', 'column' => 'property_user'],
//                 ['table' => 'profile_photo', 'column' => 'user_id'],
//                 ['table' => 'property_users', 'column' => 'id']
//             ];

//             if (!$pdo) {
//                 require_once realpath(dirname(__FILE__) . '/../../env.php');
//                 require_once realpath(dirname(__FILE__) . '/../../db.php');
//                 $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
//             }

//             $pdo->beginTransaction();

//             $deleted_at = date('Y-m-d H:i:s');

//             foreach ($tables as $tableInfo) {
//                 $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $tableInfo['table']);
//                 $column = preg_replace('/[^a-zA-Z0-9_]+/', '', $tableInfo['column']);

//                 if ($table === 'profile_photo') {
//                     $sql = "UPDATE $table SET deleted_at = :deleted_at WHERE $column = :userid";
//                     $stmt = $pdo->prepare($sql);
//                     $stmt->bindParam(':userid', $userid, PDO::PARAM_INT);
//                 } else {
//                     $sql = "UPDATE $table SET deleted_at = :deleted_at WHERE $column = :userid AND property_id = :property_id";
//                     $stmt = $pdo->prepare($sql);
//                     $stmt->bindParam(':userid', $userid, PDO::PARAM_INT);
//                     $stmt->bindParam(':property_id', $property_id, PDO::PARAM_INT);
//                 }

//                 $stmt->bindParam(':deleted_at', $deleted_at, PDO::PARAM_STR);
//                 $stmt->execute();

//                 logAudit($_SESSION['userid'], 'delete', $table, $userid, $property_id, null, $deleted_at, "Soft delete for user $userid in table $table");
//             }

//             $pdo->commit();
//             return true;
//         } catch (PDOException $e) {
//             if ($pdo->inTransaction()) {
//                 $pdo->rollBack();
//             }
//             throw new Exception("Error soft deleting user: " . $e->getMessage());
//         }
//     }
// }

if (!function_exists('restoreUser')) {
    function restoreUser($userid, $property_id)
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();
            $tables = [
                ['table' => 'audit_trail_login', 'column' => 'property_user_id'],
                ['table' => 'property_users_data', 'column' => 'property_user'],
                ['table' => 'profile_photo', 'column' => 'user_id'],
                ['table' => 'property_users', 'column' => 'id']
            ];

            if (!$pdo) {
                require_once realpath(dirname(__FILE__) . '/../../env.php');
                require_once realpath(dirname(__FILE__) . '/../../db.php');
                $pdo = getDatabaseConnection($host, $dbname, $username, $dbpassword, $port);
            }

            $pdo->beginTransaction();

            foreach ($tables as $tableInfo) {
                $table = preg_replace('/[^a-zA-Z0-9_]+/', '', $tableInfo['table']);
                $column = preg_replace('/[^a-zA-Z0-9_]+/', '', $tableInfo['column']);

                if ($table === 'profile_photo') {
                    $sql = "UPDATE $table SET deleted_at = NULL WHERE $column = :userid";
                    $stmt = $pdo->prepare($sql);
                    $stmt->bindParam(':userid', $userid, PDO::PARAM_INT);
                } else {
                    $sql = "UPDATE $table SET deleted_at = NULL WHERE $column = :userid AND property_id = :property_id";
                    $stmt = $pdo->prepare($sql);
                    $stmt->bindParam(':userid', $userid, PDO::PARAM_INT);
                    $stmt->bindParam(':property_id', $property_id, PDO::PARAM_INT);
                }

                $stmt->execute();

                logAudit($_SESSION['userid'], 'restore', $table, $userid, $property_id, "Soft deleted timestamp", null, "Restore user $userid in table $table");
            }

            $pdo->commit();
            return true;
        } catch (PDOException $e) {
            if ($pdo->inTransaction()) {
                $pdo->rollBack();
            }
            throw new Exception("Error restoring user: " . $e->getMessage());
        }
    }
}

if (!function_exists('sendJsonResponse')) {
    function sendJsonResponse($statusCode, $data)
    {
        http_response_code($statusCode);
        echo json_encode($data);
        exit;
    }
}

if (!function_exists('logAudit')) {
    function logAudit($user_id, $action, $table_name, $record_id, $property_id = null, $old_value = null, $new_value = null, $additional_info = null)
    {
        global $pdo;
        try {
            isSessionExpiredMiddleware();
            $sql = "INSERT INTO audit_logs (user_id, action, table_name, record_id, property_id, old_value, new_value, created_at, additional_info) 
                    VALUES (:user_id, :action, :table_name, :record_id, :property_id, :old_value, :new_value, NOW(), :additional_info)";

            $stmt = $pdo->prepare($sql);
            $stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            $stmt->bindParam(':action', $action, PDO::PARAM_STR);
            $stmt->bindParam(':table_name', $table_name, PDO::PARAM_STR);
            $stmt->bindParam(':record_id', $record_id, PDO::PARAM_INT);
            $stmt->bindParam(':property_id', $property_id, PDO::PARAM_INT);
            $stmt->bindParam(':old_value', $old_value, PDO::PARAM_STR);
            $stmt->bindParam(':new_value', $new_value, PDO::PARAM_STR);
            $stmt->bindParam(':additional_info', $additional_info, PDO::PARAM_STR);

            $stmt->execute();
        } catch (PDOException $e) {
            throw new Exception("Error logging audit: " . $e->getMessage());
        }
    }
}



/**
 * Fetch data with a specific ID from a table.
 * @param string $action Action should be Login,Create,Update,Delete,Command.
 * @param int $table_name Name of the table where the log is occurring.
 * @param int $log_level log levels are INFO  WARNING ERROR CRITICAL 
 * @return Object|false The fetched record as an Object, or false on failure.
 */

if (!function_exists('createAuditLog')) {
    function createAuditLog($user_id, $action, $table_name, $record_id, $user_email, $property_id = null, $old_value = null, $new_value = null, $additional_info = null, $log_level = "INFO",$module_name=null)
    {
        try {
            $utcTime = new DateTime('now', new DateTimeZone('UTC'));
            $utcTimeString = $utcTime->format('Y-m-d\TH:i:s\Z');
            $dataForLogs = [
                'user_id' => $user_id,
                'user_email' => $user_email,
                'action' => $action,
                'table_name' => $table_name,
                'record_id' => $record_id,
                'property_id' => $property_id,
                'old_value' => $old_value,
                'new_value' => $new_value,
                'ip_address' => $_SERVER['REMOTE_ADDR'] ?? null, // Captures the IP address if available
                'module_name' => $module_name,
                'log_level' => $log_level,
                'timestamp' => $utcTimeString,
                'created_at' => date('Y-m-d H:i:s'), // Current timestamp
                'additional_info' => $additional_info,
            ];
    
            insertData('audit_logs', $dataForLogs);

            return [
                'success' => true,
                'message' => 'Audit log created successfully.',
            ];
        } catch (\Throwable $th) {
            throw new Exception('Failed to create audit log: ' . $th->getMessage(), $th->getCode(), $th);
        }
    }
}


if (!function_exists('uploadToAllImages')) {
    function uploadToAllImages($fileInput, $propertyId,$targetDirBase = null)
    {
        if ($targetDirBase === null) {
            $targetDirBase = '../../../../../data/all_images/';
        }

        if (!isset($_FILES[$fileInput]) || $_FILES[$fileInput]['error'] !== 0) {
            return ["success" => false, "error" => "No file uploaded or upload error."];
        }
    
        $allowedMimeTypes = ['image/jpeg', 'image/png', 'image/gif'];
        $fileMimeType = $_FILES[$fileInput]['type'];
    
        if (!in_array($fileMimeType, $allowedMimeTypes)) {
            return ["success" => false, "error" => "Invalid image type. Only JPG, PNG, and GIF are allowed."];
        }
    
        $maxFileSize = 4 * 1024 * 1024;
        if ($_FILES[$fileInput]['size'] > $maxFileSize) {
            return ["success" => false, "error" => "File size exceeds the 4MB limit."];
        }
    
        $propertyFolder = $targetDirBase . 'property_' . $propertyId;
    
        if (!file_exists($propertyFolder)) {
            if (!mkdir($propertyFolder, 0777, true)) {
                return ["success" => false, "error" => "Failed to create property folder."];
            }
        }
        $extension = pathinfo($_FILES[$fileInput]['name'], PATHINFO_EXTENSION) ?: 'png';
        $imageFileName = 'image_' . time() . '.' . $extension;
        $targetFilePath = $propertyFolder . '/' . $imageFileName;
    
        if (!move_uploaded_file($_FILES[$fileInput]['tmp_name'], $targetFilePath)) {
            return ["success" => false, "error" => "Failed to upload the image."];
        }
    
        $imagePath = 'property_' . $propertyId . '/' . $imageFileName;
    
        return ["success" => true, "image_path" => $imagePath];
    }
}

if (!function_exists('deleteSingleImageFromAllImages')) {
    function deleteSingleImageFromAllImages($imagePath)
    {
        // Check if imagePath is empty or null
        if (empty($imagePath)) {
            return ["success" => true, "message" => "No image path provided."];
        }
        
        $targetDirBase = "../../../../../data/all_images/";
        $fullImagePath = $targetDirBase . $imagePath;
        
        // Check if the path is a directory
        if (is_dir($fullImagePath)) {
            return ["success" => false, "error" => "Path is a directory, not a file."];
        }
        
        // Check if file exists and is actually a file
        if (file_exists($fullImagePath) && is_file($fullImagePath)) {
            if (!unlink($fullImagePath)) {
                return ["success" => false, "error" => "Failed to delete the image file."];
            }
            return ["success" => true, "message" => "Image deleted successfully."];
        }
        
        return ["success" => false, "error" => "Image file not found."];
    }
}

if (!function_exists('fetch_snapshot_any')) {
    function fetch_snapshot_any(string $url, string $user, string $pass): array {
        $tries = [
            ['basic',  false],
            ['basic',  true ],
            ['digest', false],
            ['digest', true ],
        ];

        foreach ($tries as [$mode, $http10]) {
            $ch = curl_init($url);

            $headers = [
                'User-Agent: SnapshotFetcher/1.0',
                'Connection: close',
                'Expect:',
            ];

            $opts = [
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_CONNECTTIMEOUT => 8,
                CURLOPT_TIMEOUT        => 8,
                CURLOPT_SSL_VERIFYPEER => false,
                CURLOPT_SSL_VERIFYHOST => 0,
                CURLOPT_IPRESOLVE      => CURL_IPRESOLVE_V4,
                CURLOPT_HTTPHEADER     => $headers,
            ];

            if ($http10) {
                $opts[CURLOPT_HTTP_VERSION] = CURL_HTTP_VERSION_1_0;
            } else {
                $opts[CURLOPT_HTTP_VERSION] = CURL_HTTP_VERSION_1_1;
            }

            if ($mode === 'basic') {
                $authHeader = 'Authorization: Basic ' . base64_encode($user . ':' . $pass);
                $opts[CURLOPT_HTTPHEADER][] = $authHeader;
                $opts[CURLOPT_USERPWD]      = $user . ':' . $pass;
                $opts[CURLOPT_HTTPAUTH]     = CURLAUTH_BASIC;
            } else {
                $opts[CURLOPT_USERPWD]  = $user . ':' . $pass;
                $opts[CURLOPT_HTTPAUTH] = CURLAUTH_DIGEST;
            }

            curl_setopt_array($ch, $opts);
            $data = curl_exec($ch);
            $code = (int) curl_getinfo($ch, CURLINFO_HTTP_CODE);
            $err  = curl_error($ch);
            curl_close($ch);

            // error_log(sprintf(
            //     "snapshot auth try: %s %s => HTTP %d, bytes=%s, err=%s",
            //     strtoupper($mode),
            //     $http10 ? 'HTTP/1.0' : 'HTTP/1.1',
            //     $code,
            //     $data === false ? 'false' : strlen($data),
            //     $err ?: 'none'
            // ));

            if ($code === 200 && $data !== false && strlen($data) > 100) {
                return [$data, $code, strtoupper($mode) . ' ' . ($http10 ? 'HTTP/1.0' : 'HTTP/1.1')];
            }
        }

        return [null, 0, ''];
    }
}

if (!function_exists('fetch_basic_preemptive')) {
    function fetch_basic_preemptive(string $url, string $user, string $pass): array {
    $ch = curl_init($url);
    $authHeader = 'Authorization: Basic ' . base64_encode($user . ':' . $pass);
    curl_setopt_array($ch, [
        CURLOPT_RETURNTRANSFER => true,
        CURLOPT_CONNECTTIMEOUT => 8,
        CURLOPT_TIMEOUT        => 8,
        CURLOPT_SSL_VERIFYPEER => false,
        CURLOPT_SSL_VERIFYHOST => 0,
        CURLOPT_HTTP_VERSION   => CURL_HTTP_VERSION_1_1,
        CURLOPT_IPRESOLVE      => CURL_IPRESOLVE_V4,
        CURLOPT_HTTPHEADER     => [
            'User-Agent: SnapshotFetcher/1.0',
            'Connection: close',
            'Expect:',
            $authHeader,
        ],

        CURLOPT_USERPWD        => $user . ':' . $pass,
        CURLOPT_HTTPAUTH       => CURLAUTH_BASIC,
    ]);
    $data = curl_exec($ch);
    $code = (int) curl_getinfo($ch, CURLINFO_HTTP_CODE);
    $err  = curl_error($ch);
    curl_close($ch);
    return [$data, $code, $err];
    }
}

if (!function_exists('ensurePropertySnapshotVUser')) {
    function ensurePropertySnapshotVUser(int $propertyId): array {
        require_once __DIR__ . '/../auth/auth_functions.php';

        $env = getEnvVars();
        $propFtpUser = $env['ftp_env'] . '_ftp_prop_' . $propertyId;

        $snap = fetchDataSingle('ftp_credentials', [
            'property_id' => $propertyId,
            'role'        => 'snapshot'
        ]);
        if ($snap && !empty($snap['ftp_username']) && !empty($snap['ftp_password'])) {
            return [
                'user'    => $snap['ftp_username'],
                'pass'    => decryptFtpPassword($snap['ftp_password']),
                'created' => false
            ];
        }

        $serverId = null;
        $camCred  = fetchDataSingle('ftp_credentials', [
            'property_id' => $propertyId,
            'role'        => 'camera'
        ]);
        if ($camCred && !empty($camCred['ftp_server_id'])) {
            $serverId = (int)$camCred['ftp_server_id'];
        } else {
            $anyCred = fetchDataSingle('ftp_credentials', ['property_id' => $propertyId]);
            if ($anyCred && !empty($anyCred['ftp_server_id'])) {
                $serverId = (int)$anyCred['ftp_server_id'];
            }
        }
        if (!$serverId) {
            throw new RuntimeException("Snapshot setup failed: ftp_server_id not found for property {$propertyId}");
        }

        $server = fetchDataSingle('ftp_servers', ['id' => $serverId]);
        if (!$server || empty($server['url'])) {
            throw new RuntimeException("Snapshot setup failed: ftp server URL not found for id {$serverId}");
        }
        $ftpHost = $server['url'];

        $trace = bin2hex(random_bytes(8));
        $cmd = sprintf(
            'ssh -i %s -o IdentitiesOnly=yes -o StrictHostKeyChecking=no -o UserKnownHostsFile=/dev/null -T %s@%s',
            escapeshellarg($env['ftp_snap_key']),
            escapeshellarg($env['ftp_snap_user']),
            escapeshellarg($ftpHost)
        );

        $desc = [ 0 => ['pipe','r'], 1 => ['pipe','w'], 2 => ['pipe','w'] ];
        $proc = proc_open($cmd, $desc, $pipes, null, null, ['bypass_shell' => true]);
        if (!is_resource($proc)) {
            error_log("[$trace] ensureSnapshot: proc_open failed host=$ftpHost");
            throw new RuntimeException("Snapshot setup failed (trace $trace)");
        }

        fwrite($pipes[0], $propFtpUser . ' ' . $env['ftp_env'] . ' ' . $propertyId . "\n");
        fclose($pipes[0]);

        $stdout = stream_get_contents($pipes[1]); fclose($pipes[1]);
        $stderr = stream_get_contents($pipes[2]); fclose($pipes[2]);
        $exit   = proc_close($proc);

        if ($exit !== 0) {
            error_log("[$trace] ensureSnapshot failed (exit=$exit) host=$ftpHost STDERR: $stderr | STDOUT: $stdout");
            throw new RuntimeException("Snapshot setup failed (trace $trace)");
        }

        $vuser = $vpass = null;
        foreach (explode("\n", $stdout) as $l) {
            $l = trim($l);
            if ($l === '') continue;
            if (strpos($l, 'virtual_username:') === 0) {
                $vuser = trim(substr($l, strlen('virtual_username:')));
            } elseif (strpos($l, 'virtual_password:') === 0) {
                $vpass = trim(substr($l, strlen('virtual_password:')));
            }
        }
        
        if (!$vuser) {
            error_log("[$trace] ensureSnapshot: no virtual_username in stdout. STDOUT: $stdout");
            throw new RuntimeException("Snapshot setup failed (trace $trace)");
        }

        $existing = fetchDataSingle('ftp_credentials', [
            'property_id' => $propertyId,
            'role'        => 'snapshot'
        ]);

        $payload = [
            'property_id'   => $propertyId,
            'role'          => 'snapshot',
            'ftp_server_id' => $serverId,
            'ftp_username'  => $vuser
        ];
        if ($vpass) {
            $payload['ftp_password'] = encryptFtpPassword($vpass);
        }

        if ($existing) {
            updateTable('ftp_credentials', $payload, 'id = :id', ['id' => $existing['id']]);
        } else {
            insertData('ftp_credentials', $payload);
        }

        return ['user' => $vuser, 'pass' => $vpass, 'created' => (bool)$vpass];
    }
}

if (!function_exists('stripAuthFromUrl')) {
    function stripAuthFromUrl(string $url): string {
        $parts = parse_url($url);
        if ($parts === false) {
            return $url;
        }

        $host = $parts['host'] ?? '';
        if (!empty($parts['port'])) {
            $host .= ':' . $parts['port'];
        }

        $path = $parts['path'] ?? '';
        if (!empty($parts['query'])) {
            $path .= '?' . $parts['query'];
        }
        if (!empty($parts['fragment'])) {
            $path .= '#' . $parts['fragment'];
        }

        return ($parts['scheme'] ?? 'http') . '://' . $host . $path;
    }
}

if (!function_exists('storeSnapshotImage')) {
    function storeSnapshotImage(int $doorId, int $propertyId): array {

        require_once __DIR__ . '/../auth/auth_functions.php';

        $env = getEnvVars();

        $door = fetchDataSingle('gate_doors', ['id' => $doorId, 'property_id' => $propertyId]);
        if (!$door || empty($door['camera_id']) || empty($door['door_name'])) {
            error_log("Door or camera not found");
            return ['error' => 'Door or camera not found'];
        }

        $camera = fetchDataSingle('live_camera_streaming_data', ['id' => $door['camera_id'], 'property_id' => $propertyId]);
        if (!$camera || empty($camera['liveImageURL'])) {
            error_log("Camera stream URL not found");
            return ['error' => 'Camera stream URL not found'];
        }

        $clean = stripAuthFromUrl($camera['liveImageURL']);
        $p = parse_url($clean);
        if ($p === false || empty($p['scheme']) || empty($p['host'])) {
            error_log("Invalid camera URL");
            return ['error' => 'Invalid camera URL'];
        }
        $url = $p['scheme'].'://'.$p['host']
            .(isset($p['port'])?':'.$p['port']:'')
            .($p['path'] ?? '')
            .(isset($p['query'])?('?'.$p['query']):'');

        $user = $camera['userName'] ?? '';
        $pass = $camera['password'] ?? '';

        [$img, $httpCode, $modeUsed] = fetch_snapshot_any($url, $user, $pass);
        if (!$img) {
            return ['error' => 'Failed to download snapshot image (all auth modes tried)'];
        }
        error_log('snapshot auth succeeded via: ' . $modeUsed);

        $vu = ensurePropertySnapshotVUser($propertyId);
        $snapUser = $vu['user'];
        $snapPass = $vu['pass'];

        $snapCred = fetchDataSingle('ftp_credentials', [
            'property_id' => $propertyId,
            'role'        => 'snapshot'
        ]);
        if (!$snapCred || empty($snapCred['ftp_server_id']) || empty($snapCred['ftp_username'])) {
            error_log("Snapshot FTP credentials missing for property");
            return ['error' => 'Snapshot FTP credentials missing for property'];
        }
        $server = fetchDataSingle('ftp_servers', ['id' => (int)$snapCred['ftp_server_id']]);
        if (!$server || empty($server['url'])) {
            error_log("FTP server URL not found");
            return ['error' => 'FTP server URL not found'];
        }
        $ftpHost = $server['url'];

        if (!$snapPass) {
            if (empty($snapCred['ftp_password'])) {
                error_log("Snapshot FTP password missing for property");
                return ['error' => 'Snapshot FTP password missing for property'];
            }
            $snapPass = decryptFtpPassword($snapCred['ftp_password']);
        }

        $doorSlug = preg_replace('/[^a-zA-Z0-9_-]/', '_', $door['door_name']);
        $ts = (new DateTime('now', new DateTimeZone('UTC')))->format('Ymd\THis\Z');
        $fn = "{$env['ftp_env']}_p{$propertyId}_d{$doorId}_{$doorSlug}_{$ts}.jpg";

        $ftp = null;

        $port = !empty($snapCred['ftp_port']) ? (int)$snapCred['ftp_port'] : 21;
        $ftp = @ftp_ssl_connect($ftpHost, $port, 10);
        if (!$ftp) {
            error_log("FTPS connect failed");
            return ['error' => 'FTPS connect failed'];
        }
        if (!@ftp_login($ftp, $snapUser, $snapPass)) {
            if ($ftp) { @ftp_close($ftp); }
            error_log("FTPS login failed");
            return ['error' => 'FTPS login failed'];
        }
        ftp_pasv($ftp, true);

        $mem = fopen('php://temp/maxmemory:16777216', 'r+');
        fwrite($mem, $img);
        rewind($mem);

        $ok = @ftp_fput($ftp, $fn, $mem, FTP_BINARY);
        fclose($mem);
        if ($ftp) { @ftp_close($ftp); }

        if (!$ok) {
            error_log("Failed to upload snapshot to FTP");
            return ['error' => 'Failed to upload snapshot to FTP'];
        }

        $out = ['filename' => $fn, 'image' => $img];
        if (!empty($vu['created'])) {
            $out['snapshotFtpUser'] = $snapUser;
            $out['snapshotFtpPass'] = $snapPass;
        }
        return $out;
    }
}

if (!function_exists('getSnapshotImage')) {
    function getSnapshotImage(int $propertyId, string $filename, bool $asBase64 = false): array {
        require_once __DIR__ . '/../auth/auth_functions.php';

        $cred = fetchDataSingle('ftp_credentials', [
            'property_id' => $propertyId,
            'role'        => 'snapshot'
        ]);
        if (!$cred || empty($cred['ftp_server_id']) || empty($cred['ftp_username']) || empty($cred['ftp_password'])) {
            return ['success' => false, 'error' => 'Snapshot FTP credentials not found for property'];
        }

        $server = fetchDataSingle('ftp_servers', ['id' => (int)$cred['ftp_server_id']]);
        if (!$server || empty($server['url'])) {
            return ['success' => false, 'error' => 'FTP server URL not found'];
        }
        $ftpHost = $server['url'];

        try {
            $ftpPass = decryptFtpPassword($cred['ftp_password']);
        } catch (\Throwable $e) {
            return ['success' => false, 'error' => 'Failed to decrypt FTP password'];
        }

        $ftpUser = $cred['ftp_username'];
        $ftpPort = !empty($cred['ftp_port']) ? (int)$cred['ftp_port'] : 21;

        $ftp = @ftp_ssl_connect($ftpHost, $ftpPort, 10);
        if (!$ftp) {
            return ['success' => false, 'error' => 'FTPS connection failed'];
        }

        try {
            if (!@ftp_login($ftp, $ftpUser, $ftpPass)) {
                return ['success' => false, 'error' => 'FTPS login failed'];
            }
            ftp_pasv($ftp, true);

            $mem = fopen('php://temp/maxmemory:16777216', 'r+');
            if ($mem === false) {
                return ['success' => false, 'error' => 'Internal buffer allocation failed'];
            }

            $ok = @ftp_fget($ftp, $mem, $filename, FTP_BINARY, 0);
            if (!$ok) {
                fclose($mem);
                return ['success' => false, 'error' => 'Snapshot file not found on FTP server'];
            }

            rewind($mem);
            $imageContent = stream_get_contents($mem);
            fclose($mem);
            if ($imageContent === false) {
                return ['success' => false, 'error' => 'Failed to read snapshot content'];
            }

            if ($asBase64) {
                return [
                    'success'      => true,
                    'filename'     => $filename,
                    'image_base64' => base64_encode($imageContent),
                ];
            }
            return [
                'success'  => true, 
                'filename' => $filename, 
                'image'    => $imageContent,
            ];

        } finally {
            if ($ftp instanceof \FTP\Connection || is_resource($ftp)) {
                @ftp_close($ftp);
            }
        }
    }

}