<?php

use Phalcon\Mvc\Model\Migration;

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

        // add flagging target
        self::$connection->execute('
            alter table jaga.str_target
            add column if not exists is_special int
        ');


        self::$connection->execute("
            CREATE OR REPLACE VIEW jaga.stranas_target_instansi
            AS SELECT a.id,
                a.id_periode,
                a.id_level1,
                a.id_level2,
                a.id_level3,
                a.id_level4,
                a.nama,
                a.info_nilai,
                a.info_lampiran,
                a.created_by,
                a.created_at,
                a.updated_by,
                a.updated_at,
                a.deleted_by,
                a.deleted_at,
                b.instansi,
                a.is_special 
               FROM jaga.str_target a
                 JOIN ( SELECT a_1.id_target,
                        string_agg(b_1.nama::text, ', '::text) AS instansi
                       FROM jaga.str_target_master a_1
                         JOIN jaga.instansi b_1 ON b_1.id = a_1.id_instansi
                      GROUP BY a_1.id_target) b ON b.id_target = a.id
              WHERE a.deleted_at IS NULL;
        ");


    }

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