<?php declare(strict_types=1);
namespace Neos\Flow\Persistence\Doctrine\Migrations;

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

/**
 * Auto-generated Migration: Please modify to your needs! This block will be used as the migration description if
 * getDescription() is not used.
 */
class Version20201215155749 extends AbstractMigration
{

    public function getDescription(): string
    {
        return 'Upgrade the event-location relation from a one-to-many to a many-to-many relation.';
    }

    public function up(Schema $schema): void
    {
        $this->abortIf(
            $this->connection->getDatabasePlatform()->getName() != 'mysql',
            'Migration can only be executed safely on "mysql".'
        );

        $this->addSql(
            'CREATE TABLE newland_toubiz_sync_neos_domain_model_event_locations_join' .
            ' (neos_event CHAR(36) NOT NULL COMMENT \'(DC2Type:guid)\',' .
            ' neos_article CHAR(36) NOT NULL COMMENT \'(DC2Type:guid)\',' .
            ' INDEX IDX_D06EE8B7EE4FA767 (neos_event),' .
            ' INDEX IDX_D06EE8B761A7D39C (neos_article),' .
            ' PRIMARY KEY(neos_event, neos_article))' .
            ' DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB'
        );
        $this->addSql(
            'ALTER TABLE newland_toubiz_sync_neos_domain_model_event_locations_join' .
            ' ADD CONSTRAINT FK_D06EE8B7EE4FA767 FOREIGN KEY (neos_event)' .
            ' REFERENCES newland_toubiz_sync_neos_domain_model_event (persistence_object_identifier)'
        );
        $this->addSql(
            'ALTER TABLE newland_toubiz_sync_neos_domain_model_event_locations_join' .
            ' ADD CONSTRAINT FK_D06EE8B761A7D39C FOREIGN KEY (neos_article)' .
            ' REFERENCES newland_toubiz_sync_neos_domain_model_article (persistence_object_identifier)'
        );
        foreach ($this->findCityOneToMany() as $eventId => $cityId) {
            $this->addSql(
                'INSERT INTO newland_toubiz_sync_neos_domain_model_event_locations_join (neos_event, neos_article)' .
                ' VALUES (:eventId, :cityId)',
                compact('eventId', 'cityId')
            );
        }
        $this->addSql('ALTER TABLE newland_toubiz_sync_neos_domain_model_event DROP FOREIGN KEY FK_4DAD30E62D5B0234');
        $this->addSql('DROP INDEX IDX_4DAD30E62D5B0234 ON newland_toubiz_sync_neos_domain_model_event');
        $this->addSql('ALTER TABLE newland_toubiz_sync_neos_domain_model_event DROP city');
    }

    public function down(Schema $schema): void
    {
        $this->addSql(
            'ALTER TABLE newland_toubiz_sync_neos_domain_model_event' .
            ' ADD city CHAR(36) CHARACTER SET utf8mb4 DEFAULT NULL COLLATE `utf8mb4_unicode_ci` COMMENT \'(DC2Type:guid)\''
        );
        $this->addSql(
            'ALTER TABLE newland_toubiz_sync_neos_domain_model_event' .
            ' ADD CONSTRAINT FK_4DAD30E62D5B0234 FOREIGN KEY (city)' .
            ' REFERENCES newland_toubiz_sync_neos_domain_model_article (persistence_object_identifier)' .
            ' ON DELETE SET NULL'
        );
        $this->addSql('CREATE INDEX IDX_4DAD30E62D5B0234 ON newland_toubiz_sync_neos_domain_model_event (city)');
        foreach ($this->findCityManyToMany() as $eventId => $cityId) {
            $this->addSql(
                'UPDATE newland_toubiz_sync_neos_domain_model_event' .
                ' SET city = ":cityId"' .
                ' WHERE persistence_object_identifier = ":eventId"',
                compact('eventId', 'cityId')
            );
        }
        $this->abortIf(
            $this->connection->getDatabasePlatform()->getName() != 'mysql',
            'Migration can only be executed safely on "mysql".'
        );

        $this->addSql('DROP TABLE newland_toubiz_sync_neos_domain_model_event_locations_join');
    }

    private function findCityOneToMany(): array
    {
        $relations = [];

        $result = $this->connection->executeQuery(
            '
            SELECT persistence_object_identifier, city
            FROM newland_toubiz_sync_neos_domain_model_event
            WHERE city IS NOT NULL
        '
        );

        while ($row = $result->fetch(FetchMode::STANDARD_OBJECT)) {
            $relations[$row->persistence_object_identifier] = $row->city;
        }

        return $relations;
    }

    private function findCityManyToMany(): array
    {
        $relations = [];

        $result = $this->connection->executeQuery(
            '
            SELECT neos_event, neos_article
            FROM newland_toubiz_sync_neos_domain_model_event_locations_join
        '
        );

        while ($row = $result->fetch(FetchMode::STANDARD_OBJECT)) {
            $relations[$row->neos_event] = $row->neos_article;
        }

        return $relations;
    }
}
