<?php
class ModelCatalogCategory extends Model {

	public function categoryinproduct($pcategory){

	  $query = $this->db->query("select count(*) AS retorno from " . DB_PREFIX . "product_category pc where pc.category_id = " . (int)$pcategory);

      if ($query->row['retorno'] > 0){
	    return true;
	  } else {
		return false;
	  };
	}

	public function getCategory($category_id) {
		$sql =
        "WITH RECURSIVE category_path (category_id, title, name) AS
        (
          SELECT c1.category_id, cd.name AS title, cd.name AS name
                FROM oc_category c1
                INNER JOIN oc_category_description cd ON (cd.category_id = c1.category_id) AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "'
                WHERE c1.parent_id = 0
          UNION ALL
          SELECT c.category_id, cd2.name AS title, CONCAT(cp.name, '>', cd2.name)
                FROM category_path AS cp
               JOIN oc_category AS c ON cp.category_id = c.parent_id
                 JOIN oc_category_description cd2 ON (cd2.category_id = c.category_id) AND cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'
        )
        SELECT
            cp.name as path,
             (
            SELECT keyword
            FROM oc_url_alias
            WHERE QUERY = 'category_id=$category_id') AS keyword,
            cat.*,
            cds.*
        FROM category_path cp
        INNER JOIN oc_category cat ON cat.category_id =  cp.category_id
        LEFT JOIN oc_category_description cds ON (cat.category_id = cds.category_id)
        WHERE cp.category_id = $category_id";


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

		return $query->row;
	}

	public function getCategories($data) {
	    if (isset($data['isTotal']) && $data['isTotal']){
            $fields = " count(*) as total";
        } else {
            $fields = " cp.category_id, cp.name, cat.* ";
        }

        $sql = "
            WITH RECURSIVE category_path (category_id, title, name) AS
            (
            SELECT c1.category_id, cd.name AS title, cd.name AS name
                FROM oc_category c1
                INNER JOIN oc_category_description cd ON (cd.category_id = c1.category_id) AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "'
                WHERE c1.parent_id = 0
            UNION ALL
            SELECT c.category_id, cd2.name AS title, CONCAT(cp.name, '>', cd2.name)
                FROM category_path AS cp
                JOIN oc_category AS c ON cp.category_id = c.parent_id
                JOIN oc_category_description cd2 ON (cd2.category_id = c.category_id) AND cd2.language_id = '" . (int)$this->config->get('config_language_id') . "'
            )
            SELECT " . $fields . "
            FROM category_path cp
            INNER JOIN oc_category cat ON cat.category_id =  cp.category_id
            WHERE cp.category_id > 0";


		if (!empty($data['filter_name'])) {
			$sql .= " AND cp.name LIKE '%" . $this->db->escape($data['filter_name']) . "%'";
		}

        if (!empty($data['filter_code'])) {
            $sql .= " AND cp.category_id = " . $this->db->escape($data['filter_code']);
        }

        if (!empty($data['filter_id_futura'])) {
            $sql .= " AND cat.id_futura = " . $this->db->escape($data['filter_id_futura']);
        }

        if (isset($data['filter_status']) && !is_null($data['filter_status'])) {
            $sql .= " AND cat.status = " . $this->db->escape($data['filter_status']);
        }

		$sql .= " ORDER BY name";

        if (!isset($data['isTotal']) || !$data['isTotal']){
            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 getCategoryDescriptions($category_id) {
		$category_description_data = array();

		$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "category_description WHERE category_id = '" . (int)$category_id . "'");

		foreach ($query->rows as $result) {
			$category_description_data[$result['language_id']] = array(
				'name'             => $result['name'],
				'meta_keyword'     => $result['meta_keyword'],
				'meta_description' => $result['meta_description'],
				'description'      => $result['description']
			);
		}

		return $category_description_data;
	}

	public function getTotalCategories($data) {
	    // feito assim pq não é apenas um total comum, tem que estar exatamente como o SQL principal
        $data['isTotal'] = true;
        $categories = $this->getCategories($data);

		return current($categories)['total'];
	}



}
