<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute("
            INSERT INTO jaga.roles(name, label, description) VALUES ('spi_admin', 'Administrator SPI', 'Administrator SPI');
            INSERT INTO jaga.roles(name, label, description) VALUES ('spi_monitoring', 'Monitoring SPI', 'Monitoring SPI semua data KLOP');
            INSERT INTO jaga.roles(name, label, description) VALUES ('spi_admin_klop', 'Administrator SPI KLOP', 'Administrator KLOP untuk data KLOP sendiri');

        ");

        self::$connection->execute("
            INSERT INTO jaga.scopes(name, label, description) VALUES ('spi.manage_master_data', 'Manajemen SPI', 'Manajemen Data Master');
            INSERT INTO jaga.scopes(name, label, description) VALUES ('spi.monitoring', 'Monitoring SPI', 'Melihat semua data KLOP');
            INSERT INTO jaga.scopes(name, label, description) VALUES ('spi.view_klop_data', 'Administrator SPI KLOP', 'Hanya bisa lihat data KLOP itu sendiri');

        ");

        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 = 'spi_admin' and s.name = 'spi.manage_master_data' 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 = 'spi_monitoring' and s.name = 'spi.monitoring' 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 = 'spi_admin_klop' and s.name = 'spi.view_klop_data' 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 = 'spi_admin' ) 
            	AND scope_id = ( SELECT ID FROM jaga.scopes WHERE NAME = 'spi.manage_master_data' );
        ");

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

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

        self::$connection->execute("
            DELETE FROM jaga.roles WHERE name IN 
            ('spi_admin', 'spi_monitoring', 'spi_admin_klop');
        ");

        self::$connection->execute("
            DELETE FROM jaga.scopes WHERE name IN 
            ('spi.manage_master_data', 'spi.monitoring', 'spi.view_klop_data');
        ");
    }
}