<?php

declare(strict_types=1);

/*
 * Chill is a software for social workers
 *
 * For the full copyright and license information, please view
 * the LICENSE file that was distributed with this source code.
 */

namespace Chill\TicketBundle\Repository;

use Chill\PersonBundle\Entity\Person\PersonCenterHistory;
use Chill\TicketBundle\Entity\AddresseeHistory;
use Chill\TicketBundle\Entity\EmergencyStatusHistory;
use Chill\TicketBundle\Entity\MotiveHistory;
use Chill\TicketBundle\Entity\PersonHistory;
use Chill\TicketBundle\Entity\StateHistory;
use Chill\TicketBundle\Entity\Ticket;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\QueryBuilder;

final readonly class TicketACLAwareRepository implements TicketACLAwareRepositoryInterface
{
    public function __construct(private EntityManagerInterface $em) {}

    public function findTickets(array $params, int $start = 0, int $limit = 100): array
    {
        $query = $this->buildQuery($params)->select('t');

        // order by emergency. As the value can be 'yes' or 'no', we simply order by 'yes' first
        $query->addSelect(sprintf("'SELECT u.emergencyStatus FROM %s u WHERE u.ticket = t AND u.endDate IS NULL' AS HIDDEN emergencyStatus", EmergencyStatusHistory::class));
        $query->addOrderBy('emergencyStatus', 'DESC');

        // most recent tickets first
        $query->addOrderBy('t.createdAt', 'DESC');

        return $query->getQuery()
            ->setFirstResult($start)
            ->setMaxResults($limit)
            ->getResult();
    }

    public function countTickets(array $params): int
    {
        return $this->buildQuery($params)->select('COUNT(t)')->getQuery()->getSingleScalarResult();
    }

    private function buildQuery(array $params): QueryBuilder
    {
        $qb = $this->em->createQueryBuilder();
        $qb->from(Ticket::class, 't');
        // counter for all the loops
        $i = 0;

        if (array_key_exists('byTicketId', $params)) {
            $qb->andWhere($qb->expr()->in('t.id', ':byTicketId'));
            $qb->setParameter('byTicketId', $params['byTicketId']);

            return $qb;
        }

        if (array_key_exists('byPerson', $params)) {
            $or = $qb->expr()->orX();

            foreach ($params['byPerson'] as $person) {
                $or->add(
                    $qb->expr()->exists(sprintf(
                        'SELECT 1 FROM %s tp_%d WHERE tp_%d.ticket = t AND tp_%d.person = :person_%d AND tp_%d.endDate IS NULL',
                        PersonHistory::class,
                        ++$i,
                        $i,
                        $i,
                        $i,
                        $i,
                    ))
                );
                $qb->setParameter(sprintf('person_%d', $i), $person);
            }
            $qb->andWhere($or);
        }

        if (array_key_exists('byCurrentState', $params)) {
            $qb->andWhere(
                $qb->expr()->exists(sprintf(
                    'SELECT 1 FROM %s tp_state_%d WHERE tp_state_%d.ticket = t
                    AND tp_state_%d.state IN (:currentState) AND tp_state_%d.endDate IS NULL',
                    StateHistory::class,
                    ++$i,
                    $i,
                    $i,
                    $i,
                ))
            );
            $qb->setParameter('currentState', $params['byCurrentState']);
        }

        if (array_key_exists('byCurrentStateEmergency', $params)) {
            $qb->andWhere(
                $qb->expr()->exists(sprintf(
                    'SELECT 1 FROM %s tp_state_emergency_%d WHERE tp_state_emergency_%d.ticket = t
                    AND tp_state_emergency_%d.emergencyStatus IN (:currentStateEmergency) AND tp_state_emergency_%d.endDate IS NULL',
                    EmergencyStatusHistory::class,
                    ++$i,
                    $i,
                    $i,
                    $i,
                ))
            );
            $qb->setParameter('currentStateEmergency', $params['byCurrentStateEmergency']);
        }

        if (array_key_exists('byMotives', $params)) {
            $byMotives = $qb->expr()->orX();
            foreach ($params['byMotives'] as $motive) {
                $motivesWithDescendants = $motive->getDescendants()->toArray();
                $byMotives->add(
                    $qb->expr()->exists(sprintf(
                        'SELECT 1 FROM %s tp_motive_%d WHERE tp_motive_%d.ticket = t
                    AND tp_motive_%d.motive IN (:motives_%d) AND tp_motive_%d.endDate IS NULL
                    ',
                        MotiveHistory::class,
                        ++$i,
                        $i,
                        $i,
                        $i,
                        $i,
                    ))
                );
                $qb->setParameter(sprintf('motives_%d', $i), $motivesWithDescendants);
            }
            $qb->andWhere($byMotives);
        }

        if (array_key_exists('byCreatedAfter', $params)) {
            $qb->andWhere('t.createdAt >= :opening_after');
            $qb->setParameter('opening_after', $params['byCreatedAfter']);
        }

        if (array_key_exists('byCreatedBefore', $params)) {
            $qb->andWhere('t.createdAt <= :opening_before');
            $qb->setParameter('opening_before', $params['byCreatedBefore']);
        }

        if (array_key_exists('byCreator', $params)) {
            $qb->andWhere('t.createdBy IN (:creators)');
            $qb->setParameter('creators', $params['byCreator']);
        }

        if (array_key_exists('byAddressee', $params)) {
            $orx = $qb->expr()->orX();
            foreach ($params['byAddressee'] as $addressee) {
                $orx->add(
                    $qb->expr()->exists(sprintf(
                        'SELECT 1 FROM %s tp_addressee_%d WHERE tp_addressee_%d.ticket = t
                        AND tp_addressee_%d.endDate IS NULL AND tp_addressee_%d.addresseeUser = :addressee_%d',
                        AddresseeHistory::class,
                        ++$i,
                        $i,
                        $i,
                        $i,
                        $addresseeParam = $i,
                    ))
                );
                $orx->add(
                    $qb->expr()->exists(sprintf(
                        'SELECT 1 FROM %s tp_addressee_%d JOIN tp_addressee_%d.addresseeGroup group_%d WHERE tp_addressee_%d.ticket = t
                        AND tp_addressee_%d.endDate IS NULL AND :addressee_%d MEMBER OF group_%d.users',
                        AddresseeHistory::class,
                        ++$i,
                        $i,
                        $i,
                        $i,
                        $i,
                        $addresseeParam,
                        $i,
                    ))
                );
                $qb->setParameter(sprintf('addressee_%d', $addresseeParam), $addressee);
            }
            $qb->andWhere($orx);
        }

        if (array_key_exists('byAddresseeGroup', $params)) {
            $addresseeGroupOr = $qb->expr()->orX();
            foreach ($params['byAddresseeGroup'] as $addresseeGroup) {
                $addresseeGroupOr->add(
                    $qb->expr()->exists(sprintf(
                        'SELECT 1 FROM %s tp_addressee_%d JOIN tp_addressee_%d.addresseeGroup group_%d WHERE tp_addressee_%d.ticket = t
                        AND tp_addressee_%d.endDate IS NULL AND tp_addressee_%d.addresseeGroup = :addressee_%d',
                        AddresseeHistory::class,
                        ++$i,
                        $i,
                        $i,
                        $i,
                        $i,
                        $i,
                        $i,
                    ))
                );
                $qb->setParameter(sprintf('addressee_%d', $i), $addresseeGroup);
            }
            $qb->andWhere($addresseeGroupOr);
        }

        if (array_key_exists('byPersonCenter', $params)) {
            $orx = $qb->expr()->orX();
            foreach ($params['byPersonCenter'] as $userCenter) {
                $orx->add(
                    $qb->expr()->exists(sprintf(
                        'SELECT 1 FROM %s ticket_person_%d
                            JOIN %s person_center_%d WITH person_center_%d.person = ticket_person_%d.person AND TRUE = OVERLAPSI(ticket_person_%d.startDate, ticket_person_%d.startDate),(person_center_%d.startDate, person_center_%d.endDate)
                            WHERE ticket_person_%d.ticket = t AND ticket_person_%d.endDate IS NULL AND person_center_%d.center = :center_%d',
                        PersonHistory::class,
                        ++$i,
                        PersonCenterHistory::class,
                        $i,
                        $i,
                        $i,
                        $i,
                        $i,
                        $i,
                        $i,
                        $i,
                        $i,
                        $i,
                        $i,
                    ))
                );
                $qb->setParameter(sprintf('center_%d', $i), $userCenter);
            }
            $qb->andWhere($orx);
        }

        return $qb;
    }
}
