<?php
namespace App\Models;

use CodeIgniter\Model;

class UsersModel extends Model 
{
	
	protected $table      = 'users';
    protected $primaryKey = 'id';
	
	protected $allowedFields = ['first_name', 'last_name', 'email',  'photo', 'mobile', 'address',  'password', 'birthday', 'position', 'gender', 'description', 'department_id', 'status', 'relative_name', 'relative_phone', 'relative_relationship', 'twitter', 'facebook', 'linkedin', 'instagram', 'last_active', 'created_by', 'updated_by', 'updated_at'];

    protected $useTimestamps  = true;
    protected $useSoftDeletes = true;
    
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';
    protected $deletedField  = 'deleted_at';

    public function getAll()
    {
        $builder = $this->db->table('users as u');
        $builder->select('u.*');
        $builder->join('user_auth_group as uag', 'uag.user_id = u.id', 'left');
        $builder->join('user_groups as ug', 'ug.id=uag.group_id', 'left');
        $builder->where('u.deleted_at', null);
        $builder->where('u.id !=', 1);
        $result = $builder->get()->getResult();
        return $result;
    }
    

    //Check User Email Availability
    public function verifyEmail($email)
    {
        $builder = $this->db->table('users as u');
        $builder->where('u.email', $email);
        $result = $builder->get()->getRow();
		return $result;
    }

    //update password
    public function update_password($id, $password){
		$builder = $this->db->table('users');
		$builder->where('id', $id);
		$builder->update(['password' =>$password]);
		if($this->db->affectedRows()==1)
        {
			return true;
		}else{
			return false;
		}
	}

    public function singleUser($user)
    {
        $builder = $this->db->table('users as u');
        $builder->select('u.*, ug.name as group, uag.group_id,  co.name as country_name, st.name as state_name, ci.name as city_name,');
        $builder->join('user_auth_group as uag', 'uag.user_id = u.id', 'left' );
        $builder->join('user_groups as ug', 'ug.id=uag.group_id', 'left');
        $builder->join('cities as ci', 'ci.id=u.city_id', 'left');
        $builder->join('states as st', 'st.id=u.state_id', 'left');
        $builder->join('countries as co', 'co.id=u.country_id', 'left');
        $builder->where('u.id', $user);
        $results = $builder->get()->getRow();
        return $results;
    }

    public function getAllUsers()
    {
        $builder = $this->db->table('users as u');
        $builder->select('u.*,ug.name as group, uag.group_id, ');
        $builder->join('user_auth_group as uag', 'uag.user_id = u.id', 'left' );
        $builder->join('user_groups as ug', 'ug.id=uag.group_id', 'left');
        $builder->where('u.deleted_at', null);
        $builder->where('uag.user_type', 'admin');
        $result = $builder->get()->getResult();
        return $result;
    }

    //Get Super Admins  Notifications
    public function getSuperAdminUsers()
    {
        $builder = $this->db->table('users as u');
        $builder->select('u.*, ');
        $builder->join('user_auth_group as uag', 'uag.user_id = u.id', 'left' );
        $builder->where('uag.group_id', 1);
        $results = $builder->get()->getResult();
        return $results;
    }

    //Get Super Admins & Admins for  Notifications
    public function getAdminUsers()
    {
        $builder = $this->db->table('users as u');
        $builder->select('u.*, ');
        $builder->join('user_auth_group as uag', 'uag.user_id = u.id', 'left' );
        $builder->whereIn('uag.group_id', [1, 2]);
        $results = $builder->get()->getResult();
        return $results;
    }

    //Get Super Admins, Admins & Billing Manager for  Notifications
    public function getBillingUsers()
    {
        $builder = $this->db->table('users as u');
        $builder->select('u.*, ');
        $builder->join('user_auth_group as uag', 'uag.user_id = u.id', 'left' );
        $builder->whereIn('uag.group_id', [1, 2,3]);
        $results = $builder->get()->getResult();
        return $results;
    }

    //Get Super Admins & Admins for  Notifications
    public function getLTUsers()
    {
        $builder = $this->db->table('users as u');
        $builder->select('u.*, ');
        $builder->join('user_auth_group as uag', 'uag.user_id = u.id', 'left' );
        $builder->whereIn('uag.group_id', [1,2,3,4,5,6]);
        $results = $builder->get()->getResult();
        return $results;
    }

    //Display all Users accessing the Administrator Dashboard
    public function getOnlyDashboardUsers()
    {
        $builder = $this->db->table('users as u');
        $builder->select('u.*, ug.name as group, uag.group_id,');
        $builder->join('user_auth_group as uag', 'uag.user_id = u.id', 'left' );
        $builder->join('user_groups as ug', 'ug.id=uag.group_id');
        $builder->where('u.deleted_at', null);
        $builder->whereIn('uag.group_id', [1, 2, 3, 4, 5]);
        $results = $builder->get()->getResult();
        return $results;
    }

    //Get all Users / Sellers
    public function getAllSellers()
    {
        $builder = $this->db->table('users as u');
        $builder->select('u.*, group, secret as email, co.name as country_name, st.name as state_name, ci.name as city_name,');
        $builder->join('auth_identities as ai', 'ai.user_id=u.id');
        $builder->join('auth_groups_users as ag', 'ag.user_id = u.id');
        $builder->join('cities as ci', 'ci.id=u.city_id');
        $builder->join('states as st', 'st.id=u.state_id');
        $builder->join('countries as co', 'co.id=u.country_id');
        $builder->where('deleted_at', null);
        $builder->where('ag.group', 'user');
        $results = $builder->get()->getResult();
        return $results;
    }

    //Find a USER & the Group
    public function findUserGroup($id)
    {
        $builder = $this->db->table('users as u');
        $builder->select('u.*, group, secret, ag.id as group_id, co.name as country_name, st.name as state_name, ci.name as city_name,');
        $builder->join('auth_identities as ai', 'ai.user_id=u.id');
        $builder->join('auth_groups_users as ag', 'ag.user_id = u.id');
        $builder->join('cities as ci', 'ci.id=u.city_id');
        $builder->join('states as st', 'st.id=u.state_id');
        $builder->join('countries as co', 'co.id=u.country_id');
        $builder->where('u.id', $id);
        $results = $builder->get()->getRow();
        return $results;
    }

    //Get Top Sellers
    public function getTopSellers()
    {
        $builder = $this->db->table('users as u');
        $builder->select('u.*, COUNT(p.id) as product_count, co.name as country_name, st.name as state_name, ci.name as city_name');
        $builder->join('auth_identities as ai', 'ai.user_id=u.id');
        $builder->join('auth_groups_users as ag', 'ag.user_id = u.id');
        $builder->join('cities as ci', 'ci.id=u.city_id');
        $builder->join('states as st', 'st.id=u.state_id');
        $builder->join('countries as co', 'co.id=u.country_id');
        $builder->join('product as p', 'p.user_id = u.id');
        $builder->where('u.deleted_at', null);
        $builder->where('ag.group', 'user');
        
        // Calculate the date one month ago
        $oneMonthAgo = date('Y-m-d H:i:s', strtotime('-1 month'));
        
        // Get the products uploaded within the last month
        $builder->where('p.created_at >=', $oneMonthAgo);
        
        $builder->groupBy('u.id');
        $builder->orderBy('product_count', 'DESC');
        $builder->limit(10);
        
        $results = $builder->get()->getResult();
        return $results;
    }

    public function singleSeller($seller)
    {
        $builder = $this->db->table('users as u');
        $builder->select('u.*, group, secret, ag.id as group_id, co.name as country_name, st.name as state_name, ci.name as city_name,');
        $builder->join('auth_identities as ai', 'ai.user_id=u.id');
        $builder->join('auth_groups_users as ag', 'ag.user_id = u.id');
        $builder->join('cities as ci', 'ci.id=u.city_id');
        $builder->join('states as st', 'st.id=u.state_id');
        $builder->join('countries as co', 'co.id=u.country_id');
        $builder->where('u.id', $seller);
        $results = $builder->get()->getRow();
        return $results;
    }

    public function getSellerAndAdmins($productId)
    {
        // Get the product owner
        $builder = $this->db->table('users as u');
        $builder->select('u.*');
        $builder->join('product as p', 'p.user_id=u.id');
        $builder->where('p.id', $productId);
        $builder->where('u.deleted_at', null);
        $owner = $builder->get()->getRow();

        // Get the admin users
        $builder = $this->db->table('users as u');
        $builder->select('u.*, group');
        $builder->join('auth_identities as ai', 'ai.user_id=u.id');
        $builder->join('auth_groups_users as ag', 'ag.user_id=u.id');
        $builder->where('u.deleted_at', null);
        $builder->whereIn('ag.group', ['superadmin', 'admin']);
        $admins = $builder->get()->getResult();

        return array('owner' => $owner, 'admins' => $admins);
    }


}