<?php

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use Phalcon\Mvc\Model\Transaction\Manager as TransactionManager;
use Phalcon\Di;

class SpiMonitoringController extends BaseController
{
    protected $q3_rtl_update_start_date;
    protected $q3_rtl_update_end_date;

    protected $q4_rtl_update_start_date;
    protected $q4_rtl_update_end_date;

    public function onConstruct()
    {
        $this->q3_rtl_update_start_date = '2025-07-01';
        $this->q3_rtl_update_end_date = '2025-08-30';
        
        $this->q4_rtl_update_start_date = '2025-08-31';
        $this->q4_rtl_update_end_date = '2025-10-31';
    }

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

        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data', 'spi.view_pemda_data']);
        $getParams = $this->request->get();
        $getParams['tahun'] = array_key_exists('tahun', $getParams) ? $getParams['tahun'] : null;
        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : null;
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['digit']],
                'id_instansi' => ['validators' => ['digit']],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']]
            ]
        );

        $service = new SpiMonitoringService($this);
        return $service->tableForm($params);
    }

    public function tableFormAllInstansi() {

        $this->checkIsAuthenticated();

        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data', 'spi.view_pemda_data']);

        $getParams = $this->request->get();
        $getParams['tahun'] = array_key_exists('tahun', $getParams) ? $getParams['tahun'] : null;
        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : null;
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['digit']],
                'id_instansi' => ['validators' => ['digit']],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']]
            ]
        );

        $service = new SpiMonitoringService($this);
        return $service->tableFormAllInstansi($params);
    }

    public function checkIsRtlVerificator()
    {
        $instansiKpk = Instansi::findFirst([
            'conditions' => 'LOWER(nama) = LOWER(:nama:) AND deleted_at IS NULL',
            'bind' => [
                'nama' => 'komisi pemberantasan korupsi'
            ]
        ]);

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

        if ($this->getIdInstansi() !== $instansiKpk->id) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User bukan verifikator');
        }
    }

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

        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.observer_rtl']);

        $this->checkIsRtlVerificator();

        date_default_timezone_set('Asia/Jakarta');

        $getParams = $this->request->get();
        $getParams['tahun'] = array_key_exists('tahun', $getParams) ? $getParams['tahun'] : null;
        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : 0;
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['required']],
                'id_instansi' => ['validators' => ['digit']],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']]
            ]
        );

        $where = "rtl.tahun = :tahun AND rtl.deleted_at IS NULL";
        $binds = ['tahun' => $params['tahun']];

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

            $where = $where . ' AND i.id = :id_instansi';
            $binds['id_instansi'] = $params['id_instansi'];
        }

        $verifiedRtls = $this->rawQuery("
            SELECT
                rv.id as verifikasi_id,
                i.id as instansi_id
            FROM
                jaga.spi_rtl_verifikasi rv
            JOIN
                jaga.spi_rtl rtl ON rtl.id = rv.id_rtl
            JOIN
                jaga.instansi i ON i.id = rtl.id_instansi
            WHERE
                rv.deleted_at IS NULL AND
                id_rtl IN (
                    SELECT
                        rtl.id
                    FROM
                        jaga.spi_rtl rtl
                    JOIN
                        jaga.instansi i ON i.id = rtl.id_instansi
                    WHERE
                    " . $where . "
                )
        ", $binds);


        $result = $this->rawQuery("
            SELECT
                DISTINCT ON (rtl_subquery.id_instansi)
                rtl_subquery.id,
                rtl_subquery.id_instansi,
                rtl_subquery.nama_instansi,
                rtl_subquery.tahun,
                rtl_subquery.is_all_updated_q3,
                rtl_subquery.is_all_updated_q4,
                rtl_subquery.claim_progress_renaksi,
                rtl_subquery.avg_claim_progress_renaksi,
                rtl_subquery.verifikasi_progress,
                rtl_subquery.avg_verifikasi_progress,
                rtl_subquery.tgl_update_terakhir
            FROM (
                SELECT
                    rtl.id,
                    i.id AS id_instansi,
                    i.nama AS nama_instansi,
                    rtl.tahun,
                    (
                        SELECT
                            NOT EXISTS (
                                SELECT 1 FROM jaga.spi_rtl rtl WHERE rtl.is_updated_q3 = FALSE AND rtl.id_instansi = i.id AND rtl.deleted_at IS NULL AND rtl.tahun = '" . $params['tahun'] . "'
                            ) 
                    ) AS is_all_updated_q3,
                    (
                        SELECT
                            NOT EXISTS (
                                SELECT 1 FROM jaga.spi_rtl rtl WHERE rtl.is_updated_q4 = FALSE AND rtl.id_instansi = i.id AND rtl.deleted_at IS NULL AND rtl.tahun = '" . $params['tahun'] . "'
                            ) 
                    ) AS is_all_updated_q4,
                    rtl.capaian AS claim_progress_renaksi,
                    avg_data.avg_claim_progress_renaksi,
                    rtl_verifikasi.progress AS verifikasi_progress,
                    avg_data.avg_verifikasi_progress,
                    (
                        SELECT
                            srtl.updated_at
                        FROM
                            jaga.spi_rtl srtl
                        WHERE
                            srtl.id_instansi = rtl.id_instansi
                            AND srtl.deleted_at IS NULL
                            AND srtl.updated_at IS NOT NULL
                            AND srtl.tahun = '" . $params['tahun'] . "'
                        ORDER BY
                            srtl.updated_at DESC
                        LIMIT
                            1
                    ) AS tgl_update_terakhir
                FROM
                    jaga.spi_rtl rtl
                LEFT JOIN
                    jaga.spi_rtl_verifikasi rtl_verifikasi ON rtl_verifikasi.id_rtl = rtl.id
                    AND rtl_verifikasi.deleted_at IS NULL
                LEFT JOIN
                    instansi i ON i.id = rtl.id_instansi
                LEFT JOIN (
                    SELECT 
                        i.id AS id_instansi,
                        AVG(COALESCE(REPLACE(rtl.capaian, '%', '')::FLOAT, 0)) AS avg_claim_progress_renaksi,
                        AVG(COALESCE(rtl_verifikasi.progress, 0)) AS avg_verifikasi_progress
                    FROM 
                        jaga.spi_rtl rtl
                    LEFT JOIN 
                        jaga.spi_rtl_verifikasi rtl_verifikasi ON rtl_verifikasi.id_rtl = rtl.id
                        AND rtl_verifikasi.deleted_at IS NULL
                    LEFT JOIN 
                        instansi i ON i.id = rtl.id_instansi
                    WHERE 
                        " . $where . "
                    GROUP BY 
                        i.id
                ) AS avg_data ON avg_data.id_instansi = i.id
                WHERE
                    " . $where . "
                ORDER BY
                    rtl.id DESC
            ) AS rtl_subquery
        ", $binds);

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

        foreach($result as &$item) {
            $item['is_form_rtl_verified'] = FALSE;
            
            $verifikasi = array_filter($verifiedRtls, function ($verifiedRtl) use($item) {
                return $verifiedRtl['instansi_id'] === $item['id_instansi'];
            });
            
            if (!empty($verifikasi)) {
                $item['is_form_rtl_verified'] = TRUE;
            }
        }

        unset($item);

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

        $excel->setCellValue("A1", "No");
        $excel->setCellValue("B1", "Tahun");
        $excel->setCellValue("C1", "Instansi");
        $excel->setCellValue("D1", "Matriks Tindak Lanjut diterima oleh verifikator");
        $excel->setCellValue("E1", "Triwulan 3");
        $excel->setCellValue("F1", "Triwulan 4");
        // $excel->setCellValue("G1", "Laporan TL Q4");
        $excel->setCellValue("G1", "Rata-rata Klaim Progress Renaksi (%)");
        $excel->setCellValue("H1", "Rata-rata Verifikasi Progress (%)");
        $excel->setCellValue("I1", "Tanggal Update Terakhir");

        $headerStyle = [
            'fill' => [
                'fillType' => Fill::FILL_SOLID,
                'startColor' => [
                    'rgb' => 'C6E0B4'
                ]
            ],
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ];

        $excel->getStyle('A1:I1')->applyFromArray($headerStyle);
        $rowIndex = 1;
        $no = 1;

        foreach($result as $row) {
            $rowIndex++;

            $excel->setCellValue("A$rowIndex", $no);
            $excel->setCellValue("B$rowIndex", $row['tahun']);
            $excel->setCellValue("C$rowIndex", $row['nama_instansi']);
            $excel->setCellValue("D$rowIndex", $row['is_form_rtl_verified']);
            $excel->setCellValue("E$rowIndex", $row['is_all_updated_q3']);
            $excel->setCellValue("F$rowIndex", $row['is_all_updated_q4']);
            // $excel->setCellValue("G$rowIndex", $row['laporan_tl_q4']);
            $excel->setCellValue("G$rowIndex", $row['avg_claim_progress_renaksi']);
            $excel->setCellValue("H$rowIndex", $row['avg_verifikasi_progress']);
            $excel->setCellValue("I$rowIndex", $row['tgl_update_terakhir']);

            $no++;
        }

        $excel->getColumnDimension('A')->setWidth(5);
        $excel->getColumnDimension('B')->setWidth(10);
        $excel->getColumnDimension('C')->setWidth(20);
        $excel->getColumnDimension('D')->setWidth(30);
        $excel->getColumnDimension('E')->setWidth(15);
        $excel->getColumnDimension('F')->setWidth(15);
        // $excel->getColumnDimension('G')->setWidth(10);
        $excel->getColumnDimension('G')->setWidth(30);
        $excel->getColumnDimension('H')->setWidth(20);
        $excel->getColumnDimension('I')->setWidth(25);

        $excel->getStyle('A1:I' . $rowIndex)->getAlignment()->setWrapText(true);
        $excel->getStyle('A1:I' . $rowIndex)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

        for ($row = 2; $row <= $rowIndex; $row++) {
            $excel->getRowDimension($row)->setRowHeight(-1);
        }

        $writer = new Xlsx($spreadsheet);
        $filename = 'Dashboard-Rencana-Aksi-' . date('Y') . '-' . date('YmdHis') . '.xlsx';
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

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

        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.observer_rtl']);

        $this->checkIsRtlVerificator();
        
        $getParams = $this->request->get();
        $getParams['tahun'] = array_key_exists('tahun', $getParams) ? $getParams['tahun'] : null;
        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : 0;
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['required']],
                'id_instansi' => ['validators' => ['digit']],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']]
            ]
        );

        $where = "rtl.tahun = :tahun AND rtl.deleted_at IS NULL";
        $binds = ['tahun' => $params['tahun']];

        $bindsWithLimitOffset = [
            'tahun' => $params['tahun'],
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ];

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


            $where = $where . ' AND i.id = :id_instansi';
            $binds['id_instansi'] = $params['id_instansi'];
            $bindsWithLimitOffset['id_instansi'] = $params['id_instansi'];
        }

        $verifiedRtls = $this->rawQuery("
            SELECT
                rv.id as verifikasi_id,
                i.id as instansi_id
            FROM
                jaga.spi_rtl_verifikasi rv
            JOIN
                jaga.spi_rtl rtl ON rtl.id = rv.id_rtl
            JOIN
                jaga.instansi i ON i.id = rtl.id_instansi
            WHERE
                rv.deleted_at IS NULL AND
                id_rtl IN (
                    SELECT
                        rtl.id
                    FROM
                        jaga.spi_rtl rtl
                    JOIN
                        jaga.instansi i ON i.id = rtl.id_instansi
                    WHERE
                    " . $where . "
                )
        ", $binds);


        $result = $this->rawQuery("
            SELECT
                DISTINCT ON (rtl_subquery.id_instansi)
                rtl_subquery.no,
                rtl_subquery.id,
                rtl_subquery.id_instansi,
                rtl_subquery.nama_instansi,
                rtl_subquery.tahun,
                rtl_subquery.is_all_updated_q3,
                rtl_subquery.is_all_updated_q4,
                rtl_subquery.claim_progress_renaksi,
                rtl_subquery.avg_claim_progress_renaksi,
                rtl_subquery.verifikasi_progress,
                rtl_subquery.avg_verifikasi_progress,
                rtl_subquery.tgl_update_terakhir,
                FALSE as is_form_rtl_verified
            FROM (
                SELECT
                    ROW_NUMBER() OVER (ORDER BY rtl.id DESC) AS no,
                    rtl.id,
                    i.id AS id_instansi,
                    i.nama AS nama_instansi,
                    rtl.tahun,
                    (
                        SELECT
                            NOT EXISTS (
                                SELECT 1 FROM jaga.spi_rtl rtl WHERE rtl.is_updated_q3 = FALSE AND rtl.id_instansi = i.id AND rtl.deleted_at IS NULL AND rtl.tahun = '" . $params['tahun'] . "'
                            ) 
                    ) AS is_all_updated_q3,
                    (
                        SELECT
                            NOT EXISTS (
                                SELECT 1 FROM jaga.spi_rtl rtl WHERE rtl.is_updated_q4 = FALSE AND rtl.id_instansi = i.id AND rtl.deleted_at IS NULL AND rtl.tahun = '" . $params['tahun'] . "'
                            ) 
                    ) AS is_all_updated_q4,
                    rtl.capaian AS claim_progress_renaksi,
                    avg_data.avg_claim_progress_renaksi,
                    rtl_verifikasi.progress AS verifikasi_progress,
                    avg_data.avg_verifikasi_progress,
                    (
                        SELECT
                            srtl.updated_at
                        FROM
                            jaga.spi_rtl srtl
                        WHERE
                            srtl.id_instansi = rtl.id_instansi
                            AND srtl.deleted_at IS NULL
                            AND srtl.updated_at IS NOT NULL
                            AND srtl.tahun = '" . $params['tahun'] . "'
                        ORDER BY
                            srtl.updated_at DESC
                        LIMIT
                            1
                    ) AS tgl_update_terakhir
                FROM
                    jaga.spi_rtl rtl
                LEFT JOIN
                    jaga.spi_rtl_verifikasi rtl_verifikasi ON rtl_verifikasi.id_rtl = rtl.id
                    AND rtl_verifikasi.deleted_at IS NULL
                LEFT JOIN
                    instansi i ON i.id = rtl.id_instansi
                LEFT JOIN (
                    SELECT 
                        i.id AS id_instansi,
                        AVG(COALESCE(REPLACE(rtl.capaian, '%', '')::FLOAT, 0)) AS avg_claim_progress_renaksi,
                        AVG(COALESCE(rtl_verifikasi.progress, 0)) AS avg_verifikasi_progress
                    FROM 
                        jaga.spi_rtl rtl
                    LEFT JOIN 
                        jaga.spi_rtl_verifikasi rtl_verifikasi ON rtl_verifikasi.id_rtl = rtl.id
                        AND rtl_verifikasi.deleted_at IS NULL
                    LEFT JOIN 
                        instansi i ON i.id = rtl.id_instansi
                    WHERE 
                        " . $where . "
                    GROUP BY 
                        i.id
                ) AS avg_data ON avg_data.id_instansi = i.id
                WHERE
                    " . $where . "
                ORDER BY
                    rtl.id DESC
            ) AS rtl_subquery
            LIMIT
                :limit
            OFFSET
                :offset
        ", $bindsWithLimitOffset);


        if (!empty($result)) {
            foreach($result as &$item) {
                $item['is_form_rtl_verified'] = FALSE;
                
                $verifikasi = array_filter($verifiedRtls, function ($verifiedRtl) use($item) {
                    return $verifiedRtl['instansi_id'] === $item['id_instansi'];
                });
                
                if (!empty($verifikasi)) {
                    $item['is_form_rtl_verified'] = TRUE;
                }
            }

            unset($item);
        }

        $total_record = count($this->rawQuery("
            SELECT
                DISTINCT ON (rtl_subquery.id_instansi)
                rtl_subquery.no,
                rtl_subquery.id,
                rtl_subquery.id_instansi,
                rtl_subquery.nama_instansi,
                rtl_subquery.tahun,
                rtl_subquery.is_all_updated_q3,
                rtl_subquery.is_all_updated_q4,
                rtl_subquery.claim_progress_renaksi,
                rtl_subquery.avg_claim_progress_renaksi,
                rtl_subquery.verifikasi_progress,
                rtl_subquery.avg_verifikasi_progress,
                rtl_subquery.tgl_update_terakhir,
                FALSE as is_form_rtl_verified
            FROM (
                SELECT
                    ROW_NUMBER() OVER (ORDER BY rtl.id DESC) AS no,
                    rtl.id,
                    i.id AS id_instansi,
                    i.nama AS nama_instansi,
                    rtl.tahun,
                    (
                        SELECT
                            NOT EXISTS (
                                SELECT 1 FROM jaga.spi_rtl rtl WHERE rtl.is_updated_q3 = FALSE AND rtl.id_instansi = i.id AND rtl.deleted_at IS NULL AND rtl.tahun = '" . $params['tahun'] . "'
                            ) 
                    ) AS is_all_updated_q3,
                    (
                        SELECT
                            NOT EXISTS (
                                SELECT 1 FROM jaga.spi_rtl rtl WHERE rtl.is_updated_q4 = FALSE AND rtl.id_instansi = i.id AND rtl.deleted_at IS NULL AND rtl.tahun = '" . $params['tahun'] . "'
                            ) 
                    ) AS is_all_updated_q4,
                    rtl.capaian AS claim_progress_renaksi,
                    avg_data.avg_claim_progress_renaksi,
                    rtl_verifikasi.progress AS verifikasi_progress,
                    avg_data.avg_verifikasi_progress,
                    (
                        SELECT
                            srtl.updated_at
                        FROM
                            jaga.spi_rtl srtl
                        WHERE
                            srtl.id_instansi = rtl.id_instansi
                            AND srtl.deleted_at IS NULL
                            AND srtl.updated_at IS NOT NULL
                            AND srtl.tahun = '" . $params['tahun'] . "'
                        ORDER BY
                            srtl.updated_at DESC
                        LIMIT
                            1
                    ) AS tgl_update_terakhir
                FROM
                    jaga.spi_rtl rtl
                LEFT JOIN
                    jaga.spi_rtl_verifikasi rtl_verifikasi ON rtl_verifikasi.id_rtl = rtl.id
                    AND rtl_verifikasi.deleted_at IS NULL
                LEFT JOIN
                    instansi i ON i.id = rtl.id_instansi
                LEFT JOIN (
                    SELECT 
                        i.id AS id_instansi,
                        AVG(COALESCE(REPLACE(rtl.capaian, '%', '')::FLOAT, 0)) AS avg_claim_progress_renaksi,
                        AVG(COALESCE(rtl_verifikasi.progress, 0)) AS avg_verifikasi_progress
                    FROM 
                        jaga.spi_rtl rtl
                    LEFT JOIN 
                        jaga.spi_rtl_verifikasi rtl_verifikasi ON rtl_verifikasi.id_rtl = rtl.id
                        AND rtl_verifikasi.deleted_at IS NULL
                    LEFT JOIN 
                        instansi i ON i.id = rtl.id_instansi
                    WHERE 
                        " . $where . "
                    GROUP BY 
                        i.id
                ) AS avg_data ON avg_data.id_instansi = i.id
                WHERE
                    " . $where . "
                ORDER BY
                    rtl.id DESC
            ) AS rtl_subquery
        ", $binds));

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

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

        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data']);

        $getParams = $this->request->get();
        $getParams['tahun'] = array_key_exists('tahun', $getParams) ? $getParams['tahun'] : null;
        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : null;
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['required']],
                'id_instansi' => ['validators' => ['digit']]
            ]
        );

        $idInstansi = $params['id_instansi'] ?? $this->shared->user->id_instansi;
        if (empty($params['id_instansi'])) {
            $idInstansi = $this->getIdInstansi();
        }

        $instansi  = Instansi::findFirst($idInstansi);

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

        $where = "rtl.tahun = :tahun AND rtl.id_instansi = :id_instansi AND rtl.deleted_at IS NULL AND rtl_verifikasi.deleted_at IS NULL";

        $isVerified = $this->rawQuery("
            SELECT
                CASE
                    WHEN COUNT(*) = 0 THEN FALSE
                    WHEN COUNT(*) FILTER (
                        WHERE rtl_verifikasi.status IS NOT NULL
                    ) = 0 THEN FALSE
                    WHEN COUNT(*) FILTER (
                        WHERE rtl_verifikasi.status NOT IN ('on_progress', 'selesai')
                    ) = 0 THEN TRUE
                    ELSE FALSE
                END AS has_verified
            FROM
                jaga.spi_rtl_verifikasi rtl_verifikasi
            JOIN
                jaga.spi_rtl rtl ON rtl.id = rtl_verifikasi.id_rtl
            WHERE
                " . $where, [
            'tahun' => $params['tahun'],
            'id_instansi' => $idInstansi
        ]);

        return new SimpleResponse($isVerified);
    }

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

        $this->checkOneOfScopes(['spi.view_klop_data', 'spi.manage_master_data']);

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

        $validatedParams = [
            'tahun' => ['validators' => ['digit']],
            'id_instansi' => ['validators' => ['digit']],
            'limit' => ['validators' => ['digit']],
            'offset' => ['validators' => ['digit']]
        ];

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

        if (!empty($getParams['status'])) {
            $validatedParams['status'] = ['validators' => [
                [
                    'name' => 'inclusion_in',
                    'params' => ['perlu_perbaikan', 'on_progress', 'selesai']
                ]
            ]];
        }

        $params = $this->validate($getParams, $validatedParams);

        $idInstansi = $params['id_instansi'] ?? $this->shared->user->id_instansi;
        $instansi = Instansi::findFirst($idInstansi);

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

        $where = "rtl.tahun = :tahun AND rtl.id_instansi = :id_instansi AND rtl.deleted_at IS NULL";

        $bindsWithLimitOffset = [
            'tahun' => $params['tahun'],
            'id_instansi' => $idInstansi,
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ];

        $binds = [
            'tahun' => $params['tahun'],
            'id_instansi' => $idInstansi
        ];

        if (!empty($params['status'])) {
            $where .= ' AND status = :status';
            $bindsWithLimitOffset['status'] = $params['status'];
            $binds['status'] = $params['status'];
        }

        $query = "
            SELECT
                ROW_NUMBER() OVER (ORDER BY rtl.id DESC) AS no,
                rtl.id,
                rtl.id_instansi,
                rtl.tahun,
                i.id AS id_instansi,
                i.nama AS nama_instansi,
                rtl.dimensi,
                rtl.rencana_aksi,
                rtl.capaian,
                rtl_verifikasi.progress,
                rtl_verifikasi.status
            FROM
                jaga.spi_rtl rtl
            LEFT JOIN
                jaga.spi_rtl_verifikasi rtl_verifikasi ON rtl_verifikasi.id_rtl = rtl.id
                AND rtl_verifikasi.deleted_at IS NULL
            LEFT JOIN
                jaga.instansi i ON i.id = rtl.id_instansi
            WHERE
                " . $where . "
            ORDER BY
                rtl.id DESC
        ";

        $queryWithLimitOffset = $query . "
            LIMIT
                :limit
            OFFSET
                :offset
        ";

        $rtl = $this->rawQuery($queryWithLimitOffset, $bindsWithLimitOffset);

        $total_record = count($this->rawQuery($query, $binds));

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

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

        $this->checkOneOfScopes(['spi.view_klop_data', 'spi.manage_master_data', 'spi.observer_rtl']);

        $rtl = SpiRtl::findFirst($id);

        if (empty($rtl)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data RTL tidak ditemukan');
        }

        // getting filenames
        $uuidWithoutExtension = [];
        foreach(explode(',', $rtl->upload_dok_pendukung) as $doc) {
            $uuidWithoutExtension[] = pathinfo($doc, PATHINFO_FILENAME);
        }

        $attachments = Attachments::find([
            'columns' => 'filename',
            'conditions' => 'uuid IN ({uuid:array}) AND deleted_at IS NULL',
            'bind' => [
                'uuid' => $uuidWithoutExtension
            ]
        ]);

        $filenames = [];
        if (!empty($attachments)) {
            foreach($attachments as $attachment) {
                $filenames[] = $attachment->filename;
            }
        }

        $result = $rtl->toDetail();
        $result['dok_pendukung_filenames'] = implode(',', $filenames);

        return new SimpleResponse([$result]);
    }

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

        $this->checkOneOfScopes(['spi.view_klop_data']);

        date_default_timezone_set('Asia/Jakarta');
        
        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $rawBody,
            [
                'id_instansi' => ['validators' => ['required', 'digit']],
                'tahun' => ['validators' => ['required']],
                'dimensi' => [
                    'validators' => [
                        'required',
                        [
                            'name' => 'inclusion_in',
                            'params' => [
                                'Integritas Dalam Pelaksanaan Tugas',
                                'Pengelolaan Anggaran',
                                'Pengelolaan PBJ',
                                'Pengelolaan SDM',
                                'Perdagangan Pengaruh (Trading in Influence)',
                                'Sosialisasi Antikorupsi',
                                'Transparansi',
                                'Integritas Pegawai',
                                'Transparansi dan Keadilan Layanan',
                                'Upaya Pencegahan Korupsi'
                            ]
                        ]
                    ]
                ],
                'rekomendasi' => ['validators' => ['required']],
                'rencana_aksi' => ['validators' => ['required']],
                'kriteria_keberhasilan' => ['validators' => ['required']],
                'ukuran_keberhasilan' => ['validators' => ['required']],
                'data_dukung' => [
                    'validators' => [
                        'required',
                        [
                            'name' => 'min_string',
                            'params' => 50
                        ]
                    ]
                ],
                'penanggung_jawab' => ['validators' => ['required']],
                'waktu_pelaksanaan' => [
                    'validators' => [
                        'required',
                        [
                            'name' => 'inclusion_in',
                            'params' => [
                                'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni',
                                'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'
                            ]
                        ]
                    ]
                ],
                'risiko' => ['validators' => ['required']]
                // 'capaian' => ['validators' => ['required']]
            ]
        );

        $tipe = null;
        $idInstansi = $params['id_instansi'] ?? $this->shared->user->id_instansi;
        $instansi = Instansi::findFirst($idInstansi);

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

        if (empty($this->shared->user->id_instansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User bukan admin instansi');
        }

        if ($instansi->id_tipe === 1 || $instansi->id_tipe === 2) {
            $tipe = 'PEMDA';
        } else {
            $tipe = 'KL';
        }

        $isRtlExists = SpiRtl::findFirst([
            'conditions' => 'LOWER(rencana_aksi) = LOWER(:rencana_aksi:) AND id_instansi = :id_instansi: AND tahun = :tahun: AND deleted_at IS NULL',
            'bind' => [
                'rencana_aksi' => $params['rencana_aksi'],
                'id_instansi' => $idInstansi,
                'tahun' => $params['tahun']
            ]
        ]);

        if (!empty($isRtlExists)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Rencana Aksi sudah tersimpan sebelumnya');
        }

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

        $rtl = new SpiRtl();
        $rtl->id_instansi = $idInstansi;
        $rtl->tahun = $params['tahun'];
        $rtl->tipe = $tipe;
        $rtl->dimensi = $params['dimensi'];
        $rtl->rekomendasi = $params['rekomendasi'];
        $rtl->rencana_aksi = $params['rencana_aksi'];
        $rtl->kriteria_keberhasilan = $params['kriteria_keberhasilan'];
        $rtl->ukuran_keberhasilan = $params['ukuran_keberhasilan'];
        $rtl->data_dukung = $params['data_dukung'];
        $rtl->penanggung_jawab = $params['penanggung_jawab'];
        $rtl->waktu_pelaksanaan = $params['waktu_pelaksanaan'];
        $rtl->risiko = $params['risiko'];
        // $rtl->capaian = $params['capaian'];
        $rtl->created_at = $now;
        $rtl->created_by = $this->shared->user->um_id;
        $status = $rtl->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([$rtl]);
        }
    }

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

        $this->checkOneOfScopes(['spi.view_klop_data']);

        $file = $this->getAndValidateImportFile('file', ['text/csv', 'application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet']);

        date_default_timezone_set('Asia/Jakarta');

        if (!empty($file)) {
            $getParams = $this->request->get();
            $params = $this->validate(
                $getParams,
                [
                    'tahun' => ['validators' => ['required', 'digit']],
                    'id_instansi' => ['validators' => ['required', 'digit']],
                    'type' => ['validators' => []]
                ]
            );

            $tipe = null;
            $idInstansi = $params['id_instansi'] ?? $this->shared->user->id_instansi;
            $instansi = Instansi::findFirst($idInstansi);
            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 terhapus');
            }

            if (empty($this->shared->user->id_instansi)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User bukan admin instansi');
            }

            if ($instansi->id_tipe === 1 || $instansi->id_tipe === 2) {
                $tipe = 'PEMDA';
            } else {
                $tipe = 'KL';
            }

            $filename = $file['tmp_name'];
            $rows = [];
            $isEmptyRows = [];

            if ($_FILES['file']['type'] === 'text/csv') {
                $csv = new ParseCsv\Csv();
                $csv->parseFile($filename);
                $rows = $csv->data;
            } else {
                try {
                    $excel = IOFactory::load($filename);
                    $sheet = $excel->getActiveSheet()->toArray();
    
                    for($i = 1; $i < sizeof($sheet); $i++) {
                        $row = $sheet[$i];
                        $isEmpty = false;
                        foreach ($row as $cell) {
                            if (empty(trim($cell))) {
                                $isEmpty = true;
                                break;
                            }
                        }

                        if ($isEmpty) {
                            $isEmptyRows[] = true;
                            continue;
                        }

                        $isEmptyRows[] = false;
                        $tmp = [];
                        foreach($sheet[0] as $key => $value) {
                            $tmp[$value] = $sheet[$i][$key];
                        }
    
                        array_push($rows, $tmp);
                    }
                } catch(\Exception $e) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Format file tidak valid! Detail: " . $e->getMessage());
                }
            }

            if (in_array(true, $isEmptyRows, true)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Data excel tidak valid, mohon periksa kembali excel yang di unggah");
            }

            $rows = array_map(function ($row) {
                $trimmedRow = [];
                foreach ($row as $key => $value) {
                    $trimmedRow[trim($key)] = trim($value);
                }
                return $trimmedRow;
            }, $rows);

            if (empty($rows)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data tidak ditemukan pada file');
            }

            // foreach($rows as $key => $row) {
            //     $capaian = floatval(str_replace('%', '', $row['Capaian/Claim Progress (%)']));
            //     if ($capaian > 100) {
            //         throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Terdapat data capaian yang melebihi 100%, Mohon periksa kembali data Anda');
            //     }
            // }
            
            $now = date('Y-m-d H:i:s');
            $transactionManager = new TransactionManager();
            $transaction = $transactionManager->get();
            $result = [];
            $dimensions = [
                'Integritas Dalam Pelaksanaan Tugas',
                'Pengelolaan Anggaran',
                'Pengelolaan PBJ',
                'Pengelolaan SDM',
                'Perdagangan Pengaruh (Trading in Influence)',
                'Sosialisasi Antikorupsi',
                'Transparansi',
                'Integritas Pegawai',
                'Transparansi dan Keadilan Layanan',
                'Upaya Pencegahan Korupsi'
            ];
            $falseDimension = [];
            $rencanaAksiInExcel = [];

            $existingRencanaAksi = SpiRtl::find([
                'columns' => 'LOWER(rencana_aksi) AS rencana_aksi',
                'conditions' => 'LOWER(rencana_aksi) IN ({rencana_aksi_from_excel:array}) AND id_instansi = :id_instansi: AND tahun = :tahun: AND deleted_at IS NULL',
                'bind' => [
                    'rencana_aksi_from_excel' => array_map('strtolower', array_column($rows, 'Rencana Aksi')),
                    'id_instansi' => $idInstansi,
                    'tahun' => $params['tahun']
                ]
            ]);

            $existingRencanaAksi = !empty($existingRencanaAksi) ? array_column($existingRencanaAksi->toArray(), 'rencana_aksi') : [];

            foreach($rows as $key => $row) {
                if (!in_array(strtolower($row['Rencana Aksi']), $existingRencanaAksi)) {
                    if (
                        !empty($row['Dimensi']) &&
                        !empty($row['Rekomendasi']) &&
                        !empty($row['Rencana Aksi']) &&
                        !empty($row['Kriteria Keberhasilan']) &&
                        !empty($row['Ukuran Keberhasilan']) &&
                        !empty($row['Data Dukung']) &&
                        !empty($row['Penanggung Jawab']) &&
                        !empty($row['Waktu Pelaksanaan/Timeline']) &&
                        !empty($row['Risiko/Kendala Terkait'])
                    ) {
                        // ensure the dimension on the list
                        if (!in_array($row['Dimensi'], $dimensions)) {
                            $falseDimension[] = $row['Dimensi'];
                        }

                        $rencanaAksiInExcel[] = preg_replace('/[^a-z0-9]/', '', strtolower($row['Rencana Aksi']));
                    }
                } else {
                    $result['duplicate'][] = $row;
                }
            }

            // check duplicate rencana_aksi in excel
            if (count($rencanaAksiInExcel) > 0) {
                $isUniqueRencanaAksi = count($rencanaAksiInExcel) === count(array_unique($rencanaAksiInExcel));
                if (!$isUniqueRencanaAksi) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Terdapat Rencana Aksi yang sama');
                }
            }
            
            if (!empty($result) && count($result['duplicate']) > 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data dengan rencana aksi yang sama sudah terupload sebelumnya');
            }

            if (count($falseDimension) > 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Terdapat dimensi yang tidak sesuai');
            }
            
            try {
                foreach($rows as $key => $row) {
                    if (!in_array(strtolower($row['Rencana Aksi']), $existingRencanaAksi)) {
                        if (
                            !empty($row['Dimensi']) &&
                            !empty($row['Rekomendasi']) &&
                            !empty($row['Rencana Aksi']) &&
                            !empty($row['Kriteria Keberhasilan']) &&
                            !empty($row['Ukuran Keberhasilan']) &&
                            !empty($row['Data Dukung']) &&
                            !empty($row['Penanggung Jawab']) &&
                            !empty($row['Waktu Pelaksanaan/Timeline']) &&
                            !empty($row['Risiko/Kendala Terkait'])
                        ) {
                            $rtl = new SpiRtl();
                            $rtl->setTransaction($transaction);
        
                            $rtl->id_instansi = $idInstansi;
                            $rtl->tahun = $params['tahun'];
                            $rtl->tipe = $tipe;
                            $rtl->dimensi = $row['Dimensi'];
                            $rtl->rekomendasi = $row['Rekomendasi'];
                            $rtl->rencana_aksi = $row['Rencana Aksi'];
                            $rtl->kriteria_keberhasilan = $row['Kriteria Keberhasilan'];
                            $rtl->ukuran_keberhasilan = $row['Ukuran Keberhasilan'];
                            $rtl->data_dukung = $row['Data Dukung'];
                            $rtl->penanggung_jawab = $row['Penanggung Jawab'];
                            $rtl->waktu_pelaksanaan = $row['Waktu Pelaksanaan/Timeline'];
                            $rtl->risiko = $row['Risiko/Kendala Terkait'];
                            // $rtl->capaian = $row['Capaian/Claim Progress (%)'];
                            $rtl->upload_dok_pendukung = null;
                            $rtl->created_at = $now;
                            $rtl->created_by = $this->shared->user->um_id;
                        }
                        
                        $result['success'][] = $row;
                        $status = $rtl->save();
                        
                        if (!$status) {
                            $result['failed'][] = $row;
                            $transaction->rollback("Gagal menyimpan data");
                        }
                    } else {
                        $result['duplicate'][] = $row;
                    }
                }

                $transaction->commit();
            }
            catch(\Exception $e) {
                $transaction->rollback();
            }
        }
        
        return new SimpleResponse($result);
    }

    private function normalizeString(String $value): string
    {
        // to remove/normalize, space, tab, newline from a value
        $value = preg_replace('/\R+/', ' ', $value);
        $value = preg_replace('/\s+/', ' ', $value);

        return trim($value);
    }

    private function isDataDukungChanged(String $dataDukungDB, String $dataDukungInput): bool
    {
        if ($this->normalizeString($dataDukungDB) !== $this->normalizeString($dataDukungInput)) {
            return true;
        }

        return false;
    }

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

        $this->checkOneOfScopes(['spi.view_klop_data']);

        date_default_timezone_set('Asia/Jakarta');
        
        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $rawBody,
            [
                'dimensi' => [
                    'validators' => [
                        'required'
                        // [
                        //     'name' => 'inclusion_in',
                        //     'params' => [
                        //         'Integritas Dalam Pelaksanaan Tugas',
                        //         'Pengelolaan Anggaran',
                        //         'Pengelolaan PBJ',
                        //         'Pengelolaan SDM',
                        //         'Perdagangan Pengaruh (Trading in Influence)',
                        //         'Sosialisasi Antikorupsi',
                        //         'Transparansi',
                        //         'Integritas Pegawai',
                        //         'Transparansi dan Keadilan Layanan',
                        //         'Upaya Pencegahan Korupsi'
                        //     ]
                        // ]
                    ]
                ],
                'rekomendasi' => ['validators' => ['required']],
                'rencana_aksi' => ['validators' => ['required']],
                'kriteria_keberhasilan' => ['validators' => ['required']],
                'ukuran_keberhasilan' => ['validators' => ['required']],
                'data_dukung' => [
                    'validators' => [
                        'required',
                        [
                            'name' => 'min_string',
                            'params' => 50
                        ]
                    ]
                ],
                'penanggung_jawab' => ['validators' => ['required']],
                'waktu_pelaksanaan' => ['validators' => ['required']],
                'risiko' => ['validators' => ['required']],
                'capaian' => ['validators' => []],
                'upload_dok_pendukung' => ['validators' => []]
            ]
        );

        $capaianExploded = explode('%', $params['capaian']);
        $capaian = intval($capaianExploded[0]);
        if ($capaian < 0 || $capaian > 100) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Nilai capaian harus dalam range 0-100');
        }

        if (empty($this->shared->user->id_instansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User bukan admin instansi');
        }

        $rtl = SpiRtl::findFirst($id);
        if (empty($rtl)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data RTL tidak ditemukan');
        }

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

        if ($params['rencana_aksi'] !== $rtl->rencana_aksi) {
            $isExists = SpiRtl::findFirst([
                'conditions' => 'LOWER(rencana_aksi) = LOWER(:rencana_aksi:) AND id_instansi = :id_instansi: AND tahun = :tahun: AND deleted_at IS NULL',
                'bind' => [
                    'rencana_aksi' => $params['rencana_aksi'],
                    'id_instansi' => $rtl->id_instansi,
                    'tahun' => $rtl->tahun
                ]
            ]);
    
            if (!empty($isExists)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Rencana Aksi sudah tersimpan sebelumnya');
            }
        }

        // ensure rtl isnt on progress
        $idRtlVerified = [];
        $rtl_by_instansi = $this->rawQuery("
            SELECT
                rtl.id
            FROM
                jaga.spi_rtl rtl
            LEFT JOIN
                jaga.instansi i ON i.id = rtl.id_instansi
            WHERE
                rtl.id_instansi = :id_instansi AND
                rtl.deleted_at IS NULL AND
                rtl.tahun = :tahun
        ", [
            'id_instansi' => $rtl->id_instansi,
            'tahun' => $rtl->tahun
        ]);

        if (!empty($rtl_by_instansi)) {
            foreach($rtl_by_instansi as $rtlInstansi) {
                $rtl_verifikasi = SpiRtlVerifikasi::findFirst([
                    'conditions' => "id_rtl = :id_rtl: AND status = 'on_progress' AND deleted_at IS NULL",
                    'bind' => [
                        'id_rtl' => $rtlInstansi['id']
                    ]
                ]);

                if (!empty($rtl_verifikasi)) {
                    $idRtlVerified[] = $rtl_verifikasi->id;
                }
            }
        }

        $isRtlVerified = count($idRtlVerified) > 0 ? true : false;

        if (!$isRtlVerified) {
            $this->validate(
                $rawBody,
                [
                    'waktu_pelaksanaan' => [
                        'validators' => [
                            'required',
                            [
                                'name' => 'inclusion_in',
                                'params' => [
                                    'Januari', 'Februari', 'Maret', 'April', 'Mei', 'Juni',
                                    'Juli', 'Agustus', 'September', 'Oktober', 'November', 'Desember'
                                ]
                            ]
                        ]
                    ]
                ]
            );
        }

        $now = date('Y-m-d H:i:s');
        $isDataDukungReplaced = $this->isDataDukungChanged($rtl->data_dukung, $params['data_dukung']);
        if (!!$isDataDukungReplaced) {
            $dateNow = date('Y-m-d');
            if ($dateNow >= $this->q3_rtl_update_start_date && $dateNow <= $this->q3_rtl_update_end_date) {
                // triwulan 3
                $rtl->is_updated_q3 = TRUE;
                $rtl->q3_updated_at = $now;
            }

            if ($dateNow >= $this->q4_rtl_update_start_date && $dateNow <= $this->q4_rtl_update_end_date) {
                // triwulan 4
                $rtl->is_updated_q4 = TRUE;
                $rtl->q4_updated_at = $now;
            }
        }

        if ($isRtlVerified) {
            $rtl->data_dukung = $params['data_dukung'];
            $rtl->risiko = $params['risiko'];
            $rtl->capaian = $params['capaian'];
        } else {
            $rtl->dimensi = $params['dimensi'];
            $rtl->rekomendasi = $params['rekomendasi'];
            $rtl->rencana_aksi = $params['rencana_aksi'];
            $rtl->kriteria_keberhasilan = $params['kriteria_keberhasilan'];
            $rtl->ukuran_keberhasilan = $params['ukuran_keberhasilan'];
            $rtl->data_dukung = $params['data_dukung'];
            $rtl->penanggung_jawab = $params['penanggung_jawab'];
            $rtl->waktu_pelaksanaan = $params['waktu_pelaksanaan'];
            $rtl->risiko = $params['risiko'];
            $rtl->capaian = $params['capaian'];
        }

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

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

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

        $this->checkOneOfScopes(['spi.view_klop_data', 'spi.manage_master_data']);

        date_default_timezone_set('Asia/Jakarta');

        $rtl = SpiRtl::findFirst($id);

        if (empty($rtl)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data RTL tidak ditemukan');
        }

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

        // ensure rtl isn't verified yet
        $verifications = SpiRtlVerifikasi::find([
            'columns' => 'id',
            'conditions' => 'id_rtl = :id_rtl: AND deleted_at IS NULL',
            'bind' => [
                'id_rtl' => $rtl->id
            ]
        ]);

        if (count($verifications) > 0) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'RTL sudah diverifikasi dan tidak dapat di hapus');
        }

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

        $rtl->deleted_at = $now;
        $rtl->deleted_by = $this->shared->user->um_id;
        $status = $rtl->save();

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

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

        $this->checkOneOfScopes(['spi.manage_master_data']);

        // $this->checkIsRtlVerificator();

        date_default_timezone_set('Asia/Jakarta');

        $getParams = $this->request->getJsonRawBody(true);
        $params = $this->validate($getParams, [
            'id_rtls' => ['validators' => ['required']], // 1, 2, 3, 4, 5
        ]);

        $idRtls = [];
        if (count($params['id_rtls']) > 0) {
            foreach($params['id_rtls'] as $id_rtl) {
                $rtl = SpiRtl::findFirst([
                    'columns' => 'id',
                    'conditions' => 'id = :id: AND deleted_at IS NULL',
                    'bind' => [
                        'id' => $id_rtl
                    ]
                ]);

                if (!empty($rtl)) {
                    // ensure rtl isn't verified yet
                    $verification = SpiRtlVerifikasi::findFirst([
                        'conditions' => 'id_rtl = :id_rtl: AND deleted_at IS NULL',
                        'bind' => [
                            'id_rtl' => $rtl->id
                        ]
                    ]);

                    if (empty($verification)) {
                        $idRtls[] = $rtl->id;
                    }
                }
            }
        }

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

        if (count($idRtls) > 0) {
            $result = $this->rawQuery("
                UPDATE
                    jaga.spi_rtl
                SET
                    deleted_at = :deleted_at,
                    deleted_by = :deleted_by
                WHERE
                    id IN (" . implode(', ', $idRtls) . ") AND
                    deleted_at IS NULL
            ", [
                'deleted_at' => $now,
                'deleted_by' => $this->shared->user->um_id
            ]);
        }

        return new SimpleResponse($idRtls);
    }

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

        $this->checkOneOfScopes(['spi.view_klop_data']);

        date_default_timezone_set('Asia/Jakarta');

        $getParams = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $getParams,
            [
                'id_instansi' => ['validators' => ['required', 'digit']],
                'tahun' => ['validators' => ['required']],
            ]
        );

        $idInstansi = $params['id_instansi'] ?? $this->shared->user->id_instansi;
        $instansi = Instansi::findFirst($idInstansi);

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

        if (empty($this->shared->user->um_id)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Pengguna tidak di kenali');
        }

        $user = UmUser::findFirst($this->shared->user->um_id);
        if (empty($user)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Pengguna tidak ditemukan');
        }

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

        $rtl = SpiRtl::find([
            'conditions' => 'id_instansi = :id_instansi: AND tahun = :tahun: AND deleted_at IS NULL',
            'bind' => [
                'id_instansi' => $idInstansi,
                'tahun' => $params['tahun']
            ]
        ]);

        if (empty($rtl)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data RTL dengan instansi terkait tidak ditemukan');
        }

        $di = Di::getDefault();
        $modelsManager = $di->getShared('modelsManager');
        $now = date('Y-m-d H:i:s');
        $rtlIds = array_column($rtl->toArray(), 'id');
        
        if (empty($rtlIds)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Tidak ada data RTL yang perlu dihapus');
        }

        try {
            $phql = "
                UPDATE SpiRtl
                    SET
                        deleted_at = :deleted_at:,
                        deleted_by = :deleted_by:
                WHERE id IN ({ids:array})
            ";

            $query = $modelsManager->executeQuery($phql, [
                'deleted_at' => $now,
                'deleted_by' => $this->shared->user->um_id,
                'ids' => $rtlIds
            ]);

            foreach ($rtl as $item) {
                $item->refresh();
            }

            return new CollectionPaginatedResponse([$rtl]);
        } catch (\Phalcon\Mvc\Model\Exception $e) {
            $error = "Error: " . $e->getMessage();
            $error_messages = [$error];

            throw new ValidationException($error_messages);
        }
    }

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

        $this->checkOneOfScopes(['spi.view_klop_data']);

        $filename = 'template_upload_excel_rencana_tindak_lanjut_spi.xlsx';
        $key = 'template_upload_excel_rencana_tindak_lanjut_spi.xlsx';

        $directory = 'public_content' .
            DIRECTORY_SEPARATOR .
            'file-spi' .
            DIRECTORY_SEPARATOR .
            date('Y') .
            DIRECTORY_SEPARATOR . 'rencana-tindak-lanjut' .
            DIRECTORY_SEPARATOR;

        $service = new AttachmentService($this->shared, $this->minio);
        return $service->downloadPublicUsingSecondaryS3(
            $directory,
            $key,
            $filename,
            'xlsx'
        );
    }

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

        $this->checkOneOfScopes(['spi.view_klop_data']);

        date_default_timezone_set('Asia/Jakarta');

        $rtl = SpiRtl::findFirst($id);
        if (empty($rtl)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data RTL tidak ditemukan');
        }

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

        $idInstansi = $this->shared->user->id_instansi;
        if (empty($idInstansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User bukan instansi terkait');
        }

        $instansi = Instansi::findFirst($idInstansi);

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

        try {
            $service = new SpiMonitoringService($this);
            $attachment = $service->saveFile([
                'allowed-types' => ["application/pdf", "image/jpeg", "image/png"],
                'max-file-count' => 5,
                'max-size-kb' => 1024 * 5
            ], 'rencana_tindak_lanjut' . DIRECTORY_SEPARATOR . date('Y') . DIRECTORY_SEPARATOR . 'dokumen_pendukung');

            $now = date('Y-m-d H:i:s');
            $uuidFile = [];
            if (!empty($attachment) && count($attachment) > 0) {
                foreach($attachment as $file) {
                    $filenameSplitted = explode('.', $file->filename);
                    $extension = end($filenameSplitted);
                    $uuidFile[] = $file->uuid . '.' . $extension;
                }

                if (!empty($uuidFile) && count($uuidFile) > 0) {
                    $uuidToStringForDB = implode(',', $uuidFile);
                    $rtl->updated_at = $now;
                    $rtl->updated_by = $this->shared->user->um_id;
                    $rtl->upload_dok_pendukung = !empty($rtl->upload_dok_pendukung) ? $rtl->upload_dok_pendukung . ',' . $uuidToStringForDB : $uuidToStringForDB;
                    $status = $rtl->save();

                    if (!$status) {
                        $errors = $rtl->getMessages();
                        $error_messages = [];
            
                        foreach ($errors as $key => $error) {
                            $error_messages[] = ["field" => $error->getField(), "message" => $error->getMessage()];
                        }
            
                        throw new ValidationException($error_messages);
                    }
                }
            }

            return new SimpleResponse($attachment);
        }
        catch (Exception $e) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Gagal mengunggah dokumen pendukung');
        }
    }

    public function downloadRtlSupportAttachment($uuid) {
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data', 'spi.observer_rtl']);

        /** @var MinioService $minioService */
        $minioService = $this->minio;
        $service = new AttachmentService($this->shared, $this->minio);
        if ($minioService->isSecondaryS3Available()) {
            $res = $service->downloadUsingDualS3(
                'spi-monitoring-attachment' . DIRECTORY_SEPARATOR . 'rencana_tindak_lanjut' . DIRECTORY_SEPARATOR . date("Y") . DIRECTORY_SEPARATOR . 'dokumen_pendukung',
                $uuid,
                true,
                LogAttachments::SPIMonitoringAttachment
            );
        } else {
            $res = $service->download(
                'spi-monitoring-attachment' . DIRECTORY_SEPARATOR . 'rencana_tindak_lanjut' . DIRECTORY_SEPARATOR . date("Y") . DIRECTORY_SEPARATOR . 'dokumen_pendukung',
                $uuid,
                true
            );
        }

        return $res;
    }

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

        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.observer_rtl']);

        $this->checkIsRtlVerificator();

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

        $validatedParams = [
            'tahun' => ['validators' => ['required']],
            'id_instansi' => ['validators' => ['digit']],
            'limit' => ['validators' => ['digit']],
            'offset' => ['validators' => ['digit']]
        ];

        $getParams['tahun'] = array_key_exists('tahun', $getParams) ? $getParams['tahun'] : null;
        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : null;
        $getParams['dimensi'] = array_key_exists('dimensi', $getParams) ? $getParams['dimensi'] : null;
        $getParams['status'] = array_key_exists('status', $getParams) ? $getParams['status'] : null;
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;

        if (!empty($getParams['dimensi'])) {
            $validatedParams['dimensi'] =  ['validators' => [
                [
                    'name' => 'inclusion_in',
                    'params' => [
                        'Integritas Dalam Pelaksanaan Tugas',
                        'Pengelolaan Anggaran',
                        'Pengelolaan PBJ',
                        'Pengelolaan SDM',
                        'Perdagangan Pengaruh (Trading in Influence)',
                        'Sosialisasi Antikorupsi',
                        'Transparansi',
                        'Integritas Pegawai',
                        'Transparansi dan Keadilan Layanan',
                        'Upaya Pencegahan Korupsi'
                    ]
                ]
            ]];
        }

        if (!empty($getParams['status'])) {
            $validatedParams['status'] = ['validators' => [
                [
                    'name' => 'inclusion_in',
                    'params' => ['perlu_perbaikan', 'on_progress', 'selesai', 'belum_diverifikasi']
                ]
            ]];
        }

        $params = $this->validate($getParams, $validatedParams);

        $where = 'spi_rtl.tahun = :tahun AND spi_rtl.deleted_at IS NULL';
        $binds = [
            'tahun' => $params['tahun'],
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ];
        if (!empty($params['id_instansi'])) {
            $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 terhapus');
            }

            $where = $where . ' AND spi_rtl.id_instansi = :id_instansi';
            $binds['id_instansi'] = $params['id_instansi'];
        }

        if (!empty($params['dimensi'])) {
            $where = $where . ' AND spi_rtl.dimensi = :dimensi';
            $binds['dimensi'] = $params['dimensi'];
        }

        if (!empty($params['status'])) {
            if ($params['status'] === 'belum_diverifikasi') {
                $where = $where . " AND spi_rtl_verifikasi.status IS NULL OR spi_rtl_verifikasi.status = ''";
            } else {
                $where = $where . ' AND spi_rtl_verifikasi.status = :status';
                $binds['status'] = $params['status'];
            }
        }

        $rtl = [];
        $rows = $this->rawQuery("
            SELECT
                ROW_NUMBER() OVER (ORDER BY spi_rtl.id DESC) AS no,
                spi_rtl.id as rtl_id,
                spi_rtl.*,
                spi_rtl_verifikasi.id as verifikasi_id,
                spi_rtl_verifikasi.*,
                i.*
            FROM
                jaga.spi_rtl spi_rtl
            LEFT JOIN
                jaga.spi_rtl_verifikasi spi_rtl_verifikasi ON spi_rtl.id = spi_rtl_verifikasi.id_rtl
                AND spi_rtl_verifikasi.deleted_at IS NULL
            LEFT JOIN
                instansi i ON i.id = spi_rtl.id_instansi
            WHERE
                " . $where . "
            ORDER BY
                spi_rtl.id DESC
            LIMIT
                :limit
            OFFSET
                :offset
        ", $binds);

        if (!empty($rows)) {
            foreach($rows as $row) {
                $rtl[] = [
                    'no' => $row['no'],
                    'id' => $row['rtl_id'],
                    'id_instansi' => $row['id_instansi'],
                    'tahun' => $row['tahun'],
                    'tipe' => $row['tipe'],
                    'dimensi' => $row['dimensi'],
                    'rekomendasi' => $row['rekomendasi'],
                    'rencana_aksi' => $row['rencana_aksi'],
                    'kriteria_keberhasilan' => $row['kriteria_keberhasilan'],
                    'ukuran_keberhasilan' => $row['ukuran_keberhasilan'],
                    'data_dukung' => $row['data_dukung'],
                    'penanggung_jawab' => $row['penanggung_jawab'],
                    'waktu_pelaksanaan' => $row['waktu_pelaksanaan'],
                    'risiko' => $row['risiko'],
                    'capaian' => $row['capaian'],
                    'upload_dok_pendukung' => $row['upload_dok_pendukung'],
                    'created_at' => $row['created_at'],
                    'created_by' => $row['created_by'],
                    'updated_at' => $row['updated_at'],
                    'updated_by' => $row['updated_by'],
                    'deleted_at' => $row['deleted_at'],
                    'deleted_by' => $row['deleted_by'],
                    'instansi' => [
                        'id' => $row['id'],
                        'nama' => $row['nama'],
                    ],
                    'spi_rtl_verifikasi' => $row['verifikasi_id'] ? [
                        'id' => $row['verifikasi_id'],
                        'id_rtl' => $row['id_rtl'],
                        'id_user' => $row['id_user'],
                        'progress' => $row['progress'],
                        'catatan' => $row['catatan'],
                        'status' => $row['status'],
                        'created_at' => $row['created_at'],
                        'created_by' => $row['created_by'],
                        'updated_at' => $row['updated_at'],
                        'updated_by' => $row['updated_by'],
                        'deleted_at' => $row['deleted_at'],
                        'deleted_by' => $row['deleted_by']
                    ] : null
                ];
            }
        }

        unset($binds['limit']);
        unset($binds['offset']);

        $total_record = count($this->rawQuery("
            SELECT
                spi_rtl.id
            FROM
                jaga.spi_rtl spi_rtl
            LEFT JOIN
                jaga.spi_rtl_verifikasi spi_rtl_verifikasi ON spi_rtl.id = spi_rtl_verifikasi.id_rtl
                AND spi_rtl_verifikasi.deleted_at IS NULL
            LEFT JOIN
                instansi i ON i.id = spi_rtl.id_instansi
            WHERE
                " . $where . "
            ORDER BY
                spi_rtl.id DESC
        ", $binds));

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

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

        $this->checkOneOfScopes(['spi.manage_master_data']);

        $this->checkIsRtlVerificator();

        $rawBody = $this->request->getJsonRawBody(true);
        $rawBody['limit'] = $this->getDefaultLimit($rawBody);
        $rawBody['offset'] = array_key_exists('offset', $rawBody) ? $rawBody['offset'] : 0;
        $params = $this->validate(
            $rawBody,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']]
            ]
        );

        $rtl_verifikasi = SpiRtlVerifikasi::find([
            'conditions' => 'id_user = :id_user: AND deleted_at IS NULL',
            'bind' => [
                'id_user' => $this->shared->user->um_id
            ],
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'order' => 'id DESC'
        ]);

        $total_record = count($rtl_verifikasi);

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

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

        $this->checkOneOfScopes(['spi.manage_master_data']);

        $this->checkIsRtlVerificator();

        $rtl_verifikasi = SpiRtlVerifikasi::findFirst($id);

        if (empty($rtl_verifikasi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data verifikasi tidak ditemukan');
        }

        $result = $rtl_verifikasi->toDetail();

        return new CollectionPaginatedResponse([$result]);
    }

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

        $this->checkOneOfScopes(['spi.manage_master_data']);

        $this->checkIsRtlVerificator();

        date_default_timezone_set('Asia/Jakarta');

        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $rawBody,
            [
                'id_rtl' => ['validators' => ['required', 'digit']],
                'id_user' => ['validators' => ['required', 'digit']],
                // 'progress' => ['validators' => ['digit']],
                'progress' => ['validators' => []],
                'catatan' => ['validators' => []],
                // 'status' => ['validators' => ['required']]
                'status' => ['validators' => []]
            ]
        );

        $idUser = $params['id_user'] ?? $this->shared->user->um_id;
        $rtl = SpiRtl::findFirst($params['id_rtl']);
        if (empty($rtl)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'RTL tidak ditemukan');
        }

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

        $verifikator = UmUser::findFirst($idUser);
        if (empty($verifikator)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Verifikator tidak ditemukan');
        }

        $now = date('Y-m-d H:i:s');
        $rtl_verifikasi = new SpiRtlVerifikasi();
        $rtl_verifikasi->id_rtl = $params['id_rtl'];
        $rtl_verifikasi->id_user = $idUser;
        $rtl_verifikasi->progress = $params['progress'];
        $rtl_verifikasi->catatan = $params['catatan'];
        $rtl_verifikasi->status = $params['status'];
        $rtl_verifikasi->created_at = $now;
        $rtl_verifikasi->created_by = $this->shared->user->um_id;
        $status = $rtl_verifikasi->save();

        // config notify
        $notifyMessages = [];

        if ($params['status'] === 'perlu_perbaikan') {
            $notifyMessages[] = [
                'body_message_id' => '[SPI] Anda perlu melakukan perbaikan!',
                'body_message_en' => '[SPI] You need to correct it!'
            ];
        } else if ($params['status'] === 'on_progress') {
            $notifyMessages[] = [
                'body_message_id' => '[SPI] Anda Perlu Lakukan Pengkinian Data!',
                'body_message_en' => '[SPI] You need to update your data!'
            ];
        } 

        if (!empty($params['progress']) && $params['progress'] > 0 ) {
            $notifyMessages[] = [
                'body_message_id' => '[SPI] Verifikator Telah Melakukan Penilaian',
                'body_message_en' => '[SPI] Verificator has been submitted the score'
            ];
        }

        if (count($notifyMessages) > 0) {
            $userIds = $this->rawQuery("
                SELECT
                    um_id
                FROM
                    jaga.um_user uu
                JOIN
                    users_roles ur ON ur.user_id = uu.um_id
                WHERE
                    uu.id_instansi = (
                        SELECT
                            id_instansi
                        FROM
                            jaga.um_user
                        WHERE
                            um_id = " . $rtl->created_by . "
                    ) AND
                     ur.role_id = (
                        SELECT
                            id
                        FROM
                            jaga.roles
                        WHERE
                            name = 'spi_admin_klop'
                     )
            ");

            $receiverIds = [];
            if (!empty($userIds) && count($userIds) > 0) {
                foreach ($userIds as $user) {
                    $receiverIds[] = $user['um_id'];
                }
            }

            if (count($receiverIds) > 0) {
                foreach($notifyMessages as $message) {
                    NotificationHelper::batchSend(
                        $receiverIds, // receiver ids
                        // $rtl->created_by, // receiver id
                        $this->shared->user->um_id, // sender id
                        $message['body_message_id'], // message in bahasa
                        NotificationHelper::CATEGORY_SPI, // action
                        $rtl->id, // action_id
                        '/spi/formulir-monitoring-spi', // action to go
                        NotificationHelper::CATEGORY_SPI, // category notify
                        $message['body_message_en'] // message in english
                    );
                }
            }
        }

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

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

        $this->checkOneOfScopes(['spi.manage_master_data']);

        $this->checkIsRtlVerificator();

        date_default_timezone_set('Asia/Jakarta');

        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $rawBody,
            [
                'id_rtl' => ['validators' => ['required', 'digit']],
                'id_user' => ['validators' => ['required', 'digit']],
                // 'progress' => ['validators' => ['digit']],
                'progress' => ['validators' => []],
                'catatan' => ['validators' => []],
                // 'status' => ['validators' => ['required']]
                'status' => ['validators' => []]
            ]
        );

        $idUser = $params['id_user'] ?? $this->shared->user->um_id;
        $rtl_verifikasi = SpiRtlVerifikasi::findFirst($id);
        if (empty($rtl_verifikasi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data verifikasi tidak ditemukan');
        }

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

        $verifikator = UmUser::findFirst($idUser);
        if (empty($verifikator)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Verifikator tidak ditemukan');
        }

        $rtl = SpiRtl::findFirst($params['id_rtl']);
        if (empty($rtl)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data RTL tidak ditemukan');
        }

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

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

        // config notify
        $notifyMessages = [];

        if ($params['status'] === 'perlu_perbaikan') {
            $notifyMessages[] = [
                'body_message_id' => '[SPI] Anda perlu melakukan perbaikan!',
                'body_message_en' => '[SPI] You need to correct it!'
            ];
        } else if ($params['status'] === 'on_progress' && $rtl_verifikasi->status !== $params['status']) {
            $notifyMessages[] = [
                'body_message_id' => '[SPI] Anda Perlu Lakukan Pengkinian Data!',
                'body_message_en' => '[SPI] You need to update your data!'
            ];
        }

        if (!empty($params['progress']) && $rtl_verifikasi->progress !== $params['progress'] && $params['progress'] > 0 ) {
            $notifyMessages[] = [
                'body_message_id' => '[SPI] Verifikator Telah Melakukan Penilaian',
                'body_message_en' => '[SPI] Verificator has been submitted the score'
            ];
        }

        $rtl_verifikasi->id_rtl = $params['id_rtl'];
        $rtl_verifikasi->id_user = $idUser;
        $rtl_verifikasi->progress = $params['progress'];
        $rtl_verifikasi->catatan = $params['catatan'];
        $rtl_verifikasi->status = $params['status'];
        $rtl_verifikasi->updated_by = $this->shared->user->um_id;
        $rtl_verifikasi->updated_at = $now;
        $status = $rtl_verifikasi->save();

        if (count($notifyMessages) > 0) {
            $userIds = $this->rawQuery("
                SELECT
                    um_id
                FROM
                    jaga.um_user uu
                JOIN
                    users_roles ur ON ur.user_id = uu.um_id
                WHERE
                    uu.id_instansi = (
                        SELECT
                            id_instansi
                        FROM
                            jaga.um_user
                        WHERE
                            um_id = " . $rtl->created_by . "
                    ) AND
                     ur.role_id = (
                        SELECT
                            id
                        FROM
                            jaga.roles
                        WHERE
                            name = 'spi_admin_klop'
                     )
            ");

            $receiverIds = [];
            if (!empty($userIds) && count($userIds) > 0) {
                foreach ($userIds as $user) {
                    $receiverIds[] = $user['um_id'];
                }
            }

            if (count($receiverIds) > 0) {
                foreach($notifyMessages as $message) {
                    NotificationHelper::batchSend(
                        $receiverIds, // receiver ids
                        // $rtl->created_by, // receiver id
                        $this->shared->user->um_id, // sender id
                        $message['body_message_id'], // message in bahasa
                        NotificationHelper::CATEGORY_SPI, // action
                        $rtl->id, // action_id
                        '/spi/formulir-monitoring-spi', // action to go
                        NotificationHelper::CATEGORY_SPI, // category notify
                        $message['body_message_en'] // message in english
                    );
                }
            }
        }

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

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

        $this->checkOneOfScopes(['spi.manage_master_data']);

        $this->checkIsRtlVerificator();

        date_default_timezone_set('Asia/Jakarta');

        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $rawBody,
            [
                'id_user' => ['validators' => ['required', 'digit']],
                'status' => ['validators' => [
                    [
                        'name' => 'inclusion_in',
                        'params' => ['perlu_perbaikan', 'on_progress', 'selesai']
                    ]
                ]],
                'rtl_ids' => ['validators' => ['required']]
            ]
        );

        if (empty($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User bukan admin instansi');
        }

        $idUser = $params['id_user'] ?? $this->shared->user->um_id;
        $verifikator = UmUser::findFirst($idUser);
        if (empty($verifikator)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Verifikator tidak ditemukan');
        }

        $now = date('Y-m-d H:i:s');
        $rowUpdated = [];
        $rowCreated = [];
        $notifyMessages = [];
        if (count($params['rtl_ids']) > 0) {
            foreach($params['rtl_ids'] as $rtlId) {
                $rtl = SpiRtl::findFirst($rtlId);
                if (empty($rtl)) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data RTL tidak ditemukan');
                }
        
                if (!empty($rtl->deleted_at)) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data RTL sudah terhapus');
                }                

                $rtl_verifikasi = SpiRtlVerifikasi::findFirst([
                    'conditions' => 'id_rtl = :id_rtl: AND deleted_at IS NULL',
                    'bind' => [
                        'id_rtl' => $rtl->id
                    ]
                ]);

                if (empty($rtl_verifikasi)) {
                    $rtl_verifikasi_new = new SpiRtlVerifikasi();
                    $rtl_verifikasi_new->id_rtl = $rtl->id;
                    $rtl_verifikasi_new->id_user = $idUser;
                    $rtl_verifikasi_new->progress = 0;
                    $rtl_verifikasi_new->catatan = null;
                    $rtl_verifikasi_new->status = $params['status'];
                    $rtl_verifikasi_new->created_at = $now;
                    $rtl_verifikasi_new->created_by = $idUser;
                    $status = $rtl_verifikasi_new->save();
                    $rowCreated[] = $status;
                } else {
                    $rtl_verifikasi->status = $params['status'];
                    $rtl_verifikasi->updated_by = $idUser;
                    $rtl_verifikasi->updated_at = $now;
                    $status = $rtl_verifikasi->save();
                    $rowUpdated[] = $status;
                }

                // config notify
                if ($params['status'] === 'perlu_perbaikan') {
                    $notifyMessages[] = [
                        'body_message_id' => '[SPI] Anda perlu melakukan perbaikan!',
                        'body_message_en' => '[SPI] You need to correct it!'
                    ];
                } else if ($params['status'] === 'on_progress') {
                    $notifyMessages[] = [
                        'body_message_id' => '[SPI] Anda Perlu Lakukan Pengkinian Data!',
                        'body_message_en' => '[SPI] You need to update your data!'
                    ];
                }
            }


            // config notify
            if (count($notifyMessages) > 0) {
                $userIds = $this->rawQuery("
                    SELECT
                        um_id
                    FROM
                        jaga.um_user uu
                    JOIN
                        users_roles ur ON ur.user_id = uu.um_id
                    WHERE
                        uu.id_instansi = (
                            SELECT
                                id_instansi
                            FROM
                                jaga.um_user
                            WHERE
                                um_id = " . $rtl->created_by . "
                        ) AND
                        ur.role_id = (
                            SELECT
                                id
                            FROM
                                jaga.roles
                            WHERE
                                name = 'spi_admin_klop'
                        )
                ");

                $receiverIds = [];
                if (!empty($userIds) && count($userIds) > 0) {
                    foreach ($userIds as $user) {
                        $receiverIds[] = $user['um_id'];
                    }
                }

                if (count($receiverIds) > 0) {
                    foreach($notifyMessages as $message) {
                        NotificationHelper::batchSend(
                            $receiverIds, // receiver ids
                            // $rtl->created_by, // receiver id
                            $this->shared->user->um_id, // sender id
                            $message['body_message_id'], // message in bahasa
                            NotificationHelper::CATEGORY_SPI, // action
                            $rtl->id, // action_id
                            '/spi/formulir-monitoring-spi', // action to go
                            NotificationHelper::CATEGORY_SPI, // category notify
                            $message['body_message_en'] // message in english
                        );
                    }
                }
            }
        }

        $result = [
            'created' => $rowCreated,
            'updated' => $rowUpdated
        ];

        return new SimpleResponse($result);
    }

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

        $this->checkOneOfScopes(['spi.manage_master_data']);

        $this->checkIsRtlVerificator();

        date_default_timezone_set('Asia/Jakarta');

        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $rawBody,
            [
                'id_user' => ['validators' => ['required', 'digit']],
                'rtl_ids' => ['validators' => ['required']]
            ]
        );

        if (empty($this->getIdInstansi())) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User bukan admin instansi');
        }

        $idUser = $params['id_user'] ?? $this->shared->user->um_id;
        $verifikator = UmUser::findFirst($idUser);
        if (empty($verifikator)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Verifikator tidak ditemukan');
        }

        $now = date('Y-m-d H:i:s');
        $rowDeleted = [];

        if (count($params['rtl_ids']) > 0) {
            foreach($params['rtl_ids'] as $id) {
                $rtl = SpiRtl::findFirst($id);
                if (empty($rtl)) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data RTL tidak ditemukan');
                }
        
                if (!empty($rtl->deleted_at)) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data RTL sudah terhapus');
                }

                $rtl_verifikasi = SpiRtlVerifikasi::findFirst([
                    'conditions' => 'id_rtl = :id_rtl: AND deleted_at IS NULL',
                    'bind' => [
                        'id_rtl' => $rtl->id
                    ]
                ]);

                $status = null;
                $now = date('Y-m-d H:i:s');
                if (!empty($rtl_verifikasi)) {
                    $rtl_verifikasi->deleted_by = $idUser;
                    $rtl_verifikasi->deleted_at = $now;
                    $status = $rtl_verifikasi->save();
                    $rowDeleted[] = $status;
                }
            }

            $result = [
                'deleted' => $rowDeleted
            ];

            return new SimpleResponse($result);
        }


        $rtl_verifikasi = SpiRtlVerifikasi::findFirst($id);
    }

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

        $this->checkOneOfScopes(['spi.manage_master_data']);

        $this->checkIsRtlVerificator();

        date_default_timezone_set('Asia/Jakarta');

        $rtl_verifikasi = SpiRtlVerifikasi::findFirst($id);

        if (empty($rtl_verifikasi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Data verifikasi tidak ditemukan');
        }

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

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

        $rtl_verifikasi->deleted_at = $now;
        $rtl_verifikasi->deleted_by = $this->shared->user->um_id;
        $status = $rtl_verifikasi->save();

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

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

        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.observer_rtl']);

        $this->checkIsRtlVerificator();

        date_default_timezone_set('Asia/Jakarta');

        $rawBody = $this->request->get();
        $params = $this->validate(
            $rawBody,
            [
                'tahun' => ['validators' => ['required']],
                'id_instansi' => ['validators' => []]
            ]
        );
        
        $query = "
            WITH data_progress AS (
                SELECT
                    i.id,
                    i.nama AS pemda,
                    (
                        SELECT
                            count(rtl.id)
                        FROM
                            jaga.spi_rtl rtl
                        WHERE
                            rtl.tahun = :tahun_rtl AND
                            rtl.id_instansi = i.id AND
                            rtl.deleted_at IS NULL
                    ) AS jumlah_rencana_aksi,
                    (
                        SELECT
                            count(rtl.id)
                        FROM
                            jaga.spi_rtl rtl
                        WHERE
                            rtl.tahun = :tahun_dok_dukung AND
                            rtl.id_instansi = i.id AND
                            rtl.upload_dok_pendukung IS NOT NULL AND
                            rtl.deleted_at IS NULL
                    ) AS data_dukung_terlapor,
                    (
                        SELECT
                            ROUND(
                                (
                                    COALESCE(
                                        (
                                            SELECT
                                                count(rtl.id)
                                            FROM
                                                jaga.spi_rtl rtl
                                            WHERE
                                                rtl.tahun = :tahun_dok_dukung_1 AND
                                                rtl.id_instansi = i.id AND
                                                rtl.upload_dok_pendukung IS NOT NULL AND
                                                rtl.deleted_at IS NULL
                                        ), 0
                                    ) * 1.0 / NULLIF(
                                        (
                                            SELECT
                                                count(rtl.id)
                                            FROM
                                                jaga.spi_rtl rtl
                                            WHERE
                                                rtl.tahun = :tahun_rtl_1 AND
                                                rtl.id_instansi = i.id AND
                                                rtl.deleted_at IS NULL
                                        ), 0
                                    )
                                ) * 100, 0
                            )
                        ) AS progress
                    FROM
                        jaga.instansi i
                    WHERE
                    (
                        SELECT
                            id
                        FROM
                            jaga.spi_rtl rtl
                        WHERE
                            rtl.id_instansi = i.id AND
                            rtl.deleted_at IS NULL
                        LIMIT
                            1
                    ) IS NOT NULL
            )

            SELECT
                *
            FROM
                data_progress
            ORDER BY
                CASE WHEN progress = 0 THEN 1 ELSE 0 END,
                progress DESC
        ";

        $binds = [
            'tahun_rtl' => $params['tahun'],
            'tahun_dok_dukung' => $params['tahun'],
            'tahun_dok_dukung_1' => $params['tahun'],
            'tahun_rtl_1' => $params['tahun']
        ];

        if (!empty($params['id_instansi'])) {
            $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 terhapus');
            }

            if (strpos(strtolower($instansi->nama), 'pemerintah provinsi') === false) {
                // kabkot OR KL
                $query = "
                    WITH data_progress AS (
                        SELECT
                            i.id,
                            i.nama AS pemda,
                            (
                                SELECT
                                    count(rtl.id)
                                FROM
                                    jaga.spi_rtl rtl
                                WHERE
                                    rtl.tahun = :tahun_rtl AND
                                    rtl.id_instansi = i.id AND
                                    rtl.deleted_at IS NULL
                            ) AS jumlah_rencana_aksi,
                            (
                                SELECT
                                    count(rtl.id)
                                FROM
                                    jaga.spi_rtl rtl
                                WHERE
                                    rtl.tahun = :tahun_dok_dukung AND
                                    rtl.id_instansi = i.id AND
                                    rtl.upload_dok_pendukung IS NOT NULL AND
                                    rtl.deleted_at IS NULL
                            ) AS data_dukung_terlapor,
                            (
                                SELECT
                                    ROUND(
                                        (
                                            COALESCE(
                                                (
                                                    SELECT
                                                        count(rtl.id)
                                                    FROM
                                                        jaga.spi_rtl rtl
                                                    WHERE
                                                        rtl.tahun = :tahun_dok_dukung_1 AND
                                                        rtl.id_instansi = i.id AND
                                                        rtl.upload_dok_pendukung IS NOT NULL AND
                                                        rtl.deleted_at IS NULL
                                                ), 0
                                            ) * 1.0 / NULLIF(
                                                (
                                                    SELECT
                                                        count(rtl.id)
                                                    FROM
                                                        jaga.spi_rtl rtl
                                                    WHERE
                                                        rtl.tahun = :tahun_rtl_1 AND
                                                        rtl.id_instansi = i.id AND
                                                        rtl.deleted_at IS NULL
                                                ), 0
                                            )
                                        ) * 100, 0
                                    )
                            ) AS progress
                        FROM
                            jaga.instansi i
                        WHERE
                            i.id = :id_instansi AND
                            (
                                SELECT
                                    id
                                FROM
                                    jaga.spi_rtl rtl
                                WHERE
                                    rtl.id_instansi = i.id AND
                                    rtl.deleted_at IS NULL
                                LIMIT
                                    1
                            ) IS NOT NULL
                    )

                    SELECT
                        *
                    FROM
                        data_progress
                    ORDER BY
                        CASE WHEN progress = 0 THEN 1 ELSE 0 END,
                        progress DESC
                ";
            } else {
                // pemprov
                $query = "
                    WITH data_progress AS (
                        SELECT
                            i.id,
                            i.nama AS pemda,
                            (
                                SELECT
                                    count(rtl.id)
                                FROM
                                    jaga.spi_rtl rtl
                                WHERE
                                    rtl.tahun = :tahun_rtl AND
                                    rtl.id_instansi = i.id AND
                                    rtl.deleted_at IS NULL
                            ) AS jumlah_rencana_aksi,
                            (
                                SELECT
                                    count(rtl.id)
                                FROM
                                    jaga.spi_rtl rtl
                                WHERE
                                    rtl.tahun = :tahun_dok_dukung AND
                                    rtl.id_instansi = i.id AND
                                    rtl.upload_dok_pendukung IS NOT NULL AND
                                    rtl.deleted_at IS NULL
                            ) AS data_dukung_terlapor,
                            (
                                SELECT
                                    ROUND(
                                        (
                                            COALESCE(
                                                (
                                                    SELECT
                                                        count(rtl.id)
                                                    FROM
                                                        jaga.spi_rtl rtl
                                                    WHERE
                                                        rtl.tahun = :tahun_dok_dukung_1 AND
                                                        rtl.id_instansi = i.id AND
                                                        rtl.upload_dok_pendukung IS NOT NULL AND
                                                        rtl.deleted_at IS NULL
                                                ), 0
                                            ) * 1.0 / NULLIF(
                                                (
                                                    SELECT
                                                        count(rtl.id)
                                                    FROM
                                                        jaga.spi_rtl rtl
                                                    WHERE
                                                        rtl.tahun = :tahun_rtl_1 AND
                                                        rtl.id_instansi = i.id AND
                                                        rtl.deleted_at IS NULL
                                                ), 0
                                            )
                                        ) * 100, 0
                                    )
                            ) AS progress
                        FROM
                            jaga.instansi i
                        WHERE
                            i.id_provinsi = (
                                SELECT
                                    ins.id_provinsi
                                FROM
                                    jaga.instansi ins
                                WHERE
                                    ins.id = :id_instansi
                            ) AND
                            (
                                SELECT
                                    id
                                FROM
                                    jaga.spi_rtl rtl
                                WHERE
                                    rtl.id_instansi = i.id AND
                                    rtl.deleted_at IS NULL
                                LIMIT
                                    1
                            ) IS NOT NULL
                    )

                    SELECT
                        *
                    FROM
                        data_progress
                    ORDER BY
                        CASE WHEN progress = 0 THEN 1 ELSE 0 END,
                        progress DESC
                ";
            }

            $binds['id_instansi'] = $params['id_instansi'];
        }

        if (empty($this->shared->user->id_instansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User bukan admin instansi');
        }

        $rows = $this->rawQuery($query, $binds);


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

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

        $excel->setCellValue("A1", "PEMDA");
        $excel->setCellValue("B1", "JUMLAH RENCANA AKSI");
        $excel->setCellValue("C1", "DATA DUKUNG RENCANA AKSI TELAH DILAPORKAN (Update " . date('j M Y') . ")");
        $excel->setCellValue("D1", "PROGRESS UPLOAD DOK. DUKUNG (Per " . date('j M Y') . ")");

        $headerStyle = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ];

        $excel->getStyle('A1:D1')->applyFromArray($headerStyle);
        $excel->getStyle('A1:D1')->getFont()->setBold(true);

        $excel->getStyle('A1:D1')->getFill()->setFillType(FILL::FILL_SOLID)->getStartColor()->setRGB('61CBF3');

        $rowIndex = 1;

        foreach($rows as $row) {
            $rowIndex++;
            $excel->setCellValue("A$rowIndex", $row['pemda']);
            $excel->setCellValue("B$rowIndex", $row['jumlah_rencana_aksi']);
            $excel->setCellValue("C$rowIndex", $row['data_dukung_terlapor']);
            $excel->setCellValue("D$rowIndex", $row['progress'] . '%');
        }

        $excel->getColumnDimension('A')->setWidth(40);
        $excel->getColumnDimension('B')->setWidth(25);
        $excel->getColumnDimension('C')->setWidth(35);
        $excel->getColumnDimension('D')->setWidth(25);

        $excel->getStyle('A1:W' . $rowIndex)->getAlignment()->setWrapText(true);
        $excel->getStyle('A1:W' . $rowIndex)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $excel->getStyle('A1:W' . $rowIndex)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        for ($row = 2; $row <= $rowIndex; $row++) {
            $excel->getRowDimension($row)->setRowHeight(-1);
        }

        $writer = new Xlsx($spreadsheet);
        $filename = 'Progress-Upload-Data-Dukung-RTL-' . date('Y') . '-' . date('YmdHis') . '.xlsx';
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

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

        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.observer_rtl']);

        $this->checkIsRtlVerificator();

        date_default_timezone_set('Asia/Jakarta');

        $rawBody = $this->request->get();
        $params = $this->validate(
            $rawBody,
            [
                'tahun' => ['validators' => ['required']],
                'id_instansi' => ['validators' => []],
                'status' => ['validators' => []],
                'dimensi' => ['validators' => []]
            ]
        );

        $where = "
            rtl.tahun = :tahun AND rtl.deleted_at IS NULL
        ";

        $binds = [
            'tahun' => $params['tahun']
        ];

        if (!empty($params['id_instansi'])) {
            $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 terhapus');
            }

            $where = $where . " AND rtl.id_instansi = :id_instansi";
            $binds['id_instansi'] = $params['id_instansi'];
        }

        if (empty($this->shared->user->id_instansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User bukan admin instansi');
        }

        if (!empty($params['dimensi'])) {
            $where = $where . " AND rtl.dimensi = :dimensi";
            $binds['dimensi'] = $params['dimensi'];
        }

        if (!empty($params['status'])) {
            $where = $where . " AND rtl_verifikasi.status = :status";
            $binds['status'] = $params['status'];
        }

        $rows = $this->rawQuery("
            SELECT
                rtl.id,
                rtl.tahun,
                i.nama as nama_instansi,
                rtl.dimensi,
                rtl.rekomendasi,
                rtl.rencana_aksi,
                rtl.kriteria_keberhasilan,
                rtl.ukuran_keberhasilan,
                rtl.data_dukung,
                rtl.penanggung_jawab,
                rtl.waktu_pelaksanaan,
                rtl.risiko,
                rtl.updated_at,
                admin_klpd.nama as editor,
                CASE
                    WHEN rtl.upload_dok_pendukung IS NULL THEN 'FALSE'
                    ELSE 'TRUE'
                END AS sudah_upload_dok_pendukung,
                rtl.upload_dok_pendukung,
                CASE
                    WHEN array_length(string_to_array(rtl.upload_dok_pendukung, ','), 1) > 0
                    THEN array_length(string_to_array(rtl.upload_dok_pendukung, ','), 1)
                    ELSE 0
                END AS total_dok_pendukung,
                rtl.capaian,
                rtl_verifikasi.created_at as tgl_verifikasi,
                pengguna.nama as verifikator,
                rtl_verifikasi.progress,
                rtl_verifikasi.catatan,
                CASE
                    WHEN rtl_verifikasi.status = 'perlu_perbaikan' THEN 'Perlu Perbaikan'
                    WHEN rtl_verifikasi.status = 'on_progress' THEN 'On Progress'
                    WHEN rtl_verifikasi.status = 'selesai' THEN 'Selesai'
                    ELSE ''
                END AS status,
                rtl_verifikasi.updated_at as tgl_update_verifikasi,
                verifikator_update.nama as update_verificator,
                CASE
                    WHEN rtl.is_updated_q3 = 'f' THEN 'FALSE'
                    ELSE 'TRUE'
                END AS is_updated_q3,
                rtl.q3_updated_at,
                CASE
                    WHEN rtl.is_updated_q4 = 'f' THEN 'FALSE'
                    ELSE 'TRUE'
                END AS is_updated_q4,
                rtl.q4_updated_at
            FROM
                jaga.spi_rtl rtl
            LEFT JOIN
                jaga.spi_rtl_verifikasi rtl_verifikasi ON rtl_verifikasi.id_rtl = rtl.id
                AND rtl_verifikasi.deleted_at IS NULL
            JOIN
                jaga.instansi i ON i.id = rtl.id_instansi
            LEFT JOIN
                jaga.um_user pengguna ON pengguna.um_id = rtl_verifikasi.id_user
            LEFT JOIN
                jaga.um_user admin_klpd ON admin_klpd.um_id = rtl.updated_by
            LEFT JOIN
                jaga.um_user verifikator_update ON verifikator_update.um_id = rtl_verifikasi.updated_by
            WHERE
                " . $where . "
            ORDER BY
                i.nama ASC
        ", $binds);

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

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

        $excel->setCellValue("A1", "No");
        $excel->setCellValue("B1", "Tahun");
        $excel->setCellValue("C1", "Instansi");
        $excel->setCellValue("D1", "Dimensi");
        $excel->setCellValue("E1", "Rekomendasi");
        $excel->setCellValue("F1", "Rencana Aksi");
        $excel->setCellValue("G1", "Kriteria Keberhasilan");
        $excel->setCellValue("H1", "Ukuran Keberhasilan");
        $excel->setCellValue("I1", "Data Dukung");
        $excel->setCellValue("J1", "Penanggung Jawab");
        $excel->setCellValue("K1", "Waktu Pelaksanaan/Timeline");
        $excel->setCellValue("L1", "Risiko/Kendala Terkait");
        $excel->setCellValue("M1", "Tanggal Ubah");
        $excel->setCellValue("N1", "Editor");
        $excel->setCellValue("O1", "Sudah Upload Dok Pendukung?");
        $excel->setCellValue("P1", "Nama File Pendukung");
        $excel->setCellValue("Q1", "Jumlah File Pendukung");
        
        $excel->setCellValue("R1", "Capaian/Claim Progress (%)");
        
        $excel->setCellValue("S1", "Tanggal Verifikasi");
        $excel->setCellValue("T1", "PIC Verifikator");
        $excel->setCellValue("U1", "Verifikasi Progress (%)");
        $excel->setCellValue("V1", "Catatan Verifikator");
        $excel->setCellValue("W1", "Status");
        $excel->setCellValue("X1", "Tanggal Update Verifikasi");
        $excel->setCellValue("Y1", "Verifikasi Editor");

        $excel->setCellValue("Z1", "Telah Update Q3");
        $excel->setCellValue("AA1", "Tanggal Update Q3");
        $excel->setCellValue("AB1", "Telah Update Q4");
        $excel->setCellValue("AC1", "Tanggal Update Q4");
        
        $excel->setCellValue("AD1", "ID Renaksi");

        $headerStyle = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
            ],
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['rgb' => '000000']
                ]
            ]
        ];

        $excel->getStyle('A1:AD1')->applyFromArray($headerStyle);

        $excel->getStyle('A1:Q1')->getFill()->setFillType(FILL::FILL_SOLID)->getStartColor()->setRGB('C6E0B4');
        $excel->getStyle('R1')->getFill()->setFillType(FILL::FILL_SOLID)->getStartColor()->setRGB('61CBF3');
        $excel->getStyle('S1:Y1')->getFill()->setFillType(FILL::FILL_SOLID)->getStartColor()->setRGB('FFFF00');
        $excel->getStyle('Z1:AD1')->getFill()->setFillType(FILL::FILL_SOLID)->getStartColor()->setRGB('9400FF');

        $rowIndex = 1;
        $no = 1;

        foreach($rows as $row) {
            $rowIndex++;
            $excel->setCellValue("A$rowIndex", $no);
            $excel->setCellValue("B$rowIndex", $row['tahun']);
            $excel->setCellValue("C$rowIndex", $row['nama_instansi']);
            $excel->setCellValue("D$rowIndex", $row['dimensi']);
            $excel->setCellValue("E$rowIndex", $row['rekomendasi']);
            $excel->setCellValue("F$rowIndex", $row['rencana_aksi']);
            $excel->setCellValue("G$rowIndex", $row['kriteria_keberhasilan']);
            $excel->setCellValue("H$rowIndex", $row['ukuran_keberhasilan']);
            $excel->setCellValue("I$rowIndex", $row['data_dukung']);
            $excel->setCellValue("J$rowIndex", $row['penanggung_jawab']);
            $excel->setCellValue("K$rowIndex", $row['waktu_pelaksanaan']);
            $excel->setCellValue("L$rowIndex", $row['risiko']);
            $excel->setCellValue("M$rowIndex", $row['updated_at']);
            $excel->setCellValue("N$rowIndex", $row['editor']);
            $excel->setCellValue("O$rowIndex", $row['sudah_upload_dok_pendukung']);
            $excel->setCellValue("P$rowIndex", $row['upload_dok_pendukung']);
            $excel->setCellValue("Q$rowIndex", $row['total_dok_pendukung']);

            $excel->setCellValue("R$rowIndex", $row['capaian']);

            $excel->setCellValue("S$rowIndex", $row['tgl_verifikasi']);
            $excel->setCellValue("T$rowIndex", $row['verifikator']);
            $excel->setCellValue("U$rowIndex", $row['progress']);
            $excel->setCellValue("V$rowIndex", $row['catatan']);
            $excel->setCellValue("W$rowIndex", $row['status']);
            $excel->setCellValue("X$rowIndex", $row['tgl_update_verifikasi']);
            $excel->setCellValue("Y$rowIndex", $row['update_verificator']);

            $excel->setCellValue("Z$rowIndex", $row['is_updated_q3']);
            $excel->setCellValue("AA$rowIndex", $row['q3_updated_at']);
            $excel->setCellValue("AB$rowIndex", $row['is_updated_q4']);
            $excel->setCellValue("AC$rowIndex", $row['q4_updated_at']);

            $excel->setCellValue("AD$rowIndex", $row['id']);

            $no++;
        }

        $excel->getColumnDimension('A')->setWidth(5);
        $excel->getColumnDimension('B')->setWidth(10);
        $excel->getColumnDimension('C')->setWidth(20);
        $excel->getColumnDimension('D')->setWidth(20);
        $excel->getColumnDimension('E')->setWidth(30);
        $excel->getColumnDimension('F')->setWidth(30);
        $excel->getColumnDimension('G')->setWidth(25);
        $excel->getColumnDimension('H')->setWidth(25);
        $excel->getColumnDimension('I')->setWidth(30);
        $excel->getColumnDimension('J')->setWidth(20);
        $excel->getColumnDimension('K')->setWidth(15);
        $excel->getColumnDimension('L')->setWidth(20);
        $excel->getColumnDimension('M')->setWidth(20);
        $excel->getColumnDimension('N')->setWidth(20);
        $excel->getColumnDimension('O')->setWidth(20);
        $excel->getColumnDimension('P')->setWidth(50);
        $excel->getColumnDimension('Q')->setWidth(20);

        $excel->getColumnDimension('R')->setWidth(20);

        $excel->getColumnDimension('S')->setWidth(20);
        $excel->getColumnDimension('T')->setWidth(20);
        $excel->getColumnDimension('U')->setWidth(15);
        $excel->getColumnDimension('V')->setWidth(40);
        $excel->getColumnDimension('W')->setWidth(15);
        $excel->getColumnDimension('X')->setWidth(30);
        $excel->getColumnDimension('Y')->setWidth(20);

        $excel->getColumnDimension('Z')->setWidth(20);
        $excel->getColumnDimension('AA')->setWidth(20);
        $excel->getColumnDimension('AB')->setWidth(20);
        $excel->getColumnDimension('AC')->setWidth(20);
        $excel->getColumnDimension('AD')->setWidth(20);

        $excel->getStyle('A1:AD' . $rowIndex)->getAlignment()->setWrapText(true);
        $excel->getStyle('A1:AD' . $rowIndex)->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);

        for ($row = 2; $row <= $rowIndex; $row++) {
            $excel->getRowDimension($row)->setRowHeight(-1);
        }

        $writer = new Xlsx($spreadsheet);
        $filename = 'Rencana-Aksi-' . date('Y') . '-' . date('YmdHis') . '.xlsx';
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

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

        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data', 'spi.view_pemda_data']);

        $getParams = $this->request->get();
        $getParams['tahun'] = array_key_exists('tahun', $getParams) ? $getParams['tahun'] : null;
        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : null;
        $getParams['type'] = array_key_exists('type', $getParams) ? $getParams['type'] : null;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : null;
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['digit']],
                'id_instansi' => ['validators' => ['digit']],
                'type' => ['validators' => []],
                'keyword' => ['validators' => []],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']]
            ]
        );

        $service = new SpiMonitoringService($this);
        return $service->tableFormDetails($params);
    }

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

        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data', 'spi.view_pemda_data']);

        $getParams = $this->request->get();
        $getParams['tahun'] = array_key_exists('tahun', $getParams) ? $getParams['tahun'] : null;
        $getParams['id_instansi'] = array_key_exists('id_instansi', $getParams) ? $getParams['id_instansi'] : null;
        $getParams['type'] = array_key_exists('type', $getParams) ? $getParams['type'] : null;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : null;
        $params = $this->validate(
            $getParams,
            [
                'tahun' => ['validators' => ['digit']],
                'id_instansi' => ['validators' => ['digit']],
                'type' => ['validators' => []],
                'keyword' => ['validators' => []],
            ]
        );

        $service = new SpiMonitoringService($this);
        return $service->exportTableFormDetailsAsExcel($params);

    }

    public function createTarget()
    {
        $this->checkIsAuthenticated();
        $this->checkScopes(['spi.view_klop_data']);

        $rawBody = $this->request->getJsonRawBody(true);
        $rawBody['template_id'] = array_key_exists('template_id', $rawBody) ? $rawBody['template_id'] : 0;
        $body = $this->validate($rawBody, [
            'template_id' => ['validators' => ['digit']],
        ]);

        $this->db->begin();

        try {
            $service = new SpiMonitoringService($this, $body['template_id']);
            $result = $service->createTarget();

            $this->db->commit();

            return new SimpleResponse($result);
        }
        catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function getListFormDetail($id_form) {
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data', 'spi.view_pemda_data']);

        $service = new SpiMonitoringService($this);
        $result = $service->getListFormDetail($id_form);

        return new SimpleResponse($result);
    }

    public function retrieveFormDetail($id) {
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data', 'spi.view_pemda_data']);

        $service = new SpiMonitoringService($this);
        $result = $service->retrieveFormDetail($id)->toArray();

        $result['attachments'] = $service->getAttachments($id);

        return new SimpleResponse($result);
    }

    public function saveFormDetail($id) {
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data']);

        $postParams = $this->request->getPost();
        $post = $this->validate($postParams, [
            'rencana_aksi' => ['validators' => []],
            'unit_kerja_pj' => ['validators' => []],
            'id_area_intervensi' => ['validators' => ['digit']],
            'kriteria_keberhasilan' => ['validators' => []],
            'ukuran_keberhasilan' => ['validators' => []],
            'b03' => ['validators' => []],
            'capaian_b03' => ['validators' => ['numeric']],
            'b06' => ['validators' => []],
            'capaian_b06' => ['validators' => ['numeric']],
            'b09' => ['validators' => []],
            'capaian_b09' => ['validators' => ['numeric']],
            'b12' => ['validators' => []],
            'capaian_b12' => ['validators' => ['numeric']],
        ]);

        $this->db->begin();

        try {
            $service = new SpiMonitoringService($this);
            $service->saveFormDetail($id, $post);

            $this->db->commit();

            return new SimpleResponse($id);
        }
        catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function uploadAttachment() {
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data']);

        try {
            $service = new SpiMonitoringService($this);
            $attachment = $service->saveFile();

            return new SimpleResponse($attachment);
        }
        catch (Exception $e) {
            throw $e;
        }
    }

    public function updateDetailAttachment($id) {
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data']);
        $rawBody = $this->request->getJsonRawBody(true);

        $body = $this->validate($rawBody, [
            'deleted_files' => ['validators' => []],
            'added_files' => ['validators' => []],
        ]);

        $this->db->begin();

        try {
            $service = new SpiMonitoringService($this);
            $service->updateDetailAttachment($id, $body);

            $this->db->commit();

            return new SimpleResponse($id);
        }
        catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function downloadAttachment($uuid) {
        $this->checkIsAuthenticated();
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data']);

        /** @var MinioService $minioService */
        $minioService = $this->minio;
        $service = new AttachmentService($this->shared, $this->minio);
        if ($minioService->isSecondaryS3Available()) {
            $res = $service->downloadUsingDualS3(
                'spi-monitoring-attachment' . DIRECTORY_SEPARATOR,
                $uuid,
                true,
                LogAttachments::SPIMonitoringAttachment
            );
        } else {
            $res = $service->download(
                'spi-monitoring-attachment' . DIRECTORY_SEPARATOR,
                $uuid,
                true
            );
        }
        return $res;
    }

    public function listTemplate() {
        $this->checkOneOfScopes(['spi.manage_master_data', 'admin.view', 'spi.view_klop_data', 'spi.view_pemda_data']);

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

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

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

        $query = SpiMonTemplate::query();
        $queryWhere = SpiMonTemplate::query()
            ->columns('COUNT(1) AS total');
        if($validatedParams["keyword"] != null) {
            $query = $query
                ->where("nama ILIKE :keyword: OR keterangan ILIKE :keyword:")
                ->bind(["keyword" => '%' . $validatedParams['keyword'] . '%']);
            $queryWhere = $queryWhere
                ->where("nama ILIKE :keyword: OR keterangan ILIKE :keyword:")
                ->bind(["keyword" => '%' . $validatedParams['keyword'] . '%']);
        }

        $result = $query
            ->limit($validatedParams['limit'], $validatedParams['offset'])
            ->orderBy("nama ASC")
            ->execute();
        $count = $queryWhere->execute()->toArray()[0]['total'];

        return new PaginatedResponse($result, $count, $validatedParams['limit'], $validatedParams['offset']);
    }

    public function createTemplate() {
        $this->checkOneOfScopes(['spi.manage_master_data', 'admin.view']);

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

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

        //Region Validasi Tahun
        $existingData = SpiMonTemplate::findFirst([
            'tahun = ?0',
            'bind' => [
                $body['tahun']
            ]
        ]);

        if($existingData) throw new ValidationException("Template dengan tahun " . $body['tahun'] . ' sudah ada.');
        //endregion

        $data = new SpiMonTemplate();
        $data->tahun = $body['tahun'];
        $data->nama = $body['nama'];
        $data->keterangan = $body['keterangan'];
        $result = $data->save();

        return new SimpleResponse($result);
    }

    public function updateTemplate($id) {
        $this->checkOneOfScopes(['spi.manage_master_data', 'admin.view']);

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

        $body = $this->validate($rawBody, [
            'nama' => ['validators' => ['required']],
            'keterangan' => ['validators' => []],
        ]);

        $data = SpiMonTemplate::findFirst($id);
        if(!$data) throw new ValidationException("Data Template tidak ditemukan!");
        $data->nama = $body['nama'];
        $data->keterangan = $body['keterangan'];
        $result = $data->save();

        return new SimpleResponse($result);

    }

    public function import($template_id) {
        $this->checkOneOfScopes(['spi.manage_master_data', 'admin.view']);
//        AuthHelper::validateToolsKey($this->request, $this->config);

        $template = SpiMonTemplate::findFirst($template_id);
        if(!$template)
            throw new ValidationException("Template tidak ditemukan!");

        $countExistingData = SpiMonRekomendasiItem::count([
            'id_mon_template = ?0',
            'bind' => [
                $template_id
            ]
        ]);

        $existingItemRekomendasiInstansi = [];

        if($countExistingData > 0) {
            $existingItemRekomendasiInstansi = array_column(SpiMonRekomendasiItem::find([
                "conditions" => "id_mon_template = ?0",
                "bind" => [
                    $template_id
                ]
            ])->toArray(),
            "id_instansi"
            );
        }

        $rekomendasiGroup = SpiMonRekomendasiGroup::find()->toArray();
        $rekomendasiGroupKeyVal = ArrayHelper::asKeyValue($rekomendasiGroup, "name");

        $instansi = Instansi::find([
            'deleted_at IS NULL'
        ])->toArray();
        $instansiKeyVal = ArrayHelper::asKeyValue($instansi, "id");

        $document = $this->getAndValidateImportFile2('document', ['application/vnd.ms-excel', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'], $this->request->getUploadedFiles());

        if(!empty($document)) {
            ini_set('max_execution_time', '1200');
            set_time_limit(1200);

            $fileName = $document->getTempName();
            $spreadSheet = IOFactory::load($fileName);
            $workSheet = $spreadSheet->getActiveSheet();

            $data = [];
            $notInsertedData = [];

            foreach ($workSheet->getRowIterator() as $row) {
                if($row->getRowIndex() > 1) {
                    $rowData = [];
                    $item = new SpiMonRekomendasiItem();
                    $item->tahun = $template->tahun;
                    $item->id_mon_template = $template->id;
                    $instansiId = 0;
                    $rekomData = [];
                    $kode = "";

                    $namaInstitusi = "";
                    $message = "";
                    $isDataValid = true;

                    $cellIterator = $row->getCellIterator();
                    $cellIterator->setIterateOnlyExistingCells(false);
                    foreach ($cellIterator as $cell) {
                        $row = $cell->getRow();
                        $column = $cell->getColumn();
                        $text = $cell->getFormattedValue();

                        switch ($column) {
                            case "B":
                                $namaInstitusi = $text;
                                break;
                            case "C":
                                $kode = $text;
                                if($text != '0') {
                                    if(!array_key_exists($text, $instansiKeyVal)) {
                                        $isDataValid = false;
                                        $message = "ID instansi tidak ada di JAGA";
                                    }
                                    else {
                                        $instansiId = $text;
                                    }
                                }
                                else {
                                    $instansiId = 0;
                                }

                                if($isDataValid && in_array($instansiId, $existingItemRekomendasiInstansi)) {
                                    $isDataValid = false;
                                    $message = "Data Rekomendasi untuk Instansi \"" . $namaInstitusi. "\" sudah ada di Database. Data Instansi ini tidak akan ditambahkan dan tidak akan mengubah data yg sudah ada.";
                                }
                                break;
                            case "F":
                                $rekomData[1]['nama'] = $text;
                                break;
                            case "G":
                                if(!array_key_exists($text, $rekomendasiGroupKeyVal)) {
                                    $isDataValid = false;
                                    $message = "Nilai Risiko tidak ada di database [" . $text . "]";
                                }
                                else {
                                    $rekomData[1]['id_group'] = $rekomendasiGroupKeyVal[$text]['id'];
                                }
                                break;
                            case "H":
                                $rekomData[2]['nama'] = $text;
                                break;
                            case "I":
                                if(!array_key_exists($text, $rekomendasiGroupKeyVal)) {
                                    $isDataValid = false;
                                    $message = "Nilai Risiko tidak ada di database [" . $text . "]";
                                }
                                else {
                                    $rekomData[2]['id_group'] = $rekomendasiGroupKeyVal[$text]['id'];
                                }
                                break;
                            case "J":
                                $rekomData[3]['nama'] = $text;
                                break;
                            case "K":
                                if(!array_key_exists($text, $rekomendasiGroupKeyVal)) {
                                    $isDataValid = false;
                                    $message = "Nilai Risiko tidak ada di database [" . $text . "]";
                                }
                                else {
                                    $rekomData[3]['id_group'] = $rekomendasiGroupKeyVal[$text]['id'];
                                }
                                break;
                            case "L":
                                $rekomData[4]['nama'] = $text;
                                break;
                            case "M":
                                if(!array_key_exists($text, $rekomendasiGroupKeyVal)) {
                                    $isDataValid = false;
                                    $message = "Nilai Risiko tidak ada di database [" . $text . "]";
                                }
                                else {
                                    $rekomData[4]['id_group'] = $rekomendasiGroupKeyVal[$text]['id'];
                                }
                                break;
                            case "N":
                                $rekomData[5]['nama'] = $text;
                                break;
                            case "O":
                                if(!array_key_exists($text, $rekomendasiGroupKeyVal)) {
                                    $isDataValid = false;
                                    $message = "Nilai Risiko tidak ada di database [" . $text . "]";
                                }
                                else {
                                    $rekomData[5]['id_group'] = $rekomendasiGroupKeyVal[$text]['id'];
                                }
                                break;
                            case "P":
                                $rekomData[6]['nama'] = $text;
                                break;
                            case "Q":
                                if(!array_key_exists($text, $rekomendasiGroupKeyVal)) {
                                    $isDataValid = false;
                                    $message = "Nilai Risiko tidak ada di database [" . $text . "]";
                                }
                                else {
                                    $rekomData[6]['id_group'] = $rekomendasiGroupKeyVal[$text]['id'];
                                }
                                break;
                            case "R":
                                $rekomData[7]['nama'] = $text;
                                break;
                            case "S":
                                if(!array_key_exists($text, $rekomendasiGroupKeyVal)) {
                                    $isDataValid = false;
                                    $message = "Nilai Risiko tidak ada di database [" . $text . "]";
                                }
                                else {
                                    $rekomData[7]['id_group'] = $rekomendasiGroupKeyVal[$text]['id'];
                                }
                                break;
                            case "T":
                                $rekomData[8]['nama'] = $text;
                                break;
                            case "U":
                                if(!array_key_exists($text, $rekomendasiGroupKeyVal)) {
                                    $isDataValid = false;
                                    $message = "Nilai Risiko tidak ada di database [" . $text . "]";
                                }
                                else {
                                    $rekomData[8]['id_group'] = $rekomendasiGroupKeyVal[$text]['id'];
                                }
                                break;
                            case "V":
                                $rekomData[9]['nama'] = $text;
                                break;
                            case "W":
                                if(!array_key_exists($text, $rekomendasiGroupKeyVal)) {
                                    $isDataValid = false;
                                    $message = "Nilai Risiko tidak ada di database [" . $text . "]";
                                }
                                else {
                                    $rekomData[9]['id_group'] = $rekomendasiGroupKeyVal[$text]['id'];
                                }
                                break;

                        }

                        if(!$isDataValid) {
                            break;
                        }
                    }

                    if($isDataValid) {
                        foreach ($rekomData as $rekomItem) {
                            $data[] = [
                                'id_group' => $rekomItem['id_group'],
                                'id_instansi' => $instansiId,
                                'tahun' => $template->tahun,
                                'name' => $rekomItem['nama'],
                                'id_mon_template' => $template->id,
                                'kode' => $kode
                            ];
                        }
                    }
                    else {
                        $notInsertedData[] = [
                            'row' => $row,
                            'instansi' => $namaInstitusi,
                            'kode' => $kode,
                            'cause' => $message
                        ];
                    }
                }
            }

            $this->db->begin();
            try {
                foreach ($data as $item) {
                    $insertData = new SpiMonRekomendasiItem();
                    $insertData->assign($item);
                    $insertData->save();
                }
                $this->db->commit();
            }
            catch (Exception $e) {
                $this->db->rollback();
                throw $e;
            }

            return new SimpleResponse([
                'failed_data' => $notInsertedData
            ]);
        }
        else {
            throw new ValidationException("Document is empty!");
        }
    }

    public function listRekomendasi($template_id) {
        $this->checkOneOfScopes(['spi.manage_master_data', 'admin.view']);

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

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

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

        $select = "
            SpiMonRekomendasiItem.id, 
            SpiMonRekomendasiItem.id_group, 
            SpiMonRekomendasiItem.id_instansi, 
            SpiMonRekomendasiItem.tahun, 
            SpiMonRekomendasiItem.name, 
            SpiMonRekomendasiItem.id_mon_template, 
            SpiMonRekomendasiItem.kode, 
            SpiMonRekomendasiGroup.name AS group_name, 
            Instansi.nama AS instansi_name,
            SpiMonTemplate.nama AS template_name 
            ";
        $query = SpiMonRekomendasiItem::query()
            ->columns($select)
            ->leftJoin("SpiMonRekomendasiGroup", "SpiMonRekomendasiGroup.id = SpiMonRekomendasiItem.id_group")
            ->leftJoin("Instansi", "Instansi.id = SpiMonRekomendasiItem.id_instansi")
            ->leftJoin("SpiMonTemplate", "SpiMonTemplate.id = SpiMonRekomendasiItem.id_mon_template")
        ;

        $conditions = "SpiMonRekomendasiItem.id_mon_template = :id_template:";
        $bind['id_template'] = $template_id;

        $queryWhere = SpiMonRekomendasiItem::query()
            ->columns('COUNT(1) AS total');
        if($validatedParams["keyword"] != null) {
            $conditions .= " AND SpiMonRekomendasiItem.name ILIKE :keyword:";
            $bind['keyword'] = '%' . $validatedParams['keyword'] . '%';
        }

        $result = $query
            ->where($conditions)
            ->bind($bind)
            ->limit($validatedParams['limit'], $validatedParams['offset'])
            ->orderBy("SpiMonRekomendasiItem.id ASC")
            ->execute()->toArray();

        foreach ($result as $key => $item) {
            if($item['id_instansi'] == 0) {
                $result[$key]['instansi_name'] = 'Nasional';
            }
        }

        $count = $queryWhere->execute()->toArray()[0]['total'];

        return new PaginatedResponse($result, $count, $validatedParams['limit'], $validatedParams['offset']);
    }

    public function updateRekomendasiItem($id) {
        $this->checkOneOfScopes(['spi.manage_master_data', 'admin.view']);

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

        $body = $this->validate($rawBody, [
            'name' => ['validators' => ['required']],
        ]);

        $data = SpiMonRekomendasiItem::findFirst($id);

        if(!$data) throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Data Item Rekomendasi tidak ditemukan!");

        $data->name = $body['name'];
        $status = $data->save();

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

            $error_messages = [];

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

            throw new ValidationException($error_messages);
        } else {
            $qUpdate = "UPDATE jaga.spi_mon_form_detail SET rekomendasi_item_name = :rekomendasi_item_name WHERE id_rekomendasi_item = :id_rekomendasi_item";
            $bind = [
                "rekomendasi_item_name" => $body["name"],
                "id_rekomendasi_item" => $id
            ];

            $this->rawQuery($qUpdate,$bind);

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

    public function retrieveRekomendasiItem($id) {
        $this->checkOneOfScopes(['spi.manage_master_data', 'admin.view']);

        $data = SpiMonRekomendasiItem::findFirst($id);

        if(!$data) throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Data Item Rekomendasi tidak ditemukan!");

        return new SimpleResponse($data);
    }
}