<?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);
        $this->applyPipelineFilters($qb, $filters);

        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 [];
        }

        $uniqueContactIds = array_values(array_unique($contactIds));
        $expectedCount = count($uniqueContactIds);

        // Use set-based SQL to find deals that have exactly the given set of contacts:
        //   1. INNER JOIN on the association table restricts to deals touching at least one contact.
        //   2. HAVING COUNT(DISTINCT a.contactId) = N ensures the deal has exactly N matching contacts.
        //   3. A second HAVING with COUNT(*) = N (total associations) ensures no extra contacts exist
        //      on the deal beyond the requested set.
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $dealTable = $this->getClassMetadata()->getTableName();
        $assocTable = $em->getClassMetadata(DealContactAssociation::class)->getTableName();

        $placeholders = implode(', ', array_fill(0, $expectedCount, '?'));
        $params = array_values($uniqueContactIds);
        $types = array_fill(0, $expectedCount, \Doctrine\DBAL\ParameterType::INTEGER);

        if (null !== $companyId) {
            $sql = "SELECT d.id
                    FROM {$dealTable} d
                    INNER JOIN {$assocTable} a ON a.deal_id = d.id
                    WHERE a.contact_id IN ({$placeholders})
                      AND d.company_id = ?
                    GROUP BY d.id
                    HAVING COUNT(DISTINCT a.contact_id) = ?
                       AND (SELECT COUNT(*) FROM {$assocTable} WHERE deal_id = d.id) = ?";
            $params[] = $companyId;
            $params[] = $expectedCount;
            $params[] = $expectedCount;
            $types[] = \Doctrine\DBAL\ParameterType::INTEGER;
            $types[] = \Doctrine\DBAL\ParameterType::INTEGER;
            $types[] = \Doctrine\DBAL\ParameterType::INTEGER;
        } else {
            $sql = "SELECT d.id
                    FROM {$dealTable} d
                    INNER JOIN {$assocTable} a ON a.deal_id = d.id
                    WHERE a.contact_id IN ({$placeholders})
                    GROUP BY d.id
                    HAVING COUNT(DISTINCT a.contact_id) = ?
                       AND (SELECT COUNT(*) FROM {$assocTable} WHERE deal_id = d.id) = ?";
            $params[] = $expectedCount;
            $params[] = $expectedCount;
            $types[] = \Doctrine\DBAL\ParameterType::INTEGER;
            $types[] = \Doctrine\DBAL\ParameterType::INTEGER;
        }

        /** @var list<array{id: int|string}> $rows */
        $rows = $conn->fetchAllAssociative($sql, $params, $types);

        if ([] === $rows) {
            return [];
        }

        $matchingIds = array_map(static fn (array $r): int => (int) $r['id'], $rows);

        /** @var list<Deal> $deals */
        $deals = $this->createQueryBuilder('d')
            ->andWhere('d.id IN (:ids)')
            ->setParameter('ids', $matchingIds)
            ->orderBy('d.dateModified', 'DESC')
            ->getQuery()
            ->getResult();

        return $deals;
    }

    /**
     * @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();

        // Find the start of the current ISO week (Monday)
        $currentWeekStart = new \DateTimeImmutable($now->format('o-\\WW'));
        $rangeStart = $currentWeekStart->modify(sprintf('-%d weeks', $weeks - 1));
        $rangeEnd = $currentWeekStart->modify('+6 days 23:59:59');

        // Single aggregate query: fetch all won deal amounts in the range, grouped by
        // ISO year-week so we can bucket them without N round-trips to the DB.
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $dealTable = $this->getClassMetadata()->getTableName();

        /** @var list<array{iso_week: string, revenue: string}> $rows */
        $rows = $conn->fetchAllAssociative(
            "SELECT YEARWEEK(date_modified, 1) AS iso_week,
                    COALESCE(SUM(amount), 0) AS revenue
             FROM {$dealTable}
             WHERE pipeline_id = :pipelineId
               AND status = 'won'
               AND date_modified >= :rangeStart
               AND date_modified <= :rangeEnd
             GROUP BY iso_week
             ORDER BY iso_week ASC",
            [
                'pipelineId' => $pipeline->getId(),
                'rangeStart' => $rangeStart->format('Y-m-d H:i:s'),
                'rangeEnd' => $rangeEnd->format('Y-m-d H:i:s'),
            ]
        );

        // Index DB results by iso_week key for O(1) lookup
        $revenueByWeek = [];
        foreach ($rows as $row) {
            $revenueByWeek[(string) $row['iso_week']] = (string) $row['revenue'];
        }

        // Build the result array in chronological order, filling zeros for weeks
        // with no won deals (preserving the original N-element output contract).
        $results = [];
        for ($i = $weeks - 1; $i >= 0; --$i) {
            $weekStart = $currentWeekStart->modify("-{$i} weeks");
            $weekEnd = $weekStart->modify('+6 days 23:59:59');
            $isoWeekKey = $weekStart->format('oW'); // e.g. "202512"

            $results[] = [
                'weekStart' => $weekStart->format('M j'),
                'weekEnd' => $weekEnd->format('M j'),
                'revenue' => $revenueByWeek[$isoWeekKey] ?? '0',
            ];
        }

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

        $firstMonthStart = new \DateTimeImmutable($now->format('Y-m-01')." -{$months} months +1 month");
        $lastMonthEnd = new \DateTimeImmutable($now->format('Y-m-t').' 23:59:59');

        // Single aggregate query — one row per (month, status) combination.
        $qb = $this->createQueryBuilder('d')
            ->select('SUBSTRING(d.dateModified, 1, 7) AS yearMonth, d.status AS status, COUNT(d.id) AS cnt')
            ->andWhere('d.pipeline = :pipeline')
            ->andWhere('d.status IN (:statuses)')
            ->andWhere('d.dateModified >= :rangeStart')
            ->andWhere('d.dateModified <= :rangeEnd')
            ->setParameter('pipeline', $pipeline)
            ->setParameter('statuses', ['won', 'lost'])
            ->setParameter('rangeStart', $firstMonthStart)
            ->setParameter('rangeEnd', $lastMonthEnd)
            ->groupBy('yearMonth, d.status')
            ->orderBy('yearMonth', 'ASC');

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

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

        // Index by "YYYY-MM" + status for O(1) lookup
        $indexed = [];
        foreach ($rows as $row) {
            $indexed[(string) $row['yearMonth']][(string) $row['status']] = (int) $row['cnt'];
        }

        $results = [];
        for ($i = $months - 1; $i >= 0; --$i) {
            $monthStart = new \DateTimeImmutable($now->format('Y-m-01')." -{$i} months");
            $monthLabel = $monthStart->format('M Y');
            $yearMonthKey = $monthStart->format('Y-m');

            $wonCount = $indexed[$yearMonthKey]['won'] ?? 0;
            $lostCount = $indexed[$yearMonthKey]['lost'] ?? 0;

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

        return $results;
    }

    /**
     * Count deals currently in won/lost stages, modified within the window.
     *
     * @return array<string, int> stage_type => count (keys: won, lost)
     */
    public function countByStageTypeSince(Pipeline $pipeline, \DateTimeImmutable $since): array
    {
        $em = $this->getEntityManager();
        $conn = $em->getConnection();
        $dealTable = $this->getClassMetadata()->getTableName();
        $stageTable = $em->getClassMetadata(DealStage::class)->getTableName();

        $rows = $conn->fetchAllAssociative(
            "SELECT s.stage_type, COUNT(*) AS cnt
             FROM {$dealTable} d
             INNER JOIN {$stageTable} s ON s.id = d.stage_id
             WHERE d.pipeline_id = :pipelineId
               AND s.stage_type IN ('won', 'lost')
               AND d.date_modified >= :since
             GROUP BY s.stage_type",
            [
                'pipelineId' => $pipeline->getId(),
                'since' => $since->format('Y-m-d H:i:s'),
            ]
        );

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

        return $counts;
    }

    /**
     * @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();
    }

    /**
     * Write the stage FK and status directly via DBAL.
     *
     * Relationship setters (setStage()) cannot use isChanged() because
     * CommonEntity casts the current value to string for comparison, which
     * throws on entity objects.  Doctrine's UnitOfWork therefore never marks
     * the stage_id column as dirty and silently skips the UPDATE.
     *
     * DBAL bypasses the ORM entirely and writes the column unconditionally.
     * Callers MUST call $em->clear() immediately after to prevent a subsequent
     * ORM flush from reverting the DBAL write with stale identity-map state.
     */
    public function updateStageFk(int $dealId, int $stageId, string $status, string $dateModified, ?int $actorId): void
    {
        $this->getEntityManager()->getConnection()->update(
            MAUTIC_TABLE_PREFIX.'dealflow_deals',
            [
                'stage_id' => $stageId,
                'status' => $status,
                'date_modified' => $dateModified,
                'modified_by' => $actorId,
            ],
            ['id' => $dealId]
        );
    }

    /**
     * Write the expected_close_date column directly via DBAL.
     *
     * DateTimeImmutable setters cannot use isChanged() (CommonEntity's string
     * cast on DateTimeImmutable throws a TypeError), so ORM silently skips the
     * column on flush.  DBAL writes it unconditionally.
     *
     * Callers MUST call $em->clear() after this write to prevent ORM from
     * reverting it on the next flush.
     */
    public function updateExpectedCloseDate(int $dealId, ?string $date, string $dateModified, ?int $actorId): void
    {
        $this->getEntityManager()->getConnection()->update(
            MAUTIC_TABLE_PREFIX.'dealflow_deals',
            [
                'expected_close_date' => $date,
                'date_modified' => $dateModified,
                'modified_by' => $actorId,
            ],
            ['id' => $dealId]
        );
    }
}
