<?php
class Mbookingreport extends CI_Model
{
  var $table = 'job_service_date';
  var $column_order = array('user_roaster_id', 'name', null); //set column field database for datatable orderable
  var $column_search = array('user_roaster_id', 'CONCAT(admins.first_name," ",admins.last_name)'); //set column field database for datatable searchable just firstname , lastname , address are searchable
  var $order = array('user_roaster_id' => 'desc'); // default order 
  public function __construct()
  {
    parent::__construct();
  }


  public function get_details($first_date, $last_date)
  {



    $this->db->distinct();
    $this->db->select('job_service_date.service_date');
    $this->db->from("job_service_date");
    $this->db->join('car', 'car.car_id=job_service_date.car_id', 'left');
    $this->db->join('assign_job', 'assign_job.job_id=job_service_date.job_id', 'left');
    //  $this->db->join('admins','admins.admin_id=assign_job.job_id','left'); 
    $this->db->where("job_service_date.service_date>=", $first_date);
    $this->db->where("job_service_date.service_date<=", $last_date);
    $query = $this->db->get();
    // $query = $this->db->last_query();
    // echo $query; die;
    return $query->result_array();
  }

  public function get_service_details($service_date)
  {


    $this->db->select('job_service_date.service_date,car.car_no,assign_job.mechanic_id');
    $this->db->from("job_service_date");
    $this->db->join('car', 'car.car_id=job_service_date.car_id', 'left');
    $this->db->join('assign_job', 'assign_job.job_id=job_service_date.job_id', 'left');
    //  $this->db->join('admins','admins.admin_id=assign_job.job_id','left'); 
    $this->db->where("job_service_date.service_date", $service_date);
    $query = $this->db->get();
    //$query = $this->db->last_query();
    //echo $query; die;
    return $query->result_array();
  }

  public function get_mechanic_details($mec_id)
  {
    $this->db->select('*');
    $this->db->from("admins");
    $this->db->where('admins.admin_id', $mec_id);
    $query = $this->db->get();
    // $query = $this->db->last_query();
    //echo $query; die;
    return $query->row_array();
  }

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

  public function assignedJob($condition)
  {
    $this->db->select('*');
    $this->db->from("assign_job");
    $this->db->join('job', 'job.job_id=assign_job.job_id', 'LEFT');
    $this->db->where($condition);
    $query = $this->db->get();
    return $query->result_array();
  }

  /**
   * Optimized method to get all calendar data with JOINs to avoid N+1 queries
   * This replaces the inefficient loop in the controller
   */
  public function getCalendarData($condition = array(), $start_date = null, $end_date = null)
  {
    // Reset query builder to avoid conflicts
    $this->db->reset_query();

    // Use SELECT * from assign_job (like assignedJob does) and add specific fields we need
    // This avoids column name issues
    $this->db->select('
			assign_job.*,
			job.car_id,
			MIN(job_service_date.service_date) as service_date,
			MAX(job_service_date.service_end_date) as service_end_date,
			car.car_no,
			car.company_name,
			mechanic.name as mechanic_name
		');
    $this->db->from("assign_job");
    $this->db->join('job', 'job.job_id = assign_job.job_id', 'LEFT');
    $this->db->join('job_service_date', 'job_service_date.job_id = assign_job.job_id', 'LEFT');
    $this->db->join('car', 'car.car_id = job.car_id', 'LEFT');
    // Note: created_by might be in job table or assign_job table - will handle in PHP if needed
    // $this->db->join('admins as assigned_by', 'assigned_by.admin_id = assign_job.created_by', 'LEFT');
    $this->db->join('admins as mechanic', 'mechanic.admin_id = assign_job.mechanic_id', 'LEFT');

    if (!empty($condition)) {
      $this->db->where($condition);
    }

    // Add date filtering BEFORE GROUP BY using WHERE on the joined table
    // This filters at the SQL level for better performance
    // Note: FullCalendar's end date is exclusive, so we use < instead of <=
    if ($start_date !== null && $start_date !== '') {
      $this->db->where('job_service_date.service_date >=', $start_date);
    }
    if ($end_date !== null && $end_date !== '') {
      $this->db->where('job_service_date.service_date <', $end_date);
    }

    // Group by job_id only to avoid column issues - MySQL will pick first value for other columns
    $this->db->group_by('assign_job.job_id');

    $this->db->order_by('service_date', 'ASC');

    $query = $this->db->get();

    if (!$query) {
      return array();
    }

    $results = $query->result_array();

    // If no results, return empty array
    if (empty($results)) {
      return array();
    }

    // Get service counts for all jobs in one query
    $job_ids = array();
    foreach ($results as $row) {
      if (isset($row['job_id']) && $row['job_id'] !== null && $row['job_id'] !== '') {
        $job_ids[] = $row['job_id'];
      }
    }

    $service_counts = array();
    if (!empty($job_ids)) {
      $this->db->reset_query();
      $this->db->select('job_services.job_id, COUNT(DISTINCT job_services.service_id) as service_count');
      $this->db->from('job_services');
      $this->db->join('service', 'service.service_id = job_services.service_id', 'LEFT');
      $this->db->where('service.status', 1);
      $this->db->where_in('job_services.job_id', $job_ids);
      $this->db->group_by('job_services.job_id');
      $service_query = $this->db->get();

      if ($service_query) {
        foreach ($service_query->result_array() as $svc) {
          if (isset($svc['job_id']) && $svc['job_id'] !== null) {
            $service_counts[$svc['job_id']] = isset($svc['service_count']) ? (int)$svc['service_count'] : 0;
          }
        }
      }
    }

    // Get service counts to results
    foreach ($results as $key => $row) {
      $job_id = isset($row['job_id']) ? $row['job_id'] : null;
      $results[$key]['service_count'] = ($job_id && isset($service_counts[$job_id])) ? $service_counts[$job_id] : 0;
    }

    // Get assigned_by names for all jobs in one query (batch fetch)
    $created_by_ids = array();
    foreach ($results as $row) {
      if (isset($row['created_by']) && !empty($row['created_by'])) {
        $created_by_ids[] = $row['created_by'];
      }
    }

    $assigned_by_names = array();
    if (!empty($created_by_ids)) {
      $this->db->reset_query();
      $this->db->select('admin_id, name');
      $this->db->from('admins');
      $this->db->where_in('admin_id', array_unique($created_by_ids));
      $admin_query = $this->db->get();
      if ($admin_query) {
        foreach ($admin_query->result_array() as $admin) {
          $assigned_by_names[$admin['admin_id']] = isset($admin['name']) ? $admin['name'] : '';
        }
      }
    }

    // Add assigned_by names to results
    foreach ($results as $key => $row) {
      $created_by = isset($row['created_by']) ? $row['created_by'] : null;
      $results[$key]['assigned_by_name'] = ($created_by && isset($assigned_by_names[$created_by])) ? $assigned_by_names[$created_by] : '';
    }

    return $results;
  }
}