<?php
class Cheques_Model extends CI_Model
{
	private $tableName = 'loan';


	function __construct()
	{
		/* Call the Model constructor */
		parent::__construct();
		$this->load->database();
		// $this->load->helper('url');
		//$this->load->library(array('email'));
	}

	function update_cheque_status($pay_id, $data)
	{
		if ($pay_id) {
			$this->db->where('paymnt_id', $pay_id);
			return $this->db->update('loan_payment', $data);
		} else {
			return 0;
		}
	}

	public function get_all_pending_not_realize_cheque(
		$route = null,
		$cus_id = null,
		$srh_from_date = null,
		$srh_to_date = null
	) {
		$this->db->select("
        lt.term_id,
        lt.loan_id,
        l.loan_ref,
        l.cus_id,
        l.route_id,
        lt.term_no,
        lt.term_date,
        lt.cheque_date,
        lt.term_amount,
        lt.paid_amount,
        lt.status,
        b.bank_name,
        bb.branch_name
    ");

		$this->db->from('loan_terms lt');

		$this->db->join('loan l', 'l.loan_id = lt.loan_id', 'inner');
		$this->db->join('banks b', 'b.bank_id = lt.bank_id', 'left');
		$this->db->join('bank_branches bb', 'bb.id = lt.branch_id', 'left');

		// 🔹 Cheque payment term only
		$this->db->where('l.loan_payment_term', 4);

		// 🔹 Pending / Not realized cheques
		$this->db->where('lt.cheque_date IS NOT NULL', null, false);
		$this->db->where('lt.realize_date IS NULL', null, false);
		$this->db->where_in('lt.status', ['Pending', 'Overdue', 'Not Collected']);

		// 🔹 Active loans only
		$this->db->where('l.is_closed', 0);

		// 🔹 OPTIONAL FILTERS
		if (!empty($route)) {
			$this->db->where('l.route_id', $route);
		}

		if (!empty($cus_id)) {
			$this->db->where('l.cus_id', $cus_id);
		}

		if (!empty($srh_from_date)) {
			$this->db->where('lt.cheque_date >=', $srh_from_date);
		}

		if (!empty($srh_to_date)) {
			$this->db->where('lt.cheque_date <=', $srh_to_date);
		}

		$this->db->order_by('lt.cheque_date', 'ASC');

		return $this->db->get()->result_array();
	}


	public function get_all_not_realize_cheques($route = null, $cus_id = null, $srh_from_date = null, $srh_to_date = null)
	{

		$this->db->select("
        lt.term_id,
        lt.loan_id,
        l.loan_ref,
        l.cus_id,
        l.route_id,
		bi.business_name,
        lt.cheque_date AS pymnt_cheque_date,
        lt.cheque_no AS pymnt_cheque_no,
        lt.term_amount AS pymnt_amount,
        b.bank_name AS bank_code,
        bb.branch_name AS pymnt_branch,
        lt.check_realize_status
    ");

		$this->db->from('loan_terms lt');

		$this->db->join('loan l', 'l.loan_id = lt.loan_id', 'inner');
		$this->db->join('banks b', 'b.bank_id = lt.bank_id', 'left');
		$this->db->join('bank_branches bb', 'bb.id = lt.branch_id', 'left');
		$this->db->join('business_information bi', 'bi.customer_id=l.cus_id', 'left');

		// Only cheque loans
		$this->db->where('l.loan_payment_term', 4);

		// Only not realized
		$this->db->where('lt.check_realize_status !=', 'Realized');

		// Active loans
		$this->db->where('l.is_closed', 0);

		if (!empty($route)) {
			//$this->db->where('l.route_id', $route);
		}

		if (!empty($cus_id)) {
			$this->db->where('l.cus_id', $cus_id);
		}

		if (!empty($srh_from_date)) {
			$this->db->where('lt.cheque_date >=', $srh_from_date);
		}

		if (!empty($srh_to_date)) {
			//$this->db->where('lt.cheque_date <=', $srh_to_date);
		}

		$this->db->order_by('lt.cheque_date', 'ASC');

		return $this->db->get()->result_array();
	}


	function get_loan_paid_total_by_loan_id($loan_id)
	{
		$this->db->select_sum('pymnt_amount');
		$this->db->from('loan_payment');
		$this->db->where('loan_payment.pymnt_returned', 0);
		$this->db->where('loan_payment.loan_id', $loan_id);
		$query = $this->db->get();
		return $query->row_array();
	}
}
