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

$ProductionDate = isset($_SESSION["ProductionDate"]) ? $_SESSION["ProductionDate"] : date("Y-m-d");
$previousProductionDate = new DateTime($ProductionDate);
$previousProductionDate->modify("-1 day");
$previousProductionDateStr = $previousProductionDate->format("Y-m-d");

// START EGG PRODUCTION
$batchDetailsResult = executeQuery($conn, "SELECT BatchName, BldgNo FROM batch_details WHERE Status = 1 AND is_deleted = FALSE AND is_current = TRUE");

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

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

$existingPRE1Query = "SELECT COUNT(*) FROM predata_ip WHERE ProductionDate = ?";
$existingPRE1Result = executeQuery($conn, $existingPRE1Query, "s", [date("Y-m-d")]);

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

$existingPRE1Count = mysqli_fetch_row($existingPRE1Result)[0];

$existingPOST1Query = "SELECT COUNT(*) FROM input_production WHERE ProductionDate = ?";
$existingPOST1Result = executeQuery($conn, $existingPOST1Query, "s", [date("Y-m-d")]);

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

$existingPOST1Count = mysqli_fetch_row($existingPOST1Result)[0];

mysqli_begin_transaction($conn);

$per_balance = 0;

if ($existingPRE1Count == 0 && $existingPOST1Count == 0) {
    try {
        foreach ($batchDetails as $detail) {
            $BatchName = $detail["BatchName"];
            $bldgNos = explode(",", $detail["BldgNo"]);
            
            $AgeResult = 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", [date("Y-m-d"), $BatchName]);
            
            if ($AgeResult === false) { 
                throw new Exception("Error executing query: " . $conn->error);
            } 
            
            $AgeRow = mysqli_fetch_assoc($AgeResult);
            $AgeInWeeks = $AgeRow["AgeInWeeks"];
            
            foreach ($bldgNos as $bldgNo) {
                if (strpos($bldgNo, "{0}") === false) {
                    preg_match("/\[(.*?)\]/", $bldgNo, $matches);
                    $per_balance = $matches[1] ?? 0;

                    $cleanedBldgNo = preg_replace("/\[.*?\]\{.*?\}/", "", $bldgNo);
                    $cleanedBldgNo = trim($cleanedBldgNo);
                    
                    $FCodeResult = executeQuery($conn, "SELECT F_CODE FROM input_production WHERE is_deleted = FALSE AND is_current = TRUE AND ProductionDate = ? AND BatchName = ? AND BldgNo = ?", "sss", [$previousProductionDateStr, $BatchName, $cleanedBldgNo]);

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

                    $FCodeRow = mysqli_fetch_assoc($FCodeResult);
                    $FCode = $FCodeRow["F_CODE"] ?? "";

                    if (empty($FCode)) {
                        $FCodeResult = executeQuery($conn, "SELECT F_CODE FROM predata_ip WHERE is_deleted = FALSE AND is_current = TRUE AND ProductionDate = ? AND BatchName = ? AND BldgNo = ?", "sss", [$previousProductionDateStr, $BatchName, $cleanedBldgNo]);
                        
                        if ($FCodeResult === false) {
                            throw new Exception("Error executing query: " . $conn->error);
                        }

                        $FCodeRow = mysqli_fetch_assoc($FCodeResult);
                        $FCode = $FCodeRow["F_CODE"] ?? "";
                    }

                    $eggProdResult = executeQuery($conn, "INSERT INTO predata_ip (addedby_user, ProductionDate, BatchName, BldgNo, AgeInWeeks, Hen_Day, F_CODE) VALUES (?, ?, ?, ?, ?, ?, ?)", "sssssss", [$userName, date("Y-m-d"), $BatchName, $cleanedBldgNo, $AgeInWeeks, $per_balance, $FCode]);
                    
                    if ($eggProdResult === false) { 
                        throw new Exception("Error executing query: " . $conn->error);
                    } 
                }
            }
        }
        mysqli_commit($conn);
    } 
    catch (Exception $e) {
        mysqli_rollback($conn);
        // Log the error message
        error_log($e->getMessage());
        // Redirect to an error page or display an error message
        // header("Location: error_page.php");
        // exit();
    }
}
// END EGG PRODUCTION

// START EGG SIZE
$eggSizeResult = executeQuery($conn, "SELECT * FROM batch_details WHERE Status = 1 AND is_deleted = FALSE AND is_current = TRUE");

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

$eggSizeDetails = [];
if ($eggSizeResult->num_rows > 0)
	{
    while($yrow = $eggSizeResult->fetch_assoc()) 
	{
        $eggSizeDetails[] = $yrow;
    }
}

$existingPRE2Query = "SELECT COUNT(*) FROM predata_ew WHERE ProductionDate = ?";
$existingPRE2Result = executeQuery($conn, $existingPRE2Query, "s", [date("Y-m-d")]);

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

$existingPRE2Count = mysqli_fetch_row($existingPRE2Result)[0];

$existingPOST2Query = "SELECT COUNT(*) FROM egg_weight WHERE ProductionDate = ?";
$existingPOST2Result = executeQuery($conn, $existingPOST2Query, "s", [date("Y-m-d")]);

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

$existingPOST2Count = mysqli_fetch_row($existingPOST2Result)[0];

if ($existingPRE2Count == 0 && $existingPOST2Count == 0) 
{
    try 
	{
		foreach ($eggSizeDetails as $eggDetail) {
			$eggSizeBatchName = $eggDetail["BatchName"];
			$eggSizeBatchCode = $eggDetail["BatchCode"];
			
			$AgeResult = 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", [date("Y-m-d"),$eggSizeBatchName]);
			
			if ($AgeResult === false) { 
				throw new Exception("Error executing query: " . $conn->error);
			} 
			
			$AgeRow = mysqli_fetch_assoc($AgeResult);
			$AgeInWeeks = $AgeRow["AgeInWeeks"] ?? 0;
			
			$eggSizeResult = executeQuery($conn, "INSERT INTO predata_ew (addedby_user, ProductionDate, BatchName, BatchCode, AgeInWeeks) VALUES (?, ?, ?, ?, ?)", "sssss", [$userName, date("Y-m-d"), $eggSizeBatchName, $eggSizeBatchCode, $AgeInWeeks]);
			
			if ($eggSizeResult === false) { 
				throw new Exception("Error executing query: " . $conn->error);
			} 
			
			mysqli_commit($conn);
		}
    } 
    catch (Exception $e) 
    {
        mysqli_rollback($conn);
		// Log the error message
        error_log($e->getMessage());
		// Redirect to an error page or display an error message
        //header("Location: error_page.php");
        //exit();
    }
}
// END EGG SIZE

// START FEED DELIVERY 
$feedDeliveryResult = executeQuery($conn, "SELECT BatchName, BldgNo FROM batch_details WHERE Status = 1 AND is_deleted = FALSE AND is_current = TRUE");

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

$feedDeliveryDetails = [];
if ($feedDeliveryResult->num_rows > 0)
	{
    while($zrow = $feedDeliveryResult->fetch_assoc()) 
	{
        $feedDeliveryDetails[] = $zrow;
    }
}

$existingPRE3Query = "SELECT COUNT(*) FROM predata_fd WHERE ProductionDate = ?";
$existingPRE3Result = executeQuery($conn, $existingPRE3Query, "s", [date("Y-m-d")]);

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

$existingPRE3Count = mysqli_fetch_row($existingPRE3Result)[0];

$existingPOST3Query = "SELECT COUNT(*) FROM feed_delivery WHERE ProductionDate = ?";
$existingPOST3Result = executeQuery($conn, $existingPOST3Query, "s", [date("Y-m-d")]);

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

$existingPOST3Count = mysqli_fetch_row($existingPOST3Result)[0];

if ($existingPRE3Count == 0 && $existingPOST3Count == 0) {
    try {
        foreach ($feedDeliveryDetails as $feedDeliverydetail) {
            $BatchName = $feedDeliverydetail["BatchName"];
            $bldgNos = explode(",", $feedDeliverydetail["BldgNo"]);
			
			$AgeResult = 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", [date("Y-m-d"),$BatchName]);
			
			if ($AgeResult === false) { 
				throw new Exception("Error executing query: " . $conn->error);
			} 
			
			$AgeRow = mysqli_fetch_assoc($AgeResult);
			$AgeInWeeks = $AgeRow["AgeInWeeks"];
			
			foreach ($bldgNos as $bldgNo) {
				if (strpos($bldgNo, "{0}") === false) {
					preg_match("/\[(.*?)\]/", $bldgNo, $matches);

					$cleanedBldgNo = preg_replace("/\[.*?\]\{.*?\}/", "", $bldgNo);
					$cleanedBldgNo = trim($cleanedBldgNo);
					
					$FCodeResult = executeQuery($conn, "SELECT FCode FROM feed_delivery WHERE is_deleted = FALSE AND is_current = TRUE AND ProductionDate = ? AND BatchName = ? AND BldgNo = ?", "sss", [$previousProductionDateStr, $BatchName, $cleanedBldgNo]);

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

					$FCodeRow = mysqli_fetch_assoc($FCodeResult);
					$FCode = $FCodeRow["FCode"] ?? "";

					if (empty($FCode)) {
						$FCodeResult = executeQuery($conn, "SELECT FCode FROM predata_fd WHERE is_deleted = FALSE AND is_current = TRUE AND ProductionDate = ? AND BatchName = ? AND BldgNo = ?", "sss", [$previousProductionDateStr, $BatchName, $cleanedBldgNo]);
						
						if ($FCodeResult === false) {
							throw new Exception("Error executing query: " . $conn->error);
						}

						$FCodeRow = mysqli_fetch_assoc($FCodeResult);
						$FCode = $FCodeRow["FCode"] ?? "";
					}

					$feedDeliveryResult = executeQuery($conn, "INSERT INTO predata_fd (addedby_user, ProductionDate, BatchName, BldgNo, AgeInWeeks, FCode) VALUES (?, ?, ?, ?, ?, ?)", "ssssss", [$userName, date("Y-m-d"), $BatchName, $cleanedBldgNo, $AgeInWeeks, $FCode]);

					if ($feedDeliveryResult === false) {
						throw new Exception("Error executing query: " . $conn->error);
					}
				}
			}
        }
        mysqli_commit($conn);
    } 
    catch (Exception $e) 
    {
        mysqli_rollback($conn);
		// Log the error message
        error_log($e->getMessage());
		// Redirect to an error page or display an error message
        //header("Location: error_page.php");
        //exit();
    }
}
// END FEED DELIVERY 

// START TEMPERATURE
$timeInputs = [
	"6:00 AM",
    "8:00 AM",
    "10:00 AM",
    "2:00 PM",
    "4:30 PM",
    "6:00 PM"
];


$existingPRE4Query = "SELECT COUNT(*) FROM predata_temp WHERE ProductionDate = ?";
$existingPRE4Result = executeQuery($conn, $existingPRE4Query, "s", [date("Y-m-d")]);

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

$existingPRE4Count = mysqli_fetch_row($existingPRE4Result)[0];

$existingPOST4Query = "SELECT COUNT(*) FROM input_temp WHERE ProductionDate = ?";
$existingPOST4Result = executeQuery($conn, $existingPOST4Query, "s", [date("Y-m-d")]);

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

$existingPOST4Count = mysqli_fetch_row($existingPOST4Result)[0];

if ($existingPRE4Count == 0 && $existingPOST4Count == 0) {
    try {
        foreach ($timeInputs as $entry_time) {
            $tempResult = executeQuery($conn, "INSERT INTO predata_temp (addedby_user,ProductionDate,entry_time) VALUES (?, ?, ?)", "sss", [$userName, date("Y-m-d"), $entry_time]);
            
            if ($tempResult === false) { 
                throw new Exception("Error executing query: " . $conn->error);
            } 
        }
        mysqli_commit($conn);
    } 
    catch (Exception $e) 
    {
        mysqli_rollback($conn);
        // Log the error message
        error_log($e->getMessage());
        // Redirect to an error page or display an error message
        //header("Location: error_page.php");
        //exit();
    }
}
// END TEMPERATURE
?>