<?php

namespace objects;

use classes\db;
use classes\economic;
use classes\object_property;
use classes\stripe;
use config\economic_admin_fee_monthly_c;
use config\economic_admin_fee_order_c;
use config\economic_fee_product_id_c;
use Exception;
use objects\order_items_o;
use objects\orders_o;
use objects\users_o;
use Stripe\Exception\ApiErrorException;
use traits\db_object_t;

const ECONOMIC_PROCESSOR = 1;
const STRIPE_PROCESSOR = 2;
const OTHER_PROCESSOR = 3;

class collected_order_invoices_o extends db
{
    use db_object_t;

    public object_property $customer_number;
    public object_property $name;
    public object_property $notes;
    public object_property $processor;
    public object_property $external_id;

    public object_property $booked_invoice_id;
    public object_property $error_message;
    public object_property $po_number;
    public object_property $created_at;
    public object_property $updated_at;
    public object_property $closed_at;
    public int $economic_wash_subscription_user_id = 1857;
    /**
     * The processor types
     *
     * 1 = E-conomic, 2 = Stripe, 3 = Other (without tracking)
     * @var array|string[]
     */
    public array $processors = [
        ECONOMIC_PROCESSOR => 'E-conomic',
        STRIPE_PROCESSOR => 'Stripe',
        OTHER_PROCESSOR => 'Other, without tracking',
    ];

    public function structure(): void
    {
        $this->setTable('collected_order_invoices');
    }

    /**
     * List all collected order invoices for a customer
     * @param int $customer_number The E-conomic customer number
     * @return array The list of collected order invoices
     * @throws Exception If the request was not successful
     */
    public function getCustomerInvoiceCollections(int $customer_number): array
    {
        $collections = self::getFieldsWhere(
            [
                'customer_number' => $customer_number,
                'deleted_at' => null
            ],
            ['id', 'name', 'notes', 'processor', 'external_id', 'created_at', 'updated_at', 'closed_at']
        );
        // Parse the results
        $result = [];
        foreach ( $collections as $collection ) {
            $this->id = $collection['id'];
            self::getObjectProperties();
            self::requireSelected();
            $result[] = self::asArray();
        }
        return $result;
    }

    public function getObjectProperties(): void
    {
        $this->customer_number = new object_property($this->table, $this->id, 'customer_number', 'int', false);
        $this->name = new object_property($this->table, $this->id, 'name', 'string', false);
        $this->notes = new object_property($this->table, $this->id, 'notes', 'string', false);
        $this->processor = new object_property($this->table, $this->id, 'processor', 'int', false);
        $this->external_id = new object_property($this->table, $this->id, 'external_id', 'string', false);
        $this->created_at = new object_property($this->table, $this->id, 'created_at', 'string', false);
        $this->updated_at = new object_property($this->table, $this->id, 'updated_at', 'string', false);
        $this->closed_at = new object_property($this->table, $this->id, 'closed_at', 'string', false);
        $this->booked_invoice_id = new object_property($this->table, $this->id, 'booked_invoice_id', 'int', false);
        $this->po_number = new object_property($this->table, $this->id, 'po_number', 'string', false);
        $this->error_message = new object_property($this->table, $this->id, 'error_message', 'string', false);
    }

    /**
     * Get the invoice collection as an array
     * @throws ApiErrorException If the payment method is Stripe and the request fails
     * @throws Exception If the request was not successful
     */
    public function asArray(): array
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Check if the object is cached
        $cached = self::getCached('asArray', $this->id);
        if ($cached !== null) {
            //return (array)$cached;
        }
        $tmp = [
            'id' => (int)$this->id,
            'customer_number' => (int)$this->customer_number->value(),
            'name' => (string)$this->name->value(),
            'notes' => (string)$this->notes->value(),
            'processor' => (int)$this->processor->value(),
            'external_id' => (string)$this->external_id->value(),
            'po_number' => (string)$this->po_number->value(),
            'created_at' => (string)$this->created_at->value(),
            'updated_at' => (string)$this->updated_at->value(),
            'closed_at' => (string)$this->closed_at->value(),
            'orders' => $this->getOrders(),
            'economic_invoice_draft_id' => null, // This will be overwritten if the external id is set
            'economic_invoice_booked_id' => null, // This will be overwritten if the external id is set
            'stripe' => null, // This will be overwritten if the processor is Stripe
            'total_net_amount' => self::getTotalAmount(),
            'user' => (new users_o())->getUserByCustomerNumber($this->customer_number->value()),
            //'debug' => (new economic())->invoices->draft->get((new economic())->invoices->draft->get_from_external_id($this->getExternalId())),
        ];
        // If the user exists, get the user details
        $tmp['user'] = $tmp['user']->id ? (array)$tmp['user']->asArray() : [];
        // If the external id is set, get the invoice draft id
        if (!empty($this->external_id->value())) {
            $tmp['economic_invoice_draft_id'] = self::isDraftExisting() ? self::getInvoiceDraftId() : null;
            $tmp['economic_invoice_booked_id'] = self::isBooked() ? self::getInvoiceBookedId() : null;
        }
        // If the processor is Stripe, get the stripe details
        $isExternalIdStripe = !empty($this->external_id->value()) && str_starts_with($this->external_id->value(), 'pi_');
        if ((int)$this->processor->value() === STRIPE_PROCESSOR || $isExternalIdStripe) {
            // Get the stripe details
            $stripe = new stripe();
            $stripe_details = $stripe->payment_intents->get(
                $this->external_id->value(),
                [
                    'expand' => ['latest_charge', 'latest_charge.balance_transaction', 'latest_charge.payment_method_details.card.wallet']
                ]);
            $tmp['stripe'] = $stripe_details;
        }
        // Cache the result
        self::cache('asArray', $tmp, $this->id);
        self::setCachedExpiration('asArray', self::$asArrayCacheExpiration, $this->id);
        return $tmp;
    }

    /**
     * Get the orders in the invoice collection
     * @param bool $count If the count of orders should be returned instead of the orders
     * @return array|int The list of orders in the invoice collection, or the count of orders if $count is true
     * @throws Exception If the request was not successful
     */
    public function getOrders(bool $count = false): array|int
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        $orders = new orders_o();
        $order_ids = $orders->getFieldsWhere(
            [
                'invoice_collection_id' => $this->id,
                'deleted_at' => null
            ],
            ['id']
        );
        if ($count) {
            return count($order_ids);
        }
        $result = [];
        foreach ( $order_ids as $order_id ) {
            $orders->select($order_id['id']);
            $orders->requireSelected();
            if (!$orders->isIncludedInInvoicing()) {
                continue;
            }
            $result[] = $orders->asArray();
        }
        return $result;
    }

    /**
     * Get the net amount (sum) of the invoice collection
     * @return float The total amount of the invoice collection
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is not set
     */
    public function getTotalAmount(): float
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Get the orders in the invoice collection (already filtered to those included in invoicing)
        $order_ids = self::getOrderIds();
        if (empty($order_ids)) {
            return 0.0;
        }
        // Build a flat list of order IDs
        $ids = array_map(static function ($row) {
            return (int)$row['id'];
        }, $order_ids);
        if (empty($ids)) {
            return 0.0;
        }
        // Compute net amounts in a single aggregated pass over order_items
        $orders = new orders_o();
        $netByOrder = $orders->getNetAmountForOrders($ids);
        // Sum per-order totals
        return array_sum($netByOrder);
    }

    /**
     * Get the order ids in the invoice collection
     * @return array The list of order ids in the invoice collection
     * @throws Exception If the request was not successful
     */
    public function getOrderIds(): array
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Get the orders in the invoice collection
        $orders_o = new orders_o();
        $fields = $orders_o->getFieldsWhere(
            [
                'invoice_collection_id' => $this->id,
                'deleted_at' => null
            ],
            ['id']
        );
        // Remove all orders that are not included in invoicing
        foreach ( $fields as $key => $order ) {
            $orders_o->select($order['id']);
            $orders_o->requireSelected();
            if (!$orders_o->isIncludedInInvoicing()) {
                unset($fields[$key]);
            }
        }
        return $fields;
    }

    /**
     * Check if the invoice draft is existing in E-conomic
     * @returns bool If the invoice draft is existing
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is not set
     */
    public function isDraftExisting(): bool
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // If the external id is empty, the invoice draft does not exist
        if ($this->external_id->value() === null) {
            return false;
        }
        // Get the invoice draft id from the external id
        try {
            self::getInvoiceDraftId();
            return true;
        } catch (Exception $e) {
            return false;
        }
    }

    /**
     * Get the invoice draft id from the external id
     * @return int The invoice draft id
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice draft was not found
     */
    public function getInvoiceDraftId(): int
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Check if the external id is set
        if ($this->external_id->value() === null) {
            throw new Exception('Invoice draft does not exist');
        }
        // Create an economic object
        $economic = new economic();
        // Get the invoice draft id from the external id
        $invoice_draft_id = $economic->invoices->draft->get_from_external_id($this->external_id->value());
        return (int)$invoice_draft_id;
    }

    /**
     * Check if the invoice is booked in E-conomic
     * @returns bool If the invoice is booked
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is not set
     */
    public function isBooked(): bool
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Check if the booked state is cached
        $cached = self::getCached('isBooked', $this->id);
        if ($cached !== null) {
            return (bool)$cached;
        }
        // Get the invoice booked id from the external id
        try {
            self::getInvoiceBookedId();
            self::cache('isBooked', true, $this->id);
            self::setCachedExpiration('isBooked', self::$isBookedCacheExpiration, $this->id);
            return true;
        } catch (Exception $e) {
            return false;
        }
    }

    /**
     * Get the invoice booked id from the external id
     * @return int The invoice booked id
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice booked was not found
     */
    public function getInvoiceBookedId(): int
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Check if the invoice_booked_id is already set (We don't want to make a request to E-conomic if we already have the id - Since this is slow.)
        $booked_invoice_id = $this->booked_invoice_id->value();
        if (!empty($booked_invoice_id)) {
            return (int)$booked_invoice_id;
        }
        $external_id = $this->external_id->value();
        // If the external id is empty, the invoice booked does not exist
        if (empty($external_id)) {
            throw new Exception('Invoice booked does not exist');
        }
        // Create an economic object
        $economic = new economic();
        // Get the invoice booked id from the external id
        $invoice_booked_id = $economic->invoices->booked->get_from_external_id($external_id);
        $this->booked_invoice_id->set($invoice_booked_id);
        return (int)$invoice_booked_id;
    }

    /**
     * Check if a customer has an open invoice collection
     * @param int $customer_number The E-conomic customer number
     * @return bool If the customer has an open invoice collection
     */
    public function hasOpenInvoiceCollection(int $customer_number): bool
    {
        $collections = self::getFieldsWhere(
            [
                'customer_number' => $customer_number,
                'closed_at' => null,
            ],
            ['id']
        );
        return !empty($collections);
    }

    /**
     * Get the open invoice collections for a customer
     * @param int $customer_number The E-conomic customer number
     * @return array The open invoice collections
     * @throws Exception If the request was not successful
     */
    public function getOpenInvoiceCollections(int $customer_number): array
    {
        $collections = self::getFieldsWhere(
            [
                'customer_number' => $customer_number,
                'closed_at' => null,
            ],
            ['id', 'name', 'notes', 'processor', 'external_id', 'created_at', 'updated_at', 'closed_at']
        );
        // Parse the results
        $result = [];
        foreach ( $collections as $collection ) {
            $this->id = $collection['id'];
            self::getObjectProperties();
            self::requireSelected();
            $result[] = self::asArray();
        }
        return $result;
    }

    /**
     * @param int $customer_number The E-conomic customer number
     * @return self The latest open invoice collection
     * @throws Exception If no open invoice collections were found
     * @throws Exception If the request was not successful
     */
    public function getLatestOpenInvoiceCollection(int $customer_number): self
    {
        $collections = self::getFieldsWhere(
            [
                'customer_number' => $customer_number,
                'closed_at' => null,
            ],
            ['id', 'name', 'notes', 'processor', 'external_id', 'created_at', 'updated_at', 'closed_at'],
        );
        if (empty($collections)) {
            throw new Exception('No open invoice collections found');
        }
        // Parse the results
        $this->id = $collections[0]['id'];
        self::getObjectProperties();
        self::requireSelected();
        return $this;
    }

    /**
     * Add the invoice collection to E-conomic
     * @param bool $ignore_closed If the function should ignore when the invoice collection is closed
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection was not found
     * @throws Exception If the customer number is not set
     * @throws Exception If the invoice collection is already closed
     */
    public function addToEconomic(bool $ignore_closed = false): self
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Require the customer number to be set
        if (empty($this->customer_number->value())) {
            throw new Exception('Customer number is not set');
        }
        if (!$ignore_closed) {
            // Require the invoice collection to be open
            self::requireOpen();
        }

        // Ensure the administration fee is added to the invoice collection
        //self::ensureAdministrationFee();

        // Create an economic draft
        self::createInvoiceDraft();

        // Check if the invoice draft exists
        if (!self::isDraftExisting()) {
            throw new Exception('Failed to create invoice draft');
        }

        // Add the invoices to the invoice draft
        self::addInvoicesToDraft(true);

        // Close the invoice collection
        // If the invoice collection is closed, we don't want to close it again
        if ($this->closed_at->value() === null) {
            self::closeCollection();
        }
        return $this;
    }

    /**
     * Require the invoice collection to be open
     * @throws Exception If the invoice collection is closed
     * @throws Exception If the request was not successful
     */
    public function requireOpen(): void
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Require the invoice collection to not be closed
        if (!empty($this->closed_at->value()) && !empty($this->processor->value())) {
            throw new Exception('Invoice collection is closed');
        }
    }

    /**
     * Create an invoice draft in E-conomic
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is not set
     * @throws Exception If the invoice collection is already closed
     * @throws Exception If the invoice draft already exists
     */
    public function createInvoiceDraft(): self
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Check if the invoice draft already exists
        if (self::isDraftExisting() || self::isBooked()) {
            throw new Exception('Invoice draft already exists, or invoice collection is already booked');
        }
        // Require the invoice draft to not already exist
        self::requireInvoiceDraftDoesNotExist();
        // Get the closed at date
        $date = date('Y-m-d H:i:s');
        // Check if the invoice collection is closed
        if (!empty($this->closed_at->value())) {
            $date = $this->closed_at->value();
        }
        // Convert the date to the correct format
        $date = date('Y-m-d', strtotime($date));
        // Create the invoice draft
        $economic = new economic();
        $response = $economic->invoices->drafts->add(
            $this->customer_number->value(),
            self::getExternalId(),
            $date
        );
        // Validate the response, by checking if the external id is set
        if (empty($response->references->other)) {
            throw new Exception('Invoice collection was not created successfully');
        }
        // Set the processor to E-conomic, if it's not already set to Stripe.
        $this->processor->set(ECONOMIC_PROCESSOR);
        // Object changed
        self::objectChanged();
        return $this;
    }

    /**
     * Require the invoice draft to not already exist
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice draft already exists
     */
    public function requireInvoiceDraftDoesNotExist(): void
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Try to get the invoice draft id from the external id
        try {
            self::getInvoiceDraftId();
            throw new Exception('Invoice draft already exists');
        } catch (Exception $e) {
            // Ignore the exception, as it is expected.
        }
    }

    /**
     * Create a new collected order invoice
     * @param int $customer_number The E-conomic customer number
     * @param string|null $name The name of the invoice (optional)
     * @param string|null $notes The notes for the invoice (optional)
     * @param int|null $processor The processor id (optional)
     * @return self The created object
     * @throws Exception If the object was not created successfully
     */
    public function add(int $customer_number, ?string $name = null, ?string $notes = null, ?int $processor = null, ?string $closed_at = null): self
    {
        global /** @var db $db */
        $db;
        // Sanitize the input
        $customer_number = $db->escape_string($customer_number);
        if (!empty($name)) {
            $name = $db->escape_string($name);
        }
        if (!empty($notes)) {
            $notes = $db->escape_string($notes);
        }
        if (!empty($processor)) {
            $processor = $db->escape_string($processor);
        }
        if (!empty($closed_at)) {
            $closed_at = $db->escape_string($closed_at);
            // Check if the closed_at date is in the correct format (YYYY-MM-DD HH:MM:SS)
            if (!preg_match('/^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$/', $closed_at)) {
                throw new Exception('Closed at date is not in the correct format');
            }
        }
        // Require the customer number to be of a valid customer
        self::requireValidCustomer($customer_number);
        // Add the object
        $tmp_id = self::add_object([
            'customer_number' => (int)$customer_number,
            'name' => $name,
            'notes' => $notes,
            ...(!empty($closed_at) ? ['closed_at' => (string)$closed_at] : []),
        ]);
        self::select((int)$tmp_id);
        self::requireSelected();
        // Set the processor
        if (!empty($processor)) {
            $this->processor->set($processor);
        }
        self::objectChanged();
        return $this;
    }

    /**
     * Require the E-conomic customer number to be a valid customer
     * @throws Exception If the customer number is not a valid customer
     */
    private static function requireValidCustomer(string $customer_number): void
    {
        $customers = new users_o();
        $customers->getUserByCustomerNumber((int)$customer_number);
        $customers->requireSelected();
    }

    public function objectChanged(): void
    {
        // Invalidate the cache, so the next time the object is requested, it will be fetched from the database
        self::deleteCached('asArray', $this->id);
    }

    /**
     * Get the external id of the invoice collection
     * @throws Exception If the request was not successful
     * @throws Exception If the UUID could not be generated
     * @throws Exception If the external id is not unique
     */
    public function getExternalId(): string
    {
        if (empty($this->external_id->value())) {
            $this->external_id->set(self::generateExternalId());
        }
        return $this->external_id->value();
    }

    /**
     * Generate a unique external id (UUID)
     * @return string The generated UUID
     * @throws Exception If the UUID could not be generated
     * @throws Exception If the request was not successful
     */
    public static function generateExternalId(): string
    {
        $uuid = bin2hex(random_bytes(16));
        return substr($uuid, 0, 8) . '-' . substr($uuid, 8, 4) . '-' . substr($uuid, 12, 4) . '-' . substr($uuid, 16, 4) . '-' . substr($uuid, 20);
    }

    /**
     * Add the invoices to the invoice draft
     * @param bool $skip_check If the check for the invoice collection being booked should be skipped
     * @throws Exception If the invoice collection is not set
     * @throws Exception If the invoice collection is already closed
     * @throws Exception If the invoice draft was not found
     * @throws Exception If the request was not successful
     */
    public function addInvoicesToDraft(bool $skip_check = false): self
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Require the invoice collection to be open
        self::requireInvoiceIsNotBooked();
        // Set the timeout to 0, to prevent the script from timing out
        set_time_limit(0);
        // Get the orders in the invoice collection
        $orders = self::getOrders();
        // Check if there are any orders in the invoice collection
        if (empty($orders)) {
            throw new Exception('No orders in invoice collection');
        }
        // Require the invoice draft to be set (and exists)
        if (!$skip_check) {
            self::requireInvoiceDraft();
        }
        // Get the invoice draft id from the external id
        $draft_id = self::getInvoiceDraftId();
        // Get the customer currency
        $currency = self::getCustomerCurrency($this->customer_number->value());
        // Sort the orders by date
        usort($orders, function ($a, $b) {
            return strtotime($a['created_at']) - strtotime($b['created_at']);
        });
        // Add the invoices to the invoice draft
        foreach ( $orders as $order ) {
            self::addInvoiceToDraft($order['id'], true, $draft_id, $currency);
        }
        // If the customer has the onlyTankCleaning attribute, add the environmental fee & oil fees to the invoice draft
        self::addEnvironmentalAndOilFeesToDraft($draft_id, $currency);
        // Object changed
        self::objectChanged();

        return $this;
    }

    /**
     * Add the environmental fee & oil fees to the invoice draft, if the customer has the onlyTankCleaning attribute
     * @param int $draft_id The invoice draft id
     * @param string $currency The currency to use
     * @throws Exception If the request was not successful
     */
    public function addEnvironmentalAndOilFeesToDraft(int $draft_id, string $currency): void
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Get the user object
        $user = (new users_o())->getUserByCustomerNumber($this->customer_number->value());
        // Check if the user exists
        if (!$user->id) {
            return;
        }
        // Check if the user has the onlyTankCleaning attribute
        if ($user->doesUserHaveAttribute('onlyTankCleaning') && (int)$user->customer_number->value() !== 999) {
            // Add the environmental fee & oil fees to the invoice draft
            $economic = new economic();
            $economic->invoices->draft->add_environmental_and_oil_fees(
                $draft_id,
                self::getTotalAmount(),
                $currency
            );
        }
    }

    /**
     * Ensure that the administration fee is added to the invoice collection, if it has not already been paid this month.
     * @return void
     * @throws Exception
     */
    public function ensureAdministrationFee(): void
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Get the user object
        $user = (new users_o())->getUserByCustomerNumber($this->customer_number->value());

        // Check if the user exists in our database
        if (!$user->id) {
            return;
        }

        // Check if the user is exempt from the administration fee
        if ($user->doesUserHaveAttribute('exemptFromAdministrationFee')) {
            return;
        }

        // Check if the user has already paid the administration fee this month
        if (self::hasPaidAdministrationFeeThisMonth()) {
            return;
        }

        // Get the fee product ID
        $fee_product_id = (int)(new economic_fee_product_id_c())->getVariableValue();
        // Get the fee price
        if ($user->invoicePerOrder()) {
            $price = (int)(new economic_admin_fee_order_c())->getVariableValue();
        } else {
            $price = (int)(new economic_admin_fee_monthly_c())->getVariableValue();
        }

        // Add the fee order
        $order = (new orders_o())->add(
            (int)$user->customer_number->value(),
            1857,
            '',
            '',
            10,
            '',
            '',
            ''
        );
        // Assign the order to the invoice collection
        $order->assignToInvoiceCollection($this->id);
        // Set the order created_at date to 23:59:59 on the last day of the month the invoice collection was created
        $invoice_collection_closed_at = $this->closed_at->value();
        $first_day_of_month = date('Y-m-01', strtotime($invoice_collection_closed_at)) . ' 00:00:01';
        $last_day_of_month = date('Y-m-t', strtotime($first_day_of_month)) . ' 23:59:59';
        $order->created_at->set($last_day_of_month);
        // Add the order item
        (new order_items_o())->addItemToOrder(
            (int)$order->id,
            $fee_product_id,
            1857,
            1,
            null,
            '',
            $price
        );
    }

    /**
     * Check if the customer has already paid the administration fee this month
     * @return bool
     * @throws Exception
     */
    public function hasPaidAdministrationFeeThisMonth(): bool
    {
        global $db;
        // Require the invoice collection to be selected
        self::requireSelected();
        // Get the customer number
        $customer_number = (int)$this->customer_number->value();
        // Get the fee product ID
        $fee_product_id = (int)(new economic_fee_product_id_c())->getVariableValue();
        // Get the first and last day of the month the invoice collection was closed
        $invoice_collection_closed_at = $this->closed_at->value();
        $first_day_of_month = date('Y-m-01', strtotime($invoice_collection_closed_at)) . ' 00:00:01';
        $last_day_of_month = date('Y-m-t', strtotime($first_day_of_month)) . ' 23:59:59';
        // Check if the customer has already paid the fee this month
        $sql = "SELECT COUNT(*) as count 
                FROM order_items oi
                JOIN orders o ON oi.order_id = o.id
                JOIN users u ON o.customer_id = u.customer_number
                WHERE u.customer_number = $customer_number
                AND oi.product_id = $fee_product_id
                AND o.closed_at BETWEEN '$first_day_of_month' AND '$last_day_of_month'
                AND o.deleted_at IS NULL";
        // Execute the query
        $result = $db->query($sql);
        $row = $result->fetch_assoc();
        return (int)$row['count'] > 0;
    }

    /**
     * Require the invoice collection to not be booked
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is already closed
     */
    public function requireInvoiceIsNotBooked(): void
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Require the invoice collection to not be booked
        if (!empty($this->booked_invoice_id->value())) {
            throw new Exception('Invoice collection is already booked');
        }
    }

    /**
     * Require the invoice draft to be set
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice draft was not found
     */
    public function requireInvoiceDraft(): void
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Require the invoice draft to be set
        if (empty($this->external_id->value())) {
            throw new Exception('Invoice draft is not set');
        }

        // Require the invoice draft to exist
        self::requireInvoiceDraftExists();
    }

    /**
     * Require the invoice draft to exist (in E-conomic)
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice draft was not found
     */
    public function requireInvoiceDraftExists(): void
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Get the invoice draft id from the external id
        $invoice_draft_id = self::getInvoiceDraftId();
        // Check if the invoice draft id is set
        if (empty($invoice_draft_id)) {
            throw new Exception('Invoice draft not found');
        }
    }

    /**
     * Get the customer currency
     * @return string The customer currency
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is not set
     */
    public function getCustomerCurrency(int $customer_number): string
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Get the customer object
        $customer = (new economic())->getCustomer($customer_number);
        // Get the customer currency
        return $customer->getCurrency();
    }

    /**
     * Add an invoice to the invoice draft
     * @param int $order_id The order id to add
     * @param bool $skip_check If the check for the invoice collection being booked should be skipped
     * @param string $currency The currency to use (default: DKK)
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is not set
     * @throws Exception If the invoice collection is already closed
     * @throws Exception If the invoice draft was not found
     */
    public function addInvoiceToDraft(int $order_id, bool $skip_check = false, int $draft_id = null, string $currency = null): self
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Get the order object
        $order = new orders_o();
        $order->select($order_id);
        $order->requireSelected();
        // Require the invoice draft to be set (and exists)
        if (!$skip_check) {
            // Require the invoice collection to be open
            self::requireInvoiceIsNotBooked();
            // Require the invoice draft to be set
            self::requireInvoiceDraft();
        }
        // Get the invoice draft id from the external id, if not set
        if (empty($draft_id)) {
            $draft_id = self::getInvoiceDraftId();
        }

        // If the currency is not set, get the customer currency
        if (empty($currency)) {
            $currency = (string)self::getCustomerCurrency($order->customer_id->value());
        }

        // Add the invoice to the invoice draft
        $economic = new economic();
        $economic->invoices->draft->add_order(
            $draft_id,
            $order,
            (string)$currency
        );

        return $this;
    }

    /**
     * Close the invoice collection
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is not set
     * @throws Exception If the invoice collection is already closed
     */
    public function closeCollection(): self
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Require the invoice collection to be open
        self::requireOpen();
        // Close the invoice collection
        $this->closed_at->set(date('Y-m-d H:i:s'));
        self::objectChanged();
        return $this;
    }

    public function listCustomersWithIndividualOrderInvoicing(): array
    {
        // Get all the customers with the "invoiceAllOrdersIndividually" attribute
        $users = new users_o();
        return $users->getCustomerNumbersWithAttributes([
            'invoiceAllOrdersIndividually'
        ]);
    }

    /**
     * Get the total invoices using E-conomic as the processor, with the given restrictions
     * @param $collected_order_invoices collected_order_invoices_o
     * @param $restrictions array
     * @param $view string The MySQL view to use for the query
     * @param $page int The page number to return
     * @param $limit int The number of results to return per page
     * @param $processor int
     * @return array The total invoices
     * @throws Exception If the request was not successful
     * @see processors for the processor types
     */
    function getTotalInvoices(collected_order_invoices_o $collected_order_invoices, array $restrictions = [], string $view = 'invoices_with_completed_orders', int $page = 1, int $limit = 100000, int $processor = 1): array
    {
        // Set the view
        $collected_order_invoices->setView($view);
        // Get the total invoices
        return $collected_order_invoices->listObjectsWithPagination(
            $page,
            $limit,
            null,
            [
                'processor' => $processor,
                ...$restrictions,
            ],
            null,
            function ($collected_order_invoice) {
                return $collected_order_invoice;
            },
        );
    }

    /**
     * @throws Exception
     */
    public function split(): void
    {
        self::requireSelected();
        // Determine the processor type
        switch ($this->processor->value()) {
            case null:
                break;
            case 1:
                self::requireInvoiceDraftDoesNotExist();
                self::requireInvoiceIsNotBooked();
                break;
            case 2:
                throw new Exception('Stripe invoice collections cannot be split');
                break;
            case 3:
                throw new Exception('Due to the stateless nature of the processor, invoice collections cannot be split. Please contact technical support for assistance.');
                break;
            default:
                throw new Exception('Invalid processor type');
        }
        // Get the orders in the invoice collection
        $orders = self::getOrders();
        // Check if there are any orders in the invoice collection
        if (empty($orders)) {
            throw new Exception('No orders in invoice collection');
        }
        // Check if the invoice collection is closed
        if (!empty($this->closed_at->value())) {
            $closed_at = $this->closed_at->value();
        }
        // Create a new invoice collection for each order
        foreach ( $orders as $order ) {
            // Get the order object
            $order_object = new orders_o();
            $order_object->select((int)$order['id']);
            // Create a new invoice collection
            $new_invoice_collection = new collected_order_invoices_o();
            $tmp = $new_invoice_collection->add(
                (int)$this->customer_number->value(),
                $this->name->value(),
                $this->notes->value(),
                null
            );
            // Set the closed at date
            if (!empty($closed_at)) {
                $tmp->closed_at->set($closed_at);
            }
            // Set the order to the new invoice collection
            $order_object->invoice_collection_id->set($tmp->id);
        }
        // Invalidate the cache for the invoice collection
        $this->objectChanged();
    }

    /**
     * Add the vehicle subscriptions transaction to the invoice collection
     * @throws Exception If the invoice collection is not selected
     */
    public function addVehicleSubscriptionsTransaction(): void
    {
        self::requireSelected();
        self::removeVehicleSubscriptionsTransactions();
        // Get the orders in the invoice collection
        $orders = self::getOrders();
        // Remove any orders exempted from invoicing
        $orders = array_filter($orders, function ($order) {
            $order_object = new orders_o();
            $order_object->select((int)$order['id']);
            $order_object->requireSelected();
            return $order_object->isIncludedInInvoicing();
        });
        // Get the customers vehicle subscriptions
        $vehicles_o = new customer_vehicles_o();
        $vehicle_ids = $vehicles_o->getFieldsWhere(
            [
                'customer_id' => $this->customer_number->value(),
                'wash_subscription' => 1,
            ],
            ['id']
        );
        // Get the vehicle objects
        $vehicles = [];
        foreach ( $vehicle_ids as $vehicle_id ) {
            $vehicle = (new customer_vehicles_o())->select((int)$vehicle_id['id']);
            $vehicle->requireSelected();
            $vehicles[] = $vehicle;
        }
        $vehicle_array = [];
        $vehicle_regs = [];
        // Loop through the vehicles and add them to the invoice collection
        /** @var customer_vehicles_o $vehicle */
        foreach ( $vehicles as $vehicle ) {
            // Get the vehicle subscription addons
            $addons = [];
            /** @var customer_vehicles_addons_o $addon */
            foreach ( $vehicle->getAddons() as $addon ) {
                $addon->requireSelected();
                $product_id = (int)(new product_options_o())->select((int)$addon->addon_id->value())->option_id->value();
                $addons[$product_id] = [
                    'amount' => (int)$addon->amount->value(),
                    'product_id' => $product_id,
                ];
            }
            // Get the vehicle subscription
            $vehicle_subscription = [
                'reg' => $vehicle->reg->value(),
                'product_id' => $vehicle->type->value(),
                'addons' => $addons,
            ];
            $vehicle_array[] = $vehicle_subscription;
            $vehicle_regs[$vehicle->reg->value()] = [
                'product_id' => $vehicle->type->value(),
                'reg' => $vehicle->reg->value(),
                'addons' => $addons,
            ];
        }

        // Check if there are any vehicle subscriptions
        if (empty($vehicle_array)) {
            return;
        }

        // Create the vehicle subscriptions transaction
        $transaction = new orders_o();
        $transaction->add(
            (int)$this->customer_number->value(),
            $this->economic_wash_subscription_user_id,
            'Vaskeabonnementer',
            '',
            10,
        );
        $transaction->invoice_collection_id->set($this->id);
        $transaction->created_at->set(self::getFirstDayOfMonth($this->created_at->value()));
        $transaction->objectChanged();
        $this->objectChanged();
        // Add the vehicle subscriptions to the transaction
        $order_items_o = new order_items_o();
        foreach ( $vehicle_array as $vehicle ) {
            $order_items_o->add(
                (int)$transaction->id,
                (int)$vehicle['product_id'],
                (string)$vehicle['reg'],
                (string)'',
                (int)$this->economic_wash_subscription_user_id,
                (int)self::getWashSubscriptionPrice((new products_o)->select((int)$vehicle['product_id'])->price->value()) / 2,
                (int)2,
            );
            // Add the addons to the transaction
            foreach ( $vehicle['addons'] as $addon ) {
                $tmp_subscription_price = (int)self::getWashSubscriptionPrice((new products_o)->select((int)$addon['product_id'])->price->value()) / 2;
                // If the product is in the free list, set the price to 0
                $free_subscription_addons = [
                    23, // Spot Free- Varevogn
                    24, // Spot Free- Lastbil
                    21, // Undervognsskyld pr. enhed.

                ];
                if (in_array($addon['product_id'], $free_subscription_addons)) {
                    $tmp_subscription_price = 0;
                }
                $order_items_o->add(
                    (int)$transaction->id,
                    (int)$addon['product_id'],
                    (string)$vehicle['reg'],
                    (string)'',
                    (int)$this->economic_wash_subscription_user_id,
                    (int)$tmp_subscription_price,
                    (int)2,
                    (int)$order_items_o->id
                );
            }
        }
        $order_items_hidden = [];
        $order_item_addons_hidden = [];
        // Loop through the orders and check if the orders reg_1 has the same reg as the vehicle
        foreach ( $orders as $order ) {
            // Check if the orders reg_1 is equal to any of the vehicles reg
            if (isset($vehicle_regs[$order['reg_1']])) {
                // Get the order object
                $order_object = new orders_o();
                $order_object->select((int)$order['id']);
                $order_object->requireSelected();
                // Check if any of the products in the order are the same as the vehicles subscription
                $order_items = $order_object->getOrderItems($order_object->id);
                foreach ( $order_items as $order_item ) {
                    // Check if the order item product id is equal to the vehicle subscription product id, or the addon product id
                    if ($order_item['product_id'] == $vehicle_regs[$order['reg_1']]['product_id']) {
                        // Check if the limit is reached (2)
                        if (isset($order_items_hidden[$order['reg_1']]) && count($order_items_hidden[$order['reg_1']]) >= 2) {
                            continue;
                        }
                        // Add the order item to the hidden order items
                        $order_items_hidden[$order['reg_1']][] = $order_item['id'];
                        // Set the order item to be hidden
                        $order_item_object = new order_items_o();
                        $order_item_object->select((int)$order_item['id']);
                        $order_item_object->requireSelected();
                        $order_item_object->include_in_invoice->set(0);
                        $order_item_object->price->set((int)self::getWashSubscriptionPrice((new products_o())->select((int)$order_item['product_id'])->price->value()) / 2);
                        $order_item_object->objectChanged();
                    }
                    // Check if the order item product id is equal any of the vehicle subscription addons product id
                    if (isset($vehicle_regs[$order['reg_1']]['addons'][$order_item['product_id']])) {
                        // Check if the limit is reached (2)
                        if (isset($order_item_addons_hidden[$order['reg_1']][$order_item['product_id']]) && count($order_item_addons_hidden[$order['reg_1']][$order_item['product_id']]) >= 2) {
                            continue;
                        }
                        // Check if the product is in the free list, set the price to 0
                        $free_subscription_addons = [
                            23, // Spot Free- Varevogn
                            24, // Spot Free- Lastbil
                        ];
                        if (in_array($order_item['product_id'], $free_subscription_addons)) {
                            $tmp_subscription_price = (int)0;
                        } else {
                            $tmp_subscription_price = (int)self::getWashSubscriptionPrice((new products_o())->select((int)$order_item['product_id'])->price->value()) / 2;
                        }
                        // Add the order item to the hidden order items
                        $order_item_addons_hidden[$order['reg_1']][$order_item['product_id']][] = $order_item['id'];
                        // Set the order item to be hidden
                        $order_item_object = new order_items_o();
                        $order_item_object->select((int)$order_item['id']);
                        $order_item_object->requireSelected();
                        $order_item_object->include_in_invoice->set(0);
                        $order_item_object->price->set($tmp_subscription_price);
                        $order_item_object->objectChanged();
                    }
                }
                $order_object->objectChanged();
            }
        }
        $this->objectChanged();
        //print_r($vehicle_array);
    }

    /**
     * Remove the vehicle subscriptions transactions from the invoice collection
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is not set
     * @throws Exception If the invoice collection is already closed
     * @throws Exception If the invoice draft already exists
     * @throws Exception If the invoice draft was not found
     */
    private function removeVehicleSubscriptionsTransactions(): void
    {
        self::requireSelected();
        // Get the ids of the transactions to remove
        $orders_o = new orders_o();
        $transactions = $orders_o->getFieldsWhere(
            [
                'invoice_collection_id' => $this->id,
                'deleted_at' => null,
                'cashier_id' => $this->economic_wash_subscription_user_id,
            ],
            ['id']
        );
        // Remove the transactions
        foreach ( $transactions as $transaction ) {
            $order = new orders_o();
            $order->select((int)$transaction['id']);
            $order->requireSelected();
            $order->delete();
        }
        // Clear the cache for the invoice collection
        $this->objectChanged();
    }

    private static function getFirstDayOfMonth(string $timestamp): string
    {
        $date = new \DateTime($timestamp);
        $date->modify('first day of this month');
        // Set the time to 00:00:00
        $date->setTime(0, 0, 1);
        return $date->format('Y-m-d H:i:s');
    }

    /**
     * Get the wash subscription price
     * @param float $price The price of the wash subscription
     * @return float The wash subscription price
     */
    public static function getWashSubscriptionPrice(float $price): float
    {
        return $price * 1.20;
    }

    /**
     * Remove any special arrangements from the invoice collection
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is not set
     * @throws Exception If the invoice collection is already closed
     * @see self::removeVehicleSubscriptionsTransactions() for more information
     * @see self::resetPricesOfItemsNotIncludedInInvoice() Should be called after this function if needed
     * @see self::setAllItemsToBeIncludedInInvoice() Should be called after this function if needed
     */
    public function removeSpecialArrangements(): void
    {
        self::requireSelected();
        self::removeVehicleSubscriptionsTransactions();
        // Invalidate the cache for the invoice collection
        $this->objectChanged();
    }
    /**
     * Set all items in the invoice collection to be included in the invoice
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is not set
     * @throws Exception If the invoice collection is already closed
     * @see self::removeSpecialArrangements() Should be called before this function if needed
     * @see self::resetPricesOfItemsNotIncludedInInvoice() Should be called before this function if needed
     */
    public function setAllItemsToBeIncludedInInvoice(): void
    {
        self::requireSelected();
        // Get the orders in the invoice collection
        $orders = self::getOrders();
        // Check if there are any orders in the invoice collection
        if (empty($orders)) {
            throw new Exception('No orders in invoice collection');
        }
        // Loop through the orders and set all items to be included in the invoice
        foreach ( $orders as $order ) {
            // Get the order object
            $order_object = new orders_o();
            $order_object->select((int)$order['id']);
            $order_object->requireSelected();
            $tmp_order_items = $order_object->getOrderItems($order_object->id);
            // Loop through the order items and set them to be included in the invoice
            foreach ( $tmp_order_items as $order_item ) {
                // Set the order item to be included in the invoice
                $order_item_object = new order_items_o();
                $order_item_object->select((int)$order_item['id']);
                $order_item_object->requireSelected();
                $order_item_object->include_in_invoice->set(1);
                $order_item_object->objectChanged();
            }
            $order_object->objectChanged();
        }
        // Invalidate the cache for the invoice collection
        $this->objectChanged();
    }

    /**
     * Set the invoice collection as paid with Stripe
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is not set
     * @throws Exception If the invoice collection is already closed
     * @throws Exception If the invoice collection is already booked
     */
    public function paidWithStripe(string $stripe_payment_intent_id): void
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Require the invoice collection to be open
        self::requireOpen();
        // Require the processor to be Stripe (or null)
        if (!empty($this->processor->value()) && $this->processor->value() != 2) {
            throw new Exception('Invoice collection is not paid with Stripe');
        }
        // Set the stripe payment intent id
        $this->external_id->set($stripe_payment_intent_id);
        // Set the processor to Stripe
        $this->processor->set(STRIPE_PROCESSOR);
        // Set the closed at date (if not already set)
        if (empty($this->closed_at->value())) {
            $this->closed_at->set(date('Y-m-d H:i:s'));
        }

        // Object changed
        self::objectChanged();
    }

    /**
     * @throws Exception
     */
    public function overridePricesFixed(int $fixed_price): void
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Get the orders in the invoice collection
        $orders = self::getOrders();
        // Check if there are any orders in the invoice collection
        if (empty($orders)) {
            // This has been removed as it is valid to have an invoice collection with no orders, if the customer only has a fixed price agreement
            // throw new Exception('No orders in invoice collection'); DON'T RE-ADD THIS.
        }
        self::removeVehicleSubscriptionsTransactions();
        // Create the fixed prices transaction
        $transaction = new orders_o();
        $transaction->add(
            (int)$this->customer_number->value(),
            $this->economic_wash_subscription_user_id,
            'Fast pris aftale',
            '',
            10,
        );
        $transaction->invoice_collection_id->set($this->id);
        $transaction->created_at->set(self::getFirstDayOfMonth($this->created_at->value()));
        $transaction->objectChanged();
        // Add an order item to the transaction with a fixed price
        $order_items_o = new order_items_o();
        $order_items_o->add(
            (int)$transaction->id,
            (int)61,
            (string)'',
            (string)'',
            (int)$this->economic_wash_subscription_user_id,
            (int)$fixed_price,
            (int)1,
        );
        // Loop through the orders and set the prices to fixed
        foreach ( $orders as $order ) {
            // Get the order object
            $order_object = new orders_o();
            $order_object->select((int)$order['id']);
            $order_object->requireSelected();
            $tmp_order_items = $order_object->getOrderItems($order_object->id);
            // Loop through the order items and set the prices to 0, while hiding the order items
            foreach ( $tmp_order_items as $order_item ) {
                // Set the order item to be hidden
                $order_item_object = new order_items_o();
                $order_item_object->select((int)$order_item['id']);
                $order_item_object->requireSelected();
                $order_item_object->include_in_invoice->set(0);
                $order_item_object->price->set(0);
                $order_item_object->objectChanged();
            }
        }
        // Invalidate the cache for the invoice collection
        $this->objectChanged();
    }

    /**
     * This function unlinks the invoice collection from E-conomic by clearing the external_id and processor fields.
     * This can be useful if you want to reset the integration or if there was an error during the linking process.
     * Note: This function does not delete any invoices or drafts in E-conomic, it only removes the link from the local system.
     * This can be useful when a booked invoice needs to be changed, and a new invoice needs to be created in E-conomic.
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is not set
     * @return void
     */
    public function unlinkFromEconomic(): void
    {
        // Require the invoice collection to be selected
        self::requireSelected();
        // Clear the external_id and processor fields
        $this->external_id->set(null);
        $this->processor->set(null);
        $this->booked_invoice_id->set(null);
        // Object changed
        self::objectChanged();
    }

    /**
     * Reset the prices of items not included in the invoice to their original product price
     * @throws Exception If the request was not successful
     * @throws Exception If the invoice collection is not set
     * @throws Exception If the invoice collection is already closed
     * @see self::removeSpecialArrangements() Should be called before this function if needed
     * @see self::setAllItemsToBeIncludedInInvoice() Should be called before this function if needed
     */
    public function resetPricesOfItemsNotIncludedInInvoice(): void
    {
        self::requireSelected();
        // Get the orders in the invoice collection
        $orders = self::getOrders();
        // Check if there are any orders in the invoice collection
        if (empty($orders)) {
            throw new Exception('No orders in invoice collection');
        }
        // Loop through the orders and reset the prices of items not included in the invoice
        foreach ( $orders as $order ) {
            // Get the order object
            $order_object = new orders_o();
            $order_object->select((int)$order['id']);
            $order_object->requireSelected();
            $tmp_order_items = $order_object->getOrderItems($order_object->id);
            // Loop through the order items and reset the prices of items not included in the invoice
            foreach ( $tmp_order_items as $order_item ) {
                if ($order_item['include_in_invoice'] == 0) {
                    // Set the order item to be included in the invoice
                    $order_item_object = new order_items_o();
                    $order_item_object->select((int)$order_item['id']);
                    $order_item_object->requireSelected();
                    // Reset the price to the product price
                    $product = (new products_o())->select((int)$order_item_object->product_id->value());
                    $product->requireSelected();
                    // Get the product price
                    $order_item_object->price->set((int)$order_object->getCustomerProductPrice($product));
                    $order_item_object->objectChanged();
                }
            }
            $order_object->objectChanged();
        }
        // Invalidate the cache for the invoice collection
        $this->objectChanged();
    }

    /**
     * @throws Exception
     */
    public function isEmpty(): bool
    {
        global $db;
        self::requireSelected();
        // Check if there are any orders in the invoice collection
        $sql = "SELECT COUNT(*) as count 
                FROM orders 
                WHERE invoice_collection_id = " . (int)$this->id . " 
                AND deleted_at IS NULL";
        $result = $db->query($sql);
        $row = $result->fetch_assoc();
        return (int)$row['count'] === 0;
    }
}