<?php

use Phalcon\Mvc\Model\Migration;

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


        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.spi_internal_eksternal_opd_header (
                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),
                opd VARCHAR(255),
                type VARCHAR(255),
                indeks numeric
            );

            CREATE INDEX idx_spi_internal_eksternal_opd_header_tahun ON jaga.spi_internal_eksternal_opd_header (tahun);
            CREATE INDEX idx_spi_internal_eksternal_opd_header_id_instansi ON jaga.spi_internal_eksternal_opd_header (id_instansi);
            CREATE INDEX idx_spi_internal_eksternal_opd_header_id_provinsi ON jaga.spi_internal_eksternal_opd_header (id_provinsi);
            CREATE INDEX idx_spi_internal_eksternal_opd_header_id_kota_kabupaten ON jaga.spi_internal_eksternal_opd_header (id_kota_kabupaten);
            CREATE INDEX idx_spi_internal_eksternal_opd_header_type ON jaga.spi_internal_eksternal_opd_header (type);
        ");

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

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

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