<?php

use Phalcon\Mvc\Model\Migration;

class SpiIntExtDetailMigration_497 extends Migration {
    public function up()
    {
        self::$connection->execute("
            ALTER TABLE jaga.spi_int_ext_kode_mapping ADD COLUMN IF NOT EXISTS is_required boolean DEFAULT TRUE;
            INSERT INTO jaga.spi_int_ext_kode_mapping (code_key, variable, type, indikator_key, indikator, deskripsi, tipe, is_required) VALUES ('int_501b', 'INT501B', 'INTERNAL', 'sosialisasi_antikorupsi', 'Sosialisasi Antikorupsi', 'Seberapa Sering Sosialisasi Antikorupsi Dilakukan', 'Upaya Pencegahan Korupsi', false);
            UPDATE jaga.spi_int_ext_kode_mapping SET is_required = false WHERE code_key IN ('ext_405b', 'ext_407b', 'ext_407c');
        ");
        self::$connection->execute("
            DROP TABLE IF EXISTS jaga.spi_internal_eksternal_detail;
        ");

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.spi_internal_eksternal_detail (
                id SERIAL PRIMARY KEY,
                tahun VARCHAR(4),
                id_instansi integer,
                id_provinsi integer,
                id_kota_kabupaten integer,
                nama_instansi VARCHAR(255),
                indeks numeric,
                indeks_internal numeric,
                indeks_eksternal numeric,
                indeks_eksper numeric,
                koreksi_prevalensi_korupsi numeric,
                koreksi_integritas_pelaksanaan_survei numeric,
                integritas_tugas numeric,
                pengelolaan_anggaran numeric,
                pengelolaan_pbj numeric,
                pengelolaan_sdm numeric,
                trading_in_influence numeric,
                sosialisasi_antikorupsi numeric,
                transparansi numeric,
                integritas_pegawai numeric,
                transparansi_keadilan_layanan numeric,
                upaya_pencegah_korupsi numeric,
                integritas_instansi numeric,
                int_401a numeric,
                int_401b numeric,
                int_402_1 numeric,
                int_402_2 numeric,
                int_402_3 numeric,
                int_402_4 numeric,
                int_402_5 numeric,
                int_402_6 numeric,
                int_403a numeric,
                int_403b numeric,
                int_404a numeric,
                int_404b numeric,
                int_405a numeric,
                int_405b numeric,
                int_501b numeric,
                int_501c_1 numeric,
                int_501c_2 numeric,
                int_501c_3 numeric,
                int_501c_4 numeric,
                int_501c_5 numeric,
                int_502a numeric,
                int_502b numeric,
                int_503c numeric,
                int_504a numeric,
                int_504b numeric,
                int_601a_1 numeric,
                int_601a_2 numeric,
                int_601a_3 numeric,
                int_601b numeric,
                int_602a numeric,
                int_602c numeric,
                int_701 numeric,
                int_702a numeric,
                int_702b_1 numeric,
                int_702b_2 numeric,
                int_702b_3 numeric,
                int_702b_4 numeric,
                int_702b_5 numeric,
                int_703a numeric,
                int_703b numeric,
                int_705a numeric,
                int_705b numeric,
                int_706a numeric,
                int_706b numeric,
                ext_401 numeric,
                ext_402 numeric,
                ext_403 numeric,
                ext_404 numeric,
                ext_405a numeric,
                ext_405b numeric,
                ext_406 numeric,
                ext_407a numeric,
                ext_407b numeric,
                ext_407c numeric,
                ext_501 numeric,
                ext_502 numeric,
                ext_503 numeric,
                ext_504a numeric,
                ext_505a numeric,
                exp_401 numeric,
                exp_402 numeric,
                exp_403 numeric,
                exp_404 numeric,
                exp_405 numeric,
                exp_406 numeric,
                exp_407 numeric,
                exp_408 numeric,
                exp_409 numeric,
                exp_410 numeric,
                exp_601 numeric,
                exp_602 numeric
            );

            CREATE INDEX idx_spi_internal_eksternal_detail_tahun ON jaga.spi_internal_eksternal_detail (tahun);
            CREATE INDEX idx_spi_internal_eksternal_detail_id_instansi ON jaga.spi_internal_eksternal_detail (id_instansi);
            CREATE INDEX idx_spi_internal_eksternal_detail_id_provinsi ON jaga.spi_internal_eksternal_detail (id_provinsi);
            CREATE INDEX idx_spi_internal_eksternal_detail_id_kota_kabupaten ON jaga.spi_internal_eksternal_detail (id_kota_kabupaten);
        ");

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

        self::$connection->execute("
            UPDATE spi_internal_opd_detail SET id_instansi = 191, id_kota_kabupaten = '9408', kode_wilayah = '9408' WHERE kode_wilayah = '9436';
            UPDATE spi_external_opd_detail SET id_instansi = 191, id_kota_kabupaten = '9408', kode_wilayah = '9408' WHERE kode_wilayah = '9436';

            UPDATE spi_internal_opd_detail SET id_instansi = 195, id_kota_kabupaten = '9406', kode_wilayah = '9406' WHERE kode_wilayah = '9434';
            UPDATE spi_external_opd_detail SET id_instansi = 195, id_kota_kabupaten = '9406', kode_wilayah = '9406' WHERE kode_wilayah = '9434';

            UPDATE spi_internal_opd_detail SET id_instansi = 225, id_kota_kabupaten = '9407', kode_wilayah = '9407' WHERE kode_wilayah = '9435';
            UPDATE spi_external_opd_detail SET id_instansi = 225, id_kota_kabupaten = '9407', kode_wilayah = '9407' WHERE kode_wilayah = '9435';

            UPDATE spi_internal_opd_detail SET id_instansi = 296, id_provinsi = '95', id_kota_kabupaten = '9507', kode_wilayah = '9507' WHERE kode_wilayah = '9430';
            UPDATE spi_external_opd_detail SET id_instansi = 296, id_provinsi = '95', id_kota_kabupaten = '9507', kode_wilayah = '9507' WHERE kode_wilayah = '9430';

            UPDATE spi_internal_opd_detail SET id_instansi = 325, id_provinsi = '95', id_kota_kabupaten = '9505', kode_wilayah = '9505' WHERE kode_wilayah = '9431';
            UPDATE spi_external_opd_detail SET id_instansi = 325, id_provinsi = '95', id_kota_kabupaten = '9505', kode_wilayah = '9505' WHERE kode_wilayah = '9431';

            UPDATE spi_internal_opd_detail SET id_instansi = 364, id_provinsi = '95', id_kota_kabupaten = '9508', kode_wilayah = '9508' WHERE kode_wilayah = '9429';
            UPDATE spi_external_opd_detail SET id_instansi = 364, id_provinsi = '95', id_kota_kabupaten = '9508', kode_wilayah = '9508' WHERE kode_wilayah = '9429';

            UPDATE spi_internal_opd_detail SET id_instansi = 416, id_kota_kabupaten = '9405', kode_wilayah = '9405' WHERE kode_wilayah = '9433';
            UPDATE spi_external_opd_detail SET id_instansi = 416, id_kota_kabupaten = '9405', kode_wilayah = '9405' WHERE kode_wilayah = '9433';

            UPDATE spi_internal_opd_detail SET id_instansi = 516, id_provinsi = '95', id_kota_kabupaten = '9506', kode_wilayah = '9506' WHERE kode_wilayah = '9432';
            UPDATE spi_external_opd_detail SET id_instansi = 516, id_provinsi = '95', id_kota_kabupaten = '9506', kode_wilayah = '9506' WHERE kode_wilayah = '9432';

            UPDATE spi_internal_opd_detail SET id_instansi = 551, id_provinsi = '91', id_kota_kabupaten = '9171', kode_wilayah = '9171' WHERE kode_wilayah = '9471';
            UPDATE spi_external_opd_detail SET id_instansi = 551, id_provinsi = '91', id_kota_kabupaten = '9171', kode_wilayah = '9171' WHERE kode_wilayah = '9471';

            UPDATE spi_internal_opd_detail SET id_instansi = 638, id_provinsi = '92', kode_wilayah = '9200' WHERE kode_wilayah = '9100' AND id_instansi = 637;
            UPDATE spi_external_opd_detail SET id_instansi = 638, id_provinsi = '92', kode_wilayah = '9200' WHERE kode_wilayah = '9100' AND id_instansi = 637;

            UPDATE spi_internal_opd_detail SET id_instansi = 637, id_provinsi = '91', kode_wilayah = '9100' WHERE kode_wilayah = '9400' AND id_instansi = 20298;
            UPDATE spi_external_opd_detail SET id_instansi = 637, id_provinsi = '91', kode_wilayah = '9100' WHERE kode_wilayah = '9400' AND id_instansi = 20298;
        ");
    }

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

        self::$connection->execute("
            ALTER TABLE jaga.spi_int_ext_kode_mapping DROP COLUMN IF EXISTS is_required;
            DELETE FROM jaga.spi_int_ext_kode_mapping WHERE code_key = 'int_501b';
        ");

        self::$connection->execute("
            UPDATE spi_internal_opd_detail SET id_instansi = NULL, id_kota_kabupaten = '9436', kode_wilayah = '9436' WHERE kode_wilayah = '9408';
            UPDATE spi_external_opd_detail SET id_instansi = NULL, id_kota_kabupaten = '9436', kode_wilayah = '9436' WHERE kode_wilayah = '9408';

            UPDATE spi_internal_opd_detail SET id_instansi = NULL, id_kota_kabupaten = '9434', kode_wilayah = '9434' WHERE kode_wilayah = '9406';
            UPDATE spi_external_opd_detail SET id_instansi = NULL, id_kota_kabupaten = '9434', kode_wilayah = '9434' WHERE kode_wilayah = '9406';

            UPDATE spi_internal_opd_detail SET id_instansi = NULL, id_kota_kabupaten = '9435', kode_wilayah = '9435' WHERE kode_wilayah = '9407';
            UPDATE spi_external_opd_detail SET id_instansi = NULL, id_kota_kabupaten = '9435', kode_wilayah = '9435' WHERE kode_wilayah = '9407';

            UPDATE spi_internal_opd_detail SET id_instansi = NULL, id_provinsi = '94', id_kota_kabupaten = '9430', kode_wilayah = '9430' WHERE kode_wilayah = '9507';
            UPDATE spi_external_opd_detail SET id_instansi = NULL, id_provinsi = '94', id_kota_kabupaten = '9430', kode_wilayah = '9430' WHERE kode_wilayah = '9507';

            UPDATE spi_internal_opd_detail SET id_instansi = NULL, id_provinsi = '94', id_kota_kabupaten = '9431', kode_wilayah = '9431' WHERE kode_wilayah = '9505';
            UPDATE spi_external_opd_detail SET id_instansi = NULL, id_provinsi = '94', id_kota_kabupaten = '9431', kode_wilayah = '9431' WHERE kode_wilayah = '9505';

            UPDATE spi_internal_opd_detail SET id_instansi = NULL, id_provinsi = '94', id_kota_kabupaten = '9429', kode_wilayah = '9429' WHERE kode_wilayah = '9508';
            UPDATE spi_external_opd_detail SET id_instansi = NULL, id_provinsi = '94', id_kota_kabupaten = '9429', kode_wilayah = '9429' WHERE kode_wilayah = '9508';

            UPDATE spi_internal_opd_detail SET id_instansi = NULL, id_kota_kabupaten = '9433', kode_wilayah = '9433' WHERE kode_wilayah = '9405';
            UPDATE spi_external_opd_detail SET id_instansi = NULL, id_kota_kabupaten = '9433', kode_wilayah = '9433' WHERE kode_wilayah = '9405';

            UPDATE spi_internal_opd_detail SET id_instansi = NULL, id_provinsi = '94', id_kota_kabupaten = '9432', kode_wilayah = '9432' WHERE kode_wilayah = '9506';
            UPDATE spi_external_opd_detail SET id_instansi = NULL, id_provinsi = '94', id_kota_kabupaten = '9432', kode_wilayah = '9432' WHERE kode_wilayah = '9506';

            UPDATE spi_internal_opd_detail SET id_instansi = NULL, id_provinsi = '94', id_kota_kabupaten = '9471', kode_wilayah = '9471' WHERE kode_wilayah = '9171';
            UPDATE spi_external_opd_detail SET id_instansi = NULL, id_provinsi = '94', id_kota_kabupaten = '9471', kode_wilayah = '9471' WHERE kode_wilayah = '9171';

            UPDATE spi_internal_opd_detail SET id_instansi = 20298, id_provinsi = '94', kode_wilayah = '9400' WHERE kode_wilayah = '9100' AND id_instansi = 637;
            UPDATE spi_external_opd_detail SET id_instansi = 20298, id_provinsi = '94', kode_wilayah = '9400' WHERE kode_wilayah = '9100' AND id_instansi = 637;

            UPDATE spi_internal_opd_detail SET id_instansi = 637, id_provinsi = '91', kode_wilayah = '9100' WHERE kode_wilayah = '9200' AND id_instansi = 638;
            UPDATE spi_external_opd_detail SET id_instansi = 637, id_provinsi = '91', kode_wilayah = '9100' WHERE kode_wilayah = '9200' AND id_instansi = 638;
        ");
    }
}