<?php

class IngredientStockAdjustment {

	/*************************************************
	/ This function add new Ingredient Stock Adjustment
	*************************************************/
	
	public static function AddIngredientStockAdjustment($data) {
		global $db;
				
		$id = $db->query_insert(TABLE_INGREDIENT_STOCK_ADJUSTMENT, $data);
		if($db->affected_rows > 0){
			return $id;
		}else{
			return false;
		}
	
	} // AddIngredientStockAdjustment


	/*************************************************
	/ This function update Ingredient Stock Adjustment
	*************************************************/
	
	public static function UpdateIngredientStockAdjustment($pid,$data) {
		global $db;
				
		$id = $db->query_update(TABLE_INGREDIENT_STOCK_ADJUSTMENT,$data,"id='$pid'");
		
		if($db->affected_rows > 0){
			return $id;
		}else{
			return $id;
		}
	
	} // UpdateIngredientStockAdjustment


	/*************************************************
	/ Get Ingredient Stock Adjustment By Id
	*************************************************/
	public static function GetIngredientStockAdjustmentById($id) {
	
		global $db;
		$query = "SELECT * from ".TABLE_INGREDIENT_STOCK_ADJUSTMENT." WHERE id ='$id'";
		$rows = $db->query_first($query);
	
        if($db->affected_rows > 0){
            return $rows;
        }else{
            return false;
        }
	}//GetIngredientStockAdjustmentById


	/*************************************************
	/ Get Ingredient Stock Adjustment By Code
	*************************************************/
	public static function GetIngredientStockAdjustmentByCode($pcode) {
	
		global $db;
		$query = "SELECT * from ".TABLE_INGREDIENT_STOCK_ADJUSTMENT." WHERE code LIKE '$pcode%'";
		$rows = $db->query_first($query);
	
        if($db->affected_rows > 0){
            return $rows;
        }else{
            return false;
        }
	}//GetIngredientStockAdjustmentByCode


	/**********************************************************
	/ This function will return IngredientStockAdjustment list
	**********************************************************/
	public static function GetIngredientStockAdjustmentList($filter="",$pageno="null",$pagesize="null") {
	
		global $db;
		$query = "SELECT * from ".TABLE_INGREDIENT_STOCK_ADJUSTMENT." ". $filter;
		$rows = $db->fetch_all_array($query,$pageno,$pagesize);
	
		if($db->affected_rows > 0){
            return $rows;
        }else{
            return false;
        }
	}//GetIngredientStockAdjustmentList


	/*************************************************
	/ Generate Code ISA-000001
	*************************************************/
	public static function GenerateIngredientStockAdjustmentCode($id) {
		$code = "ISA-".str_pad((int)$id, 6, "0", STR_PAD_LEFT);
		return $code;
	}//GenerateIngredientStockAdjustmentCode


	/*************************************************
	/ Get purchase list for ingredient where remaining > 0
	/ (Uses ingredient_stock as source of truth)
	*************************************************/
	public static function GetPurchaseListForIngredientAdjustment($ingredient_id) {
	
		global $db;
		$ingredient_id = (int)$ingredient_id;

		// NOTE: change supplier_setup table name if different in your DB
		$query = "
			SELECT
				s.purchase_id,
				p.code AS purchase_code,
				p.create_date AS purchase_date,
				p.purchase_invoice_no,
				sup.name AS supplier_name,
				SUM(s.remaining_qty) AS remaining_qty
			FROM ".TABLE_INGREDIENT_STOCK." s
			INNER JOIN purchase p ON p.id = s.purchase_id
			LEFT JOIN supplier_setup sup ON sup.id = p.supplier_id
			WHERE s.ingredient_id = '$ingredient_id'
			GROUP BY s.purchase_id
			HAVING SUM(s.remaining_qty) > 0
			ORDER BY p.create_date ASC, s.purchase_id ASC
		";

		$rows = $db->fetch_all_array($query);

		if($db->affected_rows > 0){
			return $rows;
		}else{
			return false;
		}
	}//GetPurchaseListForIngredientAdjustment


	/*************************************************
	/ Get purchase detail panel for ingredient + purchase
	*************************************************/
	public static function GetPurchaseDetailForIngredientAdjustment($ingredient_id, $purchase_id) {
	
		global $db;
		$ingredient_id = (int)$ingredient_id;
		$purchase_id = (int)$purchase_id;

		// Totals from ingredient_stock
		$qTotals = "
			SELECT
				COALESCE(SUM(reserved_qty),0) AS used_qty,
				COALESCE(SUM(remaining_qty),0) AS remaining_qty
			FROM ".TABLE_INGREDIENT_STOCK."
			WHERE ingredient_id = '$ingredient_id'
			  AND purchase_id   = '$purchase_id'
		";
		$totals = $db->query_first($qTotals);

		// Purchased qty + purchase info from purchase_items + purchase
		// NOTE: change supplier_setup table name if different
		$qMeta = "
			SELECT
				p.create_date AS purchase_date,
				p.code AS purchase_code,
				p.purchase_invoice_no,
				sup.name AS supplier_name,
				pi.qty AS purchased_qty
			FROM purchase_items pi
			INNER JOIN purchase p ON p.id = pi.purchase_id
			LEFT JOIN supplier_setup sup ON sup.id = p.supplier_id
			WHERE pi.purchase_id = '$purchase_id'
			  AND pi.ing_id      = '$ingredient_id'
			LIMIT 1
		";
		$meta = $db->query_first($qMeta);

		if($db->affected_rows > 0 && $meta){
			$used_qty = (isset($totals->used_qty)) ? (float)$totals->used_qty : 0;
			$remaining_qty = (isset($totals->remaining_qty)) ? (float)$totals->remaining_qty : 0;
			$purchased_qty = (float)$meta->purchased_qty;

			$consumed_qty = $purchased_qty - $remaining_qty;
			if($consumed_qty < 0) $consumed_qty = 0;

			// Return as array for JSON helper
			return array(
				'purchase_date' => $meta->purchase_date,
				'purchase_code' => $meta->purchase_code,
				'purchase_invoice_no' => $meta->purchase_invoice_no,
				'supplier_name' => $meta->supplier_name,
				'purchased_qty' => $purchased_qty,
				'used_qty' => $used_qty,
				'remaining_qty' => $remaining_qty,
				'consumed_qty' => $consumed_qty
			);
		}else{
			return false;
		}

	}//GetPurchaseDetailForIngredientAdjustment


	/*************************************************
	/ POST Deduct-only adjustment:
	/ - FIFO deduct from ingredient_stock rows (batch_date ASC)
	/ - Updates: reserved_qty += qty, remaining_qty -= qty
	/ - Insert log row, update code ISA-000001
	*************************************************/
	public static function PostIngredientStockDeductAdjustment($ingredient_id, $purchase_id, $deduct_qty, $adjustment_date_db, $reason, $created_by) {
	
		global $db;

		$ingredient_id = (int)$ingredient_id;
		$purchase_id   = (int)$purchase_id;
		$deduct_qty    = (float)$deduct_qty;
		$created_by    = (int)$created_by;

		if($ingredient_id <= 0 || $purchase_id <= 0 || $deduct_qty <= 0){
			return false;
		}

		$reason = addslashes(trim($reason));
		$now = date('Y-m-d H:i:s');

		$db->query("START TRANSACTION");

		// Lock lots FIFO
		$qLots = "
			SELECT id, remaining_qty, reserved_qty, batch_date
			FROM ".TABLE_INGREDIENT_STOCK."
			WHERE ingredient_id = '$ingredient_id'
			  AND purchase_id   = '$purchase_id'
			ORDER BY batch_date ASC, id ASC
			FOR UPDATE
		";
		$lots = $db->fetch_all_array($qLots);

		if(!$lots){
			$db->query("ROLLBACK");
			return false;
		}

		$before_remaining = 0;
		$before_used = 0;
		foreach($lots as $l){
			$before_remaining += (float)$l['remaining_qty'];
			$before_used      += (float)$l['reserved_qty'];
		}

		if($deduct_qty > $before_remaining){
			$db->query("ROLLBACK");
			return false;
		}

		$left = $deduct_qty;

		foreach($lots as $lot){
			if($left <= 0) break;

			$lot_id = (int)$lot['id'];
			$rem = (float)$lot['remaining_qty'];
			$used = (float)$lot['reserved_qty'];

			if($rem <= 0) continue;

			$take = ($rem >= $left) ? $left : $rem;

			$new_rem  = $rem - $take;
			$new_used = $used + $take;

			$status = ($new_rem <= 0) ? 'Used' : 'Available';

			$updateData = array(
				'reserved_qty'  => $new_used,
				'remaining_qty' => $new_rem,
				'available_qty' => $new_rem,
				'status'        => $status,
				'last_updated'  => $now
			);

			$db->query_update(TABLE_INGREDIENT_STOCK, $updateData, "id='$lot_id'");

			$left -= $take;
		}

		if($left > 0){
			$db->query("ROLLBACK");
			return false;
		}

		// After totals
		$qAfter = "
			SELECT
				COALESCE(SUM(reserved_qty),0) AS used_qty,
				COALESCE(SUM(remaining_qty),0) AS remaining_qty
			FROM ".TABLE_INGREDIENT_STOCK."
			WHERE ingredient_id = '$ingredient_id'
			  AND purchase_id   = '$purchase_id'
		";
		$after = $db->query_first($qAfter);

		$after_used = (isset($after->used_qty)) ? (float)$after->used_qty : 0;
		$after_remaining = (isset($after->remaining_qty)) ? (float)$after->remaining_qty : 0;

		// Insert log
		$log = array(
			'code' => 'TEMP',
			'adjustment_date' => $adjustment_date_db,
			'ingredient_id' => $ingredient_id,
			'purchase_id' => $purchase_id,
			'deduct_qty' => $deduct_qty,
			'before_used_qty' => $before_used,
			'after_used_qty' => $after_used,
			'before_remaining_qty' => $before_remaining,
			'after_remaining_qty' => $after_remaining,
			'reason' => $reason,
			'created_by' => $created_by,
			'create_date' => $now,
			'datetime' => $now,
			'status' => 'Posted'
		);

		$adj_id = self::AddIngredientStockAdjustment($log);

		if(!$adj_id){
			$db->query("ROLLBACK");
			return false;
		}

		// Update ISA code
		$code = self::GenerateIngredientStockAdjustmentCode($adj_id);
		$db->query("UPDATE ".TABLE_INGREDIENT_STOCK_ADJUSTMENT." SET code='$code' WHERE id='".(int)$adj_id."'");

		$db->query("COMMIT");
		return $adj_id;

	}//PostIngredientStockDeductAdjustment
  
	public static function GetNumRows() { global $db; return $db->GetNumRows(); }
	public static function GetNumberOfPages() { global $db; return $db->GetNumberOfPages(); }

}

?>
