<?php

class ProfilDaerahController extends BaseController {
    private function baseList($withTrashed)
    {
        $getParams = $this->request->get();

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

        $allowed_order = ['id', 'code_klpd', 'jenis'];
        $allowed_order_direction = ['asc', 'desc'];

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

        $deletedAtCondition = $withTrashed ? '' : 'AND deleted_at is null';

        $condition = [
            'conditions' => "
                (0 > :code_klpd: OR code_klpd = :code_klpd:)
                AND jenis ilike :jenis:
                AND (nama_kepala_daerah ilike :keyword: OR nama_wakil_kepala_daerah ilike :keyword:)
                $deletedAtCondition
            ",
            'bind' => [
                'code_klpd' => $params['code_klpd'],
                'jenis' => '%' . $params['jenis'] . '%',
                'keyword' => '%' . $params['keyword'] . '%'
            ],
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ];

        $records = ProfilDaerah::find($condition);

        $total_record = ProfilDaerah::count($condition);

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

    public function list() {
        return $this->baseList(false);
    }

    public function listAll() {
        $this->checkScopes(['admin.view']);
        return $this->baseList(true);
    }

    public function view($id) {
        $this->checkScopes(['admin.view']);
        $model = ProfilDaerah::findFirst($id);

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

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

        return new SimpleResponse($model);
    }

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

        $this->validate(
            $params,
            [
                'tahun' => ['validators' => ['required', 'digit']],
                'kode_wilayah' => ['validators' => ['required', 'digit']],
                'level' => ['validators' => ['required']]
            ]
        );
        $tahun = $params['tahun'];
        $kodeWilayah = $params['kode_wilayah'];
        $level = $params['level']; // nasional/provinsi/kabupaten
        $isProvinsi = $level == 'provinsi';
        $kodePemda = ( $isProvinsi ? $kodeWilayah . '00' : $kodeWilayah);

        $instansi = Instansi::findFirst(
            [
                ($isProvinsi ? "id_provinsi = :kode: AND id_tipe = 1" : "id_kota_kabupaten = :kode: AND id_tipe = 2"),
                "bind" => [
                    "kode" => $kodeWilayah
                ],
                "columns" => "id, nama"
            ]
        );

        $result = array();
        $result["kepala_daerah"] = $this->getKepalaDaerah($instansi, $tahun, $level);
        $result["audit_bpk"] = $this->getAuditBpk($kodeWilayah, $tahun);
        $result["nilai_lakip"] = $this->getNilaiLakip($kodeWilayah, $tahun);
        $result["kepadatan_penduduk"] = $this->getKepadatanPenduduk($kodeWilayah);
        $result["apbd_bansos"] = $this->getApbdBansos($kodePemda);
        $result["apbd_detail"] = $this->getDetailApbd($kodePemda, $tahun);
        $result["apbd_pembiayaan"] = ($level != "nasional" ? $this->getPembiayaanDariApbd($level, $kodeWilayah, $tahun) : null);
        $result["informasi_apbd"] = ($level != "nasional" ? $this->getInformasiApbd($level, $kodeWilayah, $tahun) : null);
        $result["informasi_detail_apbd"] = ($level != "nasional" ? $this->getInformasiDetailApbd($level, $kodeWilayah, $tahun) : null);
        $result["spi"] = false;
        $result["spi_nasional"] = $this->getSpiNasional($tahun);
        $result["mcp"] = array();
        if ($instansi) {
            $result["spi"] = $this->getSpi($instansi->id, $tahun);
            $result["mcp"] = $this->getMcp($instansi->id, $tahun);
        }

        return new SimpleResponse($result);
    }

    private function getPembiayaanDariApbd($level, $kodeWilayah, $tahun) {
        $result = 0;
        if($level != "nasional") {
            $conditions = "";
            $bind = [];
            if ($level == 'provinsi') {
                $conditions = "kodeprovinsi = :kodeprovinsi:";
                $bind["kodeprovinsi"] = $kodeWilayah;
            } else if ($level == "kabupaten") {
                $conditions = "kodekabkota = :kodekabkota:";
                $bind["kodekabkota"] = $kodeWilayah;
            }
            $pemda = KemenkeuMasterPemda::findFirst([
                'conditions' => $conditions,
                'bind' => $bind
            ]);
            if (!empty($pemda)) {
                $apbd = KemenkeuRealisasiApbd::query()
                    ->columns("SUM(nilaianggaran) AS pembiayaan")
                    ->where('tahun = :tahun: AND kodepemda = :kodepemda:')
                    ->bind([
                        "tahun" => $tahun,
                        "kodepemda" => $pemda->kodepemda
                    ])
                    ->groupBy("kodepemda")
                    ->execute()
                    ->toArray();
                if (count($apbd) > 0) {
                    $result = $apbd[0]["pembiayaan"];
                }
            }
        }

        return $result;
    }

    private function getInformasiApbd($level, $kodeWilayah, $tahun) {
        $result = [];

        if($level != "nasional") {
            $conditions = "";
            $bind = [];
            if ($level == 'provinsi') {
                $conditions = "kodeprovinsi = :kodeprovinsi: AND kodekabkota IS NULL";
                $bind["kodeprovinsi"] = $kodeWilayah;
            } else if ($level == "kabupaten") {
                $conditions = "kodekabkota = :kodekabkota:";
                $bind["kodekabkota"] = $kodeWilayah;
            }
            $pemda = KemenkeuMasterPemda::findFirst([
                'conditions' => $conditions,
                'bind' => $bind
            ]);

            if (!empty($pemda)) {
                //NOTE: Nilai Anggaran & Realisasi sudah dibagi 1M, agar data yg tampil dalam Miliyar
                $apbd = KemenkeuRealisasiApbd::query()
                    ->columns("level1coa90, ROUND(SUM(nilaianggaran)/1000000000.0, 2) AS nilaianggaran, ROUND(SUM(nilairealisasi)/1000000000.0, 2) AS nilairealisasi, MAX(updatedate) AS updatedate")
                    ->where('tahun = :tahun: AND kodepemda = :kodepemda:')
                    ->bind([
                        "tahun" => $tahun,
                        "kodepemda" => $pemda->kodepemda
                    ])
                    ->groupBy("level1coa90")
                    ->execute()
                    ->toArray();
                if(!empty($apbd)) {
                    $result = [
                        "updatedate" => $apbd[0]["updatedate"],
                        "data" => $apbd
                    ];

                    if($level == 'provinsi') {
                        $result["kode_wilayah"] = $pemda->kodepemda;
                        $result["kode_sub_wilayah"] = null;
                    }
                    elseif ($level == 'kabupaten') {

                        $provinsi = KemenkeuMasterPemda::findFirst([
                            'conditions' => "kodeprovinsi = :kodeprovinsi: AND kodekabkota IS NULL",
                            'bind' => [
                                "kodeprovinsi" => $pemda->kodeprovinsi
                            ]
                        ]);

                        $result["kode_wilayah"] = $provinsi->kodepemda;
                        $result["kode_sub_wilayah"] = $pemda->kodepemda;
                    }
                }
            }
        }

        return $result;
    }

    private function getInformasiDetailApbd($level, $kodeWilayah, $tahun) {
        $result = [];

        if($level != "nasional") {
            $conditions = "";
            $bind = [];
            if ($level == 'provinsi') {
                $conditions = "kodeprovinsi = :kodeprovinsi:";
                $bind["kodeprovinsi"] = $kodeWilayah;
            } else if ($level == "kabupaten") {
                $conditions = "kodekabkota = :kodekabkota:";
                $bind["kodekabkota"] = $kodeWilayah;
            }
            $pemda = KemenkeuMasterPemda::findFirst([
                'conditions' => $conditions,
                'bind' => $bind
            ]);

            if (!empty($pemda)) {
                //NOTE: Nilai Anggaran & Realisasi sudah dibagi 1M, agar data yg tampil dalam Miliyar
                $apbd = KemenkeuRealisasiApbd::query()
                    ->columns("level1coa90, level2coa90, ROUND(SUM(nilaianggaran)/1000000000.0, 2) AS nilaianggaran, ROUND(SUM(nilairealisasi)/1000000000.0, 2) AS nilairealisasi, MAX(updatedate) AS updatedate")
                    ->where('tahun = :tahun: AND kodepemda = :kodepemda:')
                    ->bind([
                        "tahun" => $tahun,
                        "kodepemda" => $pemda->kodepemda
                    ])
                    ->groupBy("level1coa90, level2coa90")
                    ->execute()
                    ->toArray();
                $resultData = [];
                if(!empty($apbd)) {
                    $tanggalUpdate = null;
                    foreach ($apbd as $item) {
                        if(!array_key_exists($item['level1coa90'], $resultData)) {
                            $resultData[$item['level1coa90']] = [
                                'name' => $item['level1coa90'],
                                'level2' => []
                            ];
                        }
                        if($tanggalUpdate == null)
                            $tanggalUpdate = $item['updatedate'];
                        $resultData[$item['level1coa90']]['level2'][] = $item;
                    }
                    $result = [
                        "updatedate" => $tanggalUpdate,
                        "data" => $resultData
                    ];
                }
            }
        }

        return $result;
    }

    private function getKepalaDaerah($instansi, $tahun, $level) {
        $kepalaDaerah = [];

        // get kepala daerah dari db elhkpn
        if ($instansi) {
            // todo: uncomment line ini sebelum merge ke Bima
            $service = new ElhkpnMartService($this);
            $kepalaDaerah = $service->retrieveKepalaDaerah($tahun, $instansi->nama);

            // todo: comment line ini sebelum merge ke Bima
//            $kepalaDaerah = $this->rawQuery("
//                SELECT tahun, nama, jabatan, nama_instansi FROM jaga.kepala_daerah WHERE nama_instansi ILIKE '$instansi->nama' AND tahun = $tahun
//            ");
        }

        $result = array();

        foreach ($kepalaDaerah as $kd) {
            if ($level == "nasional") {
                if (str_contains($kd['jabatan'], 'WAKIL PRESIDEN')) {
                    $tmp['rank'] = 2;
                    array_push($result, array_merge($tmp, $kd));
                } elseif (str_contains($kd['jabatan'], 'PRESIDEN')) {
                    $tmp['rank'] = 1;
                    array_push($result, array_merge($tmp, $kd));
                }
            } elseif ($level == 'provinsi') {
                if (str_contains($kd['jabatan'], 'WAKIL GUBERNUR')) {
                    $tmp['rank'] = 2;
                    array_push($result, array_merge($tmp, $kd));
                } elseif (str_contains($kd['jabatan'], 'GUBERNUR')) {
                    $tmp['rank'] = 1;
                    array_push($result, array_merge($tmp, $kd));
                }
            } elseif ($level == 'kabupaten') {
                if (str_contains($kd['jabatan'], 'WAKIL WALIKOTA') || str_contains($kd['jabatan'], 'WAKIL BUPATI')) {
                    $tmp['rank'] = 2;
                    array_push($result, array_merge($tmp, $kd));
                } elseif (str_contains($kd['jabatan'], 'WALIKOTA') || str_contains($kd['jabatan'], 'BUPATI')) {
                    $tmp['rank'] = 1;
                    array_push($result, array_merge($tmp, $kd));
                }
            }
        }

        usort($result, function ($a, $b) {
            return $a['rank'] > $b['rank'];
        });

        return $result;
    }

    private function getAuditBpk($kodeWilayah, $tahun) {
        return ProfilDaerahAuditBpk::findFirst([
            'conditions' => 'kode_wilayah = :kode_wilayah: AND tahun = :tahun:',
            'bind' => [
                'kode_wilayah' => $kodeWilayah,
                'tahun' => $tahun
            ]
        ]);
    }

    private function getNilaiLakip($kodeWilayah, $tahun) {
        return ProfilDaerahNilaiLakip::findFirst([
            'conditions' => 'kode_wilayah = :kode_wilayah: AND tahun = :tahun:',
            'bind' => [
                'kode_wilayah' => $kodeWilayah,
                'tahun' => $tahun
            ]
        ]);
    }

    private function getKepadatanPenduduk($kodeWilayah) {
        return ProfilDaerahKepadatanPenduduk::find([
            'conditions' => 'kode_wilayah = :kode_wilayah:',
            'bind' => [
                'kode_wilayah' => $kodeWilayah
            ]
        ]);
    }

    private function getApbdBansos($kodePemda) {
        return ApbdBansos::findFirst(
            [
                'kode = :kode:',
                'bind' => [
                    'kode' => $kodePemda
                ],
                'columns' => 'pilkada',
                'order' => 'updated_at DESC'
            ]
        );
    }

    private function getDetailApbd($kodePemda, $tahun) {
        return Apbd2019::findFirst([
            'th_anggaran =:tahun: AND kab_key = :kab_key:',
            'bind' => [
                'tahun' => $tahun,
                'kab_key' => $kodePemda
            ]
        ]);
    }

    private function getSpi($kodeKlpd, $tahun) {
        return SPIHasilKLPD::findFirst(
            [
                'code_klpd = :kode: and tahun = :tahun: and is_active = true',
                'bind' => [
                    'kode' => $kodeKlpd,
                    'tahun' => $tahun
                ],
                'columns' => 'hasil_spi',
                'order' => 'id DESC'
            ]
        );
    }

    private function getSpiNasional($tahun) {
        return $this->rawQuery("
            SELECT
                count(s.code_klpd) filter (where i.id_tipe in (1)) as count_provinsi,
                count(s.code_klpd) filter (where i.id_tipe in (2)) as count_kota,
                count(s.code_klpd) filter (where i.id_tipe not in (1,2)) as count_kl,
                sum(s.hasil_spi)/count(s.code_klpd) as rerata
            FROM
                jaga.spi_hasil_klpd s
            LEFT JOIN
                jaga.instansi i ON i.id = s.code_klpd
            WHERE s.tahun = '".$tahun."';
        ")[0];
    }

    /**
     * Copied from KorsupgahTemplateController-baseScores()
     * @param int $instansiId
     * @param int $tahun
     * @return array
     */
    private function getMcp($instansiId, $tahun)
    {
        $findTemplateId = $this->rawQuery("
            SELECT
                survey.id_template,
                template.id_dana_desa
            FROM
                korsupgah_periode periode
            LEFT JOIN
                korsupgah_survey survey ON survey.id_periode = periode.id
            LEFT JOIN
	            korsupgah_template template ON template.id = survey.id_template
            WHERE
                survey.id_instansi = ".$instansiId."
            AND
                date_part('year', periode.started_at) = ".$tahun.";
	    ");

        if (empty($findTemplateId)) {
            return array();
        }
        $templateId = 0;
        if (sizeof($findTemplateId) > 1) {
            foreach ($findTemplateId as $value) {
                if ($value['id_dana_desa'] > 0) {
                    $templateId = $value['id_template'];
                }
            }
            if (!$templateId) {
                $templateId = $findTemplateId[0]['id_template'];
            }
        } else {
            $templateId = $findTemplateId[0]['id_template'];
        }
        $template = KorsupgahTemplate::findFirst($templateId);
        $toCharPrefix = "FM999999990.00";

        if (empty($template)) {
            return array();
        }

        $idTempDanaDesa = ($template->id_dana_desa === null ? 0 : $template->id_dana_desa);

        $areaIntervensiList = $this->rawQuery("
            SELECT distinct(ai.id), ai.nama, ai.presentase_bobot, ai.presentase_bobot_wo_dana_desa
            FROM jaga.korsupgah_template_sub_indikator tsi
                JOIN jaga.korsupgah_sub_indikator si	on tsi.id_sub_indikator = si.id
                JOIN jaga.korsupgah_indikator i on si.id_indikator = i.id
                JOIN jaga.korsupgah_area_intervensi ai on i.id_area_intervensi = ai.id
            WHERE (tsi.id_template = :templateId or tsi.id_template = :templateDanaDesa)
                    AND tsi.deleted_at IS NULL
            ORDER BY ai.id
        ", [
            'templateId' => $templateId,
            'templateDanaDesa' => $idTempDanaDesa
        ]);

        $selectQueries = '';
        $selectQueries1 = '';

        foreach ($areaIntervensiList as $key => $areaIntervensi) {
            $selectQueries .= 'to_char(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000) FILTER (WHERE ai.id = ' . $areaIntervensi['id'] . '), :toCharPrefix) as nilai_' . $areaIntervensi['id'] . ',';

            $selectQueries1 .= 'to_char(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot / 10000) FILTER (WHERE ai.id = ' . $areaIntervensi['id'] . '), \'' . $toCharPrefix . '\') as nilai_' . $areaIntervensi['id'] . ',';
        }

        $qScore = '
            SELECT
                ins.id,
                ins.nama,
                ins.flag_dana_desa,
                mprov.provinsi_name,
                kti.team_id,
                kti.team_name,' .
                        $selectQueries1 . '
                to_char(sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot * ai.presentase_bobot / 1000000), \'' . $toCharPrefix . '\') as nilai_total,
                sum(coalesce(answer.nilai_verifikasi_pic, answer.nilai_verifikasi_qa, answer.nilai_verifikasi,0)::float * si.presentase_bobot * i.presentase_bobot * ai.presentase_bobot / 1000000) as nilai_total_num
            FROM "jaga"."korsupgah_survey" s
                join jaga.korsupgah_template t on t.id = s.id_template
                join jaga.korsupgah_template_sub_indikator tsi on t.id = tsi.id_template and tsi.deleted_at is null
                join jaga.korsupgah_sub_indikator si on tsi.id_sub_indikator = si.id
                join jaga.korsupgah_indikator i on i.id = si.id_indikator
                join jaga.korsupgah_area_intervensi ai on ai.id = i.id_area_intervensi
                join jaga.korsupgah_indikator_tipe it on i.id_tipe = it.id
                join jaga.instansi ins on ins.id = s.id_instansi
                JOIN jaga.master_provinsi mprov ON ins.id_provinsi = mprov.provinsi_code::INTEGER
                join jaga.korsupgah_periode p on p.id = s.id_periode
                left outer join (
                    select distinct on (1, 2) a.id_question as id_sub_indikator, a.id_survey, a.id as answer_id, a.nilai_verifikasi, a.nilai_verifikasi_qa, a.nilai_verifikasi_pic
                    from jaga.korsupgah_answer a
                    where a.status = 1
                    ORDER BY a.id_question, a.id_survey, a.created_at DESC
                ) answer on answer.id_sub_indikator = tsi.id_sub_indikator AND s.id = answer.id_survey
                left outer join (
                    select ti.*, t.id_periode, t.name as team_name
                    from jaga.korwil_team_instansi ti
                    join jaga.korwil_teams t on t.id = ti.team_id
                    where t.deleted_at is null
                ) kti on kti.instansi_id = ins.id and kti.id_periode = s.id_periode
            WHERE (s.id_template = ' . $templateId . ' OR s.id_template = ' . $idTempDanaDesa . ') AND ins.id = ' . $instansiId . '
            GROUP BY ins."id", ins.nama, kti.team_id, kti.team_name, mprov.provinsi_name
            ORDER BY nilai_total_num DESC
        ';
        $result = $this->rawQuery($qScore);

        for ($i = 0; $i < count($result); $i++) {
            $nilai_dd = 0;
            foreach ($areaIntervensiList as $key => $ai) {
                $iNilai = 'nilai_' . $ai['id'];
                // without dana desa, add dana desa (8) bobot to Optimalisasi Pendapatan Daerah (6)
                if ($result[$i]['flag_dana_desa'] == 0 && $ai['id'] == 6) {
                    $iBobot = $areaIntervensiList[7]['presentase_bobot'] + $ai['presentase_bobot'];
                } elseif ($result[$i]['flag_dana_desa'] == 0) {
                    $iBobot = $ai['presentase_bobot_wo_dana_desa'];
                } else {
                    $iBobot = $ai['presentase_bobot'];
                }

                $nilai_dd += $iBobot *  $result[$i][$iNilai] / 100;
            }
            $result[$i]['total_ddd'] = number_format((float)$nilai_dd, 2, '.', '');
        }

        return $result;
    }

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

        $request = $this->request->getPost();
        $validateRequest = $this->validateCreateAndUpdateRequest($request);

        $date = date('Y-m-d H:i:s');
        $model = new ProfilDaerah();
        $model->assign($validateRequest);
        $model->created_at = $date;
        $model->updated_at = $date;

        $status = $model->save();

        if (!$status) {
            $this->throwValidationException($model->getMessages());
        } else {
            return new SimpleResponse($model);
        }
    }

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

        $model = ProfilDaerah::findFirst($id);

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

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

        $request = $this->request->getPost();
        $validateRequest = $this->validateCreateAndUpdateRequest($request);

        $date = date('Y-m-d H:i:s');
        $model->assign($validateRequest);
        $model->updated_at = $date;
        $status = $model->save();

        if (!$status) {
            $this->throwValidationException($model->getMessages());
        } else {
            return new SimpleResponse($model);
        }
    }

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

        $model = ProfilDaerah::findFirst($id);

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

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

        $date = date('Y-m-d H:i:s');
        $model->updated_at = $date;
        $model->deleted_at = $date;
        $status = $model->save();

        if (!$status) {
            $this->throwValidationException($model->getMessages());
        } else {
            return new SimpleResponse($model);
        }
    }

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

        $model = ProfilDaerah::findFirst($id);

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

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

        $date = date('Y-m-d H:i:s');
        $model->updated_at = $date;
        $model->deleted_at = null;
        $status = $model->save();

        if (!$status) {
            $this->throwValidationException($model->getMessages());
        } else {
            return new SimpleResponse($model);
        }
    }

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

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

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

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

            $expectedHeaders = [
                'Kode KLPD',
                'Nama',
                'Jenis',
                'Nama Kepala Daerah',
                'Jabatan Kepala Daerah',
                'Nama Wakil Kepala Daerah',
                'Jabatan Wakil Kepala Daerah',
                'Hasil Audit BPK',
                'Description Hasil Audit BPK',
                'Nilai Lakip',
                'Description Lakip',
                'Nilai PTK',
                'Description PTK'
            ];

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

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

            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 = [
                'code_klpd' => ['validators' => ['required', 'digit']],
                'nama_wilayah' => ['validators' => ['required']],
                'jenis' => ['validators' => ['required']],
                'nama_kepala_daerah' => ['validators' => []],
                'jabatan_kepala_daerah' => ['validators' => []],
                'nama_wakil_kepala_daerah' => ['validators' => []],
                'jabatan_wakil_kepala_daerah' => ['validators' => []],
                'audit_bpk' => ['validators' => []],
                'keterangan_bpk' => ['validators' => []],
                'nilai_lakip' => ['validators' => ['numeric']],
                'keterangan_lakip' => ['validators' => []],
                'nilai_ptk' => ['validators' => ['numeric']],
                'keterangan_ptk' => ['validators' => []]
            ];

            $duplicateList = [];
            $payloads = [];

            foreach ($rows as $key => $row) {
                $rowPayload = [
                    'code_klpd' => trim($row[0]),
                    'nama_wilayah' => trim($row[1]),
                    'jenis' => strtolower(trim($row[2])),
                    'nama_kepala_daerah' => trim($row[3]),
                    'jabatan_kepala_daerah' => trim($row[4]),
                    'nama_wakil_kepala_daerah' => trim($row[5]),
                    'jabatan_wakil_kepala_daerah' => trim($row[6]),
                    'audit_bpk' => trim($row[7]),
                    'keterangan_bpk' => trim($row[8]),
                    'nilai_lakip' => trim($row[9]),
                    'keterangan_lakip' => trim($row[10]),
                    'nilai_ptk' => trim($row[11]),
                    'keterangan_ptk' => trim($row[12])
                ];

                $duplicateList[] = $rowPayload['code_klpd'];

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

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

            $duplicateCounts = array_count_values($duplicateList);
            $dataDuplicates = array_filter($duplicateCounts, function ($value) {
                return $value > 1;
            });

            if (count($dataDuplicates) > 0) {
                $errors[] = [
                    'row_id' => -1,
                    'errors' => [
                        [
                            'field' => 'Kode KLPD',
                            'message' => 'Duplicate pada excel: ' . implode(',', array_keys($dataDuplicates))
                        ]
                    ]
                ];
            }

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

            $fails = [];
            $successCount = 0;
            $this->db->begin();
            try {
                foreach ($payloads as $key => $row) {
                    $model = new ProfilDaerah();
                    $model->assign($row);
                    $result = $model->save();

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

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

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

                return new SimpleResponse([
                    'success_count' => $successCount,
                    '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 statRegulasiPAK() {
        $params = $this->request->get();
        $kodeWilayah = isset($params['kode_wilayah']) && !empty($params['kode_wilayah']) ? $params['kode_wilayah'] : null;
        $countRegulasi = $this->countRegulasi($kodeWilayah);
        $countPemda = $this->countPemda($kodeWilayah);
        $allPemda = $this->countAllPemda($kodeWilayah);
        $result = array_merge($countRegulasi, $countPemda, $allPemda);

        return new SimpleResponse($result);
    }

    private function getInstansiConditions($kodeWilayah) {
        $conditions = "";
        if ($kodeWilayah) {
            $isProvinsi = strlen($kodeWilayah) == 2;
            if ($isProvinsi) {
                $conditions .= " AND (ins.id_provinsi = " . $kodeWilayah . " OR ins.id_tipe = 3)";
            } else {
                $conditions .= " AND ( (ins.id_kota_kabupaten = " . $kodeWilayah . " AND ins.id_tipe = 2)" . " OR ins.id_tipe = 3 )";
            }
        }

        return $conditions;
    }

    private function countRegulasi($kodeWilayah) {
        $conditions = $this->getInstansiConditions($kodeWilayah);
        return $this->rawQuery("
            SELECT 
                COUNT(*) FILTER (WHERE ins.id_tipe = 3) AS total_kementerian,
                COUNT(*) FILTER (WHERE ins.id_tipe = 1) AS total_provinsi,
                COUNT(*) FILTER (WHERE ins.id_tipe = 2) AS total_kab_kota,
                COUNT(*) FILTER (WHERE ins.id_sub_tipe = 1) AS total_kab,
                COUNT(*) FILTER (WHERE ins.id_sub_tipe = 2) AS total_kota
            FROM
                pak_verifikasi_implementasi pvi
                LEFT JOIN pak_questionnaire pq ON pvi.questionnaire_id = pq.id
                LEFT JOIN pak_implementasi pi ON pvi.implementasi_id = pi.id
                LEFT JOIN instansi ins ON pi.instansi_id = ins.id
                LEFT JOIN instansi_sub_tipe ist ON ist.id = ins.id_sub_tipe
                LEFT JOIN pak_verifikasi_implementasi_attachment pvia ON pvia.verifikasi_id = pvi.id
            WHERE
                pvi.nilai_verifikasi = 100
                AND pq.questionnaire_category_id = 1
                $conditions
            ;
        ")[0];
    }

    private function countPemda($kodeWilayah) {
        if ($kodeWilayah) {
            $countPemda = $this->countPemdaWithWilayah($kodeWilayah);
        } else {
            $countPemda = $this->countPemdaWithoutWilayah();
        }

        return $countPemda;
    }

    private function countAllPemda($kodeWilayah) {
        if ($kodeWilayah) {
            $allPemda = $this->countAllPemdaWithWilayah($kodeWilayah);
        } else {
            $allPemda = $this->countAllPemdaWithoutWilayah();
        }

        return $allPemda;
    }

    private function countPemdaWithWilayah($kodeWilayah) {
        if (substr($kodeWilayah, 0, 2) == '31') {
            $countPemda = $this->rawQuery("
                SELECT 
                    0 AS count_kementerian,
                    COUNT(DISTINCT(ins.id_provinsi)) as count_provinsi,
                    0 AS count_kab_kota
                FROM
                    pak_verifikasi_implementasi pvi
                    LEFT JOIN pak_questionnaire pq ON pvi.questionnaire_id = pq.id
                    LEFT JOIN pak_implementasi pi ON pvi.implementasi_id = pi.id
                    LEFT JOIN instansi ins ON pi.instansi_id = ins.id
                WHERE
                    pvi.nilai_verifikasi = 100
                    AND pq.questionnaire_category_id = 1
                    AND ins.id_provinsi = 31
                ;
            ")[0];
        } else {
            $conditions = $this->getInstansiConditions($kodeWilayah);
            $countPemda = $this->rawQuery("
                SELECT 
                    COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_tipe = 3) AS count_kementerian,
                    COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_tipe = 1) AS count_provinsi,
                    COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_tipe = 2) AS count_kab_kota,   
                    COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_sub_tipe = 1) AS count_kab,
                    COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_sub_tipe = 2) AS count_kota
                FROM
                    pak_verifikasi_implementasi pvi
                    LEFT JOIN pak_questionnaire pq ON pvi.questionnaire_id = pq.id
                    LEFT JOIN pak_implementasi pi ON pvi.implementasi_id = pi.id
                    LEFT JOIN instansi ins ON pi.instansi_id = ins.id
                    LEFT JOIN instansi_sub_tipe ist ON ist.id = ins.id_sub_tipe
                WHERE
                    pvi.nilai_verifikasi = 100
                    AND pq.questionnaire_category_id = 1
                    $conditions
                ;
            ")[0];
        }

        return $countPemda;
    }

    private function countPemdaWithoutWilayah() {
        $countPemda = $this->rawQuery("
                SELECT 
                    COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_tipe = 3) AS count_kementerian,
                    COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_tipe = 1) AS count_provinsi,
                    COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_tipe = 2) AS count_kab_kota,   
                    COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_sub_tipe = 1) AS count_kab,
                    COUNT(DISTINCT ins.id) FILTER (WHERE ins.id_sub_tipe = 2) AS count_kota
                FROM
                    pak_verifikasi_implementasi pvi
                    LEFT JOIN pak_questionnaire pq ON pvi.questionnaire_id = pq.id
                    LEFT JOIN pak_implementasi pi ON pvi.implementasi_id = pi.id
                    LEFT JOIN instansi ins ON pi.instansi_id = ins.id
                    LEFT JOIN instansi_sub_tipe ist ON ist.id = ins.id_sub_tipe
                WHERE
                    pvi.nilai_verifikasi = 100
                    AND pq.questionnaire_category_id = 1
                    AND ins.id_provinsi <> 31
                ;
            ")[0];

        $countPemdaJakarta = $this->rawQuery("
                SELECT 
                    COUNT(DISTINCT(ins.id_provinsi)) as total
                FROM
                    pak_verifikasi_implementasi pvi
                    LEFT JOIN pak_questionnaire pq ON pvi.questionnaire_id = pq.id
                    LEFT JOIN pak_implementasi pi ON pvi.implementasi_id = pi.id
                    LEFT JOIN instansi ins ON pi.instansi_id = ins.id
                WHERE
                    pvi.nilai_verifikasi = 100
                    AND pq.questionnaire_category_id = 1
                    AND ins.id_provinsi = 31
                ;
            ")[0];

        $countPemda['count_provinsi'] += $countPemdaJakarta['total'];

        return $countPemda;
    }

    private function countAllPemdaWithWilayah($kodeWilayah) {
        $conditions = $this->getInstansiConditions($kodeWilayah);
        if ($kodeWilayah == '31') {
            $allPemda = $this->rawQuery("
                SELECT 
                    COUNT(*) FILTER (WHERE id_tipe = 3) AS all_kementerian,
                    1 AS all_provinsi,
                    0 AS all_kab_kota
                FROM jaga.instansi ins WHERE deleted_at IS NULL $conditions
                ;
            ")[0];
        } else {
            $allPemda = $this->rawQuery("
                SELECT 
                    COUNT(*) FILTER (WHERE ins.id_tipe = 3) AS all_kementerian,
                    COUNT(*) FILTER (WHERE ins.id_tipe = 1) AS all_provinsi,
                    COUNT(*) FILTER (WHERE ins.id_tipe = 2) AS all_kab_kota,
                    COUNT(*) FILTER (WHERE ins.id_sub_tipe = 1) AS all_kab,
                    COUNT(*) FILTER (WHERE ins.id_sub_tipe = 2) AS all_kota
                FROM jaga.instansi ins LEFT JOIN instansi_sub_tipe ist ON ist.id = ins.id_sub_tipe WHERE ins.deleted_at IS NULL $conditions
                ;
            ")[0];
        }

        return $allPemda;
    }

    private function countAllPemdaWithoutWilayah() {
        return $this->rawQuery("
            SELECT 
                COUNT(*) FILTER (WHERE ins.id_tipe = 3) AS all_kementerian,
                COUNT(*) FILTER (WHERE ins.id_tipe = 1) AS all_provinsi,
                COUNT(*) FILTER (WHERE ins.id_tipe = 2) AS all_kab_kota,
                COUNT(*) FILTER (WHERE ins.id_sub_tipe = 1) AS all_kab,
                COUNT(*) FILTER (WHERE ins.id_sub_tipe = 2) AS all_kota
            FROM jaga.instansi ins LEFT JOIN instansi_sub_tipe ist ON ist.id = ins.id_sub_tipe WHERE ins.deleted_at IS NULL AND (id_provinsi <> 31 OR (id_provinsi = 31 AND id_kota_kabupaten IS NULL) OR id_provinsi IS NULL)
            ;
        ")[0];
    }

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

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

        $allowed_order = ['implementasi_id', 'jenis_dokumen', 'nama_instansi', 'nomor', 'tahun', 'tentang', 'nama_provinsi', 'nama_kab_kota'];
        $allowed_order_direction = ['asc', 'desc'];
        $order_by_mapping = [
            'implementasi_id' => 'pi.id',
            'jenis_dokumen' => 'pjdq.jenis_dokumen',
            'nama_instansi' => 'ins.nama',
            'nomor' => 'pvia.nomor',
            'tahun' => 'pvia.tahun',
            'tentang' => 'pvia.tentang',
            'nama_provinsi' => 'mp.provinsi_name',
            'nama_kab_kota' => 'mkk.kota_kabupaten_name'
        ];

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

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

        $conditions = "pi.deleted_at IS NULL AND pvi.deleted_at IS NULL AND pvi.nilai_verifikasi = 100 AND pq.questionnaire_category_id = 1 AND ins.flag_pak = true";

        if (!empty($params['tipe_instansi'])) {
            $conditions .= " AND ins.id_tipe = " . (int)$params['tipe_instansi'];
        } else {
            $conditions .= " AND ins.id_tipe IN (1,2,3)";
        }

        if (!empty($params['keyword'])) {
            $conditions .= " AND (";
            $conditions .= " pvia.tentang ILIKE '%" . $params['keyword'] . "%'";
            $conditions .= " OR pvia.nomor ILIKE '" . $params['keyword'] . "'";
            $conditions .= " OR ins.nama ILIKE '%" . $params['keyword'] . "%'";
            $conditions .= ")";
        }

        if (!empty($params['kode_wilayah']) && $params['tipe_instansi'] != 3) {
            $isProvinsi = strlen($params['kode_wilayah']) == 2;
            if ($isProvinsi) {
                $conditions .= " AND ins.id_provinsi = " . $params['kode_wilayah'];
            } else {
                $conditions .= " AND ins.id_kota_kabupaten = " . $params['kode_wilayah'] . "" . ($isProvinsi ? "" : " AND ins.id_tipe = 2");
            }
        }

        $query = "
            SELECT
                pvia.*, a.uuid as attachment_uuid, pi.id as implementasi_id, pjdq.jenis_dokumen, ins.id as id_instansi, ins.nama as nama_instansi, ins.id_tipe as tipe_instansi, ins.id_provinsi, ins.id_kota_kabupaten, mp.provinsi_name as nama_provinsi, mkk.kota_kabupaten_name as nama_kab_kota
            FROM
                pak_verifikasi_implementasi pvi
                LEFT JOIN pak_questionnaire pq ON pvi.questionnaire_id = pq.id
                LEFT JOIN pak_implementasi pi ON pvi.implementasi_id = pi.id
                LEFT JOIN instansi ins ON pi.instansi_id = ins.id
                LEFT JOIN master_provinsi mp ON mp.provinsi_code::INTEGER = ins.id_provinsi
                LEFT JOIN master_kota_kabupaten mkk ON mkk.kota_kabupaten_code::INTEGER = ins.id_kota_kabupaten
                LEFT JOIN pak_verifikasi_implementasi_attachment pvia ON pvia.verifikasi_id = pvi.id
                LEFT JOIN attachments a ON a.id = pvia.attachment_id
                LEFT JOIN pak_jenis_dokumen_questionnaire pjdq ON pjdq.id = pvia.jenis_dokumen_id
            WHERE
               ".$conditions."
            ORDER BY ".$order_by_column." ".$order_direction." NULLS LAST
        ";

        $total = $this->rawQuery($query);
        $rows = $this->rawQuery($query . " LIMIT " . $params['limit'] . " OFFSET " . $params['offset']);

        return new PaginatedResponse($rows, sizeof($total), $params['limit'], $params['offset']);
    }

    public function downloadRegulasiPAK($uuid)
    {
        $service = new AttachmentService($this->shared, $this->minio);

        return $service->download(
            'pak-verifikasi-implementasi-attachment' . DIRECTORY_SEPARATOR,
            $uuid, true
        );
    }

    protected function validateCreateAndUpdateRequest($request)
    {
        return $validateRequest = $this->validate(
            $request,
            [
                'nama_wilayah' => ['validators' => ['required']],
                'code_klpd' => ['validators' => ['required', 'digit']],
                'jenis' => ['validators' => ['required']],
                'nama_kepala_daerah' => ['validators' => []],
                'jabatan_kepala_daerah' => ['validators' => []],
                'nama_wakil_kepala_daerah' => ['validators' => []],
                'jabatan_wakil_kepala_daerah' => ['validators' => []],
                'audit_bpk' => ['validators' => []],
                'keterangan_bpk' => ['validators' => []],
                'nilai_lakip' => ['validators' => ['numeric']],
                'keterangan_lakip' => ['validators' => []],
                'nilai_ptk' => ['validators' => ['numeric']],
                'keterangan_ptk' => ['validators' => []]
            ]
        );
    }
}