<?php

class SpiService {
    public $context;
    public $tahun;
    public $data;
    public $row;
    public $idInstansi;
    public $idProvinsi;
    public $idKabKota;

    protected $indeks_nasional_spi_2024;

    protected $komponen_indeks_internal_nasional_2024;
    protected $komponen_indeks_eksternal_nasional_2024;
    protected $komponen_indeks_eksper_nasional_2024;
    protected $faktor_koreksi_prevalensi_korupsi_nasional_2024;
    protected $faktor_koreksi_integritas_pelaksanaan_survey_2024;

    protected $integritas_pelaksanaan_tugas_nasional_2024;
    protected $pengelolaan_anggaran_nasional_2024;
    protected $pengelolaan_pbj_nasional_2024;
    protected $pengelolaan_sdm_nasional_2024;
    protected $trading_influence_nasional_2024;
    protected $sosialisasi_antikorupsi_nasional_2024;
    protected $transparansi_nasional_2024;
    protected $integritas_pegawai_nasional_2024;
    protected $transparansi_keadilan_layanan_nasional_2024;
    protected $upaya_pencegahan_korupsi_nasional_2024;
    protected $integritas_instansi_nasional_2024;

    public function __construct($context, $tahun)
    {
        $this->context = $context;
        $this->tahun = $tahun;
        $this->_buildData();

        $this->indeks_nasional_spi_2024 = 71.53;

        $this->komponen_indeks_internal_nasional_2024 = 72.34;
        $this->komponen_indeks_eksternal_nasional_2024 = 86.44;
        $this->komponen_indeks_eksper_nasional_2024 = 69.68;
        $this->faktor_koreksi_prevalensi_korupsi_nasional_2024 = 1.08;
        $this->faktor_koreksi_integritas_pelaksanaan_survey_2024 = 3.38;

        $this->integritas_pelaksanaan_tugas_nasional_2024 = 74.08;
        $this->pengelolaan_anggaran_nasional_2024 = 70.84;
        $this->pengelolaan_pbj_nasional_2024 = 64.83;
        $this->pengelolaan_sdm_nasional_2024 = 65.93;
        $this->trading_influence_nasional_2024 = 78.83;
        $this->sosialisasi_antikorupsi_nasional_2024 = 70.17;
        $this->transparansi_nasional_2024 = 83.48;
        $this->integritas_pegawai_nasional_2024 = 95.73;
        $this->transparansi_keadilan_layanan_nasional_2024 = 82.5;
        $this->upaya_pencegahan_korupsi_nasional_2024 = 80.01;
        $this->integritas_instansi_nasional_2024 = 69.68;
    }

    private function _buildData() {
        $this->data = $this->context->rawQuery("
            select spi.*, case when ins.id_tipe in (1,2) then 'pemda' else 'kl' end as kategori_instansi, ins.id_tipe as tipe_instansi, ins.id_sub_tipe as sub_tipe_instansi
            from jaga.spi_internal_eksternal_detail spi
            left join jaga.instansi ins on ins.id = spi.id_instansi
            where spi.tahun = :tahun
        ", ["tahun" => $this->tahun]);
    }

    private function _buildInstansiFromProvinsi() {
        $this->checkRequiredProvinsi();

        $row = ArrayHelper::itemWithConditions(
            $this->data,
            [
                "id_provinsi" => $this->idProvinsi,
                "tipe_instansi" => 1
            ]
        );
        if (!$row) throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Provinsi tidak ditemukan!");

        $this->row = $row;
        $this->idInstansi = $row["id_instansi"];
    }

    private function _buildInstansiFromKabKota() {
        $this->checkRequiredKabKota();

        $row = ArrayHelper::itemWithConditions(
            $this->data,
            [
                "id_kota_kabupaten" => $this->idKabKota,
                "tipe_instansi" => 2
            ]
        );
        if (!$row) throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Kabupaten/Kota tidak ditemukan!");

        $this->row = $row;
        $this->idInstansi = $row["id_instansi"];
        $this->idProvinsi = $row["id_provinsi"];
    }

    private function _buildInstansi() {
        $this->checkRequiredInstansi();

        $row = ArrayHelper::itemWithConditions(
            $this->data,
            [
                "id_instansi" => $this->idInstansi,
                "kategori_instansi" => 'kl'
            ]
        );
        if (!$row) throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan!");

        $this->row = $row;
    }

    public function summaryNasional() {
        return [
            "rerata_nasional" => $this->rerataNasional($this->tahun),
            "rerata_nasional_prev_year" => $this->rerataNasional($this->tahun - 1),
            "rerata_pemda" => $this->rerataPemda(),
            "rerata_kl" => $this->rerataKL(),
            "detail_spi" =>  $this->rincianSpiNasional($this->tahun),
            "top_3_pemda_terjaga" => $this->getTopInstitution($this->tahun, 'PEMDA'),
            "top_3_pemda_rentan" => $this->getBottomInstitution($this->tahun, 'PEMDA'),
            "top_3_kl_terjaga" => $this->getTopInstitution($this->tahun, 'KL'),
            "top_3_kl_rentan" => $this->getBottomInstitution($this->tahun, 'KL'),
            "search_recent_result_pemda" => $this->getSearchRecentResultInstitution('PEMDA'),
            "search_recent_result_kl" => $this->getSearchRecentResultInstitution('KL')
        ];
    }

    private function getSearchRecentResultInstitution($instansiTipe)
    {
        switch ($instansiTipe) {
            CASE "PEMDA":
                return $this->context->rawQuery("
                    SELECT
                        CASE
                            WHEN isc.id_instansi IS NULL AND isc.id_provinsi IS NOT NULL AND isc.id_kota_kabupaten IS NULL
                            THEN isc.id_provinsi
                            WHEN isc.id_instansi IS NULL AND isc.id_provinsi IS NULL AND isc.id_kota_kabupaten IS NOT NULL
                            THEN isc.id_kota_kabupaten
                            ELSE null
                        END as id_instansi,
                        CASE
                            WHEN isc.id_instansi IS NULL AND isc.id_provinsi IS NOT NULL AND isc.id_kota_kabupaten IS NULL
                            THEN CONCAT(
                                'PROVINSI ', UPPER(
                                    (
                                        SELECT
                                            mp.provinsi_name
                                        FROM
                                            jaga.master_provinsi mp
                                        WHERE
                                            mp.provinsi_code = isc.id_provinsi::text
                                    )
                                )
                            ) 
                            WHEN isc.id_instansi IS NULL AND isc.id_provinsi IS NULL AND isc.id_kota_kabupaten IS NOT NULL
                            THEN UPPER(                    
                                (
                                    SELECT
                                        mkk.kota_kabupaten_name
                                    FROM
                                        jaga.master_kota_kabupaten mkk
                                    WHERE
                                        mkk.kota_kabupaten_code = isc.id_kota_kabupaten::text
                                )
                            )
                            ELSE 'Tidak diketahui'
                        END AS name,
                        isc.counter
                    FROM
                        jaga.instansi_search_counts isc
                    WHERE
                        isc.id_instansi IS NULL
                    ORDER BY
                        isc.counter DESC
                    LIMIT
                        3
                ");
                break;
            CASE "KL":
                return $this->context->rawQuery("
                    SELECT
                        CASE
                            WHEN isc.id_instansi IS NOT NULL AND isc.id_provinsi IS NULL AND isc.id_kota_kabupaten IS NULL
                            THEN isc.id_instansi
                            ELSE null
                        END as id_instansi,
                        CASE
                            WHEN isc.id_instansi IS NOT NULL AND isc.id_provinsi IS NULL AND isc.id_kota_kabupaten IS NULL
                            THEN UPPER(
                                (
                                    SELECT
                                        i.nama
                                    FROM
                                        jaga.instansi i
                                    WHERE
                                        i.id = isc.id_instansi
                                )
                            )
                            ELSE 'Tidak diketahui'
                        END AS name,
                        isc.counter
                    FROM
                        jaga.instansi_search_counts isc
                    WHERE
                        isc.id_instansi IS NOT NULL
                    ORDER BY
                        isc.counter DESC
                    LIMIT
                        3
                ");
                break;
            DEFAULT:
                return 'Tidak ditemukan';
        }
    }

    private function baseQueryTopInstituion()
    {
        return "
            SELECT
                sied.id,
                i.id as id_instansi,
                i.nama as nama_instansi,
                sied.nama_instansi as spi_nama_instansi,
                sied.tahun,
                sied.indeks
            FROM
                jaga.spi_internal_eksternal_detail sied
            JOIN
                jaga.instansi i ON i.id = sied.id_instansi
        ";
    }

    private function getTopInstitution($year, $institution, $limit = 3)
    {
        $query = $this->baseQueryTopInstituion();
        $query .= "
            WHERE
                sied.tahun = :tahun
        ";

        $klpd = $institution === 'KL' ? " AND lower(i.nama) NOT LIKE 'pemerintah%'" : " AND lower(i.nama) LIKE 'pemerintah%'";

        return $this->context->rawQuery($query .= $klpd . " ORDER BY sied.indeks DESC LIMIT :limit", [
            'tahun' => $year,
            'limit' => $limit
        ]);
    }

    private function getBottomInstitution($year, $institution, $limit = 3)
    {
        $query = $this->baseQueryTopInstituion();
        $query .= "
            WHERE
                sied.tahun = :tahun
        ";

        $klpd = $institution === 'KL' ? " AND lower(i.nama) NOT LIKE 'pemerintah%'" : " AND lower(i.nama) LIKE 'pemerintah%'";

        return $this->context->rawQuery($query .= $klpd . " ORDER BY sied.indeks ASC LIMIT :limit", [
            'tahun' => $year,
            'limit' => $limit
        ]);
    }

    private function rincianSpiNasional($tahun)
    {
        $result = [
            "komponen_internal" => [
                "indeks" => round(
                    $tahun === '2024' ?
                    $this->komponen_indeks_internal_nasional_2024 :
                    ArrayHelper::averageByKey($this->data, "indeks_internal"),
                    2
                ),
                "dimensi" => [],
                "unit_kerja" => []
            ],
            "komponen_eksternal" => [
                "indeks" => round(
                    $tahun === '2024' ?
                    $this->komponen_indeks_eksternal_nasional_2024 :
                    ArrayHelper::averageByKey($this->data, "indeks_eksternal"),
                    2
                ),
                "dimensi" => [],
                "unit_kerja" => []
            ],
            "komponen_eksper" => [
                "indeks" => round(
                    $tahun === '2024' ?
                    $this->komponen_indeks_eksper_nasional_2024 :
                    ArrayHelper::averageByKey($this->data, "indeks_eksper"),
                    2
                ),
                "dimensi" => [],
                "unit_kerja" => []
            ],
            "faktor_koreksi" => [
                "fakta_korupsi" => round(
                    $tahun === '2024' ?
                    $this->faktor_koreksi_prevalensi_korupsi_nasional_2024 :
                    ArrayHelper::averageByKey($this->data, "koreksi_prevalensi_korupsi"),
                    2
                ),
                "pelaksanaan_spi" => round(
                    $tahun === '2024' ?
                    $this->faktor_koreksi_integritas_pelaksanaan_survey_2024 :
                    ArrayHelper::averageByKey($this->data, "koreksi_integritas_pelaksanaan_survei"),
                    2
                )
            ]
        ];

        $kodeDimensiMapping = SpiIntExtKodeMapping::query()
            ->columns("indikator_key, indikator, indikator_en, type")
            ->groupBy('indikator_key, indikator, indikator_en, type')
            ->orderBy('indikator')
            ->execute();

        foreach($kodeDimensiMapping as $dimensi) {
            if ($dimensi["type"] == "INTERNAL") {
                $typeKey = "komponen_internal";
            } else if ($dimensi["type"] == "EXTERNAL") {
                $typeKey = "komponen_eksternal";
            } else if ($dimensi["type"] == "EXPERT") {
                $typeKey = "komponen_eksper";
            } else {
                $typeKey = "unknown";
            }

            try {
                $skor = round(ArrayHelper::averageByKey($this->data, $dimensi->indikator_key), 2);
                if ($tahun === '2024') {
                    SWITCH ($dimensi->indikator_key) {
                        CASE "integritas_tugas":
                            $skor = $this->integritas_pelaksanaan_tugas_nasional_2024;
                            break;
                        CASE "pengelolaan_anggaran":
                            $skor = $this->pengelolaan_anggaran_nasional_2024;
                            break;
                        CASE "pengelolaan_pbj":
                            $skor = $this->pengelolaan_pbj_nasional_2024;
                            break;
                        CASE "pengelolaan_sdm":
                            $skor = $this->pengelolaan_sdm_nasional_2024;
                            break;
                        CASE "trading_in_influence":
                            $skor = $this->trading_influence_nasional_2024;
                            break;
                        CASE "sosialisasi_antikorupsi":
                            $skor = $this->sosialisasi_antikorupsi_nasional_2024;
                            break;
                        CASE "transparansi":
                            $skor = $this->transparansi_nasional_2024;
                            break;
                        CASE "transparansi_keadilan_layanan":
                            $skor = $this->transparansi_keadilan_layanan_nasional_2024;
                            break;  
                        CASE "upaya_pencegah_korupsi":
                            $skor = $this->upaya_pencegahan_korupsi_nasional_2024;
                            break;
                        CASE "integritas_pegawai":
                            $skor = $this->integritas_pegawai_nasional_2024;
                            break;
                        CASE "integritas_instansi":
                            $skor = $this->integritas_instansi_nasional_2024;
                            break;
                        DEFAULT:
                            $skor = 0;
                    }
                }
            } catch (\Exception $e) {
                $skor = 0;
            }
            $result[$typeKey]["dimensi"][] = [
                "grup_dimensi" => $dimensi->indikator,
                "grup_dimensi_en" => $dimensi->indikator_en,
                "skor" => $skor
            ];
        }

        return $result;
    }

    public function summaryProvinsi() {
        $this->_buildInstansiFromProvinsi();
        return array_merge(
            $this->summaryNasional(),
            [
                "rerata_provinsi" => $this->rerataProvinsi($this->tahun),
                "rerata_provinsi_prev_year" => $this->rerataProvinsi($this->tahun - 1),
                "nilai_instansi" => $this->nilaiInstansi($this->tahun),
                "nilai_instansi_prev_year" => $this->nilaiInstansi($this->tahun - 1),
                "jumlah_pemda" => $this->jumlahPemda(),
                "kelompok_anggaran" => $this->kelompokAnggaranProvinsi(),
                "highlight_ranking" => $this->highlightRankingProvinsi(),
                "tren_spi" => $this->trenNilaiInstansiUsingQuery(),
                "detail_spi" => $this->rincianSpiInstansi()
            ]
        );
    }

    public function summaryKabKota() {
        $this->_buildInstansiFromKabKota();
        return array_merge(
            $this->summaryNasional(),
            [
                "rerata_provinsi" => $this->rerataProvinsi($this->tahun),
                "rerata_provinsi_prev_year" => $this->rerataProvinsi($this->tahun - 1),
                "nilai_instansi" => $this->nilaiInstansi($this->tahun),
                "nilai_instansi_prev_year" => $this->nilaiInstansi($this->tahun - 1),
                "highlight_ranking" => $this->highlightRankingKabKota(),
                "tren_spi" => $this->trenNilaiInstansiUsingQuery(),
                "detail_spi" => $this->rincianSpiInstansi()
            ]
        );
    }

    public function summaryKL() {
        $this->_buildInstansi();
        return array_merge(
            $this->summaryNasional(),
            [
                "nilai_instansi" => $this->nilaiInstansi($this->tahun),
                "nilai_instansi_prev_year" => $this->nilaiInstansi($this->tahun - 1),
                "kelompok_anggaran" => $this->kelompokAnggaranKL(),
                "highlight_ranking" => $this->highlightRankingKL(),
                "tren_spi" => $this->trenNilaiInstansiUsingQuery(),
                "detail_spi" => $this->rincianSpiInstansi(),
                "has_perluasan_ereport" => $this->hasPerluasanEreport()
            ]
        );
    }

    private function hasPerluasanEreport()
    {
        $ereport = $this->context->rawQuery("
            SELECT * FROM jaga.spi_perluasan_ereport_files WHERE id_instansi = :instansi_id
        ", ["instansi_id" => $this->idInstansi]);

        if (empty($ereport)) {
            return false;
        }

        return true;

        // $key = $ereport[0]['filename'] . '.rar';
        // $service = new AttachmentService($this->context->shared, $this->context->minio);
        // return $service->checkFilePublicInSecondaryS3(
        //     'public_content' . DIRECTORY_SEPARATOR . 'file-spi' . DIRECTORY_SEPARATOR . $ereport[0]['tahun'] . DIRECTORY_SEPARATOR . 'pdf' . DIRECTORY_SEPARATOR . 'e-report' . DIRECTORY_SEPARATOR . 'perluasan',
        //     $key
        // );
    }

    private function rerataNasional($tahun) {
        if ($tahun == $this->tahun) {
            $result = $tahun === '2024' ? $this->indeks_nasional_spi_2024 : ArrayHelper::averageByKey($this->data, "indeks");
        } else {
            $result = $this->rerataNasionalUsingQuery($tahun);
        }

        return round($result, 2);
    }

    private function rerataNasionalUsingQuery($tahun) {
        $data = SpiInternalEksternalDetail::query()
            ->columns("AVG(indeks) as avg")
            ->conditions("tahun = :tahun:")
            ->bind([
                'tahun' => $tahun
            ])
            ->execute();

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

    private function rerataPemda() {
        $conditions = ["kategori_instansi" => "pemda"];
        return [
            "total_pemda" => ArrayHelper::countWithConditions(
                $this->data,
                $conditions
            ),
            "avg_spi" => round(
                ArrayHelper::averageByKeyWithConditions(
                    $this->data,
                    "indeks",
                    $conditions
                ), 2
            )
        ];
    }

    private function rerataKL() {
        $conditions = ["kategori_instansi" => "kl"];
        return [
            "total_kl" => ArrayHelper::countWithConditions(
                $this->data,
                $conditions
            ),
            "avg_spi" => round(
                ArrayHelper::averageByKeyWithConditions(
                    $this->data,
                    "indeks",
                    $conditions
                ), 2
            )
        ];
    }

    private function rerataProvinsi($tahun) {
        $this->checkRequiredProvinsi();

        if ($tahun == $this->tahun) {
            $result = round(
                ArrayHelper::averageByKeyWithConditions(
                    $this->data,
                    "indeks",
                    [
                        "id_provinsi" => $this->idProvinsi,
                        "kategori_instansi" => "pemda"
                    ]
                ), 2
            );
        } else {
            $result = $this->rerataProvinsiUsingQuery($tahun);
        }

        return $result;
    }

    private function rerataProvinsiUsingQuery($tahun) {
        $this->checkRequiredProvinsi();

        $data = SpiInternalEksternalDetail::query()
            ->columns("avg(indeks) as avg")
            ->conditions("tahun = :tahun: AND id_provinsi = :id_provinsi:")
            ->bind([
                'tahun' => $tahun,
                'id_provinsi' => $this->idProvinsi
            ])
            ->execute();

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

        return $result;
    }

    private function nilaiInstansi($tahun) {
        if (empty($this->idInstansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "ID Instansi belum diset!");
        }

        if ($tahun == $this->tahun) {
            $result = round(
                ArrayHelper::valueByKeyWithConditions(
                    $this->data,
                    "indeks",
                    [
                        "id_instansi" => $this->idInstansi
                    ]
                ), 2
            );
        } else {
            $result = $this->nilaiInstansiUsingQuery($tahun);
        }

        return $result;
    }

    private function nilaiInstansiUsingQuery($tahun) {
        $this->checkRequiredInstansi();

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

    private function tertinggiTerendahDaerah($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 highlightRankingKL() {
        return $this->highlightRankingUsingQuery(SPIHasilKLPD::LEVEL_KEMENTERIAN);
    }

    private function highlightRankingProvinsi() {
        return $this->highlightRankingUsingQuery(SPIHasilKLPD::LEVEL_PROVINSI);
    }

    private function highlightRankingKabKota() {
        return $this->highlightRankingUsingQuery(SPIHasilKLPD::LEVEL_KABUPATEN);
    }

    private function highlightRankingUsingQuery($level)
    {
        $this->checkRequiredInstansi();
        $instansi = Instansi::findFirst($this->idInstansi);
        $kelompokAnggaran = $instansi->kelompok_anggaran;

        $result = [
            "kelompok_anggaran" => [
                'code' => $instansi->kelompok_anggaran,
                'name' => $instansi->nama_kelompok_anggaran,
                'name_en' => $instansi->nama_kelompok_anggaran_en
            ],
            "list_ranking" => []
        ];

        $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_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->context->rawQuery($q, [
                'tahun' => $this->tahun,
                'id_instansi' => $this->idInstansi
            ]);
        }

        return $result;
    }

    private function jumlahPemda() {
        $this->checkRequiredProvinsi();
        return [
            "provinsi" => ArrayHelper::countWithConditions(
                $this->data,
                [
                    "id_provinsi" => $this->idProvinsi,
                    "id_kota_kabupaten" => null
                ]
            ),
            "kota" => ArrayHelper::countWithConditions(
                $this->data,
                [
                    "id_provinsi" => $this->idProvinsi,
                    "sub_tipe_instansi" => 2
                ]
            ),
            "kabupaten" => ArrayHelper::countWithConditions(
                $this->data,
                [
                    "id_provinsi" => $this->idProvinsi,
                    "sub_tipe_instansi" => 1
                ]
            )
        ];
    }

    private function kelompokAnggaranProvinsi()
    {
        return $this->kelompokAnggaranUsingQuery(SPIHasilKLPD::LEVEL_PROVINSI);
    }

    private function kelompokAnggaranKL()
    {
        return $this->kelompokAnggaranUsingQuery(SPIHasilKLPD::LEVEL_KEMENTERIAN);
    }

    private function kelompokAnggaranUsingQuery($level) {
        $idTipe = SPIHasilKLPD::idTipeByLevel($level);
        $result = [];
        $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,
                    'nama_kelompok_anggaran_en' => $item->nama_kelompok_anggaran_en,
                    'items' => []
                ];
            }
            $result[$item->kelompok_anggaran]['items'][] = $item->nama;
        }

        return $result;
    }

    private function trenNilaiInstansiUsingQuery()
    {
        $this->checkRequiredInstansi();
        $result = [
            'skor_spi' => [],
            'internal' => [],
            'eksternal' => [],
            'eksper' => [],
        ];

        $data = SpiInternalEksternalDetail::find([
            "columns" => "tahun, indeks, indeks_internal, indeks_eksternal, indeks_eksper, koreksi_prevalensi_korupsi, koreksi_integritas_pelaksanaan_survei",
            "conditions" => "id_instansi = :id_instansi:",
            "bind" => [
                "id_instansi" => $this->idInstansi
            ],
            "order" => "tahun"
        ]);

        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);
            $result['eksper'][$item->tahun] = round($item->indeks_eksper, 2);
            $result['fakta_korupsi'][$item->tahun] = round($item->koreksi_prevalensi_korupsi, 2);
            $result['pelaksanaan_spi'][$item->tahun] = round($item->koreksi_integritas_pelaksanaan_survei, 2);
        }
        return $result;
    }

    private function trenNilaiUnitUsingQuery($idUnit, $idInstansi)
    {
        $result = [
            'skor_spi' => [],
            'internal' => [],
            'eksternal' => [],
            'eksper' => [],
        ];

        $data = SpiInternalEksternalDetail::find([
            "columns" => "tahun, indeks, indeks_internal, indeks_eksternal",
            "conditions" => "id_instansi = :id_instansi:",
            "bind" => [
                "id_instansi" => $idUnit
            ],
            "order" => "tahun"
        ]);

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

        $data = SpiInternalEksternalDetail::find([
            "columns" => "tahun, indeks, indeks_internal, indeks_eksternal",
            "conditions" => "id_instansi = :id_instansi:",
            "bind" => [
                "id_instansi" => $idInstansi
            ],
            "order" => "tahun"
        ]);

        foreach ($data as $item) {
            $result['eksper'][$item->tahun] = round($item->indeks_eksper, 2);
        }

        return $result;
    }

    private function rincianSpiInstansi() {
        $result = [
            "komponen_internal" => [
                "indeks" => round($this->row["indeks_internal"], 2),
                "dimensi" => [],
                "unit_kerja" => $this->listUnitKerjaInternal()
            ],
            "komponen_eksternal" => [
                "indeks" => round($this->row["indeks_eksternal"], 2),
                "dimensi" => [],
                "unit_kerja" => $this->listUnitKerjaEksternal()
            ],
            "komponen_eksper" => [
                "indeks" => round($this->row["indeks_eksper"], 2),
                "dimensi" => [],
                "bidang" => $this->getBidangExpert()
            ],
            "faktor_koreksi" => [
                "fakta_korupsi" => round($this->row["koreksi_prevalensi_korupsi"], 2),
                "pelaksanaan_spi" => round($this->row["koreksi_integritas_pelaksanaan_survei"], 2),
            ]
        ];

        $kodeDimensiMapping = SpiIntExtKodeMapping::query()
            ->columns("indikator_key, indikator, indikator_en, type")
            ->groupBy('indikator_key, indikator, indikator_en, type')
            ->orderBy('indikator')
            ->execute();

        foreach ($kodeDimensiMapping as $dimensi) {
            if ($dimensi["type"] == "INTERNAL") {
                $typeKey = "komponen_internal";
            } else if ($dimensi["type"] == "EXTERNAL") {
                $typeKey = "komponen_eksternal";
            } else if ($dimensi["type"] == "EXPERT") {
                $typeKey = "komponen_eksper";
            } else {
                $typeKey = "unknown";
            }

            try {
                $skor = round($this->row[$dimensi->indikator_key], 2);
            } catch (\Exception $e) {
                $skor = 0;
            }
            $result[$typeKey]["dimensi"][] = [
                "grup_dimensi" => $dimensi->indikator,
                "grup_dimensi_en" => $dimensi->indikator_en,
                "skor" => $skor
            ];
        }

        return $result;
    }

    private function listUnitKerjaInternal() {
        return $this->listUnitKerja("INTERNAL");
    }

    private function listUnitKerjaEksternal() {
        return $this->listUnitKerja("EXTERNAL");
    }

    private function listUnitKerja($type)
    {
        $this->checkRequiredInstansi();
        $conditions = [
            'tahun = :year:',
            'id_instansi = :id_instansi:',
            'id_unit_kerja IS NULL'
        ];
        $bind = [
            'year' => $this->tahun,
            'id_instansi' => $this->idInstansi
        ];
        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 getBidangExpert()
    {
        $this->checkRequiredInstansi();

        $conditions = [
            'tahun = :tahun:',
            'id_instansi = :id_instansi:'
        ];
        $bind = [
            'tahun' => $this->tahun,
            'id_instansi' => $this->idInstansi
        ];

        return SpiExpertOpdDetail::query()
            ->columns("ROUND(indeks_eksper, 2) AS nilai, opd as bidang")
            ->where(implode(' AND ', $conditions))
            ->bind($bind)
            ->orderBy("opd")
            ->execute();
    }

    private function checkRequiredInstansi() {
        if (empty($this->idInstansi)) throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "ID Instansi belum diset!");
    }

    private function checkRequiredProvinsi() {
        if (empty($this->idProvinsi)) throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "ID Provinsi belum diset!");
    }

    private function checkRequiredKabKota() {
        if (empty($this->idKabKota)) throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "ID Kabupaten/Kota belum diset!");
    }
}