<?php

use PhpOffice\PhpSpreadsheet\IOFactory;

class SPIGratifikasiController extends BaseController
{
    public function getDetailSPI()
    {
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.monitoring', 'spi.view_klop_data', 'spi.gratifikasi', 'spi.view_pemda_data']);

        $validatedRequest = $this->validateRequestSPI($this->request->get());

        if(
            empty($validatedRequest['id_instansi'])
            && empty($validatedRequest['id_provinsi'])
            && empty($validatedRequest['id_kota_kabupaten'])
        ) {
            $result = $this->detailSpiNasional($validatedRequest["year"]);
        } else {
            $result = $this->detailSpiInstansi($validatedRequest);
        }

        return new SimpleResponse($result);
    }

    private function detailSpiNasional($year) {
        $kodeMapping = SpiIntExtKodeMapping::findGratifikasiOnly();
        $groupedKodeMappingGratif = $this->groupingByKodeMapping($kodeMapping);
        $kodeMappingKeyVal = ArrayHelper::asKeyValue($kodeMapping->toArray(), 'code_key');
        $columnKode = array_merge(["indeks"], array_keys($kodeMappingKeyVal));

        $rows = SpiInternalEksternalDetail::findByYear($year, $columnKode);
        if (count($rows) == 0) throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data tidak ditemukan!");

        $rowsByKomponenGratif = [];
        $totalIndeks = 0;
        foreach ($rows as $row) {
            $rowsByKomponenGratif[] = $this->calculateDataSpiSeparateByKomponen($groupedKodeMappingGratif, $row, $kodeMappingKeyVal);
            $totalIndeks += $row["indeks"];
        }
        $averageIndeks = $totalIndeks / count($rows);

        $result = $rowsByKomponenGratif[0];
        foreach ($result as $komponenKey => $komponen) {
            $result[$komponenKey]["indeks"] = 0;
            foreach ($komponen["dimensi"] as $dimensiKey => $dimensi) {
                $result[$komponenKey]["dimensi"][$dimensiKey]["indeks"] = 0;
                foreach ($dimensi["details"] as $detailKey => $detail) {
                    $result[$komponenKey]["dimensi"][$dimensiKey]["details"][$detailKey]["indeks"] = 0;
                }
            }
        }

        foreach ($rowsByKomponenGratif as $rowByKomponenGratif) {
            foreach ($rowByKomponenGratif as $komponenKey => $komponen) {
                $result[$komponenKey]["indeks"] += $komponen["indeks"];
                foreach ($komponen["dimensi"] as $dimensiKey => $dimensi) {
                    $result[$komponenKey]["dimensi"][$dimensiKey]["indeks"] += $dimensi["indeks"];
                    foreach ($dimensi["details"] as $detailKey => $detail) {
                        $result[$komponenKey]["dimensi"][$dimensiKey]["details"][$detailKey]["indeks"] += $detail["indeks"];
                    }
                }
            }
        }

        $rowNumber = count($rows);
        foreach ($result as $komponenKey => $komponen) {
            $result[$komponenKey]["indeks"] = $komponen["indeks"] / $rowNumber;
            foreach ($komponen["dimensi"] as $dimensiKey => $dimensi) {
                $result[$komponenKey]["dimensi"][$dimensiKey]["indeks"] = $dimensi["indeks"] / $rowNumber;
                foreach ($dimensi["details"] as $detailKey => $detail) {
                    $result[$komponenKey]["dimensi"][$dimensiKey]["details"][$detailKey]["indeks"] = $detail["indeks"] / $rowNumber;
                }
            }
        }

        return [
            "detail_spi" => $result,
            "tren_spi" => [
                "keberadaan" => [$year => $result["keberadaan"]["indeks"]],
                "upaya_ppg" => [$year => $result["upaya_ppg"]["indeks"]],
                "indeks_gratif" => [$year => (1.0 * $result['keberadaan']['indeks'] + $result['upaya_ppg']['indeks']) / 2],
                "indeks_spi" => [$year => $averageIndeks],
            ]
        ];
    }

    private function detailSpiInstansi($validatedRequest) {
        $instansi = $this->getDataInstansi($validatedRequest);
        $idInstansi = $instansi->id;

        $result = [];
        $result['instansi'] = $instansi;

        $kodeMapping = SpiIntExtKodeMapping::findGratifikasiOnly();

        $groupedKodeMappingGratif = $this->groupingByKodeMapping($kodeMapping);

        $kodeMappingKeyVal = [];
        if($validatedRequest['is_use_detail']) {
            $kodeMappingKeyVal = ArrayHelper::asKeyValue($kodeMapping->toArray(), 'code_key');
        }

        $dataSpi = SpiInternalEksternalDetail::findFirstBy($idInstansi, $validatedRequest['year']);

        if(!empty($dataSpi)) {
            $result['detail_spi'] = $this->calculateDataSpiSeparateByKomponen($groupedKodeMappingGratif, $dataSpi->toArray(), $kodeMappingKeyVal);
            $result['tren_spi'] = $this->getDataTren($validatedRequest['year'], $idInstansi, $groupedKodeMappingGratif, $dataSpi->toArray());
        }

        return $result;
    }

    private function validateRequestSPI($request)
    {
        $request['id_instansi'] = array_key_exists('id_instansi', $request) ? $request['id_instansi'] : null;
        $request['id_provinsi'] = array_key_exists('id_provinsi', $request) ? $request['id_provinsi'] : null;
        $request['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $request) ? $request['id_kota_kabupaten'] : null;
        $request['is_use_detail'] = array_key_exists('is_use_detail', $request) ? $request['is_use_detail'] : false;

        $validatedRequest = $this->validate(
            $request,
            [
                'year' => ['validators' => ['required', 'digit']],
                'id_instansi' => ['validators' => ['digit']],
                'id_provinsi' => ['validators' => ['digit']],
                'id_kota_kabupaten' => ['validators' => ['digit']],
                'is_use_detail' => ['validators' => []]
            ]
        );

        return $validatedRequest;
    }

    private function groupingByKodeMapping($kodeMapping)
    {
        $result = [];
        foreach ($kodeMapping as $item) {
            if(!array_key_exists($item->komponen_gratif_key, $result)) {
                $result[$item->komponen_gratif_key] = [
                    'komponen_gratif' => $item->komponen_gratif,
                    'dimensi_gratif' => []
                ];
            }

            if(!array_key_exists($item->dimensi_gratif_key, $result[$item->komponen_gratif_key]['dimensi_gratif'])) {
                $result[$item->komponen_gratif_key]['dimensi_gratif'][$item->dimensi_gratif_key] = [
                    'dimensi_gratif' => $item->dimensi_gratif,
                    'internal' => [],
                    'external' => [],
                ];
            }

            if($item->type == SpiIntExtKodeMapping::TYPE_INTERNAL) {
                $result[$item->komponen_gratif_key]['dimensi_gratif'][$item->dimensi_gratif_key]['internal'][] = $item->code_key;
            }
            else if($item->type == SpiIntExtKodeMapping::TYPE_EXTERNAL) {
                $result[$item->komponen_gratif_key]['dimensi_gratif'][$item->dimensi_gratif_key]['external'][] = $item->code_key;
            }
        }

        return $result;
    }

    private function getDataTren($year, $idInstansi, $groupedKodeMappingGratif, array $currentYearData)
    {
        $result = [
            'keberadaan' => [],
            'upaya_ppg' => [],
            'indeks_gratif' => [],
            'indeks_spi' => [],

        ];

        $data = SpiInternalEksternalDetail::findBy($idInstansi, $year)->toArray();

        $data[] = $currentYearData;

        foreach ($data as $item) {
            $calculatedData = $this->calculateDataSpiSeparateByKomponen($groupedKodeMappingGratif, $item);

            $result['keberadaan'][$item['tahun']] = $calculatedData['keberadaan']['indeks'];
            $result['upaya_ppg'][$item['tahun']] = $calculatedData['upaya_ppg']['indeks'];
            $result['indeks_gratif'][$item['tahun']] = (1.0 * $calculatedData['keberadaan']['indeks'] + $calculatedData['upaya_ppg']['indeks']) / 2;
            $result['indeks_spi'][$item['tahun']] = $item['indeks'];
        }

        return $result;
    }

    public function getDetailOPD() {
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.monitoring', 'spi.view_klop_data', 'spi.gratifikasi', 'spi.view_pemda_data']);

        $validatedRequest = $this->validateRequestOPD($this->request->get());

        $result = [];

        $instansi = $this->getDataInstansi($validatedRequest);
        $idInstansi = $instansi->id;

        $kodeMapping = SpiIntExtKodeMapping::findGratifikasiOnly('code_key');

        $kodeMappingKeyVal = ArrayHelper::asKeyValue($kodeMapping->toArray(), 'code_key');

        $groupedKodeMappingGratif = $this->groupingByKodeMapping($kodeMapping);

        $dataSpi = $this->getDataSpiOpd($idInstansi, $validatedRequest);

        $result = $this->calculateDataSpiJoinedKomponen($groupedKodeMappingGratif, $dataSpi ,$kodeMappingKeyVal, true);

        return new SimpleResponse($result);
    }

    private function validateRequestOPD($request)
    {
        $request['id_instansi'] = array_key_exists('id_instansi', $request) ? $request['id_instansi'] : null;
        $request['id_provinsi'] = array_key_exists('id_provinsi', $request) ? $request['id_provinsi'] : null;
        $request['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $request) ? $request['id_kota_kabupaten'] : null;

        $validatedRequest = $this->validate(
            $request,
            [
                'year' => ['validators' => ['required', 'digit']],
                'opd' => ['validators' => ['required']],
                'id_instansi' => ['validators' => ['digit']],
                'id_provinsi' => ['validators' => ['digit']],
                'id_kota_kabupaten' => ['validators' => ['digit']]
            ]
        );

        if(
            empty($validatedRequest['id_instansi'])
            && empty($validatedRequest['id_provinsi'])
            && empty($validatedRequest['id_kota_kabupaten'])
        ) {
            throw new ValidationException("ID Instansi, Provinsi, atau Kota/Kabupaten wajib diisi!");
        }

        return $validatedRequest;
    }

    private function getDataSpiOpd($idInstansi, $validatedRequest)
    {
        $result = [];
        $dataInternalOpdArr = [];
        $dataInternalOpd = SpiInternalOpdDetail::findFirstBy($idInstansi, $validatedRequest['year'], $validatedRequest['opd']);
        if(!empty($dataInternalOpd)) {
            $dataInternalOpdArr = $dataInternalOpd->toArray();
        }

        $dataExternalOpdArr = [];
        $dataExternalOpd = SpiExternalOpdDetail::findFirstBy($idInstansi, $validatedRequest['year'], $validatedRequest['opd']);
        if(!empty($dataExternalOpd)) {
            $dataExternalOpdArr = $dataExternalOpd->toArray();
        }

        $result = array_merge($dataInternalOpdArr, $dataExternalOpdArr);

        return $result;
    }

    private function calculateDataSpiSeparateByKomponen(array $groupedKodeMappingGratif, $dataSpi, $kodeMappingKeyVal = [])
    {
        $result = [];

        foreach ($groupedKodeMappingGratif as $key => $value) {
            $result[$key] = [
                'komponen_gratif' => $value['komponen_gratif'],
                'indeks' => 0,
                'dimensi' => []
            ];

            $totalValueDimensi = 0.0;
            $countDimensiNotZeros = 0;

            foreach ($value['dimensi_gratif'] as $keyDimensi => $valueDimensi) {
                $result[$key]['dimensi'][$keyDimensi] = [
                    'dimensi_gratif' => $valueDimensi['dimensi_gratif'],
                    'indeks' => 0
                ];

                if(!empty($kodeMappingKeyVal)) {
                    $result[$key]['dimensi'][$keyDimensi]['details'] = [];
                }

                $totalInternal = 0.0;
                $countInternal = 0;
                $totalExternal = 0.0;
                $countExternal = 0;

                foreach ($valueDimensi['internal'] as $code) {
                    if(array_key_exists($code, $dataSpi)) {
                        if($dataSpi[$code] != null && $dataSpi[$code] != 0) {
                            $totalInternal += $dataSpi[$code];
                            $countInternal++;
                            if(!empty($kodeMappingKeyVal)) {
                                $result[$key]['dimensi'][$keyDimensi]['details'][] = [
                                    'type' => $kodeMappingKeyVal[$code]['type'],
                                    'kode' => $kodeMappingKeyVal[$code]['variable'],
                                    'pertanyaan_gratif' => $kodeMappingKeyVal[$code]['pertanyaan_gratif'],
                                    'kategori_gratif' => $kodeMappingKeyVal[$code]['kategori_gratif'],
                                    'dimensi_gratif' => $valueDimensi['dimensi_gratif'],
                                    'indeks' => $dataSpi[$code]
                                ];
                            }
                        }
                    }
                }

                foreach ($valueDimensi['external'] as $code) {
                    if(array_key_exists($code, $dataSpi)) {
                        if($dataSpi[$code] != null && $dataSpi[$code] != 0) {
                            $totalExternal += $dataSpi[$code];
                            $countExternal++;
                            if(!empty($kodeMappingKeyVal)) {
                                $result[$key]['dimensi'][$keyDimensi]['details'][] = [
                                    'type' => $kodeMappingKeyVal[$code]['type'],
                                    'kode' => $kodeMappingKeyVal[$code]['variable'],
                                    'pertanyaan_gratif' => $kodeMappingKeyVal[$code]['pertanyaan_gratif'],
                                    'kategori_gratif' => $kodeMappingKeyVal[$code]['kategori_gratif'],
                                    'dimensi_gratif' => $valueDimensi['dimensi_gratif'],
                                    'indeks' => $dataSpi[$code]
                                ];
                            }
                        }
                    }
                }

                if($countInternal + $countExternal > 0) {
                    $rerataDimensi = ($totalInternal + $totalExternal) / ($countInternal + $countExternal);

                    $result[$key]['dimensi'][$keyDimensi]['indeks'] = $rerataDimensi;

                    if ($rerataDimensi > 0) {
                        $countDimensiNotZeros++;
                        $totalValueDimensi += $rerataDimensi;
                    }
                }
                else {
                    unset($result[$key]['dimensi'][$keyDimensi]);
                }
            }

            if($countDimensiNotZeros > 0) {
                $result[$key]['indeks'] = 1.0 * $totalValueDimensi / $countDimensiNotZeros;
            }
        }

        return $result;
    }

    public function listOpdIntExtOnly() {
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.monitoring', 'spi.view_klop_data', 'spi.gratifikasi', 'spi.view_pemda_data']);

        $params = $this->request->get();
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';
        $params['id_instansi'] = array_key_exists('id_instansi', $params) ? $params['id_instansi'] : null;
        $params['id_provinsi'] = array_key_exists('id_provinsi', $params) ? $params['id_provinsi'] : null;
        $params['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $params) ? $params['id_kota_kabupaten'] : null;

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

        if(
            empty($validatedRequest['id_instansi'])
            && empty($validatedRequest['id_provinsi'])
            && empty($validatedRequest['id_kota_kabupaten'])
        ) {
            throw new ValidationException("ID Instansi, Provinsi, atau Kota/Kabupaten wajib diisi!");
        }

        $instansi = $this->getDataInstansi($validatedRequest);
        $idInstansi = $instansi->id;

        $bind = [
            'id_instansi' => $idInstansi,
            'year' => $validatedRequest['year']
        ];

        $keywordCondition = "";

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

        $q = "
            SELECT opd FROM jaga.spi_internal_opd_detail WHERE id_unit_kerja IS NULL AND id_instansi = :id_instansi AND tahun = :year $keywordCondition
            UNION
            SELECT opd FROM jaga.spi_external_opd_detail WHERE id_unit_kerja IS NULL AND id_instansi = :id_instansi AND tahun = :year $keywordCondition

            ORDER BY opd ASC
        ";

        return new SimpleResponse(array_column($this->rawQuery($q, $bind), 'opd'));
    }

    private function getDataInstansi($validatedRequest)
    {
        if(empty($validatedRequest['id_instansi'])) {
            if(!empty($validatedRequest['id_kota_kabupaten'])) {
                $instansi = Instansi::findFirst([
                    'conditions' => 'id_kota_kabupaten = ?0 AND id_tipe = ?1',
                    'bind' => [
                        $validatedRequest['id_kota_kabupaten'],
                        2 //2 is id tipe for Kota/Kabupaten
                    ]
                ]);
            }
            else {
                $instansi = Instansi::findFirst([
                    'conditions' => 'id_provinsi = ?0 AND id_tipe = ?1',
                    'bind' => [
                        $validatedRequest['id_provinsi'],
                        1 //1 is id tipe for Provinsi
                    ]
                ]);
            }
        }
        else {
            $instansi = Instansi::findFirst($validatedRequest['id_instansi']);
        }

        if(empty($instansi)) {
            throw new ValidationException("Instansi tidak ditemukan");
        }

        return $instansi;
    }

    public function listKlpd() {
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.monitoring', 'spi.view_klop_data', 'spi.gratifikasi', 'spi.view_pemda_data']);

        $params = $this->request->get();
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';
        $params['id_instansi_gol'] = array_key_exists('id_instansi_gol', $params) ? $params['id_instansi_gol'] : null;

        $validatedRequest = $this->validate(
            $params,
            [
                'id_instansi_gol' => ['validators' => ['digit']],
                'year' => ['validators' => ['required', 'digit']],
                'keyword' => ['validators' => []],
            ]
        );
        $conditions = [
            'tahun = :year:'
        ];
        $bind = [
            'year' => $validatedRequest['year']
        ];

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

        if(!empty($validatedRequest['id_instansi_gol'])) {
            $conditions[] = 'SigInstansi.id_instansi_gol = :id_instansi_gol:';
            $bind['id_instansi_gol'] = $validatedRequest['id_instansi_gol'];
        }

        $instansi = SpiInternalEksternalDetail::query()
            ->columns("SpiInternalEksternalDetail.id_instansi AS id, SpiInternalEksternalDetail.nama_instansi AS nama, SigInstansi.id_instansi_gol")
            ->leftJoin('SigInstansi', 'SpiInternalEksternalDetail.id_instansi = SigInstansi.id_instansi_jaga')
            ->where(implode(' AND ', $conditions))
            ->bind($bind)
            ->orderBy('nama')
            ->execute();
//            ->toArray();
//
//        foreach ($instansi as $idx => $item) {
//            try {
//                $realInstansi = Instansi::findFirst($item['id']);
//                $instansiHistory = $realInstansi->instansiHistory;
//                $instansi[$idx]['instansi_history'] = $instansiHistory;
//            }
//            catch (Exception $ex) {
//                $instansiHistory;
//            }
//        }

        return new SimpleResponse($instansi);
    }

    public function getListDetailOPDs() {
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.monitoring', 'spi.view_klop_data', 'spi.gratifikasi', 'spi.view_pemda_data']);

        $validatedRequest = $this->validateRequestOPDs($this->request->get());

        $result = [];

        $result['list_dimensi'] = $this->listDimensi();

        $instansi = $this->getDataInstansi($validatedRequest);
        $idInstansi = $instansi->id;

        $kodeMapping = SpiIntExtKodeMapping::findGratifikasiOnly();

        $kodeMappingKeyVal = ArrayHelper::asKeyValue($kodeMapping->toArray(), 'code_key');

        $groupedKodeMappingGratif = $this->groupingByKodeMapping($kodeMapping);

        $dataInternalOpdArr = [];
        $dataInternalOpd = SpiInternalOpdDetail::findBy($idInstansi, $validatedRequest['year']);
        if(!empty($dataInternalOpd)) {
            $dataInternalOpdArr = $dataInternalOpd->toArray();
        }

        $dataExternalOpdArr = [];
        $dataExternalOpd = SpiExternalOpdDetail::findBy($idInstansi, $validatedRequest['year']);
        if(!empty($dataExternalOpd)) {
            $dataExternalOpdArr = $dataExternalOpd->toArray();
        }

        $dataSpis = $this->mergeInternalExternalOpdByItsOpd($dataInternalOpdArr, $dataExternalOpdArr);
        foreach ($dataSpis as $itemSpi) {
            $result['heat_map'][] = $this->calculateDataSpiJoinedKomponen($groupedKodeMappingGratif, $itemSpi);
        }

        return new SimpleResponse($result);
    }

    private function listDimensi() {
        $result = SpiIntExtKodeMapping::query()
            ->columns('komponen_gratif_key, komponen_gratif, dimensi_gratif_key, dimensi_gratif')
            ->where("dimensi_gratif_key IS NOT NULL")
            ->groupBy('komponen_gratif_key, komponen_gratif, dimensi_gratif_key, dimensi_gratif')
            ->orderBy("dimensi_gratif")
            ->execute();

        return $result;
    }

    private function validateRequestOPDs($request)
    {
        $request['id_instansi'] = array_key_exists('id_instansi', $request) ? $request['id_instansi'] : null;
        $request['id_provinsi'] = array_key_exists('id_provinsi', $request) ? $request['id_provinsi'] : null;
        $request['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $request) ? $request['id_kota_kabupaten'] : null;

        $validatedRequest = $this->validate(
            $request,
            [
                'year' => ['validators' => ['required', 'digit']],
                'id_instansi' => ['validators' => ['digit']],
                'id_provinsi' => ['validators' => ['digit']],
                'id_kota_kabupaten' => ['validators' => ['digit']]
            ]
        );

        if(
            empty($validatedRequest['id_instansi'])
            && empty($validatedRequest['id_provinsi'])
            && empty($validatedRequest['id_kota_kabupaten'])
        ) {
            throw new ValidationException("ID Instansi, Provinsi, atau Kota/Kabupaten wajib diisi!");
        }

        return $validatedRequest;
    }

    private function mergeInternalExternalOpdByItsOpd(array $dataInternalOpdArr, array $dataExternalOpdArr)
    {
        $result = [];

        $tempMerged = [];

        foreach ($dataInternalOpdArr as $obj) {
            $tempMerged[$obj['opd']][] = $obj;
        }

        foreach ($dataExternalOpdArr as $obj) {
            $tempMerged[$obj['opd']][] = $obj;
        }

        foreach ($tempMerged as $opd => $objs) {
            if(count($objs) > 1) {
                $result[] = array_merge($objs[0], $objs[1]);
            }
            else if(count($objs) == 1) {
                $result[] = $objs[0];
            }
        }

        return $result;
    }

    private function calculateDataSpiJoinedKomponen(array $groupedKodeMappingGratif, $dataSpi, $kodeMappingKeyVal = [], $withSkorInstansi = false)
    {
        $result = [
            'opd' => $dataSpi['opd'],
            'indeks' => 0,
            'n' => array_key_exists('n_int', $dataSpi) ? $dataSpi['n_int'] : $dataSpi['n_ext'],
            'dimensi' => []
        ];

        $dataSpiInstansi = [];

        if($withSkorInstansi) {
            $dataSpiInstansi = SpiInternalEksternalDetail::findFirstBy($dataSpi['id_instansi'], $dataSpi['tahun']);
        }

        $totalValueDimensi = 0.0;
        $countDimensiNotZeros = 0;
        foreach ($groupedKodeMappingGratif as $key => $value) {
            foreach ($value['dimensi_gratif'] as $keyDimensi => $valueDimensi) {
                $result['dimensi'][$keyDimensi] = [
                    'dimensi_gratif' => $valueDimensi['dimensi_gratif'],
                    'indeks' => 0
                ];

                if(!empty($kodeMappingKeyVal)) {
                    $result['dimensi'][$keyDimensi]['details'] = [];
                }

                $totalInternal = 0.0;
                $countInternal = 0;
                $totalExternal = 0.0;
                $countExternal = 0;

                foreach ($valueDimensi['internal'] as $code) {
                    if(array_key_exists($code, $dataSpi)) {
                        if($dataSpi[$code] != null && $dataSpi[$code] != 0) {
                            $totalInternal += $dataSpi[$code];
                            $countInternal++;
                            if(!empty($kodeMappingKeyVal)) {
                                $itemPertanyaan = [
                                    'type' => $kodeMappingKeyVal[$code]['type'],
                                    'kode' => $kodeMappingKeyVal[$code]['variable'],
                                    'pertanyaan_gratif' => $kodeMappingKeyVal[$code]['pertanyaan_gratif'],
                                    'kategori_gratif' => $kodeMappingKeyVal[$code]['kategori_gratif'],
                                    'dimensi_gratif' => $valueDimensi['dimensi_gratif'],
                                    'indeks' => $dataSpi[$code]
                                ];

                                if($withSkorInstansi) {
                                    $itemPertanyaan['indeks_instansi'] = $dataSpiInstansi->$code;
                                }

                                $result['dimensi'][$keyDimensi]['details'][] = $itemPertanyaan;
                            }
                        }
                    }
                }

                foreach ($valueDimensi['external'] as $code) {
                    if(array_key_exists($code, $dataSpi)) {
                        if($dataSpi[$code] != null && $dataSpi[$code] != 0) {
                            $totalExternal += $dataSpi[$code];
                            $countExternal++;
                            if(!empty($kodeMappingKeyVal)) {
                                $itemPertanyaan = [
                                    'type' => $kodeMappingKeyVal[$code]['type'],
                                    'kode' => $kodeMappingKeyVal[$code]['variable'],
                                    'pertanyaan_gratif' => $kodeMappingKeyVal[$code]['pertanyaan_gratif'],
                                    'kategori_gratif' => $kodeMappingKeyVal[$code]['kategori_gratif'],
                                    'dimensi_gratif' => $valueDimensi['dimensi_gratif'],
                                    'indeks' => $dataSpi[$code]
                                ];

                                if($withSkorInstansi) {
                                    $itemPertanyaan['indeks_instansi'] = $dataSpiInstansi->$code;
                                }

                                $result['dimensi'][$keyDimensi]['details'][] = $itemPertanyaan;
                            }
                        }
                    }
                }

                if($countInternal + $countExternal > 0) {
                    $rerataDimensi = ($totalInternal + $totalExternal) / ($countInternal + $countExternal);

                    $result['dimensi'][$keyDimensi]['indeks'] = $rerataDimensi;

                    if ($rerataDimensi > 0) {
                        $countDimensiNotZeros++;
                        $totalValueDimensi += $rerataDimensi;
                    }
                }
                else {
                    unset($result['dimensi'][$keyDimensi]);
                }
            }
        }

        if($countDimensiNotZeros > 0) {
            $result['indeks'] = 1.0 * $totalValueDimensi / $countDimensiNotZeros;
        }
        return $result;
    }


    public function getDetailPertanyaanDimensi() {
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.monitoring', 'spi.view_klop_data', 'spi.gratifikasi', 'spi.view_pemda_data']);

        $validatedRequest = $this->validateRequestDetailPertanyaanDimensi($this->request->get());

        $result = [];

        $instansi = $this->getDataInstansi($validatedRequest);
        $idInstansi = $instansi->id;

        $kodeMapping = SpiIntExtKodeMapping::find([
            'conditions' => 'komponen_gratif IS NOT NULL AND dimensi_gratif_key = :dimensi_key:',
            'bind' => [
                "dimensi_key" => $validatedRequest['dimensi_key']
            ],
            'order' => 'code_key ASC'
        ]);

        $dataSpi = $this->getDataSpiOpd($idInstansi, $validatedRequest);

        $dataSpiInstansi = SpiInternalEksternalDetail::findFirstBy($dataSpi['id_instansi'], $dataSpi['tahun']);

        if(!$dataSpiInstansi) {
            throw new ValidationException("data SPI Instansi tidak ditemukan");
        }
        $dataSpiInstansi = $dataSpiInstansi->toArray();

        foreach ($kodeMapping as $itemMapping) {
            if(array_key_exists($itemMapping->code_key, $dataSpi)) {
                if($dataSpi[$itemMapping->code_key] != null && $dataSpi[$itemMapping->code_key] != 0) {
                    $itemResult = [
                        "kode" => $itemMapping->variable,
                        "pertanyaan_gratif" => $itemMapping->pertanyaan_gratif,
                        "kategori_gratif" => $itemMapping->kategori_gratif,
                        "dimensi_gratif_key" => $itemMapping->dimensi_gratif_key,
                        "dimensi_gratif" => $itemMapping->dimensi_gratif,
                        "indeks" => $dataSpi[$itemMapping->code_key],
                        "indeks_instansi" => $dataSpiInstansi[$itemMapping->code_key]
                    ];

                    $result[] = $itemResult;
                }
            }
        }

        return new SimpleResponse($result);
    }

    private function validateRequestDetailPertanyaanDimensi($request)
    {
        $request['id_instansi'] = array_key_exists('id_instansi', $request) ? $request['id_instansi'] : null;
        $request['id_provinsi'] = array_key_exists('id_provinsi', $request) ? $request['id_provinsi'] : null;
        $request['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $request) ? $request['id_kota_kabupaten'] : null;

        $validatedRequest = $this->validate(
            $request,
            [
                'year' => ['validators' => ['required', 'digit']],
                'opd' => ['validators' => ['required']],
                'dimensi_key' => ['validators' => ['required']],
                'id_instansi' => ['validators' => ['digit']],
                'id_provinsi' => ['validators' => ['digit']],
                'id_kota_kabupaten' => ['validators' => ['digit']]
            ]
        );

        if(
            empty($validatedRequest['id_instansi'])
            && empty($validatedRequest['id_provinsi'])
            && empty($validatedRequest['id_kota_kabupaten'])
        ) {
            throw new ValidationException("ID Instansi, Provinsi, atau Kota/Kabupaten wajib diisi!");
        }

        return $validatedRequest;
    }

    public function listIndeksTematik()
    {
        $validatedRequest = $this->validatedListTematikRequest();

        if($validatedRequest['instansi_type'] == 'KL') {
            $result = $this->getIndeksKementerian($validatedRequest);
        }
        else {
            $result = $this->getIndeksPemda($validatedRequest);
        }

        return $result;
    }

    public function listDetailTematik()
    {
        $validatedRequest = array_merge(
            $this->validatedListTematikRequest(),
            ["is_use_detail" => 1]
        );

        if(strtoupper($validatedRequest['instansi_type']) === 'KL') {
            $result = $this->getIndeksKementerian($validatedRequest, true);
        }
        else {
            $result = $this->getIndeksPemda($validatedRequest, true);
        }

        $arrayResult = $result->buildBody();
        $kodeMapping = SpiIntExtKodeMapping::findGratifikasiOnly();
        $groupedKodeMappingGratif = $this->groupingByKodeMapping($kodeMapping);
        foreach ($arrayResult["data"]["result"] as $index => $instansi) {
            $idInstansi = $instansi["id_instansi"];

            $dataInternalOpdArr = [];
            $dataInternalOpd = SpiInternalOpdDetail::findBy($idInstansi, $validatedRequest['year']);
            if(!empty($dataInternalOpd)) {
                $dataInternalOpdArr = $dataInternalOpd->toArray();
            }

            $dataExternalOpdArr = [];
            $dataExternalOpd = SpiExternalOpdDetail::findBy($idInstansi, $validatedRequest['year']);
            if(!empty($dataExternalOpd)) {
                $dataExternalOpdArr = $dataExternalOpd->toArray();
            }

            $opds = [];
            $dataSpis = $this->mergeInternalExternalOpdByItsOpd($dataInternalOpdArr, $dataExternalOpdArr);
            foreach ($dataSpis as $itemSpi) {
                $opd = $this->buildOpdGratifSummary($groupedKodeMappingGratif, $itemSpi);
                $opds[] = $opd;
            }

            $arrayResult["data"]["result"][$index]["opd"] = $opds;
        }

        return new PaginatedResponse(
            $arrayResult["data"]["result"],
            $arrayResult["data"]["total_record"],
            $arrayResult["data"]["per_page"],
            $arrayResult["data"]["current_page"] - 1
        );
    }

    private function buildOpdGratifSummary(array $groupedKodeMappingGratif, array $dataSpis): array
    {
        $result = [
            'opd'    => $dataSpis['opd'] ?? ($dataSpis['nama_instansi'] ?? null),
            'indeks_gratif' => null,
            'indeks_spi_internal' => array_key_exists('indeks_internal', $dataSpis) ? floatval($dataSpis['indeks_internal']) : null,
            'indeks_spi_eksternal' => array_key_exists('indeks_eksternal', $dataSpis) ? floatval($dataSpis['indeks_eksternal']) : null,
            'n'      => $dataSpis['n'] ?? ($dataSpis['n_int'] ?? null),
            'komponen' => [],
        ];

        $allDimIndices = [];

        foreach ($groupedKodeMappingGratif as $kompKey => $kompDef) {
            $dimensiOut = [];
            $dimIndicesForComponent = [];

            $dimensiDefs = $kompDef['dimensi_gratif'] ?? [];
            foreach ($dimensiDefs as $dimKey => $dimDef) {
                $codes = array_merge($dimDef['internal'] ?? [], $dimDef['external'] ?? []);

                $values = [];
                foreach ($codes as $code) {
                    if (array_key_exists($code, $dataSpis) && $dataSpis[$code] !== '' && $dataSpis[$code] !== null) {
                        $values[] = (float) $dataSpis[$code];
                    }
                }

                $dimAvg = count($values) ? array_sum($values) / count($values) : null;

                if ($dimAvg !== null) {
                    $dimIndicesForComponent[] = $dimAvg;
                    $allDimIndices[] = $dimAvg;
                }

                $dimensiOut[$dimKey] = [
                    'dimensi_gratif' => $dimDef['dimensi_gratif'] ?? $dimKey,
                    'indeks'         => $dimAvg,
                ];
            }

            $kompAvg = count($dimIndicesForComponent)
                ? array_sum($dimIndicesForComponent) / count($dimIndicesForComponent)
                : null;

            $result['komponen'][$kompKey] = [
                'komponen_gratif' => $kompDef['komponen_gratif'] ?? $kompKey,
                'indeks'          => $kompAvg,
                'dimensi'         => $dimensiOut,
            ];
        }

        $result['indeks_gratif'] = count($allDimIndices)
            ? array_sum($allDimIndices) / count($allDimIndices)
            : null;

        return $result;
    }

    private function validatedListTematikRequest()
    {
        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.monitoring', 'spi.view_klop_data', 'spi.gratifikasi', 'spi.view_pemda_data']);

        $request = $this->request->get();
        $request['id_instansi'] = array_key_exists('id_instansi', $request) ? $request['id_instansi'] : null;
        $request['id_provinsi'] = array_key_exists('id_provinsi', $request) ? $request['id_provinsi'] : null;
        $request['id_kota_kabupaten'] = array_key_exists('id_kota_kabupaten', $request) ? $request['id_kota_kabupaten'] : null;
        $request['instansi_type'] = array_key_exists('instansi_type', $request) ? $request['instansi_type'] : null;
        $request['limit'] = array_key_exists('limit', $request) ? $request['limit'] : 10;
        $request['offset'] = array_key_exists('offset', $request) ? $request['offset'] : 0;
        $request['order'] = array_key_exists('order', $request) ? $request['order'] : 'indeks_gratif';
        $request['sort'] = array_key_exists('sort', $request) ? $request['sort'] : 'DESC';
        $request['is_use_detail'] = array_key_exists('is_use_detail', $request) ? $request['is_use_detail'] : 0;

        $allowedInstansiType = ['KL', 'PEMDA'];
        $allowedOrderBy = ['id_instansi', 'nama_instansi', 'indeks_gratif'];
        $allowedSort = ['ASC', 'DESC'];

        return $this->validate(
            $request,
            [
                'year' => ['validators' => ['required', 'digit']],
                'instansi_type' => ['validators' => ['required',[
                    'name' => 'inclusion_in',
                    'params' => $allowedInstansiType
                ]]],
                'id_instansi' => ['validators' => ['digit']],
                'id_provinsi' => ['validators' => ['digit']],
                'id_kota_kabupaten' => ['validators' => ['digit']],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'order' => ['validators' => [[
                    'name' => 'inclusion_in',
                    'params' => $allowedOrderBy
                ]]],
                'sort' => ['validators' => [[
                    'name' => 'inclusion_in',
                    'params' => $allowedSort
                ]]],
                'is_use_detail' => ['validators' => ['digit']],
            ]
        );
    }

    private function getIndeksKementerian($validatedRequest, $parentOnly = false)
    {
        $strWhere = 'tahun = :year: AND id_tipe_instansi <> 1 AND id_tipe_instansi <> 2 AND src = :src:'; // id_tipe_instansi 1 & 2 adalah Pemda
        if ($parentOnly) {
            $strWhere .= " AND src = 'INSTANSI'";
        }
        $bind = [
            'year' => $validatedRequest['year'],
            'src' => ViewSpiTematikGratifikasi::SRC_INSTANSI
        ];
        if(!empty($validatedRequest['id_instansi'])) {
            $strWhere .= ' AND id_instansi = :id_instansi:';
            $bind['id_instansi'] = $validatedRequest['id_instansi'];
        }

        $findValue = [
            'columns' => 'tahun, id_instansi, id_instansi_gol, id_provinsi, id_kota_kabupaten, nama_instansi, opd, indeks_gratif',
            'conditions' => $strWhere,
            'bind' => $bind,
            'order' => $validatedRequest['order'] . ' ' . $validatedRequest['sort']
        ];

        if($validatedRequest['limit'] > 0) {
            $findValue['limit'] = $validatedRequest['limit'];
            $findValue['offset'] = $validatedRequest['offset'];
        }

        $result = ViewSpiTematikGratifikasi::find($findValue);

        $totalRecords = ViewSpiTematikGratifikasi::count([
            'conditions' => $strWhere,
            'bind' => $bind,
        ]);

        if ($validatedRequest['is_use_detail']) {
            $kodeMapping = SpiIntExtKodeMapping::findGratifikasiOnly();
            $groupedKodeMappingGratif = $this->groupingByKodeMapping($kodeMapping);
            $rows = $result;
            $result = [];
            foreach ($rows as $row) {
                $idInstansi = $row->id_instansi;
                $validatedRequest['id_instansi'] = $idInstansi;
                $res = $row->toArray();
                $dataSpi = SpiInternalEksternalDetail::findFirstBy($idInstansi, $validatedRequest['year']);
                if ($parentOnly) {
                    $res['indeks_spi'] = $dataSpi->indeks;
                }
                $res['detail_spi'] = $this->calculateDataSpiSeparateByKomponen($groupedKodeMappingGratif, $dataSpi->toArray(), []);
                $result[] = $res;
            }
        }

        return new PaginatedResponse($result, $totalRecords, $validatedRequest['limit'] == 0 ? $totalRecords : $validatedRequest['limit'], $validatedRequest['offset']);
    }

    private function getIndeksPemda($validatedRequest, $parentOnly = false)
    {
        $strWhere = 'tahun = :year: AND (id_tipe_instansi = 1 OR id_tipe_instansi = 2)'; // id_tipe_instansi 1 & 2 adalah Pemda
        if ($parentOnly) {
            $strWhere .= " AND src = 'INSTANSI'";
        }
        $bind = [
            'year' => $validatedRequest['year'],
        ];
        if(!empty($validatedRequest['id_instansi'])) {
            $strWhere .= ' AND id_instansi = :id_instansi:';
            $bind['id_instansi'] = $validatedRequest['id_instansi'];
        }
        if(!empty($validatedRequest['id_provinsi'])) {
            $strWhere .= ' AND id_provinsi = :id_provinsi:';
            $bind['id_provinsi'] = $validatedRequest['id_provinsi'];
        }
        if(!empty($validatedRequest['id_kota_kabupaten'])) {
            $strWhere .= ' AND id_kota_kabupaten = :id_kota_kabupaten:';
            $bind['id_kota_kabupaten'] = $validatedRequest['id_kota_kabupaten'];
        }

        $findValue = [
            'columns' => 'tahun, id_instansi, id_instansi_gol, id_provinsi, id_kota_kabupaten, nama_instansi, opd, indeks_gratif',
            'conditions' => $strWhere,
            'bind' => $bind,
            'order' => $validatedRequest['order'] . ' ' . $validatedRequest['sort']
        ];

        if($validatedRequest['limit'] > 0) {
            $findValue['limit'] = $validatedRequest['limit'];
            $findValue['offset'] = $validatedRequest['offset'];
        }

        $result = ViewSpiTematikGratifikasi::find($findValue);

        $totalRecords = ViewSpiTematikGratifikasi::count([
            'conditions' => $strWhere,
            'bind' => $bind,
        ]);

        if ($validatedRequest['is_use_detail']) {
            $kodeMapping = SpiIntExtKodeMapping::findGratifikasiOnly();
            $groupedKodeMappingGratif = $this->groupingByKodeMapping($kodeMapping);
            $rows = $result;
            $result = [];
            foreach ($rows as $row) {
                $idInstansi = $row->id_instansi;
                $validatedRequest['id_instansi'] = $idInstansi;
                $res = $row->toArray();
                $dataSpi = SpiInternalEksternalDetail::findFirstBy($idInstansi, $validatedRequest['year']);
                if ($parentOnly) {
                    $res['indeks_spi'] = $dataSpi->indeks;
                }
                $res['detail_spi'] = $this->calculateDataSpiSeparateByKomponen($groupedKodeMappingGratif, $dataSpi->toArray(), []);
                $result[] = $res;
            }
        }

        return new PaginatedResponse($result, $totalRecords, $validatedRequest['limit'] == 0 ? $totalRecords : $validatedRequest['limit'], $validatedRequest['offset']);
    }
}