<?php

class PancekDashboardController extends BaseController
{
    #region public
    /**
     * untuk akses publik, hanya menampilkan formulir dengan status NOT IN (NEW, DRAFT) dan instansi dengan semua status
     * untuk akses admin, dapat menampilkan formulir dengan semua status dan instansi dengan status sesuai param
     * @return PaginatedResponse
     * @throws ValidationException
     */
    public function scoreByInstansi()
    {
        $getParams = $this->request->get();
        $getParams['public'] = array_key_exists('public', $getParams) ? $getParams['public'] : 1;
        if (!$this->hasAdminScopes() || ($getParams['public'] == 1)) {
            $getParams['status'] = [PancekFormulir::STATUS_VERIFIED, PancekFormulir::STATUS_NEED_TO_REVIEW, PancekFormulir::STATUS_REVIEW_IN_PROGRESS];
            $getParams['instansi_status'] = null;
        } else {
            PancekUserAccess::checkEligibleForRoleObserver($this);
            $getParams['status'] = array_key_exists('status', $getParams) ? $getParams['status'] : null;
            $getParams['instansi_status'] = array_key_exists('instansi_status', $getParams) ? $getParams['instansi_status'] : null;
        }

        $getParams['instansi_id'] = array_key_exists('instansi_id', $getParams) ? $getParams['instansi_id'] : null;
        $getParams['jenis_instansi_id'] = array_key_exists('jenis_instansi_id', $getParams) ? $getParams['jenis_instansi_id'] : null;
        $getParams['sektor_usaha_id'] = array_key_exists('sektor_usaha_id', $getParams) ? $getParams['sektor_usaha_id'] : null;
        $getParams['provinsi_id'] = array_key_exists('provinsi_id', $getParams) ? $getParams['provinsi_id'] : null;
        $getParams['kab_kota_id'] = array_key_exists('kab_kota_id', $getParams) ? $getParams['kab_kota_id'] : null;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : null;
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['order_by'] = array_key_exists('order_by', $getParams) ? strtolower($getParams['order_by']) : 'score';
        $getParams['order_direction'] = array_key_exists('order_direction', $getParams) ? strtolower($getParams['order_direction']) : 'desc';

        $allowed_order = [
            'instansi_id',
            'jenis_instansi_id',
            'sektor_usaha_id',
            'provinsi_id',
            'kab_kota_id',
            'nama',
            'status',
            'created_at',
            'updated_at',
            'score'
        ];
        $map_order = [
            'instansi_id' => 'r.instansi_id',
            'jenis_instansi_id' => 'r.jenis_instansi_id',
            'sektor_usaha_id' => 'r.sektor_usaha_id',
            'provinsi_id' => 'r.provinsi_id',
            'kab_kota_id' => 'r.kab_kota_id',
            'nama' => 'r.instansi',
            'status' => 'r.status',
            'created_at' => 'r.created_at',
            'updated_at' => 'r.updated_at',
            'score' => 'r.score'
        ];
        $allowed_order_direction = ['asc', 'desc'];

        $params = $this->validate(
            $getParams,
            [
                'instansi_id' => ['validators' => ['digit']],
                'jenis_instansi_id' => ['validators' => ['digit']],
                'sektor_usaha_id' => ['validators' => ['digit']],
                'provinsi_id' => ['validators' => ['digit']],
                'kab_kota_id' => ['validators' => ['digit']],
                'keyword' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 100
                ]]],
                'status' => ['validators' => []],
                'instansi_status' => ['validators' => []],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'order_by' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order
                ]]],
                'order_direction' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order_direction
                ]]]
            ]
        );

        $totalKsp = $this->rawQuery("SELECT formulir_type, COUNT(formulir_type) FROM jaga.pancek_konsep_sistem_pencegahan GROUP BY formulir_type");
        $totalKspByType = [];
        foreach ($totalKsp as $value) {
            $totalKspByType[(string)$value['formulir_type']] = $value['count'];
        }

        $where = "now() < (f.created_at + INTERVAL '".getenv("PANCEK_FORMULIR_VALIDITY_PERIOD")." year') AND ki.is_header = FALSE AND f.deleted_at IS NULL";
        $bind = [
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ];
        if (!empty($params['instansi_id'])) {
            $where .= " AND i.id = :instansi_id";
            $bind['instansi_id'] = $params['instansi_id'];
        }
        if (!empty($params['jenis_instansi_id'])) {
            $where .= " AND i.jenis_instansi_id = :jenis_instansi_id";
            $bind['jenis_instansi_id'] = $params['jenis_instansi_id'];
        }
        if (!empty($params['sektor_usaha_id'])) {
            $where .= " AND i.sektor_usaha_id = :sektor_usaha_id";
            $bind['sektor_usaha_id'] = $params['sektor_usaha_id'];
        }
        if (!empty($params['provinsi_id'])) {
            $where .= " AND i.provinsi_id = :provinsi_id";
            $bind['provinsi_id'] = $params['provinsi_id'];
        }
        if (!empty($params['kab_kota_id'])) {
            $where .= " AND i.kab_kota_id = :kab_kota_id";
            $bind['kab_kota_id'] = $params['kab_kota_id'];
        }
        if (!empty($params['status'])) {
            if (is_array($params['status'])) {
                $where .= " AND (";
                foreach ($params['status'] as $k => $v) {
                    if ($k < count($params['status']) - 1) {
                        $where .= "f.status = :status_$k OR ";
                    } else {
                        $where .= "f.status = :status_$k";
                    }

                    $bind["status_$k"] = $v;
                }
                $where .= ")";
            } else {
                $where .= " AND f.status = :status";
                $bind['status'] = $params['status'];
            }
        }
        if (!empty($params['instansi_status'])) {
            $where .= " AND i.status = :instansi_status";
            $bind['instansi_status'] = $params['instansi_status'];
        }
        if (!empty($params['keyword'])) {
            $where .= " AND i.nama ILIKE :keyword";
            $bind['keyword'] = '%' . $params['keyword'] . '%';
        }

        if($getParams['public'] == 0) {
            $idInstansiBeaCukai = Instansi::getIdInstansiDitjenBeaCukai();
            $idInstansiDijtenPupr = Instansi::getIdInstansiDitjenPupr();
            $idInstansiUser = $this->shared->user->id_instansi;
            if($this->hasOneOfScopes(['pancek.monitoring', 'pancek.verify']) && $idInstansiBeaCukai == $idInstansiUser) {
                $where .= " AND i.ceklis_kepentingan = '0;1;0'";
            }
            else if($this->hasOneOfScopes(['pancek.monitoring', 'pancek.verify']) && $idInstansiDijtenPupr == $idInstansiUser) {
                $where .= " AND i.ceklis_kepentingan = '1;0;0'";
            }
            else if($this->hasScopes(['pancek.kppbc.assignor']) && $idInstansiBeaCukai != $idInstansiUser) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
            }
            else if($this->hasScopes(['pancek.kppbc.assignor']) && $idInstansiBeaCukai == $idInstansiUser) {
                $kppbcUsers = PancekKppbcUser::findAssignorOnlyBy($this->userID)->toArray();
                if(empty($kppbcUsers)) {
                    throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User belum di-assign ke KPPBC');
                }
                $where .= " AND i.kppbc_id IN (".implode(', ', array_column($kppbcUsers, 'kppbc_id')).")";
            }
            else if($this->hasScopes(['pancek.kppbc.verify']) && $idInstansiBeaCukai != $idInstansiUser) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
            }
            else if($this->hasScopes(['pancek.kppbc.verify']) && $idInstansiBeaCukai == $idInstansiUser) {
                $kppbcUsers = PancekKppbcUser::findVerifikatorOnlyBy($this->userID)->toArray();
                if(empty($kppbcUsers)) {
                    throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User belum di-assign ke KPPBC');
                }
                $where .= " AND i.kppbc_id IN (".implode(', ', array_column($kppbcUsers, 'kppbc_id')).")";

                $where .= " AND f.kppbc_um_id = :um_id";
                $bind['um_id'] = $this->userID;
            }
            else if($this->hasScopes(['pancek.kppbc.admin'])) {
                $where .= " AND i.ceklis_kepentingan = '".PancekInstansi::CEKLIST_KEPENTINGAN_BC."'";
            }
        }

        $query = "
            WITH LatestAnswer AS (
                SELECT DISTINCT ON (formulir_id) id, formulir_id
                FROM pancek_answer
                ORDER BY formulir_id, verified_at ASC NULLS LAST
            ),
            VerifikatorCTE AS (
                SELECT
                    f.id AS formulir_id,
                    a.id AS answer_id,
                    a.verify_status,
                    a.verified_at,
                    a.verified_by
                FROM jaga.pancek_formulir f
                LEFT JOIN LatestAnswer la ON la.formulir_id = f.id
                LEFT JOIN pancek_answer a ON a.id = la.id
            ),
            FirstAttachment AS (
                SELECT DISTINCT ON (answer_id) id, answer_id
                FROM pancek_answer_attachment
                ORDER BY answer_id, id
            ),
            ResultCTE AS (
                SELECT
                    i.id AS instansi_id,
                    i.nama AS instansi,
                    i.jenis_instansi_id,
                    i.sektor_usaha_id,
                    i.sektor_usaha_nama,
                    i.provinsi_id,
                    i.kab_kota_id,
                    f.id AS formulir_id,
                    f.periode,
                    f.status,
                    f.created_at,
                    f.updated_at,
                    SUM(ki.bobot) AS bobot,
                    SUM(CASE WHEN aa.id IS NOT NULL AND a.verify_status <> 'REJECTED' THEN ki.bobot ELSE 0 END) AS score,
                    COALESCE(f.kppbc_um_id, vc.verified_by) AS verify_um_id
                FROM pancek_instansi i
                LEFT JOIN pancek_formulir f ON f.instansi_id = i.id
                LEFT JOIN VerifikatorCTE vc ON vc.formulir_id = f.id
                LEFT JOIN pancek_answer a ON a.formulir_id = f.id
                LEFT JOIN pancek_komponen_indikator ki ON a.komponen_indikator_id = ki.id
                LEFT JOIN FirstAttachment aa ON aa.answer_id = a.id
                WHERE ".$where."
                GROUP BY
                    i.id, i.nama, f.id, f.periode,
                    i.provinsi_id, i.kab_kota_id,
                    i.jenis_instansi_id, i.sektor_usaha_id, i.sektor_usaha_nama,
                    f.created_at, f.updated_at, vc.verified_by, f.kppbc_um_id
            ),
            CetakHasilCTE AS (
                SELECT
                    i.id AS instansi_id,
                    f.id AS formulir_id,
                    ksp.code,
                    COUNT(DISTINCT a.id) AS count_answer,
                    COUNT(DISTINCT aa.answer_id) AS count_answered,
                    COUNT(DISTINCT aa.answer_id) FILTER (WHERE a.verify_status = 'ACCEPTED') AS count_verified,
                    (COALESCE(SUM(ki.bobot) FILTER (WHERE aa.id IS NOT NULL), 0) >= ".PancekFormulir::MINIMUM_PERCENTAGE_TO_DOWNLOAD.") AS is_reach_minimum_uploaded_limit,
                    (COALESCE(SUM(ki.bobot) FILTER (WHERE a.verify_status = 'ACCEPTED'), 0) >= ".PancekFormulir::MINIMUM_PERCENTAGE_TO_DOWNLOAD.") AS is_reach_minimum_verified_limit
                FROM pancek_formulir f
                JOIN pancek_instansi i ON f.instansi_id = i.id
                JOIN ResultCTE r ON r.instansi_id = i.id AND r.formulir_id = f.id
                LEFT JOIN pancek_answer a ON f.id = a.formulir_id
                LEFT JOIN FirstAttachment aa ON a.id = aa.answer_id
                LEFT JOIN pancek_komponen_indikator ki ON a.komponen_indikator_id = ki.id
                LEFT JOIN pancek_indikator it ON ki.indikator_id = it.id
                LEFT JOIN pancek_konsep_sistem_pencegahan ksp ON it.konsep_sistem_pencegahan_id = ksp.id
                WHERE ".$where."
                GROUP BY i.id, f.id, ksp.code
            )
            
            SELECT
                r.*,
                uu.nama AS verify_user_name,
                BOOL_AND(ch.is_reach_minimum_uploaded_limit) AS is_cetak_hasil_available,
                BOOL_AND(ch.is_reach_minimum_verified_limit) AS is_cetak_hasil_verified_available
            FROM ResultCTE r
            LEFT JOIN CetakHasilCTE ch ON r.instansi_id = ch.instansi_id AND r.formulir_id = ch.formulir_id
            LEFT JOIN um_user uu ON uu.um_id = r.verify_um_id
            GROUP BY
                r.instansi_id, r.instansi, r.jenis_instansi_id,
                r.sektor_usaha_id, r.sektor_usaha_nama,
                r.provinsi_id, r.kab_kota_id,
                r.formulir_id, r.periode,
                r.status, r.bobot, r.score, r.verify_um_id,
                uu.nama, r.created_at, r.updated_at
            ORDER BY ".$map_order[$params['order_by']]." ".$params['order_direction']."
            LIMIT :limit OFFSET :offset;
        ";


        $queryTotal = "
            SELECT COUNT(DISTINCT(f.id)) as total
            FROM pancek_instansi i
            LEFT JOIN pancek_formulir f ON f.instansi_id = i.id
            LEFT JOIN pancek_answer a ON a.formulir_id = f.id
            LEFT JOIN pancek_komponen_indikator ki ON a.komponen_indikator_id = ki.id
            LEFT JOIN pancek_answer_attachment aa ON aa.id = (
                    SELECT id FROM pancek_answer_attachment aaaa WHERE aaaa.answer_id = a.id LIMIT 1
                )
            WHERE ".$where."
        ";

        $rows = $this->rawQuery($query, $bind);
        unset($bind['limit']);
        unset($bind['offset']);
        $total = $this->rawQuery($queryTotal, $bind)[0]['total'];

        $refProvinsi = MasterProvinsi::getAsReference();
        $refKotakabupaten = MasterKotaKabupaten::getAsReference();
        $refJenisInstansi = PancekJenisInstansi::getAsReference('id');
        $refSektorUsaha = PancekSektorUsaha::getAsReference('id');

        foreach ($rows as $key => $row) {
            $rows[$key]['provinsi'] = $refProvinsi[$row['provinsi_id']]->provinsi_name;
            $rows[$key]['kota_kabupaten'] = $refKotakabupaten[$row['kab_kota_id']]->kota_kabupaten_name;
            $rows[$key]['jenis_instansi'] = $refJenisInstansi[$row['jenis_instansi_id']]['nama'];
            $rows[$key]['sektor_usaha'] = $refSektorUsaha[$row['sektor_usaha_id']]['nama'];
            $rows[$key]['bobot'] = doubleval(number_format($row['bobot'] / $totalKspByType[(string)$refJenisInstansi[$row['jenis_instansi_id']]['formulir_type']], 2));
            $rows[$key]['score'] = doubleval(number_format($row['score'] / $totalKspByType[(string)$refJenisInstansi[$row['jenis_instansi_id']]['formulir_type']], 2));
        }

        return new PaginatedResponse($rows, $total, $params['limit'], $params['offset']);
    }

    /**
     * untuk akses publik, hanya menampilkan formulir dengan status NOT IN (NEW, DRAFT) dan instansi dengan semua status
     * untuk akses admin, dapat menampilkan formulir dengan semua status dan instansi dengan status sesuai param
     * @return SimpleResponse
     * @throws ValidationException
     */
    public function scoreByIndikator()
    {
        $getParams = $this->request->get();
        $getParams['public'] = array_key_exists('public', $getParams) ? $getParams['public'] : 1;
        if (!$this->hasAdminScopes() || ($getParams['public'] == 1)) {
            $getParams['status'] = [PancekFormulir::STATUS_VERIFIED, PancekFormulir::STATUS_REVIEW_IN_PROGRESS, PancekFormulir::STATUS_NEED_TO_REVIEW];
            $getParams['instansi_status'] = null;
        } else {
            $getParams['status'] = array_key_exists('status', $getParams) ? $getParams['status'] : null;
            $getParams['instansi_status'] = array_key_exists('instansi_status', $getParams) ? $getParams['instansi_status'] : null;
        }

        $getParams['instansi_id'] = array_key_exists('instansi_id', $getParams) ? $getParams['instansi_id'] : null;
        $getParams['jenis_instansi_id'] = array_key_exists('jenis_instansi_id', $getParams) ? $getParams['jenis_instansi_id'] : null;
        $getParams['sektor_usaha_id'] = array_key_exists('sektor_usaha_id', $getParams) ? $getParams['sektor_usaha_id'] : null;
        $getParams['provinsi_id'] = array_key_exists('provinsi_id', $getParams) ? $getParams['provinsi_id'] : null;
        $getParams['kab_kota_id'] = array_key_exists('kab_kota_id', $getParams) ? $getParams['kab_kota_id'] : null;
        $getParams['konsep_sistem_pencegahan_id'] = array_key_exists('konsep_sistem_pencegahan_id', $getParams) ? $getParams['konsep_sistem_pencegahan_id'] : null;
        $getParams['level'] = array_key_exists('level', $getParams) ? $getParams['level'] : 1;

        $params = $this->validate(
            $getParams,
            [
                'instansi_id' => ['validators' => ['digit']],
                'jenis_instansi_id' => ['validators' => ['digit']],
                'sektor_usaha_id' => ['validators' => ['digit']],
                'provinsi_id' => ['validators' => ['digit']],
                'kab_kota_id' => ['validators' => ['digit']],
                'level' => ['validators' => ['digit']],
                'konsep_sistem_pencegahan_id' => ['validators' => ['digit']],
                'status' => ['validators' => []],
                'instansi_status' => ['validators' => []]
            ]
        );

        $where = "WHERE now() < f.created_at + INTERVAL '3 year' AND ki.is_header = FALSE AND f.deleted_at IS NULL";
        $whereFormulir = "now() < f.created_at + INTERVAL '3 year' AND f.deleted_at IS NULL";
        $bind = [];
        if (!empty($params['instansi_id'])) {
            $where .= " AND i.id = :instansi_id";
            $whereFormulir .= " AND i.id = :instansi_id";
            $bind['instansi_id'] = intval($params['instansi_id']);
        }
        if (!empty($params['konsep_sistem_pencegahan_id'])) {
            $where .= " AND ksp.id = :konsep_sistem_pencegahan_id";
            $bind['konsep_sistem_pencegahan_id'] = intval($params['konsep_sistem_pencegahan_id']);
        }
        if (!empty($params['jenis_instansi_id'])) {
            $where .= " AND i.jenis_instansi_id = :jenis_instansi_id";
            $whereFormulir .= " AND i.jenis_instansi_id = :jenis_instansi_id";
            $bind['jenis_instansi_id'] = $params['jenis_instansi_id'];
        }
        if (!empty($params['sektor_usaha_id'])) {
            $where .= " AND i.sektor_usaha_id = :sektor_usaha_id";
            $whereFormulir .= " AND i.sektor_usaha_id = :sektor_usaha_id";
            $bind['sektor_usaha_id'] = $params['sektor_usaha_id'];
        }
        if (!empty($params['provinsi_id'])) {
            $where .= " AND i.provinsi_id = :provinsi_id";
            $whereFormulir .= " AND i.provinsi_id = :provinsi_id";
            $bind['provinsi_id'] = $params['provinsi_id'];
        }
        if (!empty($params['kab_kota_id'])) {
            $where .= " AND i.kab_kota_id = :kab_kota_id";
            $whereFormulir .= " AND i.kab_kota_id = :kab_kota_id";
            $bind['kab_kota_id'] = $params['kab_kota_id'];
        }
        if (!empty($params['instansi_status'])) {
            $where .= " AND i.status LIKE :instansi_status";
            $whereFormulir .= " AND i.status LIKE :instansi_status";
            $bind['instansi_status'] = $params['instansi_status'];
        }
        if (!empty($params['status'])) {
            if (is_array($params['status'])) {
                $where .= " AND (";
                $whereFormulir .= " AND (";
                foreach ($params['status'] as $k => $v) {
                    if ($k < count($params['status']) - 1) {
                        $where .= "f.status LIKE :status_$k OR ";
                        $whereFormulir .= "f.status LIKE :status_$k OR ";
                    } else {
                        $where .= "f.status LIKE :status_$k";
                        $whereFormulir .= "f.status LIKE :status_$k";
                    }

                    $bind["status_$k"] = $v;
                }
                $where .= ")";
                $whereFormulir .= ")";
            } else {
                $where .= " AND f.status LIKE :status";
                $whereFormulir .= " AND f.status LIKE :status";
                $bind['status'] = $params['status'];
            }
        }

        $selectLevel = $groupLevel = $orderLevel = "";
        // karena indikator tipe 1 dan 2 jumlahnya tidak sama, maka tidak bisa di gabung
        if ($params['level'] == 2 && !empty($params['jenis_instansi_id'])) {
            $selectLevel = "ii.id as indikator_id, ii.code as indikator_code, ii.label as indikator,";
            $groupLevel = ", ii.id, ii.label";
            $orderLevel = ", ii.id";
        }

        if($getParams['public'] == 0) {
            $idInstansiBeaCukai = Instansi::getIdInstansiDitjenBeaCukai();
            $idInstansiDijtenPupr = Instansi::getIdInstansiDitjenPupr();
            $idInstansiUser = $this->shared->user->id_instansi;
            if($this->hasOneOfScopes(['pancek.monitoring', 'pancek.verify']) && $idInstansiBeaCukai == $idInstansiUser) {
                $where .= " AND i.ceklis_kepentingan = '0;1;0'";
                $whereFormulir .= " AND i.ceklis_kepentingan = '0;1;0'";
            }
            else if($this->hasOneOfScopes(['pancek.monitoring', 'pancek.verify']) && $idInstansiDijtenPupr == $idInstansiUser) {
                $where .= " AND i.ceklis_kepentingan = '1;0;0'";
            }
            else if($this->hasScopes(['pancek.kppbc.assignor']) && $idInstansiBeaCukai != $idInstansiUser) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
            }
            else if($this->hasScopes(['pancek.kppbc.assignor']) && $idInstansiBeaCukai == $idInstansiUser) {
                $kppbcUsers = PancekKppbcUser::findAssignorOnlyBy($this->userID)->toArray();
                if(empty($kppbcUsers)) {
                    throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User belum di-assign ke KPPBC');
                }
                $kppbcIds = implode(', ', array_column($kppbcUsers, 'kppbc_id'));
                $where .= " AND i.kppbc_id IN (".$kppbcIds.")";
                $whereFormulir .= " AND i.kppbc_id IN (".$kppbcIds.")";
            }
            else if($this->hasScopes(['pancek.kppbc.verify']) && $idInstansiBeaCukai != $idInstansiUser) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
            }
            else if($this->hasScopes(['pancek.kppbc.verify']) && $idInstansiBeaCukai == $idInstansiUser) {
                $kppbcUsers = PancekKppbcUser::findVerifikatorOnlyBy($this->userID)->toArray();
                if(empty($kppbcUsers)) {
                    throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User belum di-assign ke KPPBC');
                }
                $kppbcIds = implode(', ', array_column($kppbcUsers, 'kppbc_id'));
                $where .= " AND i.kppbc_id IN (".$kppbcIds.")";
                $whereFormulir .= " AND i.kppbc_id IN (".$kppbcIds.")";

                $where .= " AND f.kppbc_um_id = :um_id";
                $whereFormulir .= " AND f.kppbc_um_id = :um_id";
                $bind['um_id'] = $this->userID;
            }
            else if($this->hasScopes(['pancek.kppbc.admin'])) {
                $where .= " AND i.ceklis_kepentingan = '".PancekInstansi::CEKLIST_KEPENTINGAN_BC."'";
                $whereFormulir .= " AND i.ceklis_kepentingan = '".PancekInstansi::CEKLIST_KEPENTINGAN_BC."'";
            }
        }

        $data = $this->rawQuery("
        WITH answer_with_attachment AS (
          SELECT 
            a.id AS answer_id, aa.id AS attachment_id
          FROM 
            pancek_answer a
          LEFT JOIN (
            SELECT DISTINCT ON (answer_id) id, answer_id
            FROM pancek_answer_attachment
            ORDER BY answer_id, id DESC
          ) aa ON aa.answer_id = a.id
        )
        SELECT
            ksp.code as konsep_sistem_pencegahan_code,
            ksp.label as konsep_sistem_pencegahan,
            ".$selectLevel."
            SUM(ki.bobot) as bobot,
            SUM(CASE WHEN aa.attachment_id IS NOT NULL THEN ki.bobot ELSE 0 END) as score
        FROM pancek_konsep_sistem_pencegahan ksp
        LEFT JOIN pancek_indikator ii ON ii.konsep_sistem_pencegahan_id = ksp.id
        LEFT JOIN pancek_komponen_indikator ki ON ki.indikator_id = ii.id
        LEFT JOIN pancek_answer a ON a.komponen_indikator_id = ki.id
        LEFT JOIN answer_with_attachment aa ON aa.answer_id = a.id
        LEFT JOIN pancek_formulir f ON f.id = a.formulir_id
        LEFT JOIN pancek_instansi i ON i.id = f.instansi_id
        ".$where."
        GROUP BY ksp.code, ksp.label, ksp.view_order ".$groupLevel."
        ORDER BY ksp.view_order" . $orderLevel, $bind);

        unset($bind['konsep_sistem_pencegahan_id']);
        $totalFormulirAktif = $this->rawQuery("SELECT COUNT(f.id) as total FROM jaga.pancek_formulir f LEFT JOIN jaga.pancek_instansi i ON i.id = f.instansi_id WHERE " . $whereFormulir, $bind)[0]['total'];

        $result = [];
        if (count($data) > 0) {
            foreach ($data as $datum) {
                $item = $datum;
                $item['bobot'] = $datum['bobot']/$totalFormulirAktif;
                $item['score'] = doubleval(number_format($datum['score']/$totalFormulirAktif, 2));
                array_push($result, $item);
            }
        }

        return new SimpleResponse($result);
    }

    /**
     * untuk akses publik, hanya menampilkan formulir dengan status NOT IN (NEW, DRAFT) dan instansi dengan semua status
     * untuk akses admin, dapat menampilkan formulir dengan semua status dan instansi dengan status sesuai param
     * @return SimpleResponse
     */
    public function scoreByWilayah()
    {
        $params = $this->request->get();
        $params['public'] = array_key_exists('public', $params) ? $params['public'] : 1;
        $params['jenis_instansi_id'] = array_key_exists('jenis_instansi_id', $params) ? $params['jenis_instansi_id'] : null;
        $params['sektor_usaha_id'] = array_key_exists('sektor_usaha_id', $params) ? $params['sektor_usaha_id'] : null;
        $params['provinsi_id'] = array_key_exists('provinsi_id', $params) ? $params['provinsi_id'] : null;
        $params['status'] = array_key_exists('status', $params) ? $params['status'] : null;
        $params['instansi_status'] = array_key_exists('instansi_status', $params) ? $params['instansi_status'] : null;
        $where = "";
        $bind = [];

        $params = $this->validate(
            $params,
            [
                'public' => ['validators' => ['digit']],
                'jenis_instansi_id' => ['validators' => ['digit']],
                'sektor_usaha_id' => ['validators' => ['digit']],
                'provinsi_id' => ['validators' => ['digit']],
                'status' => ['validators' => []],
                'instansi_status' => ['validators' => []],
            ]
        );
        if (!$this->hasAdminScopes() || ($params['public'] == 1)) {
            $params['status'] = [PancekFormulir::STATUS_VERIFIED, PancekFormulir::STATUS_NEED_TO_REVIEW, PancekFormulir::STATUS_REVIEW_IN_PROGRESS];
            $params['instansi_status'] = null;
        }
        $provinsiId = array_key_exists('provinsi_id', $params) ? $params['provinsi_id'] : null;
        if (!empty($provinsiId)) {
            $where .= " AND i.provinsi_id = :provinsi_id";
            $bind['provinsi_id'] = $params['provinsi_id'];
        }
        if (!empty($params['status'])) {
            if (is_array($params['status'])) {
                $where .= " AND (";
                foreach ($params['status'] as $k => $v) {
                    if ($k < count($params['status']) - 1) {
                        $where .= "f.status LIKE :status_$k OR ";
                    } else {
                        $where .= "f.status LIKE :status_$k";
                    }

                    $bind["status_$k"] = $v;
                }
                $where .= ")";
            } else {
                $where .= " AND f.status LIKE :status";
                $bind['status'] = $params['status'];
            }
        }
        if (!empty($params['instansi_status'])) {
            $where .= " AND i.status LIKE :instansi_status";
            $bind['instansi_status'] = $params['instansi_status'];
        }
        if (!empty($params['jenis_instansi_id'])) {
            $where .= " AND i.jenis_instansi_id = :jenis_instansi_id";
            $bind['jenis_instansi_id'] = $params['jenis_instansi_id'];
        }
        if (!empty($params['sektor_usaha_id'])) {
            $where .= " AND i.sektor_usaha_id = :sektor_usaha_id";
            $bind['sektor_usaha_id'] = $params['sektor_usaha_id'];
        }

        $q = "
            WITH answer_with_attachment AS (
              SELECT 
                a.formulir_id, a.komponen_indikator_id, 
                aa.id AS attachment_id
              FROM 
                pancek_answer a
              LEFT JOIN (
                SELECT DISTINCT ON (answer_id) id, answer_id
                FROM pancek_answer_attachment
                ORDER BY answer_id, id DESC
              ) aa ON aa.answer_id = a.id
            )
            
            SELECT 
              i.provinsi_id AS id_region,
              mp.provinsi_name AS provinsi,
              SUM(ki.bobot) AS bobot,
              SUM(CASE WHEN aa.attachment_id IS NOT NULL THEN ki.bobot ELSE 0 END) AS value
            FROM 
              answer_with_attachment aa
              LEFT JOIN pancek_formulir f ON f.id = aa.formulir_id
              LEFT JOIN pancek_instansi i ON i.id = f.instansi_id
              LEFT JOIN pancek_komponen_indikator ki ON ki.id = aa.komponen_indikator_id
              LEFT JOIN pancek_indikator ii ON ii.id = ki.indikator_id
              LEFT JOIN pancek_konsep_sistem_pencegahan ksp ON ksp.id = ii.konsep_sistem_pencegahan_id
              LEFT JOIN master_provinsi mp ON mp.provinsi_code::INTEGER = i.provinsi_id
            WHERE 
              f.created_at > now() - INTERVAL '".getenv("PANCEK_FORMULIR_VALIDITY_PERIOD")." year'
              AND ki.is_header = FALSE
              AND f.deleted_at IS NULL ".$where."
            GROUP BY 
              i.provinsi_id,
              mp.provinsi_name;
        ";
        $data = $this->rawQuery($q, $bind);

        $result = [];
        foreach ($data as $datum) {
            $row = $datum;
            $pembagi = ($datum['bobot']/100);
            $row['bobot'] = $datum['bobot']/$pembagi;
            $row['value'] = doubleval(number_format($datum['value']/$pembagi, 2));
            $totalInstansi = $this->rawQuery("
                SELECT count(i.id) as total FROM jaga.pancek_instansi i LEFT JOIN jaga.pancek_formulir f ON f.instansi_id = i.id WHERE f.deleted_at IS NULL AND now() <= f.created_at + INTERVAL '".getenv("PANCEK_FORMULIR_VALIDITY_PERIOD")." year' $where
                AND i.provinsi_id = ".$datum['id_region']."
                GROUP BY i.provinsi_id
            ", $bind)[0]['total'];
            $row['total_instansi'] = $totalInstansi;
            array_push($result, $row);
        }

        return new SimpleResponse($result);
    }

    public function mapGeojson()
    {
        $id_provinsi = $this->request->get('provinsi_id');
        $version = !is_null($this->request->get('version')) ? $this->request->get('version') : getenv("MAP_VERSION");
        $dir = $this->config['application']['publicDir'];
        $geojson = null;
        if ($version == 1) {
            if ($id_provinsi === null) {
                $geojson = json_decode(file_get_contents($dir . '/maps/provinsi.json'));
            } else {
                $geojson = json_decode(file_get_contents($dir . '/maps/kabupaten.json'), true);
                $filter = function ($e) use ($id_provinsi) {
                    return ($id_provinsi === null || $e['properties']['A1CODE'] == $id_provinsi);
                };
                $geojson['features'] = array_values(array_filter($geojson['features'], $filter));
            }
        } else if ($version == 2) {
            if ($id_provinsi === null) {
                $geojson = json_decode(file_get_contents($dir . '/maps/provinsi_2020.json'), true);
            } else {
                $geojson = json_decode(file_get_contents($dir . '/maps/provinsi_2020.json'), true);

                $filter = function ($e) use ($id_provinsi) {
                    return ($id_provinsi === null || $e['properties']['ADM1_PCODE'] == 'ID' . $id_provinsi);
                };

                $geojson['features'] = array_values(array_filter($geojson['features'], $filter));
            }

            $geojson['features'] = MapsHelper::ConvertNewModelToGeneral($geojson['features']);
        }
        else if ($version == 3) {
            if ($id_provinsi === null) {
                $geojson = json_decode(file_get_contents($dir . '/maps/provinsi_2023.json'), true);
            } else {
                $geojson = json_decode(file_get_contents($dir . '/maps/kabupaten_2023.json'), true);

                $filter = function ($e) use ($id_provinsi) {
                    return ($id_provinsi === null || $e['properties']['ADM1_PCODE'] == 'ID' . $id_provinsi);
                };

                $geojson['features'] = array_values(array_filter($geojson['features'], $filter));
            }

            $geojson['features'] = MapsHelper::ConvertNewModelToGeneral($geojson['features']);
        }

        return new SimpleResponse($geojson);
    }


    #endregion

    #region private
    private function hasAdminScopes()
    {
        return $this->hasOneOfScopes(['pancek.admin', 'pancek.verify', 'pancek.monitoring', 'pancek.kppbc.admin', 'pancek.kppbc.verify', 'pancek.kppbc.assignor']);
    }
    #endregion
}