<?php

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

class PancekInstansiController extends BaseController
{
    #region public
    public function list()
    {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.monitoring', 'pancek.verify', 'pancek.kppbc.verify', 'pancek.kppbc.assignor']);
        PancekUserAccess::checkEligibleForRoleObserver($this);

        $getParams = $this->request->get();
        $getParams['jenis_instansi_id'] = array_key_exists('jenis_instansi_id', $getParams) ? $getParams['jenis_instansi_id'] : null;
        $getParams['sektor_usaha_id'] = array_key_exists('sektor_usaha_id', $getParams) ? $getParams['sektor_usaha_id'] : null;
        $getParams['provinsi_id'] = array_key_exists('provinsi_id', $getParams) ? $getParams['provinsi_id'] : null;
        $getParams['kab_kota_id'] = array_key_exists('kab_kota_id', $getParams) ? $getParams['kab_kota_id'] : null;
        $getParams['status'] = array_key_exists('status', $getParams) ? $getParams['status'] : null;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $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';
        $getParams['ceklis_kepentingan'] = array_key_exists('ceklis_kepentingan', $getParams) ? $getParams['ceklis_kepentingan'] : null;

        $allowed_order = [
            'id',
            'jenis_instansi_id',
            'sektor_usaha_id',
            'provinsi_id',
            'kab_kota_id',
            'nama',
            'status',
            'created_at',
            'updated_at'
        ];
        $allowed_order_direction = ['asc', 'desc'];

        $params = $this->validate(
            $getParams,
            [
                'jenis_instansi_id' => ['validators' => ['digit']],
                'sektor_usaha_id' => ['validators' => ['digit']],
                'provinsi_id' => ['validators' => ['digit']],
                'kab_kota_id' => ['validators' => ['digit']],
                'keyword' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 100
                ]]],
                'status' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 10
                ]]],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'order_by' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order
                ]]],
                'order_direction' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order_direction
                ]]],
                'ceklis_kepentingan' => ['validators' => [
                    [
                        'name' => 'max_string',
                        'params' => 255
                    ]
                ]]
            ]
        );

        $conditions = "(npwp ilike :keyword: OR nomor ilike :keyword: OR nama ilike :keyword: OR sektor_usaha_nama ilike :keyword:) AND deleted_at IS NULL";
        $bind = [
            'keyword' => '%' . $params['keyword'] . '%'
        ];
        if (!is_null($params['jenis_instansi_id'])) {
            $conditions .= " AND jenis_instansi_id = :jenis_instansi_id:";
            $bind['jenis_instansi_id'] = $params['jenis_instansi_id'];
        }
        if (!is_null($params['sektor_usaha_id'])) {
            $conditions .= " AND sektor_usaha_id = :sektor_usaha_id:";
            $bind['sektor_usaha_id'] = $params['sektor_usaha_id'];
        }
        if (!is_null($params['provinsi_id'])) {
            $conditions .= " AND provinsi_id = :provinsi_id:";
            $bind['provinsi_id'] = $params['provinsi_id'];
        }
        if (!is_null($params['kab_kota_id'])) {
            $conditions .= " AND kab_kota_id = :kab_kota_id:";
            $bind['kab_kota_id'] = $params['kab_kota_id'];
        }
        if (!is_null($params['status'])) {
            $conditions .= " AND status LIKE :status:";
            $bind['status'] = $params['status'];
        }
        if (!is_null($params['ceklis_kepentingan'])) {
            $ceklis = explode(';', $params['ceklis_kepentingan']);
            $filter = [];
            if ($ceklis[0] == '1') $filter[] = "ceklis_kepentingan LIKE '1;_;_'";
            if ($ceklis[1] == '1') $filter[] = "ceklis_kepentingan LIKE '_;1;_'";
            if ($ceklis[2] == '1') $filter[] = "ceklis_kepentingan LIKE '_;_;1'";
            $str = implode(' OR ', $filter);
            $conditions .= " AND ($str)";
        }

        $idInstansiBeaCukai = Instansi::getIdInstansiDitjenBeaCukai();
        $idinstansiDijtenPupr = Instansi::getIdInstansiDitjenPupr();
        $idInstansiUser = $this->shared->user->id_instansi;
        if($this->hasOneOfScopes(['pancek.monitoring', 'pancek.verify']) && $idInstansiBeaCukai == $idInstansiUser) {
            $conditions .= " AND ceklis_kepentingan = '0;1;0'";
        }
        else if($this->hasOneOfScopes(['pancek.monitoring', 'pancek.verify']) && $idinstansiDijtenPupr == $idInstansiUser) {
            $conditions .= " AND ceklis_kepentingan = '1;0;0'";
        }
        else if($this->hasScopes(['pancek.kppbc.assignor']) && $idInstansiBeaCukai != $idInstansiUser) {
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
        }
        else if($this->hasScopes(['pancek.kppbc.assignor']) && $idInstansiBeaCukai == $idInstansiUser) {
            $kppbcUsers = PancekKppbcUser::findAssignorOnlyBy($this->userID)->toArray();
            if(empty($kppbcUsers)) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User belum di-assign ke KPPBC');
            }
            $conditions .= " AND kppbc_id IN ({kppbc_ids:array})";
            $bind['kppbc_ids'] = array_values(array_column($kppbcUsers, 'kppbc_id'));
        }
        else if($this->hasScopes(['pancek.kppbc.verify']) && $idInstansiBeaCukai != $idInstansiUser) {
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
        }
        else if($this->hasScopes(['pancek.kppbc.verify']) && $idInstansiBeaCukai == $idInstansiUser) {
            $kppbcUsers = PancekKppbcUser::findVerifikatorOnlyBy($this->userID)->toArray();
            if(empty($kppbcUsers)) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User belum di-assign ke KPPBC');
            }
            $conditions .= " AND kppbc_id IN ({kppbc_ids:array})";
            $bind['kppbc_ids'] = array_values(array_column($kppbcUsers, 'kppbc_id'));
        }
        $pancekInstansis = PancekInstansi::find([
            'conditions' => $conditions,
            'bind' => $bind,
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'order' => $params['order_by'] . ' ' . $params['order_direction']
        ]);

        $records = [];
        foreach ($pancekInstansis as $pancekInstansi) {
            /** @var $pancekInstansi PancekInstansi */
            $record = $pancekInstansi->getDetail();
            $record['formulir'] = PancekFormulir::findActiveByInstansiId($pancekInstansi->id);
            if ($record['formulir']) {
                $record['formulir'] = $record['formulir']->stat(['id', 'periode', 'jumlah_revisi', 'status']);
            }
            array_push($records, $record);
        }

        $total = PancekInstansi::count([
            'conditions' => $conditions,
            'bind' => $bind
        ]);

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

    public function listBk() {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.bk.assign']);
        $getParams = $this->request->get();
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;

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

        $cte = "
            WITH instansi_verifikator AS (
                SELECT iv.user_id, iv.instansi_id
                FROM jaga.pancek_instansi_verifikator iv
                LEFT JOIN pancek_user_access ua ON ua.user_id = iv.user_id AND ua.deleted_at IS NULL
                WHERE iv.deleted_at IS NULL AND (ua.end_date IS NULL OR now() < ua.end_date)
            ),
            instansi_filtered AS (
                SELECT *
                FROM jaga.pancek_instansi
                WHERE ceklis_kepentingan = '1;0;0' AND deleted_at IS NULL AND id NOT IN (SELECT instansi_id FROM instansi_verifikator)
            ),
            latest_formulir AS (
                SELECT *,
                       ROW_NUMBER() OVER (PARTITION BY instansi_id ORDER BY created_at DESC) AS rn
                FROM jaga.pancek_formulir
                WHERE deleted_at IS NULL 
                  AND created_at > now() - INTERVAL '3 year' AND status NOT IN ('NEW', 'DRAFT')
            )
        ";

        $query = "
            SELECT i.id, i.nama, i.alamat, mkk.kota_kabupaten_name AS kab_kota, mp.provinsi_name AS provinsi, i.email, i.nib, i.nomor, i.npwp, COALESCE(i.sektor_usaha_nama, su.nama) AS sektor_usaha, i.telepon, f.id AS formulir_id
            FROM instansi_filtered i
            JOIN latest_formulir f ON f.instansi_id = i.id AND f.rn = 1
            LEFT JOIN jaga.master_provinsi mp ON mp.provinsi_code::INTEGER = i.provinsi_id
            LEFT JOIN jaga.master_kota_kabupaten mkk ON mkk.kota_kabupaten_code::INTEGER = i.kab_kota_id
            LEFT JOIN jaga.pancek_sektor_usaha su ON su.id = i.sektor_usaha_id
        ";

        $conditions = " WHERE (i.nama ILIKE :keyword OR i.alamat ILIKE :keyword OR i.nomor ILIKE :keyword OR i.npwp ILIKE :keyword)";
        $limit = " LIMIT :limit OFFSET :offset";
        $bind = [
            'keyword' => '%' . $params['keyword'] . '%'
        ];

        $result = $this->rawQuery(
            $cte . $query . $conditions . $limit,
            array_merge($bind, ['limit' => $params['limit'], 'offset' => $params['offset']])
        );

        $queryCount = "
            SELECT count(i.id) as total
            FROM instansi_filtered i
            JOIN latest_formulir f ON f.instansi_id = i.id AND f.rn = 1
            LEFT JOIN jaga.master_provinsi mp ON mp.provinsi_code::INTEGER = i.provinsi_id
            LEFT JOIN jaga.master_kota_kabupaten mkk ON mkk.kota_kabupaten_code::INTEGER = i.kab_kota_id
            LEFT JOIN jaga.pancek_sektor_usaha su ON su.id = i.sektor_usaha_id
        ";
        $total = $this->rawQuery($cte . $queryCount . $conditions, $bind)[0]['total'];

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

    public function listBc() {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.kppbc.admin', 'pancek.kppbc.assignor']);
        $getParams = $this->request->get();
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;

        $kppbcIds = [];
        if ($this->hasScopes(['pancek.kppbc.assignor']) && !$this->hasOneOfScopes(['pancek.admin', 'pancek.kppbc.admin'])) {
            $pancekKppbcUsers = PancekKppbcUser::find(['conditions' => "um_id = :um_id: AND user_type = 'ASSIGNOR' AND deleted_at IS NULL", 'bind' => ['um_id' => $this->shared->user->um_id]]);
            if (count($pancekKppbcUsers) == 0) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Anda belum terdaftar pada KPPBC tertentu.");
            }
            foreach ($pancekKppbcUsers as $pancekKppbcUser) {
                $kppbcIds[] = $pancekKppbcUser->kppbc_id;
            }
        }
        $instansiKPPBCConditions = (empty($kppbcIds) ? "" : " AND kppbc_id IN (".implode(',', $kppbcIds).")");

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

        $cte = "
            WITH instansi_verifikator AS (
                SELECT iv.user_id, iv.instansi_id
                FROM jaga.pancek_instansi_verifikator iv
                LEFT JOIN pancek_user_access ua ON ua.user_id = iv.user_id AND ua.deleted_at IS NULL
                WHERE iv.deleted_at IS NULL AND (ua.end_date IS NULL OR now() < ua.end_date)
            ),
            instansi_filtered AS (
                SELECT *
                FROM jaga.pancek_instansi
                WHERE ceklis_kepentingan = '0;1;0' AND deleted_at IS NULL AND id NOT IN (SELECT instansi_id FROM instansi_verifikator)".$instansiKPPBCConditions."
            ),
            latest_formulir AS (
                SELECT *,
                       ROW_NUMBER() OVER (PARTITION BY instansi_id ORDER BY created_at DESC) AS rn
                FROM jaga.pancek_formulir
                WHERE deleted_at IS NULL 
                  AND created_at > now() - INTERVAL '3 year' AND status NOT IN ('NEW', 'DRAFT')
            )
        ";

        $query = "
            SELECT i.id, i.nama, i.alamat, mkk.kota_kabupaten_name AS kab_kota, mp.provinsi_name AS provinsi, i.email, i.nib, i.nomor, i.npwp, COALESCE(i.sektor_usaha_nama, su.nama) AS sektor_usaha, i.telepon, f.id AS formulir_id
            FROM instansi_filtered i
            JOIN latest_formulir f ON f.instansi_id = i.id AND f.rn = 1
            LEFT JOIN jaga.master_provinsi mp ON mp.provinsi_code::INTEGER = i.provinsi_id
            LEFT JOIN jaga.master_kota_kabupaten mkk ON mkk.kota_kabupaten_code::INTEGER = i.kab_kota_id
            LEFT JOIN jaga.pancek_sektor_usaha su ON su.id = i.sektor_usaha_id
        ";

        $conditions = " WHERE (i.nama ILIKE :keyword OR i.alamat ILIKE :keyword OR i.nomor ILIKE :keyword OR i.npwp ILIKE :keyword)";
        $limit = " LIMIT :limit OFFSET :offset";
        $bind = [
            'keyword' => '%' . $params['keyword'] . '%'
        ];

        $result = $this->rawQuery(
            $cte . $query . $conditions . $limit,
            array_merge($bind, ['limit' => $params['limit'], 'offset' => $params['offset']])
        );

        $queryCount = "
            SELECT count(i.id) as total
            FROM instansi_filtered i
            JOIN latest_formulir f ON f.instansi_id = i.id AND f.rn = 1
            LEFT JOIN jaga.master_provinsi mp ON mp.provinsi_code::INTEGER = i.provinsi_id
            LEFT JOIN jaga.master_kota_kabupaten mkk ON mkk.kota_kabupaten_code::INTEGER = i.kab_kota_id
            LEFT JOIN jaga.pancek_sektor_usaha su ON su.id = i.sektor_usaha_id
        ";
        $total = $this->rawQuery($cte . $queryCount . $conditions, $bind)[0]['total'];

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

    public function listBkPerVerifikator() {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.bk.assign']);
        $getParams = $this->request->get();
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;

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

        $cte = "
            WITH instansi_verifikator AS (
                SELECT iv.user_id, iv.instansi_id
                FROM jaga.pancek_instansi_verifikator iv
                LEFT JOIN pancek_user_access ua ON ua.user_id = iv.user_id AND ua.deleted_at IS NULL
                WHERE iv.deleted_at IS NULL AND (ua.end_date IS NULL OR now() < ua.end_date) AND iv.user_id = :verifikator_id
            ),
            instansi_filtered AS (
                SELECT *
                FROM jaga.pancek_instansi
                WHERE ceklis_kepentingan = '1;0;0' AND deleted_at IS NULL AND id IN (SELECT instansi_id FROM instansi_verifikator) AND (nama ILIKE :keyword OR alamat ILIKE :keyword OR nomor ILIKE :keyword OR npwp ILIKE :keyword)
            ),
            latest_formulir AS (
                SELECT *,
                       ROW_NUMBER() OVER (PARTITION BY instansi_id ORDER BY created_at DESC) AS rn
                FROM jaga.pancek_formulir
                WHERE deleted_at IS NULL 
                  AND created_at > now() - INTERVAL '3 year'
            )
        ";

        $query = "
            SELECT i.id, i.nama, i.alamat, mkk.kota_kabupaten_name AS kab_kota, mp.provinsi_name AS provinsi, i.email, i.nib, i.nomor, i.npwp, COALESCE(i.sektor_usaha_nama, su.nama) AS sektor_usaha, i.telepon, f.id AS formulir_id
            FROM instansi_filtered i
            JOIN latest_formulir f ON f.instansi_id = i.id AND f.rn = 1
            LEFT JOIN jaga.master_provinsi mp ON mp.provinsi_code::INTEGER = i.provinsi_id
            LEFT JOIN jaga.master_kota_kabupaten mkk ON mkk.kota_kabupaten_code::INTEGER = i.kab_kota_id
            LEFT JOIN jaga.pancek_sektor_usaha su ON su.id = i.sektor_usaha_id
        ";

        $limit = " LIMIT :limit OFFSET :offset";
        $bind = [
            'verifikator_id' => $params['verifikator_id'],
            'keyword' => '%' . $params['keyword'] . '%'
        ];

        $result = $this->rawQuery(
            $cte . $query . $limit,
            array_merge($bind, ['limit' => $params['limit'], 'offset' => $params['offset']])
        );

        $queryCount = "
            SELECT count(i.id) as total
            FROM instansi_filtered i
            JOIN latest_formulir f ON f.instansi_id = i.id AND f.rn = 1
            LEFT JOIN jaga.master_provinsi mp ON mp.provinsi_code::INTEGER = i.provinsi_id
            LEFT JOIN jaga.master_kota_kabupaten mkk ON mkk.kota_kabupaten_code::INTEGER = i.kab_kota_id
            LEFT JOIN jaga.pancek_sektor_usaha su ON su.id = i.sektor_usaha_id
        ";
        $total = $this->rawQuery($cte . $queryCount, $bind)[0]['total'];

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

    public function listBcPerVerifikator() {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.kppbc.admin', 'pancek.kppbc.assignor']);
        $getParams = $this->request->get();
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;

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

        $kppbcIds = [];
        if ($this->hasScopes(['pancek.kppbc.assignor']) && !$this->hasOneOfScopes(['pancek.admin', 'pancek.kppbc.admin'])) {
            $pancekKppbcUsers = PancekKppbcUser::find(['conditions' => "um_id = :um_id: AND user_type = 'ASSIGNOR' AND deleted_at IS NULL", 'bind' => ['um_id' => $this->shared->user->um_id]]);
            if (count($pancekKppbcUsers) == 0) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Anda belum terdaftar pada KPPBC tertentu.");
            }
            foreach ($pancekKppbcUsers as $pancekKppbcUser) {
                $kppbcIds[] = $pancekKppbcUser->kppbc_id;
            }
        }
        $instansiKPPBCConditions = (empty($kppbcIds) ? "" : " AND kppbc_id IN (".implode(',', $kppbcIds).")");

        $cte = "
            WITH instansi_verifikator AS (
                SELECT iv.user_id, iv.instansi_id
                FROM jaga.pancek_instansi_verifikator iv
                LEFT JOIN pancek_user_access ua ON ua.user_id = iv.user_id AND ua.deleted_at IS NULL
                WHERE iv.deleted_at IS NULL AND (ua.end_date IS NULL OR now() < ua.end_date) AND iv.user_id = :verifikator_id
            ),
            instansi_filtered AS (
                SELECT *
                FROM jaga.pancek_instansi
                WHERE ceklis_kepentingan = '0;1;0' AND deleted_at IS NULL AND id IN (SELECT instansi_id FROM instansi_verifikator) AND (nama ILIKE :keyword OR alamat ILIKE :keyword OR nomor ILIKE :keyword OR npwp ILIKE :keyword)".$instansiKPPBCConditions."
            ),
            latest_formulir AS (
                SELECT *,
                       ROW_NUMBER() OVER (PARTITION BY instansi_id ORDER BY created_at DESC) AS rn
                FROM jaga.pancek_formulir
                WHERE deleted_at IS NULL 
                  AND created_at > now() - INTERVAL '3 year'
            )
        ";

        $query = "
            SELECT i.id, i.nama, i.alamat, mkk.kota_kabupaten_name AS kab_kota, mp.provinsi_name AS provinsi, i.email, i.nib, i.nomor, i.npwp, COALESCE(i.sektor_usaha_nama, su.nama) AS sektor_usaha, i.telepon, f.id AS formulir_id
            FROM instansi_filtered i
            JOIN latest_formulir f ON f.instansi_id = i.id AND f.rn = 1
            LEFT JOIN jaga.master_provinsi mp ON mp.provinsi_code::INTEGER = i.provinsi_id
            LEFT JOIN jaga.master_kota_kabupaten mkk ON mkk.kota_kabupaten_code::INTEGER = i.kab_kota_id
            LEFT JOIN jaga.pancek_sektor_usaha su ON su.id = i.sektor_usaha_id
        ";

        $limit = " LIMIT :limit OFFSET :offset";
        $bind = [
            'verifikator_id' => $params['verifikator_id'],
            'keyword' => '%' . $params['keyword'] . '%'
        ];

        $result = $this->rawQuery(
            $cte . $query . $limit,
            array_merge($bind, ['limit' => $params['limit'], 'offset' => $params['offset']])
        );

        $queryCount = "
            SELECT count(i.id) as total
            FROM instansi_filtered i
            JOIN latest_formulir f ON f.instansi_id = i.id AND f.rn = 1
            LEFT JOIN jaga.master_provinsi mp ON mp.provinsi_code::INTEGER = i.provinsi_id
            LEFT JOIN jaga.master_kota_kabupaten mkk ON mkk.kota_kabupaten_code::INTEGER = i.kab_kota_id
            LEFT JOIN jaga.pancek_sektor_usaha su ON su.id = i.sektor_usaha_id
        ";
        $total = $this->rawQuery($cte . $queryCount, $bind)[0]['total'];

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

    public function listWithFormulir()
    {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.monitoring', 'pancek.verify', 'pancek.kppbc.verify', 'pancek.kppbc.assignor']);
        PancekUserAccess::checkEligibleForRoleObserver($this);

        $getParams = $this->request->get();
        $getParams['jenis_instansi_id'] = array_key_exists('jenis_instansi_id', $getParams) ? $getParams['jenis_instansi_id'] : null;
        $getParams['sektor_usaha_id'] = array_key_exists('sektor_usaha_id', $getParams) ? $getParams['sektor_usaha_id'] : null;
        $getParams['provinsi_id'] = array_key_exists('provinsi_id', $getParams) ? $getParams['provinsi_id'] : null;
        $getParams['kab_kota_id'] = array_key_exists('kab_kota_id', $getParams) ? $getParams['kab_kota_id'] : null;
        $getParams['status'] = array_key_exists('status', $getParams) ? $getParams['status'] : null;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $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';
        $getParams['ceklis_kepentingan'] = array_key_exists('ceklis_kepentingan', $getParams) ? $getParams['ceklis_kepentingan'] : null;

        $allowed_order = [
            'id',
            'jenis_instansi_id',
            'sektor_usaha_id',
            'provinsi_id',
            'kab_kota_id',
            'nama',
            'status',
            'created_at',
            'updated_at'
        ];
        $allowed_order_direction = ['asc', 'desc'];

        $params = $this->validate(
            $getParams,
            [
                'jenis_instansi_id' => ['validators' => ['digit']],
                'sektor_usaha_id' => ['validators' => ['digit']],
                'provinsi_id' => ['validators' => ['digit']],
                'kab_kota_id' => ['validators' => ['digit']],
                'keyword' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 100
                ]]],
                'status' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 10
                ]]],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'order_by' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order
                ]]],
                'order_direction' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order_direction
                ]]],
                'ceklis_kepentingan' => ['validators' => [
                    [
                        'name' => 'max_string',
                        'params' => 255
                    ]
                ]]
            ]
        );

        $conditions = "(pi.npwp ilike :keyword OR pi.nomor ilike :keyword OR pi.nama ilike :keyword OR pi.sektor_usaha_nama ilike :keyword) AND pi.deleted_at IS NULL";
        $bind = [
            'keyword' => '%' . $params['keyword'] . '%'
        ];
        if (!is_null($params['jenis_instansi_id'])) {
            $conditions .= " AND pi.jenis_instansi_id = :jenis_instansi_id";
            $bind['jenis_instansi_id'] = $params['jenis_instansi_id'];
        }
        if (!is_null($params['sektor_usaha_id'])) {
            $conditions .= " AND pi.sektor_usaha_id = :sektor_usaha_id";
            $bind['sektor_usaha_id'] = $params['sektor_usaha_id'];
        }
        if (!is_null($params['provinsi_id'])) {
            $conditions .= " AND pi.provinsi_id = :provinsi_id";
            $bind['provinsi_id'] = $params['provinsi_id'];
        }
        if (!is_null($params['kab_kota_id'])) {
            $conditions .= " AND pi.kab_kota_id = :kab_kota_id";
            $bind['kab_kota_id'] = $params['kab_kota_id'];
        }
        if (!is_null($params['status'])) {
            $conditions .= " AND pi.status LIKE :status";
            $bind['status'] = $params['status'];
        }
        if (!is_null($params['ceklis_kepentingan'])) {
            $ceklis = explode(';', $params['ceklis_kepentingan']);
            $filter = [];
            if ($ceklis[0] == '1') $filter[] = "ceklis_kepentingan LIKE '1;_;_'";
            if ($ceklis[1] == '1') $filter[] = "ceklis_kepentingan LIKE '_;1;_'";
            if ($ceklis[2] == '1') $filter[] = "ceklis_kepentingan LIKE '_;_;1'";
            $str = implode(' OR ', $filter);
            $conditions .= " AND ($str)";
        }

        $idInstansiBeaCukai = Instansi::getIdInstansiDitjenBeaCukai();
        $idinstansiDijtenPupr = Instansi::getIdInstansiDitjenPupr();
        $idInstansiUser = $this->shared->user->id_instansi;
        if($this->hasOneOfScopes(['pancek.monitoring', 'pancek.verify']) && $idInstansiBeaCukai == $idInstansiUser) {
            $conditions .= " AND pi.ceklis_kepentingan = '0;1;0'";
        }
        else if($this->hasOneOfScopes(['pancek.monitoring', 'pancek.verify']) && $idinstansiDijtenPupr == $idInstansiUser) {
            $conditions .= " AND pi.ceklis_kepentingan = '1;0;0'";
        }
        else if($this->hasScopes(['pancek.kppbc.assignor']) && $idInstansiBeaCukai != $idInstansiUser) {
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
        }
        else if($this->hasScopes(['pancek.kppbc.assignor']) && $idInstansiBeaCukai == $idInstansiUser) {
            $kppbcUsers = PancekKppbcUser::findAssignorOnlyBy($this->userID)->toArray();
            if(empty($kppbcUsers)) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User belum di-assign ke KPPBC');
            }
            $conditions .= " AND pi.kppbc_id IN (".implode(', ', array_values(array_column($kppbcUsers, 'kppbc_id'))).")";
        }
        else if($this->hasScopes(['pancek.kppbc.verify']) && $idInstansiBeaCukai != $idInstansiUser) {
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
        }
        else if($this->hasScopes(['pancek.kppbc.verify']) && $idInstansiBeaCukai == $idInstansiUser) {
            $kppbcUsers = PancekKppbcUser::findVerifikatorOnlyBy($this->userID)->toArray();
            if(empty($kppbcUsers)) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User belum di-assign ke KPPBC');
            }
            $conditions .= " AND pi.kppbc_id IN (".implode(', ', array_values(array_column($kppbcUsers, 'kppbc_id'))).")";
            $conditions .= " AND f.kppbc_um_id = :um_id";
            $bind['um_id'] = $this->userID;
        }

        $sorting = 'pi.'.$params['order_by'] . ' ' . $params['order_direction'];
        $q = "
            WITH FormulirCTE AS ( 
            	SELECT 
            		* 
            	FROM pancek_formulir WHERE now() < (created_at + INTERVAL '3 year') AND deleted_at IS NULL ORDER BY ID)
            
            SELECT
            	pi.*,
            	f.id AS formulir_id,
            	f.kppbc_um_id
            FROM
            	pancek_instansi pi LEFT JOIN FormulirCTE f ON pi.id = f.instansi_id
            	WHERE $conditions
            ORDER BY $sorting;
        ";
        $pancekInstansis = $this->rawQuery($q, $bind);

        $records = [];
        foreach ($pancekInstansis as $pancekInstansi) {
            $model = (new PancekInstansi())->assign($pancekInstansi);
            $record = $model->getDetail();
            $formulir = PancekFormulir::findActiveByInstansiId($pancekInstansi['id']);
            if ($formulir) {
                $userVerifikatorKppbc = null;
                if($formulir->kppbc_um_id != null) {
                    $userVerifikatorKppbc = $formulir->kppbc_verifikator->toArraySimple();
                }
                $simpleFormulir = $formulir->stat(['id', 'periode', 'jumlah_revisi', 'status', 'kppbc_um_id']);
                $record['formulir'] = $simpleFormulir;
                $record['formulir']['verifikator_kppbc'] = $userVerifikatorKppbc;
            }
            array_push($records, $record);
        }

        $qCount = "
            WITH FormulirCTE AS ( 
            	SELECT 
            		* 
            	FROM pancek_formulir WHERE now() < (created_at + INTERVAL '3 year') AND deleted_at IS NULL ORDER BY ID)
            
            SELECT
            	COUNT(*) AS total
            FROM
            	pancek_instansi pi LEFT JOIN FormulirCTE f ON pi.id = f.instansi_id
            	WHERE $conditions
            ;
        ";
        $total = 0;
        $resultTotal = $this->rawQuery($qCount, $bind);
        if(!empty($resultTotal)) {
            $total = $resultTotal[0]['total'];
        }

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

    public function listBkWithFormulir()
    {
        $this->checkOneOfScopes(['pancek.bk.verify']);
        return $this->baseListWithAccessFormulir();
    }

    public function listBcWithFormulir()
    {
        $this->checkOneOfScopes(['pancek.kppbc.verify']);
        return $this->baseListWithAccessFormulir();
    }

    public function detail($id)
    {
        $instansi = $this->findInstansiWithValidatedScope($id);
        $result = $instansi->getDetail(null, false);
        $result['foto_url'] = MediaHelper::getUrl($result["foto"], $this->config, $this->minio);

        return new SimpleResponse($result);
    }

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

        if(!$this->hasScopes(['admin.view'])) {
            $pancekUser = PancekUser::findFirst([
                'conditions' => 'um_id = :um_id: AND instansi_id = :instansi_id:',
                'bind' => [
                    'um_id' => $this->userID,
                    'instansi_id' => $id
                ]
            ]);

            if (!$pancekUser) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, "User tidak berhak mengakses");
            }
        }

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

        if(!$instansi) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data Badan Usaha tidak ditemukan");
        }

        $rawBody = $this->request->getPost();
        $rules = [
            'jenis_instansi_id' => ['validators' => ['required', 'digit']],
            'sektor_usaha_id' => ['validators' => ['required', 'digit']],
            'nama' => ['validators' => [
                'required',
                [
                    'name' => 'min_string',
                    'params' => 5
                ],
                [
                    'name' => 'max_string',
                    'params' => 255
                ]
            ]],
            'nomor' => ['validators' => [
                [
                    'name' => 'max_string',
                    'params' => 100
                ]
            ]],
            'provinsi_id' => ['validators' => ['required', 'digit']],
            'kab_kota_id' => ['validators' => ['required', 'digit']],
            'alamat' => ['validators' => [
                [
                    'name' => 'max_string',
                    'params' => 255
                ]
            ]],
            'telepon' => ['validators' => [
                'required',
                [
                    'name' => 'max_string',
                    'params' => 15
                ]
            ]],
            'nib' => ['validators' => []]
        ];
        if (array_key_exists('sektor_usaha_id', $rawBody)) {
            if ($rawBody['sektor_usaha_id'] == PancekSektorUsaha::SEKTOR_USAHA_LAINNYA) {
                $rules['sektor_usaha_nama'] = ['validators' => [
                    'required',
                    [
                        'name' => 'min_string',
                        'params' => 6
                    ],
                    [
                        'name' => 'max_string',
                        'params' => 255
                    ]
                ]];
            } else {
                $rules['sektor_usaha_nama'] = ['validators' => [
                    [
                        'name' => 'max_string',
                        'params' => 255
                    ]
                ]];
                $rawBody['sektor_usaha_nama'] = null;
            }
        }
        $post = $this->validate($rawBody, $rules);

        // upload pictures
        if ($this->request->hasFiles()) {
            $random = new \Phalcon\Security\Random();
            foreach ($this->request->getUploadedFiles() as $file) {
                // upload profile picture
                if ($file->getKey() === "instansi_picture") {
                    if (substr($file->getRealType(), 0, 5) !== "image") {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Update User Gagal', [[
                            "field" => "image",
                            "filename" => $file->getName(),
                            "message" => "Tipe file tidak diizinkan"
                        ]]);
                    }

                    $shortName = $random->uuid() . "." . pathinfo($file->getName(), PATHINFO_EXTENSION);

                    if (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::LOCAL) {
                        $filename = getenv("USER_IMAGE_FOLDER") . DIRECTORY_SEPARATOR;
                        if (!file_exists($filename) && !is_dir($filename)) {
                            mkdir($filename, 0770, true);
                        }

                        $oldProPic = $filename . $instansi->foto;
                        if (file_exists($oldProPic) && is_file($oldProPic)) {
                            unlink($oldProPic);
                        }

                        $filename .= DIRECTORY_SEPARATOR . $shortName;
                        $file->moveTo($filename);
                        $instansi->foto = $shortName;

                    } elseif (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::MINIO) {
                        /** @var MinioService $minioService */
                        $minioService = $this->minio;
                        if ($minioService->isSecondaryS3Available()) {
                            $minioService->baseUploadUsingSecondaryS3(
                                $file->getTempName(),
                                $file->getRealType(),
                                false,
                                'users_profpic' . DIRECTORY_SEPARATOR . $shortName
                            );
                        } else {
                            $minioService->base_upload_attachment(
                                $file->getTempName(),
                                $file->getRealType(),
                                false,
                                'users_profpic' . DIRECTORY_SEPARATOR . $shortName
                            );
                        }
                        $instansi->foto = $shortName;
                    }
                }
            }
        }

        $instansi->assign($post);
        $instansi->updated_at = DateHelper::getNowTimezoned();
        if (!$instansi->save())
        {
            $errors = $instansi->getMessages();
            $error_messages = [];

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

            throw new ValidationException($error_messages);
        }

        return new SimpleResponse($instansi);
    }

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

        try {
            $this->db->begin();

            $this->doVerifyInstansi($id);

            $this->db->commit();

            return new ObjectResponse(['success' => true]);
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function massVerify()
    {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.verify']);
        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate($rawBody, [
            'instansi_ids' => ['required']
        ]);
        $instansiIds = $params['instansi_ids'];
        if (!is_array($instansiIds)) throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Format payload tidak valid");
        try {
            $this->db->begin();
            foreach ($instansiIds as $instansiId) {
                if (!is_numeric($instansiId)) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Format payload tidak valid");
                }
                $this->doVerifyInstansi($instansiId);
            }
            $this->db->commit();
            return new ObjectResponse(['success' => true]);
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function updateAdmin($id) {
        $this->hasOneOfScopes(['admin.view', 'pancek.admin']);

        $rawBody = $this->request->getPost();
        $rules = [
            'jenis_instansi_id' => ['validators' => ['required', 'digit']],
            'sektor_usaha_id' => ['validators' => ['required', 'digit']],
            'nama' => ['validators' => [
                'required',
                [
                    'name' => 'min_string',
                    'params' => 5
                ],
                [
                    'name' => 'max_string',
                    'params' => 255
                ]
            ]],
            'nomor' => ['validators' => [
                [
                    'name' => 'max_string',
                    'params' => 100
                ]
            ]],
            'npwp' => ['validators' => [
                'required',
                'numeric',
                [
                    'name' => 'min_string',
                    'params' => 6
                ]
            ]],
            'email' => ['validators' => ['required']],
            'provinsi_id' => ['validators' => ['required', 'digit']],
            'kab_kota_id' => ['validators' => ['required', 'digit']],
            'alamat' => ['validators' => [
                [
                    'name' => 'max_string',
                    'params' => 255
                ]
            ]],
            'telepon' => ['validators' => [
                'required',
                [
                    'name' => 'max_string',
                    'params' => 15
                ]
            ]],
            'ceklis_kepentingan' => ['validators' => [
                [
                    'name' => 'max_string',
                    'params' => 255
                ]
            ]],
            'nib' => ['validators' => []],
            'kppbc_id' => ['validators' => []],

        ];
        if (array_key_exists('sektor_usaha_id', $rawBody)) {
            if ($rawBody['sektor_usaha_id'] == PancekSektorUsaha::SEKTOR_USAHA_LAINNYA) {
                $rules['sektor_usaha_nama'] = ['validators' => [
                    'required',
                    [
                        'name' => 'min_string',
                        'params' => 6
                    ],
                    [
                        'name' => 'max_string',
                        'params' => 255
                    ]
                ]];
            } else {
                $rules['sektor_usaha_nama'] = ['validators' => [
                    [
                        'name' => 'max_string',
                        'params' => 255
                    ]
                ]];
                $rawBody['sektor_usaha_nama'] = null;
            }
        }
        $post = $this->validate($rawBody, $rules);

        /** @var $instansi PancekInstansi */
        $instansi = PancekInstansi::findFirst([
            'conditions' => 'id = :id:',
            'bind' => ['id' => $id]
        ]);

        if (!$instansi) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan");
        }

        $this->validateEmailAvailability($post['email'], $instansi->email);

        if($post['ceklis_kepentingan'] == '0;1;0') {
            if($post['kppbc_id'] == null) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "KPPBC harus dipilih.");
            }
            else {
                $pancekKppbcExist = PancekKppbc::findFirst($post['kppbc_id']);
                if(!$pancekKppbcExist) throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "KPPBC tidak ditemukan.");
            }
        }

        // upload pictures
        if ($this->request->hasFiles()) {
            $random = new \Phalcon\Security\Random();
            foreach ($this->request->getUploadedFiles() as $file) {
                // upload profile picture
                if ($file->getKey() === "instansi_picture") {
                    if (substr($file->getRealType(), 0, 5) !== "image") {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Update User Gagal', [[
                            "field" => "image",
                            "filename" => $file->getName(),
                            "message" => "Tipe file tidak diizinkan"
                        ]]);
                    }

                    $shortName = $random->uuid() . "." . pathinfo($file->getName(), PATHINFO_EXTENSION);

                    if (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::LOCAL) {
                        $filename = getenv("USER_IMAGE_FOLDER") . DIRECTORY_SEPARATOR;
                        if (!file_exists($filename) && !is_dir($filename)) {
                            mkdir($filename, 0770, true);
                        }

                        $oldProPic = $filename . $instansi->foto;
                        if (file_exists($oldProPic) && is_file($oldProPic)) {
                            unlink($oldProPic);
                        }

                        $filename .= DIRECTORY_SEPARATOR . $shortName;
                        $file->moveTo($filename);
                        $instansi->foto = $shortName;

                    } elseif (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::MINIO) {
                        /** @var MinioService $minioService */
                        $minioService = $this->minio;
                        if ($minioService->isSecondaryS3Available()) {
                            $minioService->baseUploadUsingSecondaryS3(
                                $file->getTempName(),
                                $file->getRealType(),
                                false,
                                'users_profpic' . DIRECTORY_SEPARATOR . $shortName
                            );
                        } else {
                            $minioService->base_upload_attachment(
                                $file->getTempName(),
                                $file->getRealType(),
                                false,
                                'users_profpic' . DIRECTORY_SEPARATOR . $shortName
                            );
                        }
                        $instansi->foto = $shortName;
                    }
                }
            }
        }

        $instansi->assign($post);
        $instansi->updated_at = DateHelper::getNowTimezoned();
        if (!$instansi->save())
        {
            $errors = $instansi->getMessages();
            $error_messages = [];

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

            throw new ValidationException($error_messages);
        }

        return new SimpleResponse($instansi);
    }

    public function generateToken()
    {
        $params = $this->validate(
            $this->request->getJsonRawBody(true),
            [
                "npwp" => ['validators' => ['required']],
                "email" => ['validators' => ['required']],
                "captcha_uuid" => ['validators' => ['required']],
                "captcha_answer" => ['validators' => ['required']]
            ]
        );

        /** @var $captcha Captcha */
        $captcha = Captcha::findFirst([
            'conditions' => 'uuid = :uuid:',
            'bind' => [
                'uuid' => $params['captcha_uuid']
            ]
        ]);

        if (!$captcha) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Captcha not found");
        }

        if ($captcha->isUsed()) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Captcha already used");
        }

        if ($captcha->isExpired()) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Captcha expired");
        }

        if (!$captcha->match($params['captcha_answer'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Captcha doesn't match");
        }

        $captcha->markUsed();

        /** @var $instansi PancekInstansi */
        $instansi = PancekInstansi::findFirst([
            'conditions' => 'npwp = :npwp: AND lower(email) = :email:',
            'bind' => [
                'npwp' => $params['npwp'],
                'email' => strtolower($params['email'])
            ]
        ]);
        if (!$instansi) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak terdaftar");
        }
        if ($instansi->status != PancekInstansi::STATUS_VERIFIED) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Status Instansi belum terverifikasi");
        }

        PancekInstansiToken::clearByInstansiId($instansi->id);
        $token = PancekInstansiToken::generateForInstansiId($instansi->id);
        $this->notifyInstansi($token, $instansi);

        return new ObjectResponse(['success' => true]);
    }

    public function viewToken()
    {
        $this->checkOneOfScopes(['admin.view', 'pancek.admin']);
        $getParams = $this->request->get();
        $params = $this->validate($getParams, [
            'email' => ['validators' => ['required']]
        ]);

        /** @var $instansi PancekInstansi */
        $instansi = PancekInstansi::findFirst(['conditions' => 'email = :email:', 'bind' => ['email' => $params['email']]]);
        if (!$instansi) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan");
        }

        $token = PancekInstansiToken::find(['conditions' => 'instansi_id = :id:', 'bind' => ['id' => $instansi->id]]);
        if (empty($token)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Token tidak ditemukan");
        }

        return new CollectionPaginatedResponse($token);
    }

    public function registerUser()
    {
        $request = $this->request->getPost();

        $allowed_gender = ['L','P','T'];

        $rules = [
            'token' => ['validators' => ['required']],
            'username' => ['validators' => [
                'required',
                'alpha_dash',
                [
                    'name' => 'min_string',
                    'params' => 6
                ]
            ]],
            'email' => ['validators' => ['required', 'email']],
            'nama' => ['validators' => ['required']],
            'phone' => ['validators' => ['required', 'digit']],
            'jenis_kelamin' => ['validators' =>  [
                [
                    'name' => 'inclusion_in',
                    'params' => $allowed_gender
                ]
            ]],
            'jabatan' => ['validators' => [
                'required',
                [
                    'name' => 'max_string',
                    'params' => 255
                ],
                [
                    'name' => 'min_string',
                    'params' => 3
                ]
            ]],
            'password' => ['validators' => [
                'required',
                'confirm_password',
                [
                    'name' => 'min_string',
                    'params' => 8
                ]
            ]],
            'confirm_password' => ['validators' => ['required']]
        ];

        $params = $this->filterExpectedRequest($request, $rules);

        \UserHelper::validateUsernameAvailability($params['username']);
        \UserHelper::validateEmailAvailability($params['email']);
        \UserHelper::validatePhoneAvailability($params['phone']);

        /**
         * format token yang dikirim melalui email: token|uuid
         */
        $token = PancekInstansiToken::findByTokenEmail($params['token']);

        /**
         * instansi yang dapat melakukan register ulang user hanya instansi yang telah ter-verifikasi
         * @var $instansiPancek PancekInstansi
         */
        $instansiPancek = PancekInstansi::findFirst([
            'conditions' => 'id = :id: AND status = :status:',
            'bind' => [
                'id' => $token->instansi_id,
                'status' => PancekInstansi::STATUS_VERIFIED
            ]
        ]);

        if (!$instansiPancek) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan");
        }
        try {
            $this->db->begin();

            // upload pictures
            $userFoto = null;
            if ($this->request->hasFiles()) {
                $random = new \Phalcon\Security\Random();
                foreach ($this->request->getUploadedFiles() as $file) {
                    // upload profile picture
                    if ($file->getKey() === "profile_picture") {
                        if (substr($file->getRealType(), 0, 5) !== "image") {
                            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Update User Gagal', [[
                                "field" => "image",
                                "filename" => $file->getName(),
                                "message" => "Tipe file tidak diizinkan"
                            ]]);
                        }

                        $shortName = $random->uuid() . "." . pathinfo($file->getName(), PATHINFO_EXTENSION);

                        if (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::LOCAL) {
                            $filename = getenv("USER_IMAGE_FOLDER") . DIRECTORY_SEPARATOR;
                            if (!file_exists($filename) && !is_dir($filename)) {
                                mkdir($filename, 0770, true);
                            }

                            $filename .= DIRECTORY_SEPARATOR . $shortName;
                            $file->moveTo($filename);
                            $userFoto = $shortName;

                        } elseif (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::MINIO) {
                            /** @var MinioService $minioService */
                            $minioService = $this->minio;
                            if ($minioService->isSecondaryS3Available()) {
                                $minioService->baseUploadUsingSecondaryS3(
                                    $file->getTempName(),
                                    $file->getRealType(),
                                    false,
                                    'users_profpic' . DIRECTORY_SEPARATOR . $shortName
                                );
                            } else {
                                $minioService->base_upload_attachment(
                                    $file->getTempName(),
                                    $file->getRealType(),
                                    false,
                                    'users_profpic' . DIRECTORY_SEPARATOR . $shortName
                                );
                            }
                            $userFoto = $shortName;
                        }
                    }
                }
            }

            // reset role user pancek sebelumnya
            $idInstansi = null;
            $prevUser = $instansiPancek->user->umUser;
            if ($prevUser) {
                $idInstansi = $prevUser->id_instansi;
                $prevUser->checkmark_verified = 0;
                $prevUser->id_instansi = null;
                if(!$prevUser->save()) {
                    throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Akun pengguna gagal disimpan(1)");
                }
                $prevUser->syncRoles(Roles::getUserRoleIds(['pengguna']));
            }

            // save user
            $user = new UmUser();
            $user->assign($params);
            $user->um_user_name = $params['username'];
            $user->um_tenant_id = -1234;
            $user->um_changed_time = date("Y-m-d H:i:s");
            $user->checkmark_verified = 1;
            $user->foto = $userFoto;
            $user->id_instansi = $idInstansi;

            if(array_key_exists('password', $params)) {
                $hashed_password = base64_encode(hash("sha256", $params['password'], true));
                $user->um_user_new_password = $this->security->hash($params['password']);
                $user->um_user_password = $hashed_password;
            }

            if(!$user->save()) {
                throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Akun pengguna gagal disimpan(2)");
            }

            // save user roles
            $user->syncRoles(Roles::getUserRoleIds(['pengguna', 'user_pancek']));

            // update user pancek
            $userPancek = $instansiPancek->user;
            $userPancek->um_id = $user->um_id;
            $userPancek->jabatan = $params['jabatan'];
            if (!$userPancek->save()) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Akun pengguna gagal disimpan(3)");
            }

            // tandai token instansi telah digunakan
            $token->markUsed();

            $this->db->commit();
            return new ObjectResponse(["success" => true]);
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function transaksiBakum()
    {
        $params = $this->validate(
            $this->request->getJsonRawBody(true),
            [
                "nama" => ['validators' => [
                    'required',
                    [
                        'name' => 'max_string',
                        'params' => 100
                    ]
                ]],
                "captcha_uuid" => ['validators' => ['required']],
                "captcha_answer" => ['validators' => ['required']]
            ]
        );

        /** @var $captcha Captcha */
        $captcha = Captcha::findFirst([
            'conditions' => 'uuid = :uuid:',
            'bind' => [
                'uuid' => $params['captcha_uuid']
            ]
        ]);

        if (!$captcha) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Captcha not found");
        }

        if ($captcha->isUsed()) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Captcha already used");
        }

        if ($captcha->isExpired()) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Captcha expired");
        }

        if (!$captcha->match($params['captcha_answer'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Captcha doesn't match");
        }

        $captcha->markUsed();

        $ahuClient = new AhuClient($this->config["ahu_prop"]);
        $result = $ahuClient->getTransaksiBakum($params['nama']);

        return new SimpleResponse($result);
    }

    public function generateExcel()
    {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.monitoring', 'pancek.verify', 'pancek.kppbc.verify', 'pancek.bk.verify']);
        PancekUserAccess::checkEligibleForRoleObserver($this);
        $ksps = PancekKonsepSistemPencegahan::find(['conditions' => 'deleted_at IS NULL', 'order' => 'id']);
        $totalKsp = count($ksps) / count(array_unique(array_column($ksps->toArray(), "formulir_type")));
        $queryScore = "";
        foreach ($ksps as $ksp) {
            $queryScore .= "SUM(j.bobot) FILTER (WHERE j.ksp_id = ".$ksp->id.")  AS score_".$ksp->id.",";
        }

        $conditions = "WHERE i.deleted_at IS NULL";
        $bind = [];
        $leftJoin = "";
        $idInstansiBeaCukai = Instansi::getIdInstansiDitjenBeaCukai();
        $idInstansiDitjenPupr = Instansi::getIdInstansiDitjenPupr();
        $idInstansiUser = $this->shared->user->id_instansi;
        if($this->hasOneOfScopes(['pancek.monitoring', 'pancek.verify']) && $idInstansiBeaCukai == $idInstansiUser) {
            $conditions .= " AND i.ceklis_kepentingan = '0;1;0'";
        }
        else if($this->hasOneOfScopes(['pancek.monitoring', 'pancek.verify']) && $idInstansiDitjenPupr == $idInstansiUser) {
            $conditions .= " AND i.ceklis_kepentingan = '1;0;0'";
        }
        else if($this->hasScopes(['pancek.kppbc.verify']) && $idInstansiBeaCukai != $idInstansiUser) {
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User is not allowed to perform this action');
        }
        else if($this->hasScopes(['pancek.kppbc.verify']) && $idInstansiBeaCukai == $idInstansiUser) {
            $kppbcUsers = PancekKppbcUser::findVerifikatorOnlyBy($this->userID)->toArray();
            if(empty($kppbcUsers)) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User belum di-assign ke KPPBC');
            }
            $conditions .= " AND i.kppbc_id IN (:kppbc_ids)";
            $bind['kppbc_ids'] = implode(', ', array_column($kppbcUsers, 'kppbc_id'));
        }
        else if($this->hasScopes(['pancek.bk.verify'])) {
            $leftJoin = " LEFT JOIN pancek_instansi_verifikator vi ON vi.instansi_id = i.id AND vi.deleted_at IS NULL";
            $conditions .= " AND vi.user_id = :user_id";
            $bind['user_id'] = $this->userID;
        }

        $query = "
            WITH f3 AS (
              SELECT f.*
              FROM pancek_formulir f
              WHERE f.created_at >= now() - INTERVAL '".getenv("PANCEK_FORMULIR_VALIDITY_PERIOD")." years' AND f.deleted_at IS NULL
            ),
            answers_ok AS (
              SELECT a.id, a.formulir_id, a.komponen_indikator_id
              FROM pancek_answer a
              WHERE (a.verify_status IS NULL OR a.verify_status IN ('UPDATED','ACCEPTED'))
                AND EXISTS (
                  SELECT 1 FROM pancek_answer_attachment x
                  WHERE x.answer_id = a.id
                )
            ),
            ksp_denoms AS (
              SELECT formulir_type, COUNT(*)::numeric AS denom
              FROM pancek_konsep_sistem_pencegahan
              GROUP BY formulir_type
            ),
            formulir_type_cte AS (
              SELECT  f.id AS formulir_id, COALESCE(MIN(ksp.formulir_type), ji.formulir_type) AS formulir_type
              FROM f3 f
              LEFT JOIN pancek_instansi i ON i.id = f.instansi_id
              LEFT JOIN pancek_jenis_instansi ji ON ji.id = i.jenis_instansi_id
              LEFT JOIN pancek_answer ax ON ax.formulir_id = f.id
              LEFT JOIN pancek_komponen_indikator ki2 ON ki2.id = ax.komponen_indikator_id
              LEFT JOIN pancek_indikator ii2 ON ii2.id = ki2.indikator_id
              LEFT JOIN pancek_konsep_sistem_pencegahan ksp ON ksp.id = ii2.konsep_sistem_pencegahan_id
              WHERE i.deleted_at IS NULL AND ax.deleted_at IS NULL
              GROUP BY f.id, ji.formulir_type
            ),
            joined AS (
              SELECT i.id AS instansi_id, i.nama AS instansi, i.npwp, i.nomor, i.status AS instansi_status, f.id AS formulir_id, f.periode, f.jumlah_revisi, f.status AS formulir_status, mp.provinsi_name AS provinsi, mkk.kota_kabupaten_name AS kota_kabupaten, ji.nama AS jenis_instansi, su.nama AS sektor_usaha, ft.formulir_type AS f_formulir_type, ksp.id AS ksp_id, ksp.formulir_type AS ksp_formulir_type, ki.bobot AS bobot
              FROM pancek_instansi i
              LEFT JOIN f3 f ON f.instansi_id = i.id
              LEFT JOIN master_provinsi mp ON mp.provinsi_code::int = i.provinsi_id
              LEFT JOIN master_kota_kabupaten mkk ON mkk.kota_kabupaten_code::int = i.kab_kota_id
              LEFT JOIN pancek_jenis_instansi ji ON ji.id = i.jenis_instansi_id
              LEFT JOIN pancek_sektor_usaha su ON su.id = i.sektor_usaha_id
              LEFT JOIN answers_ok a ON a.formulir_id = f.id
              LEFT JOIN pancek_komponen_indikator ki ON ki.id = a.komponen_indikator_id
              LEFT JOIN pancek_indikator ii ON ii.id = ki.indikator_id
              LEFT JOIN pancek_konsep_sistem_pencegahan ksp ON ksp.id = ii.konsep_sistem_pencegahan_id
              LEFT JOIN formulir_type_cte ft ON ft.formulir_id = f.id
              $leftJoin
              $conditions
            )
            SELECT j.instansi_id, j.instansi, j.npwp, j.nomor, j.instansi_status, j.formulir_id, j.periode, j.jumlah_revisi, j.formulir_status, j.provinsi, j.kota_kabupaten, j.jenis_instansi, j.sektor_usaha, 
            $queryScore
            COALESCE(SUM(j.bobot) FILTER (WHERE j.ksp_formulir_type = j.f_formulir_type), 0) / NULLIF(d.denom, 0) AS score_total
            FROM joined j LEFT JOIN ksp_denoms d ON d.formulir_type = j.f_formulir_type
            GROUP BY j.instansi_id, j.instansi, j.npwp, j.nomor, j.instansi_status, j.formulir_id, j.periode, j.jumlah_revisi, j.formulir_status, j.provinsi, j.kota_kabupaten, j.jenis_instansi, j.sektor_usaha, j.f_formulir_type, d.denom
            ORDER BY j.instansi_id;
        ";

        $rows = $this->rawQuery($query, $bind);

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue("A1", 'REKAPITULASI PANDUAN CEK BADAN USAHA');
        $sheet->setCellValue("A2", "per " . DateHelper::formatToday());
        $sheet->getStyle("A1:A2")->getFont()->setBold(true);

        $col = 'A';
        $sheet->setCellValue($col."4", 'No');
        $sheet->setCellValue(++$col."4", 'Instansi');
        $sheet->setCellValue(++$col."4", 'NPWP');
        $sheet->setCellValue(++$col."4", 'Nomor');
        $sheet->setCellValue(++$col."4", 'Provinsi');
        $sheet->setCellValue(++$col."4", 'Jenis Badan Usaha');
        $sheet->setCellValue(++$col."4", 'Sektor Usaha');
        foreach ($ksps as $ksp) {
            $sheet->setCellValue(++$col."4", strtoupper($ksp->label));
        }
        $sheet->setCellValue(++$col."4", 'Total Skor');
        $sheet->getStyle("A4:".$col."4")->getFont()->setBold(true);
        $sheet->getStyle("A1:".$col."2")
            ->getAlignment()
            ->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->mergeCells('A1:'.$col.'1');
        $sheet->mergeCells('A2:'.$col.'2');

        $row = 5;
        foreach ($rows as $index => $item) {
            $col = "A";
            $sheet->setCellValue("A$row", ($index+1));
            $sheet->setCellValue(++$col."$row", $item['instansi']);
            $sheet->setCellValue(++$col."$row", $item['npwp']);
            $sheet->setCellValue(++$col."$row", $item['nomor']);
            $sheet->setCellValue(++$col."$row", $item['provinsi']);
            $sheet->setCellValue(++$col."$row", $item['jenis_instansi']);
            $sheet->setCellValue(++$col."$row", $item['sektor_usaha']);
            foreach ($ksps as $ksp) {
                $sheet->setCellValue(++$col."$row", $item["score_".$ksp->id]);
            }
            $sheet->setCellValue(++$col."$row", floatval(number_format($item['score_total'], 2)));
            $row++;
        }
        foreach (range('A', $col) as $v) {
            $sheet->getColumnDimension($v)->setAutoSize(true);
        }
        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $sheet->getStyle('A4:'. $col . ($row - 1))->applyFromArray($styleArray);

        $writer = new Xlsx($spreadsheet);
        $filename = "Rekapitulasi Panduan Cek - " . DateHelper::formatToday() . ".xlsx";
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        return $writer->save('php://output');
    }
    #endregion

    #region private
    private function doVerifyInstansi($id) {
        /** @var $instansi PancekInstansi */
        $instansi = PancekInstansi::findFirst(['conditions' => 'id = :id:', 'bind' => ['id' => $id]]);
        if (!$instansi) throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan");
        if ($instansi->status == PancekInstansi::STATUS_VERIFIED) throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Instansi telah diverifikasi sebelumnya");
        if (!is_null($instansi->deleted_at)) throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Instansi telah dihapus");

        /** @var $userPancek PancekUser */
        $userPancek = PancekUser::findFirst(['conditions' => 'um_id = :id:', 'bind' => ['id' => $instansi->user->um_id]]);
        if (!$userPancek) throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "User tidak ditemukan");

        $instansi->status = PancekInstansi::STATUS_VERIFIED;
        $instansi->verified_at = date("Y-m-d H:i:s");
        $instansi->verified_by = $this->shared->user->um_id;
        if (!$instansi->save()) throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Gagal menyimpan data instansi");

        // simpan data instansi ke table instansi
         $jagaInstansi = $this->saveIntoInstansi($instansi);

        // begin save user roles
        $user = $userPancek->umUser;
        $user->checkmark_verified = 1;
        $user->id_instansi = $jagaInstansi->id;
        if (!$user->save()) {
           throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Gagal menyimpan data user");
        }
        $roles = ['pengguna', 'user_pancek'];
        $users_roles = Roles::find([
            'conditions' => 'name in ({roles:array})',
            'columns' => 'id',
            'bind' => [
                'roles' => $roles
            ]
        ])->toArray();

        $user_roles_ids = [];
        foreach ($users_roles as $key => $role) {
            array_push($user_roles_ids, $role['id']);
        }

        $user->syncRoles($user_roles_ids);
        // end save user roles

        // notifikasi ke user instansi bahwa pendaftaran instansi telah diverifikasi
        $this->notifyUser($user, $instansi);
        // end notifikasi

        return $instansi;
    }

    /**
     * @param $id
     * @return PancekInstansi
     * @throws CustomErrorException
     */
    private function findInstansiWithValidatedScope($id): PancekInstansi
    {
        $this->checkIsAuthenticated();
        PancekUserAccess::checkEligibleForRoleObserver($this);

        /** @var $instansi PancekInstansi */
        $instansi = PancekInstansi::findFirst(['conditions' => 'id = :id:', 'bind' => ['id' => $id]]);
        if (!$instansi)
        {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Instansi tidak ditemukan");
        }
        if (!$this->hasOneOfScopes(['pancek.admin', 'pancek.monitoring'])) {
            if ($instansi->user && $instansi->user->um_id !== $this->shared->user->um_id)
            {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User tidak berhak mengakses");
            }
        }

        return $instansi;
    }

    private function notifyInstansi(PancekInstansiToken $token, PancekInstansi $instansi)
    {
        $subject = "[JAGA] Permintaan Perubahan Data Akun JAGA";
        $message = "Hi Sahabat JAGA, terima kasih telah menggunakan Aplikasi JAGA."
            . "\n\nKamu menerima email ini karena adanya permintaan perubahan data pada akun JAGA kamu. Berikut informasi perubahan data pada Akun kamu :"
            . "\n\nNama Instansi: " . $instansi->nama
            . "\nNPWP: " . $instansi->npwp
            . "\nTelepon: " . $instansi->telepon
            . "\n\nBerikut token yang dapat digunakan untuk melakukan perubahan data akun pada Aplikasi JAGA:"
            . "\n\n" . $token->token . "|" . $token->uuid
            . "\n\nJika ada ketidaksesuaian pada data di atas, kamu dapat mengubah data kamu pada halaman dashboard."
            . "\nJangan khawatir, semua data kamu aman di JAGA."
            . "\nAkses JAGA dan bantu kami untuk Cegah Korupsi dari Ujung Jari."
            . "\n\nPerhatian: E-mail ini (termasuk seluruh lampirannya, bila ada) dikirim otomatis oleh aplikasi dan hanya ditujukan kepada penerima yang tercantum di atas. Jika Anda bukan penerima yang dituju, maka Anda tidak diperkenankan untuk memanfaatkan, menyebarkan, mendistribusikan, atau menggandakan e-mail ini beserta seluruh lampirannya. Mohon untuk tidak membalas email ini."
        ;

        $mail = new MailHelper($this->config["mail_config"]);
        $mail->queue($instansi->email, $subject, $message, false, $this->shared->user->um_id);
    }

    private function validateEmailAvailability($email, $ignore = null)
    {
        if (!empty($ignore) && ($email == $ignore)) {
            return $email;
        }

        if ($this->notAvailableEmail($email)) {
            throw new \CustomErrorException(
                \BaseResponse::INVALID_REQUEST,
                "Email has been used"
            );
        }

        return $email;
    }

    private function notifyUser(UmUser $user, PancekInstansi $instansi)
    {
        $subject = "[JAGA] Instansi Telah Diverifikasi";
        $message = "Hi Sahabat JAGA, terima kasih telah menggunakan Aplikasi JAGA."
            . "\n\nPermintaan pendaftaran Instansi kamu telah diverifikasi. Berikut informasi data registrasi kamu :"
            . "\n\nNama Instansi: " . $instansi->nama
            . "\nNPWP: " . $instansi->npwp
            . "\nTelepon: " . $instansi->telepon
            . "\n\nBerikut akses yang dapat digunakan untuk login ke Aplikasi JAGA:"
            . "\n\nUsername: " . $user->um_user_name
            . "\n\natau klik link berikut " . $this->config["web_base_url"]
            . "\n\nUntuk memulai mengisi formulir dapat dilakukan melalui link berikut ." . $this->config["web_base_url"] . "/pancek/form"
            . "\n\nJika ada ketidaksesuaian pada data di atas, kamu dapat mengubah data kamu pada halaman dashboard."
            . "\nJangan khawatir, semua data kamu aman di JAGA."
            . "\nAkses JAGA dan bantu kami untuk Cegah Korupsi dari Ujung Jari."
            . "\n\nPerhatian: E-mail ini (termasuk seluruh lampirannya, bila ada) dikirim otomatis oleh aplikasi dan hanya ditujukan kepada penerima yang tercantum di atas. Jika Anda bukan penerima yang dituju, maka Anda tidak diperkenankan untuk memanfaatkan, menyebarkan, mendistribusikan, atau menggandakan e-mail ini beserta seluruh lampirannya. Mohon untuk tidak membalas email ini."
        ;

        $mail = new MailHelper($this->config["mail_config"]);
        $mail->queue($user->email, $subject, $message, false, $this->shared->user->um_id);
        $mail->queue($instansi->email, $subject, $message, false, $this->shared->user->um_id);
    }

    private function notAvailableEmail($email)
    {
        $result = PancekInstansi::query()
            ->where('lower(email) = :email:')
            ->bind(['email' => strtolower($email)])
            ->limit(1)
            ->execute();

        return $result->count() > 0;
    }

    private function saveIntoInstansi(PancekInstansi $instansi): Instansi
    {
        $instansiTipe = InstansiTipe::findFirst([
            'conditions' => 'lower(nama) LIKE :nama:',
            'bind' => [
                'nama' => strtolower($instansi->jenisInstansi->nama)
            ]
        ]);
        if (!$instansiTipe) {
            $instansiTipe = new InstansiTipe();
            $instansiTipe->nama = $instansi->jenisInstansi->nama;
            $instansiTipe->stranas_filter = false;
            $instansiTipe->created_by = $this->shared->user->um_id;
            try {
                $instansiTipe->save();
            } catch (PDOException $e) {
                $this->db->rollback();
                $this->db->query("SELECT setval('jaga.instansi_tipe_id_seq', COALESCE((SELECT MAX(id)+1 FROM jaga.instansi_tipe), 1), false);")->execute();
                $this->db->begin();

                $instansiTipe = new InstansiTipe();
                $instansiTipe->nama = $instansi->jenisInstansi->nama;
                $instansiTipe->stranas_filter = false;
                $instansiTipe->created_by = $this->shared->user->um_id;
                if (!$instansiTipe->save()) {
                    throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Gagal menyimpan Tipe Instansi");
                }
            }
        }
        $jagaInstansi = new Instansi();
        $jagaInstansi->nama = $instansi->nama;
        $jagaInstansi->id_provinsi = $instansi->provinsi_id;
        $jagaInstansi->id_kota_kabupaten = $instansi->kab_kota_id;
        $jagaInstansi->id_tipe = $instansiTipe->id;
        $jagaInstansi->flag_dana_desa = 0;
        $jagaInstansi->flag_pak = false;
        $jagaInstansi->show_to_public = 0;
        $jagaInstansi->is_stranas = 0;
        try {
            $jagaInstansi->save();
        } catch (PDOException $e) {
            $this->db->rollback();
            $this->db->query("SELECT setval('jaga.instansi_id_seq', COALESCE((SELECT MAX(id)+1 FROM jaga.instansi), 1), false);")->execute();

            $this->db->begin();
            $instansiTipe = InstansiTipe::findFirst([
                'conditions' => 'lower(nama) LIKE :nama:',
                'bind' => [
                    'nama' => strtolower($instansi->jenisInstansi->nama)
                ]
            ]);
            if (!$instansiTipe) {
                $instansiTipe = new InstansiTipe();
                $instansiTipe->nama = $instansi->jenisInstansi->nama;
                $instansiTipe->stranas_filter = false;
                $instansiTipe->created_by = $this->shared->user->um_id;
                if (!$instansiTipe->save()) {
                    throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Gagal menyimpan Tipe Instansi");
                }
            }
            if (!$instansi->save()) {
                throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Gagal menyimpan data instansi");
            }
            $jagaInstansi->id_tipe = $instansiTipe->id;
            if (!$jagaInstansi->save()) {
                throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR,  "Gagal menyimpan Instansi JAGA");
            }
        }

        return $jagaInstansi;
    }

    private function baseListWithAccessFormulir()
    {
        $getParams = $this->request->get();
        $getParams['jenis_instansi_id'] = array_key_exists('jenis_instansi_id', $getParams) ? $getParams['jenis_instansi_id'] : null;
        $getParams['sektor_usaha_id'] = array_key_exists('sektor_usaha_id', $getParams) ? $getParams['sektor_usaha_id'] : null;
        $getParams['provinsi_id'] = array_key_exists('provinsi_id', $getParams) ? $getParams['provinsi_id'] : null;
        $getParams['kab_kota_id'] = array_key_exists('kab_kota_id', $getParams) ? $getParams['kab_kota_id'] : null;
        $getParams['status'] = array_key_exists('status', $getParams) ? $getParams['status'] : null;
        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';
        $getParams['limit'] = $this->getDefaultLimit($getParams);
        $getParams['offset'] = array_key_exists('offset', $getParams) ? $getParams['offset'] : 0;
        $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',
            'jenis_instansi_id',
            'sektor_usaha_id',
            'provinsi_id',
            'kab_kota_id',
            'nama',
            'status',
            'created_at',
            'updated_at'
        ];
        $allowed_order_direction = ['asc', 'desc'];

        $params = $this->validate(
            $getParams,
            [
                'jenis_instansi_id' => ['validators' => ['digit']],
                'sektor_usaha_id' => ['validators' => ['digit']],
                'provinsi_id' => ['validators' => ['digit']],
                'kab_kota_id' => ['validators' => ['digit']],
                'keyword' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 100
                ]]],
                'status' => ['validators' => [[
                    'name' => 'max_string',
                    'params' => 10
                ]]],
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'order_by' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order
                ]]],
                'order_direction' => ['validators' =>  [[
                    'name' => 'inclusion_in',
                    'params' => $allowed_order_direction
                ]]]
            ]
        );

        $conditions = "(pi.npwp ilike :keyword OR pi.nomor ilike :keyword OR pi.nama ilike :keyword OR pi.sektor_usaha_nama ilike :keyword) AND pi.deleted_at IS NULL";
        $bind = [
            'keyword' => '%' . $params['keyword'] . '%',
            'verifikator_id' => $this->shared->user->um_id
        ];
        if (!is_null($params['jenis_instansi_id'])) {
            $conditions .= " AND pi.jenis_instansi_id = :jenis_instansi_id";
            $bind['jenis_instansi_id'] = $params['jenis_instansi_id'];
        }
        if (!is_null($params['sektor_usaha_id'])) {
            $conditions .= " AND pi.sektor_usaha_id = :sektor_usaha_id";
            $bind['sektor_usaha_id'] = $params['sektor_usaha_id'];
        }
        if (!is_null($params['provinsi_id'])) {
            $conditions .= " AND pi.provinsi_id = :provinsi_id";
            $bind['provinsi_id'] = $params['provinsi_id'];
        }
        if (!is_null($params['kab_kota_id'])) {
            $conditions .= " AND pi.kab_kota_id = :kab_kota_id";
            $bind['kab_kota_id'] = $params['kab_kota_id'];
        }
        if (!is_null($params['status'])) {
            $conditions .= " AND pi.status LIKE :status";
            $bind['status'] = $params['status'];
        }

        $sorting = 'pi.'.$params['order_by'] . ' ' . $params['order_direction'];
        $q = "
            WITH instansi_verifikator AS (
                SELECT iv.user_id, iv.instansi_id
                FROM jaga.pancek_instansi_verifikator iv
                LEFT JOIN pancek_user_access ua ON ua.user_id = iv.user_id AND ua.deleted_at IS NULL
                WHERE iv.deleted_at IS NULL AND (ua.end_date IS NULL OR now() < ua.end_date) AND iv.user_id = :verifikator_id
            ),
            formulir AS (
              SELECT f.*
              FROM pancek_formulir f
              JOIN instansi_verifikator iv ON iv.instansi_id = f.instansi_id
              WHERE now() < (f.created_at + INTERVAL '3 year') AND f.deleted_at IS NULL
              ORDER BY f.id
            )
            SELECT pi.*, f.id AS formulir_id
            FROM pancek_instansi pi JOIN formulir f ON pi.id = f.instansi_id 
            WHERE $conditions
            ORDER BY $sorting;
        ";
        $pancekInstansis = $this->rawQuery($q, $bind);

        $records = [];
        foreach ($pancekInstansis as $pancekInstansi) {
            $model = (new PancekInstansi())->assign($pancekInstansi);
            $record = $model->getDetail();
            $formulir = PancekFormulir::findActiveByInstansiId($pancekInstansi['id']);
            if ($formulir) {
                $simpleFormulir = $formulir->stat(['id', 'periode', 'jumlah_revisi', 'status', 'kppbc_um_id']);
                $record['formulir'] = $simpleFormulir;
            }
            array_push($records, $record);
        }

        $qCount = "
            WITH instansi_verifikator AS (
                SELECT iv.user_id, iv.instansi_id
                FROM jaga.pancek_instansi_verifikator iv
                LEFT JOIN pancek_user_access ua ON ua.user_id = iv.user_id AND ua.deleted_at IS NULL
                WHERE iv.deleted_at IS NULL AND (ua.end_date IS NULL OR now() < ua.end_date) AND iv.user_id = :verifikator_id
            ),
            formulir AS (
              SELECT f.*
              FROM pancek_formulir f
              JOIN instansi_verifikator iv ON iv.instansi_id = f.instansi_id
              WHERE now() < (f.created_at + INTERVAL '3 year') AND f.deleted_at IS NULL
              ORDER BY f.id
            )
            SELECT COUNT(*) AS total
            FROM pancek_instansi pi JOIN formulir f ON pi.id = f.instansi_id
            WHERE $conditions
            ;
        ";
        $total = 0;
        $resultTotal = $this->rawQuery($qCount, $bind);
        if(!empty($resultTotal)) {
            $total = $resultTotal[0]['total'];
        }

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