<?php /** @noinspection PhpUnused */

namespace apexl\Vault\operators\drivers;

use apexl\Vault\enums\Join;
use apexl\Vault\exceptions\ExecutionException;
use apexl\Vault\exceptions\FormatColumnsException;
use apexl\Vault\exceptions\TableException;
use apexl\Vault\interfaces\driver;
use apexl\Vault\operators\pdo;
use stdClass;

/**
 * @TODO    Refactor the way SQL is built - don't just append in the order it is
 *          called.  Instead, build up array of chunks and join them in the
 *          correct order.
 */
class mysql extends pdo implements driver
{
    /**
     * @throws ExecutionException
     */
    public function createDatabase(string $dbName, bool $forceLower = true): mysql
    {
        $this->sql = "CREATE DATABASE ".($forceLower ? strtolower($dbName) : $dbName);

        return $this->execute();
    }

    /**
     * @throws ExecutionException
     */
    public function createUser(string $user, string $pass): mysql
    {
        $this->sql = sprintf("CREATE USER '%s'@'%s' IDENTIFIED BY '%s'", $user, $this->host, $pass);

        return $this->execute();
    }

    /**
     * @throws ExecutionException
     */
    public function assignUserToDB(
        string $user,
        string $database,
        string $grant = 'CREATE, DELETE, INSERT, SELECT, UPDATE'
    ): void {
        /**
         *  It turns out simply assuming the host provided is the host for the user causes... issues.
         *  So, we need to find out what the current user is, what the host is, and if that's the same as the one
         *  we're assigning to, before we try this.
         */

        $this->sql = 'select current_user()';
        $result = $this->execute()->fetchAssoc();
        $currentUser = explode('@', (string) array_shift($result));
        $host = $this->host;

        // The same user? then we can assume we need to check the host value.
        if ($currentUser[0] == $user) {
            $host = $currentUser[1];
        }

        $this->sql = sprintf("GRANT %s ON %s.* TO '%s'@'%s'", $grant, $database, $user, $host);
        $this->execute();

        $this->sql = "FLUSH PRIVILEGES";
        $this->execute();
    }

    /**
     * @throws ExecutionException
     * @throws FormatColumnsException
     */
    public function createTable(
        string $table,
        stdClass $colList,
        bool $existsCheck = true,
        string $engine = 'INNODB'
    ): mysql {
        $this->queryFields = [];
        //create the table
        $this->sql = sprintf(
        /** @lang TEXT */
            'CREATE TABLE %s %s (%s) ENGINE=%s',
            $existsCheck ? "IF NOT EXISTS " : "",
            $table,
            $this->formatColumns($colList, "CREATE"),
            $engine,
        );

        $this->execute();

        //next, check if we were passed any indexes, loop over them and apply those too.s
        if (isset($colList->_indexes)) {
            foreach ($colList->_indexes as $index) {
                $this->sql = sprintf(
                /** @lang TEXT */
                    'CREATE INDEX %1$s%2$s on %1$s (%2$s)',
                    $table,
                    $index,
                );

                $this->execute();
            }
        }
        //next, check if we were passed any unique indexes, loop over them and apply those too.s
        if (isset($colList->{'_unique'})) {
            foreach ($colList->{'_unique'} as $index) {
                $this->sql = sprintf(
                /** @lang TEXT */
                    'CREATE UNIQUE INDEX %1$s%2$s_unique on %1$s (%2$s)',
                    $table,
                    $index,
                );

                $this->execute();
            }
        }

        return $this;
    }

    /**
     * @throws FormatColumnsException
     * @noinspection PhpSameParameterValueInspection
     */
    private function formatColumns($tableData, string $operation): string
    {
        if (!isset($tableData->_columns)) {
            throw new FormatColumnsException("MYSQL Table definition must contain columns");
        }
        $noCols = count($tableData->_columns);
        $columns = $tableData->_columns;
        $formattedColumns = [];
        for ($i = 0; $i < $noCols; ++$i) {
            if (!isset($columns[$i]->name)) {
                throw new FormatColumnsException(
                    sprintf('MYSQL Column name missing from column operation .%s', $operation)
                );
            }
            if (!isset($columns[$i]->type) && $operation == "CREATE") {
                throw new FormatColumnsException(
                    sprintf('MYSQL Column type missing from column %s operation', $operation)
                );
            }
            $formattedColumns[$i] = $this->buildColumnDefinition($columns[$i], $tableData->primary_key);
        }

        return implode(', ', $formattedColumns);
    }

    private function buildColumnDefinition(stdClass $column, ?string $primaryKey = null): string
    {
        $definition = $column->name;
        //build based on type.
        switch (strtolower($column->type)) {
            case 'varchar':
                $length = $column->length ?? 255;
                $definition .= ' varchar('.$length.')';
                break;
            case 'bool':
            case 'boolean':
                $definition .= ' tinyint(1)';
                break;
            case 'decimal':
                $length = $column->length ?? 11;
                $decimalPlaces = $column->decimal_places ?? 2;
                $definition .= " DECIMAL($length,$decimalPlaces)";
                break;
            case 'int':
                $length = $column->length ?? 11;
                $definition .= ' int('.$length.')';
                break;
            case 'json':
                $definition .= ' JSON';
                break;
            default:
                $definition .= ' '.strtolower((string) $column->type);
                break;
        }

        if (isset($column->auto_increment) && $column->auto_increment) {
            $definition .= ' auto_increment';
        } else {
            if (isset($column->default)) {
                $definition .= ' default '.(is_string($column->default) ? "'".$column->default."'" : $column->default);
            }
            if (strtolower($column->type) != 'json') {
                if (isset($column->is_null) && $column->is_null) {
                    $definition .= ' null';
                } else {
                    //assume not nulls
                    $definition .= ' not null';
                }
            }
        }

        if ($primaryKey && $primaryKey == $column->name) {
            $definition .= ' primary key';
        }

        //build row
        return $definition;
    }

    /**
     * @throws TableException
     */
    public function select(string $table): mysql
    {
        $this->setTable($table);
        $this->sql = "SELECT ";

        return $this;
    }

    /**
     * @throws TableException
     */
    public function setTable(string $table): mysql
    {
        if (!empty($table)) {
            $this->table = $table;

            return $this;
        }

        throw new TableException("Cannot set a mysql table to blank string");
    }

    /**
     * @throws TableException
     */
    public function describe(string $table): mysql
    {
        $this->setTable($table);
        $this->queryFields = [];

        $this->sql = sprintf('DESCRIBE %s', $table);

        return $this;
    }

    public function join(string $table, string $field1, string $field2, string $condition = '='): mysql
    {
        $this->addJoin(Join::JOIN, $table, $field1, $field2, $condition);

        return $this;
    }

    protected function addJoin(Join $join, string $table, string $field1, string $field2, string $condition): mysql
    {
        $this->appendSql(
            sprintf(
                "%s %s ON %s %s %s",
                $join->value,
                $table,
                $field1,
                $condition,
                $field2
            )
        );

        return $this;
    }

    public function appendSql(string $append): mysql
    {
        $this->sql = sprintf('%s %s', $this->sql, $append);

        return $this;
    }

    public function innerJoin(string $table, string $field1, string $field2, string $condition = '='): mysql
    {
        $this->addJoin(Join::INNER_JOIN, $table, $field1, $field2, $condition);

        return $this;
    }

    public function leftJoin(string $table, string $field1, string $field2, string $condition = '='): mysql
    {
        $this->addJoin(Join::LEFT_JOIN, $table, $field1, $field2, $condition);

        return $this;
    }

    /**
     * @throws TableException
     */
    public function update(string $table): mysql
    {
        $this->setTable($table);
        $this->sql = sprintf('UPDATE %s', $this->table);

        return $this;
    }

    /**
     * @throws TableException
     */
    public function insert(string $table): mysql
    {
        $this->setTable($table);
        $this->sql = sprintf(
        /** @lang TEXT */
            'INSERT INTO %s',
            $this->table
        );

        return $this;
    }

    /**
     * @param  string[] | string | null  $fields
     */
    public function fields($fields = null): mysql
    {
        $this->queryFields = []; //empty the fields array.
        if ($fields) {
            $fields = (array) $fields;
        }

        if (strpos($this->sql, "INSERT INTO") !== false) {
            $batch = false;
            $fieldsRow = $fields;
            if (isset($fields[0])) {
                $batch = true;
                $fieldsRow = $fields[0];
            }

            return $this->setInsertValues(
                array_keys($fieldsRow),
                array_values($fields),
                $batch
            );
        } elseif (strpos($this->sql, "UPDATE ") !== false) {
            return $this->setUpdateValues(
                array_keys($fields),
                array_values($fields),
            );
        } elseif (strpos($this->sql, "SELECT ") !== false) {
            return $this->setSelectValues($fields);
        }

        return $this;
    }

    /**
     * @param  string | string[]  $fields
     */
    private function setInsertValues($fields, array $values, bool $batch = false): mysql
    {
        $this->appendSql(
            sprintf(
                '(%s)',
                is_array($fields) ? implode(',', $fields) : $fields
            )
        );

        $rows = [];

        $values = $batch ? $values : [$values];

        foreach ($values as $row) {
            if ($batch) {
                $row = array_values($row);
            }

            $rows[] = sprintf(
                '(%s)',
                implode(',', $this->getFieldIds($fields, $row))
            );
        }

        $this->appendSql(
            sprintf(
                'VALUES %s',
                implode(",", $rows)
            )
        );

        if ($batch) {
            $clauses = [];

            foreach ($fields as $field) {
                $clauses[] = sprintf(' %s = VALUES(%s)', $field, $field);
            }

            $this->appendSql(
                sprintf(
                    'ON DUPLICATE KEY UPDATE%s',
                    implode(", ", $clauses)
                )
            );
        }

        return $this;
    }

    private function getFieldIds(array $fields, array $values): array
    {
        $fieldIds = [];

        foreach ($fields as $key => $field) {
            $fieldIds[] = $this->setQueryField($field, $values[$key]);
        }

        return $fieldIds;
    }

    /**
     * Add fields to the active query, account for multiple fields with the same name.
     * @param  string | string[]  $value
     */
    private function setQueryField(string $field, $value): string
    {
        if (is_array($value)) {
            $fieldInc = [];
            foreach ($value as $val) {
                $fieldInc[] = $fieldRaw = $this->addQueryField($field);
                $this->queryFields[$fieldRaw] = $val;
            }

            return implode(',', $fieldInc);
        }

        $fieldInc = $this->addQueryField($field);
        $this->queryFields[$fieldInc] = $value;

        return $fieldInc;
    }

    private function addQueryField(string $field): string
    {
        $num = 1;
        $field = str_replace(['.', ' ', ')', '('], ['', '_', '', ''], (string) $field);

        $defaultKey = sprintf(':%s', $field);
        if (isset($this->queryFields[$defaultKey])) {
            while (true) {
                $key = sprintf(':%s_%d', $field, $num);
                if (!isset($this->queryFields[$key])) {
                    return $key;
                }
                $num++;
            }
        }

        return $defaultKey;
    }

    /**
     * @param  string | string[]  $fields
     */
    private function setUpdateValues($fields, array $values): mysql
    {
        $this->appendSql('SET ');

        if (is_array($fields)) {
            foreach ($fields as $key => $field) {
                $this->appendSql(
                    sprintf(
                        '%s = %s, ',
                        $field,
                        $this->setQueryField($field, $values[$key])
                    )
                );
            }

            $this->sql = rtrim($this->sql, ', ');

            return $this;
        }

        $this->appendSql(
            sprintf(
                '%s=%s',
                $fields,
                $this->setQueryField($fields, $values)
            )
        );

        return $this;
    }

    /**
     * @param  null | string | string[]  $fields
     */
    private function setSelectValues($fields): mysql
    {
        if (!is_null($fields)) {
            $fields = is_array($fields) ? implode(', ', $fields) : $fields;
        } else {
            $fields = "*";
        }

        $this->appendSql(
            sprintf(
                '%s FROM %s',
                $fields,
                $this->table
            )
        );

        return $this;
    }

    /**
     * @throws TableException
     */
    public function delete(string $table): mysql
    {
        $this->setTable($table);
        $this->queryFields = [];
        $this->sql = sprintf('DELETE FROM %s', $this->table);

        return $this;
    }

    /**
     * @param  string | int | bool  $value
     */
    public function orWhere(string $field, $value, string $condition = '='): mysql
    {
        return $this->where($field, $value, $condition, 'OR');
    }

    /**
     * @param  string | int | bool  $value
     */
    public function where(
        string $field,
        $value,
        string $condition = '=',
        string $type = 'AND',
        string $postCondition = ''
    ): mysql {
        $this->appendSql(
            strpos($this->sql, " WHERE") === false ? " WHERE " : str_pad(
                strtoupper($type),
                strlen($type) + 2,
                ' ',
                STR_PAD_BOTH
            )
        );

        if (!in_array(strtoupper($condition), [
            'IS NULL',
            'IS NOT NULL',
        ])) {
            $fieldInc = $this->setQueryField($field, $value);
            if (in_array(strtoupper($condition), ['IN', 'NOT IN'])) {
                $this->appendSql(sprintf('%s %s (%s)', $field, $condition, $fieldInc));
            } else {
                $this->appendSql(sprintf('%s %s %s', $field, $condition, $fieldInc));
            }
        } else {
            $this->appendSql(sprintf("%s %s", $field, $condition));
        }

        $this->appendSql(sprintf('%s', $postCondition));

        return $this;
    }

    /**
     * @param  string | int | bool  $value1
     * @param  string | int | bool  $value2
     */
    public function whereNestedOr(string $field1, $value1, string $field2, $value2): mysql
    {
        $arg = strpos($this->sql, " WHERE") === false ? " WHERE " : " AND ";

        $this->appendSql(
            sprintf(
                '%s(%s=%s OR %s=%s)',
                $arg,
                $field1,
                $this->setQueryField($field1, $value1),
                $field2,
                $this->setQueryField($field2, $value2)
            )
        );

        return $this;
    }

    public function orderBy(string $field, string $sort = 'ASC'): mysql
    {
        $this->appendSql(strpos($this->sql, " ORDER BY") === false ? " ORDER BY " : ' ,');
        $this->appendSql(sprintf(' %s %s', $field, $sort));

        return $this;
    }

    public function groupBy(string $field): mysql
    {
        $this->appendSql(sprintf(' GROUP BY %s', $field));

        return $this;
    }

    public function limit(int $limit, int $offset = 0): mysql
    {
        $this->appendSql(sprintf("LIMIT %d, %d", $offset, $limit));

        return $this;
    }

    /**
     * Function to Set or update field values for the stored query
     * @var array<string, string> $fields
     */
    public function setFieldValues(array $fields, bool $empty = false): mysql
    {
        if ($empty) {
            $this->queryFields = [];
        }

        foreach ($fields as $field => $value) {
            $this->setQueryField($field, $value);
        }

        return $this;
    }

    public function __get(string $name)
    {
        $this->table = $name;

        return $this;
    }

    public function lastInsertId(): int
    {
        return (int) $this->connection->lastInsertId();
    }

    /**
     * @throws ExecutionException
     */
    public function keyIsAutoIncrementing(string $keyField, string $table): bool
    {
        $this->sql = sprintf(
            "SELECT COUNT(*) count FROM INFORMATION_SCHEMA.COLUMNS
                        WHERE TABLE_NAME = '%s'
                        AND COLUMN_NAME = '%s'
                        AND EXTRA like '%%auto_increment%%'
                   ",
            $table,
            $keyField
        );
        $this->queryFields = [];
        $this->execute();

        $data = $this->statement->fetch(\PDO::FETCH_OBJ);

        return ((int) $data->count) !== 0;
    }
}
