<?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);
		}
	}
	/*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(){
		try {
			log_message('debug', 'get_stock_report method called');
			
			// Call model methods directly
			$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');
			echo json_encode($data); 
		} catch (Exception $e) {
			log_message('error', 'Stock Report Controller Error: ' . $e->getMessage());
			echo json_encode(array(
				'item_wise_report' => '<tr><td class="text-center text-danger" colspan="11">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>'
			));
		}
	}

	//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');
		$data=$this->data;
		$data['page_title']='Customer Statement Report';
		$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');
		$data=$this->data;
		$data['page_title']='Supplier Statement Report';
		$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'));
		}
	}

	// 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'";
        }
        
        $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
        ");
        
        $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();
}

}

