<?php

namespace App\Models;

//Tips for impelementing codes

use App\Core\Model;
use App\Core\Database;

class User extends Model
{

    public function userInfo($user_id)
    {
        $query = "SELECT
        e.id,
        e.asa_id,
        e.`employee_id`,
        e.`first_name`,
        e.`middle_name`,
        e.`last_name`,
        concat(e.`first_name`,
        ' ',
        e.`last_name`) AS full_name,
        ext.suffix_name ,
       e.`date_of_birth`,
        ag.gender_name,
       ag.id AS gender_id,
        e.`photo`,
         DATE_FORMAT(e.date_registered,
        '%M %d,
        %Y - %h:%i:%s %p') as date_registered,
        ea.username,
       ea.employee_role_id ,
       ec.email_address,
       ec.email_address_2,
       ec.contact_number,
       ec.contact_number_2,
       ec.fax,
       ec.business,
       ec.residential,
       ec.landline,
       ec.address,
       st.provDesc,
       st.provCode as state_id,
       ac.citymunDesc,
       ac.id as city_id,
       ec.postal,
       ed.department_name,
       ed.id as department,
        ep.position_name,
       ep.id as position,
        sc.code as company_code,
       sc.name as company_name,
       sd.code as dealer_code,
       sd.name as dealer_name,
       sd.id as dealer,
       sc.id as company  
        FROM `employee` e
        LEFT OUTER JOIN employee_account ea 
        ON e.id = ea.user_id  
        LEFT OUTER JOIN employee_contact ec
        ON e.id = ec.user_id
        LEFT OUTER JOIN all_suffix ext 
        ON e.suffix_id = ext.id
        LEFT OUTER JOIN all_gender ag 
        ON e.gender_id = ag.id
        LEFT OUTER JOIN source_company_dealer  cdi
        ON e.company_dealer_id = cdi.id
        LEFT OUTER JOIN source_company sc
        ON cdi.company_id = sc.id
        LEFT OUTER JOIN source_dealer sd
        ON cdi.dealer_id = sd.id
        LEFT OUTER JOIN employee_department ed
        ON e.department_id = ed.id
        LEFT OUTER JOIN employee_position ep 
        ON e.position_id = ep.id
        LEFT OUTER JOIN all_state st
        on ec.provCode  = st.provCode
        LEFT OUTER JOIN all_city ac
        ON ec.city_id=ac.id where  e.id = ?";
        $paramType = "i";
        $paramValue = array(
            $user_id,
        );
        $result =  Database::connect()->runQuery($query, $paramType, $paramValue);
        return $result;
    }

    public function getUserRole($user_id)
    {
        $query = "SELECT employee_role_id FROM employee_account WHERE 1 AND user_id = ?";
        $paramType = "i";
        $paramValue = array(
            $user_id,
        );
        $result =  Database::connect()->select($query, $paramType, $paramValue);
        return $result;
    }
    public function getUserPermissions($role_id)
    {
        $query =  "SELECT action_permissions,company_permissions,fields_permissions FROM `employee_role_access` WHERE status = 1 AND employee_role_id = ? ";
        $paramType = "i";
        $paramValue = array(
            $role_id,
        );
        $result =  Database::connect()->runQuery($query, $paramType, $paramValue);
        return $result;
    }
    public function userDataByEmail($email)
    {
        $query = "SELECT * FROM `user` WHERE 1 AND `email` = ?";
        $paramType = "s";
        $paramValue = array(
            $email,
        );
        $result =  Database::connect()->runQuery($query, $paramType, $paramValue);
        return $result;
    }

    public function createUserData($array_data)
    {
        $firstname = ucfirst($array_data['txt_fname']);
        $lastname = ucfirst($array_data['txt_lname']);
        $email = $array_data['txt_email'];
        $password = $array_data['txt_password'];
        $password_hash = password_hash($password, PASSWORD_DEFAULT);


        $query = "INSERT INTO `user`(`firstName`, `lastName`, `email`, `passwordHash`,`registeredAt`) VALUES (?,?,?,?,NOW())";
        $paramType = "ssss";
        $paramValue = array(
            $firstname,
            $lastname,
            $email,
            $password_hash
        );
        $insertId =  Database::connect()->insert($query, $paramType, $paramValue);
        return $insertId;
    }

    public function createUserEmailVerification($user_id, $token, $email)
    {
        $query = "INSERT INTO `user_email_verification`(`userId`, `token`, `email`,`createdAt`) VALUES (?,?,?,NOW())";
        $paramType = "iss";
        $paramValue = array(
            $user_id,
            $token,
            $email,
        );
        $insertId =  Database::connect()->insert($query, $paramType, $paramValue);
        return $insertId;
    }

    public function createLastLoginDate($user_id)
    {
        $query = "UPDATE user SET lastLogin = NOW() WHERE id = ?";
        $paramType = "i";
        $paramValue = array(
            $user_id,
        );
        Database::connect()->update($query, $paramType, $paramValue);
    }

    public function createLoginToken($user_id, $token)
    {
        $query = "INSERT INTO `user_login_session`(`userId`, `token`, `createdAt`,`status`) VALUES (?,?,NOW(),1) ON DUPLICATE KEY UPDATE    
							token  = VALUES (token),
                            createdAt = VALUES(createdAt),
                            status = VALUES(status)
                            ";
        $paramType = "is";
        $paramValue = array(
            $user_id,
            $token,
        );
        $insertId =  Database::connect()->insert($query, $paramType, $paramValue);
        return $insertId;
    }

    public function emailTokenUserId($token)
    {
        $query = "SELECT userId FROM user_email_verification WHERE token = ? AND status IS NULL";
        $paramType = "s";
        $paramValue = array(
            $token
        );
        $result =  Database::connect()->select($query, $paramType, $paramValue);
        return $result;
    }

    public function disableEmailToken($token)
    {
        $query = "UPDATE user_email_verification SET status = 1 WHERE token = ? ";
        $paramType = "s";
        $paramValue = array(
            $token
        );
        $result =  Database::connect()->update($query, $paramType, $paramValue);
        return $result;
    }
    public function activateUser($user_id)
    {
        $query = "UPDATE user SET active = 1 WHERE id = ? ";
        $paramType = "i";
        $paramValue = array(
            $user_id
        );
        $result =  Database::connect()->update($query, $paramType, $paramValue);
        return $result;
    }

    public function userIdByToken($token)
    {
        $query = "SELECT userId FROM user_login_session WHERE token = ? AND status = 1";
        $paramType = "s";
        $paramValue = array(
            $token
        );
        $result =  Database::connect()->select($query, $paramType, $paramValue);
        return $result;
    }
    public function authenticate()
    {
        $token = $_COOKIE['uid'];
        $userId = $this->userIdByToken($token);
        $query = "SELECT * FROM `user` WHERE 1 AND `id` = ?";
        $paramType = "i";
        $paramValue = array(
            $userId,
        );
        $result =  Database::connect()->runQuery($query, $paramType, $paramValue);
        return $result;
    }
    public function userStatus($userId)
    {
        $query = "SELECT * FROM `user` WHERE 1 AND `id` = ? AND status = 1 AND active = 1";
        $paramType = "i";
        $paramValue = array(
            $userId,
        );
        $result =  Database::connect()->runQuery($query, $paramType, $paramValue);
        return $result;
    }

    public function userAccess($userId)
    {
        $query = "SELECT admin,vendor FROM `user` WHERE 1 AND `id` = ? AND status = 1 AND active = 1";
        $paramType = "i";
        $paramValue = array(
            $userId,
        );
        $result =  Database::connect()->runQuery($query, $paramType, $paramValue);
        return $result;
    }
    public function userRoleId($userId)
    {
        $query = "SELECT u.roleId FROM `user` u
                    INNER JOIN role r 
                    ON u.roleId = r.id
                    WHERE 1 
                    AND r.active = 1
                    AND u.id = ?";
        $paramType = "i";
        $paramValue = array(
            $userId,
        );
        $result =  Database::connect()->select($query, $paramType, $paramValue);
        return $result;
    }

    // user table settings
    public function tableUser($input_arr)
    {
        $type = $input_arr['type'];
        $search = $input_arr['search'];
        $offset = $input_arr['offset'];
        $limit = $input_arr['limit'];

        if ($type == 1) { // table list of brand
            $fields = " `id`, `profile`, concat(`firstName`, ' ', `lastName`) AS full_name, `roleId`, `status` ";
            $count = " COUNT(1) ";

            $json_arr['rows'] = array();
            $user_list = "SELECT %s 
                            FROM `user` 
                            WHERE concat(`id`, `firstName`, `lastName`) LIKE '%%$search%%'"; // echo sprintf($user_list, $fields); exit;

            $user_list_query = Database::connect()->runBaseQuery(sprintf($user_list, $fields));
            $user_list_count = Database::connect()->selectBaseQuery(sprintf($user_list, $count));

            foreach ($user_list_query as $row) {
                $json_arr['rows'] = array_merge($json_arr['rows'], array(array(
                    'user_id' => $row['id'],
                    'profile' => $row['profile'],
                    'full_name' => $row['full_name'],
                    'role' => $row['roleId'],
                    'status' => $row['status'] == 0 ? "<span class='badge bg-danger'>INACTIVE</span>" :
                        "<span class='badge bg-success'>ACTIVE</span>"
                )));
            }

            $json_arr['total'] = $user_list_count;

            return $json_arr;
        }
    }

    public function loadUser($input_arr)
    {
        $user_id = $input_arr['user_id'];

        $sql = "SELECT u.id, u.roleId, u.profile, u.firstName, u.middleName, u.lastName, u.extName, u.mobile, u.email, u.profile, DATE_FORMAT(u.registeredAt, '%b %d, %Y') AS registered, u.status 
                FROM user u 
                WHERE u.id = '$user_id'"; // echo $sql; exit;
        $result = Database::connect()->runBaseQuery($sql);

        return $result;
    }

    public function extName()
    {
        $sql = "SELECT `id`, `name` FROM all_suffix WHERE `status` = 1";

        $result = Database::connect()->runBaseQuery($sql);

        return $result;
    }

    public function updateUser($arr_data)
    {
        $user_id = $arr_data['user_id'];
        $update_first_name = strtoupper($arr_data['user_first_name']);
        $update_middle_name = strtoupper($arr_data['user_middle_name']);
        $update_last_name = strtoupper($arr_data['user_last_name']);
        $update_ext_name = $arr_data['user_ext_name'];
        $update_mobile_number = $arr_data['user_mobile_number'];
        $update_email = $arr_data['user_email'];
        $update_profile = $arr_data['image_filename'];
        $update_status = $arr_data['user_status'];

        $return_arr = array();
        // $current_user = Session::get('user')['id'];

        $original_arr = array();
        $select_sql = "SELECT `firstName`, `middleName`, `lastName`, `extName`, `mobile`, `email`, `profile`, `status` FROM `user` WHERE `id` = '$user_id'";
        $original_user_list = Database::connect()->runBaseQuery($select_sql);

        foreach ($original_user_list as $row) {
            $original_arr[0] = $row['firstName'];
            $original_arr[1] = $row['middleName'];
            $original_arr[2] = $row['lastName'];
            $original_arr[3] = $row['extName'];
            $original_arr[4] = $row['mobile'];
            $original_arr[5] = $row['email'];
            $original_arr[6] = $row['status'];
            $original_arr[7] = $row['profile'];
        }

        if (
            $original_arr[0] == $update_first_name &&
            $original_arr[1] == $update_middle_name &&
            $original_arr[2] == $update_last_name &&
            $original_arr[3] == $update_ext_name &&
            $original_arr[4] == $update_mobile_number &&
            $original_arr[5] == $update_email &&
            $original_arr[6] == $update_status &&
            $update_profile == "Same"
        ) {
            return 10; // nothing changes
        } else {
            $duplicate_mobile = Database::connect()->selectBaseQuery("SELECT `mobile` FROM `user` WHERE `id` = '$user_id'");
            $duplicate_email = Database::connect()->selectBaseQuery("SELECT `email` FROM `user` WHERE `id` = '$user_id'");

            if ($duplicate_mobile == 1) {
                return 1; // duplicate mobile
            } else if ($duplicate_email == 1) {
                return 2; // duplicate email
            } else {
                $sql = "";
                if ($update_profile == "Same") {
                    $sql = "UPDATE `user` SET `firstName` = '$update_first_name', `middleName` = '$update_middle_name', `lastName` = '$update_last_name', `extName` = '$update_ext_name', 
                            `mobile` = '$update_mobile_number', `email` = '$update_email', `status` = '$update_status' WHERE `id` = '$user_id'";
                } else {
                    $file_name = $original_arr[7];
                    if ($file_name == "default.png") {
                        // do nothing, not deleting the default photo
                    } else {
                        $file_name = "storage/users/" . $file_name;
                        unlink($file_name);
                    }

                    $sql = "UPDATE `user` SET `firstName` = '$update_first_name', `middleName` = '$update_middle_name', `lastName` = '$update_last_name', `extName` = '$update_ext_name', 
                            `mobile` = '$update_mobile_number', `email` = '$update_email', `profile` = '$update_profile', `status` = '$update_status' WHERE `id` = '$user_id'"; // echo $sql; exit;
                }

                $update_sql = Database::connect()->updateBaseQuery($sql);

                if ($update_sql > 0) {
                    return 0; // success
                } else {
                    return 3; // update error
                }
            }
        }

        // $original_first_name = Database::connect()->selectBaseQuery("SELECT `name` FROM `brand` WHERE `id` = '$brand_id'"); 
        // $original_description = Database::connect()->selectBaseQuery("SELECT `description` FROM `brand` WHERE `id` = '$brand_id'"); 
        // $original_status = Database::connect()->selectBaseQuery("SELECT `status` FROM `brand` WHERE `id` = '$brand_id'"); 

        // if($original_name == $update_brand_name && $original_description == $update_brand_description && $original_status == $update_brand_status && $update_logo_name == "Same"){
        //     return 10; // nothing changes 
        // } 
        // else {
        //     $duplicate_name = Database::connect()->selectBaseQuery("SELECT COUNT(1) FROM `brand` WHERE `name` = '$update_brand_name' AND `id` != '$brand_id'");

        //     if($duplicate_name > 0){
        //         return 1; // duplicate found
        //     } 
        //     else {
        //         if($update_logo_name == "Same"){
        //             $sql = "UPDATE `brand` SET `name` = '$update_brand_name', `description` = '$update_brand_description', `status` = '$update_brand_status' WHERE `id` = '$brand_id'"; // echo $sql; exit;
        //         } 
        //         else {
        //             $file_name = Database::connect()->selectBaseQuery("SELECT `logo` FROM `brand` WHERE `id` = '$brand_id'");
        //             $file_name = "assets/img/logo/brands/" . $file_name;
        //             unlink($file_name);
        //             $sql = "UPDATE `brand` SET `name` = '$update_brand_name', `logo` = '$update_logo_name', `description` = '$update_brand_description', `status` = '$update_brand_status' WHERE `id` = '$brand_id'"; // echo $sql; exit;
        //         }

        //         $update_sql = Database::connect()->updateBaseQuery($sql);

        //         if($update_sql > 0){
        //             return 0; // success
        //         } 
        //         else {
        //             return 2; // update failed
        //         }
        //     }
        // }
    }
}