<?php

class KeluhanPemiluMigration_459 extends \Phalcon\Mvc\Model\Migration {
    public function up()
    {
        $this->down();

        self::$connection->execute("
            ALTER TABLE jaga.topik_diskusi_bansos ADD COLUMN IF NOT EXISTS subtype character varying(10);
        ");

        self::$connection->execute("
            SELECT setval('jaga.topik_diskusi_bansos_id_seq', COALESCE((SELECT MAX(id)+1 FROM jaga.topik_diskusi_bansos), 1), false);

            INSERT INTO jaga.topik_diskusi_bansos (deskripsi, type, view_order, subtype) VALUES ('Pemilu - Melakukan Politik Uang', 'PEMILU', 1, 'POLUANG');
            INSERT INTO jaga.topik_diskusi_bansos (deskripsi, type, view_order, subtype) VALUES ('Pemilu - Penyelenggara Menerima Suap', 'PEMILU', 2, 'SUAP');
        ");

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.objek_keluhan_pemilu
            (
                id serial primary key,
                tipe_objek character varying(10) NOT NULL,
                nama character varying(255) NOT NULL, 
                view_order int,
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6),
                deleted_at timestamp(6)
            );
        ");

        self::$connection->execute("
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Kebangkitan Bangsa', 1);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Gerakan Indonesia Raya', 2);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Demokrasi Indonesia Perjuangan', 3);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Golkar', 4);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Nasdem', 5);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Buruh', 6);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Gelombang Rakyat Indonesia', 7);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Keadilan Sejahtera', 8);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Kebangkitan Nusantara', 9);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Hati Nurani Rakyat', 10);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Garda Perubahan Indonesia', 11);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Amanat Nasional', 12);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Bulan Bintang', 13);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Demokrat', 14);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Solidaritas Indonesia', 15);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Perindo', 16);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Persatuan Pembangunan', 17);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Nangroe Aceh', 18);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Generasi Atjeh Beusaboh Tha''at Dan Taqwa', 19);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Darul Aceh', 20);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Aceh', 21);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Adil Sejahtera Aceh', 22);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Soliditas Independent Rakyat Aceh', 23);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('POLUANG', 'Partai Ummat', 24);
            
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('SUAP', 'KPU', 1);
            INSERT INTO jaga.objek_keluhan_pemilu (tipe_objek, nama, view_order) VALUES ('SUAP', 'Bawaslu', 2);
        ");

        self::$connection->execute("
            CREATE TABLE IF NOT EXISTS jaga.diskusi_keluhan_pemilu
            (
                id serial primary key,
                id_diskusi int NOT NULL,
                id_diskusi_bansos int NOT NULL,
                id_objek_pemilu int NOT NULL,
                tipe_objek_pemilu character varying(10) NOT NULL,
                jenis_gratifikasi character varying(50), 
                keterangan_gratifikasi text,
                total_nilai_gratifikasi numeric,
                isresponded int,
                respond_at timestamp(6),
                created_at timestamp(6) DEFAULT now(),
                updated_at timestamp(6),
                deleted_at timestamp(6)
            );
        ");

        self::$connection->execute("
            CREATE VIEW jaga.view_keluhan AS 
            SELECT
                diskusi_bansos.id AS id,
                diskusi_bansos.status AS status,
                diskusi_bansos.created_at AS created_at,
                Origin.id AS id_instansi_asal,
                Origin.nama AS instansi_asal,
                instansi.id AS id_instansi_respon,
                instansi.nama AS instansi_respon,
                topik_diskusi_bansos.type AS type,
                topik_diskusi_bansos.subtype AS subtype,
                topik_diskusi_bansos.deskripsi AS topik,
                topik_diskusi_bansos.id AS id_topik,
                um_user.um_user_name AS pelapor,
                NIK.nik,
                Phone.phone,
                diskusi.id AS id_diskusi,
                diskusi.title AS title,
                diskusi.content AS content,
                diskusi.uuid AS diskusi_uuid,
                diskusi.link_terkait AS link_terkait,
                diskusi_bansos.deleted_at AS delete_date,
                diskusi.is_deleted AS is_deleted,
                Confirmed.created_at AS confirmed_date,
                Done.created_at AS done_date,
                Closed.created_at AS closed_date,
                Response.created_at AS response_date,
                OriginProvinsi.id_provinsi AS id_provinsi_origin,
                REPLACE ( OriginProvinsi.nama, 'Pemerintah ', '' ) AS provinsi_origin,
                Provinsi.id_provinsi AS id_provinsi,
                REPLACE ( Provinsi.nama, 'Pemerintah ', '' ) AS provinsi,
                diskusi_bansos.isresponded,
                diskusi_objek.nama_objek,
                diskusi_bansos.source,
		        dkp.id_objek_pemilu,
		        dkp.tipe_objek_pemilu,
		        okp.nama AS nama_objek_pemilu,
		        dkp.jenis_gratifikasi,
		        (
                  SELECT STRING_AGG(
                    CASE
                      WHEN elem = '1' THEN 'Barang'
                      WHEN elem = '2' THEN 'Uang'
                      WHEN elem = '3' THEN 'Lainnya'
                    END, ';'
                  )
                  FROM unnest(string_to_array(dkp.jenis_gratifikasi, ';')) AS elem
                ) AS jenis_gratifikasi_name,
		        dkp.keterangan_gratifikasi,
		        dkp.total_nilai_gratifikasi,
		        dkp.isresponded AS pemilu_isresponded,
		        dkp.respond_at AS pemilu_respond_at
            FROM jaga.diskusi_bansos
            INNER JOIN jaga.diskusi ON diskusi_bansos.id_diskusi = diskusi.id
            INNER JOIN jaga.um_user ON diskusi.user_id = um_user.um_id
            INNER JOIN jaga.instansi AS Origin ON diskusi_bansos.id_instansi_origin = Origin.id
            INNER JOIN jaga.instansi ON diskusi_bansos.id_instansi = instansi.id
            LEFT JOIN jaga.instansi AS OriginProvinsi ON OriginProvinsi.id_tipe = 1 AND OriginProvinsi.id_provinsi = Origin.id_provinsi
            LEFT JOIN jaga.instansi AS Provinsi ON Provinsi.id_tipe = 1 AND Provinsi.id_provinsi = instansi.id_provinsi
            LEFT JOIN jaga.topik_diskusi_bansos ON diskusi_bansos.id_topik = topik_diskusi_bansos.id
            LEFT JOIN jaga.diskusi_objek ON diskusi.ID = diskusi_objek.diskusi_id
            LEFT JOIN (
                SELECT id_diskusi_bansos, max(created_at) as created_at FROM jaga.diskusi_bansos_history h 
                WHERE h.status = 2 AND h.deleted_at IS NULL GROUP BY id_diskusi_bansos
            ) Confirmed ON Confirmed.id_diskusi_bansos = diskusi_bansos.id
            LEFT JOIN (
                SELECT id_diskusi_bansos, max(created_at) as created_at FROM jaga.diskusi_bansos_history h 
                WHERE h.status = 4 AND h.deleted_at IS NULL GROUP BY id_diskusi_bansos
            ) Done ON Done.id_diskusi_bansos = diskusi_bansos.id
            LEFT JOIN (
                SELECT id_diskusi_bansos, max(created_at) as created_at FROM jaga.diskusi_bansos_history h 
                WHERE h.status IN (5,6) AND h.deleted_at IS NULL GROUP BY id_diskusi_bansos
            ) Closed ON Closed.id_diskusi_bansos = diskusi_bansos.id
            LEFT JOIN (
                SELECT h.id_diskusi_bansos, min(h.created_at) as created_at FROM jaga.diskusi_bansos_history h
                LEFT JOIN jaga.users_roles ur ON ur.user_id = h.id_user
                LEFT JOIN jaga.roles_scopes rs ON rs.role_id = ur.role_id
                LEFT JOIN jaga.scopes s ON s.id = rs.scope_id
                WHERE s.name IN ('bansos.confirm_all', 'bansos.confirm_assigned') AND h.deleted_at IS NULL AND h.status <> 1 GROUP BY id_diskusi_bansos
            ) Response ON Response.id_diskusi_bansos = diskusi_bansos.id
            LEFT JOIN (
                SELECT n.id_diskusi_bansos, string_agg(n.nik, ';') as nik FROM jaga.diskusi_bansos_nik n GROUP BY n.id_diskusi_bansos
            ) NIK ON NIK.id_diskusi_bansos = diskusi_bansos.id
            LEFT JOIN (
                SELECT id, string_agg(phone, ';') as phone FROM jaga.diskusi_bansos GROUP BY id
            ) Phone ON Phone.id = diskusi_bansos.id
            LEFT JOIN jaga.diskusi_keluhan_pemilu dkp ON diskusi.id = dkp.id_diskusi
            LEFT JOIN jaga.objek_keluhan_pemilu okp ON okp.id = dkp.id_objek_pemilu
            ;
        ");

        self::$connection->execute("
            INSERT INTO jaga.instansi (nama, show_to_public, is_stranas, id_tipe, flag_dana_desa, flag_pak) VALUES ('Dewan Kehormatan Penyelenggara Pemilu', 1, 0, 3, 0, false);
         
            INSERT INTO jaga.scopes(name, label, description) VALUES ('keluhan.pemilu.monitoring', 'Melakukan monitoring keluhan Pemilu', 'Melakukan monitoring keluhan Pemilu');

            INSERT INTO jaga.roles(name, label, description) VALUES ('observer_keluhan_pemilu', 'Observer Keluhan Pemilu', 'Observer Keluhan Pemilu');
    
            INSERT INTO jaga.roles_scopes(role_id, scope_id) values(
				(select id from jaga.roles where name = 'observer_keluhan_pemilu'), (select id from jaga.scopes where name = 'keluhan.pemilu.monitoring') 
            );
            INSERT INTO jaga.roles_scopes(role_id, scope_id) values(
				(select id from jaga.roles where name = 'observer_keluhan_pemilu'), (select id from jaga.scopes where name = 'discussion.create') 
            );
            INSERT INTO jaga.roles_scopes(role_id, scope_id) values(
				(select id from jaga.roles where name = 'observer_keluhan_pemilu'), (select id from jaga.scopes where name = 'discussion.react') 
            );
        ");
    }

    public function down()
    {
        self::$connection->execute("
            DELETE FROM jaga.instansi WHERE nama = 'Dewan Kehormatan Penyelenggara Pemilu';
    
            DELETE FROM jaga.roles_scopes WHERE 
            role_id = (select id from jaga.roles where name = 'observer_keluhan_pemilu');  

            DELETE FROM jaga.scopes WHERE name = 'keluhan.pemilu.monitoring';

            DELETE FROM jaga.roles WHERE name = 'observer_keluhan_pemilu';
        ");

        self::$connection->execute("DROP VIEW IF EXISTS jaga.view_keluhan;");

        self::$connection->execute("DROP TABLE IF EXISTS jaga.diskusi_keluhan_pemilu;");

        self::$connection->execute("DROP TABLE IF EXISTS jaga.objek_keluhan_pemilu;");

        self::$connection->execute("
            DELETE FROM jaga.topik_diskusi_bansos WHERE type = 'PEMILU';
            SELECT setval('jaga.topik_diskusi_bansos_id_seq', COALESCE((SELECT MAX(id)+1 FROM jaga.topik_diskusi_bansos), 1), false);
        ");

        self::$connection->execute("ALTER TABLE jaga.topik_diskusi_bansos DROP COLUMN IF EXISTS subtype;");

    }
}