<?php

use Phalcon\Mvc\Model\Migration;

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

        self::$connection->execute('
            CREATE TABLE jaga.str_periode (
                id serial PRIMARY KEY,
                yearstart int UNIQUE,
                is_active boolean default false,
                level1 varchar(255) not null,
                level2 varchar(255) not null,
                level3 varchar(255),
                level4 varchar(255),
                level_count int not null,
                created_by int,
                created_at timestamp default now(),
                updated_by int,
                updated_at timestamp default null,
                deleted_by int,
                deleted_at timestamp default null
            )'
        );
        self::$connection->execute("insert into jaga.str_periode (yearstart, level1, level2, level3, level_count, is_active) values ('2019', 'Fokus', 'Aksi', 'Subaksi', 3, true);");

        self::$connection->execute('
            CREATE TABLE jaga.str_target_master (
                id serial PRIMARY KEY, -- id untuk log
                id_periode int not null,
                id_target int not null,
                id_triwulan int not null default 1,
                -- flattening
                id_level1 int,
                id_level2 int,
                id_level3 int,
                id_level4 int,
                -- end 
                id_instansi int not null,
                -- periode + TW
                -- nilai klaim, tetap dikosongkan jika dilaporkan oleh verifikator
                klaim_tw1 int,
                klaim_tw2 int,
                klaim_tw3 int,
                klaim_tw4 int,
                klaim_tw5 int,
                klaim_tw6 int,
                klaim_tw7 int,
                klaim_tw8 int,
                -- nilai verifikasi
                nilai_tw1 int,
                nilai_tw2 int,
                nilai_tw3 int,
                nilai_tw4 int,
                nilai_tw5 int,
                nilai_tw6 int,
                nilai_tw7 int,
                nilai_tw8 int,
                -- nilai_ontarget_tw
                -- periode akhir pelaporan
                -- nilai akhir pelaporan
                created_by int,
                created_at timestamp default now(),
                updated_by int,
                updated_at timestamp default null,
                deleted_by int,
                deleted_at timestamp default null,
                CONSTRAINT fk_str_periode
                    FOREIGN KEY(id_periode) 
                    REFERENCES jaga.str_periode(id)
            );'
        );

        self::$connection->execute('
            CREATE TABLE jaga.str_triwulan (
                id serial PRIMARY KEY,
                triwulan int not null,
                label varchar(50) not null,
                started_at timestamp,
                ended_at timestamp,
                verification_started_at timestamp,
                verification_ended_at timestamp
            )'
        );
        $this->batchInsert('str_triwulan', [
            'id',
            'triwulan',
            'label',
            'started_at',
            'ended_at',
            'verification_started_at',
            'verification_ended_at'
        ]);

        self::$connection->execute('
            CREATE TABLE jaga.str_periode_triwulan (
                id serial PRIMARY KEY,
                id_periode int not null,
                id_triwulan int not null,
                label varchar(50) not null,
                started_at timestamp,
                ended_at timestamp,
                verification_started_at timestamp,
                verification_ended_at timestamp,
                updated_by int,
                updated_at timestamp default null
            )'
        );

        // create new periode
        self::$connection->execute("
            insert into jaga.str_periode_triwulan (id_periode, id_triwulan, label, started_at,ended_at,verification_started_at,verification_ended_at)
                select 
                    1 id_periode,
                    id as id_triwulan,
                    label,
                    case when id < 5 then started_at + MAKE_INTERVAL(YEARS := 2019 - EXTRACT(YEAR FROM started_at)::INTEGER) 
                      else started_at + MAKE_INTERVAL(YEARS := 2020 - EXTRACT(YEAR FROM started_at)::INTEGER) end as started_at,
                    case when id < 5 then ended_at + MAKE_INTERVAL(YEARS := 2019 - EXTRACT(YEAR FROM ended_at)::INTEGER) 
                      else ended_at + MAKE_INTERVAL(YEARS := 2020 - EXTRACT(YEAR FROM ended_at)::INTEGER) end as ended_at,
                    case when id < 5 then verification_started_at + MAKE_INTERVAL(YEARS := 2019 - EXTRACT(YEAR FROM verification_started_at)::INTEGER) 
                      else verification_started_at + MAKE_INTERVAL(YEARS := 2020 - EXTRACT(YEAR FROM verification_started_at)::INTEGER) end as verification_started_at,
                    case when id < 5 then verification_ended_at + MAKE_INTERVAL(YEARS := 2019 - EXTRACT(YEAR FROM verification_ended_at)::INTEGER) 
                      else verification_ended_at + MAKE_INTERVAL(YEARS := 2020 - EXTRACT(YEAR FROM verification_ended_at)::INTEGER) end as verification_ended_at
                from jaga.str_triwulan
                order by triwulan;
        ");

        self::$connection->execute('
            CREATE TABLE jaga.str_hierarchy (
                id serial PRIMARY KEY,
                id_periode int,
                id_parent int,
                id_level int,
                kode varchar(255),
                nama text not null,
                keterangan text,
                created_by int,
                created_at timestamp default now(),
                updated_by int,
                updated_at timestamp default null,
                deleted_by int,
                deleted_at timestamp default null,
                CONSTRAINT fk_str_periode
                    FOREIGN KEY(id_periode) 
                    REFERENCES jaga.str_periode(id)
            );'
        );
        // view hierarcy untuk dimasukkan ke dalam target
        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,
                    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'
        );

        self::$connection->execute('
                CREATE VIEW jaga.new_target_instansi as
                    select a.id as id_master, a.id_target, a.id_instansi, i.nama
                    from jaga.str_target_master a 
                        join jaga.instansi i on i.id=a.id_instansi'
        );

        self::$connection->execute('
            CREATE TABLE jaga.str_target (
                id serial PRIMARY KEY,
                id_periode int not null,
                id_level1 int,
                id_level2 int,
                id_level3 int,
                id_level4 int,
                --
                nama text not null,
                info_nilai text,
                info_lampiran text,
                --
                created_by int,
                created_at timestamp default now(),
                updated_by int,
                updated_at timestamp default null,
                deleted_by int,
                deleted_at timestamp default null,
                CONSTRAINT fk_str_periode
                    FOREIGN KEY(id_periode) 
                    REFERENCES jaga.str_periode(id)
            );'
        );
        self::$connection->execute('
            CREATE TABLE jaga.str_target_activities (
                id serial PRIMARY KEY,
                id_master int not null,
                name text not null,
                datadukung text,
                is_submit boolean default false,
                is_verify boolean default false,
                CONSTRAINT fk_str_activities
                    FOREIGN KEY(id_master) 
                    REFERENCES jaga.str_target_master(id)
            );'
        );

        self::$connection->execute('
            CREATE TABLE jaga.str_target_log (
                id serial PRIMARY KEY, -- id untuk log
                --id_periode int not null, -- ini perlu gak ya
                id_triwulan int not null, -- ini perlu gak ya
                id_master int not null,
                --
                keterangan text,
                nilai int,
                keterangan_verification text,
                nilai_verification int,
                --
                created_by int,
                created_at timestamp default now(),
                verified_by int,
                verified_at timestamp,
                CONSTRAINT fk_str_target_master
                    FOREIGN KEY(id_master) 
                    REFERENCES jaga.str_target_master(id)
            );'
        );

        self::$connection->execute('
            CREATE TABLE jaga.str_target_log_comment (
                id serial PRIMARY KEY, -- id untuk log
                id_log int not null, -- ini perlu gak ya
                --
                comment text,
                --
                created_by int,
                created_at timestamp default now(),
                updated_by int,
                updated_at timestamp,
                CONSTRAINT fk_str_target_log
                    FOREIGN KEY(id_log) 
                    REFERENCES jaga.str_target_log(id)
                );'
        );  

        self::$connection->execute('
            CREATE TABLE jaga.str_target_attachment (
                id serial PRIMARY KEY, -- id untuk log
                id_log int not null, -- ini perlu gak ya
                --
                tipe int4 NULL DEFAULT 0,
                id_attachment int4 REFERENCES jaga.attachments ON DELETE cascade,
                --
                created_by int,
                created_at timestamp default now(),
                CONSTRAINT fk_str_target_log
                    FOREIGN KEY(id_log) 
                    REFERENCES jaga.str_target_log(id)
                );'
        );  

        self::$connection->execute('
            CREATE TABLE jaga.str_target_comment_attachment (
                id serial PRIMARY KEY, -- id untuk log
                id_comment int not null REFERENCES jaga.str_target_log_comment ON DELETE cascade,
                --
                id_attachment int4 REFERENCES jaga.attachments ON DELETE cascade,
                --
                created_by int,
                created_at timestamp default now(),
                CONSTRAINT fk_str_target_log
                    FOREIGN KEY(id_comment) 
                    REFERENCES jaga.str_target_log_comment(id)
                );'
        );  

        self::$connection->execute("
            create view jaga.log_comment as 
            select 
              id_master, 
              max(case when id_triwulan=1 then keterangan end) as cmt1, max(case when id_triwulan=1 then keterangan_verification end) as ver1,
              max(case when id_triwulan=2 then keterangan end) as cmt2, max(case when id_triwulan=2 then keterangan_verification end) as ver2,
              max(case when id_triwulan=3 then keterangan end) as cmt3, max(case when id_triwulan=3 then keterangan_verification end) as ver3,
              max(case when id_triwulan=4 then keterangan end) as cmt4, max(case when id_triwulan=4 then keterangan_verification end) as ver4,
              max(case when id_triwulan=5 then keterangan end) as cmt5, max(case when id_triwulan=5 then keterangan_verification end) as ver5,
              max(case when id_triwulan=6 then keterangan end) as cmt6, max(case when id_triwulan=6 then keterangan_verification end) as ver6,
              max(case when id_triwulan=7 then keterangan end) as cmt7, max(case when id_triwulan=7 then keterangan_verification end) as ver7,
              max(case when id_triwulan=8 then keterangan end) as cmt8, max(case when id_triwulan=8 then keterangan_verification end) as ver8
            from (
            select * from 
            (
            select 
              id_master, 
              id_triwulan, 
              regexp_replace(keterangan, E'[\\n\\r]+', ' ', 'g' ) as keterangan, 
              regexp_replace(keterangan_verification, E'[\\n\\r]+', ' ', 'g' ) as keterangan_verification, 
              row_number() over (partition by id_master, id_triwulan order by created_at desc, verified_at desc) ox
            from jaga.str_target_log
            ) aa
            where ox =1
            ) bb
            group by id_master
            ");

        self::$connection->execute('
            create view jaga.other_nilai as 
            select
                id_target,
                max(coalesce(nilai_tw1,0)) othermax_1,
                max(coalesce(nilai_tw2,0)) othermax_2,
                max(coalesce(nilai_tw3,0)) othermax_3,
                max(coalesce(nilai_tw4,0)) othermax_4,
                max(coalesce(nilai_tw5,0)) othermax_5,
                max(coalesce(nilai_tw6,0)) othermax_6,
                max(coalesce(nilai_tw7,0)) othermax_7,
                max(coalesce(nilai_tw8,0)) othermax_8,
                min(coalesce(nilai_tw1,0)) othermin_1,
                min(coalesce(nilai_tw2,0)) othermin_2,
                min(coalesce(nilai_tw3,0)) othermin_3,
                min(coalesce(nilai_tw4,0)) othermin_4,
                min(coalesce(nilai_tw5,0)) othermin_5,
                min(coalesce(nilai_tw6,0)) othermin_6,
                min(coalesce(nilai_tw7,0)) othermin_7,
                min(coalesce(nilai_tw8,0)) othermin_8
            from jaga.str_target_master
            group by id_target
        ');

        self::$connection->execute('
                create view jaga.report_ach as 
                select 
                    a.id,
                    a.id as id_master,
                    l1.nama as level1,
                    l2.nama as level2,
                    l3.nama as level3,
                    l4.nama as level4,
                    i.nama as instansi,
                    it.nama as tipe_instansi,
                    t.nama as target,
                    t.info_nilai,
                    t.info_lampiran,
                    case when greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8) = 100 then 1 else 0 end as is_completed,
                    greatest(nilai_tw1,nilai_tw2,nilai_tw3,nilai_tw4,nilai_tw5,nilai_tw6,nilai_tw7,nilai_tw8) as nilai_verification,
                    case when coalesce(klaim_tw1,0)=0 then 0 else 1 end as is_submit1, 
                    case when coalesce(klaim_tw2,0)=0 then 0 else 1 end as is_submit2, 
                    case when coalesce(klaim_tw3,0)=0 then 0 else 1 end as is_submit3, 
                    case when coalesce(klaim_tw4,0)=0 then 0 else 1 end as is_submit4, 
                    case when coalesce(klaim_tw5,0)=0 then 0 else 1 end as is_submit5, 
                    case when coalesce(klaim_tw6,0)=0 then 0 else 1 end as is_submit6, 
                    case when coalesce(klaim_tw7,0)=0 then 0 else 1 end as is_submit7, 
                    case when coalesce(klaim_tw8,0)=0 then 0 else 1 end as is_submit8,
                    case when coalesce(nilai_tw1,0)=0 then 0 else 1 end as is_verify1, 
                    case when coalesce(nilai_tw2,0)=0 then 0 else 1 end as is_verify2, 
                    case when coalesce(nilai_tw3,0)=0 then 0 else 1 end as is_verify3, 
                    case when coalesce(nilai_tw4,0)=0 then 0 else 1 end as is_verify4, 
                    case when coalesce(nilai_tw5,0)=0 then 0 else 1 end as is_verify5, 
                    case when coalesce(nilai_tw6,0)=0 then 0 else 1 end as is_verify6, 
                    case when coalesce(nilai_tw7,0)=0 then 0 else 1 end as is_verify7, 
                    case when coalesce(nilai_tw8,0)=0 then 0 else 1 end as is_verify8,
                    a.id_instansi, a.id_target,a.id_periode,a.id_level1,a.id_level2,a.id_level3,a.id_level4
                from jaga.str_target_master a 
                    join jaga.str_hierarchy l1 on l1.id = a.id_level1 
                    left join jaga.str_hierarchy l2 on l2.id = a.id_level2
                    left join jaga.str_hierarchy l3 on l3.id = a.id_level3
                    left join jaga.str_hierarchy l4 on l4.id = a.id_level4
                    join jaga.instansi i on i.id=a.id_instansi
                    join jaga.instansi_tipe it on it.id=i.id_tipe
                    join jaga.str_target t on t.id=a.id_target;'
        );

        // MIGRATE SEQUENCE
        // [1]
        self::$connection->execute("
            with lvl3 as (
            select 
              id as id_level3, 
              id_aksi as id_parent,
              nama, 
              case when keterangan='-' then null else keterangan end as keterangan
            from jaga.stranas_subaksi
            ),
            maxlevel3 as (
            select max(id_level3) as add_level2 from lvl3
            ),
            lvl2 as (
            select 
              id as id_level2, 
              id_fokus as id_parent,
              nama, 
              case when keterangan='-' then null else keterangan end as keterangan
            from jaga.stranas_aksi
            ),
            maxlevel2 as (
            select max(id_level2)+max(add_level2) as add_level1 from lvl2, maxlevel3 
            ),
            tl3 as (
            select id_level3 as id,id_parent+add_level2 as id_parent,nama,keterangan, 3 as id_level
            from lvl3, maxlevel3
            ),
            tl2 as (
            select id_level2+add_level2 as id,id_parent+add_level1 as id_parent,nama,keterangan, 2 as id_level
            from lvl2, maxlevel3, maxlevel2
            ),
            tl1 as (
            select 
              id+add_level1 as id, 
              0 as id_parent,
              nama, 
              case when keterangan='-' then null else keterangan end as keterangan, 1 as id_level
            from jaga.stranas_fokus, maxlevel2
            ),
            tall as (
            select * from tl3
            union all 
            select * from tl2
            union all 
            select * from tl1
            )
            insert into jaga.str_hierarchy 
            (id,id_periode,id_parent,nama,keterangan,id_level)
            select id,1 id_periode,id_parent,nama,keterangan,id_level 
            from tall;
        "
        );

        // [2]
        self::$connection->execute('
            insert into jaga.str_target (id,id_periode,id_level1,id_level2,id_level3,nama,info_nilai,info_lampiran)
            select id, 1 as id_periode, b.id_level1, b.id_level2, b.id_level3,
            keterangan as nama, info_nilai, info_lampiran 
            from jaga.stranas_target a 
              left join jaga.hierachy_by_level b on b.id_level3 = a.id_subaksi; 
        '
        );

        // [3]
        self::$connection->execute('
            with t1 as (
              select 
                id_instansi, 
                id_target, 
                ori_id_periode, 
                id_periode,
                nilai_klaim, nilai_verification 
              from jaga.target_log 
            ),
            t2 as (
            select 
              a.id_instansi, a.id_target, 
              sum(case when id_periode = 1 then nilai_klaim else 0 end) as klaim_tw1, sum(case when id_periode = 1 then nilai_verification else 0 end) as nilai_tw1,
              sum(case when id_periode = 2 then nilai_klaim else 0 end) as klaim_tw2, sum(case when id_periode = 2 then nilai_verification else 0 end) as nilai_tw2,
              sum(case when id_periode = 3 then nilai_klaim else 0 end) as klaim_tw3, sum(case when id_periode = 3 then nilai_verification else 0 end) as nilai_tw3,
              sum(case when id_periode = 4 then nilai_klaim else 0 end) as klaim_tw4, sum(case when id_periode = 4 then nilai_verification else 0 end) as nilai_tw4,
              sum(case when id_periode = 5 then nilai_klaim else 0 end) as klaim_tw5, sum(case when id_periode = 5 then nilai_verification else 0 end) as nilai_tw5,
              sum(case when id_periode = 6 then nilai_klaim else 0 end) as klaim_tw6, sum(case when id_periode = 6 then nilai_verification else 0 end) as nilai_tw6,
              sum(case when id_periode = 7 then nilai_klaim else 0 end) as klaim_tw7, sum(case when id_periode = 7 then nilai_verification else 0 end) as nilai_tw7,
              sum(case when id_periode = 8 then nilai_klaim else 0 end) as klaim_tw8, sum(case when id_periode = 8 then nilai_verification else 0 end) as nilai_tw8
            from t1 a
            group by 
              a.id_instansi, a.id_target
            )
            insert into jaga.str_target_master 
            (id_instansi, id_target, id_periode, id_triwulan, id_level1, id_level2, id_level3, id_level4,
              klaim_tw1, 
              klaim_tw2, 
              klaim_tw3, 
              klaim_tw4, 
              klaim_tw5, 
              klaim_tw6, 
              klaim_tw7, 
              klaim_tw8, 
              nilai_tw1,
              nilai_tw2,
              nilai_tw3,
              nilai_tw4,
              nilai_tw5,
              nilai_tw6,
              nilai_tw7,
              nilai_tw8
            )
            select 
              a.id_instansi, 
              a.id_target, 
              1 as id_periode,
              a.id_periode as id_triwulan,
              b.id_level1,
              b.id_level2,
              b.id_level3,
              b.id_level4,
              c.klaim_tw1,
              c.klaim_tw2,
              c.klaim_tw3,
              c.klaim_tw4,
              c.klaim_tw5,
              c.klaim_tw6,
              c.klaim_tw7,
              c.klaim_tw8,
              c.nilai_tw1,
              c.nilai_tw2,
              c.nilai_tw3,
              c.nilai_tw4,
              c.nilai_tw5,
              c.nilai_tw6,
              c.nilai_tw7,
              c.nilai_tw8
            from jaga.target_instansi a 
              join jaga.str_target b on b.id = a.id_target and b.id_periode=1
              left join t2 c on c.id_instansi = a.id_instansi and c.id_target=a.id_target;
        '
        );

        // [4]
        self::$connection->execute('
            WITH t0 AS (
             SELECT stranas_survey_answer.id,
                stranas_survey_answer.id_survey,
                stranas_survey_answer.id_question,
                stranas_survey_answer.nilai,
                stranas_survey_answer.keterangan,
                stranas_survey_answer.nilai_verification,
                stranas_survey_answer.keterangan_verification,
                stranas_survey_answer.status,
                stranas_survey_answer.created_at,
                stranas_survey_answer.updated_at,
                stranas_survey_answer.deleted_at,
                stranas_survey_answer.created_by,
                stranas_survey_answer.updated_by,
                stranas_survey_answer.verified_by,
                stranas_survey_answer.verified_at,
                row_number() OVER (PARTITION BY stranas_survey_answer.id_survey, stranas_survey_answer.id_question ORDER BY stranas_survey_answer.verified_at DESC) AS selected
               FROM jaga.stranas_survey_answer
            ), tver AS (
             SELECT t0.id,
                t0.id_survey,
                t0.id_question,
                t0.nilai,
                t0.keterangan,
                t0.nilai_verification,
                t0.keterangan_verification,
                t0.status,
                t0.created_at,
                t0.updated_at,
                t0.deleted_at,
                t0.created_by,
                t0.updated_by,
                t0.verified_by,
                t0.verified_at,
                t0.selected
               FROM t0
              WHERE t0.selected = 1
            ), t1 AS (
             SELECT ss.id_instansi_satker AS id_instansi,
                ii.nama AS instansi,
                p.nama AS periode,
                ta.keterangan AS target,
                ta.id_subaksi,
                COALESCE(c.status, 0) AS status,
                c.created_by,
                c.created_at,
                c.updated_by,
                c.updated_at,
                c.verified_by,
                c.verified_at,
                COALESCE(c.nilai, 0) AS nilai_klaim,
                COALESCE(c.nilai_verification, 0) AS nilai_verification,
                c.keterangan,c.keterangan_verification,
                q.id_target,
                q.id AS id_question,
                ss.id AS id_survey,
                p.id AS id_periode,
                c.id AS id_answer,
                row_number() OVER (PARTITION BY q.id_target, ss.id_instansi_satker ORDER BY p.id) AS target_period,
                row_number() OVER (PARTITION BY q.id_target, ss.id_instansi_satker ORDER BY p.id DESC) AS last_period
               FROM jaga.stranas_survey ss
                 JOIN jaga.stranas_periode p ON ss.id_periode = p.id
                 JOIN jaga.stranas_survey_template t ON ss.id_survey_template = t.id
                 JOIN jaga.stranas_survey_template_question q ON q.id_survey_template = t.id
                 LEFT JOIN tver c ON c.id_survey = ss.id AND c.id_question = q.id_target
                 JOIN ( SELECT stranas_target.id,
                        stranas_target.id_ukuran_keberhasilan,
                        stranas_target.keterangan,
                        stranas_target.info_nilai,
                        stranas_target.info_lampiran,
                        stranas_target.created_at,
                        stranas_target.updated_at,
                        stranas_target.deleted_at,
                        stranas_target.created_by,
                        stranas_target.updated_by,
                        stranas_target.presentase_bobot,
                        stranas_target.id_subaksi
                       FROM jaga.stranas_target
                      WHERE stranas_target.deleted_at IS NULL) ta ON ta.id = q.id_target
                 JOIN jaga.instansi ii ON ii.id = ss.id_instansi_satker
            ),
            tmaster as (
            select id as id_master, id_instansi, id_target from jaga.str_target_master s
            )
            insert into jaga.str_target_log (id_master, id_triwulan, nilai, nilai_verification, keterangan, keterangan_verification, created_at, created_by, --updated_at, updated_by, 
                verified_at, verified_by)
            select a.id_master, 
              b.id_periode as id_triwulan, b.nilai_klaim as nilai, 
              b.nilai_verification, b.keterangan, b.keterangan_verification, b.created_at, b.created_by, --b.updated_at, b.updated_by,
              b.verified_at, b.verified_by
            from tmaster a 
              left join t1 b on b.id_instansi = a.id_instansi and b.id_target=a.id_target 
              where b.id_periode is not null;
        '
        );

        // [5]
        self::$connection->execute("
            SELECT setval('jaga.str_hierarchy_id_seq', 2000, true);
            SELECT setval('jaga.str_target_id_seq', 2600, true);
            SELECT setval('jaga.stranas_survey_answer_id_seq', 2600, true);
        "
        );
    }

    public function down()
    {
        self::$connection->execute("
            DROP VIEW IF EXISTS jaga.log_comment;
            DROP VIEW IF EXISTS jaga.report_ach;
            DROP VIEW IF EXISTS jaga.hierachy_by_level;
            DROP VIEW IF EXISTS jaga.new_target_instansi;
            DROP TABLE IF EXISTS jaga.str_periode cascade;
            DROP TABLE IF EXISTS jaga.str_target_attachment;
            DROP TABLE IF EXISTS jaga.str_target_comment_attachment;
            DROP TABLE IF EXISTS jaga.str_target_activities cascade;
            DROP TABLE IF EXISTS jaga.str_target_log cascade;
            DROP TABLE IF EXISTS jaga.str_target_log_comment cascade;
            DROP TABLE IF EXISTS jaga.str_target_master cascade;
            DROP TABLE IF EXISTS jaga.str_target;
            DROP TABLE IF EXISTS jaga.str_hierarchy_level;
            DROP TABLE IF EXISTS jaga.str_hierarchy cascade;
            DROP TABLE IF EXISTS jaga.str_periode_triwulan;
            DROP TABLE IF EXISTS jaga.str_triwulan;
        ");
    }
}

// create view vhierarchy as 
// select a.*, 
//     case 
//         when a.id_level = 1 then b.level1
//         when a.id_level = 2 then b.level2
//         when a.id_level = 3 then b.level3
//     else b.level4
//     end as levell
// from jaga.str_hierarchy a join jaga.str_periode b on b.id = a.id_periode 

// ALTER TABLE jaga.str_target_log  RENAME COLUMN id_target_master TO id_master;
// update view jaga.report_ach