<?php
class ModelCatalogReview extends Model {
	public function addReview($data) {
		$this->db->query("INSERT INTO " . DB_PREFIX . "review SET author = '" . $this->db->escape($data['author']) . "', product_id = '" . $this->db->escape($data['product_id']) . "', text = '" . $this->db->escape(strip_tags($data['text'])) . "', rating = '" . (int)$data['rating'] . "', status = '" . (int)$data['status'] . "', date_added = NOW()");

		$this->cache->delete('product');
	}

	public function editReview($review_id, $data) {
		$this->db->query("UPDATE " . DB_PREFIX . "review SET author = '" . $this->db->escape($data['author']) . "', product_id = '" . $this->db->escape($data['product_id']) . "', text = '" . $this->db->escape(strip_tags($data['text'])) . "', rating = '" . (int)$data['rating'] . "', status = '" . (int)$data['status'] . "', date_modified = NOW() WHERE review_id = '" . (int)$review_id . "'");

		$this->cache->delete('product');
	}

	public function deleteReview($review_id) {
		$this->db->query("DELETE FROM " . DB_PREFIX . "review WHERE review_id = '" . (int)$review_id . "'");

		$this->cache->delete('product');
	}

	public function getReview($review_id) {
		$query = $this->db->query("SELECT DISTINCT *, (SELECT pd.name FROM " . DB_PREFIX . "product_description pd WHERE pd.product_id = r.product_id AND pd.language_id = '" . (int)$this->config->get('config_language_id') . "') AS product FROM " . DB_PREFIX . "review r WHERE r.review_id = '" . (int)$review_id . "'");

		return $query->row;
	}

	public function getReviews($data = array()) {
		$sql = "SELECT r.review_id, pd.name, (r.product_id) as product_id, r.author, r.rating, r.status, r.date_added, r.text FROM " . DB_PREFIX . "review r LEFT JOIN " . DB_PREFIX . "product_description pd ON (r.product_id = pd.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

        if (isset($data['description']) && $data['description'] != '') {
            $sql .= " AND r.text LIKE '%". $this->db->escape($data['description']) . "%'";
        }

        if (isset($data['author']) && $data['author'] != '') {
            $sql .= " AND r.author LIKE '%". $this->db->escape($data['author']) . "%'";
        }

        if (isset($data['product'])) {
            $sql .= " AND pd.name ". ftr_geral::getContentSql($this->db->escape($data['product']), $data['product_content']);
        }

		if(isset($data['status'])){
			$sql .= " AND r.status = ".$data['status'];
		}

		if(isset($data['notification_status'])){
			$sql .= " AND r.notification_status=".$this->db->escape($data['notification_status']);
		}

		if (isset($data['date_start']) && isset($data['date_end'])) {
		    $sql .= " AND DATE(r.date_added) BETWEEN DATE('" . $this->db->escape(ftr_geral::formatDate($data['date_start'], 'en')) . "')
		     AND DATE('" . $this->db->escape(ftr_geral::formatDate($data['date_end'], 'en')) . "')";
        }

		$sort_data = array(
			'pd.name',
			'r.author',
			'r.rating',
			'r.status',
			'r.date_added'
		);

		if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
			$sql .= " ORDER BY " . $data['sort'];
		} else {
			$sql .= " ORDER BY r.date_added";
		}

		if (isset($data['order']) && ($data['order'] == 'DESC')) {
			$sql .= " DESC";
		} else {
			$sql .= " 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'];
		}

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

		return $query->rows;
	}

	public function getTotalReviews($data = array()) {
        $sql = "SELECT COUNT(DISTINCT r.review_id) AS total FROM " . DB_PREFIX . "review r LEFT JOIN " . DB_PREFIX . "product_description pd ON (r.product_id = pd.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";

        if (isset($data['description']) && $data['description'] != '') {
            $sql .= " AND r.text LIKE '%". $this->db->escape($data['description']) . "%'";
        }

        if (isset($data['author']) && $data['author'] != '') {
            $sql .= " AND r.author LIKE '%". $this->db->escape($data['author']) . "%'";
        }

        if (isset($data['product'])) {
            $sql .= " AND pd.name ". ftr_geral::getContentSql($this->db->escape($data['product']), $data['product_content']);
        }

        if(isset($data['status'])){
            $sql .= " AND r.status = ".$data['status'];
        }

        if(isset($data['notification_status'])){
            $sql .= " AND r.notification_status=".$this->db->escape($data['notification_status']);
        }

        if (isset($data['date_start']) && isset($data['date_end'])) {
            $sql .= " AND DATE(r.date_added) BETWEEN DATE('" . $this->db->escape(ftr_geral::formatDate($data['date_start'], 'en')) . "')
		     AND DATE('" . $this->db->escape(ftr_geral::formatDate($data['date_end'], 'en')) . "')";
        }

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

		return $query->row['total'];
	}

	public function getTotalReviewsAwaitingApproval() {
		$query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review WHERE status = '0'");

		return $query->row['total'];
	}

    public function getTotalReviewsNotApproval() {
        $query = $this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "review WHERE status = '0' AND notification_status = '1'");

        return $query->row['total'];
    }

    public function getRatedProducts($data = array()) {

        $sql = "SELECT DISTINCT
                pd.name,
                p.*";

        $filtro = '';

        if (!empty($data['product_name'])) {
            $sql .= ",
                (match(pd.name) against (QUOTE('" . $this->db->escape($data['product_name']) . "') in natural language mode)) as score,";
            $sql .= "if(LCASE(pd.name) = '" . utf8_strtolower($this->db->escape($data['product_name'])) . "', 1, 0) as match_value";

            $filtro .= " AND (";

            if ($data['product_content_name'] == Type_FilterContent::Tp_Default) {
                $implode = array();
                $words = explode(' ', trim(preg_replace('/\s\s+/', ' ', $data['product_name'])));

                $word_search = '';
                foreach ($words as $word) {
                    if (strlen($word) < 3) {
                        $word_search .= ' ' . $word;
                    } else {
                        $word_search = $word;
                    }
                    $implode[] = "(pd.name LIKE '%" . $this->db->escape(trim($word_search)) . "%'" .
                        (isset($data_language) ? " OR pt.descricao_ecommerce LIKE '%" . $this->db->escape(trim($word_search)) . "%' )" : ")");
                }

                if ($implode) {
                    $filtro .= " " . implode(" OR ", $implode) . "";
                }

                if (isset($this->config_setting_search[Type_SearchOption::Tp_DescricaoCompleta])) {
                    $filtro .= " OR pd.description LIKE '%" . $this->db->escape($data['product_name']) . "%'";
                }
            }

            if (!empty($data['product_name']) && ($data['product_content_name'] != Type_FilterContent::Tp_Default)) {
                $filtro .= "pd.name " . ftr_geral::getContentSql($this->db->escape($data['product_name']), $data['product_content_name']);
            }

            $filtro .= ")";

        }

        $sql .= " FROM " . DB_PREFIX . "product p
            INNER JOIN  " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id)
            INNER JOIN " . DB_PREFIX . "review r ON (p.product_id = r.product_id)
            WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "'";


        $sql .= $filtro;

        $sort_data = array(
            'pd.name',
            'p.model',
            'p.qty',
            'p.price',
            'r.rating',
            'p.sort_order',
            'p.date_added',
            'p.data_replicacao_ecommerce',
            'p.viewed',
            'score'
        );

        $sqlorder = '';

        if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
            if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "LCASE(" . str_replace('.', '_', $data['sort']) . ")");
            } elseif ($data['sort'] == 'p.price') {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "valor_product");
            } elseif ($data['sort'] == 'p.viewed') {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "(CASE WHEN p_viewed IS NOT NULL THEN p_viewed END)");
            } elseif ($data['sort'] == 'p.data_replicacao_ecommerce') {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "(CASE WHEN p_data_replicacao_ecommerce IS NOT NULL THEN p_data_replicacao_ecommerce END)");
            } elseif ($data['sort'] == 'score') {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "match_value DESC, score DESC");
            } else {
                $sqlorder .= ftr_geral::sqlOrderByAdd($sqlorder, "LCASE(" . str_replace('.', '_', $data['sort']) . ")");
            }
        }



        $sql .= $sqlorder;

        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'];
        }

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

        return $query->rows;
    }

	public function updateStatusNotificationReview($review_id, $status) {
		$this->load->type('tipocomum');
		$this->db->query("UPDATE " . DB_PREFIX . "review SET status = '" . (int)$status . "', notification_status = ".Type_Status::Tp_Desabilitado." WHERE review_id = '" . (int)$review_id . "'");
	}

	public function updateStatusReview($review_id, $status) {
		$this->load->type('tipocomum');
		$this->db->query("UPDATE " . DB_PREFIX . "review SET status = '" . (int)$status . "' WHERE review_id = '" . (int)$review_id . "'");
	}

}
