<?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\ThirdPartyBundle\Service;

use Chill\ThirdPartyBundle\Entity\ThirdParty;
use Doctrine\DBAL\Exception;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Mapping\ClassMetadata;
use Doctrine\ORM\Mapping\MappingException;

readonly class ThirdpartyMergeService
{
    public function __construct(private EntityManagerInterface $em) {}

    /**
     * @throws Exception
     */
    public function merge(ThirdParty $toKeep, ThirdParty $toDelete): void
    {
        $conn = $this->em->getConnection();
        $conn->beginTransaction();

        try {
            $queries = [
                ...$this->updateReferences($toKeep, $toDelete),
                ...$this->removeThirdparty($toKeep, $toDelete),
            ];

            foreach ($queries as $query) {
                $conn->executeStatement($query['sql'], $query['params']);
            }

            $conn->commit();
        } catch (\Exception $e) {
            $conn->rollBack();
            throw $e;
        }
    }

    /**
     * @throws MappingException
     */
    private function updateReferences(ThirdParty $toKeep, ThirdParty $toDelete): array
    {
        $queries = [];
        $allMeta = $this->em->getMetadataFactory()->getAllMetadata();

        foreach ($allMeta as $meta) {
            if ($meta->isMappedSuperclass) {
                continue;
            }

            $tableName = $meta->getTableName();
            foreach ($meta->getAssociationMappings() as $assoc) {
                if (ThirdParty::class !== $assoc['targetEntity']) {
                    continue;
                }

                // phpstan wants boolean for if condition
                if (($assoc['type'] & ClassMetadata::TO_ONE) !== 0) {
                    $joinColumn = $meta->getSingleAssociationJoinColumnName($assoc['fieldName']);

                    $schema = $meta->getSchemaName();
                    $prefix = null !== $schema && '' !== $schema ? $schema.'.' : '';

                    $queries[] = [
                        'sql' => "UPDATE {$prefix}{$tableName} SET {$joinColumn} = :toKeep WHERE {$joinColumn} = :toDelete",
                        'params' => ['toKeep' => $toKeep->getId(), 'toDelete' => $toDelete->getId()],
                    ];
                } elseif (ClassMetadata::MANY_TO_MANY === $assoc['type'] && isset($assoc['joinTable'])) {
                    $joinTable = $assoc['joinTable']['name'];
                    $prefix = null !== ($assoc['joinTable']['schema'] ?? null) ? $assoc['joinTable']['schema'].'.' : '';
                    $joinColumn = $assoc['joinTable']['inverseJoinColumns'][0]['name'];
                    $queries[] = [
                        'sql' => "UPDATE {$prefix}{$joinTable} SET {$joinColumn} = :toKeep WHERE {$joinColumn} = :toDelete AND NOT EXISTS (SELECT 1 FROM {$prefix}{$joinTable} WHERE {$joinColumn} = :toKeep)",
                        'params' => ['toDelete' => $toDelete->getId(), 'toKeep' => $toKeep->getId()],
                    ];

                    $queries[] = [
                        'sql' => "DELETE FROM {$prefix}{$joinTable} WHERE {$joinColumn} = :toDelete",
                        'params' => ['toDelete' => $toDelete->getId()],
                    ];
                }
            }
        }

        // Also handle many-to-many where ThirdParty is the source
        $thirdPartyMeta = $this->em->getClassMetadata(ThirdParty::class);
        foreach ($thirdPartyMeta->getAssociationMappings() as $assoc) {
            if (ClassMetadata::MANY_TO_MANY === $assoc['type'] && isset($assoc['joinTable'])) {
                $joinTable = $assoc['joinTable']['name'];
                $prefix = null !== ($assoc['joinTable']['schema'] ?? null) ? $assoc['joinTable']['schema'].'.' : '';
                $joinColumn = $assoc['joinTable']['joinColumns'][0]['name']; // Note: joinColumns, not inverseJoinColumns

                // Get the other column name to build proper duplicate check
                $otherColumn = $assoc['joinTable']['inverseJoinColumns'][0]['name'];

                $queries[] = [
                    'sql' => "UPDATE {$prefix}{$joinTable} SET {$joinColumn} = :toKeep WHERE {$joinColumn} = :toDelete AND NOT EXISTS (SELECT 1 FROM {$prefix}{$joinTable} AS t2 WHERE t2.{$joinColumn} = :toKeep AND t2.{$otherColumn} = {$prefix}{$joinTable}.{$otherColumn})",
                    'params' => ['toKeep' => $toKeep->getId(), 'toDelete' => $toDelete->getId()],
                ];

                $queries[] = [
                    'sql' => "DELETE FROM {$prefix}{$joinTable} WHERE {$joinColumn} = :toDelete",
                    'params' => ['toDelete' => $toDelete->getId()],
                ];
            }
        }

        return $queries;
    }

    public function removeThirdparty(ThirdParty $toKeep, ThirdParty $toDelete): array
    {
        return [
            [
                'sql' => 'UPDATE chill_3party.third_party SET parent_id = :toKeep WHERE parent_id = :toDelete',
                'params' => ['toKeep' => $toKeep->getId(), 'toDelete' => $toDelete->getId()],
            ],
            [
                'sql' => 'DELETE FROM chill_3party.third_party WHERE id = :toDelete',
                'params' => ['toDelete' => $toDelete->getId()],
            ],
        ];
    }
}
