<?php

class ModelCatalogProduct extends Model {

    public function updateViewed($product_id) {
        $this->db->query("UPDATE " . DB_PREFIX . "product SET viewed = (viewed + 1) WHERE product_id = '" . (int)$product_id . "'");
    }

    public function getProduct($product_id) {
        $this->load->model('catalog/content');

        $sql = "SELECT DISTINCT p.*, p2p.product_group_id, p2pd.product_id as default_group_product_id, "
               . "p.image, "
               . "pd.*, "
               . "m.name AS manufacturer, "
               . " ( select category_id from (
            select max( level) as max_level , category_id from " . DB_PREFIX . "category_path
            where category_id in ( select category_id from " . DB_PREFIX . "product_to_category where product_id = '" . $product_id . "' )
            group by category_id ) maxed
            order by max_level desc limit 1  ) as deepest_category_id, "
               . "(SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < '" . date("Y-m-d") . "') AND (pd2.date_end = '0000-00-00' OR pd2.date_end > '" . date("Y-m-d") . "')) 
               ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, "
               . "(SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' 
               AND ps.date_start < '" . date("Y-m-d") . "' AND ps.date_end > '" . date("Y-m-d") . "' 
               ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, "
               . "(SELECT points FROM " . DB_PREFIX . "product_reward pr WHERE pr.product_id = p.product_id AND pr.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "') AS reward, "
               . "(SELECT ss.name FROM " . DB_PREFIX . "stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '" . (int)$this->config->get('config_language_id') . "') AS stock_status, "
               . "(SELECT wcd.unit FROM " . DB_PREFIX . "weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS weight_class, "
               . "(SELECT lcd.unit FROM " . DB_PREFIX . "length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS length_class, "
               . "(SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, "
               . "(SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, "
               . "p.sort_order FROM " . DB_PREFIX . "product p "
               . "LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) "
               . "LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) "
               . "LEFT JOIN " . DB_PREFIX . "manufacturer m ON (p.manufacturer_id = m.manufacturer_id) "
               . "LEFT JOIN " . DB_PREFIX . "product_to_product p2p ON (p2p.product_id = '" . (int)$product_id . "') "
               . "LEFT JOIN " . DB_PREFIX . "product_to_product p2pd ON (p2pd.product_group_id = p2p.product_group_id and p2pd.default_id=1) "
               . "WHERE p.product_id = '" . (int)$product_id . "' "
               . "AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "' "
               . "AND p.status = '1' AND p.date_available <= '" . date("Y-m-d") . "' "
               . "AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";

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

        // pr($query->row);

        if ($query->num_rows) {
            return array(
              'product_id'          => $query->row['product_id'],
              'name'                => $query->row['name'],
              'product_group_id'    => $query->row['product_group_id'],
              'default_id'          => $query->row['default_group_product_id'],
              'description'         => $query->row['description'],
              'meta_title'          => $query->row['meta_title'],
              'meta_description'    => $query->row['meta_description'],
              'meta_keyword'        => $query->row['meta_keyword'],
              'tag'                 => $query->row['tag'],
              'model'               => $query->row['model'],
              'sku'                 => $query->row['sku'],
              'upc'                 => $query->row['upc'],
              'ean'                 => $query->row['ean'],
              'jan'                 => $query->row['jan'],
              'isbn'                => $query->row['isbn'],
              'mpn'                 => $query->row['mpn'],
              'location'            => $query->row['location'],
              'quantity'            => $query->row['quantity'],
              'stock_status'        => $query->row['stock_status'],
              'image'               => $query->row['image'],
              'manufacturer_id'     => $query->row['manufacturer_id'],
              'manufacturer'        => $query->row['manufacturer'],
              'price'               => ($query->row['discount'] ? $query->row['discount'] : $query->row['price']),
              'special'             => $query->row['special'],
              'reward'              => $query->row['reward'],
              'points'              => $query->row['points'],
              'tax_class_id'        => $query->row['tax_class_id'],
              'date_available'      => $query->row['date_available'],
              'weight'              => $query->row['weight'],
              'weight_class_id'     => $query->row['weight_class_id'],
              'length'              => $query->row['length'],
              'width'               => $query->row['width'],
              'height'              => $query->row['height'],
              'length_class_id'     => $query->row['length_class_id'],
              'subtract'            => $query->row['subtract'],
              'rating'              => round($query->row['rating']),
              'reviews'             => $query->row['reviews'] ? $query->row['reviews'] : 0,
              'minimum'             => $query->row['minimum'],
              'sort_order'          => $query->row['sort_order'],
              'status'              => $query->row['status'],
              'date_added'          => $query->row['date_added'],
              'date_modified'       => $query->row['date_modified'],
              'viewed'              => $query->row['viewed'],
              'content_meta'        => $this->model_catalog_content->getContentMeta($query->row['product_id'], 'product'),
              'deepest_category_id' => $query->row['deepest_category_id'],
            );
        } else {
            return false;
        }
    }

    public function getProducts($data = array()) {
        $sql = "SELECT p.product_id, p2p.product_group_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < '" . date("Y-m-d") . "') AND (pd2.date_end = '0000-00-00' OR pd2.date_end > '" . date("Y-m-d") . "')) 
        ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, 
        (SELECT price FROM " . DB_PREFIX . "product_special ps 
            WHERE true 
            AND ps.product_id = p.product_id 
            AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' 
            AND ps.date_start < '" . date("Y-m-d") . "' AND ps.date_end > '" . date("Y-m-d") . "' 
            ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special";

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (cp.category_id = p2c.category_id)";
            } else {
                $sql .= " FROM " . DB_PREFIX . "product_to_category p2c";
            }

            if (!empty($data['filter_filter'])) {
                $sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf ON (p2c.product_id = pf.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (pf.product_id = p.product_id)";
            } else {
                $sql .= " LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
            }
        } else {
            $sql .= " FROM " . DB_PREFIX . "product p";
        }

        //$sql .= " LEFT JOIN " . DB_PREFIX . "product_to_product p2p ON (p.product_id = p2p.product_id) ";
        $sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN " . DB_PREFIX . "product_to_product p2p ON (p.product_id = p2p.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= '" . date("Y-m-d") . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " AND cp.path_id = '" . (int)$data['filter_category_id'] . "'";
            } else {
                $sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
            }

            if (!empty($data['filter_filter'])) {
                $implode = array();

                $filters = explode(',', $data['filter_filter']);

                foreach ($filters as $filter_id) {
                    $implode[] = (int)$filter_id;
                }

                $sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";
            }
        }

        if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
            $sql .= " AND (";

            if (!empty($data['filter_name'])) {
                $data['filter_name'] = trim($data['filter_name']);
                $implode = array();

                $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_name'])));

                foreach ($words as $word) {
                    $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
                }

                if ($implode) {
                    $sql .= " " . implode(" AND ", $implode) . "";
                }

                if (!empty($data['filter_description'])) {
                    $sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
                }
            }

            if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
                $sql .= " OR ";
            }

            if (!empty($data['filter_tag'])) {
                $data['filter_tag'] = trim($data['filter_tag']);
                $implode = array();

                $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_tag'])));

                foreach ($words as $word) {
                    $implode[] = "pd.tag LIKE '%" . $this->db->escape($word) . "%'";
                }

                if ($implode) {
                    $sql .= " " . implode(" AND ", $implode) . "";
                }
            }

            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.model) LIKE '%" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "%'";
                $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }

            $sql .= ")";
        }

        if (!empty($data['filter_manufacturer_id'])) {
            $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
        }

        if (!empty($data['filter_in_stock'])) {
            $sql .= " AND p.quantity > 0 ";
        }

        if (isset($data['group_products']) && $data['group_products']) {
            $sql .= " AND p2p.product_group_id = '" . (int)$data['product_group_id'] . "'";
        } elseif (!empty($data['filter_name'])) {

        } else {
            $sql .= " AND not exists (select p2p.product_id from `" . DB_PREFIX . "product_to_product` p2p where p2p.product_id = p.product_id and p2p.default_id = 0)";
        }


        $sql .= " GROUP BY p.product_id, p2p.product_group_id";

        $sort_data = array(
          'pd.name',
          'p.model',
          'p.image',
          'p.quantity',
          'p.price',
          'rating',
          'p.sort_order',
          'p.date_added',
          'p.date_available',
        );

        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
                $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
            } elseif ($data['sort'] == 'p.price') {
                $sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
            } else {
                $sql .= " ORDER BY " . $data['sort'];
            }
        } else {
            $sql .= " ORDER BY p2p.default_id = 1 desc, p.sort_order";
        }

        if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sql .= " DESC, LCASE(pd.name) DESC, p.product_id DESC";
        } else {
            $sql .= " ASC, LCASE(pd.name) ASC, p.product_id ASC";
        }

        if (isset($data['start']) || isset($data['limit'])) {
            if ($data['start'] < 0) {
                $data['start'] = 0;
            }

            if ($data['limit'] < 1) {
                $data['limit'] = 20;
            }

            $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
        }

        $cache_key = 'product.getproducts.' . md5($sql);
        $product_data = $this->cache->get($cache_key);
        if ($product_data === null) {
            $product_data = [];
            $query = $this->db->query($sql);

            foreach ($query->rows as $result) {

                $product_id = $result['product_id'];

                if (empty($data['group_products'])) {
                    if (isset($result['product_group_id']) && $result['product_group_id']) {
                        $sql = "SELECT * FROM " . DB_PREFIX . "product_to_product WHERE product_group_id='" . (int)$result['product_group_id'] . "' AND default_id=1";
                        $query = $this->db->query($sql);
                        $product_id = $query->row['product_id'];
                    }
                }

                // Check, IF there is product result.
                $product_temp = $this->getProduct($product_id);
                if ($product_temp) {
                    $product_data[$product_id] = $product_temp;
                }

            }
            $this->cache->set($cache_key, $product_data);
        }

        return $product_data;
    }

    public function getProductSpecials($data = array()) {
        $sql = "SELECT DISTINCT ps.product_id, (SELECT AVG(rating) FROM " . DB_PREFIX . "review r1 
        WHERE r1.product_id = ps.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating 
        FROM " . DB_PREFIX . "product_special ps LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) 
        LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) 
        WHERE p.status = '1' AND p.date_available <= '" . date("Y-m-d") . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' 
        AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' 
        AND ps.date_start < '" . date("Y-m-d") . "' AND ps.date_end > '" . date("Y-m-d") . "' 
        GROUP BY ps.product_id";

        $sort_data = array(
          'pd.name',
          'p.model',
          'ps.price',
          'rating',
          'p.sort_order'
        );

        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
                $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
            } else {
                $sql .= " ORDER BY " . $data['sort'];
            }
        } else {
            $sql .= " ORDER BY p.sort_order";
        }

        if (isset($data['order']) && ($data['order'] == 'DESC')) {
            $sql .= " DESC, LCASE(pd.name) DESC";
        } else {
            $sql .= " ASC, LCASE(pd.name) ASC";
        }

        if (isset($data['start']) || isset($data['limit'])) {
            if ($data['start'] < 0) {
                $data['start'] = 0;
            }

            if ($data['limit'] < 1) {
                $data['limit'] = 20;
            }

            $sql .= " LIMIT " . (int)$data['start'] . "," . (int)$data['limit'];
        }

        $product_data = array();

        $cache_key = 'product.getproductspecials.' . md5($sql);

        $product_data = $this->cache->get($cache_key);
        if ($product_data === null) {
            $query = $this->db->query($sql);
            foreach ($query->rows as $result) {
                $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
            }

            $this->cache->set($cache_key, $product_data);
        }
        
        return $product_data;
    }

    public function getLatestProducts($limit) {
        $product_data = $this->cache->get('product.latest.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int)$limit);

        if (!$product_data) {
            $query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= '" . date("Y-m-d") . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY p.date_added DESC LIMIT " . (int)$limit);

            foreach ($query->rows as $result) {
                $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
            }

            $this->cache->set('product.latest.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int)$limit,
              $product_data);
        }

        return $product_data;
    }

    public function getPopularProducts($limit) {
        $product_data = $this->cache->get('product.popular.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int)$limit);

        if (!$product_data) {
            $query = $this->db->query("SELECT p.product_id FROM " . DB_PREFIX . "product p LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE p.status = '1' AND p.date_available <= '" . date("Y-m-d") . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' ORDER BY p.viewed DESC, p.date_added DESC LIMIT " . (int)$limit);

            foreach ($query->rows as $result) {
                $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
            }

            $this->cache->set('product.popular.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int)$limit,
              $product_data);
        }

        return $product_data;
    }

    public function getBestSellerProducts($limit) {
        $product_data = $this->cache->get('product.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int)$limit);

        if (!$product_data) {
            $product_data = array();

            $query = $this->db->query("SELECT op.product_id, SUM(op.quantity) AS total FROM " . DB_PREFIX . "order_product op LEFT JOIN `" . DB_PREFIX . "order` o ON (op.order_id = o.order_id) LEFT JOIN `" . DB_PREFIX . "product` p ON (op.product_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE o.order_status_id > '0' AND p.status = '1' AND p.date_available <= '" . date("Y-m-d") . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' GROUP BY op.product_id ORDER BY total DESC LIMIT " . (int)$limit);

            foreach ($query->rows as $result) {
                $product_data[$result['product_id']] = $this->getProduct($result['product_id']);
            }

            $this->cache->set('product.bestseller.' . (int)$this->config->get('config_language_id') . '.' . (int)$this->config->get('config_store_id') . '.' . $this->config->get('config_customer_group_id') . '.' . (int)$limit,
              $product_data);
        }

        return $product_data;
    }

    public function getProductAttributes($product_id) {
        $product_attribute_group_data = array();

        $product_attribute_group_query = $this->db->query("SELECT ag.attribute_group_id, agd.name FROM " . DB_PREFIX . "product_attribute pa LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "attribute_group ag ON (a.attribute_group_id = ag.attribute_group_id) LEFT JOIN " . DB_PREFIX . "attribute_group_description agd ON (ag.attribute_group_id = agd.attribute_group_id) WHERE pa.product_id = '" . (int)$product_id . "' AND agd.language_id = '" . (int)$this->config->get('config_language_id') . "' GROUP BY ag.attribute_group_id ORDER BY ag.sort_order, agd.name");

        foreach ($product_attribute_group_query->rows as $product_attribute_group) {
            $product_attribute_data = array();

            $product_attribute_query = $this->db->query("SELECT a.attribute_id, ad.name, pa.text FROM " . DB_PREFIX . "product_attribute pa LEFT JOIN " . DB_PREFIX . "attribute a ON (pa.attribute_id = a.attribute_id) LEFT JOIN " . DB_PREFIX . "attribute_description ad ON (a.attribute_id = ad.attribute_id) WHERE pa.product_id = '" . (int)$product_id . "' AND a.attribute_group_id = '" . (int)$product_attribute_group['attribute_group_id'] . "' AND ad.language_id = '" . (int)$this->config->get('config_language_id') . "' AND pa.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY a.sort_order, ad.name");

            foreach ($product_attribute_query->rows as $product_attribute) {
                $product_attribute_data[] = array(
                  'attribute_id' => $product_attribute['attribute_id'],
                  'name'         => $product_attribute['name'],
                  'text'         => $product_attribute['text']
                );
            }

            $product_attribute_group_data[] = array(
              'attribute_group_id' => $product_attribute_group['attribute_group_id'],
              'name'               => $product_attribute_group['name'],
              'attribute'          => $product_attribute_data
            );
        }

        return $product_attribute_group_data;
    }

    public function getProductOptions($product_id) {
        $product_option_data = array();

        $product_option_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option po LEFT JOIN `" . DB_PREFIX . "option` o ON (po.option_id = o.option_id) LEFT JOIN " . DB_PREFIX . "option_description od ON (o.option_id = od.option_id) WHERE po.product_id = '" . (int)$product_id . "' AND od.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY o.sort_order, po.product_option_id");

        foreach ($product_option_query->rows as $product_option) {

            if ($product_option['display'] != "") {
                $product_option['name'] = $product_option['display'];
            }

            $product_option_value_data = array();

            $product_option_value_query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_option_value pov LEFT JOIN " . DB_PREFIX . "option_value ov ON (pov.option_value_id = ov.option_value_id) LEFT JOIN " . DB_PREFIX . "option_value_description ovd ON (ov.option_value_id = ovd.option_value_id) WHERE pov.product_id = '" . (int)$product_id . "' AND pov.product_option_id = '" . (int)$product_option['product_option_id'] . "' AND ovd.language_id = '" . (int)$this->config->get('config_language_id') . "' ORDER BY ov.sort_order, pov.product_option_value_id");

            foreach ($product_option_value_query->rows as $product_option_value) {
                $product_option_value_data[] = array(
                  'product_option_value_id' => $product_option_value['product_option_value_id'],
                  'option_value_id'         => $product_option_value['option_value_id'],
                  'name'                    => $product_option_value['name'],
                  'image'                   => $product_option_value['image'],
                  'quantity'                => $product_option_value['quantity'],
                  'subtract'                => $product_option_value['subtract'],
                  'price'                   => $product_option_value['price'],
                  'price_prefix'            => $product_option_value['price_prefix'],
                  'weight'                  => $product_option_value['weight'],
                  'weight_prefix'           => $product_option_value['weight_prefix'],
                  'description'             => $product_option_value['description'],
                  'article'                 => $product_option_value['article'],
                );
            }

            $product_option_data[] = array(
              'product_option_id'    => $product_option['product_option_id'],
              'product_option_value' => $product_option_value_data,
              'option_id'            => $product_option['option_id'],
              'name'                 => $product_option['name'],
              'type'                 => $product_option['type'],
              'value'                => $product_option['value'],
              'required'             => $product_option['required']
            );
        }

        $this->hook->getHook('model/catalog/product/getProductOptions/after', $product_option_data);
        return $product_option_data;
    }

    public function getProductDiscounts($product_id) {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_discount WHERE product_id = '" . (int)$product_id . "' AND customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND quantity > 1 AND ((date_start = '0000-00-00' OR date_start < '" . date("Y-m-d") . "') AND (date_end = '0000-00-00' OR date_end > '" . date("Y-m-d") . "')) ORDER BY quantity ASC, priority ASC, price ASC");

        return $query->rows;
    }

    public function getProductImages($product_id) {
        $sql = "SELECT * FROM " . DB_PREFIX . "product_image pi "
               . "LEFT JOIN " . DB_PREFIX . "product_image_description pid "
               . "ON pi.product_image_id = pid.product_image_id AND pid.language_id='" . (int)$this->config->get('config_language_id') . "' "
               . "WHERE pi.product_id = '" . (int)$product_id . "' "
               . "ORDER BY pi.sort_order ASC";
        $query = $this->db->query($sql);

        return $query->rows;
    }

    public function getProductRelated($product_id) {
        $product_data = array();

        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_related pr LEFT JOIN " . DB_PREFIX . "product p ON (pr.related_id = p.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pr.product_id = '" . (int)$product_id . "' AND p.status = '1' AND p.date_available <= '" . date("Y-m-d") . "' AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'");

        foreach ($query->rows as $result) {
            $product_data[$result['related_id']] = $this->getProduct($result['related_id']);
        }

        return $product_data;
    }

    public function getProductLayoutId($product_id) {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_layout WHERE product_id = '" . (int)$product_id . "' AND store_id = '" . (int)$this->config->get('config_store_id') . "'");

        if ($query->num_rows) {
            return $query->row['layout_id'];
        } else {
            return 0;
        }
    }

    public function getCategories($product_id) {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "product_to_category WHERE product_id = '" . (int)$product_id . "'");

        return $query->rows;
    }

    public function getTotalProducts($data = array()) {
        $max_limit = Config::get('config_limit_admin');


        $sql = "SELECT COUNT(DISTINCT p.product_id) AS total";

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " FROM " . DB_PREFIX . "category_path cp LEFT JOIN " . DB_PREFIX . "product_to_category p2c ON (cp.category_id = p2c.category_id)";
            } else {
                $sql .= " FROM " . DB_PREFIX . "product_to_category p2c";
            }

            if (!empty($data['filter_filter'])) {
                $sql .= " LEFT JOIN " . DB_PREFIX . "product_filter pf ON (p2c.product_id = pf.product_id) LEFT JOIN " . DB_PREFIX . "product p ON (pf.product_id = p.product_id)";
            } else {
                $sql .= " LEFT JOIN " . DB_PREFIX . "product p ON (p2c.product_id = p.product_id)";
            }
        } else {
            $sql .= " FROM " . DB_PREFIX . "product p";
        }

        $sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)
        LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id)
        WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1'
        AND p.date_available <= '" . date("Y-m-d") . "'
        AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";

        if (!empty($data['filter_category_id'])) {
            if (!empty($data['filter_sub_category'])) {
                $sql .= " AND cp.path_id = '" . (int)$data['filter_category_id'] . "'";
            } else {
                $sql .= " AND p2c.category_id = '" . (int)$data['filter_category_id'] . "'";
            }

            if (!empty($data['filter_filter'])) {
                $implode = array();

                $filters = explode(',', $data['filter_filter']);

                foreach ($filters as $filter_id) {
                    $implode[] = (int)$filter_id;
                }

                $sql .= " AND pf.filter_id IN (" . implode(',', $implode) . ")";
            }
        }

        if (!empty($data['filter_name']) || !empty($data['filter_tag'])) {
            $sql .= " AND (";

            if (!empty($data['filter_name'])) {
                $implode = array();

                $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_name'])));

                foreach ($words as $word) {
                    $implode[] = "pd.name LIKE '%" . $this->db->escape($word) . "%'";
                }

                if ($implode) {
                    $sql .= " " . implode(" AND ", $implode) . "";
                }

                if (!empty($data['filter_description'])) {
                    $sql .= " OR pd.description LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
                }
            }

            if (!empty($data['filter_name']) && !empty($data['filter_tag'])) {
                $sql .= " OR ";
            }

            if (!empty($data['filter_tag'])) {
                $implode = array();

                $words = explode(' ', trim(preg_replace('/\s+/', ' ', $data['filter_tag'])));

                foreach ($words as $word) {
                    $implode[] = "pd.tag LIKE '%" . $this->db->escape($word) . "%'";
                }

                if ($implode) {
                    $sql .= " " . implode(" AND ", $implode) . "";
                }
            }

            if (!empty($data['filter_name'])) {
                $sql .= " OR LCASE(p.model) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.sku) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.upc) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.ean) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.jan) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.isbn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
                $sql .= " OR LCASE(p.mpn) = '" . $this->db->escape(utf8_strtolower($data['filter_name'])) . "'";
            }

            $sql .= ")";
        } else {
            $sql .= " AND not exists (select p2p.product_id from `" . DB_PREFIX . "product_to_product` p2p where p2p.product_id = p.product_id and p2p.default_id = 0)";
        }

        if (!empty($data['filter_manufacturer_id'])) {
            $sql .= " AND p.manufacturer_id = '" . (int)$data['filter_manufacturer_id'] . "'";
        }

        $sql .= " LIMIT $max_limit";


        $product_data = $this->cache->get('product.gettotalproducts.' . md5($sql));
        if ($product_data === null) {
            $product_data = $this->db->query($sql);
            $this->cache->set('product.gettotalproducts.' . md5($sql), $product_data->row['total']);
            $product_data = $product_data->row['total'];
        }

        // pr($sql);

        return $product_data;
    }

    public function getProfile($product_id, $recurring_id) {
        $query = $this->db->query("SELECT * FROM " . DB_PREFIX . "recurring r JOIN " . DB_PREFIX . "product_recurring pr ON (pr.recurring_id = r.recurring_id AND pr.product_id = '" . (int)$product_id . "') WHERE pr.recurring_id = '" . (int)$recurring_id . "' AND status = '1' AND pr.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "'");

        return $query->row;
    }

    public function getProfiles($product_id) {
        $query = $this->db->query("SELECT rd.* FROM " . DB_PREFIX . "product_recurring pr JOIN " . DB_PREFIX . "recurring_description rd ON (rd.language_id = " . (int)$this->config->get('config_language_id') . " AND rd.recurring_id = pr.recurring_id) JOIN " . DB_PREFIX . "recurring r ON r.recurring_id = rd.recurring_id WHERE pr.product_id = " . (int)$product_id . " AND status = '1' AND pr.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' ORDER BY sort_order ASC");

        return $query->rows;
    }

    public function getTotalProductSpecials() {
        $query = $this->db->query("SELECT COUNT(DISTINCT ps.product_id) AS total 
            FROM " . DB_PREFIX . "product_special ps 
            LEFT JOIN " . DB_PREFIX . "product p ON (ps.product_id = p.product_id) 
            LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) 
            WHERE true
            AND p.status = '1' 
            AND p.date_available <= '" . date("Y-m-d") . "' 
            AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "' 
            AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' 
            AND ps.date_start < '" . date("Y-m-d") . "' AND ps.date_end > '" . date("Y-m-d") . "'");

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

    /**
     * WIP: get product full path, including deepest category for this particular product.
     */

    public function getProductUrlFull($product_id, $category_id = false) {

        if (!$category_id) {
            return $this->url->link('product/product', 'product_id=' . $product_id);
        }

        $this->load->model('catalog/category');

        $category_path = $this->model_catalog_category->getCategoryPath($category_id, $product_id);

        // $category_url = $this->model_catalog_category->getCategorySeoLink($category_id);
        return $this->url->link('product/product', ($category_path ? 'path=' . $category_path . '&' : '') . 'product_id=' . $product_id);
    }


}