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

class Stock_adjustment_model extends CI_Model {
	//Datatable start
	var $table = 'db_stockadjustment as a';
	var $column_order = array( 
								'a.id',
								'a.adjustment_date',
								'a.reference_no',
								'a.created_by',
								'a.store_id'
								); //set column field database for datatable orderable
	var $column_search = array( 
								'a.id',
								'a.adjustment_date',
								'a.reference_no',
								'a.created_by',
								'a.store_id'
								); //set column field database for datatable searchable 
	var $order = array('a.id' => 'desc'); // default order 

	public function __construct()
	{
		parent::__construct();
	}

	private function _get_datatables_query()
	{
		$this->db->select($this->column_order);
		$this->db->from($this->table);
		
		/*If warehouse selected*/
		$warehouse_id = $this->input->post('warehouse_id');
		if(!empty($warehouse_id)){
			$this->db->join('db_warehouse as w','w.id='.$warehouse_id,'left');
			$this->db->where('a.warehouse_id',$warehouse_id);
		}

		
		//if not admin
	    /*if(!is_admin()){*/
	      $this->db->where("a.store_id",get_current_store_id());
	    /*}*/
		$i = 0;
	
		foreach ($this->column_search as $item) // loop column 
		{
			if($_POST['search']['value']) // if datatable send POST for search
			{
				
				

				if($i===0) // first loop
				{
					$this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.

					$this->db->like($item, $_POST['search']['value']);

				}
				else
				{
					$this->db->or_like($item, $_POST['search']['value']);
				}

				


				if(count($this->column_search) - 1 == $i) //last loop
					$this->db->group_end(); //close bracket
			}
			$i++;
		}
		
		if(isset($_POST['order'])) // here order processing
		{
			$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
		} 
		else if(isset($this->order))
		{
			$order = $this->order;
			$this->db->order_by(key($order), $order[key($order)]);
		}
	}

	function get_datatables()
	{
		$this->_get_datatables_query();
		if($_POST['length'] != -1)
		$this->db->limit($_POST['length'], $_POST['start']);
		$query = $this->db->get();
		return $query->result();
	}

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

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

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

	//Save Cutomers
	public function verify_save_and_update(){
		//Filtering XSS and html escape from user inputs 
		extract($this->xss_html_filter(array_merge($this->data,$_POST,$_GET)));
		//echo "<pre>";print_r($this->xss_html_filter(array_merge($this->data,$_POST,$_GET)));exit();
		
		$this->db->trans_begin();
		$adjustment_date=system_fromatted_date($adjustment_date);

	    $prev_item_ids = array();
	    
	    $store_id = (store_module() && is_admin()) ? $store_id : get_current_store_id();  
	    $warehouse_id=(warehouse_module() && warehouse_count()>1) ? $warehouse_id : get_store_warehouse_id(); 	
	    if($command=='save'){//Create purchase code unique if first time entry
		    
			$this->db->query("ALTER TABLE db_stockadjustment AUTO_INCREMENT = 1");
			
		    $purchase_entry = array(
		    				'store_id' 				=> $store_id, 
		    				'warehouse_id' 				=> $warehouse_id, 
		    				'reference_no' 				=> $reference_no, 
		    				'adjustment_date' 			=> $adjustment_date,
		    				'adjustment_note' 			=> $adjustment_note,
		    				/*System Info*/
		    				'created_date' 				=> $CUR_DATE,
		    				'created_time' 				=> $CUR_TIME,
		    				'created_by' 				=> $CUR_USERNAME,
		    				'system_ip' 				=> $SYSTEM_IP,
		    				'system_name' 				=> $SYSTEM_NAME,
		    				'status' 					=> 1,
		    			);
		    
		     	
			$q1 = $this->db->insert('db_stockadjustment', $purchase_entry);
			$adjustment_id = $this->db->insert_id();
		}
		else if($command=='update'){	
			$purchase_entry = array(
		    				'store_id' 				=> $store_id, 
		    				'warehouse_id' 				=> $warehouse_id, 
		    				'reference_no' 				=> $reference_no, 
		    				'adjustment_date' 			=> $adjustment_date,
		    				'adjustment_note' 			=> $adjustment_note,
		    			);
			
			
			$q1 = $this->db->where('id',$adjustment_id)->update('db_stockadjustment', $purchase_entry);

			##############################################START
			//FIND THE PREVIOUSE ITEM LIST ID'S
			$prev_item_ids = $this->db->select("item_id")->from("db_stockadjustmentitems")->where("adjustment_id",$adjustment_id)->get()->result_array();
			##############################################END

			$q11=$this->db->query("delete from db_stockadjustmentitems where adjustment_id='$adjustment_id'");
			if(!$q11){
				return "failed";
			}
		}
		//end

		

		//Import post data from form
		for($i=1;$i<=$rowcount;$i++){
		
			if(isset($_REQUEST['tr_item_id_'.$i]) && !empty($_REQUEST['tr_item_id_'.$i])){

				$item_id 			=$this->xss_html_filter(trim($_REQUEST['tr_item_id_'.$i]));
				$adjustment_qty		=$this->xss_html_filter(trim($_REQUEST['td_data_'.$i.'_3']));
				$description		=$this->xss_html_filter(trim($_REQUEST['description_'.$i]));

				// SERVER-SIDE VALIDATION: Check if batch details are required (for positive adjustments)
				if($adjustment_qty > 0){
					// Check if item has batch tracking enabled
					$item_info = $this->db->select('batch_tracking_enabled, item_name')->from('db_items')->where('id', $item_id)->get()->row();
					
					if($item_info && $item_info->batch_tracking_enabled == 1){
						// Batch tracking is enabled - batch details are REQUIRED
						$batch_number = isset($_REQUEST['batch_number_'.$i]) ? trim($this->xss_html_filter($_REQUEST['batch_number_'.$i])) : '';
						
						if(empty($batch_number)){
							// Batch number is missing - BLOCK SAVE
							$this->db->trans_rollback();
							$item_name = !empty($item_info->item_name) ? $item_info->item_name : 'Item ID ' . $item_id;
							return "error:Batch details are required for '{$item_name}'. This item has batch tracking enabled. Please add batch number and details before saving.";
						}
					}
				}

				$adjustment_entry = array(
							'store_id' 				=> $store_id, 
		    				'warehouse_id' 				=> $warehouse_id, 
		    				'adjustment_id' 		=> $adjustment_id,
		    				'item_id' 			=> $item_id,
		    				'adjustment_qty' 		=> $adjustment_qty,
		    				'description' 		=> $description, 
		    				'status'			=> 1,
		    			);
				
				$q2 = $this->db->insert('db_stockadjustmentitems', $adjustment_entry);
				
				// Handle batch details if batch tracking is enabled and adjustment is positive (adding stock)
				if($adjustment_qty > 0){
					// Check if item has batch tracking enabled
					$item_info = $this->db->select('batch_tracking_enabled')->from('db_items')->where('id', $item_id)->get()->row();
					
					if($item_info && $item_info->batch_tracking_enabled == 1){
						$batch_number = isset($_REQUEST['batch_number_'.$i]) ? $this->xss_html_filter(trim($_REQUEST['batch_number_'.$i])) : '';
						$manufacturing_date = isset($_REQUEST['manufacturing_date_'.$i]) ? $this->xss_html_filter(trim($_REQUEST['manufacturing_date_'.$i])) : '';
						$expiry_date = isset($_REQUEST['expiry_date_'.$i]) ? $this->xss_html_filter(trim($_REQUEST['expiry_date_'.$i])) : '';
						$batch_qty = isset($_REQUEST['batch_qty_'.$i]) ? $this->xss_html_filter(trim($_REQUEST['batch_qty_'.$i])) : $adjustment_qty;
						$batch_remarks = isset($_REQUEST['batch_remarks_'.$i]) ? $this->xss_html_filter(trim($_REQUEST['batch_remarks_'.$i])) : '';
						
						// If batch number is provided, save batch details
						if(!empty($batch_number) && $batch_qty > 0){
							$this->load->model('items_model');
							$this->items_model->add_batch_details_for_opening_stock(
								$item_id, 
								array($batch_number), 
								array($manufacturing_date), 
								array($expiry_date), 
								array($batch_qty), 
								array($batch_remarks), 
								$store_id, 
								$warehouse_id
							);
						}
					}
				}
				
				//UPDATE itemS QUANTITY IN itemS TABLE
				$this->load->model('pos_model');				
				$q6=$this->pos_model->update_items_quantity($item_id);
				if(!$q6){
					return "failed";
				}

				
			}
		
		}//for end

	
		##############################################START
		//FIND THE PREVIOUSE ITEM LIST ID'S
		$curr_item_ids = $this->db->select("item_id")->from("db_stockadjustmentitems")->where("adjustment_id",$adjustment_id)->get()->result_array();
		$two_array = array_merge($prev_item_ids,$curr_item_ids);

		/*Update items in all warehouses of the item*/
		$q7=update_warehouse_items($two_array);
		if(!$q7){
			return "failed";
		}
		##############################################END
		
		$this->db->trans_commit();
		$this->session->set_flashdata('success', 'Success!! Record Saved Successfully!');
		return "success<<<###>>>$adjustment_id";
		
	}//verify_save_and_update() function end




	public function delete_stock_adjustment($ids){
      	$this->db->trans_begin();

      	try {
      		##############################################START
			// Get adjustment details and items BEFORE deletion
			$adjustment_query = $this->db->select("id, adjustment_date, store_id, warehouse_id")
				->from("db_stockadjustment")
				->where("id in ($ids)");
			if(!is_admin()){
				$adjustment_query->where("store_id", get_current_store_id());
			}
			$adjustments = $adjustment_query->get()->result();
			
			if(empty($adjustments)){
				$this->db->trans_rollback();
				return "failed";
			}
			
			// Get all items from the adjustments
			$adjustment_items_query = $this->db->select("item_id, adjustment_qty")
				->from("db_stockadjustmentitems")
				->where("adjustment_id in ($ids)");
			if(!is_admin()){
				$adjustment_items_query->where("store_id", get_current_store_id());
			}
			$adjustment_items = $adjustment_items_query->get()->result();
			
			// Collect batches that might have been created from these adjustments
			// Group adjustments by store/warehouse/date to handle multiple adjustments
			$batches_to_check = array();
			$adjustment_groups = array();
			
			// Group items by their adjustment details
			foreach($adjustments as $adj){
				$key = $adj->store_id . '_' . (!empty($adj->warehouse_id) ? $adj->warehouse_id : 'null') . '_' . $adj->adjustment_date;
				if(!isset($adjustment_groups[$key])){
					$adjustment_groups[$key] = array(
						'store_id' => $adj->store_id,
						'warehouse_id' => $adj->warehouse_id,
						'date' => $adj->adjustment_date,
						'item_ids' => array()
					);
				}
			}
			
			// Get items for each adjustment and group them
			$adjustment_ids_arr = array();
			foreach($adjustments as $adj){
				$adjustment_ids_arr[] = $adj->id;
			}
			$adjustment_ids_str = implode(',', $adjustment_ids_arr);
			
			$all_items = $this->db->select("sa.id as adjustment_id, sa.store_id, sa.warehouse_id, sa.adjustment_date, sai.item_id, sai.adjustment_qty")
				->from("db_stockadjustmentitems sai")
				->join("db_stockadjustment sa", "sa.id = sai.adjustment_id", "inner")
				->where("sai.adjustment_id in ($adjustment_ids_str)")
				->where("sai.adjustment_qty >", 0) // Only positive adjustments create batches
				->get()->result();
			
			// For each item with positive adjustment, find batches created on that adjustment date
			foreach($all_items as $adj_item){
				// Find batches for this item created on the adjustment date
				// Match by: item_id, store_id, warehouse_id, created_date
				$batches_query = $this->db->select("ib.id, ib.item_id, ib.batch_number, ib.quantity, ib.remaining_quantity")
					->from("db_item_batches ib")
					->where("ib.item_id", $adj_item->item_id)
					->where("ib.store_id", $adj_item->store_id)
					->where("ib.created_date", $adj_item->adjustment_date)
					->where("ib.status", 1);
				
				if(!empty($adj_item->warehouse_id)){
					$batches_query->where("ib.warehouse_id", $adj_item->warehouse_id);
				}
				
				$item_batches = $batches_query->get()->result();
				
				foreach($item_batches as $batch){
					// Avoid duplicates
					$batch_key = $batch->id;
					if(!isset($batches_to_check[$batch_key])){
						$batches_to_check[$batch_key] = $batch;
					}
				}
			}
			
			// OPTIMIZED: Check all batches for sales in a single query (much faster)
			$batches_with_sales = array();
			$safe_to_delete_batch_ids = array();
			
			if(!empty($batches_to_check)){
				// Build WHERE conditions for batch check
				$batch_conditions = array();
				$batch_info_map = array(); // Map to get batch info later
				
				foreach($batches_to_check as $batch_key => $batch){
					$batch_conditions[] = "(si.item_id = " . (int)$batch->item_id . " AND si.batch_number = " . $this->db->escape($batch->batch_number) . ")";
					$batch_info_map[$batch->item_id . '_' . $batch->batch_number] = $batch;
				}
				
				if(!empty($batch_conditions)){
					// Single query to check all batches at once
					$sales_query = "
						SELECT 
							si.item_id, 
							si.batch_number,
							SUM(si.sales_qty) as total_sold
						FROM db_salesitems si
						WHERE si.status = 1
							AND si.batch_number IS NOT NULL 
							AND si.batch_number != ''
							AND (" . implode(' OR ', $batch_conditions) . ")
						GROUP BY si.item_id, si.batch_number
						HAVING total_sold > 0
					";
					
					$batches_with_sales_result = $this->db->query($sales_query)->result();
					
					// Mark batches that have been sold
					$sold_batch_keys = array();
					foreach($batches_with_sales_result as $sold_batch){
						$key = $sold_batch->item_id . '_' . $sold_batch->batch_number;
						$sold_batch_keys[] = $key;
						
						$batch = $batch_info_map[$key];
						$item_info = $this->db->select("item_name")->from("db_items")->where("id", $batch->item_id)->get()->row();
						$item_name = !empty($item_info) ? $item_info->item_name : 'Item ID ' . $batch->item_id;
						
						$batches_with_sales[] = array(
							'item_id' => $batch->item_id,
							'item_name' => $item_name,
							'batch_number' => $batch->batch_number,
							'sold_qty' => floatval($sold_batch->total_sold),
							'remaining_qty' => $batch->remaining_quantity
						);
					}
					
					// Mark safe batches (not in sold list)
					foreach($batches_to_check as $batch_key => $batch){
						$batch_map_key = $batch->item_id . '_' . $batch->batch_number;
						if(!in_array($batch_map_key, $sold_batch_keys)){
							// This batch hasn't been sold - safe to delete
							$safe_to_delete_batch_ids[] = $batch->id;
						}
					}
				} else {
					// No batches to check, mark all as safe to delete
					foreach($batches_to_check as $batch_key => $batch){
						$safe_to_delete_batch_ids[] = $batch->id;
					}
				}
			}
			
			// If there are batches with sales, prevent deletion of the entire adjustment
			if(!empty($batches_with_sales)){
				$this->db->trans_rollback();
				// Use JSON-safe error message format
				$error_msg = "Cannot delete stock adjustment: The following batches have been used in sales transactions:\\n\\n";
				foreach($batches_with_sales as $batch_sale){
					$error_msg .= "• {$batch_sale['item_name']} - Batch: {$batch_sale['batch_number']} (Sold: {$batch_sale['sold_qty']}, Remaining: {$batch_sale['remaining_qty']})\\n";
				}
				$error_msg .= "\\nPlease delete the sales transactions first or the batches cannot be removed.";
				return $error_msg;
			}
			
			// Delete safe batches (batches that haven't been sold)
			if(!empty($safe_to_delete_batch_ids)){
				$batch_ids_str = implode(',', array_map('intval', $safe_to_delete_batch_ids));
				$this->db->where("id in ($batch_ids_str)");
				$batch_delete_result = $this->db->delete("db_item_batches");
				log_message('info', "Stock Adjustment Delete: Deleted " . count($safe_to_delete_batch_ids) . " unused batches from db_item_batches");
			}
			##############################################END

			##############################################START
			//FIND THE PREVIOUSE ITEM LIST ID'S (for warehouse update)
			$prev_item_ids = $this->db->select("item_id")->from("db_stockadjustmentitems")->where("adjustment_id in ($ids)")->get()->result_array();
			##############################################END

			#----------------------------------
			$this->db->where("id in ($ids)");
			//if not admin
			if(!is_admin()){
				$this->db->where("store_id",get_current_store_id());
			}

			$q3=$this->db->delete("db_stockadjustment");
			#----------------------------------
			#----------------------------------
			$this->db->where("adjustment_id in ($ids)");
			//if not admin
			if(!is_admin()){
				$this->db->where("store_id",get_current_store_id());
			}

			$q7=$this->db->delete("db_stockadjustmentitems");
			#----------------------------------

			// OPTIMIZED: Only update quantities for affected items (not all items!)
			if(!empty($prev_item_ids)){
				// Get unique item IDs
				$unique_item_ids = array();
				foreach($prev_item_ids as $item_row){
					if(isset($item_row['item_id']) && !empty($item_row['item_id'])){
						$unique_item_ids[] = (int)$item_row['item_id'];
					}
				}
				$unique_item_ids = array_unique($unique_item_ids);
				
				if(!empty($unique_item_ids)){
					$this->load->model('pos_model');
					foreach($unique_item_ids as $item_id) {
						$q8 = $this->pos_model->update_items_quantity($item_id);
						if(!$q8){
							throw new Exception("Failed to update items quantity for item ID: " . $item_id);
						}
					}
				}
			}

			##############################################START
			/*Update items in all warehouses of the item*/
			if(!empty($prev_item_ids)){
				$q7=update_warehouse_items($prev_item_ids);
				if(!$q7){
					throw new Exception("Failed to update warehouse items");
				}
			}
			##############################################END
			
			
			if($q3!=1)
			{
				$this->db->trans_rollback();
				return "failed";
			}
			else{
				$this->db->trans_commit();
				return "success";
			}
			
		} catch (Exception $e) {
			$this->db->trans_rollback();
			log_message('error', 'Stock Adjustment Delete Error: ' . $e->getMessage());
			return "failed: " . $e->getMessage();
		}
	}
	
	
	
	public function get_items_info($rowcount,$item_id){
		$res1=$this->db->select('*')->from('db_items')->where("id=$item_id")->get()->row();
		
		$this->load->model('pos_model');
$current_stock = $this->pos_model->get_item_current_stock($item_id);
		
		$info = array(
							'item_id' 					=> $res1->id, 
							'description' 				=> $res1->description, 
							'item_name' 				=> $res1->item_name,
							'item_adjustment_qty' 		=> 1, 
							'service_bit' 				=> $res1->service_bit,
							'batch_tracking_enabled' 	=> isset($res1->batch_tracking_enabled) ? $res1->batch_tracking_enabled : 0,
							'tracking_type' 			=> isset($res1->tracking_type) ? $res1->tracking_type : 'none',
							'current_stock' => $current_stock,
						);

		$this->return_row_with_data($rowcount,$info);
	}

	/* For Stock_adjustment Items List Retrieve*/
	public function return_stock_adjustment_list($adjustment_id){
		$q1=$this->db->select('*')->from('db_stockadjustmentitems')->where("adjustment_id=$adjustment_id")->get();
		$rowcount =1;
		foreach ($q1->result() as $res1) {
			$res2=$this->db->query("select * from db_items where id=".$res1->item_id)->row();
			
			$info = array(
							'item_id' 					=> $res1->item_id, 
							'description' 				=> $res1->description, 
							'item_name' 				=> $res2->item_name,
							'item_adjustment_qty' 		=> $res1->adjustment_qty, 
							'service_bit' 				=> $res2->service_bit,
							'batch_tracking_enabled' 	=> isset($res2->batch_tracking_enabled) ? $res2->batch_tracking_enabled : 0,
							'tracking_type' 			=> isset($res2->tracking_type) ? $res2->tracking_type : 'none',
						);

			$result = $this->return_row_with_data($rowcount++,$info);
		}
		return $result;
	}

	public function return_row_with_data($rowcount,$info){
		extract($info);
		
		
	
		?>
            <tr id="row_<?=$rowcount;?>" data-row='<?=$rowcount;?>'>
               <td id="td_<?=$rowcount;?>_1">
                  <label class='form-control' style='height:auto;' data-toggle="tooltip" title='Edit ?' >
                  <a id="td_data_<?=$rowcount;?>_1" href="javascript:void()" onclick="show_purchase_item_modal(<?=$rowcount;?>)" title=""><?=$item_name;?></a> 
                  <br>
<small class="text-muted">
    Current Stock:
    <b><?=isset($current_stock) ? format_qty($current_stock) : 0;?></b>
</small>
                  		<i onclick="show_purchase_item_modal(<?=$rowcount;?>)" class="fa fa-edit pointer"></i>
                  		<?php if(isset($batch_tracking_enabled) && $batch_tracking_enabled == 1): ?>
                  		<i class="fa fa-cubes text-info pointer" onclick="show_stock_adjustment_batch_modal(<?=$item_id;?>,<?=$rowcount;?>)" title="Add Batch Details" style="margin-left: 5px;"></i>
                  		<?php endif; ?>
                  	</label>
               </td>
               <!-- Qty -->
               <td id="td_<?=$rowcount;?>_3">
                  <div class="input-group ">
                     <span class="input-group-btn">
                     <button onclick="decrement_qty(<?=$rowcount;?>)" type="button" class="btn btn-default btn-flat"><i class="fa fa-minus text-danger"></i></button></span>
                     <input typ="text" value="<?=format_qty($item_adjustment_qty);?>" class="form-control no-padding text-center" onkeyup="final_total()" id="td_data_<?=$rowcount;?>_3" name="td_data_<?=$rowcount;?>_3">
                     <span class="input-group-btn">
                     <button onclick="increment_qty(<?=$rowcount;?>)" type="button" class="btn btn-default btn-flat"><i class="fa fa-plus text-success"></i></button></span>
                  </div>
               </td>
              

               <!-- ADD button -->
               <td id="td_<?=$rowcount;?>_16" style="text-align: center;">
                  <a class=" fa fa-fw fa-minus-square text-red" style="cursor: pointer;font-size: 34px;" onclick="removerow(<?=$rowcount;?>)" title="Delete ?" name="td_data_<?=$rowcount;?>_16" id="td_data_<?=$rowcount;?>_16"></a>
               </td>
               
               <input type="hidden" id="tr_item_id_<?=$rowcount;?>" name="tr_item_id_<?=$rowcount;?>" value="<?=$item_id;?>" data-batch-tracking-enabled="<?=isset($batch_tracking_enabled) ? $batch_tracking_enabled : 0;?>">

               <input type="hidden" id="description_<?=$rowcount;?>" name="description_<?=$rowcount;?>" value="<?=$description;?>">
               <input type="hidden" id="service_bit_<?=$rowcount;?>" name="service_bit_<?=$rowcount;?>" value="<?=$service_bit;?>">
               
               <!-- Batch details hidden fields -->
               <input type="hidden" id="batch_number_<?=$rowcount;?>" name="batch_number_<?=$rowcount;?>" value="">
               <input type="hidden" id="manufacturing_date_<?=$rowcount;?>" name="manufacturing_date_<?=$rowcount;?>" value="">
               <input type="hidden" id="expiry_date_<?=$rowcount;?>" name="expiry_date_<?=$rowcount;?>" value="">
               <input type="hidden" id="batch_qty_<?=$rowcount;?>" name="batch_qty_<?=$rowcount;?>" value="">
               <input type="hidden" id="batch_remarks_<?=$rowcount;?>" name="batch_remarks_<?=$rowcount;?>" value="">
               
            </tr>
		<?php

	}

}