<?php
require_once '../app/Models/Ingredient.php';
require_once '../app/Models/IngredientStorage.php';
class StockController {
	private $ingredient; private $storage;
	public function __construct(){ $this->auth(); $this->ingredient=new Ingredient(); $this->storage=new IngredientStorage(); }
	private function auth(){ if(!isset($_SESSION['user_id'])){ header('Location:/samanta_crm/login'); exit; } }
	private function view($v,$d=[]){ extract($d); require_once "../app/Views/$v.php"; }
	private function scalar($sql,$p=[]){ $st=$GLOBALS['pdo']->prepare($sql); $st->execute($p); return (float)$st->fetchColumn(); }
	public function ingredients(){
		$mode = $_GET['mode'] ?? 'summary';
		$export = $_GET['export'] ?? null; // csv | json | xls
		$print = isset($_GET['print']);
		if($mode==='ledger'){
			$rows=$this->storage->ledger(5000); // larger limit for export
			if($print){ $this->view('stock/ingredients_ledger_print',[ 'rows'=>$rows ]); return; }
			if($export){
				if($export==='csv'){
					header('Content-Type: text/csv');
					header('Content-Disposition: attachment; filename="ingredient_ledger_'.date('Ymd_His').'.csv"');
					$out=fopen('php://output','w');
					fputcsv($out,['id','ingredient_id','ingredient','location','qty','unit','reason','ref_date','note','created_at','updated_at']);
					foreach($rows as $r){ fputcsv($out,[ $r['id'],$r['ingredient_id'],$r['name'],$r['location'],(int)round($r['change_qty']),$r['unit'],$r['reason'],$r['ref_date'],$r['note'],$r['created_at']??'',$r['updated_at']??'' ]); }
					fclose($out); exit;
				} elseif($export==='json'){
					header('Content-Type: application/json');
					echo json_encode($rows); exit;
				} elseif($export==='xls'){
					header('Content-Type: application/vnd.ms-excel');
					header('Content-Disposition: attachment; filename="ingredient_ledger_'.date('Ymd_His').'.xls"');
					echo $this->excelXmlLedger($rows);
					exit;
				}
			}
			$this->view('stock/ingredients_ledger',[ 'rows'=>$rows ]);
			return;
		}
		$stock=$this->storage->currentStockAll();
		if($print){ $this->view('stock/ingredients_print',[ 'stock'=>$stock ]); return; }
		if($export){
			if($export==='csv'){
				header('Content-Type: text/csv');
				header('Content-Disposition: attachment; filename="ingredient_stock_locations_'.date('Ymd_His').'.csv"');
				$out=fopen('php://output','w');
				fputcsv($out,['ingredient_id','ingredient','unit','location','qty']);
				foreach($stock as $r){
					$main = isset($r['main_stock'])? (int)round($r['main_stock']) : (int)round($r['stock']);
					$rollback = isset($r['rollback_stock'])? (int)round($r['rollback_stock']) : 0;
					fputcsv($out,[$r['id'],$r['name'],$r['unit'],'MAIN',$main]);
					if(isset($r['rollback_stock'])){ fputcsv($out,[$r['id'],$r['name'],$r['unit'],'ROLLBACK',$rollback]); }
				}
				fclose($out); exit;
			} elseif($export==='json'){
				header('Content-Type: application/json');
				$rows=[]; foreach($stock as $r){ $rows[]=['ingredient_id'=>$r['id'],'name'=>$r['name'],'unit'=>$r['unit'],'location'=>'MAIN','qty'=>isset($r['main_stock'])? (int)round($r['main_stock']):(int)round($r['stock'])]; if(isset($r['rollback_stock'])) $rows[]=['ingredient_id'=>$r['id'],'name'=>$r['name'],'unit'=>$r['unit'],'location'=>'ROLLBACK','qty'=>(int)round($r['rollback_stock'])]; }
				echo json_encode($rows); exit;
			} elseif($export==='xls'){
				header('Content-Type: application/vnd.ms-excel');
				header('Content-Disposition: attachment; filename="ingredient_stock_locations_'.date('Ymd_His').'.xls"');
				echo $this->excelXmlStock($stock);
				exit;
			}
		}
		$this->view('stock/ingredients',[ 'stock'=>$stock ]);
	}

	private function excelXmlHeader(){
		return "<?xml version=\"1.0\"?>\n<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\">";
	}
	private function excelXmlFooter(){ return '</Workbook>'; }
	private function sanitize($v){ return htmlspecialchars($v??'',ENT_QUOTES,'UTF-8'); }
	private function excelXmlStock($stock){
		$xml=$this->excelXmlHeader();
		$xml.='<Worksheet ss:Name="Stock"><Table>'; $xml.='<Row>';
		$cols=['Ingredient ID','Ingredient','Unit','Location','Qty']; foreach($cols as $c){ $xml.='<Cell><Data ss:Type="String">'.$this->sanitize($c).'</Data></Cell>'; }
		$xml.='</Row>';
		foreach($stock as $r){
			$main = isset($r['main_stock'])? (int)round($r['main_stock']) : (int)round($r['stock']);
			$rollback = isset($r['rollback_stock'])? (int)round($r['rollback_stock']) : 0;
			$xml.='<Row>';
			foreach([$r['id'],$r['name'],$r['unit'],'MAIN',$main] as $v){ $xml.='<Cell><Data ss:Type="String">'.$this->sanitize($v).'</Data></Cell>'; }
			$xml.='</Row>';
			if(isset($r['rollback_stock'])){ $xml.='<Row>'; foreach([$r['id'],$r['name'],$r['unit'],'ROLLBACK',$rollback] as $v){ $xml.='<Cell><Data ss:Type="String">'.$this->sanitize($v).'</Data></Cell>'; } $xml.='</Row>'; }
		}
		$xml.='</Table></Worksheet>';
		return $xml.$this->excelXmlFooter();
	}
	private function excelXmlLedger($rows){
		$xml=$this->excelXmlHeader();
		$xml.='<Worksheet ss:Name="Ledger"><Table><Row>';
		$cols=['ID','Ingredient ID','Ingredient','Location','Qty','Unit','Reason','Ref Date','Note','Created','Updated']; foreach($cols as $c){ $xml.='<Cell><Data ss:Type="String">'.$this->sanitize($c).'</Data></Cell>'; }
		$xml.='</Row>';
		foreach($rows as $r){
			$vals=[ $r['id'],$r['ingredient_id'],$r['name'],$r['location'], (int)round($r['change_qty']), $r['unit'],$r['reason'],$r['ref_date'],$r['note'],$r['created_at']??'',$r['updated_at']??'' ];
			$xml.='<Row>'; foreach($vals as $v){ $xml.='<Cell><Data ss:Type="String">'.$this->sanitize($v).'</Data></Cell>'; } $xml.='</Row>';
		}
		$xml.='</Table></Worksheet>';
		return $xml.$this->excelXmlFooter();
	}
	public function applyDaily(){
		$date=$_POST['apply_date']??date('Y-m-d');
		$res=$this->storage->applyUsageDeltas($date, $_SESSION['user_id']??null);
		$_SESSION['success'] = 'Applied usage for '.$date.' ('.$res['applied'].' ingredients updated)';
		header('Location:/samanta_crm/stock/ingredients');
	}
	public function usageHistory(){
		// If date param present show detail, else aggregated list
		if(isset($_GET['date']) && $_GET['date']!==''){
			$date=$_GET['date'];
			$detail=$this->storage->usageHistoryByDate($date);
			$this->view('stock/usage_history_date',[ 'date'=>$date,'rows'=>$detail ]);
			return;
		}
		$agg=$this->storage->usageHistoryAggregated(120);
		$this->view('stock/usage_history',[ 'rows'=>$agg ]);
	}
	public function usageChart(){
		$pdo=$GLOBALS['pdo'];
		// Inputs: start_date, end_date, ingredient_id (optional), fallback days param
		$daysParam=(int)($_GET['days']??30); if($daysParam<3) $daysParam=30; if($daysParam>180) $daysParam=180;
		$start = isset($_GET['start_date']) && preg_match('/^\d{4}-\d{2}-\d{2}$/',$_GET['start_date'])? $_GET['start_date']: date('Y-m-d', strtotime('-'.($daysParam-1).' days'));
		$end = isset($_GET['end_date']) && preg_match('/^\d{4}-\d{2}-\d{2}$/',$_GET['end_date'])? $_GET['end_date']: date('Y-m-d');
		if($end < $start){ [$start,$end]=[$end,$start]; }
		$ingredientId = isset($_GET['ingredient_id'])? (int)$_GET['ingredient_id']:0;
		// Ingredient list for filter
		$ingList=[]; try { $ingList=$pdo->query("SELECT id,name FROM ingredients ORDER BY name")->fetchAll(PDO::FETCH_ASSOC); } catch(Exception $e){}
		// Detect legacy columns for usage
		$cols=[]; try { $cols=$pdo->query("SHOW COLUMNS FROM ingredient_usage_applied")->fetchAll(PDO::FETCH_COLUMN); } catch(Exception $e){ }
		$applyDateCol = in_array('apply_date',$cols,true)?'apply_date':(in_array('usage_date',$cols,true)?'usage_date':'apply_date');
		$appliedQtyCol = in_array('applied_qty',$cols,true)?'applied_qty':(in_array('quantity',$cols,true)?'quantity':'applied_qty');
		// Storage columns
		$storCols=[]; try { $storCols=$pdo->query("SHOW COLUMNS FROM ingredient_storage")->fetchAll(PDO::FETCH_COLUMN); } catch(Exception $e){ }
		$moveCol = in_array('change_qty',$storCols,true)?'change_qty':(in_array('quantity',$storCols,true)?'quantity':'change_qty');
		$hasCreated = in_array('created_at',$storCols,true);
		$dateExpr = $hasCreated? "DATE(COALESCE(ref_date, created_at))" : "DATE(ref_date)";
		$dates=[]; $d=$start; while($d <= $end){ $dates[]=$d; $d=date('Y-m-d', strtotime($d.' +1 day')); }
		$datasets=[];
		if($ingredientId>0){
			// Single ingredient mode
			// Usage daily
			$usageStmt=$pdo->prepare("SELECT $applyDateCol d, $appliedQtyCol q FROM ingredient_usage_applied WHERE ingredient_id=? AND $applyDateCol BETWEEN ? AND ?");
			$usageArr=[]; try { $usageStmt->execute([$ingredientId,$start,$end]); while($r=$usageStmt->fetch(PDO::FETCH_ASSOC)){ $usageArr[$r['d']] = (float)$r['q']; } } catch(Exception $e){}
			// Restocks daily
			$restockStmt=$pdo->prepare("SELECT r.restock_date d, SUM(ri.quantity) q FROM restock_items ri JOIN restocks r ON r.id=ri.restock_id WHERE ri.ingredient_id=? AND r.restock_date BETWEEN ? AND ? GROUP BY r.restock_date");
			$restocks=[]; try { $restockStmt->execute([$ingredientId,$start,$end]); while($r=$restockStmt->fetch(PDO::FETCH_ASSOC)){ $restocks[$r['d']] = (float)$r['q']; } } catch(Exception $e){}
			// Stock starting balance + movements
			$startBalStmt=$pdo->prepare("SELECT SUM($moveCol) FROM ingredient_storage WHERE ingredient_id=? AND $dateExpr < ?"); $startBal=0; try { $startBalStmt->execute([$ingredientId,$start]); $startBal=(float)$startBalStmt->fetchColumn(); } catch(Exception $e){}
			$movStmt=$pdo->prepare("SELECT $dateExpr d, SUM($moveCol) qty FROM ingredient_storage WHERE ingredient_id=? AND $dateExpr BETWEEN ? AND ? GROUP BY $dateExpr");
			$movements=[]; try { $movStmt->execute([$ingredientId,$start,$end]); while($m=$movStmt->fetch(PDO::FETCH_ASSOC)){ $movements[$m['d']] = (float)$m['qty']; } } catch(Exception $e){}
			// Build series
			$usageSeries=[]; $stockSeries=[]; $restockSeries=[]; $running=$startBal; foreach($dates as $dt){ if(isset($movements[$dt])) $running += $movements[$dt]; $stockSeries[]=$running; $usageSeries[]=$usageArr[$dt]??0; $restockSeries[]=$restocks[$dt]??0; }
			// Labels need ingredient name
			$ingName='Ingredient #'.$ingredientId; try { $n=$pdo->prepare("SELECT name FROM ingredients WHERE id=?"); $n->execute([$ingredientId]); $nm=$n->fetchColumn(); if($nm) $ingName=$nm; } catch(Exception $e){}
			$datasets[]=['label'=>$ingName.' - Applied','series'=>'usage','type'=>'line','data'=>$usageSeries];
			$datasets[]=['label'=>$ingName.' - Stock','series'=>'stock','type'=>'line','data'=>$stockSeries];
			$datasets[]=['label'=>$ingName.' - Restocks','series'=>'restock','type'=>'bar','data'=>$restockSeries];
		} else {
			// Aggregated mode (all ingredients)
			$usageAggStmt=$pdo->prepare("SELECT $applyDateCol d, SUM($appliedQtyCol) q FROM ingredient_usage_applied WHERE $applyDateCol BETWEEN ? AND ? GROUP BY $applyDateCol");
			$usageAgg=[]; try { $usageAggStmt->execute([$start,$end]); while($r=$usageAggStmt->fetch(PDO::FETCH_ASSOC)){ $usageAgg[$r['d']] = (float)$r['q']; } } catch(Exception $e){}
			$restockAggStmt=$pdo->prepare("SELECT r.restock_date d, SUM(ri.quantity) q FROM restock_items ri JOIN restocks r ON r.id=ri.restock_id WHERE r.restock_date BETWEEN ? AND ? GROUP BY r.restock_date");
			$restockAgg=[]; try { $restockAggStmt->execute([$start,$end]); while($r=$restockAggStmt->fetch(PDO::FETCH_ASSOC)){ $restockAgg[$r['d']] = (float)$r['q']; } } catch(Exception $e){}
			// Stock aggregated
			$startBalAggStmt=$pdo->prepare("SELECT COALESCE(SUM($moveCol),0) FROM ingredient_storage WHERE $dateExpr < ?"); $startBalAgg=0; try { $startBalAggStmt->execute([$start]); $startBalAgg=(float)$startBalAggStmt->fetchColumn(); } catch(Exception $e){}
			$movAggStmt=$pdo->prepare("SELECT $dateExpr d, SUM($moveCol) qty FROM ingredient_storage WHERE $dateExpr BETWEEN ? AND ? GROUP BY $dateExpr");
			$movAgg=[]; try { $movAggStmt->execute([$start,$end]); while($m=$movAggStmt->fetch(PDO::FETCH_ASSOC)){ $movAgg[$m['d']] = (float)$m['qty']; } } catch(Exception $e){}
			$usageSeries=[]; $stockSeries=[]; $restockSeries=[]; $running=$startBalAgg; foreach($dates as $dt){ if(isset($movAgg[$dt])) $running += $movAgg[$dt]; $stockSeries[]=$running; $usageSeries[]=$usageAgg[$dt]??0; $restockSeries[]=$restockAgg[$dt]??0; }
			$datasets[]=['label'=>'Applied Usage (All)','series'=>'usage','type'=>'line','data'=>$usageSeries];
			$datasets[]=['label'=>'Stock (All)','series'=>'stock','type'=>'line','data'=>$stockSeries];
			$datasets[]=['label'=>'Restocks (All)','series'=>'restock','type'=>'bar','data'=>$restockSeries];
		}
		$this->view('stock/usage_chart',[ 'dates'=>$dates,'datasets'=>$datasets,'start_date'=>$start,'end_date'=>$end,'ingredient_id'=>$ingredientId,'ingredients'=>$ingList ]);
	}
	public function rollbackDaily(){
		$date=$_POST['rollback_date']??date('Y-m-d');
		// use new delete rollback behavior
		$cnt=$this->storage->rollbackDateDelete($date, $_SESSION['user_id']??null);
		$_SESSION['success']='Rolled back & deleted usage for '.$cnt.' ingredients on '.$date;
		header('Location:/samanta_crm/stock/usage-history');
	}
	public function rollbackDailyOld(){ // legacy route if needed
		$date=$_POST['rollback_date']??date('Y-m-d');
		$cnt=$this->storage->rollbackDate($date, $_SESSION['user_id']??null);
		$_SESSION['success']='(Legacy) Rolled back '.$cnt.' ingredients for '.$date;
		header('Location:/samanta_crm/stock/usage-history');
	}
	public function dailyUsage(){
		$day = $_GET['date'] ?? date('Y-m-d');
		$pdo=$GLOBALS['pdo'];
		// Build projected usage from orders of selected date (order_date) using product_ingredients * ordered quantity
		$sql="SELECT pi.ingredient_id, i.name, i.unit, SUM(pi.quantity * oi.quantity) AS needed
			FROM orders o
			JOIN order_items oi ON oi.order_id=o.id
			JOIN product_ingredients pi ON pi.product_id=oi.product_id
			JOIN ingredients i ON i.id=pi.ingredient_id
			WHERE DATE(o.order_date)=? AND (o.status!='cancelled' OR o.status IS NULL)
			GROUP BY pi.ingredient_id, i.name, i.unit
			ORDER BY i.name";
		$st=$pdo->prepare($sql); $st->execute([$day]); $usage=$st->fetchAll(PDO::FETCH_ASSOC);
		// Current stock for those ingredients
		$stockMap=[]; foreach($this->storage->currentStockAll() as $s){ $stockMap[$s['id']] = $s['stock']; }
		// Already applied usage for the day
		$appliedMap=[]; try { $cols=$pdo->query("SHOW COLUMNS FROM ingredient_usage_applied")->fetchAll(PDO::FETCH_COLUMN); $applyDateCol=in_array('apply_date',$cols,true)?'apply_date':(in_array('usage_date',$cols,true)?'usage_date':'apply_date'); $appliedQtyCol=in_array('applied_qty',$cols,true)?'applied_qty':(in_array('quantity',$cols,true)?'quantity':'applied_qty'); $ap=$pdo->prepare("SELECT ingredient_id, $appliedQtyCol v FROM ingredient_usage_applied WHERE $applyDateCol=?"); $ap->execute([$day]); while($r=$ap->fetch(PDO::FETCH_ASSOC)){ $appliedMap[$r['ingredient_id']] = (float)$r['v']; } } catch(Exception $e){ $appliedMap=[]; }
		foreach($usage as &$u){ $u['stock'] = $stockMap[$u['ingredient_id']] ?? 0; $u['applied'] = $appliedMap[$u['ingredient_id']] ?? 0; $u['remaining_after'] = $u['stock'] - $u['needed']; }
		unset($u);
		$this->view('stock/daily_usage',[ 'day'=>$day,'usage'=>$usage ]);
	}
	public function applyUsageSingle(){
		if($_SERVER['REQUEST_METHOD']!=='POST'){ header('Location:/samanta_crm/stock/daily-usage'); return; }
		$day = $_POST['apply_date'] ?? date('Y-m-d');
		$ingredientId = (int)($_POST['ingredient_id']??0);
		$desired = (float)str_replace(',','.', $_POST['desired_qty'] ?? 0);
		if($ingredientId<=0 || $desired<=0){ $_SESSION['error']='Invalid data'; header('Location:/samanta_crm/stock/daily-usage?date='.$day); return; }
		$pdo=$GLOBALS['pdo'];
		// Detect columns for backward compatibility
		$cols=[]; try { $cols=$pdo->query("SHOW COLUMNS FROM ingredient_usage_applied")->fetchAll(PDO::FETCH_COLUMN); } catch(Exception $e){ }
		$applyDateCol = in_array('apply_date',$cols,true)?'apply_date':(in_array('usage_date',$cols,true)?'usage_date':'apply_date');
		$appliedQtyCol = in_array('applied_qty',$cols,true)?'applied_qty':(in_array('quantity',$cols,true)?'quantity':'applied_qty');
		$hasRolled = in_array('rolled_back',$cols,true);
		// Fetch existing
		$existing=0; try { $sel=$pdo->prepare("SELECT $appliedQtyCol FROM ingredient_usage_applied WHERE $applyDateCol=? AND ingredient_id=?"); $sel->execute([$day,$ingredientId]); $existing=(float)$sel->fetchColumn(); } catch(Exception $e){ $existing=0; }
		$delta = $desired - $existing;
		if($delta>0){
			// subtract from stock ledger
			$this->storage->add($ingredientId, -$delta, 'usage_apply', 'Manual apply', 'MAIN', $day);
		}
		try {
			if($existing>0){
				$upd=$pdo->prepare("UPDATE ingredient_usage_applied SET $appliedQtyCol=? WHERE $applyDateCol=? AND ingredient_id=?");
				$upd->execute([$desired,$day,$ingredientId]);
			} else {
				if($hasRolled){
					$ins=$pdo->prepare("INSERT INTO ingredient_usage_applied ($applyDateCol, ingredient_id, $appliedQtyCol, rolled_back) VALUES (?,?,?,0)");
					$ins->execute([$day,$ingredientId,$desired]);
				} else {
					$ins=$pdo->prepare("INSERT INTO ingredient_usage_applied ($applyDateCol, ingredient_id, $appliedQtyCol) VALUES (?,?,?)");
					$ins->execute([$day,$ingredientId,$desired]);
				}
			}
			$_SESSION['success']='Applied usage updated';
		} catch(Exception $e){ $_SESSION['error']='Failed to apply usage: '.$e->getMessage(); }
		header('Location:/samanta_crm/stock/daily-usage?date='.$day);
	}
	public function applyUsageAll(){
		if($_SERVER['REQUEST_METHOD']!=='POST'){ header('Location:/samanta_crm/stock/daily-usage'); return; }
		$day = $_POST['apply_date'] ?? date('Y-m-d');
		$pdo=$GLOBALS['pdo'];
		// Recompute needed usage snapshot
		$sql="SELECT pi.ingredient_id, SUM(pi.quantity * oi.quantity) AS needed
			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.status!='cancelled' OR o.status IS NULL)
			GROUP BY pi.ingredient_id";
		$st=$pdo->prepare($sql); $st->execute([$day]); $rows=$st->fetchAll(PDO::FETCH_ASSOC);
		// Detect columns
		$cols=[]; try { $cols=$pdo->query("SHOW COLUMNS FROM ingredient_usage_applied")->fetchAll(PDO::FETCH_COLUMN); } catch(Exception $e){ }
		$applyDateCol = in_array('apply_date',$cols,true)?'apply_date':(in_array('usage_date',$cols,true)?'usage_date':'apply_date');
		$appliedQtyCol = in_array('applied_qty',$cols,true)?'applied_qty':(in_array('quantity',$cols,true)?'quantity':'applied_qty');
		$hasRolled = in_array('rolled_back',$cols,true);
		$sel=$pdo->prepare("SELECT ingredient_id, $appliedQtyCol v FROM ingredient_usage_applied WHERE $applyDateCol=?");
		$sel->execute([$day]); $existing=[]; while($r=$sel->fetch(PDO::FETCH_ASSOC)){ $existing[$r['ingredient_id']] = (float)$r['v']; }
		$ins = $hasRolled
			? $pdo->prepare("INSERT INTO ingredient_usage_applied ($applyDateCol, ingredient_id, $appliedQtyCol, rolled_back) VALUES (?,?,?,0)")
			: $pdo->prepare("INSERT INTO ingredient_usage_applied ($applyDateCol, ingredient_id, $appliedQtyCol) VALUES (?,?,?)");
		$upd=$pdo->prepare("UPDATE ingredient_usage_applied SET $appliedQtyCol=? WHERE $applyDateCol=? AND ingredient_id=?");
		$appliedCount=0;
		foreach($rows as $r){
			$ing=(int)$r['ingredient_id']; $needed=(float)$r['needed']; if($needed<=0) continue; $prev=$existing[$ing]??0; $delta=$needed-$prev; if($delta>0){ $this->storage->add($ing, -$delta, 'usage_apply', 'Bulk apply', 'MAIN', $day); }
			try { if($prev>0){ $upd->execute([$needed,$day,$ing]); } else { $ins->execute([$day,$ing,$needed]); } $appliedCount++; } catch(Exception $e){ /* ignore individual failure */ }
		}
		if($appliedCount>0){
			$_SESSION['success']='Bulk applied usage for '.$appliedCount.' ingredients';
		} else {
			$_SESSION['error']='No usage applied (either already applied or no orders)';
		}
		header('Location:/samanta_crm/stock/daily-usage?date='.$day);
	}
}
