<?php

namespace objects;

use classes\db;
use classes\object_property;
use Exception;
use traits\db_object_t;

class product_options_o extends db
{
    use db_object_t;

    public object_property $product_id;
    public object_property $option_id;
    public object_property $name;
    public object_property $min;
    public object_property $max;

    public static function getPDO(): \PDO
    {
        return db::getPDO();
    }

    private static function fetchRow(string $query, array $params): array
    {
        $stmt = self::getPDO()->prepare($query);
        $stmt->execute($params);
        $result = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $result ?: [];
    }

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

    /**
     * Add a product option, and set this object to the new object
     * @param int $product_id
     * @param int $option_id
     * @return void
     * @throws Exception If the object was not created successfully
     */
    public function add(int $product_id, int $option_id): void
    {
        $tmp_id = self::add_object([
            'product_id' => $product_id,
            'option_id' => $option_id
        ]);
        $this->id = $tmp_id;
        self::getObjectProperties();
        self::objectChanged();
    }

    public function getObjectProperties(): void
    {
        $this->product_id = new object_property($this->table, $this->id, 'product_id', 'int', false);
        $this->option_id = new object_property($this->table, $this->id, 'option_id', 'int', false);
        $this->name = new object_property($this->table, $this->id, 'name', 'string', false);
        $this->min = new object_property($this->table, $this->id, 'min', 'int', false);
        $this->max = new object_property($this->table, $this->id, 'max', 'int', false);
    }

    public function objectChanged(): void
    {
        //TODO: Add cache invalidation
    }

    /**
     * There's no need to keep track of when a product option is deleted,
     * so we just forcefully delete it.
     * @throws Exception If the object was not selected
     * @throws Exception If the object was not deleted successfully
     */
    public function delete(): void
    {
        self::requireSelected();
        self::deletePermanently();
    }

    /**
     * Set the name of the product option
     * @param string $name
     * @throws Exception If the object was not selected
     */
    public function set_name(string $name): void
    {
        self::requireSelected();
        // If the string is empty, set it to null
        if (empty($name)) {
            $this->name->nullify();
            self::objectChanged();
            return;
        }
        $this->name->set($name);
        self::objectChanged();
    }

    /**
     * Set the min value of the product option
     * @param int|null $min
     * @notation If the int is less than 1 or empty, set it to null
     * @throws Exception If the object was not selected
     */
    public function set_min(int|null $min): void
    {
        self::requireSelected();
        // If the int is empty, set it to null
        if (empty($min) || $min < 1) {
            $this->min->nullify();
            self::objectChanged();
            return;
        }
        $this->min->set($min);
        self::objectChanged();
    }

    /**
     * Set the max value of the product option
     * @param int|null $max
     * @notation If the int is less than 1, or empty, it will be set to null
     * @throws Exception If the object was not selected
     */
    public function set_max(int|null $max): void
    {
        self::requireSelected();
        // If the int is empty, set it to null
        if (empty($max) || $max < 1) {
            $this->max->nullify();
            self::objectChanged();
            return;
        }
        $this->max->set($max);
        self::objectChanged();
    }

    /**
     * Get all options for a product
     * @param int $id The id of the product
     * @return array
     * @throws Exception If the object was not selected
     */
    public function getProductOptions(int $id): array
    {
        $options = self::getFieldsWhere(['product_id' => $id],
            [
                'id',
                'product_id',
                'option_id',
                'name',
                'min',
                'max',
                'created_at',
                'updated_at'
            ]
        );
        $tmp = [];
        foreach ( $options as $option ) {
            $tmp_product = (new products_o())->select($option['option_id'])->asArray();
            $tmp[] = [
                'id' => (int)$option['id'],
                'product_id' => (int)$option['product_id'],
                'option_id' => (int)$option['option_id'],
                'name' => (string)$option['name'] === '' ? $tmp_product['name'] : (string)$option['name'],
                'min' => $option['min'] === null ? 0 : (int)$option['min'],
                'max' => $option['max'] === null ? -1 : (int)$option['max'],
                'created_at' => (string)$option['created_at'],
                'updated_at' => (string)$option['updated_at'],
                'price' => (int)$tmp_product['price'],
                'product' => $tmp_product
            ];
        }
        return $tmp;
    }

    /**
     * Get the product option as an array
     * @return array
     * @throws Exception If the object was not selected
     */
    public function asArray(): array
    {
        self::requireSelected();
        return [
            'id' => (int)$this->id,
            'product_id' => (int)$this->product_id->value(),
            'option_id' => (int)$this->option_id->value(),
            'name' => (string)$this->name->value(),
            'min' => $this->min->value() === null ? null : (int)$this->min->value(),
            'max' => $this->max->value() === null ? null : (int)$this->max->value()
        ];
    }

    /**
     * Get all products with a specific option
     * @param int $product_id The id of the addon product
     * @return array
     */
    public function getOptionProducts(int $product_id): array
    {
        return self::getFieldsWhere(['option_id' => $product_id], ['product_id']);
    }

    public function isOptionAllowedOnType(int $option_id, int $primary_type_id): bool
    {
        // Check if the option is allowed on the primary type
        $allowed_options = self::getFieldsWhere(
            ['option_id' => $option_id, 'product_id' => $primary_type_id],
            ['id']
        );
        return !empty($allowed_options);
    }

    public function countSoldAddonsInDateRange(int $product_id, string $date_start, string $date_end, int $department_id): int
    {
        global /** @var db $db */
        $db;
        /**
         * Count the number of sold addons (order items that's related to any another product)
         * The order they were sold in must:
         * - Be within the date range
         * - Belong to the specified department
         * - Have deleted_at IS NULL
         * The item must:
         * - Be an addon (related_item_id IS NOT NULL)
         * - Not be deleted (deleted_at IS NULL)
         */
        // Sanitize inputs
        $product_id = (int)$product_id;
        $date_start = date('Y-m-d 00:00:00', strtotime($date_start));
        $date_end = date('Y-m-d 23:59:59', strtotime($date_end));
        $department_id = (int)$department_id;
        $query = "
            SELECT SUM(oi.quantity) AS sold_addons_count
            FROM order_items oi
            INNER JOIN orders o ON oi.order_id = o.id
            AND oi.product_id = {$product_id}
            AND oi.deleted_at IS NULL
            AND o.department_id = {$department_id}
            AND o.created_at BETWEEN '{$date_start}' AND '{$date_end}'
            AND o.deleted_at IS NULL
        ";
        return (int)$db->query($query)->fetch_object()->sold_addons_count;
    }

    public function getMaxAddonsInDateRange(int $product_id, string $date_start, string $date_end, int $department_id): int
    {
        /**
         * Get the number of addons that could have been sold (maximum)
         * Every order item that has the product_id as an option should be counted (number of orders with that order item * the product_option max ?? 0)
         * The order they were sold in must:
         * - Be within the date range
         * - Belong to the specified department
         * - Have deleted_at IS NULL
         */
        global /** @var db $db */
        $db;
        // Sanitize inputs
        $product_id = (int)$product_id;
        $date_start = date('Y-m-d 00:00:00', strtotime($date_start));
        $date_end = date('Y-m-d 23:59:59', strtotime($date_end));
        $department_id = (int)$department_id;

        // Get all products that can have this addon
        $applicable_products = $this->getOptionProducts($product_id);
        if (empty($applicable_products)) {
            return 0;
        }
        $applicable_product_ids = array_column($applicable_products, 'product_id');
        $applicable_product_ids_str = implode(',', $applicable_product_ids);

        // Build the query (oi.quantity * (po.max ? (po.max >= 1 ? po.max : 1) : 1) )
        $query = "
            SELECT SUM(oi.quantity * (CASE 
                WHEN po.max IS NULL THEN 1
                WHEN po.max < 1 THEN 1
                ELSE po.max
            END)) AS max_addons_count
            FROM order_items oi
            INNER JOIN orders o ON oi.order_id = o.id
            INNER JOIN products_options po ON oi.product_id = po.product_id
            WHERE oi.product_id IN ({$applicable_product_ids_str})
            AND po.option_id = {$product_id}
            AND oi.deleted_at IS NULL
            AND o.department_id = {$department_id}
            AND o.created_at BETWEEN '{$date_start}' AND '{$date_end}'
            AND o.deleted_at IS NULL
        ";

        return (int)$db->query($query)->fetch_object()->max_addons_count;
    }
}