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

class Customer_Model extends CI_Model
{

	private $tableName = 'customer';

	function __construct()
	{
		/* Call the Model constructor */
		parent::__construct();
	}

	public function get_customer_banks($cus_id)
	{
		return $this->db->get_where('customer_banks', ['cus_id' => $cus_id])->result_array();
	}

	public function get_customer_loans($cus_id)
	{
		return $this->db->get_where('customer_loans', ['cus_id' => $cus_id])->result_array();
	}

	public function save_customer_banks($cus_id, $banks)
	{
		$this->db->delete('customer_banks', ['cus_id' => $cus_id]);
		foreach ($banks as $bank) {
			$bank['cus_id'] = $cus_id;
			$this->db->insert('customer_banks', $bank);
		}
	}

	public function save_customer_loans($cus_id, $loans)
	{
		$this->db->delete('customer_loans', ['cus_id' => $cus_id]);
		foreach ($loans as $loan) {
			$loan['cus_id'] = $cus_id;
			$this->db->insert('customer_loans', $loan);
		}
	}



	public function save_spouse($data)
	{
		$this->db->insert('customer_spouse', $data);
	}

	public function update_spouse($data, $cus_id)
	{
		$exists = $this->db->get_where('customer_spouse', ['cus_id' => $cus_id])->row();
		if ($exists) {
			$this->db->where('cus_id', $cus_id)->update('customer_spouse', $data);
		} else {
			$this->db->insert('customer_spouse', $data);
		}
	}

	public function save_guarantor($data)
	{
		$this->db->insert('customer_guarantor', $data);
	}

	public function update_guarantor($data, $cus_id)
	{
		$exists = $this->db->get_where('customer_guarantor', ['cus_id' => $cus_id])->row();
		if ($exists) {
			$this->db->where('cus_id', $cus_id)->update('customer_guarantor', $data);
		} else {
			$this->db->insert('customer_guarantor', $data);
		}
	}

	public function get_personal_info($cus_id)
	{
		$this->db->select('*');
		$this->db->from('customer');
		$this->db->where('cus_id', $cus_id);
		$query = $this->db->get();

		if ($query->num_rows() > 0) {
			return $query->row_array(); // return single row as associative array
		}
		return array(); // return empty if not found
	}


	public function is_document_uploaded($customer_id, $doc_type)
	{
		$this->db->where('cus_id', $customer_id);
		$this->db->where('doc_type', $doc_type);
		$query = $this->db->get('customer_documents');
		return $query->num_rows() > 0;
	}

	public function save_document($data)
	{
		return $this->db->insert('customer_documents', $data);
	}

	public function get_documents($customer_id)
	{
		$this->db->where('cus_id', $customer_id);
		$query = $this->db->get('customer_documents');
		return $query->result();
	}

	public function save_business_info($data)
	{
		if (!isset($data['customer_id'])) {
			return false; // customer_id is required
		}

		// Check if record already exists for this customer
		$this->db->where('customer_id', $data['customer_id']);
		$query = $this->db->get('business_information');

		if ($query->num_rows() > 0) {
			// Update existing record
			$this->db->where('customer_id', $data['customer_id']);
			$this->db->update('business_information', $data);
			return $data['customer_id']; // return customer_id for reference
		} else {
			// Insert new record
			$this->db->insert('business_information', $data);
			return $this->db->insert_id();
		}
	}


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

	public function get_business_info($customer_id)
	{
		$this->db->where('customer_id', $customer_id);
		$query = $this->db->get('business_information');
		return $query->row();
	}

	public function get_bank_accounts($business_id)
	{
		$this->db->where('business_id', $business_id);
		$query = $this->db->get('business_bank_accounts');
		return $query->result();
	}

	// Save customer data
	public function add_customer($data)
	{
		$this->db->insert('customer', $data);
		return $this->db->insert_id();
	}

	// Check if NIC already exists
	public function check_nic_exists($nic, $customer_id = null)
	{
		$this->db->where('cus_nic', $nic);
		if ($customer_id) {
			$this->db->where('cus_id !=', $customer_id);
		}
		return $this->db->get('customer')->num_rows() > 0;
	}

	// Generate customer code
	public function generate_customer_code()
	{
		$this->db->select_max('cus_id');
		$query = $this->db->get('customer');
		$result = $query->row();
		$next_id = $result->cus_id + 1;
		$timestamp = date('Ymd');

		return 'CUST' . $timestamp . str_pad($next_id, 4, '0', STR_PAD_LEFT);
	}



	function save_customer(&$customer_data, $cus_id = false)
	{
		if (!$cus_id) {
			$this->db->insert($this->tableName, $customer_data);
		} else {
			$this->db->where('cus_id', $cus_id);
			return $this->db->update($this->tableName, $customer_data);
		}
	}

	function customer_location_save($cus_id, $data)
	{
		if ($cus_id > 0) {
			$this->db->where('cus_id', $cus_id);
			return $this->db->update($this->tableName, $data);
		}
	}

	function get_all_customers()
	{
		$this->db->select($this->tableName . '.*');
		$this->db->order_by("cus_id", "asc");
		$this->db->where("cus_status", 1); //("id !=",$id);
		
		//$this->db->where("cus_id", $id);
		$query = $this->db->get($this->tableName);
		return $query->result_array();
	}


	function get_all_customer_del()
	{
		$this->db->select('customer.*');
		$this->db->from('mstr_country');
		$this->db->join('customer', 'customer.country_id = mstr_country.country_id', 'left');
		$this->db->order_by("customer.cus_id", "desc");
		$this->db->where("customer.cus_status IS NOT NULL"); //("id !=",$id);
		$query = $this->db->get();
		return $query->result_array();
	}

	function get_all_customer($start = '', $length = '', $search_key_val = '')
	{

		$search_key_val = preg_replace('/[^a-zA-Z0-9-_\.]/', '', $search_key_val);

		$this->db->select($this->tableName . '.*');
		$this->db->select('routes.route_name');
		$this->db->select('business_information.business_name,business_information.registered_address,business_information.correspondent_address');
		$this->db->from($this->tableName);
		$this->db->join('routes', 'routes.route_id=customer.cus_route', 'left');
		$this->db->join('business_information', 'business_information.customer_id=customer.cus_id', 'left');

		if ($search_key_val) {
			$this->db->where("customer.cus_code LIKE '%$search_key_val%' OR customer.cus_name LIKE '%$search_key_val%'OR customer.cus_nic LIKE '%$search_key_val%'OR customer.cus_mobile LIKE '%$search_key_val%'");
		}
		$this->db->order_by("cus_code", "asc");
		if ($start != '' && $length != '') {
			$this->db->limit($length, $start);
			$query = $this->db->get();
			return $query->result_array();
		} else {
			$query = $this->db->get();
			return  $query->num_rows();
		}
	}

	function get_all_guarantor_filter($search_key_val)
	{
		$this->db->select('*');
		$this->db->from('guarantors');
		if ($search_key_val) {
			$this->db->where("code LIKE '%$search_key_val%' OR name LIKE '%$search_key_val%'OR phone");
		}
		$this->db->order_by("name", "asc");
		$query = $this->db->get();
		return $query->result_array();
	}

	function get_all_customer_filter($search_key_val)
	{
		$this->db->select($this->tableName . '.*');
		$this->db->select('routes.route_name');
		$this->db->select('business_information.business_name');
		$this->db->from($this->tableName);
		$this->db->join('routes', 'routes.route_id=customer.cus_route', 'left');
		$this->db->join('business_information', 'business_information.customer_id=customer.cus_id', 'left');
		if ($search_key_val) {
			$this->db->where("customer.cus_code LIKE '%$search_key_val%' OR business_information.business_name LIKE '%$search_key_val%'OR customer.cus_nic LIKE '%$search_key_val%'OR customer.cus_mobile LIKE '%$search_key_val%'");
		}
		$this->db->order_by("cus_name", "asc");
		$this->db->limit(20);
		$query = $this->db->get();
		return $query->result_array();
	}

	public function get_customer_info($id)
	{
		$this->db->select('customer.*,business_information.*');
		$this->db->from('customer');
		$this->db->join('business_information', 'business_information.customer_id=customer.cus_id', 'left');
		$this->db->where("cus_id", $id);
		$this->db->order_by("cus_id", "desc");
		$query = $this->db->get();
		return $query->row_array();
	}

	public function get_spouse_info($cus_id)
	{
		return $this->db->get_where('customer_spouse', ['cus_id' => $cus_id])->row_array();
	}

	public function get_customer_guarantor_info($cus_id)
	{
		return $this->db->get_where('customer_guarantor', ['cus_id' => $cus_id])->row_array();
	}

	public function delete_customer($cus_id)
	{
		$this->db->where('cus_id', $cus_id);
		$this->db->delete('customer');
	}

	public function disable_customer($cus_id)
	{
		$data = array(
			'cus_status' => 0
		);
		$this->db->where('cus_id', $cus_id);
		$this->db->update('customer', $data);
	}

	public function enable_customer($cus_id)
	{
		$data = array(
			'cus_status' => 1
		);
		$this->db->where('cus_id', $cus_id);
		$this->db->update('customer', $data);
	}

	/********* Guarantor */

	function get_all_guarantors()
	{
		$this->db->select('guarantors.*');
		$this->db->order_by("id", "asc");
		$query = $this->db->get('guarantors');
		return $query->result_array();
	}

	public function get_guarantor_info($id)
	{
		$this->db->select('*');
		$this->db->from('guarantors');
		$this->db->where('id', $id);
		$query = $this->db->get();

		if ($query->num_rows() > 0) {
			return $query->row_array(); // return single row as associative array
		}
		return array(); // return empty if not found
	}



	public function delete_guarantor($id)
	{
		$this->db->where('id', $id);
		return $this->db->delete('guarantors');
	}

	public function disable_guarantor($id)
	{
		$data = array(
			'status' => 0
		);
		$this->db->where('id', $id);
		return $this->db->update('guarantors', $data);
	}

	public function enable_guarantor($id)
	{
		$data = array(
			'status' => 1
		);
		$this->db->where('id', $id);
		return $this->db->update('guarantors', $data);
	}
}
