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

class Reports extends MY_Controller {
	public function __construct(){
		parent::__construct();
		$this->load_global();
		$this->load->model('reports_model','reports');
	}
	
	// Reports index - redirect to sales report as default
	public function index(){
		// Check if user has permission for any report
		if($this->permissions('sales_report')){
			redirect('reports/sales');
		} elseif($this->permissions('purchase_report')){
			redirect('reports/purchase');
		} elseif($this->permissions('stock_report')){
			redirect('reports/stock');
		} elseif($this->permissions('expense_report')){
			redirect('reports/expense');
		} else {
			// If no report permissions, show access denied
			show_error('You do not have permission to access reports.', 403);
		}
	}
	
	
	//Supplier Items Report 
	public function supplier_items(){
		$this->permission_check('supplier_items_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('supplier_items_report');
		$this->load->view('report-supplier_items', $data);
	}
	public function show_supplier_items_report(){
		echo $this->reports->show_supplier_items_report();
	}
	
	//Sales Report 
	public function sales(){
		$this->permission_check('sales_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('sales_report');
		$this->load->view('report-sales', $data);
	}
	public function show_sales_report(){
		echo $this->reports->show_sales_report();
	}

	//Payment Method Breakdown Report 
	public function payment_method_breakdown(){
		$this->permission_check('payment_method_breakdown_report');
		$data=$this->data;
		$data['page_title']='Payment Method Breakdown Report';
		$this->load->view('report-payment-method-breakdown', $data);
	}
	public function show_payment_method_breakdown_report(){
		if(!$this->permissions('payment_method_breakdown_report')){
			echo '<tr><td colspan="7" class="text-center text-danger">Access denied. You do not have permission to view this report.</td></tr>';
			return;
		}
		echo $this->reports->show_payment_method_breakdown_report();
	}

	// Test method for payment breakdown
	public function test_payment_breakdown(){
		log_message('debug', 'Test Payment Breakdown - Method called');
		
		// Simple test query
		$query = "SELECT payment_type, COUNT(*) as count FROM db_salespayments WHERE payment_type IS NOT NULL GROUP BY payment_type LIMIT 5";
		$result = $this->db->query($query);
		
		if($result && $result->num_rows() > 0) {
			echo "<h3>Payment Types Found:</h3>";
			foreach($result->result() as $row) {
				echo "<p>" . $row->payment_type . ": " . $row->count . " records</p>";
			}
		} else {
			echo "<p>No payment data found or query failed</p>";
			if($this->db->error()) {
				echo "<p>Database Error: " . $this->db->error()['message'] . "</p>";
			}
		}
	}

	//Sales Return Report 
	public function sales_return(){
		$this->permission_check('sales_return_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('sales_return_report');
		$this->load->view('report-sales-return', $data);
	}
	public function show_sales_return_report(){
		echo $this->reports->show_sales_return_report();
	}

	//Purchase report
	public function purchase(){
		$this->permission_check('purchase_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('purchase_report');
		$this->load->view('report-purchase', $data);
	}
	public function show_purchase_report(){
		echo $this->reports->show_purchase_report();
	}

	//Purchase Return report
	public function purchase_return(){
		$this->permission_check('purchase_return_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('purchase_return_report');
		$this->load->view('report-purchase-return', $data);
	}
	public function show_purchase_return_report(){
		echo $this->reports->show_purchase_return_report();
	}

	//Expense report
	public function expense(){
		$this->permission_check('expense_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('expense_report');
		$this->load->view('report-expense', $data);
	}
	public function show_expense_report(){
		echo $this->reports->show_expense_report();
	}
	//Profit report
	public function get_profit_by_item(){
		echo $this->reports->get_profit_by_item();
	}
	public function get_profit_by_invoice(){
		echo $this->reports->get_profit_by_invoice();
	}

	//Summary report
	public function stock(){
		try {
			$this->permission_check('stock_report');
			$data=$this->data;
			$data['page_title']=$this->lang->line('stock_report');
			$this->load->view('report-stock', $data);
		} catch (Exception $e) {
			log_message('error', 'Stock report error: ' . $e->getMessage());
			show_error('Error loading stock report: ' . $e->getMessage(), 500);
		}
	}

	//Daily Reconciliation Report
	public function daily_reconciliation(){
		$this->permission_check('stock_report');
		$data=$this->data;
		$data['page_title']='Daily Reconciliation Report';
		$this->load->view('report-daily-reconciliation', $data);
	}
	public function show_daily_reconciliation_report(){
		echo $this->reports->show_daily_reconciliation_report();
	}
	/*Stock Report*/
	public function show_stock_report(){
		return $this->reports->show_stock_report();
	}
	public function brand_wise_stock(){
		return $this->reports->brand_wise_stock();
	}
	public function get_stock_aging_report(){
		return $this->reports->get_stock_aging_report();
	}
	public function get_most_moving_report(){
		return $this->reports->get_most_moving_report();
	}
	public function get_less_moving_report(){
		return $this->reports->get_less_moving_report();
	}
	public function get_low_stock_alert_report(){
		return $this->reports->get_low_stock_alert_report();
	}
	
	// Test method for debugging low stock alert
	public function test_low_stock_alert(){
		try {
			log_message('debug', 'Test Low Stock Alert - Method called');
			$result = $this->reports->get_low_stock_alert_report();
			log_message('debug', 'Test Low Stock Alert - Result: ' . $result);
			echo $result;
		} catch (Exception $e) {
			log_message('error', 'Test Low Stock Alert Error: ' . $e->getMessage());
			echo "Error: " . $e->getMessage();
		}
	}
	
	// Test method for debugging show stock report
	public function test_show_stock_report(){
		try {
			log_message('debug', 'Test Show Stock Report - Method called');
			
			// Set up POST data for the method
			$_POST['store_id'] = 2;
			$_POST['brand_id'] = '';
			$_POST['category_id'] = '';
			$_POST['warehouse_id'] = '';
			$_POST['from_date'] = '';
			$_POST['to_date'] = '';
			
			$result = $this->reports->show_stock_report();
			log_message('debug', 'Test Show Stock Report - Result: ' . $result);
			echo $result;
		} catch (Exception $e) {
			log_message('error', 'Test Show Stock Report Error: ' . $e->getMessage());
			echo "Error: " . $e->getMessage();
		}
	}
	public function get_stock_report(){
		// Ensure pure JSON response (no HTML, no stray output)
		while (ob_get_level()) { ob_end_clean(); }
		$fallback = array(
			'item_wise_report' => '<tr><td class="text-center text-danger" colspan="14">Error loading report. Please try again.</td></tr>',
			'brand_wise_stock' => '<tr><td class="text-center text-danger" colspan="4">Error loading report. Please try again.</td></tr>',
			'stock_aging_report' => '<tr><td class="text-center text-danger" colspan="9">Error loading report. Please try again.</td></tr>',
			'most_moving_report' => '<tr><td class="text-center text-danger" colspan="9">Error loading report. Please try again.</td></tr>',
			'less_moving_report' => '<tr><td class="text-center text-danger" colspan="9">Error loading report. Please try again.</td></tr>',
			'low_stock_alert_report' => '<tr><td class="text-center text-danger" colspan="10">Error loading report. Please try again.</td></tr>'
		);
		if (!$this->permissions('stock_report')) {
			$this->output->set_content_type('application/json', 'UTF-8')->set_output(json_encode($fallback));
			return;
		}
		try {
			log_message('debug', 'get_stock_report method called');
			$item_wise_report = $this->reports->show_stock_report();
			$brand_wise_stock = $this->reports->brand_wise_stock();
			$stock_aging_report = $this->reports->get_stock_aging_report();
			$most_moving_report = $this->reports->get_most_moving_report();
			$less_moving_report = $this->reports->get_less_moving_report();
			$low_stock_alert_report = $this->reports->get_low_stock_alert_report();
			$data = array(
				'item_wise_report' => $item_wise_report,
				'brand_wise_stock' => $brand_wise_stock,
				'stock_aging_report' => $stock_aging_report,
				'most_moving_report' => $most_moving_report,
				'less_moving_report' => $less_moving_report,
				'low_stock_alert_report' => $low_stock_alert_report,
			);
			log_message('debug', 'Stock report data generated successfully');
			$json = json_encode($data);
		} catch (Throwable $e) {
			log_message('error', 'Stock Report Controller Error: ' . $e->getMessage());
			$json = json_encode($fallback);
		}
		$this->output->set_content_type('application/json', 'UTF-8')->set_output($json);
	}

	//Item Sales Report 
	public function item_sales(){
		$this->permission_check('item_sales_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('item_sales_report');
		$this->load->view('report-sales-item', $data);
	}
	public function show_item_sales_report(){
		echo $this->reports->show_item_sales_report();
	}
	//Return Item Report 
	public function return_item(){
		$this->permission_check('return_items_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('return_items_report');
		$this->load->view('report-return-item', $data);
	}
	public function show_return_items_report(){
		echo $this->reports->show_return_items_report();
	}
	
	//Purchase Payments report
	public function purchase_payments(){
		$this->permission_check('purchase_payments_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('purchase_payments_report');
		$this->load->view('report-purchase-payments', $data);
	}
	public function show_purchase_payments_report(){
		echo $this->reports->show_purchase_payments_report();
	}

	//Sales Payments report
	public function sales_payments(){
		$this->permission_check('sales_payments_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('sales_payments_report');
		$this->load->view('report-sales-payments', $data);
	}
	public function show_sales_payments_report(){
		echo $this->reports->show_sales_payments_report();
	}

	//Sales Return Payments report
	public function sales_return_payments(){
		$this->permission_check('sales_return_payments');
		$data=$this->data;
		$data['page_title']=$this->lang->line('sales_return_payments_report');
		$this->load->view('report-sales-return-payments', $data);
	}
	public function show_sales_return_payments_report(){
		echo $this->reports->show_sales_return_payments_report();
	}

	//Expired Items Report 
	public function expired_items(){
		$this->permission_check('expired_items_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('expired_items_report');
		$this->load->view('report-expired-items', $data);
	}
	public function show_expired_items_report(){
		echo $this->reports->show_expired_items_report();
	}
	
	


	//Item Sales Report 
	public function seller_points(){
		$this->permission_check('seller_points_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('seller_points_report');
		$this->load->view('report-seller-points', $data);
	}
	public function show_seller_points_report(){
		echo $this->reports->show_seller_points_report();
	}
	
	//Sales Tax Report 
	public function sales_tax(){
		$this->permission_check('sales_tax_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('sales_tax_report');
		$this->load->view('report-sales-tax', $data);
	}
	public function show_sales_tax_report(){
		echo $this->reports->show_sales_tax_report();
	}

	//purchase Tax Report 
	public function purchase_tax(){
		$this->permission_check('purchase_tax_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('purchase_tax_report');
		$this->load->view('report-purchase-tax', $data);
	}
	public function show_purchase_tax_report(){
		echo $this->reports->show_purchase_tax_report();
	}

	//GSTR-1 Report 
	public function gstr_1(){
		$this->permission_check('gstr_1_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('gstr_1_report');
		$this->load->view('gst/report-gstr-1', $data);
	}
	public function show_gstr_1_report(){
		echo $this->reports->show_gstr_1_report();
	}
	//GSTR-2 Report 
	public function gstr_2(){
		$this->permission_check('gstr_2_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('gstr_2_report');
		$this->load->view('gst/report-gstr-2', $data);
	}
	public function show_gstr_2_report(){
		echo $this->reports->show_gstr_2_report();
	}

	//Customer Sales Item GST Report 
	public function sales_gst_report(){
		$this->permission_check('sales_gst_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('sales_gst_report');
		$this->load->view('gst/report-sales-gst', $data);
	}
	public function show_sales_gst_report(){
		echo $this->reports->show_sales_gst_report();
	}
	//Purchase Item GST Report 
	public function purchase_gst_report(){
		$this->permission_check('purchase_gst_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('purchase_gst_report');
		$this->load->view('gst/report-purchase-gst', $data);
	}
	public function show_purchase_gst_report(){
		echo $this->reports->show_purchase_gst_report();
	}
	
	//Sales Report 
	public function customer_orders(){
		$this->permission_check('customer_orders_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('customer_orders');
		$this->load->view('report-customer-orders', $data);
	}
	public function show_customer_orders(){
		echo $this->reports->show_customer_orders();
	}

	//Delivery sheet report
	public function delivery_sheet(){
		$this->permission_check('delivery_sheet_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('delivery_sheet_report');
		$this->load->view('report-delivery-sheet', $data);
	}
	public function show_delivery_sheet(){
		echo $this->reports->show_delivery_sheet();
	}

	//Load sheet report
	public function load_sheet(){
		$this->permission_check('load_sheet_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('load_sheet_report');
		$this->load->view('report-load-sheet', $data);
	}
	public function show_load_sheet(){
		echo $this->reports->show_load_sheet();
	}
	
	//Sales & payments records 
	public function sales_and_payments(){
		$this->permission_check('sales_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('sales_and_payments_report');
		$this->load->view('report-sales-and-payments', $data);
	}
	public function sales_and_payments_report(){
		echo $this->reports->sales_and_payments_report();
	}

	//Item Sales Report 
	public function stock_transfer(){
		$this->permission_check('stock_transfer_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('stock_transfer_report');
		$this->load->view('report-stock-transfer', $data);
	}
	public function show_stock_transfer_report(){
		echo $this->reports->show_stock_transfer_report();
	}

	// Sales Summary Report 
	public function sales_summary(){
		$this->permission_check('sales_summary_report');
		$data=$this->data;
		$data['page_title']=$this->lang->line('sales_summary_report');
		$this->load->view('report-sales-summary', $data);
	}
	public function show_sales_summary_report(){
		echo $this->reports->show_sales_summary_report();
	}

	// Gross Profit per Sale Report 
	public function gross_profit_per_sale(){
		$this->permission_check('sales_report');
		$data=$this->data;
		$data['page_title']='Gross Profit per Sale Report';
		$this->load->view('report-gross-profit-per-sale', $data);
	}
	public function show_gross_profit_per_sale_report(){
		echo $this->reports->show_gross_profit_per_sale_report();
	}

	// Transaction Details for Currency Blocking
	public function transaction_details(){
		$table = $this->input->get('table');
		$store_id = $this->input->get('store_id') ?: get_current_store_id();
		
		// Validate table name for security
		$allowed_tables = [
			'db_sales', 'db_purchase', 'db_salesreturn', 'db_purchasereturn',
			'db_expense', 'db_quotation', 'db_salespayments', 'db_purchasepayments',
			'db_custadvance', 'ac_transactions'
		];
		
		if (!in_array($table, $allowed_tables)) {
			show_404();
			return;
		}
		
		$data = $this->data;
		$data['page_title'] = 'Transaction Details - ' . ucfirst(str_replace('db_', '', $table));
		$data['table_name'] = $table;
		$data['store_id'] = $store_id;
		
		// Get table data
		$this->db->where('store_id', $store_id);
		$data['records'] = $this->db->get($table)->result();
		$data['record_count'] = count($data['records']);
		
		$this->load->view('report-transaction-details', $data);
	}

	//Customer Statement Report
	public function customer_statement(){
		$this->permission_check('customer_statement_report');
		$this->load->helper('multi_currency_helper');
		$data=$this->data;
		$data['page_title']='Customer Statement Report';
		$data['base_currency'] = get_base_currency();
		$this->load->view('report-customer-statement', $data);
	}
	
	public function get_customer_statement(){
		try {
			$result = $this->reports->get_customer_statement();
			echo json_encode($result);
		} catch (Exception $e) {
			echo json_encode(array('error' => true, 'message' => 'Error: ' . $e->getMessage()));
		}
	}

	//Supplier Statement Report
	public function supplier_statement(){
		$this->permission_check('supplier_statement_report');
		$this->load->helper('multi_currency_helper');
		$data=$this->data;
		$data['page_title']='Supplier Statement Report';
		$data['base_currency'] = get_base_currency();
		$this->load->view('report-supplier-statement', $data);
	}
	
	public function get_supplier_statement(){
		try {
			$result = $this->reports->get_supplier_statement();
			echo json_encode($result);
		} catch (Exception $e) {
			echo json_encode(array('error' => true, 'message' => 'Error: ' . $e->getMessage()));
		}
	}

	// Cash Flow Statement
	public function cash_flow_statement() {
		$data = $this->data;
		$data['page_title'] = 'Cash Flow Statement';
		$this->load->view('report-cash-flow-statement', $data);
	}


	public function get_cash_flow_statement() {
		try {
			$this->load->model('Reports_model');
			$result = $this->Reports_model->get_cash_flow_statement();
			echo json_encode($result);
		} catch (Exception $e) {
			log_message('error', 'Cash Flow Statement Error: ' . $e->getMessage());
			echo json_encode(array('error' => true, 'message' => 'Error generating cash flow statement'));
		}
	}

	// Profit & Loss Statement (Base Currency)
	public function profit_loss_statement() {
		$this->permission_check('sales_report');
		$data = $this->data;
		$data['page_title'] = 'Profit & Loss Statement';
		$this->load->view('report-profit-loss-statement', $data);
	}

	public function get_profit_loss_statement() {
		header('Content-Type: application/json; charset=utf-8');
		try {
			$result = $this->reports->get_profit_loss_statement_base_currency();
			
			// Check if result has error
			if(isset($result['error']) && $result['error']) {
				http_response_code(500);
				echo json_encode($result);
				return;
			}
			
			echo json_encode($result);
		} catch (Exception $e) {
			log_message('error', 'Profit & Loss Statement Error: ' . $e->getMessage());
			log_message('error', 'Profit & Loss Statement Stack Trace: ' . $e->getTraceAsString());
			http_response_code(500);
			echo json_encode(array(
				'error' => true, 
				'message' => 'Error generating profit & loss statement: ' . $e->getMessage(),
				'file' => $e->getFile(),
				'line' => $e->getLine()
			));
		} catch (Error $e) {
			log_message('error', 'Profit & Loss Statement Fatal Error: ' . $e->getMessage());
			log_message('error', 'Profit & Loss Statement Stack Trace: ' . $e->getTraceAsString());
			http_response_code(500);
			echo json_encode(array(
				'error' => true, 
				'message' => 'Fatal error generating profit & loss statement: ' . $e->getMessage(),
				'file' => $e->getFile(),
				'line' => $e->getLine()
			));
		}
	}

	public function export_profit_loss_excel() {
		$this->permission_check('sales_report');
		
		$store_id = $this->input->get('store_id');
		$from_date = $this->input->get('from_date');
		$to_date = $this->input->get('to_date');
		$warehouse_id = $this->input->get('warehouse_id');
		$show_items_sold = $this->input->get('show_items_sold');
		$show_expenses = $this->input->get('show_expenses');
		
		// Set defaults
		if(empty($store_id)){
			$store_id = get_current_store_id();
		}
		if(empty($from_date)){
			$from_date = date('Y-m-01');
		}
		if(empty($to_date)){
			$to_date = date('Y-m-d');
		}
		
		// Get report data
		$_POST['store_id'] = $store_id;
		$_POST['from_date'] = $from_date;
		$_POST['to_date'] = $to_date;
		$_POST['warehouse_id'] = $warehouse_id;
		$_POST['show_items_sold'] = $show_items_sold;
		$_POST['show_expenses'] = $show_expenses;
		
		$data = $this->reports->get_profit_loss_statement_base_currency();
		
		if(isset($data['error']) && $data['error']){
			echo "Error: " . $data['message'];
			return;
		}
		
		// Get store information
		$store = get_store_details($store_id);
		$store_name = get_store_name($store_id);
		if (empty($store_name) || $store_name === true || $store_name === 1) {
			$store_name = isset($store->store_name) ? $store->store_name : 'Company Name';
		}
		$store_address = isset($store->address) ? $store->address : '';
		$store_phone = isset($store->mobile) ? $store->mobile : '';
		$store_email = isset($store->email) ? $store->email : '';
		
		$currency = $data['currency'];
		$symbol = $currency['symbol'] ?: '$';
		$position = $currency['position'] ?: 'before';
		
		// Format currency function
		$format_amount = function($amount, $symbol, $position) {
			$formatted = number_format(abs($amount), 2);
			$sign = $amount < 0 ? '-' : '';
			if($position == 'before'){
				return $sign . $symbol . ' ' . $formatted;
			} else {
				return $sign . $formatted . ' ' . $symbol;
			}
		};
		
		// Set headers for Excel download
		$filename = 'Profit_Loss_Statement_' . date('Y-m-d_His') . '.xls';
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment; filename="' . $filename . '"');
		header('Cache-Control: max-age=0');
		
		// Start Excel output (simple HTML table format for maximum compatibility)
		echo '<table border="1" cellpadding="3" cellspacing="0" style="border-collapse: collapse; font-family: Arial, sans-serif; font-size: 9px;">';
		
		// Get warehouse name
		$warehouse_name = 'All Warehouses';
		if(!empty($warehouse_id)){
			$this->load->model('warehouse_model');
			$warehouse = $this->warehouse_model->get_details($warehouse_id);
			if($warehouse){
				$warehouse_name = $warehouse->warehouse_name;
			}
		}
		
		// Company Header
		echo '<tr><td colspan="3" align="center" style="font-size: 16px; font-weight: bold; padding: 10px;">' . htmlspecialchars($store_name) . '</td></tr>';
		if($store_address) {
			echo '<tr><td colspan="3" align="center" style="font-size: 9px; padding: 2px;">' . htmlspecialchars($store_address) . '</td></tr>';
		}
		if($store_phone || $store_email) {
			$contact_info = array();
			if($store_phone) $contact_info[] = htmlspecialchars($store_phone);
			if($store_email) $contact_info[] = htmlspecialchars($store_email);
			echo '<tr><td colspan="3" align="center" style="font-size: 9px; padding: 2px;">' . implode(' | ', $contact_info) . '</td></tr>';
		}
		echo '<tr><td colspan="3" align="center" style="font-size: 13px; font-weight: bold; padding: 8px; border-top: 2px solid #000;">PROFIT & LOSS STATEMENT</td></tr>';
		echo '<tr><td style="padding: 5px;"><strong>Store:</strong> ' . htmlspecialchars($store_name) . '</td>';
		echo '<td align="right" style="padding: 5px;"><strong>Currency:</strong> ' . htmlspecialchars($currency['code']) . ' (' . htmlspecialchars($symbol) . ')</td>';
		echo '<td></td></tr>';
		echo '<tr><td style="padding: 5px;"><strong>Warehouse:</strong> ' . htmlspecialchars($warehouse_name) . '</td>';
		echo '<td align="right" style="padding: 5px;"><strong>Generated:</strong> ' . date('Y-m-d H:i:s') . '</td>';
		echo '<td></td></tr>';
		echo '<tr><td style="padding: 5px;"><strong>Period:</strong> ' . htmlspecialchars($data['period']['from_date']) . ' to ' . htmlspecialchars($data['period']['to_date']) . '</td>';
		echo '<td></td><td></td></tr>';
		echo '<tr><td colspan="3" style="padding: 5px;"></td></tr>';
		
		// T-FORMAT P&L TABLE (matching printout exactly)
		$net_sales = $data['revenue']['net_sales'];
		$net_cogs = $data['cost_of_goods_sold']['net_cogs'];
		$operating_exp = $data['operating_expenses']['total_operating_expenses'];
		$gross_profit = $data['gross_profit'];
		$net_profit = $data['net_profit'];
		
		// Calculate totals for balancing (Gross Profit is NOT included in balance - it's informational only)
		// T-account balance: Debit = Credit
		$total_debit = $net_cogs + $operating_exp;
		$total_credit = $net_sales;
		
		if($net_profit >= 0){
			$total_credit += $net_profit;
		} else {
			$total_debit += abs($net_profit);
		}
		
		// Round to 2 decimals for accurate balance
		$total_debit = round($total_debit, 2);
		$total_credit = round($total_credit, 2);
		
		// P&L T-Format Section Header
		echo '<tr><td colspan="3" align="center" style="font-size: 11px; font-weight: bold; padding: 4px; background-color: #f0f0f0; border-left: 3px solid #000;">PROFIT & LOSS (T - FORMAT)</td></tr>';
		echo '<tr>';
		echo '<th style="width:50%; background-color: #34495e; color: #fff; padding: 4px; text-align: left; font-size: 9px;">Particulars</th>';
		echo '<th style="width:25%; background-color: #34495e; color: #fff; padding: 4px; text-align: right; font-size: 9px;">Debit</th>';
		echo '<th style="width:25%; background-color: #34495e; color: #fff; padding: 4px; text-align: right; font-size: 9px;">Credit</th>';
		echo '</tr>';
		
		// Net Sales on credit side
		echo '<tr>';
		echo '<td>Net Sales (Sales - Sales Returns)</td>';
		echo '<td align="right"></td>';
		echo '<td align="right">' . $format_amount($net_sales, $symbol, $position) . '</td>';
		echo '</tr>';
		
		// Cost of Goods Sold on debit side
		echo '<tr>';
		echo '<td>Net Cost of Goods Sold</td>';
		echo '<td align="right">' . $format_amount($net_cogs, $symbol, $position) . '</td>';
		echo '<td align="right"></td>';
		echo '</tr>';
		
		// Operating Expenses on debit side
		echo '<tr>';
		echo '<td>Operating Expenses (Expenses + Sales Tax)</td>';
		echo '<td align="right">' . $format_amount($operating_exp, $symbol, $position) . '</td>';
		echo '<td align="right"></td>';
		echo '</tr>';
		
		// Gross Profit (informational only - NOT included in T-account balance)
		echo '<tr style="background-color: #e8f4f8; font-weight: bold;">';
		echo '<td><strong>Gross Profit (Net Sales - Net COGS)</strong></td>';
		echo '<td align="right"></td>';
		echo '<td align="right"><strong>' . $format_amount($gross_profit, $symbol, $position) . '</strong></td>';
		echo '</tr>';
		
		// Net Profit / Loss balancing item (this balances the T-account)
		if($net_profit >= 0){
			echo '<tr style="background-color: #d4edda; font-weight: bold;">';
			echo '<td><strong>Net Profit transferred to Capital</strong></td>';
			echo '<td align="right"></td>';
			echo '<td align="right"><strong>' . $format_amount($net_profit, $symbol, $position) . '</strong></td>';
			echo '</tr>';
		} else {
			echo '<tr style="background-color: #f8d7da; font-weight: bold;">';
			echo '<td><strong>Net Loss transferred to Capital</strong></td>';
			echo '<td align="right"><strong>' . $format_amount(abs($net_profit), $symbol, $position) . '</strong></td>';
			echo '<td align="right"></td>';
			echo '</tr>';
		}
		
		// Total row for balancing - MUST BE EQUAL (Debit = Credit)
		echo '<tr style="background-color: #ffc107; font-weight: bold;">';
		echo '<td><strong>TOTAL</strong></td>';
		echo '<td align="right"><strong>' . $format_amount($total_debit, $symbol, $position) . '</strong></td>';
		echo '<td align="right"><strong>' . $format_amount($total_credit, $symbol, $position) . '</strong></td>';
		echo '</tr>';
		
		echo '<tr><td colspan="3" style="padding: 5px;"></td></tr>';
		
		// ITEMS SOLD DETAILS (if enabled)
		if(!empty($show_items_sold) && isset($data['items_sold']) && is_array($data['items_sold']) && !empty($data['items_sold'])){
			$items_total_sales = 0;
			$items_total_cost = 0;
			$items_total_profit = 0;
			
			// Items Sold Details Section Header
			echo '<tr><td colspan="3" align="center" style="font-size: 11px; font-weight: bold; padding: 4px; background-color: #f0f0f0; border-left: 3px solid #000;">ITEMS SOLD DETAILS</td><td></td><td></td><td></td><td></td></tr>';
			echo '<tr>';
			echo '<th style="background-color: #34495e; color: #fff; padding: 4px; text-align: center; font-size: 9px;">#</th>';
			echo '<th style="background-color: #34495e; color: #fff; padding: 4px; text-align: left; font-size: 9px;">Item Name</th>';
			echo '<th style="background-color: #34495e; color: #fff; padding: 4px; text-align: right; font-size: 9px;">Quantity</th>';
			echo '<th style="background-color: #34495e; color: #fff; padding: 4px; text-align: right; font-size: 9px;">Unit Price</th>';
			echo '<th style="background-color: #34495e; color: #fff; padding: 4px; text-align: right; font-size: 9px;">Total Sales</th>';
			echo '<th style="background-color: #34495e; color: #fff; padding: 4px; text-align: right; font-size: 9px;">Cost</th>';
			echo '<th style="background-color: #34495e; color: #fff; padding: 4px; text-align: right; font-size: 9px;">Profit</th>';
			echo '</tr>';
			foreach ($data['items_sold'] as $index => $item) {
				// Safely access array keys with defaults
				$item_name = isset($item['item_name']) ? $item['item_name'] : (isset($item->item_name) ? $item->item_name : 'N/A');
				$total_qty = isset($item['total_qty']) ? floatval($item['total_qty']) : (isset($item->total_qty) ? floatval($item->total_qty) : 0);
				$avg_price = isset($item['avg_price']) ? floatval($item['avg_price']) : (isset($item->avg_price) ? floatval($item->avg_price) : 0);
				$total_sales = isset($item['total_sales']) ? floatval($item['total_sales']) : (isset($item->total_sales) ? floatval($item->total_sales) : 0);
				$total_cost = isset($item['total_cost']) ? floatval($item['total_cost']) : (isset($item->total_cost) ? floatval($item->total_cost) : 0);
				$item_profit = $total_sales - $total_cost;
				
				$items_total_sales += $total_sales;
				$items_total_cost += $total_cost;
				$items_total_profit += $item_profit;
				
				echo '<tr>';
				echo '<td align="center">' . ($index + 1) . '</td>';
				echo '<td>' . htmlspecialchars($item_name) . '</td>';
				echo '<td align="right">' . number_format($total_qty, 2) . '</td>';
				echo '<td align="right">' . $format_amount($avg_price, $symbol, $position) . '</td>';
				echo '<td align="right">' . $format_amount($total_sales, $symbol, $position) . '</td>';
				echo '<td align="right">' . $format_amount($total_cost, $symbol, $position) . '</td>';
				echo '<td align="right">' . $format_amount($item_profit, $symbol, $position) . '</td>';
				echo '</tr>';
			}
			// Add totals row
			echo '<tr style="background-color: #ffc107; font-weight: bold;">';
			echo '<td colspan="3"><strong>TOTAL</strong></td>';
			echo '<td align="right"></td>';
			echo '<td align="right"><strong>' . $format_amount($items_total_sales, $symbol, $position) . '</strong></td>';
			echo '<td align="right"><strong>' . $format_amount($items_total_cost, $symbol, $position) . '</strong></td>';
			echo '<td align="right"><strong>' . $format_amount($items_total_profit, $symbol, $position) . '</strong></td>';
			echo '</tr>';
			echo '<tr><td colspan="7" style="padding: 5px;"></td></tr>';
		}
		
		// EXPENSE DETAILS (if enabled)
		if(!empty($show_expenses) && isset($data['expenses_list']) && is_array($data['expenses_list']) && !empty($data['expenses_list'])){
			// Expense Details Section Header
			echo '<tr><td colspan="3" align="center" style="font-size: 11px; font-weight: bold; padding: 4px; background-color: #f0f0f0; border-left: 3px solid #000;">EXPENSE DETAILS</td><td></td><td></td></tr>';
			echo '<tr>';
			echo '<th style="background-color: #34495e; color: #fff; padding: 4px; text-align: center; font-size: 9px;">#</th>';
			echo '<th style="background-color: #34495e; color: #fff; padding: 4px; text-align: center; font-size: 9px;">Date</th>';
			echo '<th style="background-color: #34495e; color: #fff; padding: 4px; text-align: left; font-size: 9px;">Category</th>';
			echo '<th style="background-color: #34495e; color: #fff; padding: 4px; text-align: left; font-size: 9px;">Description</th>';
			echo '<th style="background-color: #34495e; color: #fff; padding: 4px; text-align: right; font-size: 9px;">Amount</th>';
			echo '<td></td><td></td>';
			echo '</tr>';
			foreach ($data['expenses_list'] as $index => $expense) {
				// Safely access array keys with defaults
				$expense_date = isset($expense['expense_date']) ? $expense['expense_date'] : (isset($expense->expense_date) ? $expense->expense_date : 'N/A');
				$category_name = isset($expense['category_name']) ? $expense['category_name'] : (isset($expense->category_name) ? $expense->category_name : 'N/A');
				$expense_note = isset($expense['expense_note']) ? $expense['expense_note'] : (isset($expense->expense_note) ? $expense->expense_note : (isset($expense['note']) ? $expense['note'] : (isset($expense->note) ? $expense->note : 'N/A')));
				$expense_amt = isset($expense['expense_amt']) ? floatval($expense['expense_amt']) : (isset($expense->expense_amt) ? floatval($expense->expense_amt) : 0);
				
				echo '<tr>';
				echo '<td align="center">' . ($index + 1) . '</td>';
				echo '<td>' . htmlspecialchars($expense_date) . '</td>';
				echo '<td>' . htmlspecialchars($category_name) . '</td>';
				echo '<td>' . htmlspecialchars($expense_note) . '</td>';
				echo '<td align="right">' . $format_amount($expense_amt, $symbol, $position) . '</td>';
				echo '</tr>';
			}
			echo '<tr><td colspan="5" style="padding: 5px;"></td></tr>';
		}
		
		// CASH SUMMARY
		echo '<tr><td colspan="3" align="center" style="font-size: 11px; font-weight: bold; padding: 4px; background-color: #f0f0f0; border-left: 3px solid #000;">CASH SUMMARY</td><td></td><td></td><td></td><td></td></tr>';
		echo '<tr>';
		echo '<td><strong>Opening Cash Balance</strong></td>';
		echo '<td align="right">' . $format_amount($data['cash']['opening_balance'], $symbol, $position) . '</td>';
		echo '<td></td><td></td><td></td><td></td><td></td>';
		echo '</tr>';
		echo '<tr>';
		echo '<td><strong>Net Cash Flow (Period)</strong></td>';
		echo '<td align="right">' . $format_amount($data['cash']['net_cash_flow'], $symbol, $position) . '</td>';
		echo '<td></td><td></td><td></td><td></td><td></td>';
		echo '</tr>';
		echo '<tr style="background-color: #ffc107; font-weight: bold;">';
		echo '<td><strong>Closing Cash Balance</strong></td>';
		echo '<td align="right"><strong>' . $format_amount($data['cash']['closing_balance'], $symbol, $position) . '</strong></td>';
		echo '<td></td><td></td><td></td><td></td><td></td>';
		echo '</tr>';
		echo '<tr><td colspan="7" style="padding: 5px;"></td></tr>';
		
		// Footer
		echo '<tr><td colspan="7" align="center" style="padding-top: 10px; border-top: 1px solid #000; font-size: 8px;">';
		echo 'Transaction Counts - Sales: ' . $data['details']['sales_count'] . ' | Purchases: ' . $data['details']['purchase_count'] . ' | Expenses: ' . $data['details']['expense_count'];
		echo '</td></tr>';
		
		echo '</table>';
		exit;
	}

	// AJAX endpoint to get sale items for modal display
	public function get_sale_items($sale_id){
		// Check permissions
		if(!$this->permissions('sales_report')){
			echo json_encode(array('error' => true, 'message' => 'Access denied'));
			return;
		}
		
		// Load the model method
		$this->reports->get_sale_items($sale_id);
	}

// ==================== EXPIRY REPORT METHODS ====================

// Expiry Report Page
public function expiry_report() {
    // Use dashboard permission since expiry widgets are on dashboard
    if(!$this->permissions('dashboard_view')){
        $this->show_access_denied_page();
    }
    
    $data = $this->data;
    $data['page_title'] = 'Expiry Report';
    
    // Debug: Check what's in $this->data
    log_message('debug', 'Expiry Report - theme_link: ' . (isset($data['theme_link']) ? $data['theme_link'] : 'NOT SET'));
    log_message('debug', 'Expiry Report - base_url: ' . (isset($data['base_url']) ? $data['base_url'] : 'NOT SET'));
    
    $this->load->view('report-expiry', $data);
}

// Get Expiry Report Data (AJAX)
public function get_expiry_report_data() {
    // Use dashboard permission since expiry widgets are on dashboard
    if(!$this->permissions('dashboard_view')){
        echo json_encode(array('success' => false, 'message' => 'Access denied'));
        return;
    }
    
    $filter_type = $this->input->post('filter_type') ?: 'all';
    $from_date = $this->input->post('from_date');
    $to_date = $this->input->post('to_date');
    
    try {
        $where_conditions = array();
        $where_conditions[] = "i.batch_tracking_enabled = 1";
        $where_conditions[] = "pi.batch_number IS NOT NULL";
        $where_conditions[] = "pi.batch_number != ''";
        $where_conditions[] = "pi.expiry_date IS NOT NULL";
        $where_conditions[] = "pi.expiry_date != '0000-00-00'";
        $where_conditions[] = "pi.status = 1";
        
        // Apply filter conditions
        switch($filter_type) {
            case 'expiring':
                $where_conditions[] = "DATEDIFF(pi.expiry_date, CURDATE()) <= 30";
                $where_conditions[] = "DATEDIFF(pi.expiry_date, CURDATE()) >= 0";
                break;
            case 'expired':
                $where_conditions[] = "DATEDIFF(pi.expiry_date, CURDATE()) < 0";
                break;
            case 'critical':
                $where_conditions[] = "DATEDIFF(pi.expiry_date, CURDATE()) <= 7";
                $where_conditions[] = "DATEDIFF(pi.expiry_date, CURDATE()) >= 0";
                break;
            case 'all':
            default:
                // No additional date restrictions
                break;
        }
        
        // Apply custom date range if provided
        if($from_date) {
            $from_date_formatted = date('Y-m-d', strtotime(str_replace('-', '/', $from_date)));
            $where_conditions[] = "pi.expiry_date >= '$from_date_formatted'";
        }
        
        if($to_date) {
            $to_date_formatted = date('Y-m-d', strtotime(str_replace('-', '/', $to_date)));
            $where_conditions[] = "pi.expiry_date <= '$to_date_formatted'";
        }
        
        // Get store_id filter
        $store_id = $this->input->post('store_id') ?: get_current_store_id();
        if(!is_admin() && !is_store_admin()){
            $store_id = get_current_store_id();
        }
        
        // Use db_item_batches as the authoritative source (accounts for sales, returns, adjustments)
        $where_conditions_batches = array();
        $where_conditions_batches[] = "i.batch_tracking_enabled = 1";
        $where_conditions_batches[] = "ib.batch_number IS NOT NULL";
        $where_conditions_batches[] = "ib.batch_number != ''";
        $where_conditions_batches[] = "ib.expiry_date IS NOT NULL";
        $where_conditions_batches[] = "ib.expiry_date != '0000-00-00'";
        $where_conditions_batches[] = "ib.status = 1";
        $where_conditions_batches[] = "ib.remaining_quantity > 0";
        
        if(!empty($store_id)){
            $where_conditions_batches[] = "ib.store_id = " . (int)$store_id;
        }
        
        // Apply filter conditions
        switch($filter_type) {
            case 'expiring':
                $where_conditions_batches[] = "DATEDIFF(ib.expiry_date, CURDATE()) <= 30";
                $where_conditions_batches[] = "DATEDIFF(ib.expiry_date, CURDATE()) >= 0";
                break;
            case 'expired':
                $where_conditions_batches[] = "DATEDIFF(ib.expiry_date, CURDATE()) < 0";
                break;
            case 'critical':
                $where_conditions_batches[] = "DATEDIFF(ib.expiry_date, CURDATE()) <= 7";
                $where_conditions_batches[] = "DATEDIFF(ib.expiry_date, CURDATE()) >= 0";
                break;
            case 'all':
            default:
                // No additional date restrictions
                break;
        }
        
        // Apply custom date range if provided
        if($from_date) {
            $from_date_formatted = date('Y-m-d', strtotime(str_replace('-', '/', $from_date)));
            $where_conditions_batches[] = "ib.expiry_date >= '$from_date_formatted'";
        }
        
        if($to_date) {
            $to_date_formatted = date('Y-m-d', strtotime(str_replace('-', '/', $to_date)));
            $where_conditions_batches[] = "ib.expiry_date <= '$to_date_formatted'";
        }
        
        $where_clause_batches = implode(' AND ', $where_conditions_batches);
        
        $query = $this->db->query("
            SELECT 
                ib.item_id,
                i.item_name,
                i.item_code,
                ib.batch_number,
                ib.manufacturing_date,
                ib.expiry_date,
                ib.quantity as purchase_qty,
                (ib.quantity - ib.remaining_quantity) as sold_qty,
                ib.remaining_quantity,
                s.store_name,
                DATEDIFF(ib.expiry_date, CURDATE()) as days_until_expiry
            FROM db_item_batches ib
            JOIN db_items i ON ib.item_id = i.id
            LEFT JOIN db_store s ON s.id = ib.store_id
            WHERE $where_clause_batches
            ORDER BY ib.expiry_date ASC
        ");
        
        $items = $query->result();
        
        // Format data for frontend
        $formatted_items = array();
        foreach ($items as $item) {
            $formatted_items[] = array(
                'item_id' => $item->item_id,
                'item_name' => $item->item_name,
                'item_code' => $item->item_code,
                'batch_number' => $item->batch_number,
                'expiry_date' => $item->expiry_date,
                'manufacturing_date' => $item->manufacturing_date,
                'remaining_quantity' => $item->remaining_quantity,
                'days_until_expiry' => $item->days_until_expiry,
                'store_name' => $item->store_name
            );
        }
        
        echo json_encode(array(
            'success' => true,
            'data' => $formatted_items
        ));
        
    } catch (Exception $e) {
        echo json_encode(array(
            'success' => false,
            'message' => 'Error loading expiry report: ' . $e->getMessage()
        ));
    }
}

// Export Expiry Report to Excel
public function export_expiry_excel() {
    // Use dashboard permission since expiry widgets are on dashboard
    if(!$this->permissions('dashboard_view')){
        $this->show_access_denied_page();
    }
    
    $filter_type = $this->input->get('filter_type') ?: 'all';
    $from_date = $this->input->get('from_date');
    $to_date = $this->input->get('to_date');
    
    // Set headers for Excel download
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="expiry_report_' . date('Y-m-d') . '.xls"');
    
    echo '<table border="1">';
    echo '<tr>';
    echo '<th>Item Name</th>';
    echo '<th>Item Code</th>';
    echo '<th>Batch Number</th>';
    echo '<th>Manufacturing Date</th>';
    echo '<th>Expiry Date</th>';
    echo '<th>Days to Expiry</th>';
    echo '<th>Available Stock</th>';
    echo '<th>Status</th>';
    echo '<th>Store</th>';
    echo '</tr>';
    
    // Get data using the same logic as get_expiry_report_data
    $where_conditions = array();
    $where_conditions[] = "i.batch_tracking_enabled = 1";
    $where_conditions[] = "pi.batch_number IS NOT NULL";
    $where_conditions[] = "pi.batch_number != ''";
    $where_conditions[] = "pi.expiry_date IS NOT NULL";
    $where_conditions[] = "pi.expiry_date != '0000-00-00'";
    $where_conditions[] = "pi.status = 1";
    
    switch($filter_type) {
        case 'expiring':
            $where_conditions[] = "DATEDIFF(pi.expiry_date, CURDATE()) <= 30";
            $where_conditions[] = "DATEDIFF(pi.expiry_date, CURDATE()) >= 0";
            break;
        case 'expired':
            $where_conditions[] = "DATEDIFF(pi.expiry_date, CURDATE()) < 0";
            break;
        case 'critical':
            $where_conditions[] = "DATEDIFF(pi.expiry_date, CURDATE()) <= 7";
            $where_conditions[] = "DATEDIFF(pi.expiry_date, CURDATE()) >= 0";
            break;
    }
    
    if($from_date) {
        $from_date_formatted = date('Y-m-d', strtotime(str_replace('-', '/', $from_date)));
        $where_conditions[] = "pi.expiry_date >= '$from_date_formatted'";
    }
    
    if($to_date) {
        $to_date_formatted = date('Y-m-d', strtotime(str_replace('-', '/', $to_date)));
        $where_conditions[] = "pi.expiry_date <= '$to_date_formatted'";
    }
    
    $where_clause = implode(' AND ', $where_conditions);
    
    $query = $this->db->query("
        SELECT 
            pi.item_id,
            i.item_name,
            i.item_code,
            pi.batch_number,
            pi.manufacturing_date,
            pi.expiry_date,
            pi.purchase_qty,
            COALESCE(SUM(si.sales_qty), 0) as sold_qty,
            (pi.purchase_qty - COALESCE(SUM(si.sales_qty), 0)) as remaining_quantity,
            s.store_name,
            DATEDIFF(pi.expiry_date, CURDATE()) as days_until_expiry
        FROM db_purchaseitems pi
        JOIN db_items i ON pi.item_id = i.id
        JOIN db_purchase p ON pi.purchase_id = p.id
        LEFT JOIN db_salesitems si ON pi.item_id = si.item_id 
            AND pi.batch_number = si.batch_number 
            AND si.status = 1
        LEFT JOIN db_store s ON s.id = pi.store_id
        WHERE $where_clause
        GROUP BY pi.item_id, pi.batch_number, pi.manufacturing_date, pi.expiry_date, pi.purchase_qty, s.store_name
        HAVING remaining_quantity > 0
        ORDER BY pi.expiry_date ASC
    ");
    
    foreach ($query->result() as $item) {
        $status = '';
        if ($item->days_until_expiry < 0) {
            $status = 'Expired';
        } elseif ($item->days_until_expiry <= 7) {
            $status = 'Critical';
        } elseif ($item->days_until_expiry <= 15) {
            $status = 'Soon';
        } elseif ($item->days_until_expiry <= 30) {
            $status = 'Normal';
        } else {
            $status = 'Good';
        }
        
        echo '<tr>';
        echo '<td>' . $item->item_name . '</td>';
        echo '<td>' . ($item->item_code ?: 'N/A') . '</td>';
        echo '<td>' . ($item->batch_number ?: 'N/A') . '</td>';
        echo '<td>' . ($item->manufacturing_date && $item->manufacturing_date != '0000-00-00' ? date('d-m-Y', strtotime($item->manufacturing_date)) : 'N/A') . '</td>';
        echo '<td>' . ($item->expiry_date ? date('d-m-Y', strtotime($item->expiry_date)) : 'N/A') . '</td>';
        echo '<td>' . $item->days_until_expiry . '</td>';
        echo '<td>' . $item->remaining_quantity . '</td>';
        echo '<td>' . $status . '</td>';
        echo '<td>' . ($item->store_name ?: 'N/A') . '</td>';
        echo '</tr>';
    }
    
    echo '</table>';
}

// Export Expiry Report to PDF
public function export_expiry_pdf() {
    // Use dashboard permission since expiry widgets are on dashboard
    if(!$this->permissions('dashboard_view')){
        $this->show_access_denied_page();
    }
    
    // For now, redirect to Excel export
    // In a full implementation, you would use a PDF library like TCPDF or mPDF
    $this->export_expiry_excel();
}

// Export Stock Report to Excel
public function export_stock_excel() {
    if(!$this->permissions('stock_report')){
        $this->show_access_denied_page();
    }
    
    $store_id = $this->input->get('store_id');
    $warehouse_id = $this->input->get('warehouse_id');
    $brand_id = $this->input->get('brand_id');
    $category_id = $this->input->get('category_id');
    $from_date = $this->input->get('from_date');
    $to_date = $this->input->get('to_date');
    
    // Set headers for Excel download
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment; filename="stock_report_' . date('Y-m-d') . '.xls"');
    
    // Get store information
    $store = get_store_details();
    $store_name = get_store_name();
    if (empty($store_name) || $store_name === true || $store_name === 1) {
        $store_name = isset($store->store_name) ? $store->store_name : 'NCS Billing Book';
    }
    
    // Load helper
    $this->load->helper('multi_currency_helper');
    
    // Get base currency info
    $base_currency = get_base_currency($store_id ?: get_current_store_id());
    $base_currency_symbol = $base_currency ? $base_currency->symbol : 'USh';
    $base_currency_position = $base_currency ? $base_currency->currency_position : 'before';
    
    // Start Excel output with styling
    echo '<html xmlns:x="urn:schemas-microsoft-com:office:excel">';
    echo '<head>';
    echo '<meta http-equiv="Content-Type" content="text/html; charset=utf-8">';
    echo '<style>';
    echo 'table { border-collapse: collapse; width: 100%; font-family: Arial, sans-serif; }';
    echo 'th { background-color: #3c8dbc; color: white; font-weight: bold; text-align: center; padding: 8px; border: 1px solid #000; }';
    echo 'td { padding: 6px; border: 1px solid #000; }';
    echo '.text-left { text-align: left; }';
    echo '.text-right { text-align: right; }';
    echo '.text-center { text-align: center; }';
    echo '.header-row { background-color: #f0f0f0; font-weight: bold; }';
    echo '.total-row { background-color: #d0e8f0; font-weight: bold; }';
    echo '</style>';
    echo '</head>';
    echo '<body>';
    
    // Report Header
    echo '<table border="1" style="width:100%; margin-bottom:20px;">';
    echo '<tr><td colspan="14" style="text-align:center; font-size:16px; font-weight:bold; padding:15px; background-color:#3c8dbc; color:white;">STOCK REPORT - ITEM WISE</td></tr>';
    echo '<tr class="header-row"><td colspan="7" style="text-align:left; padding:8px;"><strong>Company:</strong> ' . htmlspecialchars($store_name) . '</td>';
    echo '<td colspan="7" style="text-align:right; padding:8px;"><strong>Generated:</strong> ' . date('d-m-Y H:i:s') . '</td></tr>';
    
    if($store_id) {
        $store_info = $this->db->get_where('db_store', array('id' => $store_id))->row();
        echo '<tr class="header-row"><td colspan="14" style="padding:5px;"><strong>Store:</strong> ' . ($store_info ? htmlspecialchars($store_info->store_name) : 'All Stores') . '</td></tr>';
    }
    if($warehouse_id) {
        $warehouse_info = $this->db->get_where('db_warehouse', array('id' => $warehouse_id))->row();
        echo '<tr class="header-row"><td colspan="14" style="padding:5px;"><strong>Warehouse:</strong> ' . ($warehouse_info ? htmlspecialchars($warehouse_info->warehouse_name) : 'All Warehouses') . '</td></tr>';
    }
    if($brand_id) {
        $brand_info = $this->db->get_where('db_brands', array('id' => $brand_id))->row();
        echo '<tr class="header-row"><td colspan="14" style="padding:5px;"><strong>Brand:</strong> ' . ($brand_info ? htmlspecialchars($brand_info->brand_name) : 'All Brands') . '</td></tr>';
    }
    if($category_id) {
        $category_info = $this->db->get_where('db_category', array('id' => $category_id))->row();
        echo '<tr class="header-row"><td colspan="14" style="padding:5px;"><strong>Category:</strong> ' . ($category_info ? htmlspecialchars($category_info->category_name) : 'All Categories') . '</td></tr>';
    }
    echo '</table>';
    
    // Table Headers
    echo '<table border="1">';
    echo '<tr>';
    echo '<th>#</th>';
    if(store_module() && is_admin()){
        echo '<th>Store Name</th>';
    }
    echo '<th>Item Code</th>';
    echo '<th>Item Name</th>';
    echo '<th>Brand</th>';
    echo '<th>Category</th>';
    echo '<th>Unit Price</th>';
    echo '<th>Tax</th>';
    echo '<th>Purchase Cost</th>';
    echo '<th>Sales Price</th>';
    echo '<th>Main Unit</th>';
    echo '<th>Alternate Unit</th>';
    echo '<th>Stock Value<br>(By Sale Price)</th>';
    echo '<th>Stock Value<br>(By Purchase Price)</th>';
    echo '</tr>';
    
    // Get data using same logic as show_stock_report
    if(empty($store_id)) {
        $store_id = get_current_store_id();
    }
    
    if(!empty($store_id)){
        $this->db->where("a.store_id",$store_id);
    }
    if(!is_admin()){
        $this->db->where("a.store_id",get_current_store_id());
    }
    $this->db->select("a.sales_price,a.item_code,a.price,a.purchase_price,a.item_name,a.tax_type,a.store_id,a.id as item_id,a.item_group,
        d.category_name,
        c.brand_name,
        a.conversion_factor,
        ");
    $this->db->select("b.tax_name");
    $this->db->select("u1.unit_name as main_unit_name");
    $this->db->select("u2.unit_name as alternate_unit_name");
    $this->db->from("db_items as a");
    $this->db->from("db_tax as b");
    $this->db->where("b.id=a.tax_id");
    $this->db->where("a.service_bit=0");
    $this->db->join("db_brands as c","c.id=a.brand_id","left");
    $this->db->join("db_category as d","d.id=a.category_id","left");
    $this->db->join("db_units as u1","u1.id=a.unit_id","left");
    $this->db->join("db_units as u2","u2.id=a.alternate_unit_id","left");
    
    if(!empty($brand_id)){
        $this->db->where("a.brand_id",$brand_id);
    }
    if(!empty($category_id)){
        $this->db->where("a.category_id",$category_id);
    }
    
    $q1=$this->db->get();
    
    $i=0;
    $tot_stock = 0;
    $tot_stock_alt = 0; // Total alternate unit quantity
    $tot_value = 0;
    $tot_stock_value_by_purchase_price = 0;
    
    foreach ($q1->result() as $res1) {
        if($res1->item_group=='Variants'){continue;}
        
        $available_qty_wh = $this->reports->get_available_qty($warehouse_id,$res1->store_id,$res1->item_id);
        
        // Calculate multi-unit quantities
        $qty_main = $available_qty_wh;
        $qty_alt = 0;
        $conversion_factor = !empty($res1->conversion_factor) ? floatval($res1->conversion_factor) : 0;
        
        if($conversion_factor > 0 && $qty_main > 0){
            $qty_alt = $qty_main * $conversion_factor;
        }
        
        $value = $available_qty_wh * $res1->sales_price;
        $stock_value_by_purchase_price = $available_qty_wh * $res1->purchase_price;
        
        $tax_type = ($res1->tax_type=='Inclusive') ? 'Inc.' : 'Exc.';
        
        // Format currency
        $unit_price_formatted = $base_currency_position == 'before' ? $base_currency_symbol . ' ' . number_format($res1->price, 2) : number_format($res1->price, 2) . ' ' . $base_currency_symbol;
        $purchase_price_formatted = $base_currency_position == 'before' ? $base_currency_symbol . ' ' . number_format($res1->purchase_price, 2) : number_format($res1->purchase_price, 2) . ' ' . $base_currency_symbol;
        $sales_price_formatted = $base_currency_position == 'before' ? $base_currency_symbol . ' ' . number_format($res1->sales_price, 2) : number_format($res1->sales_price, 2) . ' ' . $base_currency_symbol;
        $value_formatted = $base_currency_position == 'before' ? $base_currency_symbol . ' ' . number_format($value, 2) : number_format($value, 2) . ' ' . $base_currency_symbol;
        $stock_value_formatted = $base_currency_position == 'before' ? $base_currency_symbol . ' ' . number_format($stock_value_by_purchase_price, 2) : number_format($stock_value_by_purchase_price, 2) . ' ' . $base_currency_symbol;
        
        // Format quantities
        $main_unit_display = number_format($qty_main, 2);
        if(!empty($res1->main_unit_name)){
            $main_unit_display .= ' ' . strtolower($res1->main_unit_name);
        }
        
        $alt_unit_display = '-';
        if($conversion_factor > 0 && !empty($res1->alternate_unit_name)){
            $alt_unit_display = number_format($qty_alt, 2) . ' ' . strtolower($res1->alternate_unit_name);
        }
        
        echo '<tr>';
        echo '<td class="text-center">' . (++$i) . '</td>';
        if(store_module() && is_admin()){
            $store_name_display = $this->reports->get_store_name($res1->store_id);
            echo '<td class="text-left">' . htmlspecialchars($store_name_display) . '</td>';
        }
        echo '<td class="text-left">' . htmlspecialchars($res1->item_code) . '</td>';
        echo '<td class="text-left">' . htmlspecialchars($res1->item_name) . '</td>';
        echo '<td class="text-left">' . htmlspecialchars($res1->brand_name ?: '-') . '</td>';
        echo '<td class="text-left">' . htmlspecialchars($res1->category_name ?: '-') . '</td>';
        echo '<td class="text-right">' . $unit_price_formatted . '</td>';
        echo '<td class="text-center">' . htmlspecialchars($res1->tax_name ?: 'None') . '[' . $tax_type . ']</td>';
        echo '<td class="text-right">' . $purchase_price_formatted . '</td>';
        echo '<td class="text-right">' . $sales_price_formatted . '</td>';
        echo '<td class="text-center">' . $main_unit_display . '</td>';
        echo '<td class="text-center">' . $alt_unit_display . '</td>';
        echo '<td class="text-right">' . $value_formatted . '</td>';
        echo '<td class="text-right">' . $stock_value_formatted . '</td>';
        echo '</tr>';
        
        $tot_stock += $available_qty_wh;
        $tot_stock_alt += $qty_alt; // Sum alternate unit quantities
        $tot_value += $value;
        $tot_stock_value_by_purchase_price += $stock_value_by_purchase_price;
    }
    
    // Calculate colspan for "Total" label (from # to Sales Price)
    $total_label_colspan = 9; // Base columns before Sales Price
    if(store_module() && is_admin()){
        $total_label_colspan = 10; // Add Store column
    }
    
    // Format totals
    $tot_stock_formatted = number_format($tot_stock, 2);
    $tot_alt_formatted = number_format($tot_stock_alt, 2);
    $tot_value_formatted = $base_currency_position == 'before' ? $base_currency_symbol . ' ' . number_format($tot_value, 2) : number_format($tot_value, 2) . ' ' . $base_currency_symbol;
    $tot_stock_value_formatted = $base_currency_position == 'before' ? $base_currency_symbol . ' ' . number_format($tot_stock_value_by_purchase_price, 2) : number_format($tot_stock_value_by_purchase_price, 2) . ' ' . $base_currency_symbol;
    
    echo '<tr class="total-row">';
    echo '<td class="text-right" colspan="' . $total_label_colspan . '" style="font-weight:bold;">Total:</td>';
    echo '<td class="text-center" style="font-weight:bold;">' . $tot_stock_formatted . '</td>';
    echo '<td class="text-center" style="font-weight:bold;">' . $tot_alt_formatted . '</td>';
    echo '<td class="text-right" style="font-weight:bold;">' . $tot_value_formatted . '</td>';
    echo '<td class="text-right" style="font-weight:bold;">' . $tot_stock_value_formatted . '</td>';
    echo '</tr>';
    
    echo '</table>';
    echo '</body>';
    echo '</html>';
}

}

