<?php

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

class TargetHistMigration_254 extends Migration
{
  public function up()
  {
    self::$connection->execute("
            --[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 generate_historical();

            CREATE OR REPLACE FUNCTION 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()
        ");
  }
}