<?php
class ModelToolCatalog extends Model
{

    public function addManufacturer($data)
    {
        $this->db->query("INSERT INTO ps_manufacturer SET 
        name = '" . $this->db->escape($data['name']) . "', 
        sort_order = '" . (int)$data['sort_order'] . "'");
        $manufacturer_id = $this->db->getLastId();

        if (isset($data['manufacturer_description'])) {
            foreach ($data['manufacturer_description'] as $language_id => $value) {
                $this->db->query("INSERT INTO ps_manufacturer_description SET manufacturer_id = '" . (int)$manufacturer_id . "', language_id = '" . (int)$language_id . "', custom_title = '" . $this->db->escape($value['custom_title']) . "', meta_keyword = '" . $this->db->escape($value['meta_keyword']) . "', meta_description = '" . $this->db->escape($value['meta_description']) . "', description = '" . $this->db->escape($value['description']) . "'");
            }
        }

        if (isset($data['image'])) {
            $this->db->query("UPDATE ps_manufacturer SET image = '" . $this->db->escape($data['image']) . "' WHERE manufacturer_id = '" . (int)$manufacturer_id . "'");
        }

        $this->db->query("INSERT INTO ps_manufacturer_to_store SET manufacturer_id = '" . (int)$manufacturer_id . "', store_id = '0' ");

        if (isset($data['keyword'])) {
            foreach ($data['keyword'] as $language_id => $keyword) {
                if ($keyword) {
                    $this->db->query("INSERT INTO ps_url_alias SET query = 'manufacturer_id=" . (int)$manufacturer_id . "', keyword = '" . $this->db->escape($keyword) . "', language_id = " . $language_id);
                }
            }
        }

        

        $this->cache->delete('manufacturer');
        return $manufacturer_id;

    }

    public function getManufacturerName($name)
    {
        $query = $this->db->query("SELECT * FROM ps_manufacturer WHERE name='" . $this->db->escape($name) . "' ");
        return $query->row;
    }

    public function askCategory($category_name)
    {
        $sql = "SELECT * FROM ps_category c 
        LEFT JOIN ps_category_description cd ON(c.category_id = cd.category_id)
        WHERE cd.language_id = '" . (int)$this->config->get('config_language_id') . "' 
        AND cd.name = '" . $this->db->escape($category_name) . "'  ";

        $query = $this->db->query($sql);

        return $query->row;
    }

    public function addCategory($data)
    {
        $this->db->query("INSERT INTO ps_category SET parent_id = '" . (int)$data['parent_id'] . "', `top` = '" . (isset($data['top']) ? (int)$data['top'] : 0) . "', `column` = '" . (int)$data['column'] . "', sort_order = '" . (int)$data['sort_order'] . "', status = '" . (int)$data['status'] . "', date_modified = NOW(), date_added = NOW()");

        $category_id = $this->db->getLastId();

        if (isset($data['image'])) {
            $this->db->query("UPDATE ps_category SET image = '" . $this->db->escape($data['image']) . "' WHERE category_id = '" . (int)$category_id . "'");
        }

        if (isset($data['category_description'])) {
            foreach ($data['category_description'] as $language_id => $value) {
                $this->db->query("INSERT INTO ps_category_description SET category_id = '" . (int)$category_id . "', language_id = '" . (int)$language_id . "', name = '" . $this->db->escape($value['name']) . "', description = '" . $this->db->escape($value['description']) . "', meta_title = '" . $this->db->escape($value['meta_title']) . "', meta_description = '" . $this->db->escape($value['meta_description']) . "', meta_keyword = '" . $this->db->escape($value['meta_keyword']) . "'");
            }
        }

        // MySQL Hierarchical Data Closure Table Pattern
        $level = 0;

        $query = $this->db->query("SELECT * FROM ps_category_path WHERE category_id = '" . (int)$data['parent_id'] . "' ORDER BY `level` ASC");
        foreach ($query->rows as $result) {
            $this->db->query("INSERT INTO ps_category_path SET `category_id` = '" . (int)$category_id . "', `path_id` = '" . (int)$result['path_id'] . "', `level` = '" . (int)$level . "'");
            $level++;
        }

        $this->db->query("INSERT INTO ps_category_path SET `category_id` = '" . (int)$category_id . "', `path_id` = '" . (int)$category_id . "', `level` = '" . (int)$level . "'");
        $this->db->query("INSERT INTO ps_category_to_store SET category_id = '" . (int)$category_id . "', store_id = '0'");



        if (isset($data['keyword'])) {
            foreach ($data['keyword'] as $language_id => $keyword) {
                if ($keyword) {
                    $this->db->query("INSERT INTO ps_url_alias SET query = 'category_id=" . (int)$category_id . "', keyword = '" . $this->db->escape($keyword) . "', language_id = " . $language_id);
                }
            }
        }

       

        $this->cache->delete('category');

        return $category_id;
    }

    public function addProduct($data)
    {

        $this->db->query("INSERT INTO ps_product SET 
        model = '" . $this->db->escape($data['model']) . "', 
        barcode = '" . $this->db->escape($data['barcode']) . "', 
        sku = '" . $this->db->escape($data['sku']) . "', 
        image = '" . $this->db->escape($data['image']) . "', 
        quantity = '" . (int)$data['quantity'] . "', 
        minimum = '" . (int)$data['minimum'] . "', 
        subtract = '" . (int)$data['subtract'] . "', 
        stock_status_id = '" . (int)$data['stock_status_id'] . "', 
        date_available = '" . $this->db->escape($data['date_available']) . "', 
        manufacturer_id = '" . (int)$data['manufacturer_id'] . "', 
        shipping = '" . (int)$data['shipping'] . "', 
        price = '" . (float)$data['price'] . "', 
        currency_id = '" . (int)$data['currency_id'] . "', 
        points = '" . (int)$data['points'] . "', 
        weight = '" . (float)$data['weight'] . "', 
        weight_class_id = '" . (int)$data['weight_class_id'] . "', 
        length = '" . (float)$data['length'] . "', 
        width = '" . (float)$data['width'] . "', 
        height = '" . (float)$data['height'] . "', 
        length_class_id = '" . (int)$data['length_class_id'] . "', 
        status = '" . (int)$data['status'] . "', 
        tax_class_id = '" . (int)$data['tax_class_id'] . "', 
        sort_order = '" . (int)$data['sort_order'] . "', 
        date_added = NOW()");

        $product_id = $this->db->getLastId();


        $this->db->query("INSERT INTO ps_product_to_store SET product_id = '" . (int)$product_id . "', store_id = '0'");

        if ($data['product_shipping']) {
            $this->db->query("UPDATE ps_product SET product_shipping = '" . $this->db->escape($data['product_shipping']) . "' WHERE product_id = '" . (int)$product_id . "'");
        }

        if($data['product_description']){
            foreach ($data['product_description'] as $language_id => $value) {
                $this->db->query("INSERT INTO ps_product_description 
                SET product_id = '" . (int)$product_id . "', 
                language_id = '" . (int)$language_id . "', 
                name = '" . $this->db->escape($value['name']) . "', 
                description = '" . $this->db->escape($value['description']) . "', 
                tag = '" . $this->db->escape($value['tag']) . "', 
                meta_title = '" . $this->db->escape($value['meta_title']) . "', 
                custom_alt = '" . ((isset($value['custom_alt'])) ? ($this->db->escape($value['custom_alt'])) : '') . "', 
                custom_h1 = '" . ((isset($value['custom_h1'])) ? ($this->db->escape($value['custom_h1'])) : '') . "', 
                custom_h2 = '" . ((isset($value['custom_h2'])) ? ($this->db->escape($value['custom_h2'])) : '') . "', 
                custom_imgtitle = '" . ((isset($value['custom_imgtitle'])) ? ($this->db->escape($value['custom_imgtitle'])) : '') . "', 
                meta_description = '" . $this->db->escape($value['meta_description']) . "', 
                meta_keyword = '" . $this->db->escape($value['meta_keyword']) . "'");
            }
        }

        


        if ($data['product_attribute']) {
            foreach ($data['product_attribute'] as $product_attribute) {
                if ($product_attribute['attribute_id']) {
                    $this->db->query("DELETE FROM ps_product_attribute WHERE product_id = '" . (int)$product_id . "' AND attribute_id = '" . (int)$product_attribute['attribute_id'] . "'");
                    foreach ($product_attribute['product_attribute_description'] as $language_id => $product_attribute_description) {
                        $this->db->query("DELETE FROM ps_product_attribute WHERE product_id = '" . (int)$product_id . "' AND attribute_id = '" . (int)$product_attribute['attribute_id'] . "' AND language_id = '" . (int)$language_id . "'");
                        $this->db->query("INSERT INTO ps_product_attribute SET product_id = '" . (int)$product_id . "', attribute_id = '" . (int)$product_attribute['attribute_id'] . "', language_id = '" . (int)$language_id . "', text = '" . $this->db->escape($product_attribute_description['text']) . "'");
                    }
                }
            }
        }

        if ($data['product_option']) {
            foreach ($data['product_option'] as $product_option) {
                if ($product_option['type'] == 'select' || $product_option['type'] == 'radio' || $product_option['type'] == 'checkbox' || $product_option['type'] == 'image') {
                    if (isset($product_option['product_option_value'])) {
                        $this->db->query("INSERT INTO ps_product_option SET 
                        product_id = '" . (int)$product_id . "', 
                        option_id = '" . (int)$product_option['option_id'] . "', 
                        required = '" . (int)$product_option['required'] . "'");

                        $product_option_id = $this->db->getLastId();

                        foreach ($product_option['product_option_value'] as $product_option_value) {
                            $this->db->query("INSERT INTO ps_product_option_value SET 
                            product_option_id = '" . (int)$product_option_id . "', 
                            product_id = '" . (int)$product_id . "', 
                            option_id = '" . (int)$product_option['option_id'] . "', 
                            option_value_id = '" . (int)$product_option_value['option_value_id'] . "',
                            sub_option_id = '" . (int)$product_option['sub_option_id'] . "', 
                            sub_option_value_id = '" . (int)$product_option_value['sub_option_value_id'] . "', 
                            quantity = '" . (int)$product_option_value['quantity'] . "', 
                            subtract = '" . (int)$product_option_value['subtract'] . "', 
                            price = '" . (float)$product_option_value['price'] . "', 
                            price_prefix = '" . $this->db->escape($product_option_value['price_prefix']) . "', 
                            customer_group_id = '" . (int)$product_option_value['customer_group_id'] . "', 
                            points = '" . (int)$product_option_value['points'] . "', 
                            points_prefix = '" . $this->db->escape($product_option_value['points_prefix']) . "', 
                            weight = '" . (float)$product_option_value['weight'] . "', 
                            weight_prefix = '" . $this->db->escape($product_option_value['weight_prefix']) . "',
                            option_thumb_image = '" . $this->db->escape($product_option_value['option_thumb_image']) . "',
                            option_value_barcode = '" . $this->db->escape($product_option_value['option_value_barcode']) . "'
                            ");
                        }
                    }
                } else {
                    $this->db->query("INSERT INTO ps_product_option SET 
                    product_id = '" . (int)$product_id . "', 
                    option_id = '" . (int)$product_option['option_id'] . "', 
                    value = '" . $this->db->escape($product_option['value']) . "', 
                    required = '" . (int)$product_option['required'] . "'");
                }
            }
        }

        if ($data['product_discount']) {
            foreach ($data['product_discount'] as $product_discount) {
                $this->db->query("INSERT INTO " . DB_PREFIX . "product_discount SET product_id = '" . (int)$product_id . "', customer_group_id = '" . (int)$product_discount['customer_group_id'] . "', quantity = '" . (int)$product_discount['quantity'] . "', priority = '" . (int)$product_discount['priority'] . "', price = '" . (float)$product_discount['price'] . "', date_start = '" . $this->db->escape($product_discount['date_start']) . "', date_end = '" . $this->db->escape($product_discount['date_end']) . "'");
            }
        }

        if ($data['product_special']) {
            foreach ($data['product_special'] as $product_special) {
                $this->db->query("INSERT INTO ps_product_special SET 
                product_id = '" . (int)$product_id . "', 
                customer_group_id = '" . (int)$product_special['customer_group_id'] . "', 
                priority = '" . (int)$product_special['priority'] . "', 
                price = '" . (float)$product_special['price'] . "', 
                date_start = '" . $this->db->escape($product_special['date_start']) . "', 
                date_end = '" . $this->db->escape($product_special['date_end']) . "' ");
            }
        }

        if ($data['product_image']) {
            foreach ($data['product_image'] as $product_image) {
                if ($this->config->get('multiimageuploader_deletedef') && isset($data['def_img']) && $data['def_img'] == $product_image['image']) {
                    continue;
                }
                $this->db->query("INSERT INTO ps_product_image SET 
                product_id = '" . (int)$product_id . "', 
                image = '" . $this->db->escape($product_image['image']) . "', 
                sort_order = '" . (int)$product_image['sort_order'] . "' ");
            }
        }

        if ($data['product_download']) {
            foreach ($data['product_download'] as $download_id) {
                $this->db->query("INSERT INTO " . DB_PREFIX . "product_to_download SET product_id = '" . (int)$product_id . "', download_id = '" . (int)$download_id . "'");
            }
        }

        if ($data['product_category']) {
            foreach ($data['product_category'] as $category_id) {
                $check_cat_to_product = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category where product_id = '" . (int)$product_id . "'AND category_id = '" . (int)$category_id . "'");
                if ($check_cat_to_product->num_rows == 0) {
                    $parent_row = $this->db->query("SELECT parent_id From " . DB_PREFIX . "category where category_id = '" . (int)$category_id . "'");
                    if ($parent_row->num_rows > 0) {
                        if ((int)$parent_row->row['parent_id'] > 0) {
                            $check_cat_to_product = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category where product_id = '" . (int)$product_id . "'AND category_id = '" . (int)$parent_row->row['parent_id'] . "'");
                            if ($check_cat_to_product->num_rows == 0) {

                                $this->db->query("INSERT INTO " . DB_PREFIX . "product_to_category SET product_id ='" . (int)$product_id . "',category_id ='" . (int)$parent_row->row['parent_id'] . "'");
                            }
                            $parent_sec_row = $this->db->query("SELECT parent_id From " . DB_PREFIX . "category where category_id = '" . (int)$parent_row->row['parent_id'] . "'");
                            if ($parent_sec_row->num_rows > 0) {
                                if ((int)$parent_sec_row->row['parent_id'] > 0) {
                                    $check_cat_to_product = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category where product_id = '" . (int)$product_id . "'AND category_id = '" . (int)$parent_sec_row->row['parent_id'] . "'");
                                    if ($check_cat_to_product->num_rows == 0) {
                                        $this->db->query("INSERT INTO " . DB_PREFIX . "product_to_category SET product_id ='" . (int)$product_id . "',category_id ='" . (int)$parent_sec_row->row['parent_id'] . "'");
                                    }
                                }
                            }
                        }
                    }
                    $this->db->query("INSERT INTO " . DB_PREFIX . "product_to_category SET product_id = '" . (int)$product_id . "', category_id = '" . (int)$category_id . "'");
                }
            }
        }


        if ($data['keyword']) {
            foreach ($data['keyword'] as $language_id => $keyword) {
                if ($keyword) {
                    $this->db->query("INSERT INTO ps_url_alias SET query = 'product_id=" . (int)$product_id . "', keyword = '" . $this->db->escape($keyword) . "', language_id = " . $language_id);
                }
            }
        }




        return $product_id;
    }

    public function getProductModel($model){
        $query = $this->db->query("SELECT DISTINCT * FROM ps_product  WHERE model = '" . $model . "' ");
        return $query->row;
    }

    public function getProductOption($product_id, $option_id){
        $query = $this->db->query("SELECT * FROM ps_product_option WHERE  product_id = '". $product_id ."' AND option_id = '". $option_id ."' ");
        if($query->row['product_option_id']){
            return $query->row['product_option_id'];
        }else{
            return false;
        }
    }

    public function getProductOptionValue($product_id, $option_id, $sub_option_id = 0 ,$option_value_id, $sub_option_value_id = 0){
        $query = $this->db->query("SELECT * FROM ps_product_option_value WHERE 
            product_id = '". $product_id ."' AND 
            sub_option_value_id = '". $sub_option_value_id ."' AND 
            option_value_id = '". $option_value_id ."' AND
            sub_option_id = '". $sub_option_id ."' AND
             option_id = '". $option_id ."' ");


        if(isset($query->row['product_option_value_id'])){
            return $query->row;
        }else{
            return false;
        }
    }

    public function getOptionValueId($name, $option_id)
    {
        $query = $this->db->query("SELECT * FROM ps_option_value_description  WHERE name = '". $this->db->escape($name) ."' AND language_id = 1 AND option_id = '". (int)$option_id ."' ");
        if(isset($query->row['option_value_id']) AND $query->row['option_value_id']){
            return $query->row['option_value_id'];
        }else{
            return 0;
        }
		
    }

    public function addOptionValue($data){

		$this->db->query("INSERT INTO ps_option_value SET 
        option_id = '" . (int)$data['option_id'] . "', 
        image = '" . $this->db->escape(html_entity_decode($data['image'], ENT_QUOTES, 'UTF-8')) . "', 
        sort_order = '" . (int)$data['sort_order'] . "' ");

		$option_value_id = $this->db->getLastId();

		foreach ($data['description'] as $language_id => $value) {
			$this->db->query("INSERT INTO ps_option_value_description SET 
            option_value_id = '" . (int)$option_value_id . "', 
            language_id = '" . (int)$language_id . "', 
            option_id = '" . (int)$value['option_id'] . "', 
            name = '" . $this->db->escape($value['name']) . "' ");
		}

		return $option_value_id;

	}

	public function getOptionIdByOptionValueName($name){
        $query = $this->db->query("SELECT * FROM ps_option_value_description WHERE name = '". $this->db->escape($name) ."' AND language_id = 1 ");
        if(isset($query->row['option_id']) AND $query->row['option_id']){
            return $query->row['option_id'];
        }else{
            return 0;
        }
    }
}
