<?php

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

        //Create new Table for Header of Rekomendasi
        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.spi_mon_template
            (
                id serial primary key,
                tahun int NOT NULL, 
                nama character varying(255), 
                keterangan character varying(255),
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6) DEFAULT now()
            );
        ");

        //Alter Rekomendasi Item to add fields FK
        self::$connection->execute("
            ALTER TABLE jaga.spi_mon_rekomendasi_item ADD COLUMN IF NOT EXISTS id_mon_template int;
            ALTER TABLE jaga.spi_mon_rekomendasi_item ADD COLUMN IF NOT EXISTS kode character varying(5);
        ");

        //Alter Form to add template FK
        self::$connection->execute("
            ALTER TABLE jaga.spi_mon_form ADD COLUMN IF NOT EXISTS id_mon_template int;
        ");

        //Create Mapping Table
        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.spi_mon_mapping_instansi
            (
                id serial primary key,
                id_instansi int,
                nama_instansi character varying(255),
                kode character varying(10)
            );
        ");

        //Insert Mapping from all Prov/Kab/Kota
        self::$connection->execute("
            INSERT INTO jaga.spi_mon_mapping_instansi (id_instansi, nama_instansi, kode) 
            SELECT id AS id_instansi, nama AS nama_instansi, (CASE WHEN id_kota_kabupaten IS NULL THEN CONCAT(id_provinsi::TEXT, '00') ELSE id_kota_kabupaten::TEXT END) AS kode FROM instansi WHERE (id_tipe = 1 OR id_tipe = 2) AND deleted_at IS NULL;
        ");

        //Insert Mapping from all Kementerian
        $sql = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . 'mapping_instansi_kementerian.sql');
        self::$connection->execute($sql);

        //Create Template 2022
        self::$connection->execute("
            INSERT INTO jaga.spi_mon_template (tahun, nama, keterangan) VALUES (2022, 'TL SPI 2022', 'TL SPI 2022');
        ");

        //Bind all previous Rekomendasi Item to Template 2022
        self::$connection->execute("
            UPDATE jaga.spi_mon_rekomendasi_item SET id_mon_template = (SELECT id FROM spi_mon_template WHERE tahun = 2022) WHERE id_mon_template IS NULL; 
        ");

        //Bind all year 2022 previous form to Template 2022
        self::$connection->execute("
            UPDATE jaga.spi_mon_form SET id_mon_template = (SELECT id FROM spi_mon_template WHERE tahun = 2022) WHERE id_mon_template IS NULL;
        ");

        //Set kode to rekomendasi Item
        self::$connection->execute("
            UPDATE jaga.spi_mon_rekomendasi_item smri SET kode = (SELECT (CASE WHEN id_tipe = 1 THEN CONCAT(id_provinsi::TEXT, '00') WHEN id_tipe = 2 THEN id_kota_kabupaten::TEXT ELSE null END) FROM instansi WHERE id = smri.id_instansi);
            UPDATE jaga.spi_mon_rekomendasi_item smri SET kode = 0 WHERE id_instansi = 0;
        ");

        //Insert Instansi Baru lalu Map ke Mapping kode SPI
        $sql = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . 'insert_new_instansi_dirjen_then_map.sql');
        self::$connection->execute($sql);
    }

    public function down()
    {
        self::$connection->execute("
            DELETE FROM jaga.instansi WHERE nama IN ('Badan Kebijakan Fiskal Kementerian Keuangan','Inspektorat Jenderal Kementerian Keuangan','Lembaga National Single Window Kementerian Keuangan','Sekretariat Jenderal Kementerian Keuangan','Badan Pendidikan dan Pelatihan Keuangan Kementerian Keuangan','Direktorat Jenderal Anggaran Kementerian Keuangan','Direktorat Jenderal Kekayaan Negara Kementerian Keuangan','Direktorat Jenderal Pajak Kementerian Keuangan','Direktorat Jenderal Pengelolaan Pembiayaan dan Risiko Kementerian Keuangan','Direktorat Jenderal Perbendaharaan Kementerian Keuangan','Direktorat Jenderal Bea Cukai Kementerian Keuangan','Direktorat Jenderal Perimbangan Keuangan Kementerian Keuangan','Badan Pengembangan Sumber Daya Manusia Kementerian PUPR','Direktorat Jenderal Bina Konstruksi Kementerian PUPR','Direktorat Jenderal Bina Marga Kementerian PUPR','Direktorat Jenderal Cipta Karya Kementerian PUPR','Direktorat Jenderal Pembiayaan Infrastruktur Kementerian PUPR','Direktorat Jenderal Perumahan Kementerian PUPR','Direktorat Jenderal Sumber Daya Air Kementerian PUPR','Inspektorat Jenderal Kementerian PUPR');
            SELECT setval(pg_get_serial_sequence('instansi', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.instansi;
        ");

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

        self::$connection->execute("
            ALTER TABLE jaga.spi_mon_form DROP COLUMN IF EXISTS id_mon_template;
        ");

        self::$connection->execute("
            ALTER TABLE jaga.spi_mon_rekomendasi_item DROP COLUMN IF EXISTS kode_kemdagri;
            ALTER TABLE jaga.spi_mon_rekomendasi_item DROP COLUMN IF EXISTS id_mon_template;
        ");

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