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

if (!isset($_SESSION["selectBatchName"])) {
    $batchQuery = "SELECT DISTINCT BatchName 
                   FROM batch_details 
                   WHERE Status = 1 AND is_deleted = FALSE AND is_current = TRUE 
                   ORDER BY CheckInDate";

	$result = executeQuery($conn, $batchQuery);

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

    $batchNames = $result->fetch_all(MYSQLI_ASSOC);

    if (!empty($batchNames)) {
        $randomBatch = $batchNames[array_rand($batchNames)]["BatchName"];
        $_SESSION["selectBatchName"] = $randomBatch;
    }
}

$selectBatchName = $_SESSION["selectBatchName"];

$headers = [
    "BatchName",
    "AgeInWeeks", 
    "ProductionDate", 
    "S.H.D.%", 
    "S.H.H.", 
    "Hen Day", 
    "Pop", 
    "A.H.D.%", 
    "Bad Eggs",
    "Bad Eggs %",
    "A.H.H.",
    "FeedsKG",
    "GRM/Bird",
    "FCR/EW",
    "Dep",
    "Dep %",
    "Liv %"
];

echo "<table id='GraphTable' class='ui display hidden' style='display: none !important;'>";
echo "<thead><tr>";
foreach ($headers as $index => $header) {
    echo "<th>" . $header . "(" . $index . ")</th>";
}
echo "</tr></thead><tbody>";

$query = "SELECT 
    ip.BatchName, 
    ip.AgeInWeeks, 
    ip.ProductionDate,
	COALESCE(SUM(ip.H_TOTAL), 0) AS HTOTAL,
	COALESCE(SUM(ip.Depletion), 0) AS DepletionTotal,
    (SELECT Stnd_H_D_per 
		FROM egg_standards es 
		WHERE es.BreedType = bd.Breed 
			AND es.AgeInWeeks = ip.AgeInWeeks AND es.is_deleted = FALSE AND es.is_current = TRUE) as SHD_Total,
    (SELECT Stnd_H_H_per 
		FROM egg_standards es 
		WHERE es.BreedType = bd.Breed 
			AND es.AgeInWeeks = ip.AgeInWeeks AND es.is_deleted = FALSE AND es.is_current = TRUE) as Stnd_H_H_per,
    (SELECT HenHoused 
            FROM batch_details hh 
            WHERE hh.BatchName = ip.BatchName AND hh.is_deleted = FALSE AND hh.is_current = TRUE) as HenHoused,
	(SELECT Prod_Previous 
            FROM batch_details hh 
            WHERE hh.BatchName = ip.BatchName AND hh.is_deleted = FALSE AND hh.is_current = TRUE) as PreviousProduction,
    (SELECT COALESCE(SUM(fd.Consumption_KG), 0) 
            FROM feed_delivery fd 
            WHERE fd.BatchName = ip.BatchName AND fd.AgeInWeeks = ip.AgeInWeeks AND fd.is_deleted = FALSE AND fd.is_current = TRUE) as FeedsKG,
    (SELECT COALESCE(SUM(ew.B1 + ew.B2 + ew.B3 + ew.Dirty), 0) 
            FROM egg_weight ew 
            WHERE ew.BatchName = ip.BatchName AND ew.ProductionDate = ip.ProductionDate AND ew.is_deleted = FALSE AND ew.is_current = TRUE) as BadEggs
FROM input_production ip
LEFT JOIN batch_details bd ON ip.BatchName = bd.BatchName AND bd.is_deleted = FALSE AND bd.is_current = TRUE
WHERE ip.is_deleted = FALSE AND ip.is_current = TRUE AND ip.BatchName = ?
GROUP BY ip.AgeInWeeks
ORDER BY ip.ProductionDate ASC";

$result = executeQuery($conn, $query, "s", [$selectBatchName]);

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

$HenHoused = 0;
$initialHenHoused = mysqli_fetch_assoc($result);
if ($initialHenHoused) {
    $HenHoused = $initialHenHoused["HenHoused"];
}

mysqli_data_seek($result, 0);

$Pop = $HenHoused;
$AccumulatedHenDay = 0;
while ($row = mysqli_fetch_assoc($result)) 
{
	$AccumulatedHenDay = $row["PreviousProduction"];
    echo "<tr>";
    
    echo "<td>" . $row["BatchName"] . "</td>";
    echo "<td>" . $row["AgeInWeeks"] . "</td>";
    echo "<td>" . $row["ProductionDate"] . "</td>";
    echo "<td>" . number_format($row["SHD_Total"], 1) . "</td>";
    echo "<td>" . number_format($row["Stnd_H_H_per"], 2) . "</td>";
    
    $HenDay = $row["HTOTAL"];
    echo "<td>" . $HenDay . "</td>";
    echo "<td>" . $Pop . "</td>";
	
	$AHD_PER = 0;
	if (is_numeric($HenDay) && $HenDay > 0 && is_numeric($Pop) && $Pop > 0) {
		$AHD_PER = (($HenDay / 7) / $Pop) * 100;
	}
	echo "<td>" . number_format($AHD_PER, 1) . "</td>";
	
    echo "<td>" . $row["BadEggs"] . "</td>";
	
	$BE_PER = 0;
	if (is_numeric($HenDay) && $HenDay > 0 && is_numeric($row["BadEggs"])) {
		$BE_PER = ($row["BadEggs"] / $HenDay) * 100;
	}
	echo "<td>" . number_format($BE_PER, 2) . "</td>";
	
	$AccumulatedHenDay += $HenDay;
	
	$AHH = 0;
	if (is_numeric($AccumulatedHenDay) && $AccumulatedHenDay > 0 && is_numeric($row["HenHoused"])) {
		$AHH = $AccumulatedHenDay / $row["HenHoused"];
	}
	echo "<td>" . number_format($AHH, 2) . "</td>";
    
    echo "<td>" . $row["FeedsKG"] . "</td>";
	
	$GRM_BIRD = 0;
	if (is_numeric($Pop) && $Pop > 0 && is_numeric($row["FeedsKG"])) {
		$GRM_BIRD = ($row["FeedsKG"] * 1000) / $Pop;
	}
	echo "<td>" . number_format($GRM_BIRD, 2) . "</td>";
    
	$FCR_EW = 0;
	if (is_numeric($Pop) && $Pop > 0 && is_numeric($GRM_BIRD) && is_numeric($HenDay) && $HenDay > 0) {	
		$FCR_EW = ((($Pop * $GRM_BIRD) / 1000) * 12) / $HenDay;
	}
	echo "<td>" . number_format($FCR_EW, 2) . "</td>";
	
    echo "<td>" . $row["DepletionTotal"] . "</td>";
	
	$DEP_PER = 0;
	if (is_numeric($Pop) && $Pop > 0 && is_numeric($row["DepletionTotal"])) {
		$DEP_PER = $row["DepletionTotal"] / $Pop;
	}
	echo "<td>" . number_format($DEP_PER, 2) . "</td>";
    
	$LIV_PER = 0;
	if (is_numeric($Pop) && $Pop > 0 && is_numeric($row["HenHoused"])) {
		$LIV_PER = ($Pop / $row["HenHoused"]) * 100;
	}
	echo "<td>" . number_format($LIV_PER, 2) . "</td>";
    
    echo "</tr>";

    $Pop -= $row["DepletionTotal"];
}
echo "</tbody></table>";

$headers = [
    "BatchName",
    "AgeInWeeks", 
    "ProductionDate", 
    "FeedsKG",
    "GRM/Bird",
];

echo "<table id='GraphTable2' class='ui display hidden' style='display: none !important;'>";
echo "<thead><tr>";
foreach ($headers as $index => $header) {
    echo "<th>" . $header . "(" . $index . ")</th>";
}
echo "</tr></thead><tbody>";

$query = "SELECT 
    ip.BatchName, 
    ip.AgeInWeeks, 
    ip.ProductionDate,
    (SELECT COALESCE(SUM(fd.Consumption_KG), 0) 
		FROM feed_delivery fd 
		WHERE fd.BatchName = ip.BatchName AND fd.ProductionDate = ip.ProductionDate AND fd.is_deleted = FALSE AND fd.is_current = TRUE) as FeedsKG
FROM input_production ip
WHERE ip.is_deleted = FALSE AND ip.is_current = TRUE AND ip.BatchName = ?
GROUP BY ip.ProductionDate
ORDER BY ip.ProductionDate ASC";

$result = executeQuery($conn, $query, "s", [$selectBatchName]);

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

while ($row = mysqli_fetch_assoc($result)) 
{
    echo "<tr>";
    
    echo "<td>" . $row["BatchName"] . "</td>";
    echo "<td>" . $row["AgeInWeeks"] . "</td>";
    echo "<td>" . $row["ProductionDate"] . "</td>";
    
    echo "<td>" . $row["FeedsKG"] . "</td>";
	
	$GRM_BIRD = 0;
	if (is_numeric($Pop) && $Pop > 0 && is_numeric($row["FeedsKG"])) {
		$GRM_BIRD = ($row["FeedsKG"] * 1000) / $Pop;
	}
	echo "<td>" . number_format($GRM_BIRD, 2) . "</td>";
    
    echo "</tr>";
}
echo "</tbody></table>";

$headers = [
	"Production Date",
    "Temperature &deg;C", 
    "Humidity %",
    "Heat Index &deg;C",
];

echo "<table id='GraphTable3' class='ui display hidden' style='display: none !important;'>";
echo "<thead><tr>";
foreach ($headers as $index => $header) {
    echo "<th>" . $header . "(" . $index . ")</th>";
}
echo "</tr></thead><tbody>";

$query = "SELECT 
    ProductionDate,
    AVG(temp) AS avg_temp,
    AVG(humidity) AS avg_humidity,
    AVG(heat_index) AS avg_heat_index
	FROM 
		input_temp
	WHERE 
		is_deleted = FALSE AND is_current = TRUE
	GROUP BY 
		ProductionDate
	ORDER BY 
		ProductionDate ASC";

$result = executeQuery($conn, $query);

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

while ($row = mysqli_fetch_assoc($result)) 
{
    echo "<tr>";
    
	echo "<td>" . $row["ProductionDate"] . "</td>";
    echo "<td>" . number_format($row["avg_temp"], 1) . "</td>";
    echo "<td>" . number_format($row["avg_humidity"], 1) . "</td>";
    echo "<td>" . number_format($row["avg_heat_index"], 1) . "</td>";
    
    echo "</tr>";
}
echo "</tbody></table>";
?>