Version20260120114538.php

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

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

final class Version20260120114538 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Refactor package metadata';
    }

    public function up(Schema $schema): void
    {
        $this->createMetadataTables();
        $this->fillMetadataTables();
        $this->addCurrentMetadataColumnToVersionTable();
        $this->updateVersionTableColumns();
        $this->dropVersionLinkTables();
        $this->dropVersionTableFields();
    }

    public function down(Schema $schema): void
    {
        $this->createVersionTableFields();
        $this->createVersionLinkTables();
        $this->revertVersionTableColumns();
        $this->fillVersionTables();
        $this->dropCurrentMetadataColumnFromVersionTable();
        $this->dropMetadataTables();
    }

    private function createMetadataTables(): void
    {
        $this->addSql(<<<'SQL'
            CREATE TABLE metadata (
                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                revision INT NOT NULL,
                package_name VARCHAR(255) NOT NULL,
                version_name VARCHAR(255) NOT NULL,
                normalized_version_name VARCHAR(191) NOT NULL,
                description TEXT DEFAULT NULL,
                readme TEXT DEFAULT NULL,
                homepage VARCHAR(255) DEFAULT NULL,
                license JSON DEFAULT NULL,
                type VARCHAR(255) DEFAULT NULL,
                target_dir VARCHAR(255) DEFAULT NULL,
                source JSON DEFAULT NULL,
                dist JSON DEFAULT NULL,
                autoload JSON DEFAULT NULL,
                binaries JSON DEFAULT NULL,
                include_paths JSON DEFAULT NULL,
                php_ext JSON DEFAULT NULL,
                authors JSON DEFAULT NULL,
                support JSON DEFAULT NULL,
                funding JSON DEFAULT NULL,
                extra JSON DEFAULT NULL,
                released_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL,
                created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
                last_modified_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL,
                version_id INT NOT NULL,
                package_id INT NOT NULL,
                PRIMARY KEY (id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX IDX_4F1434144BBC2705 ON metadata (version_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX IDX_4F143414F44CABFF ON metadata (package_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE UNIQUE INDEX version_revision_idx ON metadata (version_id, revision)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE TABLE metadata_keyword (
                metadata_id INT NOT NULL,
                keyword_id INT NOT NULL,
                PRIMARY KEY (metadata_id, keyword_id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX IDX_C8E2A1C3DC9EE959 ON metadata_keyword (metadata_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX IDX_C8E2A1C3115D4552 ON metadata_keyword (keyword_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE TABLE metadata_conflict_link (
                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                linked_package_name VARCHAR(191) NOT NULL,
                linked_version_constraint TEXT NOT NULL,
                index INT NOT NULL,
                metadata_id INT NOT NULL,
                PRIMARY KEY (id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX IDX_97454B50DC9EE959 ON metadata_conflict_link (metadata_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE TABLE metadata_dev_require_link (
                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                linked_package_name VARCHAR(191) NOT NULL,
                linked_version_constraint TEXT NOT NULL,
                index INT NOT NULL,
                metadata_id INT NOT NULL,
                PRIMARY KEY (id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX IDX_762BA762DC9EE959 ON metadata_dev_require_link (metadata_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE TABLE metadata_provide_link (
                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                linked_package_name VARCHAR(191) NOT NULL,
                linked_version_constraint TEXT NOT NULL,
                index INT NOT NULL,
                metadata_id INT NOT NULL,
                PRIMARY KEY (id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX IDX_571F5289DC9EE959 ON metadata_provide_link (metadata_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE TABLE metadata_replace_link (
                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                linked_package_name VARCHAR(191) NOT NULL,
                linked_version_constraint TEXT NOT NULL,
                index INT NOT NULL,
                metadata_id INT NOT NULL,
                PRIMARY KEY (id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX IDX_C5E02AEFDC9EE959 ON metadata_replace_link (metadata_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE TABLE metadata_require_link (
                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                linked_package_name VARCHAR(191) NOT NULL,
                linked_version_constraint TEXT NOT NULL,
                index INT NOT NULL,
                metadata_id INT NOT NULL,
                PRIMARY KEY (id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX IDX_380D3934DC9EE959 ON metadata_require_link (metadata_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE TABLE metadata_suggest_link (
                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                linked_package_name VARCHAR(191) NOT NULL,
                linked_version_constraint TEXT NOT NULL,
                index INT NOT NULL,
                metadata_id INT NOT NULL,
                PRIMARY KEY (id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX IDX_9C8AE50DDC9EE959 ON metadata_suggest_link (metadata_id)
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
              metadata
            ADD
              CONSTRAINT FK_4F143414F44CABFF FOREIGN KEY (package_id) REFERENCES package (id) NOT DEFERRABLE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
              metadata
            ADD
              CONSTRAINT FK_4F1434144BBC2705 FOREIGN KEY (version_id) REFERENCES version (id) ON DELETE CASCADE NOT DEFERRABLE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                metadata_keyword
            ADD
                CONSTRAINT FK_C8E2A1C3DC9EE959 FOREIGN KEY (metadata_id) REFERENCES metadata (id) ON DELETE CASCADE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                metadata_keyword
            ADD
                CONSTRAINT FK_C8E2A1C3115D4552 FOREIGN KEY (keyword_id) REFERENCES keyword (id) ON DELETE CASCADE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                metadata_conflict_link
            ADD
                CONSTRAINT FK_97454B50DC9EE959 FOREIGN KEY (metadata_id) REFERENCES metadata (id) NOT DEFERRABLE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                metadata_dev_require_link
            ADD
                CONSTRAINT FK_762BA762DC9EE959 FOREIGN KEY (metadata_id) REFERENCES metadata (id) NOT DEFERRABLE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                metadata_provide_link
            ADD
                CONSTRAINT FK_571F5289DC9EE959 FOREIGN KEY (metadata_id) REFERENCES metadata (id) NOT DEFERRABLE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                metadata_replace_link
            ADD
                CONSTRAINT FK_C5E02AEFDC9EE959 FOREIGN KEY (metadata_id) REFERENCES metadata (id) NOT DEFERRABLE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                metadata_require_link
            ADD
                CONSTRAINT FK_380D3934DC9EE959 FOREIGN KEY (metadata_id) REFERENCES metadata (id) NOT DEFERRABLE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                metadata_suggest_link
            ADD
                CONSTRAINT FK_9C8AE50DDC9EE959 FOREIGN KEY (metadata_id) REFERENCES metadata (id) NOT DEFERRABLE
        SQL);
    }

    private function fillMetadataTables(): void
    {
        $this->addSql(<<<'SQL'
            INSERT INTO metadata (
                revision,
                package_name,
                version_name,
                normalized_version_name,
                description,
                readme,
                homepage,
                license,
                type,
                target_dir,
                source,
                dist,
                autoload,
                binaries,
                include_paths,
                php_ext,
                authors,
                support,
                funding,
                extra,
                released_at,
                created_at,
                version_id,
                package_id
            )
            SELECT
                1,
                name,
                version,
                normalized_version,
                description,
                readme,
                homepage,
                license,
                type,
                target_dir,
                source,
                dist,
                autoload,
                binaries,
                include_paths,
                php_ext,
                authors,
                support,
                funding,
                extra,
                released_at,
                CURRENT_TIMESTAMP,
                id,
                package_id
            FROM version
            WHERE NOT EXISTS (SELECT 1 FROM metadata WHERE metadata.version_id = version.id)
        SQL);

        $this->addSql(<<<'SQL'
            INSERT INTO metadata_keyword (metadata_id, keyword_id)
            SELECT metadata.id, version_keyword.keyword_id
            FROM version_keyword
            INNER JOIN metadata metadata ON version_keyword.version_id = metadata.version_id
        SQL);

        $linkTables = ['require', 'dev_require', 'conflict', 'provide', 'replace', 'suggest'];

        foreach ($linkTables as $linkTable) {
            $this->addSql(<<<SQL
                INSERT INTO metadata_{$linkTable}_link (metadata_id, linked_package_name, linked_version_constraint, index)
                SELECT
                    metadata.id,
                    link.linked_package_name,
                    link.linked_version_constraint,
                    ROW_NUMBER() OVER (PARTITION BY metadata.id) - 1
                FROM version_{$linkTable}_link link
                INNER JOIN metadata metadata ON link.version_id = metadata.version_id
            SQL);
        }
    }

    private function addCurrentMetadataColumnToVersionTable(): void
    {
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD current_metadata_id INT DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                version
            ADD
                CONSTRAINT FK_BF1CD3C3624A4280 FOREIGN KEY (current_metadata_id) REFERENCES metadata (id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE UNIQUE INDEX UNIQ_BF1CD3C3624A4280 ON version (current_metadata_id)
        SQL);

        $this->addSql(<<<'SQL'
            UPDATE version
            SET current_metadata_id = metadata.id
            FROM metadata
            WHERE metadata.version_id = version.id
        SQL);
    }

    private function updateVersionTableColumns(): void
    {
        $this->addSql(<<<'SQL'
            DROP INDEX pkg_ver_idx
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version RENAME COLUMN name TO package_name
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version RENAME COLUMN version TO name
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version RENAME COLUMN normalized_version TO normalized_name
        SQL);
        $this->addSql(<<<'SQL'
            CREATE UNIQUE INDEX package_version_idx ON version (package_id, normalized_name)
        SQL);

        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP CONSTRAINT fk_bf1cd3c3f44cabff
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ALTER package_id SET NOT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                version
            ADD
                CONSTRAINT FK_BF1CD3C3F44CABFF FOREIGN KEY (package_id) REFERENCES package (id) ON DELETE CASCADE NOT DEFERRABLE
        SQL);
    }

    private function dropVersionLinkTables(): void
    {
        $this->addSql(<<<'SQL'
            ALTER TABLE version_conflict_link DROP CONSTRAINT fk_ad52d6c4bbc2705
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version_dev_require_link DROP CONSTRAINT fk_98f2e46e4bbc2705
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version_keyword DROP CONSTRAINT fk_a65a946f4bbc2705
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version_keyword DROP CONSTRAINT fk_a65a946f115d4552
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version_provide_link DROP CONSTRAINT fk_150ec10c4bbc2705
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version_replace_link DROP CONSTRAINT fk_87f1b96a4bbc2705
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version_require_link DROP CONSTRAINT fk_7a1caab14bbc2705
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version_suggest_link DROP CONSTRAINT fk_de9b76884bbc2705
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE version_conflict_link
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE version_dev_require_link
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE version_keyword
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE version_provide_link
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE version_replace_link
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE version_require_link
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE version_suggest_link
        SQL);
    }

    private function dropVersionTableFields(): void
    {
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP package_name
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP description
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP readme
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP homepage
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP license
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP type
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP target_dir
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP source
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP dist
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP autoload
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP binaries
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP include_paths
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP php_ext
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP authors
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP support
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP funding
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP extra
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP released_at
        SQL);
    }

    private function createVersionTableFields(): void
    {
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD package_name VARCHAR(255) DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD description TEXT DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD readme TEXT DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD homepage VARCHAR(255) DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD license JSON DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD type VARCHAR(255) DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD target_dir VARCHAR(255) DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD source JSON DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD dist JSON DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD autoload JSON DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD binaries JSON DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD include_paths JSON DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD php_ext JSON DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD authors JSON DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD support JSON DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD funding JSON DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD extra JSON DEFAULT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ADD released_at TIMESTAMP(0) WITHOUT TIME ZONE DEFAULT NULL
        SQL);

        $this->addSql(<<<'SQL'
            UPDATE version SET package_name = 'place/holder', license = '[]', autoload = '[]'
        SQL);

        $this->addSql(<<<'SQL'
            ALTER TABLE version ALTER COLUMN package_name SET NOT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ALTER COLUMN license SET NOT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ALTER COLUMN autoload SET NOT NULL
        SQL);
    }

    private function createVersionLinkTables(): void
    {
        $this->addSql(<<<'SQL'
            CREATE TABLE version_conflict_link (
                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                linked_package_name VARCHAR(191) NOT NULL,
                linked_version_constraint TEXT NOT NULL,
                version_id INT NOT NULL,
                PRIMARY KEY (id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX idx_ad52d6c4bbc2705 ON version_conflict_link (version_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE TABLE version_dev_require_link (
                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                linked_package_name VARCHAR(191) NOT NULL,
                linked_version_constraint TEXT NOT NULL,
                version_id INT NOT NULL,
                PRIMARY KEY (id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX idx_98f2e46e4bbc2705 ON version_dev_require_link (version_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE TABLE version_keyword (
                version_id INT NOT NULL,
                keyword_id INT NOT NULL,
                PRIMARY KEY (version_id, keyword_id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX idx_a65a946f115d4552 ON version_keyword (keyword_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX idx_a65a946f4bbc2705 ON version_keyword (version_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE TABLE version_provide_link (
                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                linked_package_name VARCHAR(191) NOT NULL,
                linked_version_constraint TEXT NOT NULL,
                version_id INT NOT NULL,
                PRIMARY KEY (id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX idx_150ec10c4bbc2705 ON version_provide_link (version_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE TABLE version_replace_link (
                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                linked_package_name VARCHAR(191) NOT NULL,
                linked_version_constraint TEXT NOT NULL,
                version_id INT NOT NULL,
                PRIMARY KEY (id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX idx_87f1b96a4bbc2705 ON version_replace_link (version_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE TABLE version_require_link (
                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                linked_package_name VARCHAR(191) NOT NULL,
                linked_version_constraint TEXT NOT NULL,
                version_id INT NOT NULL,
                PRIMARY KEY (id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX idx_7a1caab14bbc2705 ON version_require_link (version_id)
        SQL);
        $this->addSql(<<<'SQL'
            CREATE TABLE version_suggest_link (
                id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
                linked_package_name VARCHAR(191) NOT NULL,
                linked_version_constraint TEXT NOT NULL,
                version_id INT NOT NULL,
                PRIMARY KEY (id)
            )
        SQL);
        $this->addSql(<<<'SQL'
            CREATE INDEX idx_de9b76884bbc2705 ON version_suggest_link (version_id)
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                version_conflict_link
            ADD
                CONSTRAINT fk_ad52d6c4bbc2705 FOREIGN KEY (version_id) REFERENCES version (id) NOT DEFERRABLE INITIALLY IMMEDIATE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                version_dev_require_link
            ADD
                CONSTRAINT fk_98f2e46e4bbc2705 FOREIGN KEY (version_id) REFERENCES version (id) NOT DEFERRABLE INITIALLY IMMEDIATE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                version_keyword
            ADD
                CONSTRAINT fk_a65a946f4bbc2705 FOREIGN KEY (version_id) REFERENCES version (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                version_keyword
            ADD
                CONSTRAINT fk_a65a946f115d4552 FOREIGN KEY (keyword_id) REFERENCES keyword (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                version_provide_link
            ADD
                CONSTRAINT fk_150ec10c4bbc2705 FOREIGN KEY (version_id) REFERENCES version (id) NOT DEFERRABLE INITIALLY IMMEDIATE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                version_replace_link
            ADD
                CONSTRAINT fk_87f1b96a4bbc2705 FOREIGN KEY (version_id) REFERENCES version (id) NOT DEFERRABLE INITIALLY IMMEDIATE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                version_require_link
            ADD
                CONSTRAINT fk_7a1caab14bbc2705 FOREIGN KEY (version_id) REFERENCES version (id) NOT DEFERRABLE INITIALLY IMMEDIATE
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                version_suggest_link
            ADD
                CONSTRAINT fk_de9b76884bbc2705 FOREIGN KEY (version_id) REFERENCES version (id) NOT DEFERRABLE INITIALLY IMMEDIATE
        SQL);
    }

    private function revertVersionTableColumns(): void
    {
        $this->addSql(<<<'SQL'
            DROP INDEX package_version_idx
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version RENAME COLUMN name TO version
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version RENAME COLUMN normalized_name TO normalized_version
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version RENAME COLUMN package_name TO name
        SQL);
        $this->addSql(<<<'SQL'
            CREATE UNIQUE INDEX pkg_ver_idx ON version (package_id, normalized_version)
        SQL);

        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP CONSTRAINT FK_BF1CD3C3F44CABFF
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version ALTER package_id DROP NOT NULL
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE
                version
            ADD
                CONSTRAINT fk_bf1cd3c3f44cabff FOREIGN KEY (package_id) REFERENCES package (id) NOT DEFERRABLE INITIALLY IMMEDIATE
        SQL);
    }

    private function fillVersionTables(): void
    {
        $this->addSql(<<<'SQL'
            UPDATE version
            SET
                name = metadata.package_name,
                description = metadata.description,
                readme = metadata.readme,
                homepage = metadata.homepage,
                license = metadata.license,
                type = metadata.type,
                target_dir = metadata.target_dir,
                source = metadata.source,
                dist = metadata.dist,
                autoload = metadata.autoload,
                binaries = metadata.binaries,
                include_paths = metadata.include_paths,
                php_ext = metadata.php_ext,
                authors = metadata.authors,
                support = metadata.support,
                funding = metadata.funding,
                extra = metadata.extra,
                released_at = metadata.released_at
            FROM metadata
            WHERE version.current_metadata_id = metadata.id
        SQL);

        $this->addSql(<<<'SQL'
            INSERT INTO version_keyword (version_id, keyword_id)
            SELECT version.id, metadata_keyword.keyword_id
            FROM metadata_keyword
            INNER JOIN metadata metadata ON metadata_keyword.metadata_id = metadata.id
            INNER JOIN version version ON version.current_metadata_id = metadata.id
            WHERE NOT EXISTS (
                SELECT 1 FROM version_keyword
                WHERE version_keyword.version_id = version.id
                AND version_keyword.keyword_id = metadata_keyword.keyword_id
            )
        SQL);

        $linkTables = ['require', 'dev_require', 'conflict', 'provide', 'replace', 'suggest'];

        foreach ($linkTables as $linkTable) {
            $this->addSql(<<<SQL
                INSERT INTO version_{$linkTable}_link (version_id, linked_package_name, linked_version_constraint)
                SELECT version.id, metadata_link.linked_package_name, metadata_link.linked_version_constraint
                FROM metadata_{$linkTable}_link metadata_link
                INNER JOIN metadata metadata ON metadata_link.metadata_id = metadata.id
                INNER JOIN version version ON version.current_metadata_id = metadata.id
                WHERE NOT EXISTS (
                    SELECT 1 FROM version_{$linkTable}_link version_link
                    WHERE version_link.version_id = metadata.version_id
                    AND version_link.linked_package_name = metadata_link.linked_package_name
                )
                ORDER BY metadata_link.index
            SQL);
        }
    }

    private function dropCurrentMetadataColumnFromVersionTable(): void
    {
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP CONSTRAINT FK_BF1CD3C3624A4280
        SQL);
        $this->addSql(<<<'SQL'
            DROP INDEX UNIQ_BF1CD3C3624A4280
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE version DROP current_metadata_id
        SQL);
    }

    private function dropMetadataTables(): void
    {
        $this->addSql(<<<'SQL'
            DROP INDEX version_revision_idx
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE metadata DROP CONSTRAINT FK_4F1434144BBC2705
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE metadata DROP CONSTRAINT FK_4F143414F44CABFF
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE metadata_keyword DROP CONSTRAINT FK_C8E2A1C3DC9EE959
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE metadata_keyword DROP CONSTRAINT FK_C8E2A1C3115D4552
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE metadata_conflict_link DROP CONSTRAINT FK_97454B50DC9EE959
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE metadata_dev_require_link DROP CONSTRAINT FK_762BA762DC9EE959
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE metadata_provide_link DROP CONSTRAINT FK_571F5289DC9EE959
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE metadata_replace_link DROP CONSTRAINT FK_C5E02AEFDC9EE959
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE metadata_require_link DROP CONSTRAINT FK_380D3934DC9EE959
        SQL);
        $this->addSql(<<<'SQL'
            ALTER TABLE metadata_suggest_link DROP CONSTRAINT FK_9C8AE50DDC9EE959
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE metadata
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE metadata_keyword
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE metadata_conflict_link
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE metadata_dev_require_link
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE metadata_provide_link
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE metadata_replace_link
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE metadata_require_link
        SQL);
        $this->addSql(<<<'SQL'
            DROP TABLE metadata_suggest_link
        SQL);
    }
}