<?php

declare(strict_types=1);

namespace apexl\Io\services;

use apexl\Io\collections\LoaderConditionCollection;
use apexl\Io\collections\LoaderSortCollection;
use apexl\Io\dto\LoaderCondition;
use apexl\Io\dto\LoaderSort;
use apexl\Io\enums\EntityFinderJsonType;
use apexl\Io\factories\LoaderArgumentResolverFactory;
use apexl\Io\includes\Entity;
use apexl\Io\includes\HookManager;
use apexl\Io\interfaces\LoaderConditionInterface;
use apexl\Io\modules\tabulator\Services\TabulatorFieldRenamer;
use apexl\Vault\interfaces\driver;
use apexl\Vault\Vault;
use app\vendor\apexl\vault\src\Vault\enums\HavingType;
use Arrayy\Arrayy;
use Arrayy\Type\StringCollection;
use DI\DependencyException;
use DI\NotFoundException;


final readonly class EntityFinderService
{
    public final const string HOOK__QUERY = 'entity_finder.query';
    private LoaderSortCollection $sorts;
    private Arrayy $relations;
    private LoaderConditionCollection $conditions;
    private ?int $limit;
    private int $offset;
    private driver $query;
    private string $table;
    private StringCollection $dataFields;
    private StringCollection $virtual;
    private StringCollection $rawFields;

    public function __construct(
        private Entity $entity,
        private Vault $db,
        private EntityFinderJsonType $json = EntityFinderJsonType::NONE,
        private TabulatorFieldRenamer $tabulatorFieldRenamer,
        private HookManager $hookManager,
        private LoaderArgumentResolverFactory $loaderArgumentResolverFactory,
    ) {}

    /**
     * @throws DependencyException
     * @throws NotFoundException
     */
    public function query(
        array|LoaderConditionCollection $conditions = [],
        ?int $limit = 20,
        int $offset = 0,
        string|array|LoaderSort|LoaderSortCollection $sorts = [],
        string|array|StringCollection $fields = ['*'],
        bool $group = false,
        bool $autoIncludePrimaryKey = true,
        ?string $tableAlias = null,
    ): driver {
        $this->table = $tableAlias ?? $this->entity->getTableName();

        $loaderArgumentResolver = $this->loaderArgumentResolverFactory->make(
            $this->entity,
            $this->table,
            $fields,
        );

        $this->dataFields = $loaderArgumentResolver->resolveDataFields($autoIncludePrimaryKey);
        $this->virtual = $loaderArgumentResolver->resolveVirtualFields();
        $this->relations = $loaderArgumentResolver->resolveRelations();
        $this->rawFields = $loaderArgumentResolver->rawFields;

        $this->sorts = $loaderArgumentResolver->resolveSorts($sorts);
        $this->conditions = $loaderArgumentResolver->resolveConditions($conditions);
        $this->offset = $offset;
        $this->limit = $loaderArgumentResolver->resolveLimit($limit, $offset);

        $this->initialiseQuery($tableAlias);

        $this->applyLimit();
        $this->applySorts();
        $this->applyConditions();

        $fields = $this->fields();
        $this->query->resetFields();
        $this->query->fields($fields);

        if ($group) {
            $this->query->groupBy(sprintf('%s.%s', $this->table, $this->entity->getPrimaryKey()));
        }

        $this->hookManager->processMany(
            [
                Entity::entityHook(EntityFinderService::HOOK__QUERY, $this->entity::class),
                EntityFinderService::HOOK__QUERY,
            ],
            $this->query,
            $this->entity,
            $tableAlias,
        );

        return $this->query;
    }

    private function initialiseQuery(?string $tableAlias = null): void
    {
        $this->query = $this->db
            ->newQuery()
            ->select($tableAlias ? sprintf('%s %s', $this->entity->getTableName(), $tableAlias) : $this->table)
            ->fields('*');
    }

    private function fields(): array
    {
        $relations = $this->relations->toArray();
        foreach ($relations as $subQuery) {
            $this->query->addSubQueryFields($subQuery->query->getBindValues());
        }
        $relations = array_map(fn(driver $subQuery) => sprintf('(%s)', $subQuery->query->getStatement()), $relations);
        $relations = array_flip($relations);
        $relations = array_map(fn(string $field): string => sprintf('`%s`', $field), $relations);

        if ($this->json === EntityFinderJsonType::NONE) {
            return [
                ...$this->dataFields->toArray(),
                ...$this->virtual->toArray(),
                ...$this->rawFields->toArray(),
                ...$relations,
            ];
        }

        $output = [];

        foreach ($this->dataFields->toArray() as $field) {
            $output[] = sprintf('\'%s\'', implode('.', array_slice(explode('.', $field), 1)));
            $output[] = $field;
        }

        foreach ($this->rawFields->toArray() as $field => $key) {
            $output[] = sprintf('\'%s\'', trim($key, '`'));
            $output[] = $field;
        }

        foreach ($this->virtual->toArray() as $field => $key) {
            $output[] = sprintf('\'%s\'', trim($key, '`'));
            $output[] = $field;
        }

        foreach ($relations as $field => $key) {
            $output[] = sprintf('\'%s\'', trim($key, '`'));
            $output[] = $field;
        }

        $select = sprintf('JSON_OBJECT(%s)', implode(', ', $output));

        if ($this->json === EntityFinderJsonType::MANY) {
            $select = sprintf(
                'JSON_ARRAYAGG(DISTINCT %s %s)',
                $select,
                $this->sorts->toSql()
            );
        }

        return [$select => 'json'];
    }

    private function applyLimit(): void
    {
        if ($this->limit === null) {
            return;
        }

        if ($this->limit || $this->offset) {
            $this->query->limit($this->limit, $this->offset);
        }
    }

    private function applySorts(): void
    {
        if ($this->json === EntityFinderJsonType::MANY) {
            return;
        }

        foreach ($this->sorts as $sort) {
            $field = $sort->field;
            $relation = null;

            if ($this->entity->isRelationField($sort->field)) {

                $relation = str_contains($sort->field, '__') ? $this->entity->relation(
                    strstr($sort->field, '__', true)
                ) : false;

                [$field] = array_reverse(explode('__', $sort->field));
            }

            if ($relation) {
                $_field = substr(strstr($sort->field, '__'), 2);
                $field = sprintf('(%s)', $relation->subSelectStringSingleField($_field));

                $this->query->orderBy($field, $sort->direction);
            } elseif ($this->entity->isVirtualField($sort->field)) {
                $field = $this->entity->virtualFields()->get($sort->field);
                $this->query->orderBy(sprintf('(%s)', $field->getSql()), $sort->direction);
            } elseif (str_starts_with($field, '(')) {
                $this->query->orderBy(sprintf('%s', $field), $sort->direction);
            } else {
                $field = str_contains($field, '.') ? $field : sprintf('%s.%s', $this->table, $field);
                $this->query->orderBy($field, $sort->direction);
            }
        }
    }

    private function applyConditions(): void
    {
        if ($this->conditions->isEmpty()) {
            return;
        }

        $condition = $this->mapCondition($this->conditions);

        if ($condition->shouldWrap()) {
            match (true) {
                ($condition->type ?? null) instanceof HavingType => $this->query->startHavingGroup(),
                default => $this->query->startWhereGroup(),
            };
        }

        $condition->applyToQuery($this->query);

        if ($condition->shouldWrap()) {
            match (true) {
                ($condition->type ?? null) instanceof HavingType => $this->query->endHavingGroup(),
                default => $this->query->endWhereGroup(),
            };
        }
    }

    private function mapCondition(LoaderConditionInterface $condition): LoaderConditionInterface
    {
        if ($condition instanceof LoaderConditionCollection) {
            $mapped = $condition->map($this->mapCondition(...));
            $mapped->setType($condition->whereType());
            $mapped->setShouldWrap($condition->shouldWrap());

            return $mapped;
        }

        /** @var LoaderCondition $condition */

        if ($this->entity->isRelationField($condition->column)) {
            $relation = $this->entity->relation($condition->column);
            $column = $this->tabulatorFieldRenamer->rename($condition->column);
            [$field] = array_reverse(explode('__', $column));

            return $condition->with(
                'column',
                sprintf(
                    '(%s)',
                    $relation->subSelectStringSingleField(
                        $field,
                    )
                )
            );
        }

        return $condition;
    }
}
