<?php

class UnitKerjaKLMigration_488 extends \Phalcon\Mvc\Model\Migration {
    public function up()
    {
        self::$connection->execute("
            ALTER TABLE jaga.spi_internal_opd_detail
            RENAME COLUMN int_402a TO int_401a;
            
            ALTER TABLE jaga.spi_internal_opd_detail
            RENAME COLUMN int_402_11 TO int_402_2;
        ");

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.spi_int_ext_unit_kerja_kl (
                id SERIAL PRIMARY KEY,
                tahun VARCHAR(4),
                id_instansi integer,
                kode_wilayah VARCHAR(255),
                nama_instansi VARCHAR(255),
                id_unit_kerja integer,
                nama_unit_kerja VARCHAR(255),
                n_int numeric,
                indeks 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_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,
                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_int_ext_unit_kerja_kl_tahun ON jaga.spi_int_ext_unit_kerja_kl (tahun);
            CREATE INDEX idx_spi_int_ext_unit_kerja_kl_id_instansi ON jaga.spi_int_ext_unit_kerja_kl (id_instansi);
            CREATE INDEX idx_spi_int_ext_unit_kerja_kl_id_unit_kerja ON jaga.spi_int_ext_unit_kerja_kl (id_unit_kerja);
        ");

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

        self::$connection->execute("
            ALTER TABLE jaga.spi_internal_opd_detail
                ADD COLUMN IF NOT EXISTS id_unit_kerja int,
                ADD COLUMN IF NOT EXISTS nama_unit_kerja varchar(255);
        ");

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

        self::$connection->execute("
            ALTER TABLE jaga.spi_external_opd_detail
                ADD COLUMN IF NOT EXISTS id_unit_kerja int,
                ADD COLUMN IF NOT EXISTS nama_unit_kerja varchar(255);
        ");

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


    }

    public function down()
    {
        self::$connection->execute("
            DELETE FROM jaga.spi_external_opd_detail WHERE id_unit_kerja IS NOT NULL;
            SELECT setval(pg_get_serial_sequence('spi_external_opd_detail', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.spi_external_opd_detail;
        ");

        self::$connection->execute("
            ALTER TABLE jaga.spi_external_opd_detail
                DROP COLUMN IF EXISTS id_unit_kerja,
                DROP COLUMN IF EXISTS nama_unit_kerja;
        ");

        self::$connection->execute("
            DELETE FROM jaga.spi_internal_opd_detail WHERE id_unit_kerja IS NOT NULL;
            SELECT setval(pg_get_serial_sequence('spi_internal_opd_detail', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.spi_internal_opd_detail;
        ");

        self::$connection->execute("
            ALTER TABLE jaga.spi_internal_opd_detail
                DROP COLUMN IF EXISTS id_unit_kerja,
                DROP COLUMN IF EXISTS nama_unit_kerja;
        ");

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

        self::$connection->execute("
            ALTER TABLE jaga.spi_internal_opd_detail
            RENAME COLUMN int_401a TO int_402a;
            
            ALTER TABLE jaga.spi_internal_opd_detail
            RENAME COLUMN int_402_2 TO int_402_11;
        ");
    }
}