<?php

/**
 * Lifestyle Webconsulting GmbH
 *
 * LICENSE: This Software is the property of Lifestyle Webconsulting GmbH (Aschaffenburg, Germany)
 * and is private by copyright law - it is NOT Freeware.
 *
 * Any unauthorized use of this software without a valid license
 * is a violation of the license agreement and will be prosecuted by
 * civil and criminal law.
 *
 * @copyright  2019 Lifestyle Webconsulting GmbH
 * @link       https://www.life-style.de
 */

namespace DoctrineMigrations;

use Doctrine\DBAL\DBALException;
use Doctrine\DBAL\Migrations\AbortMigrationException;
use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
use Ramsey\Uuid\Uuid;

require_once dirname(__DIR__) . '/InstallTrait.php';

/**
 * Class Version20190916062152
 *
 * @copyright  2019 Lifestyle Webconsulting GmbH
 * @link       https://www.life-style.de
 * @package DoctrineMigrations
 */
class Version20190916062152 extends AbstractMigration
{
    use InstallTrait;

    /**
     * @param Schema $schema
     * @throws AbortMigrationException
     */
    public function up(Schema $schema)
    {
        // this up() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        $this->addSql('CREATE TABLE wssp_servicenames (ServiceNameId INT AUTO_INCREMENT NOT NULL, ServiceName VARCHAR(255) NOT NULL, ServiceActive SMALLINT NOT NULL, PRIMARY KEY(ServiceNameId)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE wssp_servicename2services (ServiceNameId INT NOT NULL, ServiceId INT NOT NULL, INDEX IDX_9B97AD8C5CC2A9D4 (ServiceNameId), INDEX IDX_9B97AD8CB98FD0B (ServiceId), PRIMARY KEY(ServiceNameId, ServiceId)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE wssp_tokens (ServiceToken VARCHAR(255) NOT NULL, ServiceToken2Ip VARCHAR(255) NOT NULL, AllowedCallsPerDay INT NOT NULL, ValidUntil DATE NOT NULL, Blocked SMALLINT NOT NULL, BlockedReason VARCHAR(255) DEFAULT NULL, Description VARCHAR(255) NOT NULL, PRIMARY KEY(ServiceToken)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE wssp_token2servicename (ServiceToken VARCHAR(255) NOT NULL, ServiceNameId INT NOT NULL, INDEX IDX_6DCA695D15A19130 (ServiceToken), INDEX IDX_6DCA695D5CC2A9D4 (ServiceNameId), PRIMARY KEY(ServiceToken, ServiceNameId)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE wssp_tokenusage (UsageId VARCHAR(64) NOT NULL, ServiceToken VARCHAR(255) NOT NULL, Date DATETIME NOT NULL, Ip VARCHAR(64) DEFAULT NULL, Controller VARCHAR(32) DEFAULT NULL, Action VARCHAR(32) DEFAULT NULL, TrackId VARCHAR(10) DEFAULT NULL, Status VARCHAR(10) DEFAULT NULL, INDEX IDX_18F28BEC15A19130 (ServiceToken), INDEX idx_token (ServiceToken, Date, Controller, Action), INDEX idx_date (Date), PRIMARY KEY(UsageId)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE wssp_services (ServiceId INT AUTO_INCREMENT NOT NULL, ServiceType VARCHAR(100) NOT NULL, ServiceController VARCHAR(100) NOT NULL, ServiceAction VARCHAR(100) NOT NULL, ServiceMapped2Group VARCHAR(100) NOT NULL, PRIMARY KEY(ServiceId)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('ALTER TABLE wssp_servicename2services ADD CONSTRAINT FK_9B97AD8C5CC2A9D4 FOREIGN KEY (ServiceNameId) REFERENCES wssp_servicenames (ServiceNameId)');
        $this->addSql('ALTER TABLE wssp_servicename2services ADD CONSTRAINT FK_9B97AD8CB98FD0B FOREIGN KEY (ServiceId) REFERENCES wssp_services (ServiceId)');
        $this->addSql('ALTER TABLE wssp_token2servicename ADD CONSTRAINT FK_6DCA695D15A19130 FOREIGN KEY (ServiceToken) REFERENCES wssp_tokens (ServiceToken)');
        $this->addSql('ALTER TABLE wssp_token2servicename ADD CONSTRAINT FK_6DCA695D5CC2A9D4 FOREIGN KEY (ServiceNameId) REFERENCES wssp_servicenames (ServiceNameId)');
        $this->addSql('ALTER TABLE wssp_tokenusage ADD CONSTRAINT FK_18F28BEC15A19130 FOREIGN KEY (ServiceToken) REFERENCES wssp_tokens (ServiceToken)');
    }

    /**
     * @param Schema $schema
     * @throws AbortMigrationException
     */
    public function down(Schema $schema)
    {
        // this down() migration is auto-generated, please modify it to your needs
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        $this->addSql('ALTER TABLE wssp_servicename2services DROP FOREIGN KEY FK_9B97AD8C5CC2A9D4');
        $this->addSql('ALTER TABLE wssp_token2servicename DROP FOREIGN KEY FK_6DCA695D5CC2A9D4');
        $this->addSql('ALTER TABLE wssp_token2servicename DROP FOREIGN KEY FK_6DCA695D15A19130');
        $this->addSql('ALTER TABLE wssp_tokenusage DROP FOREIGN KEY FK_18F28BEC15A19130');
        $this->addSql('ALTER TABLE wssp_servicename2services DROP FOREIGN KEY FK_9B97AD8CB98FD0B');
        $this->addSql('DROP TABLE wssp_servicenames');
        $this->addSql('DROP TABLE wssp_servicename2services');
        $this->addSql('DROP TABLE wssp_tokens');
        $this->addSql('DROP TABLE wssp_token2servicename');
        $this->addSql('DROP TABLE wssp_tokenusage');
        $this->addSql('DROP TABLE wssp_services');
    }

    /**
     * @param Schema $schema
     * @throws DBALException
     * @throws AbortMigrationException
     */
    public function postUp(Schema $schema)
    {
        $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (1, \'intern\', \'User\', \'Add\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (2, \'intern\', \'User\', \'Delete\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (3, \'intern\', \'User\', \'ChangePassword\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (4, \'intern\', \'User\', \'Update\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (5, \'intern\', \'Application\', \'Add\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (6, \'intern\', \'ApplicationRole\', \'Add\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (7, \'intern\', \'Application\', \'Delete\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (8, \'intern\', \'User\', \'Show\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (9, \'intern\', \'ApplicationAttribute\', \'Add\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (10, \'intern\', \'ApplicationRole\', \'Delete\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (11, \'intern\', \'UserApplication\', \'Add\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (12, \'intern\', \'UserApplication\', \'Show\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (13, \'intern\', \'UserApplication\', \'Delete\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (14, \'intern\', \'UserApplicationAttribute\', \'Add\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (15, \'intern\', \'UserApplicationAttribute\', \'Delete\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (16, \'intern\', \'UserApplicationAttribute\', \'Show\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (17, \'intern\', \'UserApplicationRole\', \'Add\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (18, \'intern\', \'UserApplicationRole\', \'Delete\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (19, \'intern\', \'UserService\', \'PasswordRequest\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (20, \'intern\', \'UserService\', \'PasswordConfirm\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (21, \'intern\', \'Application\', \'Show\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (22, \'intern\', \'ApplicationAttribute\', \'Delete\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (23, \'intern\', \'UserApplicationRole\', \'Show\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (24, \'intern\', \'UserSearch\', \'FindByApplicationAttribute\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (25, \'intern\', \'UserSearch\', \'FindByApplication\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (26, \'intern\', \'UserService\', \'ActivateRequest\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (27, \'intern\', \'UserService\', \'ActivateConfirm\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (28, \'intern\', \'ApplicationAttribute\', \'Show\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (29, \'intern\', \'UserSearch\', \'FindByApplicationRole\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (30, \'intern\', \'ApplicationRole\', \'Show\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (31, \'intern\', \'UserIdentifier\', \'Get\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (32, \'intern\', \'UserApplication\', \'ShowDetails\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (33, \'intern\', \'UserApplicationRole\', \'ShowDetails\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (34, \'intern\', \'UserSearch\', \'Advanced\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (35, \'intern\', \'Application\', \'All\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (36, \'intern\', \'PasswordPolicy\', \'Show\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (37, \'intern\', \'UserGroup\', \'Add\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (38, \'intern\', \'UserGroup\', \'Delete\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (39, \'intern\', \'UserGroup\', \'Update\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (40, \'intern\', \'UserGroup\', \'Search\', \'no\')');
        $this->connection->executeQuery('INSERT INTO `wssp_services` VALUES (41, \'intern\', \'User\', \'ResetPassword\', \'no\')');

        $this->connection->executeQuery('INSERT INTO `wssp_servicenames` VALUES (\'1\', \'MasterService\', \'1\')');
        $this->connection->executeQuery('INSERT INTO `wssp_servicenames` VALUES (\'2\', \'WspService\', \'1\')');
        $this->connection->executeQuery('INSERT INTO `wssp_servicenames` VALUES (\'3\', \'IamService\', \'1\')');
        $this->connection->executeQuery('INSERT INTO `wssp_servicenames` VALUES (\'4\', \'LaunchpadService\', \'1\')');

        $this->connection->executeQuery('INSERT INTO `wssp_servicename2services` VALUES (\'1\', \'1\'), (\'1\', \'2\'), (\'1\', \'3\'), (\'1\', \'4\'), (\'1\', \'5\'), (\'1\', \'6\'), (\'1\', \'7\'), (\'1\', \'8\'), (\'1\', \'9\'), (\'1\', \'10\'), (\'1\', \'11\'), (\'1\', \'12\'), (\'1\', \'13\'), (\'1\', \'14\'), (\'1\', \'15\'), (\'1\', \'16\'), (\'1\', \'17\'), (\'1\', \'18\'), (\'1\', \'19\'), (\'1\', \'20\'), (\'1\', \'21\'), (\'1\', \'22\'), (\'1\', \'23\'), (\'1\', \'24\'), (\'1\', \'25\'), (\'1\', \'26\'), (\'1\', \'27\'), (\'1\', \'28\'), (\'1\', \'29\'), (\'1\', \'30\'), (\'1\', \'31\'), (\'1\', \'32\'), (\'1\', \'33\'), (\'1\', \'34\'), (\'1\', \'35\'), (\'1\', \'36\'), (\'1\', \'37\'), (\'1\', \'38\'), (\'1\', \'39\'), (\'1\', \'40\'), (\'1\', \'41\')');
        $this->connection->executeQuery('INSERT INTO `wssp_servicename2services` VALUES (\'2\', \'1\'), (\'2\', \'3\'), (\'2\', \'4\'), (\'2\', \'8\'), (\'2\', \'11\'), (\'2\', \'12\'), (\'2\', \'14\'), (\'2\', \'16\'), (\'2\', \'17\'), (\'2\', \'19\'), (\'2\', \'20\'), (\'2\', \'23\'), (\'2\', \'26\'), (\'2\', \'27\'), (\'2\', \'36\'), (\'2\', \'41\')');
        $this->connection->executeQuery('INSERT INTO `wssp_servicename2services` VALUES (\'3\', \'1\'), (\'3\', \'2\'), (\'3\', \'4\'), (\'3\', \'6\'), (\'3\', \'8\'), (\'3\', \'9\'), (\'3\', \'11\'), (\'3\', \'12\'), (\'3\', \'13\'), (\'3\', \'14\'), (\'3\', \'15\'), (\'3\', \'16\'), (\'3\', \'17\'), (\'3\', \'18\'), (\'3\', \'21\'), (\'3\', \'22\'), (\'3\', \'23\'), (\'3\', \'28\'), (\'3\', \'30\'), (\'3\', \'32\'), (\'3\', \'33\'), (\'3\', \'34\'), (\'3\', \'35\'), (\'3\', \'36\'), (\'3\', \'37\'), (\'3\', \'38\'), (\'3\', \'39\'), (\'3\', \'40\')');
        $this->connection->executeQuery('INSERT INTO `wssp_servicename2services` VALUES (\'4\', \'8\')');

        $config['master_token'] = $this->addServiceToken(1, 'Wildcard token');
        $config['wsp_token'] = $this->addServiceToken(2, 'Used by wsp - user registration and password reset');
        $config['iam_token'] = $this->addServiceToken(3, 'Used by iam application - user-management');
        $config['launchpad_token'] = $this->addServiceToken(4, 'Used by launchpad application - start applications');

        $this->addInstallDocsAttribute(['service_provider' => $config]);
    }

    /**
     * @param int $serviceId
     * @param string $description
     * @return string
     * @throws DBALException
     */
    private function addServiceToken(int $serviceId, string $description): string
    {
        $token = hash('sha512', Uuid::uuid4()->toString());
        $this->connection->executeQuery('INSERT INTO `wssp_tokens` VALUES (\'' . $token . '\', \'*\', \'2147483647\', \'2099-01-01\', \'0\', null, \''. $description . '\')');
        $this->connection->executeQuery('INSERT INTO `wssp_token2servicename` VALUES (\'' . $token . '\', \'' . $serviceId . '\')');
        return $token;
    }
}
