<?php

class RTProductOptions
{
    /**
     * Expected fields from Rentopian.
     */
    public $id
        ,$title
        ,$details
        ,$once_per_order;


    /**
     * Create a new instance.
     *
     * @param int $id
     * @param string $title
     * @param longtext $option_values
     */
    public function __construct($id, $title = '', $once_per_order = 0, $details = '')
    {
        $this->id = intval($id);
        $this->title = $title;
        $this->once_per_order = $once_per_order;
        $this->details = $details;
    }


    /**
     * Create or update a product_options.
     *
     * @return object
     */
    public function save()
    {
        global $wpdb, $rental_tables;
        $rental_product_options = $wpdb->prefix . $rental_tables["product_options"];

        $option_id = $this->id;
        $exists = $wpdb->get_var( $wpdb->prepare( "SELECT `id`, `option_values` FROM $rental_product_options WHERE `id` = %d ", [$this->id] ) );
        if ($exists) {
            // update
            $where = ['id' => $option_id];
            $result = $wpdb->update( $rental_product_options, [ 
                'title' => $this->title
                ,'once_per_order' => $this->once_per_order
                ,'option_values' => json_encode($this->details["option_values"])
            ], $where );

            $this->update($option_id);

            if (false === $result) {
                return $result;
            } 

        } else {
            // insert
            $result = $wpdb->insert( $rental_product_options, [ 
                'id' => $option_id
                ,'title' => $this->title
                ,'once_per_order' => $this->once_per_order
                ,'option_values' => json_encode($this->details["option_values"])
            ]);

            $this->create($option_id);
            
            if (false === $result) {
                return $result;
            }
        }

        return $option_id;
    }

    public function update($updated_option_id)
    {
        global $wpdb, $rental_tables;
        $rental_product_options = $wpdb->prefix . $rental_tables["product_options"];
        $rental_product_option_relations = $wpdb->prefix . $rental_tables["product_option_relations"];
        $rental_product_relations = $wpdb->prefix . $rental_tables["product_relations"];
        $rental_category_relations = $wpdb->prefix . $rental_tables["category_relations"];

        // collect every division's rental product ids
        $product_ids = $wpdb->get_results("SELECT id, rental_id FROM {$rental_product_relations}", ARRAY_A);
        // collect rental category ids
        $category_ids = $wpdb->get_results("SELECT id, rental_id FROM {$rental_category_relations}", ARRAY_A);

        $sql = "
            SELECT 
                options.*,
                GROUP_CONCAT(DISTINCT relation_products.wp_id) wp_product_ids,
                GROUP_CONCAT(DISTINCT relation_cats.wp_id) wp_cat_ids
            FROM 
                {$rental_product_options} as options
            LEFT JOIN {$rental_product_option_relations} as relation_products ON relation_products.po_id = options.id AND relation_products.type = 1
            LEFT JOIN {$rental_product_option_relations} as relation_cats ON relation_cats.po_id = options.id AND relation_cats.type = 2
            WHERE
                options.id <> %d
            GROUP BY
                options.id
        ";
        $product_options = $wpdb->get_results($wpdb->prepare($sql, $updated_option_id), ARRAY_A);

        $attached_items_grouped = [];
        // adding updated option
        $attached_items_grouped[$updated_option_id] = [
            'products' => $this->details['products_attached'],
            'cats' => $this->details["categories_attached"]
        ];
        // adding all other options
        foreach($product_options as $product_option) {
            $attached_items_grouped[$product_option['id']] = [
                'products' => explode(',',$product_option['wp_product_ids']),
                'cats' => explode(',',$product_option['wp_cat_ids'])
            ];
        }

        $rental_product_option_relations_sql = '';
        $productmeta_sql = [];
        $_option_ids = [];
        // grouping attached products and categories based on option id
        foreach($attached_items_grouped as $option_id=>$items) {
            
            $cats_wp_id_collection = [];
            $cats_rental_wp_id_collection = [];
            $product_wp_id_collection = [];
            // collecting wp product ids related to rental product ids for the current option
            if (!empty($items['products'])) {
                foreach($product_ids as $pid) {
                    // for updated option
                    if ($updated_option_id == $option_id) {
                        if (in_array($pid["rental_id"], $items['products'])) {
                            // collect products grouped by rental product id
                            // suporting all divisions
                            $product_wp_id_collection[$pid["rental_id"]][] = $pid["id"];
                        }
                    } else {
                        // other options wp_ids are already known
                        // for other options
                        foreach($items['products'] as $wp_id) {
                            if ($pid["id"] == $wp_id) {
                                $_option_ids[$wp_id][] = $option_id;
                            }
                        }
                    }
                }
            }

            $category_product_ids=[];
            // collecting wp cat ids related to rental cat ids for the current option
            if (!empty($items['cats'])) {
                
                foreach($category_ids as $cid) {
                    // for updated option
                    if ($updated_option_id == $option_id) {
                        if ( in_array($cid["rental_id"], $items['cats']) ) {
                            // group wp cats by rental category id
                            $cats_rental_wp_id_collection[$cid["rental_id"]] = $cid["id"];
                            // group wp cats
                            $cats_wp_id_collection[] = $cid["id"];
                        }
                    } else {
                        // for other options
                        foreach($items['cats'] as $wp_cat_id) {
                            if ( $cid["id"] == $wp_cat_id ) {
                                $cats_wp_id_collection[] = $wp_cat_id;
                            }
                        }
                    }
                    
                }

                if ($cats_wp_id_collection) {
                    $placeholders = array_fill(0, count($cats_wp_id_collection), '%d');
                    $placeholders_format = implode(', ', $placeholders);
                    $sql = "
                        SELECT 
                            object_id
                        FROM 
                            {$wpdb->term_relationships} terms
                        LEFT JOIN {$wpdb->posts} posts ON posts.id = terms.object_id
                        WHERE
                            term_taxonomy_id IN ({$placeholders_format})
                            AND posts.post_type = 'product'
                            AND posts.post_status = 'publish'
                    ";
                    $product_ids_by_category_ids = $wpdb->get_results($wpdb->prepare($sql, $cats_wp_id_collection), ARRAY_A);
    
                    if ($product_ids_by_category_ids) {
                        foreach($product_ids_by_category_ids as $pid) {
                            $category_product_ids[] = $pid["object_id"];
                        }
                    }
                }
                
            }

            // for updated option
            if ($updated_option_id == $option_id) {
                if (!empty($product_wp_id_collection)) {
                    // sql values of the option products (type 1 = product)
                    foreach ($product_wp_id_collection as $rental_product_id => $wp_id_list) {
                        foreach($wp_id_list as $wp_id) {
                            $_option_ids[$wp_id][] = $option_id;
                            // product option relation sql
                            $rental_product_option_relations_sql .= empty($rental_product_option_relations_sql) ? "('$option_id', '$rental_product_id', '$wp_id', '1')" : ",('$option_id', '$rental_product_id', '$wp_id', '1')";
                        }
                    }
                }
            }

           
            if (!empty($category_product_ids)) {
                foreach ($category_product_ids as $wp_id) {
                    $_option_ids[$wp_id][] = $option_id;
                }
            }

            if (!empty($cats_rental_wp_id_collection)) {
                // sql values of the option categories (type 2 = category)
                if ($updated_option_id == $option_id) {
                    foreach ($cats_rental_wp_id_collection as $rental_cat_id => $wp_cat_id) {
                        
                        $rental_product_option_relations_sql .= empty($rental_product_option_relations_sql) ? "('$option_id', '$rental_cat_id', '$wp_cat_id', '2')" : ",('$option_id', '$rental_cat_id', '$wp_cat_id', '2')";
                        
                    }
                }
            }
        }

        $wpdb->query($wpdb->prepare("DELETE FROM {$rental_product_option_relations} WHERE po_id = %d", $updated_option_id));
        $wpdb->query("INSERT INTO {$rental_product_option_relations} (`po_id`, `rental_id`, `wp_id`, `type`) VALUES " . $rental_product_option_relations_sql);

        // delete all product options postmeta
        $sql_delete = "DELETE FROM {$wpdb->postmeta} WHERE meta_key = '_product_options'";
        $wpdb->query($sql_delete);
       
        // insert new relations
        foreach($_option_ids as $wp_id => $option_ids) {
            $option_ids_rearranged = array_values(array_unique($option_ids));
            $option_ids = json_encode($option_ids_rearranged);
            $productmeta_sql[$wp_id] = "($wp_id, '_product_options', '$option_ids')";
        }
        rental_insert("INSERT INTO `$wpdb->postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES", $productmeta_sql);

    }


    public function create($created_option_id)
    {
        global $wpdb, $rental_tables;
        $rental_product_option_relations = $wpdb->prefix . $rental_tables["product_option_relations"];
        $rental_product_relations = $wpdb->prefix . $rental_tables["product_relations"];
        $rental_category_relations = $wpdb->prefix . $rental_tables["category_relations"];

        // collect every division's rental product ids
        $product_ids = $wpdb->get_results("SELECT id, rental_id FROM {$rental_product_relations}", ARRAY_A);
        // collect rental category ids
        $category_ids = $wpdb->get_results("SELECT id, rental_id FROM {$rental_category_relations}", ARRAY_A);

        $productmeta_sql = [];
        $attached_items_grouped = [];
        // adding updated option
        $attached_items_grouped[$created_option_id] = [
            'products' => $this->details['products_attached'],
            'cats' => $this->details["categories_attached"]
        ];

        $rental_product_option_relations_sql = '';
        $productmeta_sql = [];
        $_option_ids = [];
        // grouping attached products and categories based on option id
        $cats_wp_id_collection = [];
        $cats_rental_wp_id_collection = [];
        $product_wp_id_collection = [];
        // collecting wp product ids related to rental product ids for the current option
        if (!empty($attached_items_grouped[$created_option_id]['products'])) {
            foreach($product_ids as $pid) {
                if (in_array($pid["rental_id"], $attached_items_grouped[$created_option_id]['products'])) {
                    // collect products grouped by rental product id
                    // suporting all divisions
                    $product_wp_id_collection[$pid["rental_id"]][] = $pid["id"];
                }
            }
        }

        $category_product_ids=[];
        // collecting wp cat ids related to rental cat ids for the current option
        if (!empty($attached_items_grouped[$created_option_id]['cats'])) {
            foreach($category_ids as $cid) {
                if ( in_array($cid["rental_id"], $attached_items_grouped[$created_option_id]['cats']) ) {
                    // group wp cats by rental category id
                    $cats_rental_wp_id_collection[$cid["rental_id"]] = $cid["id"];
                    // group wp cats
                    $cats_wp_id_collection[] = $cid["id"];
                }
            }

            if ($cats_wp_id_collection) {
                $placeholders = array_fill(0, count($cats_wp_id_collection), '%d');
                $placeholders_format = implode(', ', $placeholders);
                $sql = "
                    SELECT 
                        object_id
                    FROM 
                        {$wpdb->term_relationships} terms
                    LEFT JOIN {$wpdb->posts} posts ON posts.id = terms.object_id
                    WHERE
                        term_taxonomy_id IN ({$placeholders_format})
                        AND posts.post_type = 'product'
                        AND posts.post_status = 'publish'
                ";
                $product_ids_by_category_ids = $wpdb->get_results($wpdb->prepare($sql, $cats_wp_id_collection), ARRAY_A);

                if ($product_ids_by_category_ids) {
                    foreach($product_ids_by_category_ids as $pid) {
                        $category_product_ids[] = $pid["object_id"];
                    }
                }
            }

        }

        // for updated option
        if (!empty($product_wp_id_collection)) {
            // sql values of the option products (type 1 = product)
            foreach ($product_wp_id_collection as $rental_product_id => $wp_id_list) {
                foreach($wp_id_list as $wp_id) {
                    $_option_ids[$wp_id][] = $created_option_id;
                    // product option relation sql
                    $rental_product_option_relations_sql .= empty($rental_product_option_relations_sql) ? "('$created_option_id', '$rental_product_id', '$wp_id', '1')" : ",('$created_option_id', '$rental_product_id', '$wp_id', '1')";
                }
            }
        }

           
        if (!empty($category_product_ids)) {
            foreach ($category_product_ids as $wp_id) {
                $_option_ids[$wp_id][] = $created_option_id;
            }
        }

        if (!empty($cats_rental_wp_id_collection)) {
            // sql values of the option categories (type 2 = category)
            foreach ($cats_rental_wp_id_collection as $rental_cat_id => $wp_cat_id) {
                $rental_product_option_relations_sql .= empty($rental_product_option_relations_sql) ? "('$created_option_id', '$rental_cat_id', '$wp_cat_id', '2')" : ",('$created_option_id', '$rental_cat_id', '$wp_cat_id', '2')";
            }
        }

        $wpdb->query($wpdb->prepare("DELETE FROM {$rental_product_option_relations} WHERE po_id = %d", $created_option_id));
        $wpdb->query("INSERT INTO {$rental_product_option_relations} (`po_id`, `rental_id`, `wp_id`, `type`) VALUES " . $rental_product_option_relations_sql);

        $delete_postmeta_ids = [];
        foreach($_option_ids as $wp_id => $option_ids) {
            $sql = "SELECT * FROM {$wpdb->postmeta} WHERE post_id = %d AND meta_key = '_product_options'";
            $existing_options = $wpdb->get_row($wpdb->prepare($sql, $wp_id), ARRAY_A);
            $existing_options_decoded = [];
            if (!empty($existing_options['meta_value'])) {
                // existing options update
                $existing_options_decoded = json_decode($existing_options['meta_value'], true);
                if (!empty($existing_options_decoded)) {
                    $existing_options_decoded[count($existing_options_decoded)+1] = $option_ids[0];
                } else {
                    $existing_options_decoded[0] = $option_ids[0];
                }
            } else {
                // existing options create
                $existing_options_decoded[0] = $option_ids[0];
            }

            $option_ids_rearranged = array_values(array_unique($existing_options_decoded));
            $option_ids_updated = json_encode($option_ids_rearranged);
            $productmeta_sql[$wp_id] = "($wp_id, '_product_options', '$option_ids_updated')";

            $delete_postmeta_ids[] = $wp_id;
        }

        $placeholders = array_fill(0, count($delete_postmeta_ids), '%d');
        $placeholders_format = implode(', ', $placeholders);
        // delete all product options postmeta with specified post(product) ids
        $sql_delete = "DELETE FROM {$wpdb->postmeta} WHERE meta_key = '_product_options' AND post_id IN ({$placeholders_format})";
        $wpdb->query($wpdb->prepare($sql_delete, $delete_postmeta_ids));
        // insert updated postmeta values
        rental_insert("INSERT INTO `$wpdb->postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES", $productmeta_sql);
    }

    /**
     * Delete a product_options.
     *
     * @return array
     */
    public function delete()
    {
        global $wpdb, $rental_tables;
        $rental_product_options = $wpdb->prefix . $rental_tables["product_options"];
        $rental_product_option_relations = $wpdb->prefix . $rental_tables["product_option_relations"];
        $productmeta_sql = [];

        $sql = "
            SELECT 
                options.*,
                GROUP_CONCAT(DISTINCT relation_products.wp_id) wp_product_ids,
                GROUP_CONCAT(DISTINCT relation_cats.wp_id) wp_cat_ids
            FROM 
                {$rental_product_options} as options
            LEFT JOIN {$rental_product_option_relations} as relation_products ON relation_products.po_id = options.id AND relation_products.type = 1
            LEFT JOIN {$rental_product_option_relations} as relation_cats ON relation_cats.po_id = options.id AND relation_cats.type = 2
            WHERE
                options.id = %d
            GROUP BY
                options.id
        ";
        $product_option = $wpdb->get_row($wpdb->prepare($sql, $this->id), ARRAY_A);

        $category_product_ids = [];
        $cat_ids = explode(',',$product_option['wp_cat_ids']);
        // get product ids based on cat ids
        if ($cat_ids) {
            $placeholders = array_fill(0, count($cat_ids), '%d');
            $placeholders_format = implode(', ', $placeholders);
            $sql = "
                SELECT 
                    object_id
                FROM 
                    {$wpdb->term_relationships} terms
                LEFT JOIN {$wpdb->posts} posts ON posts.id = terms.object_id
                WHERE
                    term_taxonomy_id IN ({$placeholders_format})
                    AND posts.post_type = 'product'
                    AND posts.post_status = 'publish'
            ";
            $product_ids_by_category_ids = $wpdb->get_results($wpdb->prepare($sql, $cat_ids), ARRAY_A);

            if ($product_ids_by_category_ids) {
                foreach($product_ids_by_category_ids as $pid) {
                    $category_product_ids[] = $pid['object_id'];
                }
            }
        }

        $product_ids = explode(',',$product_option['wp_product_ids']);
        $product_ids = array_unique(array_merge($product_ids, $category_product_ids));
        $delete_postmeta_ids = [];
        foreach($product_ids as $pid) {
            $sql = "SELECT * FROM {$wpdb->postmeta} WHERE post_id = %d AND meta_key = '_product_options'";
            $existing_options = $wpdb->get_row($wpdb->prepare($sql, $pid), ARRAY_A);
            $existing_options_decoded = [];
            $updated_values = [];
            if (!empty($existing_options['meta_value'])) {
                // existing options update
                $existing_options_decoded = json_decode($existing_options['meta_value'], true);
                if (!empty($existing_options_decoded)) {
                    foreach($existing_options_decoded as $option_id) {
                        if ($this->id != $option_id) {
                            $updated_values[] = $option_id;
                        }
                    }
                }
            }

            if ($updated_values) {
                $option_ids_rearranged = array_values(array_unique($updated_values));
                $option_ids_updated = json_encode($option_ids_rearranged);
                $productmeta_sql[$pid] = "($pid, '_product_options', '$option_ids_updated')";
                $delete_postmeta_ids[] = $pid;
            }
        }

        if ($delete_postmeta_ids) {
            $placeholders = array_fill(0, count($delete_postmeta_ids), '%d');
            $placeholders_format = implode(', ', $placeholders);
            // delete all product options postmeta with specified post(product) ids
            $sql_delete = "DELETE FROM {$wpdb->postmeta} WHERE meta_key = '_product_options' AND post_id IN ({$placeholders_format})";
            $wpdb->query($wpdb->prepare($sql_delete, $delete_postmeta_ids));
            // insert updated postmeta values
            rental_insert("INSERT INTO `$wpdb->postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES", $productmeta_sql);
        }
        
        $result = (bool) $wpdb->delete($rental_product_options, [
            "id" => $this->id
        ]);
        if ($result) {
            // delete rental_product_options_relations
            $result2 = (bool) $wpdb->delete($rental_product_option_relations, [
                "po_id" => $this->id
            ]);
            if ($result2) {
                return $this->id;
            }
        }
        return false;
    }


}