<?php

namespace DoctrineMigrations;

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

final class Version20260912000600 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Post metadata plus categories and tags, without touching any stored document.';
    }

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

    /**
     * Post metadata lives on the page, not in the block document: it describes the page, not its
     * content. That keeps the document schema unchanged and the editor untouched.
     * Columns only – see ADR 010 for why index changes would rebuild the table on SQLite.
     */
    public function up(Schema $schema): void
    {
        $page = $schema->getTable('page');
        $page->addColumn('excerpt', 'string', ['length' => 500, 'notnull' => false]);
        $page->addColumn('author', 'string', ['length' => 120, 'notnull' => false]);
        $page->addColumn('featured_media_id', 'string', ['length' => 32, 'notnull' => false]);
        // Written when a post is published, read for ordering and pagination. The editable value
        // is the draft metadata; this column is what the public archive sorts by.
        $page->addColumn('published_at', 'datetime_immutable', ['notnull' => false]);

        $term = $schema->createTable('term');
        $term->addColumn('id', 'string', ['length' => 64]);
        $term->addColumn('website_id', 'string', ['length' => 64]);
        $term->addColumn('locale', 'string', ['length' => 12]);
        // 'category' or 'tag'. One table because both behave identically apart from their archive.
        $term->addColumn('kind', 'string', ['length' => 16]);
        $term->addColumn('slug', 'string', ['length' => 80]);
        $term->addColumn('name', 'string', ['length' => 120]);
        $term->setPrimaryKey(['id']);
        $term->addUniqueIndex(['website_id', 'locale', 'kind', 'slug'], 'uniq_term_slug');

        $assignment = $schema->createTable('page_term');
        $assignment->addColumn('page_id', 'string', ['length' => 64]);
        $assignment->addColumn('term_id', 'string', ['length' => 64]);
        $assignment->setPrimaryKey(['page_id', 'term_id']);
        $assignment->addIndex(['term_id'], 'idx_page_term_term');
    }

    public function postUp(Schema $schema): void
    {
        self::applyIndexes($this->connection);
    }

    /** Shared with the test bootstraps so they get the same ordering index as a migrated database. */
    public static function applyIndexes(Connection $connection): void
    {
        $manager = $connection->createSchemaManager();
        if (isset($manager->listTableIndexes('page')['idx_page_published_at'])) {
            return;
        }
        $connection->executeStatement($connection->getDatabasePlatform()->getCreateIndexSQL(
            new Index('idx_page_published_at', ['website_id', 'locale', 'type', 'published_at']),
            'page',
        ));
    }

    public function down(Schema $schema): void
    {
        $this->throwIrreversibleMigrationException('Contains post metadata and term assignments. Restore a verified database backup with matching code.');
    }
}