<?php

use Phalcon\Mvc\Model\Migration;

class AlterTableStrTargetMigration_387 extends Migration
{
    // Melayani penambahan fitur jaga.str_target.is_forced_complete
    // Admin StranasPK dapat menyatakan bahwa indikator
    // dinyatakan selesai dan tidak perlu dibuat pelaporan lebih lanjut
    public function up()
    {

        // 1. Tambah kolom jaga.str_target.is_forced_complete
        $addColumnIsForcedCompleteQuery = <<<QUERY
        ALTER TABLE jaga.str_target ADD COLUMN IF NOT EXISTS is_forced_complete boolean default false;
        UPDATE jaga.str_target set is_forced_complete = true where id in (3021, 3022);
QUERY;
        self::$connection->execute($addColumnIsForcedCompleteQuery);

        // 2. view jaga.report_ach -> add column is_forced_complete
        $reportAchAddIsForcedComplete = <<<QUERY
        DROP VIEW IF EXISTS jaga.report_ach;
        CREATE OR REPLACE VIEW jaga.report_ach
        AS
        SELECT a.id,
           a.id AS id_master,
           l1.nama AS level1,
           l2.nama AS level2,
           l3.nama AS level3,
           l4.nama AS level4,
           i.nama AS instansi,
           it.nama AS tipe_instansi,
           t.nama AS target,
           t.info_nilai,
           t.info_lampiran,
               CASE
                   WHEN jaga.latest_verification(a.nilai_tw8, a.nilai_tw7, a.nilai_tw6, a.nilai_tw5, a.nilai_tw4, a.nilai_tw3, a.nilai_tw2, a.nilai_tw1) = 100::numeric
                   OR t.is_forced_complete
                   THEN 1
                   ELSE 0
               END AS is_completed,
           t.is_forced_complete as is_forced_complete,
           jaga.latest_verification(a.nilai_tw8, a.nilai_tw7, a.nilai_tw6, a.nilai_tw5, a.nilai_tw4, a.nilai_tw3, a.nilai_tw2, a.nilai_tw1)::numeric(10,2) AS nilai_verification,
               CASE
                   WHEN COALESCE(a.klaim_tw1, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit1,
               CASE
                   WHEN COALESCE(a.klaim_tw2, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit2,
               CASE
                   WHEN COALESCE(a.klaim_tw3, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit3,
               CASE
                   WHEN COALESCE(a.klaim_tw4, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit4,
               CASE
                   WHEN COALESCE(a.klaim_tw5, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit5,
               CASE
                   WHEN COALESCE(a.klaim_tw6, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit6,
               CASE
                   WHEN COALESCE(a.klaim_tw7, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit7,
               CASE
                   WHEN COALESCE(a.klaim_tw8, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit8,
               CASE
                   WHEN COALESCE(a.nilai_tw1, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify1,
               CASE
                   WHEN COALESCE(a.nilai_tw2, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify2,
               CASE
                   WHEN COALESCE(a.nilai_tw3, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify3,
               CASE
                   WHEN COALESCE(a.nilai_tw4, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify4,
               CASE
                   WHEN COALESCE(a.nilai_tw5, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify5,
               CASE
                   WHEN COALESCE(a.nilai_tw6, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify6,
               CASE
                   WHEN COALESCE(a.nilai_tw7, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify7,
               CASE
                   WHEN COALESCE(a.nilai_tw8, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify8,
           a.id_instansi,
           a.id_target,
           a.id_periode,
           l1.id AS id_level1,
           l2.id AS id_level2,
           l3.id AS id_level3,
           l4.id AS id_level4,
           t.is_special,
           t.start_triwulan,
           COALESCE(y.label, ''::character varying) AS target_type
          FROM jaga.str_target_master a
            JOIN jaga.instansi i ON i.id = a.id_instansi
            JOIN jaga.instansi_tipe it ON it.id = i.id_tipe
            JOIN jaga.str_target t ON t.id = a.id_target
            LEFT JOIN jaga.str_target_type y ON y.id = t.is_special
            JOIN ( SELECT str_hierarchy.id,
                   str_hierarchy.id_periode,
                   str_hierarchy.id_parent,
                   str_hierarchy.id_level,
                   str_hierarchy.kode,
                   str_hierarchy.nama,
                   str_hierarchy.keterangan,
                   str_hierarchy.created_by,
                   str_hierarchy.created_at,
                   str_hierarchy.updated_by,
                   str_hierarchy.updated_at,
                   str_hierarchy.deleted_by,
                   str_hierarchy.deleted_at,
                   str_hierarchy.ord
                  FROM jaga.str_hierarchy
                 WHERE str_hierarchy.deleted_at IS NULL) l1 ON l1.id = t.id_level1
            LEFT JOIN ( SELECT str_hierarchy.id,
                   str_hierarchy.id_periode,
                   str_hierarchy.id_parent,
                   str_hierarchy.id_level,
                   str_hierarchy.kode,
                   str_hierarchy.nama,
                   str_hierarchy.keterangan,
                   str_hierarchy.created_by,
                   str_hierarchy.created_at,
                   str_hierarchy.updated_by,
                   str_hierarchy.updated_at,
                   str_hierarchy.deleted_by,
                   str_hierarchy.deleted_at,
                   str_hierarchy.ord
                  FROM jaga.str_hierarchy
                 WHERE str_hierarchy.deleted_at IS NULL) l2 ON l2.id = t.id_level2
            LEFT JOIN ( SELECT str_hierarchy.id,
                   str_hierarchy.id_periode,
                   str_hierarchy.id_parent,
                   str_hierarchy.id_level,
                   str_hierarchy.kode,
                   str_hierarchy.nama,
                   str_hierarchy.keterangan,
                   str_hierarchy.created_by,
                   str_hierarchy.created_at,
                   str_hierarchy.updated_by,
                   str_hierarchy.updated_at,
                   str_hierarchy.deleted_by,
                   str_hierarchy.deleted_at,
                   str_hierarchy.ord
                  FROM jaga.str_hierarchy
                 WHERE str_hierarchy.deleted_at IS NULL) l3 ON l3.id = t.id_level3
            LEFT JOIN ( SELECT str_hierarchy.id,
                   str_hierarchy.id_periode,
                   str_hierarchy.id_parent,
                   str_hierarchy.id_level,
                   str_hierarchy.kode,
                   str_hierarchy.nama,
                   str_hierarchy.keterangan,
                   str_hierarchy.created_by,
                   str_hierarchy.created_at,
                   str_hierarchy.updated_by,
                   str_hierarchy.updated_at,
                   str_hierarchy.deleted_by,
                   str_hierarchy.deleted_at,
                   str_hierarchy.ord
                  FROM jaga.str_hierarchy
                 WHERE str_hierarchy.deleted_at IS NULL) l4 ON l4.id = t.id_level4
         WHERE t.deleted_at IS NULL;
QUERY;
        self::$connection->execute($reportAchAddIsForcedComplete);

        // 3. view jaga.stranas_target_instansi -> add column is_forced_complete
        $stranasTargetInstansiAddIsForcedComplete = <<<QUERY
        DROP VIEW IF EXISTS jaga.stranas_target_instansi;
        CREATE OR REPLACE VIEW jaga.stranas_target_instansi
        AS
            SELECT a.id,
            a.id_periode,
            a.id_level1,
            a.id_level2,
            a.id_level3,
            a.id_level4,
            a.nama,
            a.info_nilai,
            a.info_lampiran,
            a.created_by,
            a.created_at,
            a.updated_by,
            a.updated_at,
            a.deleted_by,
            a.deleted_at,
            b.instansi,
            a.is_special,
            COALESCE(a.start_triwulan, 1) AS start_triwulan,
            a.ord,
            a.is_forced_complete
        FROM jaga.str_target a
            JOIN ( SELECT a_1.id_target,
                    string_agg(b_1.nama::text, ', '::text) AS instansi
                FROM jaga.str_target_master a_1
                    JOIN jaga.instansi b_1 ON b_1.id = a_1.id_instansi
                GROUP BY a_1.id_target) b ON b.id_target = a.id
        WHERE a.deleted_at IS NULL;
        ;
QUERY;
        self::$connection->execute($stranasTargetInstansiAddIsForcedComplete);

    }

    public function down()
    {
        $stranasTargetInstansiDropIsForcedComplete = <<<QUERY
        DROP VIEW IF EXISTS jaga.stranas_target_instansi;
        CREATE OR REPLACE VIEW jaga.stranas_target_instansi
        AS
            SELECT a.id,
            a.id_periode,
            a.id_level1,
            a.id_level2,
            a.id_level3,
            a.id_level4,
            a.nama,
            a.info_nilai,
            a.info_lampiran,
            a.created_by,
            a.created_at,
            a.updated_by,
            a.updated_at,
            a.deleted_by,
            a.deleted_at,
            b.instansi,
            a.is_special,
            COALESCE(a.start_triwulan, 1) AS start_triwulan,
            a.ord
        FROM jaga.str_target a
            JOIN ( SELECT a_1.id_target,
                    string_agg(b_1.nama::text, ', '::text) AS instansi
                FROM jaga.str_target_master a_1
                    JOIN jaga.instansi b_1 ON b_1.id = a_1.id_instansi
                GROUP BY a_1.id_target) b ON b.id_target = a.id
        WHERE a.deleted_at IS NULL;
        ;
QUERY;
        self::$connection->execute($stranasTargetInstansiDropIsForcedComplete);
        
        $reportAchQueryDropIsForcedComplete = <<<QUERY
        DROP VIEW IF EXISTS jaga.report_ach;
        CREATE OR REPLACE VIEW jaga.report_ach
        AS
        SELECT a.id,
           a.id AS id_master,
           l1.nama AS level1,
           l2.nama AS level2,
           l3.nama AS level3,
           l4.nama AS level4,
           i.nama AS instansi,
           it.nama AS tipe_instansi,
           t.nama AS target,
           t.info_nilai,
           t.info_lampiran,
               CASE
                   WHEN jaga.latest_verification(a.nilai_tw8, a.nilai_tw7, a.nilai_tw6, a.nilai_tw5, a.nilai_tw4, a.nilai_tw3, a.nilai_tw2, a.nilai_tw1) = 100::numeric
                   THEN 1
                   ELSE 0
               END AS is_completed,
           jaga.latest_verification(a.nilai_tw8, a.nilai_tw7, a.nilai_tw6, a.nilai_tw5, a.nilai_tw4, a.nilai_tw3, a.nilai_tw2, a.nilai_tw1)::numeric(10,2) AS nilai_verification,
               CASE
                   WHEN COALESCE(a.klaim_tw1, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit1,
               CASE
                   WHEN COALESCE(a.klaim_tw2, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit2,
               CASE
                   WHEN COALESCE(a.klaim_tw3, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit3,
               CASE
                   WHEN COALESCE(a.klaim_tw4, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit4,
               CASE
                   WHEN COALESCE(a.klaim_tw5, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit5,
               CASE
                   WHEN COALESCE(a.klaim_tw6, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit6,
               CASE
                   WHEN COALESCE(a.klaim_tw7, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit7,
               CASE
                   WHEN COALESCE(a.klaim_tw8, 0) = 0 THEN 0
                   ELSE 1
               END AS is_submit8,
               CASE
                   WHEN COALESCE(a.nilai_tw1, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify1,
               CASE
                   WHEN COALESCE(a.nilai_tw2, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify2,
               CASE
                   WHEN COALESCE(a.nilai_tw3, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify3,
               CASE
                   WHEN COALESCE(a.nilai_tw4, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify4,
               CASE
                   WHEN COALESCE(a.nilai_tw5, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify5,
               CASE
                   WHEN COALESCE(a.nilai_tw6, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify6,
               CASE
                   WHEN COALESCE(a.nilai_tw7, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify7,
               CASE
                   WHEN COALESCE(a.nilai_tw8, 0::numeric) = 0::numeric THEN 0
                   ELSE 1
               END AS is_verify8,
           a.id_instansi,
           a.id_target,
           a.id_periode,
           l1.id AS id_level1,
           l2.id AS id_level2,
           l3.id AS id_level3,
           l4.id AS id_level4,
           t.is_special,
           t.start_triwulan,
           COALESCE(y.label, ''::character varying) AS target_type
          FROM jaga.str_target_master a
            JOIN jaga.instansi i ON i.id = a.id_instansi
            JOIN jaga.instansi_tipe it ON it.id = i.id_tipe
            JOIN jaga.str_target t ON t.id = a.id_target
            LEFT JOIN jaga.str_target_type y ON y.id = t.is_special
            JOIN ( SELECT str_hierarchy.id,
                   str_hierarchy.id_periode,
                   str_hierarchy.id_parent,
                   str_hierarchy.id_level,
                   str_hierarchy.kode,
                   str_hierarchy.nama,
                   str_hierarchy.keterangan,
                   str_hierarchy.created_by,
                   str_hierarchy.created_at,
                   str_hierarchy.updated_by,
                   str_hierarchy.updated_at,
                   str_hierarchy.deleted_by,
                   str_hierarchy.deleted_at,
                   str_hierarchy.ord
                  FROM jaga.str_hierarchy
                 WHERE str_hierarchy.deleted_at IS NULL) l1 ON l1.id = t.id_level1
            LEFT JOIN ( SELECT str_hierarchy.id,
                   str_hierarchy.id_periode,
                   str_hierarchy.id_parent,
                   str_hierarchy.id_level,
                   str_hierarchy.kode,
                   str_hierarchy.nama,
                   str_hierarchy.keterangan,
                   str_hierarchy.created_by,
                   str_hierarchy.created_at,
                   str_hierarchy.updated_by,
                   str_hierarchy.updated_at,
                   str_hierarchy.deleted_by,
                   str_hierarchy.deleted_at,
                   str_hierarchy.ord
                  FROM jaga.str_hierarchy
                 WHERE str_hierarchy.deleted_at IS NULL) l2 ON l2.id = t.id_level2
            LEFT JOIN ( SELECT str_hierarchy.id,
                   str_hierarchy.id_periode,
                   str_hierarchy.id_parent,
                   str_hierarchy.id_level,
                   str_hierarchy.kode,
                   str_hierarchy.nama,
                   str_hierarchy.keterangan,
                   str_hierarchy.created_by,
                   str_hierarchy.created_at,
                   str_hierarchy.updated_by,
                   str_hierarchy.updated_at,
                   str_hierarchy.deleted_by,
                   str_hierarchy.deleted_at,
                   str_hierarchy.ord
                  FROM jaga.str_hierarchy
                 WHERE str_hierarchy.deleted_at IS NULL) l3 ON l3.id = t.id_level3
            LEFT JOIN ( SELECT str_hierarchy.id,
                   str_hierarchy.id_periode,
                   str_hierarchy.id_parent,
                   str_hierarchy.id_level,
                   str_hierarchy.kode,
                   str_hierarchy.nama,
                   str_hierarchy.keterangan,
                   str_hierarchy.created_by,
                   str_hierarchy.created_at,
                   str_hierarchy.updated_by,
                   str_hierarchy.updated_at,
                   str_hierarchy.deleted_by,
                   str_hierarchy.deleted_at,
                   str_hierarchy.ord
                  FROM jaga.str_hierarchy
                 WHERE str_hierarchy.deleted_at IS NULL) l4 ON l4.id = t.id_level4
         WHERE t.deleted_at IS NULL;
QUERY;
        self::$connection->execute($reportAchQueryDropIsForcedComplete);
        $dropColumnQuery = <<<QUERY
            ALTER TABLE jaga.str_target DROP COLUMN IF EXISTS is_forced_complete;
QUERY;
        self::$connection->execute($dropColumnQuery);
        
    }
}