<?php
require_once("dbconn.php");
require_once("phpFunctions.php");

$BatchName = isset($_POST["BatchName"]) ? $_POST["BatchName"] : null;
$BldgNo = isset($_POST["BldgNo"]) ? $_POST["BldgNo"] : null;
$ProductionDate = isset($_POST["ProductionDate"]) ? $_POST["ProductionDate"] : date("Y-m-d");
$PreviousDay = date("Y-m-d", strtotime("-1 day", strtotime($ProductionDate)));

$response = array(); 
$response["BldgNo"] = $BldgNo;

$result = executeQuery($conn, "SELECT BatchCode FROM batch_details WHERE Status = 1 AND is_deleted = FALSE AND is_current = TRUE AND BatchName = ?", "s", [$BatchName]);

if ($result === false) { 
	throw new Exception("Error executing query: " . $conn->error);
} 

if ($result->num_rows > 0) 
{
	while($row = $result->fetch_assoc()) 
	{
		$response["BatchCode"] = $row["BatchCode"];
	}
}
else
{
	$response["BatchCode"] = null;
}

$result = executeQuery($conn, "SELECT FLOOR(DATEDIFF(?, CheckInDate) / 7) as AgeInWeeks FROM batch_details WHERE Status = 1 AND is_deleted = FALSE AND is_current = TRUE AND BatchName = ?", "ss", [$ProductionDate,$BatchName]);

if ($result === false) { 
	throw new Exception("Error executing query: " . $conn->error);
} 

if ($result->num_rows > 0) 
{
	while($row = $result->fetch_assoc()) 
	{
		if (empty($row["AgeInWeeks"]) || $row["AgeInWeeks"] == 0)
		{
			$response["AgeInWeeks"] = 0;
		}
		else
		{
			$response["AgeInWeeks"] = number_format($row["AgeInWeeks"], 0);
		}
	}
}
else
{
	$response["AgeInWeeks"] = 0;
}

$batchDetailsResult = executeQuery($conn, "SELECT * FROM batch_details WHERE Status = 1 AND is_deleted = FALSE AND is_current = TRUE AND BatchName = ?", "s", [$BatchName]);
$bldgNoDetails = [];

if ($batchDetailsResult->num_rows > 0) {
	while($brow = $batchDetailsResult->fetch_assoc()) {
		$bldgNoDetails[] = $brow;
	}
} 

$per_balance = 0;

if (!empty($bldgNoDetails)) 
{
	$bldgNos = explode(",", $bldgNoDetails[0]["BldgNo"]);
	foreach ($bldgNos as $bldgNo) 
	{
		$cleanedBldgNo = preg_replace("/\[\d+\]/", "", $bldgNo);
		$cleanedBldgNo = trim($cleanedBldgNo);
		if ($cleanedBldgNo == $BldgNo) 
		{
			preg_match("/\[(.*?)\]/", $bldgNo, $matches);
			$per_balance = $matches[1] ?? 0;
		}
	}
}

$result_ip = executeQuery($conn, "SELECT 
    SUM(CASE WHEN ProductionDate = ? AND BatchName = ? AND BldgNo = ? THEN Hen_Day ELSE 0 END) - 
    SUM(CASE WHEN BldgNo = ? AND ProductionDate = ? AND BatchName = ? THEN Depletion ELSE 0 END) as Result 
    FROM input_production 
    WHERE is_deleted = FALSE AND is_current = TRUE", 
    "ssssss", [$PreviousDay, $BatchName, $BldgNo, $BldgNo, $PreviousDay, $BatchName]);

if ($result_ip === false) { 
    throw new Exception("Error executing query: " . $conn->error);
}

$row_ip = mysqli_fetch_assoc($result_ip);

$result_ip_inv_adj_check = executeQuery($conn, "SELECT Inv_Adj FROM input_production 
    WHERE ProductionDate = ? AND BatchName = ? AND BldgNo = ? AND is_deleted = FALSE AND is_current = TRUE", 
    "sss", [$PreviousDay, $BatchName, $BldgNo]);

if ($result_ip_inv_adj_check === false) { 
    throw new Exception("Error executing query: " . $conn->error);
} 

$row_inv_adj_ip = mysqli_fetch_assoc($result_ip_inv_adj_check);

if ($row_ip) 
{
    $response["Hen_Day"] = $row_ip["Result"] + ($row_inv_adj_ip["Inv_Adj"] ?? 0);
} 
else 
{
    $result_pre_ip = executeQuery($conn, "SELECT 
        SUM(CASE WHEN ProductionDate = ? AND BatchName = ? AND BldgNo = ? THEN Hen_Day ELSE 0 END) - 
        SUM(CASE WHEN BldgNo = ? AND ProductionDate = ? AND BatchName = ? THEN Depletion ELSE 0 END) as Result 
        FROM predata_ip 
        WHERE is_deleted = FALSE AND is_current = TRUE", 
        "ssssss", [$PreviousDay, $BatchName, $BldgNo, $BldgNo, $PreviousDay, $BatchName]);

	if ($result_pre_ip === false) { 
		throw new Exception("Error executing query: " . $conn->error);
	} 
	
	$row_pre_ip = mysqli_fetch_assoc($result_pre_ip);
	
    $result_pre_ip_inv_adj_check = executeQuery($conn, "SELECT Inv_Adj FROM predata_ip 
        WHERE ProductionDate = ? AND BatchName = ? AND BldgNo = ? AND is_deleted = FALSE AND is_current = TRUE", 
        "sss", [$PreviousDay, $BatchName, $BldgNo]);
	
	if ($result_pre_ip_inv_adj_check === false) { 
		throw new Exception("Error executing query: " . $conn->error);
	}
	
	$row_inv_adj_pre_ip = mysqli_fetch_assoc($result_pre_ip_inv_adj_check);
	
    if ($row_pre_ip) {
		$response["Hen_Day"] = $row_pre_ip["Result"] + + ($row_inv_adj_pre_ip["Inv_Adj"] ?? 0);
    } else {
        $response["Hen_Day"] = $per_balance;
    }
}

if (empty($response)) {
	echo json_encode(["status" => "error", "message" => "ipepQuery - An error occurred while processing your request."]);
} else {
    echo json_encode($response);
}
?>