<?php

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

        self::$connection->execute("
            INSERT INTO jaga.instansi (nama, show_to_public, is_stranas, id_tipe, flag_dana_desa, flag_pak) VALUES('Sekretariat Jenderal Kementerian PUPR', 0, 0, (SELECT id FROM jaga.instansi_tipe WHERE nama = 'Unit Kerja Kementerian'), 0, false);
            INSERT INTO jaga.instansi (nama, show_to_public, is_stranas, id_tipe, flag_dana_desa, flag_pak) VALUES('Badan Pengembangan Infrastruktur Wilayah Kementerian PUPR', 0, 0, (SELECT id FROM jaga.instansi_tipe WHERE nama = 'Unit Kerja Kementerian'), 0, false);
        ");

        self::$connection->execute("
            UPDATE jaga.instansi ins1 
            SET parent_id = ins2.id
            FROM jaga.instansi ins2
            WHERE ins2.nama ILIKE 'Kementerian Pekerjaan Umum dan Perumahan Rakyat'
                AND ins1.nama ILIKE '% Kementerian PUPR'
                AND ins1.id_tipe = (SELECT id FROM jaga.instansi_tipe WHERE nama = 'Unit Kerja Kementerian')
                AND ins2.deleted_at IS NULL
            ;
        ");

        self::$connection->execute("
            INSERT INTO jaga.spi_mon_mapping_instansi (id_instansi, nama_instansi, kode) VALUES((SELECT id FROM jaga.instansi WHERE nama = 'Sekretariat Jenderal Kementerian PUPR'), (SELECT nama FROM jaga.instansi WHERE nama = 'Sekretariat Jenderal Kementerian PUPR'), 'A11_11');
            INSERT INTO jaga.spi_mon_mapping_instansi (id_instansi, nama_instansi, kode) VALUES((SELECT id FROM jaga.instansi WHERE nama = 'Badan Pengembangan Infrastruktur Wilayah Kementerian PUPR'), (SELECT nama FROM jaga.instansi WHERE nama = 'Badan Pengembangan Infrastruktur Wilayah Kementerian PUPR'), 'A11_12');
        ");
    }

    public function down()
    {
        self::$connection->execute("
            DELETE FROM jaga.spi_mon_mapping_instansi WHERE nama_instansi IN ('Sekretariat Jenderal Kementerian PUPR','Badan Pengembangan Infrastruktur Wilayah Kementerian PUPR');
            SELECT setval(pg_get_serial_sequence('spi_mon_mapping_instansi', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.spi_mon_mapping_instansi;
        ");

        self::$connection->execute("
            DELETE FROM jaga.instansi WHERE nama IN ('Sekretariat Jenderal Kementerian PUPR','Badan Pengembangan Infrastruktur Wilayah Kementerian PUPR');
            SELECT setval(pg_get_serial_sequence('instansi', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.instansi;
        ");
    }
}