<?php

declare(strict_types=1);

/*
 * This file is part of the Ferienpass package.
 *
 * (c) Richard Henkenjohann <richard@ferienpass.online>
 *
 * For more information visit the project website <https://ferienpass.online>
 * or the documentation under <https://docs.ferienpass.online>.
 */

namespace Ferienpass\CoreBundle\Repository;

use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\Persistence\ManagerRegistry;
use Ferienpass\CoreBundle\Entity\SentEmail;
use Ferienpass\CoreBundle\Entity\SentMessage;
use Ferienpass\CoreBundle\Entity\SentMessageEngagement;

class SentEmailRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, SentEmail::class);
    }

    /**
     * The comment notification emails for the given ConversationItem uuids
     * (rfc4122), with engagements eager-loaded so delivery/open status can be
     * read without extra queries.
     *
     * @param array<int, string> $conversationItemUuids
     *
     * @return array<int, SentEmail>
     */
    public function findByConversationItems(array $conversationItemUuids): array
    {
        if ([] === $conversationItemUuids) {
            return [];
        }

        $qb = $this->createQueryBuilder('email')
            ->leftJoin('email.engagements', 'engagement')
            ->addSelect('engagement')
        ;

        $orX = $qb->expr()->orX();
        foreach (array_values($conversationItemUuids) as $i => $uuid) {
            $orX->add($qb->expr()->like('email.emailData', ':ci'.$i));
            $qb->setParameter('ci'.$i, '%"conversationItem":"'.$uuid.'"%');
        }

        return $qb->andWhere($orX)->getQuery()->getResult();
    }

    /**
     * Delivery status for many messages at once, keyed by message id.
     *
     * Reading `getDeliveryStatus()` off each entity instead walks a lazy collection
     * per message — one query per rendered row. This resolves a whole list in one,
     * served by the (message_id, event) index.
     *
     * @param int[] $messageIds
     *
     * @return array<int, string>
     */
    public function deliveryStatuses(array $messageIds): array
    {
        if ([] === $messageIds) {
            return [];
        }

        $rows = $this->getEntityManager()
            ->createQuery(\sprintf('SELECT IDENTITY(e.message) AS mid, e.event AS event FROM %s e WHERE e.message IN (:ids)', SentMessageEngagement::class))
            ->setParameter('ids', $messageIds)
            ->getArrayResult()
        ;

        $events = [];
        foreach ($rows as $row) {
            $events[(int) $row['mid']][] = (string) $row['event'];
        }

        $statuses = [];
        foreach ($messageIds as $id) {
            $statuses[$id] = SentMessage::deliveryStatusFor($events[$id] ?? []);
        }

        return $statuses;
    }

    /**
     * Number of emails sent since the given date.
     */
    public function countSince(\DateTimeInterface $since): int
    {
        return (int) $this->createQueryBuilder('email')
            ->select('COUNT(email.id)')
            ->andWhere('email.createdAt >= :since')
            ->setParameter('since', $since)
            ->getQuery()
            ->getSingleScalarResult()
        ;
    }

    /**
     * Number of emails sent since the given date that recorded at least one of the given
     * engagement events (e.g. "delivered", "open", "click", "bounce").
     */
    public function countWithEventSince(array $events, \DateTimeInterface $since): int
    {
        return (int) $this->createQueryBuilder('email')
            ->select('COUNT(email.id)')
            ->andWhere('email.createdAt >= :since')
            ->andWhere('EXISTS (
                SELECT 1 FROM '.SentMessageEngagement::class.' e
                WHERE e.message = email AND e.event IN (:events)
            )')
            ->setParameter('since', $since)
            ->setParameter('events', $events)
            ->getQuery()
            ->getSingleScalarResult()
        ;
    }

    /**
     * Per-day count of emails sent since the given date, keyed by Y-m-d.
     *
     * @return array<string, int>
     */
    public function dailyCountsSince(\DateTimeInterface $since): array
    {
        $rows = $this->createQueryBuilder('email')
            ->select('SUBSTRING(email.createdAt, 1, 10) AS day', 'COUNT(email.id) AS cnt')
            ->andWhere('email.createdAt >= :since')
            ->setParameter('since', $since)
            ->groupBy('day')
            ->getQuery()
            ->getArrayResult()
        ;

        $counts = [];
        foreach ($rows as $row) {
            $counts[$row['day']] = (int) $row['cnt'];
        }

        return $counts;
    }
}
