<?php

namespace App\Repository\Prospect;

use App\Entity\Domain;
use App\Entity\Domain\MajesticTopic;
use App\Entity\Domain\TopicTrustFlow;
use App\Entity\Prospect;
use Cake\Chronos\Chronos;
use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Common\Collections\Criteria;
use Doctrine\ORM\OptimisticLockException;
use Doctrine\ORM\ORMException;
use Doctrine\ORM\Query\QueryException;
use Symfony\Bridge\Doctrine\RegistryInterface;

final class DoctrineProspectRepository extends ServiceEntityRepository implements ProspectRepositoryInterface
{
    public function __construct(RegistryInterface $registry)
    {
        parent::__construct($registry, Prospect::class);
    }

    public function findById(int $id): ?Prospect
    {
        return $this->findOneBy(['id' => $id]);
    }

    /**
     * @param Prospect $prospect
     *
     * @throws ORMException
     * @throws OptimisticLockException
     */
    public function save(Prospect $prospect): void
    {
        $this->_em->persist($prospect);
        $this->_em->flush();
    }

    /**
     * @param Prospect $prospect
     *
     * @throws ORMException
     * @throws OptimisticLockException
     */
    public function delete(Prospect $prospect): void
    {
        $this->_em->remove($prospect);
        $this->_em->flush();
    }

    public function count(array $criteria, array $orderBy = null, $limit = null, $offset = null)
    {
        $result = $this->queryByGrid($criteria);

        return count($result);
    }

    /**
     * @param array $criteria
     * @param null  $orderBy
     * @param null  $limit
     * @param null  $offset
     *
     * @return array
     *
     * @throws QueryException
     */
    private function queryByGrid(array $criteria, $orderBy = null, $limit = null, $offset = null): array
    {
        $newCriteria = $this->getCriteria($criteria, $orderBy, $limit, $offset);

        $queryBuilder = $this->createQueryBuilder('p');

        if (array_key_exists('topic', $criteria)) {
            $queryBuilder
                ->join(Domain::class, 'd', 'WITH', 'd.id = p.domain')
                ->join(TopicTrustFlow::class, 'ttf', 'WITH', 'ttf.domain = d.id')
                ->join(MajesticTopic::class, 'mt', 'WITH', 'mt.id = ttf.topic and mt.id in (:ids)')
                ->setParameter('ids', $criteria['topic']);
        }

        if (array_key_exists('TF', $criteria)) {
            $queryBuilder
                ->join('p.domain', 'd2')
                ->join('d2.stats', 'ds2')
                ->join('ds2.stat', 's2', 'with', 's2.symbol = :symbol1')
                ->setParameter('symbol1', 'TF');
            $newCriteria->andWhere(
                Criteria::expr()->orX(
                    Criteria::expr()->isNull('ds2.value'),
                    Criteria::expr()->andX(
                        Criteria::expr()->gte('ds2.value', $criteria['TF'][0]),
                        Criteria::expr()->lte('ds2.value', $criteria['TF'][1])
                    )
                )
            );
        }

        if (array_key_exists('CF', $criteria)) {
            $queryBuilder
                ->join('p.domain', 'd3')
                ->join('d3.stats', 'ds3')
                ->join('ds3.stat', 's3', 'with', 's3.symbol = :symbol2')
                ->setParameter('symbol2', 'CF');
            $newCriteria->andWhere(
                Criteria::expr()->orX(
                    Criteria::expr()->isNull('ds3.value'),
                    Criteria::expr()->andX(
                        Criteria::expr()->gte('ds3.value', $criteria['CF'][0]),
                        Criteria::expr()->lte('ds3.value', $criteria['CF'][1])
                    )
                )
            );
        }

        if (array_key_exists('DA', $criteria)) {
            $queryBuilder
                ->join('p.domain', 'd4')
                ->join('d4.stats', 'ds4')
                ->join('ds4.stat', 's4', 'with', 's4.symbol = :symbol3')
                ->setParameter('symbol3', 'DA');
            $newCriteria->andWhere(
                Criteria::expr()->orX(
                    Criteria::expr()->isNull('ds4.value'),
                    Criteria::expr()->andX(
                        Criteria::expr()->gte('ds4.value', $criteria['DA'][0]),
                        Criteria::expr()->lte('ds4.value', $criteria['DA'][1])
                    )
                )
            );
        }

        if (array_key_exists('category', $criteria)) {
            $queryBuilder
                ->innerJoin('p.categories', 'c');
            $newCriteria->andWhere(Criteria::expr()->in('c.id', $criteria['category']));
        }

        $queryBuilder->addCriteria($newCriteria);

        return $queryBuilder->getQuery()->getResult();
    }

    private function getCriteria(array $criteria, $orderBy = null, $limit = null, $offset = null): Criteria
    {
        $newCriteria = Criteria::create()
            ->setFirstResult($offset)
            ->setMaxResults($limit);

        if ($orderBy) {
            $newCriteria->orderBy($orderBy);
        }

        if (array_key_exists('q', $criteria)) {
            $newCriteria->where(Criteria::expr()->contains('name', $criteria['q']))
                ->orWhere(Criteria::expr()->contains('phone', $criteria['q']))
                ->orWhere(Criteria::expr()->contains('mobile', $criteria['q']))
                ->orWhere(Criteria::expr()->contains('email', $criteria['q']))
                ->orWhere(Criteria::expr()->contains('paypalEmail', $criteria['q']));
        }

        if (array_key_exists('domain', $criteria)) {
            $newCriteria->andWhere(Criteria::expr()->in('domain', $criteria['domain']));
        }

        if (array_key_exists('status', $criteria)) {
            $newCriteria->andWhere(Criteria::expr()->in('status', $criteria['status']));
        }

        if (array_key_exists('score', $criteria)) {
            $newCriteria->andWhere(
                Criteria::expr()->orX(
                    Criteria::expr()->andX(
                        Criteria::expr()->gte('p.score', $criteria['score'][0]),
                        Criteria::expr()->lte('p.score', $criteria['score'][1])),
                        Criteria::expr()->isNull('p.score')
                )
            );
        }

        return $newCriteria;
    }

    public function findAll()
    {
        return $this->findBy([], ['createdAt' => 'DESC']);
    }

    public function findBy(array $criteria, array $orderBy = null, $limit = null, $offset = null)
    {
        return $this->queryByGrid($criteria, $orderBy, $limit, $offset);
    }

    public function getByEmail(string $email)
    {
        return $this->findOneBy(['email' => $email]);
    }

    public function getTypes(): array
    {
        $queryBuilder = $this->createQueryBuilder('p');
        $queryBuilder->select('p.type');
        $queryBuilder->distinct('p.type');

        return $queryBuilder->getQuery()->getResult(2);
    }

    public function getNotUpdatedLastMonth()
    {
        $queryBuilder = $this->createQueryBuilder('p');

        $queryBuilder->join(Domain::class, 'd', 'WITH', 'p.domain = d.id');

        $from = Chronos::create()->subMonth(1)->endOfDay();

        $criteria = Criteria::create()
            ->where(Criteria::expr()->lte('d.checkedAt', $from->toDateTimeString()))
            ->orWhere(Criteria::expr()->isNull('d.checkedAt'))
            ->setMaxResults(10)
            ->orderBy(['d.checkedAt' => 'asc']);

        $query = $queryBuilder->addCriteria($criteria)->getQuery();

        return $query->getResult();
    }
}