<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute("
            INSERT INTO jaga.roles(name, label, description) VALUES ('korwil_kemdagri', 'Monitoring MCP Kemdagri', 'Monitoring MCP Kemdagri');
            INSERT INTO jaga.roles(name, label, description) VALUES ('korwil_bpkp', 'Monitoring MCP BPKP', 'Monitoring MCP 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.monitoring_kemdagri', 'Monitoring MCP Kemdagri', 'Monitoring MCP Kemdagri');
            INSERT INTO jaga.scopes(name, label, description) VALUES ('korwil.monitoring_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 = 'korwil_kemdagri' and s.name = 'korwil.monitoring_kemdagri' 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 = 'korwil_bpkp' and s.name = 'korwil.monitoring_bpkp' ON CONFLICT DO NOTHING;

        ");

        self::$connection->execute('
            ALTER TABLE "jaga"."jabatan"
                ADD COLUMN IF NOT EXISTS code character varying (50);
        ');

        //ALTER NAMA JABATAN LENGTH
        self::$connection->execute('
            ALTER TABLE "jaga"."jabatan" 
                ALTER COLUMN "nama" TYPE varchar(255);
        ');

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

        self::$connection->execute("
            CREATE TABLE jaga.korsupgah_area_fokus_penanggung_jawab
            (
                id bigserial NOT NULL,
            	id_area_fokus bigint NOT NULL,
            	id_jabatan bigint NOT NULL,
                PRIMARY KEY (id)
            )
            WITH (
                OIDS = FALSE
            );
        ");

        //INSERT PENANGGUNG JAWAB FOKUS AREA
        $sqlPenanggungJawab = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . '2_penanggung_jawab_fokus_area.sql');
        self::$connection->execute($sqlPenanggungJawab);

        //UPDATE JABATAN USER KEMDAGRI PROD
        $sqlUpdateJabatan = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . '3_update_jabatan_user_kemdagri.sql');
        self::$connection->execute($sqlUpdateJabatan);

        self::$connection->execute("
            CREATE TABLE jaga.korwil_user_bpkp_provinsi
            (
                id bigserial NOT NULL,
            	user_id bigint NOT NULL,
            	provinsi_id bigint NOT NULL,
                PRIMARY KEY (id)
            )
            WITH (
                OIDS = FALSE
            );
        ");

        $sql1 = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . 'user_bpkp_prod.sql');
        self::$connection->execute($sql1);

        $sql2 = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . 'user_kemdagri_prod.sql');
        self::$connection->execute($sql2);
    }

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

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

        self::$connection->execute("
            DELETE FROM jaga.roles WHERE name IN 
            ('korwil_kemdagri', 'korwil_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.monitoring_kemdagri', 'korwil.monitoring_bpkp');
                  
            SELECT setval(pg_get_serial_sequence('scopes', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.scopes;
        ");

        //DELETE JABATAN KEMDAGRI
        $sqlDelete = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . '0_down_jabatan_kemdagri.sql');
        self::$connection->execute($sqlDelete);

        self::$connection->execute('
            ALTER TABLE "jaga"."jabatan"
                DROP COLUMN IF EXISTS code;
        ');

        self::$connection->execute('
            DROP TABLE IF EXISTS jaga.korsupgah_area_fokus_penanggung_jawab;
        ');

        self::$connection->execute('
            DROP TABLE IF EXISTS jaga.korwil_user_bpkp_provinsi;
        ');
    }
}