<?php

namespace traits;

use attachments\helpers\attachment;
use attachments\helpers\attachment_content;
use classes\attachments;
use classes\db;
use classes\response;
use Exception;
use mysqli_result;
use objects\bookings_new_o;
use objects\bookings_o;
use objects\branding_o;
use objects\categories_o;
use objects\collected_order_invoices_o;
use objects\cron_o;
use objects\currency_conversion_rates_o;
use objects\customer_codes_o;
use objects\customer_notes_o;
use objects\customer_vehicles_o;
use objects\department_categories_o;
use objects\department_daily_reports_o;
use objects\department_variables_o;
use objects\departments_o;
use objects\economic_module_orders;
use objects\form_submissions_o;
use objects\fxratesapi_conversion_rates_o;
use objects\groups_o;
use objects\groups_permissions_o;
use objects\logs_o;
use objects\module_action_logs_o;
use objects\motorapi_lookups_o;
use objects\notifications_o;
use objects\object_attachments_o;
use objects\order_items_o;
use objects\orders_o;
use objects\plate_scanners_o;
use objects\plate_scans_o;
use objects\product_options_o;
use objects\products_o;
use objects\ratelimit_o;
use objects\stripe_module_customers_o;
use objects\stripe_module_orders_o;
use objects\tokens_o;
use objects\user_key_value_pairs_o;
use objects\user_price_overrides_o;
use objects\users_o;

trait db_object_t
{
    public static string $asArrayCacheKey = 'as_array'; // The cache key for the asArray function
    public static int $cashierNameCacheExpiration = 300; // The cashier name cache expiration time, in seconds. Default is 1 day (86400 seconds).
    public static int $economicCustomerNameCacheExpiration = 86400; // The economic customer name cache expiration time, in seconds. Default is 1 day (86400 seconds).
    public static int $asArrayCacheExpiration = 600; // The id of the object in the database
    public static int $isBookedCacheExpiration = 300; // The booked / not booked cache expiration time, in seconds. Default is 1 minute (60 seconds).
    public int $id; // The table of the objects in the database (e.g. users)
    private string $table; // The fields to search in the database (e.g. ['name', 'email']). If empty, all fields will be searched
    private array $searchableFields = []; // The where clauses to add to the pagination query
    private array $whereClauses = []; // The cache expiration time for the asArray function, in seconds. This is used to cache the result of the asArray function to improve performance. Default is 10 minutes (600 seconds).
    /**
     * Example:
     * // Set the CustomerId to be not "NULL".
     * setAdditionalWhereClause('AND `CustomerId` IS NOT NULL');
     * @var string
     */
    private string $additionalWhereClause = ''; // Additional where clause to add to the pagination query, this is used to add custom where clauses to the pagination query

    public function __construct()
    {
        $this->structure();
    }

    /**
     * Structure: Define the table and fields of the objects in the database
     */
    public function structure(): void
    {
        // Define the table and fields of the objects in the database
    }

    /**
     * Send a custom where clause to get objects as objects
     * @param string $whereClause The where clause to use (e.g. "status = 'active' AND role = 'admin'")
     * @return self[] The list of objects that match the where clause
     * @throws Exception If the where clause is invalid
     * @note This function does not use prepared statements, so be careful when using it to avoid SQL injection.
     * @deprecated Use getFieldsWhere or getFieldsWhereIn instead for better security and flexibility. This function is extremely prone to SQL injection attacks.
     */
    public function getObjectsWhereClause(string $whereClause): array
    {
        global $db;
        $table = $this->table;
        $sql = "SELECT id FROM $table WHERE $whereClause";
        $result = $db->query($sql);
        $rows = $db->fetch_all($result);
        $objects = [];
        foreach ( $rows as $row ) {
            $object = new static();
            $object->id = (int)$row['id'];
            $objects[] = $object;
        }
        return $objects;
    }

    public function countRowsWhere(array $fieldsAndValues): int
    {
        global $db;
        $table = $this->table;
        $where = [];
        foreach ( $fieldsAndValues as $field => $value ) {
            $where[] = "$field = '$value'";
        }
        $where = implode(' AND ', $where);
        $sql = "SELECT COUNT(*) AS count FROM $table WHERE $where";
        $result = $db->query($sql);
        $row = $db->fetch_assoc($result);
        return $row['count'];
    }

    public function getFieldsWhere(array $fieldsAndValues, array $fields): array
    {
        global $db;
        $table = $this->table;
        $where = [];
        foreach ( $fieldsAndValues as $field => $value ) {
            // If the value is null, add a where clause to check if the field is null
            if ($value === null) {
                $where[] = "$field IS NULL";
            } elseif (is_string($value) && strtolower($value) === '!null') {
                // If the value is "!null", add a where clause to check if the field is not null
                $where[] = "$field IS NOT NULL";
            } elseif (is_array($value)) {
                // If the value is an array, add a where clause to check if the field is in the array
                $in = implode(',', array_map(function ($v) {
                    // Escape the value to prevent SQL injection
                    global $db;
                    // If the value is numeric, return it as is
                    if (is_numeric($v)) {
                        return $v;
                    }
                    return "'" . $db->escape_string($v) . "'";
                }, $value));
                $where[] = "$field IN ($in)";
            } elseif (is_numeric($value)) {
                // If the value is numeric, add a where clause to check if the field is equal to the value
                $where[] = "$field = $value";
            } else {
                // Escape the value to prevent SQL injection
                global $db;
                $value = $db->escape_string($value);
                $where[] = "$field = '$value'";
            }
        }
        $where = implode(' AND ', $where);
        $sql = "SELECT " . implode(', ', $fields) . " FROM $table WHERE $where";
        $result = $db->query($sql);
        return $db->fetch_all($result);
    }

    public function getFieldsWhereIn(array $fieldsAndValues, array $fields): array
    {
        global $db;
        $table = $this->table;
        $where = [];
        foreach ( $fieldsAndValues as $field => $value ) {
            if (is_array($value)) {
                // Check if the array is empty
                if (empty($value)) {
                    // If the array is empty, return an empty array
                    return [];
                }
                // If the value is an array, add a where clause to check if the field is in the array
                $in = implode(',', array_map(function ($v) use ($field) {
                    // Escape the value to prevent SQL injection (if the value is a string)
                    if (is_numeric($v)) {
                        return $v;
                    }
                    if ($v === null) {
                        return 'NULL';
                    }
                    if (is_string($v) && strtolower($v) === 'null') {
                        return 'NULL';
                    }
                    if (is_string($v) && strtolower($v) === '!null') {
                        throw new Exception('Invalid value in array for field: ' . $field . '. Cannot use "!null" in an array.');
                    }
                    global $db;
                    return "'" . $db->escape_string($v) . "'";
                }, $value));
                $where[] = "$field IN ($in)";
            } else {
                // If the value is an integer, add a where clause to check if the field is equal to the value
                if (is_numeric($value)) {
                    $where[] = "$field = $value";
                    continue;
                }
                // If the value is null, add a where clause to check if the field is null
                if ($value === null) {
                    $where[] = "$field IS NULL";
                    continue;
                }
                // If the value is "!null", add a where clause to check if the field is not null
                if (is_string($value) && strtolower($value) === '!null') {
                    $where[] = "$field IS NOT NULL";
                    continue;
                }
                // Escape the value to prevent SQL injection
                $value = $db->escape_string($value);
                $where[] = "$field = '$value'";
            }
        }
        $where = implode(' AND ', $where);
        $sql = "SELECT " . implode(', ', $fields) . " FROM $table WHERE $where";
        $result = $db->query($sql);
        return $db->fetch_all($result);
    }

    public function getFieldsWhereContaining(array $fieldsAndValues, array $fields, array $options = ['limit' => null]): array
    {
        global $db;
        $table = $this->table;
        $where = [];
        foreach ( $fieldsAndValues as $field => $value ) {
            // Check if the value is null
            if ($value === null || $value === 'null') {
                $where[] = "$field IS NULL";
                continue;
            }
            // Escape the value to prevent SQL injection
            $value = $db->escape_string($value);
            $where[] = "$field LIKE '%$value%'";
        }
        $where = implode(' AND ', $where);
        $sql = "SELECT " . implode(', ', $fields) . " FROM $table WHERE $where";
        // If the limit is set, add it to the query
        if (isset($options['limit']) && is_int($options['limit']) && $options['limit'] > 0) {
            $sql .= " LIMIT " . $options['limit'];
        }
        $result = $db->query($sql);
        return $db->fetch_all($result);
    }

    /**
     * Update fields in the database where the conditions are met
     * @param array $fieldsAndValues The fields and values to update (e.g. ['name' => 'John Doe', 'email' => 'test@email.com'])
     * @param array $conditions The conditions to meet for the update (e.g. ['id' => 1, 'status' => 'active'])
     * @param int $limit The limit of rows to update, default is 0 (0 means no limit, update all rows that match the conditions)
     * @return mysqli_result|bool Returns the result of the update query, or false on failure
     */
    public function updateFieldsWhere(array $fieldsAndValues, array $conditions, int $limit = 1): mysqli_result|bool
    {
        global /** @var db $db */
        $db;
        $table = $this->table;
        $set = [];
        foreach ( $fieldsAndValues as $field => $value ) {
            // Escape the value to prevent SQL injection
            if (is_null($value)) {
                $set[] = "$field = NULL";
                continue;
            }
            $value = $db->escape_string($value);
            $set[] = "$field = '$value'";
        }
        $set = implode(', ', $set);

        // Build the WHERE clause
        $where = [];
        foreach ( $conditions as $field => $value ) {
            // Escape the value to prevent SQL injection
            if (is_null($value)) {
                $where[] = "$field IS NULL";
            } else {
                $value = $db->escape_string($value);
                $where[] = "$field = '$value'";
            }
        }
        $where = implode(' AND ', $where);
        // If the limit is set, add it to the query
        if ($limit > 0) {
            $where .= " LIMIT $limit";
        }

        // Execute the update query
        $sql = "UPDATE $table SET $set WHERE $where";
        return $db->query($sql);
    }

    /**
     * Set the searchable fields
     * @param array $fields The fields to search in the database (e.g. ['name', 'email'])
     * @return users_o|bookings_o|cron_o|customer_codes_o|customer_notes_o|customer_vehicles_o|departments_o|economic_module_orders|logs_o|order_items_o|orders_o|plate_scanners_o|plate_scans_o|products_o|ratelimit_o|tokens_o|user_key_value_pairs_o|user_price_overrides_o|db_object_t
     */
    public function setSearchableFields(array $fields): self
    {
        $this->searchableFields = $fields;
        return $this;
    }

    /**
     * Add a where clause to the pagination query
     * @param string $field
     * @param mixed $value
     * @return users_o|bookings_o|categories_o|cron_o|customer_codes_o|customer_notes_o|customer_vehicles_o|department_categories_o|departments_o|economic_module_orders|logs_o|order_items_o|orders_o|plate_scanners_o|plate_scans_o|product_options_o|products_o|ratelimit_o|tokens_o|user_key_value_pairs_o|user_price_overrides_o|db_object_t
     */
    public function addWhereClause(string $field, mixed $value): self
    {
        $this->whereClauses[] = "$field = '$value'";
        return $this;
    }

    public function __toString(): string
    {
        self::requireSelected();
        // Return the object as a string
        // Check if the asArray function is set, if not, return the object row directly
        if (method_exists($this, 'asArray')) {
            return json_encode($this->asArray());
        }
        // If the asArray function is not set, return the object row directly
        return json_encode($this->getArray());
    }


    /**
     * Require selected object.
     * Will throw an exception if object is not selected.
     */
    public function requireSelected(): void
    {
        if (!isset($this->id) || !$this->id) {
            // Figure out where the id was required from
            $trace = debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS, 2);
            throw new Exception('Object not selected or does not exist in ' . $this->table . ' (required from ' . ($trace[1]['function'] ?? 'unknown') . ' function)');
        }
    }

    /**
     * Get current object row as an array
     * @return array The current object row as an array
     * @throws Exception If object not found
     */
    public function getArray(): array
    {
        // Get the object from the database
        global $db;
        // Check if the id is set, if not throw an exception
        if (!isset($this->id)) {
            throw new Exception('Object not found');
        }
        $id = $this->id;
        $sql = "SELECT * FROM $this->table WHERE id = $id";
        $result = $db->query($sql);
        return $db->fetch_assoc($result);
    }

    /**
     * List objects with pagination (if set)
     * @param callable|null $parseFunction The function to parse the objects
     * @param string|null $forcedFilters The filters to force on the objects (Use the array_to_filters function to convert an array to a string)
     * @return array The list of objects in the table
     * @throws Exception
     */
    public function listObjectsWithPaginationIfSet($parseFunction = null, $forcedFilters = null, array $join = [], $additionalWhere = null): array
    {
        // Link to the listObjectsWithPaginationIfSet function.
        return self::db_object_t__listObjectsWithPaginationIfSet($parseFunction, $forcedFilters, $join, $additionalWhere);
    }

    /**
     * Set the additional where clause to add to the pagination query
     * @param string $clause The additional where clause to add to the pagination query
     * Example:
     * setAdditionalWhereClause('AND `CustomerId` IS NOT NULL');
     * @note This is cleared after each pagination query, so it can be used to add custom where clauses to the pagination query
     */
    public function setAdditionalWhereClause(string $clause): self
    {
        // Add the additional where clause to the pagination query
        $this->additionalWhereClause = $clause;
        return $this;
    }

    /**
     * List objects with pagination (if set)
     * @note This is separate from listObjectsWithPaginationIfSet, as it is used in the API routes with specific permission checks
     * @param callable|null $parseFunction The function to parse the objects
     * @param string|null $forcedFilters The filters to force on the objects (Use the array_to_filters function to convert an array to a string)
     * @return array
     * @throws Exception If the user does not have permission to list the objects
     */
    public function db_object_t__listObjectsWithPaginationIfSet($parseFunction = null, $forcedFilters = null, array $join = [], $additionalWhere = null): array
    {
        global $response;
        $page = ((int)$response->getRequestParameter('page')) ?? null; // Get the page number
        $limit = ((int)$response->getRequestParameter('limit')) ?? null; // Get the number of objects per page
        $search = $response->getRequestParameter('search') ?? null; // Get the search query
        // Apply additional where clause if provided via helper
        if ($additionalWhere !== null) {
            $additionalWhereSql = '';
            if (is_string($additionalWhere)) {
                $additionalWhereSql = $additionalWhere;
            } elseif (is_object($additionalWhere)) {
                if (method_exists($additionalWhere, 'toSql')) {
                    $additionalWhereSql = (string)$additionalWhere->toSql();
                } elseif (method_exists($additionalWhere, '__toString')) {
                    $additionalWhereSql = (string)$additionalWhere;
                }
            }
            if (!empty($additionalWhereSql)) {
                $this->setAdditionalWhereClause($additionalWhereSql);
            }
        }
        // If the forced filters are set, use them
        if ($forcedFilters) {
            $filters = $forcedFilters;
        } else {
            $filters = $response->getRequestParameter('filters') ?? null; // Get the filters ( Eg. department_id:1,role_id:2 OR department_id:1 )
        }
        $order = $response->getRequestParameter('order') ?? null; // Get the order ( Eg. name:ASC )
        // Make the filters an array
        if ($filters) {
            $filters = $this->filter_string_to_array($filters);
        }
        // Make the order an array
        if ($order) {
            $order = explode(':', $order);
            $order = [$order[0] => $order[1]];
            // Make the order direction uppercase
            $order = array_map('strtoupper', $order);
            // Check if the order direction is valid
            if (!in_array($order[array_key_first($order)], ['ASC', 'DESC'])) {
                throw new Exception('Invalid order direction, must be ASC or DESC');
            }
        } else {
            $order = ['id' => 'ASC']; // Default order
        }
        // List the objects with pagination
        if ($page && $limit) {
            return $this->listObjectsWithPagination($page, $limit, $search, $filters, $order, $parseFunction, $join);
        }
        // If the page and limit are not set, list all objects
        return $this->listObjectsWithPagination(1, 1000, $search, $filters, $order, $parseFunction, $join);
    }

    /**
     * List objects in the table with pagination
     * @param int $page The page number
     * @param int $limit The number of objects per page
     * @return array The list of objects in the table
     * @throws Exception
     */
    public function listObjectsWithPagination(int $page, int $limit, ?string $search = null, ?array $filters = null, ?array $order = null, $parseFunction = null, array $join = []): array
    {
        global /** @var response $response */
        /** @var db $db */
        $db, $response;
        $mysqli = $db->conn;

        $offset = ($page - 1) * $limit;

        // Fetch table fields to search
        $fields = $this->searchableFields;
        $fieldTypes = [];
        $tmpFields = [];
        $result = $mysqli->query("SHOW COLUMNS FROM {$this->table}");
        while ($row = $result->fetch_assoc()) {
            $tmpFields[] = $row['Field'];
            $fieldTypes[$row['Field']] = $row['Type'];
        }
        $result->free();

        if (empty($fields)) {
            $fields = $tmpFields;
        }

        $whereClauses = [];
        $params = [];
        $joinClauses = [];

        // Add where clauses from the object, if set. This is done to allow for custom where clauses in routes, while still allowing for pagination
        if (!empty($this->whereClauses)) {
            $whereClauses = $this->whereClauses;
        }

        // Search clause
        if (!empty($search)) {
            // Use prepared statements to prevent SQL injection
            $searchClauses = [];
            foreach ( $fields as $field ) {
                $searchClauses[] = "`$field` LIKE ?";
                $params[] = "%$search%";
            }
            $whereClauses[] = '(' . implode(' OR ', $searchClauses) . ')';
        }

        // Filters clause
        // To get objects with one of multiple values for a field, use an array for the value like so:
        // 'status' => ['pending', 'completed']
        if (!empty($filters)) {
            foreach ( $filters as $field => $value ) {
                if (in_array($field, $fields)) {
                    // If the value is an array, add multiple filters
                    if (is_array($value)) {
                        if (empty($value)) {
                            $whereClauses[] = '0=1';
                            continue;
                        }
                        $temp = [];
                        foreach ( $value as $v ) {
                            // Determine the type of the field
                            $type = $fieldTypes[$field] ?? '';
                            // If the field is an integer, cast the value to an integer
                            if (!empty($type) && str_contains($type, 'int')) {
                                $temp[] = "`$field` = $v";
                            } else {
                                $temp[] = "`$field` = ?";
                                $params[] = $v;
                            }
                            //$temp[] = "`$field` = ?";
                            //$params[] = $v;
                        }
                        $whereClauses[] = '(' . implode(' OR ', $temp) . ')';
                        continue;
                    }
                    // If the value is null, add a where clause to check if the field is null
                    if ($value === null || (is_string($value) && strtolower($value) === 'null') || (is_string($value) && strtolower($value === 'is_null')) || is_string($value) && strtolower($value === 'is null')) {
                        $whereClauses[] = "$field IS NULL";
                        continue;
                    }
                    // If the value is "NOT NULL", add a where clause to check if the field is not null
                    if ($value === 'NOT NULL' || (is_string($value) && strtolower($value) === 'not null')) {
                        $whereClauses[] = "$field IS NOT NULL";
                        continue;
                    }
                    // If the value is "NOT ZERO", add a where clause to check if the field is not zero
                    if ($value === 'NOT ZERO' || (is_string($value) && strtolower($value) === 'not zero')) {
                        $whereClauses[] = "$field != 0";
                        continue;
                    }
                    $whereClauses[] = "`$field` = ?";
                    $params[] = $value;
                } else {
                    // Add support for 'date_from' and 'date_to' filters
                    // Example of date_from: 'date_from:2023-01-01'
                    // Example of date_to: 'date_to:2023-12-31'
                    // In total the filter string would look like: 'created_at-date_from:2023-01-01,created_at-date_to:2023-12-31'
                    if (preg_match('/^(.+?)-(date_from|date_to)/', $field, $matches)) {
                        $fieldName = $matches[1];
                        $filterType = $matches[2];
                        $dateValue = $value;
                        // If the (date_to|date_from) is E.g. '2023-12-31', then we need to include the entire from/to date
                        // If the date is to be inclusive, we need to add 1 day to the date
                        if ($filterType === 'date_to') {
                            // If the value is null, skip this filter
                            if (empty($dateValue)) {
                                continue;
                            }
                            $dateValue = date('Y-m-d', strtotime($dateValue . ' +1 day'));
                        }

                        if (in_array($fieldName, $fields)) {
                            if ($filterType === 'date_from') {
                                if (empty($dateValue)) {
                                    continue;
                                }
                                $whereClauses[] = "`$fieldName` >= ?";
                            } elseif ($filterType === 'date_to') {
                                $whereClauses[] = "`$fieldName` <= ?";
                            }
                            $params[] = $dateValue;
                        }
                    } elseif (preg_match('/^(.+?)-has_attribute/', $field, $matches)) {
                        // If the filter is e.g. customer_number-has_attribute:invoiceOrdersIndividually
                        // Get the field name and the attribute
                        $fieldName = $matches[1];
                        // Set the attribute to the value, without the (optional) "!" prefix
                        $attribute = (string)str_replace('!', '', $value);
                        $state = !str_starts_with($value, '!');
                        // Check if the field name is valid
                        if (!in_array($fieldName, $fields)) {
                            throw new Exception('Invalid filter: ' . $field);
                        }
                        $attributes = array();
                        // Push the attribute to the array
                        $attributes[] = (string)$attribute;
                        // Get the customer numbers with the attribute
                        $tmp_customer_numbers_with_attribute = (new users_o())->getCustomerNumbersWithAttributes($attributes);
                        // If the state is true, add the customer numbers to the where clause
                        if ($state) {
                            $whereClauses[] = "$fieldName IN (" . implode(',', array_map('intval', $tmp_customer_numbers_with_attribute)) . ")";
                        } else {
                            // If the state is false, add the customer numbers to the where clause
                            $whereClauses[] = "$fieldName NOT IN (" . implode(',', array_map('intval', $tmp_customer_numbers_with_attribute)) . ")";
                        }
                    } elseif (preg_match('/^(.+?)-has_key/', $field, $matches)) {
                        // If the filter is e.g. customer_number-has_key:invoiceOrdersIndividually

                        // Get the field name and the key
                        $fieldName = $matches[1];
                        // Set the key to the value, without the (optional) "!" prefix
                        $key = (string)str_replace('!', '', $value);
                        $state = !str_starts_with($value, '!');
                        // Check if the field name is valid
                        if (!in_array($fieldName, $fields)) {
                            throw new Exception('Invalid filter: ' . $field);
                        }
                        $attributes = array();
                        // Push the attribute to the array
                        $attributes[] = (string)$key;
                        // Get the customer numbers with the attribute
                        $tmp_customer_numbers_with_attribute = (new user_key_value_pairs_o())->getCustomerNumbersWithKey($attributes);
                        // If the state is true, add the customer numbers to the where clause
                        if ($state) {
                            $whereClauses[] = "$fieldName IN (" . implode(',', array_map('intval', $tmp_customer_numbers_with_attribute)) . ")";
                        } else {
                            // If the state is false, add the customer numbers to the where clause
                            $whereClauses[] = "$fieldName NOT IN (" . implode(',', array_map('intval', $tmp_customer_numbers_with_attribute)) . ")";
                        }
                    } else {
                        throw new Exception('Invalid filter: ' . $field);
                    }
                }
            }
        }

        // Check for "deleted_at" column
        if (in_array('deleted_at', $fields)) {
            $whereClauses[] = "`deleted_at` IS NULL";
        }


        // Add the additional where clause, if set
        if (!empty($this->additionalWhereClause)) {
            $whereClauses[] = $this->additionalWhereClause;
        }

        // Combine WHERE clauses
        $whereQuery = !empty($whereClauses) ? 'WHERE ' . implode(' AND ', $whereClauses) : '';
        // Order clause
        $orderQuery = '';
        if (!empty($order)) {
            $orderParts = [];
            foreach ( $order as $field => $direction ) {
                if (in_array($field, $fields)) {
                    $direction = strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC';
                    $orderParts[] = "`$field` $direction";
                }
            }
            $orderQuery = !empty($orderParts) ? 'ORDER BY ' . implode(', ', $orderParts) : '';
        }

        // Pagination clause
        $limitQuery = 'LIMIT ? OFFSET ?';
        $params[] = $limit;
        $params[] = $offset;

        // Join clause with table prefix, so it doesn't conflict with the other columns       // This is done to allow for custom join clauses in routes, while still allowing for pagination
        $joinClauses = '';
        if (!empty($join)) {
            // Build the join clauses
            foreach ( $join as $table => $on ) {
                $joinClauses .= " LEFT JOIN `$table` ON $on";
            }
        }

        // Final query
        $sql = "SELECT * FROM {$this->table} $joinClauses $whereQuery $orderQuery $limitQuery";

        // Prepare and bind
        $stmt = $mysqli->prepare($sql);
        if ($stmt === false) {
            throw new Exception("Failed to prepare statement: " . $mysqli->error);
        }

        // Dynamically bind parameters
        $types = str_repeat('s', count($params) - 2) . 'ii'; // 's' for strings, 'i' for limit and offset
        $stmt->bind_param($types, ...$params);

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

        // Total count query
        $countSql = "SELECT COUNT(*) AS count FROM {$this->table} $whereQuery";
        $countStmt = $mysqli->prepare($countSql);
        if ($countStmt === false) {
            throw new Exception("Failed to prepare count statement: " . $mysqli->error);
        }

        // Reuse parameters (excluding LIMIT and OFFSET)
        $countParams = array_slice($params, 0, -2);
        $countTypes = substr($types, 0, count($countParams));
        if (!empty($countParams)) {
            $countStmt->bind_param($countTypes, ...$countParams);
        }

        $countStmt->execute();
        $countResult = $countStmt->get_result();
        $total = $countResult->fetch_assoc()['count'];
        $countStmt->close();

        // Provide pagination metadata
        $response->paginate($page, $limit, $total, $search, $filters, $order);

        // Parse the objects, if a parse function is provided
        if ($parseFunction) {
            $objects = array_map($parseFunction, $objects);
        }

        // Clear additional where clause after each pagination query to avoid leaking constraints
        $this->additionalWhereClause = '';
        return $objects;
    }

    /**
     * List ALL objects in the table (THIS INCLUDES DELETED OBJECTS)
     * @return array The list of objects in the table
     */
    public function listObjects($parseFunction = null): array
    {
        global /** @var db $db */
        $db;
        $sql = "SELECT * FROM $this->table";
        $result = $db->query($sql);
        $objects = $db->fetch_all($result);
        // Parse the objects, if a parse function is provided
        if ($parseFunction) {
            $objects = array_map($parseFunction, $objects);
        }
        return $objects;
    }

    /**
     * Forcefully add or override filters to the user's request
     * @param string $filterString The filters to forcefully override/add on the objects (Eg. 'department_id:1,role_id:2')
     * @returns string The updated filters string with the forced filters added
     */
    public function forceAddFilters(string $filterString): string
    {
        global /** @var response $response */
        $response;
        $filters = $response->getRequestParameter('filters') ?? null; // Get the filters ( Eg. department_id:1,role_id:2 OR department_id:1 )
        // If the filters are set, add the forced filters to the filters
        if ($filters) {
            if (is_array($filters)) {
                $filters = $this->array_to_filters($filters);
            }
            $filters .= ',' . $filterString;
        } else {
            $filters = $filterString;
        }
        return $filters;
    }

    /**
     * Forcefully restrict filters to the user's request
     * @param array $fieldsAndValues The filters to forcefully restrict on the objects (Eg. ['department_id' => 1, 'role_id' => [1,2]]) - This would throw an error if the user tries to search for a different department_id or role_id than 1 or 2
     * @returns string The updated filters string with the forced filters added (Eg. 'department_id:1,role_id:1,role_id:2')
     * @throws Exception If the user tries to search for a different department_id or role_id than 1 or 2
     */
    public function forceRestrictFilters(array $fieldsAndValues): string
    {
        global /** @var response $response */
        $response;
        $filters = $response->getRequestParameter('filters') ?? null; // Get the filters ( Eg. department_id:1,role_id:2 OR department_id:1 )
        // If the filters are set, add the forced filters to the filters
        if ($filters) {
            $filters = $this->filter_string_to_array($filters);
            foreach ( $fieldsAndValues as $field => $value ) {
                // If the value is an array, check if the user tries to search for a different value than the ones provided
                if (is_array($value)) {
                    // Check if the filter is even set
                    if (isset($filters[$field]) && !in_array($filters[$field], $value)) {
                        throw new Exception('Invalid filter: ' . $field . ' - ' . $filters[$field] . '. Permitted values: ' . implode(', ', $value));
                    }
                    continue;
                }
                // If the values can be changed to integers, convert them to integers
                if (is_numeric($value) && is_numeric($filters[$field])) {
                    $value = (int)$value;
                    $filters[$field] = (int)$filters[$field];
                }
                // Check if the user tries to search for a different value than the one provided
                if ($filters[$field] !== $value) {
                    throw new Exception('Invalid filter: ' . $field . ' - ' . $filters[$field] . '. Permitted value: ' . $value);
                }
            }
            // If any of the forced filters are not set, set them - This is done to prevent the user from searching by omitting the forced filters
            foreach ( $fieldsAndValues as $field => $value ) {
                if (!isset($filters[$field])) {
                    $filters[$field] = $value;
                }
            }
        } else {
            $filters = $fieldsAndValues;
        }
        return $this->array_to_filters($filters);
    }

    /**
     * Filter string to array
     * @param string|array $filterString The filter string (Eg. 'department_id:1,role_id:2') or an array of filters
     * @return array The array of filters (Eg. ['department_id' => 1, 'role_id' => 2])
     */
    public function filter_string_to_array(string|array $filterString): array
    {
        if (is_array($filterString)) {
            $temp = $filterString;
        } else {
            $filters = explode(',', $filterString);
            $temp = [];
            foreach ( $filters as $filter ) {
                $filter = explode(':', $filter);
                if (count($filter) < 2) {
                    continue;
                }
                // Check if the filter already exists, if it does, make it an array
                if (isset($temp[$filter[0]])) {
                    if (!is_array($temp[$filter[0]])) {
                        $temp[$filter[0]] = [$temp[$filter[0]]];
                    }
                    // Check if the filter already exists within the array with the same key, if it does, skip it
                    if (in_array($filter[1], $temp[$filter[0]])) {
                        continue;
                    }
                    $temp[$filter[0]][] = $filter[1];
                    continue;
                }
                $temp[$filter[0]] = $filter[1];
            }
        }

        // Check if the value is "null", if it is, set it to null
        foreach ( $temp as $key => $value ) {
            if (is_string($value) && strtolower($value) === 'null') {
                $temp[$key] = null;
            }
        }
        return $temp;
    }

    /**
     * Convert an array of filters to a string
     * @param $array array The array of filters
     * @return string
     * @example ['department_id' => [1,2], 'role_id' => 2, 'id' => 1] => 'department_id:1,department_id:2,role_id:2,id:1'
     */
    public function array_to_filters(array $array): string
    {
        $filter_string = '';
        foreach ( $array as $key => $value ) {
            // If this is not the first filter, add a comma to separate the filters
            if ($filter_string !== '') {
                $filter_string .= ',';
            }
            // If the value is an array, add multiple filters
            if (is_array($value)) {
                foreach ( $value as $v ) {
                    $filter_string .= $key . ':' . $v . ',';
                }
                // Remove the last comma
                $filter_string = rtrim($filter_string, ',');
                continue;
            }
            // Add the filter
            $filter_string .= $key . ':' . $value;
        }
        return $filter_string;
    }

    /**
     * Get the total number of objects
     * @param string|null $search The search query
     * @param array|null $filters The filters ( Eg. department_id:1,role_id:2 OR department_id:1 )
     * @return int The total number of objects
     */
    public function getTotalObjects(string $search = null, array $filters = null): int
    {
        global $db;
        $mysqli = $db->conn;

        // Fetch table fields
        $fields = [];
        $result = $mysqli->query("SHOW COLUMNS FROM {$this->table}");
        while ($row = $result->fetch_assoc()) {
            $fields[] = $row['Field'];
        }
        $result->free();

        $whereClauses = [];
        $params = [];

        // Search clause
        if (!empty($search)) {
            // Use prepared statements to prevent SQL injection
            $searchClauses = [];
            foreach ( $fields as $field ) {
                $searchClauses[] = "`$field` LIKE ?";
                $params[] = "%$search%";
            }
            $whereClauses[] = '(' . implode(' OR ', $searchClauses) . ')';
        }

        // Filters clause
        if (!empty($filters)) {
            foreach ( $filters as $field => $value ) {
                if (in_array($field, $fields)) {
                    $whereClauses[] = "`$field` = ?";
                    $params[] = $value;
                }
            }
        }

        // Combine WHERE clauses
        $whereQuery = !empty($whereClauses) ? 'WHERE ' . implode(' AND ', $whereClauses) : '';

        // Check for "deleted_at" column
        if (in_array('deleted_at', $fields)) {
            $whereClauses[] = "`deleted_at` IS NULL";
        }

        // Final query
        $sql = "SELECT COUNT(*) AS count FROM {$this->table} $whereQuery";

        // Prepare and bind
        $stmt = $mysqli->prepare($sql);
        if ($stmt === false) {
            throw new Exception("Failed to prepare statement: " . $mysqli->error);
        }

        // Dynamically bind parameters
        $types = str_repeat('s', count($params)); // 's' for strings
        $stmt->bind_param($types, ...$params);

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

        return $result->fetch_assoc()['count'];
    }

    /**
     * Get the table of the objects in the database
     * @returns string
     */
    public function getTable(): string
    {
        return $this->table;
    }

    /**
     * Set the table of the objects in the database
     * @param string $table The table of the objects in the database
     */
    public function setTable(string $table): void
    {
        // Add the backticks to the table name, to prevent SQL injection and reserved keyword issues.
        $table = "`$table`";
        $this->table = $table;
    }

    /**
     * Cache object
     * @param string $key The key to cache the object
     * @param mixed $data The data to cache
     */
    public function cache(string $key, mixed $data, $objectId = null): void
    {
        // If the object id is not set, use the object id
        if (!$objectId) {
            $objectId = $this->id;
        }
        // if the data is an array, object, or resource, convert it to a string
        if (is_array($data) || is_object($data) || is_resource($data)) {
            $data = json_encode($data);
        }
        // Cache the data
        redis->set($this->table . '_' . $objectId . '_' . $key, $data);
    }

    /**
     * Get cached values for multiple objects
     * @param string $key The key to get the cached object
     * @param array $objectIds The object ids to get the cached objects for
     * @return array The cached object keys
     */
    public function getCachedForMultipleObjects(string $key, array $objectIds): array
    {
        return redis->mget(array_map(function($objectId) use ($key) {
            return $this->table . '_' . $objectId . '_' . $key;
        }, $objectIds));
    }

    /**
     * Set cached object expiration time
     * @param string $key The key to set the cached object expiration time
     * @param int $seconds The number of seconds to set the cached object expiration time
     * @param null $objectId
     * @return void
     */
    public function setCachedExpiration(string $key, int $seconds, $objectId = null): void
    {
        // If the object id is not set, use the object id
        if (!$objectId) {
            $objectId = $this->id;
        }
        // Set the expiration time for the cached data
        redis->expire($this->table . '_' . $objectId . '_' . $key, $seconds);
    }

    /**
     * Get a cached object key (redis key)
     * @param string $key The key to get the cached object
     * @return string The cached object key
     * @throws Exception If the object is not selected, it throws an exception
     */
    public function getCachedKey(string $key, $objectId = null): string
    {
        // If the object id is not set, use the object id
        if (!$objectId) {
            self::requireSelected();
            $objectId = $this->id;
        }
        // Return the cached data key
        return $this->table . '_' . $objectId . '_' . $key;
    }

    /**
     * Get cached object
     * @param string $key The key to get the cached object
     * @return mixed The cached object
     */
    public function getCached(string $key, $objectId = null): mixed
    {
        // If the object id is not set, use the object id
        if (!$objectId) {
            $objectId = $this->id;
        }
        // Get the cached data
        $data = redis->get($this->table . '_' . $objectId . '_' . $key) ?? null;
        // if the data is a JSON string, convert it to an array
        if (is_string($data) && json_decode($data)) {
            $data = json_decode($data);
        }
        return $data;
    }

    /**
     * Method cache with parameters
     * @param string $method The method to cache the result of
     * @param array $params The parameters to pass to the method
     * @param int $expiration The expiration time in seconds
     * @param callable|null $function The function to call if the cached result is not found, it should return the result to be cached
     * @return mixed The cached result of the method
     */
    public function methodCacheWithParameters(string $method, array $params = [], int $expiration = 120, callable $function = null): mixed
    {
        // Generate the cache key
        $objectId = "methodCacheWithParameters";
        $key = $method . '_' . md5(serialize($params));
        // Try to get the cached result
        $cachedResult = $this->getCached($key, $objectId);
        // If the cached result is found, return it
        if ($cachedResult !== null) {
            return $cachedResult;
        }
        // If the cached result is not found, call the function to get the result
        if ($function) {
            $result = call_user_func_array($function, $params);
        } else {
            // If no function is provided, call the method on this object
            $result = call_user_func_array([$this, $method], $params);
        }
        // Cache the result
        $this->cache($key, $result, $objectId);
        // Set the expiration time
        $this->setCachedExpiration($key, $expiration, $objectId);
        return $result;
    }

    /**
     * Delete cached object
     * @param string $key The key to delete the cached object
     */
    public function deleteCached(string $key, $objectId = null): void
    {
        // If the object id is not set, use the object id
        if (!$objectId) {
            $objectId = $this->id;
        }
        // Delete the cached data
        redis->delete($this->table . '_' . $objectId . '_' . $key);
    }

    /**
     * Delete the object from the database, or if the deleted_at column exists, soft delete the object.
     * @throws Exception If the object is not selected, it throws an exception
     */
    public function delete(): void
    {
        self::requireSelected();
        // Check if the deleted_at column exists
        if (self::columnsExist(['deleted_at'])) {
            // Soft delete the object
            self::update(['deleted_at' => date('Y-m-d H:i:s')]);
        } else {
            // Delete the object from the database
            self::delete_object($this->table, $this->id);
        }
        // Trigger the object changed event
        self::objectChanged();
    }

    /**
     * Columns exist
     * Check if all the columns in the array exist in the table
     * @param array $columns The columns to check if they exist in the table E.g. ['name', 'email']
     * @return bool True if all the columns exist in the table, false otherwise
     */
    public function columnsExist(array $columns): bool
    {
        global $db;
        $table = $this->table;
        $result = $db->query("SHOW COLUMNS FROM $table");
        $fields = $db->fetch_all($result);
        $fields = array_column($fields, 'Field');
        return count(array_intersect($columns, $fields)) === count($columns);
    }

    /**
     * Update object
     * @param array $data The data to update the object with (E.g. ['name' => 'John', 'email' => 'email@example.com'])
     * @throws Exception If the object is not selected, it throws an exception
     */
    public function update(array $data): void
    {
        self::requireSelected();
        // Update the object in the database
        global $db;
        $set = [];
        foreach ( $data as $key => $value ) {
            // JSON encode objects and arrays
            if (is_object($value) || is_array($value)) {
                $value = json_encode($value, JSON_UNESCAPED_UNICODE);
                if ($value === false) {
                    throw new Exception('Failed to encode value for key: ' . $key . ' - ' . json_last_error_msg());
                }
            }
            // If the value is null, set it to null
            if ($value === null || (is_string($value) && strtolower($value) === 'null')) {
                $set[] = "$key = NULL";
                continue;
            }

            // If the value is boolean, convert it to 1 or 0
            if (is_bool($value)) {
                $value = $value ? 1 : 0;
            }

            $value = $db->escape_string($value);
            $set[] = "$key = '$value'";
        }
        $set = implode(', ', $set);
        $sql = "UPDATE $this->table SET $set WHERE id = $this->id";
        $db->query($sql);
        // Trigger the object changed event
        self::objectChanged();
    }

    /**
     * generateFakeId - This function generates a fake id for the object
     * @see orders_o::simulateOrderFromXLVask()
     * @returns int A fake (negative) id for the object
     */
    public function generateFakeId(): int
    {
        global /** @var db $db */
        $db;
        return $db->getNextFakeId($this->table);
    }

    /**
     * Object changed
     * This function should be called after an object property is changed
     * It will trigger an event to notify the object has changed
     * @return void
     */
    abstract public function objectChanged(): void;

    /**
     * Delete object by table and id (Permanently, no soft delete)
     * @param string $table The table of the object in the database
     * @param int $id The id of the object in the database
     */
    public static function delete_object(string $table, int $id): void
    {
        global $db;
        $sql = "DELETE FROM $table WHERE id = $id";
        $db->query($sql);
    }

    /**
     * Delete object permanently
     * @throws Exception If the object is not selected, it throws an exception
     */
    public function deletePermanently(): void
    {
        self::requireSelected();
        // Delete the object from the database
        self::delete_object($this->table, $this->id);
        // Trigger the object changed event
        self::objectChanged();
    }

    /**
     * Get all objects fields
     * @param array $fields The fields to get from the objects (Eg. ['name', 'description'])
     * @return array The list of fields from the objects (Eg. [['name' => 'John', 'description' => 'Doe'], ['name' => 'Jane', 'description' => 'Doe']])
     */
    public function getFields(array $fields): array
    {
        global $db;
        $sql = "SELECT " . implode(', ', $fields) . " FROM $this->table";
        $result = $db->query($sql);
        return $db->fetch_all($result);
    }

    /**
     * Select an object by id
     * @param int $id The id of the object in the database
     * @returns $this
     * @throws Exception If the object does not exist in the database, it throws an exception
     */
    public function select(int $id): self
    {
        // Check if the object exists in the database
        $this->id = $id;
        $this->getObjectProperties();
        return $this;
    }

    /**
     * Set the view, used on the pagination
     * @param string $view The view to set
     * @return users_o|bookings_new_o|bookings_o|branding_o|categories_o|collected_order_invoices_o|cron_o|currency_conversion_rates_o|customer_codes_o|customer_notes_o|customer_vehicles_o|department_categories_o|department_daily_reports_o|department_variables_o|departments_o|economic_module_orders|form_submissions_o|fxratesapi_conversion_rates_o|groups_o|groups_permissions_o|logs_o|module_action_logs_o|motorapi_lookups_o|notifications_o|order_items_o|orders_o|plate_scanners_o|plate_scans_o|product_options_o|products_o|ratelimit_o|stripe_module_customers_o|stripe_module_orders_o|tokens_o|user_key_value_pairs_o|user_price_overrides_o|db_object_t
     */
    public function setView(string $view): self
    {
        $this->table = $view;
        return $this;
    }

    /**
     * Add an object to the database, using the data provided
     * @param array $data The data to add the object with (E.g. ['name' => 'John', 'email' => 'email@example.com'])
     * @return int The id of the new object
     * @throws Exception If the creation of the object fails, it throws an exception
     */
    public function add_object(array $data): int
    {
        global /** @var db $db */
        $db;
        try {
            // Update the object in the database
            $set = [];
            foreach ( $data as $key => $value ) {
                // JSON encode objects and arrays
                if (is_object($value) || is_array($value)) {
                    $value = json_encode($value, JSON_UNESCAPED_UNICODE);
                    if ($value === false) {
                        throw new Exception('Failed to encode value for key: ' . $key . ' - ' . json_last_error_msg());
                    }
                    $set[] = "$key = '$value'";
                    continue;
                }
                // If the value is null, set it to null
                elseif ($value === null || (is_string($value) && strtolower($value) === 'null')) {
                    $value = null;
                    $set[] = "$key = NULL";
                    continue;
                }
                // If the value is numeric, don't escape it
                elseif (is_numeric($value) && strlen((int)$value) == strlen($value)) {
                    $value = (int)$value;
                    $set[] = "$key = $value";
                    continue;
                }

                // If the value is boolean, convert it to 1 or 0
                elseif (is_bool($value)) {
                    $value = $value ? 1 : 0;
                    $set[] = "$key = $value";
                    continue;
                } else {
                    // If the value is a string, escape it
                    $value = $db->escape_string($value);
                    $set[] = "$key = '$value'";
                }
            }
            $set = implode(', ', $set);
            $sql = "INSERT INTO $this->table SET $set";
            $db->query($sql);
            return $db->insert_id();
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }

    /**
     * Does this object exist in the database?
     * @return bool True if the object exists in the database, false otherwise
     */
    public function exists(): bool
    {
        // Check if the id is set
        if (!isset($this->id)) {
            return false;
        }
        // Check if the object exists in the database
        global $db;
        $id = $this->id;
        $sql = "SELECT * FROM $this->table WHERE id = $id";
        $result = $db->query($sql);
        // Check if the object exists, and if there's a deleted_at column, check if the object is not deleted
        return $db->num_rows($result) > 0 && (!$this->columnsExist(['deleted_at']) || $db->fetch_assoc($result)['deleted_at'] === null);
    }

    /**
     * Restore the object from the database, if the deleted_at column exists.
     * @throws Exception If the object is not selected, it throws an exception
     */
    public function restore(): void
    {
        self::requireSelected();
        // Check if the deleted_at column exists
        if (self::columnsExist(['deleted_at'])) {
            // Restore the object
            global $db;
            $id = $this->id;
            $sql = "UPDATE $this->table SET deleted_at = NULL WHERE id = $id";
            $db->query($sql);
            // Trigger the object changed event
            self::objectChanged();
        }
    }

    /**
     * Add an attachment to the (current) object
     * @param attachment_content $content The content of the attachment
     * @return object_attachments_o The attachment object
     * @throws Exception If the object is not selected, it throws an exception
     */
    public function addAttachment(attachment_content $content): object_attachments_o
    {
        self::requireSelected();
        // Add the attachment to the object
        $attachment = new attachments();
        $id = $attachment->create($this->table, $this->id, $content);
        return (new object_attachments_o())->select($id);
    }
    /**
     * List attachments of the (current) object
     * @return attachment[] The list of attachments of the object
     * @throws Exception If the object is not selected, it throws an exception
     */
    public function listAttachments(): array
    {
        self::requireSelected();
        // List the attachments of the object
        $attachment = new attachments();
        return $attachment->list($this->table, $this->id);
    }
    /**
     * Remove an attachment
     * @param int $attachmentId The id of the attachment to remove
     * @throws Exception If the object is not selected, it throws an exception
     */
    public function removeAttachment(int $attachmentId): void
    {
        self::requireSelected();
        // Remove the attachment from the object
        $attachment = new attachments();
        $attachment->delete($attachmentId);
    }

    /**
     * Get an attachment
     * @param int $attachmentId The id of the attachment to get
     * @return object_attachments_o The attachment object
     * @throws Exception If the object is not selected, it throws an exception
     * @throws Exception If the attachment does not exist, it throws an exception
     */
    public function getAttachment(int $attachmentId): object_attachments_o
    {
        self::requireSelected();
        // Get the attachment from the object
        $attachment = new attachments();
        return $attachment->get($attachmentId);
    }
}