<?php

use Phalcon\Mvc\Model\Migration;

/**
 * Class StranasNewHierarchyOrderMigration_312
 * add hierarchy order untuk fokus
 */
class StranasNewHierarchyOrderMigration_312 extends Migration
{
    public function up()
    {
        self::$connection->execute(
            '
            alter table jaga.str_hierarchy add column ord int;
            '
        );

        self::$connection->execute(
            '
            DROP VIEW IF EXISTS jaga.hierachy_by_level;
            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
                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;
            '
        );
    }

    // public function down()
    // {
    // }
}