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

use Dompdf\Dompdf;
use Dompdf\Options;


class Sales extends MY_Controller {
	public function __construct(){
		parent::__construct();
		$this->load_global();
		$this->load->model('sales_model','sales');
		$this->load->helper('sms_template_helper');
	}

	public function is_sms_enabled(){
		return is_sms_enabled();
	}

	public function index()
	{
		$this->permission_check('sales_view');
		$data=$this->data;
		$data['page_title']=$this->lang->line('sales_list');
		$this->load->view('sales-list',$data);
	}

	//Convert to Quotation to Sales Invoice
	public function quotation($quotation_id){
		//Load model
		$this->load->model('customers_model');

		$this->belong_to('db_quotation',$quotation_id);
		$this->permission_check('sales_add');
		$data=$this->data;
		$data['page_title']=$this->lang->line('quotation_to_sales_invoice');
		$data['quotation_id']=$quotation_id;

		$data=array_merge($data);

		$this->load->view('sales',$data);
	}

	public function add()
	{	
		try {
			$this->permission_check('sales_add');
			$data=$this->data;
			$data['page_title']=$this->lang->line('sales');
			$this->load->view('sales',$data);
		} catch (Exception $e) {
			log_message('error', 'Sales add error: ' . $e->getMessage());
			log_message('error', 'Sales add error trace: ' . $e->getTraceAsString());
			show_error('An error occurred while loading the sales page: ' . $e->getMessage(), 500);
		}
	}
	

	public function sales_save_and_update(){
		$this->form_validation->set_rules('sales_date', 'Sales Date', 'trim|required');
		$this->form_validation->set_rules('customer_id', 'Customer Name', 'trim|required');
		
		if ($this->form_validation->run() == TRUE) {
			// Add timeout and error handling
			set_time_limit(30); // 30 second timeout
			try {
				log_message('debug', 'Sales controller - Starting save process');
				error_log('Sales controller - Starting save process');
				$result = $this->sales->verify_save_and_update();
				log_message('debug', 'Sales controller - Save result: ' . $result);
				error_log('Sales controller - Save result: ' . $result);
				
				// Let the original response handling work normally
				echo $result;
			} catch (Exception $e) {
				log_message('error', 'Sales save error: ' . $e->getMessage());
				log_message('error', 'Sales save error trace: ' . $e->getTraceAsString());
				error_log('Sales save error: ' . $e->getMessage());
				error_log('Sales save error trace: ' . $e->getTraceAsString());
				echo json_encode(['success' => false, 'message' => 'An error occurred while saving. Please try again.', 'type' => 'error']);
			}
		} else {
			log_message('debug', 'Sales controller - Form validation failed');
			error_log('Sales controller - Form validation failed');
			echo json_encode(['success' => false, 'message' => 'Please Fill Compulsory(* marked) Fields.', 'type' => 'validation_error']);
		}
	}
	
	
	public function update($id){
		//Load model
		$this->load->model('customers_model');

		//Verification of invoice
		$this->belong_to('db_sales',$id);

		//Permission check
		$this->permission_check('sales_edit');

		$data=$this->data;

		$data=array_merge($data,array('sales_id'=>$id));

		$data['page_title']=$this->lang->line('sales');

		$this->load->view('sales', $data);
	}

	public function ajax_list()
	{
		// Suppress display of errors but still log them (critical for live server)
		@ini_set('display_errors', 0);
		@ini_set('log_errors', 1);
		// Don't suppress all errors - just don't display them
		error_reporting(E_ALL);
		
		// Start output buffering with error suppression
		while (ob_get_level()) {
			ob_end_clean();
		}
		ob_start();
		
		// Set JSON header early and prevent any other output
		header('Content-Type: application/json; charset=utf-8');
		header('Cache-Control: no-cache, must-revalidate');
		header('Expires: Mon, 26 Jul 1997 05:00:00 GMT');
		
		// Enable error reporting for debugging (set to false in production)
		$debug_mode = true; // Temporarily enabled to diagnose empty table issue
		
		try {
			$list = $this->sales->get_datatables();
			
			if ($list === false || $list === null) {
				throw new Exception('get_datatables() returned invalid result');
			}
			
			$data = array();
			$no = isset($_POST['start']) ? intval($_POST['start']) : 0;
			foreach ($list as $sales) {
			
			$no++;
			$row = array();
			$row[] = '<input type="checkbox" name="checkbox[]" value='.$sales->id.' class="checkbox column_checkbox" >';
			
		$row[] = date('d-m-Y', strtotime($sales->sales_date)) . '<br>' . $sales->created_time;

			#-----------------------------------------------------
			$date_difference = date_difference($sales->due_date,date("Y-m-d"));
					$str='';
					//$info='';
					if($date_difference>0){
			          $str= "<br><span class='label label-danger' style='cursor:pointer'> $date_difference days Overdue</span>";
					}
					else{
						$str= "<br><span class='label label-info' style='cursor:pointer'> ".abs($date_difference)." days Left</span>";
					}
			$row[] = (!empty($sales->due_date) && $sales->payment_status!='Paid') ? show_date($sales->due_date).$str : '';
			#-----------------------------------------------------

			$info = (!empty($sales->quotation_id)) ? "<br><span class='label label-success' style='cursor:pointer'><i class='fa fa-fw  fa-check-circle'></i>Against Quotation</span>" : '';

			// Show "Return Raised" indicator when a return exists (as shown in the image)
			$info .= (!empty($sales->return_bit)) ? "<br><span class='label label-danger' style='cursor:pointer'><i class='fa fa-fw fa-undo'></i>Return Raised</span>" : '';

			$row[] = $sales->sales_code.$info;
			
			$row[] = $sales->reference_no;
			$row[] = $sales->customer_name;
			
			// Display total with currency information
			$base_currency = get_base_currency();
			// Validate base_currency
			if (!$base_currency || !is_object($base_currency)) {
				log_message('error', 'get_base_currency() returned invalid value for sales ID ' . $sales->id);
				// Fallback to default currency object
				$base_currency = (object)array('id' => 1, 'currency_code' => 'USD');
			}
			$total_display = store_number_format($sales->grand_total);
			
			// Get transaction currency with error handling
			$transaction_currency = null;
			try {
				if (!empty($sales->transaction_currency_id)) {
					$transaction_currency = get_currency_by_id($sales->transaction_currency_id);
				} elseif (!empty($sales->customer_id)) {
					// Fallback to customer's trading currency
					$transaction_currency = get_customer_trading_currency($sales->customer_id);
				}
			} catch (Exception $e) {
				log_message('error', 'Error getting transaction currency for sales ID ' . $sales->id . ': ' . $e->getMessage());
			}
			
			// If no transaction currency found, use base currency
			if (!$transaction_currency || !is_object($transaction_currency)) {
				$transaction_currency = $base_currency;
			}
			
			// Check if we need to convert (if transaction currency is different from base currency)
			// Validate transaction_currency object before accessing properties
			if (is_object($transaction_currency) && isset($transaction_currency->id) && is_object($base_currency) && isset($base_currency->id) && $transaction_currency->id != $base_currency->id) {
				// Get exchange rate with error handling
				$exchange_rate = 1.000000;
				$has_exchange_rate = false;
				try {
					$exchange_rate = get_exchange_rate($transaction_currency->id, $base_currency->id);
					$has_exchange_rate = $this->check_exchange_rate_exists($transaction_currency->id, $base_currency->id);
				} catch (Exception $e) {
					log_message('error', 'Error getting exchange rate for sales ID ' . $sales->id . ': ' . $e->getMessage());
				}
				
				if ($has_exchange_rate && $exchange_rate != 1.000000) {
					// Convert amount to base currency
					$converted_total = convert_currency($sales->grand_total, $transaction_currency->id, $base_currency->id);
					$currency_code = isset($transaction_currency->currency_code) ? $transaction_currency->currency_code : '';
					$base_currency_code = isset($base_currency->currency_code) ? $base_currency->currency_code : '';
					$conversion_note = ' (Rate: 1 ' . $currency_code . ' = ' . number_format($exchange_rate, 6) . ' ' . $base_currency_code . ')';
					$total_display = '<strong>' . store_number_format($converted_total) . ' ' . $base_currency_code . '</strong><br><small class="text-muted">' . store_number_format($sales->grand_total) . ' ' . $currency_code . $conversion_note . '</small>';
				} else {
					// No exchange rate available
					$currency_code = isset($transaction_currency->currency_code) ? $transaction_currency->currency_code : '';
					$conversion_note = ' <span class="text-warning" title="Exchange rate not found">⚠ No exchange rate</span>';
					$total_display = '<strong>' . store_number_format($sales->grand_total) . ' ' . $currency_code . '</strong><br><small class="text-muted">' . $conversion_note . '</small>';
				}
			} else {
				// Same currency, no conversion needed
				$base_currency_code = (is_object($base_currency) && isset($base_currency->currency_code)) ? $base_currency->currency_code : '';
				$total_display = '<strong>' . store_number_format($sales->grand_total) . ' ' . $base_currency_code . '</strong>';
			}
			$row[] = $total_display;
			
			// Calculate actual paid amount by adding back return payments
			// (return payments reduce the original sales paid_amount, so we add them back for display)
			$return_payments_total = 0;
			if(!empty($sales->return_bit)){
				$return_payments = $this->db->select('COALESCE(SUM(payment),0) as total')
											->where('sales_id', $sales->id)
											->get('db_salespaymentsreturn')
											->row();
				if($return_payments){
					$return_payments_total = floatval($return_payments->total);
				}
			}
			$actual_paid_amount = $sales->paid_amount + $return_payments_total;
			
			// Display paid amount with currency information (using actual paid amount)
			$paid_display = store_number_format($actual_paid_amount);
			
			// Check if we need to convert (if transaction currency is different from base currency)
			// Validate transaction_currency object before accessing properties
			if (is_object($transaction_currency) && isset($transaction_currency->id) && is_object($base_currency) && isset($base_currency->id) && $transaction_currency->id != $base_currency->id) {
				// Get exchange rate with error handling
				$exchange_rate = 1.000000;
				$has_exchange_rate = false;
				try {
					$exchange_rate = get_exchange_rate($transaction_currency->id, $base_currency->id);
					$has_exchange_rate = $this->check_exchange_rate_exists($transaction_currency->id, $base_currency->id);
				} catch (Exception $e) {
					log_message('error', 'Error getting exchange rate for paid amount sales ID ' . $sales->id . ': ' . $e->getMessage());
				}
				
				if ($has_exchange_rate && $exchange_rate != 1.000000) {
					// Convert amount to base currency
					$converted_paid = convert_currency($actual_paid_amount, $transaction_currency->id, $base_currency->id);
					$currency_code = isset($transaction_currency->currency_code) ? $transaction_currency->currency_code : '';
					$base_currency_code = isset($base_currency->currency_code) ? $base_currency->currency_code : '';
					$paid_conversion_note = ' (Rate: 1 ' . $currency_code . ' = ' . number_format($exchange_rate, 6) . ' ' . $base_currency_code . ')';
					$paid_display = '<strong>' . store_number_format($converted_paid) . ' ' . $base_currency_code . '</strong><br><small class="text-muted">' . store_number_format($actual_paid_amount) . ' ' . $currency_code . $paid_conversion_note . '</small>';
				} else {
					// No exchange rate available
					$currency_code = isset($transaction_currency->currency_code) ? $transaction_currency->currency_code : '';
					$paid_conversion_note = ' <span class="text-warning" title="Exchange rate not found">⚠ No exchange rate</span>';
					$paid_display = '<strong>' . store_number_format($actual_paid_amount) . ' ' . $currency_code . '</strong><br><small class="text-muted">' . $paid_conversion_note . '</small>';
				}
			} else {
				// Same currency, no conversion needed
				$base_currency_code = (is_object($base_currency) && isset($base_currency->currency_code)) ? $base_currency->currency_code : '';
				$paid_display = '<strong>' . store_number_format($actual_paid_amount) . ' ' . $base_currency_code . '</strong>';
			}
			$row[] = $paid_display;
			
			// Calculate actual payment status by adding back return payments
			$actual_payment_status = $sales->payment_status;
			$tolerance = 0.01;
			if($actual_paid_amount >= ($sales->grand_total - $tolerance)){
				$actual_payment_status = "Paid";
			} else if($actual_paid_amount > 0){
				$actual_payment_status = "Partial";
			} else {
				$actual_payment_status = "Unpaid";
			}
			
			$str='';
			if($actual_payment_status=='Unpaid')
	          $str= "<span class='label label-danger' style='cursor:pointer'>Unpaid </span>";
	        if($actual_payment_status=='Partial')
	          $str="<span class='label label-warning' style='cursor:pointer'> Partial </span>";
	        if($actual_payment_status=='Paid')
	          $str="<span class='label label-success' style='cursor:pointer'> Paid </span>";

			$row[] = $str;
			$row[] = ($sales->created_by);

					 if($sales->pos ==1):
					 	$str1='pos/edit/';
					 else:
					 	$str1='sales/update/';
					 endif;

					$str2 = '<div class="btn-group" title="View Account">
										<a class="btn btn-primary btn-o dropdown-toggle" data-toggle="dropdown" href="#">
											Action <span class="caret"></span>
										</a>
										<ul role="menu" class="dropdown-menu dropdown-light pull-right">';
											if($this->permissions('sales_view'))
											$str2.='<li>
												<a title="View Invoice" href="'.base_url().'sales/invoice/'.$sales->id.'" >
													<i class="fa fa-fw fa-eye text-blue"></i>View sales
												</a>
											</li>';

											if($this->permissions('sales_edit'))
											$str2.='<li>
												<a title="Update Record ?" href="'.base_url().''.$str1.$sales->id.'">
													<i class="fa fa-fw fa-edit text-blue"></i>Edit
												</a>
											</li>';

											if($this->permissions('sales_payment_view'))
											$str2.='<li>
												<a title="Pay" class="pointer" onclick="view_payments('.$sales->id.')" >
													<i class="fa fa-fw fa-money text-blue"></i>View Payments
												</a>
											</li>';

											if($this->permissions('sales_payment_add'))
											$str2.='<li>
												<a title="Receive Payments" class="pointer" onclick="pay_now('.$sales->id.')" >
													<i class="fa fa-fw fa-hourglass-half text-blue"></i>Receive Payments
												</a>
											</li>';

											if($this->permissions('sales_add') || $this->permissions('sales_edit'))
											$str2.='
											<li>
												<a title="Download PDF" target="_blank" href="'.base_url().'pdf/sales/'.$sales->id.'">
													<i class="fa fa-fw fa-file-pdf-o text-blue"></i>PDF
												</a>
											</li>
											<li>
												<a style="cursor:pointer" title="Print POS Invoice ?" onclick="print_invoice('.$sales->id.')">
													<i class="fa fa-fw fa-file-text text-blue"></i>POS Invoice
												</a>
											</li>
											<li>
												<a title="Print Delivery Note" target="_blank" href="'.base_url().'sales/print_delivery_note/'.$sales->id.'">
													<i class="fa fa-fw fa-truck text-green"></i>Delivery Note
												</a>
											</li>';

											if($this->permissions('sales_return_add'))
											$str2.='<li>
												<a title="Sales Return" href="'.base_url().'sales_return/add/'.$sales->id.'">
													<i class="fa fa-fw fa-undo text-blue"></i>Sales Return
												</a>
											</li>';

											if($this->permissions('sales_delete'))
											$str2.='<li>
												<a style="cursor:pointer" title="Delete Record ?" onclick="delete_sales(\''.$sales->id.'\')">
													<i class="fa fa-fw fa-trash text-red"></i>Delete
												</a>
											</li>
											
										</ul>
									</div>';			

				$row[] = $str2;

				$data[] = $row;
			}

			// Get counts with error handling
			$recordsTotal = 0;
			$recordsFiltered = 0;
			try {
				$recordsTotal = $this->sales->count_all();
			} catch (Exception $e) {
				log_message('error', 'Error in count_all(): ' . $e->getMessage());
			}
			try {
				$recordsFiltered = $this->sales->count_filtered();
			} catch (Exception $e) {
				log_message('error', 'Error in count_filtered(): ' . $e->getMessage());
				// Fallback to recordsTotal if count_filtered fails
				$recordsFiltered = $recordsTotal;
			}
			
			$output = array(
							"draw" => isset($_POST['draw']) ? intval($_POST['draw']) : 1,
							"recordsTotal" => $recordsTotal,
							"recordsFiltered" => $recordsFiltered,
							"data" => $data,
					);
			
			// Clear any output buffer and output JSON (ensure clean output)
			ob_clean();
			
			// Ensure no whitespace or errors before JSON
			$json_output = json_encode($output, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
			
			if ($json_output === false) {
				throw new Exception('JSON encoding failed: ' . json_last_error_msg());
			}
			
			echo $json_output;
		} catch (Exception $e) {
			$error_message = $e->getMessage();
			$error_file = $e->getFile();
			$error_line = $e->getLine();
			$error_trace = $e->getTraceAsString();
			
			log_message('error', 'DataTables ajax_list Exception: ' . $error_message);
			log_message('error', 'File: ' . $error_file . ' Line: ' . $error_line);
			log_message('error', 'Stack trace: ' . $error_trace);
			
			// Log database errors if any
			if (isset($this->db)) {
				$db_error = $this->db->error();
				if (!empty($db_error['message'])) {
					log_message('error', 'Database error: ' . $db_error['message']);
					log_message('error', 'Last query: ' . $this->db->last_query());
				}
			}
			
			// Return error response in DataTables format
			ob_clean();
			$error_response = array(
				"draw" => isset($_POST['draw']) ? intval($_POST['draw']) : 1,
				"recordsTotal" => 0,
				"recordsFiltered" => 0,
				"data" => array(),
				"error" => "An error occurred while loading data. Please refresh the page."
			);
			
			// Include detailed error in debug mode
			if ($debug_mode) {
				$error_response["debug"] = array(
					"message" => $error_message,
					"file" => basename($error_file),
					"line" => $error_line,
					"trace" => explode("\n", substr($error_trace, 0, 500)) // Limit trace length
				);
			}
			
			$json_output = json_encode($error_response, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
			if ($json_output === false) {
				// Fallback if JSON encoding fails
				ob_clean();
				echo '{"draw":1,"recordsTotal":0,"recordsFiltered":0,"data":[],"error":"JSON encoding error"}';
			} else {
				echo $json_output;
			}
		} catch (Error $e) {
			// Handle PHP 7+ fatal errors
			$error_message = $e->getMessage();
			$error_file = $e->getFile();
			$error_line = $e->getLine();
			$error_trace = $e->getTraceAsString();
			
			log_message('error', 'DataTables ajax_list PHP Fatal Error: ' . $error_message);
			log_message('error', 'File: ' . $error_file . ' Line: ' . $error_line);
			log_message('error', 'Stack trace: ' . $error_trace);
			
			ob_clean();
			$error_response = array(
				"draw" => isset($_POST['draw']) ? intval($_POST['draw']) : 1,
				"recordsTotal" => 0,
				"recordsFiltered" => 0,
				"data" => array(),
				"error" => "An error occurred while loading data. Please refresh the page."
			);
			
			// Include detailed error in debug mode
			if ($debug_mode) {
				$error_response["debug"] = array(
					"message" => $error_message,
					"file" => basename($error_file),
					"line" => $error_line,
					"trace" => explode("\n", substr($error_trace, 0, 500)) // Limit trace length
				);
			}
			
			$json_output = json_encode($error_response, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES);
			if ($json_output === false) {
				// Fallback if JSON encoding fails
				ob_clean();
				echo '{"draw":1,"recordsTotal":0,"recordsFiltered":0,"data":[],"error":"JSON encoding error"}';
			} else {
				echo $json_output;
			}
		}
		
		// End output buffering and ensure no extra output
		ob_end_flush();
		exit; // Prevent any additional output
	}
	public function update_status(){
		$this->permission_check('sales_edit');
		$id=$this->input->post('id');
		$status=$this->input->post('status');

		
		$result=$this->sales->update_status($id,$status);
		return $result;
	}
	public function delete_sales(){
		$this->permission_check_with_msg('sales_delete');
		$id=$this->input->post('q_id');
		
		// Check for account conflicts before deletion
		$conflict_check = $this->check_account_conflicts($id);
		if ($conflict_check !== true) {
			echo "conflict:" . $conflict_check;
			return;
		}
		
		// Add timeout and error handling
		set_time_limit(30);
		try {
			$result = $this->sales->delete_sales($id);
			echo $result;
		} catch (Exception $e) {
			log_message('error', 'Sales delete error: ' . $e->getMessage());
			error_log('Sales delete error: ' . $e->getMessage());
			echo "failed";
		}
	}
	
	private function check_account_conflicts($sales_id) {
		// Check if there are any account transactions that would be affected
		$this->db->select('at.id, at.credit_account_id, a.account_name, at.credit_amt, at.transaction_date');
		$this->db->from('ac_transactions at');
		$this->db->join('ac_accounts a', 'at.credit_account_id = a.id', 'left');
		$this->db->where('at.ref_salespayments_id', $sales_id);
		$this->db->where('at.transaction_type', 'SALES PAYMENT');
		$transactions = $this->db->get();
		
		if ($transactions->num_rows() > 0) {
			$conflict_details = array();
			foreach ($transactions->result() as $transaction) {
				$conflict_details[] = array(
					'account_name' => $transaction->account_name,
					'amount' => $transaction->credit_amt,
					'date' => $transaction->transaction_date
				);
			}
			return json_encode($conflict_details);
		}
		
		return true; // No conflicts
	}
	public function multi_delete(){
		$this->permission_check_with_msg('sales_delete');
		$ids=implode (",",$_POST['checkbox']);
		
		// Add timeout and error handling
		set_time_limit(30);
		try {
			$result = $this->sales->delete_sales($ids);
			echo $result;
		} catch (Exception $e) {
			log_message('error', 'Sales multi delete error: ' . $e->getMessage());
			error_log('Sales multi delete error: ' . $e->getMessage());
			echo "failed";
		}
	}


	//Table ajax code
	public function search_item(){
		$q=$this->input->get('q');
		$result=$this->sales->search_item($q);
		echo $result;
	}
	public function find_item_details(){
		$id=$this->input->post('id');
		
		$result=$this->sales->find_item_details($id);
		echo $result;
	}

	//sales invoice form
	public function invoice($id)
	{	
		$this->belong_to('db_sales',$id);
		if(!$this->permissions('sales_add') && !$this->permissions('sales_edit') && !$this->permissions('sales_view')){
			$this->show_access_denied_page();
		}
		
		$data=$this->data;
		$data=array_merge($data,array('sales_id'=>$id));
		$data['page_title']=$this->lang->line('sales_invoice');
		$this->load->view('sal-invoice',$data);
	}
	
	//Print sales invoice 
	public function print_invoice($sales_id)
	{
		$this->belong_to('db_sales',$sales_id);
		if(!$this->permissions('sales_add') && !$this->permissions('sales_edit')){
			$this->show_access_denied_page();
		}
		
		$data=$this->data;
		$data=array_merge($data,array('sales_id'=>$sales_id));
		$data['page_title']=$this->lang->line('sales_invoice');

		$this->get_html_invoice($data);
	}


	

	//Print sales POS invoice 
	public function print_invoice_pos($sales_id)
	{

		$this->belong_to('db_sales',$sales_id);
		if(!$this->permissions('sales_add') && !$this->permissions('sales_edit')){
			$this->show_access_denied_page();
		}
		$data=$this->data;
		$data=array_merge($data,array('sales_id'=>$sales_id));
		$data['page_title']=$this->lang->line('sales_invoice');
		
		$this->load->view('sal-invoice-pos',$data);


	}

	// Print Delivery Note
	public function print_delivery_note($sales_id)
	{
		$this->belong_to('db_sales',$sales_id);
		if(!$this->permissions('sales_add') && !$this->permissions('sales_edit')){
			$this->show_access_denied_page();
		}
		$data=$this->data;
		$data=array_merge($data,array('sales_id'=>$sales_id));
		$data['page_title']='Delivery Note';

		$this->load->view('print-delivery-note',$data);
	}

	public function get_html_invoice($data){
		$invoice_format_id = get_invoice_format_id();

		if($invoice_format_id==1){
			$this->load->view('print-sales-invoice',$data);
		}
		elseif($invoice_format_id==2){
			$this->load->view('print-sales-invoice-2',$data);
		}
		elseif($invoice_format_id==4){
			$this->load->view('print-sales-invoice-4',$data);
		}
		elseif($invoice_format_id==5){
			$this->load->view('print-sales-invoice-5',$data);
		}
		else{
			$this->load->view('print-sales-invoice-3',$data);
		}
        // Get output html
        return $this->output->get_output();
	}
	public function pdf($sales_id){

		if(!$this->permissions('sales_add') && !$this->permissions('sales_edit')){
			$this->show_access_denied_page();
		}
		$this->belong_to('db_sales',$sales_id);

		$data=$this->data;
		$data['page_title']=$this->lang->line('sales_invoice');
        $data=array_merge($data,array('sales_id'=>$sales_id));


        $html = $this->get_html_invoice($data);
        
        $options = new Options();
		$options->set('isRemoteEnabled', true);
        $dompdf = new Dompdf($options);

        // Load HTML content
        $dompdf->loadHtml($html,'UTF-8');
        
        // (Optional) Setup the paper size and orientation
        $dompdf->setPaper('A4', 'portrait');/*landscape or portrait*/
        
        // Render the HTML as PDF
        $dompdf->render();
        
        // Output the generated PDF (1 = download and 0 = preview)
        $dompdf->stream("Sales-invoice-$sales_id-".date('M')."_".date('d')."_".date('Y'), array("Attachment"=>0));
	}
	
	

	
	/*v1.1*/
	public function return_row_with_data($rowcount,$item_id){
		echo $this->sales->get_items_info($rowcount,$item_id);
	}
	public function return_sales_list($sales_id){
		echo $this->sales->return_sales_list($sales_id);
	}
	public function delete_payment(){
		$this->permission_check_with_msg('sales_payment_delete');
		$payment_id = $this->input->post('payment_id');
		echo $this->sales->delete_payment($payment_id);
	}
	public function show_pay_now_modal(){
		$this->permission_check_with_msg('sales_view');
		$sales_id=$this->input->post('sales_id');
		echo $this->sales->show_pay_now_modal($sales_id);
	}
	public function save_payment(){
		$this->permission_check_with_msg('sales_add');
		echo $this->sales->save_payment();
	}
	public function view_payments_modal(){
		$this->permission_check_with_msg('sales_view');
		$sales_id=$this->input->post('sales_id');
		echo $this->sales->view_payments_modal($sales_id);
	}
	public function get_customers_select_list(){
		echo get_customers_select_list(null,$_POST['store_id']);
	}
	public function get_items_select_list(){
		$item_type = isset($_POST['item_type']) ? $_POST['item_type'] : '';
		echo get_items_select_list(null,$_POST['store_id'],$item_type);
	}
	public function get_tax_select_list(){
		echo get_tax_select_list(null,$_POST['store_id']);
	}
	/*Get warehouse select list*/
	public function get_warehouse_select_list(){
		echo get_warehouse_select_list(null,$_POST['store_id']);
	}

	public function get_brands_select_list(){
		echo get_brands_select_list(null,$_POST['store_id']);
	}
	public function get_categories_select_list(){
		echo get_categories_select_list(null,$_POST['store_id']);
	}

	public function get_payment_types_select_list(){
		echo get_payment_types_select_list(null,$_POST['store_id']);
	}

	//Print sales Payment Receipt
	public function print_show_receipt($payment_id){
		if(!$this->permissions('sales_add') && !$this->permissions('sales_edit')){
			$this->show_access_denied_page();
		}
		$data=$this->data;
		$data['page_title']=$this->lang->line('payment_receipt');
		$data=array_merge($data,array('payment_id'=>$payment_id));
		$this->load->view('print-cust-payment-receipt',$data);
	}
	
	public function get_users_select_list(){
		echo get_users_select_list($this->session->userdata("role_id"),$_POST['store_id']);
	}

	public function return_quotation_list($quotation_id){
		echo $this->sales->return_quotation_list($quotation_id);
	}
	
	public function check_currency_availability(){
		$customer_id = $this->input->post('customer_id');
		$response = array('success' => true, 'message' => '');
		
		if (!empty($customer_id)) {
			$customer_currency = get_customer_trading_currency($customer_id);
			$base_currency = get_base_currency();
			
			if ($customer_currency && $customer_currency->id != $base_currency->id) {
				$exchange_rate = get_exchange_rate($base_currency->id, $customer_currency->id);
				
				if ($exchange_rate == 1.0) {
					$response['success'] = false;
					$response['message'] = "No exchange rate found for {$base_currency->currency_name} to {$customer_currency->currency_name}. Please add an exchange rate in Exchange Rates Management.";
				}
			}
		}
		
		echo json_encode($response);
	}
	
	public function get_customer_currency(){
		$customer_id = $this->input->post('customer_id');
		$response = array('status' => false, 'currency_name' => '', 'currency_code' => '', 'currency_symbol' => '');
		
		// Debug logging
		log_message('debug', 'Getting customer currency for customer ID: ' . $customer_id);
		error_log('Getting customer currency for customer ID: ' . $customer_id);
		
		if (!empty($customer_id)) {
			$customer_currency = get_customer_trading_currency($customer_id);
			$base_currency = get_base_currency();
			
			// Debug logging
			log_message('debug', 'Customer currency: ' . ($customer_currency ? $customer_currency->currency_name : 'NULL'));
			log_message('debug', 'Base currency: ' . $base_currency->currency_name);
			error_log('Customer currency: ' . ($customer_currency ? $customer_currency->currency_name : 'NULL'));
			error_log('Base currency: ' . $base_currency->currency_name);
			
			$response['status'] = true;
			$response['base_currency_id'] = $base_currency->id;
			$response['base_currency_name'] = $base_currency->currency_name;
			$response['base_currency_code'] = $base_currency->currency_code;
			
			if ($customer_currency && $customer_currency->id != $base_currency->id) {
				$response['customer_currency_id'] = $customer_currency->id;
				$response['customer_currency_name'] = $customer_currency->currency_name;
				$response['customer_currency_code'] = $customer_currency->currency_code;
				$response['currency_name'] = $customer_currency->currency_name;
				$response['currency_code'] = $customer_currency->currency_code;
				$response['currency_symbol'] = $customer_currency->symbol;
				
				log_message('debug', 'Customer has different currency: ' . $customer_currency->currency_name);
				error_log('Customer has different currency: ' . $customer_currency->currency_name);
			} else {
				// Customer has same currency as base, but still show it
				$response['customer_currency_id'] = $base_currency->id;
				$response['customer_currency_name'] = $base_currency->currency_name;
				$response['customer_currency_code'] = $base_currency->currency_code;
				$response['currency_name'] = $base_currency->currency_name;
				$response['currency_code'] = $base_currency->currency_code;
				$response['currency_symbol'] = $base_currency->symbol;
				
				log_message('debug', 'Customer has same currency as base: ' . $base_currency->currency_name);
				error_log('Customer has same currency as base: ' . $base_currency->currency_name);
			}
		}
		
		log_message('debug', 'Customer currency response: ' . json_encode($response));
		error_log('Customer currency response: ' . json_encode($response));
		
		echo json_encode($response);
	}

	public function get_account_currency(){
		$account_id = $this->input->post('account_id');
		$response = array('success' => false, 'currency_id' => null, 'currency_name' => '', 'currency_code' => '');
		
		if (!empty($account_id)) {
			$this->db->select('a.currency_id, c.currency_name, c.currency_code');
			$this->db->from('ac_accounts a');
			$this->db->join('db_currency c', 'a.currency_id = c.id', 'left');
			$this->db->where('a.id', $account_id);
			$account = $this->db->get()->row();
			
			if ($account) {
				$response['success'] = true;
				$response['currency_id'] = $account->currency_id;
				$response['currency_name'] = $account->currency_name ?: 'Base Currency';
				$response['currency_code'] = $account->currency_code ?: get_base_currency()->currency_code;
			}
		}
		echo json_encode($response);
	}
	
	public function get_customer_type(){
		$customer_id = $this->input->post('customer_id');
		$response = array('status' => false, 'customer_type' => '', 'default_price_type' => '');
		
		if (!empty($customer_id)) {
			$this->db->select('customer_type, default_price_type');
			$this->db->from('db_customers');
			$this->db->where('id', $customer_id);
			$customer = $this->db->get()->row();
			
			if ($customer) {
				$response['status'] = true;
				$response['customer_type'] = $customer->customer_type ?: 'retail';
				$response['default_price_type'] = $customer->default_price_type ?: 'retail';
			}
		}
		
		echo json_encode($response);
	}
	
	public function get_accounts_by_currency(){
		$currency_id = $this->input->post('currency_id');
		$customer_id = $this->input->post('customer_id');
		$response = array('success' => false, 'accounts' => array());
		
		if (!empty($currency_id)) {
			$this->db->select('a.id, a.account_name, a.balance, a.currency_id, c.currency_name, c.currency_code, c.symbol as currency_symbol');
			$this->db->from('ac_accounts a');
			$this->db->join('db_currency c', 'a.currency_id = c.id', 'left');
			$this->db->where('a.currency_id', $currency_id);
			$this->db->where('a.store_id', get_current_store_id());
			$this->db->where('a.status', 1);
			$this->db->where('(a.delete_bit = 0 OR a.delete_bit = 1)'); // Include both regular and cash accounts
			$this->db->order_by('a.account_name', 'ASC');
			$accounts = $this->db->get()->result();
			
			if ($accounts) {
				$response['success'] = true;
				$response['accounts'] = $accounts;
				
				// Also get customer currency for auto-selection
				if ($customer_id) {
					$customer_currency = get_customer_trading_currency($customer_id);
					if ($customer_currency) {
						$response['customer_currency_id'] = $customer_currency->id;
					}
				}
			}
		}
		echo json_encode($response);
	}

	public function get_account_balance(){
		$account_id = $this->input->post('account_id');
		$response = array('success' => false, 'balance' => 0, 'currency_symbol' => '', 'currency_position' => 'before', 'currency_id' => null);

		if (!empty($account_id)) {
			$this->db->select('a.balance, a.currency_id, c.symbol, c.currency_position');
			$this->db->from('ac_accounts a');
			$this->db->join('db_currency c', 'a.currency_id = c.id', 'left');
			$this->db->where('a.id', $account_id);
			$this->db->where('a.store_id', get_current_store_id());
			$this->db->where('a.status', 1);
			$query = $this->db->get();
			
			if ($query->num_rows() > 0) {
				$account = $query->row();
				$response['success'] = true;
				$response['balance'] = $account->balance;
				$response['currency_symbol'] = $account->symbol;
				$response['currency_position'] = $account->currency_position;
				$response['currency_id'] = $account->currency_id;
			}
		}
		
		echo json_encode($response);
	}

	public function debug_accounts(){
		// Debug endpoint to see all accounts
		$this->db->select('a.id, a.account_name, a.currency_id, a.store_id, a.status, a.delete_bit, c.currency_name, c.currency_code');
		$this->db->from('ac_accounts a');
		$this->db->join('db_currency c', 'a.currency_id = c.id', 'left');
		$this->db->where('a.store_id', get_current_store_id());
		$accounts = $this->db->get()->result();
		
		echo "<h3>All Accounts in Database:</h3>";
		echo "<table border='1'>";
		echo "<tr><th>ID</th><th>Account Name</th><th>Currency ID</th><th>Currency Code</th><th>Status</th><th>Delete Bit</th></tr>";
		foreach($accounts as $account) {
			echo "<tr>";
			echo "<td>" . $account->id . "</td>";
			echo "<td>" . $account->account_name . "</td>";
			echo "<td>" . $account->currency_id . "</td>";
			echo "<td>" . $account->currency_code . "</td>";
			echo "<td>" . $account->status . "</td>";
			echo "<td>" . $account->delete_bit . "</td>";
			echo "</tr>";
		}
		echo "</table>";
		
		// Also show currencies
		echo "<h3>All Currencies in Database:</h3>";
		$currencies = $this->db->select('id, currency_name, currency_code, symbol, status')->get('db_currency')->result();
		echo "<table border='1'>";
		echo "<tr><th>ID</th><th>Currency Name</th><th>Currency Code</th><th>Symbol</th><th>Status</th></tr>";
		foreach($currencies as $currency) {
			echo "<tr>";
			echo "<td>" . $currency->id . "</td>";
			echo "<td>" . $currency->currency_name . "</td>";
			echo "<td>" . $currency->currency_code . "</td>";
			echo "<td>" . $currency->symbol . "</td>";
			echo "<td>" . $currency->status . "</td>";
			echo "</tr>";
		}
		echo "</table>";
	}
	
	public function get_accounts_for_currency(){
		$currency_id = $this->input->post('currency_id');
		$response = array('status' => false, 'accounts' => array());
		
		if (!empty($currency_id)) {
			$this->db->select('a.id, a.account_name, a.currency_id, c.currency_code');
			$this->db->from('ac_accounts a');
			$this->db->join('db_currency c', 'c.id = a.currency_id', 'left');
			$this->db->where('a.currency_id', $currency_id);
			$this->db->where('a.store_id', get_current_store_id()); // Add store filter
			$this->db->where('a.status', 1); // Only active accounts
			$this->db->where('a.delete_bit', 0); // Exclude deleted accounts
			$this->db->order_by('a.account_name', 'ASC');
			$result = $this->db->get();
			
			if ($result->num_rows() > 0) {
				$response['status'] = true;
				foreach ($result->result() as $account) {
					$response['accounts'][] = array(
						'id' => $account->id,
						'name' => $account->account_name,
						'currency_code' => $account->currency_code
					);
				}
			}
		}
		
		echo json_encode($response);
	}

	/**
	 * Check if exchange rate exists between two currencies
	 */
	private function check_exchange_rate_exists($from_currency_id, $to_currency_id, $store_id = null) {
		$date = date('Y-m-d');

		// Check direct rate
		$this->db->select('id');
		$this->db->from('db_exchange_rates');
		$this->db->where('from_currency_id', $from_currency_id);
		$this->db->where('to_currency_id', $to_currency_id);
		$this->db->where('effective_date <=', $date);
		$this->db->where('status', 1);
		// Include both manual and API rates - whatever is available
		$this->db->limit(1);

		$query = $this->db->get();
		log_message('debug', 'Direct rate query: ' . $this->db->last_query());
		if ($query->num_rows() > 0) {
			return true;
		}

		// Check reverse rate
		$this->db->select('id');
		$this->db->from('db_exchange_rates');
		$this->db->where('from_currency_id', $to_currency_id);
		$this->db->where('to_currency_id', $from_currency_id);
		$this->db->where('effective_date <=', $date);
		$this->db->where('status', 1);
		// Include both manual and API rates - whatever is available
		$this->db->limit(1);

		$query = $this->db->get();
		log_message('debug', 'Reverse rate query: ' . $this->db->last_query());
		return $query->num_rows() > 0;
	}

	public function get_exchange_rate(){
		$from_currency = $this->input->post('from_currency');
		$to_currency = $this->input->post('to_currency');
		$response = array('status' => false);
		
		if (!empty($from_currency) && !empty($to_currency)) {
			$exchange_rate = get_exchange_rate($from_currency, $to_currency);
			$base_currency = get_base_currency();
			
			// Check if exchange rate actually exists in database
			$has_exchange_rate = $this->check_exchange_rate_exists($from_currency, $to_currency);
			
			// Get currency details
			$from_currency_details = get_currency_by_id($from_currency);
			$to_currency_details = get_currency_by_id($to_currency);
			
			// Debug logging
			log_message('debug', 'Exchange rate check - From: ' . $from_currency . ', To: ' . $to_currency . ', Rate: ' . $exchange_rate . ', Has Rate: ' . ($has_exchange_rate ? 'Yes' : 'No'));
			
			$response['status'] = true;
			$response['exchange_rate'] = $has_exchange_rate ? $exchange_rate : null;
			$response['base_symbol'] = $base_currency->symbol;
			$response['from_code'] = $from_currency_details ? $from_currency_details->currency_code : '';
			$response['base_code'] = $base_currency->currency_code;
			$response['debug'] = array(
				'from_currency' => $from_currency,
				'to_currency' => $to_currency,
				'exchange_rate' => $exchange_rate,
				'has_exchange_rate' => $has_exchange_rate
			);
		}
		
		echo json_encode($response);
	}

	public function get_exchange_rate_for_payment()
	{
		$from_currency = $this->input->post('from_currency');
		$to_currency = $this->input->post('to_currency');
		
		if (empty($from_currency) || empty($to_currency)) {
			echo json_encode(['success' => false, 'message' => 'Currency IDs are required']);
			return;
		}
		
		$exchange_rate = get_exchange_rate($from_currency, $to_currency);
		
		if ($exchange_rate == 1.0) {
			echo json_encode(['success' => false, 'message' => 'No exchange rate found']);
			return;
		}
		
		echo json_encode(['success' => true, 'exchange_rate' => $exchange_rate]);
	}
	
	// Get available batches for an item
	public function get_available_batches() {
		$item_id      = $this->input->post('item_id');
		$warehouse_id = $this->input->post('warehouse_id');
		$response = array('success' => false, 'batches' => array());
		
		if (!$item_id) {
			echo json_encode($response);
			return;
		}
		
		log_message('error', "get_available_batches called for item_id: $item_id");
		
			// Get all batches directly from db_item_batches (the authoritative table)
			// This ensures we only show batches that actually exist and are valid
		try {
			// Query db_item_batches as the primary source
			// Calculate sold_qty from sales items for display purposes
			$all_batches_query = $this->db->query("
				SELECT 
					ib.batch_number,
					ib.manufacturing_date,
					ib.expiry_date,
					ib.quantity as purchase_qty,
					COALESCE(SUM(
						CASE 
							-- If unit_type is explicitly 'alternate', convert from sales_qty using conversion_factor
							WHEN si.unit_type = 'alternate' AND i.conversion_factor > 0 AND si.sales_qty > 0 THEN ROUND(si.sales_qty / i.conversion_factor, 4)
							-- If unit_type is 'main', use sales_qty directly
							WHEN si.unit_type = 'main' THEN si.sales_qty
							-- Use sales_qty_main when set and unit_type is NULL
							WHEN si.sales_qty_main IS NOT NULL AND si.sales_qty_main > 0 AND si.unit_type IS NULL THEN si.sales_qty_main
							-- If unit_type is NULL but item has multi-unit and sales_qty looks like alternate, convert
							WHEN si.unit_type IS NULL AND i.conversion_factor > 0 AND i.conversion_factor != 1.0 AND si.sales_qty > 0 
								 AND (si.sales_qty_main IS NULL OR si.sales_qty_main = 0) 
								 AND (si.sales_qty < i.conversion_factor OR si.sales_qty > i.conversion_factor * 10) 
								 THEN ROUND(si.sales_qty / i.conversion_factor, 4)
							-- Fallback to sales_qty_main if set
							WHEN si.sales_qty_main IS NOT NULL THEN si.sales_qty_main
							-- Default to sales_qty
							ELSE si.sales_qty
						END
					), 0) as sold_qty,
					ib.remaining_quantity as available_qty,
					ib.created_date as purchase_date
				FROM db_item_batches ib
				LEFT JOIN db_salesitems si ON ib.item_id = si.item_id 
					AND ib.batch_number = si.batch_number 
					AND si.status = 1
				LEFT JOIN db_sales s ON si.sales_id = s.id 
					AND s.status = 1 
					AND (s.sales_status = 'Final' OR s.sales_status IS NULL OR s.sales_status = '')
				LEFT JOIN db_items i ON si.item_id = i.id
				WHERE ib.item_id = ? 
					AND ib.batch_number IS NOT NULL 
					AND ib.batch_number != ''
					AND ib.status = 1
					AND ib.remaining_quantity > 0
				GROUP BY ib.id, ib.batch_number, ib.manufacturing_date, ib.expiry_date, ib.quantity, ib.remaining_quantity, ib.created_date
				ORDER BY (ib.expiry_date IS NULL), ib.expiry_date ASC, ib.manufacturing_date ASC
			", array($item_id));
			
			$all_batches = array();
			if($all_batches_query) {
				foreach($all_batches_query->result() as $row) {
					$all_batches[] = $row;
				}
			}
			
			$batches = array();
			$total_available = 0;
			
			log_message('error', "get_available_batches: Found " . count($all_batches) . " batches for item $item_id");
			log_message('error', "Purchase batches: " . ($purchase_batches ? $purchase_batches->num_rows() : 'NULL'));
			log_message('error', "Opening batches: " . ($opening_batches ? $opening_batches->num_rows() : 'NULL'));
			
			foreach($all_batches as $batch) {
				$batches[] = array(
					'batch_number' => $batch->batch_number,
					'manufacturing_date' => $batch->manufacturing_date,
					'expiry_date' => $batch->expiry_date,
					'available_qty' => $batch->available_qty,
					'purchase_qty' => $batch->purchase_qty,
					'sold_qty' => $batch->sold_qty,
					'purchase_date' => $batch->purchase_date
				);
				$total_available += $batch->available_qty;
			}

			// Item unit info for main/alternate display (batch qty is always in main unit)
			$item_units = $this->db->select('i.conversion_factor, i.main_unit_id, i.alternate_unit_id')
				->from('db_items i')->where('i.id', $item_id)->get()->row();
			$conversion_factor = 0;
			$main_unit_name = '';
			$alternate_unit_name = '';
			if ($item_units && !empty($item_units->main_unit_id) && !empty($item_units->alternate_unit_id) && !empty($item_units->conversion_factor)) {
				$conversion_factor = (float)$item_units->conversion_factor;
				$mu = $this->db->select('unit_name')->where('id', $item_units->main_unit_id)->get('db_units')->row();
				$au = $this->db->select('unit_name')->where('id', $item_units->alternate_unit_id)->get('db_units')->row();
				$main_unit_name = $mu ? $mu->unit_name : '';
				$alternate_unit_name = $au ? $au->unit_name : '';
			}
			$item_info = array(
				'conversion_factor' => $conversion_factor,
				'main_unit_name'    => $main_unit_name,
				'alternate_unit_name' => $alternate_unit_name
			);

			// Calculate overall stock using the unified helper (includes unbatched stock, returns, adjustments, transfers)
			$this->load->helper('custom_helper');
			$warehouse_id_for_stock = !empty($warehouse_id) ? (int)$warehouse_id : get_store_warehouse_id();
			$store_id_for_stock     = get_current_store_id();
			$overall_stock          = get_total_qty_of_warehouse_item($item_id, $warehouse_id_for_stock, $store_id_for_stock);
			$unbatched_stock        = max(0, $overall_stock - $total_available);
			
			$response = array(
				'success'         => true,
				'batches'         => $batches,
				'total_available' => $total_available,
				'overall_stock'   => $overall_stock,
				'unbatched_stock' => $unbatched_stock,
				'item_info'       => $item_info
			);
			
			log_message('error', "get_available_batches: Returning " . count($batches) . " batches, total available: $total_available");
			
			echo json_encode($response);
		} catch (Exception $e) {
			log_message('error', "get_available_batches error: " . $e->getMessage());
			echo json_encode(array('success' => false, 'batches' => array(), 'error' => $e->getMessage()));
		}
	}
	
	public function get_item_stock(){
		try {
			$item_id = $this->input->post('item_id');
			$warehouse_id = $this->input->post('warehouse_id');
			
			if (empty($item_id)) {
				echo json_encode(array('success' => false, 'message' => 'Item ID is required'));
				return;
			}
			
			if (empty($warehouse_id)) {
				$warehouse_id = get_store_warehouse_id();
			}
			
			// Get current store_id for proper stock calculation
			$store_id = get_current_store_id();
			
			// Get FRESH stock by calculating directly from purchases/sales (not from cache)
			// This ensures deleted purchases are excluded immediately
			// Uses purchase_qty_main and sales_qty_main for multi-unit support
			$available_qty = get_total_qty_of_warehouse_item($item_id, $warehouse_id, $store_id);
			
			// Debug logging to help troubleshoot stock issues
			log_message('debug', "get_item_stock: item_id=$item_id, warehouse_id=$warehouse_id, store_id=$store_id, available_qty=$available_qty");
			
			// Additional debug: Check recent sales for this item
			$recent_sales = $this->db->query("
				SELECT si.id, si.sales_qty, si.sales_qty_main, si.unit_type, s.sales_code, s.sales_status, s.status, s.store_id, s.warehouse_id
				FROM db_salesitems si
				INNER JOIN db_sales s ON si.sales_id = s.id
				WHERE si.item_id = ? 
				AND si.status = 1
				ORDER BY si.id DESC
				LIMIT 5
			", array($item_id));
			
			if ($recent_sales->num_rows() > 0) {
				foreach ($recent_sales->result() as $sale) {
					log_message('debug', "Recent sale: sales_code={$sale->sales_code}, sales_qty={$sale->sales_qty}, sales_qty_main={$sale->sales_qty_main}, unit_type={$sale->unit_type}, sales_status={$sale->sales_status}, status={$sale->status}, store_id={$sale->store_id}, warehouse_id={$sale->warehouse_id}");
				}
			}
			
			echo json_encode(array(
				'success' => true,
				'available_qty' => $available_qty
			));
		} catch (Exception $e) {
			log_message('error', 'get_item_stock error: ' . $e->getMessage());
			echo json_encode(array(
				'success' => false,
				'message' => 'Error fetching stock: ' . $e->getMessage(),
				'available_qty' => 0
			));
		}
	}
	
	// Get recommended batches for FIFO (First In, First Out) selection
	public function get_recommended_batches() {
		$item_id = $this->input->post('item_id');
		$required_qty = $this->input->post('required_qty') ?: 1;
		$response = array('success' => false, 'recommended_batches' => array());
		
		if (!$item_id) {
			echo json_encode($response);
			return;
		}
		
		// Get batches from both purchases and opening stock, ordered by expiry date (FIFO)
		// First get purchase batches
		// Use purchase_qty_main for multi-unit items (stock is always in main unit)
		// Get all batches directly from db_item_batches (the authoritative table)
		$all_batches_query = $this->db->query("
			SELECT 
				ib.batch_number,
				ib.manufacturing_date,
				ib.expiry_date,
				ib.quantity as purchase_qty,
				COALESCE(SUM(
					CASE 
						WHEN si.unit_type = 'alternate' AND i.conversion_factor > 0 AND si.sales_qty > 0 THEN ROUND(si.sales_qty / i.conversion_factor, 4)
						WHEN si.unit_type = 'main' THEN si.sales_qty
						WHEN si.sales_qty_main IS NOT NULL AND si.sales_qty_main > 0 AND si.unit_type IS NULL THEN si.sales_qty_main
						WHEN si.unit_type IS NULL AND i.conversion_factor > 0 AND i.conversion_factor != 1.0 AND si.sales_qty > 0 
							 AND (si.sales_qty_main IS NULL OR si.sales_qty_main = 0) 
							 AND (si.sales_qty < i.conversion_factor OR si.sales_qty > i.conversion_factor * 10) 
							 THEN ROUND(si.sales_qty / i.conversion_factor, 4)
						WHEN si.sales_qty_main IS NOT NULL THEN si.sales_qty_main
						ELSE si.sales_qty
					END
				), 0) as sold_qty,
				ib.remaining_quantity as available_qty,
				ib.created_date as purchase_date,
				DATEDIFF(ib.expiry_date, CURDATE()) as days_to_expiry
			FROM db_item_batches ib
			LEFT JOIN db_salesitems si ON ib.item_id = si.item_id 
				AND ib.batch_number = si.batch_number 
				AND si.status = 1
			LEFT JOIN db_sales s ON si.sales_id = s.id 
				AND s.status = 1 
				AND (s.sales_status = 'Final' OR s.sales_status IS NULL OR s.sales_status = '')
			LEFT JOIN db_items i ON si.item_id = i.id
			WHERE ib.item_id = ? 
				AND ib.batch_number IS NOT NULL 
				AND ib.batch_number != ''
				AND ib.status = 1
				AND ib.remaining_quantity > 0
			GROUP BY ib.id, ib.batch_number, ib.manufacturing_date, ib.expiry_date, ib.quantity, ib.remaining_quantity, ib.created_date
			ORDER BY (ib.expiry_date IS NULL), ib.expiry_date ASC, ib.manufacturing_date ASC
		", array($item_id));
		
		$all_batches = array();
		if($all_batches_query) {
			foreach($all_batches_query->result() as $row) {
				$all_batches[] = $row;
			}
		}
		
		$remaining_qty = $required_qty;
		$recommended_batches = array();
		$total_available = 0;
		
		// Calculate total available stock
		foreach ($all_batches as $row) {
			$total_available += $row->available_qty;
		}
		
		foreach ($all_batches as $row) {
			if ($remaining_qty <= 0) break;
			
			$qty_to_use = min($remaining_qty, $row->available_qty);
			
			$recommended_batches[] = array(
				'batch_number' => $row->batch_number,
				'manufacturing_date' => $row->manufacturing_date,
				'expiry_date' => $row->expiry_date,
				'available_qty' => $row->available_qty,
				'recommended_qty' => $qty_to_use,
				'days_to_expiry' => $row->days_to_expiry,
				'purchase_date' => $row->purchase_date
			);
			
			$remaining_qty -= $qty_to_use;
		}
		
		$response['success'] = true;
		$response['recommended_batches'] = $recommended_batches;
		$response['total_available'] = $total_available;
		$response['can_fulfill'] = $remaining_qty <= 0;
		$response['shortage'] = $remaining_qty > 0 ? $remaining_qty : 0;
		
		echo json_encode($response);
	}
	
	
}
