<?php

class PancekUserVerifikatorController extends BaseController
{
    public function list()
    {
        $this->checkOneOfScopes(['pancek.admin']);
        $params = $this->request->get();

        $params['username'] = array_key_exists('username', $params) ? $params['username'] : '';
        $params['email'] = array_key_exists('email', $params) ? $params['email'] : '';
        $params['nama'] = array_key_exists('nama', $params) ? $params['nama'] : '';
        $params['start_date'] = array_key_exists('start_date', $params) ? $params['start_date'] : null;
        $params['end_date'] = array_key_exists('end_date', $params) ? $params['end_date'] : null;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['order_by'] = array_key_exists('order_by', $params) ? strtolower($params['order_by']) : 'id';
        $params['order_direction'] = array_key_exists('order_direction', $params) ? strtolower($params['order_direction']) : 'asc';

        $allowed_order = [
            'id',
            'username',
            'nama',
            'email',
            'start_date',
            'end_date',
            'created_at',
            'updated_at'
        ];
        $allowed_order_direction = ['asc', 'desc'];
        $bind_order = [
            'id' => 'UmUser.um_id',
            'username' => 'UmUser.um_user_name',
            'nama' => 'UmUser.nama',
            'email' => 'UmUser.email',
            'start_date' => 'PancekUserVerifikator.start_date',
            'end_date' => 'PancekUserVerifikator.end_date',
            'created_at' => 'PancekUserVerifikator.created_at',
            'updated_at' => 'PancekUserVerifikator.updated_at'
        ];

        $this->validate(
            $params,
            [
                'username' => ['validators' => []],
                'nama' => ['validators' => []],
                'email' => ['validators' => []],
                'start_date' => ['validators' => []],
                'end_date' => ['validators' => []],
                'keyword' => ['validators' => []],
                '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
                ]]]
            ]
        );

        $wherePeriode = "";
        $whereCountPeriode = "";
        $bind = [
            "username" => '%' . $params['username'] . '%',
            "nama" => '%' . $params['nama'] . '%',
            "email" => '%' . $params['email'] . '%',
            "keyword" => '%' . $params['keyword'] . '%',
        ];

        if($params['start_date'] != null && $params['end_date'] != null) {
            $wherePeriode = " AND (PancekUserVerifikator.start_date >= :start_date: AND PancekUserVerifikator.end_date <= :end_date:) ";
            $whereCountPeriode = " AND (pancek_user_verifikator.start_date >= :start_date AND pancek_user_verifikator.end_date <= :end_date) ";
            $bind['start_date'] = $params['start_date'];
            $bind['end_date'] = $params['end_date'];
        }
        else if ($params['start_date'] != null && $params['end_date'] == null) {
            $wherePeriode = " AND PancekUserVerifikator.start_date >= :start_date:";
            $whereCountPeriode = " AND pancek_user_verifikator.start_date >= :start_date";
            $bind['start_date'] = $params['start_date'];
        }
        else if($params['start_date'] == null && $params['end_date'] != null) {
            $wherePeriode = " AND PancekUserVerifikator.end_date <= :end_date: ";
            $whereCountPeriode = " AND pancek_user_verifikator.end_date <= :end_date ";
            $bind['end_date'] = $params['end_date'];
        }

        $result = PancekUserVerifikator::query()
            ->columns("
            UmUser.um_user_name, UmUser.nama, UmUser.email, UmUser.phone, UmUser.jenis_kelamin, 
            PancekUserVerifikator.id, PancekUserVerifikator.um_id, PancekUserVerifikator.start_date, PancekUserVerifikator.end_date, PancekUserVerifikator.created_at, PancekUserVerifikator.updated_at, PancekUserVerifikator.deleted_at 
            ")
            ->leftJoin('UmUser', 'UmUser.um_id = PancekUserVerifikator.um_id')
            ->where(
                "UmUser.um_user_name ILIKE :username: AND " .
                "UmUser.nama ILIKE :nama: AND " .
                "UmUser.email ILIKE :email: AND " .
                "PancekUserVerifikator.deleted_at IS NULL AND " .
                "(UmUser.um_user_name ILIKE :keyword: OR UmUser.nama ILIKE :keyword: OR UmUser.email ILIKE :keyword:)" .
                $wherePeriode
            )
            ->bind($bind)
            ->orderBy($bind_order[$params['order_by']] . " " . $params['order_direction'])
            ->limit($params['limit'], $params['offset'])
            ->execute()
            ->toArray();

        $total = $this->rawQuery("
            SELECT count(*) as total FROM jaga.pancek_user_verifikator 
            LEFT JOIN jaga.um_user ON um_user.um_id = pancek_user_verifikator.um_id 
            WHERE um_user.um_user_name ILIKE :username
            AND um_user.nama ILIKE :nama
            AND um_user.email ILIKE :email
            AND (um_user.um_user_name ILIKE :keyword OR um_user.nama ILIKE :keyword OR um_user.email ILIKE :keyword)
            AND pancek_user_verifikator.deleted_at IS NULL
        " . $whereCountPeriode,
            $bind
        )[0]['total'];

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

    public function listBK()
    {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.bk.assign']);
        $params = $this->request->get();

        $params['username'] = array_key_exists('username', $params) ? $params['username'] : '';
        $params['email'] = array_key_exists('email', $params) ? $params['email'] : '';
        $params['nama'] = array_key_exists('nama', $params) ? $params['nama'] : '';
        $params['start_date'] = array_key_exists('start_date', $params) ? $params['start_date'] : null;
        $params['end_date'] = array_key_exists('end_date', $params) ? $params['end_date'] : null;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['order_by'] = array_key_exists('order_by', $params) ? strtolower($params['order_by']) : 'um_id';
        $params['order_direction'] = array_key_exists('order_direction', $params) ? strtolower($params['order_direction']) : 'desc';

        $allowed_order = [
            'um_id',
            'username',
            'nama',
            'email',
            'total_assignment',
            'start_date',
            'end_date'
        ];
        $allowed_order_direction = ['asc', 'desc'];
        $bind_order = [
            'um_id' => 'um.um_id',
            'username' => 'um.um_user_name',
            'nama' => 'um.nama',
            'email' => 'um.email',
            'total_assignment' => 'COALESCE(ci.total, 0)',
            'start_date' => 'ua.start_date',
            'end_date' => 'ua.end_date'
        ];

        $this->validate(
            $params,
            [
                'username' => ['validators' => []],
                'nama' => ['validators' => []],
                'email' => ['validators' => []],
                'start_date' => ['validators' => []],
                'end_date' => ['validators' => []],
                'keyword' => ['validators' => []],
                '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
                ]]]
            ]
        );

        $query = "WITH count_instansiCTE as (
                    SELECT iv.user_id, count(iv.user_id) as total
                    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)
                    GROUP BY iv.user_id
                )
                
                SELECT um.um_id, um.um_user_name as username, um.nama, um.email, um.phone, COALESCE(ci.total, 0) as total_assignment, ua.start_date, ua.end_date
                FROM jaga.um_user um
                LEFT JOIN jaga.users_roles ur ON ur.user_id = um.um_id AND ur.deleted_at IS NULL
                LEFT JOIN jaga.roles r ON r.id = ur.role_id AND r.deleted_at IS NULL
                LEFT JOIN jaga.pancek_user_access ua ON ua.user_id = um.um_id AND ua.deleted_at IS NULL
                LEFT JOIN count_instansiCTE ci ON ci.user_id = um.um_id
                WHERE r.name = 'verifikator_bk_pancek' AND um.um_user_name ILIKE :username AND um.nama ILIKE :nama AND um.email ILIKE :email
                AND (um.um_user_name ILIKE :keyword OR um.nama ILIKE :keyword OR um.email ILIKE :keyword)
                ";

        $where = "";
        $bind = [
            "username" => '%' . $params['username'] . '%',
            "nama" => '%' . $params['nama'] . '%',
            "email" => '%' . $params['email'] . '%',
            "keyword" => '%' . $params['keyword'] . '%',
        ];

        if($params['start_date'] != null && $params['end_date'] != null) {
            $where = " AND (ua.start_date >= :start_date AND ua.end_date <= :end_date) ";
            $bind['start_date'] = $params['start_date'];
            $bind['end_date'] = $params['end_date'];
        }
        else if ($params['start_date'] != null && $params['end_date'] == null) {
            $where = " AND ua.start_date >= :start_date";
            $bind['start_date'] = $params['start_date'];
        }
        else if($params['start_date'] == null && $params['end_date'] != null) {
            $where = " AND ua.end_date <= :end_date";
            $bind['end_date'] = $params['end_date'];
        }
        $query .= $where . " ORDER BY " . $bind_order[$params['order_by']] . " " . $params['order_direction'];
        $result = $this->rawQuery($query . " LIMIT :limit OFFSET :offset", array_merge($bind, ['limit' => $params['limit'], 'offset' => $params['offset']]));

        $queryTotal = "SELECT count(um.um_id) as total
                        FROM jaga.um_user um
                        LEFT JOIN jaga.users_roles ur ON ur.user_id = um.um_id
                        LEFT JOIN jaga.roles r ON r.id = ur.role_id
                        LEFT JOIN jaga.pancek_user_access ua ON ua.user_id = um.um_id
                        WHERE r.name = 'verifikator_bk_pancek' AND um.um_user_name ILIKE :username AND um.nama ILIKE :nama AND um.email ILIKE :email
                AND (um.um_user_name ILIKE :keyword OR um.nama ILIKE :keyword OR um.email ILIKE :keyword)" . $where;
        $total = $this->rawQuery($queryTotal, $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']);
        $params = $this->request->get();

        $params['username'] = array_key_exists('username', $params) ? $params['username'] : '';
        $params['email'] = array_key_exists('email', $params) ? $params['email'] : '';
        $params['nama'] = array_key_exists('nama', $params) ? $params['nama'] : '';
        $params['start_date'] = array_key_exists('start_date', $params) ? $params['start_date'] : null;
        $params['end_date'] = array_key_exists('end_date', $params) ? $params['end_date'] : null;
        $params['keyword'] = array_key_exists('keyword', $params) ? $params['keyword'] : '';
        $params['limit'] = $this->getDefaultLimit($params);
        $params['offset'] = array_key_exists('offset', $params) ? $params['offset'] : 0;
        $params['order_by'] = array_key_exists('order_by', $params) ? strtolower($params['order_by']) : 'um_id';
        $params['order_direction'] = array_key_exists('order_direction', $params) ? strtolower($params['order_direction']) : 'desc';

        $kppbcIds = [];
        if ($this->hasScopes(['pancek.kppbc.assignor']) && !$this->hasOneOfScopes(['pancek.admin', 'pancek.kppbc.admin'])) {
            $pancekKppbcUsers = PancekKppbcUser::find(['conditions' => "um_id = :um_id: AND deleted_at IS NULL AND user_type = 'ASSIGNOR'", '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;
            }
        }
        $verifikatorKPPBCConditions = (empty($kppbcIds) ? "" : " AND ku.kppbc_id IN (".implode(',', $kppbcIds).")");

        $allowed_order = [
            'um_id',
            'username',
            'nama',
            'email',
            'total_assignment',
            'start_date',
            'end_date'
        ];
        $allowed_order_direction = ['asc', 'desc'];
        $bind_order = [
            'um_id' => 'um.um_id',
            'username' => 'um.um_user_name',
            'nama' => 'um.nama',
            'email' => 'um.email',
            'total_assignment' => 'COALESCE(ci.total, 0)',
            'start_date' => 'ua.start_date',
            'end_date' => 'ua.end_date'
        ];

        $this->validate(
            $params,
            [
                'username' => ['validators' => []],
                'nama' => ['validators' => []],
                'email' => ['validators' => []],
                'start_date' => ['validators' => []],
                'end_date' => ['validators' => []],
                'keyword' => ['validators' => []],
                '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
                ]]]
            ]
        );

        $query = "WITH count_instansiCTE as (
                    SELECT iv.user_id, count(iv.user_id) as total
                    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)
                    GROUP BY iv.user_id
                )
                
                SELECT um.um_id, um.um_user_name as username, um.nama, um.email, um.phone, COALESCE(ci.total, 0) as total_assignment, ua.start_date, ua.end_date, ku.kppbc_id
                FROM jaga.um_user um
                LEFT JOIN jaga.users_roles ur ON ur.user_id = um.um_id AND ur.deleted_at IS NULL
                LEFT JOIN jaga.roles r ON r.id = ur.role_id AND r.deleted_at IS NULL
                LEFT JOIN jaga.pancek_user_access ua ON ua.user_id = um.um_id AND ua.deleted_at IS NULL
                LEFT JOIN count_instansiCTE ci ON ci.user_id = um.um_id
                LEFT JOIN jaga.pancek_kppbc_user ku ON ku.um_id = um.um_id
                WHERE r.name = 'verifikator_kppbc_pancek' AND um.um_user_name ILIKE :username AND um.nama ILIKE :nama AND um.email ILIKE :email AND ku.deleted_at IS NULL AND ku.user_type = 'VERIFIKATOR'
                AND (um.um_user_name ILIKE :keyword OR um.nama ILIKE :keyword OR um.email ILIKE :keyword)
                ".$verifikatorKPPBCConditions."
                ";

        $where = "";
        $bind = [
            "username" => '%' . $params['username'] . '%',
            "nama" => '%' . $params['nama'] . '%',
            "email" => '%' . $params['email'] . '%',
            "keyword" => '%' . $params['keyword'] . '%',
        ];

        if($params['start_date'] != null && $params['end_date'] != null) {
            $where = " AND (ua.start_date >= :start_date AND ua.end_date <= :end_date) ";
            $bind['start_date'] = $params['start_date'];
            $bind['end_date'] = $params['end_date'];
        }
        else if ($params['start_date'] != null && $params['end_date'] == null) {
            $where = " AND ua.start_date >= :start_date";
            $bind['start_date'] = $params['start_date'];
        }
        else if($params['start_date'] == null && $params['end_date'] != null) {
            $where = " AND ua.end_date <= :end_date";
            $bind['end_date'] = $params['end_date'];
        }
        $query .= $where . " ORDER BY " . $bind_order[$params['order_by']] . " " . $params['order_direction'];
        $result = $this->rawQuery($query . " LIMIT :limit OFFSET :offset", array_merge($bind, ['limit' => $params['limit'], 'offset' => $params['offset']]));

        $queryTotal = "SELECT count(um.um_id) as total
                        FROM jaga.um_user um
                        LEFT JOIN jaga.users_roles ur ON ur.user_id = um.um_id
                        LEFT JOIN jaga.roles r ON r.id = ur.role_id
                        LEFT JOIN jaga.pancek_user_access ua ON ua.user_id = um.um_id
                        LEFT JOIN jaga.pancek_kppbc_user ku ON ku.um_id = um.um_id
                        WHERE r.name = 'verifikator_kppbc_pancek' AND um.um_user_name ILIKE :username AND um.nama ILIKE :nama AND um.email ILIKE :email AND ku.deleted_at IS NULL AND ku.user_type = 'VERIFIKATOR'
                        ".$verifikatorKPPBCConditions."
                AND (um.um_user_name ILIKE :keyword OR um.nama ILIKE :keyword OR um.email ILIKE :keyword)" . $where;
        $total = $this->rawQuery($queryTotal, $bind)[0]['total'];

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

    public function assignBK() {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.bk.assign']);

        $payload = $this->request->getJsonRawBody(true);
        $payload = $this->validate($payload, [
            "um_id" => ["validators" => ["required"]],
            "instansi_ids" => ["validators" => ["required"]]
        ]);

        $verifikator = UmUser::findFirst($payload['um_id']);
        if (!$verifikator) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "User Verifikator tidak ditemukan");
        }
        if (!in_array('pancek.bk.verify', $verifikator->getScopes())) {
            throw new CustomErrorException(BaseResponse::INVALID_ACTION, "User yang dipilih bukan merupakan Verifikator Pancek Bina Konstruksi");
        }

        $instansiIds = implode(",", $payload['instansi_ids']);
        $instansis = $this->rawQuery("
            SELECT * FROM jaga.pancek_instansi WHERE ceklis_kepentingan = '".PancekInstansi::CEKLIST_KEPENTINGAN_BK."' AND deleted_at IS NULL AND id IN (".$instansiIds.")
        ");
        $ids = array_column($instansis, 'id');

        if (count($ids) > 0) {
            $this->rawQuery("update jaga.pancek_instansi_verifikator set deleted_at = now() where instansi_id in (".implode(",",$ids).") and deleted_at is null");
            $currentAssignments = PancekInstansiVerifikator::count([
                'conditions' => 'user_id = :user_id: AND deleted_at IS NULL AND instansi_id NOT IN ({instansi_ids:array})',
                'bind' => ['user_id' => $payload['um_id'], 'instansi_ids' => $ids]
            ]);
        } else {
            $currentAssignments = PancekInstansiVerifikator::count([
                'conditions' => 'user_id = :user_id: AND deleted_at IS NULL',
                'bind' => ['user_id' => $payload['um_id']]
            ]);
        }

        $maxAssignment = AppConfig::getValueByKode('VERIFIKATOR_BK_MAX_BU');
        if (($currentAssignments + count($instansis)) > $maxAssignment) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Verifikator tidak boleh memiliki lebih dari $maxAssignment assignment");
        }

        try {
            $this->db->begin();
            foreach ($instansis as $instansi) {
                $row = new PancekInstansiVerifikator();
                $row->instansi_id = $instansi['id'];
                $row->user_id = $payload['um_id'];
                $row->save();
            }
            $pancekService = new PancekService($this);
            $pancekService->handleAssignmentNotification($payload['um_id']);
            $this->db->commit();
            return new SimpleResponse($ids);
        } catch (\Exception $e) {
            $this->db->rollback();
            throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Gagal melakukan assignment verifikator");
        }
    }

    public function assignBC() {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.kppbc.admin', 'pancek.kppbc.assignor']);

        $payload = $this->request->getJsonRawBody(true);
        $payload = $this->validate($payload, [
            "um_id" => ["validators" => ["required"]],
            "instansi_ids" => ["validators" => ["required"]]
        ]);

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

        $verifikator = UmUser::findFirst($payload['um_id']);
        if (!$verifikator) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "User Verifikator tidak ditemukan");
        }
        if (!in_array('pancek.kppbc.verify', $verifikator->getScopes())) {
            throw new CustomErrorException(BaseResponse::INVALID_ACTION, "User yang dipilih bukan merupakan Verifikator Pancek KPPBC");
        }
        if (!empty($assignorKppbcIds)) {
            $verifikatorKppbcIds = [];
            $pancekKppbcVerifikators = PancekKppbcUser::find(['conditions' => "um_id = :um_id: AND deleted_at IS NULL AND user_type = 'VERIFIKATOR'", 'bind' => ['um_id' => $payload['um_id']]]);
            if (count($pancekKppbcVerifikators) == 0) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Verifikator yang dipilih belum terdaftar pada KPPBC tertentu.");
            }
            foreach ($pancekKppbcVerifikators as $pancekKppbcVerifikator) {
                $verifikatorKppbcIds[] = $pancekKppbcVerifikator->kppbc_id;
            }
            if (empty(array_intersect($assignorKppbcIds, $verifikatorKppbcIds))) {
                throw new CustomErrorException(BaseResponse::INVALID_ACTION, "Anda tidak dapat mengassign Verifikator yang berbeda KPPBC dengan Anda.");
            }
        }


        $instansiKPPBCConditions = (empty($assignorKppbcIds) ? "" : " AND kppbc_id IN (".implode(',', $assignorKppbcIds).")");
        $instansiIds = implode(",", $payload['instansi_ids']);
        $instansis = $this->rawQuery("
            SELECT * FROM jaga.pancek_instansi WHERE ceklis_kepentingan = '".PancekInstansi::CEKLIST_KEPENTINGAN_BC."' AND deleted_at IS NULL AND id IN (".$instansiIds.")".$instansiKPPBCConditions."
        ");
        $ids = array_column($instansis, 'id');

        if (count($ids) > 0) {
            $this->rawQuery("update jaga.pancek_instansi_verifikator set deleted_at = now() where instansi_id in (".implode(",",$ids).") and deleted_at is null");
            $currentAssignments = PancekInstansiVerifikator::count([
                'conditions' => 'user_id = :user_id: AND deleted_at IS NULL AND instansi_id NOT IN ({instansi_ids:array})',
                'bind' => ['user_id' => $payload['um_id'], 'instansi_ids' => $ids]
            ]);
        } else {
            $currentAssignments = PancekInstansiVerifikator::count([
                'conditions' => 'user_id = :user_id: AND deleted_at IS NULL',
                'bind' => ['user_id' => $payload['um_id']]
            ]);
        }

        // todo apakah akan sama untuk verifikator bea cukai?
        $maxAssignment = AppConfig::getValueByKode('VERIFIKATOR_BK_MAX_BU');
        if (($currentAssignments + count($instansis)) > $maxAssignment) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Verifikator tidak boleh memiliki lebih dari $maxAssignment assignment");
        }

        try {
            $this->db->begin();
            foreach ($instansis as $instansi) {
                $row = new PancekInstansiVerifikator();
                $row->instansi_id = $instansi['id'];
                $row->user_id = $payload['um_id'];
                $row->save();
            }
            $pancekService = new PancekService($this);
            $pancekService->handleAssignmentNotification($payload['um_id']);
            $this->db->commit();
            return new SimpleResponse($ids);
        } catch (\Exception $e) {
            $this->db->rollback();
            throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, "Gagal melakukan assignment verifikator");
        }
    }

    public function unassignBK() {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.bk.assign']);

        $payload = $this->request->getJsonRawBody(true);
        $payload = $this->validate($payload, [
            "um_id" => ["validators" => ["required"]],
            "instansi_ids" => ["validators" => ["required"]]
        ]);

        $verifikator = UmUser::findFirst($payload['um_id']);
        if (!$verifikator) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "User Verifikator tidak ditemukan");
        }
        if (!in_array('pancek.bk.verify', $verifikator->getScopes())) {
            throw new CustomErrorException(BaseResponse::INVALID_ACTION, "User yang dipilih bukan merupakan Verifikator Pancek Bina Konstruksi");
        }

        $instansiIds = implode(",", $payload['instansi_ids']);
        $instansis = $this->rawQuery("
            SELECT * FROM jaga.pancek_instansi WHERE ceklis_kepentingan = '".PancekInstansi::CEKLIST_KEPENTINGAN_BK."' AND deleted_at IS NULL AND id IN (".$instansiIds.")
        ");
        $ids = array_column($instansis, 'id');

        if (count($ids) > 0) {
            $this->rawQuery("update jaga.pancek_instansi_verifikator set deleted_at = now() where instansi_id in (".implode(",",$ids).") and deleted_at is null");
        }

        return new SimpleResponse($ids);
    }

    public function unassignBC() {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.kppbc.admin', 'pancek.kppbc.assignor']);

        $payload = $this->request->getJsonRawBody(true);
        $payload = $this->validate($payload, [
            "um_id" => ["validators" => ["required"]],
            "instansi_ids" => ["validators" => ["required"]]
        ]);

        $verifikator = UmUser::findFirst($payload['um_id']);
        if (!$verifikator) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "User Verifikator tidak ditemukan");
        }
        if (!in_array('pancek.kppbc.verify', $verifikator->getScopes())) {
            throw new CustomErrorException(BaseResponse::INVALID_ACTION, "User yang dipilih bukan merupakan Verifikator Pancek KPPBC");
        }

        $instansiIds = implode(",", $payload['instansi_ids']);
        $instansis = $this->rawQuery("
            SELECT * FROM jaga.pancek_instansi WHERE ceklis_kepentingan = '".PancekInstansi::CEKLIST_KEPENTINGAN_BC."' AND id IN (".$instansiIds.")
        ");
        $ids = array_column($instansis, 'id');

        if (count($ids) > 0) {
            $this->rawQuery("update jaga.pancek_instansi_verifikator set deleted_at = now() where instansi_id in (".implode(",",$ids).") and user_id = ".$payload['um_id']." and deleted_at is null");
        }

        return new SimpleResponse($ids);
    }

    public function detail($id) {
        $this->checkOneOfScopes(['pancek.admin']);
        $data = PancekUserVerifikator::findFirst($id);
        if(!$data) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data User Verifikator tidak ditemukan");
        }

        $dataUser = $data->umUser->baseToArray();
        $data = $data->toArray();
        $data['um_user'] = $dataUser;

        return new SimpleResponse($data);
    }

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

        $payload = $this->request->getJsonRawBody(true);
        $payload = $this->validatePayload($payload);

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

            $data = new PancekUserVerifikator();
            $data->um_id = $payload['um_id'];
            $data->start_date = $payload['start_date'];
            $data->end_date = $payload['end_date'];
            $data->save();

            $roleId = Roles::getIdByRole('verifikator_pancek');
            $dataUserRole = new UsersRoles();
            $dataUserRole->user_id = $payload['um_id'];
            $dataUserRole->role_id = $roleId;
            $dataUserRole->save();

            $this->db->commit();

            return new SimpleResponse($data);

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

    private function validatePayload($payload)
    {
        $payload = $this->validate($payload, [
            "um_id" => ["validators" => ["required"]],
            "start_date" => ["validators" => ["required", "date"]],
            "end_date" => ["validators" => ["required", "date"]],
        ]);

        if(!UmUser::findFirst($payload['um_id'])) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data User tidak ditemukan!");
        }

        if(PancekUserVerifikator::findFirst([
            'conditions' => 'um_id = :um_id: AND deleted_at IS NULL',
            'bind' => ['um_id' => $payload['um_id']]
        ])) {
            throw new CustomErrorException(BaseResponse::DUPLICATE_DATA, "User sudah terdaftar sebagai Verifikator");
        }

        if(strtotime($payload['start_date']) > strtotime($payload['end_date'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Tanggal Akhir harus lebih besar atau sama dengan Tanggal Awal");
        }

        return $payload;
    }

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

        $payload = $this->request->getJsonRawBody(true);
        $payload = $this->validatePayloadUpdate($payload);

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

            $data = PancekUserVerifikator::findFirst($id);
            if(!$data) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data User Verifikator tidak ditemukan!");
            }
            $data->start_date = $payload['start_date'];
            $data->end_date = $payload['end_date'];
            $data->save();

            $this->db->commit();

            return new SimpleResponse($data);

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

    private function validatePayloadUpdate($payload)
    {
        $payload = $this->validate($payload, [
            "start_date" => ["validators" => ["required", "date"]],
            "end_date" => ["validators" => ["required", "date"]],
        ]);

        if(strtotime($payload['start_date']) > strtotime($payload['end_date'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Tanggal Akhir harus lebih besar atau sama dengan Tanggal Awal");
        }

        return $payload;
    }

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


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

            $data = PancekUserVerifikator::findFirst($id);
            if(!$data) {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Data User Verifikator tidak ditemukan!");
            }
            $umId = $data->um_id;
            $data->delete();

            $roleId = Roles::getIdByRole('verifikator_pancek');
            $dataUserRole = UsersRoles::findFirst([
                'conditions' => 'user_id = :um_id: AND role_id = :role_id:',
                'bind' => [
                    'um_id' => $umId,
                    'role_id' => $roleId
                ]
            ]);

            if($dataUserRole) {
                $dataUserRole->delete();
            }

            $this->db->commit();

            return new SimpleResponse($data);

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

    public function permissionCheck() {
        $this->checkOneOfScopes(['pancek.admin', 'pancek.answer', 'pancek.monitoring', 'pancek.verify']);
        $isAllowed = true;
        $message = '';

        if(($this->hasScopes(['pancek.verify']) && !$this->hasScopes(['pancek.admin', 'pancek.answer', 'pancek.monitoring'])) || ($this->hasScopes(['pancek.verify', 'pancek.answer']) && !$this->hasScopes(['pancek.admin', 'pancek.monitoring']))) {
            $data = PancekUserVerifikator::findByUmId($this->userID, false);
            if (strtotime(date('Y-m-d')) > strtotime($data->end_date)) {

                $isAllowed = false;
                $message = 'Masa akses anda sudah berakhir (' . DateHelper::formatFromDateString($data->start_date) . ' s.d. ' . DateHelper::formatFromDateString($data->end_date) . ')';
            }
        }

        $result = [
            'is_allowed' => $isAllowed,
            'message' => $message
        ];
        return new SimpleResponse($result);
    }
}