<?php

use Phalcon\Mvc\Model\Migration;

/**
 * Class StranasNewSoftDeleteMigration_308
 * add soft delete flag exclude
 */
class StranasNewSoftDeleteMigration_308 extends Migration
{
    public function up()
    {
        $this->down();
        self::$connection->execute(
            '
            create 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,a.id_level1,a.id_level2,a.id_level3,a.id_level4
            from jaga.str_target_master a 
                join jaga.str_hierarchy l1 on l1.id = a.id_level1 
                left join jaga.str_hierarchy l2 on l2.id = a.id_level2
                left join jaga.str_hierarchy l3 on l3.id = a.id_level3
                left join jaga.str_hierarchy l4 on l4.id = a.id_level4
                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
            where t.deleted_at is null;
        '
        );
    }

    public function down()
    {
        self::$connection->execute("drop view if exists jaga.report_ach;");
    }
}