<?php

namespace rednaoeasycalculationforms\pr\QueryBuilder;

use rednaoeasycalculationforms\core\db\core\DBManager;
use rednaoeasycalculationforms\core\Loader;
use rednaoeasycalculationforms\core\Utils\ArrayUtils;
use rednaoeasycalculationforms\DTO\FormulaOptionsDTO;
use rednaoeasycalculationforms\pr\QueryBuilder\AggregationBuilder\AggregationQueryManager;
use rednaoeasycalculationforms\pr\QueryBuilder\Comparison\ColumnComparison;
use rednaoeasycalculationforms\pr\QueryBuilder\Comparison\FixedValueComparison;
use rednaoeasycalculationforms\pr\QueryBuilder\Filters\FilterGroup;
use rednaoeasycalculationforms\pr\QueryBuilder\QueryElement\Dependency;
use rednaoeasycalculationforms\pr\QueryBuilder\QueryElement\QueryElement;
use rednaoeasycalculationforms\Utilities\Sanitizer;

class QueryBuilder
{
    public $Level;
    /** @var QueryElement[] */
    public $Elements;
    public $RootTable;
    public $RootID;
    /** @var FilterGroup[] */
    public $RootConditions;
    /** @var Dependency[] */
    public $Dependencies;
    /** @var FilterGroup[] */
    public $Filters;
    public $LastQuery = '';
    public $SortItems;
    /** @var string[] */
    public $Columns;
    public $NegateFilter;
    /** @var Loader */
    public $Loader;
    /** @var FormulaOptionsDTO[] */
    public $Formulas;
    public $GlobalFormulas = [];
    public $GlobalFormulasResults = [];

    /**
     * QueryBuilder constructor.
     * @param $RootTable
     * @param $RootID
     * @param $rootConditions FilterGroup[]
     */
    public function __construct($Loader, $RootTable = null, $RootID = null, $rootConditions = [])
    {
        if($RootTable == null)
            $RootTable = $this->Loader->RECORDS_TABLE;
        if($RootID == null)
            $RootID = 'ROOT';
        $this->SortItems = [];
        $this->Loader = $Loader;
        if($rootConditions == null)
            $rootConditions = [];
        $this->RootTable = $RootTable;
        $this->RootID = $RootID;
        $this->RootConditions = $rootConditions;
        $this->Filters = [];
        $this->NegateFilter = false;
        $this->Elements = [];
        $this->Dependencies = [];
        $this->Formulas = [];
    }

    public function SetNegateFilter() {
        $this->NegateFilter = true;
    }

    /**
     * @param $queryElement QueryElement
     */
    public function AddQueryElement($queryElement)
    {
        $this->Elements[] = $queryElement;
    }

    public function Execute($limit = 0, $skip = 0)
    {

        $limitFilter = '';
        if($limit > 0)
        {
            $limitFilter = ' limit ';

            if($skip > 0)
                $limitFilter .= $skip . ' , ';

            $limitFilter .= $limit;
        }

        $this->AddFormulaDependencies();
        $sortSection = $this->GenerateSorts();
        if($sortSection == '')
            $sortSection = ' order by ' . $this->RootID . '.date desc';
        $this->GenerateColumns();
        $filters = $this->GenerateFilterString();

        $query = $this->CreateQueryString();

        $query = $query . ' ' . $filters . $sortSection . $limitFilter;
        $this->LastQuery = $query;
        return $query;

    }

    public function AddFormulaDependencies()
    {
        foreach($this->Formulas as $currentFormula)
        {
            foreach($currentFormula->Condition->ConditionGroups as $conditionGroup)
                foreach($conditionGroup->ConditionLines as $conditionLine)
                {
                    if(!$conditionLine->HasMapping)
                        continue;
                    $tableId = $this->RootID . '_' . $conditionLine->FieldId . '_' . $conditionLine->PathId;
                    $dependency = new Dependency($this->Loader->RECORDS_DETAIL, $tableId);
                    $dependency->Comparisons[] = new ColumnComparison($this->RootID, 'entry_id', 'Equal', $tableId, 'entry_id');
                    $dependency->Comparisons[] = new FixedValueComparison($tableId, 'field_id', 'Equal', $conditionLine->FieldId);

                    if($conditionLine->PathId == 'Value' || $conditionLine->PathId == '')
                        $dependency->Comparisons[] = new FixedValueComparison($tableId, 'subtype', 'Equal', '');
                    else
                        $dependency->Comparisons[] = new FixedValueComparison($tableId, 'subtype', 'Equal', '');

                    $this->AddDependency($dependency);
                }

        }

    }

    public function GetCount()
    {
        $filters = $this->GenerateFilterString();
        $query = $this->CreateQueryString(true);

        $query = $query . ' ' . $filters;
        $this->LastQuery = $query;

        $dbmanager = new DBManager(true);
        return intval($dbmanager->GetVar($query));

    }

    public function GetRows($limit = 0, $skip = 0) {
        $dbmanager = new DBManager(true);
        $result = $dbmanager->GetResults($this->Execute($limit, $skip));
        $this->LastQuery = $dbmanager->LastQuery;

        foreach($this->GlobalFormulas as $key => $currentGlobalFormula)
            $this->GlobalFormulasResults[$key] = $dbmanager->GetVar($currentGlobalFormula);
        return $result;
    }

    private function GenerateColumns()
    {
        $this->Columns = [];
        foreach($this->Elements as $element)
        {
            foreach($element->Dependencies as $dependency)
            {
                if($this->HasDependency($dependency->Id))
                    continue;
                $this->Dependencies[] = $dependency;
            }
            foreach($element->Columns as $column)
            {
                $column = $column->CreateColumn();
                if(ArrayUtils::Find($this->Columns, function ($item) use ($column) {return $item == $column;}) == null)
                    $this->Columns[] = $column;
            }

        }

        if(count($this->Columns) == 0 || count($this->Columns) == count($this->SortItems))
        {
            $this->Columns[] = $this->RootID . '.user_id';
            $this->Columns[] = $this->RootID . '.sequence';
            $this->Columns[] = $this->RootID . '.formatted_sequence';
            $this->Columns[] = $this->RootID . '.date';
            $this->Columns[] = $this->RootID . '.data';
            $this->Columns[] = $this->RootID . '.total';
            $this->Columns[] = $this->RootID . '.quantity';
            $this->Columns[] = $this->RootID . '.status';
            $this->Columns[] = $this->RootID . '.form_id';
            $this->Columns[] = $this->RootID . '.entry_id';
            $this->Columns[] = $this->RootID . '.reference_id';
            $this->Columns[] = $this->RootID . '.meta_values';
        }

    }

    public function HasDependency($dependency)
    {
        return ArrayUtils::Find($this->Dependencies, function ($item) use ($dependency) {
            return $dependency == $item->Id;
        }) != null;

    }

    private function GenerateFilterString()
    {
        $filters = '';
        if(count($this->RootConditions) > 0){
            foreach ($this->RootConditions as $filterGroup)
            {
                $filters .= ' and ' . $filterGroup->CreateGroupString();
            }
        }

        $andFilters = '';
        $orFilters = '';

        foreach($this->Filters as $filterGroup)
        {
            if(count($filterGroup->FilterLines) == 0)
                continue;
            if($filterGroup->JoinType == 'and')
            {
                if($andFilters != '')
                    $andFilters = $andFilters . ' and ';
                $andFilters .= $filterGroup->CreateGroupString();
            }
        }
        if($andFilters != '')
            $andFilters = ' and (' . $andFilters . ')';

        foreach($this->Filters as $filterGroup)
        {
            if($filterGroup->JoinType == 'or')
            {
                if($orFilters != '')
                    $orFilters = $orFilters . ' or ';
                $orFilters .= $filterGroup->CreateGroupString();
            }
        }
        if($orFilters != '')
            $orFilters = ' and (' . $orFilters . ')';

        $filters = $andFilters . $orFilters;

        if($filters != '')
        {
            if($this->NegateFilter)
                $filters = ' and not(1=1 ' . $filters . ')';

            $filters = ' where 1=1' . $filters;
        }

        return $filters;

    }

    private function CreateQueryString($isCountString = false)
    {
        $query = $this->GenerateColumnsSectionQuery($isCountString);
        $query .= ' from ' . $this->RootTable . ' ' . $this->RootID;

        foreach($this->Dependencies as $dependency)
        {
            $query .= $dependency->CreateJoin();
        }

        return $query;

    }

    protected function GenerateColumnsSectionQuery($isCountString = false)
    {
        if($isCountString)
            return ' select count(*) ';
        else
            return ' select distinct ' . \implode(', ', $this->Columns) . $this->GenerateFormulas();
    }

    public function AddFormula($currentFormula)
    {
        $this->Formulas[] = $currentFormula;
    }

    public function GenerateFormulas()
    {
        $formulas = '';
        $this->GlobalFormulas = [];
         foreach($this->Formulas as $currentFormula)
        {
            $formulaRootTable = 'Formula_' . $currentFormula->Id;
            $query = new AggregationQueryManager($this->Loader, $currentFormula->FormToUse, null, $currentFormula, $formulaRootTable);
            $query->AddCondition($currentFormula->Condition);

            $isGlobal = true;
            foreach($currentFormula->Condition->ConditionGroups as $currentCondition)
            {
                $filterGroup = new FilterGroup($query);

                foreach($currentCondition->ConditionLines as $conditionLine)
                {
                    if($conditionLine->HasMapping && Sanitizer::GetStringValueFromPath($conditionLine, ['Value', 'Type']) != 'SearchField')
                        $isGlobal = false;
                }
            }

            //Add a join to the column we are going to sum
            if($currentFormula->FieldToUse != '')
            {

                /** @var FieldSettingsBase $fieldSetting */
                $fieldSetting = ArrayUtils::Find($this->FieldSettings, function ($item) use ($currentFormula) {
                    return $item->Id == $currentFormula->FieldToUse;
                });

                $dependency = new Dependency($this->Loader->RECORDS_DETAIL, 'aggregation');
                $dependency->Comparisons[] = new ColumnComparison($formulaRootTable, 'entry_id', 'Equal', 'aggregation', 'entry_id');
                $dependency->Comparisons[] = new FixedValueComparison('aggregation', 'field_id', 'Equal', $currentFormula->FieldToUse);

                if($fieldSetting != null && $fieldSetting->GetAggregationPath() != '')
                {
                    $dependency->Comparisons[] = new FixedValueComparison('aggregation', 'path_id', 'Equal', $fieldSetting->GetAggregationPath());

                }
                $query->QueryBuilder->AddDependency($dependency);
            }

            if($isGlobal)
                $this->GlobalFormulas[$currentFormula->Id] = $query->GetQuery();
            else
                $formulas .= ',(' . $query->GetQuery() . ') ' . 'formula_' . $currentFormula->Id;

        }
        return $formulas;

    }

    /**
     * @param $dependency Dependency
     */
    public function AddDependency($dependency)
    {
        if(ArrayUtils::Some($this->Dependencies, function ($item) use ($dependency) {
            return $dependency->Id == $item->Id;
        }))
            return;

        $this->Dependencies[] = $dependency;
    }

    private function GenerateSorts()
    {
        $sortText = '';
        foreach($this->SortItems as $currentSort)
        {
            $sortId = $currentSort->FieldId . '_' . $currentSort->PathId;
            $dependency = new Dependency($this->Loader->RECORDS_DETAIL, $sortId);
            $this->AddDependency($dependency);
            $dependency->AddComparison(new ColumnComparison($this->RootID, 'entry_id', 'Equal', $sortId, 'entry_id'));
            $dependency->Comparisons[] = new FixedValueComparison($sortId, 'field_id', 'Equal', $currentSort->FieldId);

            if($currentSort->PathId == '' || $currentSort->PathId == 'Value')
                $dependency->Comparisons[] = new FixedValueComparison($sortId, 'subtype', 'Equal', '');
            else
                $dependency->Comparisons[] = new FixedValueComparison($sortId, 'subtype', 'Equal', $currentSort->PathId);

            $column = new QueryElement();
            $column->AddColumn2($sortId, 'value', $sortId);
            $this->AddQueryElement($column);

            if($sortText != '')
                $sortText .= ",";

            $sortText .= $sortId . ' ' . $currentSort->Orientation;
        }
        if($sortText != '')
            $sortText = ' order by ' . $sortText . ' ';
        return $sortText;
    }

}