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

class Batches_model extends CI_Model {
	
	public function get_batches_list(){
		extract($_POST);
		
		// Handle store_id - check POST first, then use current store
		$store_id = isset($store_id) && !empty($store_id) ? $store_id : get_current_store_id();
		
		$warehouse_id_filter = isset($warehouse_id) ? $warehouse_id : '';
		$item_id_filter = isset($item_id) ? $item_id : '';
		$batch_number_filter = isset($batch_number) ? $batch_number : '';
		
		// Build query - use db_item_batches as primary source
		$this->db->select("
			ib.id,
			ib.item_id,
			i.item_name,
			i.item_code,
			ib.batch_number,
			ib.manufacturing_date,
			ib.expiry_date,
			ib.quantity as initial_quantity,
			ib.remaining_quantity,
			(ib.quantity - ib.remaining_quantity) as sold_quantity,
			ib.purchase_price,
			ib.store_id,
			ib.warehouse_id,
			w.warehouse_name,
			ib.created_date,
			ib.status,
			CASE 
				WHEN ib.expiry_date IS NOT NULL AND ib.expiry_date < CURDATE() THEN 'Expired'
				WHEN ib.expiry_date IS NOT NULL AND ib.expiry_date <= DATE_ADD(CURDATE(), INTERVAL 7 DAY) THEN 'Expiring Soon'
				WHEN ib.remaining_quantity = 0 THEN 'Sold Out'
				ELSE 'Active'
			END as batch_status
		");
		$this->db->from("db_item_batches ib");
		$this->db->join("db_items i", "i.id = ib.item_id", "left");
		$this->db->join("db_warehouse w", "w.id = ib.warehouse_id", "left");
		
		// Only filter by batch_number if it's not empty (allow NULL batches if needed)
		if(!empty($batch_number_filter)){
			$this->db->where("ib.batch_number IS NOT NULL");
			$this->db->where("ib.batch_number != ''");
		} else {
			// Still require batch_number to be set for valid batches
			$this->db->where("(ib.batch_number IS NOT NULL AND ib.batch_number != '')");
		}
		
		// Filter by status - allow status = 1 or NULL (in case some batches don't have status set)
		$this->db->where("(ib.status = 1 OR ib.status IS NULL)");
		
		// Store filter - only apply if not admin or if store is explicitly selected
		if(!is_admin() || !empty($store_id)){
			$this->db->where("ib.store_id", $store_id);
		}
		
		if(!empty($warehouse_id_filter)){
			$this->db->where("ib.warehouse_id", $warehouse_id_filter);
		}
		
		if(!empty($item_id_filter)){
			$this->db->where("ib.item_id", $item_id_filter);
		}
		
		if(!empty($batch_number_filter)){
			$this->db->like("ib.batch_number", $batch_number_filter);
		}
		
		// Order by expiry date (expiring soon first), then by batch number
		$this->db->order_by("ib.expiry_date", "ASC");
		$this->db->order_by("ib.batch_number", "ASC");
		
		$query = $this->db->get();
		
		$html = "";
		if($query->num_rows() > 0){
			$i = 1;
			foreach($query->result() as $row){
				$status_class = '';
				$status_icon = '';
				if($row->batch_status == 'Expired'){
					$status_class = 'danger';
					$status_icon = 'fa-exclamation-circle';
				} elseif($row->batch_status == 'Expiring Soon'){
					$status_class = 'warning';
					$status_icon = 'fa-exclamation-triangle';
				} elseif($row->batch_status == 'Sold Out'){
					$status_class = 'info';
					$status_icon = 'fa-check-circle';
				} else {
					$status_class = 'success';
					$status_icon = 'fa-check';
				}
				
				$expiry_date_display = !empty($row->expiry_date) ? show_date($row->expiry_date) : 'N/A';
				$manufacturing_date_display = !empty($row->manufacturing_date) ? show_date($row->manufacturing_date) : 'N/A';
				
				$html .= "<tr>";
				$html .= "<td>".$i++."</td>";
				$html .= "<td>".$row->item_code."</td>";
				$html .= "<td>".$row->item_name."</td>";
				$html .= "<td><strong>".$row->batch_number."</strong></td>";
				$html .= "<td>".$manufacturing_date_display."</td>";
				$html .= "<td>".$expiry_date_display."</td>";
				$html .= "<td class='text-right'>".number_format($row->initial_quantity, 2)."</td>";
				$html .= "<td class='text-right'>".number_format($row->sold_quantity, 2)."</td>";
				$html .= "<td class='text-right'><strong>".number_format($row->remaining_quantity, 2)."</strong></td>";
				$html .= "<td>".(!empty($row->warehouse_name) ? $row->warehouse_name : 'N/A')."</td>";
				$html .= "<td><span class='label label-".$status_class."'><i class='fa ".$status_icon."'></i> ".$row->batch_status."</span></td>";
				$html .= "<td>".show_date($row->created_date)."</td>";
				$html .= "</tr>";
			}
		} else {
			$html .= "<tr><td colspan='12' class='text-center text-danger'>No batches found</td></tr>";
		}
		
		return $html;
	}
}
