<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute('
            CREATE VIEW jaga.hierachy_by_level as
            select 
                    p.id as id_periode,
                    a.id as id_level1, 
                    a.nama as level1,
                    a.ord,
                    b.id as id_level2,
                    b.nama as level2,
                    c.id as id_level3,
                    c.nama as level3,
                    d.id as id_level4,
                    d.nama as level4,
                    p.level_count,
                    a.ord as o1,
                    b.ord as o2,
                    c.ord as o3,
                    d.ord as o4
                from jaga.str_hierarchy a
                    join jaga.str_periode p on p.id=a.id_periode 
                    join (select * from jaga.str_hierarchy where deleted_at is null) b on b.id_parent=a.id
                    join (select * from jaga.str_hierarchy where deleted_at is null) c on c.id_parent=b.id
                    left join (select * from jaga.str_hierarchy where deleted_at is null) d on d.id_parent=c.id
                where a.id_level = 1 and a.deleted_at  is null;
        ');

        self::$connection->execute('
            CREATE TABLE jaga.str_target_guide (
                id serial PRIMARY KEY,
                id_target int not null,
                aspek text not null,
                bobot int,
                catatan text,
                ord int,
                CONSTRAINT fk_str_guide
                    FOREIGN KEY(id_target) 
                    REFERENCES jaga.str_target(id)
            );'
        );
    }

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