<?php
namespace Neos\Flow\Persistence\Doctrine\Migrations;

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

/**
 * Migration that transitions safely from site-package migrations to sync-package migrations.
 * Using this migration as a starting point future migrations can be added to the sync package.
 * This migration has been tested
 * - With an existing neos-toubiz-frontend database
 * - With an empty database (which then is provisioned with the previous migration)
 */
class Version20190606065313 extends AbstractMigration
{

    public function isTransactional()
    {
        return false;
    }

    /**
     * @param Schema $schema
     * @return void
     */
    public function up(Schema $schema)
    {
        // this up() migration is autogenerated, please modify it to your needs
        $this->abortIf(
            $this->connection->getDatabasePlatform()->getName() != 'mysql',
            'Migration can only be executed safely on "mysql".'
        );
        $this->dropExistingForeignKeyConstraintsIfTheyExist();
        $this->ensureAllColumnDefinitionsAreUpToDateIfTablesExistedPreviously();
        $this->addForeignKeyConstraintsIfTheyDontExist();
    }

    /**
     * @param Schema $schema
     * @return void
     */
    public function down(Schema $schema)
    {
        // this down() migration is autogenerated, please modify it to your needs
        $this->abortIf(true, 'Migration is not revertable');
    }

    private function columnExists(string $table, string $columnName): bool
    {
        foreach ($this->sm->listTableColumns($table) as $column) {
            if ($column->getName() === $columnName) {
                return true;
            }
        }
        return false;
    }

    private function ensureAllColumnDefinitionsAreUpToDateIfTablesExistedPreviously(): void
    {
        // Table definitions extracted from generated migration from existing but empty tables.
        // This will ensure that all tables are up to date, no matter where they are coming from:
        // It will
        // - create columns if they don't exist
        // - modify them if they do
        $definitions = [
            'newland_toubiz_sync_neos_domain_model_attribute' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'article' => 'VARCHAR(40) DEFAULT NULL',
                'name' => 'VARCHAR(255) NOT NULL',
                'data' => 'LONGTEXT DEFAULT NULL',
                'type' => 'VARCHAR(10) DEFAULT NULL',
                'identificationhash' => 'VARCHAR(32) DEFAULT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_geometry' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'type' => 'VARCHAR(255) NOT NULL',
                'label' => 'VARCHAR(255) NOT NULL',
                'geometry' => 'GEOMETRY NOT NULL COMMENT \'(DC2Type:geometry)\'',
            ],
            'newland_toubiz_sync_neos_domain_model_keyvalue' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'identifier' => 'VARCHAR(255) NOT NULL',
                'data' => 'LONGTEXT NOT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_address' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'name' => 'VARCHAR(255) DEFAULT NULL',
                'firstname' => 'VARCHAR(255) DEFAULT NULL',
                'lastname' => 'VARCHAR(255) DEFAULT NULL',
                'street' => 'VARCHAR(255) DEFAULT NULL',
                'zip' => 'VARCHAR(255) DEFAULT NULL',
                'city' => 'VARCHAR(255) DEFAULT NULL',
                'country' => 'VARCHAR(255) DEFAULT NULL',
                'phonenumber' => 'VARCHAR(255) DEFAULT NULL',
                'emailaddress' => 'VARCHAR(255) DEFAULT NULL',
                'websiteaddress' => 'LONGTEXT DEFAULT NULL',
                'latitude' => 'DOUBLE PRECISION DEFAULT NULL',
                'longitude' => 'DOUBLE PRECISION DEFAULT NULL',
                'originalid' => 'VARCHAR(50) NOT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_article' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'mainaddress' => 'VARCHAR(40) DEFAULT NULL',
                'maintype' => 'INT NOT null',
                'name' => 'VARCHAR(255) NOT null',
                'abstract' => 'LONGTEXT DEFAULT null',
                'description' => 'LONGTEXT DEFAULT null',
                'facebookuri' => 'VARCHAR(255) DEFAULT null',
                'twitteruri' => 'VARCHAR(255) DEFAULT null',
                'instagramuri' => 'VARCHAR(255) DEFAULT null',
                'youtubeuri' => 'VARCHAR(255) DEFAULT null',
                'flickruri' => 'VARCHAR(255) DEFAULT null',
                'wikipediauri' => 'VARCHAR(255) DEFAULT null',
                'sourcename' => 'VARCHAR(255) DEFAULT null',
                'authorname' => 'VARCHAR(255) DEFAULT null',
                'bookinguri' => 'VARCHAR(255) DEFAULT null',
                'client' => 'VARCHAR(255) DEFAULT null',
                'detailuri' => 'VARCHAR(255) DEFAULT null',
                'starclassification' => 'SMALLINT DEFAULT null',
                'averagerating' => 'SMALLINT DEFAULT null',
                'numberofratings' => 'SMALLINT DEFAULT null',
                'updatedat' => 'DATETIME DEFAULT null',
                'openingtimes' => 'LONGTEXT DEFAULT null',
                'openingtimesformat' => 'VARCHAR(255) DEFAULT null',
                'originalid' => 'VARCHAR(50) NOT null',
                'relationsorting' => 'LONGTEXT DEFAULT null COMMENT \'(DC2Type:array)\'',
                'language' => 'CHAR(2) DEFAULT null',
                'urlidentifier' => 'VARCHAR(10) DEFAULT null',
            ],
            'newland_toubiz_sync_neos_domain_model_article_addresses_join' => [
                'neos_article' => 'VARCHAR(40) NOT null',
                'neos_address' => 'VARCHAR(40) NOT null',
            ],
            'newland_toubiz_sync_neos_domain_model_article_categories_join' => [
                'neos_article' => 'VARCHAR(40) NOT null',
                'neos_category' => 'VARCHAR(40) NOT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_article_media_join' => [
                'neos_article' => 'VARCHAR(40) NOT null',
                'neos_medium' => 'VARCHAR(40) NOT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_article_files_join' => [
                'neos_article' => 'VARCHAR(40) NOT NULL',
                'neos_file' => 'VARCHAR(40) NOT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_benefit' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'package' => 'VARCHAR(40) DEFAULT NULL',
                'name' => 'VARCHAR(255) NOT NULL',
                'originalid' => 'VARCHAR(50) NOT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_category' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'title' => 'VARCHAR(255) NOT NULL',
                'originalid' => 'VARCHAR(50) NOT NULL',
                'language' => 'CHAR(2) DEFAULT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_event' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'location' => 'VARCHAR(40) DEFAULT NULL',
                'organizer' => 'VARCHAR(40) DEFAULT NULL',
                'title' => 'VARCHAR(255) NOT NULL',
                'description' => 'LONGTEXT DEFAULT NULL',
                'additionalinformation' => 'LONGTEXT DEFAULT NULL',
                'admission' => 'LONGTEXT DEFAULT NULL',
                'ishighlight' => 'TINYINT(1) NOT NULL',
                'istipp' => 'TINYINT(1) NOT NULL',
                'beginsat' => 'DATETIME NOT NULL',
                'endsat' => 'DATETIME NOT NULL',
                'ticketuri' => 'LONGTEXT DEFAULT NULL',
                'ticketcontact' => 'LONGTEXT DEFAULT NULL',
                'link' => 'LONGTEXT DEFAULT NULL',
                'updatedat' => 'DATETIME NOT NULL',
                'sourcename' => 'VARCHAR(255) DEFAULT NULL',
                'attributes' => 'LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\'',
                'scope' => 'SMALLINT DEFAULT NULL',
                'originalid' => 'VARCHAR(50) NOT NULL',
                'language' => 'CHAR(2) DEFAULT NULL',
                'urlidentifier' => 'VARCHAR(10) DEFAULT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_event_categories_join' => [
                'neos_event' => 'VARCHAR(40) NOT NULL',
                'neos_category' => 'VARCHAR(40) NOT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_event_eventtags_join' => [
                'neos_event' => 'VARCHAR(40) NOT NULL',
                'neos_eventtag' => 'VARCHAR(40) NOT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_event_media_join' => [
                'neos_event' => 'VARCHAR(40) NOT NULL',
                'neos_medium' => 'VARCHAR(40) NOT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_eventdate' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'event' => 'VARCHAR(40) DEFAULT NULL',
                'beginsat' => 'DATETIME NOT NULL',
                'endsat' => 'DATETIME NOT NULL',
                'note' => 'VARCHAR(255) NOT NULL',
                'detailuri' => 'VARCHAR(255) DEFAULT NULL',
                'originalid' => 'VARCHAR(50) NOT NULL',
                'language' => 'CHAR(2) DEFAULT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_eventtag' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'keyword' => 'LONGTEXT NOT NULL',
                'originalid' => 'VARCHAR(50) NOT NULL',
                'language' => 'CHAR(2) DEFAULT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_file' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'name' => 'VARCHAR(255) NOT NULL',
                'sourceuri' => 'LONGTEXT NOT NULL',
                'originalid' => 'VARCHAR(50) NOT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_medium' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'title' => 'LONGTEXT DEFAULT NULL',
                'sourceuri' => 'LONGTEXT NOT NULL',
                'copyright' => 'VARCHAR(255) DEFAULT NULL',
                'originalid' => 'VARCHAR(50) NOT NULL',
                'sorting' => 'INT DEFAULT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_offer' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'name' => 'VARCHAR(255) NOT NULL',
                'previewimage' => 'VARCHAR(255) DEFAULT NULL',
                'startingprice' => 'DOUBLE PRECISION DEFAULT NULL',
                'startingpricecurrency' => 'CHAR(3) DEFAULT NULL',
                'address' => 'VARCHAR(255) DEFAULT NULL',
                'originalid' => 'VARCHAR(50) NOT NULL',
                'durationminutes' => 'INT DEFAULT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_package' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'name' => 'VARCHAR(255) NOT NULL',
                'minimumnights' => 'DOUBLE PRECISION DEFAULT NULL',
                'startingprice' => 'DOUBLE PRECISION DEFAULT NULL',
                'startingpricecurrency' => 'CHAR(3) DEFAULT NULL',
                'client' => 'VARCHAR(255) NOT NULL',
                'originalid' => 'VARCHAR(50) NOT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_package_media_join' => [
                'neos_package' => 'VARCHAR(40) NOT NULL',
                'neos_medium' => 'VARCHAR(40) NOT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_region' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'name' => 'VARCHAR(255) NOT NULL',
                'attributes' => 'LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\'',
                'updatedat' => 'DATETIME DEFAULT NULL',
                'originalid' => 'VARCHAR(50) NOT NULL',
            ],
            'newland_toubiz_sync_neos_region_transportation_facilities_join' => [
                'neos_region' => 'VARCHAR(40) NOT NULL',
                'neos_transportationfacility' => 'VARCHAR(40) NOT NULL',
            ],
            'newland_toubiz_sync_neos_region_slopes_join' => [
                'neos_region' => 'VARCHAR(40) NOT NULL',
                'neos_slope' => 'VARCHAR(40) NOT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_slope' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'name' => 'VARCHAR(255) NOT NULL',
                'status' => 'INT NOT NULL',
                'difficulty' => 'INT DEFAULT NULL',
                'updatedat' => 'DATETIME NOT NULL',
                'originalid' => 'VARCHAR(50) NOT NULL',
                'type' => 'INT DEFAULT NULL',
            ],
            'newland_toubiz_sync_neos_domain_model_transportationfacility' => [
                'persistence_object_identifier' => 'VARCHAR(40) NOT NULL',
                'name' => 'VARCHAR(255) NOT NULL',
                'type' => 'INT NOT NULL',
                'status' => 'INT NOT NULL',
                'traveltime' => 'INT DEFAULT NULL',
                'updatedat' => 'DATETIME NOT NULL',
                'originalid' => 'VARCHAR(50) NOT NULL',
                'length' => 'INT DEFAULT NULL',
            ],
        ];

        foreach ($definitions as $tableName => $columns) {
            foreach ($columns as $columnName => $columnDefinition) {
                if ($this->columnExists($tableName, $columnName)) {
                    $this->addSql(
                        sprintf(
                            'ALTER TABLE %s CHANGE %s %s %s',
                            $tableName,
                            $columnName,
                            $columnName,
                            $columnDefinition
                        )
                    );
                } else {
                    $this->addSql(sprintf('ALTER TABLE %s ADD %s %s', $tableName, $columnName, $columnDefinition));
                }
            }
        }
    }

    private function dropExistingForeignKeyConstraintsIfTheyExist(): void
    {
        foreach ($this->getTablesForeingKeyMap() as $fk => [$table, $field, $ref]) {
            if ($this->foreignKeyExists($table, $fk)) {
                $this->addSql(
                    sprintf('ALTER TABLE %s DROP FOREIGN KEY %s', $table, $fk)
                );
            }
        }

    }

    private function addForeignKeyConstraintsIfTheyDontExist(): void
    {
        foreach ($this->getTablesForeingKeyMap() as $fk => [$table, $field, $ref]) {
            $this->addSql(
                sprintf(
                    'ALTER TABLE %s ADD CONSTRAINT %s FOREIGN KEY (%s) REFERENCES %s (persistence_object_identifier)',
                    $table,
                    $fk,
                    $field,
                    $ref
                )
            );
        }
    }

    private function foreignKeyExists(string $tableName, string $fk): bool
    {
        foreach ($this->sm->listTableForeignKeys($tableName) as $foreignKey) {
            if ($foreignKey->getName() === $fk) {
                return true;
            }
        }
        return false;
    }

    private function getTablesForeingKeyMap(): array
    {
        return [
            'FK_6265372F23A0E66' => [
                'newland_toubiz_sync_neos_domain_model_attribute',
                'article',
                'newland_toubiz_sync_neos_domain_model_article',
            ],
            'FK_2D54DF936B613014' => [
                'newland_toubiz_sync_neos_domain_model_article',
                'mainaddress',
                'newland_toubiz_sync_neos_domain_model_address',
            ],
            'FK_C2F8B1C461A7D39C' => [
                'newland_toubiz_sync_neos_domain_model_article_addresses_join',
                'neos_article',
                'newland_toubiz_sync_neos_domain_model_article',
            ],
            'FK_C2F8B1C46ED3B27B' => [
                'newland_toubiz_sync_neos_domain_model_article_addresses_join',
                'neos_address',
                'newland_toubiz_sync_neos_domain_model_address',
            ],
            'FK_40F8B83C61A7D39C' => [
                'newland_toubiz_sync_neos_domain_model_article_categories_join',
                'neos_article',
                'newland_toubiz_sync_neos_domain_model_article',
            ],
            'FK_40F8B83C5B479F1E' => [
                'newland_toubiz_sync_neos_domain_model_article_categories_join',
                'neos_category',
                'newland_toubiz_sync_neos_domain_model_category',
            ],
            'FK_2B4FD31061A7D39C' => [
                'newland_toubiz_sync_neos_domain_model_article_media_join',
                'neos_article',
                'newland_toubiz_sync_neos_domain_model_article',
            ],
            'FK_2B4FD3105DC266AA' => [
                'newland_toubiz_sync_neos_domain_model_article_media_join',
                'neos_medium',
                'newland_toubiz_sync_neos_domain_model_medium',
            ],
            'FK_6FC009A261A7D39C' => [
                'newland_toubiz_sync_neos_domain_model_article_files_join',
                'neos_article',
                'newland_toubiz_sync_neos_domain_model_article',
            ],
            'FK_6FC009A2D731232' => [
                'newland_toubiz_sync_neos_domain_model_article_files_join',
                'neos_file',
                'newland_toubiz_sync_neos_domain_model_file',
            ],
            'FK_73E5D1EADE686795' => [
                'newland_toubiz_sync_neos_domain_model_benefit',
                'package',
                'newland_toubiz_sync_neos_domain_model_package',
            ],
            'FK_4DAD30E65E9E89CB' => [
                'newland_toubiz_sync_neos_domain_model_event',
                'location',
                'newland_toubiz_sync_neos_domain_model_address',
            ],
            'FK_4DAD30E699D47173' => [
                'newland_toubiz_sync_neos_domain_model_event',
                'organizer',
                'newland_toubiz_sync_neos_domain_model_address',
            ],
            'FK_744FD94FEE4FA767' => [
                'newland_toubiz_sync_neos_domain_model_event_categories_join',
                'neos_event',
                'newland_toubiz_sync_neos_domain_model_event',
            ],
            'FK_744FD94F5B479F1E' => [
                'newland_toubiz_sync_neos_domain_model_event_categories_join',
                'neos_category',
                'newland_toubiz_sync_neos_domain_model_category',
            ],
            'FK_ECF21824EE4FA767' => [
                'newland_toubiz_sync_neos_domain_model_event_eventtags_join',
                'neos_event',
                'newland_toubiz_sync_neos_domain_model_event',
            ],
            'FK_ECF2182449341CE7' => [
                'newland_toubiz_sync_neos_domain_model_event_eventtags_join',
                'neos_eventtag',
                'newland_toubiz_sync_neos_domain_model_eventtag',
            ],
            'FK_886DAE27EE4FA767' => [
                'newland_toubiz_sync_neos_domain_model_event_media_join',
                'neos_event',
                'newland_toubiz_sync_neos_domain_model_event',
            ],
            'FK_886DAE275DC266AA' => [
                'newland_toubiz_sync_neos_domain_model_event_media_join',
                'neos_medium',
                'newland_toubiz_sync_neos_domain_model_medium',
            ],
            'FK_6E2533873BAE0AA7' => [
                'newland_toubiz_sync_neos_domain_model_eventdate',
                'event',
                'newland_toubiz_sync_neos_domain_model_event',
            ],
            'FK_1B8AF764BDF5BA6F' => [
                'newland_toubiz_sync_neos_domain_model_package_media_join',
                'neos_package',
                'newland_toubiz_sync_neos_domain_model_package',
            ],
            'FK_1B8AF7645DC266AA' => [
                'newland_toubiz_sync_neos_domain_model_package_media_join',
                'neos_medium',
                'newland_toubiz_sync_neos_domain_model_medium',
            ],
            'FK_3349FAD194D3D26B' => [
                'newland_toubiz_sync_neos_region_transportation_facilities_join',
                'neos_region',
                'newland_toubiz_sync_neos_domain_model_region',
            ],
            'FK_3349FAD1E580DEF8' => [
                'newland_toubiz_sync_neos_region_transportation_facilities_join',
                'neos_transportationfacility',
                'newland_toubiz_sync_neos_domain_model_transportationfacility',
            ],
            'FK_9603E59694D3D26B' => [
                'newland_toubiz_sync_neos_region_slopes_join',
                'neos_region',
                'newland_toubiz_sync_neos_domain_model_region',
            ],
            'FK_9603E5968D2DE845' => [
                'newland_toubiz_sync_neos_region_slopes_join',
                'neos_slope',
                'newland_toubiz_sync_neos_domain_model_slope',
            ],

        ];
    }

}
