<?php

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

class RoleController extends BaseController
{
    public function list()
    {
        $this->checkOneOfScopes(['admin.view', 'korsupgah.admin', 'korsupgah.manage_user']);

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

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


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


        $conditions = 'name ilike :keyword:';
        $bind = [
            'keyword' => '%' . $params['keyword'] . '%'
        ];
        $aksi_list = Roles::find([
            'conditions' => $conditions,
            'bind' => $bind,
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'order' => $params['order_by'] . ' ' . $params['order_direction']
        ]);

        $total_record = Roles::count([
            'conditions' => $conditions,
            'bind' => $bind
        ]);

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

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

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

        $params = $this->validate(
            $rawBody,
            [
                'name' => ['validators' => ['required']],
                'label' => ['validators' => ['required']],
                'description' => ['validators' => []]
            ]
        );

        $role = new Roles();
        $role->name = $params['name'];
        $role->label = $params['label'];
        $role->description = $params['description'];
        $role->created_at = date('Y-m-d H:i:s');
        $status = $role->save();

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

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

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

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

        $roles = Roles::findFirst($id);

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

        if (!empty($roles->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Roles sudah terhapus');
        }

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

        $params = $this->validate(
            $rawBody,
            [
                'name' => ['validators' => ['required']],
                'label' => ['validators' => ['required']],
                'description' => ['validators' => []]
            ]
        );

        $roles->name = $params['name'];
        $roles->label = $params['label'];
        $roles->description = $params['description'];
        $roles->updated_at = date('Y-m-d H:i:s');
        $status = $roles->save();

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

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

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

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

        $roles = Roles::findFirst($id);

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

        $result = $roles->toDetail();

        return new CollectionPaginatedResponse([$result]);
    }

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

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

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

        if (!is_array($params['id_scopes'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'ID Scopes tidak valid');
        }

        $id_scopes = array_unique(array_values($params['id_scopes']));
        $roles = Roles::findFirst($params['id']);

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

        if (!empty($roles->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Roles kemungkinan sudah terhapus');
        }

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

        $scopes_master_data = [];
        foreach ($scopes_list as $key => $scope) {
            $scopes_master_data[] = $scope->id;
        }

        $not_exist = array_diff($id_scopes, $scopes_master_data);

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

        $registered_scopes = RolesScopes::find([
            'conditions' => 'role_id=:role_id: AND deleted_at is NULL',
            'bind' => [
                'role_id' => $params['id']
            ]
        ]);

        $registered_scope = [];

        foreach ($registered_scopes as $key => $registered) {
            $registered_scope[] = $registered->scope_id;
        }


        foreach ($scopes_master_data as $key => $id_scope) {
            if (!in_array($id_scope, $registered_scope)) {
                $roles_scopes = new RolesScopes();
                $roles_scopes->role_id = $params['id'];
                $roles_scopes->scope_id = $id_scope;
                $roles_scopes->created_at = date('Y-m-d H:i:s');
                $status = $roles_scopes->save();
            }
        }

        return new CollectionPaginatedResponse([]);
    }

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

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

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

        $roles = Roles::findFirst($params['id']);
        if (empty($roles)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Role tidak diketemukan');
        }

        if (!empty($roles->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Role kemungkinan sudah terhapus');
        }

        $scope = Scopes::findFirst($params['id_scopes']);

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

        if (!empty($scope->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Scopes kemungkinan sudah terhapus');
        }

        $records = RolesScopes::find([
            'conditions' => 'role_id=:role_id: AND scope_id=:scope_id:',
            'bind' => [
                'role_id' => $params['id'],
                'scope_id' => $params['id_scopes']
            ]
        ]);

        if (count($records) < 1) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Role dengan' . $params['id'] . 'tidak memiliki scopes');
        }

        $roles_scopes = $records[0];
        $status = $roles_scopes->delete();

        if (!$status) {
            $errors = $roles_scopes->getMessages();

            $error_messages = [];

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

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

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

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

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

        if (!is_array($params['user_ids'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Daftar ID User tidak valid');
        }

        $userIds = array_unique(array_values($params['user_ids']));
        $roles = Roles::findFirst($roleId);

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

        if (!empty($roles->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Role kemungkinan sudah terhapus');
        }

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

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

        $notExist = array_diff($userIds, $usersUmId);

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

        $usersRegistered = UsersRoles::find([
            'conditions' => 'role_id = :role_id: AND deleted_at is NULL',
            'bind' => [
                'role_id' => $roleId
            ]
        ]);

        $usersRegisteredIds = [];

        foreach ($usersRegistered as $key => $registered) {
            $usersRegisteredIds[] = $registered->user_id;
        }

        $this->db->begin();
        try {
            foreach ($usersUmId as $userId) {
                if (!in_array($userId, $usersRegisteredIds)) {
                    $record = new UsersRoles();
                    $record->role_id = $roleId;
                    $record->user_id = $userId;
                    $record->created_at = date('Y-m-d H:i:s');
                    $status = $record->save();
                    if (!$status) {
                        $errorMessages = [];
                        $errors = $record->getMessages();
                        foreach ($errors as $key => $error) {
                            $errorMessages[] = ['field' => $error->getField(), 'message' => $error->getMessage()];
                        }
                        throw new ValidationException($errorMessages);
                    }
                }
            }
            $this->db->commit();
            return new CollectionPaginatedResponse([]);
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

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

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

        $params = $this->validate(
            $rawBody,
            [
                'user_id' => ['validators' => ['required', 'digit']]
            ]
        );
        $userId = $params['user_id'];

        $roles = Roles::findFirst($roleId);
        if (empty($roles)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Role tidak ditemukan');
        }

        if (!empty($roles->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Role sudah dihapus');
        }

        $user = UmUser::findFirst($userId);

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

        if (!empty($user->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User sudah dihapus');
        }

        $records = UsersRoles::find([
            'conditions' => 'role_id = :role_id: AND user_id = :user_id:',
            'bind' => [
                'role_id' => $roleId,
                'user_id' => $userId
            ]
        ]);

        if (count($records) < 1) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Role dengan' . $params['id'] . 'tidak memiliki user');
        }

        $this->db->begin();
        try {
            foreach ($records as $record) {
                $errorMessages = [];
                $status = $record->delete();
                if (!$status) {
                    $errors = $record->getMessages();
                    foreach ($errors as $key => $error) {
                        $errorMessages[] = ['field' => $error->getField(), 'message' => $error->getMessage()];
                    }
                    throw new ValidationException($errorMessages);
                }
            }

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

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

        $rawBody = $this->request->getJsonRawBody(true);
        $params = $this->validate(
            $rawBody,
            [
                'id' => ['validators' => ['required']],
                'id_roles' => ['validators', ['required']]
            ]
        );
        if (!is_array($params['id_roles'])) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'ID Role tidak valid');
        }

        $id_role_list = array_unique(array_values($params['id_roles']));
        //id_role

        $user = UmUser::findFirst($params['id']); //20021

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

        if (!empty($user->deleted_at)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'User kemungkinan sudah terhapus');
        }

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

        // check expected role list exists in master data

        $roles_master_data = [];

        foreach ($roles_list as $key => $role) {
            $roles_master_data[] = $role->id;
        }

        $not_exist = array_diff($id_role_list, $roles_master_data);

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

        $roles_set = UsersRoles::find([
            'conditions' => 'user_id=:user_id: AND deleted_at is NULL',
            'bind' => [
                'user_id' => $params['id']
            ]
        ]);

        $registered_roles_ids = [];

        foreach ($roles_set as $key => $set_roles) {
            $registered_roles_ids[] = $set_roles->role_id;
            if (!in_array($set_roles->role_id, $roles_master_data)) {
                $status = $set_roles->delete();
            }
        }

        foreach ($roles_master_data as $key => $roles_id) {
            if (!in_array($roles_id, $registered_roles_ids)) {
                $roles_scope_set = new UsersRoles();

                $roles_scope_set->role_id = $roles_id;
                $roles_scope_set->user_id = $params['id'];
                $roles_scope_set->created_at = date('Y-m-d H:i:s');
                $status = $roles_scope_set->save();
            }
        }

        $result = $user->baseToArray();

        return new CollectionPaginatedResponse([$result]);
    }

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

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

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


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

        $role = Roles::findFirst($id);

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

        $listScopes = $role->getScopes([
            'conditions' => 'label ilike :keyword:',
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%'
            ],
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'order' => '[Scopes].'.$params['order_by'] . ' ' . $params['order_direction']
        ]);

        $total_record = $role->getScopes([
            'conditions' => 'label ilike :keyword:',
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%'
            ],
            'columns' => 'COUNT(*) AS total',
            'group_by' => "[Scopes].[id]"
        ])[0]['total'];

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

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

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

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

        $role = Roles::findFirst($id);

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

        $keyword = '%' . $params['keyword'] . '%';
        $limit = $params['limit'];
        $offset = $params['offset'];
        $order = "s." . $params['order_by'] . " " . $params['order_direction'];
        $q = "
            SELECT s.*, CASE WHEN rs.id IS NOT NULL THEN 1 ELSE 0 END as owned
            FROM jaga.scopes s
            LEFT JOIN 
            (
                SELECT rs.id, rs.scope_id FROM jaga.roles_scopes rs LEFT JOIN jaga.roles r ON r.id = rs.role_id WHERE r.id = :id
            ) rs ON rs.scope_id = s.id
            WHERE (s.name ilike :keyword OR s.label ilike :keyword)
            ORDER BY $order
            LIMIT :limit OFFSET :offset
        ";
        $allScopes = $this->rawQuery($q, ['id' => $id, 'keyword' => $keyword, 'limit' => $limit, 'offset' => $offset]);

        $countScopes = Scopes::find([
            'conditions' => 'name ilike :keyword: or label ilike :keyword:',
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%'
            ],
            'columns' => 'COUNT(*) AS total',
            'group_by' => "[Scopes].[id]"
        ])[0]['total'];

        return new PaginatedResponse($allScopes, $countScopes, $params['limit'], $params['offset']);
    }

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

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

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


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

        $role = Roles::findFirst($id);

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

        $conditions = 'um_user_name ilike :keyword:';
        if(!$params['trashed']) {
            $conditions .= ' and is_active = true';
        }

        $listUsers = $role->getUmUser([
            'conditions' => $conditions,
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%'
            ],
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'order' => $params['order_by'] . ' ' . $params['order_direction'],
            'columns' => 'um_id, um_user_name, email, id_instansi'
        ]);

        $total_record = $role->getUmUser([
            'conditions' => $conditions,
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%'
            ],
            'columns' => 'COUNT(*) as total',
            'group_by'=> 'um_id'
        ])[0]['total'];

        $result = [];
        foreach ($listUsers as $user) {
            $resultItem = $user->toArray();
            $resultItem['instansi_name'] = "";
            $userDetail = UmUser::findFirst($user['um_id']);
            $instansi = $userDetail->instansi;
            if($instansi) {
                $resultItem['instansi_name'] = $instansi->nama;
            }
            array_push($result, $resultItem);
        }

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

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

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

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


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

        $role = Roles::findFirst($id);

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

        $limit = $params['limit'];
        $offset = $params['offset'];
        $keyword = '%' . $params['keyword'] . '%';
        $order = "u." . $params['order_by'] . " " . $params['order_direction'];
        $query = "
            SELECT u.um_id, u.um_user_name, u.email, u.id_instansi, i.nama as instansi_name, CASE WHEN ur.id IS NOT NULL THEN 1 ELSE 0 END as owned
            FROM jaga.um_user u
            LEFT JOIN
            (
                SELECT ur.id, ur.user_id FROM jaga.users_roles ur LEFT JOIN jaga.roles r ON r.id = ur.role_id WHERE r.id = :id
            ) ur ON ur.user_id = u.um_id
            LEFT JOIN jaga.instansi i ON i.id = u.id_instansi
            WHERE u.um_user_name ilike :keyword OR u.email ilike :keyword
            ORDER BY $order
            LIMIT :limit OFFSET :offset
        ";
        $bind = [
            'id' => $id,
            'keyword' => $keyword,
            'limit' => $limit,
            'offset' => $offset
        ];

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

        $totalRecord = UmUser::find([
            'conditions' => 'um_user_name ilike :keyword: or email ilike :keyword:',
            'bind' => [
                'keyword' => '%' . $params['keyword'] . '%'
            ],
            'columns' => 'COUNT(*) as total',
            'group_by'=> 'um_id'
        ])[0]['total'];

        return new PaginatedResponse($allUsers, $totalRecord, $params['limit'], $params['offset']);
    }

    public function getKorwilKlopRoles()
    {
        $result = Roles::find([
            'columns' => 'id, name, label',
            'conditions' => 'name in ({names:array})',
            'bind'  => [
                'names' => Roles::KORWIL_KLOP_ROLES
            ],
            'order' => 'name asc'
        ]);
        return new CollectionPaginatedResponse($result);
    }

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

        /** @var Roles $role */
        $role = Roles::findFirst($id);

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

        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setCellValue("A1", 'ID');
        $sheet->setCellValue("B1", "Nama");
        $sheet->setCellValue("C1", "Username");
        $sheet->setCellValue("D1", "Email");
        $sheet->setCellValue("E1", "Telepon");
        $sheet->setCellValue("F1", "Instansi");

        $row = 2;
        $users = UmUser::query()
            ->columns("UmUser.um_id, UmUser.nama, UmUser.um_user_name, UmUser.email, UmUser.phone, Instansi.nama as nama_instansi")
            ->leftJoin(UsersRoles::class, "UsersRoles.user_id = UmUser.um_id")
            ->leftJoin(Roles::class, "Roles.id = UsersRoles.role_id")
            ->leftJoin(Instansi::class, "Instansi.id = UmUser.id_instansi")
            ->where("Roles.id = :role_id: and UmUser.is_active = true")
            ->bind(["role_id" => $id])
            ->execute()
            ->toArray()
        ;
        foreach ($users as $user) {
            $sheet->setCellValue("A".$row, $user["um_id"]);
            $sheet->setCellValue("B".$row, $user["nama"]);
            $sheet->setCellValue("C".$row, $user["um_user_name"]);
            $sheet->setCellValue("D".$row, $user["email"]);
            $sheet->setCellValue("E".$row, $user["phone"]);
            $sheet->setCellValue("F".$row, $user["nama_instansi"] ?? "-");
            $row++;
        }

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

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

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

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


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


        $conditions = 'name ilike :keyword: AND name IN ({name_array:array})';
        $bind = [
            'keyword' => '%' . $params['keyword'] . '%',
            'name_array' => ['verifikator_pancek', 'observer_pancek', 'verifikator_kppbc_pancek', 'assignor_kppbc_pancek', 'verifikator_bk_pancek', 'assignor_bk_pancek', 'admin_kppbc_pancek']
        ];
        $aksi_list = Roles::find([
            'conditions' => $conditions,
            'bind' => $bind,
            'limit' => $params['limit'],
            'offset' => $params['offset'],
            'order' => $params['order_by'] . ' ' . $params['order_direction']
        ]);

        $total_record = Roles::count([
            'conditions' => $conditions,
            'bind' => $bind
        ]);

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