<?php

use PhpOffice\PhpSpreadsheet\IOFactory;

class SPIHasilKLPDController extends BaseController
{

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

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

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

        $kp = SPIHasilKLPD::find([
            'conditions' => "(tahun ilike :keyword: or nama_klpd ilike :keyword:)
                and (tahun = :tahun: or :tahun: = '-9') and is_active = true
                and (hasil_spi >= :hasil_start: or :hasil_start: = -9)
                and (hasil_spi <= :hasil_end: or :hasil_end: = -9)",
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%',
                'hasil_start' => $getParams['hasil_start'],
                'hasil_end' => $getParams['hasil_end'],
                'tahun' => $getParams['tahun']
            ],
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ]);


        $total_record = SPIHasilKLPD::find([
            'conditions' => "(tahun ilike :keyword: or nama_klpd ilike :keyword:)
                and (tahun = :tahun: or :tahun: = '-9') and is_active = true
                and (hasil_spi >= :hasil_start: or :hasil_start: = -9)
                and (hasil_spi <= :hasil_end: or :hasil_end: = -9)",
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%',
                'hasil_start' => $getParams['hasil_start'],
                'hasil_end' => $getParams['hasil_end'],
                'tahun' => $getParams['tahun']
            ],
        ])->count();

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

    public function detail($id)
    {
        $kp = SPIHasilKLPD::findFirst([
            'conditions' => 'id = :id: and is_active = true',
            'bind' => [
                'id' => $id
            ]
        ]);

        if (empty($kp)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Hasil SPI KLPD dengan id' . $id . 'tidak diketemukan');
        endif;

        $result = $kp->toDetail();

        return new CollectionPaginatedResponse($result);
    }

    public function getByKLPD($id)
    {
        // $kp = SPIHasilKLPD::findFirst([
        //     'conditions' => 'code_klpd = :id: and is_active = true',
        //     'bind' => [
        //         'id' => $id
        //     ]
        // ]);
        $kp = $this->rawQuery("
            select
                klpd.hasil_spi
            from
                spi_hasil_klpd as klpd
                inner join
                    instansi as ins
                    on
                        klpd.code_klpd = ins.id
                        and ins.id_provinsi = $id
            ");

        if (empty($kp)) :
            if ($id == -9) {
                return new CollectionPaginatedResponse(["hasil_spi" => 79.08]);
            } else {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Hasil SPI KLPD dengan id' . $id . 'tidak diketemukan');
            }
        endif;

        // $result = $kp->toDetail();

        return new CollectionPaginatedResponse($kp[0]);
    }

    public function indeksByLevelWilayah() {
        $params = $this->request->get();
        $params = $this->validate($params, [
            'level' => ['validators' => ['required']],
            'year' => ['validators' => ['required', 'digit']],
            'wilayah_id' => ['validators' => ['digit']]
        ]);

        $result = [];
        if ($params["level"] == SPIHasilKLPD::LEVEL_NASIONAL) {
            $result["rerata_nasional"] = $this->getRerataNasional($params["year"]);
        } elseif ($params["level"] == SPIHasilKLPD::LEVEL_PROVINSI) {
            $result["rerata_provinsi"] = $this->getRerataProvinsi($params["year"], $params["wilayah_id"]);
            $result["indeks_instansi"] = $this->getNilaiInstansi($params["year"], $params["wilayah_id"], $params["level"]);
        } elseif ($params["level"] == SPIHasilKLPD::LEVEL_KABUPATEN) {
            $result["indeks_instansi"] = $this->getNilaiInstansi($params["year"], $params["wilayah_id"], $params["level"]);
        } else {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Level tidak valid!");
        }

        return new SimpleResponse($result);
    }

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

        $kp = SPIHasilKLPD::findFirst($id);

        if (empty($kp)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id ' . $id . ' tidak diketemukan');
        endif;
        if (!empty($kp->deleted_at)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id ' . $id . ' mungkin sudah terhapus');
        endif;

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

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

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

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

    public function mapGeojson()
    {
        $id_wilayah = $this->request->get('wilayah_id');
        $level = $this->request->get('level');

        $id_provinsi = null;
        $id_kota_kabupaten = null;
        if ($level == SPIHasilKLPD::LEVEL_PROVINSI) {
            $id_provinsi = $id_wilayah;
        } elseif ($level == SPIHasilKLPD::LEVEL_KABUPATEN) {
            $id_provinsi = substr($id_wilayah, 0, 2);
            $id_kota_kabupaten = $id_wilayah;
        }
        $version = !is_null($this->request->get('version')) ? $this->request->get('version') : getenv("MAP_VERSION");
        $dir = $this->config['application']['publicDir'];
        $geojson = null;
        if ($version == 1) {
            if ($id_provinsi === null) {
                $geojson = json_decode(file_get_contents($dir . '/maps/provinsi.json'));
            } else {
                $geojson = json_decode(file_get_contents($dir . '/maps/kabupaten.json'), true);
                $filter = function ($e) use ($id_provinsi, $id_kota_kabupaten) {
                    return ($id_provinsi === null || $e['properties']['A1CODE'] == $id_provinsi) && ($id_kota_kabupaten === null || $e['properties']['A2CODE'] == $id_kota_kabupaten);
                };
                $geojson['features'] = array_values(array_filter($geojson['features'], $filter));
            }
        } else if ($version == 2) {
            if ($id_provinsi === null) {
                $geojson = json_decode(file_get_contents($dir . '/maps/provinsi_2020.json'), true);
            } else {
                $geojson = json_decode(file_get_contents($dir . '/maps/kabupaten_2020.json'), true);

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

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

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

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

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

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

        return new SimpleResponse($geojson);
    }

    public function summaryMapSum()
    {
        $req = $this->request;
        $this->validate(
            $req->get(),
            [
                'level' => ['validators' => ['required', [
                    'name' => 'max_string',
                    'params' => 20
                ]]],
                'year' => ['validators' => ['required', 'digit']]
            ]
        );

        $level = $this->request->get('level');
        $year = $this->request->get('year');
        $isKorwil = str_contains($req->get('_url'), '/korwil');

        $rows = [
            [
                'year' => $year,
                'rows' => $this->summaryMapYearly($level, $year, true, $isKorwil)
            ]
        ];

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

    public function summaryTable()
    {
        $req = $this->request;
        $this->validate(
            $req->get(),
            [
                'level' => ['validators' => ['required', [
                    'name' => 'max_string',
                    'params' => 20
                ]]],
                'year' => ['validators' => ['required', 'digit']]
            ]
        );

        $level = $this->request->get('level');
        $year = $this->request->get('year');
        $isKorwil = str_contains($req->get('_url'), '/korwil');

        $result = $this->summaryMapYearly($level, $year, false, $isKorwil);
        $rows = [
            [
                'total' => count($result),
                'year' => $year,
                'rows' => $result
            ]
        ];

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

    protected function summaryMapYearly($level, $year, $group_by = false, $isKorwil = false)
    {
        $q_where = 'klpd.tahun = :year';
        $params = compact('year');
        $params['tahun'] = $params['year'];
        $col_level_wilayah_show = 'COALESCE(COALESCE(riw.id_kota_kabupaten, ins.id_kota_kabupaten), COALESCE(riw.id_provinsi, ins.id_provinsi))';
        $qJoin = "";

        if ($level === SPIHasilKLPD::LEVEL_NASIONAL) {
            if ($group_by) {
                $q_where .= ' and ins.id_kota_kabupaten is null and ins.id_tipe = 1';
            }
        } else if ($level === SPIHasilKLPD::LEVEL_PROVINSI) {
            $params['wilayah_id'] = $this->request->get('wilayah_id', 'int', 0);
            $q_where .= " and ((CASE WHEN riw.id IS NULL THEN ins.id_provinsi = :wilayah_id ELSE riw.id_provinsi = :wilayah_id END) or :wilayah_id = 0)";
        } else if ($level === SPIHasilKLPD::LEVEL_KABUPATEN) {
            $col_level_wilayah_show = 'COALESCE(riw.id_kota_kabupaten, ins.id_kota_kabupaten)';
            $params['wilayah_id'] = $this->request->get('wilayah_id', 'int', 0);
            $q_where .= " and ((CASE WHEN riw.id IS NULL THEN ins.id_kota_kabupaten = :wilayah_id ELSE riw.id_kota_kabupaten = :wilayah_id END) or :wilayah_id = 0)";
        } else if ($level === SPIHasilKLPD::LEVEL_KEMENTERIAN) {
            $params['id_instansi'] = $this->request->get('id_instansi', 'int', 0);
            $q_where .= ' and ( ins.id_tipe NOT IN (1,2) or ins.id_tipe IS NULL ) and (ins.id = :id_instansi or :id_instansi = 0)';
        } else {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Invalid Level:' . $level);
        }

        $select = "klpd.*";
        $group_by_stx = "";
        if ($group_by) {
            $select = "(klpd.hasil_spi) as hasil_spi, klpd.nama_klpd as wilayah_name";
        }

        //region Check User Scope, if admin klop/iskorwil = true, only show id_instansi/id_klop/code_klpd
        if($isKorwil) {
            if ($this->hasOneOfScopes(['spi.manage_master_data', 'spi.monitoring', 'spi.view_klop_data'])) {
                if($this->hasScopes(['spi.view_klop_data'])) {
                    $id_instansi = $this->getIdInstansi();
                    if (empty($id_instansi) || $id_instansi == null) throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, "Instansi is not specified!");
                    $params['id_instansi'] = $id_instansi;
                    $q_where .= " AND klpd.code_klpd = :id_instansi";
                }
            }
            else {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, "Not Allowed!");
            }
        }
        //endregion

        $q = "
            select
                $select,
                $col_level_wilayah_show as wilayah_code
            from
                spi_hasil_klpd as klpd
            inner join
                instansi as ins on klpd.code_klpd = ins.id" . $qJoin . "
            left join rel_instansi_wilayah riw on riw.id_instansi = ins.id and :tahun >= riw.start_year and :tahun <= riw.end_year
            where
                $q_where
            $group_by_stx
            ";
        $rows = $this->rawQuery(
            $q,
            $params
        );

        //TODO: Dibawah ini solusi sementara, agar provinsi2 yg tidak ada nilainya tetap bisa diklik di map
        if($year == '2023' && $level == SPIHasilKLPD::LEVEL_NASIONAL) {
            $rows[] = [
                "hasil_spi" => 0,
                "wilayah_name" => "Pemerintah Provinsi Papua Barat Daya",
                "wilayah_code" => 96
            ];
            $rows[] = [
                "hasil_spi" => 0,
                "wilayah_name" => "Pemerintah Provinsi Papua Selatan",
                "wilayah_code" => 93
            ];
            $rows[] = [
                "hasil_spi" => 0,
                "wilayah_name" => "Pemerintah Provinsi Papua Tengah",
                "wilayah_code" => 94
            ];
            $rows[] = [
                "hasil_spi" => 0,
                "wilayah_name" => "Pemerintah Provinsi Papua Pegunungan",
                "wilayah_code" => 95
            ];
        }

        if ($year === '2024' && $level == SPIHasilKLPD::LEVEL_NASIONAL) {
            $rows[] = [
                "hasil_spi" => 0,
                "wilayah_name" => "Pemerintah Provinsi Papua Barat Daya",
                "wilayah_code" => 96
            ];

            $rows[] = [
                "hasil_spi" => 0,
                "wilayah_name" => "Pemerintah Provinsi Sulawesi Tenggara",
                "wilayah_code" => 74
            ];

            $rows[] = [
                "hasil_spi" => 0,
                "wilayah_name" => "Pemerintah Provinsi Papua Selatan",
                "wilayah_code" => 93
            ];

            $rows[] = [
                "hasil_spi" => 0,
                "wilayah_name" => "Pemerintah Provinsi Papua Tengah",
                "wilayah_code" => 94
            ];
            
            $rows[] = [
                "hasil_spi" => 0,
                "wilayah_name" => "Pemerintah Provinsi Papua Pegunungan",
                "wilayah_code" => 95
            ];
        }


        return $rows;
    }

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

        $params = $this->validate(
            $getParams,
            [
                'query' => ['validators' => ['required', [
                    'name' => 'max_string',
                    'params' => 255
                ]]]
            ]
        );

        $query = strtolower($params['query']);
        $dir = $this->config['application']['publicDir'];
        $geojson = json_decode(file_get_contents($dir . '/maps/kabupaten.json'), true);
        $filter = function ($e) use ($query) {
            return (array_key_exists('A1NAME', $e['properties']) && strpos(strtolower($e['properties']['A1NAME']), $query) !== false)
                || (array_key_exists('A2NAME', $e['properties']) && strpos(strtolower($e['properties']['A2NAME']), $query) !== false);
        };
        $mapper = function ($e) {
            return [
                'A1NAME' => $e['properties']['A1NAME'],
                'A2NAME' => $e['properties']['A2NAME'],
                'A1CODE' => $e['properties']['A1CODE'],
                'A2CODE' => $e['properties']['A2CODE']
            ];
        };
        $geojson['features'] = array_values(array_filter($geojson['features'], $filter));
        $result = array_map($mapper, $geojson['features']);

        $wilayah_list = $this->rawQuery('select id, code, name from master_wilayah');
        $wilayah_dict = [];
        for ($i = 0; $i < count($wilayah_list); $i++) {
            $wilayah_dict[$wilayah_list[$i]['code']] = $wilayah_list[$i]['code'];
            $wilayah_dict[$wilayah_list[$i]['code'] . '_name'] = $wilayah_list[$i]['name'];
        }

        for ($i = 0; $i < count($result); $i++) {
            if (array_key_exists($result[$i]['A2CODE'],  $wilayah_dict)) {
                $result[$i]['wilayah_id'] = $wilayah_dict[$result[$i]['A2CODE']];
            }
            if (array_key_exists($result[$i]['A1CODE'],  $wilayah_dict)) {
                $result[$i]['parent_wilayah_id'] = $wilayah_dict[$result[$i]['A1CODE']];
            }

            if (array_key_exists($result[$i]['A2CODE'] . '_name',  $wilayah_dict)) {
                $result[$i]['wilayah_name'] = $wilayah_dict[$result[$i]['A2CODE'] . '_name'];
            }
        }

        return new SimpleResponse($result);
    }

    public function store()
    {
        $this->db->begin();

        try {
             $this->checkScopes(['spi.manage_master_data']);

            $rawBody = $this->request->getJsonRawBody(true);
            $params = $this->validate(
                $rawBody,
                [
                    'tahun' => ['validators' => ['required']],
                    'code_klpd' => ['validators' => ['required']],
                    'hasil_spi' => ['validators' => ['required']],
                ]
            );

            $tahun = $params['tahun'];
            $code_klpd = $params['code_klpd'];
            $total_record = SPIHasilKLPD::find([
                'conditions' => "is_active = true and tahun = '$tahun' and code_klpd = '$code_klpd'"
            ])->count();

            if ($total_record > 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Report pada $tahun Sudah digunakan");
            }

            $instansi = Instansi::findFirst([
                'conditions' => 'id = :id:',
                'bind' => [
                    'id' => $code_klpd
                ]
            ]);

            if ($instansi == null) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Instansi tidak ditemukan");
            }

            $kp = new SPIHasilKLPD();
            $kp->id = null;
            $kp->tahun = $tahun;
            $kp->code_klpd = $code_klpd;
            $kp->nama_klpd = $instansi->nama;

            $kp->hasil_spi = $params['hasil_spi'];

            $kp->is_active = true;
            $kp->created_at = date('Y-m-d H:i:s');
            $kp->created_by = $this->shared->user->um_id;
            $status = $kp->save();

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

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

                throw new ValidationException($error_messages);
            }

            $this->db->commit();
        } catch (\Exception $ex) {
            $this->db->rollback();
            throw $ex;
        }

        return new CollectionPaginatedResponse([$kp]);
    }

    public function update($id)
    {
        $this->db->begin();

        try {
             $this->checkScopes(['spi.manage_master_data']);
            $kp = SPIHasilKLPD::findFirst($id);

            if (empty($kp)) :
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id' . $id . 'tidak ditemukan');
            endif;

            if (!empty($kp->deleted_at)) :
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'id ' . $id  . 'mungkin sudah terhapus');
            endif;

            $rawBody = $this->request->getJsonRawBody(true);
            $params = $this->validate(
                $rawBody,
                [
                    'tahun' => ['validators' => ['required']],
                    'code_klpd' => ['validators' => ['required']],
                    'hasil_spi' => ['validators' => ['required']],
                ]
            );

            $tahun = $params['tahun'];
            $code_klpd = $params['code_klpd'];
            $total_record = SPIHasilKLPD::find([
                'conditions' => "is_active = true and tahun = '$tahun' and code_klpd = '$code_klpd' and id != '$id'"
            ])->count();

            if ($total_record > 0) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Report pada $tahun Sudah digunakan");
            }

            $instansi = Instansi::findFirst([
                'conditions' => 'id = :id:',
                'bind' => [
                    'id' => $code_klpd
                ]
            ]);

            if ($instansi == null) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Instansi tidak ditemukan");
            }

            $kp->tahun = $tahun;
            $kp->code_klpd = $code_klpd;
            $kp->nama_klpd = $instansi->nama;

            $kp->hasil_spi = $params['hasil_spi'];

            $kp->is_active = true;

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

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

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

                throw new ValidationException($error_messages);
            }

            $this->db->commit();
        } catch (\Exception $ex) {
            $this->db->rollback();
            throw $ex;
        }

        return new CollectionPaginatedResponse([$kp]);
    }

    public function getInstansiKementerian() {
        $req = $this->request;
        $this->validate(
            $req->get(),
            [
                'year' => ['validators' => ['required', 'digit']],
                'nama' => ['validators' => ['required', [
                    'name' => 'max_string',
                    'params' => 255
                ]]]
            ]
        );

        $tahun = $this->request->get('year');
        $nama = $this->request->get('nama');
        $bind = [
            'nama' => '%' . $nama . '%',
            'tahun' => $tahun
        ];

        $result = $this->rawQuery("
            SELECT DISTINCT(s.code_klpd), sig.id_instansi_gol, s.nama_klpd, sie.kementerian_koordinator
            FROM jaga.spi_hasil_klpd s 
                INNER JOIN instansi i ON i.id = s.code_klpd
                LEFT JOIN spi_internal_eksternal sie ON sie.id_instansi = i.id
                LEFT JOIN sig_instansi sig ON s.code_klpd = sig.id_instansi_jaga
            WHERE s.tahun = :tahun AND s.nama_klpd ILIKE :nama AND s.is_active = TRUE AND i.id_tipe <> 1 AND id_tipe <> 2
        ", $bind);

        return new CollectionPaginatedResponse($result);
    }

    public function import() {
        $this->checkScopes(['spi.manage_master_data']);

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

        if (!empty($document)) {
            $inputFileType = IOFactory::identify($document->getTempName());
            $reader = IOFactory::createReader($inputFileType);
            $reader->setReadDataOnly(false);
            $reader->setLoadSheetsOnly(['Sheet1']);

            $spreadsheet = $reader->load($document->getTempName());
            $spreadsheet->setActiveSheetIndexByName('Sheet1');
            $worksheet = $spreadsheet->getActiveSheet();

            $expectedHeaders = [
                'Tahun',
                'Kode KLPD',
                'Nama KLPD',
                'Indeks SPI'
            ];

            $rows = [];
            $headers = null;
            $rowCount = 0;

            foreach ($worksheet->getRowIterator() as $row) {
                $values = [];

                $cellIterator = $row->getCellIterator();
                $cellIterator->setIterateOnlyExistingCells(FALSE); // This loops through all cells,

                foreach ($cellIterator as $cell) {
                    $values[] = $cell->getValue();
                }

                if (count($values) < count($expectedHeaders)) {
                    break;
                }

                if (empty($values[0])) {
                    break;
                }

                if ($rowCount == 0) {
                    $headers = $values;
                } else {
                    $rows[] = $values;
                }

                $rowCount++;
            }

            if (empty($headers)) {
                throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Judul kolom/headers tidak sesuai');
            }

            if (count($headers) < count($expectedHeaders)) {
                throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'Jumlah kolom tidak sesuai');
            }

            if (count($headers) > count($expectedHeaders)) {
                $headers = array_slice($headers, 0, count($expectedHeaders));
            }

            for ($i = 0; $i < count($expectedHeaders); $i++) {
                if ($expectedHeaders[$i] != $headers[$i]) {
                    throw new CustomErrorException(BaseResponse::INVALID_ACTION, "Nama kolom ke-$i tidak valid. Expected: " . $expectedHeaders[$i] . '. Got: ' . $headers[$i]);
                }
            }

            $errors = [];

            $rules = [
                'tahun' => ['validators' => ['required', 'digit']],
                'code_klpd' => ['validators' => ['required', 'digit']],
                'nama_klpd' => ['validators' => []],
                'hasil_spi' => ['validators' => ['required']]
            ];

            $payloads = [];

            foreach ($rows as $key => $row) {
                $rowPayload = [
                    'tahun' => trim($row[0]),
                    'code_klpd' => trim($row[1]),
                    'hasil_spi' => round(trim($row[3]), 2),
                ];

                $validationResult = $this->baseValidate($rowPayload, $rules);

                if (count($validationResult['errors']) > 0) {
                    $errors[] = [
                        'row_id' => $key + 1,
                        'errors' => $validationResult['errors']
                    ];
                }
                $payloads[] = $rowPayload;
            }

            if (count($errors) > 0) {
                throw new ValidationException($errors);
            }

            $fails = [];
            $inserted = $updated = 0;
            $this->db->begin();
            try {
                foreach ($payloads as $key => $row) {
                    $tahun = $row['tahun'];
                    $code_klpd = $row['code_klpd'];

                    $instansi = Instansi::findFirst([
                        'conditions' => 'id = :id:',
                        'bind' => [
                            'id' => $code_klpd
                        ]
                    ]);

                    if ($instansi == null) {
                        $fails[] = [
                            'code_klpd' => $code_klpd,
                            'error_message' => "Instansi tidak ditemukan"
                        ];
                    } else {
                        $newRow = false;
                        $model = SPIHasilKLPD::findFirst([
                            'conditions' => "is_active = true and tahun = '$tahun' and code_klpd = '$code_klpd'"
                        ]);
                        if (!$model) {
                            $model = new SPIHasilKLPD();
                            $model->id = null;
                            $newRow = true;
                        }
                        $model->tahun = $tahun;
                        $model->code_klpd = $code_klpd;
                        $model->nama_klpd = $instansi->nama;

                        $model->hasil_spi = $row['hasil_spi'];

                        $model->is_active = true;
                        $model->created_at = date('Y-m-d H:i:s');
                        $model->created_by = $this->shared->user->um_id;
                        $result = $model->save();

                        if (!$result) {
                            $errorMessage = '';

                            foreach ($model->getMessages() as $message) {
                                $errorMessage .= $message . ", ";
                            }

                            $fails[] = [
                                'code_klpd' => $model->code_klpd,
                                'error_message' => $errorMessage
                            ];
                        } else {
                            if ($newRow) $inserted++;
                            else $updated++;
                        }
                    }
                }
                $this->db->commit();

                return new SimpleResponse([
                    'inserted' => $inserted,
                    'updated' => $updated,
                    'fails' => $fails
                ]);
            } catch (\Exception $e) {
                $this->db->rollback();
                throw $e;
            }
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "File Not Found or Not active");
        }
    }

    public function internalExternal()
    {
        $req = $this->request;
        $validatedRequest = $this->validate(
            $req->get(),
            [
                'level' => ['validators' => ['required', [
                    'name' => 'max_string',
                    'params' => 20
                ]]], //nasional, provinsi, kementerian, kabupaten
                'year' => ['validators' => ['required', 'digit']],
                'wilayah_id' => ['validators' => ['digit']],
                'id_instansi' => ['validators' => ['digit']],
            ]
        );

        $where = [];
        $bind = [];

        $where[] = "tahun = :tahun:";
        $bind['tahun'] = $validatedRequest['year'];

        if(!empty($validatedRequest['id_instansi']) && $validatedRequest['id_instansi'] != null) {
            $where[] = 'id_instansi = :id_instansi:';
            $bind['id_instansi'] = $validatedRequest['id_instansi'];
        }

        switch ($validatedRequest['level']) {
            case SPIHasilKLPD::LEVEL_PROVINSI:
                if(empty($validatedRequest['wilayah_id']) || $validatedRequest['wilayah_id'] == null)
                    throw new ValidationException("Wilayah harus dipilih!");
                $where[] = "id_provinsi = :wilayah_id:";
                $bind["wilayah_id"] = $validatedRequest['wilayah_id'];

                $where[] = "type = :type:";
                $bind["type"] = "INSTANSI";
                break;
            case SPIHasilKLPD::LEVEL_KABUPATEN:
                if(empty($validatedRequest['wilayah_id']) || $validatedRequest['wilayah_id'] == null)
                    throw new ValidationException("Wilayah harus dipilih!");
                $where[] = "id_kota_kabupaten = :wilayah_id:";
                $bind["wilayah_id"] = $validatedRequest['wilayah_id'];

                $where[] = "type = :type:";
                $bind["type"] = "INSTANSI";
                break;

            case SPIHasilKLPD::LEVEL_KEMENTERIAN:
                $where[] = "type = :type:";
                $bind["type"] = "KEMENTERIAN";
                $where[] = "id_instansi IS NOT NULL";
                $this->recordSearchResult($validatedRequest['level'], $validatedRequest['id_instansi']);
                break;

            case SPIHasilKLPD::LEVEL_NASIONAL:
            default:
                $where[] = "type = :type:";
                $bind["type"] = "INSTANSI";
                break;
        }

        $stringWhere = implode(" AND ", $where);

        $data = SpiInternalEksternal::find(
            [
                'conditions' => $stringWhere,
                'bind' => $bind
            ]
        );

        $rerata = [
            'internal' => 0,
            'eksternal' => 0
        ];

        $detailKomponen = [
            'int_suap' => [
                'nama' => "Risiko Suap/Gratifikasi",
                'desc' => 'Persentase pegawai yang menilai adanya suap/ gratifikasi di instansi.',
                'nilai' => 0
            ],
            'int_tradingininfluence' => [
                'nama' => "Risiko Trading in Influence",
                'desc' => 'Persentase pegawai yang menilai adanya pengaruh dari pihak lain terhadap keputusan/ kebijakan/ proses layanan di instansi (termasuk calo, dll).',
                'nilai' => 0
            ],
            'int_pengelolaanpbj' => [
                'nama' => "Risiko Pengelolaan PBJ",
                'desc' => 'Persentase pegawai yang menilai  adanya permasalahan dalam proses Pengadaan Barang dan Jasa di instansi (termasuk nepotisme, gratifikasi dalam pengadaan, dll).',
                'nilai' => 0
            ],
            'int_penyalahgunaanfasilitaskantor' => [
                'nama' => "Risiko Penyalahgunaan Fasilitas Kantor",
                'desc' => 'Persentase pegawai yang menilai adanya penyalahgunaan fasilitas kantor di instansi untuk kepentingan pribadi.',
                'nilai' => 0
            ],
            'int_nepotismepengelolaansdm' => [
                'nama' => "Risiko Nepotisme dalam Pengelolaan SDM",
                'desc' => 'Persentase pegawai yang menilai adanya pengaruh hubungan personal (kekerabatan, dekat dengan pejabat, kesamaan almamater, dll) dalam promosi/ mutasi SDM di instansi.',
                'nilai' => 0
            ],
            'int_jualbelijabatan' => [
                'nama' => "Risiko Jual/Beli Jabatan",
                'desc' => 'Persentase pegawai yang menilai bahwa untuk mendapatkan promosi/ mutasi, pegawai harus memberikan sesuatu kepada atasan terkait di instansi.',
                'nilai' => 0
            ],
            'int_penyalahgunaanperdin' => [
                'nama' => "Risiko Penyalahgunaan Perjalanan Dinas",
                'desc' => 'Persentase pegawai yang menilai adanya penyalahgunaan anggaran untuk kepentingan perjalanan dinas (akomodasi dan transportasi, SPJ untuk honor kegiatan, dll).',
                'nilai' => 0
            ],
            'ext_suap' => [
                'nama' => "Risiko Suap/Gratifikasi",
                'desc' => 'Persentase masyarakat pengguna layanan/ penerima manfaat/ vendor yang menilai adanya suap/ gratifikasi di instansi.',
                'nilai' => 0
            ],
            'ext_pungutanliar' => [
                'nama' => "Risiko Pungutan Liar",
                'desc' => 'Persentase masyarakat pengguna layanan/ penerima manfaat/ vendor yang menilai adanya pegawai yang meminta sesuatu kepada masyarakat (pungutan liar).',
                'nilai' => 0
            ],
            'exp_keberadaanpungutanliar' => [
                'nama' => "Keberadaan Pungutan Liar",
                'desc' => 'Persentase narasumber ahli/ pemangku kepentingan yang menilai adanya pegawai yang meminta sesuatu kepada masyarakat (pungutan liar).',
                'nilai' => 0
            ],
            'exp_kualitastransparansilayanan' => [
                'nama' => "Kualitas Transparansi Layanan",
                'desc' => 'Persentase narasumber ahli/ pemangku kepentingan yang menilai buruk kualitas transparansi informasi alur proses layanan/ pelaksanaan tugas di instansi.',
                'nilai' => 0
            ],
            'exp_kualitaspengelolaanpbj' => [
                'nama' => "Kualitas Pengelolaan PBJ",
                'desc' => 'Persentase narasumber ahli/ pemangku kepentingan yang menilai buruk kualitas pengelolaan Pengadaan Barang dan Jasa di instansi.',
                'nilai' => 0
            ],
        ];

        $count = 0;
        foreach ($data as $row) {
            $count++;
            $rerata['internal'] += ($row->int_suap + $row->int_tradingininfluence + $row->int_pengelolaanpbj + $row->int_penyalahgunaanfasilitaskantor + $row->int_nepotismepengelolaansdm + $row->int_jualbelijabatan + $row->int_penyalahgunaanperdin);
            $rerata['eksternal'] += ($row->ext_suap + $row->ext_pungutanliar + $row->exp_keberadaanpungutanliar + $row->exp_kualitastransparansilayanan + $row->exp_kualitaspengelolaanpbj);
            $detailKomponen['int_suap']['nilai'] += $row->int_suap;
            $detailKomponen['int_tradingininfluence']['nilai'] += $row->int_tradingininfluence;
            $detailKomponen['int_pengelolaanpbj']['nilai'] += $row->int_pengelolaanpbj;
            $detailKomponen['int_penyalahgunaanfasilitaskantor']['nilai'] += $row->int_penyalahgunaanfasilitaskantor;
            $detailKomponen['int_nepotismepengelolaansdm']['nilai'] += $row->int_nepotismepengelolaansdm;
            $detailKomponen['int_jualbelijabatan']['nilai'] += $row->int_jualbelijabatan;
            $detailKomponen['int_penyalahgunaanperdin']['nilai'] += $row->int_penyalahgunaanperdin;
            $detailKomponen['ext_suap']['nilai'] += $row->ext_suap;
            $detailKomponen['ext_pungutanliar']['nilai'] += $row->ext_pungutanliar;
            $detailKomponen['exp_keberadaanpungutanliar']['nilai'] += $row->exp_keberadaanpungutanliar;
            $detailKomponen['exp_kualitastransparansilayanan']['nilai'] += $row->exp_kualitastransparansilayanan;
            $detailKomponen['exp_kualitaspengelolaanpbj']['nilai'] += $row->exp_kualitaspengelolaanpbj;
        }

        if($count > 0) {
            $rerata['internal'] = round($rerata['internal'] / ($count * 7), 2);
            $rerata['eksternal'] = round($rerata['eksternal'] / ($count * 5), 2);
            $detailKomponen['int_suap']['nilai'] = round($detailKomponen['int_suap']['nilai'] / $count, 2);
            $detailKomponen['int_tradingininfluence']['nilai'] = round($detailKomponen['int_tradingininfluence']['nilai'] / $count, 2);
            $detailKomponen['int_pengelolaanpbj']['nilai'] = round($detailKomponen['int_pengelolaanpbj']['nilai'] / $count, 2);
            $detailKomponen['int_penyalahgunaanfasilitaskantor']['nilai'] = round($detailKomponen['int_penyalahgunaanfasilitaskantor']['nilai'] / $count, 2);
            $detailKomponen['int_nepotismepengelolaansdm']['nilai'] = round($detailKomponen['int_nepotismepengelolaansdm']['nilai'] / $count, 2);
            $detailKomponen['int_jualbelijabatan']['nilai'] = round($detailKomponen['int_jualbelijabatan']['nilai'] / $count, 2);
            $detailKomponen['int_penyalahgunaanperdin']['nilai'] = round($detailKomponen['int_penyalahgunaanperdin']['nilai'] / $count, 2);
            $detailKomponen['ext_suap']['nilai'] = round($detailKomponen['ext_suap']['nilai'] / $count, 2);
            $detailKomponen['ext_pungutanliar']['nilai'] = round($detailKomponen['ext_pungutanliar']['nilai'] / $count, 2);
            $detailKomponen['exp_keberadaanpungutanliar']['nilai'] = round($detailKomponen['exp_keberadaanpungutanliar']['nilai'] / $count, 2);
            $detailKomponen['exp_kualitastransparansilayanan']['nilai'] = round($detailKomponen['exp_kualitastransparansilayanan']['nilai'] / $count, 2);
            $detailKomponen['exp_kualitaspengelolaanpbj']['nilai'] = round($detailKomponen['exp_kualitaspengelolaanpbj']['nilai'] / $count, 2);
        }

        $result = [
            'rerata' => $rerata,
            'detail_komponen' => $detailKomponen
        ];

        return new SimpleResponse($result);
    }

    private function getRerataNasional($tahun) {
        if ($tahun == 2021) {
            $result = 72.4;
        } else {
            $data = SPIHasilKLPD::query()
                ->columns("AVG(hasil_spi) as avg")
                ->conditions("tahun = :tahun:")
                ->bind([
                    'tahun' => $tahun
                ])
                ->execute();

            if (count($data) > 0) {
                $result = round($data[0]['avg'], 2);
            } else {
                $result = 0;
            }
        }

        return $result;
    }

    private function getRerataKementerian($tahun) {
        // notes 15 Desember 2022 nilai rata2 nasional dari semua instansi
        if ($tahun == 2021) { // notes 22 April 2022 dihardcode dlu
            return 72.4;
        } else {
            $conditions = [
                'tahun = :tahun:',
                'id_tipe IN ({types:array})'
            ];
            $bind = [
                'tahun' => $tahun,
                'types' => SPIHasilKLPD::INSTANSI_KL_ID_TIPE
            ];
            $result = SpiInternalEksternalDetail::query()
                ->columns("avg(indeks) as avg")
                ->leftJoin("Instansi", "Instansi.id = SpiInternalEksternalDetail.id_instansi")
                ->conditions(implode(" AND ", $conditions))
                ->bind($bind)
                ->execute();

            return round($result[0]['avg'], 2);
        }
    }

    private function getRerataProvinsi($tahun, $idProvinsi) {
        $conditions = [
            'tahun = :tahun:',
            'id_provinsi = :id_provinsi:'
        ];
        $bind = [
            'tahun' => $tahun,
            'id_provinsi' => $idProvinsi
        ];
        $result = SpiInternalEksternalDetail::query()
            ->columns("avg(indeks) as avg")
            ->conditions(implode(" AND ", $conditions))
            ->bind($bind)
            ->execute();

        return round($result[0]['avg'], 2);
    }

    private function getRerataKL($tahun) {
        $conditions = [
            'tahun = :tahun:',
            'id_tipe IN ({types:array})'
        ];
        $bind = [
            'tahun' => $tahun,
            'types' => SPIHasilKLPD::INSTANSI_KL_ID_TIPE
        ];
        $result = SpiInternalEksternalDetail::query()
            ->columns("avg(indeks) as avg")
            ->leftJoin("Instansi", "Instansi.id = SpiInternalEksternalDetail.id_instansi")
            ->conditions(implode(" AND ", $conditions))
            ->bind($bind)
            ->execute();

        return round($result[0]['avg'], 2);
    }

    private function getDataInstansi($tahun, $koordinator = '', $isDaerah = true, $wilayahId = '') {
        $bind = [
            'tahun' => $tahun,
            'year' => $tahun
        ];
        $where = !empty($koordinator) ? "AND shk.kementerian_koordinator = '" . $koordinator . "'" : '';

        if(!empty($wilayahId)) {
            $where .= " AND (CASE WHEN riw.id IS NULL THEN ins.id_provinsi = :wilayah_id ELSE riw.id_provinsi = :wilayah_id END)";
            $bind['wilayah_id'] = $wilayahId;
        }


        $whereInstansiTipe = ($isDaerah ? "ins.id_tipe IN ('1', '2')" : "ins.id_tipe  IN (".implode(", ", SPIHasilKLPD::INSTANSI_KL_ID_TIPE).")");

        $qInstansi = "
            SELECT
                shk.*,
                (CASE WHEN ins.id_tipe = '1' THEN COALESCE(riw.id_provinsi, ins.id_provinsi) ELSE COALESCE(riw.id_kota_kabupaten, ins.id_kota_kabupaten) END) AS wilayah_code
            FROM spi_hasil_klpd shk
            LEFT JOIN instansi ins ON shk.code_klpd = ins.id
            LEFT JOIN rel_instansi_wilayah riw ON riw.id_instansi = ins.id AND :year >= riw.start_year AND :year <= riw.end_year
            WHERE $whereInstansiTipe AND shk.tahun = :tahun AND shk.is_active = true
            $where 
            ORDER BY shk.hasil_spi DESC
            ; 
        ";

        $data = $this->rawQuery($qInstansi, $bind);

        return $data;
    }

    private function getNilaiInstansi($tahun, $wilayah_id, $level) {
        $conditions = [
            'tahun = :tahun:',
            SpiInternalEksternalDetail::buildRefConditionsBasedOnLevel($level)
        ];
        $bind = [
            'tahun' => $tahun,
            'ref_id' => $wilayah_id
        ];
        $result = SpiInternalEksternalDetail::findFirst([
            'columns' => "indeks",
            'conditions' => implode(" AND ", $conditions),
            'bind' => $bind
        ]);
        $indeks = 0;
        if ($result) {
            $indeks = round($result->indeks, 2);
        }

        return $indeks;
    }

    private function getNilaiInstansiById($tahun, $idInstansi) {
        $conditions = [
            'tahun = :tahun:',
            'id_instansi = :id_instansi:'
        ];
        $bind = [
            'tahun' => $tahun,
            'id_instansi' => $idInstansi
        ];
        $result = SpiInternalEksternalDetail::findFirst([
            'columns' => "indeks",
            'conditions' => implode(" AND ", $conditions),
            'bind' => $bind
        ]);
        $indeks = 0;
        if ($result) {
            $indeks = round($result->indeks, 2);
        }

        return $indeks;
    }

    private function getTertinggiTerendahDaerah($tahun, $idProvinsi) {
        $data = SpiInternalEksternalDetail::query()
            ->columns("id_instansi as code_klpd, nama_instansi as nama_klpd, round(indeks, 2) as hasil_spi, tahun")
            ->conditions("id_provinsi = :id_provinsi: AND tahun = :tahun:")
            ->bind(["id_provinsi" => $idProvinsi, "tahun" => $tahun])
            ->execute();
        $result = [
            'tertinggi' => [],
            'terendah' => []
        ];
        if(count($data) > 0) {
            $result['tertinggi'] = $data[0];
            $result['terendah'] = $data[0];

            foreach ($data as $item) {
                if($result['tertinggi']['hasil_spi'] < $item['hasil_spi']) {
                    $result['tertinggi'] = $item;
                }

                if($result['terendah']['hasil_spi'] > $item['hasil_spi']) {
                    $result['terendah'] = $item;
                }
            }
        }
        return $result;
    }

    private function getTertinggiTerendahKL($tahun) {
        $data = SpiInternalEksternalDetail::query()
            ->leftJoin("Instansi", "Instansi.id = SpiInternalEksternalDetail.id_instansi")
            ->columns("id_instansi as code_klpd, nama_instansi as nama_klpd, round(indeks, 2) as hasil_spi, tahun")
            ->conditions("id_tipe IN ({types:array}) AND tahun = :tahun:")
            ->bind(["types" => SPIHasilKLPD::INSTANSI_KL_ID_TIPE, "tahun" => $tahun])
            ->execute();
        $result = [
            'tertinggi' => [],
            'terendah' => []
        ];
        if(count($data) > 0) {
            $result['tertinggi'] = $data[0];
            $result['terendah'] = $data[0];

            foreach ($data as $item) {
                if($result['tertinggi']['hasil_spi'] < $item['hasil_spi']) {
                    $result['tertinggi'] = $item;
                }

                if($result['terendah']['hasil_spi'] > $item['hasil_spi']) {
                    $result['terendah'] = $item;
                }
            }
        }
        return $result;
    }

    public function dataAreaInstansi() {
        $req = $this->request;
        $validatedRequest = $this->validate(
            $req->get(),
            [
                'level' => ['validators' => ['required', [
                    'name' => 'max_string',
                    'params' => 20
                ]]],
                'year' => ['validators' => ['required', 'digit']],
                'wilayah_id' => ['validators' => ['digit']],
                'id_instansi' => ['validators' => ['digit']],
                'koordinator' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 100
                ]]],
            ]
        );
        $isKorwil = false;
        if($req->get('_url') != null)
            $isKorwil = str_contains($req->get('_url'), '/korwil');
        $where = [];
        $bind = [];

        $where[] = "tahun = :tahun AND is_active = true";
        $bind['tahun'] = $validatedRequest['year'];
        $bind['year'] = $validatedRequest['year'];

        $result = [];

        switch ($validatedRequest['level']) {
            case SPIHasilKLPD::LEVEL_PROVINSI:
                if(empty($validatedRequest['wilayah_id']) || $validatedRequest['wilayah_id'] == null)
                    throw new ValidationException("Wilayah harus dipilih!");
                $where[] = "ins.id_tipe = '1'";
                $result['rerata_provinsi'] = $this->getRerataProvinsi($validatedRequest['year'], $validatedRequest['wilayah_id']);
                $result['data_instansi'] = $this->getDataInstansi($validatedRequest['year'], '', true, $validatedRequest['wilayah_id']);
                $result['rerata_nasional'] = $this->getRerataNasional($validatedRequest['year']);
                $result['nilai_instansi'] = $this->getNilaiInstansi($validatedRequest['year'], $validatedRequest['wilayah_id'], $validatedRequest['level']);
                $result['skor_tertinggi_terendah'] = $this->getTertinggiTerendahDaerah($validatedRequest['year'], $validatedRequest['wilayah_id']);
                $result['rerata_nasional_prev_year'] = $this->getRerataNasional($validatedRequest['year'] - 1);
                $result['rerata_provinsi_prev_year'] = $this->getRerataProvinsi($validatedRequest['year'] - 1, $validatedRequest['wilayah_id']);
                $result['nilai_instansi_prev_year'] = $this->getNilaiInstansi($validatedRequest['year'] - 1, $validatedRequest['wilayah_id'], $validatedRequest['level']);

                if($validatedRequest['year'] >= 2023) {
                    $result['detail_spi'] = $this->getDetailSPI($validatedRequest['year'], $validatedRequest['level'], $validatedRequest['wilayah_id']);
                    $result['kelompok_anggaran'] = $this->getKelompokAnggaran($validatedRequest['level']);
                    $result['highlight_ranking'] = $this->getHighlightRanking($validatedRequest['year'], $validatedRequest['level'], $validatedRequest['wilayah_id']);
                    $result['tren_spi'] = $this->getTrenSPI($validatedRequest['year'], $validatedRequest['level'], $validatedRequest['wilayah_id']);
                }
                break;
            case SPIHasilKLPD::LEVEL_KABUPATEN:
                if(empty($validatedRequest['wilayah_id']) || $validatedRequest['wilayah_id'] == null)
                    throw new ValidationException("Wilayah harus dipilih!");
                $kabupaten = MasterKotaKabupaten::findFirst([
                    'conditions' => 'kota_kabupaten_code = :wilayah_id:',
                    'bind' => [
                        'wilayah_id' => $validatedRequest['wilayah_id']
                    ]
                ]);
                if(empty($kabupaten) || $kabupaten == null)
                    throw new ValidationException("Data Kabupaten tidak ditemukan!");
                $provinsiCode = $kabupaten->provinsi_code;
                $where[] = "ins.id_tipe = '1'";
                $result['rerata_provinsi'] = $this->getRerataProvinsi($validatedRequest['year'], $provinsiCode);
                $result['data_instansi'] = $this->getDataInstansi($validatedRequest['year'], '', true, $provinsiCode);
                $result['rerata_nasional'] = $this->getRerataNasional($validatedRequest['year']);
                $result['nilai_instansi'] = $this->getNilaiInstansi($validatedRequest['year'], $validatedRequest['wilayah_id'], $validatedRequest['level']);
                $result['rerata_nasional_prev_year'] = $this->getRerataNasional($validatedRequest['year'] - 1);
                $result['rerata_provinsi_prev_year'] = $this->getRerataProvinsi($validatedRequest['year'] - 1, $provinsiCode);
                $result['nilai_instansi_prev_year'] = $this->getNilaiInstansi($validatedRequest['year'] - 1, $validatedRequest['wilayah_id'], $validatedRequest['level']);
                if($validatedRequest['year'] >= 2023) {
                    $result['detail_spi'] = $this->getDetailSPI($validatedRequest['year'], $validatedRequest['level'], $validatedRequest['wilayah_id']);
                    $result['kelompok_anggaran'] = $this->getKelompokAnggaran($validatedRequest['level']);
                    $result['highlight_ranking'] = $this->getHighlightRanking($validatedRequest['year'], $validatedRequest['level'], $validatedRequest['wilayah_id']);
                    $result['tren_spi'] = $this->getTrenSPI($validatedRequest['year'], $validatedRequest['level'], $validatedRequest['wilayah_id']);
                }
                break;

            case SPIHasilKLPD::LEVEL_KEMENTERIAN:
                $where[] = "ins.id_tipe IN (".implode(", ", SPIHasilKLPD::INSTANSI_KL_ID_TIPE).")";
                $where[] = "is_koordinator = '1'";
                $result['rerata_kementerian'] = $this->getRerataKementerian($validatedRequest['year']);
                $result['data_instansi'] = $this->getDataInstansi($validatedRequest['year'], $validatedRequest['koordinator'], false);
                $result['skor_tertinggi_terendah'] = $this->getTertinggiTerendahKL($validatedRequest['year']);
                $result['rerata_kementerian_prev_year'] = $this->getRerataKementerian($validatedRequest['year'] - 1);
                break;

            case SPIHasilKLPD::LEVEL_NASIONAL:
            default:
                $where[] = "ins.id_tipe = '1'";
                $result['rerata_nasional'] = $this->getRerataNasional($validatedRequest['year']);
                $result['rerata_nasional_prev_year'] = $this->getRerataNasional($validatedRequest['year'] - 1);
                break;
        }

        //region Check User Scope, if admin klop/iskorwil = true, only show id_instansi/id_klop/code_klpd
        if($isKorwil) {
            if ($this->hasOneOfScopes(['spi.manage_master_data', 'spi.monitoring', 'spi.view_klop_data'])) {
                if($this->hasScopes(['spi.view_klop_data'])) {
                    $id_instansi = $this->getIdInstansi();
                    if (empty($id_instansi) || $id_instansi == null) throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, "Instansi is not specified!");
                    $where[] = "SPIHasilKLPD.code_klpd = :id_instansi:";
                    $bind['id_instansi'] = $id_instansi;
                }
            }
            else {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, "Not Allowed!");
            }
        }
        //endregion

        $stringWhere = implode(" AND ", $where);

        $qArea = "
            SELECT
                shk.*,
                (CASE WHEN ins.id_tipe = '1' THEN COALESCE(riw.id_provinsi, ins.id_provinsi) ELSE COALESCE(riw.id_kota_kabupaten, ins.id_kota_kabupaten) END) AS wilayah_code
            FROM spi_hasil_klpd shk
            LEFT JOIN instansi ins ON shk.code_klpd = ins.id
            LEFT JOIN rel_instansi_wilayah riw ON ins.id = riw.id_instansi AND :year >= riw.start_year AND :year <= riw.end_year
            WHERE $stringWhere
            ; 
        ";

        $dataArea = $this->rawQuery($qArea, $bind);
        unset($bind['year']);
        $result['data_area'] = [];

        foreach ($dataArea as $item) {
            $itemResult = $item;
            $itemResult['nama_klpd'] = str_replace("Pemerintah ", "", $itemResult['nama_klpd']);
            $result['data_area'][] = $itemResult;
        }

        $result['data_skor_pemda'] = $this->getSkorPemda($validatedRequest['year']);
        $result['data_skor_kementerian'] = $this->getSkorKementerian($validatedRequest['year']);

        return new SimpleResponse($result);
    }


    private function getTrenSPI($year, $level, $refId, $parentRefId = 0)
    {
        $result = [];
        //NOTE: data ini hanya untuk tampilan Dashboard SPI 2023 saja
        if ($year == 2023) {
            $result = [
                'skor_spi' => [],
                'internal' => [],
                'eksternal' => [],
                'eksper' => [],
            ];

            $dataKementerian = [];

            if ($level == SPIHasilKLPD::LEVEL_UNIT_KERJA) {
                $data = $this->getDataUnitKerja($refId);
                $dataKementerian = $this->getDataIntExtOfInstansi($parentRefId);
            } else if ($level == SPIHasilKLPD::LEVEL_KEMENTERIAN) {
                $data = $this->getDataIntExtOfInstansi($refId);
            } else {
                $data = $this->getDataIntExtOfWilayah($level, $refId);
            }

            foreach ($data as $item) {
                $result['skor_spi'][$item->tahun] = round($item->indeks, 2);
                $result['internal'][$item->tahun] = round($item->indeks_internal, 2);
                $result['eksternal'][$item->tahun] = round($item->indeks_eksternal, 2);
                if ($level != SPIHasilKLPD::LEVEL_UNIT_KERJA) {
                    $result['eksper'][$item->tahun] = round($item->indeks_eksper, 2);
                }
            }

            if ($level == SPIHasilKLPD::LEVEL_UNIT_KERJA) {
                foreach ($dataKementerian as $item) {
                    $result['eksper'][$item->tahun] = round($item->indeks_eksper, 2);
                }
            }
            return $result;
        }
    }

    private function getDataUnitKerja($idUnitKerja)
    {
        $conditions = [];
        $bind = [];

        $conditions[] = 'id_unit_kerja = :id_unit_kerja:';
        $bind['id_unit_kerja'] = $idUnitKerja;

        $data = SpiIntExtUnitKerjaKl::find([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind
        ]);

        return $data;
    }

    private function getHighlightRanking($year, $level, $wilayah_id, $idInstansi = null)
    {
        $result = [];

        if (is_null($idInstansi)) {
            $idInstansi = $this->getIdInstansiFromLevelAndWilayah($level, $wilayah_id);
        }
        if($idInstansi > 0) {
            $instansi = Instansi::findFirst($idInstansi);
            $kelompokAnggaran = $instansi->kelompok_anggaran;
            $result['kelompok_anggaran'] = [
                'code' => $instansi->kelompok_anggaran,
                'name' => $instansi->nama_kelompok_anggaran
            ];

            $idTipe = SPIHasilKLPD::idTipeByLevel($level);
            $conditions = [
                'id_tipe IN (' . implode(",", $idTipe) . ')',
                'deleted_at IS NULL'
            ];
            $bind = [];
            if($level == SPIHasilKLPD::LEVEL_KABUPATEN) {
                $conditions[] = 'id_provinsi = :id_provinsi:';
                $bind['id_provinsi'] = $instansi->id_provinsi;
            } else {
                $conditions[] = 'kelompok_anggaran = :kelompok_anggaran:';
                $bind['kelompok_anggaran'] = $kelompokAnggaran;
            }

            $listIdInstansi = array_column(Instansi::find([
                'columns' => 'id',
                'conditions' => implode(' AND ', $conditions),
                'bind' => $bind
            ])->toArray(), 'id');
            if($level == SPIHasilKLPD::LEVEL_KABUPATEN) {
                $instansiProvinsi = Instansi::findFirst([
                    'conditions' => 'id_provinsi = ?0 AND deleted_at IS NULL AND id_tipe = ?1',
                    'bind' => [
                        $instansi->id_provinsi,
                        1 //1 = Provinsi
                    ]
                ]);

                if(!empty($instansiProvinsi)) {
                    $listIdInstansi[] = $instansiProvinsi->id;
                }
            }
            if (count($listIdInstansi) > 0) {
                $q = "
                    WITH spi_CTE AS (
                        SELECT
                            id,
                            tahun,
                            id_instansi as code_klpd,
                            nama_instansi as nama_klpd,
                            round(indeks, 2) as hasil_spi,
                            ROW_NUMBER() OVER (ORDER BY indeks DESC) AS spi_rank
                        FROM
                            jaga.spi_internal_eksternal_detail
                        WHERE
                            tahun = :tahun
                            AND id_instansi IN (" . implode(', ', $listIdInstansi) . ")
                    )
                    
                    SELECT *
                    FROM spi_CTE
                    WHERE spi_rank <= 2 OR spi_rank > (SELECT COUNT(*) FROM spi_CTE) - 2 OR code_klpd = :id_instansi;
                ";

                $result['list_ranking'] = $this->rawQuery($q, [
                    'tahun' => $year,
                    'id_instansi' => $idInstansi
                ]);
            }
        }

        return $result;
    }

    private function getIdInstansiFromLevelAndWilayah($level, $wilayah_id)
    {
        $idInstansi = 0;

        if($level == SPIHasilKLPD::LEVEL_PROVINSI) {
            $instansi = Instansi::findFirst([
                'conditions' => 'id_provinsi = ?0 AND id_kota_kabupaten IS NULL AND id_tipe = 1 AND deleted_at IS NULL',
                'bind' => [
                    $wilayah_id
                ]
            ]);
            if(!empty($instansi)) {
                $idInstansi = $instansi->id;
            }
        }
        else if($level == SPIHasilKLPD::LEVEL_KABUPATEN) {
            $instansi = Instansi::findFirst([
                'conditions' => 'id_kota_kabupaten = ?0 AND id_tipe = 2 AND deleted_at IS NULL',
                'bind' => [
                    $wilayah_id
                ]
            ]);
            if(!empty($instansi)) {
                $idInstansi = $instansi->id;
            }
        }

        return $idInstansi;
    }

    private function getKelompokAnggaran($level)
    {
        $result = [];
        $idTipe = SPIHasilKLPD::idTipeByLevel($level);
        $data = Instansi::find([
            'conditions' => 'id_tipe IN ({id_tipe:array}) AND deleted_at IS NULL AND kelompok_anggaran IS NOT NULL',
            'bind' => [
                'id_tipe' => $idTipe
            ],
            'order' => 'kelompok_anggaran, id_kota_kabupaten, id_provinsi'
        ]);

        foreach ($data as $item) {
            if(!array_key_exists($item->kelompok_anggaran, $result)) {
                $result[$item->kelompok_anggaran] = [
                    'kelompok_anggaran' => $item->kelompok_anggaran,
                    'nama_kelompok_anggaran' => $item->nama_kelompok_anggaran,
                    'items' => []
                ];
            }
            $result[$item->kelompok_anggaran]['items'][] = $item->nama;
        }

        return $result;
    }

    private function getDetailSPI($year, $level, $refId)
    {
        $result = [
            'komponen_internal' => [],
            'komponen_eksternal' => [],
            'komponen_eksper' => [],
            'faktor_koreksi' => [],
        ];

        $conditions = [];
        $bind = [];

        $conditions[] = 'tahun = :tahun:';
        $conditions[] = SpiInternalEksternalDetail::buildRefConditionsBasedOnLevel($level);
        $bind['tahun'] = $year;
        $bind['ref_id'] = $refId;

        $data = SpiInternalEksternalDetail::findFirst([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind
        ]);
        if(!empty($data)) {
            $dimensi = $this->getSkorDimensiInternalEksternal($year, $level, $refId, 'INTERNAL');
            $result['komponen_internal'] = [
                'indeks' => round($data->indeks_internal, 2),
                'dimensi' => $dimensi,
                'unit_kerja' => $this->getListUnitKerja($year, $level, $refId, 'INTERNAL'),
            ];

            $dimensi = $this->getSkorDimensiInternalEksternal($year, $level, $refId, 'EXTERNAL');
            $result['komponen_eksternal'] = [
                'indeks' => round($data->indeks_eksternal, 2),
                'dimensi' => $dimensi,
                'unit_kerja' => $this->getListUnitKerja($year, $level, $refId, 'EXTERNAL'),
            ];

            $dimensi = $this->getSkorDimensiInternalEksternal($year, $level, $refId, 'EXPERT');
            $result['komponen_eksper'] = [
                'indeks' => round($data->indeks_eksper, 2),
                'dimensi' => $dimensi,
                'bidang' => $this->getBidangExpert($year, $level, $refId)
            ];

            if ($level == SPIHasilKLPD::LEVEL_KEMENTERIAN) {
                $dataKoreksi = $this->getDataIntExtOfInstansi($refId);
            }
            else {
                $dataKoreksi = $this->getDataIntExtOfWilayah($level, $refId);
            }

            $trenFaktaKorupsi = [];
            $trenPelaksanaanSPI = [];

            foreach ($dataKoreksi as $item) {
                $trenFaktaKorupsi[$item->tahun] = $item->koreksi_prevalensi_korupsi;
                $trenPelaksanaanSPI[$item->tahun] = $item->koreksi_integritas_pelaksanaan_survei;
            }

            $result['faktor_koreksi'] = [
                'fakta_korupsi' => $data->koreksi_prevalensi_korupsi,
                'pelaksanaan_spi' => $data->koreksi_integritas_pelaksanaan_survei,
                'tren_faktor_koreksi' => [
                    'fakta_korupsi' => $trenFaktaKorupsi,
                    'pelaksanaan_spi' => $trenPelaksanaanSPI
                ]
            ];
        }

        return $result;
    }

    private function getDataIntExtOfWilayah($level, $wilayah_id)
    {
        $condition = "";
        if($level == 'provinsi') {
            $condition = 'id_provinsi = :wilayah_id: and id_kota_kabupaten is null';
        }
        else if ($level == 'kabupaten') {
            $condition = 'id_kota_kabupaten = :wilayah_id:';
        }

        return SpiInternalEksternalDetail::find([
            'conditions' => $condition,
            'bind' => [
                'wilayah_id' => $wilayah_id
            ],
            'order' => 'tahun ASC'
        ]);
    }

    private function getDataIntExtOfInstansi($idInstansi)
    {
        $condition = 'id_instansi = :id_instansi:';

        return SpiInternalEksternalDetail::find([
            'conditions' => $condition,
            'bind' => [
                'id_instansi' => $idInstansi
            ],
            'order' => 'tahun ASC'
        ]);
    }

    private function getListUnitKerja($year, $level, $refId, $type)
    {
        $conditions = [
            'tahun = :year:',
            SpiInternalEksternalDetail::buildRefConditionsBasedOnLevel($level),
            'id_unit_kerja IS NULL'
        ];
        $bind = [
            'year' => $year,
            'ref_id' => $refId
        ];
        $nKey = '';
        if($type == 'INTERNAL') {
            $nKey = 'n_int';
            $model = SpiInternalOpdDetail::class;
            $indexKey = 'indeks_internal';
        }
        elseif($type == 'EXTERNAL') {
            $nKey = 'n_ext';
            $model = SpiExternalOpdDetail::class;
            $indexKey = 'indeks_eksternal';
        }
        else {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Tipe tidak valid!");
        }

        return $model::query()
            ->columns("ROUND($indexKey, 2) AS nilai, opd, $nKey AS n")
            ->conditions(implode(' AND ', $conditions))
            ->bind($bind)
            ->orderBy('opd')
            ->execute();
    }

    private function getSkorDimensi($year, $level, $refId, $type)
    {
        $result = [];
        if($type == 'INTERNAL') {
            $result =  $this->getSkorDimensiInternal($year, $level, $refId);
        }
        else if($type == 'EXTERNAL') {
            $result =  $this->gerSkorDimensiExternal($year, $level, $refId);
        }
        else if($type == 'EXPERT') {
            $result = $this->gerSkorDimensiExpert($year, $level, $refId);
        }

        return $result;
    }

    private function getSkorDimensiInternalEksternal($year, $level, $refId, $type) {
        $kodeDimensiMapping = SpiIntExtKodeMapping::query()
            ->columns("indikator_key, indikator")
            ->where('type = ?0')
            ->bind([
                $type
            ])
            ->groupBy('indikator_key, indikator')
            ->orderBy('indikator')
            ->execute();

        $conditions = [
            'tahun = :tahun:',
            SpiInternalEksternalDetail::buildRefConditionsBasedOnLevel($level)
        ];
        $bind = [
            'tahun' => $year,
            'ref_id' => $refId
        ];
        $data = SpiInternalEksternalDetail::findFirst([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind
        ]);
        $result = [];
        if (!empty($data)) {
            $row = $data->toArray();
            foreach ($kodeDimensiMapping as $item) {
                $itemResult = [
                    'tahun' => $year,
                    'id_instansi' => $row['id_instansi'],
                    'id_provinsi' => $row['id_provinsi'],
                    'id_kota_kabupaten' => $row['id_kota_kabupaten'],
                    'grup_dimensi' => $item->indikator,
                    'skor' => array_key_exists($item->indikator_key, $row) ? round($row[$item->indikator_key], 2) : 0
                ];
                $result[] = $itemResult;
            }
        }

        return $result;
    }

    private function getBidangExpert($year, $level, $refId)
    {
        $conditions = [
            'tahun = :tahun:',
        ];
        $bind = [
            'tahun' => $year,
        ];


        if($level == SPIHasilKLPD::LEVEL_PROVINSI) {
            $conditions[] = 'id_provinsi = :ref_id:';
            $conditions[] = 'id_kota_kabupaten IS NULL';
        }
        else if ($level == SPIHasilKLPD::LEVEL_KABUPATEN) {
            $conditions[] = 'id_kota_kabupaten = :ref_id:';
        }
        else if ($level == SPIHasilKLPD::LEVEL_KEMENTERIAN) {
            $conditions[] = 'id_instansi = :ref_id:';
        }
        else {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Level tidak valid!");
        }
        $bind['ref_id'] = $refId;

        $result = SpiExpertOpdDetail::query()
            ->columns("id,tahun,id_instansi,id_provinsi,id_kota_kabupaten,kode_wilayah,nama_instansi,opd as nama_bidang, ROUND(indeks_eksper, 2) AS indeks_eksper")
            ->where(implode(' AND ', $conditions))
            ->bind($bind)
            ->orderBy("opd")
            ->execute();

        return $result->toArray();
    }

    private function getSkorPemda($year)
    {
        $qSkorPemda = "
            SELECT
            	 SUM(shk.hasil_spi) AS sum_spi,
            	 COUNT(ins.id) AS total_pemda,
            	 SUM(shk.hasil_spi)/COUNT(ins.id) AS avg_spi
            FROM
            	spi_hasil_klpd shk
            	LEFT JOIN instansi ins ON shk.code_klpd = ins.id
            WHERE tahun = :year AND is_active = true AND ins.id_tipe IN ('1', '2')
            ;
            ";

        return $this->rawQuery($qSkorPemda, [
            'year' => $year
        ]);
    }

    private function getSkorKementerian($year)
    {

        $qSkorKementerian = "
            SELECT
            	 SUM(shk.hasil_spi) AS sum_spi,
            	 COUNT(ins.id) AS total_kementerian,
            	 SUM(shk.hasil_spi)/COUNT(ins.id) AS avg_spi
            FROM
            	spi_hasil_klpd shk
            	LEFT JOIN instansi ins ON shk.code_klpd = ins.id
            WHERE ins.id_tipe IN (".implode(",", SPIHasilKLPD::INSTANSI_KL_ID_TIPE).") AND tahun = :year AND is_active = TRUE
            ;
            ";
        return $this->rawQuery($qSkorKementerian, [
            'year' => $year
        ]);
    }

    private function getSkorDimensiInternal($year, $level, $refId)
    {
        $kodeDimensiMapping = SpiIntExtKodeMapping::query()
            ->columns("indikator_key, indikator")
            ->where('type = ?0')
            ->bind([
                'INTERNAL'
            ])
            ->groupBy('indikator_key, indikator')
            ->orderBy('indikator')
            ->execute();

        $conditions = [
            'tahun = :tahun:',
        ];
        $bind = [
            'tahun' => $year,
        ];


        if($level == SPIHasilKLPD::LEVEL_PROVINSI) {
            $conditions[] = 'id_provinsi = :ref_id:';
            $conditions[] = 'id_kota_kabupaten IS NULL';
        }
        else if ($level == SPIHasilKLPD::LEVEL_KABUPATEN) {
            $conditions[] = 'id_kota_kabupaten = :ref_id:';
        }
        else if ($level == SPIHasilKLPD::LEVEL_KEMENTERIAN) {
            $conditions[] = 'id_instansi = :ref_id:';
        }
        else {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Level tidak valid!");
        }
        $bind['ref_id'] = $refId;
        $data = SpiInternalOpdDetail::find([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind
        ]);
        $result = [];
        foreach ($kodeDimensiMapping as $item) {
            $itemResult = [
                'tahun' => $year,
                'id_instansi' => count($data) > 0 ? $data[0]->id_instansi : null,
                'id_provinsi' => count($data) > 0 ? $data[0]->id_provinsi : null,
                'id_kota_kabupaten' => count($data) > 0 ? $data[0]->id_kota_kabupaten : null,
                'grup_dimensi' => $item->indikator,
            ];

            $totalSkor = 0;
            $count = 0;
            foreach ($data->toArray() as $detail) {
                $totalSkor += $detail[$item->indikator_key];
                $count++;
            }
            $itemResult['skor'] = $count == 0 ? 0 : round(1.0 * $totalSkor / $count, 2);
            $result[] = $itemResult;
        }

        return $result;
    }
    private function gerSkorDimensiExternal($year, $level, $refId)
    {
        $kodeDimensiMapping = SpiIntExtKodeMapping::query()
            ->columns("indikator_key, indikator")
            ->where('type = ?0')
            ->bind([
                'EXTERNAL'
            ])
            ->groupBy('indikator_key, indikator')
            ->orderBy('indikator')
            ->execute();


        $conditions = [
            'tahun = :tahun:',
        ];
        $bind = [
            'tahun' => $year,
        ];


        if($level == SPIHasilKLPD::LEVEL_PROVINSI) {
            $conditions[] = 'id_provinsi = :ref_id:';
            $conditions[] = 'id_kota_kabupaten IS NULL';
        }
        else if ($level == SPIHasilKLPD::LEVEL_KABUPATEN) {
            $conditions[] = 'id_kota_kabupaten = :ref_id:';
        }
        else if ($level == SPIHasilKLPD::LEVEL_KEMENTERIAN) {
            $conditions[] = 'id_instansi = :ref_id:';
        }
        else {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Level tidak valid!");
        }
        $bind['ref_id'] = $refId;
        $data = SpiExternalOpdDetail::find([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind
        ]);
        $result = [];
        foreach ($kodeDimensiMapping as $item) {
            $itemResult = [
                'tahun' => $year,
                'id_instansi' => count($data) > 0 ? $data[0]->id_instansi : null,
                'id_provinsi' => count($data) > 0 ? $data[0]->id_provinsi : null,
                'id_kota_kabupaten' => count($data) > 0 ? $data[0]->id_kota_kabupaten : null,
                'grup_dimensi' => $item->indikator,
            ];

            $totalSkor = 0;
            $count = 0;
            foreach ($data->toArray() as $detail) {
                $totalSkor += $detail[$item->indikator_key];
                $count++;
            }
            $itemResult['skor'] = $count == 0 ? 0 : round(1.0 * $totalSkor / $count, 2);
            $result[] = $itemResult;
        }

        return $result;
    }
    private function gerSkorDimensiExpert($year, $level, $refId)
    {
        $kodeDimensiMapping = SpiIntExtKodeMapping::query()
            ->columns("indikator_key, indikator")
            ->where('type = ?0')
            ->bind([
                'EXPERT'
            ])
            ->groupBy('indikator_key, indikator')
            ->orderBy('indikator')
            ->execute();


        $conditions = [
            'tahun = :tahun:',
        ];
        $bind = [
            'tahun' => $year,
        ];


        if($level == SPIHasilKLPD::LEVEL_PROVINSI) {
            $conditions[] = 'id_provinsi = :ref_id:';
            $conditions[] = 'id_kota_kabupaten IS NULL';
        }
        else if ($level == SPIHasilKLPD::LEVEL_KABUPATEN) {
            $conditions[] = 'id_kota_kabupaten = :ref_id:';
        }
        else if ($level == SPIHasilKLPD::LEVEL_KEMENTERIAN) {
            $conditions[] = 'id_instansi = :ref_id:';
        }
        else {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Level tidak valid!");
        }
        $bind['ref_id'] = $refId;
        $data = SpiExpertOpdDetail::find([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind
        ]);
        $result = [];
        foreach ($kodeDimensiMapping as $item) {
            $itemResult = [
                'tahun' => $year,
                'id_instansi' => count($data) > 0 ? $data[0]->id_instansi : null,
                'id_provinsi' => count($data) > 0 ? $data[0]->id_provinsi : null,
                'id_kota_kabupaten' => count($data) > 0 ? $data[0]->id_kota_kabupaten : null,
                'grup_dimensi' => $item->indikator,
            ];

            $totalSkor = 0;
            $count = 0;
            foreach ($data->toArray() as $detail) {
                $totalSkor += $detail[$item->indikator_key];
                $count++;
            }
            $itemResult['skor'] = $count == 0 ? 0 : round(1.0 * $totalSkor / $count, 2);
            $result[] = $itemResult;
        }

        return $result;
    }

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

        $validatedRequest = $this->validate(
            $params,
            [
                'level' => ['validators' => ['required', [
                    'name' => 'max_string',
                    'params' => 20
                ]]],
            ]
        );

        return new SimpleResponse($this->getKelompokAnggaran($validatedRequest['level']));
    }

    public function listOpdIntExt() {
        $params = $this->request->get();
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';

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

        $conditions = "tahun = :tahun: AND ((id_provinsi = :wilayah_id: AND id_kota_kabupaten IS NULL) OR (id_kota_kabupaten = :wilayah_id:))";
        $bind = [
            'tahun' => $validatedRequest['year'],
            'wilayah_id' => $validatedRequest['wilayah_id'],
        ];

        if(!empty($validatedRequest['keyword'])) {
            $conditions .= ' AND opd ILIKE :keyword:';
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }

        $data = SpiInternalEksternalOpd::query()
            ->columns("opd")
            ->conditions($conditions)
            ->bind($bind)
            ->groupBy('opd')
            ->orderBy('opd')
            ->execute();

        $result = array_column($data->toArray(), 'opd');

        return new SimpleResponse($result);
    }

    public function listOpdByType() {
        $params = $this->request->get();
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';

        $validatedRequest = $this->validate(
            $params,
            [
                'year' => ['validators' => ['required', 'digit']],
                'id_instansi' => ['validators' => ['required', 'digit']],
                'type' => ['validators' => ['required', [
                    'name' => 'inclusion_in',
                    'params' => ["INTERNAL", "EXTERNAL", "EXPERT"]
                ]]],
                'keyword' => ['validators' => []],
            ]
        );

        $conditions = "tahun = :tahun: AND id_instansi = :id_instansi:";
        $bind = [
            "tahun" => $validatedRequest["year"],
            "id_instansi" => $validatedRequest["id_instansi"]
        ];

        if(!empty($validatedRequest['keyword'])) {
            $conditions .= ' AND opd ILIKE :keyword:';
            $bind['keyword'] = '%' . $validatedRequest['keyword'] . '%';
        }

        switch ($validatedRequest["type"]) {
            case "INTERNAL":
                $model = SpiInternalOpdDetail::class;
                break;
            case "EXTERNAL":
                $model = SpiExternalOpdDetail::class;
                break;
            case "EXPERT":
            default:
                $model = SpiExpertOpdDetail::class;
                break;
        }

        $data = $model::query()
            ->columns("opd")
            ->conditions($conditions)
            ->bind($bind)
            ->groupBy('opd')
            ->orderBy('opd')
            ->execute();

        $result = array_column($data->toArray(), 'opd');

        return new CollectionPaginatedResponse($result);
    }

    public function detailKomponenOpd() {
        $req = $this->request->get();
        $req['data_group'] = array_key_exists('data_group', $req) ? $req['data_group'] : 'grup_pertanyaan';
        $validatedRequest = $this->validate(
            $req,
            [
                'data_group' => ['validators' => ['required', [
                    'name' => 'inclusion_in',
                    'params' => ['grup_pertanyaan','grup_dimensi']
                ]]],
                'year' => ['validators' => ['required', 'digit']],
                'wilayah_id' => ['validators' => ['digit']],
                'id_instansi' => ['validators' => ['digit']],
                'id_unit_kerja' => ['validators' => ['digit']],
                'opd' => ['validators' => ['required']],
            ]
        );

        if (empty($validatedRequest['wilayah_id']) && empty($validatedRequest['id_instansi']) && empty($validatedRequest['id_unit_kerja'])) {
            throw new ValidationException([
                "field" => "wilayah_id",
                "message" => "Wilayah/Instansi belum dipilih!"
            ]);
        }

        $result = [];

        if($validatedRequest['data_group'] == 'grup_pertanyaan') {
            $result = $this->getDataOPDByGrupPertanyaan($validatedRequest);
        }
        else if($validatedRequest['data_group'] == 'grup_dimensi') {
            $result = $this->getDataOPDByGrupDimensi($validatedRequest);
        }

        return new SimpleResponse($result);
    }

    private function getDataOPDByGrupPertanyaan($validatedRequest)
    {
        $kode = [
            'int_suap' => 'Risiko Suap/Gratifikasi',
            'int_tradingininfluence' => 'Risiko Trading in Influence',
            'int_pengelolaanpbj' => 'Risiko Pengelolaan PBJ',
            'int_penyalahgunaanfasilitaskantor' => 'Risiko Penyalahgunaan Fasilitas Kantor',
            'int_nepotismepengelolaansdm' => 'Risiko Nepotisme dalam Pengelolaan SDM',
            'int_jualbelijabatan' => 'Risiko Jual/Beli Jabatan',
            'int_penyalahgunaanperdin' => 'Risiko Penyalahgunaan Perdin',
            'ext_suap' => 'Risiko  Suap/Gratifikasi',
            'ext_pungutanliar' => 'Risiko Pungutan Liar'
        ];

        $fields = [
            'int_suap' => "'INT.405A', 'INT.405B'",
            'int_tradingininfluence' => "'INT.402.1','INT.402.2','INT.402.3','INT.402.4','INT.402.5','INT.402.6'",
            'int_pengelolaanpbj' => "'INT.702.A','INT.702B.a','INT.702B.b','INT.702B.c','INT.702B.d','INT.702B.e'",
            'int_penyalahgunaanfasilitaskantor' => "'INT.403A'",
            'int_nepotismepengelolaansdm' => "'INT.601A.a','INT.601A.b','INT.601A.c','INT.601B'",
            'int_jualbelijabatan' => "'INT.602A'",
            'int_penyalahgunaanperdin' => "'INT.703A','INT.703B','INT.705A','INT.705B'",
            'ext_suap' => "'EXT.503'",
            'ext_pungutanliar' => "'EXT.504A'"
        ];

        $qWith = "
            WITH int_ext_opd AS (
            	SELECT
            		*
            	FROM
            		jaga.spi_internal_eksternal_opd
            	WHERE
            		tahun = :year
            		AND ((id_provinsi = :wilayah_id AND id_kota_kabupaten IS NULL) OR (id_kota_kabupaten = :wilayah_id))
            		AND opd = UPPER(:opd)
            )
        ";

        $q = "
            $qWith            

            SELECT 
                ROUND(100.0 * (SELECT COALESCE(ROUND((SUM(freq_val) * 1.0/NULLIF(SUM(n_val), 0)), 2), 0) FROM int_ext_opd WHERE kode IN ('INT.405A', 'INT.405B')), 2) AS int_suap,
                ROUND(100.0 * (SELECT col_n_val FROM int_ext_opd WHERE kode = 'INT.402'), 2) AS int_tradingininfluence,
                ROUND(100.0 * (SELECT COALESCE(ROUND((SUM(freq_val) * 1.0/NULLIF(SUM(n_val), 0)), 2), 0) FROM int_ext_opd WHERE kode IN ('INT.702B.a','INT.702B.b','INT.702B.c','INT.702B.d','INT.702B.e','INT.702.A')), 2) AS int_pengelolaanpbj,
                ROUND(100.0 * (SELECT col_n_val FROM int_ext_opd WHERE kode = 'INT.403A'), 2) AS int_penyalahgunaanfasilitaskantor,
                ROUND(100.0 * (SELECT COALESCE(ROUND((SUM(freq_val) * 1.0/NULLIF(SUM(n_val), 0)), 2), 0) FROM int_ext_opd WHERE kode IN ('INT.601A.a', 'INT.601A.b', 'INT.601A.c', 'INT.601B')), 2) AS int_nepotismepengelolaansdm,
                ROUND(100.0 * (SELECT col_n_val FROM int_ext_opd WHERE kode = 'INT.602A'), 2) AS int_jualbelijabatan,
                ROUND(100.0 * (SELECT COALESCE(ROUND((SUM(freq_val) * 1.0/NULLIF(SUM(n_val), 0)), 2), 0) FROM int_ext_opd WHERE kode IN ('INT.703A', 'INT.703B', 'INT.705A', 'INT.705B')), 2) AS int_penyalahgunaanperdin,
                ROUND(100.0 * (SELECT col_n_val FROM int_ext_opd WHERE kode = 'EXT.503'), 2) AS ext_suap,
            	ROUND(100.0 * (SELECT col_n_val FROM int_ext_opd WHERE kode = 'EXT.504A'), 2) AS ext_pungutanliar
        ";

        $bind = [
            'year' => $validatedRequest['year'],
            'wilayah_id' => $validatedRequest['wilayah_id'],
            'opd' => $validatedRequest['opd'],
        ];

        $data = $this->rawQuery($q, $bind)[0];

        $result = [];
        foreach($data as $key => $value){
            $qOPD = "
                $qWith
                
                SELECT
                    id,
                    tahun,
                    id_instansi,
                    id_provinsi,
                    id_kota_kabupaten,
                    kode_wilayah,
                    nama_instansi,
                    opd,
                    type,
                    kode,
                    nama,
                    freq_val,
                    n_val,
                    col_n_val,
                    ROUND((100.0 * freq_val / NULLIF(n_val, 0)), 2) AS col_n_val_100,
                    grup_dimensi
                 FROM int_ext_opd WHERE kode IN ($fields[$key])
            ";

            $detail = $this->rawQuery($qOPD, $bind);

            $result[] = [
                'kode' => $key,
                'nilai' => $value,
                'nama_dimensi_komponen' => $kode[$key],
                'details' => $detail
            ];
        }

        return $result;
    }

    private function getDataOPDByGrupDimensi($validatedRequest)
    {
        return [
            'internal' => $this->getDataOPDByGrupDimensiAndTypeInternal($validatedRequest),
            'external' => $this->getDataOPDByGrupDimensiAndTypeExternal($validatedRequest),
        ];
    }

    private function getDataOPDByGrupDimensiAndTypeInternal($validatedRequest)
    {
        $mappingData = SpiIntExtKodeMapping::find([
            'conditions' => 'type = ?0',
            'bind' => ['INTERNAL'],
            'order' => 'indikator, code_key'
        ]);


        $grupIndikator = [];
        $grupCode = [];

        foreach ($mappingData as $item) {
            if(!array_key_exists($item->indikator_key, $grupIndikator)) {
                $grupIndikator[$item->indikator_key] = [
                    'indikator' => $item->indikator,
                    'code' => []
                ];
            }
            $grupIndikator[$item->indikator_key]['code'][] = $item->code_key;

            if(!array_key_exists($item->code_key, $grupCode)) {
                $grupCode[$item->code_key] = [
                    'variable' => $item->variable,
                    'deskripsi' => $item->deskripsi
                ];
            }
        }

        $bind = [
            'year' => $validatedRequest['year'],
            'opd' => $validatedRequest['opd'],
        ];

        if (!empty($validatedRequest['id_unit_kerja'])) {
            $conditions = "tahun = :year: AND id_unit_kerja = :id_unit_kerja: AND opd = UPPER(:opd:)";
            $bind['id_unit_kerja'] =$validatedRequest['id_unit_kerja'];
        }
        else if (!empty($validatedRequest['id_instansi'])) {
            $conditions = "tahun = :year: AND id_instansi = :id_instansi: AND opd = UPPER(:opd:) AND id_unit_kerja IS NULL";
            $bind['id_instansi'] =$validatedRequest['id_instansi'];
        } else {
            $conditions = "tahun = :year: AND ((id_provinsi = :wilayah_id: AND id_kota_kabupaten IS NULL) OR (id_kota_kabupaten = :wilayah_id:)) AND opd = UPPER(:opd:)";
            $bind['wilayah_id'] =$validatedRequest['wilayah_id'];
        }

        $result = [];

        $first = SpiInternalOpdDetail::findFirst([
            'conditions' => $conditions,
            'bind' => $bind
        ]);

        if(!empty($first)) {
            $data = $first->toArray();

            foreach ($grupIndikator as $keyIndikator => $valueIndikator) {
                $itemResult = [
                    'kode' => $valueIndikator['indikator'],
                    'nilai' => round($data[$keyIndikator], 2),
                    'nama_dimensi_komponen' => $valueIndikator['indikator'],
                    'details' => []
                ];

                foreach ($valueIndikator['code'] as $code) {
                    if (array_key_exists($code, $data)) {
                        $detailResult = [
                            'tahun' => $data['tahun'],
                            'id_instansi' => $data['id_instansi'],
                            'id_provinsi' => $data['id_provinsi'],
                            'id_kota_kabupaten' => $data['id_kota_kabupaten'],
                            'kode_wilayah' => $data['kode_wilayah'],
                            'nama_instansi' => $data['nama_instansi'],
                            'id_unit_kerja' => $data['id_unit_kerja'],
                            'nama_unit_kerja' => $data['nama_unit_kerja'],
                            'opd' => $data['opd'],
                            'type' => 'INTERNAL',
                            'kode' => $grupCode[$code]['variable'],
                            'nama' => $grupCode[$code]['deskripsi'],
                            'freq_val' => null,
                            'n_val' => null,
                            'col_n_val' => round($data[$code] / 2.0, 2),
                            'col_n_val_100' => round($data[$code], 2),
                            'grup_dimensi' => $valueIndikator['indikator'],
                        ];

                        $itemResult['details'][] = $detailResult;
                    }
                }

                $result[] = $itemResult;
            }
        }

        return $result;
    }

    private function getDataOPDByGrupDimensiAndTypeExternal($validatedRequest)
    {
        $mappingData = SpiIntExtKodeMapping::find([
            'conditions' => 'type = ?0',
            'bind' => ['EXTERNAL'],
            'order' => 'indikator, code_key'
        ]);


        $grupIndikator = [];
        $grupCode = [];

        foreach ($mappingData as $item) {
            if(!array_key_exists($item->indikator_key, $grupIndikator)) {
                $grupIndikator[$item->indikator_key] = [
                    'indikator' => $item->indikator,
                    'code' => []
                ];
            }
            $grupIndikator[$item->indikator_key]['code'][] = $item->code_key;

            if(!array_key_exists($item->code_key, $grupCode)) {
                $grupCode[$item->code_key] = [
                    'variable' => $item->variable,
                    'deskripsi' => $item->deskripsi
                ];
            }
        }

        $bind = [
            'year' => $validatedRequest['year'],
            'opd' => $validatedRequest['opd'],
        ];

        if (!empty($validatedRequest['id_unit_kerja'])) {
            $conditions = "tahun = :year: AND id_unit_kerja = :id_unit_kerja: AND opd = UPPER(:opd:)";
            $bind['id_unit_kerja'] =$validatedRequest['id_unit_kerja'];
        }
        else if (!empty($validatedRequest['id_instansi'])) {
            $conditions = "tahun = :year: AND id_instansi = :id_instansi: AND opd = UPPER(:opd:) AND id_unit_kerja IS NULL";
            $bind['id_instansi'] =$validatedRequest['id_instansi'];
        } else {
            $conditions = "tahun = :year: AND ((id_provinsi = :wilayah_id: AND id_kota_kabupaten IS NULL) OR (id_kota_kabupaten = :wilayah_id:)) AND opd = UPPER(:opd:)";
            $bind['wilayah_id'] =$validatedRequest['wilayah_id'];
        }

        $result = [];

        $first = SpiExternalOpdDetail::findFirst([
            'conditions' => $conditions,
            'bind' => $bind
        ]);

        if(!empty($first)) {
            $data = $first->toArray();

            foreach ($grupIndikator as $keyIndikator => $valueIndikator) {
                $itemResult = [
                    'kode' => $valueIndikator['indikator'],
                    'nilai' => round($data[$keyIndikator], 2),
                    'nama_dimensi_komponen' => $valueIndikator['indikator'],
                    'details' => []
                ];

                foreach ($valueIndikator['code'] as $code) {
                    if (array_key_exists($code, $data)) {
                        $detailResult = [
                            'tahun' => $data['tahun'],
                            'id_instansi' => $data['id_instansi'],
                            'id_provinsi' => $data['id_provinsi'],
                            'id_kota_kabupaten' => $data['id_kota_kabupaten'],
                            'kode_wilayah' => $data['kode_wilayah'],
                            'nama_instansi' => $data['nama_instansi'],
                            'id_unit_kerja' => $data['id_unit_kerja'],
                            'nama_unit_kerja' => $data['nama_unit_kerja'],
                            'opd' => $data['opd'],
                            'type' => 'EXTERNAL',
                            'kode' => $grupCode[$code]['variable'],
                            'nama' => $grupCode[$code]['deskripsi'],
                            'freq_val' => null,
                            'n_val' => null,
                            'col_n_val' => round($data[$code] / 2.0, 2),
                            'col_n_val_100' => round($data[$code], 2),
                            'grup_dimensi' => $valueIndikator['indikator'],
                        ];

                        $itemResult['details'][] = $detailResult;
                    }
                }

                $result[] = $itemResult;
            }
        }

        return $result;
    }

    private function getDataOPDByGrupDimensiAndTypeExpert($validatedRequest)
    {
        $mappingData = SpiIntExtKodeMapping::find([
            'conditions' => 'type = ?0',
            'bind' => ['EXPERT'],
            'order' => 'indikator'
        ]);


        $grupIndikator = [];
        $grupCode = [];

        foreach ($mappingData as $item) {
            if(!array_key_exists($item->indikator_key, $grupIndikator)) {
                $grupIndikator[$item->indikator_key] = [
                    'indikator' => $item->indikator,
                    'code' => []
                ];
            }
            $grupIndikator[$item->indikator_key]['code'][] = $item->code_key;

            if(!array_key_exists($item->code_key, $grupCode)) {
                $grupCode[$item->code_key] = [
                    'variable' => $item->variable,
                    'deskripsi' => $item->deskripsi
                ];
            }
        }

        $bind = [
            'year' => $validatedRequest['year'],
            'opd' => $validatedRequest['opd'],
        ];
        if (!empty($validatedRequest['id_instansi'])) {
            $conditions = "tahun = :year: AND id_instansi = :id_instansi: AND opd = UPPER(:opd:)";
            $bind['id_instansi'] =$validatedRequest['id_instansi'];
        } else {
            $conditions = "tahun = :year: AND ((id_provinsi = :wilayah_id: AND id_kota_kabupaten IS NULL) OR (id_kota_kabupaten = :wilayah_id:)) AND opd = UPPER(:opd:)";
            $bind['wilayah_id'] =$validatedRequest['wilayah_id'];
        }

        $result = [];

        $first = SpiExpertOpdDetail::findFirst([
            'conditions' => $conditions,
            'bind' => $bind
        ]);

        if(!empty($first)) {
            $data = $first->toArray();

            foreach ($grupIndikator as $keyIndikator => $valueIndikator) {
                $itemResult = [
                    'kode' => $valueIndikator['indikator'],
                    'nilai' => round($data[$keyIndikator], 2),
                    'nama_dimensi_komponen' => $valueIndikator['indikator'],
                    'details' => []
                ];

                foreach ($valueIndikator['code'] as $code) {
                    if (array_key_exists($code, $data)) {
                        $detailResult = [
                            'tahun' => $data['tahun'],
                            'id_instansi' => $data['id_instansi'],
                            'id_provinsi' => $data['id_provinsi'],
                            'id_kota_kabupaten' => $data['id_kota_kabupaten'],
                            'kode_wilayah' => $data['kode_wilayah'],
                            'nama_instansi' => $data['nama_instansi'],
                            'opd' => $data['opd'],
                            'type' => 'EXTERNAL',
                            'kode' => $grupCode[$code]['variable'],
                            'nama' => $grupCode[$code]['deskripsi'],
                            'freq_val' => null,
                            'n_val' => null,
                            'col_n_val' => round($data[$code] / 2.0, 2),
                            'col_n_val_100' => round($data[$code], 2),
                            'grup_dimensi' => $valueIndikator['indikator'],
                        ];

                        $itemResult['details'][] = $detailResult;
                    }
                }

                $result[] = $itemResult;
            }
        }
        return $result;
    }

    /**
     * @throws CustomErrorException
     * @throws ValidationException
     */
    public function dataKL() {
        $request = $this->request->get();
        $validatedRequest = $this->validate(
            $request,
            [
                'year' => ['validators' => ['required', 'digit']],
                'id_instansi' => ['validators' => ['digit']]
            ]
        );
        $year = $validatedRequest['year'];
        $idInstansi = $validatedRequest['id_instansi'];
        $result = [];
        $result['rerata_nasional'] = $this->getRerataNasional($year);
        $result['rerata_nasional_prev'] = $this->getRerataNasional($year - 1);
        $result['data_skor_pemda'] = $this->getSkorPemda($year);
        $result['data_skor_kl'] = $this->getSkorKementerian($year);

        if (!empty($idInstansi)) {
            $result['nilai_instansi'] = $this->getNilaiInstansiById($year, $idInstansi);
            $result['nilai_instansi_prev'] = $this->getNilaiInstansiById($year - 1, $idInstansi);
            $result['highlight_ranking'] = $this->getHighlightRanking($year, SPIHasilKLPD::LEVEL_KEMENTERIAN, null, $idInstansi);
            $result['kelompok_anggaran'] = $this->getKelompokAnggaran(SPIHasilKLPD::LEVEL_KEMENTERIAN);
            $result['tren_spi'] = $this->getTrenSPI($year, SPIHasilKLPD::LEVEL_KEMENTERIAN, $idInstansi);
            $result['detail_spi'] = $this->getDetailSPI($year, SPIHasilKLPD::LEVEL_KEMENTERIAN, $idInstansi);
            $result['skor_tertinggi_terendah'] = $this->getTertinggiTerendahKL($year);
        }

        return new SimpleResponse($result);
    }

    public function dataUnitKerjaKL()
    {
        $request = $this->request->get();
        $validatedRequest = $this->validate(
            $request,
            [
                'year' => ['validators' => ['required', 'digit']],
                'id_instansi' => ['validators' => ['required', 'digit']]
            ]
        );
        $year = $validatedRequest['year'];
        $idInstansi = $validatedRequest['id_instansi'];
        $instansi = Instansi::findFirst($idInstansi);
        if (empty($instansi)) {
            throw new ValidationException("Instansi tidak ditemukan!");
        }

        $tipeInstansiUnitKerja = InstansiTipe::findByNama("Unit Kerja Kementerian");
        if ($instansi->id_tipe != $tipeInstansiUnitKerja->id) {
            throw new ValidationException("Instansi haru merupakan Instansi Unit Kerja (Eselon I) Kementerian");
        }
        $dataSpiEselonI = SpiIntExtUnitKerjaKl::findFirst([
            'conditions' => 'tahun = ?0 AND id_unit_kerja = ?1',
            'bind' => [
                $validatedRequest['year'],
                $validatedRequest['id_instansi']
            ]
        ]);

        if(empty($dataSpiEselonI)) {
            throw new ValidationException("Data SPI tidak ditemukan!");
        }

        $idParent = $instansi->parent_id;
        $instansiKementerian = Instansi::findFirst($idParent);
        if(empty($instansiKementerian)) {
            throw new ValidationException("Data Kementerian tidak ditemukan!");
        }

        $result = [];
        $result['rerata_nasional'] = $this->getRerataNasional($year);
        $result['rerata_nasional_prev'] = $this->getRerataNasional($year - 1);
        $result['data_skor_pemda'] = $this->getSkorPemda($year);
        $result['data_skor_kl'] = $this->getSkorKementerian($year);

        $result['data_kementerian'] = $instansiKementerian->toSimpleArray(['id', 'nama', 'id_tipe', 'parent_id']);
        $result['data_kementerian']['nilai'] = $this->getNilaiInstansiById($year, $idParent);
        $result['data_kementerian']['nilai_prev'] = $this->getNilaiInstansiById($year - 1, $idParent);

        $result['data_instansi'] = $instansi->toSimpleArray(['id', 'nama', 'id_tipe', 'parent_id']);
        $result['data_instansi']['nilai'] = $dataSpiEselonI->indeks;

        $result['highlight_ranking'] = $this->getHighlightRankingUnitKerja($year, $idInstansi, $idParent);

        $result['tren_spi'] = $this->getTrenSPI($year, SPIHasilKLPD::LEVEL_UNIT_KERJA, $idInstansi, $idParent);

        $result['detail_spi'] = $this->getDetailSPIUnitKerja($year, $idInstansi, $idParent);

        return new SimpleResponse($result);
    }

    private function getHighlightRankingUnitKerja($year, $idInstansi, $idParent)
    {
        $q = "
            WITH spi_CTE AS (
                SELECT 
                	id,
                	tahun,
                	kode_wilayah,
                	id_unit_kerja,
                	nama_unit_kerja,
                	indeks,
                	ROW_NUMBER() OVER (ORDER BY indeks DESC) AS spi_rank
                	
                FROM
                	spi_int_ext_unit_kerja_kl 
                WHERE
                	id_instansi = :id_parent
                	AND tahun = :tahun
            )
            
            SELECT * FROM spi_CTE
            	WHERE spi_rank <= 2 OR spi_rank > (SELECT COUNT(1) FROM spi_CTE) - 2 OR id_unit_kerja = :id_instansi
        ";

        $bind = [
            'tahun' => $year,
            'id_instansi' => $idInstansi,
            'id_parent' => $idParent
        ];

        $result = [
            'list_ranking' => $this->rawQuery($q, $bind)
        ];

        return $result;
    }

    private function getDetailSPIUnitKerja($year, $idInstansi, $idParent)
    {
        $result = [
            'komponen_internal' => [],
            'komponen_eksternal' => [],
            'komponen_eksper' => [],
        ];

        $conditions = [];
        $bind = [];

        $conditions[] = 'id_unit_kerja = :id_unit_kerja:';
        $bind['id_unit_kerja'] = $idInstansi;

        $conditions[] = 'tahun = :tahun:';
        $bind['tahun'] = $year;

        $dataUnitKerja = SpiIntExtUnitKerjaKl::findFirst([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind
        ]);

        $dataOpdInternal = SpiInternalOpdDetail::find([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind
        ]);

        $dataOpdExternal = SpiExternalOpdDetail::find([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind
        ]);

        if(!empty($dataUnitKerja)) {
            $result['komponen_internal'] = [
                'indeks' => $dataUnitKerja->indeks_internal,
                'dimensi' => $this->formatDataDimensiUnitKerja($dataUnitKerja, 'INTERNAL'),
                'opd' => $this->formatDataOpd($dataOpdInternal, 'INTERNAL')
            ];

            $result['komponen_eksternal'] = [
                'indeks' => $dataUnitKerja->indeks_eksternal,
                'dimensi' => $this->formatDataDimensiUnitKerja($dataUnitKerja, 'EXTERNAL'),
                'opd' => $this->formatDataOpd($dataOpdExternal, 'EXTERNAL')
            ];


            $dataKementerian = SpiInternalEksternalDetail::findFirst([
                'conditions' => "id_instansi = :id_instansi: AND tahun = :tahun:",
                'bind' => [
                    'id_instansi' => $idParent,
                    'tahun' => $year
                ]
            ]);
            if(!empty($dataKementerian)) {
                $result['komponen_eksper'] = [
                    'indeks' => $dataKementerian->indeks_eksper,
                    'dimensi' => $this->getSkorDimensiInternalEksternal($year, SPIHasilKLPD::LEVEL_KEMENTERIAN, $idParent, 'EXPERT'),
                    'opd' => $this->getBidangExpert($year, SPIHasilKLPD::LEVEL_KEMENTERIAN, $idParent)
                ];
            }
        }

        return $result;
    }

    private function formatDataDimensiUnitKerja(SpiIntExtUnitKerjaKl $dataUnitKerja, $type)
    {
        if($type == 'INTERNAL') {
            $result = [
                [
                    'grup_dimensi' => 'Integritas Dalam Pelaksanaan Tugas',
                    'skor' => $dataUnitKerja->integritas_tugas
                ],
                [
                    'grup_dimensi' => 'Pengelolaan Anggaran',
                    'skor' => $dataUnitKerja->pengelolaan_anggaran
                ],
                [
                    'grup_dimensi' => 'Pengelolaan PBJ',
                    'skor' => $dataUnitKerja->pengelolaan_pbj
                ],
                [
                    'grup_dimensi' => 'Pengelolaan SDM',
                    'skor' => $dataUnitKerja->pengelolaan_sdm
                ],
                [
                    'grup_dimensi' => 'Perdagangan Pengaruh (Trading in Influence)',
                    'skor' => $dataUnitKerja->trading_in_influence
                ],
                [
                    'grup_dimensi' => 'Sosialisasi Antikorupsi',
                    'skor' => $dataUnitKerja->sosialisasi_antikorupsi
                ],
                [
                    'grup_dimensi' => 'Transparansi',
                    'skor' => $dataUnitKerja->transparansi
                ],
            ];
        }
        else if ($type == 'EXTERNAL') {
            $result = [
                [
                    'grup_dimensi' => 'Integritas Pegawai',
                    'skor' => $dataUnitKerja->integritas_pegawai
                ],
                [
                    'grup_dimensi' => 'Transparansi dan Keadilan Layanan',
                    'skor' => $dataUnitKerja->transparansi_keadilan_layanan
                ],
                [
                    'grup_dimensi' => 'Upaya Pencegahan Korupsi',
                    'skor' => $dataUnitKerja->upaya_pencegah_korupsi
                ],
            ];
        }

        return $result;
    }

    private function formatDataOpd($dataOpd, $type)
    {
        $result = [];

        foreach ($dataOpd as $item) {
            $result[] = [
                'opd' => $item->opd,
                'nilai' => $type == 'INTERNAL' ? $item->indeks_internal : $item->indeks_eksternal
            ];
        }

        return $result;
    }

    public function summary() {
        $validatedRequest = $this->validate(
            $this->request->get(),
            [
                'level' => ['validators' => [
                    'required',
                    ['name' => 'inclusion_in', 'params' => [SPIHasilKLPD::LEVEL_NASIONAL, SPIHasilKLPD::LEVEL_PROVINSI, SPIHasilKLPD::LEVEL_KABUPATEN, SPIHasilKLPD::LEVEL_KEMENTERIAN]]
                ]]
            ]
        );
        $level = $validatedRequest["level"];
        switch ($level) {
            case SPIHasilKLPD::LEVEL_NASIONAL:
                $result = $this->summaryNasional();
                break;
            case SPIHasilKLPD::LEVEL_PROVINSI:
                $result = $this->summaryProvinsi();
                break;
            case SPIHasilKLPD::LEVEL_KABUPATEN:
                $result = $this->summaryKabKota();
                break;
            case SPIHasilKLPD::LEVEL_KEMENTERIAN:
                $result = $this->summaryKL();
                break;
            default:
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Level tidak valid!");
        }

        return new SimpleResponse($result);
    }

    private function summaryNasional() {
        $validatedRequest = $this->validate(
            $this->request->get(),
            [
                'year' => ['validators' => ['required', 'digit']]
            ]
        );

        $tahun = $validatedRequest["year"];
        $service = new SpiService($this, $tahun);
        return $service->summaryNasional();
    }

    private function summaryProvinsi() {
        $validatedRequest = $this->validate(
            $this->request->get(),
            [
                'year' => ['validators' => ['required', 'digit']],
                'wilayah_id' => ['validators' => ['required', 'digit']]
            ]
        );

        $tahun = $validatedRequest["year"];
        $idProvinsi = $validatedRequest["wilayah_id"];
        $service = new SpiService($this, $tahun);
        $service->idProvinsi = $idProvinsi;
        $this->recordSearchResult('provinsi', $idProvinsi);
        return $service->summaryProvinsi();
    }

    private function summaryKabKota() {
        $validatedRequest = $this->validate(
            $this->request->get(),
            [
                'year' => ['validators' => ['required', 'digit']],
                'wilayah_id' => ['validators' => ['required', 'digit']]
            ]
        );

        $tahun = $validatedRequest["year"];
        $idKabKota = $validatedRequest["wilayah_id"];
        $service = new SpiService($this, $tahun);
        $service->idKabKota = $idKabKota;
        $this->recordSearchResult('kabupaten', $idKabKota);
        return $service->summaryKabKota();
    }

    private function summaryKL() {
        $validatedRequest = $this->validate(
            $this->request->get(),
            [
                'year' => ['validators' => ['required', 'digit']],
                'id_instansi' => ['validators' => ['required', 'digit']]
            ]
        );

        $tahun = $validatedRequest["year"];
        $idInstansi = $validatedRequest["id_instansi"];
        $service = new SpiService($this, $tahun);
        $service->idInstansi = $idInstansi;
        $this->recordSearchResult('kementerian', $idInstansi);
        return $service->summaryKL();
    }

    private function recordSearchResult($level, $idInstansi)
    {
        date_default_timezone_set('Asia/Jakarta');
        $instansiSearched = null;
        $idProvinsi = null;
        $idKabkot = null;
        $idKl = null;
        $errorMessages = '';
        $conditions = '';
        $bind = [];

        if ($level === 'provinsi') {
            $provinsi = MasterProvinsi::findFirst([
                'columns' => 'provinsi_code',
                'conditions' => 'provinsi_code = :provinsi_code:',
                'bind' => ['provinsi_code' => (string)$idInstansi]
            ]);

            if (empty($provinsi)) {
                $errorMessage = 'Provinsi yang dicari tidak ditemukan';
            }
            
            $idProvinsi = $provinsi->provinsi_code;
            $conditions = 'id_provinsi = :id_provinsi:';
            $bind = ['id_provinsi' => intval($idInstansi)];
        } else if ($level === 'kabupaten') {
            $kabupaten = MasterKotaKabupaten::findFirst([
                'columns' => 'kota_kabupaten_code',
                'conditions' => 'kota_kabupaten_code = :kota_kabupaten_code:',
                'bind' => ['kota_kabupaten_code' => (string)$idInstansi]
            ]);

            if (empty($kabupaten)) {
                $errorMessage = 'Kota Kabupaten yang dicari tidak ditemukan';
            }

            $idKabkot = $kabupaten->kota_kabupaten_code;
            $conditions = 'id_kota_kabupaten = :id_kota_kabupaten:';
            $bind = ['id_kota_kabupaten' => intval($idInstansi)];
        } else {
            // KL
            $instansi = Instansi::findFirst([
                'columns' => 'id',
                'conditions' => 'id = :id_instansi:',
                'bind' => ['id_instansi' => intval($idInstansi)]
            ]);

            if (empty($instansi)) {
                $errorMessage = 'Instansi yang dicari tidak ditemukan';
            }

            $idKl = $instansi->id;
            $conditions = 'id_instansi = :id_instansi:';
            $bind = ['id_instansi' => intval($idInstansi)];
        }

        if (!empty($errorMessage)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, $errorMessage);
        }

        $instansiSearched = InstansiSearchCount::findFirst([
            'conditions' => $conditions,
            'bind' => $bind
        ]);

        $result = null;

        $now = date('Y-m-d H:i:s');
        if (!empty($instansiSearched)) {
            $instansiSearched->counter += 1;
            $instansiSearched->updated_at = $now;
            $result = $instansiSearched->save();
        } else {
            $newInstansiSearchCount = new InstansiSearchCount();
            $newInstansiSearchCount->id_instansi = $idKl;
            $newInstansiSearchCount->id_provinsi = $idProvinsi;
            $newInstansiSearchCount->id_kota_kabupaten = $idKabkot;
            $newInstansiSearchCount->counter = 1;
            $newInstansiSearchCount->created_at = $now;
            $result = $newInstansiSearchCount->save();
        }

        if (empty($result)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Tidak dapat merekam riwayat pencarian instansi/pemda');
        }
    }
}