<?php

use DusanKasan\Knapsack\Collection;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Phalcon\Mvc\Model\Query\Builder;

class KorsupgahTemplateController extends BaseController
{
    public function list()
    {
        $this->checkIsAuthenticated();

        $getParams = $this->request->get();

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';

        $params =  $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []]
            ]
        );

        $template_list = KorsupgahTemplate::find([
            'conditions' => 'nama ilike :keyword:',
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%'
            ],
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'order' => 'view_order'
        ]);

        $total_record = KorsupgahTemplate::count();

        return new PaginatedResponse($template_list, $total_record, $params['limit'], $params['offset']);
    }

    public function update($id)
    {
        $this->checkScopes(['korsupgah.admin']);


        /** @var KorsupgahTemplate $kt */
        $kt = KorsupgahTemplate::findFirst($id);

        if (empty($kt)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Template dengan ID #' . $id . 'tidak ditemukan');
        endif;

        if (!empty($kt->deleted_at)) :

            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Template dengan ID #' . $id . 'mungkin sudah terhapus');
        endif;

        $rawBody = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $rawBody,
            [
                'nama' => ['validators' => ['required']],
                'keterangan' => ['validators' => []],
                'id_dana_desa' => ['validators' => ['digit']],
                'view_order' => ['validators' => ['digit']],
                'tahun' => ['validators' => ['required', 'digit']],
                'is_dana_desa' => ['validators' => []],
            ]
        );

        if ($params['id_dana_desa'] > 0) {
            $exist = KorsupgahTemplate::findFirst($params['id_dana_desa']);

            if (!$exist) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Template Dana Desa dengan ID #' . $params['id_dana_desa'] . 'tidak ditemukan');
            }
        }

        $kt->nama = $params['nama'];
        $kt->keterangan = $params['keterangan'];
        $kt->id_dana_desa = $params['id_dana_desa'];
        $kt->view_order = $params['view_order'];
        $kt->is_dana_desa = $params['is_dana_desa'];
        $kt->tahun = $params['tahun'];
        $kt->updated_at = date('Y-m-d H:i:s');
        $kt->updated_by = $this->shared->user->um_id;
        $status = $kt->save();

        if (!$status) {
            $errors = $kt->getMessage();

            $error_messages = [];

            foreach ($errors as $key => $error) :
                $error_messages[] = [
                    "field" => $error->getField(),
                    "message" => $error->getMessage()
                ];
            endforeach;

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$kt]);
        }
    }

    public function delete($id)
    {
        $this->checkScopes(['korsupgah.admin']);

        $kt = KorsupgahTemplate::findFirst($id);

        if (empty($kt)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Template dengan ID #' . $id . 'tidak ditemukan');
        endif;

        if (!empty($kt->deleted_at)) :

            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Template dengan ID #' . $id . 'mungkin sudah terhapus');
        endif;

        $date = date('Y-m-d H:i:s');

        $kt->updated_at = $date;
        $kt->deleted_at = $date;
        $kt->updated_by = $this->shared->user->um_id;
        $status = $kt->save();

        if (!$status) {
            $errors = $kt->getMessages();

            $error_messages = [];

            foreach ($errors as $key => $error) :
                $error_messages[] = [
                    "field" => $error->getField(),
                    "message" => $error->getMessages()
                ];
            endforeach;

            throw new ValidationException($error_messages);
        } else {

            return new CollectionPaginatedResponse([$kt]);
        }
    }

    public function create()
    {
        $this->checkScopes(['korsupgah.admin']);

        $rawBody = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $rawBody,
            [
                'nama' => ['validators' => ['required']],
                'keterangan' => ['validators' => []],
                'id_dana_desa' => ['validators' => ['digit']],
                'view_order' => ['validators' => ['digit']],
                'tahun' => ['validators' => ['required', 'digit']],
                'is_dana_desa' => ['validators' => []],
            ]
        );

        if ($params['id_dana_desa'] > 0) {
            $exist = KorsupgahTemplate::findFirst($params['id_dana_desa']);

            if (!$exist) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Template Dana Desa dengan ID #' . $params['id_dana_desa'] . 'tidak ditemukan');
            }
        }

        $kt = new KorsupgahTemplate();
        $kt->nama = $params['nama'];
        $kt->keterangan = $params['keterangan'];
        $kt->created_at = date('Y-m-d H:i:s');
        $kt->created_by = $this->shared->user->um_id;
        $kt->id_dana_desa = $params['id_dana_desa'];
        $kt->view_order = $params['view_order'];
        $kt->is_dana_desa = $params['is_dana_desa'];
        $kt->tahun = $params['tahun'];

        $status = $kt->save();

        if (!$status) {
            $errors = $kt->getMessages();

            $error_messages = [];

            foreach ($errors as $key => $error) :
                $error_messages[] = [
                    "field" => $error->getField(),
                    "message" => $error->getMessage()
                ];
            endforeach;

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$kt]);
        }
    }

    public function restore($id)
    {
        $this->checkScopes(['korsupgah.admin']);

        $kt = KorsupgahTemplate::findFirst($id);

        if (empty($kt)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Template dengan ID #' . $id . 'Tidak Ditemukan');
        endif;

        if (empty($kt->deleted_at)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Template dengan ID #' . $id . 'Sudah Aktif');
        endif;

        $kt->updated_at = date('Y-m-d H:i:s');
        $kt->deleted_at = null;
        $kt->updated_by = $this->shared->user->um_id;
        $status = $kt->save();

        if (!$status) {
            $errors = $kt->getMessages();

            $error_messages = [];

            foreach ($errors as $key => $error) :
                $error_messages[] = [
                    "field" => $error->getField(),
                    "message" => $error->getMessage()
                ];
            endforeach;

            throw new ValidationException($error_messages);
        } else {

            return new CollectionPaginatedResponse([$kt]);
        }
    }

    public function add_question()
    {
        $this->checkScopes(['korsupgah.admin']);

        $rawBody = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $rawBody,
            [
                'id' => ['validators' => ['required', 'digit']],
                'id_sub_indikator' => ['validators' => ['required', 'digit']]
            ]
        );

        $template_id = KorsupgahTemplate::findFirst($params['id']);
        if (empty($template_id)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Template dengan ID #' . $params['id'] . 'tidak diketemukan');
        endif;

        if ($template_id->deleted_at != NULL) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Template dengan ID #' . $params['id'] . 'mungkin sudah terhapus');
        endif;

        $sub_indikator_id = KorsupgahSubIndikator::findFirst($params['id_sub_indikator']);
        if (empty($sub_indikator_id)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Sub Indikator dengan ID #' . $params['id_sub_indikator'] . 'tidak diketemukan');
        endif;

        if ($sub_indikator_id->deleted_at != NULL) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Sub Indikator dengan ID #' . $params['id_sub_indikator'] . 'mungkin sudah terhapus');
        endif;

        $records = KorsupgahTemplateSubIndikator::find([
            'conditions' => 'id_template=:id_template: AND id_sub_indikator=:id_sub_indikator:',
            'bind' => [
                'id_template' => $params['id'],
                'id_sub_indikator' => $params['id_sub_indikator']
            ]
        ]);

        // check sub indikator if already exist
        if (count($records) > 0) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Sub Indikator dengan ID #' . $params['id_sub_indikator'] . 'sudah terdaftar ditemplate');
        endif;


        $ktsi = new KorsupgahTemplateSubIndikator();
        $ktsi->id_template = $params['id'];
        $ktsi->id_sub_indikator = $params['id_sub_indikator'];
        $ktsi->created_at = date('Y-m-d H:i:s');
        $ktsi->created_by = $this->shared->user->um_id;
        $status = $ktsi->save();

        if (!$status) {
            $errors = $ktsi->getMessage();

            $error_messages = [];
            foreach ($errors as $err) :
                $error_messages[] = [
                    'field' => $err->getField(),
                    'message' => $err->getMessage()
                ];
            endforeach;

            throw new ValidationException($error_messages);
        } else {

            return new CollectionPaginatedResponse([$ktsi]);
        }
    }

    public function add_questions()
    {
        $this->checkScopes(['korsupgah.admin']);
        $rawBody = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $rawBody,
            [
                'id' => ['validators' => ['required', 'digit']],
                'id_sub_indikator_list' => ['validators' => ['required']]
            ]
        );

        if (!is_array($params['id_sub_indikator_list'])) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Format ID Sub Indikator List tidak valid');
        endif;

        $id_sub_indikator = array_unique(array_values($params['id_sub_indikator_list']));

        $template = KorsupgahTemplate::findFirst($params['id']);

        if (empty($template)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey template tidak diketemukan');
        endif;

        if ($template->deleted_at != NULL) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey template mungkin sudah terhapus');
        endif;

        $sub_indikator_list = KorsupgahSubIndikator::find([
            'conditions' => 'id in ({ids:array}) AND deleted_at is NULL',
            'bind' => [
                'ids' => array_values($id_sub_indikator)
            ]
        ]);

        $sub_indikator_master_data = [];

        foreach ($sub_indikator_list as $key => $sub_indikator_ids) :
            $sub_indikator_master_data[] = $sub_indikator_ids->id;
        endforeach;

        $not_exist_ids = array_diff($id_sub_indikator, $sub_indikator_master_data);

        if (!empty($not_exist_ids)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Sub Indikator dengan ID ' . implode(",", $not_exist_ids) . ' tidak aktif/terdaftar');
        endif;

        $registered_questions = KorsupgahTemplateSubIndikator::find([
            'conditions' => 'id_template=:id_template: AND deleted_at is NULL',
            'bind' => [
                'id_template' => $params['id']
            ]
        ]);

        $registered_questions_ids = [];

        foreach ($registered_questions as $registered_question) :
            $registered_questions_ids[] = $registered_question->id_sub_indikator;
        endforeach;

        foreach ($sub_indikator_master_data as $key => $sub_indikator_id) :
            if (!in_array($sub_indikator_id, $registered_questions_ids)) :
                $ktt = new KorsupgahTemplateSubIndikator();
                $ktt->id_template = $params['id'];
                $ktt->id_sub_indikator = $sub_indikator_id;
                $ktt->created_by = $this->shared->user->um_id;
                $status = $ktt->save();
            endif;
        endforeach;

        return new CollectionPaginatedResponse([]);
    }

    public function remove_question()
    {
        $this->checkScopes(['korsupgah.admin']);

        $rawBody = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $rawBody,
            [
                'id' => ['validators' => ['required', 'digit']],
                'id_sub_indikator' => ['validators' => ['required', 'digit']]
            ]
        );

        $template = KorsupgahTemplate::findFirst($params['id']);

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

        if (!empty($template->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Template sudah terhapus');
        }

        $sub_indikator = KorsupgahSubIndikator::findFirst($params['id_sub_indikator']);

        if (empty($sub_indikator)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Sub Indikator tidak ditemukan');
        }

        if (!empty($sub_indikator->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Sub Indikator sudah terhapus');
        }

        $records = KorsupgahTemplateSubIndikator::find([
            'conditions' => 'id_template=:id_template: AND id_sub_indikator=:id_sub_indikator:',
            'bind' => [
                'id_template' => $params['id'],
                'id_sub_indikator' => $params['id_sub_indikator']
            ]
        ]);

        if (count($records) < 1) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Pertanyaan dengan Target ID " . $params['id'] . " tidak terdaftar di Survey template ini");
        }

        $template_target = $records[0];
        $status = $template_target->delete();

        if (!$status) {
            $errors = $template_target->getMessages();
            $error_messages = [];

            foreach ($errors as $key => $error) {
                $error_messages[] = ["field" => $error->getField(), "message" => $error->getMessage()];
            }

            throw new ValidationException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$template_target]);
        }
    }

    public function set_questions()
    {
        $this->checkScopes(['korsupgah.admin']);

        $rawBody = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $rawBody,
            [
                'id' => ['validators' => ['required']],
                'id_sub_indikator_list' => ['validators' => ['required']]
            ]
        );

        if (!is_array($params['id_sub_indikator_list'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Format ID Sub Indikator List tidak valid');
        }

        $id_sub_indikator_list = array_unique(array_values($params['id_sub_indikator_list']));

        $template = KorsupgahTemplate::findFirst($params['id']);

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

        if (!empty($template->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Template sudah terhapus');
        }


        $sub_indikator_list = KorsupgahSubIndikator::find([
            'conditions' => 'id in ({ids:array}) AND deleted_at is NULL',
            'bind' => [
                'ids' => array_values($id_sub_indikator_list)
            ]
        ]);

        // Check Expected sub indikator List exists in master data

        $sub_indikator_master_data_ids = [];

        foreach ($sub_indikator_list as $key => $sub_indikator) {
            $sub_indikator_master_data_ids[] = $sub_indikator->id;
        }

        $not_exist_ids = array_diff($id_sub_indikator_list, $sub_indikator_master_data_ids);

        if (!empty($not_exist_ids)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Sub Indikator dengan ID ' . implode(",", $not_exist_ids) . ' tidak aktif/terdaftar');
        }

        $registered_questions = KorsupgahTemplateSubIndikator::find([
            'conditions' => 'id_template=:id_template: AND deleted_at is NULL',
            'bind' => [
                'id_template' => $params['id']
            ]
        ]);


        $registered_question_ids = [];

        // Delete all registered question that doesn't exist in params
        foreach ($registered_questions as $key => $registered_question) {
            $registered_question_ids[] = $registered_question->id_sub_indikator;
            if (!in_array($registered_question->id_sub_indikator, $sub_indikator_master_data_ids)) {
                $status = $registered_question->delete();
            }
        }

        // Otherwise, Register new question to this survey template
        foreach ($sub_indikator_master_data_ids as $key => $sub_indikator_id) {
            if (!in_array($sub_indikator_id, $registered_question_ids)) {
                $ktt = new KorsupgahTemplateSubIndikator();
                $ktt->id_template = $params['id'];
                $ktt->id_sub_indikator = $sub_indikator_id;
                $ktt->created_by = $this->shared->user->um_id;
                $status = $ktt->save();
            }
        }

        return new CollectionPaginatedResponse([]);
    }

    public function detail($id)
    {
        $this->checkIsAuthenticated();

        $template = KorsupgahTemplate::findFirst($id);

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

        $detail = $template->getDetail();

        return new CollectionPaginatedResponse([$detail]);
    }

    private function baseListWithAreaIntervensi($isKorwil = false)
    {
        $whereAreaFokus = "";
        if ($this->hasScopes(['korwil.monitoring_kemdagri']) && $isKorwil) {
            if ($this->shared->user->id_jabatan != null) {
                $areaFokus = array_column(KorsupgahAreaFokusPenanggungJawab::find([
                    'conditions' => 'id_jabatan = ?0',
                    'bind' => [$this->shared->user->id_jabatan]
                ])->toArray(), 'id_area_fokus');

                if (!empty($areaFokus))
                    $whereAreaFokus = " AND id_area_fokus IN ('" . implode("', '", $areaFokus) . "')";
            }
        }


        $items = $this->rawQuery("
                SELECT t.id, t.nama, t.id_dana_desa, ai.id as area_intervensi_id, ai.nama as area_intervensi_nama, t.is_dana_desa, t.tahun
                FROM jaga.korsupgah_template_sub_indikator tsi
                	JOIN jaga.korsupgah_sub_indikator si	on tsi.id_sub_indikator = si.id
                  JOIN jaga.korsupgah_indikator i on si.id_indikator = i.id
                  JOIN jaga.korsupgah_area_intervensi ai on i.id_area_intervensi = ai.id
                	RIGHT OUTER JOIN jaga.korsupgah_template t on tsi.id_template = t.id
                WHERE t.deleted_at IS NULL " . $whereAreaFokus . "
                group by t.id, t.nama, ai.id, ai.nama
                ORDER BY t.view_order, ai.id
        ", []);

        $templates = Collection::from($items)
            ->groupBy(function ($item) {
                return $item['id'];
            })
            ->values()
            ->map(function ($areaIntervensiEntries) {
                $areaIntervensiList = $areaIntervensiEntries->map(function ($areaIntervensi) {
                    return [
                        'id' => $areaIntervensi['area_intervensi_id'],
                        'nama' => $areaIntervensi['area_intervensi_nama'],
                        'id_dana_desa' => $areaIntervensi['id_dana_desa'],
                    ];
                })
                    ->values()
                    ->toArray();

                return [
                    'id' => $areaIntervensiEntries->get(0)['id'],
                    'nama' => $areaIntervensiEntries->get(0)['nama'],
                    'id_dana_desa' => $areaIntervensiEntries->get(0)['id_dana_desa'],
                    'is_dana_desa' => $areaIntervensiEntries->get(0)['is_dana_desa'],
                    'tahun' => $areaIntervensiEntries->get(0)['tahun'],
                    'area_intervensi_list' => $areaIntervensiList
                ];
            })
            ->toArray();

        // Get additional data from e-koorsupdb
        $neoListAreaIntervensi = $this->getNeoListAreaIntervensi(null);
        if ($neoListAreaIntervensi['success'] && !empty($neoListAreaIntervensi['data'])) {
            $templates = array_merge($neoListAreaIntervensi['data'], $templates);
        }

        return $templates;
    }

    private function getNeoListAreaIntervensi($year = null)
    {
        try {
            $mcpService = new McpService();
            $params = [];
            if ($year !== null) {
                $params['year'] = $year;
            }
            $result = $mcpService->get('/api/template', $params);

            return [
                'success' => $result->success,
                'data' => $result->data
            ];
        } catch (Exception $e) {
            throw $e;
        }
    }

    public function listWithAreaIntervensi()
    {
        $params = $this->request->get();
        $isKorwil = false;
        if (array_key_exists('isKorwil', $params)) $isKorwil = filter_var($params['isKorwil'], FILTER_VALIDATE_BOOLEAN);

        $result = $this->baseListWithAreaIntervensi($isKorwil);

        return new CollectionPaginatedResponse($result);
    }

    public function listForInstansi($instansiId)
    {
        $this->checkIsAuthenticated();

        $getParams = $this->request->get();

        $getParams['limit'] = array_key_exists('limit', $getParams) ? $getParams['limit'] : 100;
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['id_periode'] = array_key_exists('id_periode', $getParams) ? $getParams['id_periode'] : '';
        $getParams['stat'] = array_key_exists('stat', $getParams) ? $getParams['stat'] : 0;


        $params =  $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'id_periode' => ['validators' => ['digit']],
                'stat' => ['validators' => ['digit']]
            ]
        );

        $idInstansi = $instansiId;
        $stat = $params['stat'];
        $idPeriode = $params['id_periode'];
        $keyword = $params['keyword'];

        $result = $this->detailList($idInstansi, $stat, $idPeriode, $keyword);

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

    public function listMy()
    {
        $this->checkScopes(['korsupgah.answer']);

        $getParams = $this->request->get();

        $getParams['limit'] = array_key_exists('limit', $getParams) ? $getParams['limit'] : 100;
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['id_periode'] = array_key_exists('id_periode', $getParams) ? $getParams['id_periode'] : '';
        $getParams['stat'] = array_key_exists('stat', $getParams) ? $getParams['stat'] : 0;


        $params =  $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'id_periode' => ['validators' => ['digit']],
                'stat' => ['validators' => ['digit']]
            ]
        );

        $current_instansi = $this->shared->user->instansi;

        if (empty($current_instansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki instansi');
        }

        if (!empty($current_instansi->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi user telah dihapus/tidak aktif');
        }

        $idInstansi = $current_instansi->id;
        $stat = $params['stat'];
        $idPeriode = $params['id_periode'];
        $keyword = $params['keyword'];

        $result = $this->detailList($idInstansi, $stat, $idPeriode, $keyword);

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

    private function detailList($idInstansi, $stat = 0, $idPeriode = null, $keyword = null)
    {
        $where = "";
        $bind = ["id_instansi" => $idInstansi];

        if (!empty($idPeriode)) :
            $where .= ' and id_periode = :id_periode';
            $bind['id_periode'] = $idPeriode;
        endif;

        if (!empty($keyword)) :
            $where .= ' and ks.keterangan ilike :keyword';
            $bind["keyword"] = '%' . $keyword . '%';
        endif;

        $result = [];
        if ($stat > 0) {
            $baseQuery = "
                from jaga.korsupgah_template kt
                left join jaga.korsupgah_survey ks on ks.id_template = kt.id
                left join jaga.instansi i on i.id = ks.id_instansi
                left join (
                    select id_template, count(id) as total from jaga.korsupgah_template_sub_indikator tsi group by id_template
                ) question on question.id_template = kt.id
                left join (
                    select id_survey, count(ka.id) as total
                    from jaga.korsupgah_survey ks
                    left join jaga.korsupgah_answer ka on ka.id_survey = ks.id
                    group by id_survey
                ) answer on answer.id_survey = ks.id
                left join (
                    select id_survey, count(distinct id_question) as total
                    from jaga.korsupgah_survey ks
                    left join jaga.korsupgah_answer ka on ka.id_survey = ks.id
                    group by id_survey
                ) answered on answered.id_survey = ks.id
                left join (
                    select id_survey, count(ka.id) as total
                    from jaga.korsupgah_survey ks
                    left join jaga.korsupgah_answer ka on ka.id_survey = ks.id
                    where ka.status >= 1
                    group by id_survey
                ) verified on verified.id_survey = ks.id
                where i.id = :id_instansi $where
            ";
            $rows = $this->rawQuery("select kt.*, ks.id as id_survey, question.total as total_question, answer.total as total_answer, answered.total as total_answered, verified.total as total_verified $baseQuery order by view_order", $bind);
            foreach ($rows as $row) {
                $i = $row;
                $i["percentage_answered"] = $row["total_question"] > 0 ? round(($row["total_answered"] / $row["total_question"]) * 100, 1) : 0;
                $i["percentage_verified"] = $row["total_answer"] > 0 ? round(($row["total_verified"] / $row["total_answer"]) * 100, 1) : 0;
                $result[] = $i;
            }
        } else {
            $baseQuery = "
                from jaga.korsupgah_template kt
                left join jaga.korsupgah_survey ks on ks.id_template = kt.id
                left join jaga.instansi i on i.id = ks.id_instansi
                where i.id = :id_instansi $where
            ";
            $result = $this->rawQuery("select kt.*, ks.id as id_survey $baseQuery order by view_order", $bind);
        }

        $total = $this->rawQuery("select count(*) as total $baseQuery", $bind)[0]['total'];

        return [
            "result" => $result,
            "total" => $total
        ];
    }

    private function neoBaseScores($year)
    {
        $query = "
                SELECT
                    ins.id,
                    ins.nama,
                    mprov.provinsi_name
                FROM
                    jaga.instansi ins
                    LEFT JOIN jaga.master_provinsi mprov
                        ON ins.id_provinsi = mprov.provinsi_code::INTEGER
                WHERE
                    ins.id_tipe IN (1, 2)
                    AND ins.deleted_at IS NULL
                ORDER BY
                    ins.id
            ";

        $instansi = $this->rawQuery($query);
        if (empty($instansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi tidak ditemukan');
        }

        $instansiIds = array_column($instansi, 'id');
        $instansiIdsString = implode(',', $instansiIds);

        // Setup URI parameters for the API call
        $uriParams = '';
        if (!empty($instansiIds)) {
            $uriParams = '?instansi_ids=' . $instansiIdsString;
        }

        // Create a lookup array for instansi data
        $instansiLookup = [];
        foreach ($instansi as $inst) {
            $instansiLookup[$inst['id']] = [
                'nama' => $inst['nama'],
                'provinsi_name' => $inst['provinsi_name']
            ];
        }

        try {
            $mcpService = new McpService($this->logger);
            $result = $mcpService->get("/api/template/score/{$year}{$uriParams}", [], null, true);

            if ($result->success && !empty($result->data)) {
                // Convert to array if it's an object
                $resultData = is_array($result->data) ? $result->data : (array) $result->data;
            } else {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Fetching data failed');
            }
        } catch (Exception $e) {
            throw $e;
        }

        // Merge instansi data with result
        foreach ($resultData as $key => &$item) {
            // Convert item to array if it's an object
            if (is_object($item)) {
                $item = (array) $item;
            }
            $item['rank'] = $key + 1;

            if (isset($item['id']) && isset($instansiLookup[$item['id']])) {
                $item['nama'] = $instansiLookup[$item['id']]['nama'];
                $item['provinsi_name'] = $instansiLookup[$item['id']]['provinsi_name'];
            }
        }

        return $resultData;
    }


    private function baseScores($templateId, $idTempDanaDesa = 0)
    {
        $template = KorsupgahTemplate::findById($templateId);
        $toCharPrefix = "FM999999990.00";

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

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

        $whereTemplate = "";
        $bind = [
            'templateId' => $templateId,
        ];

        if ($idTempDanaDesa > 0) {
            $whereTemplate = "(tsi.id_template = :templateId or tsi.id_template = :templateDanaDesa)";
            $bind['templateDanaDesa'] = $idTempDanaDesa;
        } else {
            $whereTemplate = "(tsi.id_template = :templateId)";
        }

        $areaIntervensiList = $this->rawQuery("
SELECT distinct(ai.id), ai.nama, ai.presentase_bobot, ai.presentase_bobot_wo_dana_desa
FROM jaga.korsupgah_template_sub_indikator tsi
	JOIN jaga.korsupgah_sub_indikator si	on tsi.id_sub_indikator = si.id
    JOIN jaga.korsupgah_indikator i on si.id_indikator = i.id
    JOIN jaga.korsupgah_area_intervensi ai on i.id_area_intervensi = ai.id
WHERE " . $whereTemplate . "
        AND tsi.deleted_at IS NULL
ORDER BY ai.id
        ", $bind);

        $selectQueries = '';
        $selectQueries1 = '';
        $selectQueries2 = '';

        foreach ($areaIntervensiList as $key => $areaIntervensi) {
            $selectQueries .= 'to_char(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000) FILTER (WHERE ai.id = ' . $areaIntervensi['id'] . '), :toCharPrefix) as nilai_' . $areaIntervensi['id'] . ',';

            $selectQueries1 .= 'to_char(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000) FILTER (WHERE ai.id = ' . $areaIntervensi['id'] . '), \'' . $toCharPrefix . '\') as nilai_' . $areaIntervensi['id'] . ',';

            $selectQueries2 .= '(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000) FILTER (WHERE ai.id = ' . $areaIntervensi['id'] . ')) as nilai_' . $areaIntervensi['id'] . ',';
        }

        $whereTemplateScore = "";
        if ($idTempDanaDesa > 0) {
            $whereTemplateScore = '(s.id_template = ' . $templateId . ' OR s.id_template = ' . $idTempDanaDesa . ')';
        } else {
            $whereTemplateScore = '(s.id_template = ' . $templateId . ')';
        }
        $whereTemplateScore .= " and s.deleted_at IS NULL";

        $whereBPKP = '';
        if ($this->hasScopes(['korwil.monitoring_bpkp'])) {
            $bpkpProvinsi = array_column(KorwilUserBpkpProvinsi::find([
                'conditions' => 'user_id = ?0',
                'bind' => [$this->shared->user->um_id]
            ])->toArray(), 'provinsi_id');
            if (!empty($bpkpProvinsi)) {
                $cond = "('" . implode("','", $bpkpProvinsi) . "')";
                $whereBPKP = " AND (case when riw.id_provinsi is null then ins.id_provinsi IN " . $cond . " else riw.id_provinsi IN " . $cond . " end) ";
            }
        }

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

        $qScore = '
SELECT
	ins.id,
    ins.nama,
    ins.flag_dana_desa,
    mprov.provinsi_name,
    kti.team_id,
    kti.team_name,
    kp_direktorat.nama as direktorat,
    ' .
            $selectQueries2 . '
    to_char(sum(coalesce(answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot * ai.presentase_bobot / 1000000) - COALESCE(kmps.score, 0), \'' . $toCharPrefix . '\') as nilai_total,
    sum(coalesce(answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot * ai.presentase_bobot / 1000000) - COALESCE(kmps.score, 0) as nilai_total_num,
    COALESCE(kmps.score, 0) 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 mprov.provinsi_code::INTEGER = coalesce(riw.id_provinsi, ins.id_provinsi) 
    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
    left join jaga.korsupgah_pengampu kp_satgas ON kp_satgas.id = s.id_pengampu
    left join jaga.korsupgah_pengampu kp_direktorat ON kp_direktorat.id = kp_satgas.id_parent
    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 ' . $whereTemplateScore . $whereBPKP . ' 
GROUP BY ins."id", ins.nama, kti.team_id, kti.team_name, mprov.provinsi_name, kp_direktorat.nama, kmps.score
ORDER BY nilai_total_num DESC
        ';

        // echo "<pre>";
        // print_r($qScore);
        // echo "</pre>";
        // die;
        $result = $this->rawQuery($qScore);

        //         $result = $this->rawQuery('
        // SELECT
        // 	ins.id,
        //     ins.nama,
        //     mprov.provinsi_name,
        //     kti.team_id,' .
        //             $selectQueries . '
        //     to_char(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot * ai.presentase_bobot / 1000000), :toCharPrefix) as nilai_total,
        //     sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot * ai.presentase_bobot / 1000000) as nilai_total_num
        // 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
        // 	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 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
        // 		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 jaga.korwil_team_instansi kti on kti.instansi_id = ins.id
        // WHERE s.id_template = '.$templateId.'
        // GROUP BY ins."id", ins.nama, kti.team_id, mprov.provinsi_name
        // ORDER BY nilai_total_num DESC
        //         ', [
        //             'templateId' => $templateId,
        //             'toCharPrefix' => $toCharPrefix
        //         ]);

        for ($i = 0; $i < count($result); $i++) {
            $nilai_dd = 0;
            // recalculate total
            foreach ($areaIntervensiList as $key => $ai) {
                $iNilai = 'nilai_' . $ai['id'];
                // without dana desa, add dana desa (8) bobot to Optimalisasi Pendapatan Daerah (6)
                if ($result[$i]['flag_dana_desa'] == 0 && $ai['id'] == 6) {
                    $iBobot = $areaIntervensiList[7]['presentase_bobot'] + $ai['presentase_bobot'];
                } elseif ($result[$i]['flag_dana_desa'] == 0) {
                    $iBobot = $ai['presentase_bobot_wo_dana_desa'];
                } else {
                    $iBobot = $ai['presentase_bobot'];
                }

                $nilai_dd += $iBobot * $result[$i][$iNilai] / 100;

                $result[$i][$iNilai] = number_format((float) $result[$i][$iNilai], 2, '.', '');
            }
            // add rank
            // $result[$i]['rank'] = $i + 1;
            // add nilai total dengan/tanpa dana desa

            // kurangi penalty score, jika ada

            $result[$i]['total_mcp_score'] = number_format((float)$nilai_dd, 2, '.', '');
            $nilai_dd -= intval($result[$i]['penalty_score']);
            $result[$i]['total_ddd'] = number_format((float)$nilai_dd, 2, '.', '');
        }

        // rank
        usort($result, function ($a, $b) {
            return $a['total_ddd'] < $b['total_ddd'];
        });

        $rank = 1;
        $res = [];
        foreach ($result as $key => $r) {
            $r['rank'] = $rank++;
            array_push($res, $r);
        }

        return $res;
    }

    public function scores($templateId)
    {
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'year' => ['validators' => ['digit']],
            ]
        );
        if ($params['year'] && $params['year'] >= 2025) {
            $result = $this->neoBaseScores($params['year']);
        } else {
            $result = $this->baseScores($templateId);
        }

        return new CollectionPaginatedResponse($result);
    }

    public function scoresAsExcel($idTemplate)
    {
        $params = $this->request->get();
        if (!array_key_exists('level', $params)) {
            $params['level'] = 'AI';
        }
        $this->validate($params, [
            'level' => ['validators' => [
                ['name' => 'inclusion_in', 'params' => ['AI', 'I', 'SI', 'all']]
            ]]
        ]);

        if ($params['year'] && $params['year'] >= 2025 && $params['level'] === 'all') {
            $scores = $this->neoBaseScores($params['year']);
            if ($scores === null || !is_array($scores)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Gagal mendapatkan data skor dari e-koorsupdb');
            }

            $areaIntervensiList = $this->getNeoListAreaIntervensi($params['year']);
            if (!$areaIntervensiList['success'] || empty($areaIntervensiList['data'])) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Gagal mendapatkan data area intervensi dari e-koorsupdb');
            }
            $areaIntervensiList = json_decode(json_encode($areaIntervensiList['data'][0]), true);
            $writer = $this->writeExcelMcsp2025($scores, [], $areaIntervensiList['area_intervensi_list']);
            return $writer->save('php://output');
        }

        $level = $params['level'];
        switch ($level) {
            case 'AI':
                $writer = $this->scoresAsExcelAI($idTemplate);
                break;
            case 'I':
                $this->checkScopes(['korsupgah.admin']);
                $this->validate($params, [
                    'id_area_intervensi' => ['validators' => ['required']]
                ]);
                $idAreaIntervensi = $params['id_area_intervensi'];
                $writer = $this->scoresAsExcelI($idTemplate, $idAreaIntervensi);
                break;
            case 'SI':
                $this->checkScopes(['korsupgah.admin']);
                $this->validate($params, [
                    'id_area_intervensi' => ['validators' => ['required']]
                ]);
                $idAreaIntervensi = $params['id_area_intervensi'];
                $writer = $this->scoresAsExcelSI($idTemplate, $idAreaIntervensi);
                break;
            default:
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Level tidak valid.");
        }

        return $writer->save('php://output');
    }

    private function neoScoresAsExcel($year) {}

    private function scoresAsExcelAI($templateId)
    {
        $template = KorsupgahTemplate::findById($templateId);
        $toCharPrefix = "FM999999990.00";

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

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

        $bind = [
            'templateId' => $templateId,
        ];

        if ($idTempDanaDesa > 0) {
            $whereTemplate = "(tsi.id_template = :templateId or tsi.id_template = :templateDanaDesa)";
            $bind['templateDanaDesa'] = $idTempDanaDesa;
        } else {
            $whereTemplate = "(tsi.id_template = :templateId)";
        }

        $areaIntervensiList = $this->rawQuery(
            "
            SELECT distinct(ai.id), ai.nama, ai.presentase_bobot, ai.presentase_bobot_wo_dana_desa
            FROM jaga.korsupgah_template_sub_indikator tsi
                JOIN jaga.korsupgah_sub_indikator si	on tsi.id_sub_indikator = si.id
                JOIN jaga.korsupgah_indikator i on si.id_indikator = i.id
                JOIN jaga.korsupgah_area_intervensi ai on i.id_area_intervensi = ai.id
            WHERE " . $whereTemplate . "
                    AND tsi.deleted_at IS NULL
            ORDER BY ai.id
                    ",
            $bind
        );

        $selectQueries = '';
        $selectQueries1 = '';
        $selectQueries2 = '';

        foreach ($areaIntervensiList as $key => $areaIntervensi) {
            $selectQueries .= 'to_char(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000) FILTER (WHERE ai.id = ' . $areaIntervensi['id'] . '), :toCharPrefix) as nilai_' . $areaIntervensi['id'] . ',';

            $selectQueries1 .= 'to_char(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000) FILTER (WHERE ai.id = ' . $areaIntervensi['id'] . '), \'' . $toCharPrefix . '\') as nilai_' . $areaIntervensi['id'] . ',';

            $selectQueries2 .= '(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000) FILTER (WHERE ai.id = ' . $areaIntervensi['id'] . ')) as nilai_' . $areaIntervensi['id'] . ',';
        }

        if ($idTempDanaDesa > 0) {
            $whereTemplateScore = '(s.id_template = ' . $templateId . ' OR s.id_template = ' . $idTempDanaDesa . ')';
        } else {
            $whereTemplateScore = '(s.id_template = ' . $templateId . ')';
        }
        $whereTemplateScore .= " and s.deleted_at IS NULL";

        $whereBPKP = '';
        if ($this->hasScopes(['korwil.monitoring_bpkp'])) {
            $bpkpProvinsi = array_column(KorwilUserBpkpProvinsi::find([
                'conditions' => 'user_id = ?0',
                'bind' => [$this->shared->user->um_id]
            ])->toArray(), 'provinsi_id');
            if (!empty($bpkpProvinsi)) {
                $cond = "('" . implode("','", $bpkpProvinsi) . "')";
                $whereBPKP = " AND (case when riw.id_provinsi is null then ins.id_provinsi IN " . $cond . " else riw.id_provinsi IN " . $cond . " end) ";
            }
        }

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

        $qScore = '
            SELECT
                ins.id,
                ins.nama,
                ins.flag_dana_desa,
                mprov.provinsi_name,' . $selectQueries2 . '
                to_char(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot * ai.presentase_bobot / 1000000), \'' . $toCharPrefix . '\') as nilai_total,
                sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot * ai.presentase_bobot / 1000000) as nilai_total_num,
                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 mprov.provinsi_code::INTEGER = coalesce(riw.id_provinsi, ins.id_provinsi) 
                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 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 ' . $whereTemplateScore . $whereBPKP . ' 
            GROUP BY ins."id", ins.nama, mprov.provinsi_name, kmps.score
            ORDER BY nilai_total_num DESC
        ';
        $result = $this->rawQuery($qScore);

        for ($i = 0; $i < count($result); $i++) {
            $nilai_dd = 0;
            foreach ($areaIntervensiList as $key => $ai) {
                $iNilai = 'nilai_' . $ai['id'];
                if ($result[$i]['flag_dana_desa'] == 0 && $ai['id'] == 6) {
                    $iBobot = $areaIntervensiList[7]['presentase_bobot'] + $ai['presentase_bobot'];
                } elseif ($result[$i]['flag_dana_desa'] == 0) {
                    $iBobot = $ai['presentase_bobot_wo_dana_desa'];
                } else {
                    $iBobot = $ai['presentase_bobot'];
                }

                $nilai_dd += $iBobot * $result[$i][$iNilai] / 100;

                $result[$i][$iNilai] = number_format((float) $result[$i][$iNilai], 2, '.', '');
            }
            $nilai_dd -= intval($result[$i]['penalty_score']);
            $result[$i]['total_ddd'] = number_format((float)$nilai_dd, 2, '.', '');
        }

        // rank
        usort($result, function ($a, $b) {
            return $a['total_ddd'] < $b['total_ddd'];
        });

        $rank = 1;
        $scores = [];
        foreach ($result as $key => $r) {
            $r['rank'] = $rank++;
            array_push($scores, $r);
        }
        $templateData = $this->baseListWithAreaIntervensi();
        $templateIndex = array_search($templateId, array_column($templateData, 'id'));

        if (false === $templateIndex) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data Template tidak ditemukan');
        }

        $areaIntervensiListDanaDesa = [];
        if ($templateData[$templateIndex]['id_dana_desa']) {
            $templateDanaDesaIndex = array_search($templateData[$templateIndex]['id_dana_desa'], array_column($templateData, 'id'));
            $areaIntervensiListDanaDesa = $templateData[$templateDanaDesaIndex]['area_intervensi_list'];
        }

        $areaIntervensiList = $templateData[$templateIndex]['area_intervensi_list'];
        foreach ($areaIntervensiListDanaDesa as $aildd) {
            $found = false;
            foreach ($areaIntervensiList as $ail) {
                if ($aildd['id'] == $ail['id']) {
                    $found = true;
                }
            }
            if (!$found) {
                array_push($areaIntervensiList, $aildd);
            }
        }

        return $this->writeExcel($scores, [], $areaIntervensiList);
    }

    private function scoresAsExcelI($idTemplate, $idAreaIntervensi)
    {
        $template = KorsupgahTemplate::findById($idTemplate);
        $areaIntervensi = KorsupgahAreaIntervensi::findById($idAreaIntervensi);
        $toCharPrefix = "FM999999990.00";

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

        $bind = [
            'templateId' => $idTemplate,
            'areaIntervensiId' => $idAreaIntervensi
        ];

        $areaInterVensiList = $areaIntervensi->toArray();
        $indikatorList = $this->rawQuery("
            SELECT distinct(i.id), ai.id as parent_id, i.nama, i.presentase_bobot
            FROM jaga.korsupgah_template_sub_indikator tsi
                JOIN jaga.korsupgah_sub_indikator si	on tsi.id_sub_indikator = si.id
                JOIN jaga.korsupgah_indikator i on si.id_indikator = i.id
                JOIN jaga.korsupgah_area_intervensi ai on i.id_area_intervensi = ai.id
            WHERE tsi.id_template = :templateId AND tsi.deleted_at IS NULL AND i.id_area_intervensi = :areaIntervensiId
            ORDER BY i.id
        ", $bind);

        $selectQueries = '';
        foreach ($indikatorList as $key => $indikator) {
            $idIndikator = $indikator["id"];
            $selectQueries .= 'to_char(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot / 100) FILTER (WHERE i.id = ' . $idIndikator . '), \'' . $toCharPrefix . '\') as nilai_' . $idIndikator . ',';
        }

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

        $qScore = '
            SELECT
                ins.id,
                ins.nama,
                ins.flag_dana_desa,
                mprov.provinsi_name,' . $selectQueries . '
                to_char(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000), \'' . $toCharPrefix . '\') as nilai_total,
                sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000) as nilai_total_num
            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 mprov.provinsi_code::INTEGER = coalesce(riw.id_provinsi, ins.id_provinsi) 
                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
            WHERE s.id_template = :templateId and s.deleted_at is null and ai.id = :areaIntervensiId 
            GROUP BY ins."id", ins.nama, mprov.provinsi_name
            ORDER BY nilai_total_num DESC
        ';

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

        for ($i = 0; $i < count($result); $i++) {
            $nilai_dd = 0;
            foreach ($indikatorList as $key => $indikator) {
                $iNilai = 'nilai_' . $indikator['id'];
                $iBobot = $indikator['presentase_bobot'];
                $nilai_dd += $iBobot * $result[$i][$iNilai] / 100;
                $result[$i][$iNilai] = number_format((float) $result[$i][$iNilai], 2, '.', '');
            }
            $result[$i]['total_ddd'] = number_format((float)$nilai_dd, 2, '.', '');
        }
        // rank
        usort($result, function ($a, $b) {
            return $a['total_ddd'] < $b['total_ddd'];
        });

        $rank = 1;
        $scores = [];
        foreach ($result as $key => $r) {
            $r['rank'] = $rank++;
            array_push($scores, $r);
        }

        return $this->writeExcel($scores, $areaInterVensiList, $indikatorList);
    }

    private function scoresAsExcelSI($idTemplate, $idAreaIntervensi)
    {
        $template = KorsupgahTemplate::findById($idTemplate);
        $areaIntervensi = KorsupgahAreaIntervensi::findById($idAreaIntervensi);
        $toCharPrefix = "FM999999990.00";

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

        $bind = [
            'templateId' => $idTemplate,
            'areaIntervensiId' => $idAreaIntervensi
        ];

        $indikatorList = $this->rawQuery("
            SELECT distinct(i.id), i.nama, i.presentase_bobot
            FROM jaga.korsupgah_template_sub_indikator tsi
                JOIN jaga.korsupgah_sub_indikator si	on tsi.id_sub_indikator = si.id
                JOIN jaga.korsupgah_indikator i on si.id_indikator = i.id
                JOIN jaga.korsupgah_area_intervensi ai on i.id_area_intervensi = ai.id
            WHERE tsi.id_template = :templateId AND tsi.deleted_at IS NULL AND i.id_area_intervensi = :areaIntervensiId
            ORDER BY i.id
        ", $bind);
        $subIndikatorList = $this->rawQuery("
            SELECT distinct(si.id), i.id as parent_id, si.nama, si.presentase_bobot
            FROM jaga.korsupgah_template_sub_indikator tsi
                JOIN jaga.korsupgah_sub_indikator si	on tsi.id_sub_indikator = si.id
                JOIN jaga.korsupgah_indikator i on si.id_indikator = i.id
                JOIN jaga.korsupgah_area_intervensi ai on i.id_area_intervensi = ai.id
            WHERE tsi.id_template = :templateId AND tsi.deleted_at IS NULL AND i.id_area_intervensi = :areaIntervensiId
            ORDER BY si.id
        ", $bind);

        $selectQueries = '';
        foreach ($subIndikatorList as $key => $subIndikator) {
            $idSubIndikator = $subIndikator["id"];
            $selectQueries .= 'to_char(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float) FILTER (WHERE si.id = ' . $idSubIndikator . '), \'' . $toCharPrefix . '\') as nilai_' . $idSubIndikator . ',';
        }

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

        $qScore = '
            SELECT
                ins.id,
                ins.nama,
                ins.flag_dana_desa,
                mprov.provinsi_name,' . $selectQueries . '
                to_char(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float), \'' . $toCharPrefix . '\') as nilai_total,
                sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float) as nilai_total_num
            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 mprov.provinsi_code::INTEGER = coalesce(riw.id_provinsi, ins.id_provinsi) 
                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
            WHERE s.id_template = :templateId and s.deleted_at is null and ai.id = :areaIntervensiId 
            GROUP BY ins."id", ins.nama, mprov.provinsi_name
            ORDER BY nilai_total_num DESC
        ';

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

        for ($i = 0; $i < count($result); $i++) {
            $nilai_dd = 0;
            foreach ($subIndikatorList as $subIndikator) {
                $iNilai = 'nilai_' . $subIndikator['id'];
                $iBobot = 100;
                $nilai_dd += $iBobot * $result[$i][$iNilai] / 100;
                $result[$i][$iNilai] = number_format((float) $result[$i][$iNilai], 2, '.', '');
            }
            $result[$i]['total_ddd'] = number_format((float)($nilai_dd), 2, '.', '');
        }
        // rank
        usort($result, function ($a, $b) {
            return $a['total_ddd'] < $b['total_ddd'];
        });

        $rank = 1;
        $scores = [];
        foreach ($result as $key => $r) {
            $r['rank'] = $rank++;
            array_push($scores, $r);
        }

        return $this->writeExcel($scores, $indikatorList, $subIndikatorList);
    }

    private function writeExcel($scores, $columns, $subColumns)
    {
        $columnIndex = 1;
        $rowIndex = 1;
        $alphabet = range("A", "Z");

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'No');
        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, "Instansi");
        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, "Provinsi");
        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Faktor Koreksi');
        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Nilai');

        if (is_array($columns) && count($columns) > 0) {
            foreach (range($alphabet[0], $alphabet[$columnIndex - 2]) as $r) {
                $sheet->mergeCells($r . "1:" . $r . "2");
            }
            foreach ($columns as $column) {
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $column['nama']);
                $subColumnIndex = $columnIndex - 1;
                foreach ($subColumns as $subColumn) {
                    if ($subColumn["parent_id"] == $column["id"]) {
                        $sheet->setCellValueByColumnAndRow($subColumnIndex++, $rowIndex + 1, $subColumn['nama']);
                    }
                }
                $sheet->mergeCells($alphabet[$columnIndex - 2] . "1:" . $alphabet[$subColumnIndex - 2] . "1");
                $columnIndex = $subColumnIndex;
            }
            $rowIndex = 3;
            $sheet->getStyle("A1:" . $alphabet[$columnIndex - 2] . "2")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        } else {
            foreach ($subColumns as $subColumn) {
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $subColumn['nama']);
            }
            $rowIndex = 2;
            $sheet->getStyle("A1:" . $alphabet[$columnIndex - 2] . "1");
        }

        foreach ($scores as $score) {
            $columnIndex = 1;
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score['rank']);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score['nama']);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score['provinsi_name']);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score['penalty_score'] ?? 0);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score['total_ddd'] ?? 0);
            foreach ($subColumns as $subColumn) {
                $nilaiKey = 'nilai_' . $subColumn['id'];
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score[$nilaiKey]);
            }
            $rowIndex++;
        }

        $todayLabel = DateHelper::formatTodayIndex();

        $writer = new Xlsx($spreadsheet);
        $filename = $todayLabel . "-RekapitulasiMCP" .  ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer;
    }

    private function writeExcelMcsp2025($scores, $columns, $subColumns)
    {
        $columnIndex = 1;
        $rowIndex = 1;
        $alphabet = range("A", "Z");

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();

        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'No');
        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, "Instansi");
        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, "Provinsi");
        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Nilai Sebelum QA');
        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Nilai Setelah QA');
        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Faktor Koreksi');
        $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, 'Final');

        if (is_array($columns) && count($columns) > 0) {
            foreach (range($alphabet[0], $alphabet[$columnIndex - 2]) as $r) {
                $sheet->mergeCells($r . "1:" . $r . "2");
            }
            foreach ($columns as $column) {
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $column['nama']);
                $subColumnIndex = $columnIndex - 1;
                foreach ($subColumns as $subColumn) {
                    if ($subColumn["parent_id"] == $column["id"]) {
                        $sheet->setCellValueByColumnAndRow($subColumnIndex++, $rowIndex + 1, $subColumn['nama']);
                    }
                }
                $sheet->mergeCells($alphabet[$columnIndex - 2] . "1:" . $alphabet[$subColumnIndex - 2] . "1");
                $columnIndex = $subColumnIndex;
            }
            $rowIndex = 3;
            $sheet->getStyle("A1:" . $alphabet[$columnIndex - 2] . "2")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
        } else {
            foreach ($subColumns as $subColumn) {
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $subColumn['nama']);
            }
            $rowIndex = 2;
            $sheet->getStyle("A1:" . $alphabet[$columnIndex - 2] . "1");
        }

        foreach ($scores as $score) {
            $columnIndex = 1;
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score['rank']);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score['nama']);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score['provinsi_name']);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score['total_verification_score']);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score['total_mcp_score']);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score['penalty_score']);
            $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score['total_ddd']);
            foreach ($subColumns as $subColumn) {
                $nilaiKey = 'nilai_' . $subColumn['id'];
                $sheet->setCellValueByColumnAndRow($columnIndex++, $rowIndex, $score[$nilaiKey]);
            }
            $rowIndex++;
        }

        $todayLabel = DateHelper::formatTodayIndex();

        $writer = new Xlsx($spreadsheet);
        $filename = $todayLabel . "-RekapitulasiMCP" .  ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer;
    }

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

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

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

        $id_area_intervensi = $params['id_area_intervensi'];
        $id_indikator = $params['id_indikator'];
        $id_subindikator = $params['id_subindikator'];
        $instansi_type = $params['instansi_type'];

        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");
            }
            $hierarchyId = $id_subindikator ?? $id_indikator ?? $id_area_intervensi;
            $data = $this->mcsp_2025_dashboard_scores($templateId, $hierarchyId);
            return new CollectionPaginatedResponse($data);
        }

        $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'];
        $bobot_query = '';
        $add_condition = '';

        if ((!empty($params['id_subindikator']) || !empty($params['id_indikator']) || !empty($params['id_area_intervensi'])) && $params['id_area_intervensi'] == 8) {
            $add_condition = 'AND ins.flag_dana_desa = 1';
        }

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

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

        $penaltyScoreData = KorsupgahMcpPenaltyScore::find([
            'conditions' => 'id_template = :id_template: and deleted_at is null',
            'bind'      => [
                'id_template' => $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_area_intervensi' => $id_area_intervensi,
            'id_indikator' => $id_indikator,
            'id_subindikator' => $id_subindikator,
            'instansi_type' => $instansi_type
        ];

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

        $query = '
        SELECT
        	ins.id,
            ins.nama,
            ins.flag_dana_desa,
            max(greatest(answer.qa_verified_at, answer.verified_at)) datetime,
            case when riw.id_provinsi is null then ins.id_provinsi else riw.id_provinsi end as id_provinsi,
            case when riw.id_kota_kabupaten is null then ins.id_kota_kabupaten else riw.id_kota_kabupaten end as id_kota_kabupaten,
            ' . $bobot_query . ' as nilai_total
        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.updated_at, a.verified_at, a.qa_verified_at
        		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
            join jaga.korsupgah_area_intervensi aidd on aidd.id = 8
        WHERE ' . $qIdTemplate . ' AND
            s.deleted_at IS NULL 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
            (ins.id_tipe = :instansi_type OR 0 > :instansi_type)
            ' . $add_condition . '
        GROUP BY ins."id", riw.id
        ORDER BY nilai_total DESC
        ';
        $result = $this->rawQuery($query, $bind);

        foreach ($result as &$x) {
            $penaltyTotal = 0;
            foreach ($penaltyScoreData as $y) {
                $x['id'] === $y->id_instansi ? ($penaltyTotal += $y->score) : 0;
            }
            $x['nilai_total'] -= $penaltyTotal;
        }

        uasort($result, function ($x1, $x2) {
            return $x1['nilai_total'] < $x2['nilai_total'];
        });

        $result = array_values($result);

        for ($i = 0; $i < count($result); $i++) {
            $result[$i]['rank'] = $i + 1;
        }

        return new CollectionPaginatedResponse($result);
    }

    public function mcpNationalOverviewScores()
    {

        $t = KorsupgahTemplate::find([
            "conditions" => "keterangan NOT ILIKE :value: AND deleted_at is null",
            "bind"       => [
                "value" => "%desa%"
            ]
        ]);

        $data = [];

        if (!empty($t)) {
            foreach ($t as $dt) {
                $data[] = [
                    'templateId' =>  $dt->id,
                    'tahun'      =>  $dt->tahun != null ? $dt->tahun : 0,
                    'id_dana_desa' =>   $dt->id_dana_desa
                ];
            }
        } else {
            throw new Exception("Error Processing Request", 1);
        }

        $mainData = [
            'name' => 'National',
            'progress' => []
        ];

        foreach ($data as $dt) {

            $tahun = $dt['tahun'];
            $id_desa = $dt['id_dana_desa'];

            $bobot_query = '';
            $add_condition = '';

            $tahun = $dt['tahun'];

            $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)';

            $bind = [
                'templateId' => $dt['templateId'],
                'instansi_type' => -1
            ];

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

            $query = '
           SELECT
           	ins.id,
               ' . $bobot_query . ' as nilai_total
           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, a.updated_at, a.verified_at
           		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
               join jaga.korsupgah_area_intervensi aidd on aidd.id = 8
           WHERE ' . $qIdTemplate . ' AND
               s.deleted_at IS NULL  AND
          (ins.id_tipe = :instansi_type OR 0 > :instansi_type)
           GROUP BY ins."id", riw.id
           ORDER BY nilai_total DESC
                   ';

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

            for ($i = 0; $i < count($result); $i++) {
                $result[$i]['rank'] = $i + 1;
            }

            $average = $this->sumPercentage($result);

            $mainData['progress'][] = [

                "year" => $tahun,
                "average" => $average,
            ];
        } //end of looping

        return $mainData;
    }

    public function areaInteversionNationalOverviewScore()
    {

        $area_intervensi = $this->request->getQuery('area_intervensi', 'int', null);
        $area_name = $this->request->getQuery('area_name', 'string', null);

        $t = KorsupgahTemplate::find([
            "conditions" => "keterangan NOT ILIKE :value:",
            "bind"       => [
                "value" => "%desa%"
            ]
        ]);

        $data = [];

        if (!empty($t)) {
            foreach ($t as $dt) {
                $data[] = [
                    'templateId' =>  $dt->id,
                    'tahun'      =>  $dt->tahun != null ? $dt->tahun : 0
                ];
            }
        } else {
            throw new Exception("Error Processing Request", 1);
        }
        $mainData = [
            'name' => 'National',
            'progress' => []
        ];

        $t1  = [];
        foreach ($data as $dt) {

            $tahun = $dt['tahun'];

            $bobot_query = '';
            $add_condition = '';

            if (!empty($area_name)) {
                $areaIntervensi = (new Builder())
                    ->from(['t' => 'KorsupgahTemplate'])
                    ->columns([
                        't.id',
                        't.nama',
                        't.is_dana_desa',
                        'ai.id as area_intervensi_id',
                        'ai.nama as area_intervensi_nama',
                        't.tahun'
                    ])
                    ->innerJoin('KorsupgahTemplateSubIndikator', 'tsi.id_template = t.id', 'tsi')
                    ->innerJoin('KorsupgahSubIndikator', 'tsi.id_sub_indikator = si.id', 'si')
                    ->innerJoin('KorsupgahIndikator', 'si.id_indikator = i.id', 'i')
                    ->innerJoin('KorsupgahAreaIntervensi', 'i.id_area_intervensi = ai.id', 'ai')
                    ->where('t.deleted_at IS NULL AND t.tahun = :year: and t.nama not ilike :subdistrict:  and ai.nama ilike :area:', [
                        'year'  => $tahun,
                        'area'   => "%" . $area_name . "%",
                        'subdistrict' => "%desa%"
                    ])
                    ->groupBy([
                        't.id',
                        't.nama',
                        'ai.id',
                        'ai.nama'
                    ])
                    ->orderBy('t.view_order, ai.id')
                    ->limit(1)
                    ->getQuery()
                    ->execute();
            } else {
                $areaIntervensi = (new Builder())
                    ->from(['t' => 'KorsupgahTemplate'])
                    ->columns([
                        't.id',
                        't.nama',
                        't.is_dana_desa',
                        'ai.id as area_intervensi_id',
                        'ai.nama as area_intervensi_nama',
                        't.tahun'
                    ])
                    ->innerJoin('KorsupgahTemplateSubIndikator', 'tsi.id_template = t.id', 'tsi')
                    ->innerJoin('KorsupgahSubIndikator', 'tsi.id_sub_indikator = si.id', 'si')
                    ->innerJoin('KorsupgahIndikator', 'si.id_indikator = i.id', 'i')
                    ->innerJoin('KorsupgahAreaIntervensi', 'i.id_area_intervensi = ai.id', 'ai')
                    ->where('t.deleted_at IS NULL AND t.tahun = :year: AND t.nama NOT ILIKE :subdistrict: ', [
                        'year'  => $tahun,
                        'subdistrict' => '%desa%'
                    ])
                    ->groupBy([
                        't.id',
                        't.nama',
                        'ai.id',
                        'ai.nama'
                    ])
                    ->orderBy('t.view_order, ai.id')
                    ->getQuery()
                    ->execute();
            }

            $mainData['progress'][] = [

                "year" => $tahun,
                "area_intervensi" => []
            ];

            foreach ($areaIntervensi as $ar) {

                $id_area_intervensi = $ar->area_intervensi_id;


                $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000)';
                $bind = [
                    'templateId' => $dt['templateId'],
                    'id_area_intervensi' => $id_area_intervensi,
                ];

                $qIdTemplate = '(s.id_template = :templateId)';

                $whereClause = '
            WHERE ' . $qIdTemplate . ' 
            AND s.deleted_at IS NULL 
            AND (ai.id = :id_area_intervensi OR 0 > :id_area_intervensi)
            AND ins.id_tipe in (1,2)
            ';

                if ($area_intervensi != null) {
                    $whereClause .= ' AND ' . $whereAreaFokus;
                }
                $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000)';
                $query = '
            SELECT
            	ins.id,
                ' . $bobot_query . ' as nilai_total
            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, a.updated_at, a.verified_at
            		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
                join jaga.korsupgah_area_intervensi aidd on aidd.id = 8
                ' . $whereClause . '
            GROUP BY ins."id", riw.id
            ORDER BY nilai_total DESC
            ';

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

                for ($i = 0; $i < count($result); $i++) {
                    $result[$i]['rank'] = $i + 1;
                }

                $average = $this->sumPercentage($result);

                foreach ($mainData['progress'] as &$progress) {
                    if ($progress['year'] == $tahun) {
                        $progress['area_intervensi'][] = [
                            "area_name" => $ar->area_intervensi_nama,
                            "value" => $average
                        ];
                    }
                }
            }
            // end of looping area intervi list  
        }
        //end of looping mainData

        return $mainData;
    }

    public function mcpProvinceOverviewScores($id_province)
    {

        $t = KorsupgahTemplate::find([
            "conditions" => "keterangan NOT ILIKE :value: and deleted_at is null",
            "bind"       => [
                "value" => "%desa%"
            ]
        ]);

        $id_kota_kabupaten =  $this->request->getQuery('id_kota_kabupaten', 'int', null);
        $data = [];

        if (!empty($t)) {
            foreach ($t as $dt) {
                $data[] = [
                    'templateId' =>  $dt->id,
                    'tahun'      =>  $dt->tahun != null ? $dt->tahun : 0,
                    'id_dana_desa'  => $dt->id_dana_desa
                ];
            }
        } else {
            throw new Exception("Error Processing Request", 1);
        }

        if (!empty($id_province) && !empty($id_kota_kabupaten)) {
            $provinceList = Instansi::find([
                "conditions"    => " id_provinsi = :value1: and id_kota_kabupaten = :value2: AND id_tipe in(1, 2)",
                "bind"       => [
                    "value1" => $id_province,
                    "value2" => $id_kota_kabupaten
                ]
            ]);
        } else if (!empty($id_province) && empty($id_kota_kabupaten)) {
            $provinceList = Instansi::find([
                "conditions"    => " id_provinsi = :value1: and id_kota_kabupaten is null AND id_tipe in(1, 2)",
                "bind"       => [
                    "value1" => $id_province,
                ]
            ]);
        }
        $mainData = [];
        foreach ($provinceList as $dt) {
            $instansiId = $dt->id;

            $instansiData = [
                'nama' => $dt->nama,
                'progress' => []
            ];

            foreach ($data as $dt) {

                $id_desa = $dt['id_dana_desa'];
                $tahun = $dt['tahun'];
                $bobot_query = '';
                $add_condition = '';

                $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_pic, 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 (!empty($id_province) && !empty($id_kota_kabupaten)) {
                    $whereInstansi = "ins.id_provinsi = '$id_province' AND ins.id_kota_kabupaten = '$id_kota_kabupaten' AND ins.id_tipe in (1, 2)";
                } else if (!empty($id_province) && empty($id_kota_kabupaten)) {
                    $whereInstansi = "ins.id_provinsi = '$id_province' AND ins.id_kota_kabupaten is null AND ins.id_tipe in (1,2) ";
                }

                $bind = [
                    'templateId' => $dt['templateId'],
                ];


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

                $query = '
            SELECT
            	ins.id,
                ins.nama,
                ins.flag_dana_desa,
                max(case when answer.updated_at > answer.verified_at then answer.updated_at else answer.verified_at end) datetime,
                case when riw.id_provinsi is null then ins.id_provinsi else riw.id_provinsi end as id_provinsi,
                case when riw.id_kota_kabupaten is null then ins.id_kota_kabupaten else riw.id_kota_kabupaten end as id_kota_kabupaten,
                ' . $bobot_query . ' as nilai_total
            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, a.updated_at, a.verified_at
            		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
                join jaga.korsupgah_area_intervensi aidd on aidd.id = 8
            WHERE ' . $qIdTemplate . ' AND
                s.deleted_at IS NULL AND
                ins.id = ' . $instansiId . ' AND
                ' . $whereInstansi . '
            GROUP BY ins."id", riw.id
            ORDER BY nilai_total DESC
                    ';

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

                for ($i = 0; $i < count($result); $i++) {
                    $result[$i]['rank'] = $i + 1;
                }

                $average = $this->sumPercentage($result);

                $instansiData['progress'][] = [
                    "year" => $tahun,
                    "average" => $average,
                ];
            } //end of looping $mainData
            $mainData[] = $instansiData;
        } // end of looping instansi

        return $mainData;
    }

    public function AreaIntervensiProvinceOverviewScores($id_province)
    {

        $id_kota_kabupaten = $this->request->getQuery('id_kota_kabupaten', 'int', null);
        $area_intervensi = $this->request->getQuery('area_intervensi', 'int', null);
        $area_name = $this->request->getQuery('area_name', 'string', null);


        $t = KorsupgahTemplate::find([
            "conditions" => "keterangan NOT ILIKE :value:",
            "bind"       => ["value" => "%desa%"]
        ]);

        if (empty($t)) {
            throw new Exception("Error Processing Request", 1);
        }

        $data = [];
        foreach ($t as $dt) {
            $data[] = [
                'templateId' => $dt->id,
                'tahun'      => $dt->tahun != null ? $dt->tahun : 0
            ];
        }


        if (!empty($id_province) && !empty($id_kota_kabupaten)) {
            $provinceList = Instansi::find([
                "conditions" => "id_provinsi = :value1: AND id_kota_kabupaten = :value2: AND id_tipe in (1,2)",
                "bind"       => [
                    "value1" => $id_province,
                    "value2" => $id_kota_kabupaten
                ]
            ]);
        } else if (!empty($id_province) && empty($id_kota_kabupaten)) {
            $provinceList = Instansi::find([
                "conditions" => "id_provinsi = :value1: AND id_kota_kabupaten IS NULL AND id_tipe in(1,2)",
                "bind"       => [
                    "value1" => $id_province,
                ]
            ]);
        }


        $mainData = [];

        foreach ($provinceList as $pl) {
            $instansiId = $pl->id;

            $instansiData = [
                'nama' => $pl->nama,
                'progress' => []
            ];

            if (!empty($id_province) && !empty($id_kota_kabupaten)) {
                $whereInstansi = "ins.id_provinsi ='$id_province' AND ins.id_kota_kabupaten = '$id_kota_kabupaten' AND ins.id_tipe in (1,2)";
            } else if (!empty($id_province) && empty($id_kota_kabupaten)) {
                $whereInstansi = "ins.id_provinsi ='$id_province' AND ins.id_tipe in (1,2) AND ins.id_kota_kabupaten is null";
            }

            foreach ($data as $dt) {

                $tahun = $dt['tahun'];
                if (!empty($area_name)) {

                    $areaIntervensi = (new Builder())
                        ->from(['t' => 'KorsupgahTemplate'])
                        ->columns([
                            't.id',
                            't.nama',
                            't.is_dana_desa',
                            'ai.id as area_intervensi_id',
                            'ai.nama as area_intervensi_nama',
                            't.tahun'
                        ])
                        ->innerJoin('KorsupgahTemplateSubIndikator', 'tsi.id_template = t.id', 'tsi')
                        ->innerJoin('KorsupgahSubIndikator', 'tsi.id_sub_indikator = si.id', 'si')
                        ->innerJoin('KorsupgahIndikator', 'si.id_indikator = i.id', 'i')
                        ->innerJoin('KorsupgahAreaIntervensi', 'i.id_area_intervensi = ai.id', 'ai')
                        ->where('t.deleted_at IS NULL AND t.tahun = :year: and t.nama not ilike :subdistrict:  and ai.nama ilike :area:', [
                            'year'  => $tahun,
                            'area'   => "%" . $area_name . "%",
                            'subdistrict' => "%desa%"
                        ])
                        ->groupBy([
                            't.id',
                            't.nama',
                            'ai.id',
                            'ai.nama'
                        ])
                        ->orderBy('t.view_order, ai.id')
                        ->limit(1)
                        ->getQuery()
                        ->execute();
                } else {
                    $areaIntervensi = (new Builder())
                        ->from(['t' => 'KorsupgahTemplate'])
                        ->columns([
                            't.id',
                            't.nama',
                            't.is_dana_desa',
                            'ai.id as area_intervensi_id',
                            'ai.nama as area_intervensi_nama',
                            't.tahun'
                        ])
                        ->innerJoin('KorsupgahTemplateSubIndikator', 'tsi.id_template = t.id', 'tsi')
                        ->innerJoin('KorsupgahSubIndikator', 'tsi.id_sub_indikator = si.id', 'si')
                        ->innerJoin('KorsupgahIndikator', 'si.id_indikator = i.id', 'i')
                        ->innerJoin('KorsupgahAreaIntervensi', 'i.id_area_intervensi = ai.id', 'ai')
                        ->where('t.deleted_at IS NULL AND t.tahun = :year: AND t.nama NOT ILIKE :subdistrict: ', [
                            'year'  => $tahun,
                            'subdistrict' => '%desa%'
                        ])
                        ->groupBy([
                            't.id',
                            't.nama',
                            'ai.id',
                            'ai.nama'
                        ])
                        ->orderBy('t.view_order, ai.id')
                        ->getQuery()
                        ->execute();
                }


                $instansiData['progress'][] = [
                    "year" => $tahun,
                    "area_intervensi" => []
                ];


                foreach ($areaIntervensi as $ar) {
                    $id_area_intervensi = $ar->area_intervensi_id;

                    $bind = [
                        'templateId' => $dt['templateId'],
                        'id_area_intervensi' => $id_area_intervensi,
                    ];

                    $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000)';

                    $qIdTemplate = '(s.id_template = :templateId)';
                    $whereClause = '
                    WHERE ' . $qIdTemplate . ' 
                    AND s.deleted_at IS NULL 
                    AND (ai.id = :id_area_intervensi OR 0 > :id_area_intervensi)  AND
                    ' . $whereInstansi . '
                    ';

                    if ($area_intervensi != null) {
                        $whereClause .= ' AND ' . $whereAreaFokus;
                    }

                    $query = '
                    SELECT
                        ins.id,
                        ' . $bobot_query . ' as nilai_total
                    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, a.updated_at, a.verified_at
                            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
                        join jaga.korsupgah_area_intervensi aidd on aidd.id = 8
                        ' . $whereClause . '
                    GROUP BY ins."id", riw.id
                    ORDER BY nilai_total DESC
                    ';


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

                    $average = $this->sumPercentage($result);

                    foreach ($instansiData['progress'] as &$progress) {
                        if ($progress['year'] == $tahun) {
                            $progress['area_intervensi'][] = [
                                "area_name" => $ar->area_intervensi_nama,
                                "value" => $average
                            ];
                        }
                    }
                }
            }

            $mainData[] = $instansiData;
        }

        return $mainData;
    }

    public function mcpPemdaOverviewScores()
    {

        $t = KorsupgahTemplate::find([
            "conditions" => "keterangan NOT ILIKE :value:",
            "bind"       => [
                "value" => "%desa%"
            ]
        ]);

        $data = [];

        if (!empty($t)) {
            foreach ($t as $dt) {
                $data[] = [
                    'templateId' =>  $dt->id,
                    'tahun'      =>  $dt->tahun != null ? $dt->tahun : 0
                ];
            }
        } else {
            throw new Exception("Error Processing Request", 1);
        }

        $provinceList = Instansi::find([
            "conditions"    => " id_provinsi is not null and id_kota_kabupaten is not null"
        ]);

        $mainData = [];

        foreach ($provinceList as $dt) {


            $instansiId = $dt->id;

            $instansiData = [
                'nama' => $dt->nama,
                'progress' => []
            ];


            foreach ($data as $dt) {
                $tahun = $dt['tahun'];


                $bobot_query = '';
                $add_condition = '';


                $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_pic, 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)';


                $bind = [
                    'templateId' => $dt['templateId'],
                ];

                $qIdTemplate = '(s.id_template = :templateId)';

                $query = '
            SELECT
            	ins.id,
                ' . $bobot_query . ' as nilai_total
            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, a.updated_at, a.verified_at
            		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
                join jaga.korsupgah_area_intervensi aidd on aidd.id = 8
            WHERE ' . $qIdTemplate . ' AND
                s.deleted_at IS NULL AND
                ins.id = ' . $instansiId . '
            GROUP BY ins."id", riw.id
            ORDER BY nilai_total DESC
                    ';

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

                for ($i = 0; $i < count($result); $i++) {
                    $result[$i]['rank'] = $i + 1;
                }
                // Extract 'nilai_total' values from the data array
                $nilaiTotalArray = array_column($result, 'nilai_total');


                if (!empty($nilaiTotalArray)) {
                    $mean = array_sum($nilaiTotalArray) / count($nilaiTotalArray);
                } else {
                    $mean = 0;
                }


                $roundedMean = (int) round($mean, 0);

                $instansiData['progress'][] = [
                    "year" => $tahun,
                    "average" => $roundedMean,
                ];
            } //end of looping $mainData
            $mainData[] = $instansiData;
        } // end of looping instansi
        return new CollectionPaginatedResponse($mainData);
    }

    public function AreaIntervensiPemdaOverviewScores()
    {
        $t = KorsupgahTemplate::find([
            "conditions" => "keterangan NOT ILIKE :value:",
            "bind"       => ["value" => "%desa%"]
        ]);

        if (empty($t)) {
            throw new Exception("Error Processing Request", 1);
        }
        // Prepare template data
        $data = [];
        foreach ($t as $dt) {
            $data[] = [
                'templateId' => $dt->id,
                'tahun'      => $dt->tahun != null ? $dt->tahun : 0
            ];
        }
        // Fetch provinces (instansi)
        $provinceList = Instansi::find([
            "conditions" => " id_provinsi is not null and id_kota_kabupaten is not null"
        ]);

        $mainData = [];
        foreach ($provinceList as $pl) {
            // Prepare data for each instansi
            $instansiId = $pl->id;
            $instansiData = [
                'nama' => $pl->nama,
                'progress' => []
            ];

            foreach ($data as $dt) {
                $tahun = $dt['tahun'];

                // Query for area_intervensi based on the current year
                $areaIntervensi = KorsupgahAreaIntervensi::find([
                    "conditions" => "keterangan ILIKE :value:",
                    "bind"       => ["value" => "%MCP-" . $tahun . "%"]
                ]);

                $instansiData['progress'][] = [

                    "year" => $tahun,
                    "area_intervensi" => []
                ];

                foreach ($areaIntervensi as $ar) {

                    $id_area_intervensi = $ar->id;

                    $bind = [
                        'templateId' => $dt['templateId'],
                        'id_area_intervensi' => $id_area_intervensi,
                    ];

                    $bobot_query = 'sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000)';
                    $qIdTemplate = '(s.id_template = :templateId)';


                    $query = '
                    SELECT
                        ins.id,
                        ' . $bobot_query . ' as nilai_total
                    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, a.updated_at, a.verified_at
                            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
                        join jaga.korsupgah_area_intervensi aidd on aidd.id = 8
                    WHERE ' . $qIdTemplate . ' AND
                        s.deleted_at IS NULL AND
                        ins.id = ' . $instansiId . ' AND
                        (ai.id = :id_area_intervensi OR 0 > :id_area_intervensi)
                    GROUP BY ins."id", riw.id
                    ORDER BY nilai_total DESC
                            ';
                    // dd($query);


                    // Execute query
                    $result = $this->rawQuery($query, $bind);
                    $nilaiTotalArray = array_column($result, 'nilai_total');

                    // Calculate mean score
                    $mean = !empty($nilaiTotalArray) ? array_sum($nilaiTotalArray) / count($nilaiTotalArray) : 0;
                    $roundedMean = (int) round($mean, 0);


                    foreach ($instansiData['progress'] as &$progress) {
                        if ($progress['year'] == $tahun) {
                            $progress['area_intervensi'][] = [
                                "area_name" => $ar->nama,
                                "value" => $roundedMean
                            ];
                        }
                    }
                }
            }

            $mainData[] = $instansiData;
        }

        return new CollectionPaginatedResponse($mainData);
    }

    function sumPercentage($data)
    {
        $total = 0;
        $count = 0;
        foreach ($data as $row) {
            if (isset($row['nilai_total'])) {
                $total += $row['nilai_total'];
                $count++;
            }
        }
        $mean = $count > 0 ? $total / $count : 0;

        return  round($mean);
    }

    function insertMcpPenaltyScore()
    {
        $this->checkScopes(['korsupgah.admin']);
        $params = $this->request->getJsonRawBody(true);

        $validatedRequest = $this->validate(
            $params,
            [
                'score'             => ['validators' => ['required', 'digit']],
                'id_instansi'       => ['validators' => ['required', 'digit']],
                'id_template'       => ['validators' => ['digit']],
                'description'       => ['validators' => []],
            ]
        );

        $templateId = $validatedRequest['id_template'];
        $instansiData = Instansi::findFirst($validatedRequest['id_instansi']);

        $templateData = KorsupgahTemplate::findFirst($templateId);
        $year = $templateData->tahun;

        $isDataExist = KorsupgahMcpPenaltyScore::find([
            "conditions" => "id_instansi = :id_instansi: AND id_template = :id_template: AND deleted_at is null",
            "bind"       => [
                "id_instansi" => $validatedRequest['id_instansi'],
                "id_template" => $validatedRequest['id_template']
            ]
        ]);

        if (count($isDataExist) > 0) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Sorry, we are unable to perform this request due to the data is already exist on selected year and local government");
        }

        $newData = new KorsupgahMcpPenaltyScore();

        $newData->id_template     = $templateId;
        $newData->id_instansi     = $validatedRequest['id_instansi'];
        $newData->score           = $validatedRequest['score'];
        $newData->description     = $validatedRequest['description'];
        $newData->year            = $year;
        $newData->created_at      =  'now();';
        $newData->created_by      = $this->shared->user->um_id;
        $newData->name            = $instansiData->nama;

        $res = $newData->create();

        if ($res) {
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        $newData
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Error during saving the data! Please contact the IT with the error message!");
        }
    }

    function insertMultipleMcpPenaltyScore()
    {
        $this->checkScopes(['korsupgah.admin']);
        $params = $this->request->getJsonRawBody(true);

        $validatedRequest = $this->validate(
            $params,
            [
                'id_template' => ['validators' => ['digit']],
                'form_data'   => ['validators' => ['required']],
            ]
        );


        $templateId = $validatedRequest['id_template'];
        $data = $validatedRequest['form_data'];
        $c = 0;

        foreach ($data as $x) {

            $instansiData = Instansi::findFirst($x['id_instansi']);
            $templateData = KorsupgahTemplate::findFirst($templateId);

            $year = $templateData->tahun;

            $newData = new KorsupgahMcpPenaltyScore();

            $newData->id_template     = $templateId;
            $newData->id_instansi     = $x['id_instansi'];
            $newData->score           = $x['score'];
            $newData->description     = $x['desc'];
            $newData->name            = $instansiData->nama;
            $newData->year            = $year;
            $newData->created_by      = $this->shared->user->um_id;

            $newData->create()
                ? $c++
                : (function () use ($newData) {
                    $messages = $newData->getMessages();
                    foreach ($messages as $message) {
                        error_log($message->getMessage());
                    }
                })();
        }

        if ($c == count($data)) {
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "penalty score has been inserted successfully!The total of data is being inserted : " => $c
                    ],
                ]
            );
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Error during saving the data! Please contact the IT with the error message!");
        }
    }


    function uploadPenaltyScoreAttachments()
    {
        $this->checkScopes(['korsupgah.admin']);

        $params = $this->request->getJsonRawBody(true);

        $validatedRequest = $this->validate(
            $params,
            [
                'attachments'       => ['validators' => []],
                "id_template"       => ['validators' => ['required', 'digit']]
            ]
        );

        if (!empty($params['attachments'])) {
            foreach ($params['attachments'] as $key => $uuid) {
                $attachment_record = Attachments::findFirstByUuid($uuid);
                if (!empty($attachment_record) && empty($attachment_record->deleted_at)) {
                    $existing_mcp_penalty_score_attachments = KorsupgahMcpPenaltyScoreAttachment::find([
                        'conditions' => ' id_attachment=:id_attachment: AND deleted_at is null',
                        'bind'       => [
                            'id_attachment' => $attachment_record->id
                        ]
                    ]);

                    if (count($existing_mcp_penalty_score_attachments) < 1) {

                        $score_penalty_attachment = new KorsupgahMcpPenaltyScoreAttachment();
                        $score_penalty_attachment->id_attachment = $attachment_record->id;
                        $score_penalty_attachment->id_template = $validatedRequest['id_template'];
                        $score_penalty_attachment->created_by = $this->shared->user->um_id;
                        $score_penalty_attachment->save();


                        $attachment_record->status = Attachments::STATUS_REFERRED;
                        $attachment_record->referer = Attachments::REFERER_KORSUPGAH_ANSWER;
                        $res = $attachment_record->save();

                        if ($res) {
                            return new ObjectResponse(
                                [
                                    "success" => true,
                                    "data" => [$attachment_record]
                                ]
                            );
                        } else {
                            throw new CustomErrorExecption(BaseResponse::INVALID_REQUEST, "Error during saving the data! If you're a developer check the error message, if not just contact the IT team!");
                        }
                    }
                }
            }
        }
    }

    function detailMCpPenaltyScore($id)
    {
        $this->checkScopes(['korsupgah.admin']);
        $id_template = $this->request->getQuery('id_template', 'int', null);

        $penaltyScoreData = KorsupgahMcpPenaltyScore::find([
            'conditions'  => 'id = :id: and deleted_at is null',
            'bind' => [
                'id' => $id
            ]
        ]);

        echo json_encode($penaltyScoreData);
    }

    function mcpPenaltyScoreList()
    {
        $this->checkScopes(['korsupgah.admin']);
        $data = KorsupgahMcpPenaltyScore::find([
            "conditions" => "deleted_at is null",
            "order" => "id"
        ]);

        return new SimpleResponse($data);
    }

    public function deleteMcpPenaltyScore($id)
    {
        $this->checkScopes(['korsupgah.admin']);
        $penaltyScoreData = KorsupgahMcpPenaltyScore::findFirst([
            'conditions' => 'id = :id: and deleted_at is null',
            'bind' => [
                'id' => $id
            ]
        ]);

        $date = date('Y-m-d H:i:s');
        $penaltyScoreData->updated_at = $date;
        $penaltyScoreData->deleted_at = $date;
        $penaltyScoreData->updated_by = $this->shared->user->um_id;
        $status = $penaltyScoreData->save();

        if (!$status) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Error during deleting the data! Please contact the IT with the error message!");
        } else {
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [
                        "penalty score has been removed successfully! " => $penaltyScoreData->id
                    ],
                ]
            );
        }
    }

    public function editMcpPenaltyScore($id)
    {
        $params = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $params,
            [
                'score'             => ['validators' => ['required', 'digit']],
                'description'        => ['validators' => []],
                'attachments'       => ['validators' => []],
            ]
        );

        $data = KorsupgahMcpPenaltyScore::findFirst(
            [
                "conditions"  => "id = :id: AND deleted_at is null",
                "bind" => [
                    "id" => $id
                ]
            ]
        );

        $data->score = $validatedRequest['score'];
        $data->description = $validatedRequest['description'];
        $data->attachments = $validatedRequest['attachments'];
        $data->updated_by = $this->shared->user->um_id;
        $data->updated_at = 'now();';

        $res = $data->save();

        if ($res) {
            $data = KorsupgahMcpPenaltyScore::findFirst($data->id);
            return new ObjectResponse(
                [
                    "success" => true,
                    "data" => [$data]
                ]
            );
        } else {

            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Error during deleting the data! Please contact the IT with the error message!");
        }
    }

    public function getHierarchy()
    {
        $this->checkScopes(['korsupgah.admin']);

        $params = $this->request->get();
        $validatedParams = $this->validate($params, [
            'id' => ['validators' => ['required', 'digit']],
            'year' => ['validators' => ['required', 'digit']]
        ]);

        try {
            $mcpService = new McpService();
            $result = $mcpService->get('/api/hierarchy', [
                'id' => $validatedParams['id'],
                'year' => $validatedParams['year']
            ]);
            $response = $result->success ? new SimpleResponse($result->data) : new SimpleResponse(null);
            $response->setStatus($result->success);
            $response->setMessage($result->message);
            return $response;
        } catch (Exception $e) {
            throw $e;
        }
    }

    public function postHierarchy()
    {
        $this->checkScopes(['korsupgah.admin']);

        $params = $this->request->getJsonRawBody(true);

        $validatedRequest = $this->validate(
            $params,
            [
                'year' => ['validators' => ['required', 'digit']],
                'hierarchy' => ['validators' => ['required']],
                'copied_question' => ['validators' => []]
            ]
        );

        $additionalData = [
            'created_by' => $this->shared->user->um_id,
            'created_at' => date('Y-m-d H:i:s'),
            'updated_by' => $this->shared->user->um_id,
            'updated_at' => date('Y-m-d H:i:s'),
            'deleted_at' => null,
            'deleted_by' => null,
            'children' => []
        ];
        $validatedRequest['hierarchy'] = array_merge($validatedRequest['hierarchy'], $additionalData);

        $postParams = [
            'year' => $validatedRequest['year'],
            'hierarchy' => $validatedRequest['hierarchy']
        ];

        if (!empty($validatedRequest['copied_question'])) {
            $postParams['copied_question'] = $validatedRequest['copied_question'];
        }

        try {
            $mcpService = new McpService();
            $result = $mcpService->post('/api/hierarchy', $postParams);

            $response = $result->success ? new SimpleResponse($result->data) : new SimpleResponse(null);
            $response->setStatus($result->success);
            $response->setMessage($result->message);
            return $response;
        } catch (Exception $e) {
            throw $e;
        }
    }

    public function editHierarchy($id)
    {
        $this->checkScopes(['korsupgah.admin']);

        $params = $this->request->getJsonRawBody(true);

        $validatedRequest = $this->validate(
            $params,
            [
                'year' => ['validators' => ['required', 'digit']],
                'hierarchy' => ['validators' => ['required']],
                'copied_question' => ['validators' => []]
            ]
        );

        $validatedRequest['updated_by'] = [
            'id' => $this->shared->user->um_id,
            'name' => $this->shared->user->nama
        ];

        try {
            $mcpService = new McpService();
            $result = $mcpService->post('/api/hierarchy/update/' . $id, $validatedRequest);

            $response = $result->success ? new SimpleResponse($result->data) : new SimpleResponse(null);
            $response->setStatus($result->success);
            $response->setMessage($result->message);
            return $response;
        } catch (Exception $e) {
            throw $e;
        }
    }

    public function deleteHierarchy($id)
    {
        $this->checkScopes(['korsupgah.admin']);

        $params = $this->request->getJsonRawBody(true);

        $validatedRequest = $this->validate(
            $params,
            [
                'year' => ['validators' => ['required', 'digit']]
            ]
        );

        $validatedRequest['deleted_by'] = [
            'id' => $this->shared->user->um_id,
            'name' => $this->shared->user->nama
        ];

        try {
            $mcpService = new McpService();
            $result = $mcpService->post('/api/hierarchy/delete/' . $id, $validatedRequest);

            $response = $result->success ? new SimpleResponse($result->data) : new SimpleResponse(null);
            $response->setStatus($result->success);
            $response->setMessage($result->message);
            return $response;
        } catch (Exception $e) {
            throw $e;
        }
    }

    public function rearrangeHierarchyChildren($id)
    {
        $this->checkScopes(['korsupgah.admin']);

        $params = $this->request->getJsonRawBody(true);

        $validatedRequest = $this->validate(
            $params,
            [
                'year' => ['validators' => ['required', 'digit']],
                'children' => ['validators' => ['required']]
            ]
        );

        $validatedRequest['updated_by'] = [
            'id' => $this->shared->user->um_id,
            'name' => $this->shared->user->nama
        ];

        try {
            $mcpService = new McpService();
            $result = $mcpService->post('/api/hierarchy/rearrange/' . $id, $validatedRequest);

            $response = $result->success ? new SimpleResponse($result->data) : new SimpleResponse(null);
            $response->setStatus($result->success);
            $response->setMessage($result->message);
            return $response;
        } catch (Exception $e) {
            throw $e;
        }
    }

    private function mcsp_2025_dashboard_scores($templateId, $hierarchyId = null)
    {
        $mcpService = new McpService();
        $url = "/api/dashboard/scores/$templateId".($hierarchyId === null ? '' : "/$hierarchyId");
        $result = $mcpService->get($url);
        if ($result->success) {
            return $result->data;
        }
        return [];
    }

    public function mcpInstansiList($year)
    {
        $mcpService = new McpService();
        $result = $mcpService->get("/api/hierarchy/instansi/$year");
        return new SimpleResponse(
            $result->success ? ['result' => $result->data] : []
        );
    }
}