<?php

declare(strict_types=1);

namespace MauticPlugin\DealFlowBundle\Entity;

use Doctrine\DBAL\Query\QueryBuilder as DbalQueryBuilder;
use Doctrine\ORM\QueryBuilder;
use Mautic\CoreBundle\Entity\CommonRepository;

/**
 * @extends CommonRepository<Deal>
 */
class DealRepository extends CommonRepository
{
    public function getTableAlias(): string
    {
        return 'd';
    }

    /**
     * @return array<int, array{string, string}>
     */
    protected function getDefaultOrder(): array
    {
        return [
            ['d.dateModified', 'DESC'],
        ];
    }

    /**
     * @return string[]
     */
    public function getSearchCommands(): array
    {
        return array_merge(parent::getSearchCommands(), [
            'mautic.core.searchcommand.name',
            'mautic.plugin.dealflow.searchcommand.stage',
            'mautic.plugin.dealflow.searchcommand.status',
        ]);
    }

    /**
     * @param QueryBuilder|DbalQueryBuilder $q
     * @param \StdClass                     $filter
     */
    protected function addCatchAllWhereClause($q, $filter): array
    {
        return $this->addStandardCatchAllWhereClause(
            $q,
            $filter,
            [
                'd.title',
            ]
        );
    }

    /**
     * @param QueryBuilder|DbalQueryBuilder $q
     * @param \StdClass                     $filter
     */
    protected function addSearchCommandWhereClause($q, $filter): array
    {
        $command = $filter->command;
        $unique = $this->generateRandomParameterName();
        $expr = false;

        switch ($command) {
            case $this->translator->trans('mautic.core.searchcommand.name'):
            case $this->translator->trans('mautic.core.searchcommand.name', [], null, 'en_US'):
                $expr = $q->expr()->like('d.title', ":$unique");

                return [
                    $expr,
                    [$unique => ($filter->strict ? $filter->string : "%{$filter->string}%")],
                ];

            case $this->translator->trans('mautic.plugin.dealflow.searchcommand.stage'):
            case $this->translator->trans('mautic.plugin.dealflow.searchcommand.stage', [], null, 'en_US'):
            case 'stage':
                $expr = $q->expr()->like('s.name', ":$unique");

                if ($q instanceof QueryBuilder) {
                    $q->leftJoin('d.stage', 's');
                }

                return [
                    $expr,
                    [$unique => ($filter->strict ? $filter->string : "%{$filter->string}%")],
                ];

            case $this->translator->trans('mautic.plugin.dealflow.searchcommand.status'):
            case $this->translator->trans('mautic.plugin.dealflow.searchcommand.status', [], null, 'en_US'):
            case 'status':
                $expr = $q->expr()->eq('d.status', ":$unique");

                return [
                    $expr,
                    [$unique => $filter->string],
                ];
        }

        return parent::addSearchCommandWhereClause($q, $filter);
    }

    /**
     * @param array<string, mixed> $filters
     *
     * @return list<Deal>
     */
    public function findForPipeline(Pipeline $pipeline, array $filters = [], ?int $currentUserId = null, bool $includeOtherDeals = true): array
    {
        $qb = $this->createQueryBuilder('d')
            ->leftJoin('d.stage', 's')
            ->addSelect('s')
            ->andWhere('d.pipeline = :pipeline')
            ->setParameter('pipeline', $pipeline)
            ->andWhere('d.isPublished = :isPublished')
            ->setParameter('isPublished', true)
            ->orderBy('s.sortOrder', 'ASC')
            ->addOrderBy('d.dateModified', 'DESC');

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

        if (!empty($filters['ownerId'])) {
            $qb->andWhere('d.ownerId = :ownerId')
                ->setParameter('ownerId', (int) $filters['ownerId']);
        }

        if (!empty($filters['status'])) {
            $qb->andWhere('d.status = :status')
                ->setParameter('status', (string) $filters['status']);
        }

        if (!empty($filters['companyId'])) {
            $qb->andWhere('d.companyId = :companyId')
                ->setParameter('companyId', (int) $filters['companyId']);
        }

        if (!empty($filters['stageId'])) {
            $qb->andWhere('s.id = :stageId')
                ->setParameter('stageId', (int) $filters['stageId']);
        }

        if (!empty($filters['search'])) {
            $qb->andWhere(
                'LOWER(d.title) LIKE :search'
                .' OR LOWER(COALESCE(d.externalRef, \'\')) LIKE :search'
                .' OR LOWER(COALESCE(s.name, \'\')) LIKE :search'
                .' OR LOWER(d.status) LIKE :search'
            )
                ->setParameter('search', '%'.mb_strtolower((string) $filters['search']).'%');
        }

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

    /**
     * @return list<Deal>
     */
    public function findByCompany(int $companyId, ?int $currentUserId = null, bool $includeOtherDeals = true, ?int $limit = null): array
    {
        $qb = $this->createQueryBuilder('d')
            ->leftJoin('d.stage', 's')
            ->addSelect('s')
            ->addSelect("CASE WHEN d.status = 'open' THEN 0 WHEN d.status = 'won' THEN 1 ELSE 2 END AS HIDDEN companyStatusSort")
            ->andWhere('d.companyId = :companyId')
            ->setParameter('companyId', $companyId)
            ->orderBy('companyStatusSort', 'ASC')
            ->addOrderBy('d.dateModified', 'DESC');

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

        if (null !== $limit) {
            $qb->setMaxResults($limit);
        }

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

    public function countByCompany(int $companyId, ?int $currentUserId = null, bool $includeOtherDeals = true): int
    {
        $qb = $this->createQueryBuilder('d')
            ->select('COUNT(d.id)')
            ->andWhere('d.companyId = :companyId')
            ->setParameter('companyId', $companyId);

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

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

    /**
     * @return array<int, array{stageId: int, name: string, count: int|string, totalAmount: string}>
     */
    public function getStageBreakdown(Pipeline $pipeline, ?int $currentUserId = null, bool $includeOtherDeals = true): array
    {
        $qb = $this->createQueryBuilder('d')
            ->select('s.id AS stageId, s.name AS name, COUNT(d.id) AS count, COALESCE(SUM(d.amount), 0) AS totalAmount')
            ->join('d.stage', 's')
            ->andWhere('d.pipeline = :pipeline')
            ->setParameter('pipeline', $pipeline)
            ->groupBy('s.id')
            ->orderBy('s.sortOrder', 'ASC');

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

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

    public function getPipelineValue(Pipeline $pipeline, ?int $currentUserId = null, bool $includeOtherDeals = true): string
    {
        $qb = $this->createQueryBuilder('d')
            ->select('COALESCE(SUM(d.amount), 0)')
            ->andWhere('d.pipeline = :pipeline')
            ->andWhere('d.status = :status')
            ->setParameter('pipeline', $pipeline)
            ->setParameter('status', 'open');

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

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

    /**
     * @return array<string, int>
     */
    public function getWonLostCounts(Pipeline $pipeline, ?int $currentUserId = null, bool $includeOtherDeals = true): array
    {
        $qb = $this->createQueryBuilder('d')
            ->select('d.status AS status, COUNT(d.id) AS count')
            ->andWhere('d.pipeline = :pipeline')
            ->setParameter('pipeline', $pipeline)
            ->groupBy('d.status');

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

        $rows = $qb->getQuery()->getArrayResult();

        $result = ['won' => 0, 'lost' => 0];
        foreach ($rows as $row) {
            $result[(string) $row['status']] = (int) $row['count'];
        }

        return $result;
    }

    /**
     * @return array<int, array{ownerId: int|null, dealsCount: int|string, totalAmount: string}>
     */
    public function getOwnerLeaderboard(Pipeline $pipeline, ?int $currentUserId = null, bool $includeOtherDeals = true): array
    {
        $qb = $this->createQueryBuilder('d')
            ->select('d.ownerId AS ownerId, COUNT(d.id) AS dealsCount, COALESCE(SUM(d.amount), 0) AS totalAmount')
            ->andWhere('d.pipeline = :pipeline')
            ->setParameter('pipeline', $pipeline)
            ->groupBy('d.ownerId')
            ->orderBy('dealsCount', 'DESC');

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

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

    public function contactHasOpenDeal(int $contactId): bool
    {
        return (int) $this->createQueryBuilder('d')
            ->select('COUNT(d.id)')
            ->join('d.contactAssociations', 'a')
            ->andWhere('a.contactId = :contactId')
            ->andWhere('d.status = :status')
            ->setParameter('contactId', $contactId)
            ->setParameter('status', 'open')
            ->getQuery()
            ->getSingleScalarResult() > 0;
    }

    public function contactHasDealInStage(int $contactId, int $stageId): bool
    {
        return (int) $this->createQueryBuilder('d')
            ->select('COUNT(d.id)')
            ->join('d.contactAssociations', 'a')
            ->join('d.stage', 's')
            ->andWhere('a.contactId = :contactId')
            ->andWhere('s.id = :stageId')
            ->setParameter('contactId', $contactId)
            ->setParameter('stageId', $stageId)
            ->getQuery()
            ->getSingleScalarResult() > 0;
    }

    /**
     * @param array<string, int|string|float> $criteria
     */
    public function hasMatchingDealForContact(int $contactId, array $criteria = []): bool
    {
        if ($contactId <= 0) {
            return false;
        }

        return (int) $this->createMatchingDealsForContactQueryBuilder($contactId, $criteria)
            ->select('COUNT(DISTINCT d.id)')
            ->getQuery()
            ->getSingleScalarResult() > 0;
    }

    /**
     * @param array<string, int|string|float> $criteria
     *
     * @return list<Deal>
     */
    public function findMatchingDealsForContact(int $contactId, array $criteria = []): array
    {
        if ($contactId <= 0) {
            return [];
        }

        return $this->createMatchingDealsForContactQueryBuilder($contactId, $criteria)
            ->select('d', 's', 'p')
            ->distinct()
            ->orderBy('d.dateModified', 'DESC')
            ->getQuery()
            ->getResult();
    }

    public function findOneByExternalRef(string $externalRef): ?Deal
    {
        return $this->findOneBy(['externalRef' => $externalRef]);
    }

    /**
     * @param array<string, mixed> $filters
     *
     * @return list<Deal>
     */
    public function findForPipelinePaginated(Pipeline $pipeline, array $filters, ?int $currentUserId, bool $includeOtherDeals, int $page, int $limit): array
    {
        $qb = $this->createQueryBuilder('d')
            ->leftJoin('d.stage', 's')
            ->addSelect('s')
            ->andWhere('d.pipeline = :pipeline')
            ->setParameter('pipeline', $pipeline)
            ->andWhere('d.isPublished = :isPublished')
            ->setParameter('isPublished', true)
            ->orderBy('s.sortOrder', 'ASC')
            ->addOrderBy('d.dateModified', 'DESC');

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

        $qb->setFirstResult(($page - 1) * $limit)
            ->setMaxResults($limit);

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

    /**
     * @param array<string, mixed> $filters
     */
    public function countForPipeline(Pipeline $pipeline, array $filters, ?int $currentUserId, bool $includeOtherDeals): int
    {
        $qb = $this->createQueryBuilder('d')
            ->select('COUNT(d.id)')
            ->leftJoin('d.stage', 's')
            ->andWhere('d.pipeline = :pipeline')
            ->setParameter('pipeline', $pipeline)
            ->andWhere('d.isPublished = :isPublished')
            ->setParameter('isPublished', true);

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

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

    /**
     * @param array<string, mixed> $filters
     */
    private function applyPipelineFilters(QueryBuilder $qb, array $filters): void
    {
        if (!empty($filters['ownerId'])) {
            $qb->andWhere('d.ownerId = :ownerId')
                ->setParameter('ownerId', (int) $filters['ownerId']);
        }

        if (!empty($filters['status'])) {
            $qb->andWhere('d.status = :status')
                ->setParameter('status', (string) $filters['status']);
        }

        if (!empty($filters['companyId'])) {
            $qb->andWhere('d.companyId = :companyId')
                ->setParameter('companyId', (int) $filters['companyId']);
        }

        if (!empty($filters['stageId'])) {
            $qb->andWhere('s.id = :stageId')
                ->setParameter('stageId', (int) $filters['stageId']);
        }

        if (!empty($filters['search'])) {
            $qb->andWhere(
                'LOWER(d.title) LIKE :search'
                .' OR LOWER(COALESCE(d.externalRef, \'\')) LIKE :search'
                .' OR LOWER(COALESCE(s.name, \'\')) LIKE :search'
                .' OR LOWER(d.status) LIKE :search'
            )
                ->setParameter('search', '%'.mb_strtolower((string) $filters['search']).'%');
        }
    }

    public function countByStage(DealStage $stage): int
    {
        return (int) $this->createQueryBuilder('d')
            ->select('COUNT(d.id)')
            ->andWhere('d.stage = :stage')
            ->setParameter('stage', $stage)
            ->getQuery()
            ->getSingleScalarResult();
    }

    public function countByPipeline(Pipeline $pipeline): int
    {
        return (int) $this->createQueryBuilder('d')
            ->select('COUNT(d.id)')
            ->andWhere('d.pipeline = :pipeline')
            ->setParameter('pipeline', $pipeline)
            ->getQuery()
            ->getSingleScalarResult();
    }

    /**
     * @return list<string>
     */
    public function findDistinctSources(): array
    {
        $rows = $this->createQueryBuilder('d')
            ->select('DISTINCT d.source AS source')
            ->andWhere('d.source IS NOT NULL')
            ->andWhere('d.source != :emptySource')
            ->setParameter('emptySource', '')
            ->orderBy('d.source', 'ASC')
            ->getQuery()
            ->getArrayResult();

        return array_values(array_map(
            static fn (array $row): string => (string) $row['source'],
            $rows
        ));
    }

    public function moveDealsToStage(DealStage $fromStage, DealStage $toStage, ?int $updatedBy = null): int
    {
        $status = match ($toStage->getStageType()) {
            'won' => 'won',
            'lost' => 'lost',
            default => 'open',
        };

        $qb = $this->createQueryBuilder('d')
            ->update()
            ->set('d.stage', ':toStage')
            ->set('d.status', ':status')
            ->set('d.dateModified', ':dateModified')
            ->where('d.stage = :fromStage')
            ->setParameter('toStage', $toStage)
            ->setParameter('status', $status)
            ->setParameter('dateModified', new \DateTime())
            ->setParameter('fromStage', $fromStage);

        if (null !== $updatedBy) {
            $qb->set('d.modifiedBy', ':modifiedBy')
                ->setParameter('modifiedBy', $updatedBy);
        }

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

    /**
     * @param list<int> $contactIds
     *
     * @return list<Deal>
     */
    public function findMatchingDealsByContacts(array $contactIds, ?int $companyId = null): array
    {
        if ([] === $contactIds) {
            return [];
        }

        $candidates = $this->createQueryBuilder('d')
            ->join('d.contactAssociations', 'a')
            ->andWhere('a.contactId IN (:contactIds)')
            ->setParameter('contactIds', array_values(array_unique($contactIds)))
            ->getQuery()
            ->getResult();

        $matches = [];
        foreach ($candidates as $candidate) {
            $candidateIds = [];
            foreach ($candidate->getContactAssociations() as $association) {
                $candidateIds[] = $association->getContactId();
            }

            sort($candidateIds);
            $expected = array_values(array_unique($contactIds));
            sort($expected);

            if ($candidateIds !== $expected) {
                continue;
            }

            if (null !== $companyId && $candidate->getCompanyId() !== $companyId) {
                continue;
            }

            $matches[] = $candidate;
        }

        return $matches;
    }

    /**
     * @return list<Deal>
     */
    public function findClosingThisMonth(Pipeline $pipeline, ?\DateTimeInterface $now = null): array
    {
        $now ??= new \DateTimeImmutable();
        $firstDay = new \DateTimeImmutable($now->format('Y-m-01'));
        $lastDay = new \DateTimeImmutable($now->format('Y-m-t'));

        return $this->createQueryBuilder('d')
            ->andWhere('d.pipeline = :pipeline')
            ->andWhere('d.status = :status')
            ->andWhere('d.expectedCloseDate >= :firstDay')
            ->andWhere('d.expectedCloseDate <= :lastDay')
            ->setParameter('pipeline', $pipeline)
            ->setParameter('status', 'open')
            ->setParameter('firstDay', $firstDay)
            ->setParameter('lastDay', $lastDay)
            ->orderBy('d.expectedCloseDate', 'ASC')
            ->setMaxResults(20)
            ->getQuery()
            ->getResult();
    }

    /**
     * @return list<Deal>
     */
    public function findStaleDeals(Pipeline $pipeline, int $staleDays = 14, ?\DateTimeInterface $now = null): array
    {
        $now ??= new \DateTimeImmutable();
        $threshold = \DateTimeImmutable::createFromInterface($now)->modify(sprintf('-%d days', $staleDays));

        return $this->createQueryBuilder('d')
            ->andWhere('d.pipeline = :pipeline')
            ->andWhere('d.status = :status')
            ->andWhere('d.dateModified < :threshold')
            ->setParameter('pipeline', $pipeline)
            ->setParameter('status', 'open')
            ->setParameter('threshold', $threshold)
            ->orderBy('d.dateModified', 'ASC')
            ->setMaxResults(20)
            ->getQuery()
            ->getResult();
    }

    public function getWeightedForecast(Pipeline $pipeline): string
    {
        return (string) $this->createQueryBuilder('d')
            ->select('COALESCE(SUM(d.amount * s.probability / 100), 0)')
            ->join('d.stage', 's')
            ->andWhere('d.pipeline = :pipeline')
            ->andWhere('d.status = :status')
            ->setParameter('pipeline', $pipeline)
            ->setParameter('status', 'open')
            ->getQuery()
            ->getSingleScalarResult();
    }

    /**
     * @return list<array{weekStart: string, weekEnd: string, revenue: string}>
     */
    public function getWeeklyRevenue(Pipeline $pipeline, int $weeks = 16, ?\DateTimeInterface $now = null): array
    {
        $now ??= new \DateTimeImmutable();

        // Build week boundaries going backwards
        // Find the start of the current week (Monday)
        $currentWeekStart = new \DateTimeImmutable($now->format('o-\\WW')); // ISO week start (Monday)

        $results = [];
        for ($i = $weeks - 1; $i >= 0; --$i) {
            $weekStart = $currentWeekStart->modify("-{$i} weeks");
            $weekEnd = $weekStart->modify('+6 days 23:59:59');

            $revenue = $this->createQueryBuilder('d')
                ->select('COALESCE(SUM(d.amount), 0)')
                ->andWhere('d.pipeline = :pipeline')
                ->andWhere('d.status = :status')
                ->andWhere('d.dateModified >= :weekStart')
                ->andWhere('d.dateModified <= :weekEnd')
                ->setParameter('pipeline', $pipeline)
                ->setParameter('status', 'won')
                ->setParameter('weekStart', $weekStart)
                ->setParameter('weekEnd', $weekEnd)
                ->getQuery()
                ->getSingleScalarResult();

            $results[] = [
                'weekStart' => $weekStart->format('M j'),
                'weekEnd' => $weekEnd->format('M j'),
                'revenue' => (string) $revenue,
            ];
        }

        return $results;
    }

    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);
    }

    /**
     * @param array<string, int|string|float> $criteria
     */
    private function createMatchingDealsForContactQueryBuilder(int $contactId, array $criteria): QueryBuilder
    {
        $qb = $this->createQueryBuilder('d')
            ->join('d.contactAssociations', 'a')
            ->join('d.pipeline', 'p')
            ->leftJoin('d.stage', 's')
            ->andWhere('a.contactId = :dealflowMatchContactId')
            ->setParameter('dealflowMatchContactId', $contactId);

        $this->applyContactMatchCriteria($qb, $criteria);

        return $qb;
    }

    /**
     * @param array<string, int|string|float> $criteria
     */
    private function applyContactMatchCriteria(QueryBuilder $qb, array $criteria): void
    {
        $pipelineId = (int) ($criteria['pipelineId'] ?? 0);
        if ($pipelineId > 0) {
            $qb->andWhere('p.id = :dealflowMatchPipelineId')
                ->setParameter('dealflowMatchPipelineId', $pipelineId);
        }

        $stageId = (int) ($criteria['stageId'] ?? 0);
        if ($stageId > 0) {
            $qb->andWhere('s.id = :dealflowMatchStageId')
                ->setParameter('dealflowMatchStageId', $stageId);
        }

        $status = trim((string) ($criteria['status'] ?? ''));
        if ('' !== $status) {
            $qb->andWhere('d.status = :dealflowMatchStatus')
                ->setParameter('dealflowMatchStatus', $status);
        }

        $stakeholderRole = trim((string) ($criteria['stakeholderRole'] ?? ''));
        if ('' !== $stakeholderRole) {
            $qb->andWhere('a.label = :dealflowMatchStakeholderRole')
                ->setParameter('dealflowMatchStakeholderRole', $stakeholderRole);
        }

        if (array_key_exists('minAmount', $criteria) && '' !== (string) $criteria['minAmount']) {
            $qb->andWhere('d.amount >= :dealflowMatchMinAmount')
                ->setParameter('dealflowMatchMinAmount', (float) $criteria['minAmount']);
        }

        if (array_key_exists('maxAmount', $criteria) && '' !== (string) $criteria['maxAmount']) {
            $qb->andWhere('d.amount <= :dealflowMatchMaxAmount')
                ->setParameter('dealflowMatchMaxAmount', (float) $criteria['maxAmount']);
        }
    }

    /**
     * Find all won deals for velocity calculations.
     *
     * @return list<Deal>
     */
    public function findWonDeals(Pipeline $pipeline, ?int $currentUserId = null, bool $includeOtherDeals = true): array
    {
        $qb = $this->createQueryBuilder('d')
            ->andWhere('d.pipeline = :pipeline')
            ->andWhere('d.status = :status')
            ->setParameter('pipeline', $pipeline)
            ->setParameter('status', 'won')
            ->orderBy('d.dateModified', 'DESC');

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

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

    /**
     * Find all closed deals (won + lost) for velocity calculations.
     *
     * @return list<Deal>
     */
    public function findClosedDeals(Pipeline $pipeline, ?int $currentUserId = null, bool $includeOtherDeals = true): array
    {
        $qb = $this->createQueryBuilder('d')
            ->andWhere('d.pipeline = :pipeline')
            ->andWhere('d.status IN (:statuses)')
            ->setParameter('pipeline', $pipeline)
            ->setParameter('statuses', ['won', 'lost'])
            ->orderBy('d.dateModified', 'DESC');

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

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

    /**
     * Monthly close counts for the last N months.
     *
     * @return list<array{month: string, wonCount: int, lostCount: int, totalClosed: int}>
     */
    public function getMonthlyCloseCounts(Pipeline $pipeline, int $months = 6, ?int $currentUserId = null, bool $includeOtherDeals = true, ?\DateTimeInterface $now = null): array
    {
        $now ??= new \DateTimeImmutable();
        $results = [];

        for ($i = $months - 1; $i >= 0; --$i) {
            $monthStart = new \DateTimeImmutable($now->format('Y-m-01')." -{$i} months");
            $monthEnd = new \DateTimeImmutable($monthStart->format('Y-m-t').' 23:59:59');
            $monthLabel = $monthStart->format('M Y');

            $wonCount = $this->countClosedInPeriod($pipeline, 'won', $monthStart, $monthEnd, $currentUserId, $includeOtherDeals);
            $lostCount = $this->countClosedInPeriod($pipeline, 'lost', $monthStart, $monthEnd, $currentUserId, $includeOtherDeals);

            $results[] = [
                'month' => $monthLabel,
                'wonCount' => $wonCount,
                'lostCount' => $lostCount,
                'totalClosed' => $wonCount + $lostCount,
            ];
        }

        return $results;
    }

    private function countClosedInPeriod(Pipeline $pipeline, string $status, \DateTimeInterface $from, \DateTimeInterface $to, ?int $currentUserId, bool $includeOtherDeals): int
    {
        $qb = $this->createQueryBuilder('d')
            ->select('COUNT(d.id)')
            ->andWhere('d.pipeline = :pipeline')
            ->andWhere('d.status = :status')
            ->andWhere('d.dateModified >= :from')
            ->andWhere('d.dateModified <= :to')
            ->setParameter('pipeline', $pipeline)
            ->setParameter('status', $status)
            ->setParameter('from', $from)
            ->setParameter('to', $to);

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

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

    /**
     * @return Deal[]
     */
    public function findOpenDeals(int $limit = 10): array
    {
        return $this->createQueryBuilder('d')
            ->andWhere('d.status = :status')
            ->setParameter('status', 'open')
            ->orderBy('d.dateModified', 'DESC')
            ->setMaxResults($limit)
            ->getQuery()
            ->getResult();
    }
}
