<?php

use Phalcon\Mvc\Model\Migration;

class StranasothernilaiMigration_394 extends Migration
{
    public function up()
    {
        // Perubahan:
        // othermax_{1..8}, othermin_{1..8} diambil sebagai integer 
        // dan nilainya bisa null

        // Tambahan:
        // count(distinct id) as count
        // untuk mengetahui apakah indikator merupakan kolaboratif (count > 1)
        // atau tidak (count = 1)
         
        $query = <<<QUERY
            DROP VIEW "jaga"."other_nilai";
            CREATE OR REPLACE VIEW "jaga"."other_nilai" AS
                SELECT str_target_master.id_target,
                max(str_target_master.nilai_tw1::integer) AS othermax_1,
                max(str_target_master.nilai_tw2::integer) AS othermax_2,
                max(str_target_master.nilai_tw3::integer) AS othermax_3,
                max(str_target_master.nilai_tw4::integer) AS othermax_4,
                max(str_target_master.nilai_tw5::integer) AS othermax_5,
                max(str_target_master.nilai_tw6::integer) AS othermax_6,
                max(str_target_master.nilai_tw7::integer) AS othermax_7,
                max(str_target_master.nilai_tw8::integer) AS othermax_8,
                min(str_target_master.nilai_tw1::integer) AS othermin_1,
                min(str_target_master.nilai_tw2::integer) AS othermin_2,
                min(str_target_master.nilai_tw3::integer) AS othermin_3,
                min(str_target_master.nilai_tw4::integer) AS othermin_4,
                min(str_target_master.nilai_tw5::integer) AS othermin_5,
                min(str_target_master.nilai_tw6::integer) AS othermin_6,
                min(str_target_master.nilai_tw7::integer) AS othermin_7,
                min(str_target_master.nilai_tw8::integer) AS othermin_8,
                count(distinct id) as count
            FROM jaga.str_target_master
            GROUP BY str_target_master.id_target;
QUERY;
        self::$connection->execute($query);
    }

    public function down()
    {
        $query = <<<QUERY
            DROP VIEW "jaga"."other_nilai";
            CREATE OR REPLACE VIEW "jaga"."other_nilai" AS
                SELECT str_target_master.id_target,
                max(COALESCE(str_target_master.nilai_tw1, 0::numeric)) AS othermax_1,
                max(COALESCE(str_target_master.nilai_tw2, 0::numeric)) AS othermax_2,
                max(COALESCE(str_target_master.nilai_tw3, 0::numeric)) AS othermax_3,
                max(COALESCE(str_target_master.nilai_tw4, 0::numeric)) AS othermax_4,
                max(COALESCE(str_target_master.nilai_tw5, 0::numeric)) AS othermax_5,
                max(COALESCE(str_target_master.nilai_tw6, 0::numeric)) AS othermax_6,
                max(COALESCE(str_target_master.nilai_tw7, 0::numeric)) AS othermax_7,
                max(COALESCE(str_target_master.nilai_tw8, 0::numeric)) AS othermax_8,
                min(COALESCE(str_target_master.nilai_tw1, 0::numeric)) AS othermin_1,
                min(COALESCE(str_target_master.nilai_tw2, 0::numeric)) AS othermin_2,
                min(COALESCE(str_target_master.nilai_tw3, 0::numeric)) AS othermin_3,
                min(COALESCE(str_target_master.nilai_tw4, 0::numeric)) AS othermin_4,
                min(COALESCE(str_target_master.nilai_tw5, 0::numeric)) AS othermin_5,
                min(COALESCE(str_target_master.nilai_tw6, 0::numeric)) AS othermin_6,
                min(COALESCE(str_target_master.nilai_tw7, 0::numeric)) AS othermin_7,
                min(COALESCE(str_target_master.nilai_tw8, 0::numeric)) AS othermin_8
            FROM jaga.str_target_master
            GROUP BY str_target_master.id_target;
QUERY;
        self::$connection->execute($query);
    }
}