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

/**
 * Moves free-text comments out of the per-entity *Log tables into the unified
 * {@see \Ferienpass\CoreBundle\Entity\ConversationItem} table. The *Log tables keep only
 * system events from now on.
 *
 * Each comment is copied with the **log row's own uuid as the ConversationItem primary
 * key**, so the migration is idempotent: a `NOT EXISTS` guard skips rows already
 * copied and re-running is a no-op. Runs after the schema diff has created the
 * ConversationItem table; the source `comment` columns are dropped in a later, separate
 * step once the backfill is verified in production.
 */
class MoveCommentsToConversationItemMigration extends AbstractMigration
{
    public function __construct(private readonly Connection $db)
    {
    }

    public function shouldRun(): bool
    {
        if (!$this->db->createSchemaManager()->tablesExist(['ConversationItem'])) {
            // Schema diff hasn't created the target table yet.
            return false;
        }

        foreach ($this->sources() as $source) {
            if (!$this->commentColumnExists($source['table'])) {
                continue;
            }

            $pending = (bool) $this->db
                ->executeQuery(\sprintf(
                    'SELECT 1 FROM `%1$s` src WHERE src.comment IS NOT NULL AND (%2$s) AND NOT EXISTS (SELECT 1 FROM `ConversationItem` c WHERE c.uuid = src.uuid) LIMIT 1',
                    $source['table'],
                    $source['where'],
                ))
                ->fetchOne()
            ;

            if ($pending) {
                return true;
            }
        }

        return false;
    }

    public function run(): MigrationResult
    {
        $total = 0;

        foreach ($this->sources() as $source) {
            if (!$this->commentColumnExists($source['table'])) {
                continue;
            }

            $total += (int) $this->db->executeStatement(\sprintf(
                'INSERT INTO `ConversationItem` (uuid, `%1$s`, author_id, body, channel, visibility, createdAt)
                 SELECT src.uuid, src.`%2$s`, src.user_id, src.comment, \'app\', \'internal\', src.createdAt
                 FROM `%3$s` src
                 WHERE src.comment IS NOT NULL
                   AND (%4$s)
                   AND NOT EXISTS (SELECT 1 FROM `ConversationItem` c WHERE c.uuid = src.uuid)',
                $source['target'],
                $source['source'],
                $source['table'],
                $source['where'],
            ));
        }

        return $this->createResult(true, \sprintf('Migrated %d comment(s) into the ConversationItem table.', $total));
    }

    /**
     * One INSERT per row: each log row maps to exactly one ConversationItem subject FK.
     * ParticipantLog is split — rows carrying an attendance become attendance
     * comments, the rest become participant comments (mirroring how the two
     * comment kinds were originally created). The `where` clauses are mutually
     * exclusive, so a ParticipantLog row is never copied twice.
     *
     * @return list<array{table: string, source: string, target: string, where: string}>
     */
    private function sources(): array
    {
        return [
            ['table' => 'OfferLog', 'source' => 'offer_id', 'target' => 'offer_id', 'where' => 'src.offer_id IS NOT NULL'],
            ['table' => 'ParticipantLog', 'source' => 'attendance_id', 'target' => 'attendance_id', 'where' => 'src.attendance_id IS NOT NULL'],
            ['table' => 'ParticipantLog', 'source' => 'participant_id', 'target' => 'participant_id', 'where' => 'src.attendance_id IS NULL AND src.participant_id IS NOT NULL'],
            ['table' => 'EditionLog', 'source' => 'edition_id', 'target' => 'edition_id', 'where' => 'src.edition_id IS NOT NULL'],
            ['table' => 'HostActivity', 'source' => 'host_id', 'target' => 'host_id', 'where' => 'src.host_id IS NOT NULL'],
            ['table' => 'UserActivity', 'source' => 'account_id', 'target' => 'account_id', 'where' => 'src.account_id IS NOT NULL'],
        ];
    }

    private function commentColumnExists(string $table): bool
    {
        $schemaManager = $this->db->createSchemaManager();

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

        // Safe to keep in the codebase after the `comment` column is dropped.
        return isset($schemaManager->listTableColumns($table)['comment']);
    }
}
