<?php

class RoleScopeGOLMigration_500 extends \Phalcon\Mvc\Model\Migration {
    public function up()
    {
        self::$connection->execute("
            INSERT INTO jaga.roles(name, label, description) VALUES ('spi_gol', 'GOL SPI', 'Pengguna GOL untuk mengakses API SPI Gratifikasi');
        ");

        self::$connection->execute("
            INSERT INTO jaga.scopes(name, label, description) VALUES ('spi.gratifikasi', 'Mengakses API SPI Gratifikasi', 'Mengakses API SPI Gratifikasi');
        ");

        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_gol' and s.name = 'spi.gratifikasi' 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_gol' ) 
            	AND scope_id = ( SELECT ID FROM jaga.scopes WHERE NAME = 'spi.gratifikasi' );
        ");

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

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