<?php

use Phalcon\Mvc\Model\Migration;
class KemenkeuMigration_412 extends Migration
{
    public function up()
    {
        $this->down();

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.kemenkeu_master_pemda
            (
                kodepemda character varying(10) NOT NULL,
                kodesatker character varying(20) NOT NULL,
                namapemda character varying(255) NOT NULL,
                kodeprovinsi character varying(2),
                kodekabkota character varying(4)
            );
        ");

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.kemenkeu_tkdd
            (
                id BIGSERIAL PRIMARY KEY,
                kodepemda character varying(10),
                tahun smallint NOT NULL,
                namaprovinsi character varying(255) NOT NULL,
                namapemda character varying(255) NOT NULL,
                urmap1 character varying(255),
                urakun character varying(255),
                pagu numeric,
                realisasi numeric,
                prosen numeric
            );
        ");

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.kemenkeu_realisasi_apbd
            (
                id BIGSERIAL PRIMARY KEY,
                tahun smallint NOT NULL,
                kodesatker character varying(20) NOT NULL,
                kodepemda character varying(10) NOT NULL,
                namapemda character varying(255) NOT NULL,
                namaprovinsi character varying(255) NOT NULL,
                level1coa90 character varying(255),
                level2coa90 character varying(255),
                level3coa90 character varying(255),
                nilaianggaran numeric,
                nilairealisasi numeric,
                persentase numeric,
                updatedate timestamp(6)
            );
        ");

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.log_kemenkeu_errors
            (
                id BIGSERIAL PRIMARY KEY,
                type character varying(4) NOT NULL,
                range character varying(20) NOT NULL,
                created_at timestamp(6) DEFAULT now()
            );
        ");

        self::$connection->execute("
            INSERT INTO jaga.app_config (kode, nilai, keterangan) VALUES ('KEMENKEU_CLIENT_ID', 'dwY7E1keIdmp2jsNkYooRAowXyvdeayJ', 'Client ID yg dipakai untuk call API ke Kemenkeu');
            INSERT INTO jaga.app_config (kode, nilai, keterangan) VALUES ('KEMENKEU_CLIENT_SECRET', 'P9HxnGYnp925nstHqe6Fo2fRVhIK5jbf', 'Client Secret yg dipakai untuk call API ke Kemenkeu');
            INSERT INTO jaga.app_config (kode, nilai, keterangan) VALUES ('KEMENKEU_PROVISION_KEY', '9PLzaSvYQ0BWvDNXkvy8mPAd09kDEgnH', 'Provision Key yg dipakai untuk call API ke Kemenkeu');
            INSERT INTO jaga.app_config (kode, nilai, keterangan) VALUES ('KEMENKEU_GRANT_TYPE', 'client_credentials', 'Grant Type yg dipakai untuk call API ke Kemenkeu');
            INSERT INTO jaga.app_config (kode, nilai, keterangan) VALUES ('KEMENKEU_SCOPE', 'username', 'Scope yg dipakai untuk call API ke Kemenkeu');
        ");

        self::$connection->execute("
            CREATE INDEX IF NOT EXISTS idx_kemenkeu_master_pemda_kodepemda ON jaga.kemenkeu_master_pemda USING btree (kodepemda);
            
            CREATE INDEX IF NOT EXISTS idx_kemenkeu_tkdd_kodepemda ON jaga.kemenkeu_tkdd USING btree (kodepemda);
            CREATE INDEX IF NOT EXISTS idx_kemenkeu_tkdd_tahun ON jaga.kemenkeu_tkdd USING btree (tahun);
            CREATE INDEX IF NOT EXISTS idx_kemenkeu_tkdd_urmap1 ON jaga.kemenkeu_tkdd USING btree (urmap1);
            CREATE INDEX IF NOT EXISTS idx_kemenkeu_tkdd_urakun ON jaga.kemenkeu_tkdd USING btree (urakun);
            
            CREATE INDEX IF NOT EXISTS idx_kemenkeu_realisasi_apbd_tahun ON jaga.kemenkeu_realisasi_apbd USING btree (tahun);
            CREATE INDEX IF NOT EXISTS idx_kemenkeu_realisasi_apbd_kodesatker ON jaga.kemenkeu_realisasi_apbd USING btree (kodesatker);
            CREATE INDEX IF NOT EXISTS idx_kemenkeu_realisasi_apbd_kodepemda ON jaga.kemenkeu_realisasi_apbd USING btree (kodepemda);
            CREATE INDEX IF NOT EXISTS idx_kemenkeu_realisasi_apbd_level1coa90 ON jaga.kemenkeu_realisasi_apbd USING btree (level1coa90);
            CREATE INDEX IF NOT EXISTS idx_kemenkeu_realisasi_apbd_level2coa90 ON jaga.kemenkeu_realisasi_apbd USING btree (level2coa90);
            CREATE INDEX IF NOT EXISTS idx_kemenkeu_realisasi_apbd_level3coa90 ON jaga.kemenkeu_realisasi_apbd USING btree (level3coa90);
        ");

    }

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

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

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

        self::$connection->execute(
            "DROP TABLE IF EXISTS jaga.log_kemenkeu_errors;"
        );
        self::$connection->execute("
            DELETE FROM jaga.app_config WHERE kode ILIKE 'KEMENKEU_%';
SELECT setval(pg_get_serial_sequence('app_config', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.app_config;
        ");

        self::$connection->execute("
            DROP INDEX IF EXISTS idx_kemenkeu_master_pemda_kodepemda;
            
            DROP INDEX IF EXISTS idx_kemenkeu_tkdd_kodepemda;
            DROP INDEX IF EXISTS idx_kemenkeu_tkdd_tahun;
            DROP INDEX IF EXISTS idx_kemenkeu_tkdd_urmap1;
            DROP INDEX IF EXISTS idx_kemenkeu_tkdd_urakun;
            
            DROP INDEX IF EXISTS idx_kemenkeu_realisasi_apbd_tahun;
            DROP INDEX IF EXISTS idx_kemenkeu_realisasi_apbd_kodesatker;
            DROP INDEX IF EXISTS idx_kemenkeu_realisasi_apbd_kodepemda;
            DROP INDEX IF EXISTS idx_kemenkeu_realisasi_apbd_level1coa90;
            DROP INDEX IF EXISTS idx_kemenkeu_realisasi_apbd_level2coa90;
            DROP INDEX IF EXISTS idx_kemenkeu_realisasi_apbd_level3coa90;
        ");
    }
}