<?php 

use Phalcon\Mvc\Model\Migration;

/**
 * Class ReportAchBasedOnLatestVerificationMigration_376
 */
class ReportAchBasedOnLatestVerificationMigration_376 extends Migration
{
    /**
     * Run the migrations
     *
     * @return void
     */
    public function up()
    {
        $createLastVerificationFunction = <<<QUERY
        CREATE OR REPLACE FUNCTION latest_verification(
            nilai_tw8 numeric,
            nilai_tw7 numeric,
            nilai_tw6 numeric,
            nilai_tw5 numeric,
            nilai_tw4 numeric,
            nilai_tw3 numeric,
            nilai_tw2 numeric,
            nilai_tw1 numeric)
            RETURNS numeric AS $$
            BEGIN
                IF nilai_tw8 <> 0 THEN
                    RETURN nilai_tw8;
                END IF;
                IF nilai_tw7 <> 0 THEN
                    RETURN nilai_tw7;
                END IF;
                IF nilai_tw6 <> 0 THEN
                    RETURN nilai_tw6;
                END IF;
                IF nilai_tw5 <> 0 THEN
                    RETURN nilai_tw5;
                END IF;
                IF nilai_tw4 <> 0 THEN
                    RETURN nilai_tw4;
                END IF;
                IF nilai_tw3 <> 0 THEN
                    RETURN nilai_tw3;
                END IF;
                IF nilai_tw2 <> 0 THEN
                    RETURN nilai_tw2;
                END IF;
                IF nilai_tw1 <> 0 THEN
                    RETURN nilai_tw1;
                END IF;
                RETURN 0;
            END;
        $$ LANGUAGE plpgsql;

QUERY;
    
        self::$connection->execute($createLastVerificationFunction);

        $modifyReportAchViewIntoLatestVerification = <<<QUERY
        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 greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8) = 100 then 1 else 0 end as is_completed,
                CAST(latest_verification(nilai_tw8,nilai_tw7,nilai_tw6,nilai_tw5,nilai_tw4,nilai_tw3,nilai_tw2,nilai_tw1) as numeric(10,2)) as nilai_verification,
                case when coalesce(klaim_tw1,0)=0 then 0 else 1 end as is_submit1, 
                case when coalesce(klaim_tw2,0)=0 then 0 else 1 end as is_submit2, 
                case when coalesce(klaim_tw3,0)=0 then 0 else 1 end as is_submit3, 
                case when coalesce(klaim_tw4,0)=0 then 0 else 1 end as is_submit4, 
                case when coalesce(klaim_tw5,0)=0 then 0 else 1 end as is_submit5, 
                case when coalesce(klaim_tw6,0)=0 then 0 else 1 end as is_submit6, 
                case when coalesce(klaim_tw7,0)=0 then 0 else 1 end as is_submit7, 
                case when coalesce(klaim_tw8,0)=0 then 0 else 1 end as is_submit8,
                case when coalesce(nilai_tw1,0)=0 then 0 else 1 end as is_verify1, 
                case when coalesce(nilai_tw2,0)=0 then 0 else 1 end as is_verify2, 
                case when coalesce(nilai_tw3,0)=0 then 0 else 1 end as is_verify3, 
                case when coalesce(nilai_tw4,0)=0 then 0 else 1 end as is_verify4, 
                case when coalesce(nilai_tw5,0)=0 then 0 else 1 end as is_verify5, 
                case when coalesce(nilai_tw6,0)=0 then 0 else 1 end as is_verify6, 
                case when coalesce(nilai_tw7,0)=0 then 0 else 1 end as is_verify7, 
                case when coalesce(nilai_tw8,0)=0 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,'') 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 * from jaga.str_hierarchy where deleted_at is null) l1 on l1.id = t.id_level1 
                left join (select * from jaga.str_hierarchy where deleted_at is null) l2 on l2.id = t.id_level2
                left join (select * from jaga.str_hierarchy where deleted_at is null) l3 on l3.id = t.id_level3
                left join (select * from jaga.str_hierarchy where deleted_at is null) l4 on l4.id = t.id_level4
            where t.deleted_at is null;
        ;
QUERY;
        self::$connection->execute($modifyReportAchViewIntoLatestVerification);
    }
    
    /**
     * Reverse the migrations
     *
     * @return void
     */
    public function down()
    {
        // revert the view using the function first
        $revertReportAchView = <<<QUERY
        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 greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8) = 100 then 1 else 0 end as is_completed,
                greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8) as nilai_verification,
                case when coalesce(klaim_tw1,0)=0 then 0 else 1 end as is_submit1, 
                case when coalesce(klaim_tw2,0)=0 then 0 else 1 end as is_submit2, 
                case when coalesce(klaim_tw3,0)=0 then 0 else 1 end as is_submit3, 
                case when coalesce(klaim_tw4,0)=0 then 0 else 1 end as is_submit4, 
                case when coalesce(klaim_tw5,0)=0 then 0 else 1 end as is_submit5, 
                case when coalesce(klaim_tw6,0)=0 then 0 else 1 end as is_submit6, 
                case when coalesce(klaim_tw7,0)=0 then 0 else 1 end as is_submit7, 
                case when coalesce(klaim_tw8,0)=0 then 0 else 1 end as is_submit8,
                case when coalesce(nilai_tw1,0)=0 then 0 else 1 end as is_verify1, 
                case when coalesce(nilai_tw2,0)=0 then 0 else 1 end as is_verify2, 
                case when coalesce(nilai_tw3,0)=0 then 0 else 1 end as is_verify3, 
                case when coalesce(nilai_tw4,0)=0 then 0 else 1 end as is_verify4, 
                case when coalesce(nilai_tw5,0)=0 then 0 else 1 end as is_verify5, 
                case when coalesce(nilai_tw6,0)=0 then 0 else 1 end as is_verify6, 
                case when coalesce(nilai_tw7,0)=0 then 0 else 1 end as is_verify7, 
                case when coalesce(nilai_tw8,0)=0 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,'') 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 * from jaga.str_hierarchy where deleted_at is null) l1 on l1.id = t.id_level1 
                left join (select * from jaga.str_hierarchy where deleted_at is null) l2 on l2.id = t.id_level2
                left join (select * from jaga.str_hierarchy where deleted_at is null) l3 on l3.id = t.id_level3
                left join (select * from jaga.str_hierarchy where deleted_at is null) l4 on l4.id = t.id_level4
            where t.deleted_at is null;
        ;
QUERY;
        self::$connection->execute($revertReportAchView);

        // then drop the function
        $dropLastVerificationFunction = <<<QUERY
        DROP FUNCTION IF EXISTS latest_verification;
QUERY;
        self::$connection->execute($dropLastVerificationFunction);
    }
}