<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use DusanKasan\Knapsack\Collection;

class KorsupgahSurveyController extends BaseController
{
    /** @var KorsupgahTemplate $template */
    private $template;

    public function listForInstansi()
    {
        $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'] : '';


        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : '';
        $getParams['id_periode'] = array_key_exists('id_periode', $getParams) ? $getParams['id_periode'] : '';
        $getParams['order_by'] = array_key_exists('order_by', $getParams) ? strtolower($getParams['order_by']) : 'view_order';
        $getParams['order_direction'] = array_key_exists('order_direction', $getParams) ? strtolower($getParams['order_direction']) : 'asc';

        $allowed_order = ['id', 'id_periode', 'nama_instansi', 'view_order'];
        $allowed_order_direction = ['asc', 'desc'];
        $order_by_mapping = [
            'id' => 'KorsupgahSurvey.id',
            'id_periode' => 'KorsupgahSurvey.id_periode',
            'nama_instansi' => 'Instansi.nama',
            'view_order' => 'KorsupgahTemplate.view_order'
        ];


        $params = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'id_instansi' => ['validators' => ['digit']],
                'id_periode' => ['validators' => ['digit']],
                'order_by' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => $allowed_order
                        ]
                    ]
                ],
                'order_direction' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => $allowed_order_direction
                        ]
                    ]
                ]
            ]
        );

        $order_by_column = $order_by_mapping[$params['order_by']];
        $order_direction = $params['order_direction'];

        $builder = $this->modelsManager->createBuilder()->from('KorsupgahSurvey')
            ->join('Instansi', 'KorsupgahSurvey.id_instansi = Instansi.id')
            ->join('KorsupgahTemplate', 'KorsupgahSurvey.id_template = KorsupgahTemplate.id')
            ->limit($params['limit'])
            ->offset($params['offset'])
            ->orderBy(["$order_by_column $order_direction NULLS LAST"]);

        $countBuilder = $this->modelsManager->createBuilder()->from('KorsupgahSurvey')
            ->columns(['total_record' => 'COUNT(*)'])
            ->join('Instansi', 'KorsupgahSurvey.id_instansi = Instansi.id')
            ->limit(1);

        if (!empty($params['id_instansi'])):
            $builder->where('id_instansi = :id_instansi:', ['id_instansi' => $params['id_instansi']]);
            $countBuilder->where('id_instansi = :id_instansi:', ['id_instansi' => $params['id_instansi']]);
        endif;

        if (!empty($params['id_periode'])):
            $builder->where('id_periode = :id_periode:', ['id_periode' => $params['id_periode']]);
            $countBuilder->where('id_periode = :id_periode:', ['id_periode' => $params['id_periode']]);
        endif;

        if (!empty($params['keyword'])):
            $builder->where('Instansi.nama ilike :keyword:', ['keyword' => '%' . $params['keyword'] . '%'])
                ->orWhere('KorsupgahSurvey.keterangan ilike :keyword:', ['keyword' => '%' . $params['keyword'] . '%']);
            $countBuilder->where('Instansi.nama ilike :keyword:', ['keyword' => '%' . $params['keyword'] . '%'])
                ->orWhere('KorsupgahSurvey.keterangan ilike :keyword:', ['keyword' => '%' . $params['keyword'] . '%']);
        endif;

        $rows = $builder->getQuery()->execute();
        $total_record = (int) $countBuilder->getQuery()->execute()->getFirst()->total_record;

        $result = [];
        foreach ($rows as $key => $survey):
            $result[] = $survey->getDetail(true);
        endforeach;

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

    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'] : '';


        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : '';
        $getParams['id_periode'] = array_key_exists('id_periode', $getParams) ? $getParams['id_periode'] : '';
        $getParams['order_by'] = array_key_exists('order_by', $getParams) ? strtolower($getParams['order_by']) : 'id';
        $getParams['order_direction'] = array_key_exists('order_direction', $getParams) ? strtolower($getParams['order_direction']) : 'asc';

        $allowed_order = ['id', 'id_periode', 'nama_instansi'];
        $allowed_order_direction = ['asc', 'desc'];
        $order_by_mapping = [
            'id' => 'KorsupgahSurvey.id',
            'id_periode' => 'KorsupgahSurvey.id_periode',
            'nama_instansi' => 'Instansi.nama'
        ];


        $params = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'id_instansi' => ['validators' => ['digit']],
                'id_periode' => ['validators' => ['digit']],
                'order_by' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => $allowed_order
                        ]
                    ]
                ],
                'order_direction' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => $allowed_order_direction
                        ]
                    ]
                ]
            ]
        );

        $order_by_column = $order_by_mapping[$params['order_by']];
        $order_direction = $params['order_direction'];

        $builder = $this->modelsManager->createBuilder()->from('KorsupgahSurvey')
            ->join('Instansi', 'KorsupgahSurvey.id_instansi = Instansi.id')
            ->where('1 = 1')
            ->limit($params['limit'])
            ->offset($params['offset'])
            ->orderBy(["$order_by_column $order_direction NULLS LAST"]);

        $countBuilder = $this->modelsManager->createBuilder()->from('KorsupgahSurvey')
            ->columns(['total_record' => 'COUNT(*)'])
            ->join('Instansi', 'KorsupgahSurvey.id_instansi = Instansi.id')
            ->where('1 = 1')
            ->limit(1);

        if (!empty($params['id_instansi'])):
            $builder->andWhere('id_instansi = :id_instansi:', ['id_instansi' => $params['id_instansi']]);
            $countBuilder->andWhere('id_instansi = :id_instansi:', ['id_instansi' => $params['id_instansi']]);
        endif;

        if (!empty($params['id_periode'])):
            $builder->andWhere('id_periode = :id_periode:', ['id_periode' => $params['id_periode']]);
            $countBuilder->andWhere('id_periode = :id_periode:', ['id_periode' => $params['id_periode']]);
        endif;

        if (!empty($params['keyword'])):
            $builder->andWhere('Instansi.nama ilike :keyword: OR KorsupgahSurvey.keterangan ilike :keyword:', ['keyword' => '%' . $params['keyword'] . '%']);
            $countBuilder->andWhere('Instansi.nama ilike :keyword: OR KorsupgahSurvey.keterangan ilike :keyword:', ['keyword' => '%' . $params['keyword'] . '%']);
        endif;

        $rows = $builder->getQuery()->execute();
        $total_record = (int) $countBuilder->getQuery()->execute()->getFirst()->total_record;

        $result = [];
        foreach ($rows as $key => $survey):
            $result[] = $survey->overview();
        endforeach;

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

    public function all_instansi_overview()
    {
        $this->hasOneOfScopes(['korsupgah.monitoring', 'korwil.monitoring_kemdagri', 'korwil.monitoring_bpkp', 'korwil.observer_kemdagri_bpkp', 'korwil.verifikator_kemdagri_bpkp']);

        $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'] : '';
        $getParams['order_by'] = array_key_exists('order_by', $getParams) ? strtolower($getParams['order_by']) : 'total_question';
        $getParams['order_direction'] = array_key_exists('order_direction', $getParams) ? strtolower($getParams['order_direction']) : 'asc';

        $allowed_order = [
            'nama',
            'total_question',
            'total_answered_question',
            'total_unverified_question',
            'total_aset_progress',
            'total_pad_progress',
            'percent_verification_pad',
            'percent_verification_aset',
            'percent_answered_question'
        ];
        $allowed_order_direction = ['asc', 'desc'];

        $params = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => ['validators' => []],
                'id_template' => ['validators' => ['required', 'digit']],
                'tahun' => ['validators' => ['required', 'digit']],
                'order_by' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => $allowed_order
                        ]
                    ]
                ],
                'order_direction' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => $allowed_order_direction
                        ]
                    ]
                ]
            ]
        );

        $tahun = $params['tahun'];
        $order_direction = $params['order_direction'];
        $order_column = $params['order_by'];
        $orderStatement = "";
        if (!empty($order_column)) {
            $orderStatement = "order by $order_column $order_direction NULLS LAST";
        }

        $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 i.id_provinsi IN $cond else riw.id_provinsi IN $cond end)";
            }
        }

        $q = "
            SELECT q.*,
                 round((q.total_answered_question * 100.0) / NULLIF(q.total_question,0), 2) as percent_answered_question,
                 round((q.total_pad_progress_verified * 100.0) / NULLIF(q.total_pad_progress,0), 2) as percent_verification_pad,
                 round((q.total_aset_progress_verified * 100.0) / NULLIF(q.total_aset_progress,0), 2) as percent_verification_aset
            FROM (
                SELECT
                    i.id,
                    i.nama,
                    lu.max AS last_update,
                    CASE WHEN tq.count IS NULL THEN 0 ELSE tq.count END AS total_question,
                    CASE WHEN taq.count IS NULL THEN 0 ELSE taq.count END AS total_answered_question,
                    CASE WHEN tuq.count IS NULL THEN 0 ELSE tuq.count END AS total_unverified_question,
                    CASE WHEN tpp.count IS NULL THEN 0 ELSE tpp.count END AS total_pad_progress,
                    CASE WHEN tppv.count IS NULL THEN 0 ELSE tppv.count END AS total_pad_progress_verified,
                    CASE WHEN tap.count IS NULL THEN 0 ELSE tap.count END AS total_aset_progress,
                    CASE WHEN tapv.count IS NULL THEN 0 ELSE tapv.count END AS total_aset_progress_verified
                FROM jaga.instansi i
                LEFT JOIN rel_instansi_wilayah riw ON riw.id_instansi = i.id AND $tahun >= riw.start_year AND $tahun <= riw.end_year
                LEFT JOIN (
                    SELECT
                        MAX (ka.created_at),
                        ks.id_instansi
                    FROM jaga.korsupgah_survey ks
                    JOIN jaga.korsupgah_answer ka ON ks.id = ka.id_survey
                    WHERE ks.id_template = :id_template AND ks.deleted_at IS NULL
                    GROUP BY ks.id_instansi
                ) lu ON i.id = lu.id_instansi
                LEFT JOIN (
                    SELECT 
                        COUNT(*),
                        ks.id_instansi
                    FROM jaga.korsupgah_survey ks
                    JOIN jaga.korsupgah_template kt ON ks.id_template = kt.id
                    JOIN jaga.korsupgah_template_sub_indikator ktsi ON kt.ID = ktsi.id_template
                    WHERE ks.id_template = :id_template AND ks.deleted_at IS NULL
                    GROUP BY ks.id_instansi
                ) tq ON tq.id_instansi = i.id
                LEFT JOIN (
                    SELECT 
                        COUNT(*),
                        sq.id_instansi
                    FROM
                    (
                        SELECT 
                            DISTINCT ks.id,
                            ka.id_question,
                            ks.id_instansi
                        FROM jaga.korsupgah_survey ks
                        JOIN jaga.korsupgah_answer ka ON ks.ID = ka.id_survey 
                        WHERE ks.id_template = :id_template AND ks.deleted_at IS NULL
                    ) sq
                    GROUP BY sq.id_instansi
                ) taq ON taq.id_instansi = i.id
                LEFT JOIN (
                    SELECT 
                        COUNT(*),
                        sq2.id_instansi 
                    FROM
                    (
                        SELECT 
                            DISTINCT ks.ID,
                            ka.id_question,
                            ks.id_instansi 
                        FROM
                            jaga.korsupgah_survey ks
                        JOIN jaga.korsupgah_answer ka ON ks.ID = ka.id_survey 
                        WHERE ka.verified_at IS NULL AND ks.id_template = :id_template AND ks.deleted_at IS NULL
                    ) sq2 
                    GROUP BY sq2.id_instansi
                ) tuq ON i.id = tuq.id_instansi
                LEFT JOIN (
                    SELECT 
                        COUNT (*),
                        kpt.id_instansi
                    FROM jaga.korwil_pad_progress kpp
                    JOIN jaga.korwil_pad_target kpt ON kpt.ID = kpp.id_pad_target
                    WHERE kpt.tahun = :tahun
                    GROUP BY kpt.id_instansi
                ) tpp ON i.id = tpp.id_instansi
                LEFT JOIN (
                    SELECT 
                        COUNT (*),
                        kpt.id_instansi
                    FROM jaga.korwil_pad_progress kpp
                    JOIN jaga.korwil_pad_target kpt ON kpt.ID = kpp.id_pad_target 
                    WHERE kpp.status = 1 AND kpt.tahun = :tahun
                    GROUP BY kpt.id_instansi
                ) tppv ON i.id = tppv.id_instansi
                LEFT JOIN (
                    SELECT 
                        COUNT(*),
                        kat.id_instansi 
                    FROM jaga.korwil_aset_progress kap
                    JOIN jaga.korwil_aset_target kat ON kat.ID = kap.id_aset_target 
                    WHERE kat.tahun = :tahun
                    GROUP BY kat.id_instansi
                ) tap ON i.id = tap.id_instansi
                LEFT JOIN (
                    SELECT 
                        COUNT (*),
                        kat.id_instansi
                    FROM jaga.korwil_aset_progress kap
                    JOIN jaga.korwil_aset_target kat ON kat.ID = kap.id_aset_target 
                    WHERE kap.status = 1 AND kat.tahun = :tahun
                    GROUP BY kat.id_instansi
                ) tapv ON i.id = tapv.id_instansi
                WHERE i.nama ilike :keyword AND i.id_tipe in (1, 2) $whereBPKP
            ) q
            $orderStatement
            limit :limit
            offset :offset;
        ";

        $result = $this->rawQuery($q, [
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'keyword' => '%' . $params['keyword'] . '%',
            'id_template' => $params['id_template'],
            'tahun' => $params['tahun']
        ]);

        $total_record = $this->rawQuery("
            select count(*) as total from jaga.instansi i 
            left join jaga.rel_instansi_wilayah riw on i.id = riw.id_instansi and $tahun >= riw.start_year and $tahun <= riw.end_year
            where nama ilike :keyword and id_tipe IN (1, 2) $whereBPKP
        ", [
            "keyword" => '%' . $params['keyword'] . '%'
        ])[0]['total'];

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

    public function instansi_overview()
    {
        $this->checkOneOfScopes(['korsupgah.monitoring', 'korwil.monitoring_kemdagri', 'korwil.monitoring_bpkp', 'korwil.verifikator_kemdagri_bpkp', 'korwil.observer_kemdagri_bpkp']);

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

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['order_by'] = array_key_exists('order_by', $getParams) ? strtolower($getParams['order_by']) : 'percent_completion';
        $getParams['order_direction'] = array_key_exists('order_direction', $getParams) ? strtolower($getParams['order_direction']) : 'asc';

        $allowed_order = ['percent_completion', 'percent_verification', 'total_question', 'total_verified_question', 'total_answered_question'];
        $allowed_order_direction = ['asc', 'desc'];

        $params = $this->validate(
            $getParams,
            [
                'id_instansi' => ['validators' => ['digit', 'required']],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'order_by' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => $allowed_order
                        ]
                    ]
                ],
                'order_direction' => [
                    'validators' => [
                        [
                            'name' => 'inclusion_in',
                            'params' => $allowed_order_direction
                        ]
                    ]
                ]
            ]
        );

        $instansi = Instansi::findFirst($params['id_instansi']);

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

        $order_direction = $params['order_direction'];
        $order_column = $params['order_by'];

        $rows = $this->rawQuery("
        select q.*,
                round((q.total_verified_question * 100.0) / NULLIF(q.total_answered_question,0), 2) as percent_verification,
                round((q.total_answered_question * 100.0) / NULLIF(q.total_question,0), 2) as percent_completion
            from (
                select ks.id, id_periode, id_instansi,id_template,
                    (
                        select count(*)
                        from jaga.korsupgah_template kt
                            join jaga.korsupgah_template_sub_indikator ktt on kt.id = ktt.id_template
                        where kt.id = ks.id_template
                    ) total_question,
                    (
                        select count(*)
                        from jaga.korsupgah_template kt
                            join jaga.korsupgah_template_sub_indikator ktt on kt.id = ktt.id_template
                            join jaga.korsupgah_answer ka on ka.id_survey = ks.id AND ka.id_question = ktt.id_sub_indikator
                        where kt.id = ks.id_template
                    ) total_answered_question,
                    (
                        select count(*)
                        from jaga.korsupgah_template kt
                            join jaga.korsupgah_template_sub_indikator ktt on kt.id = ktt.id_template
                            join jaga.korsupgah_answer ka on ka.id_survey = ks.id AND ka.id_question = ktt.id_sub_indikator
                        where kt.id = ks.id_template AND ka.status = :status_verified
                    ) total_verified_question
                from jaga.korsupgah_survey ks
                where ks.id_instansi = :id_instansi AND ks.deleted_at IS NULL
            ) q
            order by $order_column $order_direction NULLS LAST
            limit :limit
            offset :offset;
        ", [
            'id_instansi' => $params['id_instansi'],
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'status_verified' => KorsupgahAnswer::STATUS_VERIFIED
        ]);

        $total_record = Instansi::count(
            [
                "id = :id_instansi:",
                "bind" => [
                    "id_instansi" => $params['id_instansi']
                ]
            ]
        );

        return new PaginatedResponse(
            $rows,
            $total_record,
            $params['limit'],
            $params['offset'],
            ['references' => ['instansi' => $instansi->toArray()]]
        );
    }

    public function exportInstansiOverview()
    {
        $this->checkOneOfScopes(['admin.view', 'korsupgah.admin', 'korsupgah.verify', 'korwil.verifikator_kemdagri_bpkp']);
        $params = $this->request->get();
        $validatedParams = $this->validate($params, [
            'template_id' => ['validators' => ['required', 'digit']],
            'team_id' => ['validators' => ['digit']],
            'provinsi_id' => ['validators' => ['digit']]
        ]);
        $andWhere = "";
        $bind = [];
        if (!is_null($validatedParams['team_id'])) {
            $andWhere .= " AND kts.id = :team_id";
            $bind['team_id'] = $validatedParams['team_id'];
        }
        if (!is_null($validatedParams['provinsi_id'])) {
            $andWhere .= " AND CASE WHEN riw.id_provinsi IS NULL THEN i.id_provinsi = :provinsi_id ELSE riw.id_provinsi = :provinsi_id END";
            $bind['provinsi_id'] = $validatedParams['provinsi_id'];
        }

        $templateId = $validatedParams['template_id'];
        $areaIntervensiList = $this->getListAreaIntervensi($templateId);
        if (sizeof($areaIntervensiList) === 1) {
            $areaIntervensiList = $areaIntervensiList[0];
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Area Intervensi dengan Template [$templateId] tidak ditemukan");
        }

        $this->template = KorsupgahTemplate::findFirst($templateId);
        if (!$this->template) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Template [$templateId] tidak ditemukan");
        }
        $tahun = $this->template->tahun;

        $qTotalQuestion = "SELECT ks.id_instansi as id, i.nama as instansi, kts.name as team, mp.provinsi_name as provinsi, ";
        $qTotalAnswered = "SELECT i.id, i.nama as instansi, kts.name as team, mp.provinsi_name as provinsi, ";
        $refAreaIntervensi = [];
        foreach ($areaIntervensiList["area_intervensi_list"] as $ai) {
            $aiId = $ai['id'];
            $qTotalQuestion .= "(count(*) FILTER (WHERE ii.id_area_intervensi = $aiId)) as total_question_$aiId,";
            $qTotalAnswered .= "(count(ka.id) FILTER (WHERE ai.id = $aiId)) as total_answered_$aiId,";
            $qTotalAnswered .= "(count(ka.id) FILTER (WHERE ai.id = $aiId and ka.nilai_verifikasi IS NULL)) as total_unverified_$aiId,";
            $refAreaIntervensi[$aiId] = $ai;
        }
        $qTotalQuestion .= "
            1
            FROM jaga.korsupgah_survey ks
            JOIN jaga.korsupgah_template kt ON ks.id_template = kt.id
            JOIN jaga.korsupgah_template_sub_indikator ktsi ON kt.ID = ktsi.id_template
            LEFT JOIN jaga.instansi i ON i.id = ks.id_instansi
            LEFT JOIN jaga.korsupgah_sub_indikator si ON si.id = ktsi.id_sub_indikator
            LEFT JOIN jaga.korsupgah_indikator ii ON ii.id = si.id_indikator
            LEFT JOIN jaga.korwil_teams kts ON kts.id_periode = ks.id_periode
            LEFT JOIN jaga.korwil_team_instansi kti ON (kti.team_id = kts.id AND kti.instansi_id = i.id)
            LEFT JOIN jaga.rel_instansi_wilayah riw ON (riw.id_instansi = i.id AND $tahun >= riw.start_year AND $tahun <= riw.end_year)
            LEFT JOIN jaga.master_provinsi mp ON mp.provinsi_code::INTEGER = COALESCE(riw.id_provinsi, i.id_provinsi)
            WHERE kt.id = $templateId AND kti.id IS NOT NULL AND ks.deleted_at IS NULL $andWhere
            GROUP BY ks.id_instansi, i.id, kts.id, mp.provinsi_code
            ORDER BY mp.provinsi_code, i.nama NULLS FIRST
        ";
        $qTotalAnswered .= "
            1
            FROM jaga.korsupgah_survey ks
            JOIN jaga.korsupgah_template t on t.id = ks.id_template
            JOIN jaga.korsupgah_template_sub_indikator ktsi ON t.id = ktsi.id_template
            LEFT JOIN jaga.korsupgah_sub_indikator si ON si.id = ktsi.id_sub_indikator
            LEFT JOIN (
                SELECT distinct on (1, 2) a.id_question, a.id_survey, a.id, a.nilai, a.nilai_verifikasi, a.created_at, a.verified_at, a.keterangan_verifikasi
                FROM jaga.korsupgah_answer a
                ORDER BY a.id_question, a.id_survey, a.created_at DESC
            ) ka ON ( ka.id_question = si.id AND ka.id_survey = ks.id )
            LEFT JOIN jaga.instansi i ON i.id = ks.id_instansi
            LEFT JOIN jaga.korsupgah_indikator ii ON ii.id = si.id_indikator
            LEFT JOIN jaga.korsupgah_area_intervensi ai ON ai.id = ii.id_area_intervensi
            LEFT JOIN jaga.korwil_teams kts ON kts.id_periode = ks.id_periode
            LEFT JOIN jaga.korwil_team_instansi kti ON (kti.team_id = kts.id AND kti.instansi_id = i.id)
            LEFT JOIN jaga.rel_instansi_wilayah riw ON (riw.id_instansi = i.id AND $tahun >= riw.start_year AND $tahun <= riw.end_year)
            LEFT JOIN jaga.master_provinsi mp ON mp.provinsi_code::INTEGER = COALESCE(riw.id_provinsi, i.id_provinsi)
            WHERE ks.id_template = $templateId AND kti.id IS NOT NULL AND ks.deleted_at IS NULL $andWhere
            GROUP BY i.id, kts.id, mp.provinsi_code
            ORDER BY mp.provinsi_code, i.nama NULLS FIRST
        ";

        $totalQuestion = $this->rawQuery($qTotalQuestion, $bind);
        $totalAnswered = $this->rawQuery($qTotalAnswered, $bind);

        $rows = [];
        foreach ($totalQuestion as $q) {
            $rows[$q['id']] = $q;
        }
        foreach ($totalAnswered as $a) {
            if (array_key_exists($a['id'], $rows)) {
                $rows[$a['id']] = array_merge($a, $rows[$a['id']]);
            } else {
                $rows[$a['id']] = $a;
            }
        }

        $lastCol = "C";
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue("A1", 'Status Pengisian MCP');
        $sheet->setCellValue("A2", $areaIntervensiList['nama']);

        $sheet->setCellValue("A4", 'No');
        $sheet->mergeCells('A4:A5');
        $sheet->setCellValue("B4", 'Instansi');
        $sheet->mergeCells('B4:B5');
        $sheet->setCellValue("C4", 'Provinsi');
        $sheet->mergeCells('C4:C5');

        // Total header
        $startCol = $lastCol;
        $sheet->setCellValue(++$startCol . "4", "Total");
        $sheet->setCellValue(++$lastCol . "5", 'Pertanyaan');
        $sheet->setCellValue(++$lastCol . "5", 'Terjawab');
        $sheet->setCellValue(++$lastCol . "5", '% Terjawab');
        $sheet->setCellValue(++$lastCol . "5", 'Belum Verifikasi');
        $sheet->getStyle($startCol . "4:" . $lastCol . "4")
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells($startCol . '4:' . $lastCol . '4');
        $startTotalCol = $startCol;

        // per Area Intervensi header
        foreach ($refAreaIntervensi as $ai) {
            $startCol = $lastCol;
            $sheet->setCellValue(++$startCol . "4", $ai['nama']);
            $sheet->setCellValue(++$lastCol . "5", 'Pertanyaan');
            $sheet->setCellValue(++$lastCol . "5", 'Terjawab');
            $sheet->setCellValue(++$lastCol . "5", '% Terjawab');
            $sheet->setCellValue(++$lastCol . "5", 'Belum Verifikasi');
            $sheet->getStyle($startCol . "4:" . $lastCol . "4")
                ->getAlignment()
                ->setHorizontal(Alignment::HORIZONTAL_CENTER);
            $sheet->mergeCells($startCol . '4:' . $lastCol . '4');
        }

        // All Header Styling
        $sheet->getStyle("A1:" . $lastCol . "2")
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('A1:' . $lastCol . '1');
        $sheet->mergeCells('A2:' . $lastCol . '2');
        $sheet->getStyle("A1:" . $lastCol . "5")->getFont()->setBold(true);

        $rowNum = 5;
        $num = 1;
        foreach ($rows as $row) {
            $lastCol = 'G';
            $rowNum++;
            $sheet->setCellValue("A$rowNum", $num);
            $sheet->setCellValue("B$rowNum", $row['instansi']);
            $sheet->setCellValue("C$rowNum", $row['provinsi']);

            $totalQuestion = $totalAnswered = $totalUnverified = 0;
            foreach ($refAreaIntervensi as $ai) {
                // per Area Intervensi Row data
                $question = array_key_exists('total_question_' . $ai['id'], $row) ? $row['total_question_' . $ai['id']] : 0;
                $answered = array_key_exists('total_answered_' . $ai['id'], $row) ? $row['total_answered_' . $ai['id']] : 0;
                $percentage = $answered > 0 && $question > 0 ? number_format(doubleval(($answered / $question) * 100), 2) : 0;
                $unverified = array_key_exists('total_unverified_' . $ai['id'], $row) ? $row['total_unverified_' . $ai['id']] : 0;
                $sheet->setCellValue(++$lastCol . $rowNum, $question);
                $sheet->setCellValue(++$lastCol . $rowNum, $answered);
                $sheet->setCellValue(++$lastCol . $rowNum, $percentage);
                $sheet->setCellValue(++$lastCol . $rowNum, $unverified);

                $totalQuestion += $question;
                $totalAnswered += $answered;
                $totalUnverified += $unverified;
            }
            // Total Row data
            $totalPercentage = $totalAnswered > 0 && $totalQuestion > 0 ? number_format(doubleval(($totalAnswered / $totalQuestion) * 100), 2) : 0;
            $totalCol = $startTotalCol;
            $sheet->setCellValue($totalCol . $rowNum, $totalQuestion);
            $sheet->setCellValue(++$totalCol . $rowNum, $totalAnswered);
            $sheet->setCellValue(++$totalCol . $rowNum, $totalPercentage);
            $sheet->setCellValue(++$totalCol . $rowNum, $totalUnverified);
            $num++;
        }

        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $sheet->getStyle('A4:' . $lastCol . ($rowNum))->applyFromArray($styleArray);

        $writer = new Xlsx($spreadsheet);
        $filename = "Status Pengisian MCP - " . $areaIntervensiList['nama'] . DateHelper::formatTodayIndex() . ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    private function getListAreaIntervensi($templateId = null)
    {
        $whereTemplate = "";
        if ($templateId) {
            $whereTemplate = " AND t.id = $templateId ";
        }
        $items = $this->rawQuery("
            SELECT t.id, t.nama, t.id_dana_desa, ai.id AS area_intervensi_id, ai.nama AS area_intervensi_nama
            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 $whereTemplate
            GROUP BY t.id, t.nama, ai.id, ai.nama
            ORDER BY t.id, 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'],
                    'area_intervensi_list' => $areaIntervensiList
                ];
            })
            ->toArray();

        return $templates;
    }

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

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

        $activeSurvey = KorsupgahSurvey::find([
            'conditions' => 'id_instansi=:id_instansi: AND deleted_at is NULL',
            'bind' => [
                'id_instansi' => $current_instansi->id
            ]
        ]);
        $result = [];

        foreach ($activeSurvey as $key => $survey) {
            $result[] = $survey->getDetail(true);
        }

        return new CollectionPaginatedResponse($result);
    }

    public function myPad()
    {
        $this->checkOneOfScopes(['korsupgah.answer', 'pad.answer']);

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

        $activeSurvey = KorwilPadTarget::find([
            'conditions' => 'id_instansi = :id: AND deleted_at is NULL',
            'bind' => [
                'id' => $current_instansi->id
            ]
        ]);

        if (!empty($activeSurvey->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Target instansi sudah tidak aktif');
        }

        $result = [];

        foreach ($activeSurvey as $key => $survey) {
            $result[] = $survey->toDetail();
        }

        return new CollectionPaginatedResponse($result);
    }

    public function myAset()
    {
        $this->checkOneOfScopes(['korsupgah.answer', 'aset.answer']);

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

        $activeSurvey = KorwilAsetTarget::find([
            'conditions' => 'id_instansi = :id: AND deleted_at is NULL',
            'bind' => [
                'id' => $current_instansi->id
            ]
        ]);

        if (!empty($activeSurvey->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Aset instansi sudah tidak aktif');
        }

        $result = [];

        foreach ($activeSurvey as $key => $survey) {
            $result[] = $survey->toDetail();
        }

        return new CollectionPaginatedResponse($result);
    }

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

        if ($id == 'null')
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Parameter ID tidak boleh "null"!');

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

        $survey = KorsupgahSurvey::findFirst([
            'conditions' => 'id=:id: AND id_instansi=:id_instansi: AND deleted_at is NULL',
            'bind' => [
                'id_instansi' => $current_instansi->id,
                'id' => $id
            ]
        ]);

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

        $result = $survey->getDetail();

        return new CollectionPaginatedResponse([$result]);
    }

    public function detail($id)
    {
        ini_set('max_execution_time', '600'); // timeout: 10 menit
        set_time_limit(600);
        $this->checkOneOfScopes(['korsupgah.admin', 'korsupgah.monitoring', 'korsupgah.verify', 'korwil.monitoring_kemdagri', 'korwil.monitoring_bpkp', 'korwil.verifikator_kemdagri_bpkp', 'korwil.observer_kemdagri_bpkp']);
        $survey = KorsupgahSurvey::findFirst(['conditions' => 'id = :id: AND deleted_at IS NULL', 'bind' => ['id' => $id]]);

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

        if ($this->hasOneOfScopes(['korwil.monitoring_kemdagri'])) {
            if ($this->shared->user->id_jabatan != null) {
                $idAreaFokus = array_column(
                    KorsupgahAreaFokusPenanggungJawab::find([
                        'conditions' => 'id_jabatan = ?0',
                        'bind' => [$this->shared->user->id_jabatan]
                    ])->toArray(),
                    'id_area_fokus'
                );

                if (!empty($idAreaFokus)) {
                    $idAreaIntervensi = array_column(
                        KorsupgahAreaIntervensi::find([
                            'conditions' => 'id_area_fokus IN ({id_area_fokus:array})',
                            'bind' => [
                                'id_area_fokus' => array_values($idAreaFokus)
                            ]
                        ])->toArray(),
                        'id'
                    );

                    if (!empty($idAreaIntervensi)) {
                        $idIndikator = array_column(
                            KorsupgahIndikator::find([
                                'conditions' => 'id_area_intervensi IN ({id_area_intervensi:array})',
                                'bind' => [
                                    'id_area_intervensi' => array_values($idAreaIntervensi)
                                ]
                            ])->toArray(),
                            'id'
                        );

                        if (!empty($idIndikator)) {
                            $idSubIndikator = array_column(
                                KorsupgahSubIndikator::find([
                                    'conditions' => 'id_indikator IN ({id_indikator:array})',
                                    'bind' => [
                                        'id_indikator' => array_values($idIndikator)
                                    ]
                                ])->toArray(),
                                'id'
                            );

                            if (!empty($idSubIndikator))
                                $survey->ids_sub_indikator_json = json_encode($idSubIndikator);
                        }
                    }
                }
            }
        }
        $result = $survey->getDetail();
        $result = $this->processSurveyDetails($result);

        return new CollectionPaginatedResponse([$result]);
    }

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

        $survey = KorsupgahSurvey::findFirst($id);

        if (empty($survey)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Tidak Ditemukan');
        }

        if (!empty($survey->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Sudah Terhapus');
        }

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

        $params = $this->validate(
            $rawBody,
            [
                'id_periode' => ['validators' => ['required', 'digit']],
                'id_instansi' => ['validators' => ['required', 'digit']],
                'id_template' => ['validators' => ['required', 'digit']],
                'keterangan' => ['validators' => []]
            ]
        );

        $periode = KorsupgahPeriode::findFirst($params['id_periode']);

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

        if (!empty($periode->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Periode sudah tidak aktif');
        }

        $instansi = Instansi::findFirst($params['id_instansi']);
        if (empty($instansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi tidak ditemukan');
        }

        if (!empty($instansi->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi sudah tidak aktif');
        }

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

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

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

        $survey->id_periode = $params['id_periode'];
        $survey->id_instansi = $params['id_instansi'];
        $survey->id_template = $params['id_template'];
        $survey->keterangan = $params['keterangan'];
        $survey->updated_at = date('Y-m-d H:i:s');
        $survey->updated_by = $this->shared->user->um_id;
        $status = $survey->save();

        if (!$status) {
            $errors = $survey->getMessage();
            $error_messages = [];

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

            throw new ValidationsException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$survey]);
        }
    }

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

        $survey = KorsupgahSurvey::findFirst($id);

        if (empty($survey)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Tidak Ditemukan');
        }

        if (!empty($survey->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Sudah Terhapus');
        }

        if (!empty($survey->KorsupgahAnswer->toArray())) {
            throw new CustomErrorException(BaseResponse::PRECONDITION_REQUIRED, 'Survey Sudah Pernah Diisi oleh Instansi');
        }

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

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

            $error_messages = [];

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

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

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

        $survey = KorsupgahSurvey::findFirst($id);

        if (empty($survey)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Tidak Ditemukan');
        }

        if (!empty($survey->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Sudah Terhapus');
        }

        $this->db->begin();
        try {
            $now = date('Y-m-d H:i:s');

            $answers = $survey->KorsupgahAnswer;
            foreach ($answers as $answer) {
                $answer->updated_at = $now;
                $answer->deleted_at = $now;
                $answer->updated_by = $this->shared->user->um_id;
                $status = $answer->save();

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

                    $error_messages = [];

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

                    throw new ValidationException($error_messages);
                }
            }

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

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

                $error_messages = [];

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

                throw new ValidationException($error_messages);
            } else {
                $this->db->commit();
                return new CollectionPaginatedResponse([$survey]);
            }
        } catch (\Exception $e) {
            $this->db->rollback();
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, $e->getMessage());
        }
    }

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

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

        $params = $this->validate(
            $rawBody,
            [
                'id_periode' => ['validators' => ['required', 'digit']],
                'id_instansi' => ['validators' => ['required', 'digit']],
                'id_template' => ['validators' => ['required', 'digit']],
                'keterangan' => ['validators' => []]
            ]
        );

        $periode = KorsupgahPeriode::findFirst($params['id_periode']);

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

        if (!empty($periode->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Periode sudah tidak aktif');
        }

        $instansi = Instansi::findFirst($params['id_instansi']);

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

        if (!empty($instansi->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi sudah tidak aktif');
        }

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

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

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

        $activeSurvey = KorsupgahSurvey::find([
            'conditions' => 'id_periode=:id_periode: AND id_instansi=:id_instansi: AND id_template=:id_template: AND deleted_at is NULL',
            'bind' => [
                'id_periode' => $params['id_periode'],
                'id_instansi' => $params['id_instansi'],
                'id_template' => $params['id_template']
            ]
        ]);

        if (count($activeSurvey) > 0) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey serupa sudah ada');
        }

        $survey = new KorsupgahSurvey();
        $survey->id_periode = $params['id_periode'];
        $survey->id_instansi = $params['id_instansi'];
        $survey->id_template = $params['id_template'];
        $survey->keterangan = $params['keterangan'];
        $survey->created_at = date('Y-m-d H:i:s');
        $survey->created_by = $this->shared->user->um_id;
        $status = $survey->save();

        if (!$status) {
            $errors = $survey->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([$survey]);
        }
    }


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

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

        $params = $this->validate(
            $rawBody,
            [
                'id_periode' => ['validators' => ['required', 'digit']],
                'id_instansi_list' => ['validators' => []],
                'id_template' => ['validators' => ['required', 'digit']],
                'keterangan' => ['validators' => []]
            ]
        );

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

        if (count($params['id_instansi_list']) < 1) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi kosong');
        }

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

        $periode = KorsupgahPeriode::findFirst($params['id_periode']);
        if (empty($periode)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Periode tidak ditemukan');
        }

        if (!empty($periode->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Periode sudah tidak aktif');
        }

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

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

        if (!empty($template->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey template sudah tidak aktif');
        }
        $instansi_list = Instansi::find([
            'conditions' => 'id in ({ids:array}) AND deleted_at is NULL',
            'bind' => [
                'ids' => array_values($id_instansi_list)
            ]
        ]);

        // Check Expected instansi List exists in master data

        $existing_instasi_list = [];

        foreach ($instansi_list as $key => $instansi) {
            $existing_instasi_list[] = $instansi->id;
        }

        $not_exist_ids = array_diff($id_instansi_list, $existing_instasi_list);

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

        $registered_instansi = KorsupgahSurvey::find([
            'conditions' => 'id_template=:id_template: AND id_periode=:id_periode: AND deleted_at is NULL',
            'bind' => [
                'id_template' => $params['id_template'],
                'id_periode' => $params['id_periode']
            ]
        ]);

        $registered_instansi_ids = [];

        foreach ($registered_instansi as $key => $registered_survey) {
            $registered_instansi_ids[] = $registered_survey->id_instansi;
        }

        //Register new surveys
        foreach ($existing_instasi_list as $key => $instansi_id) {
            if (!in_array($instansi_id, $registered_instansi_ids)) {
                $survey = new KorsupgahSurvey();
                $survey->id_periode = $params['id_periode'];
                $survey->id_instansi = $instansi_id;
                $survey->id_template = $params['id_template'];
                $survey->keterangan = $params['keterangan'];
                $survey->created_at = date('Y-m-d H:i:s');
                $survey->created_by = $this->shared->user->um_id;
                $survey->save();
            }
        }
        return new CollectionPaginatedResponse([]);
    }

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

        $survey = KorsupgahSurvey::findFirst($id);

        if (empty($survey)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Tidak Ditemukan');
        }

        if (empty($survey->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey Sudah Aktif');
        }

        $this->db->begin();
        try {
            $now = date('Y-m-d H:i:s');

            $answers = $survey->KorsupgahAnswer;
            foreach ($answers as $answer) {
                $answer->updated_at = $now;
                $answer->deleted_at = null;
                $answer->updated_by = $this->shared->user->um_id;
                $status = $answer->save();

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

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

                    throw new ValidationException($error_messages);
                }
            }

            $survey->updated_at = $now;
            $survey->deleted_at = null;
            $survey->updated_by = $this->shared->user->um_id;
            $status = $survey->save();

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

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

                throw new ValidationException($error_messages);
            } else {
                $this->db->commit();
                return new CollectionPaginatedResponse([$survey]);
            }
        } catch (\Exception $e) {
            $this->db->rollback();
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, $e->getMessage());
        }
    }

    public function verify($id)
    {
        $this->checkOneOfScopes(['korsupgah.verify', 'korwil.monitoring_kemdagri', 'korwil.verifikator_kemdagri_bpkp', 'korsup.qa', 'korsup.pic']);

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

        $params = $this->validate(
            $postParams,
            [
                'nilai_verifikasi' => ['validators' => ['digit']],
                'nilai_verifikasi_qa' => ['validators' => ['digit']],
                'nilai_verifikasi_pic' => ['validators' => ['digit']],
                'keterangan_verifikasi' => ['validators' => []],
                'keterangan_verifikasi_qa' => ['validators' => []],
                'keterangan_verifikasi_pic' => ['validators' => []],
                'attachments' => ['validators' => []],
                'deleted_attachments' => ['validators' => []]
            ]
        );

        $answer = KorsupgahAnswer::findFirstById($id);

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

        if (
            !empty($answer->qa_verified_at) &&
            $this->hasOneOfScopes(['korwil.verifikator_kemdagri_bpkp', 'korsup.qa']) &&
            $this->shared->user->um_id !== $answer->qa_verified_by
        ) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Not authorized to edit this QA');
        }

        $survey = $answer->korsupgahSurvey;
        if (empty($survey)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey tidak ditemukan');
        }

        if (!empty($survey->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey sudah tidak aktif');
        }

        /** @var $periode KorsupgahPeriode */
        $periode = $survey->korsupgahPeriode;
        if (empty($periode)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Periode survey tidak valid');
        }
        if ($this->hasScopes(['korsupgah.verify']) && !$periode->verifyTimeValidForKorsupgah()) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Tidak dapat melakukan verifikasi di luar waktu yang telah ditentukan');
        }
        if ($this->hasOneOfScopes(['korwil.verifikator_kemdagri_bpkp', 'korsup.qa']) && !$periode->verifyTimeValidForBpkpKemdagri()) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Tidak dapat melakukan verifikasi di luar waktu yang telah ditentukan');
        }
        if ($this->hasScopes(['korsup.pic']) && !$periode->verifyTimeValidForPic()) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Tidak dapat melakukan verifikasi di luar waktu yang telah ditentukan');
        }

        if (isset($params['nilai_verifikasi']) && ($params['nilai_verifikasi'] < 0 || $params['nilai_verifikasi'] > 100)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Nilai tidak valid (0-100)');
        } else if (isset($params['nilai_verifikasi_qa']) && ($params['nilai_verifikasi_qa'] < 0 || $params['nilai_verifikasi_qa'] > 100)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Nilai tidak valid (0-100)');
        } else if (isset($params['nilai_verifikasi_pic']) && ($params['nilai_verifikasi_pic'] < 0 || $params['nilai_verifikasi_pic'] > 100)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Nilai tidak valid (0-100)');
        } else if (
            !isset($params['nilai_verifikasi'])
            && !isset($params['nilai_verifikasi_qa'])
            && !isset($params['nilai_verifikasi_pic'])
        ) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Nilai tidak valid');
        }

        if (!empty($params['attachments']) && !is_array($params['attachments'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Format Attachments tidak valid');
        }

        if (!empty($params['deleted_attachments']) && !is_array($params['deleted_attachments'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Format Deleted Attachments tidak valid');
        }

        // if ($this->countRelatedTeam($this->shared->user->um_id, $survey->id_instansi) < 1) {
        //     throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User dan Instansi tidak 1 Korwil');
        // }

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

        if (isset($params['nilai_verifikasi']) && isset($params['nilai_verifikasi'])) {
            $answer->nilai_verifikasi = $params['nilai_verifikasi'];
            $answer->keterangan_verifikasi = $params['keterangan_verifikasi'];
            if ($answer->status < KorsupgahAnswer::STATUS_VERIFIED) {
                $answer->status = KorsupgahAnswer::STATUS_VERIFIED;
            }
            $answer->verified_at = $now;
            $answer->verified_by = $this->shared->user->um_id;
            $tipeVerifikasi = KorsupgahAnswerAttachment::TIPE_VERIFIKASI;
        } else if (isset($params['nilai_verifikasi_qa']) && isset($params['nilai_verifikasi_qa'])) {
            $answer->nilai_verifikasi_qa = $params['nilai_verifikasi_qa'];
            $answer->keterangan_verifikasi_qa = $params['keterangan_verifikasi_qa'];
            if ($answer->status < KorsupgahAnswer::STATUS_VERIFIED_QA) {
                $answer->status = KorsupgahAnswer::STATUS_VERIFIED_QA;
            }
            $answer->qa_verified_at = $now;
            $answer->qa_verified_by = $this->shared->user->um_id;
            $tipeVerifikasi = KorsupgahAnswerAttachment::TIPE_VERIFIKASI_QA;
        } else if (isset($params['nilai_verifikasi_pic']) && isset($params['nilai_verifikasi_pic'])) {
            $answer->nilai_verifikasi_pic = $params['nilai_verifikasi_pic'];
            $answer->keterangan_verifikasi_pic = $params['keterangan_verifikasi_pic'];
            if ($answer->status < KorsupgahAnswer::STATUS_VERIFIED_PIC) {
                $answer->status = KorsupgahAnswer::STATUS_VERIFIED_PIC;
            }
            $answer->pic_verified_at = $now;
            $answer->pic_verified_by = $this->shared->user->um_id;
            $tipeVerifikasi = KorsupgahAnswerAttachment::TIPE_VERIFIKASI_PIC;
        }

        $status = $answer->save();

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

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

            throw new ValidationException($error_messages);
        }

        if (!empty($params['deleted_attachments'])) {
            foreach ($params['deleted_attachments'] as $uuid) {
                $attachment_record = Attachments::findFirstByUuid($uuid);

                if (!empty($attachment_record)) {
                    $korsupgah_attachment_record = KorsupgahAnswerAttachment::findFirstByIdAttachment($attachment_record->id);

                    if (!empty($korsupgah_attachment_record) && $korsupgah_attachment_record->tipe == $tipeVerifikasi) {
                        $korsupgah_attachment_record->delete();
                    }

                    AttachmentHelper::delete($attachment_record, true, $this->minio);
                }
            }
        }

        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_korsupgah_attachments = KorsupgahAnswerAttachment::find([
                        'conditions' => 'id_answer=:id_answer: AND id_attachment=:id_attachment:',
                        'bind' => [
                            'id_answer' => $answer->id,
                            'id_attachment' => $attachment_record->id
                        ]
                    ]);

                    if (count($existing_korsupgah_attachments) < 1) {
                        $korsupgah_attachment = new KorsupgahAnswerAttachment();
                        $korsupgah_attachment->id_answer = $answer->id;
                        $korsupgah_attachment->id_attachment = $attachment_record->id;
                        $korsupgah_attachment->tipe = $tipeVerifikasi;
                        $korsupgah_attachment->created_by = $this->shared->user->um_id;
                        $korsupgah_attachment->save();

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

        return new CollectionPaginatedResponse([$answer]);
    }

    public function verifyWithoutAnswer($id_question, $id_survey)
    {
        $this->checkScopes(['korsupgah.verify']);

        $postParams = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $postParams,
            [
                'nilai_verifikasi' => ['validators' => ['required', 'digit']],
                'keterangan_verifikasi' => ['validators' => ['required']],
                'attachments' => ['validators' => []],
                'deleted_attachments' => ['validators' => []]
            ]
        );

        $answer = new KorsupgahAnswer();
        $answer->id_question = $id_question;
        $answer->id_survey = $id_survey;
        $answer->nilai = null;
        $answer->keterangan = null;
        $answer->nilai_verifikasi = $postParams['nilai_verifikasi'];
        $answer->keterangan_verifikasi = $postParams['keterangan_verifikasi'];
        $answer->status = KorsupgahAnswer::STATUS_VERIFIED;

        $survey = $answer->korsupgahSurvey;

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

        if (!empty($survey->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey sudah tidak aktif');
        }

        $periode = $survey->korsupgahPeriode;

        if (empty($periode)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Periode survey tidak valid');
        }

        $korsupgah_verif_started_at = new DateTime($periode->korsupgah_verif_started_at);
        $korsupgah_verif_ended_at = new DateTime($periode->korsupgah_verif_ended_at);
        $now = new DateTime();

        if ($now > $korsupgah_verif_ended_at) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Masa pengisian tidak valid: telah berakhir pada $periode->korsupgah_verif_ended_at");
        }

        if ($now < $korsupgah_verif_started_at) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Masa pengisian tidak valid: baru dimulai pada $periode->korsupgah_verif_started_at");
        }

        if ($params['nilai_verifikasi'] < 0 || $params['nilai_verifikasi'] > 100) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Nilai verifikasi tidak valid (0-100)');
        }

        $now = date('Y-m-d H:i:s');
        $answer->verified_at = $now;
        $answer->verified_by = $this->shared->user->um_id;

        // attachment
        $tipeVerifikasi = KorsupgahAnswerAttachment::TIPE_VERIFIKASI_QA;

        $status = $answer->save();

        foreach ($params['attachments'] as $key => $uuid) {
            $attachment_record = Attachments::findFirstByUuid($uuid);

            if (!empty($attachment_record) && empty($attachment_record->deleted_at)) {
                $existing_korsupgah_attachments = KorsupgahAnswerAttachment::find([
                    'conditions' => 'id_answer=:id_answer: AND id_attachment=:id_attachment:',
                    'bind' => [
                        'id_answer' => $answer->id,
                        'id_attachment' => $attachment_record->id
                    ]
                ]);

                if (count($existing_korsupgah_attachments) < 1) {
                    $korsupgah_attachment = new KorsupgahAnswerAttachment();
                    $korsupgah_attachment->id_answer = $answer->id;
                    $korsupgah_attachment->id_attachment = $attachment_record->id;
                    $korsupgah_attachment->tipe = $tipeVerifikasi;
                    $korsupgah_attachment->created_by = $this->shared->user->um_id;
                    $korsupgah_attachment->save();

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


        return new CollectionPaginatedResponse([$answer]);
    }

    public function downloadAttachment($uuid)
    {
        $attachment = KorsupgahAnswerAttachment::findFirst(
            [
                "uuid = :uuid:",
                "bind" => [
                    "uuid" => $uuid
                ]
            ]
        );
        if (!empty($attachment)) {
            $attachment_folder = getenv("USER_IMAGE_FOLDER") . DIRECTORY_SEPARATOR . 'korsupgah' . DIRECTORY_SEPARATOR;
            $filename = $attachment_folder . DIRECTORY_SEPARATOR . $uuid;

            if (!file_exists($filename)) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "File Not Found");
            }

            return MediaHelper::fileResponse($filename, $attachment->filename);
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Attachment Not Found");
        }
    }

    public function getAllPreviousAnswerAttachment($id_survey, $id_sub_indikator)
    {
        //id_sub_indikator sama dengan id_question di korsupgah_answer
        $korsupgahAnswer = KorsupgahAnswer::find([
            "conditions" => "id_survey = :id_survey: AND id_question = :id_question:",
            "bind" => [
                "id_survey" => $id_survey,
                "id_question" => $id_sub_indikator,
            ],
            "order" => "id DESC"
        ]);

        if (!empty($korsupgahAnswer)) {
            $result = $korsupgahAnswer->toArray();
            foreach ($korsupgahAnswer as $keyAns => $answer) {
                foreach ($answer->korsupgahAnswerAttachment as $key => $answer_attachment) {
                    if (!empty($answer_attachment->attachments)) {
                        $key = $answer_attachment->tipe == KorsupgahAnswerAttachment::TIPE_JAWABAN
                            ? 'attachments'
                            : 'attachments_verification';

                        $answerAttachmentArr = $answer_attachment->attachments->toArray();
                        $oldAttachments = KorsupgahAnswerAttachment::find([
                            'conditions' => 'id_attachment = :id_attachment: AND id_answer < :id_answer:',
                            'bind' => [
                                'id_attachment' => $answer_attachment->id_attachment,
                                'id_answer' => $answer_attachment->id_answer
                            ]
                        ])->toArray();
                        $answerAttachmentArr['is_old_attachment'] = (!empty($oldAttachments) ? 1 : 0);

                        $result[$keyAns][$key][] = array_merge($answerAttachmentArr, $answer_attachment->getDetail());
                    }
                }
            }
            return new CollectionPaginatedResponse($result);
        } else
            return new CollectionPaginatedResponse([]);
    }

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

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

        $params = $this->validate(
            $postParams,
            [
                'id_survey' => ['validators' => ['required']],
                'id_sub_indikator' => ['validators' => ['required']],
                'nilai' => ['validators' => ['required', 'digit']],
                'keterangan' => ['validators' => []],
                'attachments' => ['validators' => []],
                'deleted_attachments' => ['validators' => []],
                'old_attachments' => ['validators' => []]
            ]
        );

        // it takes one paramater of um_id from the body template.
        $survey = KorsupgahSurvey::findFirst($params['id_survey']);
        // echo $params['id_survey'];
        // echo json_encode($survey);
        // die();

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

        if (!empty($survey->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey sudah tidak aktif');
        }

        if ($this->shared->user->id_instansi != $survey->id_instansi) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi user dan survey tidak sesuai');
        }

        $periode = $survey->korsupgahPeriode;

        if (empty($periode)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Periode survey tidak valid');
        }

        $started_at = new DateTime($periode->started_at);
        $ended_at = new DateTime($periode->ended_at);
        $now = new DateTime();

        if ($now > $ended_at) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Masa pengisian tidak valid: telah berakhir pada $periode->ended_at");
        }

        if ($now < $started_at) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Masa pengisian tidak valid: baru dimulai pada $periode->started_at");
        }

        $sub_indikator = KorsupgahSubIndikator::findFirst($params['id_sub_indikator']);
        // echo $params['id_sub_indikator'];
        // die();
        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 tidak aktif');
        }

        if ($params['nilai'] < 0 || $params['nilai'] > 100) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Nilai tidak valid (0-100)');
        }

        if (!empty($params['attachments']) && !is_array($params['attachments'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Format Attachments tidak valid');
        }

        if (!empty($params['deleted_attachments']) && !is_array($params['deleted_attachments'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Format Deleted Attachments tidak valid');
        }

        $question_id = $params['id_sub_indikator'];
        $survey_id = $params['id_survey'];
        // checking whether the data is already exist or not
        if (KorsupgahAnswer::is_unverified_data_exists($survey_id, $question_id)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Laporan untuk sub-indikator ini telah terkirim');
        }

        $answer = new KorsupgahAnswer();

        $answer->id_survey = $params['id_survey'];
        $answer->id_question = $params['id_sub_indikator'];
        $answer->nilai = $params['nilai'];
        $answer->keterangan = $params['keterangan'];
        $answer->created_at = date('Y-m-d H:i:s');
        $answer->created_by = $this->shared->user->um_id;
        $answer->status = KorsupgahAnswer::STATUS_INIT;
        $status = $answer->save();

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

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

            throw new ValidationException($error_messages);
        }

        if (!empty($params['deleted_attachments'])) {
            foreach ($params['deleted_attachments'] as $uuid) {
                $attachment_record = Attachments::findFirstByUuid($uuid);

                if (!empty($attachment_record)) {
                    $korsupgah_attachment_record = KorsupgahAnswerAttachment::findFirst([
                        'conditions' => "id_attachment = :id_attachment: AND id_answer = :id_answer: AND deleted_at IS NULL",
                        'bind' => [
                            'id_attachment' => $attachment_record->id,
                            'id_answer' => $answer->id,
                        ]
                    ]);

                    if (!empty($korsupgah_attachment_record) && $korsupgah_attachment_record->tipe == KorsupgahAnswerAttachment::TIPE_JAWABAN) {
                        $korsupgah_attachment_record->delete();
                    }

                    $idAnswers = array_column(
                        KorsupgahAnswer::find([
                            'conditions' => "id_survey = :id_survey: AND id_question = :id_question:",
                            'bind' => [
                                'id_survey' => $answer->id_survey,
                                'id_question' => $answer->id_question,

                            ]
                        ])->toArray(),
                        'id'
                    );

                    $existingAttachment = [];

                    if (!empty($idAnswers)) {
                        $existingAttachment = KorsupgahAnswerAttachment::find([
                            'conditions' => "id_attachment = :id_attachment: AND id_answer IN ({id_answer:array}) AND deleted_at IS NULL",
                            'bind' => [
                                'id_attachment' => $attachment_record->id,
                                'id_answer' => array_values($idAnswers),
                            ]
                        ]);
                    }

                    if (empty($existingAttachment))
                        AttachmentHelper::delete($attachment_record, true, $this->minio);
                }
            }
        }

        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_korsupgah_attachments = KorsupgahAnswerAttachment::find([
                        'conditions' => 'id_answer=:id_answer: AND id_attachment=:id_attachment:',
                        'bind' => [
                            'id_answer' => $answer->id,
                            'id_attachment' => $attachment_record->id
                        ]
                    ]);

                    if (count($existing_korsupgah_attachments) < 1) {
                        $korsupgah_attachment = new KorsupgahAnswerAttachment();
                        $korsupgah_attachment->id_answer = $answer->id;
                        $korsupgah_attachment->id_attachment = $attachment_record->id;
                        $korsupgah_attachment->tipe = KorsupgahAnswerAttachment::TIPE_JAWABAN;
                        $korsupgah_attachment->created_by = $this->shared->user->um_id;
                        $korsupgah_attachment->save();

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

        if (!empty($params['old_attachments'])) {
            foreach ($params['old_attachments'] as $id_attachment) {
                $korsupgahAttachment = new KorsupgahAnswerAttachment();
                $korsupgahAttachment->id_answer = $answer->id;
                $korsupgahAttachment->id_attachment = $id_attachment;
                $korsupgahAttachment->tipe = KorsupgahAnswerAttachment::TIPE_JAWABAN;
                $korsupgahAttachment->created_by = $this->shared->user->um_id;
                $korsupgahAttachment->save();
            }
        }

        return new CollectionPaginatedResponse([$answer]);
    }

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

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

        $params = $this->validate(
            $postParams,
            [
                'nilai' => ['validators' => ['required', 'digit']],
                'keterangan' => ['validators' => []],
                'attachments' => ['validators' => []],
                'deleted_attachments' => ['validators' => []],
                'old_attachments' => ['validators' => []]
            ]
        );

        $answer = KorsupgahAnswer::findFirstById($id);

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

        $survey = $answer->KorsupgahSurvey;

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

        if (!empty($survey->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey sudah tidak aktif');
        }

        if ($this->shared->user->id_instansi != $survey->id_instansi) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi user dan survey tidak sesuai');
        }

        $periode = $survey->korsupgahPeriode;

        if (empty($periode)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Periode survey tidak valid');
        }

        $started_at = new DateTime($periode->started_at);
        $ended_at = new DateTime($periode->ended_at);
        $now = new DateTime();

        if ($now > $ended_at) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Masa pengisian tidak valid: telah berakhir pada $periode->ended_at");
        }

        if ($now < $started_at) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Masa pengisian tidak valid: baru dimulai pada $periode->started_at");
        }

        if ($params['nilai'] < 0 || $params['nilai'] > 100) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Nilai tidak valid (0-100)');
        }

        if (!empty($params['attachments']) && !is_array($params['attachments'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Format Attachments tidak valid');
        }

        if (!empty($params['deleted_attachments']) && !is_array($params['deleted_attachments'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Format Deleted Attachments tidak valid');
        }

        $answer->nilai = $params['nilai'];
        $answer->keterangan = $params['keterangan'];
        $answer->updated_by = $this->shared->user->um_id;
        $answer->updated_at = date('Y-m-d H:i:s');
        $status = $answer->save();

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

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

            throw new ValidationException($error_messages);
        }

        if (!empty($params['deleted_attachments'])) {
            foreach ($params['deleted_attachments'] as $uuid) {
                $attachment_record = Attachments::findFirstByUuid($uuid);

                if (!empty($attachment_record)) {
                    $korsupgah_attachment_record = KorsupgahAnswerAttachment::findFirst([
                        'conditions' => "id_attachment = :id_attachment: AND id_answer = :id_answer: AND deleted_at IS NULL",
                        'bind' => [
                            'id_attachment' => $attachment_record->id,
                            'id_answer' => $answer->id,
                        ]
                    ]);

                    if (!empty($korsupgah_attachment_record) && $korsupgah_attachment_record->tipe == KorsupgahAnswerAttachment::TIPE_JAWABAN) {
                        $korsupgah_attachment_record->delete();
                    }

                    $idAnswers = array_column(
                        KorsupgahAnswer::find([
                            'conditions' => "id_survey = :id_survey: AND id_question = :id_question:",
                            'bind' => [
                                'id_survey' => $answer->id_survey,
                                'id_question' => $answer->id_question,

                            ]
                        ])->toArray(),
                        'id'
                    );

                    $existingAttachment = [];

                    if (!empty($idAnswers)) {
                        $existingAttachment = KorsupgahAnswerAttachment::find([
                            'conditions' => "id_attachment = :id_attachment: AND id_answer IN ({id_answer:array}) AND deleted_at IS NULL",
                            'bind' => [
                                'id_attachment' => $attachment_record->id,
                                'id_answer' => array_values($idAnswers),
                            ]
                        ]);
                    }

                    if (empty($existingAttachment))
                        AttachmentHelper::delete($attachment_record, true, $this->minio);
                }
            }
        }

        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_korsupgah_attachments = KorsupgahAnswerAttachment::find([
                        'conditions' => 'id_answer=:id_answer: AND id_attachment=:id_attachment:',
                        'bind' => [
                            'id_answer' => $answer->id,
                            'id_attachment' => $attachment_record->id
                        ]
                    ]);

                    if (count($existing_korsupgah_attachments) < 1) {
                        $korsupgah_attachment = new KorsupgahAnswerAttachment();
                        $korsupgah_attachment->id_answer = $answer->id;
                        $korsupgah_attachment->id_attachment = $attachment_record->id;
                        $korsupgah_attachment->tipe = KorsupgahAnswerAttachment::TIPE_JAWABAN;
                        $korsupgah_attachment->created_by = $this->shared->user->um_id;
                        $korsupgah_attachment->save();

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

        if (!empty($params['old_attachments'])) {
            foreach ($params['old_attachments'] as $id_attachment) {
                $korsupgahAttachment = new KorsupgahAnswerAttachment();
                $korsupgahAttachment->id_answer = $answer->id;
                $korsupgahAttachment->id_attachment = $id_attachment;
                $korsupgahAttachment->tipe = KorsupgahAnswerAttachment::TIPE_JAWABAN;
                $korsupgahAttachment->created_by = $this->shared->user->um_id;
                $korsupgahAttachment->save();
            }
        }

        return new CollectionPaginatedResponse([$answer]);
    }

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

        $answer = KorsupgahAnswer::findFirst($id);
        if (empty($answer)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Answer ' . $id . ' Tidak Ditemukan');
        }

        if (!empty($answer->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Answer ' . $id . ' Sudah Terhapus');
        }

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

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

            $error_messages = [];

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

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

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

        $answer = KorsupgahAnswer::findFirst($id);
        if (empty($answer)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Answer ' . $id . ' Tidak Ditemukan');
        }

        if (empty($answer->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Answer ' . $id . ' Belum Terhapus');
        }

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

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

            $error_messages = [];

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

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

    public function lastAnswersBySurvey($id)
    {
        $this->checkScopes(['korsupgah.admin']);
        $answers = $this->rawQuery("
            SELECT
                distinct on (ka.id_question) ka.*
            FROM korsupgah_answer ka
            LEFT JOIN korsupgah_survey ks ON ks.id = ka.id_survey
            WHERE ks.id = :id
            ORDER BY ka.id_question, ka.updated_at DESC;
        ", ["id" => $id]);

        return new CollectionPaginatedResponse($answers);
    }

    public function unverifiedAnswers()
    {
        $this->checkScopes(["korsupgah.admin"]);
        $params = $this->request->get();
        $params = $this->validate(
            $params,
            [
                "id_survey" => ["validators" => ["digit"]],
                "id_provinsi" => ["validators" => ["digit"]],
                "id_template" => ["validators" => ["digit"]]
            ]
        );
        $where = "ka.status = 0";
        $bind = [];

        if (!empty($params["id_survey"])) {
            $where .= " and ks.id = :id_survey";
            $bind["id_survey"] = $params["id_survey"];
        }

        if (!empty($params["id_provinsi"])) {
            $where .= " and i.id_provinsi = :id_provinsi";
            $bind["id_provinsi"] = $params["id_provinsi"];
        }

        if (!empty($params["id_template"])) {
            $where .= " and ks.id_template = :id_template";
            $bind["id_template"] = $params["id_template"];
        }

        $answers = $this->rawQuery("
            SELECT ka.*
            FROM jaga.korsupgah_answer ka
            LEFT JOIN jaga.korsupgah_survey ks ON ks.id = ka.id_survey
            LEFT JOIN jaga.instansi i ON i.id = ks.id_instansi
            WHERE $where
            ORDER BY i.id, ks.id_template, ka.id_question, ka.updated_at;
        ", $bind);

        return new CollectionPaginatedResponse($answers);
    }

    public function canVerify()
    {
        $this->checkIsAuthenticated();

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

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

        $survey = KorsupgahSurvey::findFirstById($params['id']);
        if (empty($survey)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey tidak ditemukan');
        }

        /** @var $periode KorsupgahPeriode */
        $periode = $survey->korsupgahPeriode;
        if (!$periode) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Periode tidak ditemukan');
        }

        $total = 0;
        // jika verifikator pic, cek apakah waktu verifikasi valid
        // jika verifikator kemdagri bpkp, cek apakah waktu verifikasi valid
        // jika bukan verifikator kemdagri atau waktu verifikasi valid,
        // periksa apakah user merupakan anggota tim korwil yang sesuai
        if ($this->hasScopes(['korsup.pic'])) {
            if ($periode->verifyTimeValidForPic()) {
                $total = $this->countRelatedTeam($this->shared->user->um_id, $survey->id_instansi);
            }
        } else if ($this->hasScopes(['korwil.verifikator_kemdagri_bpkp'])) {
            if ($periode->verifyTimeValidForBpkpKemdagri()) {
                $total = $this->countRelatedTeam($this->shared->user->um_id, $survey->id_instansi);
            }
        } else if ($this->hasScopes(['korsupgah.verify'])) {
            if ($periode->verifyTimeValidForKorsupgah()) {
                $total = $this->countRelatedTeam($this->shared->user->um_id, $survey->id_instansi);
            }
        }

        return new SimpleResponse(['total' => $total]);
    }

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

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

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

        $survey = KorsupgahSurvey::findFirstById($params['id']);
        if (empty($survey)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Survey tidak ditemukan');
        }

        /** @var $periode KorsupgahPeriode */
        $periode = $survey->korsupgahPeriode;
        if (!$periode) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Periode tidak ditemukan');
        }

        $total = 0;
        // jika verifikator pic, cek apakah waktu verifikasi valid
        // jika verifikator kemdagri bpkp, cek apakah waktu verifikasi valid
        // jika bukan verifikator kemdagri atau waktu verifikasi valid,
        // periksa apakah user merupakan anggota tim korwil yang sesuai
        if ($this->hasScopes(['korsup.pic'])) {
            if ($periode->verifyTimeValidForPic()) {
                $total = $this->countRelatedTeam($params["um_id"], $survey->id_instansi);
            }
        } else if ($this->hasOneOfScopes(['korwil.verifikator_kemdagri_bpkp', 'korsup.qa'])) {
            if ($periode->verifyTimeValidForBpkpKemdagri()) {
                $total = $this->countRelatedTeam($params["um_id"], $survey->id_instansi);
            }
        } else if ($this->hasScopes(['korsupgah.verify'])) {
            if ($periode->verifyTimeValidForKorsupgah()) {
                $total = $this->countRelatedTeam($params["um_id"], $survey->id_instansi);
            }
        }

        return new SimpleResponse(['total' => $total]);
    }

    public function summary($id)
    {
        $this->checkOneOfScopes(['korsupgah.verify', 'korsupgah.answer', 'korsupgah.monitoring']);

        $survey = KorsupgahSurvey::findFirst($id);

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

        $result = $survey->overview();

        $items = $this->getSurveyItems($id);
        $items = $this->processSurveyItems($result, $items);

        $result['items'] = $items;
        $result['penalty'] = $this->getPenalty($id);

        return new SimpleResponse($result);
    }

    public function excelSummary($id)
    {
        $this->checkOneOfScopes(['korsupgah.verify', 'korsupgah.answer', 'korsupgah.monitoring']);

        $survey = KorsupgahSurvey::findFirst($id);

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

        $surveyOverview = $survey->overview();

        $items = $this->getSurveyItems($id);
        $items = $this->processSurveyItems($surveyOverview, $items);

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

        $areaIntervensiDictionaries = Collection::from($items)->groupBy(function ($item) {
            return $item['area_intervensi_id'];
        });

        $todayLabel = DateHelper::formatToday();

        $sheet->setCellValue("A1", 'Progress Capaian MCP Korsupgah');
        $sheet->setCellValue("A2", strtoupper($surveyOverview['instansi']->nama));
        $sheet->setCellValue("A3", $todayLabel);

        $sheet->getStyle('A1:A3')
            ->getAlignment()
            ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);

        $sheet->mergeCells('A1:E1');
        $sheet->mergeCells('A2:E2');
        $sheet->mergeCells('A3:E3');

        $sheet->setCellValue("A5", 'No');
        $sheet->setCellValue("B5", 'Area Intervensi');
        $sheet->setCellValue("C5", '% Bobot');
        $sheet->setCellValue("D5", '% Capaian');
        $sheet->setCellValue("E5", 'Indikator');
        $sheet->setCellValue("F5", '% Bobot');
        $sheet->setCellValue("G5", '% Klaim Capaian Pemda');
        $sheet->setCellValue("H5", '% Nilai Verifikasi');
        $sheet->setCellValue("I5", 'Keterangan Verifikasi');

        $row = 6;
        $areaIntervensiIndex = 1;

        foreach ($areaIntervensiDictionaries as $areaIntervensiId => $areaIntervensiEntries) {

            $startRow = $row;
            $sheet->setCellValue("A$row", $areaIntervensiIndex);
            $sheet->setCellValue("B$row", $areaIntervensiEntries->get(0)['area_intervensi_name']);
            $sheet->setCellValue("C$row", $areaIntervensiEntries->get(0)['area_intervensi_bobot']);
            $sheet->setCellValue(
                "D$row",
                $areaIntervensiEntries->reduce(function ($prev, $value) {
                    $nilai = $value['nilai_verifikasi'];
                    if (empty($nilai)) {
                        $nilai = 0;
                    }

                    return $prev + ($nilai * $value['sub_indikator_bobot'] * $value['indikator_bobot'] / 10000);
                }, 0) . ' %'
            );
            $areaIntervensiIndex++;

            $indikatorDictionaries = $areaIntervensiEntries->groupBy(function ($value) {
                return $value['indikator_id'];
            });

            $indikatorIndex = 'a';
            foreach ($indikatorDictionaries as $indikatorId => $indikatorEntries) {
                $sheet->setCellValue("E$row", $indikatorIndex . ". " . $indikatorEntries->get(0)['indikator_name']);
                $sheet->setCellValue("F$row", $indikatorEntries->get(0)['indikator_bobot']);
                $sheet->setCellValue(
                    "G$row",
                    $indikatorEntries->reduce(function ($prev, $value) {
                        $nilai = $value['nilai_pemda'];
                        if (empty($nilai)) {
                            $nilai = 0;
                        }

                        return $prev + ($nilai * $value['sub_indikator_bobot'] / 100);
                    }, 0) . ' %'
                );
                $sheet->setCellValue(
                    "H$row",
                    $indikatorEntries->reduce(function ($prev, $value) {
                        $nilai = $value['nilai_verifikasi'];
                        if (empty($nilai)) {
                            $nilai = 0;
                        }

                        return $prev + ($nilai * $value['sub_indikator_bobot'] / 100);
                    }, 0) . ' %'
                );
                $sheet->getStyle("F$row:H$row")
                    ->getAlignment()
                    ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);

                $opt = [
                    'ignore_errors' => true, //default false
                    'drop_links' => false // default false
                ];
                $ket_verif = \Soundasleep\Html2Text::convert($indikatorEntries->get(0)['keterangan_verifikasi'], $opt);
                $sheet->setCellValue("I$row", $ket_verif);

                $row++;
                $indikatorIndex++;
            }

            $lastRow = $row - 1;
            $sheet->mergeCells("A$startRow:A$lastRow");
            $sheet->getStyle("A$startRow:A$lastRow")
                ->getAlignment()
                ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
            $sheet->mergeCells("B$startRow:B$lastRow");
            $sheet->getStyle("B$startRow:B$lastRow")
                ->getAlignment()
                ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
            $sheet->mergeCells("C$startRow:C$lastRow");
            $sheet->getStyle("C$startRow:C$lastRow")
                ->getAlignment()
                ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
            $sheet->getStyle("C$startRow:C$lastRow")
                ->getAlignment()
                ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
            $sheet->mergeCells("D$startRow:D$lastRow");
            $sheet->getStyle("D$startRow:D$lastRow")
                ->getAlignment()
                ->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
            $sheet->getStyle("D$startRow:D$lastRow")
                ->getAlignment()
                ->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
        }

        $sheet->getColumnDimension('A')->setAutoSize(true);
        $sheet->getColumnDimension('B')->setAutoSize(true);
        $sheet->getColumnDimension('C')->setAutoSize(true);
        $sheet->getColumnDimension('D')->setAutoSize(true);
        $sheet->getColumnDimension('E')->setAutoSize(true);
        $sheet->getColumnDimension('F')->setAutoSize(true);
        $sheet->getColumnDimension('G')->setAutoSize(true);
        $sheet->getColumnDimension('H')->setAutoSize(true);
        $sheet->getColumnDimension('I')->setAutoSize(true);

        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];

        $sheet->getStyle('A5:I' . ($row - 1))->applyFromArray($styleArray);

        $writer = new Xlsx($spreadsheet);
        $filename = $surveyOverview['instansi']->nama . "-" . $surveyOverview['template']->nama . "-" . $todayLabel . ".xls";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    private function getSurveyItems($id)
    {
        return $this->rawQuery('
SELECT
	p.nama as periode_name,
	p.id as periode_id,
	s.id as survey_id,
	t.id as template_id,
    ins.id as instansi_id,
	ai.id as area_intervensi_id,
	i.id as indikator_id,
	si.id as sub_indikator_id,
	answer.answer_id as answer_id,
	ins.nama as instansi_name,
	t.nama as template_name,
	ai.nama as area_intervensi_name,
	i.nama as indikator_name,
	si.nama as sub_indikator_name,
    COALESCE(answer.nilai_verifikasi_qa, answer.nilai_verifikasi, 0) as nilai_verifikasi,
    answer.nilai as nilai_pemda,
	CASE WHEN ins.flag_dana_desa = 1 THEN ai.presentase_bobot ELSE ai.presentase_bobot_wo_dana_desa END as area_intervensi_bobot,
	i.presentase_bobot as indikator_bobot,
	si.presentase_bobot as sub_indikator_bobot,
    coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::decimal  * (CASE WHEN ins.flag_dana_desa = 1 THEN ai.presentase_bobot ELSE ai.presentase_bobot_wo_dana_desa END) * i.presentase_bobot * si.presentase_bobot/ 1000000 as nilai_bersih,
    answer.keterangan_verifikasi
FROM "jaga"."korsupgah_survey" s
	join jaga.korsupgah_template t on t.id = s.id_template
	join jaga.korsupgah_template_sub_indikator tsi on t.id = tsi.id_template and tsi.deleted_at is null
	join jaga.korsupgah_sub_indikator si on tsi.id_sub_indikator = si.id
	join jaga.korsupgah_indikator i on i.id = si.id_indikator
	join jaga.korsupgah_area_intervensi ai on ai.id = i.id_area_intervensi
	join jaga.korsupgah_indikator_tipe it on i.id_tipe = it.id
    join jaga.instansi ins on ins.id = s.id_instansi
    JOIN jaga.master_provinsi mprov ON ins.id_provinsi = mprov.provinsi_code::INTEGER
	join jaga.korsupgah_periode p on p.id = s.id_periode
	left 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.keterangan_verifikasi, a.nilai
		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 = :survey_id and tsi.deleted_at is null
ORDER BY ai.id, i.id, si.id;
        ', [
            'survey_id' => $id
        ]);
    }

    public function reportIndikator($templateId)
    {
        $getParams = $this->request->get();

        $res_report = $this->rawQuery('
SELECT
	*
FROM
	(
	SELECT
		ins.ID AS id_instansi,
		ins.nama AS instansi,
		mp.provinsi_name AS provinsi,
		kai.nama AS area_intervensi,
		kai.presentase_bobot AS bobot_area_intervensi,
		ki.nama AS indikator,
		ki.presentase_bobot AS bobot_indikator,
		ka.id_question AS id_subindikator,
		ksi.nama AS sub_indikator,
		ksi.presentase_bobot AS bobot_sub_indikator,
		ka.nilai,
		ka.nilai_verifikasi,
		ka.created_at,
		ka.verified_at,
		RANK ( ) OVER ( PARTITION BY ins.nama, kai.nama, ki.nama, ka.id_question ORDER BY ka.created_at DESC ) AS rk
	FROM
		instansi ins
		JOIN master_provinsi mp ON mp.provinsi_code :: INTEGER = ins.id_provinsi
		FULL OUTER JOIN korsupgah_survey ks ON ks.id_instansi = ins.
		ID FULL OUTER JOIN korsupgah_answer ka ON ka.id_survey = ks.
		ID FULL OUTER JOIN korsupgah_sub_indikator ksi ON ksi.ID = ka.id_question
		FULL OUTER JOIN korsupgah_indikator ki ON ki.ID = ksi.id_indikator
		FULL OUTER JOIN korsupgah_area_intervensi kai ON kai.ID = ki.id_area_intervensi
		WHERE-- 	ka.status >= 1 AND
		ks.id_template = :template_id
	ORDER BY
		ins.ID,
		ka.id_question ASC
	) AS t_report
WHERE
	rk = 1;
        ', [
            'template_id' => $templateId
        ]);

        return new CollectionPaginatedResponse($res_report);
    }

    public function listNotificationNeedVerification()
    {
        $params = $this->request->get();

        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['instansi_name'] = array_key_exists('instansi_name', $params) ? $params['instansi_name'] : null;
        $params['template_name'] = array_key_exists('template_name', $params) ? $params['template_name'] : null;

        $validatedRequest = $this->validate(
            $params,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'instansi_name' => [],
                'template_name' => [],
            ]
        );

        $result = [];
        $totalResult = 0;

        if ($this->hasScopes(['korsupgah.verify'])) {
            $teamIds = array_column(
                KorwilTeamUser::find([
                    'columns' => 'team_id',
                    'conditions' => 'user_id = :user_id:',
                    'bind' => [
                        'user_id' => $this->userID
                    ]
                ])->toArray(),
                "team_id"
            );

            if (!empty($teamIds)) {
                $instansiIds = array_column(
                    KorwilTeamInstansi::find([
                        'columns' => 'instansi_id',
                        'conditions' => 'team_id IN ({team_ids:array})',
                        'bind' => [
                            'team_ids' => $teamIds
                        ]
                    ])->toArray(),
                    'instansi_id'
                );

                if (!empty($instansiIds)) {
                    date_default_timezone_set("Asia/Jakarta");
                    $today = date('Y-m-d H:i:s');

                    $conditions = [
                        'id_instansi IN ({instansi_ids:array})',
                        'start_periode <= :today_1:',
                        'end_periode >= :today_2:',
                    ];
                    $binds = [
                        'instansi_ids' => $instansiIds,
                        'today_1' => $today,
                        'today_2' => $today
                    ];

                    if ($params['instansi_name'] != null) {
                        $conditions[] = 'instansi_name ILIKE :instansi_name:';
                        $binds['instansi_name'] = '%' . $validatedRequest['instansi_name'] . '%';
                    }

                    if ($params['template_name'] != null) {
                        $conditions[] = 'template_name ILIKE :template_name:';
                        $binds['template_name'] = '%' . $validatedRequest['template_name'] . '%';
                    }

                    $totalResult = ViewNotifikasiMcp::count([
                        'conditions' => implode(" AND ", $conditions),
                        'bind' => $binds
                    ]);

                    $result = ViewNotifikasiMcp::find([
                        'conditions' => implode(" AND ", $conditions),
                        'bind' => $binds,
                        'limit' => $validatedRequest['limit'],
                        'offset' => $validatedRequest['offset']
                    ]);
                }
            }
        }
        return new PaginatedResponse($result, $totalResult, $validatedRequest['limit'], $validatedRequest['offset']);
    }

    public function uploadAttachment()
    {
        $this->checkIsAuthenticated();

        $post = $this->request->getPost();
        $params = $this->validate($post, [
            'id_survey' => ['validators' => ['required', 'digit']]
        ]);

        $survey = KorsupgahSurvey::findFirst(["conditions" => "id = :id:", "bind" => ["id" => $params["id_survey"]]]);
        if (!$survey) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Survey tidak ditemukan");
        }
        $template = $survey->KorsupgahTemplate;
        if (!$template || !$template->tahun) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Template tidak valid");
        }

        /** @var MinioService $minioService */
        $minioService = $this->minio;
        $subFolder = KorsupgahAnswerAttachment::getSubfolder($template->tahun, $survey->id);
        $service = new AttachmentService($this->shared, $minioService);
        if ($minioService->isSecondaryS3Available()) {
            $attachments = $service->uploadUsingSecondaryS3(
                getenv("FILESYSTEM_CLOUD"),
                [
                    'allowed-types' => $this->config["allowed_mime_types"],
                ],
                [
                    'tipe' => Attachments::TIPE_PRIVATE
                ],
                $this->request,
                $subFolder,
                DIRECTORY_SEPARATOR
            );
        } else {
            $attachments = $service->upload(
                getenv("FILESYSTEM_CLOUD"),
                [
                    'allowed-types' => $this->config["allowed_mime_types"],
                ],
                [
                    'tipe' => Attachments::TIPE_PRIVATE
                ],
                $this->request,
                $subFolder,
                DIRECTORY_SEPARATOR
            );
        }

        return new CollectionPaginatedResponse($attachments);
    }

    public function downloadAttachmentInline($uuid)
    {
        if ($uuid == 'undefined')
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Parameter UUID tidak boleh "undefined"!');

        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'is_inline' => ['validators' => []],
                'id_survey' => ['validators' => ['required', 'digit']]
            ]
        );

        $survey = KorsupgahSurvey::findFirst(["conditions" => "id = :id:", "bind" => ["id" => $params["id_survey"]]]);
        if (!$survey) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Survey tidak ditemukan");
        }
        $template = $survey->KorsupgahTemplate;
        if (!$template || !$template->tahun) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Template tidak valid");
        }

        $params['is_inline'] = $params['is_inline'] == null ? false : filter_var($params['is_inline'], FILTER_VALIDATE_BOOLEAN);

        $attachment = Attachments::findFirst(
            [
                "uuid = :uuid:",
                "bind" => [
                    "uuid" => $uuid
                ]
            ]
        );

        if ($attachment) {
            if (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::MINIO) {
                /** @var MinioService $minioService */
                $minioService = $this->minio;
                $key = DIRECTORY_SEPARATOR . DIRECTORY_SEPARATOR . $uuid;
                $url = $minioService->base_get_public_url('users_profpic' . DIRECTORY_SEPARATOR . $key, $attachment->filename, !$params['is_inline']);
                if (!MinioService::checkFileExist($url)) {
                    $subFolder = KorsupgahAnswerAttachment::getSubfolder($template->tahun, $survey->id);
                    $key = $subFolder . DIRECTORY_SEPARATOR . $uuid;
                    if ($minioService->isSecondaryS3Available()) {
                        $url = $minioService->basePrivateUrlUsingSecondaryS3($key, $attachment->filename, !$params['is_inline']);
                    } else {
                        $url = $minioService->base_get_private_url($key, $attachment->filename, !$params['is_inline']);
                    }
                }
                $response = new \Phalcon\Http\Response();
                return $response->redirect($url, true, 302);
            } elseif (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::LOCAL) {
                $subFolder = getenv("USER_IMAGE_FOLDER");
                $filename = $subFolder . DIRECTORY_SEPARATOR . $uuid;
                if (file_exists($filename)) {
                    return MediaHelper::fileResponse($filename, null, true, $params['is_inline']);
                } else {
                    $subFolder = getenv("PRIVATE_FOLDER") . KorsupgahAnswerAttachment::getSubfolder($template->tahun, $survey->id);
                    $filename = $subFolder . DIRECTORY_SEPARATOR . $uuid;
                    if (file_exists($filename)) {
                        return MediaHelper::fileResponse($filename, null, true, $params['is_inline']);
                    } else {
                        throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "File Not Found");
                    }
                }
            }
        } else {
            return new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Attachment Not Found");
        }
    }


    private function processSurveyItems($overview, $items)
    {
        $result = [];

        if ($overview['template']->id_dana_desa > 0) {
            if ($overview['instansi']->flag_dana_desa > 0) {
                //summaryWithDanaDesa
                $result = $this->getSurveyItemDanaDesa($overview, $items);
            } else {
                //summaryTransferBobot
                $result = $this->getSurveyItemTransferBobot($overview, $items);
            }
        } else {
            $parTemplate = KorsupgahTemplate::findFirst(
                [
                    "id_dana_desa = :id_template:",
                    "bind" => [
                        "id_template" => $overview['template']->id
                    ]
                ]
            );

            if (!empty($parTemplate)) {
                //summaryUpScale
                $result = $this->getSurveyItemUpscale($overview, $items);
            } else {
                $result = $items;
            }
        }
        return $result;
    }

    private function getSurveyItemDanaDesa($overview, $items)
    {
        $result = $items;

        $surveyDanaDesa = KorsupgahSurvey::findFirst(
            [
                "id_instansi = :id_instansi: AND id_template = :id_template: AND id_periode = :id_periode: AND deleted_at is null",
                "bind" => [
                    "id_instansi" => $overview['id_instansi'],
                    "id_periode" => $overview['id_periode'],
                    "id_template" => $overview['template']->id_dana_desa
                ]
            ]
        );

        if (!empty($surveyDanaDesa)) {
            $surveyItemDanaDesa = $this->rawQuery('
                SELECT
                    p.nama as periode_name,
                    p.id as periode_id,
                    s.id as survey_id,
                    t.id as template_id,
                    ins.id as instansi_id,
                    ai.id as area_intervensi_id,
                    i.id as indikator_id,
                    si.id as sub_indikator_id,
                    answer.answer_id as answer_id,
                    ins.nama as instansi_name,
                    t.nama as template_name,
                    ai.nama as area_intervensi_name,
                    i.nama as indikator_name,
                    si.nama as sub_indikator_name,
                    COALESCE(answer.nilai_verifikasi_qa, answer.nilai_verifikasi, 0) as nilai_verifikasi,
                    answer.nilai as nilai_pemda,
                    ai.presentase_bobot as area_intervensi_bobot,
                    i.presentase_bobot as indikator_bobot,
                    si.presentase_bobot as sub_indikator_bobot,
                    coalesce(answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::decimal  * ai.presentase_bobot * i.presentase_bobot * si.presentase_bobot/ 1000000 as nilai_bersih,
                    answer.keterangan_verifikasi
                FROM "jaga"."korsupgah_survey" s
                    join jaga.korsupgah_template t on t.id = s.id_template
                    join jaga.korsupgah_template_sub_indikator tsi on t.id = tsi.id_template and tsi.deleted_at is null
                    join jaga.korsupgah_sub_indikator si on tsi.id_sub_indikator = si.id
                    join jaga.korsupgah_indikator i on i.id = si.id_indikator
                    join jaga.korsupgah_area_intervensi ai on ai.id = i.id_area_intervensi
                    join jaga.korsupgah_indikator_tipe it on i.id_tipe = it.id
                    join jaga.instansi ins on ins.id = s.id_instansi
                    JOIN jaga.master_provinsi mprov ON ins.id_provinsi = mprov.provinsi_code::INTEGER
                    join jaga.korsupgah_periode p on p.id = s.id_periode
                    left 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.keterangan_verifikasi, a.nilai
                        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 = :survey_id and tsi.deleted_at is null
                ORDER BY ai.id, i.id, si.id;
                        ', [
                'survey_id' => $surveyDanaDesa->id
            ]);

            $result = array_merge(
                array_values($result),
                array_values($surveyItemDanaDesa)
            );
        }

        return $result;
    }

    private function getSurveyItemTransferBobot($overview, $items)
    {
        $result = $items;

        $aiDanaDesa = $this->rawQuery(
            '
            SELECT
                ai.id_transfer_bobot AS id_transfer,
                ai.presentase_bobot AS bobot
            FROM
                jaga.korsupgah_template t
                JOIN jaga.korsupgah_template_sub_indikator tsi ON tsi.id_template = t.id
                JOIN jaga.korsupgah_sub_indikator si ON si.id = tsi.id_sub_indikator
                JOIN jaga.korsupgah_indikator i ON i.id = si.id_indikator
                JOIN jaga.korsupgah_area_intervensi ai ON ai.id = i.id_area_intervensi
            WHERE t.id = :template_id AND tsi.deleted_at IS NULL;',
            [
                'template_id' => $overview['template']->id_dana_desa
            ]
        );

        $bobotDanaDesa = [];
        foreach ($aiDanaDesa as $item) {
            $bobotDanaDesa[$item['id_transfer']] = $item['bobot'];
        }

        for ($i = 0; $i < count($result); $i++) {
            if (array_key_exists($result[$i]['area_intervensi_id'], $bobotDanaDesa)) {
                $bobotAwal = $result[$i]['area_intervensi_bobot'];
                $result[$i]['area_intervensi_bobot'] = $bobotAwal + $bobotDanaDesa[$result[$i]['area_intervensi_id']];
                $result[$i]['nilai_bersih'] = $result[$i]['nilai_bersih'] * $result[$i]['area_intervensi_bobot'] / $bobotAwal;
            }
        }

        return $result;
    }

    private function getSurveyItemUpscale($overview, $items)
    {
        $result = $items;

        $ai_bobot = $this->rawQuery(
            '
            SELECT
                ai.id as ai_id,
                ai.presentase_bobot as bobot
            FROM
                jaga.korsupgah_template t
                JOIN jaga.korsupgah_template_sub_indikator tsi ON tsi.id_template = t.id
                JOIN jaga.korsupgah_sub_indikator si ON si.id = tsi.id_sub_indikator
                JOIN jaga.korsupgah_indikator i ON i.id = si.id_indikator
                JOIN jaga.korsupgah_area_intervensi ai ON ai.id = i.id_area_intervensi
            WHERE t.id = :template_id AND tsi.deleted_at IS NULL
            GROUP BY ai.id, ai.presentase_bobot;',
            [
                'template_id' => $overview['template']->id
            ]
        );

        $total_bobot = 0;
        foreach ($ai_bobot as $ai) {
            $total_bobot += $ai['bobot'];
        }

        $newBobot = [];
        foreach ($ai_bobot as $item) {
            $newBobot[$item['ai_id']] = $item['bobot'] * (100 / $total_bobot);
        }

        for ($i = 0; $i < count($result); $i++) {
            //var_dump($result[$i]);
            $bobot_awal = $result[$i]['area_intervensi_bobot'];
            $result[$i]['area_intervensi_bobot'] = $newBobot[$result[$i]['area_intervensi_id']];
            $result[$i]['nilai_bersih'] = $result[$i]['nilai_bersih'] * $result[$i]['area_intervensi_bobot'] / $bobot_awal;
        }

        //var_dump($total_bobot);

        return $result;
    }

    private function processSurveyDetails($details)
    {
        $result = $details;
        $answerDanaDesa = [];

        if ($details['template']['id_dana_desa'] > 0) {
            if ($details['instansi']->flag_dana_desa > 0) {
                //summaryWithDanaDesa
                $answerDanaDesa = $this->getAnswerDanaDesa($details);
            }
        }

        $result['answers'] = array_merge(
            array_values($details['answers']),
            array_values($answerDanaDesa)
        );

        return $result;
    }

    private function getAnswerDanaDesa($details)
    {
        $result = [];
        $surveyDanaDesa = KorsupgahSurvey::findFirst(
            [
                "id_instansi = :id_instansi: AND id_template = :id_template: AND id_periode = :id_periode: AND deleted_at is null",
                "bind" => [
                    "id_instansi" => $details['id_instansi'],
                    "id_periode" => $details['id_periode'],
                    "id_template" => $details['template']['id_dana_desa']
                ]
            ]
        );

        if (!empty($surveyDanaDesa)) {
            $answer = $surveyDanaDesa->korsupgahAnswer;

            foreach ($answer as $key => $a):
                $result[] = $a->toArray();
            endforeach;
        }

        return $result;
    }

    public function dashboardVerifikator()
    {
        $this->checkOneOfScopes(['korsupgah.admin', 'korsupgah.verify']);
        $request = $this->request->get();
        $this->validate($request, [
            'id_template' => ['validators' => ['required', 'digit']],
            'id_provinsi' => ['validators' => ['required', 'digit']],
            'id_instansi' => ['validators' => ['digit']],
        ]);

        $idTemplate = $request['id_template'];
        $this->template = KorsupgahTemplate::findFirst($idTemplate);
        if (!$this->template) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Template [$idTemplate] tidak ditemukan.");
        }

        $idProvinsi = $request['id_provinsi'];
        $idInstansi = !empty($request['id_instansi']) ? $request['id_instansi'] : null;

        $areaIntervensi = $this->listAreaIntervensiByIdTemplate($idTemplate);
        $areaIntervensiIds = array_column($areaIntervensi, 'id');
        $data = $this->listInstansiQuestionAnswer($idTemplate, $idProvinsi, $areaIntervensiIds, $idInstansi);

        $rerata = $this->totalRerataPengisian($data);
        $rerataByArea = $this->totalRerataPengisianPerArea($data, $areaIntervensiIds);
        $rerataByInstansi = $this->totalRerataPengisianPerInstansi($data);

        $result = array_merge($rerata, $rerataByArea);
        $result['instansi'] = $rerataByInstansi;
        $result['area_intervensi'] = $areaIntervensi;

        return new SimpleResponse($result);
    }

    private function listInstansiQuestionAnswer($idTemplate, $idProvinsi, $areaIntervensiIds, $idInstansi = null)
    {
        $selectQuestion = $selectAnswer = $selectUnverified = $selectAnswers = [];
        $selectAll = ["a.id", "a.nama", "c.last_update"];
        foreach ($areaIntervensiIds as $id) {
            array_push($selectAll, "a.question_$id");
            array_push($selectAll, "b.answer_$id");
            array_push($selectAll, "d.unverified_$id");
            array_push($selectAll, "e.answers_$id");
            array_push($selectQuestion, "COUNT(*) FILTER (WHERE ki.id_area_intervensi = $id) as question_$id");
            array_push($selectAnswer, "COUNT(*) FILTER (WHERE id_area_intervensi = $id) as answer_$id");
            array_push($selectAnswers, "COUNT(*) FILTER (WHERE id_area_intervensi = $id) as answers_$id");
            array_push($selectUnverified, "COUNT(*) FILTER (WHERE id_area_intervensi = $id) as unverified_$id");
        }
        $qSelectAll = implode(",", $selectAll);
        $qSelectQuestion = implode(",", $selectQuestion);
        $qSelectAnswer = implode(",", $selectAnswer);
        $qSelectAnswers = implode(",", $selectAnswers);
        $qSelectUnverified = implode(",", $selectUnverified);

        $where = "";
        if (!is_null($idInstansi)) {
            $where = "AND ks.id_instansi = $idInstansi";
        }

        $tahun = $this->template->tahun;
        $qJoin = "";
        $qIdProvinsi = "i.id_provinsi";
        if (RelInstansiWilayah::isHasAgingForYear($tahun)) {
            $qJoin = "LEFT JOIN jaga.rel_instansi_wilayah riw ON riw.id_instansi = i.id AND $tahun >= riw.start_year AND $tahun <= riw.end_year";
            $qIdProvinsi = "riw.id_provinsi";
        }

        $query = "
            SELECT $qSelectAll
            FROM
            (
                SELECT
                    i.id,
                    i.nama,
                    $qSelectQuestion
                FROM jaga.instansi i
                LEFT JOIN jaga.korsupgah_survey ks ON ks.id_instansi = i.id
                LEFT JOIN jaga.korsupgah_template_sub_indikator ktsi ON ktsi.id_template = ks.id_template
                LEFT JOIN jaga.korsupgah_sub_indikator ksi ON ksi.id = ktsi.id_sub_indikator
                LEFT JOIN jaga.korsupgah_indikator ki ON ki.id = ksi.id_indikator
                $qJoin
                WHERE ks.id_template = :id_template AND ks.deleted_at IS NULL AND i.id_tipe IN (1,2) AND $qIdProvinsi = :id_provinsi $where
                GROUP BY i.id
            ) a
            LEFT JOIN
            (
                SELECT
                    i.id,
                    i.nama,
                    $qSelectAnswer
                FROM 
                (
                    SELECT DISTINCT ks.id as id_survey, ka.id_question as id_answer, ks.id_instansi, ki.id_area_intervensi
                    FROM jaga.korsupgah_survey ks
                    LEFT JOIN jaga.korsupgah_answer ka ON ka.id_survey = ks.id
                    LEFT JOIN jaga.korsupgah_sub_indikator ksi ON ksi.id = ka.id_question
                    LEFT JOIN jaga.korsupgah_indikator ki ON ki.id = ksi.id_indikator
                    WHERE ks.id_template = :id_template AND ks.deleted_at IS NULL AND ka.id IS NOT NULL $where
                ) aa
                LEFT JOIN jaga.instansi i ON aa.id_instansi = i.id
                WHERE id_provinsi = :id_provinsi
                GROUP BY i.id
            ) b ON a.id = b.id
            LEFT JOIN
            (
                SELECT
                    ks.id_instansi as id,
                    MAX(ka.updated_at) as last_update
                FROM jaga.korsupgah_survey ks
                JOIN jaga.korsupgah_answer ka ON ks.id = ka.id_survey
                JOIN jaga.instansi i ON i.id = ks.id_instansi
                WHERE ks.id_template = :id_template AND ks.deleted_at IS NULL AND id_provinsi = :id_provinsi $where
                GROUP BY ks.id_instansi
            ) c ON c.id = b.id
            LEFT JOIN
            (
                SELECT
                    i.id,
                    i.nama,
                    $qSelectUnverified
                FROM 
                (
                    SELECT DISTINCT ks.id as id_survey, ka.id_question as id_answer, ks.id_instansi, ki.id_area_intervensi
                    FROM jaga.korsupgah_survey ks
                    LEFT JOIN jaga.korsupgah_answer ka ON ka.id_survey = ks.id
                    LEFT JOIN jaga.korsupgah_sub_indikator ksi ON ksi.id = ka.id_question
                    LEFT JOIN jaga.korsupgah_indikator ki ON ki.id = ksi.id_indikator
                    WHERE ks.id_template = :id_template AND ks.deleted_at IS NULL AND ka.id IS NOT NULL AND ka.verified_at IS NULL $where
                ) aa
                LEFT JOIN jaga.instansi i ON aa.id_instansi = i.id
                WHERE id_provinsi = :id_provinsi
                GROUP BY i.id
            ) d ON d.id = c.id
            LEFT JOIN (
                SELECT
                    i.id,
                    i.nama,
                    $qSelectAnswers
                FROM 
                (
                    SELECT ks.id as id_survey, ka.id_question as id_answer, ks.id_instansi, ki.id_area_intervensi
                    FROM jaga.korsupgah_survey ks
                    LEFT JOIN jaga.korsupgah_answer ka ON ka.id_survey = ks.id
                    LEFT JOIN jaga.korsupgah_sub_indikator ksi ON ksi.id = ka.id_question
                    LEFT JOIN jaga.korsupgah_indikator ki ON ki.id = ksi.id_indikator
                    WHERE ks.id_template = :id_template AND ks.deleted_at IS NULL AND ka.id IS NOT NULL $where 
                ) aa
                LEFT JOIN jaga.instansi i ON aa.id_instansi = i.id
                WHERE id_provinsi = :id_provinsi
                GROUP BY i.id
            ) e ON a.id = e.id
            ORDER BY c.last_update DESC NULLS LAST;
        ";

        return $this->rawQuery($query, ['id_template' => $idTemplate, 'id_provinsi' => $idProvinsi]);
    }

    private function listAreaIntervensiByIdTemplate($idTemplate)
    {
        return $this->rawQuery("
            SELECT DISTINCT kai.id, kai.nama
            FROM jaga.korsupgah_template_sub_indikator ktsi
            LEFT JOIN jaga.korsupgah_sub_indikator ksi ON ksi.id = ktsi.id_sub_indikator
            LEFT JOIN jaga.korsupgah_indikator ki ON ki.id = ksi.id_indikator
            LEFT JOIN jaga.korsupgah_area_intervensi kai ON kai.id = ki.id_area_intervensi
            WHERE ktsi.id_template = :id_template
            GROUP BY kai.id
            ORDER BY kai.id;
        ", ['id_template' => $idTemplate]);
    }

    private function totalRerataPengisian($data)
    {
        $totalQuestion = $totalAnswer = $totalAnswers = $totalUnverified = 0;
        $count = count($data);
        if ($count > 0) {
            foreach ($data as $datum) {
                foreach ($datum as $key => $value) {
                    if (str_contains($key, 'question_')) {
                        $totalQuestion += $value;
                    } else if (str_contains($key, 'answer_')) {
                        $totalAnswer += $value;
                    } else if (str_contains($key, 'answers_')) {
                        $totalAnswers += $value;
                    } else if (str_contains($key, 'unverified_')) {
                        $totalUnverified += $value;
                    }
                }
            }
            $totalQuestion = $totalQuestion / $count;
            $totalAnswer = $totalAnswer / $count;
            $totalAnswers = $totalAnswers / $count;
            $totalUnverified = $totalUnverified / $count;
            $percentAnswer = $totalQuestion > 0 ? ($totalAnswer / $totalQuestion) * 100 : 0;
            $percentVerified = $totalAnswers > 0 ? 100 - (($totalUnverified / $totalAnswers) * 100) : 0;
        } else {
            $percentAnswer = 0;
            $percentVerified = 0;
        }

        return [
            'percent_answer_total' => $percentAnswer,
            'percent_verified_total' => $percentVerified
        ];
    }

    private function totalRerataPengisianPerArea($data, $areaIntervensiIds)
    {
        $area = [];
        $count = count($data);
        $percent = [];
        if ($count > 0) {
            foreach ($data as $datum) {
                foreach ($datum as $key => $value) {
                    if (str_contains($key, 'question_') || str_contains($key, 'answer_') || str_contains($key, 'answers_') || str_contains($key, 'unverified_')) {
                        if (!array_key_exists($key, $area)) {
                            $area[$key] = 0;
                        }
                        $area[$key] += $value / $count;
                    }
                }
            }
            foreach ($areaIntervensiIds as $id) {
                $percent["percent_answer_$id"] = $area["question_$id"] > 0 ? ($area["answer_$id"] / $area["question_$id"]) * 100 : 0;
                $percent["percent_verified_$id"] = $area["answers_$id"] > 0 ? 100 - (($area["unverified_$id"] / $area["answers_$id"]) * 100) : 0;
            }
        }
        return $percent;
    }

    private function totalRerataPengisianPerInstansi($data)
    {
        $rows = [];
        foreach ($data as $datum) {
            $totalQuestion = $totalAnswer = $totalAnswers = $totalUnverified = 0;
            foreach ($datum as $key => $value) {
                if (str_contains($key, 'question_')) {
                    $totalQuestion += $value;
                } else if (str_contains($key, 'answer_')) {
                    $totalAnswer += $value;
                } else if (str_contains($key, 'answers_')) {
                    $totalAnswers += $value;
                } else if (str_contains($key, 'unverified_')) {
                    $totalUnverified += $value;
                }
            }
            $percentAnswer = $totalQuestion > 0 ? ($totalAnswer / $totalQuestion) * 100 : 0;
            $percentVerified = $totalAnswers > 0 ? 100 - (($totalUnverified / $totalAnswers) * 100) : 0;
            $row = [
                'id' => $datum['id'],
                'nama' => $datum['nama'],
                'percent_answer' => $percentAnswer,
                'percent_verified' => $percentVerified
            ];
            $rows[] = $row;
        }

        return $rows;
    }

    public function downloadVerificationPayloadPerDirectorate()
    {
        // verification payload per directorate
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'id_periode' => ['validators' => ['digit', 'required']],
                'date_from' => ['validators' => ['required']],
                'date_to' => ['validators' => ['required']],
                'id_pengampu' => []
            ]
        );

        // tanggal di antara date_from dan date_to
        $dates = $this->getDatesBetween($params['date_from'], $params['date_to']);
        $date_from = $params['date_from'];

        // verification count per day
        $dailyVerifCountFields = [];
        $dailyVerifCountFieldsPersonal = [];
        foreach ($dates as $i => $date) {
            $dailyVerifCountFields[] =
                "SUM(CASE WHEN date(E.verified_at) = '$date' THEN 1 ELSE 0 END ) as daily_verif_count_$i";
            $dailyVerifCountFieldsPersonal[] =
                "SUM(CASE WHEN date(C.verified_at) = '$date' THEN 1 ELSE 0 END ) as daily_verif_count_$i";
        }
        $dailyVerifCountFields = implode(",", $dailyVerifCountFields);
        $dailyVerifCountFieldsPersonal = implode(",", $dailyVerifCountFieldsPersonal);


        // accumulative answer count per day
        $accAnswerCountFields = [];
        foreach ($dates as $i => $date) {
            $accAnswerCountFields[] =
                "SUM(CASE WHEN date(E.created_at) < DATE '$date' + INTERVAL '1 day' THEN 1 ELSE 0 END ) as acc_answer_count_$i";
        }
        $accAnswerCountFields = implode(",", $accAnswerCountFields);

        // accumulative unverified count per day
        $accUnverifiedCountFields = [];
        foreach ($dates as $i => $date) {
            $accUnverifiedCountFields[] =
                "SUM(CASE WHEN date(E.created_at) < DATE '$date' + INTERVAL '1 day' AND (E.verified_at is null OR E.verified_at >= DATE '$date' + INTERVAL '1 day') THEN 1 ELSE 0 END ) as acc_unverified_count_$i";
        }
        $accUnverifiedCountFields = implode(",", $accUnverifiedCountFields);

        // Accumulative personal performance
        $accPersonalCountFields = [];
        foreach ($dates as $i => $date) {
            $accPersonalCountFields[] =
                "'$date'";
        }
        $accPersonalCountFields = implode(",", $accPersonalCountFields);

        // kondisi apabila ada syarat id_pengampu
        $whereIdPengampu = '';
        $whereIdPengampuPersonal = '';
        if ($params['id_pengampu'] && sizeof($params['id_pengampu']) > 0) {
            $idPengampuList = implode(',', $params['id_pengampu']);
            $whereIdPengampu = "AND C.id_pengampu in ($idPengampuList)";
            $whereIdPengampuPersonal = "AND (D.id_pengampu in ($idPengampuList) OR D.id_pengampu IS NULL)";
        }

        // QUERY
        $id_periode = $params['id_periode'];
        $query = <<<QUERY
        select A.nama as direktorat,
        B.nama as satgas,
        nQuestionPengampu.count as nquestions,
        $dailyVerifCountFields,
        $accAnswerCountFields,
        $accUnverifiedCountFields
        from jaga.korsupgah_pengampu as A inner join jaga.korsupgah_pengampu as B on A.id = B.id_parent
        left join jaga.korsupgah_survey as C on B.id = C.id_pengampu
        inner join jaga.korsupgah_template_sub_indikator as D on D.id_template = C.id_template
        left join jaga.korsupgah_answer as E on C.id = E.id_survey and E.id_question = D.id_sub_indikator
        left join (
            select A.id, count(D.id_sub_indikator) as count from jaga.korsupgah_pengampu as A
            inner join jaga.korsupgah_survey as C on A.id = C.id_pengampu
            inner join jaga.korsupgah_template_sub_indikator as D on D.id_template = C.id_template
            where C.id_periode = $id_periode
            $whereIdPengampu
            group by A.id
        ) nQuestionPengampu on B.id = nQuestionPengampu.id
        where C.id_periode = $id_periode
        and B.id in ($idPengampuList)
        group by A.nama, B.nama, nQuestionPengampu.count
        order by A.nama, B.nama;
QUERY;

        // Query for personal performance
        $queryPersonal = <<<QUERY
        SELECT
            A.nama AS verifikator,
            SUM(CASE WHEN DATE(C.verified_at) IN ($accPersonalCountFields) THEN 1 ELSE 0 END) AS total_performance,
            $dailyVerifCountFieldsPersonal
        FROM 
            jaga.um_user A
        INNER JOIN 
            jaga.users_roles B ON A.um_id = B.user_id
        LEFT JOIN 
            jaga.korsupgah_answer C ON A.um_id = C.verified_by
        LEFT JOIN 
            jaga.korsupgah_survey D ON C.id_survey = D.id
        LEFT JOIN 
            jaga.korsupgah_pengampu E ON E.id = D.id_pengampu
        WHERE 
            B.role_id = (
                SELECT id FROM jaga.roles WHERE name = 'verifikator_korsupgah' LIMIT 1
            )
            AND (D.id_periode = $id_periode OR D.id_periode IS NULL)
            $whereIdPengampuPersonal
        GROUP BY 
            A.um_id, A.nama
        ORDER BY 
            A.nama ASC;
QUERY;

        $rows = $this->rawQuery($query);
        $rowsPersonal = $this->rawQuery($queryPersonal);

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

        // write table headers
        $sheet->setCellValue("A1", 'Kinerja Verifikator');
        $sheet->setCellValue("A2", 'Direktorat');
        $sheet->setCellValue("B2", 'Satgas');
        foreach ($dates as $i => $date) {
            $dateValue = new Datetime($date);
            $sheet->setCellValueByColumnAndRow($i + 3, 2, $dateValue->format('d M Y'));
        }

        // tabel jumlah kinerja verifikasi per hari
        $lastRow = 3;
        foreach ($rows as $i => $row) {
            $sheet->setCellValueByColumnAndRow(1, $lastRow + $i, $row['direktorat']);
            $sheet->setCellValueByColumnAndRow(2, $lastRow + $i, $row['satgas']);
            for ($j = 0; $j < sizeof($dates); $j++) { // iterate by dates

                $sheet->setCellValueByColumnAndRow($j + 3, $lastRow + $i, $row["daily_verif_count_$j"]);
            }
        }
        $lastRow += $i + 2;

        // tabel Beban Verifikasi
        // Table Header
        $sheet->setCellValueByColumnAndRow(1, $lastRow, 'Kinerja Pemda');
        $topTableRow = ++$lastRow;
        $bottomTableRow = ++$lastRow;

        $sheet->mergeCells("A$topTableRow:A$bottomTableRow");
        $sheet->setCellValue("A$topTableRow", "Direktorat");
        $sheet->mergeCells("B$topTableRow:B$bottomTableRow");
        $sheet->setCellValue("B$topTableRow", "Satgas");
        // Lima kolom per tanggal, merged, diisi tanggal
        foreach ($dates as $i => $date) {
            $leftCol = 5 * $i + 3;
            $leftColLetter = Coordinate::stringFromColumnIndex($leftCol);
            $rightCol = $leftCol + 4;
            $rightColLetter = Coordinate::stringFromColumnIndex($rightCol);
            $sheet->mergeCells("{$leftColLetter}{$topTableRow}:{$rightColLetter}{$topTableRow}");
            $dateValue = new Datetime($date);
            $sheet->setCellValue("{$leftColLetter}{$topTableRow}", $dateValue->format('d M Y'));
            // 5 subkolom
            $sheet->setCellValueByColumnAndRow($leftCol, $bottomTableRow, 'Pertanyaan');
            $sheet->setCellValueByColumnAndRow($leftCol + 1, $bottomTableRow, 'Jawaban');
            $sheet->setCellValueByColumnAndRow($leftCol + 2, $bottomTableRow, '%');
            $sheet->setCellValueByColumnAndRow($leftCol + 3, $bottomTableRow, 'Blm Verif');
            $sheet->setCellValueByColumnAndRow($leftCol + 4, $bottomTableRow, '%');
        }

        // Isi tabel
        $lastRow++;
        foreach ($rows as $i => $row) {
            $sheet->setCellValueByColumnAndRow(1, $lastRow + $i, $row['direktorat']);
            $sheet->setCellValueByColumnAndRow(2, $lastRow + $i, $row['satgas']);
            for ($j = 0; $j < sizeof($dates); $j++) { // iterate by dates

                $sheet->setCellValueByColumnAndRow(5 * $j + 3, $lastRow + $i, $row["nquestions"]);
                $sheet->setCellValueByColumnAndRow(5 * $j + 4, $lastRow + $i, $row["acc_answer_count_$j"]);
                $percentage = $row["nquestions"] === 0 ? 0 : ($row["acc_answer_count_$j"] / $row["nquestions"]) * 100;
                $percentage = number_format($percentage, 2);
                $sheet->setCellValueByColumnAndRow(5 * $j + 5, $lastRow + $i, $percentage);
                $sheet->setCellValueByColumnAndRow(5 * $j + 6, $lastRow + $i, $row["acc_unverified_count_$j"]);
                $percentage = $row["acc_answer_count_$j"] === 0 ? 0 : ($row["acc_unverified_count_$j"] / $row["acc_answer_count_$j"]) * 100;
                $percentage = number_format($percentage, 2);
                $sheet->setCellValueByColumnAndRow(5 * $j + 7, $lastRow + $i, $percentage);
            }
        }
        $lastRow += $i + 2;

        // Tabel kinerja per individu
        // Table Header
        $sheet->setCellValueByColumnAndRow(1, $lastRow, 'Kinerja per Individu');
        $topTableRow = ++$lastRow;
        $sheet->setCellValue("A$topTableRow", "Nama");
        $sheet->setCellValue("B$topTableRow", "Total Kinerja");
        foreach ($dates as $i => $date) {
            $dateValue = new Datetime($date);
            $sheet->setCellValueByColumnAndRow($i + 3, $topTableRow, "" . $dateValue->format('d M Y'));
        }

        // Isi tabel
        $lastRow++;
        foreach ($rowsPersonal as $i => $row) {
            $sheet->setCellValueByColumnAndRow(1, $lastRow + $i, $row['verifikator']);
            $sheet->setCellValueByColumnAndRow(2, $lastRow + $i, $row['total_performance']);
            for ($j = 0; $j < sizeof($dates); $j++) { // iterate by dates
                $sheet->setCellValueByColumnAndRow($j + 3, $lastRow + $i, $row["daily_verif_count_$j"]);
            }
        }

        // generate output
        $writer = new Xlsx($spreadsheet);
        $todayLabel = DateHelper::formatTodayIndex();
        $filename = $todayLabel . "-MCP-Verification-Payload" . ".xlsx";
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    private function getDatesBetween($date1, $date2)
    {
        // dates between the two dates
        $start = new DateTime($date1);
        $end = new DateTime($date2);
        $interval = DateInterval::createFromDateString('1 day');
        $period = new DatePeriod($start, $interval, $end->modify('+1 day'));
        $dates = [];
        foreach ($period as $date) {
            $dates[] = $date->format("Y-m-d");
        }
        return $dates;
    }

    public function downloadVerificationProgressPerDirectorate()
    {
        // verification progress per directorate
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'id_periode' => ['validators' => ['digit', 'required']],
                'date_from' => ['validators' => ['required']],
                'date_to' => ['validators' => ['required']],
                'id_pengampu' => []
            ]
        );

        // tanggal di antara date_from dan date_to
        $dates = $this->getDatesBetween($params['date_from'], $params['date_to']);
        $date_from = $params['date_from'];

        // kondisi apabila ada syarat id_pengampu
        $whereIdPengampu = '';
        if ($params['id_pengampu'] && sizeof($params['id_pengampu']) > 0) {
            $idPengampuList = implode(',', $params['id_pengampu']);
            $whereIdPengampu = "AND C.id_pengampu in ($idPengampuList)";
        }

        // questions answered count per day
        $accAnswerCountFields = [];
        foreach ($dates as $i => $date) {
            $accAnswerCountFields[] =
                "(
                    select count(distinct (id_survey, id_question)) from answersPerSurvey
                        where created_at < '$date'::date + INTERVAL '1 day' and id_pengampu = nQuestionPengampu.id
                ) as acc_answer_count_$i";
        }
        $accAnswerCountFields = implode(",", $accAnswerCountFields);

        // verified questions count per day
        $dailyVerifCountFields = [];
        foreach ($dates as $i => $date) {
            $dailyVerifCountFields[] =
                "(
                    select count(distinct (id_survey, id_question)) from answersPerSurvey t1
                        where exists (
                            SELECT 1
                            FROM answersPerSurvey t2
                            WHERE t2.id_survey = t1.id_survey
                              and t2.id_question = t1.id_question
                              and created_at < '$date'::date + INTERVAL '1 day' 
                              and verified_at < '$date'::date + INTERVAL '1 day'
                              and id_pengampu = nQuestionPengampu.id
                        )
                        and not exists (
                            SELECT 1
                            FROM answersPerSurvey t3
                            WHERE t3.id_survey = t1.id_survey
                              and t3.id_question = t1.id_question
                              and created_at < '$date'::date + INTERVAL '1 day' 
                              and (
                                verified_at > '$date'::date + INTERVAL '1 day'
                                or verified_at is NULL
                              )
                              and id_pengampu = nQuestionPengampu.id
                        )
                ) as daily_verif_count_$i"; // CAUTION: verified_at di belakang, lalu ada lagi created_at -> 0
        }
        $dailyVerifCountFields = implode(",", $dailyVerifCountFields);

        // accumulative unverified questions count per day
        $accUnverifiedCountFields = [];
        foreach ($dates as $i => $date) {
            $accUnverifiedCountFields[] =
                "(
                    select count(distinct (id_survey, id_question)) from answersPerSurvey
                        where created_at < '$date'::date + INTERVAL '1 day' 
                            AND ( verified_at is null or verified_at > '$date'::date + INTERVAL '1 day' )
                            AND id_pengampu = nQuestionPengampu.id
                ) as acc_unverified_count_$i";
        }
        $accUnverifiedCountFields = implode(",", $accUnverifiedCountFields);

        // QUERY
        $id_periode = $params['id_periode'];
        $query = <<<QUERY
        WITH answersPerSurvey as (
            SELECT C.id_pengampu, C.id as id_survey, D.id_sub_indikator as id_question, E.id as id_answer, E.status, E.created_at, E.verified_at from jaga.korsupgah_survey as C
                inner join jaga.korsupgah_template_sub_indikator as D on D.id_template = C.id_template
                left join jaga.korsupgah_answer as E on E.id_survey = C.id and E.id_question = D.id_sub_indikator
            where 
                C.id_periode = $id_periode
                AND (
                    E.created_at < '$date'::date + INTERVAL '1 day'
                    or E.verified_at < '$date'::date + INTERVAL '1 day'
                ) 
            $whereIdPengampu
        ),
        nQuestionPengampu as (
            select A.id, count(D.id_sub_indikator) as count from jaga.korsupgah_pengampu as A
            inner join jaga.korsupgah_survey as C on A.id = C.id_pengampu
            inner join jaga.korsupgah_template_sub_indikator as D on D.id_template = C.id_template
            where C.id_periode = $id_periode
            $whereIdPengampu
            group by A.id
        )
        select A.nama as direktorat,
        B.nama as satgas,
        nQuestionPengampu.id as idPengampu,
        nQuestionPengampu.count as nquestions,
        $accAnswerCountFields,
        $dailyVerifCountFields,
        $accUnverifiedCountFields
        from jaga.korsupgah_pengampu as A
        inner join jaga.korsupgah_pengampu as B on A.id = B.id_parent
        left join answersPerSurvey on B.id = answersPerSurvey.id_pengampu
        left join nQuestionPengampu on B.id = nQuestionPengampu.id
        where B.id in ($idPengampuList)
        group by A.nama, B.nama, answersPerSurvey.id_pengampu, nQuestionPengampu.id, nquestions
        order by A.nama, B.nama;
QUERY;
        $rows = $this->rawQuery($query);

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

        // write table headers
        $sheet->setCellValue("A1", 'Progress Verifikasi');
        $sheet->setCellValue("A2", 'Direktorat');
        $sheet->setCellValue("B2", 'Satgas');
        foreach ($dates as $i => $date) {
            $dateValue = new Datetime($date);
            $sheet->setCellValueByColumnAndRow($i + 3, 2, "s.d " . $dateValue->format('d M Y'));
        }

        // tabel jumlah kinerja verifikasi per hari
        $lastRow = 3;
        foreach ($rows as $i => $row) {
            $sheet->setCellValueByColumnAndRow(1, $lastRow + $i, $row['direktorat']);
            $sheet->setCellValueByColumnAndRow(2, $lastRow + $i, $row['satgas']);
            for ($j = 0; $j < sizeof($dates); $j++) { // iterate by dates

                $sheet->setCellValueByColumnAndRow($j + 3, $lastRow + $i, $row["daily_verif_count_$j"]);
            }
        }
        $lastRow += $i + 2;

        // tabel Beban Verifikasi
        // Table Header
        $sheet->setCellValueByColumnAndRow(1, $lastRow, 'Beban Verifikasi');
        $topTableRow = ++$lastRow;
        $bottomTableRow = ++$lastRow;

        $sheet->mergeCells("A$topTableRow:A$bottomTableRow");
        $sheet->setCellValue("A$topTableRow", "Direktorat");
        $sheet->mergeCells("B$topTableRow:B$bottomTableRow");
        $sheet->setCellValue("B$topTableRow", "Satgas");
        // Lima kolom per tanggal, merged, diisi tanggal
        foreach ($dates as $i => $date) {
            $leftCol = 5 * $i + 3;
            $leftColLetter = Coordinate::stringFromColumnIndex($leftCol);
            $rightCol = $leftCol + 4;
            $rightColLetter = Coordinate::stringFromColumnIndex($rightCol);
            $sheet->mergeCells("{$leftColLetter}{$topTableRow}:{$rightColLetter}{$topTableRow}");
            $dateValue = new Datetime($date);
            $sheet->setCellValue("{$leftColLetter}{$topTableRow}", $dateValue->format('d M Y'));
            // 5 subkolom
            $sheet->setCellValueByColumnAndRow($leftCol, $bottomTableRow, 'Pertanyaan');
            $sheet->setCellValueByColumnAndRow($leftCol + 1, $bottomTableRow, 'Jawaban');
            $sheet->setCellValueByColumnAndRow($leftCol + 2, $bottomTableRow, '%');
            $sheet->setCellValueByColumnAndRow($leftCol + 3, $bottomTableRow, 'Blm Verif');
            $sheet->setCellValueByColumnAndRow($leftCol + 4, $bottomTableRow, '%');
        }

        // Isi tabel
        $lastRow++;
        foreach ($rows as $i => $row) {
            $sheet->setCellValueByColumnAndRow(1, $lastRow + $i, $row['direktorat']);
            $sheet->setCellValueByColumnAndRow(2, $lastRow + $i, $row['satgas']);
            for ($j = 0; $j < sizeof($dates); $j++) { // iterate by dates

                $sheet->setCellValueByColumnAndRow(5 * $j + 3, $lastRow + $i, $row["nquestions"]);
                $sheet->setCellValueByColumnAndRow(5 * $j + 4, $lastRow + $i, $row["acc_answer_count_$j"]);
                $percentage = $row["nquestions"] === 0 ? 0 : ($row["acc_answer_count_$j"] / $row["nquestions"]) * 100;
                $percentage = number_format($percentage, 2);
                $sheet->setCellValueByColumnAndRow(5 * $j + 5, $lastRow + $i, $percentage);
                $sheet->setCellValueByColumnAndRow(5 * $j + 6, $lastRow + $i, $row["acc_unverified_count_$j"]);
                $percentage = $row["acc_answer_count_$j"] === 0 ? 0 : ($row["acc_unverified_count_$j"] / $row["acc_answer_count_$j"]) * 100;
                $percentage = number_format($percentage, 2);
                $sheet->setCellValueByColumnAndRow(5 * $j + 7, $lastRow + $i, $percentage);
            }
        }

        // generate output
        $writer = new Xlsx($spreadsheet);
        $todayLabel = DateHelper::formatTodayIndex();
        $filename = $todayLabel . "-MCP-Verification-Progress" . ".xlsx";
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    public function getVerificationProgressPerTaskForce()
    {
        // verification progress per directorate
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'id_periode' => ['validators' => ['digit', 'required']],
                'date_from' => ['validators' => ['required']],
                'date_to' => ['validators' => ['required']],
                'id_pengampu' => []
            ]
        );

        // tanggal di antara date_from dan date_to
        $dates = $this->getDatesBetween($params['date_from'], $params['date_to']);
        $date_from = $params['date_from'];

        // kondisi apabila ada syarat id_pengampu
        $whereIdPengampu = '';
        if ($params['id_pengampu'] && sizeof($params['id_pengampu']) > 0) {
            $idPengampuList = implode(',', $params['id_pengampu']);
            $whereIdPengampu = "AND C.id_pengampu in ($idPengampuList)";
        }

        // questions answered count per day
        $accAnswerCountFields = [];
        foreach ($dates as $i => $date) {
            $accAnswerCountFields[] =
                "(
                    select count(distinct (id_survey, id_question)) from answersPerSurvey
                        where created_at < '$date'::date + INTERVAL '1 day' 
                ) as acc_answer_count_$i";
        }
        $accAnswerCountFields = implode(",", $accAnswerCountFields);

        // accumulated verified questions per day
        $accVerifCountFields = [];
        foreach ($dates as $i => $date) {
            $accVerifCountFields[] =
                "(
                    select count(distinct (id_survey, id_question)) from answersPerSurvey t1
                        where exists (
                            SELECT 1
                            FROM answersPerSurvey t2
                            WHERE t2.id_survey = t1.id_survey
                              and t2.id_question = t1.id_question
                              and created_at < '$date'::date + INTERVAL '1 day' 
                              and verified_at < '$date'::date + INTERVAL '1 day'
                        )
                        and not exists (
                            SELECT 1
                            FROM answersPerSurvey t3
                            WHERE t3.id_survey = t1.id_survey
                              and t3.id_question = t1.id_question
                              and created_at < '$date'::date + INTERVAL '1 day' 
                              and (
                                verified_at > '$date'::date + INTERVAL '1 day'
                                or verified_at is NULL
                            )
                        )
                ) as acc_verif_count_$i"; // CAUTION: verified_at di belakang, lalu ada lagi created_at -> 0
        }
        $accVerifCountFields = implode(",", $accVerifCountFields);

        // accumulative unverified questions count per day
        $accUnverifiedCountFields = [];
        foreach ($dates as $i => $date) {
            $accUnverifiedCountFields[] =
                "(
                    select count(distinct (id_survey, id_question)) from answersPerSurvey
                        where created_at < '$date'::date + INTERVAL '1 day' 
                            AND ( verified_at is null or verified_at > '$date'::date + INTERVAL '1 day' )
                ) as acc_unverified_count_$i";
        }
        $accUnverifiedCountFields = implode(",", $accUnverifiedCountFields);

        // QUERY
        $id_periode = $params['id_periode'];
        $query = <<<QUERY
        WITH answersPerSurvey as (
            SELECT C.id_pengampu, C.id as id_survey, D.id_sub_indikator as id_question, E.id as id_answer, E.status, E.created_at, E.verified_at from jaga.korsupgah_survey as C
                inner join jaga.korsupgah_template_sub_indikator as D on D.id_template = C.id_template
                left join jaga.korsupgah_answer as E on E.id_survey = C.id and E.id_question = D.id_sub_indikator
            where 
                C.id_periode = $id_periode
                AND (
                    E.created_at < '$date'::date + INTERVAL '1 day'
                    or E.verified_at < '$date'::date + INTERVAL '1 day'
                ) 
            $whereIdPengampu
        ),
        nQuestionPengampu as (
            select count(D.id_sub_indikator) as count from jaga.korsupgah_pengampu as A
            inner join jaga.korsupgah_survey as C on A.id = C.id_pengampu
            inner join jaga.korsupgah_template_sub_indikator as D on D.id_template = C.id_template
            where C.id_periode = $id_periode
            $whereIdPengampu
        )
        select 
        (SELECT count as nquestions from nQuestionPengampu),
        $accAnswerCountFields,
        $accVerifCountFields,
        $accUnverifiedCountFields
        from jaga.korsupgah_pengampu as A
        inner join jaga.korsupgah_pengampu as B on A.id = B.id_parent
        inner join answersPerSurvey on B.id = answersPerSurvey.id_pengampu
        group by A.nama, B.nama, answersPerSurvey.id_pengampu, nquestions
        order by A.nama, B.nama;
QUERY;
        $rows = $this->rawQuery($query);
        return new SimpleResponse($rows[0] ?? []);
    }

    public function getVerificationPayloadPerTaskForce()
    {
        // verification progress per directorate
        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'id_periode' => ['validators' => ['digit', 'required']],
                'date_from' => ['validators' => ['required']],
                'date_to' => ['validators' => ['required']],
                'id_pengampu' => []
            ]
        );

        // tanggal di antara date_from dan date_to
        $dates = $this->getDatesBetween($params['date_from'], $params['date_to']);
        $date_from = $params['date_from'];

        // verification count per day
        $dailyVerifCountFields = [];
        foreach ($dates as $i => $date) {
            $dailyVerifCountFields[] =
                "SUM(CASE WHEN date(E.verified_at) = '$date' THEN 1 ELSE 0 END ) as daily_verif_count_$i";
        }
        $dailyVerifCountFields = implode(",", $dailyVerifCountFields);

        // accumulative answer count per day
        $accVerifCountFields = [];
        foreach ($dates as $i => $date) {
            $accVerifCountFields[] =
                "SUM(CASE WHEN date(E.verified_at) < DATE '$date' + INTERVAL '1 day' THEN 1 ELSE 0 END ) as acc_verif_count_$i";
        }
        $accVerifCountFields = implode(",", $accVerifCountFields);

        // accumulative answer count per day
        $accAnswerCountFields = [];
        foreach ($dates as $i => $date) {
            $accAnswerCountFields[] =
                "SUM(CASE WHEN date(E.created_at) < DATE '$date' + INTERVAL '1 day' THEN 1 ELSE 0 END ) as acc_answer_count_$i";
        }
        $accAnswerCountFields = implode(",", $accAnswerCountFields);

        // accumulative unverified count per day
        $accUnverifiedCountFields = [];
        foreach ($dates as $i => $date) {
            $accUnverifiedCountFields[] =
                "SUM(CASE WHEN date(E.created_at) < DATE '$date' + INTERVAL '1 day' AND (E.verified_at is null OR E.verified_at >= DATE '$date' + INTERVAL '1 day') THEN 1 ELSE 0 END ) as acc_unverified_count_$i";
        }
        $accUnverifiedCountFields = implode(",", $accUnverifiedCountFields);

        $whereIdPengampu = '';
        if ($params['id_pengampu'] && sizeof($params['id_pengampu']) > 0) {
            $idPengampuList = implode(',', $params['id_pengampu']);
            $whereIdPengampu = "AND C.id_pengampu in ($idPengampuList)";
        }

        // QUERY
        $id_periode = $params['id_periode'];
        $query = "WITH t_answer_verif as (
                SELECT $dailyVerifCountFields,
                    $accVerifCountFields,
                    $accAnswerCountFields,
                    $accUnverifiedCountFields
                from jaga.korsupgah_survey as C
                inner join jaga.korsupgah_template_sub_indikator as D on D.id_template = C.id_template
                inner join jaga.korsupgah_answer as E on C.id = E.id_survey and E.id_question = D.id_sub_indikator
                where C.id_periode = $id_periode
                    $whereIdPengampu
            ),
            t_questions as (
                select count(D.id_sub_indikator) as nQuestions
                from jaga.korsupgah_survey as C
                inner join jaga.korsupgah_template_sub_indikator as D on D.id_template = C.id_template
                where C.id_periode = $id_periode
                    $whereIdPengampu
            )
            SELECT * from t_questions, t_answer_verif limit 1
        ";
        $rows = $this->rawQuery($query);
        return new SimpleResponse($rows[0]);
    }

    public function listAreaIntervensiAndSubs(): SimpleResponse
    {
        /**
         * retrieve the list of area intervensi, first Indikator List, first Subindikator List,
         * based on year and id_instansi.
         * this function is invoked at the first time the MCP page is loaded.
         **/
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.answer',
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );

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

        $params['id_area'] = array_key_exists('id_area', $params) ? $params['id_area'] : 1;
        $validatedParams = $this->validate($params, [
            'id_template' => ['validators' => ['required', 'digit']],
            'id_instansi' => ['validators' => ['required', 'digit']],
            'id_area' => ['validators' => ['digit']]
        ]);

        $idInstansi = $validatedParams['id_instansi'];
        $idTemplate = $validatedParams['id_template'];
        $areaIntervensiId = $validatedParams['id_area'];

        $instansiQuery = "
            SELECT id, nama FROM jaga.instansi WHERE id = :id_instansi;
        ";
        $instansi = $this->rawQuery($instansiQuery, ['id_instansi' => $idInstansi]);

        $periodeQuery = "
            SELECT id, started_at, ended_at, bpkp_kemdagri_verif_started_at, bpkp_kemdagri_verif_ended_at, korsupgah_verif_started_at, korsupgah_verif_ended_at
            FROM jaga.korsupgah_periode
            WHERE id = (SELECT id_periode FROM jaga.korsupgah_survey WHERE id_template = :id_template LIMIT 1);
        ";
        $periode = $this->rawQuery($periodeQuery, [
            'id_template' => $idTemplate
        ]);

        $query = "
            with t0 as (
                SELECT C.id, C.nama, count(distinct A.id) as count_question
                    from jaga.korsupgah_template_sub_indikator as Z
                    inner join jaga.korsupgah_sub_indikator as A
                        on Z.id_sub_indikator = A.id
                    inner join jaga.korsupgah_indikator as B
                        on A.id_indikator = B.id
                    inner join jaga.korsupgah_area_intervensi as C
                        on B.id_area_intervensi = C.id
                    where Z.id_template = $idTemplate
                    GROUP BY C.id, C.nama
                    order by id
            ),
            t1 as (
                SELECT DISTINCT ON (id_area_intervensi, id_sub_indikator)
                C.id as id_area_intervensi, A.id as id_sub_indikator,
                D.status, D.nilai_verifikasi, D.created_at
                from jaga.korsupgah_template_sub_indikator as Z
                    inner join jaga.korsupgah_sub_indikator as A
                        on Z.id_sub_indikator = A.id
                    inner join jaga.korsupgah_indikator as B
                        on A.id_indikator = B.id
                    inner join jaga.korsupgah_area_intervensi as C
                        on B.id_area_intervensi = C.id
                    left join jaga.korsupgah_answer as D
                        on D.id_question = A.id
                    left join jaga.korsupgah_survey as E
                        on E.id = D.id_survey
                where Z.id_template = :id_template
                    and E.id_instansi = :id_instansi
                    GROUP BY C.id, A.id, D.status, D.nilai_verifikasi, D.created_at
                    order by id_area_intervensi, id_sub_indikator, D.created_at desc
            )
            select A.id, A.nama, A.count_question,
                COALESCE(COUNT(CASE WHEN B.status = 0 THEN 1 END), 0) AS answered,
                COALESCE(COUNT(CASE WHEN B.status = 1 THEN 1 END), 0) AS verified,
                COALESCE(COUNT(CASE WHEN B.status = 2 THEN 1 END), 0) AS qa_verified
            from t0 as A left join t1 as B
                on A.id = B.id_area_intervensi
                group by A.id, A.nama, A.count_question
                order by A.id
            ";

        $bind = [
            'id_template' => $idTemplate,
            'id_instansi' => $idInstansi
        ];

        $areaIntervensiList = $this->rawQuery($query, $bind);
        // take the area intervensi according to the selectedArea Intervensi
        $firstAreaIntervensiId = $areaIntervensiList[$areaIntervensiId - 1]['id'];
        $firstIndikatorList = $this->retrieveIndikatorList($firstAreaIntervensiId, $idInstansi);

        // ambil daftar subindikator dari indikator pertama
        $firstIndikatorId = $firstIndikatorList[0]['id'];
        $firstSubindikatorList = $this->retrieveSubindikatorList($firstIndikatorId, $idInstansi);

        $output = [
            'instansi' => $instansi[0],
            'periode' => $periode[0],
            'areaIntervensiList' => $areaIntervensiList,
            'indikatorList' => $firstIndikatorList,
            'subindikatorList' => $firstSubindikatorList
        ];

        return new SimpleResponse($output);
    }

    public function listIndikatorAndSubs(): SimpleResponse
    {
        /**
         * Get list of indikator based on idArea and the first' subindikators
         */
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.answer',
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );

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

        $idArea = $validatedParams['id_area'];
        $idInstansi = $validatedParams['id_instansi'];
        $indikatorList = $this->retrieveIndikatorList($idArea, $idInstansi);
        $firstIndikatorId = $indikatorList[0]['id'];
        $firstSubindikatorList = $this->retrieveSubindikatorList($firstIndikatorId, $idInstansi);
        $output = [
            'indikatorList' => $indikatorList,
            'subindikatorList' => $firstSubindikatorList
        ];
        return new SimpleResponse($output);
    }

    public function listSubIndikator(): SimpleResponse
    {
        /**
         * Get list of subindikator based on idIndikator
         */
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.answer',
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );
        $params = $this->request->get();
        $validatedParams = $this->validate($params, [
            'id_indikator' => ['validators' => ['required', 'digit']],
            'id_instansi' => ['validators' => ['required', 'digit']]
        ]);
        $idIndikator = $validatedParams['id_indikator'];
        $idInstansi = $validatedParams['id_instansi'];
        $subindikatorList = $this->retrieveSubindikatorList($idIndikator, $idInstansi);
        $output = [
            'subindikatorList' => $subindikatorList
        ];
        return new SimpleResponse($output);
    }

    public function listAnswers(): SimpleResponse
    {
        /**
         * Get list of answers of an id_subindikator & id_instansi
         */
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.answer',
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );
        $params = $this->request->get();
        $validatedParams = $this->validate($params, [
            'id_subindikator' => ['validators' => ['required', 'digit']],
            'id_instansi' => ['validators' => ['required', 'digit']]
        ]);
        $idSubindikator = $validatedParams['id_subindikator'];
        $idInstansi = $validatedParams['id_instansi'];
        $answers = $this->retrieveAnswers($idSubindikator, $idInstansi);

        $attachments = [];
        if (!empty($answers)) {
            $idAnswerList = array_column($answers, 'id');
            $attachments = $this->retrieveAttachments($idAnswerList);
        }
        $output = [
            'answerList' => $answers,
            'attachmentList' => $attachments
        ];
        return new SimpleResponse($output);
    }

    public function singleAnswer(): SimpleResponse
    {
        /**
         * Get a single answer
         */
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.answer',
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );
        $params = $this->request->get();
        $validatedParams = $this->validate($params, [
            'id' => ['validators' => ['required', 'digit']]
        ]);
        $id = $validatedParams['id'];
        $answer = $this->retrieveSingleAnswer($id);

        $output = [
            'answer' => $answer[0]
        ];
        return new SimpleResponse($output);
    }

    private function retrieveIndikatorList($idArea, $idInstansi)
    {
        /**
         * Get list of indikator based on idArea and id_instansi
         */
        $query = "
            with t0 as (
            SELECT B.id, B.nama, B.presentase_bobot, count(distinct A.id) as count_question
                from jaga.korsupgah_sub_indikator as A
                inner join jaga.korsupgah_indikator as B
                    on A.id_indikator = B.id
                inner join jaga.korsupgah_area_intervensi as C
                    on B.id_area_intervensi = C.id
                where C.id = $idArea
                GROUP BY B.id, B.nama
                order by id
        ),
        t1 as (
            SELECT DISTINCT ON (id_indikator, id_sub_indikator)
            B.id as id_indikator, A.id as id_sub_indikator,
            D.status, D.nilai_verifikasi, D.created_at
            from jaga.korsupgah_sub_indikator as A
                inner join jaga.korsupgah_indikator as B
                    on A.id_indikator = B.id
                inner join jaga.korsupgah_area_intervensi as C
                    on B.id_area_intervensi = C.id
                left join jaga.korsupgah_answer as D
                    on D.id_question = A.id
                left join jaga.korsupgah_survey as E
                    on E.id = D.id_survey
            where C.id = $idArea
                and E.id_instansi = $idInstansi
                GROUP BY B.id, A.id, D.status, D.nilai_verifikasi, D.created_at
                order by id_indikator, id_sub_indikator, D.created_at desc
        )
        select A.id, A.nama, A.count_question, A.presentase_bobot,
            COALESCE(COUNT(CASE WHEN B.status = 0 THEN 1 END), 0) AS answered,
            COALESCE(COUNT(CASE WHEN B.status = 1 THEN 1 END), 0) AS verified,
            COALESCE(COUNT(CASE WHEN B.status = 2 THEN 1 END), 0) AS qa_verified
        from t0 as A left join t1 as B
            on A.id = B.id_indikator
            group by A.id, A.nama, A.count_question, A.presentase_bobot
            order by A.id
        ";
        return $this->rawQuery($query);
    }

    private function retrieveSubindikatorList($idIndikator, $idInstansi)
    {
        $query = "
            SELECT
            DISTINCT ON (A.id)
            A.id, A.nama, A.info_nilai, A.info_lampiran, A.presentase_bobot, D.nilai,
            D.verified_at, D.status, D.nilai_verifikasi, D.nilai_verifikasi_qa, F.id as id_survey
            FROM jaga.korsupgah_sub_indikator AS A
                INNER JOIN jaga.korsupgah_indikator AS B
                    ON A.id_indikator = B.id
                INNER JOIN jaga.korsupgah_template_sub_indikator as C
                    on A.id = C.id_sub_indikator
                INNER JOIN jaga.korsupgah_survey as F
                    on F.id_template = C.id_template
                        AND id_instansi = :id_instansi
                LEFT JOIN (
				    SELECT D.*
				    FROM jaga.korsupgah_answer AS D
				    LEFT JOIN jaga.korsupgah_survey AS E
				        ON E.id = D.id_survey
				    WHERE E.id_instansi = :id_instansi
				) AS D ON D.id_question = A.id
            WHERE B.id = :id_indikator
            AND D.deleted_at IS NULL
                ORDER BY A.id, D.created_at DESC
        ";

        return $this->rawQuery($query, [
            'id_indikator' => $idIndikator,
            'id_instansi' => $idInstansi
        ]);
    }

    private function retrieveAnswers($idSubindikator, $idInstansi)
    {
        $query = "
        SELECT A.id, A.id_survey, A.status, A.nilai, A.nilai_verifikasi, A.nilai_verifikasi_qa, A.nilai_verifikasi_pic,
            A.created_at, A.verified_at, A.qa_verified_at, A.pic_verified_at,
            A.keterangan, A.keterangan_verifikasi, A.keterangan_verifikasi_qa,
            B.nama as created_by,
            C.nama as verified_by,
            D.nama as qa_verified_by,
            E.nama as pic_verified_by
            FROM jaga.korsupgah_answer as A
            LEFT JOIN jaga.um_user as B
                on A.created_by = B.um_id
            LEFT JOIN jaga.um_user as C
                on A.verified_by = C.um_id
            LEFT JOIN jaga.um_user as D
                on A.qa_verified_by = D.um_id
            LEFT JOIN jaga.um_user as E
                on A.pic_verified_by = E.um_id
            WHERE id_question = :id_subindikator
                AND id_survey IN
                (SELECT id FROM jaga.korsupgah_survey WHERE id_instansi = :id_instansi
                    AND id_template IN
                (SELECT id_template FROM jaga.korsupgah_template_sub_indikator
                    WHERE id_sub_indikator = :id_subindikator
                ))
                AND deleted_at IS NULL
            ORDER BY created_at DESC";
        return $this->rawQuery($query, [
            'id_subindikator' => $idSubindikator,
            'id_instansi' => $idInstansi
        ]);
    }

    private function retrieveAttachments($idAnswerList)
    {
        $idAnswers = implode(',', $idAnswerList);
        $query = "
            SELECT A.id_answer, B.uuid, B.filename
            FROM jaga.korsupgah_answer_attachment AS A
                INNER JOIN jaga.attachments AS B
                ON A.id_attachment = B.id
            WHERE A.id_answer in ($idAnswers)
            AND A.deleted_at IS NULL
        ";
        return $this->rawQuery($query);
    }

    private function retrieveSingleAnswer($id)
    {
        $query = "
            SELECT A.keterangan, A.keterangan_verifikasi, A.keterangan_verifikasi_qa, 
                B.nama as created_by, C.nama as verified_by,
                D.nama as qa_verified_by, E.nama as pic_verified_by
            FROM jaga.korsupgah_answer as A
            LEFT JOIN jaga.um_user as B
                on A.created_by = B.um_id
            LEFT JOIN jaga.um_user as C
                on A.verified_by = C.um_id
            LEFT JOIN jaga.um_user as D
                on A.qa_verified_by = D.um_id
            LEFT JOIN jaga.um_user as E
                on A.pic_verified_by = E.um_id
            WHERE id = :id
        ";
        return $this->rawQuery($query, [
            'id' => $id
        ]);
    }
    public function listInstansi()
    {
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.answer',
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );

        $instansiQuery = "
            SELECT id, nama, id_tipe FROM jaga.instansi WHERE id_tipe IN (1,2) AND deleted_at IS NULL ORDER BY nama;
        ";

        $instansiList = $this->rawQuery($instansiQuery);
        $output = [
            'result' => $instansiList,
        ];

        return new SimpleResponse($output);
    }

    public function listTemplate()
    {
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.answer',
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );

        $templateQuery = "
            SELECT id, nama FROM jaga.korsupgah_template WHERE deleted_at IS NULL ORDER BY view_order ASC;
        ";

        $templateQuery = $this->rawQuery($templateQuery);
        $output = [
            'result' => $templateQuery,
        ];

        return new SimpleResponse($output);
    }

    public function betaIdSurvey()
    {
        // beta function to return the id_instansi & id_survey of the current user
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.answer',
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );

        $getParams = $this->request->get();
        $params = $this->validate(
            $getParams,
            [
                'id_periode' => ['validators' => ['digit']]
            ]
        );

        $idPeriode = $params['id_periode'];
        $idInstansi = $this->shared->user->instansi->id;
        $query = "SELECT id from jaga.korsupgah_survey where
            id_periode = $idPeriode and id_instansi = $idInstansi limit 1";
        $result = $this->rawQuery($query);
        $output = [
            'id_instansi' => $idInstansi,
            'id_survey' => $result[0]['id']
        ];
        return new SimpleResponse($output);
    }

    public function qaOverview()
    {
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(['admin.view', 'korsupgah.admin', 'korsupgah.verify', 'korwil.verifikator_kemdagri_bpkp']);
        $params = $this->request->get();
        $params['id_template'] = !empty($params['id_template']) ? $params['id_template'] : 11;

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

        $andWhere = "";
        $bind = [];

        if (!is_null($validatedParams['id_provinsi'])) {
            $andWhere .= " AND i.id_provinsi = :id_provinsi";
            $bind['id_provinsi'] = $validatedParams['id_provinsi'];
        }

        $this->template = KorsupgahTemplate::findFirst($validatedParams['id_template']);
        if (!$this->template) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Template tidak ditemukan");
        } else {
            $bind['id_template'] = $validatedParams['id_template'];
        }

        $query = "
            with t1 as (
                select
                    distinct on (kai.id, kai.nama, i.nama, ks.id_instansi, ktsi.id)
                    kai.id as id_area_intervensi, kai.nama as nama_area_intervensi, i.nama as nama_instansi, ks.id_instansi, ktsi.id, ka.status, ka.created_at
                    from jaga.korsupgah_area_intervensi as kai
                    inner join jaga.korsupgah_indikator as ki
                        on kai.id = ki.id_area_intervensi
                    inner join jaga.korsupgah_sub_indikator as ksi
                        on ki.id = ksi.id_indikator
                    inner join jaga.korsupgah_template_sub_indikator as ktsi
                        on ksi.id = ktsi.id_sub_indikator
                    inner join jaga.korsupgah_survey as ks
                        on ks.id_template = ktsi.id_template
                    inner join jaga.instansi as i
                        on i.id = ks.id_instansi
                    left join jaga.korsupgah_answer as ka
                        on  ka.id_survey = ks.id
                        and ka.id_question = ksi.id
                    where ktsi.id_template = :id_template
                        $andWhere
                order by kai.id, kai.nama, i.nama, ks.id_instansi, ktsi.id, ka.created_at desc NULLS LAST
            ) select nama_area_intervensi, nama_instansi, id_area_intervensi,
                CASE
                    WHEN SUM(CASE WHEN status = 1 THEN 1 ELSE 0 END) > 0 THEN 'Belum'
                    ELSE 'Sudah'
                END AS Selesai
                from t1 
                group by nama_area_intervensi, nama_instansi, id_area_intervensi
                order by nama_area_intervensi, id_area_intervensi;
        ";

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

        $spreadsheet = new Spreadsheet();
        $firstSheet = true;
        $currentSheet = null;
        $rowNum = 2; // Start from row 2 since row 1 is header
        $lastAreaIntervensi = '';

        foreach ($result as $i => $row) {
            if ($lastAreaIntervensi != $row['nama_area_intervensi']) {
                $rowNum = 2;

                if ($firstSheet) {
                    $currentSheet = $spreadsheet->getActiveSheet();
                    $currentSheet->setTitle($row['nama_area_intervensi']);
                    $firstSheet = false;
                } else {
                    $currentSheet = $spreadsheet->createSheet();
                    $currentSheet->setTitle($row['nama_area_intervensi']);
                }

                $currentSheet->setCellValue("A1", 'No');
                $currentSheet->setCellValue("B1", 'Instansi');
                $currentSheet->setCellValue("C1", 'Status Pengisian');

                $currentSheet->getColumnDimension('B')->setAutoSize(true);
                $currentSheet->getColumnDimension('C')->setAutoSize(true);
                $currentSheet->getStyle('A1:C1')->getFont()->setBold(true);

                $lastAreaIntervensi = $row['nama_area_intervensi'];
            }

            $currentSheet->setCellValue("A$rowNum", ($rowNum - 1));
            $currentSheet->setCellValue("B$rowNum", $row['nama_instansi']);
            $currentSheet->setCellValue("C$rowNum", $row['selesai']);

            $rowNum++;
        }

        foreach ($spreadsheet->getAllSheets() as $sheet) {
            $lastRow = $sheet->getHighestRow();
            $styleArray = [
                'borders' => [
                    'allBorders' => [
                        'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                        'color' => ['argb' => 'FF000000'],
                    ],
                ],
            ];
            $sheet->getStyle('A1:C' . $lastRow)->applyFromArray($styleArray);
        }

        $spreadsheet->setActiveSheetIndex(0);

        $writer = new Xlsx($spreadsheet);
        $filename = "Status Pengisian QA MCP - " . DateHelper::formatTodayIndex() . ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

    public function uploadAttachmentPenaltyScore()
    {
        $this->checkIsAuthenticated();
        $post = $this->request->getPost();
        // $uploadedFile = $this->request->getUploadedFiles();

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

        $dataTemplate = KorsupgahTemplate::findFirst($validatedRequest['id_template']);

        /** @var MinioService $minioService */
        $minioService = $this->minio;
        $subFolder = KorsupgahMcpPenaltyScoreAttachment::getSubfolder($validatedRequest['id_template'], $dataTemplate->tahun);
        $service = new AttachmentService($this->shared, $minioService);
        if ($minioService->isSecondaryS3Available()) {
            $attachments = $service->uploadUsingSecondaryS3(
                getenv("FILESYSTEM_CLOUD"),
                [
                    'allowed-types' => $this->config["allowed_mime_types"],
                ],
                [
                    'tipe' => Attachments::TIPE_PRIVATE
                ],
                $this->request,
                $subFolder,
                DIRECTORY_SEPARATOR
            );
        } else {
            $attachments = $service->upload(
                getenv("FILESYSTEM_CLOUD"),
                [
                    'allowed-types' => $this->config["allowed_mime_types"],
                ],
                [
                    'tipe' => Attachments::TIPE_PRIVATE
                ],
                $this->request,
                $subFolder,
                DIRECTORY_SEPARATOR
            );
        }

        return new CollectionPaginatedResponse($attachments);
    }

    private function getPenalty($idSurvey)
    {

        $query = "
            SELECT
                COALESCE(kmps.score, 0) as score
            FROM
                jaga.korsupgah_mcp_penalty_score as kmps
                inner join jaga.korsupgah_survey as ks
                    on ks.id_template = kmps.id_template
                        and ks.id_instansi = kmps.id_instansi
            WHERE
                ks.id = :id_survey";

        $penalty = $this->rawQuery($query, [
            'id_survey' => $idSurvey
        ])[0]['score'] ?? 0;
        return $penalty;
    }

    public function neoList()
    {
        $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");
        }

        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.answer',
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );

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

        if (intval($params['is_qa']) === 1) {
            $this->checkOneOfScopes([
                'korsupgah.verify',
                'korsupgah.admin',
                'korwil.monitoring_kemdagri',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.qa'
            ]);
        }

        $validatedParams = $this->validate($params, [
            'id' => ['validators' => ['required', 'digit']],
            'id_instansi' => ['validators' => ['required', 'digit']],
            'year' => ['validators' => ['required', 'digit']],
            'is_instansi_change' => ['digit'] // 1 when user changes instansi
        ]);

        $id = $validatedParams['id'];
        $idInstansi = $this->hasScopes(['korsupgah.answer']) && $this->shared->user->id_instansi
            ? $this->shared->user->id_instansi
            : $validatedParams['id_instansi'];
        $year = $validatedParams['year'];
        $is_instansi_change = isset($validatedParams['is_instansi_change']) &&
            intval($validatedParams['is_instansi_change']) === 1;

        $instansiQuery = "
            SELECT id, nama, id_tipe FROM jaga.instansi WHERE id = :id_instansi;
        ";
        $instansi = $this->rawQuery($instansiQuery, ['id_instansi' => $idInstansi]);
        if (empty($instansi)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan");
        }

        $params = [
            'id' => $id,
            'instansi_id' => $idInstansi,
            'year' => $year
        ];

        $url = '/api/' . ($is_instansi_change ? 'neo-list-on-instansi-change' : 'neo-list');

        try {
            $mcpService = new McpService($this->logger);
            $result = $mcpService->get($url, $params, null, true);
            if ($result->success && property_exists($result->data, "areaList")) {
                $result->data->instansi = $instansi[0];
            }

            $response = $result->success ? new SimpleResponse($result->data) : new SimpleResponse(null);
            $response->setStatus($result->success);
            $response->setMessage($result->message);

            if (property_exists($result, 'status_code') && $result->status_code !== 200) {
                $response->setStatusCode($result->status_code);
            }

            return $response;
        } catch (Exception $e) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, $e->getMessage());
        }
    }

    public function neoPostAnswer()
    {
        $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");
        }
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.answer',
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );
        $postParams = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $postParams,
            [
                'id' => ['validators' => ['required', 'digit']],
                'year' => ['validators' => ['required', 'digit']],
                'instansi_id' => ['validators' => ['required', 'digit']],
                'attachments' => ['validators' => ['required']],
            ]
        );

        $id = $params['id'];
        $year = $params['year'] ?? '2025';
        $instansi_id = $params['instansi_id'];

        $attachments = [];
        if (!empty($params['attachments'])) {
            foreach ($params['attachments'] as $attachment) {
                if (!isset($attachment['batch']) || !is_array($attachment['batch'])) {
                    $attachment['batch'] = [];
                }

                $attachment['batch']['at'] = date('Y-m-d H:i:s');
                $attachment['batch']['by']['id'] = $this->shared->user->um_id;
                $attachment['batch']['by']['name'] = $this->shared->user->nama;

                $attachments[] = $attachment;
            }
        } else {
            $attachments = [];
        }

        // get from MCP 2025 Service
        $payload = [
            'id' => $id,
            'year' => $year,
            'instansi_id' => $instansi_id,
            'attachments' => $attachments
        ];

        try {
            $mcpService = new McpService($this->logger);
            return $mcpService->postResponse('/api/neo-answer', $payload, [], null, true);
        } catch (Exception $e) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, $e->getMessage());
        }
    }

    public function neoUpdateAnswer()
    {
        $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");
        }
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.answer',
                'korsupgah.admin'
            ]
        );
        $postParams = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $postParams,
            [
                'id' => ['validators' => ['required', 'digit']],
                'instansi_id' => ['validators' => ['required', 'digit']],
                'year' => ['validators' => ['required', 'digit']],
                'attachments' => ['validators' => ['required']],
            ]
        );

        if ($this->checkOneOfScopes(['korsupgah.answer']) && $this->shared->user->id_instansi != $params['instansi_id']) {
            throw new CustomErrorException(
                BaseResponse::INVALID_REQUEST,
                'Anda tidak memiliki akses untuk mengupdate jawaban ini.'
            );
        }

        $attachments = [];
        if (!empty($params['attachments'])) {
            foreach ($params['attachments'] as $attachment) {
                if (!isset($attachment['batch']) || !is_array($attachment['batch'])) {
                    $attachment['batch'] = [];
                }

                $attachment['batch']['at'] = date('Y-m-d H:i:s');
                $attachment['batch']['by']['id'] = $this->shared->user->um_id;
                $attachment['batch']['by']['name'] = $this->shared->user->nama;

                $attachments[] = $attachment;
            }
        } else {
            $attachments = [];
        }

        // get from MCP 2025 Service
        $payload = [
            'id' => $params['id'],
            'year' => $params['year'],
            'attachments' => $attachments,
            'instansi_id' => $params['instansi_id']
        ];

        try {
            $mcpService = new McpService();
            return $mcpService->postResponse('/api/neo-answer/update', $payload);
        } catch (Exception $e) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, $e->getMessage());
        }
    }

    public function neoListIndikatorForm()
    {
        $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");
        }
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.answer',
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );

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

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

        $id = $validatedParams['id'];
        $id_instansi = ($this->hasScopes(['korsupgah.answer']) && $this->shared->user->id_instansi) ? $this->shared->user->id_instansi : $validatedParams['id_instansi'];
        $id_tipe = $validatedParams['id_tipe'];
        $year = $params['year'] ?? '2025';


        $queryParams = [
            'id' => $id,
            'instansi_id' => $id_instansi,
            'year' => $year,
            'id_tipe' => $id_tipe
        ];

        try {
            $mcpService = new McpService($this->logger);
            $result = $mcpService->get('/api/neo-list/indikator', $queryParams, null, true);

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

    public function neoPostRatioAnswer()
    {
        $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");
        }
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.answer',
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );
        $postParams = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $postParams,
            [
                'instansi_id' => ['validators' => ['required', 'digit']],
                'id' => ['validators' => ['required', 'digit']],
                'year' => ['validators' => ['required', 'digit']],
                'ratio_answers' => ['validators' => ['required']],
            ]
        );

        $year = $params['year'] ?? '2025';
        $answerId = $params['id'];
        $instansi_id = $params['instansi_id'];
        $params['ratio_answers']['at'] = date('Y-m-d H:i:s');
        $params['ratio_answers']['by'] = $this->shared->user->um_id;

        // get from MCP 2025 Service
        $payload = [
            'id' => $answerId,
            'year' => $year,
            'instansi_id' => $instansi_id,
            'ratio_answers' => $params['ratio_answers'],
        ];

        try {
            $mcpService = new McpService($this->logger);
            $result = $mcpService->post('/api/neo-answer/ratio', $payload, [], null, true);

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

    public function neoLockAnswer()
    {
        $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");
        }
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );
        $postParams = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $postParams,
            [
                'id' => ['validators' => ['required', 'digit']],
                'year' => ['validators' => ['required', 'digit']],
                'done' => ['validators' => ['required']],
                'instansi_id' => ['validators' => ['required', 'digit']]
            ]
        );

        // get from MCP 2025 Service
        $payload = [
            'id' => $params['id'],
            'year' => $params['year'],
            'done' => $params['done'],
            'instansi_id' => $params['instansi_id']
        ];

        try {
            $mcpService = new McpService($this->logger);
            $result = $mcpService->post('/api/neo-answer/lock', $payload, [], null, true);

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

    public function neoLockAttachment()
    {
        $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");
        }
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.verify',
                'korsupgah.admin',
                'korsupgah.monitoring',
                'korsup.qa',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.pic'
            ]
        );
        $postParams = $this->request->getJsonRawBody(true);

        $params = $this->validate(
            $postParams,
            [
                'id' => ['validators' => ['required', 'digit']],
                'year' => ['validators' => ['required', 'digit']],
                'is_final' => ['validators' => ['required']],
                'attachment_uid' => ['validators' => ['required']],
                'instansi_id' => ['validators' => ['required', 'digit']]
            ]
        );

        // get from MCP 2025 Service
        $payload = [
            'id' => $params['id'],
            'year' => $params['year'],
            'is_final' => $params['is_final'],
            'attachment_uid' => $params['attachment_uid'],
            'instansi_id' => $params['instansi_id']
        ];

        try {
            $mcpService = new McpService($this->logger);
            $result = $mcpService->post('/api/neo-answer/attachment/lock', $payload, [], null, true);

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

    public function postDocumentNote()
    {
        $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");
        }
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.verify',
                'korsupgah.admin',
                'korwil.monitoring_kemdagri',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.qa'
            ]
        );
        $postParams = $this->request->getJsonRawBody(true);
        $postParams['year'] = $postParams['year'] ?? '2025';
        $params = $this->validate(
            $postParams,
            [
                'id' => ['validators' => ['required', 'digit']],
                'year' => ['validators' => ['required']],
                'attachment_uid' => ['validators' => ['required']],
                'batch_id' => ['validators' => ['required', 'digit']],
                'note' => ['validators' => ['required']],
                'approved' => ['validators' => []],
                'completed' => ['validators' => []],
                'instansi_id' => ['validators' => ['required', 'digit']],
            ]
        );
        $params['by']['id'] = $this->shared->user->um_id;
        $params['by']['name'] = $this->shared->user->nama;
        $params['at'] = date('Y-m-d H:i:s');

        // get from MCP 2025 Service
        try {
            $mcpService = new McpService($this->logger);
            $response = $mcpService->postResponse('/api/neo-answer/note', $params, [], 10, true);
            return $response;
        } catch (Exception $e) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, $e->getMessage());
        }
    }

    public function neoPostVerif()
    {
        $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");
        }
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(
            [
                'korsupgah.verify',
                'korsupgah.admin',
                'korwil.monitoring_kemdagri',
                'korwil.verifikator_kemdagri_bpkp',
                'korsup.qa'
            ]
        );
        $postParams = $this->request->getJsonRawBody(true);
        $postParams['year'] = $postParams['year'] ?? '2025';
        $params = $this->validate(
            $postParams,
            [
                'instansi_id' => ['validators' => ['required', 'digit']],
                'id' => ['validators' => ['required', 'digit']],
                'year' => ['validators' => ['required', 'digit']],
                'verify' => ['validators' => ['required']]
            ]
        );
        $params['verify']['by']['id'] = $this->shared->user->um_id;
        $params['verify']['by']['name'] = $this->shared->user->nama;
        $params['verify']['at'] = date('Y-m-d H:i:s');

        // get from MCP 2025 Service
        try {
            $mcpService = new McpService($this->logger);
            $result = $mcpService->post('/api/neo-answer/verify', $params, [], null, true);

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

    // di bawah ini ngga direfactor, tapi ganti question_id jadi year langsung
    public function neoAttachmentUpload()
    {
        $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");
        }
        $this->checkIsAuthenticated();

        // check whether the present time is within the answer period
        try {
            $mcpService = new McpService($this->logger);
            $isWithinAnswerPeriod = $mcpService->get('/api/neo-answer', [], 10, false);
        } catch (Exception $e) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, $e->getMessage());
        }

        if ($isWithinAnswerPeriod && !$isWithinAnswerPeriod->success) {
            throw new CustomErrorException(BaseResponse::INVALID_ACTION, $isWithinAnswerPeriod->message);
        }

        $post = $this->request->getPost();
        $params = $this->validate($post, [
            'year' => ['validators' => ['required', 'digit']],
            'instansi_id' => ['validators' => ['required', 'digit']]
        ]);

        /** @var MinioService $minioService */
        $minioService = $this->minio;
        $subFolder = McpAnswerAttachment::getSubfolder($params['year'], $params['instansi_id']);
        $service = new McpAttachmentService($this->shared, $minioService);
        if ($minioService->isSecondaryS3Available()) {
            $attachments = $service->uploadUsingSecondaryS3(
                getenv("FILESYSTEM_CLOUD"),
                [
                    'allowed-types' => $this->config["allowed_mime_types"],
                ],
                [
                    'tipe' => McpAttachments::TIPE_PRIVATE
                ],
                $this->request,
                $subFolder,
                DIRECTORY_SEPARATOR
            );
        } else {
            $attachments = $service->upload(
                getenv("FILESYSTEM_CLOUD"),
                [
                    'allowed-types' => $this->config["allowed_mime_types"],
                ],
                [
                    'tipe' => McpAttachments::TIPE_PRIVATE
                ],
                $this->request,
                $subFolder,
                DIRECTORY_SEPARATOR
            );
        }

        return new CollectionPaginatedResponse($attachments);
    }

    public function neoAttachmentInlineDownload($uuid)
    {
        $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");
        }
        if ($uuid == 'undefined')
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Parameter UUID tidak boleh "undefined"!');

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

        $params['is_inline'] = $params['is_inline'] == null ? false : filter_var($params['is_inline'], FILTER_VALIDATE_BOOLEAN);

        $attachment = McpAttachments::findFirst(
            [
                "uuid = :uuid:",
                "bind" => [
                    "uuid" => $uuid
                ]
            ]
        );

        if ($attachment) {
            if (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::MINIO) {
                /** @var MinioService $minioService */

                $minioService = $this->minio;
                $key = DIRECTORY_SEPARATOR . DIRECTORY_SEPARATOR . $uuid;
                $url = $minioService->base_get_public_url('users_profpic' . DIRECTORY_SEPARATOR . $key, $attachment->filename, !$params['is_inline']);
                if (!MinioService::checkFileExist($url)) {
                    $subFolder = McpAnswerAttachment::getSubfolder($params['year'], $params['instansi_id']);
                    $key = $subFolder . DIRECTORY_SEPARATOR . $uuid;
                    if ($minioService->isSecondaryS3Available()) {
                        $url = $minioService->basePrivateUrlUsingSecondaryS3($key, $attachment->filename, !$params['is_inline']);
                    } else {
                        $url = $minioService->base_get_private_url($key, $attachment->filename, !$params['is_inline']);
                    }
                }
                $response = new \Phalcon\Http\Response();
                return $response->redirect($url, true, 302);
            } elseif (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::LOCAL) {
                $subFolder = getenv("USER_IMAGE_FOLDER");
                $filename = $subFolder . DIRECTORY_SEPARATOR . $uuid;
                if (file_exists($filename)) {
                    return MediaHelper::fileResponse($filename, null, true, $params['is_inline']);
                } else {
                    $subFolder = getenv("PRIVATE_FOLDER") . McpAnswerAttachment::getSubfolder($params['year'], $params['instansi_id']);
                    $filename = $subFolder . DIRECTORY_SEPARATOR . $uuid;
                    if (file_exists($filename)) {
                        return MediaHelper::fileResponse($filename, null, true, $params['is_inline']);
                    } else {
                        throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "File Not Found");
                    }
                }
            }
        } else {
            return new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Attachment Not Found");
        }
    }
}