<?php

  namespace Modules\Redd\Models;

use CodeIgniter\Model;
use CodeIgniter\I18n\Time;

class ClientsModel extends Model
{

  protected $table = 'redd_clients';
    protected $primaryKey = 'id';

    protected $allowedFields = ['fname', 'lname', 'alias', 'password', 'phone','email', 'national_id', 'photo', 'address', 'entity_type_id', 'geo_lat', 'geo_long', 'country_id', 'state_id', 'city_id', 'status', 'description', 'website', 'created_by', 'updated_by',];

    protected $returnType     = 'array';
    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('redd_clients');
        $builder->where('deleted_at', null);
        $result = $builder->get()->getResult();
        return $result;
    }

     //Get Search Results
     public function searchClients($searchTerm)
     {
         $builder = $this->db->table('redd_clients as m');
         $builder->select('m.fname, m.lname, m.email, m.phone');
           if (!empty($searchTerm)) {
             $builder->like('m.fname', $searchTerm)
                     ->orWhere('m.lname', $searchTerm)
                     ->orWhere('m.email', $searchTerm)
                     ->orWhere('m.phone', $searchTerm)
                     ->groupBy('m.id');
           }
           $query = $builder->get();
           return $query->getResultArray();
     }
 
     public function getAllClients($start = 0, $length = 10, $searchValue = '')
     {
         $this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");
         $builder = $this->db->table('redd_clients');
         $builder->select('redd_clients.*, redd_entity_types.name as entity_name, countries.name as country_name, states.name as state_name, cities.name as city_name');

          // Subquery to get count of products per client
        $subqueryProjects = $this->db->table('redd_projects')
                                     ->select('COUNT(*)')
                                     ->where('redd_projects.client_id=redd_clients.id')
                                     ->where('redd_projects.deleted_at', null)
                                     ->getCompiledSelect();
        
        $builder->select("SUM(($subqueryProjects)) as total_projects");
         
         // Join table
         $builder->join('redd_entity_types', 'redd_entity_types.id=redd_clients.entity_type_id', 'left');
         $builder->join('countries', 'countries.id=redd_clients.country_id', 'left');
         $builder->join('states', 'states.id=redd_clients.state_id', 'left');
         $builder->join('cities', 'cities.id=redd_clients.city_id', 'left');    
        
         // Conditions to filter Events
         $builder->where('redd_clients.deleted_at', null);
         $builder->groupBy('redd_clients.id');
 
         // Apply search filter
         if ($searchValue) {
             $builder->groupStart()
                 ->like('redd_clients.fname', $searchValue)
                 ->orLike('redd_clients.lname', $searchValue)
                 ->orLike('redd_clients.email', $searchValue)
                 ->orLike('redd_clients.phone', $searchValue)
                 ->orLike('redd_entity_types.name', $searchValue)
                 ->orLike('countries.name', $searchValue)
                 ->orLike('states.name', $searchValue)
                 ->orLike('cities.name', $searchValue)
             ->groupEnd();
         }
 
         // Clone builder for total count (filtered)
         $filteredBuilder = clone $builder;
 
         // Limit and offset for pagination
         $builder->limit($length, $start);
         $members = $builder->get()->getResult();
 
         // Total records after filtering
         $recordsFiltered = $filteredBuilder->countAllResults(false);
         $recordsTotal = $this->countAllClients(); // Total unfiltered records
 
         return [
             'data' => $members,
             'recordsFiltered' => $recordsFiltered,
             'recordsTotal' => $recordsTotal,
         ];
     }
 
     public function getSingleClient($member)
     {
        $builder = $this->db->table('redd_clients');
         $builder->select('redd_clients.*, redd_entity_types.name as entity_name, countries.name as country_name, states.name as state_name, cities.name as city_name');
         
         // Join table
         $builder->join('redd_entity_types', 'redd_entity_types.id=redd_clients.entity_type_id', 'left');
         $builder->join('countries', 'countries.id=redd_clients.country_id', 'left');
         $builder->join('states', 'states.id=redd_clients.state_id', 'left');
         $builder->join('cities', 'cities.id=redd_clients.city_id', 'left');
         $builder->where('redd_clients.deleted_at', null);
         $builder->where('redd_clients.id', $member);
         return $results = $builder->get()->getRow();
     }
 
     public function countAllClients()
     {
         $builder = $this->db->table('redd_clients as c');
         $builder->where('c.deleted_at', null);
         return $results = $builder->countAllResults();
     }
}
