<?php

namespace App\Repository\Campaign\Keyword;

use App\Entity\Campaign\Keyword;
use App\Entity\Campaign\Keyword\Check;
use App\Service\Campaign\KeywordRepositoryInterface;
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 Exception;
use Symfony\Bridge\Doctrine\RegistryInterface;

final class DoctrineKeywordRepository extends ServiceEntityRepository implements KeywordRepositoryInterface
{
    public function __construct(RegistryInterface $registry)
    {
        parent::__construct($registry, Keyword::class);
    }

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

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

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

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

        return array_values($result);
    }

    /**
     * @param array|null $orderBy
     * @param int|null $limit
     * @param int|null $offset
     */
    private function queryByGrid(array $criteria, ?array $orderBy = null, ?int $limit = null, ?int $offset = null): array
    {
        $queryBuilder = $this->createQueryBuilder('k');

        $newCriteria = Criteria::create()
            ->setFirstResult($offset)
            ->setMaxResults($limit);

        $queryBuilder
            ->leftJoin('k.latestCheck', 'c');

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

        if (array_key_exists('q', $criteria)) {
            $newCriteria->where(Criteria::expr()->contains('title', $criteria['q']));
        }

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

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

        if (array_key_exists('position', $criteria) || array_key_exists('cpc', $criteria) || (is_array($orderBy) &&
                array_key_exists('diff',
                    $orderBy))) {
            if (array_key_exists('position', $criteria)) {
                $newCriteria->andWhere(
                    Criteria::expr()->orX(
                        Criteria::expr()->andX(
                            Criteria::expr()->gte('c.position', $criteria['position'][0]),
                            Criteria::expr()->lte('c.position', $criteria['position'][1])),
                        Criteria::expr()->andX(
                            Criteria::expr()->isNull('c.position')
                        )
                    ));
            }

            if (array_key_exists('cpc', $criteria)) {
                $newCriteria->andWhere(
                    Criteria::expr()->orX(
                        Criteria::expr()->andX(
                            Criteria::expr()->gte('c.googleAdWordsCPC', $criteria['cpc'][0]),
                            Criteria::expr()->lte('c.googleAdWordsCPC', $criteria['cpc'][1])),
                        Criteria::expr()->isNull('c.googleAdWordsCPC'),
                        Criteria::expr()->eq('c.googleAdWordsCPC', 'n/a')
                    ));
            }

            if (is_array($orderBy) && array_key_exists('diff', $orderBy)) {
                $newCriteria->orderBy(['c.diff30' => $orderBy['diff']]);
            }
        }

        try {
            $queryBuilder->addCriteria($newCriteria);
        } catch (QueryException $e) {
            return [];
        }

        $query = $queryBuilder->getQuery();

        return $query->getResult(1);
    }

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

        return count($result);
    }

    public function getLowestCPCValue()
    {
        try {
            return $this->createQueryBuilder('keyword')
                ->innerJoin(Check::class, 'c', 'WITH', 'c.keyword = keyword.id')
                ->addCriteria((new Criteria())->where(Criteria::expr()->neq('c.googleAdWordsCPC', 'n/a')))
                ->select('c.googleAdWordsCPC')
                ->orderBy('c.googleAdWordsCPC', 'asc')
                ->setMaxResults(1)
                ->getQuery()
                ->getSingleScalarResult();
        } catch (Exception $exception) {
            return null;
        }
    }

    public function getHighestCPCValue()
    {
        try {
            return $this->createQueryBuilder('keyword')
                ->innerJoin(Check::class, 'c', 'WITH', 'c.keyword = keyword.id')
                ->addCriteria((new Criteria())->where(Criteria::expr()->neq('c.googleAdWordsCPC', 'n/a')))
                ->select('c.googleAdWordsCPC')
                ->orderBy('c.googleAdWordsCPC', 'desc')
                ->setMaxResults(1)
                ->getQuery()
                ->getSingleScalarResult();
        } catch (Exception $exception) {
            return null;
        }
    }
}