<?php

declare(strict_types=1);

/*
 * This file is part of the VITICORE2 package.
 *
 * (c) CP Creation <web@cpcreation.fr>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace CpCreation\VitiCore\Marketing;

use Doctrine\DBAL\ArrayParameterType;
use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Types\Types;

final readonly class ReviewDataRetentionPurger
{
    private const int BATCH_SIZE = 500;

    private const array REVIEW_EMAIL_TYPES = [
        'review_invitation',
        'review_invitation_reminder',
        'review_submitted',
        'review_rejected',
        'review_withdrawn',
    ];

    public function __construct(
        private Connection $connection,
        private ReviewPublicationPolicy $publicationPolicy,
    ) {}

    public function purge(?\DateTimeImmutable $now = null): ReviewDataRetentionPurgeResult
    {
        $now ??= new \DateTimeImmutable();

        return $this->connection->transactional(function (Connection $connection) use ($now): ReviewDataRetentionPurgeResult {
            $unusedInvitationIds = $this->findUnusedInvitationIds($connection, $now);
            $reviewInvitationIds = $this->findExpiredReviewInvitationIds($connection, $now);
            $allInvitationIds = array_values(array_unique([
                ...$unusedInvitationIds,
                ...$reviewInvitationIds,
            ]));

            if ([] === $allInvitationIds) {
                return new ReviewDataRetentionPurgeResult(0, 0, 0, 0);
            }

            $emailLogsDeleted = $this->deleteEmailLogs($connection, $allInvitationIds);
            $reviewsDeleted = [] === $reviewInvitationIds
                ? 0
                : $connection->executeStatement(
                    'DELETE FROM marketing_product_review WHERE invitation_id IN (:invitationIds)',
                    ['invitationIds' => $reviewInvitationIds],
                    ['invitationIds' => ArrayParameterType::STRING],
                );

            $connection->executeStatement(
                'UPDATE marketing_review_solicitation_opt_out SET source_invitation_id = NULL WHERE source_invitation_id IN (:invitationIds)',
                ['invitationIds' => $allInvitationIds],
                ['invitationIds' => ArrayParameterType::STRING],
            );
            $connection->executeStatement(
                <<<'SQL'
                    UPDATE marketing_review_invitation
                    SET recipient_email = CONCAT('redacted-', id::text),
                        token_hash = MD5(id::text) || MD5('review-' || id::text),
                        reminder_token_hash = NULL,
                        status = CASE WHEN status IN ('scheduled', 'sent') THEN 'expired' ELSE status END,
                        personal_data_purged_at = :purgedAt,
                        updated_at = :purgedAt
                    WHERE id IN (:invitationIds)
                    SQL,
                [
                    'purgedAt' => $now,
                    'invitationIds' => $allInvitationIds,
                ],
                [
                    'purgedAt' => Types::DATETIME_IMMUTABLE,
                    'invitationIds' => ArrayParameterType::STRING,
                ],
            );

            return new ReviewDataRetentionPurgeResult(
                unusedInvitationsPurged: \count($unusedInvitationIds),
                reviewInvitationsPurged: \count($reviewInvitationIds),
                reviewsDeleted: $reviewsDeleted,
                emailLogsDeleted: $emailLogsDeleted,
            );
        });
    }

    /**
     * @return list<string>
     */
    private function findUnusedInvitationIds(Connection $connection, \DateTimeImmutable $now): array
    {
        /** @var list<string> $ids */
        $ids = $connection->fetchFirstColumn(
            <<<'SQL'
                SELECT invitation.id::text
                FROM marketing_review_invitation invitation
                WHERE invitation.personal_data_purged_at IS NULL
                  AND invitation.expires_at <= :purgeBefore
                  AND NOT EXISTS (
                      SELECT 1
                      FROM marketing_product_review review
                      WHERE review.invitation_id = invitation.id
                  )
                ORDER BY invitation.expires_at ASC
                LIMIT :batchSize
                SQL,
            [
                'purgeBefore' => $this->publicationPolicy->unusedInvitationPurgeBefore($now),
                'batchSize' => self::BATCH_SIZE,
            ],
            [
                'purgeBefore' => Types::DATETIME_IMMUTABLE,
                'batchSize' => Types::INTEGER,
            ],
        );

        return $ids;
    }

    /**
     * @return list<string>
     */
    private function findExpiredReviewInvitationIds(Connection $connection, \DateTimeImmutable $now): array
    {
        /** @var list<string> $ids */
        $ids = $connection->fetchFirstColumn(
            <<<'SQL'
                SELECT invitation.id::text
                FROM marketing_review_invitation invitation
                WHERE invitation.personal_data_purged_at IS NULL
                  AND invitation.expires_at <= :now
                  AND EXISTS (
                      SELECT 1
                      FROM marketing_product_review review
                      WHERE review.invitation_id = invitation.id
                  )
                  AND NOT EXISTS (
                      SELECT 1
                      FROM marketing_product_review review
                      WHERE review.invitation_id = invitation.id
                        AND (
                            COALESCE(
                                review.withdrawn_at,
                                review.rejected_at,
                                review.published_at,
                                review.submitted_at
                            ) >= :retainedSince
                            OR EXISTS (
                                SELECT 1
                                FROM marketing_product_review_report report
                                WHERE report.review_id = review.id
                                  AND COALESCE(report.resolved_at, report.reported_at) >= :retainedSince
                            )
                            OR EXISTS (
                                SELECT 1
                                FROM marketing_product_review_moderation_action action
                                WHERE action.review_id = review.id
                                  AND action.occurred_at >= :retainedSince
                            )
                        )
                  )
                ORDER BY invitation.expires_at ASC
                LIMIT :batchSize
                SQL,
            [
                'now' => $now,
                'retainedSince' => $this->publicationPolicy->retainedReviewDataSince($now),
                'batchSize' => self::BATCH_SIZE,
            ],
            [
                'now' => Types::DATETIME_IMMUTABLE,
                'retainedSince' => Types::DATETIME_IMMUTABLE,
                'batchSize' => Types::INTEGER,
            ],
        );

        return $ids;
    }

    /**
     * @param list<string> $invitationIds
     */
    private function deleteEmailLogs(Connection $connection, array $invitationIds): int
    {
        return $connection->executeStatement(
            <<<'SQL'
                DELETE FROM mailer_email_log
                WHERE type IN (:emailTypes)
                  AND cart_id IN (
                      SELECT cart_id
                      FROM marketing_review_invitation
                      WHERE id IN (:invitationIds)
                  )
                SQL,
            [
                'emailTypes' => self::REVIEW_EMAIL_TYPES,
                'invitationIds' => $invitationIds,
            ],
            [
                'emailTypes' => ArrayParameterType::STRING,
                'invitationIds' => ArrayParameterType::STRING,
            ],
        );
    }
}
