<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.spi_internal_opd_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),
                opd VARCHAR(255),
                n_int numeric,
                    int_402a numeric,
                    int_401b numeric,
                    int_402_1 numeric,
                    int_402_11 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_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,
                    transparansi numeric,
                    sosialisasi_antikorupsi numeric,
                    trading_in_influence numeric,
                    pengelolaan_sdm numeric,
                    pengelolaan_pbj numeric,
                    pengelolaan_anggaran numeric,
                    integritas_tugas numeric,
                    indeks_internal numeric
            );

            CREATE INDEX idx_spi_internal_opd_detail_tahun ON jaga.spi_internal_opd_detail (tahun);
            CREATE INDEX idx_spi_internal_opd_detail_id_instansi ON jaga.spi_internal_opd_detail (id_instansi);
            CREATE INDEX idx_spi_internal_opd_detail_id_provinsi ON jaga.spi_internal_opd_detail (id_provinsi);
            CREATE INDEX idx_spi_internal_opd_detail_id_kota_kabupaten ON jaga.spi_internal_opd_detail (id_kota_kabupaten);
        ");

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

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.spi_external_opd_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),
                opd VARCHAR(255),
                n_ext numeric,
                ext_401 numeric,
                ext_402 numeric,
                ext_403 numeric,
                ext_404 numeric,
                ext_405a numeric,
                ext_406 numeric,
                ext_407a numeric,
                ext_501 numeric,
                ext_502 numeric,
                ext_503 numeric,
                ext_504a numeric,
                ext_505a numeric,
                upaya_pencegah_korupsi numeric,
                transparansi_keadilan_layanan numeric,
                integritas_pegawai numeric,
                indeks_eksternal numeric
            );

            CREATE INDEX idx_spi_external_opd_detail_tahun ON jaga.spi_external_opd_detail (tahun);
            CREATE INDEX idx_spi_external_opd_detail_id_instansi ON jaga.spi_external_opd_detail (id_instansi);
            CREATE INDEX idx_spi_external_opd_detail_id_provinsi ON jaga.spi_external_opd_detail (id_provinsi);
            CREATE INDEX idx_spi_external_opd_detail_id_kota_kabupaten ON jaga.spi_external_opd_detail (id_kota_kabupaten);
        ");

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

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.spi_expert_opd_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),
                opd VARCHAR(255),
                n_exp 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,
                integritas_instansi numeric, 
                indeks_eksper numeric
            );

            CREATE INDEX idx_spi_expert_opd_detail_tahun ON jaga.spi_expert_opd_detail (tahun);
            CREATE INDEX idx_spi_expert_opd_detail_id_instansi ON jaga.spi_expert_opd_detail (id_instansi);
            CREATE INDEX idx_spi_expert_opd_detail_id_provinsi ON jaga.spi_expert_opd_detail (id_provinsi);
            CREATE INDEX idx_spi_expert_opd_detail_id_kota_kabupaten ON jaga.spi_expert_opd_detail (id_kota_kabupaten);
        ");

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

        self::$connection->execute("
            CREATE TABLE jaga.spi_int_ext_kode_mapping (
              code_key varchar(255),
              variable varchar(255),
              type varchar(255),
              indikator_key varchar(255),
              indikator varchar(255),
              deskripsi varchar(255),
              tipe varchar(255)
            )
            ;

            CREATE INDEX idx_spi_int_ext_kode_mapping_code_key ON jaga.spi_int_ext_kode_mapping (code_key);
            CREATE INDEX idx_spi_int_ext_kode_mapping_type ON jaga.spi_int_ext_kode_mapping (type);
            CREATE INDEX idx_spi_int_ext_kode_mapping_indikator_key ON jaga.spi_int_ext_kode_mapping (indikator_key);
        ");

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

    public function down()
    {
        self::$connection->execute("
            DROP TABLE IF EXISTS jaga.spi_int_ext_kode_mapping;
        ");
        self::$connection->execute("
            DROP TABLE IF EXISTS jaga.spi_expert_opd_detail;
        ");
        self::$connection->execute("
            DROP TABLE IF EXISTS jaga.spi_external_opd_detail;
        ");
        self::$connection->execute("
            DROP TABLE IF EXISTS jaga.spi_internal_opd_detail;
        ");
    }
}