<?php

declare(strict_types=1);

namespace MauticPlugin\DealFlowBundle\Entity;

use Doctrine\Bundle\DoctrineBundle\Repository\ServiceEntityRepository;
use Doctrine\ORM\QueryBuilder;
use Doctrine\Persistence\ManagerRegistry;

/** @extends ServiceEntityRepository<DealAuditEvent> */
class DealAuditEventRepository extends ServiceEntityRepository
{
    public function __construct(ManagerRegistry $registry)
    {
        parent::__construct($registry, DealAuditEvent::class);
    }

    /**
     * @return list<DealAuditEvent>
     */
    public function findByDeal(Deal $deal): array
    {
        return $this->findBy(['deal' => $deal], ['createdAt' => 'DESC']);
    }

    /**
     * @return list<DealAuditEvent>
     */
    public function findRecentForPipeline(Pipeline $pipeline, ?int $currentUserId = null, bool $includeOtherDeals = true, int $limit = 8): array
    {
        $qb = $this->createQueryBuilder('a')
            ->join('a.deal', 'd')
            ->addSelect('d')
            ->leftJoin('d.stage', 's')
            ->addSelect('s')
            ->andWhere('d.pipeline = :pipeline')
            ->setParameter('pipeline', $pipeline)
            ->orderBy('a.createdAt', 'DESC')
            ->setMaxResults($limit);

        $this->applyVisibilityScope($qb, 'd', $currentUserId, $includeOtherDeals);

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

    /**
     * Count stage moves bucketed by the TARGET stage type (open/won/lost).
     *
     * Joins audit events to the stages table via the payload's currentStage name
     * so moves to "Closed Won" count as won, "Closed Lost" as lost, etc.
     *
     * @return array<string, int> stage_type => count  (keys: open, won, lost)
     */
    public function countVelocityByStageType(Pipeline $pipeline, \DateTimeImmutable $since): array
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();

        $auditTable = $em->getClassMetadata(DealAuditEvent::class)->getTableName();
        $dealTable = $em->getClassMetadata(Deal::class)->getTableName();
        $stageTable = $em->getClassMetadata(DealStage::class)->getTableName();

        $rows = $conn->fetchAllAssociative(
            "SELECT s.stage_type, COUNT(*) AS cnt
             FROM {$auditTable} a
             INNER JOIN {$dealTable} d ON d.id = a.deal_id
             INNER JOIN {$stageTable} s
                ON s.pipeline_id = d.pipeline_id
               AND s.name = JSON_UNQUOTE(JSON_EXTRACT(a.payload_json, '$.currentStage'))
             WHERE d.pipeline_id = :pipelineId
               AND a.created_at >= :since
               AND a.event_type = :stageChanged
             GROUP BY s.stage_type",
            [
                'pipelineId' => $pipeline->getId(),
                'since' => $since->format('Y-m-d H:i:s'),
                'stageChanged' => \MauticPlugin\DealFlowBundle\DealFlowEvents::DEAL_STAGE_CHANGED,
            ]
        );

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

        return $counts;
    }

    private function applyVisibilityScope(QueryBuilder $qb, string $alias, ?int $currentUserId, bool $includeOtherDeals): void
    {
        if ($includeOtherDeals) {
            return;
        }

        if (null === $currentUserId) {
            $qb->andWhere('1 = 0');

            return;
        }

        $qb->andWhere(sprintf(
            '(%1$s.ownerId = :dealflowCurrentUserId OR (%1$s.ownerId IS NULL AND %1$s.createdBy = :dealflowCurrentUserId))',
            $alias
        ))
            ->setParameter('dealflowCurrentUserId', $currentUserId);
    }
}
