<?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\MainBundle\Repository;

use Chill\MainBundle\Entity\Notification;
use Chill\MainBundle\Entity\User;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Result;
use Doctrine\DBAL\Statement;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ObjectRepository;

final class NotificationRepository implements ObjectRepository
{
    private ?Statement $notificationByRelatedEntityAndUserAssociatedStatement = null;

    private readonly EntityRepository $repository;

    private const BASE_COUNTER_SQL = <<<'SQL'
                    SELECT
                   SUM((EXISTS (SELECT 1 AS c FROM chill_main_notification_addresses_unread cmnau WHERE user_id = :userid and cmnau.notification_id = cmn.id))::int) AS unread,
                   SUM((cmn.sender_id = :userid)::int) AS sent,
                   SUM((EXISTS (SELECT 1 AS c FROM chill_main_notification_addresses_user cmnau_all WHERE user_id = :userid and cmnau_all.notification_id = cmn.id))::int) + SUM((cmn.sender_id = :userid)::int) AS total
                FROM chill_main_notification cmn
    SQL;

    public function __construct(private readonly EntityManagerInterface $em)
    {
        $this->repository = $em->getRepository(Notification::class);
    }

    public function countAllForAttendee(User $addressee): int
    {
        return $this->queryByAddressee($addressee)
            ->select('count(n)')
            ->getQuery()
            ->getSingleScalarResult();
    }

    public function countAllForSender(User $sender): int
    {
        return $this->queryBySender($sender)
            ->select('count(n)')
            ->getQuery()
            ->getSingleScalarResult();
    }

    /**
     * @param list<array{relatedEntityClass: class-string, relatedEntityId: int}> $more
     *
     * @return array{unread: int, sent: int, total: int}
     */
    public function countNotificationByRelatedEntityAndUserAssociated(string $relatedEntityClass, int $relatedEntityId, User $user, array $more = []): array
    {
        $sqlParams = ['relatedEntityClass' => $relatedEntityClass, 'relatedEntityId' => $relatedEntityId, 'userid' => $user->getId()];

        if ([] === $more) {
            if (null === $this->notificationByRelatedEntityAndUserAssociatedStatement) {
                $sql = self::BASE_COUNTER_SQL.' WHERE relatedentityclass = :relatedEntityClass AND relatedentityid = :relatedEntityId AND sender_id IS NOT NULL';

                $this->notificationByRelatedEntityAndUserAssociatedStatement =
                    $this->em->getConnection()->prepare($sql);
            }

            $results = $this->notificationByRelatedEntityAndUserAssociatedStatement
                ->executeQuery($sqlParams);

            $result = $results->fetchAssociative();

            $results->free();
        } else {
            $wheres = [];
            foreach ([['relatedEntityClass' => $relatedEntityClass, 'relatedEntityId' => $relatedEntityId], ...$more] as $k => ['relatedEntityClass' => $relClass, 'relatedEntityId' => $relId]) {
                $wheres[] = "(relatedEntityClass = :relatedEntityClass_{$k} AND relatedEntityId = :relatedEntityId_{$k})";
                $sqlParams["relatedEntityClass_{$k}"] = $relClass;
                $sqlParams["relatedEntityId_{$k}"] = $relId;
            }

            $sql = self::BASE_COUNTER_SQL.' WHERE sender_id IS NOT NULL AND ('.implode(' OR ', $wheres).')';

            $result = $this->em->getConnection()->fetchAssociative($sql, $sqlParams);
        }

        return array_map(fn (?int $number) => $number ?? 0, $result);
    }

    public function countUnreadByUser(User $user): int
    {
        $sql = 'SELECT count(*) AS c FROM chill_main_notification_addresses_unread WHERE user_id = :userId';

        $rsm = new Query\ResultSetMapping();
        $rsm->addScalarResult('c', 'c', Types::INTEGER);

        $nq = $this->em->createNativeQuery($sql, $rsm)
            ->setParameter('userId', $user->getId());

        return $nq->getSingleScalarResult();
    }

    public function countUnreadByUserWhereAddressee(User $user): int
    {
        $qb = $this->repository->createQueryBuilder('n');
        $qb
            ->select('count(n)')
            ->where($qb->expr()->isMemberOf(':user', 'n.addressees'))
            ->andWhere($qb->expr()->isMemberOf(':user', 'n.unreadBy'))
            ->setParameter('user', $user);

        return $qb->getQuery()->getSingleScalarResult();
    }

    public function countUnreadByUserWhereSender(User $user): int
    {
        $qb = $this->repository->createQueryBuilder('n');
        $qb
            ->select('count(n)')
            ->where($qb->expr()->eq('n.sender', ':user'))
            ->andWhere($qb->expr()->isMemberOf(':user', 'n.unreadBy'))
            ->setParameter('user', $user);

        return $qb->getQuery()->getSingleScalarResult();
    }

    public function find($id, $lockMode = null, $lockVersion = null): ?Notification
    {
        return $this->repository->find($id, $lockMode, $lockVersion);
    }

    /**
     * @return Notification[]
     */
    public function findAll(): array
    {
        return $this->repository->findAll();
    }

    /**
     * @param mixed|null $limit
     * @param mixed|null $offset
     *
     * @return Notification[]
     */
    public function findAllForAttendee(User $addressee, $limit = null, $offset = null): array
    {
        $query = $this->queryByAddressee($addressee)->select('n');

        if ($limit) {
            $query = $query->setMaxResults($limit);
        }

        if ($offset) {
            $query = $query->setFirstResult($offset);
        }

        $query->addOrderBy('n.date', 'DESC');

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

    public function findAllForSender(User $sender, $limit = null, $offset = null): array
    {
        $query = $this->queryBySender($sender)->select('n');

        if ($limit) {
            $query = $query->setMaxResults($limit);
        }

        if ($offset) {
            $query = $query->setFirstResult($offset);
        }

        $query->addOrderBy('n.date', 'DESC');

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

    /**
     * @param int|null $limit
     * @param int|null $offset
     *
     * @return Notification[]
     */
    public function findBy(array $criteria, ?array $orderBy = null, $limit = null, $offset = null): array
    {
        return $this->repository->findBy($criteria, $orderBy, $limit, $offset);
    }

    /**
     * @param list<array{relatedEntityClass: class-string, relatedEntityId: int}> $more
     *
     * @return array|Notification[]
     */
    public function findNotificationByRelatedEntityAndUserAssociated(string $relatedEntityClass, int $relatedEntityId, User $user, array $more): array
    {
        return
            $this->buildQueryNotificationByRelatedEntityAndUserAssociated($relatedEntityClass, $relatedEntityId, $user, $more)
                ->select('n')
                ->addOrderBy('n.date', 'DESC')
                ->getQuery()
                ->getResult();
    }

    public function findOneBy(array $criteria, ?array $orderBy = null): ?Notification
    {
        return $this->repository->findOneBy($criteria, $orderBy);
    }

    /**
     * @return array|Notification[]
     */
    public function findUnreadByUser(User $user, int $limit = 20, int $offset = 0): array
    {
        $rsm = new Query\ResultSetMappingBuilder($this->em);
        $rsm->addRootEntityFromClassMetadata(Notification::class, 'cmn');

        $sql = 'SELECT '.$rsm->generateSelectClause(['cmn' => 'cmn']).' '.
            'FROM chill_main_notification cmn '.
            'WHERE '.
            'EXISTS (select 1 FROM chill_main_notification_addresses_unread cmnau WHERE cmnau.user_id = :userId and cmnau.notification_id = cmn.id) '.
            'ORDER BY cmn.date DESC '.
            'LIMIT :limit OFFSET :offset';

        $nq = $this->em->createNativeQuery($sql, $rsm)
            ->setParameter('userId', $user->getId())
            ->setParameter('limit', $limit)
            ->setParameter('offset', $offset);

        return $nq->getResult();
    }

    public function getClassName()
    {
        return Notification::class;
    }

    /**
     * @param list<array{relatedEntityClass: class-string, relatedEntityId: int}> $more
     */
    private function buildQueryNotificationByRelatedEntityAndUserAssociated(string $relatedEntityClass, int $relatedEntityId, User $user, array $more = []): QueryBuilder
    {
        $qb = $this->repository->createQueryBuilder('n');

        // add condition for related entity (in main arguments, and in more)
        $or = $qb->expr()->orX(
            $qb->expr()->andX(
                $qb->expr()->eq('n.relatedEntityClass', ':relatedEntityClass'),
                $qb->expr()->eq('n.relatedEntityId', ':relatedEntityId')
            )
        );
        $qb
            ->setParameter('relatedEntityClass', $relatedEntityClass)
            ->setParameter('relatedEntityId', $relatedEntityId);

        foreach ($more as $k => ['relatedEntityClass' => $relatedClass, 'relatedEntityId' => $relatedId]) {
            $or->add(
                $qb->expr()->andX(
                    $qb->expr()->eq('n.relatedEntityClass', ':relatedEntityClass_'.$k),
                    $qb->expr()->eq('n.relatedEntityId', ':relatedEntityId_'.$k)
                )
            );
            $qb
                ->setParameter('relatedEntityClass_'.$k, $relatedClass)
                ->setParameter('relatedEntityId_'.$k, $relatedId);
        }

        $qb
            ->andWhere($or)
            ->andWhere($qb->expr()->isNotNull('n.sender'))
            ->andWhere(
                $qb->expr()->orX(
                    $qb->expr()->isMemberOf(':user', 'n.addressees'),
                    $qb->expr()->eq('n.sender', ':user')
                )
            )
            ->setParameter('user', $user);

        return $qb;
    }

    private function queryByAddressee(User $addressee): QueryBuilder
    {
        $qb = $this->repository->createQueryBuilder('n');

        $qb
            ->leftJoin('n.addresseeUserGroups', 'aug')
            ->leftJoin('aug.users', 'ugu')
            ->where(
                $qb->expr()->orX(
                    $qb->expr()->isMemberOf(':addressee', 'n.addressees'),
                    $qb->expr()->eq('ugu.id', ':addressee')
                )
            )
            ->setParameter('addressee', $addressee);

        return $qb;
    }

    private function queryBySender(User $sender): QueryBuilder
    {
        $qb = $this->repository->createQueryBuilder('n');

        $qb
            ->where($qb->expr()->eq('n.sender', ':sender'))
            ->setParameter('sender', $sender);

        return $qb;
    }

    /**
     * @return list<int> the ids of the notifications marked as unread
     */
    public function markAllNotificationAsReadForUser(User $user): array
    {
        // Get the database connection from the entity manager
        $connection = $this->em->getConnection();

        /** @var Result $results */
        $results = $connection->transactional(function (Connection $connection) use ($user) {
            // Define the SQL query
            $sql = <<<'SQL'
            DELETE FROM chill_main_notification_addresses_unread
            WHERE user_id = :user_id
            RETURNING notification_id
            SQL;

            return $connection->executeQuery($sql, ['user_id' => $user->getId()]);
        });

        $notificationIdsTouched = [];
        foreach ($results->iterateAssociative() as $row) {
            $notificationIdsTouched[] = $row['notification_id'];
        }

        return array_values($notificationIdsTouched);
    }

    /**
     * @param list<int> $notificationIds
     */
    public function markAllNotificationAsUnreadForUser(User $user, array $notificationIds): array
    {
        // Get the database connection from the entity manager
        $connection = $this->em->getConnection();

        /** @var Result $results */
        $results = $connection->transactional(function (Connection $connection) use ($user, $notificationIds) {
            // This query double-check that the user is one of the addresses of the notification or the sender,
            // if the notification is already marked as unread, this query does not fails.
            // this query return the list of notification id which are affected
            $sql = <<<'SQL'
                INSERT INTO chill_main_notification_addresses_unread (user_id, notification_id)
                SELECT ?, chill_main_notification_addresses_user.notification_id
                FROM chill_main_notification_addresses_user JOIN chill_main_notification ON chill_main_notification_addresses_user.notification_id = chill_main_notification.id
                WHERE (chill_main_notification_addresses_user.user_id = ? OR chill_main_notification.sender_id = ?)
                    AND chill_main_notification_addresses_user.notification_id IN ({ notification_ids })
                ON CONFLICT (user_id, notification_id) DO NOTHING
                RETURNING notification_id
                SQL;

            $params = [$user->getId(), $user->getId(), $user->getId(), ...array_values($notificationIds)];
            $sql = strtr($sql, ['{ notification_ids }' => implode(', ', array_fill(0, count($notificationIds), '?'))]);

            return $connection->executeQuery($sql, $params);
        });

        $notificationIdsTouched = [];
        foreach ($results->iterateAssociative() as $row) {
            $notificationIdsTouched[] = $row['notification_id'];
        }

        return array_values($notificationIdsTouched);
    }

    public function findAllUnreadByUser(User $user): array
    {
        $rsm = new Query\ResultSetMappingBuilder($this->em);
        $rsm->addRootEntityFromClassMetadata(Notification::class, 'cmn');

        $sql = 'SELECT '.$rsm->generateSelectClause(['cmn' => 'cmn']).' '.
            'FROM chill_main_notification cmn '.
            'WHERE '.
            'EXISTS (SELECT 1 FROM chill_main_notification_addresses_unread cmnau WHERE cmnau.user_id = :userId AND cmnau.notification_id = cmn.id) '.
            'ORDER BY cmn.date DESC';

        $nq = $this->em->createNativeQuery($sql, $rsm)
            ->setParameter('userId', $user->getId());

        return $nq->getResult();
    }

    /**
     * Find all notifications for a user that were created between two dates.
     *
     * @return array|Notification[]
     */
    public function findNotificationsForUserBetweenDates(int $userId, \DateTimeInterface $startDate, \DateTimeInterface $endDate): array
    {
        $rsm = new Query\ResultSetMappingBuilder($this->em);
        $rsm->addRootEntityFromClassMetadata(Notification::class, 'cmn');

        $sql = 'SELECT '.$rsm->generateSelectClause(['cmn' => 'cmn']).' '.
            'FROM chill_main_notification cmn '.
            'JOIN chill_main_notification_addresses_user cmnau ON cmnau.notification_id = cmn.id '.
            'WHERE cmnau.user_id = :userId '.
            'AND cmn.date >= :startDate '.
            'AND cmn.date <= :endDate '.
            'ORDER BY cmn.date DESC';

        $nq = $this->em->createNativeQuery($sql, $rsm)
            ->setParameter('userId', $userId)
            ->setParameter('startDate', $startDate, Types::DATETIME_MUTABLE)
            ->setParameter('endDate', $endDate, Types::DATETIME_MUTABLE);

        return $nq->getResult();
    }
}
