<?php
class IngredientStorage {
	private $pdo; private $col='change_qty'; private static $checked=false; private $usageDateCol='apply_date'; private $appliedQtyCol='applied_qty'; private $hasRolledBack=true; private $hasNote=true; private static $hasNoteStatic=true; private $aggregateUnique=false; private static $aggregateUniqueStatic=false; private $hasLocation=true;
	public function __construct(){ $this->pdo=$GLOBALS['pdo']; $this->initSchema(); }
	private function initSchema(){
		if(self::$checked){ $this->hasNote = self::$hasNoteStatic; return; }
		self::$checked=true;
		try {
			$cols=$this->pdo->query("SHOW COLUMNS FROM ingredient_storage")->fetchAll(PDO::FETCH_COLUMN);
			$this->hasLocation = in_array('location',$cols,true);
			if(!in_array('change_qty',$cols,true) && in_array('quantity',$cols,true)){
				try { $this->pdo->exec("ALTER TABLE ingredient_storage CHANGE quantity change_qty DECIMAL(12,3) NOT NULL"); $cols[]='change_qty'; } catch(Exception $e){ $this->col='quantity'; }
			}
			$this->col = in_array('change_qty',$cols,true)?'change_qty':(in_array('quantity',$cols,true)?'quantity':'change_qty');
			self::$hasNoteStatic = in_array('note',$cols,true);
			$this->hasNote = self::$hasNoteStatic;
			// Detect if there is a unique aggregate key on (ingredient_id, location)
			try {
				$idx = $this->pdo->query("SHOW INDEX FROM ingredient_storage")->fetchAll(PDO::FETCH_ASSOC);
				$grouped=[]; foreach($idx as $r){ $grouped[$r['Key_name']][]=$r; }
				foreach($grouped as $k=>$rows){
					if($rows[0]['Non_unique']==0 && count($rows)>=2){
						$colsIn=array_column($rows,'Column_name'); sort($colsIn); if($colsIn===['ingredient_id','location']){ self::$aggregateUniqueStatic=true; break; }
					}
				}
			} catch(Exception $e){}
			$this->aggregateUnique = self::$aggregateUniqueStatic;
		} catch(Exception $e){ $this->hasNote=true; }
		// usage applied columns
		try { $u=$this->pdo->query("SHOW COLUMNS FROM ingredient_usage_applied")->fetchAll(PDO::FETCH_COLUMN); if(in_array('usage_date',$u,true) && !in_array('apply_date',$u,true)) $this->usageDateCol='usage_date'; if(in_array('quantity',$u,true) && !in_array('applied_qty',$u,true)) $this->appliedQtyCol='quantity'; $this->hasRolledBack=in_array('rolled_back',$u,true); } catch(Exception $e){}
	}

	public function add($ingredientId, $qty, $reason='manual', $note=null, $location='MAIN', $refDate=null){
		$colName=$this->col;
		if($this->aggregateUnique){
			// Upsert accumulate mode
			if($this->hasNote){
				$sql="INSERT INTO ingredient_storage (ingredient_id, location, $colName, reason, note, ref_date) VALUES (?,?,?,?,?,?) ON DUPLICATE KEY UPDATE $colName=$colName+VALUES($colName), reason=VALUES(reason), note=VALUES(note), ref_date=IFNULL(VALUES(ref_date),ref_date)";
				$st=$this->pdo->prepare($sql); return $st->execute([$ingredientId,$location,$qty,$reason,$note,$refDate]);
			} else {
				$sql="INSERT INTO ingredient_storage (ingredient_id, location, $colName, reason, ref_date) VALUES (?,?,?,?,?) ON DUPLICATE KEY UPDATE $colName=$colName+VALUES($colName), reason=VALUES(reason), ref_date=IFNULL(VALUES(ref_date),ref_date)";
				$st=$this->pdo->prepare($sql); return $st->execute([$ingredientId,$location,$qty,$reason,$refDate]);
			}
		} else {
			// Ledger mode (multiple rows allowed)
			if($this->hasNote){
				$st = $this->pdo->prepare("INSERT INTO ingredient_storage (ingredient_id, location, $colName, reason, note, ref_date) VALUES (?,?,?,?,?,?)");
				return $st->execute([$ingredientId, $location, $qty, $reason, $note, $refDate]);
			} else {
				$st = $this->pdo->prepare("INSERT INTO ingredient_storage (ingredient_id, location, $colName, reason, ref_date) VALUES (?,?,?,?,?)");
				return $st->execute([$ingredientId, $location, $qty, $reason, $refDate]);
			}
		}
	}

	public function movements($ingredientId, $limit=200){
		$st=$this->pdo->prepare("SELECT * FROM ingredient_storage WHERE ingredient_id=? ORDER BY id DESC LIMIT ?");
		$st->execute([$ingredientId, $limit]);
		return $st->fetchAll();
	}

	public function currentStockAll(){
		$col=$this->col;
		if($this->hasLocation){
			$sql = "SELECT i.id, i.name, i.unit,
				COALESCE(SUM(CASE WHEN s.location='MAIN' THEN s.$col END),0) main_stock,
				COALESCE(SUM(CASE WHEN s.location='ROLLBACK' THEN s.$col END),0) rollback_stock,
				COALESCE(SUM(s.$col),0) stock
			FROM ingredients i
			LEFT JOIN ingredient_storage s ON i.id=s.ingredient_id
			GROUP BY i.id
			ORDER BY i.name";
			return $this->pdo->query($sql)->fetchAll();
		} else {
			$sql = "SELECT i.id, i.name, i.unit, COALESCE(SUM(s.$col),0) as stock FROM ingredients i LEFT JOIN ingredient_storage s ON i.id=s.ingredient_id GROUP BY i.id ORDER BY i.name";
			return $this->pdo->query($sql)->fetchAll();
		}
	}

	public function stockByIngredient($ingredientId){
		$st=$this->pdo->prepare("SELECT COALESCE(SUM({$this->col}),0) FROM ingredient_storage WHERE ingredient_id=?");
		$st->execute([$ingredientId]);
		return (float)$st->fetchColumn();
	}

	public function stockByLocations($ingredientId){
		$st=$this->pdo->prepare("SELECT location, COALESCE(SUM({$this->col}),0) qty FROM ingredient_storage WHERE ingredient_id=? GROUP BY location");
		$st->execute([$ingredientId]);
		$map=[]; foreach($st->fetchAll() as $r){ $map[$r['location']] = (float)$r['qty']; }
		return $map;
	}

	public function ledger($limit=500){
		$col=$this->col;
		// Detect optional columns (note, updated_at, created_at)
		try { $cols=$this->pdo->query("SHOW COLUMNS FROM ingredient_storage")->fetchAll(PDO::FETCH_COLUMN); } catch(Exception $e){ $cols=[]; }
		$hasNote = in_array('note',$cols,true);
		$hasUpdated = in_array('updated_at',$cols,true);
		$hasCreated = in_array('created_at',$cols,true);
		$noteSel = $hasNote? 's.note':'NULL as note';
		$updatedSel = $hasUpdated? 's.updated_at':'NULL as updated_at';
		$createdSel = $hasCreated? 's.created_at':'NULL as created_at';
		$sql="SELECT s.id, s.ingredient_id, i.name, s.location, s.$col change_qty, s.reason, s.ref_date, i.unit, $noteSel, $updatedSel, $createdSel
			FROM ingredient_storage s
			JOIN ingredients i ON i.id=s.ingredient_id
			ORDER BY s.id DESC
			LIMIT ?";
		$st=$this->pdo->prepare($sql); $st->execute([$limit]);
		return $st->fetchAll();
	}

	public function applyUsageDeltas($applyDate,$userId=null){
		$sql="SELECT pi.ingredient_id, SUM(oi.quantity * pi.quantity) usage_qty FROM orders o JOIN order_items oi ON oi.order_id=o.id JOIN product_ingredients pi ON pi.product_id=oi.product_id WHERE DATE(o.order_date)=? AND o.confirmed=1 GROUP BY pi.ingredient_id";
		$st=$this->pdo->prepare($sql); $st->execute([$applyDate]); $rows=$st->fetchAll(); if(!$rows) return ['applied'=>0,'details'=>[]];
		$appliedCount=0; $details=[]; $dCol=$this->usageDateCol; $qCol=$this->appliedQtyCol;
		foreach($rows as $r){ $ingId=$r['ingredient_id']; $need=(float)$r['usage_qty']; if($need<=0) continue; $st2=$this->pdo->prepare("SELECT $qCol q FROM ingredient_usage_applied WHERE $dCol=? AND ingredient_id=?"); $st2->execute([$applyDate,$ingId]); $appliedRow=$st2->fetch(); $already=$appliedRow? (float)$appliedRow['q']:0.0; $delta=$need-$already; if($delta<=0) continue; $this->add($ingId,-$delta,'apply_usage','Daily usage apply','MAIN',$applyDate); if($appliedRow){ $up=$this->pdo->prepare("UPDATE ingredient_usage_applied SET $qCol=$qCol+? WHERE $dCol=? AND ingredient_id=?"); $up->execute([$delta,$applyDate,$ingId]); } else { $insCols="$dCol, ingredient_id, $qCol"; $vals='?,?,?'; if($this->hasRolledBack) { $insCols.=', rolled_back'; $vals.=',0'; } $ins=$this->pdo->prepare("INSERT INTO ingredient_usage_applied ($insCols) VALUES ($vals)"); $ins->execute([$applyDate,$ingId,$delta]); } try { $log=$this->pdo->prepare("INSERT INTO ingredient_usage_applied_log (apply_date, ingredient_id, delta_qty, action, user_id) VALUES (?,?,?,?,?)"); $log->execute([$applyDate,$ingId,$delta,'apply',$userId]); } catch(Exception $e){} $appliedCount++; $details[]=['ingredient_id'=>$ingId,'delta'=>$delta]; }
		return ['applied'=>$appliedCount,'details'=>$details]; }
	public function rollbackDate($applyDate,$userId=null){ $dCol=$this->usageDateCol; $qCol=$this->appliedQtyCol; $cond=$this->hasRolledBack?" AND rolled_back=0":""; $st=$this->pdo->prepare("SELECT ingredient_id, $qCol q FROM ingredient_usage_applied WHERE $dCol=?$cond"); $st->execute([$applyDate]); $rows=$st->fetchAll(); if(!$rows) return 0; $count=0; foreach($rows as $r){ $ing=$r['ingredient_id']; $qty=(float)$r['q']; if($qty<=0) continue; $this->add($ing,$qty,'rollback_usage','Rollback usage','ROLLBACK',$applyDate); if($this->hasRolledBack){ $up=$this->pdo->prepare("UPDATE ingredient_usage_applied SET rolled_back=1 WHERE $dCol=? AND ingredient_id=?"); $up->execute([$applyDate,$ing]); } try { $log=$this->pdo->prepare("INSERT INTO ingredient_usage_applied_log (apply_date, ingredient_id, delta_qty, action, user_id) VALUES (?,?,?,?,?)"); $log->execute([$applyDate,$ing,$qty,'rollback',$userId]); } catch(Exception $e){} $count++; } return $count; }

	public function mergeRollback($ingredientId){
	$qtySt=$this->pdo->prepare("SELECT COALESCE(SUM({$this->col}),0) FROM ingredient_storage WHERE ingredient_id=? AND location='ROLLBACK'");
		$qtySt->execute([$ingredientId]); $qty=(float)$qtySt->fetchColumn(); if($qty==0) return false;
		$this->add($ingredientId, $qty, 'merge_rollback', 'Merge rollback to main', 'MAIN');
		$this->add($ingredientId, -$qty, 'merge_rollback', 'Clear rollback bucket', 'ROLLBACK');
		return true;
	}

	public function usageHistory($limit=30){ $dCol=$this->usageDateCol; $qCol=$this->appliedQtyCol; $rb=$this->hasRolledBack? 'ua.rolled_back':'NULL as rolled_back'; $sql="SELECT ua.$dCol apply_date, i.name, ua.ingredient_id, ua.$qCol applied_qty, $rb FROM ingredient_usage_applied ua JOIN ingredients i ON i.id=ua.ingredient_id ORDER BY ua.$dCol DESC, i.name ASC LIMIT ?"; $st=$this->pdo->prepare($sql); $st->execute([$limit]); return $st->fetchAll(); }

	// Aggregated usage history by date
	public function usageHistoryAggregated($limit=90){
		$dCol=$this->usageDateCol; $qCol=$this->appliedQtyCol; $rolled=$this->hasRolledBack? ', SUM(CASE WHEN ua.rolled_back=1 THEN 1 ELSE 0 END) rolled_items':''; $sql="SELECT ua.$dCol apply_date, COUNT(*) items, SUM(ua.$qCol) total_applied $rolled FROM ingredient_usage_applied ua GROUP BY ua.$dCol ORDER BY ua.$dCol DESC LIMIT ?"; $st=$this->pdo->prepare($sql); $st->execute([$limit]); return $st->fetchAll(); }

	// Detail rows for a specific date
	public function usageHistoryByDate($date){ $dCol=$this->usageDateCol; $qCol=$this->appliedQtyCol; $rb=$this->hasRolledBack? 'ua.rolled_back':'NULL as rolled_back'; $st=$this->pdo->prepare("SELECT ua.$dCol apply_date, i.name, i.unit, ua.ingredient_id, ua.$qCol applied_qty, $rb FROM ingredient_usage_applied ua JOIN ingredients i ON i.id=ua.ingredient_id WHERE ua.$dCol=? ORDER BY i.name"); $st->execute([$date]); return $st->fetchAll(); }

	// New rollback that restores stock to ROLLBACK location and removes usage rows entirely
	public function rollbackDateDelete($applyDate,$userId=null){
		$dCol=$this->usageDateCol; $qCol=$this->appliedQtyCol;
		try { $st=$this->pdo->prepare("SELECT ingredient_id, $qCol q FROM ingredient_usage_applied WHERE $dCol=?"); $st->execute([$applyDate]); $rows=$st->fetchAll(PDO::FETCH_ASSOC); } catch(Exception $e){ return 0; }
		if(!$rows) return 0; $count=0; foreach($rows as $r){ $ing=(int)$r['ingredient_id']; $qty=(float)$r['q']; if($qty<=0) continue; // restore to ROLLBACK bucket
			$this->add($ing,$qty,'usage_rollback','Rollback delete','ROLLBACK',$applyDate); $count++; try { $log=$this->pdo->prepare("INSERT INTO ingredient_usage_applied_log (apply_date, ingredient_id, delta_qty, action, user_id) VALUES (?,?,?,?,?)"); $log->execute([$applyDate,$ing,$qty,'rollback_delete',$userId]); } catch(Exception $e){} }
		// Delete rows
		try { $del=$this->pdo->prepare("DELETE FROM ingredient_usage_applied WHERE $dCol=?"); $del->execute([$applyDate]); } catch(Exception $e){}
		return $count; }
}

