<?php

use Jaga\Models\UsersSekolah;
use Phalcon\Tag;
use Phalcon\Mvc\Model\Criteria;
use Phalcon\Paginator\Adapter\Model as Paginator;
use Phalcon\Http\Request;
use Phalcon\Security;
use Phalcon\Forms\Element\Check;
use Phalcon\Forms\Element\Text;
use Phalcon\Mvc\Model\Resultset\Simple;
use \Firebase\JWT\JWT;
use Google\Client;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class UserController extends BaseController
{
    const CHANGE_PROFILE_PICTURE = 1;
    const KEEP_PROFILE_PICTURE = 2;
    const KORWIL_ROLES = ['admin_korsupgah', 'admin_klop_korsupgah', 'admin_komunitas_korsupgah', 'observer_korsupgah', 'verifikator_korsupgah', 'admin_user_korsupgah', 'pic_korsup', 'qa_korsup'];
    const LOGIN_TYPE_USER = 'USER';
    const LOGIN_TYPE_ADMIN = 'ADMIN';

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

        $validatedRequest = $this->validate(
            $rawBody,
            [
                'username' => ['validators' => ['required']],
                'email' => ['validators' => ['required', 'email']],
                'password' => ['validators' => []],
                'h_password' => ['validators' => []],
                'nama' => ['validators' => ['required']],
                'phone' => ['validators' => ['required', 'digit']],
                'tempat_lahir' => ['validators' => []],
                'tgl_lahir' => ['validators' => []],
                'jenis_kelamin' => ['validators' => []],
                'no_ktp_sim' => ['validators' => []]
            ]
        );

        $pass = AuthHelper::decryptPassword($validatedRequest); //um_user_password & um_user_new_password (bcrypt)
        AuthHelper::validatePassword($pass);
        $nama = $rawBody['nama']; //nama
        $tmpLahir = $rawBody['tempat_lahir']; //tempat_lahir
        $email = $rawBody['email']; //um_user_name & email
        $phone = $rawBody['phone']; //phone
        $tglLahir = !empty($rawBody['tgl_lahir']) && !is_null($rawBody['tgl_lahir']) ? $rawBody['tgl_lahir'] : null;
        $jk = $validatedRequest['jenis_kelamin'];
        $wso2Id = '';
        $oauthId = '';
        $twitterId = '';
        $facebookId = '';
        $googleId = '';
        $noKtp = array_key_exists("no_ktp_sim", $rawBody) ? $rawBody['no_ktp_sim'] : ''; //no_ktp_sim
        $hashed_password = base64_encode(hash("sha256", $pass, true));
        $foto = null;

        //Cek User and phone
        $errMsgs = [];
        if ($this->email_exists($email)) {
            $errMsgs[] = [
                'field' => 'email',
                'message' => 'Email has been used.'
            ];
        }

        if (!UserHelper::validateBlacklistedEmail($email)) {
            $errMsgs[] = [
                'field' => 'email',
                'message' => 'Email is not valid.'
            ];
        }

        if ($this->phone_exists($phone)) {
            $errMsgs[] = [
                'field' => 'phone',
                'message' => 'Phone has been used.'
            ];
        }

        $validateUsername = UserHelper::validateUsername($rawBody["username"]);

        if ($validateUsername == 1) {
            $errMsgs[] = [
                'field' => 'username',
                'message' => 'Username length must be more than 6 characters.'
            ];
        }

        if ($validateUsername == 2) {
            $errMsgs[] = [
                'field' => 'username',
                'message' => 'Username must be consist of Alphanumeric, underscore, dash or dot only.'
            ];
        }

        if ($validateUsername == 3) {
            $errMsgs[] = [
                'field' => 'username',
                'message' => 'Username has been used.'
            ];
        }

        if (!empty($errMsgs)) {
            throw new CustomErrorException(BaseResponse::REGISTRATION_ERROR, "Registrasi User Gagal", $errMsgs);
        }

        $this->checkUsername($rawBody["username"]);


        $foto = $this->uploadProfilePicture($this->request);

        $this->db->begin();
        try {
            $user = new UmUser();
            $user->um_changed_time = date("Y-m-d H:i:s");
            $user->um_tenant_id = -1234;
            $user->um_user_new_password = $this->security->hash($pass);
            $user->um_user_password = $hashed_password;
            $user->nama = $nama;
            $user->tempat_lahir = $tmpLahir;
            $user->tgl_lahir = $tglLahir;
            $user->email = $email;
            $user->um_user_name = $rawBody["username"];
            $user->foto = $foto;
            $user->phone = $phone;
            $user->wso2_id = $wso2Id;
            $user->oauth_id = $oauthId;
            $user->twitter_id = $twitterId;
            $user->facebook_id = $facebookId;
            $user->google_id = $googleId;
            $user->no_ktp_sim = $noKtp;
            $user->jenis_kelamin = $jk;
            $user->is_email_verified = false;
            $user->save();

            $new_user = UmUser::findFirstByEmail($email);

            UserHelper::reqVerifyEmail(
                $new_user,
                $email,
                new MailHelper($this->config["mail_config"]),
                $this->config["web_base_url"]
            );

            // Get default roles
            $roles = Roles::find(['conditions' => 'id = 1']);

            $new_user->setRoles2($roles);

            $this->db->commit();

            $data = new stdClass();
            $data->message = 'Success';
            return new ObjectResponse([
                "success" => true,
                "message" => "Berhasil mendaftarkan user"
            ]);
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    /**
     * @throws CustomErrorException
     */
    private function uploadProfilePicture($request): string
    {
        $fotoFilename = '';

        if ($request->hasFiles()) {
            $uploadedFiles = $request->getUploadedFiles();
            UserHelper::validateProfilePicture($uploadedFiles);

            foreach ($uploadedFiles as $file) {
                if (substr($file->getRealType(), 0, 5) !== "image") {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Registrasi User Gagal", [[
                        "field" => "image",
                        "filename" => $file->getName(),
                        "message" => "Tipe file tidak diizinkan"
                    ]]);
                }
            }

            $random = new \Phalcon\Security\Random();

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

                    $extension = pathinfo($file->getName(), PATHINFO_EXTENSION);
                    if (empty($extension)) {
                        $extension = "." . $extension;
                    } else {
                        $extension = "";
                    }
                    $shortName = $random->uuid() . $extension;
                    $fotoFilename = $shortName;
                    $filename .=  $shortName;
                    $file->moveTo($filename);
                }
            } elseif (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::MINIO) {
                foreach ($uploadedFiles as $file) {
                    $extension = pathinfo($file->getName(), PATHINFO_EXTENSION);
                    if (empty($extension)) {
                        $extension = "." . $extension;
                    } else {
                        $extension = "";
                    }
                    $shortName = $random->uuid() . $extension;

                    /** @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
                        );
                    }

                    $fotoFilename = $shortName;
                }
            }
        }

        return $fotoFilename;
    }

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

        $rawBody = $this->request->getPost();

        $validatedRequest = $this->validate(
            $rawBody,
            [
                'username' => ['validators' => ['required']],
                'email' => ['validators' => ['required', 'email']],
                'password' => ['validators' => []],
                'h_password' => ['validators' => []],
                'nama' => ['validators' => ['required']],
                'phone' => ['validators' => ['required', 'digit']],
                'jenis_kelamin' => ['validators' => []],
                'id_instansi' => ['validators' => []],
                'checkmark_verified' => ['validators' => []]
            ]
        );

        $pass = AuthHelper::decryptPassword($validatedRequest); //um_user_password & um_user_new_password (bcrypt)
        AuthHelper::validatePassword($pass);
        $nama = $rawBody['nama']; //nama
        $tmpLahir = '-';
        $email = $rawBody['email']; //um_user_name & email
        $phone = $rawBody['phone']; //phone
        $jk = $validatedRequest['jenis_kelamin'];
        $wso2Id = '';
        $oauthId = '';
        $twitterId = '';
        $facebookId = '';
        $googleId = '';
        $noKtp = '-'; //no_ktp_sim
        $hashed_password = base64_encode(hash("sha256", $pass, true));
        $foto = array_key_exists('image', $rawBody) ? $rawBody['image'] : null;
        $rawRoles = array_key_exists('roles', $rawBody) ? $rawBody['roles'] : '';
        $roles = explode(',', $rawRoles);

        //Cek User and phone
        $errMsgs = [];
        if ($this->email_exists($email)) {
            $errMsgs[0] = array(
                'field' => 'email',
                'message' => 'Email has been used.'
            );
        }

        if ($this->phone_exists($phone)) {
            $errMsgs[count($errMsgs)] = array(
                'field' => 'phone',
                'message' => 'Phone has been used.'
            );
        }

        if (empty($roles)) {
            $errMsgs[count($errMsgs)] = array(
                'field' => 'roles',
                'message' => 'Roles must present'
            );
        }

        // validasi ketersediaan username
        $validateUsername = UserHelper::validateUsername($rawBody["username"]);
        if ($validateUsername == 1) {
            $errMsgs[] = [
                'field' => 'username',
                'message' => 'Username length must be more than 6 characters.'
            ];
        }

        if ($validateUsername == 2) {
            $errMsgs[] = [
                'field' => 'username',
                'message' => 'Username must be consist of Alphanumeric, underscore, dash or dot only.'
            ];
        }

        if ($validateUsername == 3) {
            $errMsgs[] = [
                'field' => 'username',
                'message' => 'Username has been used.'
            ];
        }

        if (count($errMsgs) > 0) {
            throw new CustomErrorException(BaseResponse::REGISTRATION_ERROR, "Registrasi User Gagal", $errMsgs);
        }

        $this->checkUsername($rawBody["username"]);

        $foto = $this->uploadProfilePicture($this->request);

        $user = new UmUser();
        $user->um_changed_time = date("Y-m-d H:i:s");
        $user->um_tenant_id = -1234;
        $user->um_user_new_password = $this->security->hash($pass);
        $user->um_user_password = $hashed_password;
        $user->nama = $nama;
        $user->tempat_lahir = $tmpLahir;
        $user->email = $email;
        $user->um_user_name = $rawBody["username"];
        $user->foto = $foto;
        $user->phone = $phone;
        $user->wso2_id = $wso2Id;
        $user->oauth_id = $oauthId;
        $user->twitter_id = $twitterId;
        $user->facebook_id = $facebookId;
        $user->google_id = $googleId;
        $user->no_ktp_sim = $noKtp;
        $user->jenis_kelamin = $jk;
        if (!empty($validatedRequest['id_instansi']) && $validatedRequest['id_instansi'] != null) $user->id_instansi = $validatedRequest['id_instansi'];
        $user->id_ins = $jk;
        $user->checkmark_verified = (empty($validatedRequest['checkmark_verified']) || $validatedRequest['checkmark_verified'] == null) ? 0 : $validatedRequest['checkmark_verified'];
        $user->save();

        $new_user = UmUser::findFirstByEmail($email);

        $roles = Roles::find([
            'conditions' => 'id in ({roles:array})',
            'bind' => ['roles' => $roles]
        ]);

        $new_user->setRoles2($roles);

        $data = new stdClass();
        $data->message = 'Success';
        return new ObjectResponse([
            "success" => true,
            "message" => "Berhasil mendaftarkan user"
        ]);
    }

    private function email_exists($email)
    {
        $result = UmUser::query()
            ->where('lower(email) = :email: and is_active = true')
            ->bind(['email' => strtolower($email)])
            ->limit(1)
            ->execute();
        return $result->count() == 1;
    }

    private function phone_exists($phone)
    {
        $result = UmUser::query()
            ->where('phone = :phone: and is_active = true')
            ->bind(['phone' => $phone])
            ->limit(1)
            ->execute();
        return $result->count() == 1;
    }

    private function checkUsername($value)
    {
        $blacklistedUsername = [
            'JAGA',
            'JAGA.ID',
            'JAGA-ID',
            'PENJAGA',
            'PEN-JAGA',
            'PEN.JAGA',
            'ADMINJAGA',
            'ADMIN-JAGA ',
            'ADMIN.JAGA',
            'WWW.JAGA.ID',
            'WWW-JAGA-ID',
            'WWWJAGAID'
        ];

        foreach ($blacklistedUsername as $username) {
            if (strtolower(trim($username)) == strtolower(trim($value))) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Invalid username');
            }
        }
    }

    public function facebookOAuth()
    {
        $rawBody = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $rawBody,
            [
                'access_token' => ['validators' => ['required']],
                'facebook_id' => ['validators' => ['required']],
                'nama' => ['validators' => ['required']],
                'email' => ['validators' => ['required', 'email']],
                'change_profile_picture' => ['validators' => ['digit']], // 1 : change, 2 : not change
                'platform' => ['validators' => []],
            ]
        );

        $urlPropic = array_key_exists("url_propic", $rawBody) ? $rawBody["url_propic"] : null;

        $userProfile = FacebookHelper::checkToken($rawBody["access_token"]);
        if ($userProfile["data"]["is_valid"]) {
            if ($userProfile["data"]["user_id"] == $rawBody["facebook_id"]) {
                $change_profile_picture = self::CHANGE_PROFILE_PICTURE;
                if (array_key_exists("change_profile_picture", $rawBody)) {
                    $change_profile_picture = $rawBody["change_profile_picture"];
                }

                $data = $this->processOAuthData($rawBody["facebook_id"], $rawBody["email"], $rawBody["access_token"], $rawBody["nama"], 1, $this->getPropicFromNetwork($rawBody["email"], $urlPropic, $change_profile_picture), $validatedRequest['platform']);
                return new ObjectResponse(
                    [
                        "success" => true,
                        "data" => $data
                    ]
                );
            } else {
                return new ObjectResponse(
                    [
                        "success" => false,
                        "message" => "Token and facebook id are not match."
                    ]
                );
            }
        } else {
            return new ObjectResponse(
                [
                    "success" => false,
                    "message" => "Invalid token."
                ]
            );
        }
    }

    public function googleOAuth()
    {
        $rawBody = $this->request->getJsonRawBody(true);

        $validatedRequest = $this->validate(
            $rawBody,
            [
                'email' => ['validators' => ['required', 'email']],
                'token' => ['validators' => ['required']],
                'google_id' => ['validators' => ['required']],
                'nama' => ['validators' => ['required']],
                'change_profile_picture' => ['validators' => ['digit']], // 1 : change, 2 : not change
                'platform' => ['validators' => []],
            ]
        );

        $osType = array_key_exists("ot", $rawBody) ? $rawBody["ot"] : 0;

        $client = new Google_Client();
        Jwt::$leeway = 6000;

        if ($osType == 0) {
            $client->setClientId($this->config["google_client_id"]);
            $client->setClientSecret($this->config["google_client_secret"]);
            $client->setApplicationName("project-177749671546");
        } else {
            $client->setClientId($this->config["google_ios_client_id"]);
        }

        $urlPropic = array_key_exists("url_propic", $rawBody) ? $rawBody["url_propic"] : null;

        $client->setIncludeGrantedScopes(true);

        $userData = $client->verifyIdToken($rawBody["token"]);
        if ($userData) {
            if ($userData["sub"] == $rawBody["google_id"]) {
                $change_profile_picture = self::CHANGE_PROFILE_PICTURE;
                if (array_key_exists("change_profile_picture", $rawBody)) {
                    $change_profile_picture = $rawBody["change_profile_picture"];
                }

                $data = $this->processOAuthData($rawBody["google_id"], $rawBody["email"], $rawBody["token"], $rawBody["nama"], 0, $this->getPropicFromNetwork($rawBody["email"], $urlPropic, $change_profile_picture), $validatedRequest['platform']);
                return new ObjectResponse(
                    [
                        "success" => true,
                        "data" => $data
                    ]
                );
            } else {
                return new ObjectResponse(
                    [
                        "success" => false,
                        "message" => "Token, email and google id are not match."
                    ]
                );
            }
        } else {
            return new ObjectResponse(
                [
                    "success" => false,
                    "message" => "Invalid token."
                ]
            );
        }
    }

    public function twitterOAuth()
    {
        $rawBody = $this->request->getJsonRawBody(true);

        $validatedRequest = $this->validate(
            $rawBody,
            [
                'email' => ['validators' => ['required', 'email']],
                'access_token' => ['validators' => ['required']],
                'access_token_secret' => ['validators' => ['required']],
                'twitter_id' => ['validators' => ['required']],
                'nama' => ['validators' => ['required']],
                'change_profile_picture' => ['validators' => ['digit']], // 1 : change, 2 : not change
                'platform' => ['validators' => []],
            ]
        );

        $urlPropic = array_key_exists("url_propic", $rawBody) ? $rawBody["url_propic"] : null;

        $userData = TwitterHelper::checkToken(
            $this->config["twitter_consumer_key"],
            $this->config["twitter_consumer_secret"],
            $rawBody["access_token"],
            $rawBody["access_token_secret"]
        );
        if ($userData) {
            if ($userData->id == $rawBody["twitter_id"]) {
                $change_profile_picture = self::CHANGE_PROFILE_PICTURE;
                if (array_key_exists("change_profile_picture", $rawBody)) {
                    $change_profile_picture = $rawBody["change_profile_picture"];
                }

                $data = $this->processOAuthData($rawBody["twitter_id"], $rawBody["email"], $rawBody["access_token_secret"], $rawBody["nama"], 2, $this->getPropicFromNetwork($rawBody["email"], $urlPropic, $change_profile_picture), $validatedRequest['platform']);
                return new ObjectResponse(
                    [
                        "success" => true,
                        "data" => $data
                    ]
                );
            } else {
                return new ObjectResponse(
                    [
                        "success" => false,
                        "message" => "Token, email and twitter id are not match."
                    ]
                );
            }
        } else {
            return new ObjectResponse(
                [
                    "success" => false,
                    "message" => "Invalid token."
                ]
            );
        }
    }

    public function appleOAuth()
    {
        $rawBody = $this->request->getJsonRawBody(true);

        $validatedRequest = $this->validate(
            $rawBody,
            [
                'email' => ['validators' => ['email']],
                'nama' => ['validators' => []],
                'apple_id' => ['validators' => ['required']],
                'authorization_code' => ['validators' => ['required']],
                'identity_token' => ['validators' => ['required']],
                'platform' => ['validators' => []]
            ]
        );

        $userData = AppleHelper::checkToken($validatedRequest['authorization_code']);
        // $userData['response_code'] = "200";
        // $userData['id_token'] = "eyJraWQiOiI4NkQ4OEtmIiwiYWxnIjoiUlMyNTYifQ.eyJpc3MiOiJodHRwczovL2FwcGxlaWQuYXBwbGUuY29tIiwiYXVkIjoiY29tLmtway5KYWdhIiwiZXhwIjoxNjA0OTkxNjYyLCJpYXQiOjE2MDQ5MDUyNjIsInN1YiI6IjAwMDQ3MS5hOWJjYzc1NDNlZWI0MDNjYTM5NDhmNmFmYTA4ZTFjOS4wNjQxIiwiYXRfaGFzaCI6InBsNnFFSEVJSENfLVJoRDdsZGM0NWciLCJlbWFpbCI6Indpc251LmZlcmRpYW5Aa3BrLmdvLmlkIiwiZW1haWxfdmVyaWZpZWQiOiJ0cnVlIiwiYXV0aF90aW1lIjoxNjA0OTA1MjA4LCJub25jZV9zdXBwb3J0ZWQiOnRydWV9.T-3y8E774mNhOxa4RbWKBeCYFiXbAD-v0fJbMisL6gDtLaOslmjmyz_XaQKgwBq8lnMqirvr-0aedAncmA9E_MnQtrucBlxLy_ppDPvh0AQIA81lsmNbqGmJ1X5MAM1e7CEPZl6Ohi-cRbfa8UNY5eKWk7NUjoGgLleJ1EbvrRwN0y-BlKwsBKHfzE9k1_-Lmz9oYiFK-nOsq61A4na85z5OzEIAAzWgEb5kw4H6wQW475vTZer5T9k51Amb34LWkDR3DfDEDbmR_S9K5eiQjNDwRSQuscUk3cq_SZyE8-XJk4PgNkSPxnkKiuyWpSFv7nk4hkRTMa7CWfe9I4YB6w";

        if ($userData['response_code'] == "200") {
            //if ($userData['id_token'] == $validatedRequest['identity_token']) {
            if (1 == 1) {
                $jwt = AppleHelper::decodeIdentityToken($userData['id_token']);
                $data = $this->processOAuthData(
                    $validatedRequest['apple_id'],
                    $jwt->email,
                    $userData['refresh_token'],
                    $validatedRequest['nama'],
                    3,
                    null,
                    $validatedRequest['platform']
                );
                return new ObjectResponse(
                    [
                        "success" => true,
                        "data" => $data
                    ]
                );
            } else {
                echo $userData['id_token'] . "\n";
                echo $validatedRequest['identity_token'];
                return new ObjectResponse(
                    [
                        "success" => false,
                        "message" => "Token did not match"
                    ]
                );
            }
        } else {
            return new ObjectResponse(
                [
                    "success" => false,
                    "message" => "Authentication Failed"
                ]
            );
        }
    }

    public function kemdikbudOauth()
    {
        $rawBody = $this->request->getJsonRawBody(true);

        $validatedRequest = $this->validate(
            $rawBody,
            [
                'token' => ['validators' => ['required']]
            ]
        );
        $token = $validatedRequest["token"];

        try {
            $this->db->begin();
            $client = new SSOKemdikbudClient($this->config["sso"]["kemdikbud"]);
            $client->setLogger($this->logger);
            $isValidToken = $client->checkToken($token);
            if (array_key_exists("tokenStatus", $isValidToken) && $isValidToken["tokenStatus"] == 1) {
                $userdata = $client->getUsername($token);
                $data = $this->processOAuthDataKemdikbud(
                    $userdata["pengguna_id"],
                    $userdata["npsn"] ?? $userdata["NPSN"],
                    $userdata["username"],
                    $token,
                    $userdata["nama"]
                );

                $this->db->commit();

                return new ObjectResponse(
                    [
                        "success" => true,
                        "data" => $data
                    ]
                );
            } else {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Otentikasi gagal: Token tidak valid");
            }
        }
        catch (CustomErrorException $e) {
            $this->db->rollback();
            return new ObjectResponse(
                [
                    "success" => false,
                    "message" => $e->getMessages()
                ]
            );
        }
        catch (Exception $e) {
            $this->db->rollback();
            return new ObjectResponse(
                [
                    "success" => false,
                    "message" => $e->getMessage()
                ]
            );
        }
    }

    private function processOAuthData($id, $email, $password, $nama, $oauthType, $foto = null, $platform = null)
    {
        $user = UmUser::find(
            [
                'lower(email) = :email: and is_active = true',
                "bind" => [
                    "email" => strtolower($email)
                ]
            ]
        );
        if ($user->count() == 0) {
            $hashed_password = base64_encode(hash("sha256", $password, true));
            $newUser = new UmUser();
            $newUser->email = $email;
            $newUser->um_user_password = $hashed_password;
            $newUser->um_changed_time = date("Y-m-d H:i:s");
            $newUser->um_tenant_id = -1234;
            $newUser->nama = $nama;
            $newUser->um_user_name = UserHelper::generateUsernameForEmail($email, $nama);
            $newUser->is_banned = false;
            $newUser->is_active = true;
        } else {
            $newUser = $user[0];
        }

        if ($oauthType == 0) {
            $newUser->google_id = $id;
        } elseif ($oauthType == 1) {
            $newUser->facebook_id = $id;
        } elseif ($oauthType == 2) {
            $newUser->twitter_id = $id;
        } elseif ($oauthType == 3) {
            if (empty($newUser->nama)) {
                if (empty($nama)) {
                    $newUser->nama = substr($email, 0, strpos($email, "@"));
                } else {
                    $newUser->nama = $nama;
                }
            }

            $newUser->apple_id = $id;
        }
        $newUser->foto = $foto;
        $newUser->save();

        if ($user->count() == 0) {
            $roles = Roles::find(['conditions' => 'id = 1 ']);
            $newUser->setRoles2($roles);

            $user = UmUser::find(
                [
                    'email = :email:',
                    "bind" => [
                        "email" => $email
                    ]
                ]
            );
        }
        return $this->generateJwtToken($user[0], JwtData::TYPE_USER, $platform);
    }

    private function processOAuthDataKemdikbud($id, $npsn, $email, $password, $nama)
    {
        $user = UmUser::find(
            [
                'um_user_name = :npsn: and is_active = true',
                "bind" => [
                    "npsn" => $npsn
                ]
            ]
        );
        if ($user->count() == 0) {
            \UserHelper::validateEmailAvailability($email);
            $hashed_password = base64_encode(hash("sha256", $password, true));
            $newUser = new UmUser();
            $newUser->email = $email;
            $newUser->um_user_password = $hashed_password;
            $newUser->um_changed_time = date("Y-m-d H:i:s");
            $newUser->um_tenant_id = -1234;
            $newUser->nama = $nama;
            $newUser->um_user_name = $npsn;
            $newUser->is_banned = false;
            $newUser->is_active = true;
            $newUser->save();

            $roles = ['pengguna'];
            $sekolah = Sekolah::findFirst("npsn = '" . $npsn . "'");
            if ($sekolah) {
                $roles[] = 'user_sekolah_pak';
            }

            $roles = Roles::find([
                'conditions' => 'name in ({roles:array})',
                'columns' => 'id',
                'bind' => [
                    'roles' => $roles
                ]
            ])->toArray();
            $roleIds = [];
            foreach ($roles as $role) {
                $roleIds[] = $role['id'];
            }
            $newUser->syncRoles($roleIds);

            if ($sekolah) {
                $newUser->syncSekolahWithNpsn([$npsn]);
            }

            $user = UmUser::find(
                [
                    'um_user_name = :npsn: and is_active = true',
                    "bind" => [
                        "npsn" => $npsn
                    ]
                ]
            );
        }

        $newUser = $user[0];

        $response = $this->generateJwtToken($newUser, JwtData::TYPE_USER);
        $response->diff_email = $newUser->email != $email;
        return $response;
    }

    private function generateJwtToken($user, $loginType = self::LOGIN_TYPE_USER, $platform = null)
    {
        if (!empty($user->is_banned)) {
            throw new CustomErrorException(BaseResponse::USER_IS_BANNED, 'User is banned');
        }

        $data = new stdClass();
        $data->uuid = $user->uuid_user;
        $data->is_banned = $user->is_banned;
        $data->user_id = $user->um_id;
        $random = new \Phalcon\Security\Random();
        $tokenId = $random->uuid();
        $refreshToken = $random->uuid();
        if ($platform == JwtData::PLATFORM_WEB) {
            $roles = $user->getRoles();
            $hasAdminRole = $this->hasAdminRole($roles);
            if ($hasAdminRole) {
                $exp = time() + 86400; //in seconds. 86400s = 1 day
            } else {
                $exp = time() + 3600; //in seconds. 1 hour = 60 minutes * 60 seconds
            }
        } else {
            $exp = time() + 60 * 86400;
        }
        $refreshExp = $exp + 120 * 86400;

        $tokenPayload = [
            "jti" => $tokenId,
            "sub" => $user->email,
            "refresh_token" => $refreshToken,
            "exp" => $exp,
            "refesh_token_exp" => $refreshExp,
            "scope" => implode(' ', $user->getScopes())
        ];

        $data->token = JWT::encode($tokenPayload, $this->config['jwt_private_key'], 'RS256');
        $data->refresh_token = $refreshToken;
        $data->email = $user->email;
        $data->um_require_change = $user->um_require_change;

        $jwtData = new JwtData();
        $jwtData->user_id = $user->um_id;
        $jwtData->token = $tokenId;
        $jwtData->refresh_token = $refreshToken;
        $jwtData->refresh_token_exp = $refreshExp;
        $jwtData->client = $this->request->getHeader("User-Agent");
        $jwtData->ip_address = JwtData::retrieveIpAddress($_SERVER);
        $jwtData->login_type = $loginType;
        $jwtData->platform = $platform;
        $jwtData->save();

        return $data;
    }

    public function kemdikbudOauthChangeEmail()
    {
        $this->checkIsAuthenticated();
        $rawBody = $this->request->getJsonRawBody(true);

        $validatedRequest = $this->validate(
            $rawBody,
            [
                'token' => ['validators' => ['required']]
            ]
        );
        $token = $validatedRequest["token"];

        try {
            $this->db->begin();
            $client = new SSOKemdikbudClient($this->config["sso"]["kemdikbud"]);
            $client->setLogger($this->logger);
            $isValidToken = $client->checkToken($token);
            $data = [];
            if (array_key_exists("tokenStatus", $isValidToken) && $isValidToken["tokenStatus"] == 1) {
                $userdata = $client->getUsername($token);
                $npsn = $userdata["npsn"] ?? $userdata["NPSN"];
                $newEmail = $userdata["username"];
                $user = UmUser::findFirst(
                    [
                        'um_user_name = :npsn: and is_active = true',
                        "bind" => [
                            "npsn" => $npsn
                        ]
                    ]
                );
                if ($user) {
                    if ($this->shared->user->um_id != $user->um_id) {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Anda tidak berhak melakukan aksi ini!");
                    }
                    \UserHelper::validateEmailAvailability($newEmail);
                    $oldData = LogModeration::extractUmUser($user->um_id);
                    $user->email = $newEmail;
                    $res = $user->save();
                    if (!$res) {
                        throw new CustomErrorException(BaseResponse::INVALID_REQUEST, $user->getMessages());
                    }

                    $newData = LogModeration::extractUmUser($user->um_id);
                    $action = [LogModeration::ACTION_CHANGEEMAIL_UMUSER];
                    LogModeration::loggedUmUser(
                        $action,
                        LogModeration::ACTIONMENU_PROFILE,
                        $user->um_id,
                        $oldData,
                        $newData,
                        $this->shared->user->um_id
                    );

                    $this->rawQuery(
                        "update jaga.jwt_data set status = :status_revoked where status = :status_active and user_id = :um_id",
                        [
                            "status_revoked" => JwtData::STATUS_REVOKED,
                            "status_active" => JwtData::STATUS_ACTIVE,
                            "um_id" => $user->um_id,
                        ]
                    );

                    $data = $this->generateJwtToken($user, JwtData::TYPE_USER);
                }

                $this->db->commit();

                return new ObjectResponse(
                    [
                        "success" => true,
                        "data" => $data
                    ]
                );
            } else {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Otentikasi gagal: Token tidak valid");
            }
        }
        catch (CustomErrorException $e) {
            $this->db->rollback();
            return new ObjectResponse(
                [
                    "success" => false,
                    "message" => $e->getMessages()
                ]
            );
        }
        catch (Exception $e) {
            $this->db->rollback();
            return new ObjectResponse(
                [
                    "success" => false,
                    "message" => $e->getMessage()
                ]
            );
        }
    }

    public function loginAction()
    {
        try {
            if ($this->request->isPost()) {
                $validatedRequest = $this->validate(
                    $this->request->getJsonRawBody(true),
                    [
                        'username' => ['validators' => ['required']],
                        'password' => ['validators' => []],
                        'h_password' => ['validators' => []],
                        'platform' => ['validators' => []],
                    ]
                );
                $username = $validatedRequest['username'];
                $pass = AuthHelper::decryptPassword($validatedRequest);
            } else {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Invalid request");
            }

            $result = UmUser::query()
                ->leftJoin(UsersSekolah::class, UsersSekolah::class . '.um_id = ' . UmUser::class . '.um_id')
                ->where("(lower(UmUser.um_user_name) = :username: or lower(UmUser.email) = :username: or " . UsersSekolah::class . ".npsn = :username:) AND UmUser.is_active = true")
                ->bind(['username' => strtolower($username)])
                ->execute();

            $success = false;
            $user = false;
            $captchaRequired = false;
            if ($result->count() > 0) {
                /** @var UmUser $user */
                $user = $result[0];
                if (!$user->is_email_verified) {
                    throw new CustomErrorException(BaseResponse::INVALID_ACTION, "Anda perlu melakukan aktivasi email terlebih dahulu.");
                }
                $loginLimit = AppConfig::getValueByKode("TRY_LOGIN_LIMIT");
                $captchaLoginLimit = AppConfig::getValueByKode("CAPTCHA_LOGIN_LIMIT");
                $timeLoginLimit = AppConfig::getValueByKode("TIME_LOGIN_LIMIT");
                if ($user->try_login >= $captchaLoginLimit && $user->try_login <= $loginLimit) {
                    $captcha = $this->validate(
                        $this->request->getJsonRawBody(true),
                        [
                            "captcha_uuid" => ['validators' => ['required']],
                            "captcha_answer" => ['validators' => ['required']]
                        ]
                    );
                    $captchaUuid = $captcha['captcha_uuid'];
                    $captchaAnswer = $captcha['captcha_answer'];
                    $this->validateCaptcha($captchaUuid, $captchaAnswer);
                }
                if ($user->try_login >= $loginLimit) {
                    $now = strtotime(DateHelper::getNowTimezoned());
                    $lastLogin = $user->last_try_login ? strtotime($user->last_try_login, $now) : $now;
                    $sub = $now - $lastLogin;
                    if ($sub > $timeLoginLimit) {
                        $user->try_login = $captchaLoginLimit - 1;
                        $user->last_try_login = DateHelper::getNowTimezoned();
                        $user->save();
                    } else {
                        $dataResp = new stdClass();
                        $dataResp->success = false;
                        $dataResp->message = "Anda telah mencapai batas percobaan login. Mohon menunggu untuk beberapa saat.";
                        $dataResp->code = 101;
                        return new ObjectResponse($dataResp);
                    }
                }

                if ($user->sumber == UmUser::SUMBER_KORSUPGAH && $user->migration_status != UmUser::MIGRATION_STATUS_PASS_CHANGED) {
                    $korsupgah_password_hasher = new KorsupgahPasswordHash();

                    $success = $korsupgah_password_hasher->CheckPassword($pass, $user->um_user_korsupgah_password);

                    if ($success) {
                        $user->um_user_new_password = $this->security->hash($pass);
                        $user->migration_status = UmUser::MIGRATION_STATUS_PASS_CHANGED;
                        $user->try_login = 0;
                        $user->last_try_login = DateHelper::getNowTimezoned();
                        $user->last_login = DateHelper::getNowTimezoned();
                        $user->save();
                    }
                } else {
                    if (is_null($user->um_user_new_password)) {
                        $hashed_password = base64_encode(hash("sha256", $pass, true));
                        if ($user->um_user_password == $hashed_password) {
                            $user->um_user_new_password = $this->security->hash($pass);
                            $user->try_login = 0;
                            $user->last_try_login = DateHelper::getNowTimezoned();
                            $user->last_login = DateHelper::getNowTimezoned();
                            $user->save();
                            $success = true;
                        }
                    } else {
                        $success = $this->security->checkHash($pass, $user->um_user_new_password);
                        if ($success) {
                            $user->try_login = 0;
                            $user->last_try_login = DateHelper::getNowTimezoned();
                            $user->last_login = DateHelper::getNowTimezoned();
                            $user->save();
                        } else {
                            $user->try_login = $user->try_login + 1;
                            $user->last_try_login = DateHelper::getNowTimezoned();
                            $user->save();
                            $captchaRequired = $user->try_login >= $captchaLoginLimit && $user->try_login <= $loginLimit;
                        }
                    }
                }
            }
            $dataResp = new stdClass();
            $dataResp->success = $success;
            if ($success && $user) {
                $data = $this->generateJwtToken($user, JwtData::TYPE_USER, $validatedRequest['platform']);
                $dataResp->data = $data;
            } else {
                $dataResp->message = 'Username atau Password tidak sesuai';
                if ($captchaRequired) {
                    $dataResp->code = 101;
                }
            }
            return new ObjectResponse($dataResp);
        } catch (AuthException $e) {
            $this->flash->error($e->getMessage());
        }
    }

    private function validateCaptcha($uuid, $answer)
    {
        $captchas = Captcha::find([
            'conditions' => 'uuid = :uuid:',
            'bind' => [
                'uuid' => $uuid
            ]
        ]);

        if (empty($captchas) || count($captchas) < 1) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Captcha not found");
        }

        $captcha = $captchas[0];

        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($answer)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Captcha doesn't match");
        }

        $captcha->markUsed();
    }

    public function refresh_token()
    {
        $postParams = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $this->request->getJsonRawBody(true),
            [
                'refresh_token' => ['validators' => ['required']]
            ]
        );

        $tokenData = JwtData::find(
            [
                "refresh_token = :refresh_token:",
                "bind" => [
                    "refresh_token" => $postParams["refresh_token"]
                ]
            ]
        );

        if ($tokenData->count() == 0) {
            throw new CustomErrorException(BaseResponse::INVALID_REFRESH_TOKEN, 'Invalid refresh token');
        }

        $jwtData = $tokenData[0];

        $user = $jwtData->UmUser;

        if (empty($user)) {
            throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, 'User not found');
        }

        if (!empty($user->is_banned)) {
            throw new CustomErrorException(BaseResponse::USER_IS_BANNED, 'User is banned');
        }

        // status 1 = revoked
        if ($jwtData->status == 1) {
            throw new CustomErrorException(BaseResponse::TOKEN_EXPIRED, 'Refresh token is revoked');
        }

        if ($jwtData->refresh_token_exp < time()) {
            throw new CustomErrorException(BaseResponse::TOKEN_EXPIRED, 'Refresh token is expired');
        }

        if ($jwtData->platform == JwtData::PLATFORM_WEB) {
            $roles = $user->getRoles();
            $hasAdminRole = $this->hasAdminRole($roles);
            if ($hasAdminRole) {
                $exp = time() + 86400; //in seconds. 86400s = 1 day
            } else {
                $exp = time() + 3600; //in seconds
            }
        } else {
            $exp = time() + 7 * 86400;
        }
        $refreshExp = $exp + 23 * 86400;
        $tokenPayload = [
            "jti" => $jwtData->token,
            "sub" => $jwtData->umUser->email,
            "refresh_token" => $jwtData->refresh_token,
            "exp" => $exp,
            "refesh_token_exp" => $refreshExp,
            "scope" => implode(' ', $jwtData->umUser->getScopes())
        ];

        $token = JWT::encode($tokenPayload, $this->config['jwt_private_key'], 'RS256');

        $user->last_login = DateHelper::getNowTimezoned();
        $user->save();

        $jwtData->exp = $exp;
        $jwtData->refresh_token_exp = $refreshExp;
        $jwtData->updated_at = "now()";
        $jwtData->ip_address = JwtData::retrieveIpAddress($_SERVER);
        $jwtData->save();

        return new ObjectResponse(
            [
                "success" => true,
                "data" => [
                    "token" => $token,
                    "refresh_token" => $jwtData->refresh_token
                ]
            ]
        );
    }

    public function profilePicture($uuid)
    {
        $resUser = UmUser::find(
            [
                "columns" => "foto",
                "uuid_user = :uuid:",
                "bind" => [
                    "uuid" => $uuid
                ]
            ]
        )
            ->toArray();
        if (count($resUser) > 0) {
            $foto = $resUser[0]["foto"];
            if (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::MINIO) {
                $url = $this->minio->base_get_public_url('users_profpic' . DIRECTORY_SEPARATOR . $foto, $foto, false);
                $response = new \Phalcon\Http\Response();
                return $response->redirect($url, true, 302);
            } elseif (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::LOCAL) {
                $filename = getenv("USER_IMAGE_FOLDER") . DIRECTORY_SEPARATOR . $foto;

                if (empty($foto) || is_dir($filename) || !file_exists($filename)) {
                    $filename = __DIR__ . DIRECTORY_SEPARATOR . '..' . DIRECTORY_SEPARATOR . '..' . DIRECTORY_SEPARATOR . "default_profile_picture.png";
                }
            }

            return MediaHelper::fileResponse($filename);
        }
    }

    public function adminList()
    {
        $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;
        $getParams['is_active'] = $getParams['is_active'] ?? 1;
        $getParams['roles'] = array_key_exists('roles', $getParams) ? $getParams['roles'] : null; // comma separated roles

        $allowedOrder = ['um_id', 'um_user_name', 'nama', 'email', 'last_login'];
        $mapOrder = [
            'um_id' => 'u.um_id',
            'um_user_name' => 'u.um_user_name',
            'nama' => 'u.nama',
            'email' => 'u.email',
            'last_login' => 'u.last_login'
        ];

        $validatedParams = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => [],
                'order_by' => ['order_by' => [[
                    'name' => 'inclusion_in',
                    'params' => $allowedOrder
                ]]],
                'order_direction' => ['order_direction' => []],
                'trashed' => ['validators' => [[
                    'name' => 'inclusion_in',
                    'params' => [0, 1]
                ]]],
                'roles' => ['validators' => []]
            ]
        );

        $conditions = '(lower(u.email) like :keyword OR lower(u.nama) like :keyword OR lower(u.um_user_name) like :keyword)';
        $bind = ['keyword' => "%" . strtolower($validatedParams['keyword']) . "%"];

        if (!$getParams['trashed'] && $getParams['is_active']) {
            $conditions .= " AND u.is_active = true";
        } elseif (!$getParams['is_active']) {
            $conditions .= " AND u.is_active = false";
        }

        $limit = $validatedParams['limit'];
        $offset = $validatedParams['offset'];
        $order = $mapOrder[$validatedParams['order_by']] . ' ' . $validatedParams['order_direction'];

        if (!is_null($getParams['roles'])) {
            $conditions .= " AND r.name = ANY(:roles)";
            $roles = explode(";", $getParams['roles']);
            $bind['roles'] = '{' . implode(',', $roles) . '}';
        }

        $q = "
            SELECT 
                distinct u.um_id,
                u.um_user_name,
                u.email,
                u.nama,
                u.tempat_lahir,
                u.foto,
                u.phone,
                u.uuid_user,
                u.tgl_lahir,
                u.is_banned,
                u.jenis_kelamin,
                u.checkmark_verified,
                u.last_login,
                u.id_instansi
            FROM jaga.um_user u
            --LEFT JOIN (SELECT user_id, max(updated_at) as last_login FROM jaga.jwt_data WHERE (login_type IS NULL OR login_type = 'USER') GROUP BY user_id) j ON j.user_id = u.um_id
            LEFT JOIN jaga.users_roles ur ON ur.user_id = u.um_id
            LEFT JOIN jaga.roles r ON r.id = ur.role_id
            WHERE $conditions
            ORDER BY $order NULLS LAST
            LIMIT $limit OFFSET $offset
        ";
        $users = $this->rawQuery($q, $bind);
        $result = [];
        foreach ($users as $user) {
            $object = new UmUser();
            $object->assign($user);
            $row = $object->baseToArray();
            $row['last_login'] = $user['last_login'];
            array_push($result, $row);
        }

        $q = "
            SELECT count(distinct u.um_id) as total
            FROM jaga.um_user u
            LEFT JOIN jaga.users_roles ur ON ur.user_id = u.um_id
            LEFT JOIN jaga.roles r ON r.id = ur.role_id
            WHERE $conditions
        ";
        $total = $this->rawQuery($q, $bind)[0]['total'];

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

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

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

        $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;
        $getParams['is_active'] = $getParams['is_active'] ?? 1;
        $getParams['roles'] = array_key_exists('roles', $getParams) ? $getParams['roles'] : null; // comma separated roles

        $allowedOrder = ['um_id', 'um_user_name', 'nama', 'email', 'last_login'];
        $mapOrder = [
            'um_id' => 'u.um_id',
            'um_user_name' => 'u.um_user_name',
            'nama' => 'u.nama',
            'email' => 'u.email',
            'last_login' => 'u.last_login'
        ];

        $validatedParams = $this->validate(
            $getParams,
            [
                'keyword' => [],
                'order_by' => ['order_by' => [[
                    'name' => 'inclusion_in',
                    'params' => $allowedOrder
                ]]],
                'order_direction' => ['order_direction' => []],
                'trashed' => ['validators' => [[
                    'name' => 'inclusion_in',
                    'params' => [0, 1]
                ]]],
                'roles' => ['validators' => []]
            ]
        );

        $conditions = '(lower(u.email) like :keyword OR lower(u.nama) like :keyword OR lower(u.um_user_name) like :keyword)';
        $bind = ['keyword' => "%" . strtolower($validatedParams['keyword']) . "%"];

        if (!$getParams['trashed'] && $getParams['is_active']) {
            $conditions .= " AND u.is_active = true";
        } elseif (!$getParams['is_active']) {
            $conditions .= " AND u.is_active = false";
        }

        $order = $mapOrder[$validatedParams['order_by']] . ' ' . $validatedParams['order_direction'];

        if (!is_null($getParams['roles'])) {
            $conditions .= " AND r.name IN (:roles)";
            $roles = explode(";", $getParams['roles']);
            $bind['roles'] = implode(",", $roles);
        }

        $q = "
            SELECT 
            distinct u.um_id,
            u.um_user_name,
            u.email,
            u.nama,
            u.tempat_lahir,
            u.foto,
            u.phone,
            u.uuid_user,
            u.tgl_lahir,
            u.is_banned,
            u.jenis_kelamin,
            u.checkmark_verified,
            u.last_login,
            u.id_instansi
            FROM jaga.um_user u
            --LEFT JOIN (SELECT user_id, max(updated_at) as last_login FROM jaga.jwt_data WHERE (login_type IS NULL OR login_type = 'USER') GROUP BY user_id) j ON j.user_id = u.um_id
            LEFT JOIN jaga.users_roles ur ON ur.user_id = u.um_id
            LEFT JOIN jaga.roles r ON r.id = ur.role_id
            WHERE $conditions
            ORDER BY $order NULLS LAST
        ";
        $users = $this->rawQuery($q, $bind);

        $userRoles = [];
        foreach ($users as $user) {
            $roles = $this->rawQuery("
            SELECT r.name
            FROM jaga.users_roles ur
            JOIN jaga.roles r ON r.id = ur.role_id
            WHERE ur.user_id = :user_id
            ORDER BY r.name
            ", ['user_id' => $user['um_id']]);

            $roleNames = [];
            foreach ($roles as $role) {
                $roleNames[] = $role['name'];
            }
            $userRoles[$user['um_id']] = implode(', ', $roleNames);
        }

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

        $sheet->setCellValue("A1", 'Daftar Pengguna');
        $sheet->mergeCells('A1:J1');
        $sheet->getStyle('A1')->getFont()->setBold(true);
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $sheet->setCellValue("A3", 'No');
        $sheet->setCellValue("B3", 'ID');
        $sheet->setCellValue("C3", 'Username');
        $sheet->setCellValue("D3", 'Nama');
        $sheet->setCellValue("E3", 'Email');
        $sheet->setCellValue("F3", 'Tempat Lahir');
        $sheet->setCellValue("G3", 'Tanggal Lahir');
        $sheet->setCellValue("H3", 'No. Telepon');
        $sheet->setCellValue("I3", 'Status');
        $sheet->setCellValue("J3", 'Roles');

        $sheet->getStyle("A3:J3")->getFont()->setBold(true);
        $sheet->getStyle("A3:J3")->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

        $rowNum = 3;
        $num = 1;

        foreach ($users as $user) {
            $rowNum++;
            $sheet->setCellValue("A$rowNum", $num++);
            $sheet->setCellValue("B$rowNum", $user['um_id']);
            $sheet->setCellValue("C$rowNum", $user['um_user_name']);
            $sheet->setCellValue("D$rowNum", $user['nama']);
            $sheet->setCellValue("E$rowNum", $user['email']);
            $sheet->setCellValue("F$rowNum", $user['tempat_lahir']);
            $sheet->setCellValue("G$rowNum", $user['tgl_lahir']);
            $sheet->setCellValue("H$rowNum", $user['phone']);

            $status = $user['is_banned'] ? 'Banned' : 'Active';
            $sheet->setCellValue("I$rowNum", $status);
            $sheet->setCellValue("J$rowNum", $userRoles[$user['um_id']]);

            if ($user['is_banned']) {
                $sheet->getStyle("I$rowNum")->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()->setARGB('FFFF0000');
                $sheet->getStyle("I$rowNum")->getFont()->getColor()->setARGB('FFFFFFFF');
            } else {
                $sheet->getStyle("I$rowNum")->getFill()
                    ->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
                    ->getStartColor()->setARGB('FF00FF00'); // Green for active
            }
        }

        foreach (range('A', 'J') as $col) {
            $sheet->getColumnDimension($col)->setAutoSize(true);
        }

        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_THIN,
                    'color' => ['argb' => 'FF000000'],
                ],
            ],
        ];
        $sheet->getStyle('A3:J' . $rowNum)->applyFromArray($styleArray);

        $writer = new Xlsx($spreadsheet);
        $filename = "Daftar_User_" . DateHelper::formatTodayIndex() . ".xlsx";
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment; filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        return $writer->save('php://output');
    }

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

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

        $validatedRequest = $this->validate(
            $rawBody,
            [
                'id' => ['validators' => ['required']],
                'nama' => ['validators' => ['required']],
                'username' => ['validators' => ['required']],
                'email' => ['validators' => ['required', 'email']],
                'password' => ['validators' => []],
                'h_password' => ['validators' => []],
                'id_instansi' => ['validators' => []],
                'roles' => ['validators' => ['required']],
                'notify' => ['validators' => []],
                'checkmark_verified' => ['validators' => []],
            ]
        );

        try {
            $this->db->begin();
            $has_password = !empty($validatedRequest['password']) || !empty($validatedRequest['h_password']);
            $has_id_instansi = !empty($validatedRequest['id_instansi']);

            if ($has_password) {
                $pass = AuthHelper::decryptPassword($validatedRequest);
                AuthHelper::validatePassword($pass);
            }

            if ($has_id_instansi) {
                $instansi = Instansi::findFirst($validatedRequest['id_instansi']);

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

            $user = UmUser::findFirstByUmId($validatedRequest['id']);
            $oldData = LogModeration::extractUmUser($validatedRequest['id']);

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

            $isUserDataChanged = (
                $user->nama != $validatedRequest['nama']
                || $user->um_user_name != $validatedRequest['username']
                || $user->email != strtolower(trim($validatedRequest['email']))
                || $user->id_instansi != $validatedRequest['id_instansi']
            );

            $korwil_roles = Roles::find([
                'conditions' => 'name in ({names:array})',
                'bind'  => [
                    'names' => ['admin_korsupgah', 'admin_klop_korsupgah', 'admin_komunitas_korsupgah', 'observer_korsupgah', 'verifikator_korsupgah', 'qa_korsup']
                ]
            ]);

            $korwil_role_ids = [];

            foreach ($korwil_roles as $role) {
                $korwil_role_ids[] = $role->id;
            }

            $roles = $user->roles;
            $current_user_role_ids = [];

            foreach ($roles as $role) {
                $current_user_role_ids[] = $role->id;
            }

            $new_role_ids = array_intersect($validatedRequest['roles'], $korwil_role_ids);
            $other_current_role_ids = array_diff($validatedRequest['roles'], $korwil_role_ids);
            $final_new_role_ids = array_unique(array_merge($new_role_ids, $other_current_role_ids));

            if (!$isUserDataChanged) $isUserDataChanged = count(array_diff($validatedRequest['roles'], $current_user_role_ids));

            $username = strtolower(trim($validatedRequest['username']));
            // check username availability
            if (strtolower(trim($user->um_user_name)) != $username) {
                $duplicate_users = UmUser::find([
                    'conditions' => 'um_user_name ilike :username:',
                    'bind' => [
                        'username' => $username
                    ]
                ]);

                if (count($duplicate_users) > 0) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User dengan usename $username telah terdaftar");
                }
            }

            $email = strtolower(trim($validatedRequest['email']));

            // Check email availability
            if (strtolower(trim($user->email)) != $email) {
                $duplicate_users = UmUser::find([
                    'conditions' => 'email ilike :email:',
                    'bind' => [
                        'email' => $email
                    ]
                ]);

                if (count($duplicate_users) > 0) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User dengan email $email telah terdaftar");
                }
            }

            $user->email = $email;
            $user->nama = $validatedRequest['nama'];
            $user->um_user_name = $validatedRequest['username'];
            if ($has_password) {
                $user->um_user_new_password = $this->security->hash($pass);
            }
            if ($has_id_instansi) {
                $user->id_instansi = $validatedRequest['id_instansi'];
            }
            $user->migration_status = UmUser::MIGRATION_STATUS_PASS_CHANGED;
            if (in_array($validatedRequest['checkmark_verified'], [0, 1, 2, '0', '1', '2']))
                $user->checkmark_verified = $validatedRequest['checkmark_verified'];
            $status = $user->save();

            $newRoles = Roles::find([
                'conditions' => 'id in ({roles:array})',
                'bind' => ['roles' => $final_new_role_ids]
            ]);

            $user->setRoles2($newRoles);

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

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

                throw new ValidationException($error_messages);
            } else {
                if (array_key_exists('notify', $validatedRequest)) {
                    if (!empty($validatedRequest['notify'])) {
                        $subject = "";
                        $message = "";

                        if ($isUserDataChanged && $has_password) {
                            $subject = "[JAGA] Perubahan Data dan Password Akun JAGA";
                            $message = "Hi Sahabat JAGA, terima kasih telah menggunakan Aplikasi JAGA."
                                . "\n\nKamu menerima email ini karena adanya perubahan data pada akun JAGA kamu. Berikut informasi perubahan data pada Akun kamu :"
                                . "\n\n-username: " . $user->um_user_name
                                . "\n\n-nama: " . $user->nama
                                . "\n-tanggal lahir: " . $user->tgl_lahir
                                . "\n-no tlp: " . $user->phone
                                . "\n-email: " . $user->email
                                . "\n\ndan melakukan permintaan perubahan password pada akun JAGA kamu. Berikut akses yang dapat digunakan untuk login ke Aplikasi JAGA:"
                                . "\n\n-Username: " . $user->um_user_name
                                . "\n-Password: " . $pass
                                . "\n\natau klik link berikut " . $this->config["web_base_url"]
                                . "\n\nJika ada ketidaksesuaian pada data di atas, kamu dapat mengubah data kamu pada halaman profil."
                                . "\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.";
                        } else if ($isUserDataChanged) {
                            $subject = "[JAGA] Perubahan Data Akun JAGA";
                            $message = "Hi Sahabat JAGA, terima kasih telah menggunakan Aplikasi JAGA."
                                . "\n\nKamu menerima email ini karena adanya perubahan data pada akun JAGA kamu. Berikut informasi perubahan data pada Akun kamu :"
                                . "\n\n-nama: " . $user->nama
                                . "\n\n-username: " . $user->um_user_name
                                . "\n-tanggal lahir: " . $user->tgl_lahir
                                . "\n-no tlp: " . $user->phone
                                . "\n-email: " . $user->email
                                . "\n\nJika ada ketidaksesuaian pada data di atas, kamu dapat mengubah data kamu pada halaman profil."
                                . "\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.";
                        } else if ($has_password) {
                            $subject = "[JAGA] Permintaan Perubahan Password diterima.";
                            $message = "Hi Sahabat JAGA, terima kasih telah menggunakan Aplikasi JAGA."
                                . "\n\nKamu menerima email ini karena telah melakukan permintaan perubahan password pada akun JAGA kamu. Berikut akses yang dapat digunakan untuk login ke Aplikasi JAGA:"
                                . "\n\n-Username: " . $user->um_user_name
                                . "\n-Password: " . $pass
                                . "\n\natau klik link berikut " . $this->config["web_base_url"]
                                . "\n\nJangan khawatir. Tingkatkan keamanan akun JAGA-mu dengan mengganti password setelah berhasil melakukan login. Perubahan password dapat dilakukan pada halaman profil."
                                . "\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.";
                        }

                        if ($isUserDataChanged || $has_password) {
                            $mail = new MailHelper($this->config["mail_config"]);
                            $mail->queue($rawBody["email"], $subject, $message, false, $this->shared->user->um_id);
                        }
                    }
                }

                $newData = LogModeration::extractUmUser($validatedRequest['id']);
                $action = [LogModeration::ACTION_UPDATE_UMUSER];
                if ($has_password) $action[] = LogModeration::ACTION_CHANGEPWD_UMUSER;
                LogModeration::loggedUmUser(
                    $action,
                    LogModeration::ACTIONMENU_ADMINISTRASI_JAGA,
                    $user->um_id,
                    $oldData,
                    $newData,
                    $this->shared->user->um_id
                );

                $result = $user->baseToArray();
                $instansi = $user->instansi;
                $result['instansi'] = empty($instansi) ? null : $instansi;

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

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

        $postParams = $this->request->getJsonRawBody(true);
        $params = [];

        $params['user_id'] = array_key_exists('user_id', $postParams) ? $postParams['user_id'] : null;

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

        $user = UmUser::findFirst([
            'conditions' => 'um_id = :user_id:',
            'bind' => ['user_id' => $validatedParams['user_id']]
        ]);

        if (empty($user)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'User not found');
        }

        if ($user->is_banned) {
            throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'User already banned');
        }
        $oldData = LogModeration::extractUmUser($validatedParams['user_id']);

        $user->is_banned = true;
        $user->save();
        $newData = LogModeration::extractUmUser($validatedParams['user_id']);

        $token_list = JwtData::findByUserId($validatedParams['user_id']);

        foreach ($token_list as $token) {
            if ($token->status == JwtData::STATUS_ACTIVE) {
                $token->status = JwtData::STATUS_REVOKED;
                $token->save();
            }
        }

        LogModeration::loggedUmUser(
            [LogModeration::ACTION_BAN_UMUSER],
            LogModeration::ACTIONMENU_ADMINISTRASI_JAGA,
            $user->um_id,
            $oldData,
            $newData,
            $this->shared->user->um_id
        );

        return new ObjectResponse([
            'success' => true,
            'data' => $user->baseToArray()
        ]);
    }

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

        $postParams = $this->request->getJsonRawBody(true);
        $params = [];

        $params['user_id'] = array_key_exists('user_id', $postParams) ? $postParams['user_id'] : null;

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

        $user = UmUser::findFirst([
            'conditions' => 'um_id = :user_id:',
            'bind' => ['user_id' => $validatedParams['user_id']]
        ]);

        if (empty($user)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'User not found');
        }

        if (!$user->is_banned) {
            throw new CustomErrorException(BaseResponse::INVALID_ACTION, 'User already active');
        }
        $oldData = LogModeration::extractUmUser($validatedParams['user_id']);

        $user->is_banned = false;
        $user->save();
        $newData = LogModeration::extractUmUser($validatedParams['user_id']);

        $token_list = JwtData::findByUserId($validatedParams['user_id']);

        foreach ($token_list as $token) {
            if ($token->status == JwtData::STATUS_REVOKED) {
                $token->status = JwtData::STATUS_ACTIVE;
                $token->save();
            }
        }

        LogModeration::loggedUmUser(
            [LogModeration::ACTION_UNBAN_UMUSER],
            LogModeration::ACTIONMENU_ADMINISTRASI_JAGA,
            $user->um_id,
            $oldData,
            $newData,
            $this->shared->user->um_id
        );

        return new ObjectResponse([
            'success' => true,
            'data' => $user->baseToArray()
        ]);
    }

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

        $postParams = $this->request->getJsonRawBody(true);
        $params = [];

        $params['user_id'] = array_key_exists('user_id', $postParams) ? $postParams['user_id'] : null;
        $params['roles'] = array_key_exists('roles', $postParams) ? $postParams['roles'] : null;

        $validatedParams = $this->validate(
            $params,
            [
                'user_id' => ['validators' => ['required']],
                'roles' => ['validators' => ['required']]
            ]
        );

        $user = UmUser::findFirst([
            'conditions' => 'um_id = :user_id:',
            'bind' => ['user_id' => $validatedParams['user_id']]
        ]);

        if (empty($user)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, 'User not found');
        }

        $roles = Roles::find([
            'conditions' => 'id in ({roles:array})',
            'bind' => ['roles' => $validatedParams['roles']]
        ]);

        $user->setRoles2($roles);

        return new ObjectResponse([
            'success' => true,
            'data' => $user->baseToArray()
        ]);
    }

    public function requestResetPassword()
    {
        if (getenv('REDIS_RATE_LIMITER')) {
            $rateLimiter = new RateLimiter($this->redis, 5, 300);
            try {
                $rateLimiter->handle($this->request);
            } catch(Exception $e) {
                throw $e;
            }
        }
        
        $rawBody = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $rawBody,
            [
                'email' => ['validators' => ['required']]
            ]
        );

        $userRes = UmUser::count(
            [
                "email ilike :email:",
                "bind" => [
                    "email" => trim($rawBody["email"])
                ]
            ]
        );

        if ($userRes == 1) {
            $countExistedRequest = $this->rawQuery(
                "select count(*) as total from jaga.password_reset where email = :email and created_at > now() - interval '" . PasswordReset::INTERVAL_REQUEST_MINUTES . " minute'",
                [
                    'email' => $rawBody['email']
                ]
            )[0]['total'];
            if ($countExistedRequest > 0) {
                throw new CustomErrorException(BaseResponse::INVALID_ACTION, "Anda harus menunggu " . PasswordReset::INTERVAL_REQUEST_MINUTES . " menit sebelum membuat permintaan baru!");
            }

            $this->db->begin();
            try {
                $existingRequests = PasswordReset::find(["email = :email:", "bind" => ["email" => $validatedRequest["email"]]]);
                foreach ($existingRequests as $existingRequest) {
                    $existingRequest->is_active = 0;
                    $existingRequest->save();
                }

                $passwordReset = new PasswordReset();
                $random = new \Phalcon\Security\Random();
                $passwordReset->token = $random->uuid();
                $passwordReset->email = $validatedRequest["email"];
                $passwordReset->save();

                $link = $this->config["web_base_url"] . '/ubah_sandi?token=' . $passwordReset->token;

                $message = "Dear Pengguna,"
                    . "\nUntuk melakukan reset password, silahkan kunjungi link berikut: "
                    . $link
                    . "\n\nTerima kasih";

                $mail = new MailHelper($this->config["mail_config"]);
                $mail->queue($rawBody["email"], "[JAGA] Reset Password Request", $message);

                $this->db->commit();
                return new SimpleResponse(PasswordReset::INTERVAL_REQUEST_MINUTES);
            } catch (\Exception $e) {
                $this->db->rollback();
            }
        } else {
            // bila email tidak ditemukan, tetap mengembalikan response yang sama 
            // agar tidak terjadi enumerasi email oleh penyerang
            return new SimpleResponse(PasswordReset::INTERVAL_REQUEST_MINUTES);
        }
    }

    public function requestResetEmailPassword()
    {
        $rawBody = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $rawBody,
            [
                'um_id' => ['validators' => ['required', 'digit']],
                'email' => ['validators' => ['required']]
            ]
        );

        $countExistedRequest = $this->rawQuery(
            "select count(*) as total from jaga.password_reset where email = :email and created_at > now() - interval '" . PasswordReset::INTERVAL_REQUEST_MINUTES . " minute'",
            [
                'email' => $validatedRequest['email']
            ]
        )[0]['total'];
        if ($countExistedRequest > 0) {
            throw new CustomErrorException(BaseResponse::INVALID_ACTION, "Anda harus menunggu " . PasswordReset::INTERVAL_REQUEST_MINUTES . " menit sebelum membuat permintaan baru!");
        }

        $this->db->begin();
        try {
            $this->db->execute(
                "UPDATE jaga.um_user 
                SET temp_email = :email
                WHERE
                    email <> :email AND
                    um_id = :um_id",
                [
                    'um_id' => (int) $validatedRequest['um_id'],
                    'email' => (string) $validatedRequest['email'],
                ]
            );

            $existingRequests = PasswordReset::find(["email = :email:", "bind" => ["email" => $validatedRequest["email"]]]);
            foreach ($existingRequests as $existingRequest) {
                $existingRequest->is_active = 0;
                $existingRequest->save();
            }

            $passwordReset = new PasswordReset();
            $random = new \Phalcon\Security\Random();
            $passwordReset->token = $random->uuid();
            $passwordReset->email = $validatedRequest["email"];
            $passwordReset->save();

            $user = UmUser::findFirst($validatedRequest['um_id']);
            $link = $this->config["web_base_url"] . '/ubah_sandi?token=' . $passwordReset->token;

            $message = "Dear {$user->nama},"
                . "\nUntuk melakukan reset password, silahkan kunjungi link berikut: "
                . $link
                . "\n\nTerima kasih";

            $mail = new MailHelper($this->config["mail_config"]);
            $mail->queue($validatedRequest["email"], "[JAGA] Reset Password Request", $message);

            $this->db->commit();
            return new SimpleResponse(PasswordReset::INTERVAL_REQUEST_MINUTES);
        } catch (\Exception $e) {
            $this->db->rollback();
            $this->logger->info($e->getMessage());
        }
    }

    public function checkResetPasswordToken()
    {
        $rawBody = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $rawBody,
            [
                'token' => ['validators' => ['required']]
            ]
        );
        try {
            $passwordResetRes = $this->rawQuery(
                "select * from jaga.password_reset where token = :token and is_active = 1 and created_at > (now() - interval '" . PasswordReset::TOKEN_EXPIRATION_MINUTES . " minute')",
                [
                    "token" => $validatedRequest["token"]
                ]
            );
        } catch (\Exception $e) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Token tidak valid!");
        }

        if (count($passwordResetRes) == 0) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Token tidak valid atau telah expired!");
        }

        return new SimpleResponse(1);
    }

    public function doResetPassword()
    {
        $rawBody = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $rawBody,
            [
                'token' => ['validators' => ['required']],
                'password' => ['validators' => []],
                'h_password' => ['validators' => []]
            ]
        );
        $pass = AuthHelper::decryptPassword($validatedRequest);
        AuthHelper::validatePassword($pass);

        try {
            $passwordResetRes = $this->rawQuery(
                "select * from jaga.password_reset where token = :token and is_active = 1 and created_at > (now() - interval '" . PasswordReset::TOKEN_EXPIRATION_MINUTES . " minute')",
                [
                    "token" => $rawBody["token"]
                ]
            );
        } catch (\Exception $e) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Token tidak valid!");
        }


        if (count($passwordResetRes) > 0) {
            $passwordResetRes = $passwordResetRes[0];
            $model = new PasswordReset();
            $passwordResetRes = $model->assign($passwordResetRes);
            $email = trim($passwordResetRes->email);
            $userRes = UmUser::find(
                [
                    "email ilike :email: OR temp_email ilike :email:",
                    "bind" => [
                        "email" => $email
                    ]
                ]
            );
            if ($userRes->count() == 1) {
                $hashed_password = base64_encode(hash("sha256", $pass, true));
                $user = $userRes[0];
                $user->um_user_new_password = $this->security->hash($pass);
                $user->um_user_password = $hashed_password;
                $user->um_require_change = false;
                $user->email = $email;
                $user->temp_email = null;
                $res = $user->save();
                if ($res) {
                    $passwordReset = $passwordResetRes;
                    $passwordReset->is_active = 0;
                    $passwordReset->save();
                    return new ObjectResponse(
                        [
                            "success" => true
                        ]
                    );
                } else {
                    throw new CustomErrorException(BaseResponse::SQL_ERROR, $user->getMessages());
                }
            } else {
                throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Email not found");
            }
        } else {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Token tidak valid atau telah expired!");
        }
    }

    public function getProfile()
    {
        $this->checkIsAuthenticated();
        $params = $this->request->get();
        $isSimple = array_key_exists("is_simple", $params) ? $params["is_simple"] : false;

        $dataUser = UmUser::find(
            [
                "columns" => "email, um_user_name as username, nama, tgl_lahir, tempat_lahir, phone, uuid_user, foto, presigned_url, presigned_exp, jmlh_dukung_crt_mskn, jenis_kelamin, id_instansi, checkmark_verified",
                "um_id = :um_id:",
                "bind" => [
                    "um_id" => $this->shared->user->um_id
                ]
            ]
        )
            ->toArray()[0];

        $dataUser["profile_picture_url"] = MediaHelper::getPresignedUrlFoto($dataUser, UmUser::class, $this->config, $this->minio);

        if (!$isSimple) {
            $medali = Medali::findFirst(
                [
                    "columns" => "medali_name",
                    "medali_point_min <= :poin: and medali_point_max >= :poin:",
                    "bind" => [
                        "poin" => $dataUser["jmlh_dukung_crt_mskn"]
                    ]
                ]
            );

            if (!$medali) {
                $medali = Medali::findFirst(
                    [
                        "columns" => "medali_name",
                        "order" => "medali_point_max desc"
                    ]
                );
            }
            $dataUser["medali_name"] = $medali->medali_name;

            $level = LevelUser::findFirst(
                [
                    "columns" => "level_name",
                    "level_point_min <= :poin: and level_point_max >= :poin: and not is_deleted",
                    "bind" => [
                        "poin" => $dataUser["jmlh_dukung_crt_mskn"]
                    ]
                ]
            );

            if (!$level) {
                $level = LevelUser::findFirst(
                    [
                        "columns" => "level_name",
                        "not is_deleted",
                        "order" => "level_point_max desc"
                    ]
                );
            }

            $roles = $this->shared->user->roles;
            $arrRoles = [];
            foreach ($roles as $role) {
                array_push($arrRoles, $role->name);
            }

            $dataUser["roles"] = $arrRoles;
            $dataUser["level_name"] = $level->level_name;
            $dataUser["jabatan"] = $this->shared->user->jabatan;
            $dataUser["instansi"] = $this->shared->user->instansi;
        }
        return new PaginatedResponse($dataUser, 1, 1, 0);
    }

    public function getInstansi()
    {
        $this->checkIsAuthenticated();
        $instansi = $this->shared->user->instansi;
        $result = [];

        if (!empty($instansi)) {
            $result[] = $instansi;
        }
        return new CollectionPaginatedResponse($result);
    }

    public function getPublicProfile($uuid)
    {
        $this->checkIsAuthenticated();
        $dataUser = UmUser::find(
            [
                "columns" => "um_id, um_user_name as username, email, nama, uuid_user, jmlh_dukung_crt_mskn, foto, presigned_url, presigned_exp",
                "uuid_user = :uuid_user: and is_active = true",
                "bind" => [
                    "uuid_user" => $uuid
                ]
            ]
        );
        if (empty($dataUser)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "User tidak ditemukan");
        }
        $dataUser = $dataUser->toArray()[0];

        $dataUser["profile_picture_url"] = MediaHelper::getPresignedUrlFoto($dataUser, UmUser::class, $this->config, $this->minio);

        $medali = Medali::findFirst(
            [
                "columns" => "medali_name",
                "medali_point_min <= :poin: and medali_point_max >= :poin:",
                "bind" => [
                    "poin" => $dataUser["jmlh_dukung_crt_mskn"]
                ]
            ]
        );

        if (!$medali) {
            $medali = Medali::findFirst(
                [
                    "columns" => "medali_name",
                    "order" => "medali_point_max desc"
                ]
            );
        }
        $dataUser["medali_name"] = $medali->medali_name;

        $level = LevelUser::findFirst(
            [
                "columns" => "level_name",
                "level_point_min <= :poin: and level_point_max >= :poin: and not is_deleted",
                "bind" => [
                    "poin" => $dataUser["jmlh_dukung_crt_mskn"]
                ]
            ]
        );

        if (!$level) {
            $level = LevelUser::findFirst(
                [
                    "columns" => "level_name",
                    "not is_deleted",
                    "order" => "level_point_max desc"
                ]
            );
        }

        $dataUser["level_name"] = $level->level_name;
        return new PaginatedResponse($dataUser, 1, 1, 0);
    }

    public function getPublicProfileNew($uuid)
    {
        $this->checkIsAuthenticated();
        $user = UmUser::findFirst(
            [
                "uuid_user = :uuid_user: and is_active = true",
                "bind" => [
                    "uuid_user" => $uuid
                ]
            ]
        );

        if (empty($user)) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "User tidak ditemukan");
        }
        $dataUser = $user->toArrayForPublicProfile();
        $dataUser["profile_picture_url"] = MediaHelper::getPresignedUrlFoto($dataUser, UmUser::class, $this->config, $this->minio);

        $medali = Medali::findFirst(
            [
                "columns" => "medali_name",
                "medali_point_min <= :poin: and medali_point_max >= :poin:",
                "bind" => [
                    "poin" => $dataUser["jmlh_dukung_crt_mskn"]
                ]
            ]
        );

        if (!$medali) {
            $medali = Medali::findFirst(
                [
                    "columns" => "medali_name",
                    "order" => "medali_point_max desc"
                ]
            );
        }
        $dataUser["medali_name"] = $medali->medali_name;

        $level = LevelUser::findFirst(
            [
                "columns" => "level_name",
                "level_point_min <= :poin: and level_point_max >= :poin: and not is_deleted",
                "bind" => [
                    "poin" => $dataUser["jmlh_dukung_crt_mskn"]
                ]
            ]
        );

        if (!$level) {
            $level = LevelUser::findFirst(
                [
                    "columns" => "level_name",
                    "not is_deleted",
                    "order" => "level_point_max desc"
                ]
            );
        }

        $dataUser["level_name"] = $level->level_name;
        return new PaginatedResponse($dataUser, 1, 1, 0);
    }

    public function getUuid($username)
    {
        $this->checkIsAuthenticated();
        $user = UmUser::findFirst(
            [
                "columns" => "uuid_user",
                "um_user_name = :username: and is_active = true",
                "bind" => [
                    "username" => $username
                ]
            ]
        );

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

        return new SimpleResponse($user);
    }

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

        $rawBody = $this->request->getPost();
        $validatedRequest = $this->validate(
            $rawBody,
            [
                // #988 tidak dapat mengubah username
                //                 'username' => ['validators' => ['required']],
                'nama' => ['validators' => ['required']],
                'phone' => ['validators' => ['required', 'digit']],
                'tgl_lahir' => ['validators' => ['required']],
                'jenis_kelamin' => ['validators' => []]
            ]
        );

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

            $dataUser = UmUser::findFirst(
                [
                    "um_id = :um_id:",
                    "bind" => [
                        "um_id" => $this->shared->user->um_id
                    ]
                ]
            );
            $oldData = LogModeration::extractUmUser($this->shared->user->um_id);

            $errMsgs = [];

            if ($rawBody["phone"] !== $this->shared->user->phone) {
                if ($this->phone_exists($rawBody["phone"])) {
                    $errMsgs[] = array(
                        'field' => 'phone',
                        'message' => 'Phone has been used.'
                    );
                }
            }

            //        issue #988, username tidak dapat diubah
            //        if ($rawBody["username"] !== $this->shared->user->um_user_name) {
            //            $validateUsername = UserHelper::validateUsername($rawBody["username"]);
            //
            //            if ($validateUsername == 1) {
            //                $errMsgs[] = [
            //                    'field' => 'username',
            //                    'message' => 'Username length must be more than 6 characters.'
            //                ];
            //            }
            //
            //            if ($validateUsername == 2) {
            //                $errMsgs[] = [
            //                    'field' => 'username',
            //                    'message' => 'Username must be consist of Alphanumeric, underscore, dash or dot only.'
            //                ];
            //            }
            //
            //            if ($validateUsername == 3) {
            //                $errMsgs[] = [
            //                    'field' => 'username',
            //                    'message' => 'Username has been used.'
            //                ];
            //            }
            //
            //            $this->checkUsername($rawBody["username"]);
            //        }

            if (count($errMsgs) > 0) {
                throw new CustomErrorException(BaseResponse::REGISTRATION_ERROR, "Update User Gagal", $errMsgs);
            }

            if ($this->request->hasFiles()) {
                $random = new \Phalcon\Security\Random();

                $files = $this->request->getUploadedFiles();
                UserHelper::validateProfilePicture($files);
                foreach ($files as $file) {
                    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 . $dataUser->foto;
                        if (file_exists($oldProPic) && is_file($oldProPic)) {
                            unlink($oldProPic);
                        }

                        $dataUser->foto = $shortName;
                        $filename .= DIRECTORY_SEPARATOR . $shortName;
                        $file->moveTo($filename);
                    } elseif (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::MINIO) {
                        $uploadedFiles = $this->request->getUploadedFiles();
                        foreach ($uploadedFiles as $file) {
                            /** @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
                                );
                            }

                            $dataUser->foto = $shortName;
                            $dataUser->presigned_exp = (new DateTime())->format('Y-m-d H:i:s');
                        }
                    }
                }
            }

            $dataUser->nama = $rawBody["nama"];
            // issue #988, username tidak dapat diubah
            // $dataUser->um_user_name = $rawBody["username"];
            $dataUser->phone = $rawBody["phone"];
            $dataUser->tempat_lahir = $rawBody["tempat_lahir"];
            $dataUser->tgl_lahir = $rawBody["tgl_lahir"];
            $dataUser->jenis_kelamin = $validatedRequest["jenis_kelamin"];
            $res = $dataUser->save();
            if (!$res) {
                return new CustomErrorException(BaseResponse::SQL_ERROR, $dataUser->getMessages());
            }

            $newData = LogModeration::extractUmUser($this->shared->user->um_id);
            $action = [LogModeration::ACTION_UPDATE_UMUSER];
            LogModeration::loggedUmUser(
                $action,
                LogModeration::ACTIONMENU_PROFILE,
                $this->shared->user->um_id,
                $oldData,
                $newData,
                $this->shared->user->um_id
            );

            $this->db->commit();

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

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

        $rawBody = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $rawBody,
            [
                'password' => ['validators' => []],
                'h_password' => ['validators' => []],
                'new_password' => ['validators' => []],
                'h_new_password' => ['validators' => []],
            ]
        );
        $oldPass = AuthHelper::decryptPassword($validatedRequest);
        $newPass = AuthHelper::decryptPassword($validatedRequest, 'new_password', 'h_new_password');
        AuthHelper::validatePassword($newPass);

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

            $user = UmUser::findFirst(
                [
                    "um_id = :um_id:",
                    "bind" => [
                        "um_id" => $this->shared->user->um_id
                    ]
                ]
            );

            if ($this->security->checkHash($oldPass, $user->um_user_new_password)) {
                $oldData = LogModeration::extractUmUser($this->shared->user->um_id);
                $hashed_password = base64_encode(hash("sha256", $oldPass, true));
                $user->um_user_new_password = $this->security->hash($newPass);
                $user->um_user_password = $hashed_password;
                $res = $user->save();
                if ($res) {
                    $newData = LogModeration::extractUmUser($this->shared->user->um_id);
                    $action = [LogModeration::ACTION_CHANGEPWD_UMUSER];
                    LogModeration::loggedUmUser(
                        $action,
                        LogModeration::ACTIONMENU_PROFILE,
                        $this->shared->user->um_id,
                        $oldData,
                        $newData,
                        $this->shared->user->um_id
                    );
                    $this->db->commit();
                    return new ObjectResponse(
                        [
                            "success" => true
                        ]
                    );
                } else {
                    throw new CustomErrorException(BaseResponse::SQL_ERROR, $user->getMessages());
                }
            } else {
                throw new CustomErrorException(BaseResponse::INVALID_PASSWORD, "Invalid Password");
            }
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function readNotification($notifId)
    {
        $this->checkIsAuthenticated();
        $notification = Notifications::findFirst($notifId);
        if ($notification && $notification->owner_user_id == $this->shared->user->um_id) {
            $notification->is_read = 1;
            $notification->save();
        }

        return new SimpleResponse(1);
    }

    public function readNotifications()
    {
        $this->checkIsAuthenticated();
        $req = $this->validate(
            $this->request->getJsonRawBody(true),
            [
                'ids' => ['validators' => ['required']],
            ]
        );
        $ids = $req["ids"];
        $notifications = Notifications::find([
            "conditions" => "id in ({ids:array}) and owner_user_id = :owner_id:",
            "bind" => ["ids" => array_values($ids), "owner_id" => $this->shared->user->um_id]
        ]);

        foreach ($notifications as $notification) {
            $notification->is_read = 1;
            $notification->save();
        }

        return new SimpleResponse(1);
    }

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

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

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

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

        $totalCount = $this->rawQuery(
            "select count(id) as total from jaga.notifications where owner_user_id = :user_id and (category = :category or :category = -1) and ((created_at >= 'now'::timestamp - '1 month'::interval and is_read = 1) or is_read = 0)",
            [
                "user_id" => $this->shared->user->um_id,
                "category" => $params["category"]
            ]
        )[0]['total'];
        if ($totalCount == 0) {
            $res = [];
        } else {
            $rows = $this->rawQuery(
                "select * from jaga.notifications where owner_user_id = :user_id and (category = :category or :category = -1) and ((created_at >= 'now'::timestamp - '1 month'::interval and is_read = 1) or is_read = 0) order by id desc limit :limit offset :offset",
                [
                    "user_id" => $this->shared->user->um_id,
                    "category" => $params["category"],
                    "limit" => $params["limit"],
                    "offset" => $params["offset"]
                ]
            );
            $notificationsRes = [];
            foreach ($rows as $row) {
                $notif = new Notifications();
                $notif->assign($row);
                $notificationsRes[] = $notif;
            }
            $res = $this->transformNotificationResponse($notificationsRes);
        }

        return new PaginatedResponse($res, $totalCount, $params['limit'], $params['offset']);
    }

    private function transformNotificationResponse($data)
    {
        $res = [];
        foreach ($data as $datum) {
            $obj = new stdClass();
            $obj->id = $datum->id;
            $obj->owner = [
                "id" => $datum->owner->um_id,
                "nama" => $datum->owner->nama,
                "uuid" => $datum->owner->uuid_user,
                "profile_picture_url" => MediaHelper::getPresignedUrlFoto($datum->owner->toArray(), UmUser::class, $this->config, $this->minio),
                "jenis_kelamin" => $datum->owner->jenis_kelamin
            ];
            $obj->sender = [
                "id" => $datum->sender->um_id,
                "nama" => $datum->sender->nama,
                "uuid" => $datum->sender->uuid_user,
                "profile_picture_url" => MediaHelper::getPresignedUrlFoto($datum->sender->toArray(), UmUser::class, $this->config, $this->minio),
                "jenis_kelamin" => $datum->sender->jenis_kelamin
            ];

            $obj->message = $datum->message;
            $obj->message_en = $datum->message_en;
            $obj->action = $datum->action;
            if (
                $obj->action == NotificationHelper::ACTION_DISKUSI ||
                $obj->action == NotificationHelper::ACTION_CERITA ||
                $obj->action == NotificationHelper::ACTION_REPORTED
            ) {
                $diskusi = Diskusi::findFirst(
                    [
                        "uuid = :uuid:",
                        "bind" => [
                            "uuid" => $datum->action_id
                        ]
                    ]
                );

                if (!$diskusi) {
                    continue;
                }
                $obj->action_data = [
                    "id" => $diskusi->id,
                    "uuid" => $diskusi->uuid,
                    "produk_id" => $diskusi->kategori->produk_id,
                    "is_deleted" => $diskusi->is_deleted,
                    "type" => $diskusi->type

                ];
            } elseif ($obj->action == NotificationHelper::ACTION_PANCEK) {
                $url = "";
                switch ($datum->category) {
                    case NotificationHelper::CATEGORY_PANCEK_ASSIGN:
                        $url = PancekService::ACTION_ASSIGN;
                        break;
                    case NotificationHelper::CATEGORY_PANCEK_ASSIGN_KPPBC:
                        $url = PancekService::ACTION_ASSIGN_KPPBC . $datum->action_id;
                        break;
                    case NotificationHelper::CATEGORY_PANCEK_VERIFY:
                        $url = PancekService::ACTION_VERIFY . $datum->action_id;
                        break;
                    case NotificationHelper::CATEGORY_PANCEK_REKAPITULASI:
                        $url = PancekService::ACTION_REKAPITULASI;
                        break;
                    case NotificationHelper::CATEGORY_PANCEK_FORM:
                        $url = PancekService::ACTION_FORM;
                        break;
                    default:
                        break;
                }
                $obj->action_data = [
                    "action_id" => $datum->action_id,
                    "category" => $datum->category,
                    "action_url" => $url
                ];
            }
            $obj->is_read = $datum->is_read;
            $obj->created_at = $datum->created_at;

            array_push($res, $obj);
        }
        return $res;
    }

    public function getNotificationUnreadCount($category = -1)
    {
        if ($this->isAuthenticated()) {
            return Notifications::count(
                [
                    "owner_user_id = :user_id: and is_read = 0 and (category = :category: or :category: = -1)",
                    "bind" => [
                        "user_id" => $this->shared->user->um_id,
                        "category" => $category
                    ]
                ]
            );
        } else {
            return 0;
        }
    }

    private function setNotificationsRead($data)
    {
        $ids = [];
        $idsStr = "";
        $i = 0;
        foreach ($data as $datum) {
            if (strlen($idsStr) > 0) {
                $idsStr .= ",";
            }
            $idsStr .= ":ids" . $i . ":";
            $i++;
            $ids[] = $datum->id;
        }

        if (count($ids) > 0) {
            $this->modelsManager->createQuery("UPDATE Notifications SET is_read = 1 where id in (" . $idsStr . ")")
                ->execute([
                    "ids" => $ids
                ]);
        }
    }

    private function getPropicFromNetwork($email, $urlPropic, $change_profile_picture)
    {
        if (empty($urlPropic)) {
            return null;
        }
        try {
            if (empty($change_profile_picture)) {
                $change_profile_picture = self::CHANGE_PROFILE_PICTURE;
            }

            $user = UmUser::find(
                [
                    'email = :email:',
                    "bind" => [
                        "email" => $email
                    ]
                ]
            );

            $random = new \Phalcon\Security\Random();
            $uuid = $random->uuid();
            $shortName = $uuid . ".png";

            if ($user->count() != 0) {
                $shortName = $user[0]->foto;
            }

            if (empty($shortName) && $change_profile_picture == self::CHANGE_PROFILE_PICTURE) {
                $shortName = $uuid . ".png";
            }

            if ($user->count() == 0 || $change_profile_picture == self::CHANGE_PROFILE_PICTURE) {
                $tempPath = tempnam(sys_get_temp_dir(), 'profpict_');

                $client = new GuzzleHttp\Client();
                $response = $client->request("GET", $urlPropic, ["sink" => $tempPath]);

                if (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::MINIO) {
                    /** @var MinioService $minioService */
                    $minioService = $this->minio;
                    if ($minioService->isSecondaryS3Available()) {
                        $minioService->baseUploadUsingSecondaryS3(
                            $tempPath,
                            mime_content_type($tempPath),
                            false,
                            'users_profpic' . DIRECTORY_SEPARATOR . $shortName
                        );
                    } else {
                        $minioService->base_upload_attachment(
                            $tempPath,
                            mime_content_type($tempPath),
                            false,
                            'users_profpic' . DIRECTORY_SEPARATOR . $shortName
                        );
                    }
                } elseif (getenv("FILESYSTEM_CLOUD") == FileSystemCloud::LOCAL) {
                    $filename = getenv("USER_IMAGE_FOLDER");
                    if (!file_exists($filename) && !is_dir($filename)) {
                        mkdir($filename, 0770, true);
                    }
                    $filename .= $shortName;

                    copy($tempPath, $filename);
                }
            }

            return $shortName;
        } catch (\Exception $e) {
        }
    }

    public function stranasList()
    {
        $this->checkScopes(['stranas.monitoring']);

        $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'] : '';

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

        $stranas_klop_role = Roles::findFirstByName('admin_klop_stranas');

        if (empty($stranas_klop_role)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Role admin_klop_stranas tidak ditemukan");
        }

        $stranas_users = UmUser::query()
            ->join('UsersRoles', 'UsersRoles.user_id = UmUser.um_id')
            ->where('role_id=:role_id: AND (UmUser.email ilike :keyword: OR UmUser.um_user_name ilike :keyword: OR UmUser.nama ilike :keyword:) ')
            ->bind([
                'keyword' => "%" . $validatedParams['keyword'] . "%",
                'role_id' => $stranas_klop_role->id
            ])
            ->limit($validatedParams['limit'], $validatedParams['offset'])
            ->execute();

        $total_stranas_users = (int) UmUser::query()
            ->columns('count(distinct um_id) as jumlah')
            ->join('UsersRoles', 'UsersRoles.user_id = UmUser.um_id')
            ->where('role_id=:role_id:  AND (UmUser.email ilike :keyword: OR UmUser.um_user_name ilike :keyword: OR UmUser.nama ilike :keyword:)')
            ->bind([
                'role_id' => $stranas_klop_role->id,
                'keyword' => "%" . $validatedParams['keyword'] . "%",
            ])
            ->execute()[0]['jumlah'];

        $stranas_new_user_email = StranasNewUserEmail::find();
        $email_notifikasi = [];
        foreach ($stranas_new_user_email as $row) {
            $email_notifikasi[$row->um_id] = $row->email;
        }

        $result = [];

        foreach ($stranas_users as $key => $user) {
            $current_user = $user->baseToArray();
            $instansi = $user->instansi;
            $current_user['instansi'] = empty($instansi) ? null : $instansi;
            $current_user['email_notifikasi'] = $email_notifikasi[$current_user['um_id']] ?? null;

            $result[] = $current_user;
        }

        return new PaginatedResponse($result, $total_stranas_users, $validatedParams['limit'], $validatedParams['offset']);
    }

    /**
     * @throws CustomErrorException
     * @throws ValidationException
     */
    public function resetEmailPasswordStranas()
    {
        $this->checkScopes(['stranas.admin']);

        $rawBody = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $rawBody,
            [
                'id' => ['validators' => ['required']],
                'nama' => ['validators' => ['required']],
                'email' => ['validators' => ['required', 'email']],
                'password' => ['validators' => []],
                'id_instansi' => ['validators' => []],
                'email_notifikasi' => ['validators' => []]
            ]
        );

        $has_password = !empty($validatedRequest['password']);
        $has_id_instansi = !empty($validatedRequest['id_instansi']);

        if ($has_password) {
            $newPassword = AuthHelper::decryptPassword($validatedRequest, 'password', 'password');
            AuthHelper::validatePassword($newPassword);
        }

        if ($has_id_instansi) {
            $instansi = Instansi::findFirst($validatedRequest['id_instansi']);

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

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

            $user = UmUser::findFirstByUmId($validatedRequest['id']);
            $oldData = LogModeration::extractUmUser($validatedRequest['id']);
            $action = [LogModeration::ACTION_UPDATE_UMUSER];

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

            $admin_role = Roles::findFirstByName('admin_sistem');
            $stranas_admin_role = Roles::findFirstByName('admin_stranas');
            $stranas_klop_role = Roles::findFirstByName('admin_klop_stranas');

            if (empty($admin_role) || empty($stranas_admin_role) || empty($stranas_klop_role)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Role penting gagal ditemukan");
            }


            $roles = $user->roles;
            $current_user_role_ids = [];

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

            if (!in_array($stranas_klop_role->id, $current_user_role_ids)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User tidak memiliki role admin_klop_stranas");
            }

            $blacklist_role_ids = [$admin_role->id, $stranas_admin_role->id];
            $intersect_role_ids = array_intersect($current_user_role_ids, $blacklist_role_ids);

            if (!empty($intersect_role_ids)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User memiliki role admin sistem/admin stranas");
            }

            $email = strtolower(trim($validatedRequest['email']));

            // Check email availability
            if (strtolower(trim($user->email)) != $email) {
                $duplicate_users = UmUser::find([
                    'conditions' => 'email ilike :email:',
                    'bind' => [
                        'email' => $email
                    ]
                ]);

                if (count($duplicate_users) > 0) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User dengan email $email telah terdaftar");
                }

                $action[] = LogModeration::ACTION_CHANGEEMAIL_UMUSER;
            }

            $user->email = $email;
            $user->nama = $validatedRequest['nama'];
            if ($has_password) {
                $user->um_user_new_password = $this->security->hash($newPassword);
                $action[] = LogModeration::ACTION_CHANGEPWD_UMUSER;
            }
            if ($has_id_instansi) {
                $user->id_instansi = $validatedRequest['id_instansi'];
            }
            $user->migration_status = UmUser::MIGRATION_STATUS_PASS_CHANGED;
            $status = $user->save();

            $email_notifikasi = $validatedRequest['email_notifikasi'] ?? null;
            $strNewUserEmail = StranasNewUserEmail::findFirstByUmId($validatedRequest['id']);
            if (empty($strNewUserEmail)) {
                $strNewUserEmail = new StranasNewUserEmail();
                $strNewUserEmail->um_id = $validatedRequest['id'];
            }
            $strNewUserEmail->email = $email_notifikasi;
            $strNewUserEmail->save();

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

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

                throw new ValidationException($error_messages);
            }

            $newData = LogModeration::extractUmUser($validatedRequest['id']);
            LogModeration::loggedUmUser(
                $action,
                LogModeration::ACTIONMENU_ADMINISTRASI_STRANAS,
                $validatedRequest['id'],
                $oldData,
                $newData,
                $this->shared->user->um_id
            );


            $result = $user->baseToArray();
            $instansi = $user->instansi;
            $result['instansi'] = empty($instansi) ? null : $instansi;
            $result['email_notifikasi'] = $email_notifikasi;

            $this->db->commit();

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

    public function korwilList()
    {
        // $this->checkOneOfScopes(['korsupgah.monitoring', '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['role'] = array_key_exists('role', $getParams) ? $getParams['role'] : 'all';
        $getParams['id_jabatan'] = array_key_exists('id_jabatan', $getParams) ? $getParams['id_jabatan'] : null;
        $getParams['id_team'] = array_key_exists('id_team', $getParams) ? $getParams['id_team'] : null;
        $getParams['trashed'] = $getParams['trashed'] ?? 0;

        $validatedParams = $this->validate(
            $getParams,
            [
                'limit' => ['validators' => ['digit']],
                'offset' => ['validators' => ['digit']],
                'keyword' => [],
                'role' => ['validators' => [[
                    'name' => 'inclusion_in',
                    'params' => array_merge(Roles::KORWIL_KLOP_ROLES, ['all'])
                ]]],
                'id_jabatan' => ['validators' => ['digit']],
                'id_team' => ['validators' => ['digit']],
                'trashed' => ['validators' => ['digit']],
            ]
        );
        if ($validatedParams['role'] !== 'all') {
            $korwil_klop_role = Roles::find([
                'conditions' => 'name LIKE :name:',
                'bind'  => [
                    'name' => $validatedParams['role']
                ]
            ]);
        } else {
            $names = Roles::KORWIL_KLOP_ROLES;
            if (!$this->hasScopes(['korsupgah.monitoring'])) {
                $names = ['admin_klop_korsupgah'];
            }

            $korwil_klop_role = Roles::find([
                'conditions' => 'name in ({names:array})',
                'bind'  => [
                    'names' => $names
                ]
            ]);
        }

        $role_ids = [];

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

        if (empty($role_ids)) {
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Role terkait Korwil tidak ditemukan");
        }

        $role_ids_querystring = "{" . implode(', ', $role_ids) . "}";

        $queryParams = [
            'role_ids' => $role_ids_querystring,
            'limit' => $validatedParams['limit'],
            'offset' => $validatedParams['offset'],
            'keyword' => '%' . $validatedParams['keyword'] . '%'
        ];

        $countParams = [
            'role_ids' => $role_ids_querystring,
            'keyword' => '%' . $validatedParams['keyword'] . '%'
        ];

        $filterQuery = " ";
        if (!empty($validatedParams['id_jabatan'])) {
            $filterQuery .= " AND id_jabatan = :id_jabatan ";
            $queryParams['id_jabatan'] = $validatedParams['id_jabatan'];
            $countParams['id_jabatan'] = $validatedParams['id_jabatan'];
        }

        $joinQuery = " ";
        if (!empty($validatedParams['id_team'])) {
            $joinQuery = " JOIN jaga.korwil_team_user tu
                ON u.um_id = tu.user_id ";
            $filterQuery .= " AND tu.team_id = :id_team ";
            $queryParams['id_team'] = $validatedParams['id_team'];
            $countParams['id_team'] = $validatedParams['id_team'];
        }

        if (!$validatedParams['trashed']) {
            $filterQuery .= " AND u.is_active = TRUE";
        }

        $korwil_users = $this->rawQuery('
            SELECT u.um_id, u.um_user_name, u.nama, u.foto, u.uuid_user, u.is_banned, u.jenis_kelamin
            FROM jaga.um_user u ' . $joinQuery . '
            WHERE um_id IN (
                SELECT user_id
                FROM jaga.users_roles
                WHERE user_id = u.um_id
                AND role_id = ANY (:role_ids)
            ) AND (nama ilike :keyword or um_user_name ilike :keyword or email ilike :keyword) 
             ' . $filterQuery . ' 
            LIMIT :limit
            OFFSET :offset
        ', $queryParams);

        $total_korwil_users = $this->rawQuery(
            '
            SELECT count(*) as total
            FROM jaga.um_user u ' . $joinQuery . '
            WHERE um_id IN (
                SELECT user_id
                FROM jaga.users_roles
                WHERE user_id = u.um_id
                AND role_id = ANY (:role_ids)
            ) AND (nama ilike :keyword or um_user_name ilike :keyword or email ilike :keyword)
            ' . $filterQuery . ' 
        ',
            $countParams
        )[0]['total'];

        // $korwil_users = UmUser::query()
        //     ->join('UsersRoles', 'UsersRoles.user_id = UmUser.um_id')
        //     ->where('role_id in ({role_ids:array}) AND (UmUser.email ilike :keyword: OR UmUser.um_user_name like :keyword:)')
        //     ->bind([
        //         'keyword' => "%" . $validatedParams['keyword'] . "%",
        //         'role_ids' => $role_ids
        //     ])
        //     ->limit($validatedParams['limit'], $validatedParams['offset'])
        //     ->execute();

        // $total_korwil_users = (int) UmUser::query()
        //     ->columns('count(distinct um_id) as jumlah')
        //     ->join('UsersRoles', 'UsersRoles.user_id = UmUser.um_id')
        //     ->where('role_id in ({role_ids:array})')
        //     ->bind([
        //         'role_ids' => $role_ids
        //     ])
        //     ->execute()[0]['jumlah'];


        $result = [];

        foreach ($korwil_users as $key => $entry) {
            $user = UmUser::findByUmId($entry['um_id'])[0];
            $current_user = $user->baseToArray();
            $instansi = $user->instansi;
            $current_user['instansi'] = empty($instansi) ? null : $instansi;
            $jabatan = $user->jabatan;
            $current_user['jabatan'] = empty($jabatan) ? null : $jabatan;

            $bind["user_id"] = $entry['um_id'];
            $korwil_teams = KorwilTeamUser::query()
                ->columns('team_id, team_member_status_id, KorwilTeam.name as team_name, KorsupgahPeriode.nama as periode_name')
                ->join('KorwilTeam', 'KorwilTeam.id = KorwilTeamUser.team_id')
                ->join('KorsupgahPeriode', 'KorwilTeam.id_periode = KorsupgahPeriode.id')
                ->where('KorwilTeamUser.deleted_at is null AND KorwilTeamUser.user_id = :user_id:')
                ->bind($bind)
                ->orderBy('id_periode, name')
                ->execute()
                ->toArray();
            $current_user['tim'] = $korwil_teams;

            $result[] = $current_user;
        }

        return new PaginatedResponse($result, $total_korwil_users, $validatedParams['limit'], $validatedParams['offset']);
    }

    public function resetEmailPasswordKorwil()
    {
        $this->checkOneOfScopes(['korsupgah.admin', 'korsupgah.manage_user']);

        $rawBody = $this->request->getJsonRawBody(true);
        $validatedRequest = $this->validate(
            $rawBody,
            [
                'id' => ['validators' => ['required']],
                'nama' => ['validators' => ['required']],
                'email' => ['validators' => ['required', 'email']],
                'password' => ['validators' => []],
                'h_password' => ['validators' => []],
                'id_instansi' => ['validators' => []],
                'roles' => ['validators' => ['required']],
                'notify' => ['validators' => []],
                'id_teams' => ['validators' => []],
                'id_jabatan' => ['validators' => ['digit']]
            ]
        );

        try {
            $this->db->begin();
            $has_password = !empty($validatedRequest['password']) || !empty($validatedRequest['h_password']);
            $has_id_instansi = !empty($validatedRequest['id_instansi']);

            if ($has_password) {
                $pass = AuthHelper::decryptPassword($validatedRequest);
                AuthHelper::validatePassword($pass);
            }

            if ($has_id_instansi) {
                $instansi = Instansi::findFirst($validatedRequest['id_instansi']);

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

            $user = UmUser::findFirstByUmId($validatedRequest['id']);

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

            $oldData = LogModeration::extractUmUser($validatedRequest['id']);

            $isUserDataChanged = (
                $user->nama != $validatedRequest['nama']
                || $user->email != strtolower(trim($validatedRequest['email']))
                || $user->id_instansi != $validatedRequest['id_instansi']
                || $user->id_jabatan != $validatedRequest['id_jabatan']
            );

            $admin_role = Roles::findFirstByName('admin_sistem');

            $korwil_roles = Roles::find([
                'conditions' => 'name in ({names:array})',
                'bind'  => [
                    'names' => Roles::KORWIL_KLOP_ROLES
                ]
            ]);

            $korwil_role_ids = [];

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

            $roles = $user->roles;
            $current_user_role_ids = [];

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

            if (in_array($admin_role->id, $current_user_role_ids)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User memiliki role Admin Sistem");
            }

            $intersect_role_ids = array_intersect($current_user_role_ids, $korwil_role_ids);

            if (empty($intersect_role_ids)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User tidak terkait dengan Korwil");
            }

            $new_role_ids = array_intersect($validatedRequest['roles'], $korwil_role_ids);
            $other_current_role_ids = array_diff($validatedRequest['roles'], $korwil_role_ids);
            $final_new_role_ids = array_values(array_unique(array_merge($new_role_ids, $other_current_role_ids)));

            if (empty($new_role_ids)) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Role baru menyebabkan user tidak bisa mengakses modul korwil");
            }

            if (!$isUserDataChanged) $isUserDataChanged = count(array_diff($validatedRequest['roles'], $current_user_role_ids));

            $email = strtolower(trim($validatedRequest['email']));

            // Check email availability
            if (strtolower(trim($user->email)) != $email) {
                $duplicate_users = UmUser::find([
                    'conditions' => 'email ilike :email:',
                    'bind' => [
                        'email' => $email
                    ]
                ]);

                if (count($duplicate_users) > 0) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User dengan email $email telah terdaftar");
                }
            }

            $user->email = $email;
            $user->nama = $validatedRequest['nama'];
            if ($has_password) {
                $user->um_user_new_password = $this->security->hash($pass);
            }
            if ($has_id_instansi) {
                $user->id_instansi = $validatedRequest['id_instansi'];
            }
            $user->migration_status = UmUser::MIGRATION_STATUS_PASS_CHANGED;

            // set jabatan
            if (!empty($validatedRequest['id_jabatan'])) {
                $findRoleKemdagri = RolesScopes::query()
                    ->join("Scopes", "Scopes.id = RolesScopes.scope_id")
                    ->where('Scopes.name = "korwil.monitoring_kemdagri" ')
                    ->execute();

                $kemdagri_role_ids = [];

                foreach ($findRoleKemdagri as $row) {
                    $kemdagri_role_ids[] = $row->role_id;
                }
                $intersect_scope_ids = array_intersect($validatedRequest['roles'], $kemdagri_role_ids);
                if (sizeof($intersect_scope_ids) < 1) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User tidak memiliki scope diperlukan untuk mendapatkan jabatan kemdagri");
                }

                $user->id_jabatan = $validatedRequest['id_jabatan'];
            } else {
                // delete jabatan if already has jabatan and request param is empty
                if (!empty($user->id_jabatan)) {
                    $user->id_jabatan = null;
                }
            }

            $status = $user->save();

            // set roles
            $newRoles = Roles::find([
                'conditions' => 'id in ({roles:array})',
                'bind' => ['roles' => $final_new_role_ids]
            ]);

            $user->setRoles2($newRoles);

            // set tim
            if (!empty($validatedRequest['id_teams'])) {
                $findRoleVerif = RolesScopes::query()
                    ->join("Scopes", "Scopes.id = RolesScopes.scope_id")
                    ->where('Scopes.name = "korsupgah.verify" OR Scopes.name = "korwil.verifikator_kemdagri_bpkp" ')
                    ->execute();

                $verif_role_ids = [];

                foreach ($findRoleVerif as $row) {
                    $verif_role_ids[] = $row->role_id;
                }
                $intersect_scope_ids = array_intersect($validatedRequest['roles'], $verif_role_ids);
                if (sizeof($intersect_scope_ids) < 1) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User tidak memiliki scope diperlukan untuk register tim");
                }

                $team_ids = $validatedRequest['id_teams'];

                $teams = KorwilTeam::find([
                    'conditions' => 'id in ({ids:array}) and deleted_at is null',
                    'bind' => [
                        'ids' => array_values($team_ids)
                    ]
                ]);

                if (count($teams) !== count($team_ids)) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Terdapat ID tim yang tidak ditemukan');
                }

                // hard delete old data
                $deleteTeams = KorwilTeamUser::find([
                    'conditions' => 'user_id = :id: and deleted_at is null',
                    'bind' => [
                        'id' => $validatedRequest['id']
                    ]
                ]);

                if (!empty($deleteTeams)) {
                    foreach ($deleteTeams as $row) {
                        $status = $row->delete();
                        if (!$status) {
                            $errors = $row->getMessages();
                            $error_messages = [];

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

                            throw new ValidationException($error_messages);
                        }
                    }
                }


                // create new data
                foreach ($team_ids as $id) {
                    $memberStatus = KorwilTeamMemberStatus::find()[0];

                    $member = new KorwilTeamUser();
                    $member->team_id = $id;
                    $member->user_id = $validatedRequest['id'];
                    $member->team_member_status_id = $memberStatus->id;
                    $statusTim = $member->save();

                    if (!$statusTim) {
                        $errors = $member->getMessages();
                        $error_messages = [];

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

                        throw new ValidationException($error_messages);
                    }
                }
            } else {
                // delete user's tim if user already has tim and request param is empty
                $checkRows = KorwilTeamUser::find([
                    'conditions' => 'user_id=:id: AND deleted_at is NULL',
                    'bind' => [
                        'id' => $validatedRequest['id'],
                    ]
                ]);
                if (!empty($checkRows)) {
                    foreach ($checkRows as $row) {
                        $row->deleted_at = date('Y-m-d H:i:s');
                        $status = $row->delete();
                        if (!$status) {
                            $errors = $row->getMessages();
                            $error_messages = [];

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

                            throw new ValidationException($error_messages);
                        }
                    }
                }
            }

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

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

                throw new ValidationException($error_messages);
            } else {
                if (array_key_exists('notify', $validatedRequest)) {
                    if (!empty($validatedRequest['notify']) && $validatedRequest['notify']) {
                        $subject = "";
                        $message = "";

                        if ($isUserDataChanged && $has_password) {
                            $subject = "[JAGA] Perubahan Data dan Password Akun JAGA";
                            $message = "Hi Sahabat JAGA, terima kasih telah menggunakan Aplikasi JAGA."
                                . "\n\nKamu menerima email ini karena adanya perubahan data pada akun JAGA kamu. Berikut informasi perubahan data pada Akun kamu :"
                                . "\n\n-nama: " . $user->nama
                                . "\n-tanggal lahir: " . $user->tgl_lahir
                                . "\n-no tlp: " . $user->phone
                                . "\n-email: " . $user->email
                                . "\n\ndan melakukan permintaan perubahan password pada akun JAGA kamu. Berikut akses yang dapat digunakan untuk login ke Aplikasi JAGA:"
                                . "\n\n-Username: " . $user->um_user_name
                                . "\n-Password: " . $pass
                                . "\n\natau klik link berikut " . $this->config["web_base_url"]
                                . "\n\nJika ada ketidaksesuaian pada data di atas, kamu dapat mengubah data kamu pada halaman profil."
                                . "\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.";
                        } else if ($isUserDataChanged) {
                            $subject = "[JAGA] Perubahan Data Akun JAGA";
                            $message = "Hi Sahabat JAGA, terima kasih telah menggunakan Aplikasi JAGA."
                                . "\n\nKamu menerima email ini karena adanya perubahan data pada akun JAGA kamu. Berikut informasi perubahan data pada Akun kamu :"
                                . "\n\n-nama: " . $user->nama
                                . "\n-tanggal lahir: " . $user->tgl_lahir
                                . "\n-no tlp: " . $user->phone
                                . "\n-email: " . $user->email
                                . "\n\nJika ada ketidaksesuaian pada data di atas, kamu dapat mengubah data kamu pada halaman profil."
                                . "\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.";
                        } else if ($has_password) {
                            $subject = "[JAGA] Permintaan Perubahan Password diterima.";
                            $message = "Hi Sahabat JAGA, terima kasih telah menggunakan Aplikasi JAGA."
                                . "\n\nKamu menerima email ini karena telah melakukan permintaan perubahan password pada akun JAGA kamu. Berikut akses yang dapat digunakan untuk login ke Aplikasi JAGA:"
                                . "\n\n-Username: " . $user->um_user_name
                                . "\n-Password: " . $pass
                                . "\n\natau klik link berikut " . $this->config["web_base_url"]
                                . "\n\nJangan khawatir. Tingkatkan keamanan akun JAGA-mu dengan mengganti password setelah berhasil melakukan login. Perubahan password dapat dilakukan pada halaman profil."
                                . "\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.";
                        }

                        if ($isUserDataChanged || $has_password) {
                            $mail = new MailHelper($this->config["mail_config"]);
                            $mail->queue($rawBody["email"], $subject, $message, false, $this->shared->user->um_id);
                        }
                    }
                }

                $newData = LogModeration::extractUmUser($validatedRequest['id']);
                $action = [LogModeration::ACTION_UPDATE_UMUSER];
                if ($has_password) $action[] = LogModeration::ACTION_CHANGEPWD_UMUSER;
                LogModeration::loggedUmUser(
                    $action,
                    LogModeration::ACTIONMENU_ADMINISTRASI_KORWIL,
                    $user->um_id,
                    $oldData,
                    $newData,
                    $this->shared->user->um_id
                );

                $result = $user->baseToArray();
                $instansi = $user->instansi;
                $result['instansi'] = empty($instansi) ? null : $instansi;
                $jabatan = $user->jabatan;
                $result['jabatan'] = empty($jabatan) ? null : $jabatan;
                // tambah response tim di sini jika perlu

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

    public function registerKorwilUser()
    {
        $this->checkOneOfScopes(['korsupgah.admin', 'korsupgah.manage_user']);

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

        $validatedRequest = $this->validate(
            $rawBody,
            [
                'username' => ['validators' => ['required']],
                'email' => ['validators' => ['required', 'email']],
                'password' => ['validators' => []],
                'h_password' => ['validators' => []],
                'confirm_password' => ['validators' => []],
                'h_confirm_password' => ['validators' => []],
                'nama' => ['validators' => ['required']],
                'phone' => ['validators' => ['required', 'digit']],
                'jenis_kelamin' => ['validators' => []],
                'roles' => ['validators' => ['required']],
                'id_instansi' => ['validators' => []],
                'id_teams' => ['validators' => []],
                'id_jabatan' => ['validators' => ['digit']]
            ]
        );

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

            $pass = AuthHelper::decryptPassword($validatedRequest);
            $confirmPass = AuthHelper::decryptPassword($validatedRequest, 'confirm_password', 'h_confirm_password');
            AuthHelper::validatePassword($pass);

            if ($pass != $confirmPass) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Password dan konfirmasi tidak sama");
            }

            $has_id_instansi = !empty($validatedRequest['id_instansi']);

            if ($has_id_instansi) {
                $instansi = Instansi::findFirst($validatedRequest['id_instansi']);

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

            $tmpLahir = '-';
            $jk = $validatedRequest['jenis_kelamin'];
            $wso2Id = '';
            $oauthId = '';
            $twitterId = '';
            $facebookId = '';
            $googleId = '';
            $noKtp = '-'; //no_ktp_sim
            $hashed_password = base64_encode(hash("sha256", $pass, true));

            //Cek User and phone
            $errMsgs = [];
            if ($this->email_exists($validatedRequest['email'])) {
                $errMsgs[0] = array(
                    'field' => 'email',
                    'message' => 'Email has been used.'
                );
            }

            if ($this->phone_exists($validatedRequest['phone'])) {
                $errMsgs[count($errMsgs)] = array(
                    'field' => 'phone',
                    'message' => 'Phone has been used.'
                );
            }

            $validateUsername = UserHelper::validateUsername($validatedRequest["username"]);

            if ($validateUsername == 1) {
                $errMsgs[] = [
                    'field' => 'username',
                    'message' => 'Username length must be more than 6 characters.'
                ];
            }

            if ($validateUsername == 2) {
                $errMsgs[] = [
                    'field' => 'username',
                    'message' => 'Username must be consist of Alphanumeric, underscore, dash or dot only.'
                ];
            }

            if ($validateUsername == 3) {
                $errMsgs[] = [
                    'field' => 'username',
                    'message' => 'Username has been used.'
                ];
            }

            if (empty($validatedRequest['roles'])) {
                $errMsgs[count($errMsgs)] = array(
                    'field' => 'roles',
                    'message' => 'Roles must present'
                );
            }

            if (count($errMsgs) > 0) {
                throw new CustomErrorException(BaseResponse::REGISTRATION_ERROR, "Registrasi User Gagal", $errMsgs);
            }

            $korwil_roles = Roles::find([
                'conditions' => 'name in ({names:array})',
                'bind'  => [
                    'names' => (!$this->hasScopes(['korsupgah.admin']) ? ['admin_klop_korsupgah'] : Roles::KORWIL_KLOP_ROLES)
                ]
            ]);

            $korwil_role_ids = [];

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

            $intersect_role_ids = array_intersect($validatedRequest['roles'], $korwil_role_ids);

            if (sizeof($intersect_role_ids) < sizeof($validatedRequest['roles'])) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Terdapat role yang tidak terkait Korwil");
            }

            //periksa jika user yang diinput memiliki scope untuk masuk ke tim korwil
            if (!empty($validatedRequest['id_teams'])) {
                $findRoleVerif = RolesScopes::query()
                    ->join("Scopes", "Scopes.id = RolesScopes.scope_id")
                    ->where('Scopes.name = "korsupgah.verify" OR Scopes.name = "korwil.verifikator_kemdagri_bpkp" ')
                    ->execute();

                $verif_role_ids = [];

                foreach ($findRoleVerif as $row) {
                    $verif_role_ids[] = $row->role_id;
                }
                $intersect_scope_ids = array_intersect($validatedRequest['roles'], $verif_role_ids);
                if (sizeof($intersect_scope_ids) < 1) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User tidak memiliki scope diperlukan untuk register tim");
                }
            }

            //periksa jika user yang diinput memiliki scope untuk mendapatkan jabatan kemdagri
            if (!empty($validatedRequest['id_jabatan'])) {
                $findRoleKemdagri = RolesScopes::query()
                    ->join("Scopes", "Scopes.id = RolesScopes.scope_id")
                    ->where('Scopes.name = "korwil.monitoring_kemdagri" ')
                    ->execute();

                $kemdagri_role_ids = [];

                foreach ($findRoleKemdagri as $row) {
                    $kemdagri_role_ids[] = $row->role_id;
                }
                $intersect_scope_ids = array_intersect($validatedRequest['roles'], $kemdagri_role_ids);
                if (sizeof($intersect_scope_ids) < 1) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "User tidak memiliki scope diperlukan untuk mendapatkan jabatan kemdagri");
                }
            }

            $this->checkUsername($validatedRequest["username"]);

            $user = new UmUser();
            $user->um_changed_time = date("Y-m-d H:i:s");
            $user->um_tenant_id = -1234;
            $user->um_user_new_password = $this->security->hash($pass);
            $user->um_user_password = $hashed_password;
            $user->nama = $validatedRequest['nama'];
            $user->tempat_lahir = $tmpLahir;
            $user->email = $validatedRequest['email'];
            $user->um_user_name = $validatedRequest["username"];
            $user->phone = $validatedRequest['phone'];
            $user->wso2_id = $wso2Id;
            $user->oauth_id = $oauthId;
            $user->twitter_id = $twitterId;
            $user->facebook_id = $facebookId;
            $user->google_id = $googleId;
            $user->no_ktp_sim = $noKtp;
            $user->jenis_kelamin = $jk;
            if ($has_id_instansi) {
                $user->id_instansi = $validatedRequest['id_instansi'];
            }
            if (!empty($validatedRequest['id_jabatan'])) {
                $user->id_jabatan = $validatedRequest['id_jabatan'];
            }
            $statusUser = $user->save();

            if (!$statusUser) {
                $errors = $user->getMessages();
                $error_messages = [];

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

                throw new ValidationException($error_messages);
            }

            $new_user = UmUser::findFirstByEmail($validatedRequest['email']);

            $roles = Roles::find([
                'conditions' => 'id in ({roles:array})',
                'bind' => ['roles' => $intersect_role_ids]
            ]);

            $new_user->setRoles2($roles);

            //daftar user ke tim
            if (!empty($validatedRequest['id_teams'])) {
                $team_ids = $validatedRequest['id_teams'];
                $teams = KorwilTeam::find([
                    'conditions' => 'id in ({ids:array}) and deleted_at is null',
                    'bind' => [
                        'ids' => array_values($team_ids)
                    ]
                ]);

                if (count($teams) !== count($team_ids)) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, 'Terdapat ID tim yang tidak ditemukan');
                }

                // create new data
                foreach ($team_ids as $id) {
                    $memberStatus = KorwilTeamMemberStatus::find()[0];

                    $member = new KorwilTeamUser();
                    $member->team_id = $id;
                    $member->user_id = $new_user->um_id;
                    $member->team_member_status_id = $memberStatus->id;
                    $statusTim = $member->save();

                    if (!$statusTim) {
                        $errors = $member->getMessages();
                        $error_messages = [];

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

                        throw new ValidationException($error_messages);
                    }
                }
            }

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

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

        $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'] : '';

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

        $roleKemdagri = Roles::findFirst([
            'conditions' => 'name = ?0',
            'bind' => [
                'korwil_kemdagri'
            ]
        ]);

        if (empty($roleKemdagri)) throw new ValidationException("Role Kemdagri tidak ditemukan!");

        $userIds = array_column(
            UsersRoles::find([
                'conditions' => 'role_id = ?0',
                'bind' => [
                    $roleKemdagri->id
                ]
            ])->toArray(),
            'user_id'
        );

        $result = [];
        $count = 0;

        if (count($userIds) > 0) {
            $result = UmUser::find([
                'columns' => 'um_id, um_user_name, nama, foto, uuid_user,is_banned,jenis_kelamin',
                'conditions' => "um_id IN ({user_ids:array}) AND nama ilike :keyword: AND is_active = true",
                'bind' => [
                    'user_ids' => array_values($userIds),
                    'keyword' => '%' . $params['keyword'] . '%'
                ],
                'limit' => $params['limit'],
                'offset' => $params['offset']
            ]);

            $count = UmUser::count([
                'conditions' => "um_id IN ({user_ids:array}) AND is_active = true",
                'bind' => [
                    'user_ids' => array_values($userIds)
                ]
            ]);
        }

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

    public function listUserVerifikatorKppbc()
    {
        $this->checkOneOfScopes(['admin.view', 'pancek.admin', 'pancek.monitoring', 'pancek.kppbc.admin']);
        if ($this->hasScopes(['pancek.monitoring']) && !$this->hasScopes(['pancek.admin']) && !$this->hasScopes(['admin.view'])) {
            $idInstansiBeaCukai = Instansi::getIdInstansiDitjenBeaCukai();
            if ($this->shared->user->id_instansi != $idInstansiBeaCukai) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, "Hanya bisa diakses oleh Observer Pancek Bea Cukai");
            }
        }

        $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'] : '';

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

        $q = "
            SELECT 
            	uu.um_id, 
            	uu.um_user_name, 
            	uu.nama
            FROM um_user uu 
            LEFT JOIN users_roles ur ON uu.um_id = ur.user_id
            LEFT JOIN roles r ON r.id = ur.role_id
            WHERE r.name = :role_kppbc
            AND (uu.um_user_name ILIKE :keyword OR uu.nama ILIKE :keyword)
            GROUP BY 
            	uu.um_id, 
            	uu.um_user_name, 
            	uu.nama
            LIMIT :limit OFFSET :offset
        ";

        $qCount = "
            SELECT 
            	COUNT(uu.um_id) AS total
            FROM um_user uu 
            LEFT JOIN users_roles ur ON uu.um_id = ur.user_id
            LEFT JOIN roles r ON r.id = ur.role_id
            WHERE r.name = :role_kppbc
            AND (uu.um_user_name ILIKE :keyword OR uu.nama ILIKE :keyword)
            GROUP BY 
            	uu.um_id
        ";

        $bind = [
            'role_kppbc' => Roles::ROLE_PANCEK_VERIFIKATOR_KPPBC,
            'keyword' => '%' . $params['keyword'] . '%',
            'limit' => $params['limit'],
            'offset' => $params['offset'],
        ];

        $bindCount = [
            'role_kppbc' => Roles::ROLE_PANCEK_VERIFIKATOR_KPPBC,
            'keyword' => '%' . $params['keyword'] . '%',
        ];

        $result = $this->rawQuery($q, $bind);
        $countResult = $this->rawQuery($qCount, $bindCount);
        $count = empty($countResult) ? 0 : $countResult[0]['total'];

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

    public function listUserAssignorKppbc()
    {
        $this->checkOneOfScopes(['admin.view', 'pancek.admin', 'pancek.monitoring', 'pancek.kppbc.admin']);
        if ($this->hasScopes(['pancek.monitoring']) && !$this->hasScopes(['pancek.admin']) && !$this->hasScopes(['admin.view'])) {
            $idInstansiBeaCukai = Instansi::getIdInstansiDitjenBeaCukai();
            if ($this->shared->user->id_instansi != $idInstansiBeaCukai) {
                throw new CustomErrorException(BaseResponse::INVALID_AUTHENTICATION_AUTHORIZATION, "Hanya bisa diakses oleh Observer Pancek Bea Cukai");
            }
        }

        $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'] : '';

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

        $q = "
            SELECT 
            	uu.um_id, 
            	uu.um_user_name, 
            	uu.nama
            FROM um_user uu 
            LEFT JOIN users_roles ur ON uu.um_id = ur.user_id
            LEFT JOIN roles r ON r.id = ur.role_id
            WHERE r.name = :role_kppbc
            AND (uu.um_user_name ILIKE :keyword OR uu.nama ILIKE :keyword)
            GROUP BY 
            	uu.um_id, 
            	uu.um_user_name, 
            	uu.nama
            LIMIT :limit OFFSET :offset
        ";

        $qCount = "
            SELECT 
            	COUNT(uu.um_id) AS total
            FROM um_user uu 
            LEFT JOIN users_roles ur ON uu.um_id = ur.user_id
            LEFT JOIN roles r ON r.id = ur.role_id
            WHERE r.name = :role_kppbc
            AND (uu.um_user_name ILIKE :keyword OR uu.nama ILIKE :keyword)
            GROUP BY 
            	uu.um_id
        ";

        $bind = [
            'role_kppbc' => Roles::ROLE_PANCEK_ASSIGNOR_KPPBC,
            'keyword' => '%' . $params['keyword'] . '%',
            'limit' => $params['limit'],
            'offset' => $params['offset'],
        ];

        $bindCount = [
            'role_kppbc' => Roles::ROLE_PANCEK_ASSIGNOR_KPPBC,
            'keyword' => '%' . $params['keyword'] . '%',
        ];

        $result = $this->rawQuery($q, $bind);
        $countResult = $this->rawQuery($qCount, $bindCount);
        $count = empty($countResult) ? 0 : $countResult[0]['total'];

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

    // validasi dinamis,
    // jika regis socmed, required captcha
    // jika regis normal, required password
    public function deletionAction()
    {
        $this->checkIsAuthenticated();

        /** @var UmUser $user */
        $user = $this->shared->user;
        $umId = $user->um_id;
        if (!$user) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "User not found");
        }

        if (!$user->isPenggunaOnly()) {
            throw new CustomErrorException(BaseResponse::INVALID_ACTION, "User is not allowed to perform this action");
        }

        $rawBody = $this->request->getJsonRawBody(true);
        $isSocmed = $user->isRegisBySocmed();
        if ($isSocmed) { // regis socmed
            $rules = [
                'captcha_uuid' => ['validators' => ['required']],
                'captcha_answer' => ['validators' => ['required']]
            ];
        } else { // regis normal
            $rules = [
                'password' => ['validators' => []],
                'h_password' => ['validators' => []]
            ];
            $pass = AuthHelper::decryptPassword($rawBody);
        }
        $validatedRequest = $this->validate(
            $rawBody,
            $rules
        );

        if ($isSocmed) {
            /** @var $captcha Captcha */
            $captcha = Captcha::findFirst([
                'conditions' => 'uuid = :uuid:',
                'bind' => [
                    'uuid' => $validatedRequest['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($validatedRequest['captcha_answer'])) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Captcha doesn't match");
            }

            $captcha->markUsed();
        } else {
            if (!$this->security->checkHash($pass, $user->um_user_new_password)) {
                throw new CustomErrorException(BaseResponse::INVALID_PASSWORD, "Invalid Password");
            }
        }

        try {
            $this->db->begin();
            $oldData = LogModeration::extractUmUser($umId);
            // soft delete akun & ubah username (untuk handle username unique, sehingga username lama bisa digunakan utk register)
            $time = time();
            $user->is_active = false;
            $user->um_user_name = UserHelper::markDeletedUsername($user->um_user_name, $time);
            $user->email = UserHelper::markDeletedEmail($user->email, $time);
            $res = $user->save();
            if (!$res) {
                throw new CustomErrorException(BaseResponse::SQL_ERROR, $this->getModelErrors($user));
            }
            $newData = LogModeration::extractUmUser($umId);
            LogModeration::loggedUmUser(
                [LogModeration::ACTION_DELETE_UMUSER],
                LogModeration::ACTIONMENU_PROFILE,
                $umId,
                $oldData,
                $newData,
                $this->shared->user->um_id
            );

            $params = [
                "conditions" => "user_id = :um_id:",
                "bind" => [
                    "um_id" => $umId
                ]
            ];
            // soft delete diskusi
            $diskusis = Diskusi::find($params);
            foreach ($diskusis as $diskusi) {
                $diskusi->is_deleted = 1;
                $res = $diskusi->save();
                if (!$res) {
                    throw new CustomErrorException(BaseResponse::SQL_ERROR, $this->getModelErrors($diskusi));
                }
            }
            // soft delete komentar
            $komentars = DiskusiComment::find($params);
            foreach ($komentars as $komentar) {
                $komentar->is_deleted = 1;
                $res = $komentar->save();
                if (!$res) {
                    throw new CustomErrorException(BaseResponse::SQL_ERROR, $this->getModelErrors($komentar));
                }
            }
            // hard delete like diskusi
            $likes = DiskusiLike::find($params);
            foreach ($likes as $like) {
                $res = $like->delete();
                if (!$res) {
                    throw new CustomErrorException(BaseResponse::SQL_ERROR, $this->getModelErrors($like));
                }
            }
            // hard delete like komentar
            $komenLikes = DiskusiCommentLike::find($params);
            foreach ($komenLikes as $komenLike) {
                $res = $komenLike->delete();
                if (!$res) {
                    throw new CustomErrorException(BaseResponse::SQL_ERROR, $this->getModelErrors($komenLike));
                }
            }
            // hard delete share diskusi
            $shares = DiskusiShare::find($params);
            foreach ($shares as $share) {
                $res = $share->delete();
                if (!$res) {
                    throw new CustomErrorException(BaseResponse::SQL_ERROR, $this->getModelErrors($share));
                }
            }
            // hard delete laporan yg dibuat
            $laporans = DiskusiLapor::find([
                "conditions" => "id_user = :um_id:",
                "bind" => [
                    "um_id" => $umId
                ]
            ]);
            foreach ($laporans as $laporan) {
                $res = $laporan->delete();
                if (!$res) {
                    throw new CustomErrorException(BaseResponse::SQL_ERROR, $this->getModelErrors($laporan));
                }
            }
            // hard delete fcm_device_token
            $fcms = FcmDeviceToken::find($params);
            foreach ($fcms as $fcm) {
                $res = $fcm->delete();
                if (!$res) {
                    throw new CustomErrorException(BaseResponse::SQL_ERROR, $this->getModelErrors($fcm));
                }
            }
            // revoke jwt_data
            $jwts = JwtData::find($params);
            foreach ($jwts as $jwt) {
                $jwt->status = JwtData::STATUS_REVOKED;
                $res = $jwt->save();
                if (!$res) {
                    throw new CustomErrorException(BaseResponse::SQL_ERROR, $this->getModelErrors($jwt));
                }
            }

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

    public function adminDelete($umId)
    {
        $this->checkScopes(['admin.view']);
        $user = UmUser::findFirst($umId);
        if (!$user) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "User [$umId] tidak ditemukan.");
        }

        $this->db->begin();
        try {
            $oldData = LogModeration::extractUmUser($umId);
            UserHelper::deletion($user);
            $newData = LogModeration::extractUmUser($umId);
            LogModeration::loggedUmUser(
                [LogModeration::ACTION_DELETE_UMUSER],
                LogModeration::ACTIONMENU_ADMINISTRASI_JAGA,
                $umId,
                $oldData,
                $newData,
                $this->shared->user->um_id
            );
            $this->db->commit();
            return new SimpleResponse(1);
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function adminBatchDelete()
    {
        $this->checkScopes(['admin.view']);
        AuthHelper::validateToolsKey($this->request, $this->config);
        $rawBody = $this->request->getJsonRawBody(true);
        $validatedBody = $this->validate($rawBody, [
            'um_ids' => ['validators' => []]
        ]);
        $umIds = $validatedBody['um_ids'];
        try {
            $umIds = explode(";", $umIds);
        } catch (\Exception $e) {
            $umIds = [];
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Payload tidak valid.");
        }

        ini_set('max_execution_time', '600');
        set_time_limit(600);

        $this->db->begin();
        try {
            foreach ($umIds as $umId) {
                $this->validate(
                    [
                        "um_id" => $umId
                    ],
                    [
                        'um_id' => ['validators' => ['required', 'digit']]
                    ]
                );
                $user = UmUser::findFirst($umId);
                if (!$user) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "User [$umId] tidak ditemukan.");
                }
                $oldData = LogModeration::extractUmUser($umId);
                UserHelper::deletion($user);
                $newData = LogModeration::extractUmUser($umId);
                LogModeration::loggedUmUser(
                    [LogModeration::ACTION_DELETE_UMUSER],
                    LogModeration::ACTIONMENU_ADMINISTRASI_JAGA,
                    $umId,
                    $oldData,
                    $newData,
                    $this->shared->user->um_id
                );
            }
            $this->db->commit();
            return new SimpleResponse(count($umIds));
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    private function getModelErrors($model)
    {
        $errors = $model->getMessages();
        $error_messages = [];

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

        return $error_messages;
    }

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

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

        $getParams['keyword'] = array_key_exists('keyword', $getParams) ? $getParams['keyword'] : '';

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

        $result = UmUser::find([
            'columns' => 'um_id, um_user_name, nama',
            'conditions' => 'is_active = TRUE AND is_banned = FALSE AND (um_user_name ILIKE :keyword: OR nama ILIKE :keyword:)',
            'bind' => ['keyword' => '%' . $params['keyword'] . '%'],
            'order' => 'nama ASC'
        ]);

        return new SimpleResponse($result);
    }

    public function loginAsOtherUser()
    {
        $this->checkScopes(['admin.view']);
        try {
            if ($this->request->isPost()) {
                $validatedRequest = $this->validate(
                    $this->request->getJsonRawBody(true),
                    [
                        'username' => ['validators' => ['required']]
                    ]
                );
                $username = $validatedRequest['username'];
            } else {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Invalid request");
            }

            $result = UmUser::query()
                ->leftJoin(UsersSekolah::class, UsersSekolah::class . '.um_id = ' . UmUser::class . '.um_id')
                ->where("(lower(UmUser.um_user_name) = :username: or lower(UmUser.email) = :username: or " . UsersSekolah::class . ".npsn = :username:) AND UmUser.is_active = true")
                ->bind(['username' => strtolower($username)])
                ->execute();

            $success = false;
            $user = null;
            if ($result->count() > 0) {
                $success = true;
                $user = $result[$result->count() - 1];
            }

            $dataResp = new stdClass();
            $dataResp->success = $success;
            if ($success) {
                $data = $this->generateJwtToken($user, self::LOGIN_TYPE_ADMIN);
                $dataResp->data = $data;
            } else {
                $dataResp->message = 'Username Tidak ditemukan';
            }
            return new ObjectResponse($dataResp);
        } catch (AuthException $e) {
            $this->flash->error($e->getMessage());
        }
    }

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

        $rawBody = $this->request->getJsonRawBody(true);
        $validatedReqeust = $this->validate(
            $rawBody,
            [
                'email' => ['validators' => ['required']],
            ]
        );
        /** @var UmUser $umUser */
        $umUser = $this->shared->user;

        try {
            $this->db->begin();
            $verifCode = ReqUmUserEmail::reqChange($umUser, $validatedReqeust['email']);
            $subject = "[JAGA] Perubahan Email Akun JAGA";
            $message = "Hi Sahabat JAGA, terima kasih telah menggunakan Aplikasi JAGA."
                . "\n\nKamu menerima email ini karena adanya perubahan data pada akun JAGA kamu. Berikut informasi perubahan data pada Akun kamu :"
                . "\n\n-Nama: " . $umUser->nama
                . "\n-Username: " . $umUser->um_user_name
                . "\n-Email Lama: " . $umUser->email
                . "\n-Email Baru: " . $validatedReqeust["email"]
                . "\n\nPerubahan email pada akun JAGA kamu akan berlaku setelah kamu klik link verifikasi berikut " . $this->config["web_base_url"] . "/verification?code=" . $verifCode
                . "\n\nJika ada ketidaksesuaian pada data di atas, kamu dapat mengubah data kamu pada halaman profil."
                . "\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.";
            $message = "
                <div style='font-family: Roboto,Open Sans,sans-serif'>
                    <h4>Hi Sahabat JAGA, terima kasih telah menggunakan Aplikasi JAGA.</h4>
                    <p>Kamu menerima email ini karena adanya perubahan data pada akun JAGA kamu. Berikut informasi perubahan data pada Akun kamu :</p>
                    <p>Nama: " . $umUser->nama . "</p>
                    <p>Username: " . $umUser->um_user_name . "</p>
                    <p>Email Lama: " . $umUser->email . "</p>
                    <p>Email Baru: " . $validatedReqeust["email"] . "</p>
                    <p>Klik tombol berikut untuk melakukan konfirmasi perubahan email yang kamu ajukan:</p>
                    <div style='text-align: center'>
                        <a href='" . $this->config["web_base_url"] . "/verification?code=" . $verifCode . "' style='background-color: #ed1c24; border-radius: .5rem; cursor: pointer;display: inline-flex; flex-direction: column; align-items: stretch; position: relative; outline: 0; border: 0; vertical-align: middle; padding: 8px 11px; font-size: 14px; line-height: 1.715em; text-decoration: none; color: white; font-weight: 500; text-transform: uppercase; text-align: center; width: auto; height: auto'>
                            KONFIRMASI PERUBAHAN
                        </a>
                    </div>
                    <p>atau akses link berikut " . $this->config["web_base_url"] . "/verification?code=" . $verifCode . "</p>
                    <p>Bila ada kendala, hubungi WA JAGA (0811 989 7494) atau gunakan fitur chat pada situs jaga.id.</p>
                    <p>Jika ada ketidaksesuaian pada data di atas, kamu dapat mengubah data kamu pada halaman profil.</p>
                    <p>Jangan khawatir, semua data kamu aman di JAGA.</p>
                    <p>Akses JAGA dan bantu kami untuk Cegah Korupsi dari Ujung Jari.</p>
                    <p>Perhatian: 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.</p>
                </div>
            ";
            $mail = new MailHelper($this->config["mail_config"]);
            $mail->queue($validatedReqeust["email"], $subject, $message, true, $umUser->um_id);
            $this->db->commit();
            return new SimpleResponse(ReqUmUserEmail::INTERVAL);
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function reqRegisterEmail()
    {
        $rawBody = $this->request->getJsonRawBody(true);
        $validatedReqeust = $this->validate(
            $rawBody,
            [
                'email' => ['validators' => ['required', 'email']],
            ]
        );
        $email = $validatedReqeust["email"];
        $umUser = UmUser::findFirst([
            "email = :email: and is_active = true and is_banned = false and is_email_verified = false",
            "bind" => [
                "email" => $email
            ]
        ]);
        if (!$umUser) {
            throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "Email is not valid");
        }
        try {
            $this->db->begin();
            UserHelper::reqVerifyEmail(
                $umUser,
                $email,
                new MailHelper($this->config["mail_config"]),
                $this->config["web_base_url"]
            );
            $this->db->commit();
            return new SimpleResponse(ReqUmUserEmail::INTERVAL);
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

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

        $rawBody = $this->request->getJsonRawBody(true);
        $validatedReqeust = $this->validate(
            $rawBody,
            [
                'verify_code' => ['validators' => ['required']],
            ]
        );
        /** @var UmUser $umUser */
        $umUser = $this->shared->user;

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

            $reqUmUserEmail = ReqUmUserEmail::findByVerifCode($validatedReqeust["verify_code"]);
            if ($reqUmUserEmail->verifyCode($umUser)) {
                $umId = $umUser->um_id;
                $user = UmUser::findFirst(
                    [
                        "um_id = :um_id:",
                        "bind" => [
                            "um_id" => $umId
                        ]
                    ]
                );

                $oldData = LogModeration::extractUmUser($umId);
                $user->email = $reqUmUserEmail->new_value;
                $res = $user->save();
                if (!$res) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, $user->getMessages());
                }

                $this->rawQuery(
                    "update jaga.jwt_data set status = :status_revoked where status = :status_active and user_id = :um_id",
                    [
                        "status_revoked" => JwtData::STATUS_REVOKED,
                        "status_active" => JwtData::STATUS_ACTIVE,
                        "um_id" => $umId
                    ]
                );

                $newData = LogModeration::extractUmUser($umId);
                $action = [LogModeration::ACTION_CHANGEEMAIL_UMUSER];
                LogModeration::loggedUmUser(
                    $action,
                    LogModeration::ACTIONMENU_PROFILE,
                    $umId,
                    $oldData,
                    $newData,
                    $this->shared->user->um_id
                );

                $this->db->commit();
                return new SimpleResponse([]);
            } else {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Kode verifikasi tidak valid.");
            }
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function verifRegisterEmail()
    {
        $rawBody = $this->request->getJsonRawBody(true);
        $validatedReqeust = $this->validate(
            $rawBody,
            [
                'verify_code' => ['validators' => ['required']],
            ]
        );

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

            $reqUmUserEmail = ReqUmUserEmail::findByVerifCode($validatedReqeust["verify_code"]);
            /** @var UmUser $umUser */
            $umUser = UmUser::findFirst([
                "um_id = :um_id:",
                "bind" => ["um_id" => $reqUmUserEmail->um_id]
            ]);
            if (!$umUser) {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Kode verifikasi tidak valid.");
            }
            if ($reqUmUserEmail->verifyCode($umUser)) {
                $umId = $umUser->um_id;
                $umUser->is_email_verified = true;
                $res = $umUser->save();
                if (!$res) {
                    throw new CustomErrorException(BaseResponse::INVALID_REQUEST, $umUser->getMessages());
                }

                $this->rawQuery(
                    "update jaga.jwt_data set status = :status_revoked where status = :status_active and user_id = :um_id",
                    [
                        "status_revoked" => JwtData::STATUS_REVOKED,
                        "status_active" => JwtData::STATUS_ACTIVE,
                        "um_id" => $umId
                    ]
                );

                $this->db->commit();
                return new SimpleResponse([]);
            } else {
                throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Kode verifikasi tidak valid.");
            }
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function adminBatchUpdatePassword()
    {
        $this->checkScopes(['admin.view']);
        AuthHelper::validateToolsKey($this->request, $this->config);
        $rawBody = $this->request->getJsonRawBody(true);
        $validatedBody = $this->validate($rawBody, [
            'um_ids' => ['validators' => []],
            'password' => ['validators' => ['required']]
        ]);
        $umIds = $validatedBody['um_ids'];
        try {
            $umIds = explode(";", $umIds);
        } catch (\Exception $e) {
            $umIds = [];
            throw new CustomErrorException(BaseResponse::INVALID_REQUEST, "Payload tidak valid.");
        }

        $this->db->begin();
        try {
            foreach ($umIds as $umId) {
                $this->validate(
                    [
                        "um_id" => $umId
                    ],
                    [
                        'um_id' => ['validators' => ['required', 'digit']]
                    ]
                );
                $oldData = LogModeration::extractUmUser($umId);
                $user = UmUser::findFirst($umId);
                if (!$user) {
                    throw new CustomErrorException(BaseResponse::DATA_NOT_FOUND, "User [$umId] tidak ditemukan.");
                }
                $hashed_password = base64_encode(hash("sha256", $rawBody["password"], true));
                $user->um_user_new_password = $this->security->hash($rawBody["password"]);
                $user->um_user_password = $hashed_password;
                if (!$user->save()) {
                    throw new CustomErrorException(BaseResponse::UNEXPECTED_ERROR, $user->getMessages());
                }
                $newData = LogModeration::extractUmUser($umId);
                LogModeration::loggedUmUser(
                    [LogModeration::ACTION_CHANGEPWD_UMUSER],
                    LogModeration::ACTIONMENU_ADMINISTRASI_JAGA,
                    $umId,
                    $oldData,
                    $newData,
                    $this->shared->user->um_id
                );
            }
            $this->db->commit();
            return new SimpleResponse(count($umIds));
        } catch (\Exception $e) {
            $this->db->rollback();
            throw $e;
        }
    }

    public function checkEmail()
    {
        $params = $this->request->get();
        $validatedRequest = $this->validate(
            $params,
            [
                'email' => ['validators' => ['required', 'email']]
            ]
        );

        $email = $validatedRequest["email"];
        return new SimpleResponse($this->email_exists($email));
    }

    private function hasAdminRole($roles)
    {
        $hasAdminRole = false;
        foreach ($roles as $role) {
            /** @var Roles $role */
            if ($role->name != 'pengguna') {
                $hasAdminRole = true;
                break;
            }
        }

        return $hasAdminRole;
    }
}