<?php
defined('BASEPATH') or exit('No direct script access allowed');

class Mjob extends CI_Model
{
    var $table = 'job';
    var $column_order = array('job.job_id', 'car.car_no', 'job.service', null, null, 'job.km_reading'); //set column field database for datatable orderable
    var $column_search = array('job.job_id', 'car.car_no', 'job.service', 'CONCAT(admins.first_name," ",admins.last_name)', 'job.km_reading'); //set column field database for datatable searchable just firstname , lastname , address are searchable
    //var $order = array('job.job_id' => 'desc'); // default order 

    var $column_order_reject = array('assign_job.assign_job_id', 'name', 'assign_job.remarks', null); //set column field database for datatable orderable
    var $column_search_reject = array('assign_job.assign_job_id', 'CONCAT(admins.first_name," ",admins.last_name)', 'assign_job.remarks'); //set column field database for datatable searchable just firstname , lastname , address are searchable
    var $order = array('job.job_id' => 'desc'); // default order 
    var $reject_order = array('assign_job.job_id' => 'desc');

    var $table_requested_job = 'booking_request';
    var $column_order_requested_job = array('br.created_ts', 'br.service_date', 'br.status', 'c.car_no', 's.service_name', 'br.job_note'); //set column field database for datatable orderable
    var $column_search_requested_job = array('br.created_ts', 'br.service_date', 'br.status', 'c.car_no', 's.service_name', 'br.job_note'); //set column field database for datatable searchable just firstname , lastname , address are searchable
    var $order_requested_job = array('br.booking_request_id' => 'desc'); // default order 
    public function __construct()
    {
        parent::__construct();
    }
    private function _get_datatables_query()
    {
        $search_value = isset($_POST['search']['value']) ? trim($_POST['search']['value']) : '';

        // Simplified query - only get essential fields, batch load related data separately
        $this->db->select('job.job_id, car.car_no, job.service, job.job_status, job.status, IFNULL(job.km_reading,"N/A") as km_reading');
        $this->db->from($this->table);
        $this->db->join('car', 'car.car_id=job.car_id', 'left');

        // Build search conditions - highly optimized for performance
        if (!empty($search_value)) {
            $escaped_search = $this->db->escape_like_str($search_value);

            // Check if search looks like a number
            $is_numeric_search = is_numeric($search_value);
            // Only search mechanic names if search is 3+ chars and not numeric
            $search_mechanic = !$is_numeric_search && strlen($search_value) >= 3;

            // Only join mechanic tables when necessary (huge performance boost)
            if ($search_mechanic) {
                $this->db->distinct();
                $this->db->join('assign_job', 'assign_job.job_id=job.job_id', 'left');
                $this->db->join('admins', 'admins.admin_id=assign_job.mechanic_id', 'left');
            }

            $this->db->group_start();

            // Optimized search: use 'after' positioning for better index usage
            // This allows MySQL to use indexes more effectively (prefix matching)
            $this->db->like('job.job_id', $escaped_search, 'after', false);
            $this->db->or_like('car.car_no', $escaped_search, 'after', false);
            $this->db->or_like('job.service', $escaped_search, 'after', false);

            // For km_reading, still use 'both' as it's less common
            $this->db->or_like('job.km_reading', $escaped_search, 'both', false);

            // Search mechanic names with optimized pattern
            if ($search_mechanic) {
                // Use 'after' for first_name and last_name for index usage
                $this->db->or_like('admins.first_name', $escaped_search, 'after', false);
                $this->db->or_like('admins.last_name', $escaped_search, 'after', false);
            }

            $this->db->group_end();
        }

        if (isset($_POST['order']) && isset($_POST['order'][0]) && isset($_POST['order'][0]['column']) && isset($this->column_order[$_POST['order'][0]['column']])) { // here order processing
            $this->db->order_by($this->column_order[$_POST['order'][0]['column']], $_POST['order'][0]['dir']);
        } else if (isset($this->order)) {
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }

    private function _get_datatables_query_reject($job_id)
    {
        $this->db->select('assign_job.assign_job_id,CONCAT(admins.first_name," ",admins.last_name) name,assign_job.remarks');
        $this->db->from("assign_job");
        $this->db->join('admins', 'admins.admin_id=assign_job.mechanic_id', 'left');
        $this->db->where('job_id', $job_id);


        $i = 0;
        foreach ($this->column_search_reject as $item) { // loop column		
            if ($_POST['search']['value']) { // if datatable send POST for search                 
                if ($i === 0) { // first loop
                    $this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
                    $this->db->like($item, $_POST['search']['value']);
                } else {
                    $this->db->or_like($item, $_POST['search']['value']);
                }
                if (count($this->column_search_reject) - 1 == $i) //last loop
                    $this->db->group_end(); //close bracket
            }
            $i++;
        }

        if (isset($_POST['order'])) { // here order processing
            $this->db->order_by($this->column_order_reject[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        } else if (isset($this->order)) {
            //$order = $this->order;
            $order = $this->reject_order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }


    public function get_datatables()
    {
        $this->_get_datatables_query();
        if ($_POST['length'] != -1)
            $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
        return $query->result();
    }

    public function get_datatables_reject($job_id)
    {
        $this->_get_datatables_query_reject($job_id);
        if ($_POST['length'] != -1)
            $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
        return $query->result();
    }

    public function count_filtered()
    {
        $this->_get_datatables_query();
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_filtered_reject($job_id)
    {
        $this->_get_datatables_query_reject($job_id);
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all()
    {
        // Simple count without unnecessary joins for better performance
        $this->db->from($this->table);
        $this->db->join('car', 'car.car_id=job.car_id', 'left');
        return $this->db->count_all_results();
    }

    public function count_all_reject($job_id)
    {
        $this->db->select('CONCAT(admins.first_name," ",admins.last_name) name,assign_job.remarks');
        $this->db->from("assign_job");
        $this->db->join('admins', 'admins.admin_id=assign_job.mechanic_id', 'left');
        $this->db->where('job_id', $job_id);

        return $this->db->count_all_results();
    }

    private function _get_datatables_query_requested_job()
    {
        $row[] = $person->created_ts;
        $this->db->select('br.booking_request_id,date_format(br.created_ts,"%d/%m/%Y %H:%i:%s") as created_ts,date_format(br.service_date,"%d/%m/%Y") as service_date,br.status,c.car_no,GROUP_CONCAT(s.service_name SEPARATOR ",") as service_name,br.job_note');
        $this->db->from('booking_request br');
        $this->db->join('booking_request_services brs', 'brs.booking_request_id = br.booking_request_id', 'inner');
        $this->db->join('car c', 'c.car_id = br.car_id', 'inner');
        $this->db->join('service s', 's.service_id = brs.service_id', 'inner');
        $this->db->group_by('br.booking_request_id');
        $i = 0;
        foreach ($this->column_search_requested_job as $item) { // loop column 
            //echo $_POST['search']['value'];exit;
            if ($_POST['search']['value']) { // if datatable send POST for search                 
                if ($i === 0) { // first loop
                    $this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
                    $this->db->like($item, $_POST['search']['value']);
                } else {
                    $this->db->or_like($item, $_POST['search']['value']);
                }
                if (count($this->column_search_requested_job) - 1 == $i) //last loop
                    $this->db->group_end(); //close bracket
            }
            $i++;
        }

        if (isset($_POST['order'])) { // here order processing
            $this->db->order_by($this->column_order_requested_job[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        } else if (isset($this->order_requested_job)) {
            $order = $this->order_requested_job;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }

    public function get_datatables_requested_job()
    {
        $this->_get_datatables_query_requested_job();
        if ($_POST['length'] != -1)
            $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
        return $query->result();
    }

    public function count_filtered_requested_job()
    {
        $this->_get_datatables_query_requested_job();
        $query = $this->db->get();
        return $query->num_rows();
    }

    public function count_all_requested_job()
    {
        $this->db->from($this->table_requested_job);
        return $this->db->count_all_results();

        return $this->db->count_all_results();
    }

    public function get_details($job_id)
    {
        $this->db->select('job.job_id,car.car_no,job.service,job.job_status,job.car_id,job.car_id');
        $this->db->from($this->table);
        $this->db->join('car', 'car.car_id=job.car_id', 'left');
        $this->db->where('job.job_id', $job_id);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function get_details_requested_job($booking_request_id)
    {

        $this->db->select('br.booking_request_id,br.service_date,br.status,br.car_id,c.car_no');
        $this->db->from('booking_request br');
        $this->db->join('car c', 'c.car_id = br.car_id', 'inner');
        $this->db->where('br.booking_request_id', $booking_request_id);
        $this->db->group_by('br.booking_request_id');
        $query = $this->db->get();
        return $query->row_array();
    }



    public function update($condition, $data)
    {
        $this->db->where($condition);
        $this->db->update($this->table, $data);
        return 1;
    }
    public function update_due_date($condition, $data)
    {
        $table = 'job_service_date';
        $this->db->where($condition);
        $this->db->update($table, $data);
        //$query = $this->db->last_query();
        //echo $query; die;
        return 1;
    }
    public function add($data)
    {
        $this->db->insert($this->table, $data);
        return $this->db->insert_id();
    }

    public function delete($condition)
    {
        $this->db->delete($this->table, $condition);
        return 1;
    }

    public function active($condition, $data)
    {
        $this->db->where($condition);
        $this->db->update($this->table, $data);
        return 1;
    }

    public function get_parent_category()
    {
        $this->db->select('*');
        $this->db->from('categories');
        $this->db->where('parent_id=0');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_client_list()
    {
        $this->db->select('*');
        $this->db->from('client');
        $this->db->group_by('client_name');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_city_list()
    {
        $this->db->select('*');
        $this->db->from('city');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_client_address($client_name)
    {
        $this->db->select('*');
        $this->db->from('client');
        $this->db->where('client_name', $client_name);
        $this->db->group_by('client_address');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_client_phone($client_name)
    {
        $this->db->select('*');
        $this->db->from('job');
        $this->db->where('client_name', $client_name);
        $this->db->group_by('client_phoneno');
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_sub_category($category_id)
    {
        $this->db->select('*');
        $this->db->from('categories');
        $this->db->where('parent_id', $category_id);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_sub_category_by_category_id($category_id)
    {
        $this->db->select('*');
        $this->db->from('categories');
        $this->db->where('category_id', $category_id);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function get_parent_category_by_parent_id($parent_id)
    {
        $this->db->select('*');
        $this->db->from('categories');
        $this->db->where('category_id', $parent_id);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function get_sub_categories_by_parent_id($parent_id)
    {
        $this->db->select('*');
        $this->db->from('categories');
        $this->db->where('parent_id', $parent_id);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_category_by_id($category_id)
    {
        $this->db->select('*');
        $this->db->from('categories');
        $this->db->where('category_id', $category_id);
        $query = $this->db->get();
        return $query->row_array();
    }

    public function deviceList($cat_id, $city_id)
    {
        $this->db->select('*');
        $this->db->from('devices');
        $this->db->join('users', 'users.user_id=devices.user_id', 'left');
        $this->db->where('users.cat_id', $cat_id);
        $this->db->where('users.city_id', $city_id);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function getRow($table, $condition)
    {
        $this->db->where($condition);
        $query = $this->db->get($table);
        return $query->row_array();
    }

    public function getRowA($table, $condition)
    {
        $this->db->where($condition);
        $this->db->order_by('assign_job_id', 'DESC');
        $this->db->limit(1);
        $query = $this->db->get($table);
        return $query->row_array();
    }

    public function getJobInformationForclient($client_name, $client_address)
    {
        $this->db->select('*');
        $this->db->from($this->table);
        $this->db->join('categories', 'job.cat_id=categories.category_id', 'left');
        $this->db->where('client_name', $client_name);
        $this->db->where('job_address', $client_address);
        $this->db->where('is_applied', 0);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function getRows($table, $condition, $order_col = null, $order_type = null)
    {
        $this->db->where($condition);

        if (isset($limit)) {
            $this->db->limit($limit);
        }
        if (!empty($order_col) && !empty($order_type)) {
            $this->db->order_by($order_col, $order_type);
        }
        $query = $this->db->get($table);
        return $query->result_array();
    }

    public function getAsssignJob($job_id)
    {
        $this->db->select('CONCAT(admins.first_name," ",admins.last_name) name,assign_job.assign_status,assign_job.remarks');
        $this->db->from("assign_job");
        $this->db->join('admins', 'admins.admin_id=assign_job.mechanic_id', 'left');
        $this->db->where('job_id', $job_id);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function insert($table, $data)
    {
        $this->db->insert($table, $data);
        return $this->db->insert_id();
    }

    public function batch_insert($table, $data)
    {
        $this->db->insert_batch($table, $data);
        return 1;
    }

    public function getServices($job_id)
    {
        $this->db->from("job_services");
        $this->db->join('service', 'service.service_id=job_services.service_id');
        $this->db->where('job_id', $job_id);
        $query = $this->db->get();
        return $query->result_array();
    }

    public function get_completed_datatables()
    {
        $this->_get_completed_datatables_query();
        if ($_POST['length'] != -1)
            $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
        // $query = $this->db->last_query();
        // echo $query; die;
        return $query->result();
    }
    private function _get_completed_datatables_query()
    {
        $this->db->select('job.job_id,car.car_no,job.service,job.job_status');
        $this->db->from($this->table);
        $this->db->where("job_status", 2);
        $this->db->join('car', 'car.car_id=job.car_id', 'left');


        $i = 0;
        foreach ($this->column_search as $item) { // loop column     
            if ($_POST['search']['value']) { // if datatable send POST for search                 
                if ($i === 0) { // first loop
                    $this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
                    $this->db->like($item, $_POST['search']['value']);
                } else {
                    $this->db->or_like($item, $_POST['search']['value']);
                }
                if (count($this->column_search) - 1 == $i) //last loop
                    $this->db->group_end(); //close bracket
            }
            $i++;
        }

        if (isset($_POST['order'])) { // here order processing
            $this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        } else if (isset($this->order)) {
            $order = $this->order;
            $this->db->order_by(key($order), $order[key($order)]);
        }
    }

    public function count_completed_filtered()
    {
        $this->_get_completed_datatables_query();
        $query = $this->db->get();
        return $query->num_rows();
    }
    public function count_completed_all()
    {
        $this->db->select('job.job_id,car.car_no,job.service,job.job_status');
        $this->db->from($this->table);
        $this->db->where("job_status", 2);
        $this->db->join('car', 'car.car_id=job.car_id', 'left');

        return $this->db->count_all_results();
    }

    public function get_cars($table, $condition)
    {
        $this->db->where($condition);
        $this->db->order_by('car_no', 'asc');
        $query = $this->db->get($table);
        return $query->result_array();
    }
    public function delete_all_job_details($job_id)
    {
        $condition = array();
        $condition2 = array();
        $condition['job_id'] = $job_id;
        $job_details = $this->mjob->getRow('job', $condition);
        $condition2['car_id'] = $job_details['car_id'];
        $data['status'] = 0;
        $this->db->where($condition2);
        $this->db->update('car', $data);
        $this->db->delete('job_service_date', $condition);
        $this->db->delete('job_services', $condition);
        $this->db->delete('assign_job', $condition);
        $this->db->delete('job', $condition);
        return 1;
    }

    public function getAssignedMechanic($job_id)
    {
        $this->db->select('assign_job.mechanic_id,admins.name,admins.first_name,admins.last_name');
        $this->db->from('assign_job');
        $this->db->join('admins', 'assign_job.mechanic_id=admins.admin_id', 'left');
        $this->db->where('assign_job.job_id', $job_id);
        $query = $this->db->get();
        return $query->row_array();
    }

    /**
     * Batch load assign_job records for multiple job IDs
     * Returns array indexed by job_id with latest assign_job record
     */
    public function getBatchAssignJobs($job_ids)
    {
        if (empty($job_ids)) return array();
        $this->db->select('assign_job.*');
        $this->db->from('assign_job');
        $this->db->where_in('job_id', $job_ids);
        $this->db->order_by('assign_job_id', 'DESC');
        $query = $this->db->get();
        $results = $query->result_array();

        // Get only the latest assign_job per job_id
        $latest = array();
        foreach ($results as $row) {
            if (!isset($latest[$row['job_id']])) {
                $latest[$row['job_id']] = $row;
            }
        }
        return $latest;
    }

    /**
     * Batch load job_service_date records for multiple job IDs
     * Returns array indexed by job_id
     */
    public function getBatchJobServiceDates($job_ids)
    {
        if (empty($job_ids)) return array();
        $this->db->select('*');
        $this->db->from('job_service_date');
        $this->db->where_in('job_id', $job_ids);
        $query = $this->db->get();
        $results = $query->result_array();

        $map = array();
        foreach ($results as $row) {
            $map[$row['job_id']] = $row;
        }
        return $map;
    }

    /**
     * Batch load services for multiple job IDs
     * Returns array with job_id as key and array of service names as value
     */
    public function getBatchServices($job_ids)
    {
        if (empty($job_ids)) return array();
        $this->db->select('job_services.job_id, service.service_name');
        $this->db->from('job_services');
        $this->db->join('service', 'service.service_id = job_services.service_id');
        $this->db->where_in('job_services.job_id', $job_ids);
        $query = $this->db->get();
        $results = $query->result_array();

        $map = array();
        foreach ($results as $row) {
            if (!isset($map[$row['job_id']])) {
                $map[$row['job_id']] = array();
            }
            $map[$row['job_id']][] = $row['service_name'];
        }
        return $map;
    }

    /**
     * Batch load mechanics for multiple mechanic IDs
     * Returns array indexed by admin_id
     */
    public function getBatchMechanics($mechanic_ids)
    {
        if (empty($mechanic_ids)) return array();
        $unique_ids = array_unique(array_filter($mechanic_ids));
        if (empty($unique_ids)) return array();

        $this->db->select('admin_id, first_name, last_name');
        $this->db->from('admins');
        $this->db->where_in('admin_id', $unique_ids);
        $query = $this->db->get();
        $results = $query->result_array();

        $map = array();
        foreach ($results as $row) {
            $map[$row['admin_id']] = $row;
        }
        return $map;
    }
}