<?php 

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

/**
 * Class Public.UpdateIdSubaksi_236
 */
class MasterTargetMigration_242 extends Migration
{
    /**
     * Define the table structure
     *
     * @return void
     */
    public function morph()
    {
        
    }

    /**
     * Run the migrations
     *
     * @return void
     */
    public function up()
    {
    self::$connection->execute('
        -- hierarki target to fokus
        create view jaga.target_hierarchy as (
            select 
                c.id_fokus,
                d.nama as fokus,
                b.id_aksi,
                c.nama as aksi,
                a.id_subaksi,
                b.nama as subaksi,
                a.id as id_target,
                a.keterangan as target,
                a.info_nilai as target_info_nilai,
                a.info_lampiran  as target_info_lampiran
            from jaga.stranas_target a 
                join jaga.stranas_subaksi b on b.id = a.id_subaksi 
                join jaga.stranas_aksi c on c.id = b.id_aksi 
                join jaga.stranas_fokus d on d.id = c.id_fokus 
        );
        
        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 jaga.stranas_target 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
            ),
            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,
                b.verified_at,
                a.created_at,
                --case when b.created_at is not null then 1 else 0 end as is_submitted,
                case when a.status = 1 or b.is_verified is not null then 1 else 0 end as is_verified,
                --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
                --case when a.nilai_verification = 100 or b.nilai_verification = 100 then 1 else 0 end is_completed
                /*
                case 
                    -- jika periode akhir = periode target maka gada hutang
                    when a.ori_id_periode = coalesce(b.id_periode, a.ori_id_periode) then 0 
                    -- jika nilai akhir = 0 maka nilai terhutang adalah 100 dikurangi nilai on target
                    when b.nilai_verification = 0 then 0 --100 - a.nilai_verification
                    else b.nilai_verification - a.nilai_verification end as nilai_terhutang
                */
            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
            ),
            tfinal as (
            select 
                a.*, 
                case when a.created_at is not null then 1 else 0 end as is_submitted,
                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
        );

        create view jaga.target_instansi_rekap_qty as (
            with t1 as (
            select 
                a.id_instansi,
                b.nama as periode,
                id_periode, 
                count(1) as jumlah_target,
                sum(coalesce(is_verified,0)) as count_verified,
                sum(nilai_ontarget) as nilai_ontarget,
                sum(nilai_terhutang) as nilai_terhutang,
                round(sum(nilai_ontarget) / (count(1) * 100.00), 2) as pct_ontarget,
                round(sum(nilai_terhutang) / (count(1) * 100.00), 2) as pct_terhutang,
                round((sum(nilai_ontarget) + sum(nilai_terhutang)) / (count(1) * 100.00), 2) as pct_total
            from jaga.target_instansi a 
                join jaga.stranas_periode b on b.id = a.id_periode
            group by a.id_instansi, id_periode, b.nama
            )
            select * from t1
        );

        create view jaga.target_instansi_rekap as (
            with t1 as (
            select 
                id_instansi, 
                sum(jumlah_target) as jumlah_target, 
                sum(count_verified) as count_verified,
                sum(nilai_ontarget) as nilai_ontarget,
                sum(nilai_terhutang) as nilai_terhutang,
                sum(nilai_ontarget) / (sum(jumlah_target) * 100.0) as pct_ontarget,
                sum(nilai_terhutang) / (sum(jumlah_target) * 100.0) as pct_terhutang,
                sum(nilai_ontarget + nilai_terhutang) / (sum(jumlah_target) * 100.0) as pct_total
            from jaga.target_instansi_rekap_qty
            group by id_instansi 
            )
            select b.nama as instansi, t1.* 
            from t1 
                join jaga.instansi b on b.id = t1.id_instansi
        );

        create view jaga.periode_aktif as (
            with t1 as (
            select 
                case 
                    when 
                        started_at::date <= now()::timestamp 
                        and now()::timestamp <= ended_at::date
                    then 
                        case when now()::timestamp <= started_at::date then 0 else 1 end
                    else 0 
                end as is_active,
                *
            from jaga.stranas_periode 
            ),
            t2 as (
            select 
                row_number() over (partition by is_active order by id asc) as active_ord,
                * 
            from t1
            where is_active = 1
            )
            select * from t2
        );
    ');
    }

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

    }

}
/*
drop view jaga.target_instansi_rekap;
drop view jaga.target_instansi_rekap_qty;
drop view jaga.target_instansi;
drop view jaga.target_log;
drop view jaga.target_hierarchy;
drop view jaga.periode_aktif;
drop table jaga.stranas_ranking;
drop function jaga.generate_achievementlist;
*/