<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.spi_internal_eksternal (
                id SERIAL PRIMARY KEY,
                tahun VARCHAR(4),
                type VARCHAR(20), --INSTANSI/KEMENTERIAN
                id_instansi integer,
                id_provinsi integer,
                id_kota_kabupaten integer,
                nama_instansi VARCHAR(255),
                kelompok_instansi VARCHAR(255),
                kementerian_koordinator VARCHAR(255),
                int_integritas numeric,
                int_anggaran numeric,
                int_pbj numeric,
                int_sdm numeric,
                int_trading numeric,
                int_antikorupsi numeric,
                int_transparansi numeric,
                ext_transparansi numeric,
                ext_pencegahan numeric,
                ext_pegawai numeric,
                esp_integritas numeric,
                esp_transparansi numeric
            );
        ");

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

        self::$connection->execute("
            ALTER TABLE IF EXISTS jaga.spi_hasil_klpd
                ADD COLUMN IF NOT EXISTS kementerian_koordinator CHARACTER VARYING (255),
                ADD COLUMN IF NOT EXISTS is_koordinator smallint
                ;
        ");

        self::$connection->execute("
            UPDATE spi_hasil_klpd klpd SET kementerian_koordinator = (SELECT kementerian_koordinator FROM spi_internal_eksternal sie WHERE sie.id_instansi = klpd.code_klpd AND kementerian_koordinator <> '');
        ");

        self::$connection->execute("
            UPDATE spi_hasil_klpd klpd SET is_koordinator = (CASE WHEN nama_klpd = kementerian_koordinator THEN 1 ELSE 0 END);
        ");
    }

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

        self::$connection->execute("
            ALTER TABLE IF EXISTS jaga.spi_hasil_klpd
                DROP COLUMN IF EXISTS kementerian_koordinator,
                DROP COLUMN IF EXISTS is_koordinator
                ;
        ");
    }
}