<?php

use DusanKasan\Knapsack\Collection;

class KorwilDashboardController extends BaseController
{
    const ALL_INSTANSI_TYPE = -1;
    const PROVINSI_INSTANSI_TYPE = 1;
    const KOTA_KAB_INSTANSI_TYPE = 2;

    public function mapGeojson()
    {
        $id_provinsi = $this->request->get('id_provinsi');
        $id_kota_kabupaten = $this->request->get('id_kota_kabupaten');
        $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, $id_kota_kabupaten) {
                    return ($id_provinsi === null || $e['properties']['A1CODE'] == $id_provinsi) && ($id_kota_kabupaten === null || $e['properties']['A2CODE'] == $id_kota_kabupaten);
                };
                $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/kabupaten_2020.json'), true);

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

                $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, $id_kota_kabupaten) {
                    return ($id_provinsi === null || $e['properties']['ADM1_PCODE'] == 'ID' . $id_provinsi) && ($id_kota_kabupaten === null || $e['properties']['ADM2_PCODE'] == 'ID' . $id_kota_kabupaten);
                };

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

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

        return new SimpleResponse($geojson);
    }

    public function mapGeojsonKab()
    {
        // returning all kabupaten, no filter
        $version = !is_null($this->request->get('version')) ? $this->request->get('version') : getenv("MAP_VERSION");
        $geojson = null;
        $dir = $this->config['application']['publicDir'];

        if ($version == 1) {
            $geojson = json_decode(file_get_contents($dir . '/maps/kabupaten.json'), true);
            $geojson['features'] = array_values(array_filter($geojson['features']));
        } else {
            if ($version == 2) {
                $geojson = json_decode(file_get_contents($dir . '/maps/kabupaten_2020.json'), true);
            } else {
                $geojson = json_decode(file_get_contents($dir . '/maps/kabupaten_2023.json'), true);
            }
            $geojson['features'] = array_values(array_filter($geojson['features']));
            $geojson['features'] = MapsHelper::ConvertNewModelToGeneral($geojson['features']);
        }

        return new SimpleResponse($geojson);
    }

    public function scores($templateId, $idTempDanaDesa = 0)
    {
        $getParams = $this->request->get();
        if (!array_key_exists('instansi_type', $getParams)) {
            $getParams['instansi_type'] = self::ALL_INSTANSI_TYPE;
        }

        if ($idTempDanaDesa == 0) {
            $dataKorsupgahTemplate = KorsupgahTemplate::findFirst($templateId);
            if(!empty($dataKorsupgahTemplate)) {
                $idTempDanaDesa = $dataKorsupgahTemplate->id_dana_desa;
            }
        }

        $params = $this->validate(
            $getParams,
            [
                'level' => ['validators' => ['required']],
                'id_provinsi' => ['validators' => []],
                'id_kota_kabupaten' => ['validators' => []],
                'id_area_intervensi' => ['validators' => []],
                'id_indikator' => ['validators' => []],
                'id_subindikator' => ['validators' => []],
                'instansi_type' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => [self::ALL_INSTANSI_TYPE, self::KOTA_KAB_INSTANSI_TYPE, self::PROVINSI_INSTANSI_TYPE]
                        ]
                    ]
                ],
                'year' => []
            ]
        );

        if ($templateId >= 14) {
            $maintainanceUntil = getenv('MCSP_2025_SERVICE_IN_MAINTENANCE_UNTIL') ?? false;
            if ($maintainanceUntil) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, "Mohon maaf, MCSP dalam pemeliharaan hingga $maintainanceUntil WIB");
            }
            $year = $params['year'];
            $params['region_id'] = $params['id_kota_kabupaten'] ?? $params['id_provinsi'] ?? null;
            $data = $this->mcsp_2025_scores($year, $params);
            return new SimpleResponse($data);
        }

        $id_provinsi = empty($params['id_provinsi']) ? -1 : $params['id_provinsi'];
        $id_kota_kabupaten = empty($params['id_kota_kabupaten']) ? -1 : $params['id_kota_kabupaten'];
        $id_area_intervensi = empty($params['id_area_intervensi']) ? -1 : $params['id_area_intervensi'];
        $id_indikator = empty($params['id_indikator']) ? -1 : $params['id_indikator'];
        $id_subindikator = empty($params['id_subindikator']) ? -1 : $params['id_subindikator'];
        $instansi_type = $params['instansi_type'];

        //Jika yang dipilih Provinsi DKI Jakarta, isi value semua region kota Jakarta dari Provinsi DKI
        $tempIdProvinsi = 0;
        if($id_provinsi == 31) {
            $tempIdProvinsi = 31;
            $id_provinsi = -1;
            $params['level'] = 'nasional';
        }

        $bobot_query = '';
        $region_select_query = '';
        $region_group_by_query = '';

        if (!empty($params['id_subindikator'])) {
            $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float)';
        } else if (!empty($params['id_indikator'])) {
            $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot / 100)';
        } else if (!empty($params['id_area_intervensi'])) {
            $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000)';
        } else {
            $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot * CASE WHEN ins.flag_dana_desa = 0 AND ai.id = 6
            THEN ai.presentase_bobot + aidd.presentase_bobot
            WHEN ins.flag_dana_desa = 0
            THEN ai.presentase_bobot_wo_dana_desa
            ELSE ai.presentase_bobot END
             / 1000000)';
        }

        if ($params['level'] == 'nasional') {
            $region_select_query = 'instansi_value.id_provinsi as id_region';
            $region_group_by_query = 'instansi_value.id_provinsi';
        } else if ($params['level'] == 'provinsi') {
            $region_select_query = 'instansi_value.id_kota_kabupaten as id_region';
            $region_group_by_query = 'instansi_value.id_kota_kabupaten';
        } else if ($params['level'] == 'kabupaten') {
            $region_select_query = 'instansi_value.id_kota_kabupaten as id_region';
            $region_group_by_query = 'instansi_value.id_kota_kabupaten';
        } else {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Level tidak valid');
        }

        // to fucking find the year on based on the selected template
        $template = KorsupgahTemplate::findById($templateId);

        if (empty($template)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Template tidak ditemukan');
        }

        try {
            $tahun = $template->getFirst()->tahun ?? 0;
        } catch (\Exception $e) {
            $tahun = 0;
        }

        $q = '
SELECT
     ' . $region_select_query . ',
     AVG(instansi_value.value) as value
FROM
        (
    SELECT
        ins.id,
        ins.nama,
        ins.id_provinsi,
        ins.id_kota_kabupaten,
        kti.team_id,
        kti.team_name,
        ' . $bobot_query . ' - COALESCE(kmps.score, 0) as value,
        kmps.score as penalty_score
    FROM "jaga"."korsupgah_survey" s
        join jaga.korsupgah_template t on t.id = s.id_template
        join jaga.korsupgah_template_sub_indikator tsi on t.id = tsi.id_template and tsi.deleted_at is null
        join jaga.korsupgah_sub_indikator si on tsi.id_sub_indikator = si.id
        join jaga.korsupgah_indikator i on i.id = si.id_indikator
        join jaga.korsupgah_area_intervensi ai on ai.id = i.id_area_intervensi
        join jaga.korsupgah_indikator_tipe it on i.id_tipe = it.id
        join jaga.instansi ins on ins.id = s.id_instansi
        left join jaga.rel_instansi_wilayah riw on riw.id_instansi = ins.id and '.$tahun.' >= riw.start_year and '.$tahun.' <= riw.end_year 
        JOIN jaga.master_provinsi mprov ON coalesce(riw.id_provinsi, ins.id_provinsi) = mprov.provinsi_code::INTEGER
        join jaga.korsupgah_periode p on p.id = s.id_periode
        left outer join (
            select distinct on (1, 2) a.id_question as id_sub_indikator, a.id_survey, a.id as answer_id, a.nilai_verifikasi, a.nilai_verifikasi_qa, a.nilai_verifikasi_pic
            from jaga.korsupgah_answer a
            where a.status >= 1
            ORDER BY a.id_question, a.id_survey, a.created_at DESC
        ) answer on answer.id_sub_indikator = tsi.id_sub_indikator AND s.id = answer.id_survey
        left outer join (
            select ti.*, t.id_periode, t.name as team_name
            from jaga.korwil_team_instansi ti
            join jaga.korwil_teams t on t.id = ti.team_id
            where t.deleted_at is null
        ) kti on kti.instansi_id = ins.id and kti.id_periode = s.id_periode
        join jaga.korsupgah_area_intervensi aidd on aidd.id = 8
        left outer join jaga.korsupgah_mcp_penalty_score kmps
            on kmps.id_instansi = ins.id and kmps.id_template = s.id_template and kmps.deleted_at is null
    WHERE (s.id_template = :templateId OR s.id_template = :templateIdDanaDesa) AND
        s.deleted_at IS NULL AND
        ((case when riw.id_provinsi is null then ins.id_provinsi = :id_provinsi else riw.id_provinsi = :id_provinsi end) OR 0 > :id_provinsi) AND
        ((case when riw.id_kota_kabupaten is null then ins.id_kota_kabupaten = :id_kota_kabupaten else riw.id_kota_kabupaten = :id_kota_kabupaten end) OR 0 > :id_kota_kabupaten) AND
        (ins.id_tipe = :instansi_type OR 0 > :instansi_type) AND
        (ai.id = :id_area_intervensi OR 0 > :id_area_intervensi) AND
        (i.id = :id_indikator OR 0 > :id_indikator) AND
        (si.id = :id_subindikator OR 0 > :id_subindikator)
    GROUP BY ins."id", ins.nama, ins.id_provinsi, ins.id_kota_kabupaten, kti.team_id, kti.team_name, riw.id, riw.id_provinsi, riw.id_kota_kabupaten, kmps.score
    ORDER BY value DESC
    ) as instansi_value
    '. ($tempIdProvinsi == 31 ? ' WHERE instansi_value.id_provinsi = 31 ' : '') .'
GROUP BY ' . $region_group_by_query;

        $result = $this->rawQuery(
            $q,
            [
                'templateId' => $templateId,
                'templateIdDanaDesa' => $idTempDanaDesa,
                'id_provinsi' => $id_provinsi,
                'id_kota_kabupaten' => $id_kota_kabupaten,
                'id_area_intervensi' => $id_area_intervensi,
                'id_indikator' => $id_indikator,
                'id_subindikator' => $id_subindikator,
                'instansi_type' => $instansi_type
            ]
        );

        $valueDKI = 0;

        if($tempIdProvinsi == 31) {
            //Provinsi DKI Jakarta, isi semua region kota dengan value dari Prov DKI
            if(count($result) > 0) {
                if($result[0]['id_region'] = 31) $valueDKI = $result[0]['value'];
            }
            $result = [
                [
                    'id_region' => 3173,
                    'value' => $valueDKI
                ],
                [
                    'id_region' => 3171,
                    'value' => $valueDKI
                ],
                [
                    'id_region' => 3174,
                    'value' => $valueDKI
                ],
                [
                    'id_region' => 3175,
                    'value' => $valueDKI
                ],
                [
                    'id_region' => 3172,
                    'value' => $valueDKI
                ],
            ];
        }

        $id_regions = [];

        foreach ($result as $item) {
            if($item['id_region']!=null) {
                array_push($id_regions, $item['id_region']);
            }
        }

        if($tempIdProvinsi == 31) $id_provinsi = 31;

        $dataInstansi = Instansi::find([
            'conditions' => '(id_provinsi = :id_provinsi: OR 0 > :id_provinsi:) AND (id_kota_kabupaten = :id_kota_kabupaten: OR 0 > :id_kota_kabupaten:) AND (id_tipe = :id_tipe: OR 0 > :id_tipe:)',
            'bind' => [
                'id_provinsi' => $id_provinsi,
                'id_kota_kabupaten' => $id_kota_kabupaten,
                'id_tipe' => $instansi_type
            ]
        ]);

        foreach ($dataInstansi as $item) {
            if(!in_array($item->id_kota_kabupaten, $id_regions) && $item->id_kota_kabupaten != null) {
                array_push($result, [
                    'id_region' => $item->id_kota_kabupaten,
                    'value' => $tempIdProvinsi == 31 ? $valueDKI : 0
                ]);
            }
        }
        
        return new SimpleResponse($result);
    }

    public function indikators($templateId, $idTempDanaDesa = 0)
    {
        $getParams = $this->request->get();
        if (!array_key_exists('instansi_type', $getParams)) {
            $getParams['instansi_type'] = self::ALL_INSTANSI_TYPE;
        }

        if ($idTempDanaDesa == 0) {
            $idTempDanaDesa = getenv('KORWIL_ID_DD');
        }

        $params = $this->validate(
            $getParams,
            [
                'id_provinsi' => ['validators' => []],
                'id_kota_kabupaten' => ['validators' => []],
                'id_area_intervensi' => ['validators' => []],
                'id_indikator' => ['validators' => []],
                'instansi_type' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => [self::ALL_INSTANSI_TYPE, self::KOTA_KAB_INSTANSI_TYPE, self::PROVINSI_INSTANSI_TYPE]
                        ]
                    ]
                ]
            ]
        );

        $id_provinsi = empty($params['id_provinsi']) ? -1 : $params['id_provinsi'];
        $id_kota_kabupaten = empty($params['id_kota_kabupaten']) ? -1 : $params['id_kota_kabupaten'];
        $id_area_intervensi = empty($params['id_area_intervensi']) ? -1 : $params['id_area_intervensi'];
        $id_indikator = empty($params['id_indikator']) ? -1 : $params['id_indikator'];
        $instansi_type = $params['instansi_type'];

        $template = KorsupgahTemplate::findById($templateId);

        if (empty($template)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Template tidak ditemukan');
        }

        $result = $this->rawQuery('
SELECT
    ai.id as id_area_intervensi,
    i.id as id_indikator,
    ai.presentase_bobot as bobot_area_intervensi,
    i.presentase_bobot as bobot_indikator,
    ai.nama as area_intervensi,
    i.nama as indikator,
    sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi, 0)::float * si.presentase_bobot /100) as nilai,
    CASE WHEN ai.id = 8
    THEN count(distinct ins) FILTER (WHERE ins.flag_dana_desa = 1)
    ELSE count(distinct ins) END as count_items
FROM "jaga"."korsupgah_survey" s
    join jaga.korsupgah_template t on t.id = s.id_template
    join jaga.korsupgah_template_sub_indikator tsi on t.id = tsi.id_template and tsi.deleted_at is null
    join jaga.korsupgah_sub_indikator si on tsi.id_sub_indikator = si.id
    join jaga.korsupgah_indikator i on i.id = si.id_indikator
    join jaga.korsupgah_area_intervensi ai on ai.id = i.id_area_intervensi
    join jaga.korsupgah_indikator_tipe it on i.id_tipe = it.id
    join jaga.instansi ins on ins.id = s.id_instansi
    JOIN jaga.master_provinsi mprov ON ins.id_provinsi = mprov.provinsi_code::INTEGER
    join jaga.korsupgah_periode p on p.id = s.id_periode
    left join (
        select distinct on (1, 2) a.id_question as id_sub_indikator, a.id_survey, a.id as answer_id, a.nilai_verifikasi, a.nilai_verifikasi_qa, a.nilai_verifikasi_pic
        from jaga.korsupgah_answer a
        where a.status >= 1
        ORDER BY a.id_question, a.id_survey, a.created_at DESC
    ) answer on answer.id_sub_indikator = tsi.id_sub_indikator AND s.id = answer.id_survey
WHERE (s.id_template = :templateId OR s.id_template = :templateIdDanaDesa) AND
    (ins.id_provinsi = :id_provinsi OR 0 > :id_provinsi) AND
    (ins.id_kota_kabupaten = :id_kota_kabupaten OR 0 > :id_kota_kabupaten) AND
    (ins.id_tipe = :instansi_type OR 0 > :instansi_type) AND
    (ai.id = :id_area_intervensi OR 0 > :id_area_intervensi) AND
    (i.id = :id_indikator OR 0 > :id_indikator)
GROUP BY ai.id, i.id, ai.presentase_bobot, i.presentase_bobot, ai.nama, i.nama
ORDER BY nilai DESC, id_area_intervensi, id_indikator
        ', [
            'templateId' => $templateId,
            'id_provinsi' => $id_provinsi,
            'id_kota_kabupaten' => $id_kota_kabupaten,
            'id_area_intervensi' => $id_area_intervensi,
            'id_indikator' => $id_indikator,
            'instansi_type' => $instansi_type
        ]);

        return new SimpleResponse($result);
    }

    public function subindikators($templateId, $idTempDanaDesa = 0)
    {
        $getParams = $this->request->get();

        if (!array_key_exists('instansi_type', $getParams)) {
            $getParams['instansi_type'] = self::ALL_INSTANSI_TYPE;
        }

        if ($idTempDanaDesa == 0) {
            $dataKorsupgahTemplate = KorsupgahTemplate::findFirst($templateId);
            if(!empty($dataKorsupgahTemplate)) {
                $idTempDanaDesa = $dataKorsupgahTemplate->id_dana_desa;
            }
        }

        $params = $this->validate(
            $getParams,
            [
                'id_provinsi' => ['validators' => []],
                'id_kota_kabupaten' => ['validators' => []],
                'id_area_intervensi' => ['validators' => []],
                'id_indikator' => ['validators' => []],
                'id_subindikator' => ['validators' => []],
                'instansi_type' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => [self::ALL_INSTANSI_TYPE, self::KOTA_KAB_INSTANSI_TYPE, self::PROVINSI_INSTANSI_TYPE]
                        ]
                    ]
                ]
            ]
        );

        $id_provinsi = empty($params['id_provinsi']) ? -1 : $params['id_provinsi'];
        $id_kota_kabupaten = empty($params['id_kota_kabupaten']) ? -1 : $params['id_kota_kabupaten'];
        $id_area_intervensi = empty($params['id_area_intervensi']) ? -1 : $params['id_area_intervensi'];
        $id_indikator = empty($params['id_indikator']) ? -1 : $params['id_indikator'];
        $id_subindikator = empty($params['id_subindikator']) ? -1 : $params['id_subindikator'];
        $instansi_type = $params['instansi_type'];

        $template = KorsupgahTemplate::findById($templateId);

        if (empty($template)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Template tidak ditemukan');
        }

        try {
            $tahun = $template->getFirst()->tahun ?? 0;
        } catch (\Exception $e) {
            $tahun = 0;
        }

        $bind = [
            'templateId' => $templateId,
            'id_provinsi' => $id_provinsi,
            'id_area_intervensi' => $id_area_intervensi,
            'id_indikator' => $id_indikator,
            'id_subindikator' => $id_subindikator,
            'instansi_type' => $instansi_type
        ];
        $whereTemplate = '';

        if($idTempDanaDesa > 0) {
            $whereTemplate = '(s.id_template = :templateId OR s.id_template = :templateIdDanaDesa)';
            $bind['templateIdDanaDesa'] = $idTempDanaDesa;
        }
        else {
            $whereTemplate = '(s.id_template = :templateId)';
        }

        $whereProvinsi = '
            ((case when riw.id_provinsi is null then ins.id_provinsi = :id_provinsi else riw.id_provinsi = :id_provinsi end) OR 0 > :id_provinsi) AND 
            ((case when riw.id_kota_kabupaten is null then ins.id_kota_kabupaten = :id_kota_kabupaten else riw.id_kota_kabupaten = :id_kota_kabupaten end) OR 0 > :id_kota_kabupaten) AND 
        ';
        if (array_key_exists('level', $getParams) && $getParams['level'] == 'provinsi') {
            $whereProvinsi = '
               ((case when riw.id_provinsi is null then ins.id_provinsi = :id_provinsi else riw.id_provinsi = :id_provinsi end) OR 0 > :id_provinsi) AND 
               ((case when riw.id_kota_kabupaten is null then ins.id_kota_kabupaten IS NULL else riw.id_kota_kabupaten is null end)) AND 
           ';
        } else {
            $bind['id_kota_kabupaten'] = $id_kota_kabupaten;
        }

        $query = '
            SELECT
                ai.id as id_area_intervensi,
                i.id as id_indikator,
                si.id as id_subindikator,
                ai.presentase_bobot as bobot_area_intervensi,
                i.presentase_bobot as bobot_indikator,
                si.presentase_bobot as bobot_subindikator,
                ai.nama as area_intervensi,
                i.nama as indikator,
                si.nama as subindikator,
                sum(coalesce(answer.nilai_verifikasi_qa, answer.nilai_verifikasi, 0)::float) as nilai,
                CASE WHEN ai.id = 8
                THEN count(distinct ins) FILTER (WHERE ins.flag_dana_desa = 1)
                ELSE count(distinct ins) END as count_items,
                ai.id_area_fokus, 
                af.title as title_area_fokus, 
                af.description as description_area_fokus
            FROM "jaga"."korsupgah_survey" s
                join jaga.korsupgah_template t on t.id = s.id_template
                join jaga.korsupgah_template_sub_indikator tsi on t.id = tsi.id_template and tsi.deleted_at is null
                join jaga.korsupgah_sub_indikator si on tsi.id_sub_indikator = si.id
                join jaga.korsupgah_indikator i on i.id = si.id_indikator
                join jaga.korsupgah_area_intervensi ai on ai.id = i.id_area_intervensi
                join jaga.korsupgah_indikator_tipe it on i.id_tipe = it.id
                join jaga.instansi ins on ins.id = s.id_instansi
                left join jaga.rel_instansi_wilayah riw on riw.id_instansi = ins.id and '.$tahun.' >= riw.start_year and '.$tahun.' <= riw.end_year 
                JOIN jaga.master_provinsi mprov ON coalesce(riw.id_provinsi, ins.id_provinsi) = mprov.provinsi_code::INTEGER
                join jaga.korsupgah_periode p on p.id = s.id_periode
                left join (
                    select distinct on (1, 2) a.id_question as id_sub_indikator, a.id_survey, a.id as answer_id, a.nilai_verifikasi, a.nilai_verifikasi_qa, a.nilai_verifikasi_pic
                    from jaga.korsupgah_answer a
                    where a.status >= 1
                    ORDER BY a.id_question, a.id_survey, a.created_at DESC
                ) answer on answer.id_sub_indikator = tsi.id_sub_indikator AND s.id = answer.id_survey
                LEFT JOIN jaga.korsupgah_area_fokus af ON ai.id_area_fokus = af.id
            WHERE '.$whereTemplate.' AND '.$whereProvinsi.'
                (ins.id_tipe = :instansi_type OR 0 > :instansi_type) AND
                (ai.id = :id_area_intervensi OR 0 > :id_area_intervensi) AND
                (i.id = :id_indikator OR 0 > :id_indikator) AND
                (si.id = :id_subindikator OR 0 > :id_subindikator) AND
                (s.deleted_at IS NULL)
            GROUP BY ai.id, i.id, si.id, ai.presentase_bobot, i.presentase_bobot, si.presentase_bobot, ai.nama, i.nama, si.nama, af.title, af.description
            ORDER BY nilai DESC, id_area_intervensi, id_indikator;
        ';

        $result = $this->rawQuery($query, $bind);

        return new SimpleResponse($result);
    }

    public function subindikatorsProvinsiRataRata($templateId, $idTempDanaDesa = 0)
    {
        $getParams = $this->request->get();

        if (!array_key_exists('instansi_type', $getParams)) {
            $getParams['instansi_type'] = self::ALL_INSTANSI_TYPE;
        }

        if ($idTempDanaDesa == 0) {
            $dataKorsupgahTemplate = KorsupgahTemplate::findFirst($templateId);
            if(!empty($dataKorsupgahTemplate)) {
                $idTempDanaDesa = $dataKorsupgahTemplate->id_dana_desa;
            }
        }

        $params = $this->validate(
            $getParams,
            [
                'id_provinsi' => ['validators' => []],
                'id_kota_kabupaten' => ['validators' => []],
                'id_area_intervensi' => ['validators' => []],
                'id_indikator' => ['validators' => []],
                'id_subindikator' => ['validators' => []],
                'instansi_type' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => [self::ALL_INSTANSI_TYPE, self::KOTA_KAB_INSTANSI_TYPE, self::PROVINSI_INSTANSI_TYPE]
                        ]
                    ]
                ]
            ]
        );

        $id_provinsi = empty($params['id_provinsi']) ? -1 : $params['id_provinsi'];
        $id_kota_kabupaten = empty($params['id_kota_kabupaten']) ? -1 : $params['id_kota_kabupaten'];
        $id_area_intervensi = empty($params['id_area_intervensi']) ? -1 : $params['id_area_intervensi'];
        $id_indikator = empty($params['id_indikator']) ? -1 : $params['id_indikator'];
        $id_subindikator = empty($params['id_subindikator']) ? -1 : $params['id_subindikator'];
        $instansi_type = $params['instansi_type'];

        $template = KorsupgahTemplate::findById($templateId);

        if (empty($template)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Template tidak ditemukan');
        }

        try {
            $tahun = $template->getFirst()->tahun ?? 0;
        } catch (\Exception $e) {
            $tahun = 0;
        }

        $bind = [
            'templateId' => $templateId,
            'id_provinsi' => $id_provinsi,
            'id_area_intervensi' => $id_area_intervensi,
            'id_indikator' => $id_indikator,
            'id_subindikator' => $id_subindikator,
            'instansi_type' => $instansi_type
        ];
        $whereTemplate = '';

        if($idTempDanaDesa > 0) {
            $whereTemplate = '(s.id_template = :templateId OR s.id_template = :templateIdDanaDesa)';
            $bind['templateIdDanaDesa'] = $idTempDanaDesa;
        }
        else {
            $whereTemplate = '(s.id_template = :templateId)';
        }

        $whereProvinsi = '
            ((case when riw.id_provinsi is null then ins.id_provinsi = :id_provinsi else riw.id_provinsi = :id_provinsi end) OR 0 > :id_provinsi) AND 
            ((case when riw.id_kota_kabupaten is null then ins.id_kota_kabupaten = :id_kota_kabupaten else riw.id_kota_kabupaten = :id_kota_kabupaten end) OR 0 > :id_kota_kabupaten) AND 
        ';
        $bind['id_kota_kabupaten'] = $id_kota_kabupaten;

        // Commented code below is the original code (sementara nilai_verifikasi_qa dinonaktifkan)
        $query = '
            SELECT
                ai.id as id_area_intervensi,
                i.id as id_indikator,
                si.id as id_subindikator,
                ai.presentase_bobot as bobot_area_intervensi,
                i.presentase_bobot as bobot_indikator,
                si.presentase_bobot as bobot_subindikator,
                ai.nama as area_intervensi,
                i.nama as indikator,
                si.nama as subindikator,
                sum(coalesce(answer.nilai_verifikasi_qa, answer.nilai_verifikasi, 0)::float) as nilai,
                CASE WHEN ai.id = 8
                THEN count(distinct ins) FILTER (WHERE ins.flag_dana_desa = 1)
                ELSE count(distinct ins) END as count_items,
                ai.id_area_fokus, 
                af.title as title_area_fokus, 
                af.description as description_area_fokus
            FROM "jaga"."korsupgah_survey" s
                join jaga.korsupgah_template t on t.id = s.id_template
                join jaga.korsupgah_template_sub_indikator tsi on t.id = tsi.id_template and tsi.deleted_at is null
                join jaga.korsupgah_sub_indikator si on tsi.id_sub_indikator = si.id
                join jaga.korsupgah_indikator i on i.id = si.id_indikator
                join jaga.korsupgah_area_intervensi ai on ai.id = i.id_area_intervensi
                join jaga.korsupgah_indikator_tipe it on i.id_tipe = it.id
                join jaga.instansi ins on ins.id = s.id_instansi
                left join jaga.rel_instansi_wilayah riw on riw.id_instansi = ins.id and '.$tahun.' >= riw.start_year and '.$tahun.' <= riw.end_year
                JOIN jaga.master_provinsi mprov ON coalesce(riw.id_provinsi, ins.id_provinsi) = mprov.provinsi_code::INTEGER
                join jaga.korsupgah_periode p on p.id = s.id_periode
                left join (
                    select distinct on (1, 2) a.id_question as id_sub_indikator, a.id_survey, a.id as answer_id, a.nilai_verifikasi, a.nilai_verifikasi_qa, a.nilai_verifikasi_pic
                    from jaga.korsupgah_answer a
                    where a.status >= 1
                    ORDER BY a.id_question, a.id_survey, a.created_at DESC
                ) answer on answer.id_sub_indikator = tsi.id_sub_indikator AND s.id = answer.id_survey
                LEFT JOIN jaga.korsupgah_area_fokus af ON ai.id_area_fokus = af.id
            WHERE '.$whereTemplate.' AND '.$whereProvinsi.'
                (ins.id_tipe = :instansi_type OR 0 > :instansi_type) AND
                (ai.id = :id_area_intervensi OR 0 > :id_area_intervensi) AND
                (i.id = :id_indikator OR 0 > :id_indikator) AND
                (si.id = :id_subindikator OR 0 > :id_subindikator) AND
                (s.deleted_at IS NULL)
            GROUP BY ai.id, i.id, si.id, ai.presentase_bobot, i.presentase_bobot, si.presentase_bobot, ai.nama, i.nama, si.nama, af.title, af.description
            ORDER BY nilai DESC, id_area_intervensi, id_indikator;
        ';

        $result = $this->rawQuery($query, $bind);


        return new SimpleResponse($result);
    }

    private function getTableauIframe($view_url)
    {
        $userTableau = 'webviewer';
        $serverTableau = 'statistik.kpk.go.id';

        try {

            $tableau_client = new Tableau();
            $tab_resp = $tableau_client->get_trusted_url_tableau($userTableau, $serverTableau, $view_url);

            return new CollectionPaginatedResponse([$tab_resp]);
        } catch (\Throwable $th) {
            echo "error<br>";
            echo "<pre>";
            echo "$th";
            echo "</pre>";
            die();

            return ['error_message' => $th];
        }
    }

    public function tableau2018()
    {
        return $this->getTableauIframe("views/Korsupgah_Renaksi_RenaksiNasional_2018/RenaksiNasional");
    }

    public function tableau2019()
    {
        return $this->getTableauIframe("views/Korsupgah_Renaksi_RenaksiNasional_2019/RenaksiNasional");
    }

    public function mcspInstansiDashboard($year, $instansi_id) {
        $endpoint = "/api/dashboard/instansi/$year/$instansi_id";
        if ($this->hasOneOfScopes(['korsupgah.verify'])) {
            $endpoint .= "?verifier_id=" . $this->shared->user->um_id;
        }
        $mcpService = new McpService();
        try {
            $result = $mcpService->get($endpoint);
            if ($result->success) {
                return $result->data;
            }
            return [];
        } catch (Exception $e) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, $e->getMessage());
        }
    }

    private function mcsp_2025_scores($year, $params) {
        $mcpService = new McpService();
        $result = $mcpService->get("/api/dashboard/map/$year", $params);
        if ($result->success) {
            return $result->data;
        }
        return [];
    }

    public function mcspNasionalDashboard($year, $hierarchy_id) {
        $endpoint = "/api/dashboard/nasional/$year/$hierarchy_id";
        $mcpService = new McpService();
        try {
            $result = $mcpService->get($endpoint);
            if ($result->success) {
                return $result->data;
            }
            return [];
        } catch (Exception $e) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, $e->getMessage());
        }
    }

    public function mcspAnswersDashboard($year) {
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'level' => ['validators' => ['required']],
                'id_provinsi' => ['validators' => ['digit']],
                'id_kota_kabupaten' => ['validators' => ['digit']],
                'id_area_intervensi' => ['validators' => ['digit']],
                'id_parent' => ['validators' => []],
                'is_new_region_selected' => ['validators' => []],
            ]
        );
        
        $mcspParams = [
            'region_id' => 'level' === 'nasional' ? null :
                ($params['id_kota_kabupaten'] ?? $params['id_provinsi']),
            'hierarchy_id' => $params['id_area_intervensi'] ?? null,
            'parent_id' => $params['id_parent'] ?? 1,
            'is_new_region_selected' => $params['is_new_region_selected']
        ];
        $endpoint = "/api/dashboard/answers/$year";
        $mcpService = new McpService();
        try {
            $result = $mcpService->get($endpoint, $mcspParams);
            if ($result->success) {
                return $result->data;
            }
            return [];
        } catch (Exception $e) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, $e->getMessage());
        }
    }

    public function mcsp_scores($year) {
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'id_region' => ['validators' => []],
                'id_answer' => ['validators' => ['digit']]
            ]
        );

        $maintainanceUntil = getenv('MCSP_2025_SERVICE_IN_MAINTENANCE_UNTIL') ?? false;
        if ($maintainanceUntil) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_AVAILABLE, "Mohon maaf, MCSP dalam pemeliharaan hingga $maintainanceUntil WIB");
        }
        $mcpService = new McpService();
        $mcspParams = [
            'region_id' => $params['id_region'] ?? null,
            'answer_id' => $params['id_answer'] ?? null
        ];
        $result = $mcpService->get("/api/dashboard/map/$year", $mcspParams);
        if ($result->success) {
            return new SimpleResponse($result->data);
        }
        return [];
    }
}   // end of controller