<?php

use Phalcon\Mvc\Model\Migration;

class PerubahanSPIBidangExpertMigration_482 extends Migration {
    public function up()
    {
        $this->down();

        self::$connection->execute("
            DROP INDEX IF EXISTS idx_spi_bidang_expert_tahun;
            DROP INDEX IF EXISTS idx_spi_bidang_expert_id_instansi;
            DROP INDEX IF EXISTS idx_spi_bidang_expert_id_provinsi;
            DROP INDEX IF EXISTS idx_spi_bidang_expert_id_kota_kabupaten;
        ");

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

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.spi_bidang_expert (
                id SERIAL PRIMARY KEY,
                tahun VARCHAR(4),
                id_instansi integer,
                id_provinsi integer,
                id_kota_kabupaten integer,
                kode_wilayah VARCHAR(255),
                nama_instansi VARCHAR(255),
                nama_bidang VARCHAR(255),
                indeks_eksper numeric
            );

            CREATE INDEX idx_spi_bidang_expert_tahun ON jaga.spi_bidang_expert (tahun);
            CREATE INDEX idx_spi_bidang_expert_id_instansi ON jaga.spi_bidang_expert (id_instansi);
            CREATE INDEX idx_spi_bidang_expert_id_provinsi ON jaga.spi_bidang_expert (id_provinsi);
            CREATE INDEX idx_spi_bidang_expert_id_kota_kabupaten ON jaga.spi_bidang_expert (id_kota_kabupaten);
        ");

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

    public function down()
    {
    }
}