<?php

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

class InstansiController extends BaseController
{
    public function index()
    {
        $getParams = $this->request->get();

        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['id_tipe'] = array_key_exists('id_tipe', $getParams) ? $getParams['id_tipe'] : null;
        $getParams['id_sub_tipe'] = array_key_exists('id_sub_tipe', $getParams) ? $getParams['id_sub_tipe'] : null;
        $getParams['id_provinsi'] = array_key_exists('id_provinsi', $getParams) ? $getParams['id_provinsi'] : null;
        $getParams['is_stranas'] = array_key_exists('is_stranas', $getParams) ? $getParams['is_stranas'] : null;
        $getParams['order_by'] = $getParams['order_by'] ?? 'id';
        $getParams['order_direction'] = $getParams['order_direction'] ?? 'asc';
        $getParams['show_to_public'] = $getParams['show_to_public'] ?? null;
        $getParams['flag_dana_desa'] = $getParams['flag_dana_desa'] ?? null;
        $getParams['keluhan_type'] = $getParams['keluhan_type'] ?? null;
        $getParams['trashed'] = array_key_exists('trashed', $getParams);

        $params = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => [],
                'id_tipe' => ['validators' => ['digit']],
                'id_sub_tipe' => ['validators' => ['digit']],
                'id_provinsi' => ['validators' => ['digit']],
                'is_stranas' => ['validators' => ['digit']],
                'flag_pak' => ['validators' => []],
                'show_to_public' => ['validators' => []],
                'flag_dana_desa' => ['validators' => []],
                'keluhan_type' => ['validators' => []],
                'order_by' => ['order_by' => []],
                'order_direction' => ['order_direction' => []],
                'trashed' => ['validators' => []]
            ]
        );

        $builder = $this->modelsManager->createBuilder()->from('Instansi')
            ->limit($params['limit'])
            ->offset($params['offset'])
            ->orderBy($params['order_by'] . ' ' . $params['order_direction']);

        $countBuilder = $this->modelsManager->createBuilder()->from('Instansi')
            ->columns(['total_record' => 'COUNT(*)'])
            ->limit(1);

        if (!$params['trashed']) {
            $builder->andWhere(
                "Instansi.deleted_at IS NULL"
            );
            $countBuilder->andWhere(
                "Instansi.deleted_at IS NULL"
            );
        }

        if (!empty($params['keyword'])) {
            $builder->andWhere(
                "Instansi.nama ilike :keyword:",
                [
                    'keyword' => '%' . $params['keyword'] . '%',
                ]
            );
            $countBuilder->andWhere(
                "Instansi.nama ilike :keyword:",
                [
                    'keyword' => '%' . $params['keyword'] . '%',
                ]
            );
        }
        if (!is_null($params['is_stranas'])) {
            $builder->andWhere(
                "is_stranas = :is_stranas:",
                [
                    'is_stranas' => $getParams['is_stranas']
                ]
            );
            $countBuilder->andWhere(
                "is_stranas = :is_stranas:",
                [
                    'is_stranas' => $getParams['is_stranas']
                ]
            );
        }
        if (!is_null($params['id_tipe'])) {
            $builder->andWhere(
                "id_tipe = :id_tipe:",
                [
                    'id_tipe' => $getParams['id_tipe'],
                ]
            );
            $countBuilder->andWhere(
                "id_tipe = :id_tipe:",
                [
                    'id_tipe' => $getParams['id_tipe'],
                ]
            );
        }
        if (!is_null($params['id_sub_tipe'])) {
            $builder->andWhere(
                "id_sub_tipe = :id_sub_tipe:",
                [
                    'id_sub_tipe' => $getParams['id_sub_tipe'],
                ]
            );
            $countBuilder->andWhere(
                "id_sub_tipe = :id_sub_tipe:",
                [
                    'id_sub_tipe' => $getParams['id_sub_tipe'],
                ]
            );
        }
        if (!is_null($params['id_provinsi'])) {
            $builder->andWhere(
                "id_provinsi = :id_provinsi:",
                [
                    'id_provinsi' => $getParams['id_provinsi'],
                ]
            );
            $countBuilder->andWhere(
                "id_provinsi = :id_provinsi:",
                [
                    'id_provinsi' => $getParams['id_provinsi'],
                ]
            );
        }
        if (!is_null($params['flag_pak'])) {
            $builder->andWhere(
                "flag_pak = :flag_pak:",
                [
                    'flag_pak' => $getParams['flag_pak']
                ]
            );
            $countBuilder->andWhere(
                "flag_pak = :flag_pak:",
                [
                    'flag_pak' => $getParams['flag_pak']
                ]
            );
        }
        if(!is_null($params['show_to_public'])) {
            $builder->andWhere(
                'show_to_public = :show_to_public:',
                [
                    'show_to_public' => $getParams['show_to_public']
                ]
            );
            $countBuilder->andWhere(
                'show_to_public = :show_to_public:',
                [
                    'show_to_public' => $getParams['show_to_public']
                ]
            );
        }
        if(!is_null($params['flag_dana_desa'])) {
            $builder->andWhere(
                'flag_dana_desa = :flag_dana_desa:',
                [
                    'flag_dana_desa' => $getParams['flag_dana_desa']
                ]
            );
            $countBuilder->andWhere(
                'flag_dana_desa = :flag_dana_desa:',
                [
                    'flag_dana_desa' => $getParams['flag_dana_desa']
                ]
            );
        }

        if(!is_null($params['keluhan_type'])) {
            $builder->andWhere(
                'keluhan_type LIKE :keluhan_type:',
                [
                    'keluhan_type' => '%' . $getParams['keluhan_type'] . '%'
                ]
            );
            $countBuilder->andWhere(
                'keluhan_type LIKE :keluhan_type:',
                [
                    'keluhan_type' => '%' . $getParams['keluhan_type'] . '%'
                ]
            );
        }

        $rows = $builder->getQuery()->execute();
        $total_record = (int) $countBuilder->getQuery()->execute()->getFirst()->total_record;

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

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

        $instansi = Instansi::findFirst($id);

        if (empty($instansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi Tidak Ditemukan');
        }

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

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

        if (!$status) {
            $errors = $instansi->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([$instansi]);
        }
    }

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

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

        $params = $this->validate(
            $rawBody,
            [
                'id_user' => ['validators' => []],
                'id_instansi' => ['validators' => ['required']]
            ]
        );

        if (!is_array($params['id_user'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User tidak diketemukan');
        }

        $instansi = Instansi::findFirst($params['id_instansi']);
        if (empty($instansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi tidak diketemukan');
        }
        if ($instansi->deleted_at) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi sudah terhapus');
        }

        if (count($params['id_user']) < 1) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User kosong');
        }

        $id_user = array_unique(array_values($params['id_user']));

        $user_list = UmUser::find([
            'conditions' => 'um_id in ({ids:array})',
            'bind' => [
                'ids' => array_values($id_user)
            ]
        ]);

        $existing_user_list = [];

        foreach ($user_list as $key => $user) {
            $existing_user_list[] = $user->um_id;
        }

        $not_exist_ids = array_diff($id_user, $existing_user_list);

        if (!empty($not_exist_ids)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User dengan ID ' . implode(",", $not_exist_ids) . ' tidak aktif/terdaftar');
        }

        foreach ($user_list as $key => $user) {
            $user->id_user = $params['id_user'];
            $user->id_instansi = $params['id_instansi'];
            $user->updated_at = date('Y-m-d H:i:s');
            $user->save();
        }
        return new CollectionPaginatedResponse($user_list);
    }


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

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

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

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

        $conditions = $bind = [];

        if (!is_null($getParams['flag_dana_desa'])) {
            $this->validate($getParams, [
                'flag_dana_desa' => ['validators' => ['digit']]
            ]);

            array_push($conditions, 'flag_dana_desa = :flag_dana_desa:');
            $bind['flag_dana_desa'] = $getParams['flag_dana_desa'];
        }

        if (!is_null($getParams['id_tipe'])) {
            $this->validate($getParams, [
                'id_tipe' => ['validators' => ['digit']]
            ]);

            array_push($conditions, 'id_tipe = :id_tipe:');
            $bind['id_tipe'] = $getParams['id_tipe'];
        }

        array_push($conditions, 'nama ilike :keyword:');
        $bind['keyword'] = '%' . $params['keyword'] . '%';

        $instansiList = Instansi::find([
            'conditions' => implode(" and ", $conditions),
            'bind' => $bind,
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ]);

        $total_instansi = Instansi::count([
            'conditions' => implode(" and ", $conditions),
            'bind' => $bind
        ]);

        return new PaginatedResponse($instansiList, $total_instansi, $params['limit'], $params['offset']);
    }


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

        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $rawBody,
            [
                'id_tipe' => ['validators' => ['required', 'digit']],
                'id_provinsi' => ['validators' => ['digit']],
                'id_kota_kabupaten' => ['validators' => ['digit']],
                'id_sub_tipe' => ['validators' => ['digit']],
                'show_to_public' => ['validators' => ['digit']],
                'is_stranas' => ['validators' => ['digit']],
                'nama' => ['validators' => ['required']],
                'flag_dana_desa' => ['validators' => ['digit']],
                'flag_pak' => ['validators' => []],
                'keluhan_type' => ['validators' =>  []]
            ]
        );

        if ($params['keluhan_type']) {
            $typeBansos = explode(';', $params['keluhan_type']);
            foreach ($typeBansos as $type) {
                if (!in_array($type, [TopikDiskusiBansos::TYPE_BANSOS, TopikDiskusiBansos::TYPE_COVID, TopikDiskusiBansos::TYPE_IKN, TopikDiskusiBansos::TYPE_DESA, TopikDiskusiBansos::TYPE_KAMPUS])) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Tipe keluhan tidak valid');
                }
            }
        }

        $instansi_tipe = InstansiTipe::findFirst($params['id_tipe']);
        if (empty($instansi_tipe)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Tipe tidak ditemukan');
        }

        if (!empty($instansi_tipe->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Tipe tidak aktif');
        }

        if (!empty($params['id_sub_tipe'])) {
            $instansiSubTipe = InstansiSubTipe::findFirst($params['id_sub_tipe']);
            if (empty($instansiSubTipe)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Sub tipe tidak ditemukan');
            }

            if (!empty($instansiSubTipe->deleted_at)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Sub tipe tidak aktif');
            }

            if ($instansiSubTipe->id_tipe != $instansi_tipe->id) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Tipe dan sub tipe tidak sesuai');
            }
        }

        if (!empty($params['id_provinsi'])) {
            $provinsi = MasterProvinsi::findFirst($params['id_provinsi']);
            if (empty($provinsi)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Provinsi tidak ditemukan');
            }
        } else {
            $params['id_provinsi'] = null;
        }

        if (!empty($params['id_kota_kabupaten'])) {
            $kotaKabupaten = MasterKotaKabupaten::findFirst($params['id_kota_kabupaten']);
            if (empty($kotaKabupaten)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Kota/Kabupaten tidak ditemukan');
            }

            if (!empty($provinsi) && $kotaKabupaten->provinsi_code != $provinsi->provinsi_code) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Provinsi tidak sesuai');
            }
        } else {
            $params['id_kota_kabupaten'] = null;
        }

        $instansi = new Instansi();
        $instansi->nama = $params['nama'];
        $instansi->show_to_public = $params['show_to_public'];
        $instansi->is_stranas = $params['is_stranas'];
        $instansi->created_at = date('Y-m-d H:i:s');
        $instansi->created_by = $this->shared->user->um_id;
        $instansi->id_tipe = $params['id_tipe'];
        $instansi->id_sub_tipe = $params['id_sub_tipe'];
        $instansi->id_provinsi = (!empty($params['id_provinsi']) ? $params['id_provinsi'] : null);
        $instansi->id_kota_kabupaten = (!empty($params['id_kota_kabupaten']) ? $params['id_kota_kabupaten'] : null);
        $instansi->flag_dana_desa = $params['flag_dana_desa'];
        $instansi->flag_pak = $params['flag_pak'];
        $instansi->keluhan_type = $params['keluhan_type'];
        $status = $instansi->save();
        if (!$status) {
            $errors = $instansi->getMessages();
            $error_messages = [];

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

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

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

        $instansiDetail = Instansi::findFirst($id);
        if (empty($instansiDetail)) :
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'instansi dengan id' . $id . 'tidak diketemukan');
        endif;

        $result = $instansiDetail->toDetail();
        return new CollectionPaginatedResponse([$result]);
    }

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

        $instansi = Instansi::findFirst($id);

        if (empty($instansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Indikator Tidak Ditemukan');
        }

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

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

        if (!$status) {
            $errors = $instansi->getMessage();
            $error_messages = [];
            foreach ($errors as $e) :
                $error_messages[] = [
                    'field' => $e->getField(),
                    'message' => $e->getMessage()
                ];
            endforeach;
        } else {
            return new CollectionPaginatedResponse([$instansi]);
        }
    }

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

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

        $params = $this->validate(
            $rawBody,
            [
                'id_tipe' => ['validators' => ['required', 'digit']],
                'id_sub_tipe' => ['validators' => ['digit']],
                'id_instansi_list' => ['validators' => []]
            ]
        );

        if (!is_array($params['id_instansi_list'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Format ID Instansi List tidak valid');
        }

        $cekTipe = InstansiTipe::findFirst($params['id_tipe']);

        if (empty($cekTipe)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'ID tidak diketemukan');
        }

        if (count($params['id_instansi_list']) < 1) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi kosong');
        }

        $id_instansi_list = array_unique(array_values($params['id_instansi_list']));

        $instansi_list = Instansi::find([
            'conditions' => 'id in ({ids:array}) AND deleted_at is NULL',
            'bind' => [
                'ids' => array_values($id_instansi_list)
            ]
        ]);

        $existing_instasi_list = [];

        foreach ($instansi_list as $key => $instansi) {
            $existing_instasi_list[] = $instansi->id;
        }

        $not_exist_ids = array_diff($id_instansi_list, $existing_instasi_list);

        if (!empty($not_exist_ids)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Instansi dengan ID ' . implode(",", $not_exist_ids) . ' tidak aktif/terdaftar');
        }

        foreach ($instansi_list as $key => $instansi) {
            $instansi->id_tipe = $params['id_tipe'];
            $instansi->id_sub_tipe = $params['id_sub_tipe'];
            $instansi->id_instansi_list = $params['id_instansi_list'];
            $instansi->updated_at = date('Y-m-d H:i:s');
            $instansi->save();
        }
        return new CollectionPaginatedResponse($instansi_list);
    }

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

        $instansi = Instansi::findFirst($id);
        if (empty($instansi)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'ID dengan ' . $id . ' tidak diketemukan');
        }

        if (!empty($instansi->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'ID dengan ' . $id . ' sudah terhapus');
        }

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

        $params =  $this->validate(
            $rawBody,
            [
                'id_tipe' => ['validators' => ['required', 'digit']],
                'id_sub_tipe' => ['validators' => ['digit']],
                'id_provinsi' => ['validators' => ['digit']],
                'id_kota_kabupaten' => ['validators' => ['digit']],
                'show_to_public' => ['validators' => ['digit']],
                'is_stranas' => ['validators' => ['digit']],
                'nama' => ['validators' => ['required']],
                'flag_dana_desa' => ['validators' => ['digit']],
                'flag_pak' => ['validators' => []],
                'keluhan_type' => ['validators' =>  []],
                'kelompok_anggaran' => ['validators' =>  []],
                'nama_kelompok_anggaran' => ['validators' =>  []]
            ]
        );

        if ($params['keluhan_type']) {
            $typeBansos = explode(';', $params['keluhan_type']);
            foreach ($typeBansos as $type) {
                if (!in_array($type, [TopikDiskusiBansos::TYPE_BANSOS, TopikDiskusiBansos::TYPE_COVID, TopikDiskusiBansos::TYPE_IKN, TopikDiskusiBansos::TYPE_DESA, TopikDiskusiBansos::TYPE_KAMPUS])) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Tipe keluhan tidak valid');
                }
            }
        }

        $cekTipe = InstansiTipe::findFirst($params['id_tipe']);
        if (empty($cekTipe)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'ID dengan ' . $params['id_tipe'] . ' tidak diketemukan');
        }

        if (!empty($cekTipe->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'ID dengan ' . $params['id_tipe'] . ' sudah terhapus');
        }

        if (!empty($params['id_sub_tipe'])) {
            $instansiSubTipe = InstansiSubTipe::findFirst($params['id_sub_tipe']);
            if (empty($instansiSubTipe)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Sub tipe tidak ditemukan');
            }

            if (!empty($instansiSubTipe->deleted_at)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Sub tipe tidak aktif');
            }

            if ($instansiSubTipe->id_tipe != $cekTipe->id) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Tipe dan sub tipe tidak sesuai');
            }
        } else {
            $params['id_sub_tipe'] = null;
        }

        if (!empty($params['id_provinsi'])) {
            $provinsi = MasterProvinsi::findFirst(['conditions' => 'provinsi_code = :code:', 'bind' => ['code' => $params['id_provinsi']]]);
            if (empty($provinsi)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Provinsi tidak ditemukan');
            }
        } else {
            $params['id_provinsi'] = null;
        }

        if (!empty($params['id_kota_kabupaten'])) {
            $kotaKabupaten = MasterKotaKabupaten::findFirst(['conditions' => 'kota_kabupaten_code = :code:', 'bind' => ['code' => $params['id_kota_kabupaten']]]);
            if (empty($kotaKabupaten)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Kota/Kabupaten tidak ditemukan');
            }

            if (!empty($provinsi) && $kotaKabupaten->provinsi_code != $provinsi->provinsi_code) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Provinsi tidak sesuai');
            }
        } else {
            $params['id_kota_kabupaten'] = null;
        }

        $instansi->nama = $params['nama'];
        $instansi->id_tipe = $params['id_tipe'];
        $instansi->id_sub_tipe = $params['id_sub_tipe'];
        $instansi->show_to_public = $params['show_to_public'];
        $instansi->is_stranas = $params['is_stranas'];
        $instansi->updated_at = date('Y-m-d H:i:s');
        $instansi->flag_dana_desa = $params['flag_dana_desa'];
        $instansi->flag_pak = $params['flag_pak'];
        $instansi->keluhan_type = $params['keluhan_type'];
        $instansi->id_provinsi = $params['id_provinsi'];
        $instansi->id_kota_kabupaten = $params['id_kota_kabupaten'];
        $instansi->kelompok_anggaran = $params['kelompok_anggaran'];
        $instansi->nama_kelompok_anggaran = $params['nama_kelompok_anggaran'];
        $status = $instansi->save();

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

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

            throw new ValidationsException($error_messages);
        } else {
            return new CollectionPaginatedResponse([$instansi]);
        }
    }

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

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

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

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

        $instansi = Instansi::findFirst($id);

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

        $listScopes = $instansi->getUmUser([
            'conditions' => 'nama ilike :keyword:',
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%'
            ],
            'columns' => 'um_id, um_user_name, email, id_instansi',
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'order' => '[UmUser].'.$params['order_by'] . ' ' . $params['order_direction']
        ]);

        $total_record = $instansi->getUmUser([
            'conditions' => 'nama ilike :keyword:',
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%'
            ],
            'columns' => 'COUNT(*) AS total',
            'group_by' => "[UmUser].[id]"
        ])[0]['total'];

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

    public function deleteUsers($id) {

        $this->checkScopes(['admin.view']);

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

        $params = $this->validate(
            $rawBody,
            [
                'um_user_id' => ['validator' => ['required']]
            ]
        );

        $umUserIds = is_array($params['um_user_id']) ? $params['um_user_id'] : [$params['um_user_id']];
        $umUsers = UmUser::find([
            'conditions' => 'id_instansi = :id_instansi: AND um_id IN ({umUserIds:array})',
            'bind' => [
                'umUserIds' => $umUserIds,
                'id_instansi' => $id
            ]
        ]);

        foreach ($umUsers as $umUser) {
            $umUser->id_instansi = null;
            $status = $umUser->save();
            if (!$status) {
                $errors = $umUser->getMessage();
                $error_messages = [];

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

                throw new ValidationException($error_messages);
            }
        }

        return new SimpleResponse("Success");
    }

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

        $request = $this->request->get();
        $request['limit'] = array_key_exists('limit', $request) ? $request['limit'] : 50;
        $request['offset'] = array_key_exists('offset', $request) ? $request['offset'] : 0;
        $validatedRequest = $this->validate($request, [
            'limit' => ['validators' => ['required', 'digit']],
            'offset' => ['validators' => ['required', 'digit']],
            'id_template' => ['validators' => ['required', 'digit']],
            'keyword' => ['validators' => []],
        ]);

        $idTemplate = $validatedRequest['id_template'];
        $limit = $validatedRequest['limit'];
        $offset = $validatedRequest['offset'];
        $keyword = $validatedRequest['keyword'];

        $andWhere = "";
        $bind = ['id_template' => $idTemplate];

        if (!empty($keyword)) {
            $andWhere .= " AND i.nama ILIKE :keyword";
            $bind['keyword'] = '%' . $keyword . '%';
        }

        $query = "
            FROM jaga.instansi i
            LEFT JOIN jaga.korsupgah_survey ks ON ks.id_instansi = i.id
            WHERE ks.id_template = :id_template $andWhere
        ";

        $result = $this->rawQuery("SELECT DISTINCT i.id, i.nama, i.id_provinsi, i.id_kota_kabupaten $query GROUP BY i.id ORDER BY i.id_provinsi, i.id_kota_kabupaten NULLS FIRST LIMIT $limit OFFSET $offset", $bind);
        $total = $this->rawQuery("SELECT COUNT(DISTINCT i.id) AS total $query", $bind)[0]['total'];

        return new PaginatedResponse($result, $total, $limit, $offset);
    }

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

        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data']);

        $getParams = $this->request->get();
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : null;
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $params = $this->validate(
            $getParams,
            [
                'keyword' => ['validators' => []],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']]
            ]
        );

        $idTipeInstansiKlpd = [1,2,3]; //Sesuai Prod: 1 = Pemda Prov, 2 = Pemda Kabkota, 3 = K/L

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

        $conditions[] = 'id_tipe IN ({id_tipe:array})';
        $bind['id_tipe'] = array_values($idTipeInstansiKlpd);

        if(!empty($params['keyword']) && $params['keyword'] != null) {
            $conditions[] = 'nama ILIKE :keyword:';
            $bind['keyword'] = '%' . $params['keyword'] . '%';
        }

        $result = Instansi::find([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind,
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ]);

        $count = Instansi::count([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind,
        ]);

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

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

        $this->checkOneOfScopes(['spi.manage_master_data', 'spi.view_klop_data', 'spi.observer_rtl']);

        $getParams = $this->request->get();
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : null;
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $params = $this->validate(
            $getParams,
            [
                'keyword' => ['validators' => []],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']]
            ]
        );

        $idTipeInstansiKlpd = [1,2,3,5]; //Sesuai Prod: 1 = Pemda Prov, 2 = Pemda Kabkota, 3 = K/L, 5 = Lainnya

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

        $conditions[] = 'id_tipe IN ({id_tipe:array})';
        $bind['id_tipe'] = array_values($idTipeInstansiKlpd);

        if(!empty($params['keyword']) && $params['keyword'] != null) {
            $conditions[] = 'nama ILIKE :keyword:';
            $bind['keyword'] = '%' . $params['keyword'] . '%';
        }

        $result = Instansi::find([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind,
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ]);

        $count = Instansi::count([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind,
        ]);

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

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

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

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

        $idTipeInstansi = [1, 2]; // Sesuai Prod: 1 = Pemda Prov, 2 = Pemda Kabkota

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

        $conditions[] = 'id_tipe IN ({id_tipe:array})';
        $bind['id_tipe'] = array_values($idTipeInstansi);

        if(!empty($params['keyword']) && $params['keyword'] != null) {
            $conditions[] = 'nama ILIKE :keyword:';
            $bind['keyword'] = '%' . $params['keyword'] . '%';
        }

        $result = Instansi::find([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind,
            'limit' => $params['limit'],
            'offset' => $params['offset']
        ]);

        $count = Instansi::count([
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind,
        ]);

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

    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)) {
            $rows = [];
            $this->db->begin();
            try {
                $filename = $document->getTempName();
                $spreadsheet = PhpOffice\PhpSpreadsheet\IOFactory::load($filename);
                $worksheet = $spreadsheet->getActiveSheet();

                foreach ($worksheet->getRowIterator() as $row) {
                    $rowIndex = $row->getRowIndex();
                    if ($rowIndex < 2) {
                        $colIndex = 1;
                        if ($worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getFormattedValue() != 'id' ||
                            $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getFormattedValue() != 'nama' ||
                            $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getFormattedValue() != 'show_to_public' ||
                            $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getFormattedValue() != 'id_provinsi' ||
                            $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getFormattedValue() != 'id_kota_kabupaten' ||
                            $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getFormattedValue() != 'is_stranas' ||
                            $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getFormattedValue() != 'flag_dana_desa' ||
                            $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getFormattedValue() != 'flag_pak' ||
                            $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getFormattedValue() != 'id_tipe' ||
                            $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getFormattedValue() != 'id_sub_tipe'
                        ) {
                            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Format kolom tidak sesuai! Kolom: [" . ($colIndex - 1) . "] " . $worksheet->getCellByColumnAndRow(($colIndex - 1), $rowIndex)->getFormattedValue());
                        }
                    } else {
                        $colIndex = 2;
                        $nama = $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getValue();
                        $show_to_public = $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getValue();
                        $id_provinsi = $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getValue();
                        $id_kota_kabupaten = $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getValue();
                        $is_stranas = $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getValue();
                        $flag_dana_desa = $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getValue();
                        $flag_pak = $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getValue();
                        $id_tipe = $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getValue();
                        $id_sub_tipe = $worksheet->getCellByColumnAndRow($colIndex++, $rowIndex)->getValue();

                        if (!is_null($nama)) {
                            $exist = Instansi::findFirst([
                                'conditions' => 'nama ilike :nama:',
                                'bind' => ['nama' => '%' . $nama . '%']
                            ]);
                            if ($exist) {
                                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Instansi dengan nama [".$nama."] telah ada sebelumnya.");
                            }

                            $instansi = new Instansi();
                            $instansi->nama = $nama;
                            $instansi->show_to_public = $show_to_public;
                            $instansi->id_provinsi = $id_provinsi;
                            $instansi->id_kota_kabupaten = $id_kota_kabupaten;
                            $instansi->is_stranas = $is_stranas;
                            $instansi->flag_dana_desa = $flag_dana_desa;
                            $instansi->flag_pak = $flag_pak;
                            $instansi->id_tipe = $id_tipe;
                            $instansi->id_sub_tipe = $id_sub_tipe;

                            if ($instansi->save()) {
                                array_push($rows, $instansi);
                            } else {
                                $errors = $instansi->getMessages();
                                $error_messages = [];

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

                                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, $error_messages);
                            }
                        }
                    }
                }
                $this->db->commit();

                return new CollectionPaginatedResponse($rows);
            } catch (\Exception $e) {
                $this->db->rollback();
                throw $e;
            }
        }
    }

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

        $getParams = $this->request->get();
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['flag_dana_desa'] = array_key_exists('flag_dana_desa', $getParams) ? $getParams['flag_dana_desa'] : null;
        $getParams['id_tipe'] = array_key_exists('id_tipe', $getParams) ? $getParams['id_tipe'] : [];

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

        $conditions = $bind = [];

        if (!is_null($getParams['flag_dana_desa'])) {
            $this->validate($getParams, [
                'flag_dana_desa' => ['validators' => ['digit']]
            ]);

            array_push($conditions, 'flag_dana_desa = :flag_dana_desa:');
            $bind['flag_dana_desa'] = $getParams['flag_dana_desa'];
        }

        if (!empty($getParams['id_tipe'])) {
            $idTipes = explode(";", $getParams['id_tipe']);
            foreach ($idTipes as $idTipe) {
                $arr = ['id_tipe' => $idTipe];
                $this->validate($arr, [
                    'id_tipe' => ['validators' => ['digit']]
                ]);
            }

            array_push($conditions, 'id_tipe IN ({id_tipe:array})');
            $bind['id_tipe'] = $idTipes;
        }

        array_push($conditions, 'nama ilike :keyword:');
        $bind['keyword'] = '%' . $params['keyword'] . '%';

        $rows = Instansi::find([
            'conditions' => implode(" and ", $conditions),
            'bind' => $bind
        ]);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $col = "A";

        $sheet->setCellValue($col++."1", 'id');
        $sheet->setCellValue($col++."1", 'nama');
        $sheet->setCellValue($col++."1", 'show_to_public');
        $sheet->setCellValue($col++."1", 'id_provinsi');
        $sheet->setCellValue($col++."1", 'id_kota_kabupaten');
        $sheet->setCellValue($col++."1", 'is_stranas');
        $sheet->setCellValue($col++."1", 'flag_dana_desa');
        $sheet->setCellValue($col++."1", 'flag_pak');
        $sheet->setCellValue($col++."1", 'id_tipe');
        $sheet->setCellValue($col++."1", 'id_sub_tipe');

        $sheet->getStyle("A1:".$col."1")->getFont()->setBold(true);

        $rowIdx = 1;
        foreach ($rows as $row) {
            $rowIdx++;
            $col = "A";
            $sheet->setCellValue($col++.$rowIdx, $row->id);
            $sheet->setCellValue($col++.$rowIdx, $row->nama);
            $sheet->setCellValue($col++.$rowIdx, $row->show_to_public);
            $sheet->setCellValue($col++.$rowIdx, $row->id_provinsi);
            $sheet->setCellValue($col++.$rowIdx, $row->id_kota_kabupaten);
            $sheet->setCellValue($col++.$rowIdx, $row->is_stranas);
            $sheet->setCellValue($col++.$rowIdx, $row->flag_dana_desa);
            $sheet->setCellValue($col++.$rowIdx, $row->flag_pak);
            $sheet->setCellValue($col++.$rowIdx, $row->id_tipe);
            $sheet->setCellValue($col++.$rowIdx, $row->id_sub_tipe);
        }

        $writer = new Xlsx($spreadsheet);
        $filename = "Daftar Instansi - " . DateHelper::getNowTimezoned() . ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }

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

        $validatedRequest = $this->validate(
            $params,
            [
                'keyword' => ['validators' => []],
            ]
        );
        $conditions = [
            'deleted_at IS NULL',
            'show_to_public = :show_to_public:',
            'id_tipe IN ({id_tipe:array})'
        ];
        $bind = [
            'show_to_public' => 1,
            'id_tipe' => [1,2,3,5]
        ];

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

        $instansi = Instansi::find([
            'columns' => 'id, nama',
            'conditions' => implode(' AND ', $conditions),
            'bind' => $bind,
            'order' => 'nama'
        ]);

        return new SimpleResponse($instansi);
    }

    public function listForPancek()
    {
        $this->checkOneOfScopes(['pancek.admin', 'admin.view']);

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

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

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

        $conditions = $bind = [];

        $conditions[] = 'nama ilike :keyword:';
        $bind['keyword'] = '%' . $params['keyword'] . '%';

        $conditions[] = 'nama IN ({nama_arr:array})';
        $bind['nama_arr'] = [
            Instansi::DITJEN_BEA_CUKAI,
            Instansi::DITJEN_BINA_KONSTRUKSI_PUPR
        ];

        $instansiList = Instansi::find([
            'conditions' => implode(" and ", $conditions),
            'bind' => $bind,
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'order' => $params['order_by'] . ' ' . $params['order_direction']
        ]);

        $total_instansi = Instansi::count([
            'conditions' => implode(" and ", $conditions),
            'bind' => $bind
        ]);

        return new PaginatedResponse($instansiList, $total_instansi, $params['limit'], $params['offset']);
    }
}