<?php
    include_once("CUserSession.php");
    include_once("helper.php");
    include_once("CUtil.php");
    include_once("CProjectInfo.php");
    include_once("CEnums.php");

    require_once(__DIR__ . '/vendor/autoload.php');

    class User{

        public $_id;
        public $sl_no;
        public $email_id;
        public $password;
        public $salt;
        public $first_name;
        public $middle_name;
        public $last_name;
        public $organization_id;
        public $job_title;
        public $phone;
        public $country_name;
        public $country_code;
        public $authtype_id;
        public $securityquestion_id;
        public $securityquestion_text;
        public $securityquestion_ans;
        public $ts_created;
        public $reset_link;
        public $ts_reset;
        public $profile_change_link;
        public $ts_profile_change;
        public $ts_password_change;
        public $ts_last_login_success;
        public $login_success_count;
        public $ts_last_login_fail;
        public $ts_survey_completed;
        public $uses_sr;        // Flag for screen reader using users
        public $subscription_type;
        public $is_verified;
        public $signuprole_id;
        public $signupreason_id;
        public $date_created;
        public $free_trial_expiry_date;
        public $supporter_expiry_date;
        public $innovator_expiry_date;
        public $upgrade_date;
        

        function populateFromDBRow( $row ){
          
            $this->_id = $row["_id"];
            $this->sl_no = $row["sl_no"];
            $this->email_id = $row["email_id"];
            $this->password = $row["password"];
            $this->salt = $row["salt"];
            $this->first_name = $row["first_name"];
            $this->middle_name = $row["middle_name"];
            $this->last_name = $row["last_name"];
            $this->organization_id = $row["organization_id"];
            $this->job_title = $row["job_title"];
            $this->phone = $row["phone"];
            $this->country_name = $row["country_name"];
            $this->country_code = $row["country_code"];
            $this->authtype_id = $row["authtype_id"];
            $this->securityquestion_id = $row["securityquestion_id"];
            $this->securityquestion_text = $row["question"];
            $this->securityquestion_ans = $row["securityquestion_ans"];
            $this->ts_created = $row["ts_created"];
            $this->reset_link = $row["reset_link"];
            $this->ts_reset = $row["ts_reset"];
            $this->profile_change_link = $row["profile_change_link"];
            $this->ts_profile_change = $row["ts_profile_change"];
            $this->ts_password_change = $row["ts_password_change"];
            $this->ts_last_login_success = $row["ts_last_login_success"];
            $this->login_success_count = $row["login_success_count"];
            $this->ts_last_login_fail = $row["ts_last_login_fail"];
            $this->ts_survey_completed = $row["ts_survey_completed"];
            $this->uses_sr = $row["uses_sr"];
            $this->subscription_type = $row["subscription_type"];
            $this->is_verified = $row["is_verified"];
            $this->signuprole_id = $row["signuprole_id"];
            $this->signupreason_id = $row["signupreason_id"];
            $this->date_created = $row["date_created"];
            $this->free_trial_expiry_date = $row["free_trial_expiry_date"];
            $this->supporter_expiry_date = $row["supporter_expiry_date"];
            $this->innovator_expiry_date = $row["innovator_expiry_date"];
            $this->upgrade_date = $row["upgrade_date"];
        }

    }

    function getPublicUserData($user){
        $n_user = json_decode(json_encode($user));
        $n_user->_id = "";
        $n_user->salt = "";
        $n_user->password = "";
        $n_user->authtype_id = "";
        $n_user->sl_no = "";
        $n_user->securityquestion_ans = "";  //Hide security answer as well
        $n_user->securityquestion_id = "";
        $n_user->securityquestion_text = "";
        $n_user->reset_link = "";
        $n_user->profile_change_link = "";

        return $n_user;
    }
    // Get email id using user //
    function get_user_mail_from_db($user_id){
      $sql_conn = mysqli_connection();
      $user_id = Util::escapeObject($user_id);

      $s = "SELECT `email_id` FROM  `Users_temp` WHERE _id = '".$user_id."'";
      if (!($stmt = $sql_conn->prepare($s))){
          echo "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error. $s;
      }

      if (!($stmt->execute())) {
          echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
      }

      if (!($ret = $stmt->get_result())) {
          echo "Getting Result failed: (" . $stmt->errno . ") " . $stmt->error;
      }
      $row = mysqli_fetch_array($ret);

      $stmt->close();

      mysqli_close($sql_conn);
      $r = $row[0];
      //echo "hello : ". $r;
      return $r;
    }
    
        // Get subscription type  using user //
    function get_subscription_type_of_user($user_id){
    $sql_conn = mysqli_connection();
    $user_id = Util::escapeObject($user_id);

    $s = "SELECT `subsubscription_type` FROM  `Users_temp` WHERE _id = '".$user_id."'";
    if (!($stmt = $sql_conn->prepare($s))){
        echo "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error. $s;
    }

    if (!($stmt->execute())) {
        echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
    }

    if (!($ret = $stmt->get_result())) {
        echo "Getting Result failed: (" . $stmt->errno . ") " . $stmt->error;
    }
    $row = mysqli_fetch_array($ret);

    $stmt->close();

    mysqli_close($sql_conn);
    $r = $row[0];
    //echo "hello : ". $r;
    return $r;
    }
    
    // getUser('svangire@buffalo.edu');
    // Get user : Defaults to email search
    function getUser($clause, $where = 'email_id'){

        $sql_conn = mysqli_connection();
        $clause = Util::escapeObject($clause);

        $s = "SELECT * FROM  `Users_temp` INNER JOIN  `SecurityQuestions` ON (  `Users_temp`.`securityquestion_id` =  `SecurityQuestions`.`sl_no` ) WHERE `Users_temp`.`".$where."` = ? ";
        if (!($stmt = $sql_conn->prepare($s))){
            echo "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error. $s;
        }

        if(!($stmt->bind_param("s", $clause))){
            echo "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
        }

        if (!($stmt->execute())) {
            echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
        }

        if (!($ret = $stmt->get_result())) {
            echo "Getting Result failed: (" . $stmt->errno . ") " . $stmt->error;
        }

        $user = new User();
        $res = array();
        while($row = mysqli_fetch_array($ret)) {
            $user = new User();
            $user->populateFromDBRow($row);
            $res[] = $user;
        }

        $stmt->close();

        mysqli_close($sql_conn);
        $r = json_encode($res);
        // echo "hello : ". $r;
        return $r;

    }


    /*
         Insert a new user

        [sl_no], email_id, password, salt, first_name, middle_name, last_name
        organization_id, authtype_id, securityquestion_id, securityquestion_ans,
        [ts_created]
    */

    function insertNewUser( $user ){

        $sql_conn = mysqli_connection();
        $user = Util::escapeObject($user);

        if (!($stmt = $sql_conn->prepare("INSERT INTO `Users_temp`(email_id, password, salt, first_name, middle_name, last_name, organization_id, job_title, authtype_id, securityquestion_id, securityquestion_ans, free_trial_expiry_date ,phone, country_name, country_code, _id, uses_sr, signuprole_id, signupreason_id) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"))){
            $ret =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
        }

        if(!($stmt->bind_param("ssssssssiissssssiii",
                     $user->email_id,
                     $user->password,
                     $user->salt,
                     $user->first_name,
                     $user->middle_name,
                     $user->last_name,
                     $user->organization_id,
                     $user->job_title,
                     $user->authtype_id,
                     $user->securityquestion_id,
                     $user->securityquestion_ans,
                     $user->free_trial_expiry_date,
                     $user->phone,
                     $user->country_name,
                     $user->country_code,
                     $user->_id,
                     $user->uses_sr,
                     $user->signuprole_id,
                     $user->signupreason_id
                      ))){
            $ret =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
        }

        if (!($ret = $stmt->execute())) {
            $ret =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
        }

        $stmt->close();
        mysqli_close($sql_conn);
        return $ret;
    }


    /*
         Create a new user

        [sl_no], email_id, password, salt, first_name, middle_name, last_name
        organization_id, authtype_id, securityquestion_id, securityquestion_ans,
        [ts_created]
    */

    function createNewUser( $email_id, $password,
                            $first_name, $middle_name, $last_name,
                            $organization_id, $job_title,$authtype_id, $securityquestion_id, $securityquestion_ans, $free_trial_expiry_date, $phone="", $country_name="", $country_code="", $uses_sr = 0, $signuprole_id = 1, $signupreason_id=1 ){

        $sql_conn = mysqli_connection();

        $salt = Util::genRandText(10);
        $hashpassword = Util::hash_pbkdf2($password, $salt);

        $user = new User();
        $user->_id = strtolower($first_name)."-".Util::genRandText(10);
        $user->email_id = strtolower($email_id);
        $user->password = $hashpassword;
        $user->salt = $salt;
        $user->first_name = ucfirst(strtolower($first_name));
        $user->middle_name = ucfirst(strtolower($middle_name));
        $user->last_name = ucfirst(strtolower($last_name));
        $user->organization_id = $organization_id;
        $user->job_title = $job_title;
        $user->authtype_id = $authtype_id;
        $user->securityquestion_id = $securityquestion_id;
        $user->securityquestion_ans = strtolower($securityquestion_ans);
        $user->phone = $phone;
        $user->country_name = $country_name;
        $user->country_code = strtoupper($country_code);
        $user->uses_sr = $uses_sr;
        $user->signuprole_id = $signuprole_id;
        $user->signupreason_id = $signupreason_id;
        $user->free_trial_expiry_date = $free_trial_expiry_date;

        $user = Util::escapeObject($user);
        $response = insertNewUser($user);
        $ret['response'] = is_bool($response) && $response;
        $ret['message'] = $response;

        if (strpos($response, 'Duplicate') !== false) {
           $ret['message'] = "Duplicate User";
        } else if (strpos($response, ')') !== false) {
            $response = split(")", $response);
            $ret['message'] = $response[1];
        }
        return json_encode($ret);
    }

    function UpdateUser( $email_id, $password,
                            $first_name, $middle_name, $last_name,
                            $organization_id, $job_title,$authtype_id, $securityquestion_id, $securityquestion_ans, $phone="", $country_name="", $country_code="", $uses_sr = 0 ){

        $country_code = strtoupper($country_code);
        $sql_conn = mysqli_connection();
        $q_type = 1;
        $param = "sssssssss";
        $fields = " email_id = ?,
                    first_name = ?,
                    last_name = ?,
                    organization_id = ?,
                    job_title = ?,
                    phone = ?,
                    country_name = ?,
                    country_code = ?,
                    uses_sr = ?
                    ";

        if($securityquestion_ans !== ""){
            $q_type = 2;
            $fields .= ",securityquestion_id = ?,
                        securityquestion_ans = ?";
            $param .= "is";
        }

        if($password !== ""){
            $q_type = ($q_type === 2? 4 : 3);
            $fields .= ",password = ?,
                         ts_password_change = NOW()";
            $param .= "s";
        }

        $query = "UPDATE `Users_temp` SET ".$fields." WHERE _id = ?";
        $param .= "s";

        if (!($stmt = $sql_conn->prepare($query))){
            $resp["message"] =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            $resp["response"] = false;
        }

        if($q_type === 1 && !($stmt->bind_param($param,
                     $email_id,
                     $first_name,
                     $last_name,
                     $organization_id,
                     $job_title,
                     $phone,
                     $country_name,
                     $country_code,
                     $uses_sr,
                     $_SESSION["user"]->_id
                      ))){
            $resp["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            $resp["response"] = false;
        }

        if($q_type === 2 && !($stmt->bind_param($param,
                     $email_id,
                     $first_name,
                     $last_name,
                     $organization_id,
                     $job_title,
                     $phone,
                     $country_name,
                     $country_code,
                     $uses_sr,
                     $securityquestion_id,
                     $securityquestion_ans,
                     $_SESSION["user"]->_id
                      ))){
            $resp["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            $resp["response"] = false;
        }

        if($q_type === 4 && !($stmt->bind_param($param,
                     $email_id,
                     $first_name,
                     $last_name,
                     $organization_id,
                     $job_title,
                     $phone,
                     $country_name,
                     $country_code,
                     $uses_sr,
                     $securityquestion_id,
                     $securityquestion_ans,
                     Util::hash_pbkdf2($password, $_SESSION['user']->salt),
                     $_SESSION["user"]->_id
                      ))){
            $resp["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            $resp["response"] = false;
        }

        if($q_type === 3 && !($stmt->bind_param($param,
                     $email_id,
                     $first_name,
                     $last_name,
                     $organization_id,
                     $job_title,
                     $phone,
                     $country_name,
                     $country_code,
                     $uses_sr,
                     Util::hash_pbkdf2($password, $_SESSION['user']->salt),
                     $_SESSION["user"]->_id
                      ))){
            $resp["message"] =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            $resp["response"] = false;
        }

        if (!($ret = $stmt->execute())) {
            $resp["message"] =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $resp["response"] = false;
        }

        if($ret){
            $resp["response"] = true;
            $resp["message"] = "";
            $user = json_decode(getUser($_SESSION["user"]->_id,"_id"));
            $user = $user[0];
            $_SESSION["user"] = json_decode(json_encode($user));
        }

        $stmt->close();
        mysqli_close($sql_conn);
        return json_encode($resp);

    }

    // validateUser('svangire@buffalo.edu', 'Sreedhar.098');
    function validateUser( $username, $password ){
        $user = json_decode(getUser($username));
        $user = $user[0];
        $hashpassword = Util::hash_pbkdf2($password, $user->salt);

        $ret['response'] = !is_null($user) && ($hashpassword === $user->password);
        $ret['message'] = 'SUCCESS';
        $ret['first_name'] = $user->first_name;
        $ret['last_name'] = $user->last_name;
        $ret['authtype_id'] = $user->authtype_id;
        $ret['is_verified'] = $user->is_verified;
        $ret['email_id'] = $user->email_id;
        $ret['subscription_type'] = $user->subscription_type;

        $sql_conn = mysqli_connection();

        if(is_null($user)){
            $ret['message'] = "NO USER";
        } else if (($hashpassword !== $user->password)){
            $ret['message'] = "PASSWORD MISMATCH";

            if (!($stmt = $sql_conn->prepare("UPDATE `Users_temp` SET ts_last_login_fail = NOW() WHERE email_id = ?"))){
                $sql_ret =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            }

            if(!($stmt->bind_param("s",
                        $username ))){
                $sql_ret =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            if (!($sql_ret = $stmt->execute())) {
                $sql_ret =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            $stmt->close();
            mysqli_close($sql_conn);

        } else{
            // print($user->last_name);
            // die();
            loginUser($user);
            // echo(isLoggedIn());
            // echo(isVerified());
            // var_dump($_SESSION["user"]);

            // to do: create dummy project if login_success_count == 0 i.e. user logs in for the first time
            if ($user->login_success_count === 0)
            {
                // create dummy project
                //createDemoProject();
            }

            if (!($stmt = $sql_conn->prepare("UPDATE `Users_temp` SET ts_last_login_success = NOW(), `login_success_count` = `login_success_count` + 1 WHERE email_id = ?"))){
                $sql_ret =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            }

            if(!($stmt->bind_param("s",
                        $username ))){
                $sql_ret =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            if (!($sql_ret = $stmt->execute())) {
                $sql_ret =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            $stmt->close();
            mysqli_close($sql_conn);
        }
        // print(json_encode($ret));
        return json_encode($ret);
    }

    function checkPassword($username, $password){
        $user = json_decode(getUser($username));
        $user = $user[0];
        $hashpassword = Util::hash_pbkdf2($password, $user->salt);

        $ret['response'] = !is_null($user) && ($hashpassword === $user->password);
        $ret['message'] = 'SUCCESS';
        $ret['first_name'] = $user->first_name;
        $ret['last_name'] = $user->last_name;

        if(is_null($user)){
            $ret['message'] = "NO USER";
        } else if (($hashpassword !== $user->password)){
            $ret['message'] = "PASSWORD MISMATCH";
        } else{
            $micro = microtime();
            $micro = str_replace(' ', '', $micro);
            $profile_change_link = Util::hash_pbkdf2($username.$micro, $user->salt);

            $sql_conn = mysqli_connection();
            $profile_change_link = Util::escapeObject($profile_change_link);
            $current_ts = "NOW()";
            if (!($stmt = $sql_conn->prepare("UPDATE `Users_temp` SET profile_change_link = ?, ts_profile_change = NOW() WHERE email_id = ?"))){
                $sql_ret =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            }

            if(!($stmt->bind_param("ss",
                        $profile_change_link,
                        $username ))){
                $sql_ret =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            if (!($sql_ret = $stmt->execute())) {
                $sql_ret =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            $stmt->close();
            mysqli_close($sql_conn);

            $ret['link'] = $profile_change_link;
            $ret['response'] = true;
        }
        return json_encode($ret);
    }

    /*
     *  Password reset flow
     *  Prep -> init -> retrieve -> conf
     *
     *
     *
     */



    function resetPassword_init($username, $securityquestion_ans){
        $user = json_decode(getUser($username));
        $user = $user[0];
        if(is_null($user)){
            $ret['message'] = "NO USER";
            $ret['response'] = false;
            echo (1);
        } else if (($username === $user->email_id) && ($securityquestion_ans !== $user->securityquestion_ans)){
            $ret['message'] = "SECURITY ANSWER MISMATCH";
            $ret['response'] = false;
        } else if (($username === $user->email_id) && ($securityquestion_ans === $user->securityquestion_ans)){
            $micro = microtime();
            $micro = str_replace(' ', '', $micro);
            $reset_link = Util::hash_pbkdf2($username.$micro, $user->salt);

            $sql_conn = mysqli_connection();
            $reset_link = Util::escapeObject($reset_link);
            $current_ts = "NOW()";
            if (!($stmt = $sql_conn->prepare("UPDATE `Users_temp` SET reset_link = ?, ts_reset = NOW() WHERE email_id = ?"))){
                $sql_ret =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            }

            if(!($stmt->bind_param("ss",
                        $reset_link,
                        $username ))){
                $sql_ret =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            if (!($sql_ret = $stmt->execute())) {
                $sql_ret =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            $stmt->close();
            mysqli_close($sql_conn);

            $ret['message'] = $reset_link;
            $ret['response'] = true;
        }
        return json_encode($ret);
    }

    function resetPassword_prep($email){
        $user = json_decode(getUser($email));
        $user = $user[0];

        if(is_null($user)){
            $ret['message'] = "NO SUCH USER";
            $ret['response'] = false;
        } else {
            //hide fields
            $n_user = new User();
            $n_user->email_id = $user->email_id;
            $n_user->securityquestion_text = $user->securityquestion_text;
            $ret['response'] = true;
        }

        $ret['data'] = $n_user;
        return json_encode($ret);
    }

    function resetPassword_retrieve($reset_link){
        $user = json_decode(getUser($reset_link, ""));
        $user = $user[0];

        if(is_null($user)){
            $ret['message'] = "NO SUCH USER";
            $ret['response'] = false;
        } else {
            //hide fields
            $user->password = "";
            $user->salt = "";
            $user->authtype_id = "";
            $ret['response'] = true;
        }

        $ret['data'] = $user;
        return json_encode($ret);
    }

    function resetPassword_conf($reset_link, $password, $confirm_password){
        $user = json_decode(getUser($reset_link, "reset_link"));
        $user = $user[0];
        $ret['response'] = false;
        if(is_null($user)){
            $ret['message'] = "NO SUCH LINK";
        } else if (strtotime($user->ts_reset) < strtotime('-3 day')){
            $ret['message'] = "LINK EXPIRED";
        } else if ($password !== Util::escapeObject($password)){
            $ret['message'] = "PASSWORD CONTAINS ILLEGAL CHARACTERS";
        } else if ($password !== $confirm_password){
            $ret['message'] = "PASSWORD MISMATCH";
        } else if ($password === $confirm_password){
            $salt = Util::genRandText(10);
            $password = Util::hash_pbkdf2($password, $salt);
            $sql_conn = mysqli_connection();
            $username = $user->email_id;

            if (!($stmt = $sql_conn->prepare("UPDATE `Users_temp` SET `password` = ?, `salt` = ?, `reset_link` = NULL, `ts_password_change` = NOW() WHERE email_id = ?"))){
                $sql_ret =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            }

            if(!($stmt->bind_param("sss",
                        $password,
                        $salt,
                        $username ))){
                $sql_ret =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            if (!($sql_ret = $stmt->execute())) {
                $sql_ret =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            $stmt->close();
            mysqli_close($sql_conn);

            $ret['message'] = "PASSWORD RESET SUCCESS";
            $ret['response'] = ($sql_ret === true);

        }

        return json_encode($ret);
    }

    function updateSurveyStatus($complete){
        if(isLoggedIn()){
            $sql_conn = mysqli_connection();
            $val = $complete ? "NOW()" : "NULL";
            if(!($stmt = $sql_conn->prepare('UPDATE `Users_temp` SET `ts_survey_completed` = '.$val.' WHERE `_id` = "'.$_SESSION["user"]->_id.'"'))){
                $sql_ret =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            }

            if (!($sql_ret = $stmt->execute())) {
                $sql_ret =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            $stmt->close();
            mysqli_close($sql_conn);

            $ret['response'] = ($sql_ret === true);
            return json_encode($ret);
        }
    }
//getAllUsers();
    function getAllUsers($public = true){
        $sql_conn = mysqli_connection();
        $clause = Util::escapeObject($clause);

        $s = "SELECT * FROM  `Users_temp`";
        //Edited to contain total no of projects for each user.

        // $s= "SELECT usersTable._id
        //         ,usersTable.email_id
        //         ,usersTable.password
        //         ,usersTable.salt
        //         ,usersTable.first_name
        //         ,usersTable.middle_name
        //         ,usersTable.last_name
        //         ,usersTable.organization_id
        //         ,usersTable.phone
        //         ,usersTable.country_name
        //         ,usersTable.country_code
        //         ,usersTable.authtype_id
        //         ,usersTable.securityquestion_id
        //         ,usersTable.securityquestion_ans
        //         ,usersTable.ts_created
        //         ,usersTable.reset_link
        //         ,usersTable.ts_reset
        //         ,usersTable.profile_change_link
        //         ,usersTable.ts_profile_change
        //         ,usersTable.ts_password_change
        //         ,usersTable.ts_last_login_success
        //         ,usersTable.login_success_count
        //         ,usersTable.ts_last_login_fail
        //         ,usersTable.ts_survey_completed
        //         ,usersTable.uses_sr
        //         ,usersTable.subscription_type
        //         ,count(*) as projCounts
        //     from Users_temp as usersTable 
        //     left join UD_P_Info as projectsTable 
        //     on usersTable._id = projectsTable.creating_user_id 
        //     group by usersTable._id
        //         ,usersTable.email_id
        //         ,usersTable.password
        //         ,usersTable.salt
        //         ,usersTable.first_name
        //         ,usersTable.middle_name
        //         ,usersTable.last_name
        //         ,usersTable.organization_id
        //         ,usersTable.phone
        //         ,usersTable.country_name
        //         ,usersTable.country_code
        //         ,usersTable.authtype_id
        //         ,usersTable.securityquestion_id
        //         ,usersTable.securityquestion_ans
        //         ,usersTable.ts_created
        //         ,usersTable.reset_link
        //         ,usersTable.ts_reset
        //         ,usersTable.profile_change_link
        //         ,usersTable.ts_profile_change
        //         ,usersTable.ts_password_change
        //         ,usersTable.ts_last_login_success
        //         ,usersTable.login_success_count
        //         ,usersTable.ts_last_login_fail
        //         ,usersTable.ts_survey_completed
        //         ,usersTable.uses_sr
        //         ,usersTable.subscription_type
        //    ";



        if (!($stmt = $sql_conn->prepare($s))){
            echo "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error. $s;
        }

        if (!($stmt->execute())) {
         
            echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
        }

        if (!($ret = $stmt->get_result())) {
      
            echo "Getting Result failed: (" . $stmt->errno . ") " . $stmt->error;

        }
        

        $user = new User();
        $res = array();
        while($row = mysqli_fetch_array($ret)) {
            
            $user = new User();
            $user->populateFromDBRow($row);
            if($public){
                $user = getPublicUserData($user);
            }
            $res[] = $user;
        }

        $stmt->close();

        mysqli_close($sql_conn);
        $r = json_encode($res);
        // print('Printing Original Function  ');
        // print($r);
        return $r;
    }

    // get all reviewers
    // for now we consider if user is admin then he/she is the reviewer
    // we might change this in future with new user type as reviewer
    function getReviewers($public = true){
        $sql_conn = mysqli_connection();
        $clause = Util::escapeObject($clause);

        $s = "SELECT * FROM  `Users_temp` WHERE authtype_id = ".TypeOfUser::ADMIN." OR authtype_id =".TypeOfUser::REVIEWER." OR authtype_id=".TypeOfUser::SUPER_ADMIN;
        if (!($stmt = $sql_conn->prepare($s))){
            echo "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error. $s;
        }

        if (!($stmt->execute())) {
            echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
        }

        if (!($ret = $stmt->get_result())) {
            echo "Getting Result failed: (" . $stmt->errno . ") " . $stmt->error;
        }


        $user = new User();
        $res = array();
        while($row = mysqli_fetch_array($ret)) {
            $user = new User();
            
            $user->populateFromDBRow($row);
            if($public){
                $user = getPublicUserData($user);
            }
            $res[] = $user;
        }

        $stmt->close();

        mysqli_close($sql_conn);
        $r = json_encode($res);
        //echo "hello : ". $r;
        return $r;
    }

    // create demo project for the first time logged in user
    // We are offering free (starter) subscription to the user in which we will give user one pre-created project
    // user is not allowed to Create new Project or Submit project in free(starter) subscription.
    // user can edit this pre-created DEMO project.
    function createDemoProject()
    {
        $data['title'] = "Demo Project";
        $data['owner'] = "";
        $data['architect'] = "";
        $data['habitable_floor_area'] = "";
        $data['site_area'] = "";
        $data['cost'] = "";
        $data['a_start_date'] ="";
        $data['a_end_date'] = "";
        $data['building_type'] ="";
        $data['project_scope']="";
        $data['contact_person']="";
        $data['contact_email']="";
        $data['contact_telephone']="";
        $data['contact_telephone_c_code'] ="";
        $data['address_line_1'] ="";
        $data['address_line_2'] ="";
        $data['city'] ="";
        $data['state'] ="";
        $data['country'] ="";
        $data['zipcode'] = "";
        // mark this created project as demo project
        $data['demo_project'] = "1";

        createProjectInfo($data['title'], $data['owner'], $data['architect'], $data['habitable_floor_area'], $data['site_area'], $data['cost'],
                                 $data['a_start_date'], $data['a_end_date'], $data['building_type'], $data['project_scope'], $data['contact_person'], $data['contact_email'], $data['contact_telephone'], $data['contact_telephone_c_code'],
                                 $data['address_line_1'], $data['address_line_2'], $data['city'], $data['state'], $data['country'], $data['zipcode'], $data['demo_project']);
    }

    // News Signup
    function add_mailid_to_news_signup_db($mailID){
        $sql_conn = mysqli_connection();
        $ret["mail_exists"]=false;
        $response=true;
        $s = "SELECT * FROM UD_News_Signup WHERE Mail_ID=?";
        if (!($stmt = $sql_conn->prepare($s))){
            //echo "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error. $s;
            $response=false;
        }
        if(!($stmt->bind_param("s",
                $mailID))){
            $sql_ret =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            $response=false;
        }
        if (!($stmt->execute())) {
            //echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $response=false;
        }

        if (!($results = $stmt->get_result())) {
            //echo "Getting Result failed: (" . $stmt->errno . ") " . $stmt->error;
            $response=false;
        }
        if(mysqli_num_rows($results) == 0){
            $s = "INSERT INTO `UD_News_Signup` (Mail_ID) VALUES (?)";
            if (!($stmt = $sql_conn->prepare($s))){
            //echo "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error. $s;
                $response=false;
            }
            if(!($stmt->bind_param("s",
                        $mailID))){
                $sql_ret =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
                $response=false;
            }
            if (!($stmt->execute())) {
            //echo "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            $response=false;
            }
        }
        else{
            $ret["mail_exists"]=true;
        }
        $ret["response"]=$response;
        $stmt->close();
        mysqli_close($sql_conn);
        return $ret;
    }

    // SELECT 
    //         COUNT(userTable._id) as numUsers, 
    //         CASE 
    //             WHEN authtype_id = 1 and datediff(CURDATE(), `ts_last_login_success`) >= 60 THEN "Inactive Admin" 
    //             WHEN authtype_id = 1 and datediff(CURDATE(), `ts_last_login_success`) < 60 THEN "Active Admin" 
    //             WHEN authtype_id = 2 and datediff(CURDATE(), `ts_last_login_success`) >= 60 THEN "Inactive DB Admin" 
    //             WHEN authtype_id = 2 and datediff(CURDATE(), `ts_last_login_success`) < 60 THEN "Active DB Admin" 
    //             WHEN authtype_id = 3 and datediff(CURDATE(), `ts_last_login_success`) >= 180 THEN "Inactive User" 
    //             WHEN authtype_id = 3 and datediff(CURDATE(), `ts_last_login_success`) < 180 THEN "Active User"
    //             WHEN authtype_id = 4 and datediff(CURDATE(), `ts_last_login_success`) >= 180 THEN "Inactive Organization" 
    //             WHEN authtype_id = 4 and datediff(CURDATE(), `ts_last_login_success`) < 180 THEN "Active Organization" 
    //         END AS stat
    //     FROM `Users_temp` as userTable 
    //     GROUP BY stat
//getUserStats();
    function getUserStats(){

        $sql_conn = mysqli_connection();
        if(isLoggedIn()){

            if($_SESSION['user']->authtype_id == TypeOfUser::ADMIN || $_SESSION['user']->authtype_id == TypeOfUser::SUPER_ADMIN)  // If the user is super admin or admin
            {
              
                // $sql = "SELECT 
                          
                //           CASE 
                //               WHEN authtype_id = 1 and datediff(CURDATE(), `ts_last_login_success`) >= 60 THEN 'Inactive Admin' 
                //               WHEN authtype_id = 1 and datediff(CURDATE(), `ts_last_login_success`) < 60 THEN 'Active Admin' 
                //               WHEN authtype_id = 2 and datediff(CURDATE(), `ts_last_login_success`) >= 60 THEN 'Inactive DB Admin'
                //               WHEN authtype_id = 2 and datediff(CURDATE(), `ts_last_login_success`) < 60 THEN 'Active DB Admin' 
                //               WHEN authtype_id = 3 and datediff(CURDATE(), `ts_last_login_success`) >= 180 THEN 'Inactive User' 
                //               WHEN authtype_id = 3 and datediff(CURDATE(), `ts_last_login_success`) < 180 THEN 'Active User'
                //               WHEN authtype_id = 4 and datediff(CURDATE(), `ts_last_login_success`) >= 180 THEN 'Inactive Organization' 
                //               WHEN authtype_id = 4 and datediff(CURDATE(), `ts_last_login_success`) < 180 THEN 'Active Organization' 
                //               ELSE 'Never Loggedin'
                //           END AS stat,

                //           COUNT(userTable._id) as rowCounts 
                //         FROM `Users_temp` as userTable 
                //         GROUP BY stat";

              $sql = "SELECT 
                          
                          CASE 
                              WHEN authtype_id = 1 then 'Admin' 
                              
                              WHEN authtype_id = 2 then 'DB-Admin'
                              
                              WHEN authtype_id = 3 then 'User' 
                             
                              WHEN authtype_id = 4 then 'Organization' 
                             
                              ELSE 'Unknown'
                          END AS stat,
                          
                          CASE 
                              WHEN datediff(CURDATE(), `ts_last_login_success`) <= 60 THEN 'Active' 
                                WHEN datediff(CURDATE(), `ts_last_login_success`) > 60 THEN 'In-Active'
                                ELSE 'Never Loggedin'
                          END as activity,
                          
                          COUNT(userTable._id) as rowCounts 
                        FROM `Users_temp` as userTable 
                        GROUP BY stat, activity;";
            
            
        
            $udProjectStats = array();
            $res = array();
            try{
                $result = mysqli_query($sql_conn, $sql);
                while($row = mysqli_fetch_array($result)) {
                    //$project = new UD_Project_Info();
                    //$project->populateFromDBRow($row);

                    // hide fields
                    // unset($row["creating_user_id"]);
                    
                    foreach($row as $key => $val){
                        //print($val);
                        //print($val);
                        if(gettype($key) === "integer"){
                            unset($row[$key]);

                        }


                    }
                    //array_push($udProjectStats, $row['stat'], (int) $row['rowCounts']);
                    // $udProjectStats['stat'] = $row['stat'];
                    // $udProjectStats['rowCounts'] = (int) $row['rowCounts'];
                    // foreach($r in $row){
                    //     echo($r);
                    // }
                    $udProjectStats[] = $row;
                }

                mysqli_close($sql_conn);
                $res = $udProjectStats;
            } catch (Exception $e){
                $res['message'] = "Error : ".$e->getMessage();
            }
            } else {
            $res['message'] = "Error : User not Admin";
        }

        } else {
            $res['message'] = "Error : User not logged in";
        }

        // $format = '{
        //                 "cols":
        //                 [
        //                 {"id":"","label":"Type","pattern":"","type":"string"},
        //                 {"id":"","label":"Counts","pattern":"","type":"number"}
        //                 ],
        //                 "rows":'.json_encode($res).'}';

        $r = json_encode($res, JSON_NUMERIC_CHECK);
        //print($r);

        return $r;

    }


    //fetchAllUsersData();
    function fetchAllUsersData(){
        $sql_conn = mysqli_connection();
        $clause = Util::escapeObject($clause);

        // $s = "SELECT * FROM  `Users_temp`";
        //Edited to contain total no of projects for each user.

        $s= "SELECT usersTable._id
                ,usersTable.email_id
                ,usersTable.password
                ,usersTable.salt
                ,usersTable.first_name
                ,usersTable.middle_name
                ,usersTable.last_name
                ,usersTable.organization_id
                ,usersTable.job_title
                ,usersTable.phone
                ,usersTable.country_name
                ,usersTable.country_code
                ,usersTable.authtype_id
                ,usersTable.securityquestion_id
                ,usersTable.securityquestion_ans
                ,usersTable.ts_created
                ,usersTable.reset_link
                ,usersTable.ts_reset
                ,usersTable.profile_change_link
                ,usersTable.ts_profile_change
                ,usersTable.ts_password_change
                ,usersTable.ts_last_login_success
                ,usersTable.login_success_count
                ,usersTable.ts_last_login_fail
                ,usersTable.ts_survey_completed
                ,usersTable.uses_sr
                ,usersTable.subscription_type
                ,usersTable.date_created
                ,usersTable.supporter_expiry_date
                ,usersTable.innovator_expiry_date
                ,usersTable.upgrade_date
                ,count(*) as projCounts
            from Users_temp as usersTable 
            left join UD_P_Info as projectsTable 
            on usersTable._id = projectsTable.creating_user_id 
            group by usersTable._id
                ,usersTable.email_id
                ,usersTable.password
                ,usersTable.salt
                ,usersTable.first_name
                ,usersTable.middle_name
                ,usersTable.last_name
                ,usersTable.organization_id
                ,usersTable.job_title
                ,usersTable.phone
                ,usersTable.country_name
                ,usersTable.country_code
                ,usersTable.authtype_id
                ,usersTable.securityquestion_id
                ,usersTable.securityquestion_ans
                ,usersTable.ts_created
                ,usersTable.reset_link
                ,usersTable.ts_reset
                ,usersTable.profile_change_link
                ,usersTable.ts_profile_change
                ,usersTable.ts_password_change
                ,usersTable.ts_last_login_success
                ,usersTable.login_success_count
                ,usersTable.ts_last_login_fail
                ,usersTable.ts_survey_completed
                ,usersTable.uses_sr
                ,usersTable.subscription_type
                ,usersTable.date_created
                ,usersTable.supporter_expiry_date
                ,usersTable.innovator_expiry_date
                ,usersTable.upgrade_date
           ";

        $udUsersData = array();
            $res = array();
            try{
                $result = mysqli_query($sql_conn, $s);
                while($row = mysqli_fetch_array($result)) {
                    foreach($row as $key => $val){
                        if(gettype($key) === "integer"){
                            unset($row[$key]);
                        }
                    }
                    $udUsersData[] = $row;
                }
                mysqli_close($sql_conn);
                $res = $udUsersData;
            } catch (Exception $e){
                $res['message'] = "Error : ".$e->getMessage();
            }

        mysqli_close($sql_conn);
        $r = json_encode($res, JSON_NUMERIC_CHECK);
        // print('Printing Cloned Function  ');
        // print($r);
        return $r;
    }

/* BELOW CODE IS FOR ACCOUNT EMAIL VERIFICATION AFTER REGISTRATION */
/* USING SAME LOGIC AND COLUMNS AS USED FOR RESET PASSWORD FOR EMAIL VERIFICATION. DONT CONFUSED */
    function verifyEmail_init($username){
        $user = json_decode(getUser($username));
        $user = $user[0];
        if(is_null($user)){
            $ret['message'] = "NO USER";
            $ret['response'] = false;
            echo (1);
        } 
        else if (($username === $user->email_id)){
            $micro = microtime();
            $micro = str_replace(' ', '', $micro);
            $reset_link = Util::hash_pbkdf2($username.$micro, $user->salt);

            $sql_conn = mysqli_connection();
            $reset_link = Util::escapeObject($reset_link);
            $current_ts = "NOW()";
            if (!($stmt = $sql_conn->prepare("UPDATE `Users_temp` SET reset_link = ?, ts_reset = NOW() WHERE email_id = ?"))){
                $sql_ret =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            }

            if(!($stmt->bind_param("ss",
                        $reset_link,
                        $username ))){
                $sql_ret =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            if (!($sql_ret = $stmt->execute())) {
                $sql_ret =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            $stmt->close();
            mysqli_close($sql_conn);

            $ret['message'] = $reset_link;
            $ret['response'] = true;
        }
        return json_encode($ret);
    }

    function verifyEmail_conf($reset_link){
        $user = json_decode(getUser($reset_link, "reset_link"));
        $user = $user[0];
        $ret['response'] = false;
        if(is_null($user)){
            $ret['message'] = "NO SUCH LINK";
        } else if (strtotime($user->ts_reset) < strtotime('-1 day')){
            $ret['message'] = "LINK EXPIRED";
        } else {
            
            $sql_conn = mysqli_connection();
            $username = $user->email_id;


            if (!($stmt = $sql_conn->prepare("UPDATE `Users_temp` SET `is_verified` = 1, `reset_link` = NULL WHERE email_id = ?"))){
                $sql_ret =  "Prepare failed: (" . $sql_conn->errno . ") " . $sql_conn->error;
            }

            if(!($stmt->bind_param("s",
                        $username ))){
                $sql_ret =  "Binding parameters failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            if (!($sql_ret = $stmt->execute())) {
                $sql_ret =  "Execute failed: (" . $stmt->errno . ") " . $stmt->error;
            }

            $stmt->close();
            mysqli_close($sql_conn);

            $ret['message'] = "EMAIL VERIFIED SUCCESSFULLY";
            $ret['response'] = ($sql_ret === true);


        }

        return json_encode($ret);
    }


    

?>