<?php

use Phalcon\Mvc\Model\Migration;
class PengembanganPancekMigration_533 extends Migration
{
    public function up()
    {
        $this->down();

        self::$connection->execute("
            ALTER TABLE jaga.pancek_answer ADD COLUMN IF NOT EXISTS verify_status CHARACTER VARYING(50);
            ALTER TABLE jaga.pancek_answer ADD COLUMN IF NOT EXISTS verified_at timestamp;
            ALTER TABLE jaga.pancek_answer ADD COLUMN IF NOT EXISTS verified_by int;
        ");

        self::$connection->execute('
            CREATE TABLE IF NOT EXISTS "jaga"."pancek_answer_log_verifikasi" (
                id bigserial NOT NULL,
                um_id INTEGER NOT NULL,
                answer_id INTEGER NOT NULL,
                komentar text,
                verify_status CHARACTER VARYING(50) NOT NULL,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6) DEFAULT now(),
                PRIMARY KEY (id)
            );
        ');

        self::$connection->execute("
            INSERT INTO jaga.scopes(name, label, description) VALUES ('pancek.kppbc.verify', 'Melakukan Verifikasi Badan Usaha terdaftar untuk Bea Cukai', 'Melakukan Verifikasi Badan Usaha terdaftar untuk Bea Cukai');

            INSERT INTO jaga.roles(name, label, description) VALUES ('verifikator_kppbc_pancek', 'Verifikator Pancek KPPBC', 'Verifikator Pancek KPPBC');

            INSERT INTO jaga.roles_scopes(role_id, scope_id) values(
				(select id from jaga.roles where name = 'verifikator_kppbc_pancek'), (select id from jaga.scopes where name = 'pancek.kppbc.verify') 
            );
        ");

        self::$connection->execute('
            CREATE TABLE IF NOT EXISTS "jaga"."pancek_kppbc" (
                id bigserial NOT NULL,
                nama CHARACTER VARYING(255) NOT NULL,
                id_provinsi INTEGER,
                id_kota_kabupaten INTEGER,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6) DEFAULT now(),
                deleted_at timestamp(6),
                PRIMARY KEY (id)
            );
        ');

        self::$connection->execute('
            CREATE TABLE IF NOT EXISTS "jaga"."pancek_kppbc_user" (
                id bigserial NOT NULL,
                kppbc_id BIGINT NOT NULL,
                um_id INT NOT NULL,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6) DEFAULT now(),
                deleted_at timestamp(6),
                PRIMARY KEY (id)
            );
        ');

        self::$connection->execute('
            ALTER TABLE jaga.pancek_instansi ADD COLUMN IF NOT EXISTS kppbc_id INT;
        ');

        self::$connection->execute('
            ALTER TABLE jaga.pancek_formulir ADD COLUMN IF NOT EXISTS kppbc_um_id INT;
            ALTER TABLE jaga.pancek_formulir ADD COLUMN IF NOT EXISTS kppbc_verify_start_date timestamp(6);
            ALTER TABLE jaga.pancek_formulir ADD COLUMN IF NOT EXISTS kppbc_verify_end_date timestamp(6);
        ');

        self::$connection->execute("
            INSERT INTO jaga.scopes(name, label, description) VALUES ('pancek.kppbc.assignor', 'Melakukan Assign KPPBC ke Badan Usaha Bea Cukai tertentu', 'Melakukan Assign KPPBC ke Badan Usaha Bea Cukai tertentu');

            INSERT INTO jaga.roles(name, label, description) VALUES ('assignor_kppbc_pancek', 'Assignor Pancek KPPBC', 'Assignor Pancek KPPBC');

            INSERT INTO jaga.roles_scopes(role_id, scope_id) values(
				(select id from jaga.roles where name = 'assignor_kppbc_pancek'), (select id from jaga.scopes where name = 'pancek.kppbc.assignor') 
            );
        ");

        self::$connection->execute('
            ALTER TABLE "jaga"."pancek_kppbc_user" ADD COLUMN IF NOT EXISTS user_type CHARACTER VARYING(50);
        ');

        self::$connection->execute('
			UPDATE "jaga"."pancek_kppbc_user" set user_type = \'VERIFIKATOR\';
        ');

        self::$connection->execute('
            CREATE TABLE IF NOT EXISTS "jaga"."pancek_formulir_log_verifikasi" (
                id bigserial NOT NULL,
                um_id INTEGER NOT NULL,
                formulir_id INTEGER NOT NULL,
                komentar text,
                verify_status CHARACTER VARYING(50) NOT NULL,
                sla_started_at timestamp(6),
                sla_ended_at timestamp(6),
                verified_at timestamp(6),
                created_at timestamp(6) DEFAULT now(),
                PRIMARY KEY (id)
            );
        ');

        self::$connection->execute("
            UPDATE jaga.pancek_jenis_instansi SET formulir_type = 1 WHERE nama = 'SWASTA'
        ");

        self::$connection->execute("
            ALTER TABLE jaga.pancek_konsep_sistem_pencegahan ADD COLUMN IF NOT EXISTS label_en CHARACTER VARYING(255);
            ALTER TABLE jaga.pancek_indikator ADD COLUMN IF NOT EXISTS label_en text;
            ALTER TABLE jaga.pancek_komponen_indikator ADD COLUMN IF NOT EXISTS label_en text;
        ");

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

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

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

        self::$connection->execute("
            ALTER TABLE jaga.pancek_komponen_template ADD COLUMN IF NOT EXISTS language CHARACTER VARYING(2);
        ");

        self::$connection->execute("
            UPDATE jaga.pancek_komponen_template SET language = 'ID';
        ");
    }

    public function down()
    {
        self::$connection->execute("
            ALTER TABLE jaga.pancek_komponen_template DROP COLUMN IF EXISTS language;
        ");

        self::$connection->execute("
            ALTER TABLE jaga.pancek_konsep_sistem_pencegahan DROP COLUMN IF EXISTS label_en;
            ALTER TABLE jaga.pancek_indikator DROP COLUMN IF EXISTS label_en;
            ALTER TABLE jaga.pancek_komponen_indikator DROP COLUMN IF EXISTS label_en;
        ");

        self::$connection->execute('
            UPDATE "jaga"."pancek_jenis_instansi" SET "formulir_type" = 2 WHERE "nama" = \'SWASTA\'
        ');

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

        self::$connection->execute("
            DELETE FROM jaga.roles_scopes WHERE 
            role_id = (select id from jaga.roles where name = 'assignor_kppbc_pancek');
            SELECT setval(pg_get_serial_sequence('roles_scopes', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.roles_scopes;
            
            DELETE FROM jaga.scopes WHERE name = 'pancek.kppbc.assignor';
            SELECT setval(pg_get_serial_sequence('roles', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.roles;

            DELETE FROM jaga.roles WHERE name = 'assignor_kppbc_pancek';
            SELECT setval(pg_get_serial_sequence('scopes', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.scopes;
        ");

        self::$connection->execute("
            ALTER TABLE jaga.pancek_formulir DROP COLUMN IF EXISTS kppbc_um_id;
            ALTER TABLE jaga.pancek_formulir DROP COLUMN IF EXISTS kppbc_verify_start_date;
            ALTER TABLE jaga.pancek_formulir DROP COLUMN IF EXISTS kppbc_verify_end_date;
        ");

        self::$connection->execute("
            ALTER TABLE jaga.pancek_instansi DROP COLUMN IF EXISTS kppbc_id;
        ");

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

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

        self::$connection->execute("
            DELETE FROM jaga.roles_scopes WHERE 
            role_id = (select id from jaga.roles where name = 'verifikator_kppbc_pancek');
            SELECT setval(pg_get_serial_sequence('roles_scopes', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.roles_scopes;
            
            DELETE FROM jaga.scopes WHERE name = 'pancek.kppbc.verify';
            SELECT setval(pg_get_serial_sequence('roles', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.roles;

            DELETE FROM jaga.roles WHERE name = 'verifikator_kppbc_pancek';
            SELECT setval(pg_get_serial_sequence('scopes', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.scopes;
        ");

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

        self::$connection->execute("
            ALTER TABLE jaga.pancek_answer DROP COLUMN IF EXISTS verified_by;
            ALTER TABLE jaga.pancek_answer DROP COLUMN IF EXISTS verified_at;
            ALTER TABLE jaga.pancek_answer DROP COLUMN IF EXISTS verify_status;
        ");
    }
}