<?php 

use Phalcon\Db\Column;
use Phalcon\Db\Index;
use Phalcon\Db\Reference;
use Phalcon\Mvc\Model\Migration;

class TargetRankMigration_254 extends Migration
{
    public function up()
    {
        self::$connection->execute("
            --[1]
            drop table if exists jaga.stranas_ranking;
            create table if not exists jaga.stranas_ranking (
                generate_time timestamp,
                id_instansi int4 NULL,
                instansi varchar(100) NULL,
                id_tipe int4 NULL,
                tipe_instansi varchar(100) NULL,
                ach numeric NULL,
                jml_subaksi int8 NULL,
                jml_target int8 NULL,
                nilai_akhir float NULL
            );

            drop function IF EXISTS jaga.generate_ranking();
            CREATE OR REPLACE FUNCTION jaga.generate_ranking()        
              RETURNS TABLE (cnt bigint) as
              $$
              BEGIN
                  TRUNCATE TABLE jaga.stranas_ranking;
                  insert into jaga.stranas_ranking
                  select 
                    CURRENT_TIMESTAMP as generate_time,
                    a.id_instansi,
                    b.nama as instansi, 
                    b.id_tipe, 
                    c.nama as tipe_instansi,
                    a.ach,
                    a.jml_target,
                    a.jml_subaksi,
                    a.nilai_akhir
                  from 
                    (
                    select 
                      id_instansi, 
                      sum(nilai_ontarget + nilai_terhutang) / (count(id_target) * 100.0) as ach, 
                      count(distinct id_subaksi) as jml_subaksi, 
                      count(id_target) as jml_target, 
                      sum(nilai_ontarget + nilai_terhutang) as nilai_akhir
                    from jaga.target_instansi 
                    group by id_instansi 
                    ) a 
                    join jaga.instansi b on b.id = a.id_instansi
                    left join jaga.instansi_tipe c on c.id = b.id_tipe
                  order by ach desc
                  ;
                RETURN QUERY
                select count(1) as cnt from jaga.stranas_ranking;
              RETURN;
              END;    
            $$ LANGUAGE plpgsql;

            --[2]
            drop table if exists jaga.target_instansi_hist;
           
            create table if not exists jaga.target_instansi_hist (
              generate_time timestamp,
              id_instansi int4 NULL,
              id_target int4 NULL,
              id_subaksi int4 NULL,
              id_periode int4 NULL,
              id_question int4 NULL,
              id_survey int4 NULL,
              status int4 NULL,
              is_verified int4 NULL,
              instansi varchar(100) NULL,
              target text NULL,
              periode varchar(255) NULL,
              id_periode_berjalan int4 NULL,
              periode_berjalan varchar(255) NULL,
              nilai_ontarget int4 NULL,
              nilai_terakhir int4 NULL,
              nilai_max int4 NULL,
              nilai_akhir int4 NULL,
              subaksi text NULL,
              nilai_terhutang int4 NULL,
              is_completed int4 NULL,
              id_aksi int4 NULL,
              aksi varchar(255) NULL,
              id_fokus int4 NULL,
              fokus varchar(255) NULL,
              id_tipe int4 NULL, 
              tipe varchar(255) NULL
            );

            drop function IF EXISTS jaga.generate_historical();

            CREATE OR REPLACE FUNCTION jaga.generate_historical()        
                          RETURNS TABLE (cnt bigint) as
                          $$
                          BEGIN
                              TRUNCATE TABLE jaga.target_instansi_hist;
                             insert into jaga.target_instansi_hist 
                      select 
                                CURRENT_TIMESTAMP as generate_time,
                              id_instansi,
                              a.id_target,
                              a.id_subaksi,
                              id_periode,
                              id_question,
                              id_survey,
                              status,
                              is_verified,
                              instansi,
                              regexp_replace(a.target, E'[\\n\\r]+', ' ', 'g' ) as target,
                              periode,
                              id_periode_berjalan,
                              periode_berjalan,
                              nilai_ontarget,
                              nilai_terakhir,
                              nilai_max,
                              nilai_akhir,
                              regexp_replace(b.subaksi, E'[\\n\\r]+', ' ', 'g' ) as subaksi,
                              nilai_terhutang,
                              is_completed,
                              b.id_aksi,
                              regexp_replace(b.aksi, E'[\\n\\r]+', ' ', 'g' ) as aksi,
                              b.id_fokus,
                              regexp_replace(b.fokus, E'[\\n\\r]+', ' ', 'g' ) as fokus,
                              s.id_tipe, c.nama as tipe
                            from jaga.target_instansi a 
                              join jaga.target_hierarchy b on b.id_target = a.id_target 
                              join jaga.instansi s on s.id = a.id_instansi 
                              join jaga.instansi_tipe c on c.id = s.id_tipe; 
                            RETURN QUERY
                            select count(1) as cnt from jaga.target_instansi_hist;
                          RETURN;
                          END;    
                        $$ LANGUAGE plpgsql;
            --execute: select jaga.generate_historical()
        ");
    }
}