<?php

use Phalcon\Mvc\Model\Migration;

/**
 * Class AddOrderMigration_348
 */
class AddOrderMigration_348 extends Migration
{
    public function up()
    {
        $this->down();


        self::$connection->execute("
        ALTER TABLE jaga.str_target ADD COLUMN ord 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,
                coalesce(a.start_triwulan,1) as start_triwulan,
                a.ord
               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('ALTER TABLE jaga.str_target DROP COLUMN IF EXISTS ord cascade;');
        self::$connection->execute('drop view if EXISTS jaga.stranas_target_instansi');
        self::$connection->execute('drop view if EXISTS jaga.report_ach');
    }
}