<?php /** @noinspection PhpUnused */

namespace Apexl\Vault\Operators\Drivers;

use Apexl\Dto\Raw;
use Apexl\Vault\Aura\SqlQuery\QueryFactory;
use Apexl\Vault\Collections\JoinConditionCollection;
use Apexl\Vault\Dtos\JoinCondition;
use Apexl\Vault\Enums\HavingType;
use Apexl\Vault\Enums\Join;
use Apexl\Vault\Enums\SortDirection;
use Apexl\Vault\Enums\WhereCondition;
use Apexl\Vault\Enums\WhereType;
use Apexl\Vault\Exceptions\ExecutionException;
use Apexl\Vault\Interfaces\Driver;
use Apexl\Vault\Interfaces\Operators;
use Apexl\Vault\Interfaces\QueryBuilderInterface;
use Apexl\Vault\Interfaces\ResultInterface;
use Apexl\Vault\Interfaces\SchemaInterface;
use Apexl\Vault\Operators\Pdo;
use Apexl\Vault\Queries\RawStatement;
use Apexl\Vault\Schemas\MysqlSchema;
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\QueryInterface;
use Closure;
use PDO as PdoConnection;
use stdClass;

/** @noinspection PhpDeprecationInspection — `driver` is implemented for 4.x callers on purpose. */
class MySql extends Pdo implements QueryBuilderInterface, Driver, Operators
{
    public QueryInterface $query;
    protected mixed $queryFactory;
    protected array $subQueryFields = [];

    /**
     * Upsert state for the current query. `null` = plain INSERT (collisions
     * surface as ExecutionException); `[]` = upsert updating ALL inserted
     * columns on conflict; a non-empty list = upsert updating only those
     * columns. Set exclusively by upsert(); cleared by insert()/reset().
     * @var string[]|null
     */
    protected ?array $upsertColumns = null;

    public function __construct(
        stdClass $connectionDetails
    ) {
        $this->queryFactory = $this->createQueryFactory();

        parent::__construct($connectionDetails);
    }

    protected function createQueryFactory(): QueryFactory
    {
        return new QueryFactory('mysql');
    }

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

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

        $this->addSubQueryFields(
            $subQuery->query->getBindValues(),
        );

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

    public function newQuery(): static
    {
        $_query = clone $this;

        $_query->reset();

        return $_query;
    }

    public function reset(): void
    {
        $this->sql = '';
        $this->queryFields = [];
        $this->subQueryFields = [];
        $this->upsertColumns = null;
        $this->queryFactory = $this->createQueryFactory();
    }

    public function addSubQueryFields(array $fields): void
    {
        $this->subQueryFields = [
            ...$this->subQueryFields,
            ...$fields,
        ];
    }

    public function __clone(): void
    {
        if (isset($this->query)) {
            $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 = []): ResultInterface
    {
        $this->query = new RawStatement($sql, $params);

        return $this->execute();
    }

    public function execute(): ResultInterface
    {
        $this->buildSql();
        $this->queryFields = [
            ...$this->query->getBindValues(),
            ...$this->subQueryFields,
        ];

        return parent::execute();
    }

    private function buildSql(): void
    {
        $this->sql = preg_replace([
            '/\(\s*(OR|AND)/',
            '/(AND|OR)\s*\)/',
            '/#SINGLEQUOTE#/',
            '/#DOUBLEQUOTE#/',
            '/WHERE\s+\(\s+\)/',
        ], [
            '(',
            ')',
            '\'',
            '"',
            '',
        ], $this->query->getStatement());

    }

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

        return $this;
    }

    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,
    ): 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 = match ($type) {
            WhereType::AND => $this->query->where(...),
            WhereType::OR => $this->query->orWhere(...),
        };

        $arranged = $this->arrangeCondition($field, $value, $condition);

        if ($arranged) {
            [$test, $bind] = $arranged;

            $method($test, $bind);
        }

        return $this;
    }

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

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

        switch ($condition) {
            case WhereCondition::IN:
            case WhereCondition::NOT_IN:
                if (is_string($value)) {
                    $test = sprintf('%s %s %s', $this->maybeBacktick($field), $condition->value, $value);
                    break;
                }

                $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:
                if (!$value) {
                    return null;
                }

                if (!($value['start'] ?? null)) {
                    return $this->arrangeCondition($field, $value['end'], WhereCondition::LESS_THAN_OR_EQUAL);
                }

                if (!($value['end'] ?? null)) {
                    return $this->arrangeCondition($field, $value['start'], WhereCondition::GREATER_THAN_OR_EQUAL);
                }

                $placeholderStart = $this->placeholder(sprintf('%s_start', $field));
                $placeholderEnd = $this->placeholder(sprintf('%s_end', $field));

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

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

            case WhereCondition::JSON_CONTAINS:
                $placeholder = $this->placeholder($field);
                $test = sprintf('JSON_CONTAINS(%s, %s)', $field, $placeholder);
                $bind = [
                    $placeholder => $value,
                ];

                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 = hash('xxh3', $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]+\s*\(.*\)$/s', $field)) {
            return $field;
        }

        $field = preg_replace('/\s+/s', ' ', $field);
        $bits = explode(' ', $field);

        $bits = array_map(function (string $bit): string {
            if (preg_match('/^[\w\.]+$/', $bit)) {
                $moreBits = explode('.', $bit);

                return implode('.', array_map(fn(string $moreBit): string => sprintf('`%s`', $moreBit), $moreBits));
            }

            return $bit;
        }, $bits);

        return implode(' ', $bits);
    }

    public function startHavingGroup(): MySql
    {
        if ($this->query instanceof SelectInterface) {
            $this->query->having('(');
        }

        return $this;
    }

    public function having(
        string $field,
        $value,
        string|WhereCondition $condition = WhereCondition::EQUALS,
        HavingType $type = HavingType::AND
    ): MySql {
        if (!$condition instanceof WhereCondition) {
            $condition = WhereCondition::from($condition);
        }

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

        $method = match ($type) {
            HavingType::AND => $this->query->having(...),
            HavingType::OR => $this->query->orHaving(...),
        };

        $arranged = $this->arrangeCondition($field, $value, $condition);

        if ($arranged) {
            [$test, $bind] = $arranged;

            $method($test, $bind);
        }

        return $this;
    }

    public function endHavingGroup(): MySql
    {
        if ($this->query instanceof SelectInterface) {
            $this->query->having(')');
        }

        return $this;
    }

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

    public function schema(): SchemaInterface
    {
        return new MysqlSchema($this);
    }

    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 innerJoin(
        string $table,
        string|array|Raw $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|Raw $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);
        }

        if ($field1 instanceof Raw) {
            $this->query->join(
                $join->value,
                $table,
                $field1->value
            );

            return $this;
        }

        $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|Raw $field1,
        ?string $field2 = null,
        WhereCondition|string $condition = WhereCondition::EQUALS
    ): MySql {
        return $this->innerJoin(...func_get_args());
    }

    public function leftJoin(
        string $table,
        string|array|Raw $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->upsertColumns = null;
        $this->query = $this->queryFactory->newInsert()->into($table);

        return $this;
    }

    /**
     * INSERT that merges on unique/primary-key conflict instead of failing
     * (MySQL `INSERT ... ON DUPLICATE KEY UPDATE`). Use this — not insert() —
     * when a colliding row should be updated in place. Plain insert() lets
     * key collisions surface as ExecutionException.
     *
     * @param string[] $updateColumns Columns to overwrite on conflict.
     *                                Empty (default) = every inserted column,
     *                                matching the historical implicit-upsert
     *                                behaviour insert() used to have.
     */
    public function upsert(string $table, array $updateColumns = []): MySql
    {
        $this->insert($table);
        $this->upsertColumns = $updateColumns;

        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;

            $arranged = $this->arrangeCondition($field, $value, $condition);

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

        $method = $type->toCallable($this);
        $test = sprintf('(%s)', implode(' OR ', $groupTest));
        $method($test, $groupBind);

        return $this;
    }

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

    public function orHaving(string $field, $value, string|WhereCondition $condition = WhereCondition::EQUALS): MySql
    {
        return $this->having($field, $value, $condition, HavingType::OR);
    }

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

        return $this;
    }

    /**
     * @throws ExecutionException
     */
    public function orWhereRaw(string $condition, array $params = []): QueryBuilderInterface
    {
        return $this->whereRaw($condition, $params, WhereType::OR);
    }

    public function whereRaw(string $condition, array $params = [], WhereType|string $type = WhereType::AND): QueryBuilderInterface
    {
        if (!$this->query instanceof WhereInterface) {
            return $this;
        }

        $method = $type === WhereType::AND ? 'where' : 'orWhere';

        $this->query->{$method}($condition, $params);

        return $this;
    }

    public function toCountQuery(): QueryBuilderInterface
    {
        $_query = clone $this;

        $_query->resetFields();
        $_query->limit(1);
        $_query->fields(['COUNT(*) AS count']);
        $_query->resetOrderBy();
        $_query->resetSubQueryFields();

        return $_query;
    }

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

    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 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) {
            foreach ($row as $col => $value) {

                // Only upsert() opts in to ON DUPLICATE KEY UPDATE. Plain
                // insert() must NOT get this clause — historically it was
                // added unconditionally for every column, which made MySQL
                // swallow unique-key collisions as silent full-row upserts
                // (no error ever reached PDO, so even ERRMODE_EXCEPTION
                // couldn't surface them) and let a colliding insert
                // overwrite an existing row's data.
                if ($this->upsertColumns !== null
                    && $this->query instanceof Insert
                    && ($this->upsertColumns === [] || in_array($col, $this->upsertColumns, true))
                ) {
                    // Backtick the column ref: reserved-word columns (`key`,
                    // `condition`, ...) otherwise 1064 inside VALUES() — our
                    // Quoter disables quoteNamesIn(), so Aura won't quote it.
                    $this->query->onDuplicateKeyUpdate($col, sprintf('VALUES(%s)', $this->maybeBacktick($col)));
                }

                if ($value instanceof Raw) {
                    $this->query->set($col, $value->value);


                    continue;
                }

                $this->query->col($col, $value);

            }
            $this->query->addRow();
        }

        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 resetOrderBy(): void
    {
        if ($this->query instanceof SelectInterface) {
            $this->query->resetOrderBy();
        }
    }

    public function resetSubQueryFields(): void
    {
        $this->subQueryFields = [];
    }

    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 DESC', $field),
                    sprintf('%s ASC', $field),
                ]),
                SortDirection::NULL_FIRST_DESC => $this->query->orderBy([
                    sprintf('%s IS NULL DESC', $field),
                    sprintf('%s DESC', $field),
                ]),
                SortDirection::NULL_LAST_ASC => $this->query->orderBy([
                    sprintf('%s IS NULL ASC', $field),
                    sprintf('%s ASC', $field),

                ]),
                SortDirection::NULL_LAST_DESC => $this->query->orderBy([
                    sprintf('%s IS NULL ASC', $field),
                    sprintf('%s DESC', $field),
                ]),
                SortDirection::NULL_FIRST => $this->query->orderBy([
                    sprintf('%s IS NULL DESC', $field),
                ]),
                SortDirection::NULL_LAST => $this->query->orderBy([
                    sprintf('%s IS NULL ASC', $field),
                ]),
            };
        }

        return $this;
    }

    protected function makeConnection(): PdoConnection
    {
        $dsn = sprintf('%s:host=%s', $this->schema, $this->host);

        //allow us to connect to a mysql server, but without a database if one isn't specified.
        if ($this->database) {
            $dsn = sprintf('%s;dbname=%s', $dsn, $this->database);
        }

        $pdo = new PdoConnection($dsn, $this->user, $this->pass, $this->options);

        // Force exception mode regardless of caller-supplied $options. Without
        // this, PDO defaults to ERRMODE_SILENT and constraint violations (e.g.
        // unique-key collisions) fail without a Throwable — execute()'s
        // try/catch below never fires, and callers get a "successful" result
        // for a write that never happened. sqlite driver already does this;
        // mysql never did.
        $pdo->setAttribute(PdoConnection::ATTR_ERRMODE, PdoConnection::ERRMODE_EXCEPTION);

        return $pdo;
    }
}
