<?php

use Phalcon\Mvc\Model\Migration;
class ViewNotifikasiMCPMigration_447 extends Migration
{
    public function up()
    {
        $this->down();

        self::$connection->execute("
            CREATE VIEW jaga.view_notifikasi_mcp AS
            (
            SELECT
            	COUNT(ka.*) AS total_answer,
            	ka.id_survey,
            	ks.id_periode,
            	ks.id_template,
            	kt.nama AS template_name,
            	ks.id_instansi,
            	ins.nama AS instansi_name,
            	MAX(ka.updated_at) AS last_updated_at,
				kp.started_at AS start_periode,
				kp.ended_at AS end_periode
            	
            FROM
            	jaga.korsupgah_answer ka
            	LEFT JOIN jaga.korsupgah_survey ks ON ka.id_survey = ks.id
            	LEFT JOIN jaga.korsupgah_periode kp ON ks.id_periode = kp.id
            	LEFT JOIN jaga.korsupgah_template kt ON ks.id_template = kt.id
            	LEFT JOIN jaga.instansi ins ON ks.id_instansi = ins.id
            	
            	WHERE ka.verified_by IS NULL AND ks.deleted_at IS NULL
            	GROUP BY ka.id_survey, ks.id_periode, ks.id_template, kt.nama, ks.id_instansi, ins.nama, kp.started_at, kp.ended_at
            	ORDER BY MAX(ka.updated_at) DESC    
            );
        ");
    }

    public function down()
    {
        self::$connection->execute(
            "DROP VIEW IF EXISTS jaga.view_notifikasi_mcp;"
        );
    }
}