<?

final class TreeChanges extends Migration
{

    const FIELDS = [
        'RANGE_TREE_PERM',
        'SEM_TREE_PERM'
    ];

    public function description()
    {
        return 'Removes old sem_- and range_tree permission settings and institute assignments for sem_tree entries';
    }

    protected function up()
    {
        // Remove config fields for special permissions concerning sem_- and range_tree administration.
        DBManager::get()->execute(
            "DELETE FROM `config_values` WHERE `field` IN (:fields)",
            ['fields' => self::FIELDS]
        );
        DBManager::get()->execute(
            "DELETE FROM `config` WHERE `field` IN (:fields)",
            ['fields' => self::FIELDS]
        );

        // "Transfer" names from assigned institutes to sem_tree entries.
        $stmt = DBManager::get()->prepare("UPDATE `sem_tree` SET `name` = :name WHERE `studip_object_id` = :inst");
        $query = "SELECT DISTINCT `Institut_id`, `Name` FROM `Institute` WHERE `Institut_id` IN (
                SELECT DISTINCT `studip_object_id` FROM  `sem_tree`
            )";
        foreach (DBManager::get()->fetchAll($query) as $institute) {
            $stmt->execute(['name' => $institute['Name'], 'inst' => $institute['Institut_id']]);
        }
        // Remove institute assignments for sem_tree entries.
        DBManager::get()->exec("ALTER TABLE `sem_tree` DROP `studip_object_id`");
    }

    protected function down()
    {
        // Restore config entries to their defaults.
        DBManager::get()->exec("INSERT IGNORE INTO `config`
                ( `config_id` , `parent_id` , `field` , `value` ,
                 `is_default` , `type` , `range` , `section` ,
                  `position` , `mkdate` , `chdate` , `description` ,
                   `comment` , `message_template` )
                VALUES (
                MD5( 'RANGE_TREE_ADMIN_PERM' ) , '', 'RANGE_TREE_ADMIN_PERM',
                 'admin', '1', 'string', 'global', '', '0',
                  UNIX_TIMESTAMP( ) , UNIX_TIMESTAMP( ) ,
                   'mit welchem Status darf die Einrichtungshierarchie bearbeitet werden (admin oder root)', '', ''
                ), (
                MD5( 'SEM_TREE_ADMIN_PERM' ) , '', 'SEM_TREE_ADMIN_PERM',
                 'admin', '1', 'string', 'global', '', '0', UNIX_TIMESTAMP( ) ,
                  UNIX_TIMESTAMP( ) , 'mit welchem Status darf die Veranstaltungshierarchie bearbeitet werden (admin oder root)', '', ''
                )");

        // Add database column for sem_tree institute assignments.
        DBManager::get()->exec("ALTER TABLE `sem_tree` ADD
            `studip_object_id` CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin NULL DEFAULT NULL AFTER `name`");
        // Add index for studip_object_id.
        DBManager::get()->exec("ALTER TABLE `sem_tree` ADD INDEX `studip_object_id` (`studip_object_id`)");
    }

}