<?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;

/**
 * Backfills SentMessage.toAddress/subject/toName for mail sent before those columns existed.
 *
 * The Postausgang used to search `emailData LIKE '%…%'` — a leading wildcard over a
 * JSON blob holding every mail's full HTML body, so no index could ever serve it and
 * each keystroke read the whole table off disk. The search now runs against these
 * three columns instead, which means historic rows have to carry them too or they
 * simply stop being findable.
 *
 * toName mirrors what the list used to resolve per row by walking log→users; lifting
 * it onto the message is what removes that query-per-row.
 *
 * Runs after the schema diff (contao:migrate) has added the columns.
 */
class SentEmailRecipientColumnsMigration extends AbstractMigration
{
    public function __construct(private readonly Connection $db)
    {
    }

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

        if (!$schemaManager->tablesExist(['SentMessage'])) {
            return false;
        }

        $columns = $schemaManager->listTableColumns('SentMessage');
        foreach (['toaddress', 'toname', 'subject'] as $column) {
            if (!isset($columns[$column])) {
                // Columns not created yet — let the schema diff run first.
                return false;
            }
        }

        return (bool) $this->db
            ->executeQuery("SELECT 1 FROM `SentMessage` WHERE type = 'email' AND toAddress IS NULL AND JSON_EXTRACT(emailData, '$.to') IS NOT NULL LIMIT 1")
            ->fetchOne()
        ;
    }

    public function run(): MigrationResult
    {
        // Gated on toAddress alone, which every sent mail has: a subject may legitimately
        // be absent, so keying the condition on it would leave rows matching forever.
        $addresses = $this->db->executeStatement(
            "UPDATE `SentMessage`
                SET toAddress = LEFT(JSON_UNQUOTE(JSON_EXTRACT(emailData, '$.to')), 255),
                    subject = LEFT(JSON_UNQUOTE(JSON_EXTRACT(emailData, '$.subject')), 255)
                WHERE type = 'email'
                  AND toAddress IS NULL
                  AND JSON_EXTRACT(emailData, '$.to') IS NOT NULL"
        );

        // The name the outbox showed for a recipient, resolved once here rather than
        // per rendered row. A log carries at most one account for transactional mail;
        // the lowest id keeps the pick deterministic if it ever carries more.
        $names = 0;
        if ($this->db->createSchemaManager()->tablesExist(['UserMessengerLog', 'User'])) {
            $names = $this->db->executeStatement(
                "UPDATE `SentMessage` m
                    INNER JOIN (
                        SELECT log_id, MIN(user_id) AS user_id
                        FROM UserMessengerLog
                        GROUP BY log_id
                    ) pick ON pick.log_id = m.log_id
                    INNER JOIN `User` u ON u.id = pick.user_id
                    SET m.toName = LEFT(NULLIF(TRIM(CONCAT(COALESCE(u.firstname, ''), ' ', COALESCE(u.lastname, ''))), ''), 255)
                    WHERE m.type = 'email' AND m.toName IS NULL"
            );
        }

        return $this->createResult(true, \sprintf('Backfilled recipient columns on %d sent mail(s), of which %d gained a recipient name.', $addresses, $names));
    }
}
