<?php 

use Phalcon\Db\Column;
use Phalcon\Db\Index;
use Phalcon\Db\Reference;
use Phalcon\Mvc\Model\Migration;

class TargetInstansiMigration_264 extends Migration
{
    /**
     * Define the table structure
     *
     * @return void
     */
    public function morph()
    {
        
    }

    /**
     * Run the migrations
     *
     * @return void
     */
    public function up()
    {
    self::$connection->execute('
        drop view if exists jaga.target_instansi;
        drop view if exists jaga.target_log;

        create view jaga.target_log as (
            with t0 as (
                select *, row_number () over (partition by id_survey, id_question order by verified_at desc) as selected
                from jaga.stranas_survey_answer
            ),
            tver as (
                select * from t0 where selected = 1
            ),
            t1 as (
            select 
                ss.id_instansi_satker as id_instansi, 
                ii.nama as instansi,
                p.nama as periode, 
                ta.keterangan as target,
                ta.id_subaksi,
                coalesce(c.status, 0) as status,
                c.created_by,
                c.created_at,
                c.updated_by,
                c.updated_at,
                c.verified_by,
                c.verified_at,
                coalesce(nilai, 0) as nilai_klaim,
                coalesce(nilai_verification, 0) as nilai_verification,
                q.id_target, 
                q.id as id_question,
                ss.id as id_survey,
                p.id as id_periode,
                c.id as id_answer, 
                row_number() over (partition by id_target, ss.id_instansi_satker order by p.id asc) as target_period,
                row_number() over (partition by id_target, ss.id_instansi_satker order by p.id desc) as last_period
            from jaga.stranas_survey ss
                join jaga.stranas_periode p on ss.id_periode = p.id
                join jaga.stranas_survey_template t on ss.id_survey_template = t.id
                join jaga.stranas_survey_template_question q on q.id_survey_template = t.id 
                left join tver c on c.id_survey  = ss.id and c.id_question = q.id_target 
                join (select * from jaga.stranas_target where deleted_at is null) ta on ta.id = q.id_target 
                join jaga.instansi ii on ii.id = ss.id_instansi_satker
            ),
            tperiode as (
            select id_instansi, id_target, id_periode  from t1 where target_period = 1
            ),
            tsrc as (
            select 
                --id 
                a.id_instansi,
                a.id_target,
                a.id_subaksi,
                b.id_periode as ori_id_periode,
                a.id_periode,
                id_survey,
                id_question,
                id_answer,
                --status pelaporan
                status,
                nilai_klaim,
                nilai_verification,
                a.created_by,
                coalesce(c.nama, \'\') as created_name,
                a.created_at,
                case when a.created_at is not null then 1 else 0 end as is_submitted,
                a.updated_by,
                a.updated_at,
                a.verified_by,
                coalesce(v.nama, \'\') as verified_name,
                a.verified_at,
                --case when a.verified_at is not null then 1 else 0 end as is_verified,
                --label
                instansi,
                target,
                periode,
                -- flagging
                target_period,
                last_period
            from t1 a 
                join tperiode b on b.id_instansi = a.id_instansi and b.id_target = a.id_target
                left join public.um_user c on c.um_id = a.created_by
                left join public.um_user v on v.um_id = a.verified_by
            )
            select * from tsrc
        );

        -- target instansi 
        create view jaga.target_instansi as (
            with tsrc as (
                select * from jaga.target_log
            ),
            tonperiod as (
                select * from tsrc where target_period = 1
            ),
            tlast_verify as (
                select *, row_number() over (partition by id_target, id_instansi order by id_periode desc) as last_period2
                from tsrc where verified_at is not null and target_period <> 1
            ),
            tlast as (
                select *, case when verified_at is not null or status = 1 then 1 else 0 end as is_verified from tlast_verify where last_period2 = 1
            ),
            tmax as (
                select *, row_number() over (partition by id_target, id_instansi order by nilai_verification desc) as max_nilai
                from tsrc --where ori_id_periode <> id_periode
            ),
            tmaxone as (
                select * from tmax where max_nilai = 1
            ),
              tins as (
              select id_instansi, id_target, count(1) as count_log from tsrc group by id_instansi, id_target
              ),
              tsum as (
              select
                  a.id_instansi,
                  a.id_target,
                  a.id_subaksi,
                  a.ori_id_periode as id_periode,
                  a.id_question,
                  a.id_survey,
                  a.status,
                  case when i.count_log = 1 then a.verified_at else b.verified_at end as verified_at,
                  case when i.count_log = 1 then a.created_at else b.created_at end as created_at,
                  --label
                  a.instansi,
                  a.target,
                  a.periode,
                  coalesce(b.id_periode, a.ori_id_periode) as id_periode_berjalan,
                  p.nama as periode_berjalan,
                  a.nilai_verification as nilai_ontarget,
                  coalesce(b.nilai_verification,0) as nilai_terakhir,
                  coalesce(m.nilai_verification,0) as nilai_max,
                  case 
                      when coalesce(m.nilai_verification,0) > coalesce(b.nilai_verification,0)
                      then coalesce(m.nilai_verification,0)
                      else coalesce(b.nilai_verification,0)
                  end as nilai_akhir
              from tonperiod a 
                  left join tlast b on b.id_instansi = a.id_instansi and b.id_target = a.id_target
                  left join jaga.stranas_periode p on p.id = coalesce(b.id_periode, a.ori_id_periode)
                  left join tmaxone m on m.id_instansi = a.id_instansi and m.id_target = a.id_target
                  left join tins i on i.id_instansi = a.id_instansi and i.id_target = a.id_target
              ),
              tfinal as (
              select 
                  a.*, 
                  case when a.created_at is not null then 1 else 0 end as is_submitted,
                  case when a.verified_at is not null then 1 else 0 end as is_verified,
                  b.nama as subaksi,
                  case 
                      -- jika periode akhir dan periode target sama, maka tidak ada terhutang
                      when id_periode = id_periode_berjalan then 0
                      -- jika max value = 0 maka tidak ada terhutan
                      when nilai_max = 0 then 0
                      else nilai_max - nilai_ontarget 
                  end as nilai_terhutang,
                  case when nilai_ontarget = 100 or nilai_max = 100 then 1 else 0 end is_completed
              from tsum a 
              join jaga.stranas_subaksi b on b.id = a.id_subaksi
              )
            select * from tfinal
        );
    ');
    }

    /**
     * Reverse the migrations
     *
     * @return void
     */
    public function down()
    {

    }

}