<?php

use Phalcon\Mvc\Model\Migration;

class PAKRoleAndScopesMigration_294 extends Migration
{
    public function up()
    {
        $this->down(); 
        
        self::$connection->execute("
            INSERT INTO jaga.scopes (name, label, description) VALUES 
                ('pak.manage.isiansekolah', 'Mengelola isian sekolah modul PAK', 'Mengelola isian sekolah modul PAK'),
                ('pak.view.isiansekolah', 'Melihat isian sekolah modul PAK', 'Melihat isian sekolah modul PAK'),
                ('pak.verify.isiansekolah', 'Verifikasi isian sekolah PAK', 'Verifikasi isian sekolah PAK')
                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 = 'admin_pak' and s.name = 'pak.manage.isiansekolah' 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 = 'user_sekolah_pak' and s.name = 'pak.manage.isiansekolah' 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 = 'admin_pak' and s.name = 'pak.view.isiansekolah' 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 = 'user_sekolah_pak' and s.name = 'pak.view.isiansekolah' 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 = 'user_dinas_pak' and s.name = 'pak.view.isiansekolah' 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 = 'verifikator_pak' and s.name = 'pak.view.isiansekolah' 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 = 'observer_pak' and s.name = 'pak.view.isiansekolah' 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 = 'verifikator_pak' and s.name = 'pak.verify.isiansekolah' ON CONFLICT DO NOTHING;
        ");
        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 = 'user_dinas_pak' and s.name = 'pak.manage.user' 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 = 'admin_pak' and s.name = 'pak.manage.user' ON CONFLICT DO NOTHING;
        ");
    }

    public function down()
    {

        self::$connection->execute("
            DELETE FROM jaga.roles_scopes WHERE scope_id IN 
            (SELECT id as scope_id FROM scopes WHERE 
                name = 'pak.manage.isiansekolah' OR
                name = 'pak.view.isiansekolah' OR
                name = 'pak.verify.isiansekolah'
            );
        ");

        self::$connection->execute("

            DELETE FROM jaga.scopes WHERE 
                name = 'pak.manage.isiansekolah' OR
                name = 'pak.view.isiansekolah' OR
                name = 'pak.verify.isiansekolah';

        ");

    }
}