<?php

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

        self::$connection->execute("
            INSERT INTO jaga.app_config (kode, nilai, keterangan) VALUES ('EMIS_KEMENAG_PUBLIC_KEY', 'OL82LbKBFzFwrJgt9tv7NjgfySRCzLky', 'Public Key yg dipakai untuk call API ke EMIS Kemenag');
            INSERT INTO jaga.app_config (kode, nilai, keterangan) VALUES ('EMIS_KEMENAG_PRIVATE_KEY', '0kRQhORbpeQT9vGHcNBcKrVIOxTr4VV3', 'Private Key yg dipakai untuk call API ke EMIS Kemenag');
        ");

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.madrasah
            (
                id bigserial PRIMARY KEY,
                id_emis bigint NOT NULL,
                statistic_num CHARACTER VARYING(20),
                npsn CHARACTER VARYING(20),
                name CHARACTER VARYING(255),
                status CHARACTER VARYING(100),
                phone CHARACTER VARYING(100),
                accreditation_status CHARACTER VARYING(100),
                category CHARACTER VARYING(100),
                student_count INT,
                jumlah_siswa INT,
                rombel_count INT,
                jumlah_rombel INT,
                personnel_count INT,
                waktu_pembelajaran_sesuai_kn INT,
                mapel_wajib_sesuai_kn INT,
                jumlah_lulusan_terakhir INT,
                has_graduate INT,
                institution_id bigint,
                address CHARACTER VARYING(255),
                city_id CHARACTER VARYING(5),
                province_id CHARACTER VARYING(3),
                longitude CHARACTER VARYING(20),
                latitude CHARACTER VARYING(20),
                formatted_address CHARACTER VARYING(255),
                sub_district CHARACTER VARYING(255),
                district CHARACTER VARYING(255),
                city CHARACTER VARYING(255),
                province CHARACTER VARYING(255),
                student_male INT,
                student_female INT,
                student_normal INT,
                student_special_need INT,
                student_with_study_group INT,
                student_non_study_group INT,
                student_stop_learning INT,
                student_dropout INT,
                student_ipa INT,
                student_ips INT,
                student_bahasa INT,
                student_agama INT,
                study_group_num INT,
                personnel_teacher INT,
                personnel_education_staff INT,
                teacher_male INT,
                teacher_female INT,
                teacher_pns INT,
                teacher_non_pns INT,
                teacher_permanent INT,
                teacher_temporary INT,
                teacher_other INT,
                education_staff_male INT,
                education_staff_female INT,
                education_staff_pns INT,
                education_staff_non_pns INT,
                education_staff_permanent INT,
                jenjang_id INT
            );
        ");

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.madrasah_province
            (
                province_code CHARACTER VARYING(3) PRIMARY KEY,
                province_name CHARACTER VARYING(255),
            	last_update_at timestamp(6),
            	last_update_page character varying (4)
            );
            
            INSERT INTO jaga.madrasah_province (SELECT provinsi_code, provinsi_name, null, null FROM jaga.master_provinsi);
        ");

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.madrasah_like
            (
                madrasah_id BIGINT NOT NULL,
                user_id BIGINT NOT NULL,
            	created_at TIMESTAMP (6) DEFAULT NOW()
            );
        ");
    }

    public function down()
    {
        self::$connection->execute("
            DELETE FROM jaga.app_config WHERE kode ILIKE 'EMIS_KEMENAG_%';
            SELECT setval(pg_get_serial_sequence('app_config', 'id'), coalesce(max(id),0) + 1, false) FROM jaga.app_config;
        ");

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

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

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


    }
}