<?php

use Phalcon\Mvc\Model\Migration;

/**
 * Class PerguruanTinggi_326
 *
 */
class PerguruanTinggiMigration_326 extends Migration
{
    public function up()
    {
        $this->down();

        //NOTE: Reset max id
        self::$connection->execute("
            SELECT setval('jaga.pt_info_anggaran_id_seq',
                (SELECT MAX(id) FROM jaga.pt_info_anggaran)
            );
        ");

        //NOTE: Add row pada table `pt_info_anggaran`
        self::$connection->execute("
            INSERT INTO pt_info_anggaran ( nama, tahun, kode ) SELECT 'Total', 2017, NULL WHERE NOT EXISTS (SELECT * FROM pt_info_anggaran WHERE nama ilike 'Total' AND tahun = 2017);
            INSERT INTO pt_info_anggaran ( nama, tahun, kode ) SELECT 'Total', 2019, NULL WHERE NOT EXISTS (SELECT * FROM pt_info_anggaran WHERE nama ilike 'Total' AND tahun = 2019);
            INSERT INTO pt_info_anggaran ( nama, tahun, kode ) SELECT 'Total', 2020, NULL WHERE NOT EXISTS (SELECT * FROM pt_info_anggaran WHERE nama ilike 'Total' AND tahun = 2020);
            INSERT INTO pt_info_anggaran ( nama, tahun, kode ) SELECT 'Total', 2021, NULL WHERE NOT EXISTS (SELECT * FROM pt_info_anggaran WHERE nama ilike 'Total' AND tahun = 2021);
        ");

        //NOTE: Add field pada table `pt_output_anggaran`
        self::$connection->execute("
             ALTER TABLE jaga.pt_output_anggaran 
                ADD COLUMN anggaran_bpptnbh character varying(255);
        ");

        //RUN FROM SQL FILE
        $sql1 = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . '1_pt_output_anggaran_insert_boptn.sql');
        self::$connection->execute($sql1);

        //RUN FROM SQL FILE
        $sql2 = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . '2_pt_output_anggaran_update_boptn.sql');
        self::$connection->execute($sql2);

        //RUN FROM SQL FILE
        $sql3 = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . '3_pt_output_anggaran_insert_boptn_2020.sql');
        self::$connection->execute($sql3);

        //RUN FROM SQL FILE
        $sql4 = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . '4_pt_output_anggaran_update_2020_boptn.sql');
        self::$connection->execute($sql4);

        //RUN FROM SQL FILE
        $sql5 = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . '5_pt_output_anggaran_insert_boptn_detail_2020.sql');
        self::$connection->execute($sql5);

        //RUN FROM SQL FILE
        $sql6 = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . '6_pt_output_anggaran_update_boptn_detail_2020.sql');
        self::$connection->execute($sql6);

        //RUN FROM SQL FILE
        $sql7 = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . '7_pt_output_anggaran_insert_bpptnbh.sql');
        self::$connection->execute($sql7);

        //RUN FROM SQL FILE
        $sql8 = file_get_contents(__DIR__ . DIRECTORY_SEPARATOR . '8_pt_output_anggaran_update_bppthnbh.sql');
        self::$connection->execute($sql8);
    }

    public function down()
    {
        //NOTE: Add field pada table `pt_output_anggaran`
        self::$connection->execute("
        ALTER TABLE jaga.pt_output_anggaran
                DROP COLUMN IF EXISTS anggaran_bpptnbh;
        ");
    }
}