<?php

use Phalcon\Mvc\Model\Migration;

class RoleKemendagriBpkpMigration_349  extends Migration
{
    public function up()
    {
        $this->down();

        self::$connection->execute("
            INSERT INTO jaga.roles(name, label, description) VALUES ('observer_kemdagri_bpkp', 'Observer MCP Kemdagri/BPKP', 'Observer MCP Kemdagri/BPKP (Tidak bisa melihat lampiran)');
            INSERT INTO jaga.roles(name, label, description) VALUES ('verifikator_kemdagri_bpkp', 'Verifikator MCP Kemdagri/BPKP', 'Verifikator MCP Kemdagri/BPKP');
            SELECT setval(pg_get_serial_sequence('roles', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.roles;

        ");

        self::$connection->execute("
            INSERT INTO jaga.scopes(name, label, description) VALUES ('korwil.observer_kemdagri_bpkp', 'Observer MCP Kemdagri/BPKP', 'Observer MCP Kemdagri/BPKP (Tidak bisa melihat lampiran)');
            INSERT INTO jaga.scopes(name, label, description) VALUES ('korwil.verifikator_kemdagri_bpkp', 'Monitoring MCP BPKP', 'Monitoring MCP BPKP');
            SELECT setval(pg_get_serial_sequence('scopes', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.scopes;

        ");

        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 = 'observer_kemdagri_bpkp' and s.name = 'korwil.observer_kemdagri_bpkp' 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 = 'verifikator_kemdagri_bpkp' and s.name = 'korwil.verifikator_kemdagri_bpkp' ON CONFLICT DO NOTHING;

        ");

        //INSERT NEW JABATAN
        $sql = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . 'user_kemdagri_bpkp_prod.sql');
        self::$connection->execute($sql);

    }

    public function down()
    {
        self::$connection->execute("
            DELETE 
            FROM
            	jaga.roles_scopes 
            WHERE
            	role_id = ( SELECT ID FROM jaga.roles WHERE NAME = 'observer_kemdagri_bpkp' ) 
            	AND scope_id = ( SELECT ID FROM jaga.scopes WHERE NAME = 'korwil.observer_kemdagri_bpkp' );
        ");

        self::$connection->execute("
            DELETE 
            FROM
            	jaga.roles_scopes 
            WHERE
            	role_id = ( SELECT ID FROM jaga.roles WHERE NAME = 'verifikator_kemdagri_bpkp' ) 
            	AND scope_id = ( SELECT ID FROM jaga.scopes WHERE NAME = 'korwil.verifikator_kemdagri_bpkp' );
        ");

        self::$connection->execute("
            DELETE FROM jaga.roles WHERE name IN 
            ('observer_kemdagri_bpkp', 'verifikator_kemdagri_bpkp');
            
            SELECT setval(pg_get_serial_sequence('roles', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.roles;
        ");

        self::$connection->execute("
            DELETE FROM jaga.scopes WHERE name IN 
            ('korwil.observer_kemdagri_bpkp', 'korwil.verifikator_kemdagri_bpkp');
                  
            SELECT setval(pg_get_serial_sequence('scopes', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.scopes;
        ");
    }
}