<?php

declare(strict_types=1);

namespace CpCreation\VitiCore\Tests\User;

use CpCreation\VitiCore\User\ExpiredRegistrationPurger;
use Doctrine\DBAL\ArrayParameterType;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\Platforms\PostgreSQLPlatform;
use Doctrine\DBAL\Types\Types;
use PHPUnit\Framework\TestCase;
use Ramsey\Uuid\Uuid;
use Symfony\Component\Dotenv\Dotenv;

final class ExpiredRegistrationPurgerPostgreSqlTest extends TestCase
{
    public function testItDeletesOnlyOldInactiveRegistrationsAndTheirOwnTechnicalTraces(): void
    {
        $connection = $this->createPostgreSqlConnection();
        $connection->beginTransaction();

        try {
            $suffix = bin2hex(random_bytes(6));
            $now = new \DateTimeImmutable('2026-08-27 12:00:00');
            $oldExpiration = '2026-08-10 12:00:00';
            $recentExpiration = '2026-08-25 12:00:00';
            $eligibleId = Uuid::uuid4()->toString();
            $eligibleEmail = sprintf('purge-eligible-%s@example.invalid', $suffix);
            $recentId = Uuid::uuid4()->toString();
            $recentlyUpdatedId = Uuid::uuid4()->toString();
            $verifiedId = Uuid::uuid4()->toString();
            $adminId = Uuid::uuid4()->toString();
            $resetId = Uuid::uuid4()->toString();
            $addressId = Uuid::uuid4()->toString();
            $addressUserId = Uuid::uuid4()->toString();
            $groupId = Uuid::uuid4()->toString();
            $groupUserId = Uuid::uuid4()->toString();
            $cartUserId = Uuid::uuid4()->toString();
            $promoUserId = Uuid::uuid4()->toString();
            $businessLogUserId = Uuid::uuid4()->toString();
            $guestOrderUserId = Uuid::uuid4()->toString();

            $this->insertUser($connection, $eligibleId, $eligibleEmail, $oldExpiration, $suffix.'-eligible');
            $this->insertUser($connection, $recentId, sprintf('purge-recent-%s@example.invalid', $suffix), $recentExpiration, $suffix.'-recent');
            $this->insertUser(
                $connection,
                $recentlyUpdatedId,
                sprintf('purge-updated-%s@example.invalid', $suffix),
                $oldExpiration,
                $suffix.'-updated',
                updatedAt: '2026-08-25 12:00:00',
            );
            $this->insertUser($connection, $verifiedId, sprintf('purge-verified-%s@example.invalid', $suffix), $oldExpiration, $suffix.'-verified', verified: true);
            $this->insertUser($connection, $adminId, sprintf('purge-admin-%s@example.invalid', $suffix), $oldExpiration, $suffix.'-admin', roles: ['ROLE_ADMIN']);
            $this->insertUser(
                $connection,
                $resetId,
                sprintf('purge-reset-%s@example.invalid', $suffix),
                $oldExpiration,
                $suffix.'-reset',
                resetToken: $suffix.'-reset-token',
                resetExpiresAt: '2026-08-28 12:00:00',
            );

            $connection->executeStatement(
                'INSERT INTO user_invoice_address (id, created_at, updated_at) VALUES (:id, :now, :now)',
                ['id' => $addressId, 'now' => '2026-08-01 12:00:00'],
            );
            $this->insertUser(
                $connection,
                $addressUserId,
                sprintf('purge-address-%s@example.invalid', $suffix),
                $oldExpiration,
                $suffix.'-address',
                invoiceAddressId: $addressId,
            );

            $connection->executeStatement(
                <<<'SQL'
                    INSERT INTO product_price_group (
                        id, name, by_default, view_ht, free_shipping, created_at, updated_at
                    ) VALUES (
                        :id, :name, FALSE, FALSE, FALSE, :now, :now
                    )
                    SQL,
                ['id' => $groupId, 'name' => 'PURGE TEST '.$suffix, 'now' => '2026-08-01 12:00:00'],
            );
            $this->insertUser(
                $connection,
                $groupUserId,
                sprintf('purge-group-%s@example.invalid', $suffix),
                $oldExpiration,
                $suffix.'-group',
                groupId: $groupId,
            );

            $this->insertUser($connection, $cartUserId, sprintf('purge-cart-%s@example.invalid', $suffix), $oldExpiration, $suffix.'-cart');
            $connection->executeStatement(
                <<<'SQL'
                    INSERT INTO cart_cart (id, user_id, expires_at, created_at, updated_at)
                    VALUES (:id, :userId, :expiresAt, :now, :now)
                    SQL,
                [
                    'id' => Uuid::uuid4()->toString(),
                    'userId' => $cartUserId,
                    'expiresAt' => '2026-08-02 12:00:00',
                    'now' => '2026-08-01 12:00:00',
                ],
            );

            $this->insertUser($connection, $promoUserId, sprintf('purge-promo-%s@example.invalid', $suffix), $oldExpiration, $suffix.'-promo');
            $connection->executeStatement(
                <<<'SQL'
                    INSERT INTO cart_promo (id, assigned_user_id, created_at, updated_at)
                    VALUES (:id, :userId, :now, :now)
                    SQL,
                [
                    'id' => Uuid::uuid4()->toString(),
                    'userId' => $promoUserId,
                    'now' => '2026-08-01 12:00:00',
                ],
            );

            $this->insertUser($connection, $businessLogUserId, sprintf('purge-business-log-%s@example.invalid', $suffix), $oldExpiration, $suffix.'-business-log');
            $this->insertEmailLog(
                $connection,
                Uuid::uuid4()->toString(),
                $businessLogUserId,
                sprintf('purge-business-log-%s@example.invalid', $suffix),
                'delivery_customer',
            );

            $guestEmail = sprintf('purge-guest-order-%s@example.invalid', $suffix);
            $this->insertUser($connection, $guestOrderUserId, $guestEmail, $oldExpiration, $suffix.'-guest-order');
            $this->insertCompletedGuestOrder($connection, $guestEmail);

            $linkedTechnicalLogId = Uuid::uuid4()->toString();
            $historicalTechnicalLogId = Uuid::uuid4()->toString();
            $this->insertEmailLog($connection, $linkedTechnicalLogId, $eligibleId, $eligibleEmail, 'account_activation');
            $this->insertEmailLog($connection, $historicalTechnicalLogId, null, $eligibleEmail, 'account_activation');
            $connection->executeStatement(
                <<<'SQL'
                    INSERT INTO user_user_audit (type, object_id, diffs, created_at)
                    VALUES ('insert', :userId, :diffs, :now)
                    SQL,
                [
                    'userId' => $eligibleId,
                    'diffs' => json_encode(['email' => [null, $eligibleEmail]], \JSON_THROW_ON_ERROR),
                    'now' => '2026-08-01 12:00:00',
                ],
            );

            $result = (new ExpiredRegistrationPurger($connection))->purge($now, batchSize: 50, maxBatches: 2);

            self::assertSame(1, $result->deletedCount);
            self::assertSame(1, $result->emailLogsDeleted);
            self::assertSame(1, $result->auditRowsDeleted);
            self::assertSame(0, (int) $connection->fetchOne(
                'SELECT COUNT(*) FROM user_user WHERE id = :id',
                ['id' => $eligibleId],
            ));

            $protectedIds = [
                $recentId,
                $recentlyUpdatedId,
                $verifiedId,
                $adminId,
                $resetId,
                $addressUserId,
                $groupUserId,
                $cartUserId,
                $promoUserId,
                $businessLogUserId,
                $guestOrderUserId,
            ];
            $persistedProtectedIds = $connection->fetchFirstColumn(
                'SELECT id::text FROM user_user WHERE id IN (:ids) ORDER BY id',
                ['ids' => $protectedIds],
                ['ids' => ArrayParameterType::STRING],
            );
            sort($protectedIds);

            self::assertSame($protectedIds, $persistedProtectedIds);
            self::assertSame(0, (int) $connection->fetchOne(
                'SELECT COUNT(*) FROM mailer_email_log WHERE id = :id',
                ['id' => $linkedTechnicalLogId],
            ));
            self::assertSame(1, (int) $connection->fetchOne(
                'SELECT COUNT(*) FROM mailer_email_log WHERE id = :id',
                ['id' => $historicalTechnicalLogId],
            ));
            self::assertSame(0, (int) $connection->fetchOne(
                'SELECT COUNT(*) FROM user_user_audit WHERE object_id = :id',
                ['id' => $eligibleId],
            ));
        } finally {
            $connection->rollBack();
            $connection->close();
        }
    }

    /**
     * @param list<string> $roles
     */
    private function insertUser(
        Connection $connection,
        string $id,
        string $email,
        string $expiresAt,
        string $token,
        bool $verified = false,
        array $roles = [],
        ?string $resetToken = null,
        ?string $resetExpiresAt = null,
        ?string $invoiceAddressId = null,
        ?string $groupId = null,
        string $updatedAt = '2026-08-01 12:00:00',
    ): void {
        $connection->executeStatement(
            <<<'SQL'
                INSERT INTO user_user (
                    id, invoice_address_id, group_id, roles, email, password,
                    marketing_opt_in, is_verified, email_verification_token,
                    email_verification_requested_at, email_verification_expires_at,
                    reset_password_token, reset_password_expires_at, created_at, updated_at
                ) VALUES (
                    :id, :invoiceAddressId, :groupId, :roles, :email, 'test-password-hash',
                    FALSE, :verified, :token, :requestedAt, :expiresAt,
                    :resetToken, :resetExpiresAt, :createdAt, :updatedAt
                )
                SQL,
            [
                'id' => $id,
                'invoiceAddressId' => $invoiceAddressId,
                'groupId' => $groupId,
                'roles' => json_encode($roles, \JSON_THROW_ON_ERROR),
                'email' => $email,
                'verified' => $verified,
                'token' => $token,
                'requestedAt' => '2026-08-01 12:00:00',
                'expiresAt' => $expiresAt,
                'resetToken' => $resetToken,
                'resetExpiresAt' => $resetExpiresAt,
                'createdAt' => '2026-08-01 12:00:00',
                'updatedAt' => $updatedAt,
            ],
            ['verified' => Types::BOOLEAN],
        );
    }

    private function insertEmailLog(
        Connection $connection,
        string $id,
        ?string $userId,
        string $recipientEmail,
        string $type,
    ): void {
        $connection->executeStatement(
            <<<'SQL'
                INSERT INTO mailer_email_log (id, user_id, type, recipient_email, status, queued_at)
                VALUES (:id, :userId, :type, :recipientEmail, 'sent', :now)
                SQL,
            [
                'id' => $id,
                'userId' => $userId,
                'type' => $type,
                'recipientEmail' => $recipientEmail,
                'now' => '2026-08-01 12:00:00',
            ],
        );
    }

    private function insertCompletedGuestOrder(Connection $connection, string $email): void
    {
        $invoiceAddressId = Uuid::uuid4()->toString();
        $deliveryAddressId = Uuid::uuid4()->toString();
        $cartId = Uuid::uuid4()->toString();
        $now = '2026-08-01 12:00:00';

        $connection->executeStatement(
            'INSERT INTO cart_invoice_address (id, email) VALUES (:id, :email)',
            ['id' => $invoiceAddressId, 'email' => 'different-'.$email],
        );
        $connection->executeStatement(
            'INSERT INTO cart_delivery_address (id, email) VALUES (:id, :email)',
            ['id' => $deliveryAddressId, 'email' => $email],
        );
        $connection->executeStatement(
            <<<'SQL'
                INSERT INTO cart_cart (
                    id, invoice_address_id, delivery_address_id, expires_at, created_at, updated_at
                ) VALUES (
                    :id, :invoiceAddressId, :deliveryAddressId, :expiresAt, :now, :now
                )
                SQL,
            [
                'id' => $cartId,
                'invoiceAddressId' => $invoiceAddressId,
                'deliveryAddressId' => $deliveryAddressId,
                'expiresAt' => '2026-08-02 12:00:00',
                'now' => $now,
            ],
        );
        $connection->executeStatement(
            <<<'SQL'
                INSERT INTO cart_cart_checkout (id, cart_cart_id, payed_at, created_at, updated_at)
                VALUES (:id, :cartId, :now, :now, :now)
                SQL,
            ['id' => Uuid::uuid4()->toString(), 'cartId' => $cartId, 'now' => $now],
        );
        $connection->executeStatement(
            <<<'SQL'
                INSERT INTO cart_cart_item (id, cart_cart_id, quantity, created_at, updated_at)
                VALUES (:id, :cartId, 1, :now, :now)
                SQL,
            ['id' => Uuid::uuid4()->toString(), 'cartId' => $cartId, 'now' => $now],
        );
    }

    private function createPostgreSqlConnection(): Connection
    {
        $databaseUrl = $_ENV['DATABASE_URL'] ?? $_SERVER['DATABASE_URL'] ?? null;

        if ((!\is_string($databaseUrl) || '' === $databaseUrl) && is_file(getcwd().'/.env')) {
            (new Dotenv())->bootEnv(getcwd().'/.env');
            $databaseUrl = $_ENV['DATABASE_URL'] ?? $_SERVER['DATABASE_URL'] ?? null;
        }

        if (!\is_string($databaseUrl) || '' === $databaseUrl) {
            self::markTestSkipped('DATABASE_URL est indisponible pour le test PostgreSQL.');
        }

        $connection = DriverManager::getConnection(['url' => $databaseUrl]);

        if (!$connection->getDatabasePlatform() instanceof PostgreSQLPlatform) {
            $connection->close();
            self::markTestSkipped('Ce test necessite PostgreSQL.');
        }

        $requiredTables = [
            'user_user',
            'user_user_audit',
            'mailer_email_log',
            'cart_cart',
            'cart_cart_checkout',
            'cart_cart_item',
            'cart_promo',
            'product_price_group',
        ];

        if (!$connection->createSchemaManager()->tablesExist($requiredTables)) {
            $connection->close();
            self::markTestSkipped('Le schema VitiCore complet est requis pour ce test PostgreSQL.');
        }

        return $connection;
    }
}
