<?php

use Phalcon\Mvc\Model\Migration;

class InsertMasterProvinsiMigration_446 extends Migration {
    public function up()
    {
        self::$connection->execute("
            INSERT INTO jaga.master_provinsi VALUES ('93', 'Papua Selatan', now(), now());
            INSERT INTO jaga.master_provinsi VALUES ('94', 'Papua Tengah', now(), now());
            INSERT INTO jaga.master_provinsi VALUES ('95', 'Papua Pegunungan', now(), now());
            INSERT INTO jaga.master_provinsi VALUES ('96', 'Papua Barat Daya', now(), now());

            CREATE TABLE IF NOT EXISTS jaga.rel_instansi_wilayah
            (
                id serial PRIMARY KEY,
                id_instansi int NOT NULL,
                id_provinsi int NOT NULL,
                id_kota_kabupaten int,
                start_year int NOT NULL,
                end_year int NOT NULL,
                created_at timestamp without time zone NOT NULL DEFAULT now(),
                updated_at timestamp without time zone,
                deleted_at timestamp without time zone
            );

            INSERT INTO jaga.rel_instansi_wilayah (id_instansi, id_provinsi, id_kota_kabupaten, start_year, end_year)
            SELECT id, id_provinsi, id_kota_kabupaten, 1900 as start_year, 2022 as end_year FROM jaga.instansi WHERE id_tipe IN (1,2) AND id_provinsi IS NOT NULL ORDER BY id;

            ALTER TABLE jaga.master_kota_kabupaten ADD COLUMN IF NOT EXISTS deleted_at timestamp without time zone;

            ALTER TABLE jaga.master_wilayah ADD COLUMN IF NOT EXISTS code_old character varying (4);
            UPDATE jaga.master_wilayah SET code_old = code;
            comment on column jaga.master_wilayah.code_old is 'Kode wilayah versi jaga (est. 2019)';
            comment on column jaga.master_wilayah.code is 'Kode wilayah versi kemendagri (est. 2023)';

            ALTER TABLE jaga.korsupgah_template ADD COLUMN IF NOT EXISTS tahun int;
        ");

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

    public function down()
    {
        $sql = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . 'rollback_instansi.sql');
        self::$connection->execute($sql);

        self::$connection->execute("
            DELETE FROM jaga.master_provinsi WHERE provinsi_code = '93';
            DELETE FROM jaga.master_provinsi WHERE provinsi_code = '94';
            DELETE FROM jaga.master_provinsi WHERE provinsi_code = '95';
            DELETE FROM jaga.master_provinsi WHERE provinsi_code = '96';

            DROP TABLE IF EXISTS jaga.rel_instansi_wilayah;

            ALTER TABLE jaga.master_kota_kabupaten DROP COLUMN IF EXISTS deleted_at;

            UPDATE jaga.master_wilayah SET code = code_old;
            ALTER TABLE jaga.master_wilayah DROP COLUMN IF EXISTS code_old;
            comment on column jaga.master_wilayah.code is NULL;

            ALTER TABLE jaga.korsupgah_template DROP COLUMN IF EXISTS tahun;
        ");
    }
}