<?php

/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace onespace\tools\components\azuredb;

use DateTime;
use MicrosoftAzure\Storage\Table\Models\EdmType;
use Yii;
use yii\helpers\ArrayHelper;
use yii\base\InvalidParamException;
use yii\db\ExpressionInterface;
use yii\db\Query as DbQuery;
use yii\db\QueryInterface;

/**
 * Query represents a SELECT SQL statement in a way that is independent of DBMS.
 *
 * Query provides a set of methods to facilitate the specification of different clauses
 * in a SELECT statement. These methods can be chained together.
 *
 * By calling [[createCommand()]], we can get a [[Command]] instance which can be further
 * used to perform/execute the DB query against a database.
 *
 * For example,
 *
 * ```php
 * $query = new Query;
 * // compose the query
 * $query->select('id, name')
 *     ->from('user')
 *     ->limit(10);
 * // build and execute the query
 * $rows = $query->all();
 * // alternatively, you can create DB command and execute it
 * $command = $query->createCommand();
 * // $command->sql returns the actual SQL
 * $rows = $command->queryAll();
 * ```
 *
 * Query internally uses the [[QueryBuilder]] class to generate the SQL statement.
 *
 * A more detailed usage guide on how to work with Query can be found in the [guide article on Query Builder](guide:db-query-builder).
 *
 * @property string[] $tablesUsedInFrom Table names indexed by aliases. This property is read-only.
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @author Carsten Brandt <mail@cebe.cc>
 * @since 2.0
 */
class Query extends DbQuery implements QueryInterface, ExpressionInterface {
    /**
     * @var array
     */
    protected $conditionFilters = [
        'NOT' => 'applyClientFilterNotCondition',
        'AND' => 'applyClientFilterAndCondition',
        'OR' => 'applyClientFilterOrCondition',
        'BETWEEN' => 'applyClientFilterBetweenCondition',
        'NOT BETWEEN' => 'applyClientFilterBetweenCondition',
        'IN' => 'applyClientFilterInCondition',
        'NOT IN' => 'applyClientFilterInCondition',
        'LIKE' => 'applyClientFilterLikeCondition',
        'NOT LIKE' => 'applyClientFilterLikeCondition',
        'OR LIKE' => 'applyClientFilterLikeCondition',
        'OR NOT LIKE' => 'applyClientFilterLikeCondition',
        'CALLBACK' => 'applyClientFilterCallbackCondition',
    ];

    /**
     * @var array the columns being selected. For example, `['id', 'name']`.
     * This is used to construct the SELECT clause in a SQL statement. If not set, it means selecting all columns.
     * @see select()
     */
    public $select;
    /**
     * @var string the table to be selected from. For example, `'user'`.
     * This is used to construct the FROM clause in a SQL statement.
     * @see from()
     */
    public $from;
    /**
     * @var string|array|ExpressionInterface CLIENT-SIDE query condition. This refers to the WHERE clause in a SQL statement.
     * For example, `['age' => 31, 'team' => 1]`.
     * @see where() for valid syntax on specifying this value.
     */
    public $where;
    /**
     * @var string|array|ExpressionInterface DB-SIDE query condition. This refers to the WHERE clause in a SQL statement.
     * For example, `['age' => 31, 'team' => 1]`.
     * @see where() for valid syntax on specifying this value.
     */
    public $whereDb;
    /**
     * @var int maximum number of records to be returned. May be an instance of [[ExpressionInterface]].
     * If not set or less than 0, it means no limit.
     */
    public $limit;
    /**
     * @var int zero-based offset from where the records are to be returned.
     * May be an instance of [[ExpressionInterface]]. If not set or less than 0, it means starting from the beginning.
     */
    public $offset;
    /**
     * @var array how to sort the query results. This is used to construct the ORDER BY clause in a SQL statement.
     * The array keys are the columns to be sorted by, and the array values are the corresponding sort directions which
     * can be either [SORT_ASC](https://secure.php.net/manual/en/array.constants.php#constant.sort-asc)
     * or [SORT_DESC](https://secure.php.net/manual/en/array.constants.php#constant.sort-desc).
     */
    public $orderBy;
    /**
     * @var string|callable the name of the column by which the query results should be indexed by.
     * This can also be a callable (e.g. anonymous function) that returns the index value based on the given
     * row data. For more details, see [[indexBy()]]. This property is only used by [[QueryInterface::all()|all()]].
     */
    public $indexBy;
    /**
     * @var bool whether to emulate the actual query execution, returning empty or false results.
     * @see emulateExecution()
     * @since 2.0.11
     */
    public $emulateExecution = false;

    public $defaultSort = false;

    /**
     * Prepares for building SQL.
     * This method is called by [[QueryBuilder]] when it starts to build SQL from a query object.
     * You may override this method to do some final preparation work when converting a query into a SQL statement.
     * @param QueryBuilder $builder
     * @return $this a prepared query instance which will be used by [[QueryBuilder]] to build the SQL
     */
    public function prepare($builder) {
        return $this;
    }

    /**
     * Creates a DB command that can be used to execute this query.
     * @param Connection $adb the database connection used to generate the SQL statement.
     * If this parameter is not given, the `db` application component will be used.
     * @return Command the created DB command instance.
     */
    public function createCommand($adb = null) {
        if ($adb === null) {
            /** @var Connection */
            $adb = Yii::$app->azureDb;
        }

        $this->defaultSort = false;
        if ($this->orderBy != null) {
            if ($this->orderBy == ['PartitionKey' => SORT_ASC, 'RowKey' => SORT_ASC] || $this->orderBy == ['PartitionKey' => SORT_ASC]) {
                $this->defaultSort = true;
            }
        } else {
            $this->defaultSort = true;
        }

        $commandData = $adb->getQueryBuilder()->build($this);

        $command = $adb->createCommand(null, $commandData);

        return $command;
    }

    /**
     * Creates a new Query object and copies its property values from an existing one.
     * The properties being copies are the ones to be used by query builders.
     * @param Query $from the source query object
     * @return Query the new Query object
     */
    public static function create($from) {
        return new self([
            'whereDb' => $from->whereDb,
            'where' => $from->where,
            'limit' => $from->limit,
            'offset' => $from->offset,
            'orderBy' => $from->orderBy,
            'indexBy' => $from->indexBy,
            'select' => $from->select,
            'from' => $from->from,
        ]);
    }

    /**
     * Executes the query and returns all results as an array.
     * @param Connection $db the database connection used to execute the query.
     * If this parameter is not given, the `db` application component will be used.
     * @return array the query results. If the query results in nothing, an empty array will be returned.
     */
    public function all($db = null) {
        if ($this->emulateExecution) {
            return [];
        }
        return $this->populate($this->createCommand($db)->queryAll());
    }

    /**
     * Executes the query and returns all results as an array.
     * @param Connection $db the database connection used to execute the query.
     * If this parameter is not given, the `db` application component will be used.
     * @return array the query results. If the query results in nothing, an empty array will be returned.
     */
    public function column($db = null) {
        if ($this->emulateExecution) {
            return [];
        }
        $rows = $this->createCommand($db)->queryAll();
        if ($this->select != null) {
            if (ArrayHelper::isAssociative($this->select)) {
                $column = $this->select[array_keys($this->select)[0]];
            } else {
                $column = $this->select[0];
            }
        } else {
            $column = 0;
        }
        return array_column($this->populate($rows), $column);
    }

    /**
     * Converts the raw query results into the format as specified by this query.
     * This method is internally used to convert the data fetched from database
     * into the format as required by this query.
     * @param array $rows the raw query result from database
     * @return array the converted query result
     */
    public function populate($rows) {
        // we need to run the client side filtering
        // Yii::debug('Filtering '.count($rows).' rows with '.json_encode($this->where), __METHOD__);
        $rows = $this->applyClientFilterCondition($rows, $this->where);

        // sort here, since the db doesn't offer it
        if ($this->orderBy != null) {
            ArrayHelper::multisort($rows, array_keys($this->orderBy), array_values($this->orderBy));
        }

        // only run client-side limit/offset if there is client-side filtering or sorting being done
        if (!$this->defaultSort) {
            // Yii::debug('Offsetting '.count($rows).' by '.$this->offset.', and limiting to '.$this->limit.' on client', __METHOD__);
            //limit and offset actually need to be done here, since it won't be reliably sorted otherwise
            if ($this->offset > 0) {
                $offset = $this->offset;
            } else {
                $offset = 0;
            }
            if ($this->limit > 0) {
                $limit = $this->limit;
            } else {
                $limit = null;
            }
            $rows = array_slice($rows, $offset, $limit);
        }

        // Yii::debug('Ended up with '.count($rows), __METHOD__);

        if ($this->indexBy === null) {
            return $rows;
        }

        $results = [];
        foreach ($rows as $result) {
            $results[ArrayHelper::getValue($result, $this->indexBy)] = $result;
        }

        return $results;
    }

    /**
     * Executes the query and returns a single row of result.
     * @param Connection $db the database connection used to execute the query.
     * If this parameter is not given, the `db` application component will be used.
     * @return array|bool the first row (in terms of an array) of the query result. False is returned if the query
     * results in nothing.
     */
    public function one($db = null) {
        if ($this->emulateExecution) {
            return false;
        }

        $result = $this->populate($this->createCommand($db)->queryAll());
        if ($result != null) {
            return reset($result);
        } else {
            return null;
        }
    }

    /**
     * Returns the number of records.
     * @param string $q the COUNT expression. Defaults to '*'.
     * @param Connection $db the database connection used to execute the query.
     * If this parameter is not given, the `db` application component will be used.
     * @return int number of records.
     */
    public function count($q = '*', $db = null) {
        if ($this->emulateExecution) {
            return [];
        }

        // $tempSelect = $this->select;
        // $this->select('pk');
        $command = $this->createCommand($db);
        if ($command->commandData['clientWhere'] != null || $command->commandData['sort'] != null) {
            return count($this->populate($command->queryAll()));
        } else {
            $command->commandData['selectFields'] = ['PartitionKey', 'RowKey', 'Timestamp'];
            return count($command->queryAll());
        }
    }

    /**
     * Returns a value indicating whether the query result contains any row of data.
     * @param Connection $db the database connection used to execute the query.
     * If this parameter is not given, the `db` application component will be used.
     * @return bool whether the query result contains any row of data.
     */
    public function exists($db = null) {
        if ($this->emulateExecution) {
            return [];
        }

        $rows = $this->createCommand($db)->queryAll();
        $result = $this->populate($rows);
        return ($result != null);
    }

    /**
     * Sets the SELECT part of the query.
     * @param string|array|ExpressionInterface $columns the columns to be selected.
     * Columns can be specified in either a string (e.g. "id, name") or an array (e.g. ['id', 'name']).
     * Columns can be prefixed with table names (e.g. "user.id") and/or contain column aliases (e.g. "user.id AS user_id").
     * The method will automatically quote the column names unless a column contains some parenthesis
     * (which means the column contains a DB expression). A DB expression may also be passed in form of
     * an [[ExpressionInterface]] object.
     *
     * Note that if you are selecting an expression like `CONCAT(first_name, ' ', last_name)`, you should
     * use an array to specify the columns. Otherwise, the expression may be incorrectly split into several parts.
     *
     * When the columns are specified as an array, you may also use array keys as the column aliases (if a column
     * does not need alias, do not use a string key).
     *
     * Starting from version 2.0.1, you may also select sub-queries as columns by specifying each such column
     * as a `Query` instance representing the sub-query.
     *
     * @param string $option additional option that should be appended to the 'SELECT' keyword. For example,
     * in MySQL, the option 'SQL_CALC_FOUND_ROWS' can be used.
     * @return $this the query object itself
     */
    public function select($columns, $option = null) {
        $this->select = $this->normalizeSelect($columns);
        return $this;
    }

    /**
     * Sets the FROM part of the query.
     * @param string|array $table the table(s) to be selected from. Only the first one will be used
     *
     * @return $this the query object itself
     */
    public function from($table) {
        if (is_string($table)) {
            $tables = preg_split('/\s*,\s*/', trim($table), -1, PREG_SPLIT_NO_EMPTY);
        }
        $this->from = $tables[0];
        return $this;
    }

    /**
     * Add more columns to the SELECT part of the query.
     *
     * Note, that if [[select]] has not been specified before, you should include `*` explicitly
     * if you want to select all remaining columns too:
     *
     * ```php
     * $query->addSelect(["*", "CONCAT(first_name, ' ', last_name) AS full_name"])->one();
     * ```
     *
     * @param string|array|ExpressionInterface $columns the columns to add to the select. See [[select()]] for more
     * details about the format of this parameter.
     * @return $this the query object itself
     * @see select()
     */
    public function addSelect($columns) {
        if ($this->select === null) {
            return $this->select($columns);
        }
        if (!is_array($this->select)) {
            $this->select = $this->normalizeSelect($this->select);
        }
        $this->select = array_merge($this->select, $this->normalizeSelect($columns));

        return $this;
    }

    /**
     * Normalizes the SELECT columns passed to [[select()]] or [[addSelect()]].
     *
     * @param string|array|ExpressionInterface $columns
     * @return array
     * @since 2.0.21
     */
    protected function normalizeSelect($columns) {
        if ($columns instanceof ExpressionInterface) {
            $columns = [$columns];
        } elseif (!is_array($columns)) {
            $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY);
        }
        $select = [];
        foreach ($columns as $columnAlias => $columnDefinition) {
            if (is_string($columnAlias)) {
                // Already in the normalized format, good for them
                $select[$columnAlias] = $columnDefinition;
                continue;
            }
            if (is_string($columnDefinition)) {
                if (
                    preg_match('/^(.*?)(?i:\s+as\s+|\s+)([\w\-_\.]+)$/', $columnDefinition, $matches) &&
                    !preg_match('/^\d+$/', $matches[2]) &&
                    strpos($matches[2], '.') === false
                ) {
                    // Using "columnName as alias" or "columnName alias" syntax
                    $select[$matches[2]] = $matches[1];
                    continue;
                }
                if (strpos($columnDefinition, '(') === false) {
                    // Normal column name, just alias it to itself to ensure it's not selected twice
                    $select[$columnDefinition] = $columnDefinition;
                    continue;
                }
            }
            // Either a string calling a function, DB expression, or sub-query
            $select[] = $columnDefinition;
        }
        return $select;
    }

    /**
     * Sets the [[indexBy]] property.
     * @param string|callable $column the name of the column by which the query results should be indexed by.
     * This can also be a callable (e.g. anonymous function) that returns the index value based on the given
     * row data. The signature of the callable should be:
     *
     * ```php
     * function ($row)
     * {
     *     // return the index value corresponding to $row
     * }
     * ```
     *
     * @return $this the query object itself
     */
    public function indexBy($column) {
        $this->indexBy = $column;
        return $this;
    }

    /**
     * Sets the WHERE part of the query.
     *
     * See [[QueryInterface::where()]] for detailed documentation.
     *
     * @param string|array $condition the conditions that should be put in the WHERE part.
     * @return $this the query object itself
     * @see andWhere()
     * @see orWhere()
     */
    public function where($condition, $params = []) {
        $this->where = $condition;
        return $this;
    }

    /**
     * Adds an additional WHERE condition to the existing one.
     * The new condition and the existing one will be joined using the 'AND' operator.
     * @param string|array $condition the new WHERE condition. Please refer to [[where()]]
     * on how to specify this parameter.
     * @return $this the query object itself
     * @see where()
     * @see orWhere()
     */
    public function andWhere($condition, $params = []) {
        if ($this->where === null) {
            $this->where = $condition;
        } else {
            $this->where = ['and', $this->where, $condition];
        }

        return $this;
    }

    /**
     * Adds an additional WHERE condition to the existing one.
     * The new condition and the existing one will be joined using the 'OR' operator.
     * @param string|array $condition the new WHERE condition. Please refer to [[where()]]
     * on how to specify this parameter.
     * @return $this the query object itself
     * @see where()
     * @see andWhere()
     */
    public function orWhere($condition, $params = []) {
        if ($this->where === null) {
            $this->where = $condition;
        } else {
            $this->where = ['or', $this->where, $condition];
        }

        return $this;
    }

    /**
     * Sets the WHERE part of the query.
     *
     * See [[QueryInterface::where()]] for detailed documentation.
     *
     * @param string|array $condition the conditions that should be put in the WHERE part.
     * @return $this the query object itself
     * @see andWhere()
     * @see orWhere()
     */
    public function whereDb($condition) {
        $this->whereDb = $condition;
        return $this;
    }

    /**
     * Adds an additional WHERE condition to the existing one.
     * The new condition and the existing one will be joined using the 'AND' operator.
     * @param string|array $condition the new WHERE condition. Please refer to [[where()]]
     * on how to specify this parameter.
     * @return $this the query object itself
     * @see where()
     * @see orWhere()
     */
    public function andWhereDb($condition) {
        if ($this->whereDb === null) {
            $this->whereDb = $condition;
        } else {
            $this->whereDb = ['and', $this->whereDb, $condition];
        }

        return $this;
    }

    /**
     * Adds an additional WHERE condition to the existing one.
     * The new condition and the existing one will be joined using the 'OR' operator.
     * @param string|array $condition the new WHERE condition. Please refer to [[where()]]
     * on how to specify this parameter.
     * @return $this the query object itself
     * @see where()
     * @see andWhere()
     */
    public function orWhereDb($condition) {
        if ($this->whereDb === null) {
            $this->whereDb = $condition;
        } else {
            $this->whereDb = ['or', $this->whereDb, $condition];
        }

        return $this;
    }

    /**
     * Sets the WHERE part of the query but ignores [[isEmpty()|empty operands]].
     *
     * This method is similar to [[where()]]. The main difference is that this method will
     * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited
     * for building query conditions based on filter values entered by users.
     *
     * The following code shows the difference between this method and [[where()]]:
     *
     * ```php
     * // WHERE `age`=:age
     * $query->filterWhere(['name' => null, 'age' => 20]);
     * // WHERE `age`=:age
     * $query->where(['age' => 20]);
     * // WHERE `name` IS NULL AND `age`=:age
     * $query->where(['name' => null, 'age' => 20]);
     * ```
     *
     * Note that unlike [[where()]], you cannot pass binding parameters to this method.
     *
     * @param array $condition the conditions that should be put in the WHERE part.
     * See [[where()]] on how to specify this parameter.
     * @return $this the query object itself
     * @see where()
     * @see andFilterWhere()
     * @see orFilterWhere()
     */
    public function filterWhere(array $condition) {
        $condition = $this->filterCondition($condition);
        if ($condition !== []) {
            $this->where($condition);
        }

        return $this;
    }

    /**
     * Adds an additional WHERE condition to the existing one but ignores [[isEmpty()|empty operands]].
     * The new condition and the existing one will be joined using the 'AND' operator.
     *
     * This method is similar to [[andWhere()]]. The main difference is that this method will
     * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited
     * for building query conditions based on filter values entered by users.
     *
     * @param array $condition the new WHERE condition. Please refer to [[where()]]
     * on how to specify this parameter.
     * @return $this the query object itself
     * @see filterWhere()
     * @see orFilterWhere()
     */
    public function andFilterWhere(array $condition) {
        $condition = $this->filterCondition($condition);
        if ($condition !== []) {
            $this->andWhere($condition);
        }

        return $this;
    }

    /**
     * Adds an additional WHERE condition to the existing one but ignores [[isEmpty()|empty operands]].
     * The new condition and the existing one will be joined using the 'OR' operator.
     *
     * This method is similar to [[orWhere()]]. The main difference is that this method will
     * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited
     * for building query conditions based on filter values entered by users.
     *
     * @param array $condition the new WHERE condition. Please refer to [[where()]]
     * on how to specify this parameter.
     * @return $this the query object itself
     * @see filterWhere()
     * @see andFilterWhere()
     */
    public function orFilterWhere(array $condition) {
        $condition = $this->filterCondition($condition);
        if ($condition !== []) {
            $this->orWhere($condition);
        }

        return $this;
    }

    /**
     * Sets the WHERE part of the query but ignores [[isEmpty()|empty operands]].
     *
     * This method is similar to [[where()]]. The main difference is that this method will
     * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited
     * for building query conditions based on filter values entered by users.
     *
     * The following code shows the difference between this method and [[where()]]:
     *
     * ```php
     * // WHERE `age`=:age
     * $query->filterWhere(['name' => null, 'age' => 20]);
     * // WHERE `age`=:age
     * $query->where(['age' => 20]);
     * // WHERE `name` IS NULL AND `age`=:age
     * $query->where(['name' => null, 'age' => 20]);
     * ```
     *
     * Note that unlike [[where()]], you cannot pass binding parameters to this method.
     *
     * @param array $condition the conditions that should be put in the WHERE part.
     * See [[where()]] on how to specify this parameter.
     * @return $this the query object itself
     * @see where()
     * @see andFilterWhere()
     * @see orFilterWhere()
     */
    public function filterWhereDb(array $condition) {
        $condition = $this->filterDbCondition($condition);
        if ($condition !== []) {
            $this->whereDb($condition);
        }

        return $this;
    }

    /**
     * Adds an additional WHERE condition to the existing one but ignores [[isEmpty()|empty operands]].
     * The new condition and the existing one will be joined using the 'AND' operator.
     *
     * This method is similar to [[andWhere()]]. The main difference is that this method will
     * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited
     * for building query conditions based on filter values entered by users.
     *
     * @param array $condition the new WHERE condition. Please refer to [[where()]]
     * on how to specify this parameter.
     * @return $this the query object itself
     * @see filterWhere()
     * @see orFilterWhere()
     */
    public function andFilterWhereDb(array $condition) {
        $condition = $this->filterDbCondition($condition);
        if ($condition !== []) {
            $this->andWhereDb($condition);
        }

        return $this;
    }

    /**
     * Adds an additional WHERE condition to the existing one but ignores [[isEmpty()|empty operands]].
     * The new condition and the existing one will be joined using the 'OR' operator.
     *
     * This method is similar to [[orWhere()]]. The main difference is that this method will
     * remove [[isEmpty()|empty query operands]]. As a result, this method is best suited
     * for building query conditions based on filter values entered by users.
     *
     * @param array $condition the new WHERE condition. Please refer to [[where()]]
     * on how to specify this parameter.
     * @return $this the query object itself
     * @see filterWhere()
     * @see andFilterWhere()
     */
    public function orFilterWhereDb(array $condition) {
        $condition = $this->filterDbCondition($condition);
        if ($condition !== []) {
            $this->orWhereDb($condition);
        }

        return $this;
    }

    /**
     * Removes [[isEmpty()|empty operands]] from the given query condition.
     *
     * @param array $condition the original condition
     * @return array the condition with [[isEmpty()|empty operands]] removed.
     * @throws NotSupportedException if the condition operator is not supported
     */
    protected function filterDbCondition($condition) {
        if (!is_array($condition)) {
            return $condition;
        }

        if (!isset($condition[0])) {
            // hash format: 'column1' => 'value1', 'column2' => 'value2', ...
            foreach ($condition as $name => $value) {
                if ($this->isEmpty($value)) {
                    unset($condition[$name]);
                }
            }

            return $condition;
        }

        // operator format: operator, operand 1, operand 2, ...

        $operator = array_shift($condition);

        switch (strtoupper($operator)) {
            case 'NOT':
            case 'AND':
            case 'OR':
                foreach ($condition as $i => $operand) {
                    $subCondition = $this->filterCondition($operand);
                    if ($this->isEmpty($subCondition)) {
                        unset($condition[$i]);
                    } else {
                        $condition[$i] = $subCondition;
                    }
                }

                if (empty($condition)) {
                    return [];
                }
                break;
            case 'BETWEEN':
            case 'NOT BETWEEN':
                if (array_key_exists(1, $condition) && array_key_exists(2, $condition)) {
                    if ($this->isEmpty($condition[1]) || $this->isEmpty($condition[2])) {
                        return [];
                    }
                }
                break;
            default:
                if (array_key_exists(1, $condition) && $this->isEmpty($condition[1])) {
                    return [];
                }
        }

        array_unshift($condition, $operator);

        return $condition;
    }

    /**
     * Removes [[isEmpty()|empty operands]] from the given query condition.
     *
     * @param array $condition the original condition
     * @return array the condition with [[isEmpty()|empty operands]] removed.
     * @throws NotSupportedException if the condition operator is not supported
     */
    protected function filterCondition($condition) {
        if (!is_array($condition)) {
            return $condition;
        }

        if (!isset($condition[0])) {
            // hash format: 'column1' => 'value1', 'column2' => 'value2', ...
            foreach ($condition as $name => $value) {
                if ($this->isEmpty($value)) {
                    unset($condition[$name]);
                }
            }

            return $condition;
        }

        // operator format: operator, operand 1, operand 2, ...

        $operator = array_shift($condition);

        switch (strtoupper($operator)) {
            case 'NOT':
            case 'AND':
            case 'OR':
                foreach ($condition as $i => $operand) {
                    $subCondition = $this->filterCondition($operand);
                    if ($this->isEmpty($subCondition)) {
                        unset($condition[$i]);
                    } else {
                        $condition[$i] = $subCondition;
                    }
                }

                if (empty($condition)) {
                    return [];
                }
                break;
            case 'BETWEEN':
            case 'NOT BETWEEN':
                if (array_key_exists(1, $condition) && array_key_exists(2, $condition)) {
                    if ($this->isEmpty($condition[1]) || $this->isEmpty($condition[2])) {
                        return [];
                    }
                }
                break;
            default:
                if (array_key_exists(1, $condition) && $this->isEmpty($condition[1])) {
                    return [];
                }
        }

        array_unshift($condition, $operator);

        return $condition;
    }

    /**
     * Returns a value indicating whether the give value is "empty".
     *
     * The value is considered "empty", if one of the following conditions is satisfied:
     *
     * - it is `null`,
     * - an empty string (`''`),
     * - a string containing only whitespace characters,
     * - or an empty array.
     *
     * @param mixed $value
     * @return bool if the value is empty
     */
    protected function isEmpty($value) {
        return $value === '' || $value === [] || $value === null || is_string($value) && trim($value) === '';
    }

    /**
     * Sets the ORDER BY part of the query.
     * @param string|array|ExpressionInterface $columns the columns (and the directions) to be ordered by.
     * Columns can be specified in either a string (e.g. `"id ASC, name DESC"`) or an array
     * (e.g. `['id' => SORT_ASC, 'name' => SORT_DESC]`).
     *
     * The method will automatically quote the column names unless a column contains some parenthesis
     * (which means the column contains a DB expression).
     *
     * Note that if your order-by is an expression containing commas, you should always use an array
     * to represent the order-by information. Otherwise, the method will not be able to correctly determine
     * the order-by columns.
     *
     * Since version 2.0.7, an [[ExpressionInterface]] object can be passed to specify the ORDER BY part explicitly in plain SQL.
     * @return $this the query object itself
     * @see addOrderBy()
     */
    public function orderBy($columns) {
        $this->orderBy = $this->normalizeOrderBy($columns);
        return $this;
    }

    /**
     * Adds additional ORDER BY columns to the query.
     * @param string|array|ExpressionInterface $columns the columns (and the directions) to be ordered by.
     * Columns can be specified in either a string (e.g. "id ASC, name DESC") or an array
     * (e.g. `['id' => SORT_ASC, 'name' => SORT_DESC]`).
     *
     * The method will automatically quote the column names unless a column contains some parenthesis
     * (which means the column contains a DB expression).
     *
     * Note that if your order-by is an expression containing commas, you should always use an array
     * to represent the order-by information. Otherwise, the method will not be able to correctly determine
     * the order-by columns.
     *
     * Since version 2.0.7, an [[ExpressionInterface]] object can be passed to specify the ORDER BY part explicitly in plain SQL.
     * @return $this the query object itself
     * @see orderBy()
     */
    public function addOrderBy($columns) {
        $columns = $this->normalizeOrderBy($columns);
        if ($this->orderBy === null) {
            $this->orderBy = $columns;
        } else {
            $this->orderBy = array_merge($this->orderBy, $columns);
        }

        return $this;
    }

    /**
     * Normalizes format of ORDER BY data.
     *
     * @param array|string|ExpressionInterface $columns the columns value to normalize. See [[orderBy]] and [[addOrderBy]].
     * @return array
     */
    protected function normalizeOrderBy($columns) {
        if ($columns instanceof ExpressionInterface) {
            return [$columns];
        } elseif (is_array($columns)) {
            return $columns;
        }

        $columns = preg_split('/\s*,\s*/', trim($columns), -1, PREG_SPLIT_NO_EMPTY);
        $result = [];
        foreach ($columns as $column) {
            if (preg_match('/^(.*?)\s+(asc|desc)$/i', $column, $matches)) {
                $result[$matches[1]] = strcasecmp($matches[2], 'desc') ? SORT_ASC : SORT_DESC;
            } else {
                $result[$column] = SORT_ASC;
            }
        }

        return $result;
    }

    /**
     * Sets the LIMIT part of the query.
     * @param int|ExpressionInterface|null $limit the limit. Use null or negative value to disable limit.
     * @return $this the query object itself
     */
    public function limit($limit) {
        $this->limit = $limit;
        return $this;
    }

    /**
     * Sets the OFFSET part of the query.
     * @param int|ExpressionInterface|null $offset the offset. Use null or negative value to disable offset.
     * @return $this the query object itself
     */
    public function offset($offset) {
        $this->offset = $offset;
        return $this;
    }

    /**
     * Sets whether to emulate query execution, preventing any interaction with data storage.
     * After this mode is enabled, methods, returning query results like [[QueryInterface::one()]],
     * [[QueryInterface::all()]], [[QueryInterface::exists()]] and so on, will return empty or false values.
     * You should use this method in case your program logic indicates query should not return any results, like
     * in case you set false where condition like `0=1`.
     * @param bool $value whether to prevent query execution.
     * @return $this the query object itself.
     * @since 2.0.11
     */
    public function emulateExecution($value = true) {
        $this->emulateExecution = $value;
        return $this;
    }

    /**
     * Applies filter conditions.
     *
     * @param array $data data to be filtered
     * @param array $condition filter condition
     *
     * @return array filtered data
     *
     * @throws InvalidParamException
     */
    public function applyClientFilterCondition(array $data, $condition) {
        if (empty($condition)) {
            return $data;
        }

        if (!is_array($condition)) {
            throw new InvalidParamException('Condition must be an array');
        }

        if (isset($condition[0])) { // operator format: operator, operand 1, operand 2, ...
            $operator = strtoupper($condition[0]);
            if (isset($this->conditionFilters[$operator])) {
                $method = $this->conditionFilters[$operator];
            } else {
                $method = 'applyClientFilterSimpleCondition';
            }

            array_shift($condition);

            return $this->$method($data, $operator, $condition);
        } else { // hash format: 'column1' => 'value1', 'column2' => 'value2', ...
            return $this->applyClientFilterHashCondition($data, $condition);
        }
    }

    /**
     * Applies a condition based on column-value pairs.
     *
     * @param array $data data to be filtered
     * @param array $condition the condition specification
     *
     * @return array filtered data
     */
    public function applyClientFilterHashCondition(array $data, $condition) {
        foreach ($condition as $column => $value) {
            if (is_array($value)) {
                // IN condition
                $data = $this->applyClientFilterInCondition($data, 'IN', [$column, $value]);
            } else {
                $data = array_filter($data, function ($row) use ($column, $value) {
                    if ($value instanceof \Closure) {
                        return call_user_func($value, $row[$column]);
                    }

                    return $row[$column] == $value;
                });
            }
        }

        return $data;
    }

    /**
     * Applies 2 or more conditions using 'AND' logic.
     *
     * @param array $data data to be filtered
     * @param string $operator operator
     * @param array $operands conditions to be united
     *
     * @return array filtered data
     */
    protected function applyClientFilterAndCondition(array $data, $operator, $operands) {
        foreach ($operands as $operand) {
            if (is_array($operand)) {
                $data = $this->applyClientFilterCondition($data, $operand);
            }
        }

        return $data;
    }

    /**
     * Applies 2 or more conditions using 'OR' logic.
     *
     * @param array $data data to be filtered
     * @param string $operator operator
     * @param array $operands conditions to be united
     *
     * @return array filtered data
     */
    protected function applyClientFilterOrCondition(array $data, $operator, $operands) {
        $parts = [];
        foreach ($operands as $operand) {
            if (is_array($operand)) {
                $parts[] = $this->applyClientFilterCondition($data, $operand);
            }
        }

        if (empty($parts)) {
            return $data;
        }

        $data = [];
        foreach ($parts as $part) {
            foreach ($part as $row) {
                $data[] = $row;
            }
        }

        return $data;
    }

    /**
     * Inverts a filter condition.
     *
     * @param array $data data to be filtered
     * @param string $operator operator
     * @param array $operands operands to be inverted
     *
     * @return array filtered data
     *
     * @throws InvalidParamException if wrong number of operands have been given
     */
    protected function applyClientFilterNotCondition(array $data, $operator, $operands) {
        if (count($operands) != 1) {
            throw new InvalidParamException("Operator '$operator' requires exactly one operand.");
        }

        $operand = reset($operands);
        $filteredData = $this->applyClientFilterCondition($data, $operand);
        if (empty($filteredData)) {
            return $data;
        }

        // $pkName = $this->_query->primaryKeyName;
        foreach ($data as $key => $row) {
            foreach ($filteredData as $filteredRowKey => $filteredRow) {
                if ($row[$key] === $filteredRow[$key]) {
                    unset($data[$key]);
                    unset($filteredData[$filteredRowKey]);
                    break;
                }
            }
        }

        return $data;
    }

    /**
     * Applies `BETWEEN` condition.
     *
     * @param array $data data to be filtered
     * @param string $operator operator
     * @param array $operands the first operand is the column name. The second and third operands
     * describe the interval that column value should be in
     *
     * @return array filtered data
     *
     * @throws InvalidParamException if wrong number of operands have been given
     */
    protected function applyClientFilterBetweenCondition(array $data, $operator, $operands) {
        if (!isset($operands[0], $operands[1], $operands[2])) {
            throw new InvalidParamException("Operator '$operator' requires three operands.");
        }

        list($column, $value1, $value2) = $operands;

        if (strncmp('NOT', $operator, 3) === 0) {
            return array_filter($data, function ($row) use ($column, $value1, $value2) {
                return $row[$column] < $value1 || $row[$column] > $value2;
            });
        }

        return array_filter($data, function ($row) use ($column, $value1, $value2) {
            return $row[$column] >= $value1 && $row[$column] <= $value2;
        });
    }

    /**
     * Applies 'IN' condition.
     *
     * @param array $data data to be filtered
     * @param string $operator operator
     * @param array $operands the first operand is the column name.
     * The second operand is an array of values that column value should be among
     *
     * @return array filtered data
     *
     * @throws InvalidParamException if wrong number of operands have been given
     */
    protected function applyClientFilterInCondition(array $data, $operator, $operands) {
        if (!isset($operands[0], $operands[1])) {
            throw new InvalidParamException("Operator '$operator' requires two operands.");
        }

        list($column, $values) = $operands;

        if ($values === [] || $column === []) {
            return $operator === 'IN' ? [] : $data;
        }

        $values = (array) $values;

        if (count((array) $column) > 1) {
            throw new InvalidParamException("Operator '$operator' allows only a single column.");
        }

        if (is_array($column)) {
            $column = reset($column);
        }

        foreach ($values as $i => $value) {
            if (is_array($value)) {
                $values[$i] = isset($value[$column]) ? $value[$column] : null;
            }
        }

        if (strncmp('NOT', $operator, 3) === 0) {
            return array_filter($data, function ($row) use ($column, $values) {
                return !in_array($row[$column], $values);
            });
        }

        return array_filter($data, function ($row) use ($column, $values) {
            return in_array($row[$column], $values);
        });
    }

    /**
     * Applies 'LIKE' condition.
     *
     * @param array $data data to be filtered
     * @param string $operator operator
     * @param array $operands the first operand is the column name. The second operand is a single value
     * or an array of values that column value should be compared with
     *
     * @return array filtered data
     *
     * @throws InvalidParamException if wrong number of operands have been given
     */
    protected function applyClientFilterLikeCondition(array $data, $operator, $operands) {
        if (!isset($operands[0], $operands[1])) {
            throw new InvalidParamException("Operator '$operator' requires two operands.");
        }

        list($column, $values) = $operands;

        if (!is_array($values)) {
            $values = [$values];
        }

        $not = (stripos($operator, 'NOT ') !== false);
        $or = (stripos($operator, 'OR ') !== false);

        if ($not) {
            if (empty($values)) {
                return $data;
            }

            if ($or) {
                return array_filter($data, function ($row) use ($column, $values) {
                    foreach ($values as $value) {
                        if (stripos($this->serializeValue(self::propertyType($row[$column]), $row[$column]), strval($value)) === false) {
                            return true;
                        }
                    }

                    return false;
                });
            }

            return array_filter($data, function ($row) use ($column, $values) {
                foreach ($values as $value) {
                    if (stripos($this->serializeValue(self::propertyType($row[$column]), $row[$column]), strval($value)) !== false) {
                        return false;
                    }
                }

                return true;
            });
        }

        if (empty($values)) {
            return [];
        }

        if ($or) {
            return array_filter($data, function ($row) use ($column, $values) {
                foreach ($values as $value) {
                    if (stripos($this->serializeValue(self::propertyType($row[$column]), $row[$column]), strval($value)) !== false) {
                        return true;
                    }
                }

                return false;
            });
        }

        return array_filter($data, function ($row) use ($column, $values) {
            foreach ($values as $value) {
                if (stripos($this->serializeValue(self::propertyType($row[$column]), $row[$column]), strval($value)) === false) {
                    return false;
                }
            }

            return true;
        });
    }

    /**
     * Applies 'CALLBACK' condition.
     *
     * @param array $data data to be filtered
     * @param string $operator operator
     * @param array $operands the only one operand is the PHP callback, which should be compatible with
     * `array_filter()` PHP function, e.g.:
     *
     * ```php
     * function ($row) {
     *     //return bool whether row matches condition or not
     * }
     * ```
     *
     * @return array filtered data
     *
     * @throws InvalidParamException if wrong number of operands have been given
     *
     * @since 1.0.3
     */
    public function applyClientFilterCallbackCondition(array $data, $operator, $operands) {
        if (count($operands) != 1) {
            throw new InvalidParamException("Operator '$operator' requires exactly one operand.");
        }

        $callback = reset($operands);

        return array_filter($data, $callback);
    }

    /**
     * Applies comparison condition, e.g. `column operator value`.
     *
     * @param array $data data to be filtered
     * @param string $operator operator
     * @param array $operands
     *
     * @return array filtered data
     *
     * @throws InvalidParamException if wrong number of operands have been given or operator is not supported
     *
     * @since 1.0.4
     */
    public function applyClientFilterSimpleCondition(array $data, $operator, $operands) {
        if (count($operands) !== 2) {
            throw new InvalidParamException("Operator '$operator' requires two operands.");
        }
        list($column, $value) = $operands;

        return array_filter($data, function ($row) use ($operator, $column, $value) {
            // if its a datetime, we need to convert the comparator to datetime
            if (self::propertyType($row[$column]) == EdmType::DATETIME && !($value instanceof DateTime)) {
                $value = new DateTime($value);
            }

            switch ($operator) {
                case '=':
                case '==':
                    return $row[$column] == $value;
                case '===':
                    return $row[$column] === $value;
                case '!=':
                case '<>':
                    return $row[$column] != $value;
                case '!==':
                    return $row[$column] !== $value;
                case '>':
                    return $row[$column] > $value;
                case '<':
                    return $row[$column] < $value;
                case '>=':
                    return $row[$column] >= $value;
                case '<=':
                    return $row[$column] <= $value;
                default:
                    throw new InvalidParamException("Operator '$operator' is not supported.");
            }
        });
    }

    public function serializeValue($type, $value) {
        switch ($type) {
            case null:
                return $value;

            case EdmType::INT32:
                return intval($value);

            case EdmType::INT64:
            case EdmType::GUID:
            case EdmType::STRING:
                return strval($value);

            case EdmType::DOUBLE:
                return strval($value);

            case EdmType::BINARY:
                return base64_encode($value);

            case EdmType::DATETIME:
                return $value->format('Y-m-d H:i:s');

            case EdmType::BOOLEAN:
                return (is_null($value) ? '' : ($value == true ? true : false));

            default:
                throw new \InvalidArgumentException();
        }
    }

    public static function propertyType($value) {
        $prop = EdmType::propertyType($value);

        // PHP considers #####E# strings to be floating point, and hence numeric
        // which leads to edge cases where 0000000E3 for example is treated as a number
        // therefore, if the type is detected to be double and there is an E, just count it as a string rather
        if ($prop == EdmType::DOUBLE) {
            if (is_string($value) && str_contains(strtolower($value), 'e')) {
                $prop = EdmType::STRING;
            }
        }
        return $prop;
    }
}
