<?php

namespace Mautic\PluginBundle\Entity;

use Doctrine\DBAL\ArrayParameterType;
use Doctrine\DBAL\Query\Expression\CompositeExpression;
use Mautic\CoreBundle\Entity\CommonRepository;

/**
 * @extends CommonRepository<IntegrationEntity>
 */
class IntegrationEntityRepository extends CommonRepository
{
    /**
     * @param array<int>|int|null               $internalEntityIds
     * @param mixed                             $startDate
     * @param mixed                             $endDate
     * @param bool                              $push
     * @param int                               $start
     * @param int                               $limit
     * @param int|string|array<int|string>|null $integrationEntityIds
     */
    public function getIntegrationsEntityId(
        $integration,
        $integrationEntity,
        $internalEntity,
        $internalEntityIds = null,
        $startDate = null,
        $endDate = null,
        $push = false,
        $start = 0,
        $limit = 0,
        $integrationEntityIds = null
    ): array {
        $q = $this->_em->getConnection()->createQueryBuilder()
            ->select('DISTINCT(i.integration_entity_id), i.id, i.internal_entity_id, i.integration_entity, i.internal_entity')
            ->from(MAUTIC_TABLE_PREFIX.'integration_entity', 'i');

        $q->where('i.integration = :integration')
            ->andWhere('i.internal_entity = :internalEntity')
            ->setParameter('integration', $integration)
            ->setParameter('internalEntity', $internalEntity);

        if ($integrationEntity) {
            $q->andWhere('i.integration_entity = :integrationEntity')
                ->setParameter('integrationEntity', $integrationEntity);
        }

        if ('lead' === $internalEntity) {
            $joinCondition = $q->expr()->and(
                $q->expr()->eq('l.id', 'i.internal_entity_id')
            );

            if ($push) {
                $joinCondition->with(
                    $q->expr()->gte('l.last_active', ':startDate')
                );
                $q->setParameter('startDate', $startDate);
            }

            $q->join('i', MAUTIC_TABLE_PREFIX.'leads', 'l', $joinCondition);
        }

        if ($internalEntityIds) {
            if (is_array($internalEntityIds)) {
                $q->andWhere('i.internal_entity_id in (:internalEntityIds)')
                    ->setParameter('internalEntityIds', $internalEntityIds, ArrayParameterType::STRING);
            } else {
                $q->andWhere('i.internal_entity_id = :internalEntityId')
                    ->setParameter('internalEntityId', $internalEntityIds);
            }
        }

        if ($startDate and !$push) {
            $q->andWhere('i.last_sync_date >= :startDate')
                ->setParameter('startDate', $startDate);
        }

        if ($endDate and !$push) {
            $q->andWhere('i.last_sync_date <= :endDate')
                ->setParameter('endDate', $endDate);
        }

        if ($integrationEntityIds) {
            if (is_array($integrationEntityIds)) {
                $q->andWhere('i.integration_entity_id in (:integrationEntityIds)')
                    ->setParameter('integrationEntityIds', $integrationEntityIds, ArrayParameterType::STRING);
            } else {
                $q->andWhere('i.integration_entity_id = :integrationEntityId')
                    ->setParameter('integrationEntityId', $integrationEntityIds);
            }
        }

        if ($start) {
            $q->setFirstResult((int) $start);
        }

        if ($limit) {
            $q->setMaxResults((int) $limit);
        }

        return $q->executeQuery()->fetchAllAssociative();
    }

    /**
     * @return array
     */
    public function getIntegrationEntity($integration, $integrationEntity, $internalEntity, $internalEntityId, $leadFields = null)
    {
        $q = $this->_em->getConnection()->createQueryBuilder()
            ->from(MAUTIC_TABLE_PREFIX.'integration_entity', 'i')
            ->join('i', MAUTIC_TABLE_PREFIX.'leads', 'l', 'l.id = i.internal_entity_id');
        $q->select('i.integration_entity_id, i.integration_entity, i.id, i.internal_entity_id');
        if ($leadFields) {
            $q->addSelect($leadFields);
        }

        $q->where(
            $q->expr()->and(
                $q->expr()->eq('i.integration', ':integration'),
                $q->expr()->eq('i.internal_entity', ':internalEntity'),
                $q->expr()->eq('i.integration_entity', ':integrationEntity'),
                $q->expr()->eq('i.internal_entity_id', (int) $internalEntityId)
            )
        )
            ->setParameter('integration', $integration)
            ->setParameter('internalEntity', $internalEntity)
            ->setParameter('integrationEntity', $integrationEntity)
            ->setMaxResults(1);

        $results = $q->executeQuery()->fetchAllAssociative();

        return ($results) ? $results[0] : null;
    }

    /**
     * @return IntegrationEntity[]
     */
    public function getIntegrationEntities($integration, $integrationEntity, $internalEntity, $internalEntityIds)
    {
        $q = $this->createQueryBuilder('i', 'i.internalEntityId');

        $q->where(
            $q->expr()->andX(
                $q->expr()->eq('i.integration', ':integration'),
                $q->expr()->eq('i.internalEntity', ':internalEntity'),
                $q->expr()->eq('i.integrationEntity', ':integrationEntity'),
                $q->expr()->in('i.internalEntityId', ':internalEntityIds')
            )
        )
            ->setParameter('integration', $integration)
            ->setParameter('internalEntity', $internalEntity)
            ->setParameter('integrationEntity', $integrationEntity)
            ->setParameter('internalEntityIds', $internalEntityIds);

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

    /**
     * @param int          $limit
     * @param array|string $integrationEntity
     * @param array        $excludeIntegrationIds
     *
     * @return mixed[]
     */
    public function findLeadsToUpdate(
        $integration,
        $internalEntity,
        $leadFields,
        $limit = 25,
        $fromDate = null,
        $toDate = null,
        $integrationEntity = ['Contact', 'Lead'],
        $excludeIntegrationIds = []
    ): array {
        if ('company' == $internalEntity) {
            $joinTable = 'companies';
        } else {
            $joinTable = 'leads';
        }
        $q = $this->_em->getConnection()->createQueryBuilder()
            ->from(MAUTIC_TABLE_PREFIX.'integration_entity', 'i')
            ->join('i', MAUTIC_TABLE_PREFIX.$joinTable, 'l', 'l.id = i.internal_entity_id');

        if (false === $limit) {
            $q->select('count(i.integration_entity_id) as total');

            if ($integrationEntity) {
                $q->addSelect('i.integration_entity');
            }
        } else {
            $q->select('i.integration_entity_id, i.integration_entity, i.id, i.internal_entity_id,'.$leadFields);
        }

        $q->where('i.integration = :integration');

        if ($integrationEntity) {
            if (!is_array($integrationEntity)) {
                $integrationEntity = [$integrationEntity];
            }
            $sub = null;
            foreach ($integrationEntity as $key => $entity) {
                if (null === $sub) {
                    $sub = CompositeExpression::or($q->expr()->eq('i.integration_entity', ':entity'.$key));
                    $q->setParameter('entity'.$key, $entity);
                    continue;
                }

                $sub->with($q->expr()->eq('i.integration_entity', ':entity'.$key));
                $q->setParameter('entity'.$key, $entity);
            }
            $q->andWhere($sub);
        }

        $q->andWhere('i.internal_entity = :internalEntity')
            ->setParameter('integration', $integration)
            ->setParameter('internalEntity', $internalEntity);

        if (!empty($excludeIntegrationIds)) {
            $q->andWhere(
                $q->expr()->notIn(
                    'i.integration_entity_id',
                    array_map(
                        fn ($x): string => "'".$x."'",
                        $excludeIntegrationIds
                    )
                )
            );
        }

        $q->andWhere(
            $q->expr()->and(
                $q->expr()->isNotNull('i.integration_entity_id'),
                $q->expr()->or(
                    $q->expr()->and(
                        $q->expr()->isNotNull('i.last_sync_date'),
                        $q->expr()->gt('l.date_modified', 'i.last_sync_date')
                    ),
                    $q->expr()->and(
                        $q->expr()->isNull('i.last_sync_date'),
                        $q->expr()->isNotNull('l.date_modified'),
                        $q->expr()->gt('l.date_modified', 'l.date_added')
                    )
                )
            )
        );

        if ('lead' == $internalEntity) {
            $q->andWhere(
                $q->expr()->and($q->expr()->isNotNull('l.email')));
        } else {
            $q->andWhere(
                $q->expr()->and($q->expr()->isNotNull('l.companyname')));
        }

        if ($fromDate) {
            if ($toDate) {
                $q->andWhere(
                    $q->expr()->comparison('l.date_modified', 'BETWEEN', ':dateFrom and :dateTo')
                )
                    ->setParameter('dateFrom', $fromDate)
                    ->setParameter('dateTo', $toDate);
            } else {
                $q->andWhere(
                    $q->expr()->gte('l.date_modified', ':dateFrom')
                )
                    ->setParameter('dateFrom', $fromDate);
            }
        } elseif ($toDate) {
            $q->andWhere(
                $q->expr()->lte('l.date_modified', ':dateTo')
            )
                ->setParameter('dateTo', $toDate);
        }

        // Group by email to prevent duplicates from affecting this

        if (false === $limit and $integrationEntity) {
            $q->groupBy('i.integration_entity')->having('total');
        }
        if ($limit) {
            $q->setMaxResults($limit);
        }

        $results = $q->executeQuery()->fetchAllAssociative();

        $leads   = [];

        if ($integrationEntity) {
            foreach ($integrationEntity as $entity) {
                $leads[$entity] = (false === $limit) ? 0 : [];
            }
        }

        foreach ($results as $result) {
            if ($integrationEntity) {
                if (false === $limit) {
                    $leads[$result['integration_entity']] = (int) $result['total'];
                } else {
                    $leads[$result['integration_entity']][$result['internal_entity_id']] = $result;
                }
            } else {
                $leads[$result['internal_entity_id']] = $result['internal_entity_id'];
            }
        }

        return $leads;
    }

    /**
     * @param int $limit
     *
     * @return array|int
     */
    public function findLeadsToCreate($integration, $leadFields, $limit = 25, $fromDate = null, $toDate = null, $internalEntity = 'lead')
    {
        if ('company' == $internalEntity) {
            $joinTable = 'companies';
        } else {
            $joinTable = 'leads';
        }
        $q = $this->_em->getConnection()->createQueryBuilder()
            ->from(MAUTIC_TABLE_PREFIX.$joinTable, 'l');

        if (false === $limit) {
            $q->select('count(*) as total');
        } else {
            $q->select('l.id as internal_entity_id,'.$leadFields);
        }
        if ('company' == $internalEntity) {
            $q->where('not exists (select null from '.MAUTIC_TABLE_PREFIX
                .'integration_entity i where i.integration = :integration and i.internal_entity LIKE "'.$internalEntity.'%" and i.internal_entity_id = l.id)')
                ->setParameter('integration', $integration);
        } else {
            $q->where('l.date_identified is not null')
                ->andWhere(
                    'not exists (select null from '.MAUTIC_TABLE_PREFIX
                    .'integration_entity i where i.integration = :integration and i.internal_entity LIKE "'.$internalEntity.'%" and i.internal_entity_id = l.id)'
                )
                ->setParameter('integration', $integration);
        }

        if ('company' == $internalEntity) {
            $q->andWhere('l.companyname is not null');
        } else {
            $q->andWhere('l.email is not null');
        }
        if ($limit) {
            $q->setMaxResults($limit);
        }

        if ($fromDate) {
            if ($toDate) {
                $q->andWhere(
                    $q->expr()->or(
                        $q->expr()->and(
                            $q->expr()->isNotNull('l.date_modified'),
                            $q->expr()->comparison('l.date_modified', 'BETWEEN', ':dateFrom and :dateTo')
                        ),
                        $q->expr()->and(
                            $q->expr()->isNull('l.date_modified'),
                            $q->expr()->comparison('l.date_added', 'BETWEEN', ':dateFrom and :dateTo')
                        )
                    )
                )
                    ->setParameter('dateFrom', $fromDate)
                    ->setParameter('dateTo', $toDate);
            } else {
                $q->andWhere(
                    $q->expr()->or(
                        $q->expr()->and(
                            $q->expr()->isNotNull('l.date_modified'),
                            $q->expr()->gte('l.date_modified', ':dateFrom')
                        ),
                        $q->expr()->and(
                            $q->expr()->isNull('l.date_modified'),
                            $q->expr()->gte('l.date_added', ':dateFrom')
                        )
                    )
                )
                    ->setParameter('dateFrom', $fromDate);
            }
        } elseif ($toDate) {
            $q->andWhere(
                $q->expr()->or(
                    $q->expr()->and(
                        $q->expr()->isNotNull('l.date_modified'),
                        $q->expr()->lte('l.date_modified', ':dateTo')
                    ),
                    $q->expr()->and(
                        $q->expr()->isNull('l.date_modified'),
                        $q->expr()->lte('l.date_added', ':dateTo')
                    )
                )
            )
                ->setParameter('dateTo', $toDate);
        }

        $results = $q->executeQuery()->fetchAllAssociative();

        if (false === $limit) {
            return (int) $results[0]['total'];
        }

        $leads = [];
        foreach ($results as $result) {
            $leads[$result['internal_entity_id']] = $result;
        }

        return $leads;
    }

    /**
     * @return int
     */
    public function getIntegrationEntityCount($leadId, $integration = null, $integrationEntity = null, $internalEntity = null)
    {
        return $this->getIntegrationEntityByLead($leadId, $integration, $integrationEntity, $internalEntity, false);
    }

    /**
     * @param int|bool $limit
     *
     * @return array|int
     */
    public function getIntegrationEntityByLead($leadId, $integration = null, $integrationEntity = null, $internalEntity = null, $limit = 100)
    {
        $q = $this->_em->getConnection()->createQueryBuilder()
            ->from(MAUTIC_TABLE_PREFIX.'integration_entity', 'i');

        if (false === $limit) {
            $q->select('count(*) as total');
        } else {
            $q->select('i.integration, i.integration_entity, i.integration_entity_id, i.date_added, i.last_sync_date, i.internal');
        }

        $q->where('i.internal not like \'%error%\' and i.integration_entity_id is not null');
        $q->orderBy('i.last_sync_date', 'DESC');

        if (empty($integration)) {
            // get list of published integrations
            $pq = $this->_em->getConnection()->createQueryBuilder()
                ->select('p.name')
                ->from(MAUTIC_TABLE_PREFIX.'plugin_integration_settings', 'p')
                ->where('p.is_published = 1');
            $rows    = $pq->executeQuery()->fetchAllAssociative();
            $plugins = array_map(static fn ($i): string => "'{$i['name']}'", $rows);
            if (count($plugins) > 0) {
                $q->andWhere($q->expr()->in('i.integration', $plugins));
            } else {
                return [];
            }
        } else {
            $q->andWhere($q->expr()->eq('i.integration', ':integration'));
            $q->setParameter('integration', $integration);
        }

        $q->andWhere(
            $q->expr()->and(
                "i.internal_entity='lead'",
                $q->expr()->eq('i.internal_entity_id', ':internalEntityId')
            )
        );

        $q->setParameter('internalEntityId', $leadId);

        if (!empty($internalEntity)) {
            $q->andWhere($q->expr()->eq('i.internalEntity', ':internalEntity'));
            $q->setParameter('internalEntity', $internalEntity);
        }

        if (!empty($integrationEntity)) {
            $q->andWhere($q->expr()->eq('i.integrationEntity', ':integrationEntity'));
            $q->setParameter('integrationEntity', $integrationEntity);
        }

        $results = $q->executeQuery()->fetchAllAssociative();

        if (false === $limit && count($results) > 0) {
            return (int) $results[0]['total'];
        }

        return $results;
    }

    public function markAsDeleted(array $integrationIds, $integration, $internalEntityType): void
    {
        $q = $this->_em->getConnection()->createQueryBuilder();
        $q->update(MAUTIC_TABLE_PREFIX.'integration_entity')
            ->set('internal_entity', ':entity')
            ->where(
                $q->expr()->and(
                    $q->expr()->eq('integration', ':integration'),
                    $q->expr()->in('integration_entity_id', array_map([$q->expr(), 'literal'], $integrationIds))
                )
            )
            ->setParameter('integration', $integration)
            ->setParameter('entity', $internalEntityType.'-deleted')
            ->executeStatement();
    }

    public function findLeadsToDelete($internalEntity, $leadId): void
    {
        $q = $this->_em->getConnection()->createQueryBuilder()
            ->delete(MAUTIC_TABLE_PREFIX.'integration_entity')
            ->from(MAUTIC_TABLE_PREFIX.'integration_entity');

        $q->where('internal_entity_id = :leadId')
            ->andWhere($q->expr()->like('internal_entity', ':internalEntity'))
            ->setParameter('leadId', $leadId)
            ->setParameter('internalEntity', $internalEntity)
            ->executeStatement();
    }

    public function updateErrorLeads($internalEntity, $leadId): void
    {
        $q = $this->_em->getConnection()->createQueryBuilder()
            ->update(MAUTIC_TABLE_PREFIX.'integration_entity')
            ->set('internal_entity', ':lead')->setParameter('lead', 'lead');

        $q->where('internal_entity_id = :leadId')
            ->andWhere($q->expr()->isNotNull('integration_entity_id'))
            ->andWhere($q->expr()->eq('internal_entity', ':internalEntity'))
            ->setParameter('leadId', $leadId)
            ->setParameter('internalEntity', $internalEntity)
            ->executeStatement();

        $z = $this->_em->getConnection()->createQueryBuilder()
            ->delete(MAUTIC_TABLE_PREFIX.'integration_entity')
            ->from(MAUTIC_TABLE_PREFIX.'integration_entity');

        $z->where('internal_entity_id = :leadId')
            ->andWhere($q->expr()->isNull('integration_entity_id'))
            ->andWhere($q->expr()->like('internal_entity', ':internalEntity'))
            ->setParameter('leadId', $leadId)
            ->setParameter('internalEntity', $internalEntity)
            ->executeStatement();
    }
}