<?php
class ModelCatalogCategory extends Model {
	public function getCategory($category_id, $status=1) {
		$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 and c1.status = " . $status . "
          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') . "'
                WHERE c.status = " . $status . "
        )
        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 ";

//        if ( ! $status === null){
//            $sql .="' AND cat.status = '".$status."' ";
//        }

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

		return $query->row;
	}


	public function hasChildren($parent, $status = 1) {

		$query = $this->db->query("SELECT DISTINCT * FROM " . DB_PREFIX . "category c"
		. " WHERE c.parent_id = '" . (int)$parent . "' AND c.status = '".$status."' ");

		return count($query->row) > 0;
	}


	public function getCategories($parent_id,$fidelity=false) {

        $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 and c1.status = " . Type_Status::Tp_Habilitado . "
            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') . "'
                WHERE c.status = " . Type_Status::Tp_Habilitado . "
            )
            SELECT
                cp.category_id,
                cp.title as name,
                cat.*
            FROM category_path cp
            INNER JOIN oc_category cat ON cat.category_id =  cp.category_id ";

        if($fidelity){
            $sql.= " INNER JOIN oc_product p ON (p.category_id = cat.category_id and p.quantity > 0)
                     INNER JOIN oc_fidelity_present fp ON (fp.product_id = p.product_id)";
        }

        $sql .= " WHERE cat.parent_id = '$parent_id' ";

        if($fidelity){
            $sql .= " group by cat.category_id ";
        }

        $sql .=" ORDER BY cat.sort_order, LCASE(cp.name)";

		$query = $this->db->query($sql, true, Type_TimeCache::Tp_Medio);

		return $query->rows;
	}

	public function getCategoryStatus($category_id) {
		$query = $this->db->query("SELECT c.status FROM " . DB_PREFIX . "category c WHERE c.category_id = '".$category_id."' ");

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


	public function getCategoriesByProductId($product_id) {
		$query = $this->db->query("SELECT (cd.name) as categoryname FROM ".DB_PREFIX ."product ptc INNER JOIN ".DB_PREFIX."category_description cd ON(ptc.category_id = cd.category_id) WHERE ptc.product_id = '".(int)$product_id."' AND cd.language_id = '" . (int)$this->config->get('config_language_id') . "' LIMIT 0,1");

		return (isset($query->row['categoryname'])) ? $query->row['categoryname'] : '';
	}

}
