<?php

declare(strict_types=1);

/*
 * Chill is a software for social workers
 *
 * For the full copyright and license information, please view
 * the LICENSE file that was distributed with this source code.
 */

namespace Chill\Migrations\Main;

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

/**
 * add username and username canonical, email and email canonical columns
 * to users.
 */
final class Version20180709181423 extends AbstractMigration
{
    public function down(Schema $schema): void
    {
        $this->abortIf('postgresql' !== $this->connection->getDatabasePlatform()->getName(), 'Migration can only be executed safely on \'postgresql\'.');

        $this->addSql('DROP INDEX UNIQ_1483A5E9F5A5DC32');
        $this->addSql('DROP INDEX UNIQ_1483A5E9885281E');
        $this->addSql('ALTER TABLE users DROP usernameCanonical');
        $this->addSql('ALTER TABLE users DROP email');
        $this->addSql('ALTER TABLE users DROP emailCanonical');
        $this->addSql('DROP TRIGGER canonicalize_user_on_insert ON users');
        $this->addSql('DROP FUNCTION canonicalize_user_on_insert()');
        $this->addSql('DROP TRIGGER canonicalize_user_on_update ON users');
        $this->addSql('DROP FUNCTION canonicalize_user_on_update()');
    }

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

        $this->addSql('ALTER TABLE users ADD usernameCanonical VARCHAR(80) DEFAULT NULL');
        $this->addSql('UPDATE users SET usernameCanonical=LOWER(UNACCENT(username))');
        $this->addSql('ALTER TABLE users ALTER usernameCanonical DROP NOT NULL');
        $this->addSql('ALTER TABLE users ALTER usernameCanonical SET DEFAULT NULL');
        $this->addSql('ALTER TABLE users ADD email VARCHAR(150) DEFAULT NULL');
        $this->addSql('ALTER TABLE users ADD emailCanonical VARCHAR(150) DEFAULT NULL');
        $this->addSql('CREATE UNIQUE INDEX UNIQ_1483A5E9F5A5DC32 ON users (usernameCanonical)');
        $this->addSql('CREATE UNIQUE INDEX UNIQ_1483A5E9885281E ON users (emailCanonical)');

        $this->addSql(
            <<<'SQL_WRAP'
            CREATE OR REPLACE FUNCTION canonicalize_user_on_update() RETURNS TRIGGER AS
            $BODY$
            BEGIN
            IF NEW.username <> OLD.username OR NEW.email <> OLD.email OR OLD.emailcanonical IS NULL OR OLD.usernamecanonical IS NULL THEN
               UPDATE users SET usernamecanonical=LOWER(UNACCENT(NEW.username)), emailcanonical=LOWER(UNACCENT(NEW.email)) WHERE id=NEW.id;
            END IF;

            RETURN NEW;
            END;
            $BODY$ LANGUAGE PLPGSQL
SQL_WRAP
        );

        $this->addSql(
            <<<'SQL'
                            CREATE TRIGGER canonicalize_user_on_update
                              AFTER UPDATE
                              ON users
                              FOR EACH ROW
                              EXECUTE PROCEDURE canonicalize_user_on_update();
                SQL
        );

        $this->addSql(
            <<<'SQL_WRAP'
            CREATE OR REPLACE FUNCTION canonicalize_user_on_insert() RETURNS TRIGGER AS
            $BODY$
            BEGIN
            UPDATE users SET usernamecanonical=LOWER(UNACCENT(NEW.username)), emailcanonical=LOWER(UNACCENT(NEW.email)) WHERE id=NEW.id;

            RETURN NEW;
            END;
            $BODY$ LANGUAGE PLPGSQL;
SQL_WRAP
        );

        $this->addSql(
            <<<'SQL'
                            CREATE TRIGGER canonicalize_user_on_insert
                              AFTER INSERT
                              ON users
                              FOR EACH ROW
                              EXECUTE PROCEDURE canonicalize_user_on_insert();
                SQL
        );
    }
}
