<?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 Version20190916062212
 *
 * @copyright  2019 Lifestyle Webconsulting GmbH
 * @link       https://www.life-style.de
 * @package DoctrineMigrations
 */
class Version20190916062212 extends AbstractMigration
{
    use InstallTrait;

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

        $this->addSql('CREATE TABLE dh_attribute_employee_id (id INT NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE dh_employee_id (employeeId INT NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY(employeeId)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE attribute (attribute_id VARCHAR(64) NOT NULL, application_id VARCHAR(64) DEFAULT NULL, attribute_name VARCHAR(64) NOT NULL, attribute_lowername VARCHAR(64) NOT NULL, attribute_type VARCHAR(4) NOT NULL, INDEX IDX_FA7AEFFB3E030ACD (application_id), UNIQUE INDEX udx_application_attrribute (application_id, attribute_id), PRIMARY KEY(attribute_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE password_history (password_id VARCHAR(255) NOT NULL, user_guid VARCHAR(255) NOT NULL, password VARCHAR(255) NOT NULL, added_at DATETIME NOT NULL, password_encrypt_type VARCHAR(127) NOT NULL, PRIMARY KEY(password_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE user_application (id VARCHAR(64) NOT NULL, user_id VARCHAR(64) DEFAULT NULL, application_id VARCHAR(64) DEFAULT NULL, isactive TINYINT(1) NOT NULL, application_name VARCHAR(127) NOT NULL, updated_at DATETIME DEFAULT NULL, INDEX IDX_D401454A76ED395 (user_id), INDEX IDX_D4014543E030ACD (application_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE password_policies (policy_id VARCHAR(255) NOT NULL, title VARCHAR(255) NOT NULL, regex VARCHAR(255) DEFAULT NULL, regex_error_messages LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\', validity_duration INT NOT NULL, validity_duration_error_messages LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\', history_size INT NOT NULL, history_size_error_messages LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\', edit_distance INT NOT NULL, edit_distance_error_messages LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\', change_delay INT NOT NULL, change_delay_error_messages LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\', description VARCHAR(255) NOT NULL, is_default TINYINT(1) DEFAULT NULL, error_messages LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\', PRIMARY KEY(policy_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE application (application_id VARCHAR(64) NOT NULL, application_name VARCHAR(127) NOT NULL, application_lowername VARCHAR(127) NOT NULL, application_description VARCHAR(255) DEFAULT NULL, UNIQUE INDEX udx_application_lowername (application_lowername), PRIMARY KEY(application_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE password_policy_validations (validation_id VARCHAR(255) NOT NULL, policy_id VARCHAR(255) DEFAULT NULL, order_id INT NOT NULL, regex VARCHAR(255) NOT NULL, error_messages LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\', hint_messages LONGTEXT DEFAULT NULL COMMENT \'(DC2Type:array)\', INDEX IDX_BECD1BD02D29E3C6 (policy_id), PRIMARY KEY(validation_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE user_group (user_group_id INT AUTO_INCREMENT NOT NULL, user_group_name VARCHAR(64) NOT NULL, user_group_lower_name VARCHAR(64) NOT NULL, user_group_description VARCHAR(255) DEFAULT NULL, created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, UNIQUE INDEX UNIQ_8F02BF9DD01EE39A (user_group_name), UNIQUE INDEX UNIQ_8F02BF9DFD864F93 (user_group_lower_name), INDEX idx_user_group_id (user_group_id), INDEX idx_user_group_name (user_group_lower_name), PRIMARY KEY(user_group_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE user (user_id VARCHAR(64) NOT NULL, user_guid VARCHAR(64) NOT NULL, user_email VARCHAR(127) DEFAULT NULL, user_loweremail VARCHAR(127) DEFAULT NULL, user_username VARCHAR(127) NOT NULL, user_lowerusername VARCHAR(127) NOT NULL, user_password VARCHAR(255) NOT NULL, user_firstname VARCHAR(127) DEFAULT NULL, user_lastname VARCHAR(127) DEFAULT NULL, user_isactive TINYINT(1) NOT NULL, user_passwordtoken VARCHAR(64) DEFAULT NULL, user_passwordtokenexpire DATETIME DEFAULT NULL, user_reset_password_token VARCHAR(64) DEFAULT NULL, user_reset_password_token_expire DATETIME DEFAULT NULL, user_activatetoken VARCHAR(64) DEFAULT NULL, user_activatetokenexpire DATETIME DEFAULT NULL, user_auth_id VARCHAR(64) DEFAULT NULL, user_ldap_search_attributes VARCHAR(255) DEFAULT NULL, user_ldap_search_value VARCHAR(127) DEFAULT NULL, user_deleted TINYINT(1) NOT NULL, user_deleted_at DATETIME DEFAULT NULL, user_created_at DATETIME DEFAULT NULL, user_updated_at DATETIME DEFAULT NULL, user_updated_references_at DATETIME DEFAULT NULL, user_last_login_at DATETIME DEFAULT NULL, user_login_fails INT DEFAULT NULL, mfa_enabled TINYINT(1) DEFAULT NULL, mfa_secret VARCHAR(255) DEFAULT NULL, mfa_secret_received TINYINT(1) DEFAULT NULL, mfa_created_at DATETIME DEFAULT NULL, mfa_updated_at DATETIME DEFAULT NULL, login_failed_first_at DATETIME DEFAULT NULL, login_failed_last_at DATETIME DEFAULT NULL, password_policy VARCHAR(127) DEFAULT NULL, last_pwd_change DATETIME DEFAULT NULL, password_expired TINYINT(1) DEFAULT NULL, password_encrypt_type VARCHAR(127) NOT NULL, INDEX idx_id (user_id, user_isactive), INDEX idx_firstname (user_firstname), INDEX idx_lastname (user_lastname), INDEX idx_passwordtoken (user_passwordtoken), UNIQUE INDEX udx_guid (user_guid), UNIQUE INDEX udx_email (user_loweremail), UNIQUE INDEX udx_username (user_lowerusername), PRIMARY KEY(user_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE user_to_group (user_id VARCHAR(64) NOT NULL, user_group_id INT NOT NULL, INDEX IDX_867191F5A76ED395 (user_id), INDEX IDX_867191F51ED93D47 (user_group_id), PRIMARY KEY(user_id, user_group_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE role (role_id VARCHAR(64) NOT NULL, application_id VARCHAR(64) DEFAULT NULL, role_lowername VARCHAR(127) NOT NULL, role_name VARCHAR(127) NOT NULL, role_description VARCHAR(255) DEFAULT NULL, INDEX IDX_57698A6A3E030ACD (application_id), PRIMARY KEY(role_id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE user_application_attribute (id VARCHAR(64) NOT NULL, userapplication_id VARCHAR(64) DEFAULT NULL, attribute_id VARCHAR(64) DEFAULT NULL, attribute_name VARCHAR(64) NOT NULL, attribute_type VARCHAR(4) NOT NULL, attribute_value TEXT NOT NULL, INDEX IDX_BE7FD8847FDDE7B3 (userapplication_id), INDEX IDX_BE7FD884B6E62EFA (attribute_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('CREATE TABLE user_application_role (id VARCHAR(64) NOT NULL, role_id VARCHAR(64) DEFAULT NULL, userapplication_id VARCHAR(64) DEFAULT NULL, isactive TINYINT(1) NOT NULL, role_name VARCHAR(127) NOT NULL, INDEX IDX_DD90A03F7FDDE7B3 (userapplication_id), INDEX IDX_DD90A03FD60322AC (role_id), UNIQUE INDEX udx_role (userapplication_id, role_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
        $this->addSql('ALTER TABLE attribute ADD CONSTRAINT FK_FA7AEFFB3E030ACD FOREIGN KEY (application_id) REFERENCES application (application_id)');
        $this->addSql('ALTER TABLE user_application ADD CONSTRAINT FK_D401454A76ED395 FOREIGN KEY (user_id) REFERENCES user (user_id) ON DELETE CASCADE');
        $this->addSql('ALTER TABLE user_application ADD CONSTRAINT FK_D4014543E030ACD FOREIGN KEY (application_id) REFERENCES application (application_id)');
        $this->addSql('ALTER TABLE password_policy_validations ADD CONSTRAINT FK_BECD1BD02D29E3C6 FOREIGN KEY (policy_id) REFERENCES password_policies (policy_id) ON DELETE CASCADE');
        $this->addSql('ALTER TABLE user_to_group ADD CONSTRAINT FK_867191F5A76ED395 FOREIGN KEY (user_id) REFERENCES user (user_id) ON DELETE CASCADE');
        $this->addSql('ALTER TABLE user_to_group ADD CONSTRAINT FK_867191F51ED93D47 FOREIGN KEY (user_group_id) REFERENCES user_group (user_group_id) ON DELETE CASCADE');
        $this->addSql('ALTER TABLE role ADD CONSTRAINT FK_57698A6A3E030ACD FOREIGN KEY (application_id) REFERENCES application (application_id)');
        $this->addSql('ALTER TABLE user_application_attribute ADD CONSTRAINT FK_BE7FD8847FDDE7B3 FOREIGN KEY (userapplication_id) REFERENCES user_application (id) ON DELETE CASCADE');
        $this->addSql('ALTER TABLE user_application_attribute ADD CONSTRAINT FK_BE7FD884B6E62EFA FOREIGN KEY (attribute_id) REFERENCES attribute (attribute_id)');
        $this->addSql('ALTER TABLE user_application_role ADD CONSTRAINT FK_DD90A03F7FDDE7B3 FOREIGN KEY (userapplication_id) REFERENCES user_application (id) ON DELETE CASCADE');
        $this->addSql('ALTER TABLE user_application_role ADD CONSTRAINT FK_DD90A03FD60322AC FOREIGN KEY (role_id) REFERENCES role (role_id)');
    }

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

        $this->addSql('ALTER TABLE user_application_attribute DROP FOREIGN KEY FK_BE7FD884B6E62EFA');
        $this->addSql('ALTER TABLE user_application_attribute DROP FOREIGN KEY FK_BE7FD8847FDDE7B3');
        $this->addSql('ALTER TABLE user_application_role DROP FOREIGN KEY FK_DD90A03F7FDDE7B3');
        $this->addSql('ALTER TABLE password_policy_validations DROP FOREIGN KEY FK_BECD1BD02D29E3C6');
        $this->addSql('ALTER TABLE attribute DROP FOREIGN KEY FK_FA7AEFFB3E030ACD');
        $this->addSql('ALTER TABLE user_application DROP FOREIGN KEY FK_D4014543E030ACD');
        $this->addSql('ALTER TABLE role DROP FOREIGN KEY FK_57698A6A3E030ACD');
        $this->addSql('ALTER TABLE user_to_group DROP FOREIGN KEY FK_867191F51ED93D47');
        $this->addSql('ALTER TABLE user_application DROP FOREIGN KEY FK_D401454A76ED395');
        $this->addSql('ALTER TABLE user_to_group DROP FOREIGN KEY FK_867191F5A76ED395');
        $this->addSql('ALTER TABLE user_application_role DROP FOREIGN KEY FK_DD90A03FD60322AC');
        $this->addSql('DROP TABLE dh_attribute_employee_id');
        $this->addSql('DROP TABLE dh_employee_id');
        $this->addSql('DROP TABLE attribute');
        $this->addSql('DROP TABLE password_history');
        $this->addSql('DROP TABLE user_application');
        $this->addSql('DROP TABLE password_policies');
        $this->addSql('DROP TABLE application');
        $this->addSql('DROP TABLE password_policy_validations');
        $this->addSql('DROP TABLE user_group');
        $this->addSql('DROP TABLE user');
        $this->addSql('DROP TABLE user_to_group');
        $this->addSql('DROP TABLE role');
        $this->addSql('DROP TABLE user_application_attribute');
        $this->addSql('DROP TABLE user_application_role');
    }

    /**
     * @param Schema $schema
     * @throws DBALException
     */
    public function postUp(Schema $schema)
    {
        $docs = $this->getInstallDocsAttributes();

        // Default password policy
        $policyId = Uuid::uuid4()->toString();
        $policyValidationId = Uuid::uuid4()->toString();
        $this->connection->executeQuery('INSERT INTO `password_policies` VALUES (\''. $policyId . '\', \'Default Policy\', NULL, \'a:0:{}\', 0, \'a:0:{}\', 0, \'a:0:{}\', 0, \'a:0:{}\', 0, \'a:0:{}\', \'This is an initial password policy. Should be replaced by projects policies.\', 1, \'a:0:{}\')');
        $this->connection->executeQuery('INSERT INTO `password_policy_validations` VALUES (\''. $policyValidationId . '\', \''. $policyId . '\', 1, \'.{12}\', \'a:1:{s:7:\"default\";s:50:\"Password should contain at least twelve characters\";}\', \'a:1:{s:7:\"default\";s:39:\"Please enter at least twelve characters\";}\')');

        // Application WebserviceConsumer
        $config['application']['WebserviceConsumer']['id'] = $this->addApplication('WebserviceConsumer', 'Use this application to consume this API');
        $config['application']['WebserviceConsumer']['attributes']['UserWS-ServiceToken'] = $this->addAttribute($config['application']['WebserviceConsumer']['id'], 'UserWS-ServiceToken', false);
        $config['application']['WebserviceConsumer']['roles']['WebserviceConsumer:write'] = $this->addRole($config['application']['WebserviceConsumer']['id'], 'WebserviceConsumer:write');
        $config['application']['WebserviceConsumer']['roles']['WebserviceConsumer:read'] = $this->addRole($config['application']['WebserviceConsumer']['id'], 'WebserviceConsumer:read');
        $config['application']['WebserviceConsumer']['roles']['SsoIamApplication:write'] = $this->addRole($config['application']['WebserviceConsumer']['id'], 'SsoIamApplication:write');
        $config['application']['WebserviceConsumer']['roles']['SsoIamApplication:read'] = $this->addRole($config['application']['WebserviceConsumer']['id'], 'SsoIamApplication:read');
        $config['application']['WebserviceConsumer']['roles']['SsoLaunchpadApplication:write'] = $this->addRole($config['application']['WebserviceConsumer']['id'], 'SsoLaunchpadApplication:write');
        $config['application']['WebserviceConsumer']['roles']['SsoLaunchpadApplication:read'] = $this->addRole($config['application']['WebserviceConsumer']['id'], 'SsoLaunchpadApplication:read');

        // Application SsoIamApplication
        $config['application']['SsoIamApplication']['id'] = $this->addApplication('SsoIamApplication', 'IAM - Manage user accounts');
        $config['application']['SsoIamApplication']['roles']['ROLE_SUPER_ADMIN'] = $this->addRole($config['application']['SsoIamApplication']['id'], 'ROLE_SUPER_ADMIN');
        $config['application']['SsoIamApplication']['roles']['ROLE_ADMIN'] = $this->addRole($config['application']['SsoIamApplication']['id'], 'ROLE_ADMIN');

        // Application SsoLaunchpadApplication
        $config['application']['SsoLaunchpadApplication']['id'] = $this->addApplication('SsoLaunchpadApplication', 'Launchpad - Start applications');

        // Master-user
        $config['user']['master-user'] = $this->addUser('master-' . hash('crc32', Uuid::uuid4()->toString()), '$2y$10$4e.M0c0Y4pRs1TwmFnjI7uKH8ckLjlWzZQjcXGN.oISccGy2h1cdi', 'api-master-user');
        $config['user']['master-user']['applications']['WebserviceConsumer']['id'] = $this->addUserApplication(
            $config['user']['master-user']['id'],
            $config['application']['WebserviceConsumer']['id'],
            'WebserviceConsumer'
        );
        $config['user']['master-user']['applications']['WebserviceConsumer']['attributes']['UserWS-ServiceToken'] = $this->addUserAttribute(
            $config['user']['master-user']['applications']['WebserviceConsumer']['id'],
            $config['application']['WebserviceConsumer']['attributes']['UserWS-ServiceToken'],
            'UserWS-ServiceToken',
            false,
            $docs['service_provider']['master_token']
        );
        $config['user']['master-user']['applications']['WebserviceConsumer']['roles']['WebserviceConsumer:write'] = $this->addUserRole(
            $config['user']['master-user']['applications']['WebserviceConsumer']['id'],
            $config['application']['WebserviceConsumer']['roles']['WebserviceConsumer:write'],
            'WebserviceConsumer:write'
        );
        $config['user']['master-user']['applications']['WebserviceConsumer']['roles']['SsoIamApplication:write'] = $this->addUserRole(
            $config['user']['master-user']['applications']['WebserviceConsumer']['id'],
            $config['application']['WebserviceConsumer']['roles']['SsoIamApplication:write'],
            'SsoIamApplication:write'
        );
        $config['user']['master-user']['applications']['WebserviceConsumer']['roles']['SsoLaunchpadApplication:write'] = $this->addUserRole(
            $config['user']['master-user']['applications']['WebserviceConsumer']['id'],
            $config['application']['WebserviceConsumer']['roles']['SsoLaunchpadApplication:write'],
            'SsoLaunchpadApplication:write'
        );

        // Wsp User
        $config['user']['wsp-user'] = $this->addUser('wsp-' . hash('crc32', Uuid::uuid4()->toString()), '$2y$10$YUNES70EtKC4Zn6mGNcjn.gGhO7LuYDQlq.gi6aaCmxbwISpyObsy', 'api-wsp-user');
        $config['user']['wsp-user']['applications']['WebserviceConsumer']['id'] = $this->addUserApplication(
            $config['user']['wsp-user']['id'],
            $config['application']['WebserviceConsumer']['id'],
            'WebserviceConsumer'
        );
        $config['user']['wsp-user']['applications']['WebserviceConsumer']['attributes']['UserWS-ServiceToken'] = $this->addUserAttribute(
            $config['user']['wsp-user']['applications']['WebserviceConsumer']['id'],
            $config['application']['WebserviceConsumer']['attributes']['UserWS-ServiceToken'],
            'UserWS-ServiceToken',
            false,
            $docs['service_provider']['wsp_token']
        );

        // IAM User
        $config['user']['iam-user'] = $this->addUser('iam-' . hash('crc32', Uuid::uuid4()->toString()), '$2y$10$ctUlKjyaBuN3eJOf9Z92JO1M3ehQZiZN3liO1EM10/wpJb0d9mgCi', 'api-iam-user');
        $config['user']['iam-user']['applications']['WebserviceConsumer']['id'] = $this->addUserApplication(
            $config['user']['iam-user']['id'],
            $config['application']['WebserviceConsumer']['id'],
            'WebserviceConsumer'
        );
        $config['user']['iam-user']['applications']['WebserviceConsumer']['attributes']['UserWS-ServiceToken'] = $this->addUserAttribute(
            $config['user']['iam-user']['applications']['WebserviceConsumer']['id'],
            $config['application']['WebserviceConsumer']['attributes']['UserWS-ServiceToken'],
            'UserWS-ServiceToken',
            false,
            $docs['service_provider']['iam_token']
        );
        $config['user']['iam-user']['applications']['WebserviceConsumer']['roles']['SsoLaunchpadApplication:write'] = $this->addUserRole(
            $config['user']['iam-user']['applications']['WebserviceConsumer']['id'],
            $config['application']['WebserviceConsumer']['roles']['SsoLaunchpadApplication:write'],
            'SsoLaunchpadApplication:write'
        );

        // Launchpad User
        $config['user']['launchpad-user'] = $this->addUser('launchpad-' . hash('crc32', Uuid::uuid4()->toString()), '$2y$10$Rb9vTyRXAnBIvnVUefQ.4OBokrveJ3f/c/IJoXzj9dBlU5xXC/mfu', 'api-launchpad-user');
        $config['user']['launchpad-user']['applications']['WebserviceConsumer']['id'] = $this->addUserApplication(
            $config['user']['launchpad-user']['id'],
            $config['application']['WebserviceConsumer']['id'],
            'WebserviceConsumer'
        );
        $config['user']['launchpad-user']['applications']['WebserviceConsumer']['attributes']['UserWS-ServiceToken'] = $this->addUserAttribute(
            $config['user']['launchpad-user']['applications']['WebserviceConsumer']['id'],
            $config['application']['WebserviceConsumer']['attributes']['UserWS-ServiceToken'],
            'UserWS-ServiceToken',
            false,
            $docs['service_provider']['launchpad_token']
        );
        $config['user']['launchpad-user']['applications']['WebserviceConsumer']['roles']['SsoIamApplication:read'] = $this->addUserRole(
            $config['user']['launchpad-user']['applications']['WebserviceConsumer']['id'],
            $config['application']['WebserviceConsumer']['roles']['SsoIamApplication:read'],
            'SsoIamApplication:read'
        );

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

    /**
     * @param string $name
     * @param string $description
     * @return string
     * @throws DBALException
     */
    private function addApplication(string $name, string $description): string
    {
        $id = hash('sha256', Uuid::uuid4()->toString());
        $this->connection->executeQuery('INSERT INTO `application` VALUES (\'' . $id . '\', \''. $name . '\', \''. strtolower($name) . '\', \''. $description . '\')');
        return $id;
    }

    /**
     * @param string $appId
     * @param string $name
     * @param bool $typeOne
     * @return string
     * @throws DBALException
     */
    private function addAttribute(string $appId, string $name, bool $typeOne): string
    {
        $id = hash('sha256', Uuid::uuid4()->toString());
        $this->connection->executeQuery('INSERT INTO `attribute` VALUES (\'' . $id . '\', \'' . $appId . '\', \''. $name . '\', \'' . strtolower($name) . '\', \'' . ($typeOne ? 'one' : 'many') . '\')');
        return $id;
    }

    /**
     * @param string $appId
     * @param string $name
     * @return string
     * @throws DBALException
     */
    private function addRole(string $appId, string $name): string
    {
        $id = hash('sha256', Uuid::uuid4()->toString());
        $this->connection->executeQuery('INSERT INTO `role` VALUES (\'' . $id . '\', \'' . $appId . '\', \'' . strtolower($name) . '\', \''. $name . '\', NULL)');
        return $id;
    }

    /**
     * @param string $name
     * @param string $password
     * @param string $passwordPlain
     * @return array
     * @throws DBALException
     */
    private function addUser(string $name, string $password, string $passwordPlain): array
    {
        $id = hash('sha256', Uuid::uuid4()->toString());
        $guid = Uuid::uuid4()->toString();
        $email = $name . '@life-style.de';
        $this->connection->executeQuery('INSERT INTO `user` VALUES (\'' . $id . '\', \''. $guid . '\', \''. $email . '\', \''. strtolower($email) . '\', \''. $name . '\', \''. strtolower($name) . '\', \''. $password . '\', \'\', \'\', 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, \'2000-01-01 00:00:00\', \'2000-01-01 00:00:00\', \'2000-01-01 00:00:00\', NULL, NULL, 0, NULL, 1, NULL, NULL, NULL, NULL, \'\', NULL, NULL, \'default\')');
        return ['id' => $id, 'guid' => $guid, 'name' => $name, 'email' => $email, 'password' => $passwordPlain];
    }

    /**
     * @param string $userId
     * @param string $appId
     * @param string $appName
     * @return string
     * @throws DBALException
     */
    private function addUserApplication(string $userId, string $appId, string $appName): string
    {
        $id = hash('sha256', Uuid::uuid4()->toString());
        $this->connection->executeQuery('INSERT INTO `user_application` VALUES (\'' . $id . '\', \'' . $userId . '\', \'' . $appId . '\', 1, \''. $appName . '\', \'2000-01-01 00:00:00\')');
        return $id;
    }

    /**
     * @param string $userAppId
     * @param string $attributeId
     * @param string $attributeName
     * @param bool $attributeTypeOne
     * @param string $value
     * @return string
     * @throws DBALException
     */
    private function addUserAttribute(string $userAppId, string $attributeId, string $attributeName, bool $attributeTypeOne, string $value): string
    {
        $id = hash('sha256', Uuid::uuid4()->toString());
        $this->connection->executeQuery('INSERT INTO `user_application_attribute` VALUES (\'' . $id . '\', \''. $userAppId. '\', \'' . $attributeId . '\', \'' . $attributeName . '\', \'' . ($attributeTypeOne ? 'one' : 'many') . '\', \''. $value . '\')');
        return $id;
    }

    /**
     * @param string $userAppId
     * @param string $roleId
     * @param string $roleName
     * @return string
     * @throws DBALException
     */
    private function addUserRole(string $userAppId, string $roleId, string $roleName): string
    {
        $id = hash('sha256', Uuid::uuid4()->toString());
        $this->connection->executeQuery('INSERT INTO `user_application_role` VALUES (\'' . $id . '\', \''. $roleId. '\', \'' . $userAppId . '\', 1, \'' . $roleName . '\')');
        return $id;
    }
}
