<?php

use Phalcon\Mvc\Model\Migration;

class TargetLogMigration_296 extends Migration
{
    public function up()
    {
        $this->down(); 

        self::$connection->execute("
            CREATE OR REPLACE VIEW jaga.target_log
            AS
            WITH t0 AS (
                    SELECT stranas_survey_answer.id,
                    stranas_survey_answer.id_survey,
                    stranas_survey_answer.id_question,
                    stranas_survey_answer.nilai,
                    stranas_survey_answer.keterangan,
                    stranas_survey_answer.nilai_verification,
                    stranas_survey_answer.keterangan_verification,
                    stranas_survey_answer.status,
                    stranas_survey_answer.created_at,
                    stranas_survey_answer.updated_at,
                    stranas_survey_answer.deleted_at,
                    stranas_survey_answer.created_by,
                    stranas_survey_answer.updated_by,
                    stranas_survey_answer.verified_by,
                    stranas_survey_answer.verified_at,
                    row_number() OVER (PARTITION BY stranas_survey_answer.id_survey, stranas_survey_answer.id_question ORDER BY stranas_survey_answer.verified_at DESC) AS selected
                    FROM jaga.stranas_survey_answer
                ), tver AS (
                    SELECT t0.id,
                    t0.id_survey,
                    t0.id_question,
                    t0.nilai,
                    t0.keterangan,
                    t0.nilai_verification,
                    t0.keterangan_verification,
                    t0.status,
                    t0.created_at,
                    t0.updated_at,
                    t0.deleted_at,
                    t0.created_by,
                    t0.updated_by,
                    t0.verified_by,
                    t0.verified_at,
                    t0.selected
                    FROM t0
                    WHERE t0.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(c.nilai, 0) AS nilai_klaim,
                    COALESCE(c.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 q.id_target, ss.id_instansi_satker ORDER BY p.id) AS target_period,
                    row_number() OVER (PARTITION BY q.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 stranas_target.id,
                            stranas_target.id_ukuran_keberhasilan,
                            stranas_target.keterangan,
                            stranas_target.info_nilai,
                            stranas_target.info_lampiran,
                            stranas_target.created_at,
                            stranas_target.updated_at,
                            stranas_target.deleted_at,
                            stranas_target.created_by,
                            stranas_target.updated_by,
                            stranas_target.presentase_bobot,
                            stranas_target.id_subaksi
                            FROM jaga.stranas_target
                            WHERE stranas_target.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 t1.id_instansi,
                    t1.id_target,
                    t1.id_periode
                    FROM t1
                    WHERE t1.target_period = 1
                ), tsrc AS (
                    SELECT a.id_instansi,
                    a.id_target,
                    a.id_subaksi,
                    b.id_periode AS ori_id_periode,
                    a.id_periode,
                    a.id_survey,
                    a.id_question,
                    a.id_answer,
                    a.status,
                    a.nilai_klaim,
                    a.nilai_verification,
                    a.created_by,
                    COALESCE(c.nama, ''::character varying) 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, ''::character varying) AS verified_name,
                    a.verified_at,
                    a.instansi,
                    a.target,
                    a.periode,
                    a.target_period,
                    a.last_period
                    FROM t1 a
                        JOIN tperiode b ON b.id_instansi = a.id_instansi AND b.id_target = a.id_target
                        LEFT JOIN jaga.um_user c ON c.um_id = a.created_by
                        LEFT JOIN jaga.um_user v ON v.um_id = a.verified_by
                )
            SELECT tsrc.id_instansi,
            tsrc.id_target,
            tsrc.id_subaksi,
            tsrc.ori_id_periode,
            tsrc.id_periode,
            tsrc.id_survey,
            tsrc.id_question,
            tsrc.id_answer,
            tsrc.status,
            tsrc.nilai_klaim,
            tsrc.nilai_verification,
            tsrc.created_by,
            tsrc.created_name,
            tsrc.created_at,
            tsrc.is_submitted,
            tsrc.updated_by,
            tsrc.updated_at,
            tsrc.verified_by,
            tsrc.verified_name,
            tsrc.verified_at,
            tsrc.instansi,
            tsrc.target,
            tsrc.periode,
            tsrc.target_period,
            tsrc.last_period
            FROM tsrc;
        ");
    }

    public function down()
    {
        
    }
}