<?php

declare(strict_types=1);

/*
 * This file is part of the Ferienpass package.
 *
 * (c) Richard Henkenjohann <richard@ferienpass.online>
 *
 * For more information visit the project website <https://ferienpass.online>
 * or the documentation under <https://docs.ferienpass.online>.
 */

namespace Ferienpass\CoreBundle\Migration;

use Contao\CoreBundle\Migration\AbstractMigration;
use Contao\CoreBundle\Migration\MigrationResult;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Types\BinaryType;
use Ferienpass\CoreBundle\Entity\Attendance;
use Ferienpass\CoreBundle\Entity\Host;
use Ferienpass\CoreBundle\Entity\User;
use Symfony\Bridge\Doctrine\Types\UuidType;
use Symfony\Component\DependencyInjection\Attribute\Autowire;
use Symfony\Component\Uid\Uuid;

class UuidMigration extends AbstractMigration
{
    private const UUID_TABLES = [
        'AccessCode',
        'AccessCodeStrategy',
        'AttendanceStash',
        'BlockedContact',
        'BookEntry',
        'Consent',
        'Edition',
        'EditionLog',
        'EditionTask',
        'FriendCode',
        'HostActivity',
        'HostMemberAssociation',
        'Notification',
        'OfferAttachment',
        'OfferCategory',
        'OfferDate',
        'OfferLog',
        'OfferMedia',
        'OfferMemberAssociation',
        'ParticipantDuplicate',
        'ParticipantLog',
        'PaymentItem',
        'PmPaymentTransactionStatus',
        'PostalAddress',
        'ResetPasswordRequest',
        'SentMessage',
        'SentMessageEngagement',
        'UserActivity',
        'WorkflowSurvey',
    ];
    private readonly array $tables;

    public function __construct(
        private readonly Connection $db,
        #[Autowire(param: 'ferienpass.model.participant.class')]
        private readonly string $participantEntityClass,
        #[Autowire(param: 'ferienpass.model.offer.class')]
        private readonly string $offerEntityClass
    ) {
        $this->tables = [
            'Attendance' => Attendance::class,
            'Host' => Host::class,
            'Offer' => $this->offerEntityClass,
            'Participant' => $this->participantEntityClass,
            'User' => User::class,
        ];
    }

    public function shouldRun(): bool
    {
        $schemaManager = $this->db->createSchemaManager();

        foreach (array_merge(array_keys($this->tables), self::UUID_TABLES) as $tableName) {
            if (!$schemaManager->tablesExist([$tableName])) {
                continue;
            }

            $columns = $schemaManager->listTableColumns($tableName);
            if (!isset($columns['uuid'])) {
                return true;
            }

            $column = $columns['uuid'];
            $type = $column->getType();
            // If the column is not BINARY(16), we should run the migration to convert it
            if (16 !== $column->getLength() || !($type instanceof BinaryType || $type instanceof UuidType)) {
                return true;
            }

            // Check for NULL UUIDs
            if ((bool) $this->db->executeQuery("SELECT 1 FROM $tableName WHERE uuid IS NULL LIMIT 1")->fetchOne()) {
                return true;
            }

            // Check for duplicated UUIDs
            if ((bool) $this->db->executeQuery("SELECT 1 FROM $tableName GROUP BY uuid HAVING COUNT(*) > 1 LIMIT 1")->fetchOne()) {
                return true;
            }

            // Check for missing unique index for Offer, OfferDate, OfferAttachment and OfferMedia
            if (\in_array($tableName, ['Offer', 'OfferDate', 'OfferAttachment', 'OfferMedia'], true)) {
                $indexes = $schemaManager->listTableIndexes($tableName);
                $hasUniqueUuidIndex = false;
                foreach ($indexes as $index) {
                    if ($index->isUnique() && $index->getColumns() === ['uuid']) {
                        $hasUniqueUuidIndex = true;
                        break;
                    }
                }
                if (!$hasUniqueUuidIndex) {
                    return true;
                }
            }
        }

        return false;
    }

    public function run(): MigrationResult
    {
        $schemaManager = $this->db->createSchemaManager();

        foreach (array_merge(array_keys($this->tables), self::UUID_TABLES) as $tableName) {
            if (!$schemaManager->tablesExist([$tableName])) {
                continue;
            }

            $columns = $schemaManager->listTableColumns($tableName);
            $pkColumn = isset($columns['id']) ? 'id' : array_key_first($columns);

            if (!isset($columns['uuid'])) {
                $this->db->executeStatement("ALTER TABLE $tableName ADD COLUMN uuid BINARY(16) DEFAULT NULL");
            } else {
                $column = $columns['uuid'];
                $type = $column->getType();
                if (16 !== $column->getLength() || !($type instanceof BinaryType || $type instanceof UuidType)) {
                    // Convert existing VARCHAR uuid to BINARY(16)

                    // 1. Update content to be HEX-friendly if it's currently a string with dashes
                    $this->db->executeStatement("UPDATE $tableName SET uuid = UNHEX(REPLACE(uuid, '-', '')) WHERE LENGTH(uuid) = 36");

                    // 2. Change column type
                    $this->db->executeStatement("ALTER TABLE $tableName MODIFY uuid BINARY(16)");
                }
            }

            // Fill NULL UUIDs
            foreach ($this->db->executeQuery("SELECT $pkColumn FROM $tableName WHERE uuid IS NULL")->fetchFirstColumn() as $pk) {
                $uuid = Uuid::v7()->toRfc4122();
                $this->db->executeStatement("UPDATE $tableName SET uuid = UNHEX(REPLACE(?, '-', '')) WHERE $pkColumn = ?", [$uuid, $pk]);
            }

            // Identify and fix duplicated UUIDs
            $duplicates = $this->db->executeQuery("SELECT HEX(uuid) FROM $tableName GROUP BY uuid HAVING COUNT(*) > 1")->fetchFirstColumn();
            foreach ($duplicates as $hexUuid) {
                if (null === $hexUuid) {
                    continue;
                }

                // Get all IDs for this UUID
                $ids = $this->db->executeQuery("SELECT $pkColumn FROM $tableName WHERE uuid = UNHEX(?)", [$hexUuid])->fetchFirstColumn();
                // Skip the first one
                array_shift($ids);
                foreach ($ids as $pk) {
                    $newUuid = Uuid::v7()->toRfc4122();
                    $this->db->executeStatement("UPDATE $tableName SET uuid = UNHEX(REPLACE(?, '-', '')) WHERE $pkColumn = ?", [$newUuid, $pk]);
                }
            }

            // Add unique index if missing for Offer, OfferDate, OfferAttachment and OfferMedia
            if (\in_array($tableName, ['Offer', 'OfferDate', 'OfferAttachment', 'OfferMedia'], true)) {
                $indexes = $schemaManager->listTableIndexes($tableName);
                $hasUniqueUuidIndex = false;
                foreach ($indexes as $index) {
                    if ($index->isUnique() && $index->getColumns() === ['uuid']) {
                        $hasUniqueUuidIndex = true;
                        break;
                    }
                }

                if (!$hasUniqueUuidIndex) {
                    $this->db->executeStatement("ALTER TABLE $tableName ADD UNIQUE (uuid)");
                }
            }
        }

        return new MigrationResult(true, 'Migration successful');
    }
}
