<?php

namespace NoahVet\Reef\Repository;

use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\NonUniqueResultException;
use Doctrine\ORM\NoResultException;
use Doctrine\ORM\Query\Expr;
use Doctrine\ORM\Query\Expr\Join as DoctrineJoin;
use Doctrine\ORM\QueryBuilder;
use NoahVet\Reef\Domain\Tool\ArrayTool;
use Symfony\Component\Uid\Uuid;

/**
 * @template EntityType of object
 *
 * @extends ServiceEntityRepository<EntityType>
 *
 * @implements BaseRepositoryInterface<EntityType>
 */
abstract class AbstractBaseRepository extends ServiceEntityRepository implements BaseRepositoryInterface
{
    public const FROM = 'from';
    public const NULL = 'null';
    public const TO = 'to';

    protected string $alias = 'e';

    protected string $locale;

    /**
     * @var array<string, array<int, mixed>>
     */
    protected array $filters = [];

    /**
     * @var array<string, string>
     */
    protected array $sorting = [];

    /**
     * @var array<string, array<Expr\Andx|Expr\Orx|Expr\Comparison|string>>
     */
    protected array $qbFilterConditions = [];

    /**
     * @var array<string, array<Expr\Andx|Expr\Orx|Expr\Comparison|string>>
     */
    protected array $qbSortingConditions = [];

    public function countOnId(?QueryBuilder $qb = null): int
    {
        $qb ??= $this->createQueryBuilder($this->alias);

        try {
            return (int) $qb
                ->select('count(DISTINCT '.$this->alias.'.id)')
                ->getQuery()
                ->getSingleScalarResult()
            ;
        } catch (NoResultException|NonUniqueResultException $e) {
            return 0;
        }
    }

    /**
     * @return array<int, EntityType>
     */
    public function findPaginated(int $page, int $limit, ?QueryBuilder $qb = null): array
    {
        $qb ??= $this->createQueryBuilder($this->alias);

        $qb
            ->setFirstResult($page * $limit)
            ->setMaxResults($limit)
        ;

        return $this->findAllResult($qb);
    }

    /**
     * @return array<int, EntityType>
     */
    public function findAllResult(?QueryBuilder $qb = null): array
    {
        $qb ??= $this->createQueryBuilder($this->alias);

        return $qb
            ->addGroupBy($this->alias)
            ->getQuery()
            ->getResult()
        ;
    }

    /**
     * @param array<string, array<int, mixed>> $permissions
     * @param array<string, array<int, mixed>> $filters
     * @param array<string, string>            $sorting
     */
    public function prepareQuery(
        array $permissions = [],
        array $filters = [],
        array $sorting = [],
        string $keywords = '',
        ?QueryBuilder $qb = null,
    ): QueryBuilder {
        $this->filters = $filters;
        $this->sorting = $sorting;

        $qb ??= $this->createQueryBuilder($this->alias);
        $this
            ->applyPermissionIdNative($qb, $permissions)
            ->applyFilterIdNative($qb)
            ->applyFromToNativeFilters($qb)
            ->applyNativeEqualsFilters($qb)
            ->applyNativeSorting($qb)
            ->applyDefaultSorting($qb)
        ;

        unset($this->filters);

        foreach ($this->qbFilterConditions as $qbFilterCondition) {
            $qb->andWhere($qb->expr()->orX(...$qbFilterCondition));
        }

        return $qb;
    }

    /**
     * @return $this
     */
    public function setLocale(string $locale): self
    {
        $this->locale = $locale;

        return $this;
    }

    /**
     * If the values are "ids", they must be converted to binary so that Doctrine can compare values correctly.
     *
     * @param array<int, mixed> $nativeFilterValue
     *
     * @return array<int, mixed>
     */
    public function convertUuidFilterValues(string $nativeFilterKey, array $nativeFilterValue): array
    {
        return \array_map(
            static function ($value) {
                if (\is_string($value) && Uuid::isValid($value)) {
                    return Uuid::fromRfc4122($value)->toBinary();
                }

                return $value;
            },
            $nativeFilterValue,
        );
    }

    public function filterByJsonEntitled(QueryBuilder $qb): QueryBuilder
    {
        return $this->filterByTranslatableJson($qb, 'entitled');
    }

    public function filterByTranslatableJson(QueryBuilder $qb, string $field): QueryBuilder
    {
        if (isset($this->filters[$field])) {
            $increment = 0;

            foreach (\array_filter($this->filters[$field], static fn ($val) => 'null' !== $val) as $el) {
                $incrementString = $field.'Value'.$increment++;
                $this->qbFilterConditions[$field][] = $qb->expr()->eq(
                    'JSON_EXTRACT('.$this->alias.'.translations, :'.$field.'Path)',
                    ':'.$incrementString,
                );

                $qb->setParameter($field.'Path', '$.'.$this->locale.'.'.$field);
                $qb->setParameter($incrementString, $el);
            }

            unset($this->filters[$field]);
        }

        return $qb;
    }

    /**
     * @return $this
     */
    protected function sortByTranslatableField(
        QueryBuilder $qb,
        string $field,
    ): self {
        $fieldName = 'sort'.\ucfirst($field);

        if (!isset($this->sorting[$fieldName])) {
            return $this;
        }
        $direction = $this->sorting[$fieldName];

        $pathParam = $field.'Path';
        $valueExpr = 'JSON_UNQUOTE(JSON_EXTRACT('.$this->alias.'.translations, :'.$pathParam.'))';

        $qb->addOrderBy($valueExpr, $direction);
        $qb->setParameter($pathParam, '$.'.$this->locale.'.'.$field);

        unset($this->sorting[$fieldName]);

        return $this;
    }

    /**
     * @param array<string, mixed> $whitelist
     * @param array<string, mixed> $mappingKeys
     *
     * @return $this
     */
    protected function recursiveWhitelist(
        array $whitelist,
        QueryBuilder $qb,
        array $mappingKeys = [],
        ?string $entity = null,
    ): self {
        foreach ($whitelist as $whitelistKey => $whitelistElement) {
            if (!\is_array($whitelistElement)) {
                $qb->addSelect(
                    null === $entity
                        ? $this->alias.'.'.$whitelistElement
                        : $entity.'.'.$whitelistElement.' AS '.$entity.'_'.$whitelistElement,
                );
            }
        }

        $entity ??= $this->alias;

        foreach ($whitelist as $whitelistKey => $whitelistElement) {
            if (\is_array($whitelistElement)) {
                $entity .= '.'.$whitelistKey;

                $qb->leftJoin(
                    $entity,
                    $whitelistKey,
                    DoctrineJoin::WITH,
                    $entity.' = '.$whitelistKey.'.id',
                );
                $this->recursiveWhitelist(
                    $whitelistElement,
                    $qb,
                    $mappingKeys,
                    $entity,
                );
            }
        }

        return $this;
    }

    /**
     * @return $this
     */
    protected function filterBy(
        QueryBuilder $qb,
        string $filterName,
        string $fieldName,
        ?string $alias = null,
    ): self {
        $filterNameWithField = $filterName;
        if (!\preg_match(\sprintf('/%s/i', $fieldName), $filterName)) {
            $filterNameWithField .= \ucfirst($fieldName);
        }

        $filterParameterName = $filterNameWithField.'s';

        if (ArrayTool::isArrayExist($this->filters, $filterNameWithField)) {
            $values = $this->convertUuidFilterValues($filterNameWithField, $this->filters[$filterNameWithField]);

            $qb
                ->andWhere($qb->expr()->in($alias ?? $filterName.'.'.$fieldName, ':'.$filterParameterName))
                ->setParameter($filterParameterName, $values)
            ;
            unset($this->filters[$filterNameWithField]);
        }

        return $this;
    }

    /**
     * @return $this
     */
    protected function filterById(
        QueryBuilder $qb,
        string $filterName,
        ?string $alias = null,
    ): self {
        return $this->filterBy($qb, $filterName, 'id', $alias);
    }

    /**
     * @return $this
     */
    protected function filterByFromTo(
        QueryBuilder $qb,
        ?string $alias = null,
    ): self {
        foreach ($this->filters as $filterName => $filterVal) {
            $isFrom = \str_starts_with($filterName, self::FROM);
            $isTo = \str_starts_with($filterName, self::TO);

            if ($isFrom || $isTo) {
                $this->addFromToQbFilterConditions($filterName, $filterVal, $isFrom, $qb, $alias);
            }
        }

        return $this;
    }

    /**
     * @return $this
     */
    protected function filterByKeyOnJsonArrayColumn(
        QueryBuilder $qb,
        string $column,
        string $searchKey,
        ?string $pluralizedColumn = null,
    ): self {
        if (isset($this->filters[$column])) {
            $increment = 0;
            $pluralizedColumn ??= $column.'s';
            foreach (\array_filter($this->filters[$column], static fn ($val) => 'null' !== $val) as $el) {
                $incrementString = $column.$increment++;
                $this->qbFilterConditions[$column][] = $qb->expr()->orX(
                    \sprintf(
                        "JSON_SEARCH ( %s.%s, 'all', :%s, NULL, '$[*].%s' ) IS NOT NULL",
                        $this->alias,
                        $pluralizedColumn,
                        $incrementString,
                        $searchKey,
                    ),
                );
                $qb->setParameter($incrementString, $el);
            }

            unset($this->filters[$column]);
        }

        return $this;
    }

    /**
     * @param array<int, mixed> $filterVal
     *
     * @return $this
     */
    protected function addFromToQbFilterConditions(
        string $filterName,
        array $filterVal,
        bool $isFrom,
        QueryBuilder $qb,
        ?string $alias = null,
    ): self {
        $alias ??= $this->alias;

        $fieldName = \lcfirst(
            \str_replace(
                $isFrom ? self::FROM : self::TO,
                '',
                $filterName,
            ),
        );

        if (\in_array(self::NULL, $filterVal)) {
            $this->qbFilterConditions[$filterName][] = $qb->expr()->isNull($alias.'.'.$fieldName);
        }

        $increment = 0;

        foreach (\array_filter($filterVal, static fn ($val) => self::NULL !== $val) as $el) {
            $incrementString = $filterName.$increment++;

            if ($isFrom) {
                $expr = $qb->expr()->gte($alias.'.'.$fieldName, ':'.$incrementString);
            } else {
                $expr = $qb->expr()->lte($alias.'.'.$fieldName, ':'.$incrementString);
            }
            $this->qbFilterConditions[$filterName][] = $expr;

            $qb->setParameter($incrementString, $el);
        }

        unset($this->filters[$filterName]);

        return $this;
    }

    protected function applyDefaultSorting(QueryBuilder $qb): void
    {
        if (!\str_contains($qb->getDQL(), 'ORDER BY')) {
            $qb->addOrderBy($this->alias.'.createdDate', 'DESC');
        }
    }

    /**
     * @param array<int|string, array<int, string>|string> $fields
     *                                                             E.g.
     *
     * [
     *     'key1' => [
     *         'subKey1',
     *     ],
     *     'key2',
     * ]
     *
     * @return $this
     */
    protected function prepareLeftJoins(QueryBuilder $qb, array $fields): self
    {
        $data = [];

        $this->recursivePrepareJoins($qb, $fields, $data);

        /** @var string $datum */
        foreach ($data as $datumKey => $datum) {
            $qb
                ->leftJoin(
                    $datumKey,
                    $datum,
                )
            ;
        }

        return $this;
    }

    /**
     * @return $this
     */
    protected function filterByTranslatableField(
        QueryBuilder $qb,
        string $field,
    ): self {
        if (ArrayTool::isArrayExist($this->filters, $field)) {
            $increment = 0;
            foreach (\array_filter($this->filters[$field], static fn ($val) => 'null' !== $val) as $el) {
                $incrementString = 'value'.$increment++;
                $this->qbFilterConditions[$field][] = $qb->expr()->eq(
                    'JSON_EXTRACT('.$this->alias.'.translations, :'.$field.'Path)',
                    ':'.$incrementString,
                );
                $qb->setParameter($field.'Path', '$.'.$this->locale.'.'.$field);
                $qb->setParameter($incrementString, $el);
            }
            unset($this->filters[$field]);
        }

        return $this;
    }

    /**
     * @param array<int|string, array<int, string>|string> $fields
     * @param array<string, int|string>                    $data
     *
     * @param-out array<string, int|string>                $data
     *                                                             E.g.
     *
     * [
     *     "selfEntity.entity1" => "entity1"
     *     "entity1.entity2" => "entity2"
     *     "selfEntity.entity3" => "entity3"
     * ]
     */
    protected function recursivePrepareJoins(
        QueryBuilder $qb,
        array $fields,
        array &$data = [],
        string $node = '',
    ): void {
        foreach ($fields as $fieldKey => $field) {
            $currentKey = \is_array($field) ? $fieldKey : $field;
            $data[('' === $node ? $this->alias : $node).'.'.$currentKey] = $currentKey;
            $qb->addSelect($currentKey);

            if (\is_array($field) && \is_string($currentKey)) {
                $this->recursivePrepareJoins($qb, $field, $data, $currentKey);
            }
        }
    }

    /**
     * @return $this
     */
    protected function applyFilterIdNative(QueryBuilder $qb): self
    {
        if (isset($this->filters['id'])) {
            $qb
                ->andWhere($qb->expr()->in($this->alias.'.id', ':filterIds'))
                ->setParameter(
                    'filterIds',
                    \array_map(static fn ($id) => Uuid::fromRfc4122($id)->toBinary(), $this->filters['id']),
                )
            ;
            unset($this->filters['id']);
        }

        return $this;
    }

    /**
     * @return $this
     */
    protected function applyFromToNativeFilters(QueryBuilder $qb): self
    {
        foreach ($this->filters as $filterName => $filterVal) {
            $isFrom = \str_starts_with($filterName, self::FROM);
            $isTo = \str_starts_with($filterName, self::TO);

            if ($isFrom || $isTo) {
                $this->addFromToQbFilterConditions($filterName, $filterVal, $isFrom, $qb);
            }
        }

        return $this;
    }

    /**
     * @return $this
     */
    protected function applyNativeEqualsFilters(QueryBuilder $qb): self
    {
        foreach ($this->filters as $nativeFilterKey => $nativeFilterValue) {
            $increment = 0;
            $nativeFilterValue = $this->convertUuidFilterValues($nativeFilterKey, $nativeFilterValue);

            foreach (\array_filter($nativeFilterValue, static fn ($val) => 'null' !== $val) as $el) {
                $incrementString = $nativeFilterKey.$increment++;
                $this->qbFilterConditions[$nativeFilterKey][] = $qb->expr()->eq(
                    $this->alias.'.'.$nativeFilterKey,
                    ':'.$incrementString,
                );
                if ('true' === $el || 'false' === $el) {
                    $el = \filter_var($el, \FILTER_VALIDATE_BOOLEAN);
                }
                $qb->setParameter($incrementString, $el);
            }
        }

        return $this;
    }

    /**
     * @return $this
     */
    protected function applyNativeSorting(QueryBuilder $qb): self
    {
        foreach ($this->sorting as $sortingKey => $sortingValue) {
            $qb->addOrderBy(
                $this->alias.'.'.\lcfirst(\substr($sortingKey, 4)),
                $sortingValue,
            );
        }

        return $this;
    }

    /**
     * @param array<string, array<int, mixed>> $permissions
     *
     * @return $this
     */
    protected function applyPermissionIdNative(
        QueryBuilder $qb,
        array &$permissions,
    ): self {
        if (isset($permissions['id'])) {
            $qb
                ->andWhere($qb->expr()->in($this->alias.'.id', ':permissionIds'))
                ->setParameter(
                    'permissionIds',
                    \array_map(static fn ($id) => Uuid::fromRfc4122($id)->toBinary(), $permissions['id']),
                )
            ;
            unset($permissions['id']);
        }

        return $this;
    }
}
