<?php

namespace DoctrineMigrations;

use Doctrine\DBAL\Connection;
use Doctrine\DBAL\Schema\Index;
use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

final class Version20260912000900 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Scheduled publication and a recycle bin instead of immediate deletion.';
    }

    public function isTransactional(): bool
    {
        return false;
    }

    public function up(Schema $schema): void
    {
        $page = $schema->getTable('page');
        // When set and in the past, the stored draft is published by cms:publish-due.
        $page->addColumn('publish_at', 'datetime_immutable', ['notnull' => false]);
        // A page in the bin keeps every revision but answers publicly with 404 and stays out of
        // menus, archives and the sitemap until it is restored or finally removed.
        $page->addColumn('deleted_at', 'datetime_immutable', ['notnull' => false]);

        // created_at only has second precision, and several saves land in the same second. The
        // revision list and "restore this version" need a strict order, so every revision of a
        // page carries a counter. Filled in postUp() from the existing timestamps.
        $schema->getTable('revision')->addColumn('seq', 'integer', ['notnull' => true, 'default' => 0]);
    }

    public function postUp(Schema $schema): void
    {
        // Existing revisions keep their order; within one second the previous order is preserved.
        foreach ($this->connection->fetchAllAssociative('SELECT DISTINCT page_id FROM revision') as $page) {
            $number = 0;
            foreach ($this->connection->fetchAllAssociative(
                'SELECT id FROM revision WHERE page_id = ? ORDER BY created_at ASC, id ASC',
                [$page['page_id']],
            ) as $revision) {
                $this->connection->executeStatement('UPDATE revision SET seq = ? WHERE id = ?', [++$number, $revision['id']]);
            }
        }
        self::applyIndexes($this->connection);
    }

    /** Shared with the test bootstraps so they get the same lookup indexes as a migrated database. */
    public static function applyIndexes(Connection $connection): void
    {
        $manager = $connection->createSchemaManager();
        $existing = $manager->listTableIndexes('page');
        foreach ([
            new Index('idx_page_publish_at', ['publish_at']),
            new Index('idx_page_deleted_at', ['deleted_at']),
        ] as $index) {
            if (!isset($existing[$index->getName()])) {
                $connection->executeStatement($connection->getDatabasePlatform()->getCreateIndexSQL($index, 'page'));
            }
        }
        if (!isset($manager->listTableIndexes('revision')['idx_revision_order'])) {
            $connection->executeStatement($connection->getDatabasePlatform()->getCreateIndexSQL(
                new Index('idx_revision_order', ['page_id', 'seq']),
                'revision',
            ));
        }
    }

    public function down(Schema $schema): void
    {
        $this->throwIrreversibleMigrationException('Dropping deleted_at would silently republish pages an editor removed. Restore a verified database backup with matching code.');
    }
}