<?php
// no direct access
defined( '_JEXEC' ) or die( 'Restricted access' );

class FSAssignFiltersModel {

	static private $productsCount = 0;
	static private $productTypesData = array();
	static private $parametersData = array();
	static private $executeTime = 0;
	static private $thereAreChildrenProducts = -1;


	static public function showFiltersAssignPage() {
		self::initProductTypeAndParameterData();

		self::loadView();
	//	require(FS_PATH .'views/FSAssignFiltersView.php');
		FSAssignFiltersView::printFiltersAssignPage();


	}


	static public function showProductDetailsAndPageNavigation() {
		self::initProductTypeAndParameterData();

		self::loadView();
		//require(FS_PATH .'views/FSAssignFiltersView.php');
		FSAssignFiltersView::printProductDetailsAndPageNavigation();

	}


	static public function showAvailableParametersForProductType() {
		self::initProductTypeAndParameterData();

		$pid = (int)JRequest::getVar('pid', null);
		$ptid = (int)JRequest::getVar('ptid', null);
		$record_id = (int)JRequest::getVar('record_id', null);
		$currentRow = JRequest::getVar('current_row', null);
		$assignedFilters = self::getAssignedFilterData($pid, $ptid, $record_id);


		//require(FS_PATH .'views/FSAssignFiltersView.php');
		self::loadView();

		FSAssignFiltersView::setCurrentRow($currentRow);
		echo FSAssignFiltersView::getProductFilterData($assignedFilters);

	}


	static public function getProductsData() {
		$db = JFactory::getDBO();
		$time_start = microtime(true);

		$keyword = JRequest::getVar('q','');
		$page = intval(JRequest::getCmd('page',1));
		$skip = intval(JRequest::getCmd('skip',0));
		$showonpage = intval(JRequest::getCmd('showonpage', FSConf::get('products_num_on_page')));
		$session_onpage = (isset($_COOKIE['onpage'])) ? $_COOKIE['onpage'] : null;
		if ($session_onpage) $showonpage = $session_onpage;

		$cid = JRequest::getVar('cid', null);
		$ptid = JRequest::getVar('ptid', null);
		$ppid = JRequest::getVar('ppid', null); // product parent id
		$orderby = JRequest::getVar('orderby', 'cat');
		$sc = JRequest::getVar('sc', 'asc');

		$show_product_code = FSConf::get('show_product_code');
		$showProductImage = 1;

		$columns = array("p.`product_id` as pid, p.`product_name`, p.`product_published` as published");
		$columns[] = "GROUP_CONCAT(DISTINCT CAST(`product_type_id` AS CHAR) SEPARATOR ';') as ptids";
		$tables = array("`#__hikashop_product` as p");
		$joins = array();
		//$joins[] = "LEFT JOIN `#__virtuemart_products_". VMLANG ."` as plan ON p.`virtuemart_product_id`=plan.`virtuemart_product_id`";
		$joins[] ="LEFT JOIN `#__fastseller_hs_product_product_type_xref` as ptx ON p.`product_id`=ptx.`product_id`";
		$where = array();

		//if ($show_product_code)
			$columns[] = 'p.`product_code`';

		if (($cid || $orderby == 'cat') && !$ppid) {
			$joins[] = "LEFT JOIN `#__hikashop_product_category` as pc ON p.`product_id`=pc.`product_id`";
			if ($orderby == 'cat') {
				$columns[] = "pc.`category_id` as cid, c.`category_name`";
				$joins[] = "LEFT JOIN `#__hikashop_category` as c ".
						"ON pc.`category_id`=c.`category_id` ";
			}
		}


		if ($ppid) {
			$where[] = "(p.`product_id`='$ppid' OR p.`product_parent_id`='$ppid')";
		} else if ($cid) {
			$where[] = "pc.`category_id`='$cid'";
		}


		if ($ptid == 'wopt') {
			$where[] = "ptx.`product_type_id` IS NULL ";
		} else if ($ptid) {
			//$where[] = "ptx.`product_type_id`=$ptid";
			$where[] = "p.`product_id` IN (".
				"SELECT `product_id` FROM `#__fastseller_hs_product_product_type_xref`".
				" WHERE `product_type_id`='$ptid')";
		}

		if (! $ppid)
			$where[] = "p.`product_parent_id`=0";

		if ($keyword) {
			$sq = "p.`product_name` LIKE '%$keyword%'";
			if ($show_product_code)
				$sq .= " OR p.`product_code` LIKE '%$keyword%'";
			if (is_numeric($keyword))
				$sq.= " OR p.`product_id`=$keyword";

			$where[] = "($sq)";
		}


		if ($showProductImage) {
			$columns[] = "hsfile.`file_path` as image";
			$joins[] = "LEFT JOIN `#__hikashop_file` as hsfile ON p.`product_id`=hsfile.`file_ref_id`";
			//$joins[] = "LEFT JOIN `#__virtuemart_medias` as medias ON pm.`virtuemart_media_id`=medias.`virtuemart_media_id`";
		}


		if (! FSConf::get('show_unpublished_products'))
			$where[] = "p.`product_published`='1'";



		$query = "SELECT SQL_CALC_FOUND_ROWS ". implode(', ', $columns) .
			" FROM (". implode(', ', $tables) .") ".
			implode(' ', $joins) .
			" WHERE ". implode(' AND ', $where);


		if ($orderby) {
			switch ($orderby) {
				case 'cat':
					if (!$ppid) {
						$ordercolumn = '`cid`, `pid`';
						break;
					}

				case 'pid':
					$ordercolumn = '`pid`';
					break;

				case 'pname':
					$ordercolumn = '`product_name`';
					break;

				case 'ptid':
					$ordercolumn = '`product_type_id`';
					break;
			}

			$query .= " GROUP BY p.`product_id`";
			$query .= " ORDER BY $ordercolumn ". strtoupper($sc);
		}

		$query .= " LIMIT $skip, $showonpage";

		$db->setQuery($query);
		$rows = $db->loadAssocList();

		// now for each product get it's assigned filters
		if ($rows) {

			$db->setQuery('SELECT FOUND_ROWS()');
			self::$productsCount = $db->loadResult();

			foreach ($rows as &$row) {
				$row['assigned_filters'] = ($row['ptids']) ? self::getAssignedFilterData($row['pid'], $row['ptids']) : '';
			}

		}


		//echo '<pre style="font-size:13px">';
		//var_dump($db);
		//print_r($rows);
		//echo '</pre>';


		$time_end = microtime(true);
		$elapsed = $time_end - $time_start;

		self::$executeTime = $elapsed;

		return $rows;
	}


	static private function getAssignedFilterData($pid, $ptids_str, $record_id = null) {
//return null;
		$ptids = explode(';', $ptids_str);
		sort($ptids);

		$db = JFactory::getDBO();
		$d = array();

		foreach ($ptids as $ptid) {

			$q = "SELECT * FROM `#__fastseller_hs_product_type_$ptid` WHERE `product_id`='$pid'";
			if ($record_id) $q .= " AND `id`='$record_id'";

			$db->setQuery($q);
			$res = $db->loadAssocList();

			if (!$res) continue;

			foreach ($res as $r) {
				$r['ptid'] = $ptid;
				$d[] = $r;
			}

		}

		return $d;
	}


	static private function initProductTypeAndParameterData() {
		$db = JFactory::getDBO();

		$q = "SELECT `product_type_id` as id, `product_type_name` as name FROM `#__fastseller_hs_product_type`";
		$db->setQuery($q);
		$res = $db->loadAssocList();

		if ($res) {
			$d = array();
			foreach ($res as $r) {
				$d[$r['id']] = $r;
			}

			self::$productTypesData = $d;
		}


		$q = "SELECT * FROM `#__fastseller_hs_product_type_parameter` ORDER BY `parameter_list_order`";
		$db->setQuery($q);
		$res = $db->loadAssocList();

		// structurize data for ease of access
		if ($res) {
			$d = array();
			foreach ($res as $r) {
				$d[$r['product_type_id']][] = $r;
			}

			self::$parametersData = $d;
		}

	}



	static public function processAssignProductTypeToProductEvent() {
		$pid = (int)JRequest::getVar('pid', null);
		$ptid = (int)JRequest::getVar('ptid', null);

		if (!$pid || !$ptid) die();

		$lastInsertId = self::assignProductToProductType($pid, $ptid);

		JRequest::setVar('record_id', $lastInsertId);

		self::showAvailableParametersForProductType();
	}


	static private function assignProductToProductType($pid, $ptid) {
		$db = JFactory::getDBO();

		$q = "INSERT INTO `#__fastseller_hs_product_product_type_xref` SET `product_id`='$pid', `product_type_id`='$ptid'";
		$db->setQuery($q);
		$db->query();

		$q = "INSERT INTO `#__fastseller_hs_product_type_$ptid` SET `product_id`='$pid'";
		$db->setQuery($q);
		$db->query();

		$lastInsertId = $db->insertid();

		return $lastInsertId;
	}


	static public function deleteProductTypeFromProduct() {
		$pid = (int)JRequest::getVar('pid', null);
		$ptids = JRequest::getVar('ptid', null);
		$recordId = (int)JRequest::getVar('record_id', null);


		$db = JFactory::getDBO();

		if ($recordId) { // delete just one PT

			$ptid = (int)$ptids[0];
			if (!$ptid) die();

			$q = "DELETE FROM `#__fastseller_hs_product_type_$ptid` WHERE `id`='$recordId'";
			$db->setQuery($q);
			$db->query();

			$q = "DELETE FROM `#__fastseller_hs_product_product_type_xref` WHERE `product_id`='$pid' AND `product_type_id`='$ptid' LIMIT 1";
			$db->setQuery($q);
			$db->query();

		} else { // delete all PTs for this product

			foreach ($ptids as $_ptid) {
				$ptid = (int)$_ptid;
				if (!$ptid) continue;

				$q = "DELETE FROM `#__fastseller_hs_product_type_$ptid` WHERE `product_id`='$pid'";
				$db->setQuery($q);
				$db->query();

				$q = "DELETE FROM `#__fastseller_hs_product_product_type_xref` WHERE `product_id`='$pid' AND `product_type_id`='$ptid'";
				$db->setQuery($q);
				$db->query();

			}

		}
	}


	public static function deleteProductTypesFromSelectedProducts() {
		$pids = JRequest::getVar('pids', null);
		$ptid = JRequest::getVar('ptid', null);
		$pidsArray = explode('|', $pids);

		if ($ptid) {
			self::_deleteProductsFromProductType($pidsArray, $ptid);
		} else {
			$db = JFactory::getDBO();
			$q = "SELECT `product_type_id` FROM `#__fastseller_hs_product_type`";
			$db->setQuery($q);
			$ptids = $db->loadColumn();
			foreach ($ptids as $ptid) self::_deleteProductsFromProductType($pidsArray, $ptid);
		}
	}


	private static function _deleteProductsFromProductType($pids = array(), $ptid) {
		if (!$pids || !$ptid) return;

		$db = JFactory::getDBO();
		$q = "DELETE FROM `#__fastseller_hs_product_type_$ptid` WHERE `product_id` IN ('".
			implode("', '", $pids) ."')";
		$db->setQuery($q);
		$db->query();

		$q = "DELETE FROM `#__fastseller_hs_product_product_type_xref` WHERE ".
			"`product_id` IN ('". implode("', '", $pids) ."')".
			" AND `product_type_id`='$ptid'";
		$db->setQuery($q);
		$db->query();
	}


	static public function saveProductParameterFilters() {
		$pid = (int)JRequest::getVar('pid', null);
		$ptid = (int)JRequest::getVar('ptid', null);
		$recordId = (int)JRequest::getVar('record_id', null);
		$paramName = JRequest::getVar('param_name', null);
		$value = JRequest::getVar('value', null, 'post', 'string', JREQUEST_ALLOWHTML);

//JRequest::getVar( 'yourfieldname', '', 'post', 'string', JREQUEST_ALLOWHTML );

		if (!$pid || !$ptid || !$recordId || !$paramName) die();

		$db = JFactory::getDBO();

		$value = $value ? $db->quote($value) : 'NULL';

		$q = "UPDATE `#__fastseller_hs_product_type_". $ptid .
			"` SET `". $paramName ."`=". $value .
			" WHERE `id`='". $recordId ."' AND `product_id`='". $pid ."'";

		$db->setQuery($q);
		$db->query();

		//var_dump($db);
		//var_dump($filters);
		$q = "SELECT `parameter_type` FROM `#__fastseller_hs_product_type_parameter`".
			" WHERE `parameter_name`='$paramName'";
		$db->setQuery($q);
		$type = $db->loadResult();

		// parameters of type HTML (T) do not need type re-determinition
		// or table rebuild
		if ($type != 'T') {
			if (self::parameterFiltersAreDefinedManually($paramName)) {
				self::determineParameterMultiAssignedStatus($ptid, $paramName);
			} else {
				self::rebuildFiltersForParameter($ptid, $paramName);
			}
		}
	}


	private static function parameterFiltersAreDefinedManually($paramName) {
		$db = JFactory::getDBO();
		$q = "SELECT `define_filters_manually` FROM `#__fastseller_hs_product_type_parameter`".
			" WHERE `parameter_name`='$paramName'";
		$db->setQuery($q);

		return $db->loadResult();
	}



	private function determineParameterMultiAssignedStatus($ptid, $paramName) {
		$db = JFactory::getDBO();
		$q = "SELECT `$paramName` FROM `#__fastseller_hs_product_type_$ptid` WHERE `$paramName` LIKE '%;%' LIMIT 1";
		$db->setQuery($q);
		$thereAreMultiAssigned = $db->loadResult();
		$paramType = ($thereAreMultiAssigned) ? 'V' : 'S';

		$q = "UPDATE `#__fastseller_hs_product_type_parameter` SET `parameter_type`='$paramType'".
			" WHERE `product_type_id`='$ptid' AND `parameter_name`='$paramName'";

		$db->setQuery($q);
		$db->query();
	}


	// We collect all currently assigned filters for certain parameter,
	// sort them, and update "parameter_values" column
	static private function rebuildFiltersForParameter($ptid, $paramName) {
		$db = JFactory::getDBO();
		$q = "SELECT DISTINCT `$paramName` FROM `#__fastseller_hs_product_type_$ptid`";
		$db->setQuery($q);
		$result = $db->loadColumn();

		$data = array();
		$multiAssigned = false;
		foreach ($result as $r) {
			if (!$r) continue;

			$values = explode(';', $r);
			if (count($values) > 1) {
				$multiAssigned = true;
				foreach ($values as $value) {
					if (!in_array($value, $data)) $data[] = $value;
				}
			} else {
				if (!in_array($values[0], $data)) $data[] = $values[0];
			}
		}

		//if (!$data) return;

		//sort($data);
		if ($data) natcasesort($data);
		$filtersStr = implode(';', $data);

		// We need to keep eye on "parameter_values" VARCHAR(LIMIT)
		// If current filters string size larger then the limit -- increase it.
		// Update: we're using TEXT type now for paramter schema
		//$filtersColumnSize = self::getSizeOfParameterValuesColumn();
		//if (function_exists('mb_strlen')) {
		//	$currentFiltersLen = mb_strlen($filtersStr, 'UTF-8');
		//} else {
		//	$currentFiltersLen = strlen($filtersStr);
		//}
		//if ($currentFiltersLen > $filtersColumnSize)
		//	self::increaseParameterValuesColumnSize($currentFiltersLen);

		//print_r($data);
		//echo $multiAssigned;

		$paramType = ($multiAssigned) ? 'V' : 'S';

		$q = "UPDATE `#__fastseller_hs_product_type_parameter` SET `parameter_type`='$paramType', `parameter_values`=".
			$db->quote($filtersStr) ." WHERE `product_type_id`='$ptid' AND `parameter_name`='$paramName'";


		$db->setQuery($q);
		$db->query();

		//var_dump($db);

	}


	static public function loadAvailableFiltersForParameter() {
		$ptid = (int)JRequest::getVar('ptid', null);
		$paramName = JRequest::getVar('param_name', null);

		if (!$ptid || !$paramName) die();

		$db = JFactory::getDBO();

		$q = "SELECT `parameter_values` FROM `#__fastseller_hs_product_type_parameter`".
			" WHERE `product_type_id`='$ptid' AND `parameter_name`='$paramName' LIMIT 1";

		$db->setQuery($q);
		$filtersStr = $db->loadResult();

		//require(FS_PATH .'views/FSAssignFiltersView.php');
		self::loadView();
		FSAssignFiltersView::printAvailableFiltersForParameter($filtersStr);

	}


	static public function showCategoriesTree() {
		$db = JFactory::getDBO();
		$cid = (int)JRequest::getVar('cid', null);

		//if (!$cid) die();

		if (! $cid) {
			$q = "SELECT `category_id` FROM `#__hikashop_category`".
				" WHERE `category_parent_id` IN (".
					" SELECT `category_id` FROM `#__hikashop_category`".
					" WHERE `category_parent_id`=0)";
			$db->setQuery($q);
			$cid = $db->loadResult();
		}


		$q = "SELECT `category_id` as id, `category_name` as name, `category_published` as published".
			" FROM `#__hikashop_category`".
			" WHERE `category_parent_id`='$cid'".
			//" AND c.`virtuemart_category_id`=c_lang.`virtuemart_category_id`".
			" ORDER BY `category_ordering`";

		$db->setQuery($q);
		$categoriesData = $db->loadAssocList();

		if (!$categoriesData)
			return;

		//require(FS_PATH .'views/FSAssignFiltersView.php');
		self::loadView();
		FSAssignFiltersView::printCategoriesTree($categoriesData);
	}


	static public function showProductTypesList() {
		$db = JFactory::getDBO();

		$q = "SELECT * FROM `#__fastseller_hs_product_type` ORDER BY `product_type_list_order`";
		$db->setQuery($q);
		$ptsData = $db->loadAssocList();

		//require(FS_PATH .'views/FSAssignFiltersView.php');
		self::loadView();
		FSAssignFiltersView::printProductTypesList($ptsData);
	}


	static public function getCategoryName($cid) {
		$db = JFactory::getDBO();

		$q = "SELECT `category_name` FROM `#__hikashop_category` WHERE `category_id`='$cid'";
		$db->setQuery($q);
		$categoryName = $db->loadResult();

		return $categoryName;
	}


	static public function product_has_children($pid) {
		if (! self::thereAreChildrenProducts())
			return false;

		$db = JFactory::getDBO();
		$q = "SELECT COUNT(`product_id`) FROM `#__hikashop_product` WHERE `product_parent_id`='$pid'".
			" LIMIT 1";
		$db->setQuery($q);

		return ($db->loadResult() ? true : false);
	}


	static private function thereAreChildrenProducts() {
		if (self::$thereAreChildrenProducts !== -1) {
			return self::$thereAreChildrenProducts;
		}

		$db = JFactory::getDBO();
		$q = "SELECT `product_id` FROM `#__hikashop_product`".
			" WHERE `product_parent_id`<>0".
			" LIMIT 1";

		$db->setQuery($q);
		$res = $db->loadResult();

		$thereAreChildrenProducts = ($res) ? true : false;
		self::$thereAreChildrenProducts = $thereAreChildrenProducts;

		return $thereAreChildrenProducts;
	}


	static public function showProductDescription() {
		$db = JFactory::getDBO();
		$pid = (int)JRequest::getVar('pid', null);

		$q = "SELECT `product_name`, `product_description`, hsfile.`file_path` as image".
			" FROM (`#__hikashop_product` as p)".
			" LEFT JOIN `#__hikashop_file` as hsfile ON p.`product_id`=hsfile.`file_ref_id`".
			//" LEFT JOIN `#__virtuemart_medias` as medias ON pm.`virtuemart_media_id`=medias.`virtuemart_media_id` ".
			" WHERE `product_id`='$pid'";

		$db->setQuery($q);
		$productData = $db->loadAssoc();

		//$custom_fields = self::getProductCustomFields($pid);
		//$productData['custom_fields'] = empty($custom_fields) ? '' : $custom_fields;

		//require(FS_PATH .'views/FSAssignFiltersView.php');
		self::loadView();
		FSAssignFiltersView::printProductDescription($productData);
	}


	static private function getProductCustomFields($product_id) {
		$db = JFactory::getDBO();

		$q = "SELECT GROUP_CONCAT( IF (pc.`custom_value`<>'', pc.`custom_value`, NULL) SEPARATOR ';') as filters,".
			" `custom_title`, `custom_field_desc`, `virtuemart_custom_id`, `custom_parent_id`, `field_type`".
			" FROM `#__virtuemart_product_customfields` as pc".
			" LEFT JOIN `#__virtuemart_customs` USING (`virtuemart_custom_id`)".
			" WHERE `virtuemart_product_id`='". $product_id ."'".
			" GROUP BY `custom_title`".
			" ORDER BY `custom_parent_id`, `virtuemart_custom_id`";

		$db->setQuery($q);
		$data = $db->loadAssocList();
		if (! $data)
			return false;

//		foreach ($data as $cf) {
//			if ($cf['field_type'] == 'P') {
//				$custom_fields[ $cf['virtuemart_custom_id'] ]['title'] = $data['custom_title'];
//				$custom_fields[ $cf['virtuemart_custom_id'] ]['desc'] = $data['custom_field_desc'];
//				foreach ($data as $cfi) {
//					if ($cf['virtuemart_custom_id'] == $cfi['custom_parent_id']) {
//						$custom_fields[ $cf['virtuemart_custom_id'] ]['fields'][] = $cfi;
//					}
//				}

		return $data;
	}



	static public function loadView() {
		require(FS_PATH .'views/FSAssignFilters/FSAssignFiltersView.php');
	}


	//static public function getSizeOfParameterValuesColumn() {
	//	$db = JFactory::getDBO();
	//	$config = JFactory::getConfig();

	//	$q = "SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS".
	//		" WHERE table_name = '". $config->getValue( 'config.dbprefix' ). "fastseller_hs_product_type_parameter'".
	//		" AND table_schema = '". $config->getValue( 'config.db' ) ."'".
	//		" AND column_name LIKE 'parameter_values'";

	//	$db->setQuery($q);
	//	$size = $db->loadResult();

	//	return $size;
	//}


	//static public function increaseParameterValuesColumnSize($curentSize) {
	//	$chunks = ceil($curentSize / 256);
	//	$next_column_size = $chunks * 256;

	//	$db = JFactory::getDBO();

	//	$q = "ALTER TABLE `#__fastseller_hs_product_type_parameter` CHANGE `parameter_values` `parameter_values`".
	//		" VARCHAR( $next_column_size )";
	//	$db->setQuery($q);
	//	if ($db->query()) echo $next_column_size;
	//}






	//

	static public function getProductTypeName($id) {
		return self::$productTypesData[$id]['name'];
	}

	static public function getProductTypeParameters($id) {
		return self::$parametersData[$id];
	}

	static public function getAvailableProductTypes() {
		return self::$productTypesData;
	}

	static public function getExecuteTime() {
		return self::$executeTime;
	}

	static public function getProductsCount() {
		return self::$productsCount;
	}


}
