<?php

use Phalcon\Mvc\Model\Migration;

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

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

        self::$connection->execute("
            ALTER TABLE jaga.spi_internal_eksternal ADD COLUMN IF NOT EXISTS integritas_tugas numeric; 
            ALTER TABLE jaga.spi_internal_eksternal ADD COLUMN IF NOT EXISTS pengelolaan_anggaran numeric; 
            ALTER TABLE jaga.spi_internal_eksternal ADD COLUMN IF NOT EXISTS pengelolaan_pbj numeric; 
            ALTER TABLE jaga.spi_internal_eksternal ADD COLUMN IF NOT EXISTS pengelolaan_sdm numeric; 
            ALTER TABLE jaga.spi_internal_eksternal ADD COLUMN IF NOT EXISTS sosialisasi_antikorupsi numeric; 
            ALTER TABLE jaga.spi_internal_eksternal ADD COLUMN IF NOT EXISTS trading_in_influence numeric; 
            ALTER TABLE jaga.spi_internal_eksternal ADD COLUMN IF NOT EXISTS transparansi numeric; 
            ALTER TABLE jaga.spi_internal_eksternal ADD COLUMN IF NOT EXISTS integritas_pegawai numeric; 
            ALTER TABLE jaga.spi_internal_eksternal ADD COLUMN IF NOT EXISTS transparansi_keadilan_layanan numeric; 
            ALTER TABLE jaga.spi_internal_eksternal ADD COLUMN IF NOT EXISTS upaya_pencegah_korupsi numeric; 
            ALTER TABLE jaga.spi_internal_eksternal ADD COLUMN IF NOT EXISTS integritas_instansi numeric;
        ");

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

    public function down()
    {
        self::$connection->execute("
            DELETE FROM spi_internal_eksternal_opd_header WHERE tahun = '2023' AND kode_wilayah LIKE '___' AND id_provinsi IS NULL AND id_kota_kabupaten IS NULL;
            SELECT setval('jaga.spi_internal_eksternal_opd_header_id_seq', COALESCE((SELECT MAX(id)+1 FROM jaga.spi_internal_eksternal_opd_header), 1), false);
        ");

        self::$connection->execute("
            ALTER TABLE jaga.spi_internal_eksternal DROP COLUMN IF EXISTS integritas_tugas; 
            ALTER TABLE jaga.spi_internal_eksternal DROP COLUMN IF EXISTS pengelolaan_anggaran; 
            ALTER TABLE jaga.spi_internal_eksternal DROP COLUMN IF EXISTS pengelolaan_pbj; 
            ALTER TABLE jaga.spi_internal_eksternal DROP COLUMN IF EXISTS pengelolaan_sdm; 
            ALTER TABLE jaga.spi_internal_eksternal DROP COLUMN IF EXISTS sosialisasi_antikorupsi; 
            ALTER TABLE jaga.spi_internal_eksternal DROP COLUMN IF EXISTS trading_in_influence; 
            ALTER TABLE jaga.spi_internal_eksternal DROP COLUMN IF EXISTS transparansi; 
            ALTER TABLE jaga.spi_internal_eksternal DROP COLUMN IF EXISTS integritas_pegawai; 
            ALTER TABLE jaga.spi_internal_eksternal DROP COLUMN IF EXISTS transparansi_keadilan_layanan; 
            ALTER TABLE jaga.spi_internal_eksternal DROP COLUMN IF EXISTS upaya_pencegah_korupsi; 
            ALTER TABLE jaga.spi_internal_eksternal DROP COLUMN IF EXISTS integritas_instansi;
        ");
    }
}