<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class KorwilAsetTargetController extends BaseController
{
    /** @var UmUser $currentUser */
    protected $currentUser;

    /** @var KorwilAsetTarget $kat */
    protected $kat;

    public function setCurrentUser($user) {
        $this->currentUser = $user;
    }

    public function setKat($kat) {
        $this->kat = $kat;
    }

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

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

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

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

        if (empty($params['id_instansi'])) {
            $rows = KorwilAsetTarget::find([
                'conditions' => 'tahun ilike :keyword:',
                'bind' => [
                    'keyword' => '%' . $params['keyword'] . '%'
                ],
                'order' => 'id',
                'limit' => $params['limit'],
                'offset' => $params['offset']
            ]);

            $total_record = KorwilAsetTarget::count([
                'conditions' => 'tahun ilike :keyword:',
                'bind' => [
                    'keyword' => '%' . $params['keyword'] . '%'
                ],
            ]);
        } else {
            $rows = KorwilAsetTarget::find([
                'conditions' => 'tahun ilike :keyword: AND id_instansi = :id_instansi:',
                'bind' => [
                    'keyword' => '%' . $params['keyword'] . '%',
                    'id_instansi' => $params['id_instansi']
                ],
                'limit' => $params['limit'],
                'offset' => $params['offset']
            ]);

            $total_record = KorwilAsetTarget::count([
                'conditions' => 'tahun ilike :keyword: AND id_instansi = :id_instansi:',
                'bind' => [
                    'keyword' => '%' . $params['keyword'] . '%',
                    'id_instansi' => $params['id_instansi']
                ],
            ]);
        }

        $kat = [];
        foreach ($rows as $key => $survey) :
            $kat[] = $survey->toDetail();
        endforeach;

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

    public function listWithDetail()
    {
        $this->checkOneOfScopes(['korsupgah.monitoring', 'korwil.monitoring_kemdagri', 'korwil.monitoring_bpkp', 'korwil.verifikator_kemdagri_bpkp', 'korwil.observer_kemdagri_bpkp']);

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

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

        $params =  $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'id_instansi' => ['validators' => ['digit']]
            ]
        );

        $instansi_operator = $params < 0 ? '<>' : '=';

        $kat = KorwilAsetTarget::find([
            'conditions' => "id_instansi $instansi_operator :id_instansi:",
            'bind' => [
                'id_instansi' => $params['id_instansi']
            ],
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ]);

        $total_record = KorwilAsetTarget::count([
            'conditions' => "id_instansi $instansi_operator :id_instansi:",
            'bind' => [
                'id_instansi' => $params['id_instansi']
            ]
        ]);

        $result = [];

        foreach ($kat as $key => $target) {
            $result[] = $target->toDetail();
        }

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

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

        $kat = KorwilAsetTarget::findFirst($id);

        if (empty($kat)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Target Aset tidak ditemukan');
        }

        $result = $kat->toDetail();

        return new CollectionPaginatedResponse([$result]);
    }

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

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

        $params = $this->validate(
            $rawBody,
            [
                'id_instansi' => ['validators' => ['required', 'digit']],
                'tahun' => ['validators' => []],
                'target' => ['validators' => []],
                'anggaran' => ['validators' => []],
                'jumlah_bidang' => ['validators' => ['digit']],
                'keterangan' => ['validators' => []]
            ]
        );

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

        if (!empty($instansi->deleted_at)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi sudah terhapus');
        endif;

        $cekAsetTarget = KorwilAsetTarget::findFirst([
            'conditions' => 'id_instansi = :id: AND tahun = :tahun:',
            'bind' => [
                'id' => $params['id_instansi'],
                'tahun' => $params['tahun']
            ]
        ]);

        if (!empty($cekAsetTarget)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi sudah memiliki Target Aset di tahun yang sama');
        }

        $kat = new KorwilAsetTarget();
        $kat->id_instansi = $params['id_instansi'];
        $kat->tahun = $params['tahun'];
        $kat->target = $params['target'];
        $kat->anggaran = $params['anggaran'];
        $kat->jumlah_bidang = $params['jumlah_bidang'];
        $kat->keterangan = $params['keterangan'];
        $kat->created_at = date('Y-m-d H:i:s');
        $kat->created_by = $this->shared->user->um_id;
        $status = $kat->save();

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

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

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

    public function createMine()
    {
        $this->checkOneOfScopes(['korsupgah.answer', 'aset.answer']);

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

        $params = $this->validate(
            $rawBody,
            [
                'tahun' => ['validators' => ['required', 'digit']],
                'target' => ['validators' => ['required']],
                'anggaran' => ['validators' => []],
                'jumlah_bidang' => ['validators' => ['digit']],
                'keterangan' => ['validators' => []]
            ]
        );

        $this->setAndValidateUser();

        $cekAsetTarget = KorwilAsetTarget::findFirst([
            'conditions' => 'id_instansi = :id: AND tahun = :tahun:',
            'bind' => [
                'id' => $this->currentUser->id_instansi,
                'tahun' => $params['tahun']
            ]
        ]);

        if (!empty($cekAsetTarget)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi sudah memiliki Target Aset di tahun yang sama');
        }

        $kat = new KorwilAsetTarget();
        $kat->id_instansi = $this->currentUser->id_instansi;
        $kat->tahun = $params['tahun'];
        $kat->target = $params['target'];
        $kat->anggaran = $params['anggaran'];
        $kat->jumlah_bidang = $params['jumlah_bidang'];
        $kat->keterangan = $params['keterangan'];
        $kat->created_at = date('Y-m-d H:i:s');
        $kat->created_by = $this->currentUser->um_id;
        $status = $kat->save();

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

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

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

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

        $kat = KorwilAsetTarget::findFirst($id);

        if (empty($kat)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Target Aset Tidak Ditemukan');
        }

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

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

        $params = $this->validate(
            $rawBody,
            [
                'id_instansi' => ['validators' => ['required', 'digit']],
                'tahun' => ['validators' => []],
                'target' => ['validators' => []],
                'anggaran' => ['validators' => []],
                'jumlah_bidang' => ['validators' => ['digit']],
                'keterangan' => ['validators' => []]
            ]
        );

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

        if (!empty($instansi->deleted_at)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi sudah terhapus');
        endif;

        $cekAsetTarget = KorwilAsetTarget::findFirst([
            'conditions' => 'id_instansi = :id: AND tahun = :tahun:',
            'bind' => [
                'id' => $params['id_instansi'],
                'tahun' => $params['tahun']
            ]
        ]);

        if (!empty($cekAsetTarget) && $kat->id != $cekAsetTarget->id) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi sudah memiliki Target Aset di tahun yang sama');
        }

        $date = date('Y-m-d H:i:s');
        $kat->id_instansi = $params['id_instansi'];
        $kat->tahun = $params['tahun'];
        $kat->target = $params['target'];
        $kat->anggaran = $params['anggaran'];
        $kat->jumlah_bidang = $params['jumlah_bidang'];
        $kat->keterangan = $params['keterangan'];
        $kat->updated_at = $date;
        $kat->updated_by = $this->shared->user->um_id;
        $status = $kat->save();

        if (!$status) {
            $errors = $kat->getMessage();
            $error_messages = [];

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

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

    public function updateMine($id)
    {
        $this->checkOneOfScopes(['korsupgah.answer', 'aset.answer']);

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

        $params = $this->validate(
            $rawBody,
            [
                'tahun' => ['validators' => ['required', 'digit']],
                'target' => ['validators' => ['required']],
                'anggaran' => ['validators' => []],
                'jumlah_bidang' => ['validators' => ['digit']],
                'keterangan' => ['validators' => []]
            ]
        );

        $kat = KorwilAsetTarget::findFirst($id);

        if (empty($kat)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Target Aset Tidak Ditemukan');
        }

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

        $this->setAndValidateUser();
        $this->setAndValidateUserKat($kat);

        $cekAsetTarget = KorwilAsetTarget::findFirst([
            'conditions' => 'id_instansi = :id: AND tahun = :tahun:',
            'bind' => [
                'id' => $this->currentUser->id_instansi,
                'tahun' => $params['tahun']
            ]
        ]);

        if (!empty($cekAsetTarget) && $kat->id != $cekAsetTarget->id) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi sudah memiliki Target Aset di tahun yang sama');
        }

        $date = date('Y-m-d H:i:s');
        $kat->id_instansi = $this->currentUser->id_instansi;
        $kat->tahun = $params['tahun'];
        $kat->target = $params['target'];
        $kat->anggaran = $params['anggaran'];
        $kat->jumlah_bidang = $params['jumlah_bidang'];
        $kat->keterangan = $params['keterangan'];
        $kat->updated_at = $date;
        $kat->updated_by = $this->currentUser->um_id;
        $status = $kat->save();

        if (!$status) {
            $errors = $kat->getMessage();
            $error_messages = [];

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

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

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

        $kat = KorwilAsetTarget::findFirst($id);

        if (empty($kat)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Target Aset Tidak Ditemukan');
        }

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

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

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

            foreach ($errors as $key => $error) {
                $error_messages[] = ["field" => $error->getField(), "message" => $error->getMessages()];
            }

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

    public function deleteMine($id)
    {
        $this->checkOneOfScopes(['korsupgah.answer', 'aset.answer']);

        $kat = KorwilAsetTarget::findFirst($id);

        if (empty($kat)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Target Aset Tidak Ditemukan');
        }

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

        $this->setAndValidateUser();
        $this->setAndValidateUserKat($kat);

        $now = date('Y-m-d H:i:s');
        $kat->updated_at = $now;
        $kat->deleted_at = $now;
        $kat->updated_by = $this->currentUser->um_id;
        $status = $kat->save();

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

            foreach ($errors as $key => $error) {
                $error_messages[] = ["field" => $error->getField(), "message" => $error->getMessages()];
            }

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

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

        $kat = KorwilAsetTarget::findFirst($id);

        if (empty($kat)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Target Aset Tidak Ditemukan');
        }

        if (empty($kat->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Target Aset sudah aktif');
        }


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

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

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

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

    public function restoreMine($id)
    {
        $this->checkOneOfScopes(['korsupgah.answer', 'aset.answer']);

        $kat = KorwilAsetTarget::findFirst($id);

        if (empty($kat)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Target Aset Tidak Ditemukan');
        }

        if (empty($kat->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Target Aset sudah aktif');
        }

        $this->setAndValidateUser();
        $this->setAndValidateUserKat($kat);

        $now = date('Y-m-d H:i:s');
        $kat->updated_at = $now;
        $kat->deleted_at = null;
        $kat->updated_by = $this->currentUser->um_id;
        $status = $kat->save();

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

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

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

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

        // $result = KorwilAsetTarget::find();
        $query = "SELECT * FROM korwil_aset_target";
        $result = $this->rawQuery($query, []);

        $header = [
            "id_instansi",
            "tahun",
            "target",
            "anggaran",
            "jumlah_bidang",
            "keterangan"
        ];

        $rows = [];
        $rows[] = $header;
        foreach ($result as $index => $data) {
            $rows[] = [
                $data['id_instansi'],
                $data['tahun'],
                $data['target'],
                $data['anggaran'],
                $data['jumlah_bidang'],
                $data['keterangan']
            ];
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->fromArray($rows, NULL, 'A1');

        $writer = new Xlsx($spreadsheet);
        $filename = "sample_aset_target_" . date('Ymd') . ".xls";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        $writer->save('php://output');
    }

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

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

        $failed = [];
        $updated = [];
        $added = [];

        if (!empty($document)) {
            $filename = $document['tmp_name'];
            $rows = [];
            if ($_FILES['document']['type'] == 'text/csv') {
                $csv = new ParseCsv\Csv();
                $csv->parse($filename);
                $rows = $csv->data;
            } else {
                try{
                    $spreadsheet = PhpOffice\PhpSpreadsheet\IOFactory::load($filename);
                    $worksheet = $spreadsheet->getActiveSheet()->toArray();
                    for ($i = 1; $i < sizeof($worksheet); $i++) {
                        $tmp = [];
                        foreach ($worksheet[0] as $key => $value) {
                            $tmp[$value] = $worksheet[$i][$key];
                        }
                        array_push($rows, $tmp);
                    }
                } catch (\Exception $e) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Format file excel tidak valid! Detail: " . $e->getMessage());
                }
            }

            foreach ($rows as $key => $row) {
                if (!empty($row['id_instansi']) && !empty($row['tahun']) && !empty($row['target']) && !empty($row['anggaran']) && !empty($row['jumlah_bidang'])) {
                    if (empty($row['id_instansi']) || empty($row['tahun']) || empty($row['target']) || empty($row['anggaran']) || empty($row['jumlah_bidang'])) {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Id Instansi/tahun/target/anggaran/jumlah bidang/keterangan tidak ditemukan pada baris ' . ($key + 2));
                    }

                    $instansi = Instansi::findFirst($row['id_instansi']);

                    if (empty($instansi)) {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi tidak ditemukan: ' . $row['id_instansi']);
                    }

                    $cekAsetTarget = KorwilAsetTarget::findFirst([
                        'conditions' => 'id_instansi=:id_instansi: AND tahun=:tahun: AND deleted_at is NULL',
                        'bind' => [
                            'id_instansi' => $row['id_instansi'],
                            'tahun' => $row['tahun']
                        ]
                    ]);

                    if (!empty($cekAsetTarget)) {
                        $cekAsetTarget->id_instansi = $row['id_instansi'];
                        $cekAsetTarget->tahun = $row['tahun'];
                        $cekAsetTarget->target = $row['target'];
                        $cekAsetTarget->anggaran = $row['anggaran'];
                        $cekAsetTarget->jumlah_bidang = $row['jumlah_bidang'];
                        $cekAsetTarget->keterangan = $row['keterangan'];
                        $status = $cekAsetTarget->save();

                        if ($status) {
                            $added[] = ['id_instansi' => $row['id_instansi'], 'tahun' => $row['tahun'], 'target' => $row['target'], 'anggaran' => $row['anggaran'], 'jumlah_bidang' => $row['jumlah_bidang'], 'keterangan' => $row['keterangan']];
                        } else {
                            $failed[] = ['id_instansi' => $row['id_instansi'], 'tahun' => $row['tahun'], 'target' => $row['target'], 'anggaran' => $row['anggaran'], 'jumlah_bidang' => $row['jumlah_bidang'], 'keterangan' => $row['keterangan']];
                        }
                    } else {
                        $setNewAsetTarget = new KorwilAsetTarget();
                        $setNewAsetTarget->id_instansi = $row['id_instansi'];
                        $setNewAsetTarget->tahun = $row['tahun'];
                        $setNewAsetTarget->target = $row['target'];
                        $setNewAsetTarget->anggaran = $row['anggaran'];
                        $setNewAsetTarget->jumlah_bidang = $row['jumlah_bidang'];
                        $setNewAsetTarget->keterangan = $row['keterangan'];
                        $status = $setNewAsetTarget->save();

                        if ($status) {
                            $updated[] = ['id_instansi' => $row['id_instansi'], 'tahun' => $row['tahun'], 'target' => $row['target'], 'anggaran' => $row['anggaran'], 'jumlah_bidang' => $row['jumlah_bidang'], 'keterangan' => $row['keterangan']];
                        } else {
                            $failed[] = ['id_instansi' => $row['id_instansi'], 'tahun' => $row['tahun'], 'target' => $row['target'], 'anggaran' => $row['anggaran'], 'jumlah_bidang' => $row['jumlah_bidang'], 'keterangan' => $row['keterangan']];
                        }
                    }
                }
            }
        }

        return new SimpleResponse(compact('added', 'failed', 'skipped'));
    }

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

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

        $qInstansi = "";
        if($idInstansi) {
            Instansi::findOrFail($idInstansi);
            $qInstansi .= " AND id = " . $idInstansi;
        }

        $result = [];
        $data = Instansi::find([
            'columns' => 'id, nama, id_provinsi',
            'conditions' => 'id_tipe = 1 AND deleted_at is NULL' . $qInstansi,
            'order' => 'id_provinsi'
        ]);
        foreach ($data as $provinsi) {
            array_push($result, ['id' => $provinsi->id, 'nama' => $provinsi->nama, 'is_provinsi' => true]);
            $params = [
                'columns' => 'id, nama',
                'conditions' => 'id_tipe = 2 AND id_provinsi = :id_provinsi: AND deleted_at is NULL',
                'order' => 'id_kota_kabupaten',
                'bind' => ['id_provinsi' => $provinsi->id_provinsi]
            ];
            $kabKotas = Instansi::find($params);
            foreach ($kabKotas as $kabKota) {
                array_push($result, ['id' => $kabKota->id, 'nama' => $kabKota->nama, 'is_provinsi' => false]);
            }
        }

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue("A1", 'id_instansi');
        $sheet->setCellValue("B1", 'nama_instansi');
        $sheet->setCellValue("C1", 'tahun');
        $sheet->setCellValue("D1", 'target');
        $sheet->setCellValue("E1", 'anggaran');
        $sheet->setCellValue("F1", 'jumlah_bidang');
        $sheet->setCellValue("G1", 'keterangan');

        $rowIdx = 2;
        foreach ($result as $item) {
            $sheet->setCellValue("A$rowIdx", $item['id']);
            $sheet->setCellValue("B$rowIdx", $item['nama']);
            if ($item['is_provinsi']) {
                $sheet->getStyle("A$rowIdx:B$rowIdx")
                    ->getFont()->setBold(true);
            }
            $rowIdx++;
        }
        $sheet->getColumnDimension('A')->setAutoSize(true);
        $sheet->getColumnDimension('B')->setAutoSize(true);
        $sheet->getColumnDimension('C')->setAutoSize(true);
        $sheet->getColumnDimension('D')->setAutoSize(true);

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

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

        $params = $this->validate(
            $getParams,
            [
                'id' => ['validators' => ['required', 'digit']]
            ]
        );

        $target = KorwilAsetTarget::findFirst($params['id']);

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

        $teamCount = $this->countRelatedTeam($this->shared->user->um_id, $target->id_instansi);

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

    private function setAndValidateUser() {
        $currentUser = $this->shared->user;
        if (!$currentUser) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak berhak mengakses.');
        }

        $idInstansi = $currentUser->id_instansi;
        if (!$idInstansi) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak memiliki instansi');
        }

        $instansi = Instansi::findFirst($idInstansi);
        if (empty($instansi)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi tidak ditemukan');
        endif;

        if (!empty($instansi->deleted_at)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi sudah terhapus');
        endif;

        $this->setCurrentUser($currentUser);
    }

    private function validateUserInstansiAndKatInstansi() {
        if (!$this->currentUser || !$this->kat || $this->currentUser->id_instansi != $this->kat->id_instansi) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User tidak berhak mengakses");
        }
    }

    private function setAndValidateUserKat(KorwilAsetTarget $kat) {
        $this->setKat($kat);
        $this->setAndValidateUser();
        $this->validateUserInstansiAndKatInstansi();
    }
}