<?php

namespace Drupal\basket\Query;

class BasketGetNodePriceQuery{

	protected static $getQuery;
	protected static $getNodeTypesFields;
	protected static $getDefCurrency;
	protected static $isConvert;

	const EMPTY_PRICE_FIELD = 'EMPTY_PRICE_FIELD';

	public static function getQuery($keyPriceField = 'MIN'){
		if(!self::$getQuery){
			self::$getQuery = NULL;
			$getNodeTypesFields = self::getNodeTypesFields();
	        if(!empty($getNodeTypesFields)){
	            $queries = [];
	            foreach ($getNodeTypesFields as $priceField => $keyNodeTypes){
	            	$queries[$priceField] = NULL;
	            	if($priceField == self::EMPTY_PRICE_FIELD){
	            		// Alter
	            		\Drupal::moduleHandler()->alter('basketNodeGetPriceField', $queries[$priceField], $keyNodeTypes);
	            		// End alter
	            	}
	            	if(empty($queries[$priceField])){
		                $priceFields = explode('->', $priceField);
		                $isAddPrice = FALSE;
		                switch(count($priceFields)){
		                    case 1:
		                        $priceFields = reset($priceFields);
		                        if(\Drupal::database()->schema()->tableExists('node__'.$priceFields)){
		                            $queries[$priceField] = \Drupal::database()->select('node__'.$priceFields, $priceFields);
		                            $queries[$priceField]->addExpression($priceFields.'.entity_id', 'nid');
		                            $queries[$priceField]->addExpression($priceFields.'.'.$priceFields.'_value', 'price');
		                            $queries[$priceField]->addExpression($priceFields.'.'.$priceFields.'_currency', 'currency');
		                            // Price convert
		                        	if(self::isConvert()){
			                        	$queries[$priceField]->innerJoin('basket_currency', 'bcDef', 'bcDef.id = '.\Drupal::service('Basket')->Currency()->getCurrent());
			                        	$queries[$priceField]->innerJoin('basket_currency', 'bc', 'bc.id = '.$priceFields.'.'.$priceFields.'_currency');
		                        		$queries[$priceField]->addExpression($priceFields.'.'.$priceFields.'_value*(bc.rate/bcDef.rate)', 'priceConvert');
			                        	$queries[$priceField]->addExpression($priceFields.'.'.$priceFields.'_old_value*(bc.rate/bcDef.rate)', 'priceConvertOld');
		                        	} else {
			                        	$queries[$priceField]->addExpression($priceFields.'.'.$priceFields.'_value', 'priceConvert');
			                        	$queries[$priceField]->addExpression($priceFields.'.'.$priceFields.'_old_value', 'priceConvertOld');
		                        	}
		                        	// Price convert AND
		                        	if($keyPriceField == 'FIRST'){
		                        		$queries[$priceField]->condition($priceFields.'.delta', 0);
		                        	}
		                        }
		                    break;
		                    case 2:
		                        if(\Drupal::database()->schema()->tableExists("node__$priceFields[0]") && \Drupal::database()->schema()->tableExists("paragraph__$priceFields[1]")){
		                        	$queries[$priceField] = \Drupal::database()->select("node__$priceFields[0]", $priceFields[0]);
		                            $queries[$priceField]->addExpression("$priceFields[0].entity_id", 'nid');
		                        	$queries[$priceField]->leftJoin("paragraph__$priceFields[1]", $priceFields[1], "$priceFields[1].entity_id = $priceFields[0].$priceFields[0]_target_id");
		                        	$queries[$priceField]->addExpression("$priceFields[1].$priceFields[1]_value", 'price');
		                        	$queries[$priceField]->addExpression("$priceFields[1].$priceFields[1]_currency", 'currency');
		                        	// Price convert
		                        	if(self::isConvert()){
			                        	$queries[$priceField]->innerJoin('basket_currency', 'bcDef', 'bcDef.id = '.\Drupal::service('Basket')->Currency()->getCurrent());
			                        	$queries[$priceField]->innerJoin('basket_currency', 'bc', "bc.id = $priceFields[1].$priceFields[1]_currency");
			                        	$queries[$priceField]->addExpression("$priceFields[1].$priceFields[1]_value*(bc.rate/bcDef.rate)", 'priceConvert');
			                        	$queries[$priceField]->addExpression("$priceFields[1].$priceFields[1]_old_value*(bc.rate/bcDef.rate)", 'priceConvertOld');
		                        	} else {
		                        		$queries[$priceField]->addExpression("$priceFields[1].$priceFields[1]_value", 'priceConvert');
			                        	$queries[$priceField]->addExpression("$priceFields[1].$priceFields[1]_old_value", 'priceConvertOld');
		                        	}
		                        	// Price convert AND
		                        	if($keyPriceField == 'FIRST'){
		                        		$queries[$priceField]->condition("$priceFields[0].delta", 0);
		                        	}
		                        }
		                    break;
		                }
	            	}
	            	if(empty($queries[$priceField])) unset($queries[$priceField]);
	            }
	            if(!empty($queries)){
	            	$priceQuery = NULL;
		            foreach ($queries as $subQuery) {
		                if(is_null($priceQuery)){
		                    $priceQuery = $subQuery;
		                } else {
		                    $priceQuery->union($subQuery, 'ALL');
		                }
		            }
		            self::$getQuery = $priceQuery;
	            }
	        }
		}
		return self::$getQuery;
	}
	public static function isConvert(){
		if(!isset(self::$isConvert)){
			self::$isConvert = FALSE;
			$currency = \Drupal::service('Basket')->Currency()->tree();
			if(!empty($currency) && count($currency) > 1){
				self::$isConvert = TRUE;
			}
		}
		return self::$isConvert;
	}
	public static function getNodeTypesFields(){
        if(!self::$getNodeTypesFields){
            self::$getNodeTypesFields = [];
            $results = \Drupal::database()->select('basket_node_types', 'n')
                                                    ->fields('n', ['type', 'price_field'])
                                                    ->execute()->fetchAll();
            if(!empty($results)){
                foreach ($results as $result){
                	if(empty($result->price_field)) $result->price_field = self::EMPTY_PRICE_FIELD;
                    self::$getNodeTypesFields[$result->price_field][$result->type] = $result->type;
                }
            }
        }
        return self::$getNodeTypesFields;
    }
    public static function getDefCurrency(){
        if(!self::$getDefCurrency){
            self::$getDefCurrency = \Drupal::service('Basket')->Currency()->getCurrent();
        }
        return self::$getDefCurrency;
    }

    public static function viewsJoin(&$view, $keyPriceField = 'MIN', $filter = []){
    	if(empty($view->query->relationships[$view->field.'_getPriceQuery_'.$keyPriceField])){
    		$getPriceQuery = self::getQuery($keyPriceField);
	        if(!empty($getPriceQuery)){
	        	// ---
	        	$joinType = 'LEFT';
	        	// ---
	        	$subQuery = \Drupal::database()->select('node_field_data', 'n');
	        	$subQuery->leftJoin($getPriceQuery, 'getPriceQuery', 'getPriceQuery.nid = n.nid');
	        	$subQuery->addExpression('n.nid', 'nid');
	        	$subQuery->addExpression("COALESCE($keyPriceField(getPriceQuery.priceConvert), 0)", 'priceConvert');
	        	$subQuery->addExpression("COALESCE($keyPriceField(getPriceQuery.priceConvertOld), 0)", 'priceConvertOld');
	        	$subQuery->groupBy('n.nid');
	        	if(!empty($filter['min']) || !empty($filter['max'])){
					if(!empty($filter['min'])){
	        			$subQuery->where('ROUND(COALESCE(getPriceQuery.priceConvert, 0), 0) >= '.$filter['min']);
					}
					if(!empty($filter['max'])){
	        			$subQuery->where('ROUND(COALESCE(getPriceQuery.priceConvert, 0), 0) <= '.$filter['max']);
					}
					$joinType = 'INNER';
	        	}
	        	// ---
	            $join = \Drupal\views\Views::pluginManager('join')->createInstance('standard', [
	                'type'          => $joinType,
	                'table'         => $subQuery,
	                'field'         => 'nid',
	                'left_table'    => 'node_field_data',
	                'left_field'    => 'nid',
	                'operator'      => '=',
	            ]);
	            $rel = $view->query->addRelationship($view->field.'_getPriceQuery_'.$keyPriceField, $join, 'node_field_data');
	            $view->query->addField($view->field.'_getPriceQuery_'.$keyPriceField, 'priceConvert', 'basket_node_priceConvert');
	        } else {
	    		$view->query->addField(NULL, 0, 'basket_node_priceConvert');
	        }
    	}
    }
    public static function viewsJoinSort(&$view, $order, $keyPriceField = 'MIN', $filter = []){
    	self::viewsJoin($view, $keyPriceField);
    	if(!empty($view->query->relationships[$view->field.'_getPriceQuery_'.$keyPriceField])){
    		$view->query->addOrderBy(NULL, $view->field.'_getPriceQuery_'.$keyPriceField.'.priceConvert', $order, '_getPriceSort');
    	}
    }
    public static function getNodePriceMIN($entity, $keyPriceField = 'MIN', $filter = []){
    	$getPrice = NULL;
    	$getSettings = \Drupal::service('Basket')->getClass('Drupal\basket\BasketExtraFields')->getNodeTypeSettings($entity->bundle());
    	if(empty($getSettings->price_field)){
    		$queryPrice = NULL;
    		$nodeTypes = [$entity->bundle()];
    		$nid = $entity->id();
    		// Alter
    		\Drupal::moduleHandler()->alter('basketNodeGetPriceField', $queryPrice, $nodeTypes, $nid);
    		// End alter
    		if(!empty($queryPrice)){
    			$getPrice = $queryPrice->execute()->fetchObject();
    		}
    	}
        if(!empty($getSettings->price_field)){
        	$query = \Drupal::database()->select('node_field_data', 'n');
            $query->condition('n.nid', $entity->id());
            $query->condition('n.default_langcode', 1);
            // ---
            $priceFields = explode('->', $getSettings->price_field);
            switch(count($priceFields)){
                case 1:
                    $priceFields = reset($priceFields);
                    if(\Drupal::database()->schema()->tableExists('node__'.$priceFields)){
                        $query->leftJoin('node__'.$priceFields, $priceFields, $priceFields.'.entity_id = n.nid');
                        // Price convert
                    	$query->innerJoin('basket_currency', 'bcDef', 'bcDef.id = '.\Drupal::service('Basket')->Currency()->getCurrent());
                    	$query->innerJoin('basket_currency', 'bc', 'bc.id = '.$priceFields.'.'.$priceFields.'_currency');
                    	$query->addExpression($priceFields.'.'.$priceFields.'_value*(bc.rate/bcDef.rate)', 'priceConvert');
                    	$query->addExpression($priceFields.'.'.$priceFields.'_old_value*(bc.rate/bcDef.rate)', 'priceConvertOld');
                    	// Price convert AND
                    	if(!empty($filter['min']) || !empty($filter['max'])){
							if(!empty($filter['min'])){
				    			$query->where('ROUND('.$priceFields.'.'.$priceFields.'_value*(bc.rate/bcDef.rate)) >= '.$filter['min']);
							}
							if(!empty($filter['max'])){
				    			$query->where('ROUND('.$priceFields.'.'.$priceFields.'_value*(bc.rate/bcDef.rate)) <= '.$filter['max']);
							}
				    	}
                    }
                break;
                case 2:
                    if(\Drupal::database()->schema()->tableExists("node__$priceFields[0]") && \Drupal::database()->schema()->tableExists("paragraph__$priceFields[1]")){
                    	$query->leftJoin("node__$priceFields[0]", $priceFields[0], "$priceFields[0].entity_id = n.nid");
                    	$query->leftJoin("paragraph__$priceFields[1]", $priceFields[1], "$priceFields[1].entity_id = $priceFields[0].$priceFields[0]_target_id");
                    	$query->addExpression("$priceFields[1].entity_id", 'pid');
                    	// Price convert
                    	$query->innerJoin('basket_currency', 'bcDef', 'bcDef.id = '.\Drupal::service('Basket')->Currency()->getCurrent());
                    	$query->innerJoin('basket_currency', 'bc', "bc.id = $priceFields[1].$priceFields[1]_currency");
                    	switch($keyPriceField){
                    		case'FIRST':
	                    		$query->condition("$priceFields[0].delta", 0);
	                    		$query->addExpression("$priceFields[1].$priceFields[1]_value*(bc.rate/bcDef.rate)", 'priceConvert');
		                    	$query->addExpression("$priceFields[1].$priceFields[1]_old_value*(bc.rate/bcDef.rate)", 'priceConvertOld');
                    		break;
                    		default:
                    			$query->addExpression("$priceFields[1].$priceFields[1]_value*(bc.rate/bcDef.rate)", 'priceConvert');
	                    		$query->addExpression("$priceFields[1].$priceFields[1]_old_value*(bc.rate/bcDef.rate)", 'priceConvertOld');
	                    		$query->range(0, 1);
	                    		switch($keyPriceField){
	                    			case'MIN':
	                    				$query->orderBy('priceConvert', 'ASC');
	                    			break;
	                    			case'MAX':
	                    				$query->orderBy('priceConvert', 'DESC');
	                    			break;
	                    		}
                    		break;
                    	}
                    	$query->range(0, 1);
                    	// Price convert AND
			            if(!empty($filter['min']) || !empty($filter['max'])){
							if(!empty($filter['min'])){
				    			$query->where("ROUND($priceFields[1].$priceFields[1]_value*(bc.rate/bcDef.rate)) >= ".$filter['min']);
							}
							if(!empty($filter['max'])){
				    			$query->where("ROUND($priceFields[1].$priceFields[1]_value*(bc.rate/bcDef.rate)) <= ".$filter['max']);
							}
				    	}
                    }
                break;
            }
            $getPrice = $query->execute()->fetchObject();
        }
    	return !empty($getPrice) ? $getPrice : NULL;
    }
}