<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute("
            ALTER TABLE jaga.instansi
                ADD COLUMN IF NOT EXISTS kelompok_anggaran varchar(2),
                ADD COLUMN IF NOT EXISTS nama_kelompok_anggaran varchar(255);
        ");

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

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.spi_internal_eksternal_header (
                id SERIAL PRIMARY KEY,
                tahun VARCHAR(4),
                id_instansi integer,
                id_provinsi integer,
                id_kota_kabupaten integer,
                kode_wilayah VARCHAR(255),
                nama_instansi VARCHAR(255),
                indeks_spi NUMERIC,
                indeks_int NUMERIC,
                indeks_ext NUMERIC,
                indeks_exp NUMERIC,
                koreksi_prevalensi_korupsi NUMERIC,
                koreksi_integritas_pelaksanaan_survei NUMERIC
            );

            CREATE INDEX idx_spi_internal_eksternal_header_tahun ON jaga.spi_internal_eksternal_header (tahun);
            CREATE INDEX idx_spi_internal_eksternal_header_id_instansi ON jaga.spi_internal_eksternal_header (id_instansi);
            CREATE INDEX idx_spi_internal_eksternal_header_id_provinsi ON jaga.spi_internal_eksternal_header (id_provinsi);
            CREATE INDEX idx_spi_internal_eksternal_header_id_kota_kabupaten ON jaga.spi_internal_eksternal_header (id_kota_kabupaten);
        ");

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

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

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

        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,
                kode_wilayah VARCHAR(255),
                nama_instansi VARCHAR(255),
                type VARCHAR(20), --INTERNAL/EKSTERNAL
                kode VARCHAR(255),
                nama VARCHAR(255),
                grup_pertanyaan VARCHAR(255),
                grup_dimensi VARCHAR(255),
                freq_val numeric,
                n_val numeric,
                col_n_val 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);
            CREATE INDEX idx_spi_internal_eksternal_detail_kode ON jaga.spi_internal_eksternal_detail (kode);
        ");

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

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.spi_bidang_expert (
                id SERIAL PRIMARY KEY,
                tahun VARCHAR(4),
                id_instansi integer,
                id_provinsi integer,
                id_kota_kabupaten integer,
                kode_wilayah VARCHAR(255),
                nama_instansi VARCHAR(255),
                nama_bidang VARCHAR(255)
            );

            CREATE INDEX idx_spi_bidang_expert_tahun ON jaga.spi_internal_eksternal_detail (tahun);
            CREATE INDEX idx_spi_bidang_expert_id_instansi ON jaga.spi_internal_eksternal_detail (id_instansi);
            CREATE INDEX idx_spi_bidang_expert_id_provinsi ON jaga.spi_internal_eksternal_detail (id_provinsi);
            CREATE INDEX idx_spi_bidang_expert_id_kota_kabupaten ON jaga.spi_internal_eksternal_detail (id_kota_kabupaten);
        ");

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

        self::$connection->execute("
            UPDATE jaga.spi_bidang_expert 
            SET id_instansi = jaga.spi_internal_eksternal_header.id_instansi 
            FROM
            	jaga.spi_internal_eksternal_header 
            WHERE
            	jaga.spi_bidang_expert.kode_wilayah NOT LIKE '____'
            	AND jaga.spi_bidang_expert.kode_wilayah = jaga.spi_internal_eksternal_header.kode_wilayah
            	AND jaga.spi_internal_eksternal_header.tahun = '2023';
        ");

        self::$connection->execute("
            UPDATE jaga.spi_bidang_expert 
            SET id_instansi = jaga.spi_mon_mapping_instansi.id_instansi 
            FROM
            	jaga.spi_mon_mapping_instansi 
            WHERE
            	jaga.spi_bidang_expert.kode_wilayah IN ('A04','A20','A25','A46','B02','B15','B18','B21')
            	AND jaga.spi_bidang_expert.kode_wilayah = jaga.spi_mon_mapping_instansi.kode;
        ");

        self::$connection->execute("
            UPDATE jaga.spi_bidang_expert 
            SET id_instansi = 20049 
            WHERE
            	kode_wilayah = 'C08';
        ");

        self::$connection->execute("
            UPDATE jaga.spi_bidang_expert 
            SET id_instansi = 20296 
            WHERE
            	kode_wilayah = '20296';
        ");

        self::$connection->execute("
            ALTER TABLE jaga.spi_internal_eksternal_opd
                ADD COLUMN IF NOT EXISTS grup_dimensi varchar(255);
        ");

        self::$connection->execute("
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Integritas Pegawai' WHERE kode = 'EXT.503';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Integritas Pegawai' WHERE kode = 'EXT.504A';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Perdagangan Pengaruh' WHERE kode = 'INT.402';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Perdagangan Pengaruh' WHERE kode = 'INT.402.1';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Perdagangan Pengaruh' WHERE kode = 'INT.402.2';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Perdagangan Pengaruh' WHERE kode = 'INT.402.3';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Perdagangan Pengaruh' WHERE kode = 'INT.402.4';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Perdagangan Pengaruh' WHERE kode = 'INT.402.5';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Perdagangan Pengaruh' WHERE kode = 'INT.402.6';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Integritas Pelaksanaan Tugas' WHERE kode = 'INT.403A';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Integritas Pelaksanaan Tugas' WHERE kode = 'INT.405A';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Integritas Pelaksanaan Tugas' WHERE kode = 'INT.405B';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan SDM' WHERE kode = 'INT.601A';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan SDM' WHERE kode = 'INT.601A.a';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan SDM' WHERE kode = 'INT.601A.b';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan SDM' WHERE kode = 'INT.601A.c';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan SDM' WHERE kode = 'INT.601B';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan SDM' WHERE kode = 'INT.602A';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan Anggaran' WHERE kode = 'INT.702.A';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan PBJ' WHERE kode = 'INT.702B';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan PBJ' WHERE kode = 'INT.702B.a';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan PBJ' WHERE kode = 'INT.702B.b';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan PBJ' WHERE kode = 'INT.702B.c';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan PBJ' WHERE kode = 'INT.702B.d';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan PBJ' WHERE kode = 'INT.702B.e';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan Anggaran' WHERE kode = 'INT.703A';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan Anggaran' WHERE kode = 'INT.703B';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan Anggaran' WHERE kode = 'INT.705A';
            UPDATE spi_internal_eksternal_opd SET grup_dimensi = 'Pengelolaan Anggaran' WHERE kode = 'INT.705B';
        ");
    }

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

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

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

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

        self::$connection->execute("
            ALTER TABLE jaga.instansi
                DROP COLUMN IF EXISTS kelompok_anggaran,
                DROP COLUMN IF EXISTS nama_kelompok_anggaran;
        ");
    }
}