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

class Sales_payments_model extends CI_Model {

	//Datatable start
	var $table = 'db_salespayments as a';
	var $column_order = array( 
							'a.id',
							'a.payment_date',
							'a.payment_code',
							'a.customer_id',
							'a.sales_id',
							'a.payment',
							'a.payment_type',
							'a.created_by',
							'a.store_id',
							'a.cheque_number',
							'a.cheque_period',
							'a.cheque_status',
							'a.payment_note',
							); //set column field database for datatable orderable
	var $column_search = array( 
							'a.id',
							'a.payment_date',
							'a.payment_code',
							'a.customer_id',
							'a.sales_id',
							'a.payment',
							'a.payment_type',
							'a.created_by',
							'a.store_id',
							'a.cheque_number',
							'a.cheque_period',
							'a.cheque_status',
							'a.payment_note',
							);//set column field database for datatable searchable 
	var $order = array('a.id' => 'desc'); // default order  

	public function __construct()
	{
		parent::__construct();
		$CI =& get_instance();
	}

	private function _get_datatables_query()
	{
		
		$this->db->select($this->column_order);
		$this->db->from($this->table);
		
		//if(!is_admin()){
	      $this->db->where("a.store_id",get_current_store_id());
	    //}
	      if(!is_admin()){
	      	if($this->session->userdata('role_id')!='2'){
	      		if(!permissions('show_all_users_sales_invoices')){
	      			$this->db->where("upper(a.created_by)",strtoupper($this->session->userdata('inv_username')));
	      		}
	      	}
	      }

	     // Enhanced filter options
	     $payment_type_search = $this->input->post('payment_type_search');
	     $cheque_status_search = $this->input->post('cheque_status_search');
	     $customer_search = $this->input->post('customer_search');
	     $from_date = $this->input->post('from_date');
	     $to_date = $this->input->post('to_date');
	     $payment_amount_from = $this->input->post('payment_amount_from');
	     $payment_amount_to = $this->input->post('payment_amount_to');
	     $created_by_search = $this->input->post('created_by_search');
	     
	     if(!empty($payment_type_search)){
	     	$this->db->where("upper(payment_type)",strtoupper($payment_type_search));
	     }
	     if(!empty($cheque_status_search)){
	     	$this->db->where("upper(cheque_status)",strtoupper($cheque_status_search));
	     }
	     if(!empty($customer_search)){
	     	$this->db->where("a.customer_id",$customer_search);
	     }
	     if(!empty($from_date)){
	     	$from_date = date('Y-m-d', strtotime($from_date));
	     	$this->db->where("a.payment_date >=",$from_date);
	     }
	     if(!empty($to_date)){
	     	$to_date = date('Y-m-d', strtotime($to_date));
	     	$this->db->where("a.payment_date <=",$to_date);
	     }
	     if(!empty($payment_amount_from)){
	     	$this->db->where("a.payment >=",$payment_amount_from);
	     }
	     if(!empty($payment_amount_to)){
	     	$this->db->where("a.payment <=",$payment_amount_to);
	     }
	     if(!empty($created_by_search)){
	     	$this->db->where("upper(a.created_by)",strtoupper($created_by_search));
	     }
	     
	   // echo $this->db->get_compiled_select();exit();
		$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)]);
		}
	}

	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();
	}

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

	public function count_all()
	{
		$this->db->where("store_id",get_current_store_id());
		$this->db->from($this->table);
		return $this->db->count_all_results();
	}
	//Datatable end

	public function xss_html_filter($input){
		return $this->security->xss_clean(html_escape($input));
	}

	// Get opening balance till before from date
	public function get_opening_balance($customer_id = null, $from_date = null) {
		$store_id = get_current_store_id();
		
		// Get customer opening balance
		$opening_balance = 0;
		if($customer_id) {
			$customer = $this->db->select('opening_balance')->where('id', $customer_id)->get('db_customers')->row();
			if($customer) {
				$opening_balance = $customer->opening_balance;
			}
		}
		
		// Add sales dues before from_date
		if($from_date && $customer_id) {
			$from_date = date('Y-m-d', strtotime($from_date));
			
			// Get total sales amount before from_date
			$sales_total = $this->db->select('COALESCE(SUM(grand_total), 0) as total')
								  ->where('customer_id', $customer_id)
								  ->where('store_id', $store_id)
								  ->where('sales_date <', $from_date)
								  ->get('db_sales')->row()->total;
			
			// Get total payments before from_date
			$payments_total = $this->db->select('COALESCE(SUM(payment), 0) as total')
									   ->where('customer_id', $customer_id)
									   ->where('store_id', $store_id)
									   ->where('payment_date <', $from_date)
									   ->get('db_salespayments')->row()->total;
			
			$opening_balance += ($sales_total - $payments_total);
		}
		
		return $opening_balance;
	}

	// Get closing balance 
	public function get_closing_balance($customer_id = null, $to_date = null) {
		$store_id = get_current_store_id();
		
		if(!$customer_id) {
			return 0;
		}
		
		// Get customer opening balance
		$customer = $this->db->select('opening_balance')->where('id', $customer_id)->get('db_customers')->row();
		$opening_balance = $customer ? $customer->opening_balance : 0;
		
		// Calculate total sales and payments up to to_date
		$date_condition = '';
		if($to_date) {
			$to_date = date('Y-m-d', strtotime($to_date));
			$date_condition = " AND sales_date <= '$to_date'";
		}
		
		// Get total sales amount up to to_date
		$sales_query = "SELECT COALESCE(SUM(grand_total), 0) as total FROM db_sales 
						WHERE customer_id = $customer_id AND store_id = $store_id" . $date_condition;
		$sales_total = $this->db->query($sales_query)->row()->total;
		
		// Get total payments up to to_date
		$payment_date_condition = '';
		if($to_date) {
			$payment_date_condition = " AND payment_date <= '$to_date'";
		}
		
		$payments_query = "SELECT COALESCE(SUM(payment), 0) as total FROM db_salespayments 
						   WHERE customer_id = $customer_id AND store_id = $store_id" . $payment_date_condition;
		$payments_total = $this->db->query($payments_query)->row()->total;
		
		return $opening_balance + $sales_total - $payments_total;
	}

	// Get summary data for the filtered period
	public function get_payments_summary() {
		$payment_type_search = $this->input->post('payment_type_search');
		$cheque_status_search = $this->input->post('cheque_status_search');
		$customer_search = $this->input->post('customer_search');
		$from_date = $this->input->post('from_date');
		$to_date = $this->input->post('to_date');
		$payment_amount_from = $this->input->post('payment_amount_from');
		$payment_amount_to = $this->input->post('payment_amount_to');
		$created_by_search = $this->input->post('created_by_search');
		
		$this->db->select('
			COUNT(*) as total_records,
			COALESCE(SUM(payment), 0) as total_amount,
			COALESCE(SUM(CASE WHEN sales_id IS NULL THEN payment ELSE 0 END), 0) as advance_collections,
			COALESCE(SUM(CASE WHEN sales_id IS NOT NULL THEN payment ELSE 0 END), 0) as sales_collections,
			COUNT(CASE WHEN sales_id IS NOT NULL THEN 1 END) as linked_sales_count
		');
		$this->db->from('db_salespayments a');
		$this->db->where('a.store_id', get_current_store_id());
		
		// Apply same filters as datatable
		if(!empty($payment_type_search)){
			$this->db->where("upper(payment_type)",strtoupper($payment_type_search));
		}
		if(!empty($cheque_status_search)){
			$this->db->where("upper(cheque_status)",strtoupper($cheque_status_search));
		}
		if(!empty($customer_search)){
			$this->db->where("a.customer_id",$customer_search);
		}
		if(!empty($from_date)){
			$from_date = date('Y-m-d', strtotime($from_date));
			$this->db->where("a.payment_date >=",$from_date);
		}
		if(!empty($to_date)){
			$to_date = date('Y-m-d', strtotime($to_date));
			$this->db->where("a.payment_date <=",$to_date);
		}
		if(!empty($payment_amount_from)){
			$this->db->where("a.payment >=",$payment_amount_from);
		}
		if(!empty($payment_amount_to)){
			$this->db->where("a.payment <=",$payment_amount_to);
		}
		if(!empty($created_by_search)){
			$this->db->where("upper(a.created_by)",strtoupper($created_by_search));
		}
		
		$result = $this->db->get()->row();
		
		// Add opening and closing balances if customer is selected
		if(!empty($customer_search)) {
			$result->opening_balance = $this->get_opening_balance($customer_search, $from_date);
			$result->closing_balance = $this->get_closing_balance($customer_search, $to_date);
		}
		
		// Calculate total debit amount (sales amounts for linked payments)
		$result->total_debit_amount = $this->get_total_sales_amount_for_filtered_payments($payment_type_search, $cheque_status_search, $customer_search, $from_date, $to_date, $payment_amount_from, $payment_amount_to, $created_by_search);
		
		return $result;
	}

	// Get total sales amount for filtered payments (debit side)
	private function get_total_sales_amount_for_filtered_payments($payment_type_search, $cheque_status_search, $customer_search, $from_date, $to_date, $payment_amount_from, $payment_amount_to, $created_by_search) {
		$this->db->select('COALESCE(SUM(s.grand_total), 0) as total_sales_amount');
		$this->db->from('db_salespayments a');
		$this->db->join('db_sales s', 'a.sales_id = s.id', 'left');
		$this->db->where('a.store_id', get_current_store_id());
		$this->db->where('a.sales_id IS NOT NULL');
		
		// Apply same filters as summary
		if(!empty($payment_type_search)){
			$this->db->where("upper(a.payment_type)",strtoupper($payment_type_search));
		}
		if(!empty($cheque_status_search)){
			$this->db->where("upper(a.cheque_status)",strtoupper($cheque_status_search));
		}
		if(!empty($customer_search)){
			$this->db->where("a.customer_id",$customer_search);
		}
		if(!empty($from_date)){
			$from_date = date('Y-m-d', strtotime($from_date));
			$this->db->where("a.payment_date >=",$from_date);
		}
		if(!empty($to_date)){
			$to_date = date('Y-m-d', strtotime($to_date));
			$this->db->where("a.payment_date <=",$to_date);
		}
		if(!empty($payment_amount_from)){
			$this->db->where("a.payment >=",$payment_amount_from);
		}
		if(!empty($payment_amount_to)){
			$this->db->where("a.payment <=",$payment_amount_to);
		}
		if(!empty($created_by_search)){
			$this->db->where("upper(a.created_by)",strtoupper($created_by_search));
		}
		
		$result = $this->db->get()->row();
		return $result ? $result->total_sales_amount : 0;
	}



	
	public function delete_payment($payment_id){
        $this->db->trans_begin();

        //ACCOUNT RESET
		$reset_accounts = $this->db->select("debit_account_id,credit_account_id")
									->where("ref_salespayments_id in ($payment_id)")
									->group_by("debit_account_id,credit_account_id")
									->get("ac_transactions");
		//ACCOUNT RESET END

		$salespayments = $this->db->query("select sales_id,customer_id from db_salespayments where id=$payment_id")->row();
		$sales_id = $salespayments->sales_id;
		$customer_id = $salespayments->customer_id;

		$q1=$this->db->query("delete from db_salespayments where id='$payment_id'");
		if(!$q1){
			return "failed";
		}
		$q2=$this->update_sales_payment_status($sales_id,$customer_id);
		if(!$q2){
			return "failed";
		}

		//ACCOUNT RESET
        if($reset_accounts->num_rows()>0){
        	foreach ($reset_accounts->result() as $res1) {
        		if(!update_account_balance($res1->debit_account_id)){
					return 'failed';
				}

				if(!update_account_balance($res1->credit_account_id)){
					return 'failed';
				}

        	}
        }
        //ACCOUNT RESET END

        if(!set_customer_tot_advance($customer_id)){
        	return 'failed';
        }
		$this->db->trans_commit();
		return "success";
		
	}

	public function show_cheque_payments_modal($payment_id){
		 $q3 = $this->db->select("sales_id,cheque_status")->where("id",$payment_id)->get("db_salespayments")->row();

		 $sales_id = $q3->sales_id;
		 $cheque_status = $q3->cheque_status;
		$q1=$this->db->query("select * from db_sales where id=$sales_id");
		$res1=$q1->row();
		$customer_id = $res1->customer_id;
		$q2=$this->db->query("select * from db_customers where id=$customer_id");
		$res2=$q2->row();

		$customer_name=$res2->customer_name;
	    $customer_mobile=$res2->mobile;
	    $customer_phone=$res2->phone;
	    $customer_email=$res2->email;
	    $customer_country=$res2->country_id;
	    $customer_state=$res2->state_id;
	    $customer_address=$res2->address;
	    $customer_postcode=$res2->postcode;
	    $customer_gst_no=$res2->gstin;
	    $customer_tax_number=$res2->tax_number;
	    $customer_opening_balance=$res2->opening_balance;
	    $customer_tot_advance=$res2->tot_advance;

	    $sales_date=$res1->sales_date;
	    $reference_no=$res1->reference_no;
	    $sales_code=$res1->sales_code;
	    $sales_note=$res1->sales_note;
	    $grand_total=$res1->grand_total;
	    $paid_amount=$res1->paid_amount;
	    $due_amount =$grand_total - $paid_amount;

	    if(!empty($customer_country)){
	      $customer_country = $this->db->query("select country from db_country where id='$customer_country'")->row()->country;  
	    }
	    if(!empty($customer_state)){
	      $customer_state = $this->db->query("select state from db_states where id='$customer_state'")->row()->state;  
	    }

		?>
		<div class="modal fade" id="pay_now">
		  <div class="modal-dialog ">
		    <div class="modal-content">
		      <div class="modal-header header-custom">
		        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
		          <span aria-hidden="true">&times;</span></button>
		        <h4 class="modal-title text-center"><?= $this->lang->line('payments'); ?></h4>
		      </div>
		      <div class="modal-body">
		        
		    <div class="row">
		      <div class="col-md-12">
		      	<div class="row invoice-info">
			        <div class="col-sm-4 invoice-col">
			          <?= $this->lang->line('customer_details'); ?>
			          <address>
			            <strong><?php echo  $customer_name; ?></strong><br>
			            <?php echo (!empty(trim($customer_mobile))) ? $this->lang->line('mobile').": ".$customer_mobile."<br>" : '';?>
			            <?php echo (!empty(trim($customer_phone))) ? $this->lang->line('phone').": ".$customer_phone."<br>" : '';?>
			            <?php echo (!empty(trim($customer_email))) ? $this->lang->line('email').": ".$customer_email."<br>" : '';?>
			            <?php echo (!empty(trim($customer_gst_no))) ? $this->lang->line('gst_number').": ".$customer_gst_no."<br>" : '';?>
			            <?php echo (!empty(trim($customer_tax_number))) ? $this->lang->line('tax_number').": ".$customer_tax_number."<br>" : '';?>
			            
			          </address>
			        </div>
			        <!-- /.col -->
			        <div class="col-sm-4 invoice-col">
			          <?= $this->lang->line('sales_details'); ?>
			          <address>
			            <b><?= $this->lang->line('invoice'); ?> #<?php echo  $sales_code; ?></b><br>
			            <b><?= $this->lang->line('date'); ?> :<?php echo show_date($sales_date); ?></b><br>
			            <b><?= $this->lang->line('grand_total'); ?> :<?php echo $grand_total; ?></b><br>
			          </address>
			        </div>
			        <!-- /.col -->
			       
			        <div class="col-sm-4 invoice-col">
			          <b><?= $this->lang->line('paid_amount'); ?> :<span><?php echo number_format($paid_amount,2,'.',''); ?></span></b><br>
			          <b><?= $this->lang->line('due_amount'); ?> :<span id='due_amount_temp'><?php echo number_format($due_amount,2,'.',''); ?></span></b><br>
			         
			        </div>
			        <!-- /.col -->
			      </div>
			      <!-- /.row -->
		      </div>
		      <div class="col-md-12">
		        <div>
		        <input type="hidden" name="payment_row_count" id='payment_row_count' value="1">
		        <div class="col-md-12  payments_div">
		          <div class="box box-solid bg-gray">
		            <div class="box-body">
			           

		              <div class="row">
		         		
		             
		                <div class="col-md-6">
		                  <div class="">
		                    <label for="cheque_status"><?= $this->lang->line('cheque_status'); ?></label>
		                    <select class="form-control" id='cheque_status' name="cheque_status">
		                     <?=get_cheque_status_select_list($cheque_status)?>
		                    </select>
		                    <span id="cheque_status_msg" style="display:none" class="text-danger"></span>
		                  </div>
		                </div>
		               

		                



		            <div class="clearfix"></div>
		        </div>  

		        </div>
		        </div>
		      </div><!-- col-md-12 -->
		    </div>
		      </div><!-- col-md-9 -->
		      <!-- RIGHT HAND -->
		    </div>
		      </div>
		      <div class="modal-footer">
		      	<input type="hidden" id="customer_id" value="<?=$customer_id?>">
		        <button type="button" class="btn btn-default btn-lg" data-dismiss="modal">Close</button>
		        <button type="button" onclick="update_cheque_payment(<?=$payment_id;?>)" class="btn bg-green btn-lg place_order btn-lg payment_save">Update<i class="fa  fa-check "></i></button>
		      </div>
		    </div>
		    <!-- /.modal-content -->
		  </div>
		  <!-- /.modal-dialog -->
		</div>
		<?php
	}

	public function update_cheque_payment(){
		extract($this->xss_html_filter(array_merge($this->data,$_POST,$_GET)));
		//print_r($this->xss_html_filter(array_merge($this->data,$_POST,$_GET)));exit();
    	$q1 = $this->db->query("update db_salespayments set cheque_status='".$cheque_status."' where id=$payment_id");
    	if(!$q1){
    		return "failed";
    	}
    	return "success";
	}

	// Update payment amount with validation and account adjustments
	public function update_payment_amount($payment_id, $new_amount) {
		$this->db->trans_begin();
		
		// Validate input
		if(!is_numeric($new_amount) || $new_amount <= 0) {
			return json_encode(array('status' => 'failed', 'message' => 'Invalid payment amount'));
		}
		
		// Get current payment details
		$current_payment = $this->db->select('payment, customer_id, sales_id, store_id')
									->where('id', $payment_id)
									->where('store_id', get_current_store_id())
									->get('db_salespayments')->row();
		
		if(!$current_payment) {
			return json_encode(array('status' => 'failed', 'message' => 'Payment record not found'));
		}
		
		$old_amount = $current_payment->payment;
		$customer_id = $current_payment->customer_id;
		$sales_id = $current_payment->sales_id;
		
		// Update payment amount
		$update_data = array(
			'payment' => $new_amount,
			'updated_date' => date('Y-m-d'),
			'updated_time' => date('h:i:s A')
		);
		
		$q1 = $this->db->where('id', $payment_id)->update('db_salespayments', $update_data);
		
		if(!$q1) {
			$this->db->trans_rollback();
			return json_encode(array('status' => 'failed', 'message' => 'Failed to update payment'));
		}
		
		// Update sales payment status if linked to a sale
		if(!empty($sales_id)) {
			$q2 = $this->update_sales_payment_status($sales_id, $customer_id);
			if(!$q2) {
				$this->db->trans_rollback();
				return json_encode(array('status' => 'failed', 'message' => 'Failed to update sales status'));
			}
		}
		
		// Update customer advance if needed
		if(!set_customer_tot_advance($customer_id)) {
			$this->db->trans_rollback();
			return json_encode(array('status' => 'failed', 'message' => 'Failed to update customer advance'));
		}
		
		$this->db->trans_commit();
		return json_encode(array(
			'status' => 'success', 
			'message' => 'Payment amount updated successfully',
			'new_amount' => store_number_format($new_amount)
		));
	}

	// Update payment date
	public function update_payment_date($payment_id, $new_date) {
		// Validate date format
		$date_obj = DateTime::createFromFormat('d-m-Y', $new_date);
		if(!$date_obj) {
			return json_encode(array('status' => 'failed', 'message' => 'Invalid date format. Use DD-MM-YYYY'));
		}
		
		$formatted_date = $date_obj->format('Y-m-d');
		
		// Check if payment exists and belongs to current store
		$payment_exists = $this->db->select('id')
								   ->where('id', $payment_id)
								   ->where('store_id', get_current_store_id())
								   ->get('db_salespayments')->num_rows();
		
		if(!$payment_exists) {
			return json_encode(array('status' => 'failed', 'message' => 'Payment record not found'));
		}
		
		// Update payment date
		$update_data = array(
			'payment_date' => $formatted_date,
			'updated_date' => date('Y-m-d'),
			'updated_time' => date('h:i:s A')
		);
		
		$q1 = $this->db->where('id', $payment_id)->update('db_salespayments', $update_data);
		
		if(!$q1) {
			return json_encode(array('status' => 'failed', 'message' => 'Failed to update payment date'));
		}
		
		return json_encode(array(
			'status' => 'success', 
			'message' => 'Payment date updated successfully',
			'new_date' => show_date($formatted_date)
		));
	}

	// Helper method to update sales payment status (if not exists)
	public function update_sales_payment_status($sales_id, $customer_id) {
		if(empty($sales_id)) {
			return true; // No sales linked, so nothing to update
		}
		
		// Get total sales amount
		$sales_info = $this->db->select('grand_total')
							   ->where('id', $sales_id)
							   ->get('db_sales')->row();
		
		if(!$sales_info) {
			return false;
		}
		
		// Calculate total payments for this sale
		$total_payments = $this->db->select('COALESCE(SUM(payment), 0) as total')
								   ->where('sales_id', $sales_id)
								   ->get('db_salespayments')->row()->total;
		
		// Update sales record
		$sales_status = ($total_payments >= $sales_info->grand_total) ? 'Final' : 'Partial';
		
		$update_data = array(
			'paid_amount' => $total_payments,
			'payment_status' => $sales_status
		);
		
		return $this->db->where('id', $sales_id)->update('db_sales', $update_data);
	}

	// Get export data with filters applied
	public function get_export_data($filters = array()) {
		$this->db->select('
			a.payment_code,
			a.payment_date,
			a.sales_id,
			a.customer_id,
			a.payment,
			a.payment_type,
			a.payment_note,
			a.created_by,
			a.cheque_number,
			a.cheque_period,
			a.cheque_status
		');
		$this->db->from('db_salespayments a');
		$this->db->where('a.store_id', get_current_store_id());
		
		// Apply filters
		if(!empty($filters['customer_search'])){
			$this->db->where("a.customer_id", $filters['customer_search']);
		}
		if(!empty($filters['payment_type_search'])){
			$this->db->where("upper(a.payment_type)", strtoupper($filters['payment_type_search']));
		}
		if(!empty($filters['cheque_status_search'])){
			$this->db->where("upper(a.cheque_status)", strtoupper($filters['cheque_status_search']));
		}
		if(!empty($filters['from_date'])){
			$from_date = date('Y-m-d', strtotime($filters['from_date']));
			$this->db->where("a.payment_date >=", $from_date);
		}
		if(!empty($filters['to_date'])){
			$to_date = date('Y-m-d', strtotime($filters['to_date']));
			$this->db->where("a.payment_date <=", $to_date);
		}
		if(!empty($filters['payment_amount_from'])){
			$this->db->where("a.payment >=", $filters['payment_amount_from']);
		}
		if(!empty($filters['payment_amount_to'])){
			$this->db->where("a.payment <=", $filters['payment_amount_to']);
		}
		if(!empty($filters['created_by_search'])){
			$this->db->where("upper(a.created_by)", strtoupper($filters['created_by_search']));
		}
		
		$this->db->order_by('a.id', 'desc');
		return $this->db->get()->result();
	}

	// Generate detailed CSV content
	public function generate_detailed_csv($filters, $payments_data, $summary_data) {
		$csv_content = '';
		
		// Add BOM for UTF-8
		$csv_content .= "\xEF\xBB\xBF";
		
		// Header information
		$csv_content .= '"SALES PAYMENTS DETAILED REPORT"' . "\n";
		$csv_content .= '"Generated on: ' . date('Y-m-d H:i:s') . '"' . "\n";
		$csv_content .= '""' . "\n";
		
		// Filter information
		$csv_content .= '"APPLIED FILTERS"' . "\n";
		$csv_content .= '"================="' . "\n";
		
		// Get customer name
		$customer_name = 'All Customers';
		if(!empty($filters['customer_search'])) {
			$customer = $this->db->select('customer_name')->where('id', $filters['customer_search'])->get('db_customers')->row();
			if($customer) {
				$customer_name = $customer->customer_name;
			}
		}
		$csv_content .= '"Customer Name:","' . $customer_name . '"' . "\n";
		
		$payment_type = !empty($filters['payment_type_search']) ? $filters['payment_type_search'] : 'All Types';
		$csv_content .= '"Payment Type:","' . $payment_type . '"' . "\n";
		
		$cheque_status = !empty($filters['cheque_status_search']) ? $filters['cheque_status_search'] : 'All Status';
		$csv_content .= '"Payment Status:","' . $cheque_status . '"' . "\n";
		
		$from_date = !empty($filters['from_date']) ? $filters['from_date'] : 'All Dates';
		$csv_content .= '"From Date:","' . $from_date . '"' . "\n";
		
		$to_date = !empty($filters['to_date']) ? $filters['to_date'] : 'All Dates';
		$csv_content .= '"To Date:","' . $to_date . '"' . "\n";
		
		$created_by = !empty($filters['created_by_search']) ? $filters['created_by_search'] : 'All Users';
		$csv_content .= '"Created By:","' . $created_by . '"' . "\n";
		
		$amount_from = !empty($filters['payment_amount_from']) ? $filters['payment_amount_from'] : 'No Minimum';
		$csv_content .= '"Amount From:","' . $amount_from . '"' . "\n";
		
		$amount_to = !empty($filters['payment_amount_to']) ? $filters['payment_amount_to'] : 'No Maximum';
		$csv_content .= '"Amount To:","' . $amount_to . '"' . "\n";
		
		$csv_content .= '""' . "\n";
		
		// Summary information
		$csv_content .= '"SUMMARY INFORMATION"' . "\n";
		$csv_content .= '"=================="' . "\n";
		$csv_content .= '"Total Payments Count:","' . $summary_data->total_records . '"' . "\n";
		$csv_content .= '"Total Credit Amount:","' . number_format($summary_data->total_amount, 2) . '"' . "\n";
		$csv_content .= '"Total Debit Amount:","' . number_format($summary_data->total_debit_amount, 2) . '"' . "\n";
		$csv_content .= '"Linked Sales Count:","' . $summary_data->linked_sales_count . '"' . "\n";
		
		// Customer balance if available
		if(!empty($filters['customer_search']) && isset($summary_data->opening_balance)) {
			$csv_content .= '""' . "\n";
			$csv_content .= '"CUSTOMER BALANCE"' . "\n";
			$csv_content .= '"==============="' . "\n";
			$csv_content .= '"Opening Balance:","' . number_format($summary_data->opening_balance, 2) . '"' . "\n";
			$csv_content .= '"Closing Balance:","' . number_format($summary_data->closing_balance, 2) . '"' . "\n";
		}
		
		$csv_content .= '""' . "\n";
		$csv_content .= '"PAYMENT DETAILS"' . "\n";
		$csv_content .= '"==============="' . "\n";
		
		// Table headers
		$csv_content .= '"Payment Code","Payment Date","Sales Code","Customer Name","Credit Amount","Debit Amount","Bill Amount","Payment Type","Payment Note","Created By"' . "\n";
		
		// Table data
		foreach($payments_data as $payment) {
			$sales_code = 'Account Payment Collection';
			$credit_amount = number_format($payment->payment, 2);
			$debit_amount = '0.00';
			$bill_amount = '0.00';
			
			if(!empty($payment->sales_id)) {
				$sales_code = get_sales_code($payment->sales_id);
				// For sales payments, show debit amount and bill amount
				$sales_details = get_sales_details($payment->sales_id);
				if($sales_details) {
					$debit_amount = number_format($sales_details->grand_total, 2);
					$bill_amount = number_format($sales_details->grand_total, 2);
				}
			}
			
			$customer_name = 'N/A';
			$customer = get_customer_details($payment->customer_id);
			if($customer) {
				$customer_name = $customer->customer_name;
			}
			
			$csv_content .= '"' . $payment->payment_code . '",';
			$csv_content .= '"' . show_date($payment->payment_date) . '",';
			$csv_content .= '"' . $sales_code . '",';
			$csv_content .= '"' . str_replace('"', '""', $customer_name) . '",';
			$csv_content .= '"' . $credit_amount . '",';
			$csv_content .= '"' . $debit_amount . '",';
			$csv_content .= '"' . $bill_amount . '",';
			$csv_content .= '"' . $payment->payment_type . '",';
			$csv_content .= '"' . str_replace('"', '""', $payment->payment_note) . '",';
			$csv_content .= '"' . $payment->created_by . '"' . "\n";
		}
		
		return $csv_content;
	}
	
	// Get customers with outstanding balances who have no payment records
	public function get_customers_with_outstanding_balances() {
		$store_id = get_current_store_id();
		
		$sql = "
		SELECT 
			c.id as customer_id,
			c.customer_name,
			c.opening_balance,
			c.current_balance,
			COALESCE(SUM(s.grand_total), 0) as total_sales,
			COALESCE(SUM(sp.payment), 0) as total_payments
		FROM db_customers c
		LEFT JOIN db_sales s ON c.id = s.customer_id AND s.store_id = ? AND s.status = 1
		LEFT JOIN db_salespayments sp ON c.id = sp.customer_id AND sp.store_id = ? AND sp.status = 1
		WHERE c.store_id = ? 
		AND c.status = 1 
		AND c.id != 1  -- Exclude walk-in customer
		AND (c.opening_balance > 0 OR c.current_balance > 0 OR COALESCE(SUM(s.grand_total), 0) > COALESCE(SUM(sp.payment), 0))
		GROUP BY c.id, c.customer_name, c.opening_balance, c.current_balance
		HAVING (c.opening_balance > 0 OR c.current_balance > 0 OR SUM(s.grand_total) > SUM(sp.payment))
		";
		
		$query = $this->db->query($sql, array($store_id, $store_id, $store_id));
		return $query->result();
	}
	
	// Get unpaid invoices for customers
	public function get_unpaid_invoices($customer_id = null) {
		$store_id = get_current_store_id();
		
		$this->db->select('s.*, c.customer_name');
		$this->db->from('db_sales s');
		$this->db->join('db_customers c', 's.customer_id = c.id', 'left');
		$this->db->where('s.store_id', $store_id);
		$this->db->where('s.status', 1);
		$this->db->where('s.payment_status !=', 'Paid');
		$this->db->where('c.id !=', 1); // Exclude walk-in customer
		
		if($customer_id) {
			$this->db->where('s.customer_id', $customer_id);
		}
		
		$query = $this->db->get();
		return $query->result();
	}
	
	// Create entries for unpaid invoices to show in payments report
	public function create_unpaid_invoice_entries($customer_id = null) {
		$unpaid_invoices = $this->get_unpaid_invoices($customer_id);
		$created_entries = 0;
		
		foreach($unpaid_invoices as $invoice) {
			// Check if this invoice already has payment entries
			$existing_payments = $this->db->where('sales_id', $invoice->id)
										  ->where('store_id', get_current_store_id())
										  ->get('db_salespayments')->num_rows();
			
			if($existing_payments == 0) {
				// Calculate due amount
				$due_amount = $invoice->grand_total - $invoice->paid_amount;
				
				if($due_amount > 0) {
					// Create a "Pending Payment" entry to show the outstanding invoice
					$payment_data = array(
						'count_id' => $this->get_next_payment_count_id(),
						'payment_code' => $this->generate_payment_code(),
						'store_id' => get_current_store_id(),
						'sales_id' => $invoice->id,
						'payment_date' => $invoice->sales_date,
						'payment_type' => 'Pending Payment',
						'payment' => 0, // No payment made yet
						'payment_note' => 'Unpaid Invoice - Due Amount: ' . number_format($due_amount, 2) . ' (Invoice: ' . $invoice->sales_code . ')',
						'change_return' => 0,
						'system_ip' => $this->input->ip_address(),
						'system_name' => gethostname(),
						'created_time' => date('h:i:s a'),
						'created_date' => date('Y-m-d'),
						'created_by' => $this->session->userdata('inv_username'),
						'status' => 1,
						'customer_id' => $invoice->customer_id
					);
					
					if($this->db->insert('db_salespayments', $payment_data)) {
						$created_entries++;
					}
				}
			}
		}
		
		return $created_entries;
	}
	
	// Create virtual payment records for customers with outstanding balances
	public function create_outstanding_balance_entries($customer_id = null) {
		$customers = $this->get_customers_with_outstanding_balances();
		$created_entries = 0;
		
		foreach($customers as $customer) {
			// Skip if customer filter is specified and doesn't match
			if($customer_id && $customer->customer_id != $customer_id) {
				continue;
			}
			
			// Check if customer already has payment records
			$existing_payments = $this->db->where('customer_id', $customer->customer_id)
										  ->where('store_id', get_current_store_id())
										  ->get('db_salespayments')->num_rows();
			
			if($existing_payments == 0 && $customer->current_balance > 0) {
				// Create a virtual "Outstanding Balance" entry
				$payment_data = array(
					'count_id' => $this->get_next_payment_count_id(),
					'payment_code' => $this->generate_payment_code(),
					'store_id' => get_current_store_id(),
					'sales_id' => null, // No specific sale
					'payment_date' => date('Y-m-d'),
					'payment_type' => 'Outstanding Balance',
					'payment' => 0, // No payment made
					'payment_note' => 'Outstanding balance entry - Opening Balance: ' . $customer->opening_balance,
					'change_return' => 0,
					'system_ip' => $this->input->ip_address(),
					'system_name' => gethostname(),
					'created_time' => date('h:i:s a'),
					'created_date' => date('Y-m-d'),
					'created_by' => $this->session->userdata('inv_username'),
					'status' => 1,
					'customer_id' => $customer->customer_id
				);
				
				if($this->db->insert('db_salespayments', $payment_data)) {
					$created_entries++;
				}
			}
		}
		
		return $created_entries;
	}
	
	// Get next payment count ID
	private function get_next_payment_count_id() {
		$query = $this->db->select_max('count_id')->get('db_salespayments');
		$max_id = $query->row()->count_id;
		return ($max_id ? $max_id + 1 : 1);
	}
	
	// Generate payment code
	private function generate_payment_code() {
		$count_id = $this->get_next_payment_count_id();
		return 'SP' . str_pad($count_id, 4, '0', STR_PAD_LEFT);
	}
	
	
}
