<?php

use Phalcon\Mvc\Model\Migration;

class RoleScopeMcpPicMigration_506 extends Migration {
    public function up() {
        self::$connection->execute("
            INSERT INTO jaga.roles(name, label, description) VALUES ('pic_korsup', 'PIC Korsup', 'Verifikasi final akhir periode');
        ");

        self::$connection->execute("
            INSERT INTO jaga.scopes(name, label, description) VALUES ('korsup.pic', 'Verifikasi final akhir periode MCP', 'Verifikasi final akhir periode MCP');
        ");
        self::$connection->execute("
            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id
                    FROM jaga.roles r
                    JOIN jaga.scopes s ON r.name = 'pic_korsup' and s.name = 'korsup.pic' ON CONFLICT DO NOTHING;
            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id
                    FROM jaga.roles r
                    JOIN jaga.scopes s ON r.name = 'pic_korsup' and s.name = 'korsupgah.monitoring' ON CONFLICT DO NOTHING;
            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id
                    FROM jaga.roles r
                    JOIN jaga.scopes s ON r.name = 'pic_korsup' and s.name = 'korsupgah.inspect_data' ON CONFLICT DO NOTHING;
            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id
                    FROM jaga.roles r
                    JOIN jaga.scopes s ON r.name = 'pic_korsup' and s.name = 'bansos.view_assigned' ON CONFLICT DO NOTHING;
            INSERT INTO jaga.roles_scopes (role_id, scope_id)
                SELECT r.id, s.id
                    FROM jaga.roles r
                    JOIN jaga.scopes s ON r.name = 'pic_korsup' and s.name = 'bansos.confirm_assigned' ON CONFLICT DO NOTHING;
        ");
    }

    public function down() {
        self::$connection->execute("
            DELETE 
            FROM
            	jaga.roles_scopes 
            WHERE
            	role_id = ( SELECT ID FROM jaga.roles WHERE NAME = 'pic_korsup')
                and scope_id in (SELECT id from jaga.scopes where name in 
                ('korsup.pic', 'korsupgah.monitoring', 'korsupgah.inspect_data',
                 'bansos.view_assigned', 'bansos.confirm_assigned'));
        ");

        self::$connection->execute("
            DELETE FROM jaga.roles WHERE name = 'pic_korsup';
        ");

        self::$connection->execute("
            DELETE FROM jaga.scopes WHERE name = 'korsup.pic';
        ");
    }
}