<?php

declare(strict_types=1);

/*
 * Chill is a software for social workers
 *
 * For the full copyright and license information, please view
 * the LICENSE file that was distributed with this source code.
 */

namespace Chill\MainBundle\Service\UserGroup;

use Chill\MainBundle\Entity\UserGroup;
use Chill\MainBundle\Repository\UserJobRepositoryInterface;
use Chill\MainBundle\Templating\TranslatableStringHelperInterface;
use Doctrine\DBAL\Connection;
use Doctrine\ORM\EntityManagerInterface;
use Psr\Log\LoggerInterface;
use Symfony\Contracts\Translation\TranslatorInterface;

final readonly class UserGroupRelatedToUserJobSync implements UserGroupRelatedToUserJobSyncInterface
{
    private const LOG_PREFIX = '[UserGroupRelatedToUserJobSync] ';

    public function __construct(
        private UserJobRepositoryInterface $userJobRepository,
        private EntityManagerInterface $entityManager,
        private TranslatorInterface $translator,
        private TranslatableStringHelperInterface $translatableStringHelper,
        private LoggerInterface $logger,
    ) {}

    public function __invoke(): array
    {
        $created = $this->createNotExistingUserGroups();

        $connection = $this->entityManager->getConnection();
        $stats = $connection->transactional(function (Connection $connection) {
            $removed = $this->removeUserNotRelatedToJob($connection);
            $created = $this->createNewAssociations($connection);

            return ['association_removed' => $removed, 'association_created' => $created];
        });

        $fullStats = ['userjob_created' => $created, ...$stats];

        $this->logger->info(self::LOG_PREFIX.'Executed synchronisation', $fullStats);

        return $fullStats;
    }

    private function createNotExistingUserGroups(): int
    {
        $jobs = $this->userJobRepository->findAllNotAssociatedWithUserGroup();
        $counter = 0;

        foreach ($jobs as $job) {
            $userGroup = new UserGroup();
            $userGroup->setUserJob($job);
            $userGroup->setLabel(
                [
                    $this->translator->getLocale() => $this->translator->trans(
                        'user_group.label_related_to_user_job',
                        ['job' => $this->translatableStringHelper->localize($job->getLabel())]
                    )]
            );
            $userGroup->setBackgroundColor('#e5a50a')->setForegroundColor('#f6f5f4');

            $this->entityManager->persist($userGroup);
            $this->logger->info(self::LOG_PREFIX.'Will create user group', ['job' => $this->translatableStringHelper->localize($userGroup->getLabel())]);
            ++$counter;
        }

        $this->entityManager->flush();

        return $counter;
    }

    private function removeUserNotRelatedToJob(Connection $connection): int
    {
        $sql = <<<'SQL'
        DELETE FROM chill_main_user_group_user
        USING users AS u, chill_main_user_group ug
        WHERE
            chill_main_user_group_user.usergroup_id = ug.id
            AND chill_main_user_group_user.user_id = u.id
            -- only where user_group.userjob_id is set (we ignore groups not automatically created)
            AND ug.userjob_id IS NOT NULL
            AND (
                -- Case 1: User has no job history records matching the time period
                NOT EXISTS (
                    SELECT 1 FROM chill_main_user_job_history jh
                    WHERE jh.user_id = u.id
                    AND tsrange(jh.startdate, jh.enddate) @> localtimestamp
                )
                OR
                -- Case 2: User has job history but with different job_id or user is disabled
                EXISTS (
                    SELECT 1 FROM chill_main_user_job_history jh
                    WHERE jh.user_id = u.id
                    AND tsrange(jh.startdate, jh.enddate) @> localtimestamp
                    AND (jh.job_id <> ug.userjob_id OR u.enabled IS FALSE OR jh.job_id IS NULL)
                )
            )
        SQL;

        $result = $connection->executeQuery($sql);

        return $result->rowCount();
    }

    private function createNewAssociations(Connection $connection): int
    {
        $sql = <<<'SQL'
            INSERT INTO chill_main_user_group_user (usergroup_id, user_id)
            SELECT cmug.id, jh.user_id
            FROM chill_main_user_group cmug
            JOIN chill_main_user_job_history jh ON jh.job_id = cmug.userjob_id AND tsrange(jh.startdate, jh.enddate) @> localtimestamp
            JOIN users u ON u.id = jh.user_id
            WHERE cmug.userjob_id IS NOT NULL AND u.enabled IS TRUE
            ON CONFLICT DO NOTHING
            SQL;

        $result = $connection->executeQuery($sql);

        return $result->rowCount();
    }
}
