<?php

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

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.spi_internal_eksternal_opd (
                id SERIAL PRIMARY KEY,
                tahun VARCHAR(4),
                id_instansi integer,
                id_provinsi integer,
                id_kota_kabupaten integer,
                kode_wilayah integer,
                nama_instansi VARCHAR(255),
                opd VARCHAR(255),
                type VARCHAR(20), --INTERNAL/EKSTERNAL
                kode VARCHAR(255),
                nama VARCHAR(255),
                freq_val numeric,
                n_val numeric,
                col_n_val numeric
            );

            CREATE INDEX idx_spi_internal_eksternal_opd_tahun ON jaga.spi_internal_eksternal_opd (tahun);
            CREATE INDEX idx_spi_internal_eksternal_opd_id_instansi ON jaga.spi_internal_eksternal_opd (id_instansi);
            CREATE INDEX idx_spi_internal_eksternal_opd_id_provinsi ON jaga.spi_internal_eksternal_opd (id_provinsi);
            CREATE INDEX idx_spi_internal_eksternal_opd_id_kota_kabupaten ON jaga.spi_internal_eksternal_opd (id_kota_kabupaten);
            CREATE INDEX idx_spi_internal_eksternal_opd_kode_wilayah ON jaga.spi_internal_eksternal_opd (kode_wilayah);
            CREATE INDEX idx_spi_internal_eksternal_opd_kode ON jaga.spi_internal_eksternal_opd (kode);
        ");

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

//        self::$connection->execute("
//            WITH max_instansi_id AS (
//                SELECT
//                    id_provinsi,
//                    COALESCE(id_kota_kabupaten, -1) AS id_kota_kabupaten, -- Using -1 as a placeholder for NULL
//                    MAX(id) AS max_instansi_id
//                FROM
//                    jaga.instansi
//                WHERE
//                    deleted_at IS NULL
//                    AND id_tipe IN (1, 2)
//                GROUP BY
//                    id_provinsi, COALESCE(id_kota_kabupaten, -1)
//            )
//
//            UPDATE jaga.spi_internal_eksternal_opd opd
//            SET id_instansi = max_instansi_id.max_instansi_id
//            FROM max_instansi_id
//            WHERE
//                opd.id_provinsi = max_instansi_id.id_provinsi
//                AND (opd.id_kota_kabupaten IS NULL AND max_instansi_id.id_kota_kabupaten = -1 OR opd.id_kota_kabupaten = max_instansi_id.id_kota_kabupaten);
//        ");
    }

    public function down()
    {
        self::$connection->execute("
            DROP TABLE IF EXISTS jaga.spi_internal_eksternal_opd;
        ");
    }
}