<?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 Exception;
use MicrosoftAzure\Storage\Common\Exceptions\ServiceException;
use MicrosoftAzure\Storage\Table\Models\Entity;
use MicrosoftAzure\Storage\Table\Models\Filters\Filter;
use MicrosoftAzure\Storage\Table\Models\Property;
use MicrosoftAzure\Storage\Table\Models\QueryEntitiesOptions;
use Yii;
use yii\db\Command as DbCommand;
use yii\helpers\ArrayHelper;

/**
 * Command represents a SQL statement to be executed against a database.
 *
 * A command object is usually created by calling [[Connection::createCommand()]].
 * The SQL statement it represents can be set via the [[sql]] property.
 *
 * To execute a non-query SQL (such as INSERT, DELETE, UPDATE), call [[execute()]].
 * To execute a SQL statement that returns a result data set (such as SELECT),
 * use [[queryAll()]], [[queryOne()]], [[queryColumn()]], [[queryScalar()]], or [[query()]].
 *
 * For example,
 *
 * ```php
 * $users = $connection->createCommand('SELECT * FROM user')->queryAll();
 * ```
 *
 * Command supports SQL statement preparation and parameter binding.
 * Call [[bindValue()]] to bind a value to a SQL parameter;
 * Call [[bindParam()]] to bind a PHP variable to a SQL parameter.
 * When binding a parameter, the SQL statement is automatically prepared.
 * You may also call [[prepare()]] explicitly to prepare a SQL statement.
 *
 * Command also supports building SQL statements by providing methods such as [[insert()]],
 * [[update()]], etc. For example, the following code will create and execute an INSERT SQL statement:
 *
 * ```php
 * $connection->createCommand()->insert('user', [
 *     'name' => 'Sam',
 *     'age' => 30,
 * ])->execute();
 * ```
 *
 * To build SELECT SQL statements, please use [[Query]] instead.
 *
 * For more details and usage information on Command, see the [guide article on Database Access Objects](guide:db-dao).
 *
 * @property string $rawSql The raw SQL with parameter values inserted into the corresponding placeholders in
 * [[sql]].
 * @property string $sql The SQL statement to be executed.
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @since 2.0
 */
class Command extends DbCommand {
    const MODE_SELECT = 'select';
    const MODE_INSERT_ENTITY = 'insert';
    const MODE_CREATE_TABLE = 'create';
    const MODE_UPDATE_ENTITY = 'update';
    const MODE_DELETE_ENTITY = 'delete';
    const MODE_DELETE_TABLE = 'drop';

    const ENTITY_PARTITION_KEY = 'PartitionKey';
    const ENTITY_ROW_KEY = 'RowKey';

    public static $allowedMode = array(
        self::MODE_SELECT,
        self::MODE_INSERT_ENTITY,
        self::MODE_UPDATE_ENTITY,
        self::MODE_DELETE_ENTITY
    );

    public QueryEntitiesOptions $queryOptions;
    public string $table;

    /**
     * @var Connection the DB connection that this command is associated with
     */
    public $adb;
    /**
     * @var array the parameters (name => value) that are bound to the current statement.
     * This property is maintained by methods. It is mainly provided for logging purpose
     * and is used to generate the output. Do not modify it directly.
     * 
     * [
     *      'mode' => 'select'|'insert'|'update'|'delete',
     *      'table' => name of table to work on,
     *      'PartitionKey' => partition key of record to be changed or deleted,
     *      'RowKey' => row key of record to be changed or deleted,
     *      'clientWhere' => array of conditions to apply once the data has been pulled,
     *      'filterString' => prepared filter string,
     *      'selectFields' => array of fields to include,
     *      'limit' => max number of entities to return,
     *      'offset' => number of entities to skip,
     *      'serverOffsettingLimiting' => whether to use the server to limit and offset records
     *      'entity' => [
     *          name => [
     *              'value' => value,
     *              'type' => edm type
     *      ],
     * ]
     */
    public $commandData = [];

    /**
     * @var callable a callable (e.g. anonymous function) that is called when [[\yii\db\Exception]] is thrown
     * when executing the command.
     */
    private $_retryHandler;

    public function createTable($table, $columns, $options = null) {
        $this->commandData = $this->adb->getQueryBuilder()->createTable($table, $columns, $options);

        return $this;
    }

    public function dropTable($table) {
        $this->commandData = $this->adb->getQueryBuilder()->dropTable($table);

        return $this;
    }

    /**
     * Executes the SQL statement and returns query result.
     * This method is for executing a SQL query that returns result set, such as `SELECT`.
     * @return DataReader the reader object for fetching the query result
     * @throws Exception execution failed
     */
    public function query() {
        return $this->queryInternal();
    }

    /**
     * Executes the SQL statement and returns ALL rows at once.
     * @return array all rows of the query result. Each array element is an array representing a row of data.
     * An empty array is returned if the query results in nothing.
     * @throws Exception execution failed
     */
    public function queryAll($fetchMode = null) {
        return $this->queryInternal('fetchAll', $fetchMode);
    }

    /**
     * Executes the SQL statement and returns the first row of the result.
     * This method is best used when only the first row of result is needed for a query.
     * @return array|false the first row (in terms of an array) of the query result. False is returned if the query
     * results in nothing.
     * @throws Exception execution failed
     */
    public function queryOne($fetchMode = null) {
        // $tempTop = ArrayHelper::getValue($this->commandData, 'limit', null);
        // $this->commandData['limit'] = 1;
        $result = $this->queryInternal('fetch', $fetchMode);

        // reset the limiter to what it was before this
        // if ($tempTop != null) {
        //     $this->commandData['limit'] = $tempTop;
        // } else {
        //     unset($this->commandData['limit']);
        // }

        if ($result != null) {
            return $result[0];
        } else {
            return null;
        }
    }

    /**
     * Executes the SQL statement and returns the value of the first column in the first row of data.
     * This method is best used when only a single value is needed for a query.
     * @return string|null|false the value of the first column in the first row of the query result.
     * False is returned if there is no value.
     * @throws Exception execution failed
     */
    public function queryScalar() {
        $tempTop = ArrayHelper::getValue($this->commandData, 'limit', null);
        $this->commandData['limit'] = 1;
        $result = $this->queryInternal('fetchColumn');

        // reset the limiter to what it was before this
        if ($tempTop != null) {
            $this->commandData['limit'] = $tempTop;
        } else {
            unset($this->commandData['limit']);
        }

        if (is_resource($result) && get_resource_type($result) === 'stream') {
            return stream_get_contents($result);
        }

        if ($result != null) {
            return $result[0][0];
        } else {
            return null;
        }
    }

    /**
     * Executes the SQL statement and returns the first column of the result.
     * This method is best used when only the first column of result (i.e. the first element in each row)
     * is needed for a query.
     * @return array the first column of the query result. Empty array is returned if the query results in nothing.
     * @throws Exception execution failed
     */
    public function queryColumn() {
        $result = $this->queryInternal('fetchAll');
        if ($result != null) {
            return array_column($result, 0);
        } else {
            return null;
        }
    }

    /**
     * Creates an INSERT command.
     *
     * For example,
     *
     * ```php
     * $connection->createCommand()->insert('user', [
     *     'name' => 'Sam',
     *     'age' => 30,
     * ])->execute();
     * ```
     *
     * The method will properly escape the column names, and bind the values to be inserted.
     *
     * Note that the created command is not executed until [[execute()]] is called.
     *
     * @param string $table the table that new rows will be inserted into.
     * @param array|\yii\db\Query $columns the column data (name => value) to be inserted into the table or instance
     * of [[yii\db\Query|Query]] to perform INSERT INTO ... SELECT SQL statement.
     * Passing of [[yii\db\Query|Query]] is available since version 2.0.11.
     * @return $this the command object itself
     */
    public function insert($table, $columns) {
        $params = [];
        $this->commandData = $this->adb->getQueryBuilder()->insert($table, $columns);

        return $this;
    }

    /**
     * Creates a command to insert rows into a database table if
     * they do not already exist (matching unique constraints),
     * or update them if they do.
     *
     * For example,
     *
     * ```php
     * $sql = $queryBuilder->upsert('pages', [
     *     'name' => 'Front page',
     *     'url' => 'http://example.com/', // url is unique
     *     'visits' => 0,
     * ], [
     *     'visits' => new \yii\db\Expression('visits + 1'),
     * ], $params);
     * ```
     *
     * The method will properly escape the table and column names.
     *
     * @param string $table the table that new rows will be inserted into/updated in.
     * @param array|Query $insertColumns the column data (name => value) to be inserted into the table or instance
     * of [[Query]] to perform `INSERT INTO ... SELECT` SQL statement.
     * @param array|bool $updateColumns the column data (name => value) to be updated if they already exist.
     * If `true` is passed, the column data will be updated to match the insert column data.
     * If `false` is passed, no update will be performed if the column data already exists.
     * @param array $params the parameters to be bound to the command.
     * @return $this the command object itself.
     * @since 2.0.14
     */
    public function upsert($table, $insertColumns, $updateColumns = true, $params = []) {
        $this->commandData = $this->adb->getQueryBuilder()->upsert($table, $insertColumns, $updateColumns, $params);

        return $this;
    }

    /**
     * Creates an UPDATE command.
     *
     * For example,
     *
     * ```php
     * $connection->createCommand()->update('user', ['status' => 1], 'age > 30')->execute();
     * ```
     *
     * or with using parameter binding for the condition:
     *
     * ```php
     * $minAge = 30;
     * $connection->createCommand()->update('user', ['status' => 1], 'age > :minAge', [':minAge' => $minAge])->execute();
     * ```
     *
     * The method will properly escape the column names and bind the values to be updated.
     *
     * Note that the created command is not executed until [[execute()]] is called.
     *
     * @param string $table the table to be updated.
     * @param array $columns the column data (name => value) to be updated.
     * @param array $condition the primary key of the record ([PartitionKey, RowKey]) to be updated
     * @param array $params the parameters to be bound to the command
     * @return $this the command object itself
     */
    public function update($table, $columns, $condition = [], $params = []) {
        $this->commandData = $this->adb->getQueryBuilder()->update($table, $columns, $condition, $params);

        return $this;
    }

    /**
     * Creates a DELETE command.
     *
     * For example,
     *
     * ```php
     * $connection->createCommand()->delete('user', 'status = 0')->execute();
     * ```
     *
     * or with using parameter binding for the condition:
     *
     * ```php
     * $status = 0;
     * $connection->createCommand()->delete('user', 'status = :status', [':status' => $status])->execute();
     * ```
     *
     * The method will properly escape the table and column names.
     *
     * Note that the created command is not executed until [[execute()]] is called.
     *
     * @param string $table the table where the data will be deleted from.
     * @param string|array $condition the condition that will be put in the WHERE part. Please
     * refer to [[Query::where()]] on how to specify condition.
     * @param array $params the parameters to be bound to the command
     * @return $this the command object itself
     */
    public function delete($table, $condition = [], $params = []) {
        $this->commandData = $this->adb->getQueryBuilder()->delete($table, $condition, $params);

        return $this;
    }

    /**
     * Executes the SQL statement.
     * This method should only be used for executing non-query SQL statement, such as `INSERT`, `DELETE`, `UPDATE` SQLs.
     * No result set will be returned.
     * @return int number of rows affected by the execution.
     * @throws Exception execution failed
     */
    public function execute() {
        list($profile, $commandData) = $this->logQuery('app\components\azuredb\Command::execute');

        try {
            $profile and Yii::beginProfile(json_encode($commandData), 'app\components\azuredb\Command::execute');

            $result = $this->internalExecute($commandData);

            $profile and Yii::endProfile(json_encode($commandData), 'app\components\azuredb\Command::execute');

            return $result;
        } catch (Exception $e) {
            $profile and Yii::endProfile(json_encode($commandData), 'app\components\azuredb\Command::execute');
            throw $e;
        }
    }

    /**
     * Logs the current database query if query logging is enabled and returns
     * the profiling token if profiling is enabled.
     * @param string $category the log category.
     * @return array array of two elements, the first is boolean of whether profiling is enabled or not.
     * The second is the rawSql if it has been created.
     */
    protected function logQuery($category) {
        if ($this->adb->enableLogging) {
            $commandData = $this->commandData;
            // Yii::info(json_encode($commandData), $category);
        }
        if (!$this->adb->enableProfiling) {
            return [false, isset($commandData) ? $commandData : null];
        }

        return [true, isset($commandData) ? $commandData : $this->commandData];
    }

    /**
     * Performs the actual query for entities.
     * @return array the resulting entities
     * @throws Exception if the query causes any problem
     * @since 2.0.1 this method is protected (was private before).
     */
    protected function queryInternal($method = '', $fetchMode = null) {
        // ensure the mode is set to select
        $this->commandData['mode'] = self::MODE_SELECT;
        list($profile, $commandData) = $this->logQuery('app\components\azuredb\Command::query');

        try {
            $profile and Yii::beginProfile(json_encode($commandData), 'app\components\azuredb\Command::query');
            //prep the query object
            $this->queryOptions = new QueryEntitiesOptions();

            if ($select = ArrayHelper::getValue($commandData, 'selectFields', null)) {
                $this->queryOptions->setSelectFields($select);
            }

            $this->table = ArrayHelper::getValue($commandData, 'table', null);

            if ($queryString = ArrayHelper::getValue($commandData, 'filterString', null)) {
                $this->queryOptions->setFilter(Filter::applyQueryString($queryString));
            }

            if ($method === '') {
                return new DataReader($this);
            }
            // if we need to offset, things get more complicated
            // we can only use the offset if there is no client side filtering or sorting being done
            if ($serverOffsetLimit = ArrayHelper::getValue($commandData, 'serverOffsettingLimiting', false)) {
                if (($offset = ArrayHelper::getValue($commandData, 'offset', -1)) > 0) {
                    $profile and Yii::beginProfile('Offsetting on server', 'app\components\azuredb\Command::query');
                    //skip $offset 
                    $offsetQueryOptions = new QueryEntitiesOptions();
                    if (($filter = $this->queryOptions->getFilter()) != null) {
                        $offsetQueryOptions->setFilter($filter);
                    }
                    $offsetQueryOptions->setSelectFields(array('pk'));

                    // issue - we can only get up to 1000 rows at a time. Meaning, if our offset is over 1000, we need to make multiple queries
                    // let's just burn through the 1000s, then do the more detailed work
                    $thousands = floor($offset / 1000);
                    $overflow = $offset % 1000;
                    // for each thousand needed, just run the query
                    $offsetQueryOptions->setTop(1000);
                    for ($x = 0; $x < $thousands; $x++) {
                        $profile and Yii::beginProfile("Skipping rows {$x}000-" . ($x + 1) . "000", 'app\components\azuredb\Command::query');
                        $offsetResult = $this->adb->client->queryEntities($this->table, $offsetQueryOptions);

                        // we may need these later if there's no overflow
                        $nextPartitionKey = $offsetResult->getNextPartitionKey();
                        $nextRowKey = $offsetResult->getNextRowKey();

                        $offsetQueryOptions->setNextPartitionKey($nextPartitionKey);
                        $offsetQueryOptions->setNextRowKey($nextRowKey);
                        $profile and Yii::endProfile("Skipping rows {$x}000-" . ($x + 1) . "000", 'app\components\azuredb\Command::query');
                    }
                    // if there's an additional overflow, calculate it. Otherwise, we'll use the last values from the thousands
                    if ($overflow > 0) {
                        // now we're into the sub-1000 range, we can do what we actually came here for
                        $profile and Yii::beginProfile("Reaching final offset", 'app\components\azuredb\Command::query');
                        $offsetQueryOptions->setTop($overflow);
                        $offsetResult = $this->adb->client->queryEntities($this->table, $offsetQueryOptions);
                        $nextPartitionKey = $offsetResult->getNextPartitionKey();
                        $nextRowKey = $offsetResult->getNextRowKey();
                        // Yii::debug('Offsetting by '.count($offsetResult->getEntities())." - PartitionKey: '$nextPartitionKey' - RowKey: '$nextRowKey'");
                    }

                    $this->queryOptions->setNextPartitionKey($nextPartitionKey);
                    $this->queryOptions->setNextRowKey($nextRowKey);
                    $profile and Yii::endProfile("Reaching final offset", 'app\components\azuredb\Command::query');
                    $profile and Yii::endProfile('Offsetting on server', 'app\components\azuredb\Command::query');
                }

                // if we had to calculate an offset, these values may need to be re-set
                if (($top = ArrayHelper::getValue($commandData, 'limit', -1)) > 0) {
                    $this->queryOptions->setTop($top);
                }
            }
            // we should now be properly set up - time to run the query
            $done = false;
            $entities = [];
            while (!$done) {
                $profile and Yii::beginProfile('Actual Query: ' . json_encode($commandData), 'app\components\azuredb\Command::query');
                $result = $this->adb->client->queryEntities($this->table, $this->queryOptions);
                $profile and Yii::endProfile('Actual Query: ' . json_encode($commandData), 'app\components\azuredb\Command::query');

                // check if there are more results
                if ($result->getNextPartitionKey() != null || $result->getNextRowKey() != null) {
                    // get this list of results
                    $entities = ArrayHelper::merge($entities, $result->getEntities());
                    $this->queryOptions->setNextPartitionKey($result->getNextPartitionKey());
                    $this->queryOptions->setNextRowKey($result->getNextRowKey());
                } else {
                    if ($entities == null) {
                        $entities = $result->getEntities();
                    } else {
                        $entities = ArrayHelper::merge($entities, $result->getEntities());
                    }
                    $done = true;
                }
                // if we're doing server-side limiting, make sure we don't pull back more than we want
                if ($serverOffsetLimit && ($limit = ArrayHelper::getValue($commandData, 'limit', -1)) > 0 && count($entities) >= $limit) {
                    $done = true;
                }
            }
            $result = null;
            // convert to an easier to work with array (direct property=>value mapping)
            /** @var Entity[] $entities */
            foreach ($entities as &$entity) {
                $responseEntity = [];
                foreach ($entity->getProperties() as $key => $property) {
                    /** @var Property $property */
                    $responseEntity[$key] = $property->getValue();
                }
                $entity = $responseEntity;
            }
            $profile and Yii::endProfile(json_encode($commandData), 'app\components\azuredb\Command::query');

            return $entities;
        } catch (Exception $e) {
            $profile and Yii::endProfile(json_encode($commandData), 'app\components\azuredb\Command::query');
            throw $e;
        }
        return $result;
    }

    /**
     * Sets a callable (e.g. anonymous function) that is called when [[Exception]] is thrown
     * when executing the command. The signature of the callable should be:
     *
     * ```php
     * function (\yii\db\Exception $e, $attempt)
     * {
     *     // return true or false (whether to retry the command or rethrow $e)
     * }
     * ```
     *
     * The callable will recieve a database exception thrown and a current attempt
     * (to execute the command) number starting from 1.
     *
     * @param callable $handler a PHP callback to handle database exceptions.
     * @return $this this command instance.
     * @since 2.0.14
     */
    protected function setRetryHandler(callable $handler) {
        $this->_retryHandler = $handler;
        return $this;
    }

    /**
     * Executes a prepared statement.
     *
     * @param string|null $commandData the command data defining this operation
     * @throws Exception if execution failed.
     * @since 2.0.14
     */
    protected function internalExecute($commandData) {
        $attempt = 0;
        while (true) {
            try {
                $table = ArrayHelper::getValue($commandData, 'table', null);
                switch (ArrayHelper::getValue($commandData, 'mode', self::MODE_SELECT)) {
                    case self::MODE_INSERT_ENTITY: {
                            // insert the new entity
                            $entityObj = new Entity();
                            $entity = ArrayHelper::getValue($commandData, 'entity', []);
                            // populate the entity object with the properties
                            foreach ($entity as $property => $details) {
                                $entityObj->addProperty($property, $details['type'], $details['value']);
                            }

                            $this->adb->client->insertEntity($table, $entityObj);
                            return true;
                            break;
                        }
                    case self::MODE_UPDATE_ENTITY: {
                            // update an existing entity
                            $entityObj = new Entity();
                            $entity = ArrayHelper::getValue($commandData, 'entity', []);
                            // populate the entity object with the properties
                            foreach ($entity as $property => $details) {
                                $entityObj->addProperty($property, $details['type'], $details['value']);
                            }

                            // an added complexity - since we cannot separately reference the object, if the Partitionkey and/or rowkey has changed, we have to do a delete and insert
                            if ($commandData[self::ENTITY_PARTITION_KEY] != $commandData['entity'][self::ENTITY_PARTITION_KEY]['value'] || $commandData[self::ENTITY_ROW_KEY] != $commandData['entity'][self::ENTITY_ROW_KEY]['value']) {
                                // we need to get the existing data of the entity
                                $entity = $this->adb->client->getEntity($table, $commandData[self::ENTITY_PARTITION_KEY], $commandData[self::ENTITY_ROW_KEY])->getEntity();
                                // merge the existing data with the new
                                foreach ($entity->getProperties() as $key => $property) {
                                    if (!isset($commandData['entity'][$key]) && $key != 'Timestamp') {
                                        $commandData['entity'][$key] = [
                                            'value' => $property->getValue(),
                                            'type' => $property->getEdmType()
                                        ];
                                    }
                                }

                                // insert the new entity
                                $entityObj = new Entity();
                                $newEntity = ArrayHelper::getValue($commandData, 'entity', []);
                                // populate the entity object with the properties
                                foreach ($newEntity as $property => $details) {
                                    $entityObj->addProperty($property, $details['type'], $details['value'], $details['value']);
                                }

                                try {
                                    $this->adb->client->insertEntity($table, $entityObj);
                                    // now we have saved the new entity. Delete the old one
                                    $this->adb->client->deleteEntity($table, $commandData[self::ENTITY_PARTITION_KEY], $commandData[self::ENTITY_ROW_KEY]);
                                    return true;
                                } catch (ServiceException $e) {
                                    // something went wrong - put stuff back where we found it
                                    Yii::error($e->getMessage(), __METHOD__);
                                    throw $e;
                                    return false;
                                }
                            } else {
                                // NOTE: we use a merge, NOT an update. 
                                // An update will remove any attributes not set by this operation, while a merge will keep them at their previous values
                                // This will allow only sending the changed attributes, as is the default behaviour
                                $this->adb->client->mergeEntity($table, $entityObj);
                            }


                            return true;
                            break;
                        }
                    case self::MODE_DELETE_ENTITY: {
                            if ($commandData[self::ENTITY_PARTITION_KEY] === null && $commandData[self::ENTITY_ROW_KEY] === null) {
                                // no criteria set - clear table
                                $this->adb->client->deleteTable($table);
                                $this->adb->client->createTable($table);
                            } else {
                                // delete an entity
                                $this->adb->client->deleteEntity($table, $commandData[self::ENTITY_PARTITION_KEY], $commandData[self::ENTITY_ROW_KEY]);
                            }
                            break;
                        }
                    case self::MODE_CREATE_TABLE: {
                            $this->adb->client->createTable($table);
                            break;
                        }
                    case self::MODE_DELETE_TABLE: {
                            $this->adb->client->deleteTable($table);
                            break;
                        }
                    default:
                }
                break;
            } catch (\Exception $e) {
                if ($this->_retryHandler === null || !call_user_func($this->_retryHandler, $e, $attempt)) {
                    throw $e;
                }
            }
        }
        return false;
    }

    /**
     * Resets command properties to their initial state.
     *
     * @since 2.0.13
     */
    protected function reset() {
        $this->commandData = [];
        $this->_retryHandler = null;
    }
}
