<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of login
 *
 * @author Lenovo
 */
class ManageUsers {

    //put your code here

    private $conn;
    private $mcrypt;
    private $user_account = "user_account";
    // object properties
    public $user_id;
    public $login_id;
    public $mobile_no;
    public $password;
    public $status = 'Active';
    public $login_flag = 0;
    public $date_time;
    public $last_name = '';
    public $designation = '';
    public $grade = '';
    public $department = '';
    public $project = '';
    public $location = '';
    public $user_image = '';
    public $biography = '';
    public $last_updated_ts;

    // constructor
    public function __construct() {
        $this->conn = DBConnection();
        $this->mcrypt = new MCrypt();
    }

    /*
     * Check Username
     *
     * @param $username
     * @return boolean
     * */

    public function isUsername($username) {
        try {
            $query = $this->conn->prepare("SELECT user_id FROM " . $this->user_account . " WHERE login_id=:login_id");
            $query->bindParam("login_id", $username, PDO::PARAM_STR);
            $query->execute();
            if ($query->rowCount() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Check Email
     *
     * @param $email
     * @return boolean
     * */

    public function isEmail($email) {
        try {
            $query = $this->conn->prepare("SELECT user_id FROM " . $this->user_account . " WHERE email_id=:email_id");
            $query->bindParam("email_id", $email, PDO::PARAM_STR);
            $query->execute();
            if ($query->rowCount() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Check Mobile
     *
     * @param $mobile
     * @return boolean
     * */

    public function isMobile($mobile) {
        try {
            $query = $this->conn->prepare("SELECT user_id FROM " . $this->user_account . " WHERE mobile_no=:mobile_no");
            $query->bindParam("mobile_no", $mobile, PDO::PARAM_STR);
            $query->execute();
            if ($query->rowCount() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Check Login Id
     *
     * @param $mobile
     * @return boolean
     * */

    public function isLoginId($loginid) {
        try {
            $query = $this->conn->prepare("SELECT user_id FROM " . $this->user_account . " WHERE login_id=:login_id");
            $query->bindParam("login_id", $loginid, PDO::PARAM_STR);
            $query->execute();
            if ($query->rowCount() > 0) {
                return true;
            } else {
                return false;
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Login
     *
     * @param $username, $password
     * @return $mixed
     * */

    public function Login_corp($username, $password, $date) {
        try {
            $error = "";
            $query = $this->conn->prepare("SELECT ua.user_id,ua.status,ua.login_flag,ua.last_gen_pw_ts,ua.company_id,c.status as status1,allowed_device_type,count,cfg.attempts AS attempts FROM " . $this->user_account . " as ua JOIN company c on c.company_id=ua.company_id LEFT JOIN configuration cfg ON ua.company_id = cfg.company_id WHERE (ua.mobile_no=:username OR ua.email_id=:username1 OR ua.login_id=:username2) AND ua.password=:password");
            $query->bindParam("username", $username, PDO::PARAM_STR);
            $query->bindParam("username1", $username, PDO::PARAM_STR);
            $query->bindParam("username2", $username, PDO::PARAM_STR);
            $enc_password = $this->mcrypt->encrypt($password);
            $query->bindParam("password", $enc_password, PDO::PARAM_STR);
            $query->execute();
            if ($query->rowCount() > 0) {
                $result = $query->fetch(PDO::FETCH_OBJ);

                if ($result->status1 == 'Active') {
                    if ($result->status == 'Inactive') {
                        return 'error1';
                    } else if ($result->status == 'Deleted') {
                        return 'error3';
                    } else if ($result->allowed_device_type == 'A') {
                        return 'error6';
                    } else {
                        $return_error_code = $this->check_license($result->company_id, $date);
                        if ($return_error_code == false) {
                            if ($result->login_flag == 1 && !is_null($result->count) && !is_null($result->attempts) && $result->count >= $result->attempts) {
                                return 'error7';
                            } else {
                                $this->resetFailedAttempts($result->user_id, $date);
                                return $result->user_id;
                            }
                        } else {
                            return 'error5';
                        }
                    }
                } else {
                    return 'error4';
                }
            } else {
                // LockOut feature
                $failedAttemptQuery = $this->conn->prepare("SELECT ua.user_id, ua.count, ua.last_attempted_ts, 
                    cfg.attempts AS attempts, cfg.time_gap AS time_gap
                FROM user_account AS ua 
                LEFT JOIN configuration cfg ON ua.company_id = cfg.company_id
                WHERE ua.mobile_no = :username OR ua.email_id = :username1 OR ua.login_id = :username2;
                ");
                $failedAttemptQuery->bindParam("username", $username, PDO::PARAM_STR);
                $failedAttemptQuery->bindParam("username1", $username, PDO::PARAM_STR);
                $failedAttemptQuery->bindParam("username2", $username, PDO::PARAM_STR);

                $failedAttemptQuery->execute();
                $failedData = $failedAttemptQuery->fetch(PDO::FETCH_OBJ);
                $count = 0;
                if ($failedData) {
                    $count = (!is_null($failedData->count)) ? (int) $failedData->count : 0;
                    if ($failedData->attempts != NULL || $failedData->attempts > 0) {  // add null check and display remaining attempt in current attempt
                        $count = $count + 1;
                        $this->incrementFailedAttempts($failedData->user_id, $date, $count);
                        $remainingAttempts = $failedData->attempts - $count;
                        // Lock the account if max attempts are reached
                        if ($count >= $failedData->attempts) {
                            return 'error7';
                        }
                        return array("error_code" => "error8", "remaining_attempts" => $remainingAttempts);
                    }
                }
                return 'error2';
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    // Increment failed attempts for Lockout feature
    private function incrementFailedAttempts($user_id, $date, $client_count) {
        $query = $this->conn->prepare("
            UPDATE " . $this->user_account . " 
            SET count = :new_count, last_attempted_ts = :date,last_updated_ts = :date1, 
                last_updated_user = :user_id
            WHERE user_id = :user_id1
        ");
        $query->bindParam("new_count", $client_count, PDO::PARAM_INT);
        $query->bindParam("date", $date, PDO::PARAM_STR);
        $query->bindParam("user_id", $user_id, PDO::PARAM_INT);
        $query->bindParam(":date1", $date, PDO::PARAM_STR);
        $query->bindParam(":user_id1", $user_id, PDO::PARAM_INT);
        $query->execute();
    }

    // Reset failed attempts for Lockout feature
    private function resetFailedAttempts($user_id, $date) {
        $query = $this->conn->prepare("
            UPDATE " . $this->user_account . " 
            SET count = 0, last_attempted_ts = NULL,last_updated_ts = :date, last_updated_user = :user_id
            WHERE user_id = :user_id1
        ");
        $query->bindParam("user_id", $user_id, PDO::PARAM_INT);
        $query->bindParam(":user_id1", $user_id, PDO::PARAM_INT);
        $query->bindParam("date", $date, PDO::PARAM_STR);

        $query->execute();
    }

    public function check_license($company_id, $date) {
        try {
            $error = false;
            $query = $this->conn->prepare("select * from license_key where company_code=(Select company_code FROM company where company_id=:company_id)");
            $query->bindParam(":company_id", $company_id, PDO::PARAM_INT);
            $query->execute();
            if ($query->rowCount() > 0) {
                // return $query;
                $result = $query->fetch(PDO::FETCH_OBJ);
                $mcrypt = new MCrypt();
                $decrypted_start_date = $mcrypt->decrypt($result->start_date);
                $decrypted_start_date = str_replace('/', '-', $decrypted_start_date);
                $decrypted_date = $mcrypt->decrypt($result->end_date);
                $decrypted_date = str_replace('/', '-', $decrypted_date);
                $date = date("Y-m-d", strtotime($date));
                if (strtotime($decrypted_date) < strtotime($date)) {
                    $error = true;
                    return $error;
                }
                if (strtotime($decrypted_start_date) > strtotime($date)) {
                    $error = true;
                    return $error;
                }
            } else {
                $error = true;
            }
            return $error;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Login
     *
     * @param $username, $password
     * @return $mixed
     * */

    public function ForgotPassword($mobile) {
        try {
            $query = $this->conn->prepare("SELECT user_id,first_name,email_id,status,last_gen_pw_ts,mobile_no,sms,email  FROM " . $this->user_account . " "
                    . "as ua join configuration c on c.company_id=ua.company_id "
                    . " WHERE (mobile_no=:mobile1 OR email_id=:mobile2)");
            //$query->bindParam(":login_id", $username, PDO::PARAM_STR);
            $query->bindParam(":mobile1", $mobile, PDO::PARAM_STR);
            $query->bindParam(":mobile2", $mobile, PDO::PARAM_STR);
            $query->execute();
            return $query;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function ForgotUsername($mobile) {
        try {
            $error = "";
            $query = $this->conn->prepare("SELECT user_id,first_name,email_id,status,login_id,mobile_no,sms,email FROM " . $this->user_account . " as ua join configuration c on c.company_id=ua.company_id WHERE email_id=:mobile1 OR mobile_no=:mobile2");
            $query->bindParam(":mobile1", $mobile, PDO::PARAM_STR);
            $query->bindParam(":mobile2", $mobile, PDO::PARAM_STR);
            $query->execute();
            return $query;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * get User Details
     *
     * @param $user_id
     * @return $mixed
     * */

    public function UserDetails($user_id) {
        try {
//            select * from user_account as ua join company c on c.company_id=ua.company_id left join department d on d.department_id =ua.department_id where ua.user_id=9
            $query = $this->conn->prepare("SELECT dm.designation_name as designation,ua.*,c.name,d.dept_name FROM " . $this->user_account . " as ua "
                    . "JOIN company c on c.company_id=ua.company_id "
                    . "LEFT JOIN department d on d.department_id =ua.department_id "
                    . "LEFT JOIN designation_master dm on dm.designation_id =ua.designation_id "
                    . " where ua.user_id=:user_id");
            $query->bindParam("user_id", $user_id, PDO::PARAM_STR);
            $query->execute();
            return $query;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    //added 15/3/2022
    public function UserCompanyDetails($user_id) {
        try {
            $query = $this->conn->prepare("SELECT cc.* FROM " . $this->user_account . " as ua,company as cc "
                    . "WHERE ua.user_id=:user_id AND cc.company_id=ua.company_id");
            $query->bindParam("user_id", $user_id, PDO::PARAM_STR);
            $query->execute();
            return $query;
        } catch (PDOException $e) {
            //exit($e->getMessage());
            error_log("Caught $e");
        }
    }

    public function getUserImageSize($user_id) {
        try {
//            select * from user_account as ua join company c on c.company_id=ua.company_id left join department d on d.department_id =ua.department_id where ua.user_id=9
            $query = $this->conn->prepare("SELECT * FROM configuration "
                    . "where company_id=(select company_id from user_account where user_id=:user_id)");
            $query->bindParam("user_id", $user_id, PDO::PARAM_STR);
            $query->execute();
            return $query;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Check user login flag
     *
     * @param $user_id
     * @return $mixed
     * */

    public function CheckUserLoginFlag($user_id) {
        try {
            $query = $this->conn->prepare("SELECT login_flag FROM " . $this->user_account . " WHERE user_id=:user_id");
            $query->bindParam("user_id", $user_id, PDO::PARAM_STR);
            $query->execute();
            if ($query->rowCount() > 0) {
                $result = $query->fetch(PDO::FETCH_OBJ);
                if ($result->login_flag == 0) {
                    return 0;
                } else {
                    return 1;
                }
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Update user last_loggedd_in after login
     *
     * @param $user_id
     * @return $mixed
     * */

    public function UpdateUserLastLoggedIN($user_id, $date) {
        try {
            $query = $this->conn->prepare("UPDATE " . $this->user_account . " SET  last_logged_in= :date WHERE user_id= :user_id");
            $query->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            $query->bindParam(':date', $date, PDO::PARAM_STR);
            if ($query->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Update user login_track after login
     *
     * @param $user_id
     * @return $mixed
     * */

    public function UpdateUserTrackLoggedIN($user_id, $ip_address, $browser, $os, $device_name, $date) {
        try {
            $query = $this->conn->prepare("select * from user_activity_tracker where user_id = :user_id");
            $query->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            $query->execute();
            if ($query->rowCount() > 0) {

                $query1 = $this->conn->prepare("UPDATE user_activity_tracker SET activity_type='Login',ip_address=:ip,device_type='Desktop',"
                        . "device_name=:device_name,browser=:browser,os=:os,android_version=null,last_logged_in=:date WHERE user_id=:user_id");
                $query1->bindParam(':user_id', $user_id, PDO::PARAM_INT);
                $query1->bindParam(':date', $date, PDO::PARAM_STR);
                $query1->bindParam(':ip', $ip_address, PDO::PARAM_STR);
                $query1->bindParam(':browser', $browser, PDO::PARAM_STR);
                $query1->bindParam(':os', $os, PDO::PARAM_STR);
                $query1->bindParam(':device_name', $device_name, PDO::PARAM_STR);
                if ($query1->execute()) {
                    return true;
                }
                return false;
            } else {

                $query1 = $this->conn->prepare("INSERT into user_activity_tracker (user_id ,activity_type,ip_address,device_type,device_name,"
                        . "browser,os,android_version,last_logged_in) VALUES (:user_id,'Login',:ip,'Desktop',:device_name,:browser,:os,null,:date)");
                $query1->bindParam(':user_id', $user_id, PDO::PARAM_INT);
                $query1->bindParam(':date', $date, PDO::PARAM_STR);
                $query1->bindParam(':ip', $ip_address, PDO::PARAM_STR);
                $query1->bindParam(':browser', $browser, PDO::PARAM_STR);
                $query1->bindParam(':os', $os, PDO::PARAM_STR);
                $query1->bindParam(':device_name', $device_name, PDO::PARAM_STR);
                if ($query1->execute()) {
                    return true;
                }
                return false;
            }
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Update user password after forgot password
     *
     * @param $user_id
     * @return $mixed
     * */

    public function UpdateUserPasword($user_id, $date, $password, $login_flag) {
        try {
            $query = $this->conn->prepare("UPDATE " . $this->user_account . " SET login_flag=:login_flag, password=:password, last_gen_pw_ts=:date1 WHERE user_id= :user_id");
            $query->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            $query->bindParam(':date1', $date, PDO::PARAM_STR);
            $query->bindParam(':password', $password, PDO::PARAM_STR);
            $query->bindParam(':login_flag', $login_flag, PDO::PARAM_STR);
            if ($query->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function ChangePassword($login_flag, $password, $date, $user_id) {
        try {
            $query = $this->conn->prepare("UPDATE " . $this->user_account . " SET login_flag=:login_flag, password=:password, last_updated_ts= :date1 WHERE user_id= :user_id");
            $query->bindParam(':login_flag', $login_flag, PDO::PARAM_INT);
            $enc_password = $this->mcrypt->encrypt($password);
            $query->bindParam("password", $enc_password, PDO::PARAM_STR);
            $query->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            $query->bindParam(':date1', $date, PDO::PARAM_STR);
            if ($query->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Get User Assigned Groups
     *
     * @param $user_id
     * @return $mixed
     * */

    public function AssignedGroups($user_id) {
        try {
            $query = $this->conn->prepare("SELECT ug.group_name,ug.status "
                    . "FROM user_group_allocation as uga,user_group as ug "
                    . "where uga.user_id=:user_id and uga.group_id=ug.group_id");
            $query->bindParam("user_id", $user_id, PDO::PARAM_STR);
            $query->execute();
            return $query;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Get User Assigned Roles
     *
     * @param $user_id
     * @return $mixed
     * */

    public function AssignedRoles($user_id) {
        try {
            $query = $this->conn->prepare("SELECT r.role_name,r.status "
                    . "FROM user_roles as ur,roles as r "
                    . "where ur.user_id=:user_id and ur.role_id=r.role_id");
            $query->bindParam("user_id", $user_id, PDO::PARAM_STR);
            $query->execute();
            return $query;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Get User Permissions
     *
     * @param $user_id
     * @return $mixed
     * */

    public function AssignedPermissions($user_id) {
        try {
            $query = $this->conn->prepare("SELECT s.screen_name,p.permission_name,usp.start_date,usp.end_date "
                    . "FROM user_spcl_permission as usp,screen_permission as sp, "
                    . "screens as s,permission as p"
                    . " where usp.user_id=:user_id "
                    . "and usp.screen_permission_id=sp.screen_permission_id "
                    . "and sp.screen_id=s.screen_id "
                    . "and sp.permission_id=p.permission_id");
            $query->bindParam("user_id", $user_id, PDO::PARAM_STR);
            $query->execute();
            return $query;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Get Password
     *
     * @param $user_id
     * @return $mixed
     * */

    public function getPassword($user_id) {
        try {
            $query = $this->conn->prepare("SELECT first_name,email_id,password from user_account where user_id=:user_id");
            $query->bindParam("user_id", $user_id, PDO::PARAM_STR);
            $query->execute();
            return $query;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Update Password
     *
     * @param $user_id
     * @return $mixed
     * */

    public function UpdatePassword($password, $user_id, $date) {
        try {
            $query = $this->conn->prepare("UPDATE " . $this->user_account . " SET password=:password, last_updated_ts= :date WHERE user_id= :user_id");
            $enc_password = $this->mcrypt->encrypt($password);
            $query->bindParam(":password", $enc_password, PDO::PARAM_STR);
            $query->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            $query->bindParam(':date', $date, PDO::PARAM_STR);
            if ($query->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    /*
     * Update Profile Image
     *
     * @param $user_id
     * @return $mixed
     * */

    public function UpdateProfileImage($user_image, $user_id, $date) {
        try {
            $query = $this->conn->prepare("UPDATE " . $this->user_account . " SET user_image=:user_image, last_updated_user=:last_updated_user,last_updated_ts= :date WHERE user_id= :user_id");
            $query->bindParam(":user_image", $user_image, PDO::PARAM_STR);
            $query->bindParam(':last_updated_user', $user_id, PDO::PARAM_INT);
            $query->bindParam(':user_id', $user_id, PDO::PARAM_INT);
            $query->bindParam(':date', $date, PDO::PARAM_STR);
            if ($query->execute()) {
                return true;
            }
            return false;
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

    public function getCompletionPoints() {
        try {
            $query = $this->conn->prepare("SELECT total_completion_points FROM " . $this->user_account
                    . "WHERE user_id=:user_id ");
            $query->bindParam("user_id", $user_id, PDO::PARAM_STR);
            // get row value
            $rows = $query->fetch(PDO::FETCH_NUM);
            // return count
            return $rows[0];
        } catch (PDOException $e) {
            //exit($e->getMessage());
            error_log("Caught $e");
        }
    }

    // Fetch password rules based on company ID 
    public function GetPasswordRules($companyId) {
        $query = "SELECT min_length, max_length, lower_case, uppercase, `numeric`, special_char
                  FROM company_password_rule WHERE company_id = :companyId";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':companyId', $companyId, PDO::PARAM_INT);
        $stmt->execute();
        $result = $stmt->fetch(PDO::FETCH_ASSOC);

        return $result ?: null;  // Return rules if found, otherwise null
    }
    
    //added on 08-12-2025 for AI feature
    public function getUserConfig($column) {
        try {
            $query = $this->conn->prepare("SELECT $column FROM " . $this->user_account . " "
                    . "as ua join configuration c on c.company_id=ua.company_id "
                    . " WHERE user_id=:user_id ");
            $query->bindParam(":user_id", $this->user_id, PDO::PARAM_INT);
            $query->execute();
            $rows = $query->fetch(PDO::FETCH_ASSOC);
         
            // return count
            return $rows[$column];
        } catch (PDOException $e) {
            error_log("Caught $e");
        }
    }

}
