<?php /** @noinspection PhpUnused */

namespace apexl\Vault\operators\drivers;

use apexl\Vault\enums\SortDirection;
use apexl\Vault\collections\joinConditionCollection;
use apexl\Vault\dto\joinCondition;
use apexl\Vault\dto\raw;
use apexl\Vault\enums\Join;
use apexl\Vault\enums\WhereCondition;
use apexl\Vault\enums\WhereType;
use apexl\Vault\exceptions\ExecutionException;
use apexl\Vault\exceptions\FormatColumnsException;
use apexl\Vault\interfaces\driver;
use apexl\Vault\operators\pdo;
use apexl\Vault\queries\RawStatement;
use Arrayy\Type\StringCollection;
use Aura\SqlQuery\Common\InsertInterface;
use Aura\SqlQuery\Common\LimitInterface;
use Aura\SqlQuery\Common\LimitOffsetInterface;
use Aura\SqlQuery\Common\OrderByInterface;
use Aura\SqlQuery\Common\SelectInterface;
use Aura\SqlQuery\Common\UpdateInterface;
use Aura\SqlQuery\Common\WhereInterface;
use Aura\SqlQuery\Mysql\Insert;
use Aura\SqlQuery\QueryFactory;
use Aura\SqlQuery\QueryInterface;
use Closure;
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
{
    public QueryInterface $query;
    private mixed $queryFactory;
    private array $subQueryFields = [];

    public function __construct(
        stdClass $connectionDetails
    ) {
        $this->queryFactory = new QueryFactory('mysql');

        parent::__construct($connectionDetails);
    }

    public function subQuery(Closure $callback, ?string $alias = null): string
    {
        $_query = clone $this;

        $_query->reset();

        /** @var driver $subQuery */
        $subQuery = $callback($_query);

        $this->subQueryFields = [
            ...$this->subQueryFields,
            ...$subQuery->getQueryFields(),
        ];

        return sprintf(
            '(%s)%s',
            $callback($_query)->query,
            $alias ? sprintf('AS %s', $alias) : ''
        );
    }

    public function reset(): void
    {
        $this->sql = '';
        $this->queryFields = [];
        $this->subQueryFields = [];
        $this->queryFactory = new QueryFactory('mysql');
    }

    public function __clone(): void
    {
        $this->query = clone $this->query;
    }

    /**
     * @throws ExecutionException
     */
    public function createDatabase(string $dbName, bool $forceLower = true): mysql
    {
        return $this->executeSql(sprintf("CREATE DATABASE %s", $forceLower ? strtolower($dbName) : $dbName));
    }

    /**
     * @throws ExecutionException
     */
    public function executeSql(string $sql, array $params = []): mysql
    {
        $this->query = new RawStatement($sql, $params);

        return $this->execute();
    }

    public function execute(): pdo
    {
        $this->sql = preg_replace([
            '/\(\s*OR/',
            '/AND\s*\)/',
        ], [
            '(',
            ')',
        ], $this->query->getStatement());

        $this->queryFields = [
            ...$this->query->getBindValues(),
            ...$this->subQueryFields,
        ];

        return parent::execute();
    }

    public function startWhereGroup(): mysql
    {
        if ($this->query instanceof WhereInterface) {
            $this->query->where('(');
        }

        return $this;
    }

    public function where(
        string $field,
        $value,
        WhereCondition|string $condition = WhereCondition::EQUALS,
        WhereType|string $type = WhereType::AND,
        string $postCondition = ''
    ): mysql {
        if (!$condition instanceof WhereCondition) {
            $condition = WhereCondition::from($condition);
        }

        if (!$type instanceof WhereType) {
            $type = WhereType::get($type);
        }

        if (!$this->query instanceof WhereInterface) {
            return $this;
        }

        $method = [$this->query, $type === WhereType::AND ? 'where' : 'orWhere'];

        [$test, $bind] = $this->arrangeCondition($field, $value, $condition);

        $method($test, $bind);

        return $this;
    }

    private function arrangeCondition($field, $value, $condition): array
    {
        $isRaw = $value instanceof raw;

        $placeholder = $isRaw ? $value : $this->placeholder($field);
        $bind = [];

        switch ($condition) {
            case WhereCondition::IN:
            case WhereCondition::NOT_IN:
                $placeholders = array_map(
                    fn(int $ix) => sprintf('%s_%d', $this->placeholder($field), $ix),
                    array_keys($value),
                );

                $placeholder = sprintf(
                    '(%s)',
                    implode(
                        ',',
                        $placeholders
                    )
                );

                $test = sprintf('%s %s %s', $this->maybeBacktick($field), $condition->value, $placeholder);
                if (!$isRaw) {
                    $bind = array_combine($placeholders, $value);
                }
                break;

            case WhereCondition::IS_NULL:
            case WhereCondition::IS_NOT_NULL:
                $test = sprintf('%s %s', $this->maybeBacktick($field), $condition->value);
                break;

            case WhereCondition::LIKE:
                $test = sprintf('%s %s %s', $this->maybeBacktick($field), $condition->value, $placeholder);
                if (!$isRaw) {
                    $bind = [$placeholder => sprintf('%%%s%%', $value)];
                }

                break;

            case WhereCondition::BETWEEN:
                $placeholderStart = $this->placeholder($field.'_start');
                $placeholderEnd = $this->placeholder($field.'_end');

                $test = sprintf(
                    '%s BETWEEN %s AND %s',
                    $this->maybeBacktick($field),
                    $placeholderStart,
                    $placeholderEnd,
                );

                $bind = [
                    $placeholderStart => $value['start'],
                    $placeholderEnd => $value['end'],
                ];
                break;

            case WhereCondition::EQUALS:
            case WhereCondition::GREATER_THAN:
            case WhereCondition::GREATER_THAN_OR_EQUAL:
            case WhereCondition::LESS_THAN:
            case WhereCondition::LESS_THAN_OR_EQUAL:
            case WhereCondition::REGEXP:
            default:
                $test = sprintf(
                    '%s %s %s',
                    $this->maybeBacktick($field),
                    $condition->value,
                    $placeholder
                );
                if (!$isRaw) {
                    $bind = [$placeholder => $value];
                }
        }

        return [$test, $bind];
    }

    private function placeholder(string $field): string
    {
        $statement = $this->query->getStatement();
        $slug = preg_replace('/[^a-zA-Z0-9_]+/', '_', $field);
        $placeholder = sprintf(':%s', $slug);
        $ix = 1;

        while (str_contains($statement, $placeholder)) {
            $placeholder = sprintf(':%s_%d', $slug, $ix++);
        }

        return $placeholder;
    }

    private function maybeBacktick(string $field): string
    {
        if (preg_match('/^`\s`|[A-Z]+\(.*\)$/', $field)) {
            return $field;
        }

        $bits = StringCollection::createFromArray(explode('.', $field));

        return $bits->map(fn(string $bit) => sprintf('`%s`', $bit))->implode('.');
    }

    public function endWhereGroup(): mysql
    {
        if ($this->query instanceof WhereInterface) {
            $this->query->where(')');
        }

        return $this;
    }

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

    /**
     * @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->executeSql(sprintf("GRANT %s ON %s.* TO '%s'@'%s'", $grant, $database, $user, $host));

        $this->executeSql("FLUSH PRIVILEGES");
    }

    /**
     * @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->executeSql(
                    sprintf(
                    /** @lang TEXT */
                        'CREATE INDEX %1$s%2$s on %1$s (%2$s)',
                        $table,
                        $index,
                    )
                );
            }
        }
        //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->executeSql(
                    sprintf(
                    /** @lang TEXT */
                        'CREATE UNIQUE INDEX %1$s%2$s_unique on %1$s (%2$s)',
                        $table,
                        $index,
                    )
                );
            }
        }

        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;
    }

    public function select(string $table): mysql
    {
        $this->query = $this->queryFactory->newSelect()->from($table);

        return $this;
    }

    public function selectDistinct(string $table): mysql
    {
        $this->query = $this->queryFactory->newSelect()->distinct()->from($table);

        return $this;
    }

    /**
     * @throws ExecutionException
     */
    public function describe(string $table): mysql
    {
        return $this->executeSql(sprintf('DESCRIBE %s', $table));
    }

    public function innerJoin(
        string $table,
        string|array $field1,
        ?string $field2 = null,
        WhereCondition|string $condition = WhereCondition::EQUALS
    ): mysql {
        $this->addJoin(Join::INNER, $table, $field1, $field2, $condition);

        return $this;
    }

    protected function addJoin(
        Join $join,
        string $table,
        string|array $field1,
        ?string $field2 = null,
        WhereCondition|string $condition = WhereCondition::EQUALS
    ): mysql {
        if (!$this->query instanceof SelectInterface) {
            return $this;
        }

        if (!$condition instanceof WhereCondition) {
            $condition = WhereCondition::from($condition);
        }

        $joinConditionCollection = is_array($field1) ? joinConditionCollection::createFromArray(
            $field1
        ) : joinConditionCollection::createFromArray([
            new JoinCondition($field1, $field2, $condition),
        ]);

        $this->query->join(
            $join->value,
            $table,
            $joinConditionCollection->toConditionString()
        );

        return $this;
    }

    public function join(
        string $table,
        string|array $field1,
        ?string $field2 = null,
        WhereCondition|string $condition = WhereCondition::EQUALS
    ): mysql {
        return $this->innerJoin(...func_get_args());
    }

    public function leftJoin(
        string $table,
        string|array $field1,
        ?string $field2 = null,
        WhereCondition|string $condition = WhereCondition::EQUALS
    ): mysql {
        $this->addJoin(Join::LEFT_JOIN, $table, $field1, $field2, $condition);

        return $this;
    }

    public function update(string $table): mysql
    {
        $this->query = $this->queryFactory->newUpdate()->table($table);

        return $this;
    }

    public function insert(string $table): mysql
    {
        $this->query = $this->queryFactory->newInsert()->into($table);

        return $this;
    }

    public function fields(array|string|null $fields = null): mysql
    {
        $fields = (array) $fields;

        return match (true) {
            $this->query instanceof InsertInterface => $this->fieldsForInsert($fields),
            $this->query instanceof UpdateInterface => $this->fieldsForUpdate($fields),
            $this->query instanceof SelectInterface => $this->fieldsForSelect($fields),
            default => $this,
        };

    }

    private function fieldsForInsert(array $fields): mysql
    {
        if (!$this->query instanceof InsertInterface) {
            return $this;
        }

        if (!isset($fields[0])) {
            $fields = [$fields];
        }

        foreach ($fields as $row) {
            $this->query->addRow($row);

            if ($this->query instanceof Insert) {
                $this->query->onDuplicateKeyUpdateCols($row);
            }
        }

        return $this;
    }

    private function fieldsForUpdate(array $fields): mysql
    {
        if (!$this->query instanceof UpdateInterface) {
            return $this;
        }

        $this->query->cols($fields);

        return $this;
    }

    private function fieldsForSelect(array $fields): mysql
    {
        if (!$this->query instanceof SelectInterface) {
            return $this;
        }
        $this->query->cols($fields ?: ['*']);

        return $this;
    }

    public function delete(string $table): mysql
    {
        $this->query = $this->queryFactory->newDelete()->from($table);

        return $this;
    }

    /**
     * Function provides a way to group OR conditions
     * e.g. WHERE field0 = value0 AND (field1=:value1 OR field2=:value2)
     * @param  array  $groupedClauses  [[field, value, condition],[field,value,condition]] etc.
     * @param  WhereType|string  $type
     * @return $this
     */
    public function orWhereMultiple(array $groupedClauses, WhereType|string $type = WhereType::AND): mysql
    {
        $groupBind = [];
        $groupTest = [];
        foreach ($groupedClauses as $clause) {
            [$field, $value, $condition] = $clause;
            [$test, $bind] = $this->arrangeCondition($field, $value, $condition);

            $groupTest[] = $test;
            $groupBind = array_merge($groupBind, $bind);
        }

        $method = [$this->query, $type === WhereType::AND ? 'where' : 'orWhere'];
        $test = '('.implode(' OR ', $groupTest).')';
        $method($test, $groupBind);

        return $this;
    }

    public function orWhere(string $field, $value, WhereCondition $condition = WhereCondition::EQUALS): mysql
    {
        return $this->where($field, $value, $condition, WhereType::OR);
    }

    public function orderBy(string|array $fieldOrMultipleOrderBys, SortDirection $sort = SortDirection::ASC): mysql
    {
        if (is_array($fieldOrMultipleOrderBys)) {
            foreach ($fieldOrMultipleOrderBys as $orderBy) {
                [$field, $sort] = array_pad($orderBy, 2, null);
                $this->orderBy($field, $sort ?? SortDirection::ASC);
            }

            return $this;
        }

        $field = $fieldOrMultipleOrderBys;

        if ($this->query instanceof OrderByInterface) {
            match ($sort) {
                SortDirection::ASC,
                SortDirection::DESC => $this->query->orderBy([
                    sprintf('%s %s', $field, $sort->name),
                ]),
                SortDirection::NULL_FIRST_ASC => $this->query->orderBy([
                    sprintf('%s IS NULL ASC', $field),
                ]),
                SortDirection::NULL_FIRST_DESC => $this->query->orderBy([
                    sprintf('%s IS NULL DESC', $field),
                ]),
                SortDirection::NULL_LAST_ASC => $this->query->orderBy([
                    sprintf('%s IS NOT NULL ASC', $field),
                ]),
                SortDirection::NULL_LAST_DESC => $this->query->orderBy([
                    sprintf('%s IS NOT NULL DESC', $field),
                ]),
            };
        }

        return $this;
    }

    public function groupBy(string|array $field): mysql
    {
        if ($this->query instanceof SelectInterface) {
            $this->query->groupBy((array) $field);
        }

        return $this;
    }

    public function limit(int $limit, int $offset = 0): mysql
    {
        if ($this->query instanceof LimitInterface) {
            $this->query->limit($limit);
        }

        if ($this->query instanceof LimitOffsetInterface) {
            $this->query->offset($offset);
        }

        return $this;
    }

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

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

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

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

    public function resetOrderBy(): void
    {
        if ($this->query instanceof SelectInterface) {
            $this->query->resetOrderBy();
        }
    }

    public function resetFields(): void
    {
        if ($this->query instanceof SelectInterface) {
            $this->query->resetCols();
        }
    }
}
