<?php

class ViewKeluhanMigration_487 extends \Phalcon\Mvc\Model\Migration {
    public function up()
    {
        self::$connection->execute("DROP VIEW IF EXISTS jaga.view_keluhan;");

        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,
                diskusi.is_anonymous,
                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
            ;
        ");
    }

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

        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
            ;
        ");
    }
}