<?php

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

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

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

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

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

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

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

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

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

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

        self::$connection->execute("UPDATE jaga.instansi SET kelompok_anggaran = 'A', nama_kelompok_anggaran = 'Anggaran Besar, Jumlah Pegawai Besar' WHERE id = 20029;");
    }

    public function down()
    {
        self::$connection->execute("
            UPDATE jaga.spi_bidang_expert SET id_instansi = NULL WHERE kode_wilayah LIKE '___' AND id_provinsi IS NULL AND id_kota_kabupaten IS NULL; 
            UPDATE jaga.spi_internal_eksternal_detail SET id_instansi = NULL WHERE kode_wilayah LIKE '___' AND id_provinsi IS NULL AND id_kota_kabupaten IS NULL; 
            UPDATE jaga.spi_internal_opd_detail SET id_instansi = NULL WHERE kode_wilayah LIKE '___' AND id_provinsi IS NULL AND id_kota_kabupaten IS NULL; 
            UPDATE jaga.spi_external_opd_detail SET id_instansi = NULL WHERE kode_wilayah LIKE '___' AND id_provinsi IS NULL AND id_kota_kabupaten IS NULL; 
            UPDATE jaga.spi_expert_opd_detail SET id_instansi = NULL WHERE kode_wilayah LIKE '___' AND id_provinsi IS NULL AND id_kota_kabupaten IS NULL; 
            DELETE FROM jaga.spi_internal_eksternal_header WHERE kode_wilayah LIKE '___' AND id_provinsi IS NULL AND id_kota_kabupaten IS NULL AND tahun LIKE '2022';
            DELETE FROM jaga.spi_internal_eksternal_header WHERE kode_wilayah LIKE '___' AND id_provinsi IS NULL AND id_kota_kabupaten IS NULL AND tahun LIKE '2021';
            SELECT setval(pg_get_serial_sequence('spi_internal_eksternal_header', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.spi_internal_eksternal_header;
        ");
    }
}