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

$currentVersion = 1;
$AddedSuccess = $UpdateSuccess = $ConfirmSuccess = false;

mysqli_begin_transaction($conn);

if (isset($_POST["AddNew_DR"])) 
{
    try
    {
        $keys = [
            "drsi_customer_name",
			"drsi_effectivity_date",
			"drsi_date_created",
            "drsi_number",
			"drsi_eggtray_pcs",
			"drsi_eggtray_charge",
            "drsi_jmb_crates",
            "drsi_jmb_trays",
            "drsi_xlr_crates",
            "drsi_xlr_trays",
            "drsi_lrg_crates",
            "drsi_lrg_trays",
            "drsi_med_crates",
            "drsi_med_trays",
            "drsi_sml_crates",
            "drsi_sml_trays",
            "drsi_pul_crates",
            "drsi_pul_trays",
            "drsi_pwe_crates",
            "drsi_pwe_trays",
            "drsi_spw_crates",
            "drsi_spw_trays",
            "drsi_b1_crates",
            "drsi_b1_trays",
            "drsi_b2_crates",
            "drsi_b2_trays",
            "drsi_b3_crates",
            "drsi_b3_trays",
            "drsi_dirty_crates",
            "drsi_dirty_trays",
            "drsi_assorted",
            "drsi_others",
            "drsi_ass_jmb_bd",
            "drsi_ass_xlr_bd",
            "drsi_ass_lrg_bd",
            "drsi_ass_med_bd",
            "drsi_ass_sml_bd",
            "drsi_ass_pul_bd",
            "drsi_ass_pwe_bd",
            "drsi_ass_spw_bd",
            "drsi_ass_b1_bd",
            "drsi_ass_b2_bd",
            "drsi_ass_b3_bd",
            "drsi_ass_dirty_bd",
            "drsi_oth_jmb_bd",
            "drsi_oth_xlr_bd",
            "drsi_oth_lrg_bd",
            "drsi_oth_med_bd",
            "drsi_oth_sml_bd",
            "drsi_oth_pul_bd",
            "drsi_oth_pwe_bd",
            "drsi_oth_spw_bd",
            "drsi_oth_b1_bd",
            "drsi_oth_b2_bd",
            "drsi_oth_b3_bd",
            "drsi_oth_dirty_bd"
        ];
        $data = getDataFromPost($keys, $_POST);
		
        $checkQuery = "SELECT * FROM transactions WHERE dr_date_created = ? AND dr_customer_name = ? AND is_deleted = FALSE AND is_current = TRUE";
        $checkResult = executeQuery($conn, $checkQuery, "ss", [$data["drsi_date_created"], $data["drsi_customer_name"]]);

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

        if ($checkResult->num_rows > 0) { 
            $row = $checkResult->fetch_assoc();
			?>
			<script>
				alert("The Delivery Receipt (DR) you are trying to create already exists in our system. DR Number: <?php echo $row["dr_number"]; ?>");
			</script>
			<?php
			exit();
        }
		
		$type = isset($data["drsi_customer_name"]) && $data["drsi_customer_name"] === "PG 7 - CEBU" ? 1 : 2;

        $result = executeQuery(
			$conn,
			"INSERT INTO transactions (
				addedby_user,
				dr_customer_name,
				dr_effectivity_date,
				dr_date_created,
				dr_number,
				dr_eggtray_pcs,
				dr_eggtray_charge,
				dr_jmb_crates,
				dr_jmb_trays,
				dr_xlr_crates,
				dr_xlr_trays,
				dr_lrg_crates,
				dr_lrg_trays,
				dr_med_crates,
				dr_med_trays,
				dr_sml_crates,
				dr_sml_trays,
				dr_pul_crates,
				dr_pul_trays,
				dr_pwe_crates,
				dr_pwe_trays,
				dr_spw_crates,
				dr_spw_trays,
				dr_b1_crates,
				dr_b1_trays,
				dr_b2_crates,
				dr_b2_trays,
				dr_b3_crates,
				dr_b3_trays,
				dr_dirty_crates,
				dr_dirty_trays,
				dr_assorted,
				dr_others,
				dr_ass_jmb_bd,
				dr_ass_xlr_bd,
				dr_ass_lrg_bd,
				dr_ass_med_bd,
				dr_ass_sml_bd,
				dr_ass_pul_bd,
				dr_ass_pwe_bd,
				dr_ass_spw_bd,
				dr_ass_b1_bd,
				dr_ass_b2_bd,
				dr_ass_b3_bd,
				dr_ass_dirty_bd,
				dr_oth_jmb_bd,
				dr_oth_xlr_bd,
				dr_oth_lrg_bd,
				dr_oth_med_bd,
				dr_oth_sml_bd,
				dr_oth_pul_bd,
				dr_oth_pwe_bd,
				dr_oth_spw_bd,
				dr_oth_b1_bd,
				dr_oth_b2_bd,
				dr_oth_b3_bd,
				dr_oth_dirty_bd,
				dr_type,
				is_deleted,
				version,
				is_current
			) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, FALSE, ?, TRUE)",
			"sssssiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii",
			[
				$userName,
				$data["drsi_customer_name"],
				$data["drsi_effectivity_date"],
				$data["drsi_date_created"],
				$data["drsi_number"],
				$data["drsi_eggtray_pcs"],
				$data["drsi_eggtray_charge"],
				$data["drsi_jmb_crates"],
				$data["drsi_jmb_trays"],
				$data["drsi_xlr_crates"],
				$data["drsi_xlr_trays"],
				$data["drsi_lrg_crates"],
				$data["drsi_lrg_trays"],
				$data["drsi_med_crates"],
				$data["drsi_med_trays"],
				$data["drsi_sml_crates"],
				$data["drsi_sml_trays"],
				$data["drsi_pul_crates"],
				$data["drsi_pul_trays"],
				$data["drsi_pwe_crates"],
				$data["drsi_pwe_trays"],
				$data["drsi_spw_crates"],
				$data["drsi_spw_trays"],
				$data["drsi_b1_crates"],
				$data["drsi_b1_trays"],
				$data["drsi_b2_crates"],
				$data["drsi_b2_trays"],
				$data["drsi_b3_crates"],
				$data["drsi_b3_trays"],
				$data["drsi_dirty_crates"],
				$data["drsi_dirty_trays"],
				$data["drsi_assorted"],
				$data["drsi_others"],
				$data["drsi_ass_jmb_bd"],
				$data["drsi_ass_xlr_bd"],
				$data["drsi_ass_lrg_bd"],
				$data["drsi_ass_med_bd"],
				$data["drsi_ass_sml_bd"],
				$data["drsi_ass_pul_bd"],
				$data["drsi_ass_pwe_bd"],
				$data["drsi_ass_spw_bd"],
				$data["drsi_ass_b1_bd"],
				$data["drsi_ass_b2_bd"],
				$data["drsi_ass_b3_bd"],
				$data["drsi_ass_dirty_bd"],
				$data["drsi_oth_jmb_bd"],
				$data["drsi_oth_xlr_bd"],
				$data["drsi_oth_lrg_bd"],
				$data["drsi_oth_med_bd"],
				$data["drsi_oth_sml_bd"],
				$data["drsi_oth_pul_bd"],
				$data["drsi_oth_pwe_bd"],
				$data["drsi_oth_spw_bd"],
				$data["drsi_oth_b1_bd"],
				$data["drsi_oth_b2_bd"],
				$data["drsi_oth_b3_bd"],
				$data["drsi_oth_dirty_bd"],
				$type,
				$currentVersion
			]
		);

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

        mysqli_commit($conn);
		
		if ($AddedSuccess) 
        {
            $_SESSION["AddedSuccess"] = true;
            
            header("Location: " . $_SERVER["REQUEST_URI"]);
            exit();
        }
    }
    catch (Exception $e) 
    {
        mysqli_rollback($conn);
        // Log the error message
        error_log($e->getMessage());
        // Redirect to an error page or display an error message
        echo "Error: " . $e->getMessage();
        // header("Location: error_page.php");
        // exit();
    }
}

if (isset($_POST["Update_DR"])) 
{
    try
    {
        $keys = [
			"drsi_eggtray_pcs",
			"drsi_eggtray_charge",
            "drsi_jmb_crates",
            "drsi_jmb_trays",
            "drsi_xlr_crates",
            "drsi_xlr_trays",
            "drsi_lrg_crates",
            "drsi_lrg_trays",
            "drsi_med_crates",
            "drsi_med_trays",
            "drsi_sml_crates",
            "drsi_sml_trays",
            "drsi_pul_crates",
            "drsi_pul_trays",
            "drsi_pwe_crates",
            "drsi_pwe_trays",
            "drsi_spw_crates",
            "drsi_spw_trays",
            "drsi_b1_crates",
            "drsi_b1_trays",
            "drsi_b2_crates",
            "drsi_b2_trays",
            "drsi_b3_crates",
            "drsi_b3_trays",
            "drsi_dirty_crates",
            "drsi_dirty_trays",
            "drsi_assorted",
            "drsi_others",
            "drsi_ass_jmb_bd",
            "drsi_ass_xlr_bd",
            "drsi_ass_lrg_bd",
            "drsi_ass_med_bd",
            "drsi_ass_sml_bd",
            "drsi_ass_pul_bd",
            "drsi_ass_pwe_bd",
            "drsi_ass_spw_bd",
            "drsi_ass_b1_bd",
            "drsi_ass_b2_bd",
            "drsi_ass_b3_bd",
            "drsi_ass_dirty_bd",
            "drsi_oth_jmb_bd",
            "drsi_oth_xlr_bd",
            "drsi_oth_lrg_bd",
            "drsi_oth_med_bd",
            "drsi_oth_sml_bd",
            "drsi_oth_pul_bd",
            "drsi_oth_pwe_bd",
            "drsi_oth_spw_bd",
            "drsi_oth_b1_bd",
            "drsi_oth_b2_bd",
            "drsi_oth_b3_bd",
            "drsi_oth_dirty_bd"
        ];
        $data = getDataFromPost($keys, $_POST);
		$result = executeQuery($conn, "SELECT * FROM transactions WHERE id = ?", "s", [$_POST["drsi_id"]]);
		
		if ($result === false) { 
			throw new Exception("Error executing query: " . $conn->error);
		}
		
		if ($result->num_rows > 0)
		{
			$currentRecord = $result->fetch_assoc();
            $id = $currentRecord["id"];
			$userName = $currentRecord["addedby_user"];
			$drcustomerName = $currentRecord["dr_customer_name"];
			$dreffectivityDate = $currentRecord["dr_effectivity_date"];
			$drDate = $currentRecord["dr_date_created"];
			$drNumber = $currentRecord["dr_number"];
			$drType = $currentRecord["dr_type"];
			
			$currentVersion = $currentRecord["version"];
			
			// Mark the current version as outdated
            $updateOldVersionQuery = "UPDATE transactions SET is_current = FALSE WHERE id = ? AND is_current = TRUE";
            $updateOldVersionResult = executeQuery($conn, $updateOldVersionQuery, "i", [$id]);
			
			if ($updateOldVersionResult === false) { 
				throw new Exception("Error executing query: " . $conn->error);
			}
			
			// Insert the new version
            $newVersion = $currentVersion + 1;
            $insertNewVersionQuery = "INSERT INTO transactions (
				addedby_user,
				dr_customer_name,
				dr_effectivity_date,
				dr_date_created,
				dr_number,
				dr_eggtray_pcs,
				dr_eggtray_charge,
				dr_jmb_crates,
				dr_jmb_trays,
				dr_xlr_crates,
				dr_xlr_trays,
				dr_lrg_crates,
				dr_lrg_trays,
				dr_med_crates,
				dr_med_trays,
				dr_sml_crates,
				dr_sml_trays,
				dr_pul_crates,
				dr_pul_trays,
				dr_pwe_crates,
				dr_pwe_trays,
				dr_spw_crates,
				dr_spw_trays,
				dr_b1_crates,
				dr_b1_trays,
				dr_b2_crates,
				dr_b2_trays,
				dr_b3_crates,
				dr_b3_trays,
				dr_dirty_crates,
				dr_dirty_trays,
				dr_assorted,
				dr_others,
				dr_ass_jmb_bd,
				dr_ass_xlr_bd,
				dr_ass_lrg_bd,
				dr_ass_med_bd,
				dr_ass_sml_bd,
				dr_ass_pul_bd,
				dr_ass_pwe_bd,
				dr_ass_spw_bd,
				dr_ass_b1_bd,
				dr_ass_b2_bd,
				dr_ass_b3_bd,
				dr_ass_dirty_bd,
				dr_oth_jmb_bd,
				dr_oth_xlr_bd,
				dr_oth_lrg_bd,
				dr_oth_med_bd,
				dr_oth_sml_bd,
				dr_oth_pul_bd,
				dr_oth_pwe_bd,
				dr_oth_spw_bd,
				dr_oth_b1_bd,
				dr_oth_b2_bd,
				dr_oth_b3_bd,
				dr_oth_dirty_bd,
				dr_type,
				version,
				is_current
			) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, TRUE)";
            $updateResult = executeQuery($conn, $insertNewVersionQuery, 
			"sssssiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiii", 
			[
				$userName, 
				$drcustomerName,
				$dreffectivityDate,
				$drDate,
				$drNumber,
				$data["drsi_eggtray_pcs"], 
				$data["drsi_eggtray_charge"], 
				$data["drsi_jmb_crates"], 
				$data["drsi_jmb_trays"], 
				$data["drsi_xlr_crates"], 
				$data["drsi_xlr_trays"], 
				$data["drsi_lrg_crates"], 
				$data["drsi_lrg_trays"], 
				$data["drsi_med_crates"], 
				$data["drsi_med_trays"], 
				$data["drsi_sml_crates"], 
				$data["drsi_sml_trays"], 
				$data["drsi_pul_crates"], 
				$data["drsi_pul_trays"], 
				$data["drsi_pwe_crates"], 
				$data["drsi_pwe_trays"], 
				$data["drsi_spw_crates"], 
				$data["drsi_spw_trays"], 
				$data["drsi_b1_crates"], 
				$data["drsi_b1_trays"], 
				$data["drsi_b2_crates"], 
				$data["drsi_b2_trays"], 
				$data["drsi_b3_crates"], 
				$data["drsi_b3_trays"], 
				$data["drsi_dirty_crates"], 
				$data["drsi_dirty_trays"], 
				$data["drsi_assorted"], 
				$data["drsi_others"], 
				$data["drsi_ass_jmb_bd"], 
				$data["drsi_ass_xlr_bd"], 
				$data["drsi_ass_lrg_bd"], 
				$data["drsi_ass_med_bd"], 
				$data["drsi_ass_sml_bd"], 
				$data["drsi_ass_pul_bd"], 
				$data["drsi_ass_pwe_bd"], 
				$data["drsi_ass_spw_bd"], 
				$data["drsi_ass_b1_bd"], 
				$data["drsi_ass_b2_bd"], 
				$data["drsi_ass_b3_bd"], 
				$data["drsi_ass_dirty_bd"], 
				$data["drsi_oth_jmb_bd"], 
				$data["drsi_oth_xlr_bd"], 
				$data["drsi_oth_lrg_bd"], 
				$data["drsi_oth_med_bd"], 
				$data["drsi_oth_sml_bd"], 
				$data["drsi_oth_pul_bd"], 
				$data["drsi_oth_pwe_bd"], 
				$data["drsi_oth_spw_bd"], 
				$data["drsi_oth_b1_bd"], 
				$data["drsi_oth_b2_bd"], 
				$data["drsi_oth_b3_bd"], 
				$data["drsi_oth_dirty_bd"], 
				$drType,
				$newVersion
			]);
			
			if ($updateResult === false) { 
				throw new Exception("Error executing query: " . $conn->error);
			} else {
				$UpdateSuccess = true;
			}

			mysqli_commit($conn);
			
			if ($UpdateSuccess) 
			{
				$_SESSION["UpdateSuccess"] = true;
				
				header("Location: " . $_SERVER["REQUEST_URI"]);
				exit();
			}
		}
    } catch (Exception $e) {
        mysqli_rollback($conn);
        // Log the error message
        error_log($e->getMessage());
        // Redirect to an error page or display an error message
        echo "Error: " . $e->getMessage();
        // header("Location: error_page.php");
        // exit();
    }
}

if (isset($_POST["Confirm_DR"]) && isset($_POST["receiver"]) && !empty($_POST["receiver"]) && isset($_POST["drsi_id"]) && !empty($_POST["drsi_id"])) {
    try {
        $result = executeQuery($conn, "SELECT * FROM transactions WHERE id = ? AND is_current = TRUE", "s", [$_POST["drsi_id"]]);
        
        if ($result === false) { 
            throw new Exception("Error executing query: " . $conn->error);
        } 
        
        if ($result->num_rows > 0) {
            $row = $result->fetch_assoc();
            $id = $row["id"];
            $receiver = isset($_POST["receiver"]) ? $_POST["receiver"] : null;
            $remarks = isset($_POST["remarks"]) ? $_POST["remarks"] : null;
			
            if ($row["dr_type"] === 1) {
                $updateQuery = "UPDATE transactions SET lastupdateby_user = CURRENT_TIMESTAMP, dr_status = 2, received_date = ?, receiver = ?, remarks = ? WHERE id = ? AND is_current = TRUE";
                $updateResult = executeQuery($conn, $updateQuery, "sssi", [date("Y-m-d"), $receiver, $remarks, $id]);
                
                if ($updateResult === false) { 
                    throw new Exception("Error executing query: " . $conn->error);
                } else {
                    $ConfirmSuccess = true;
                }

                mysqli_commit($conn);
                
                if ($ConfirmSuccess) {
                    $_SESSION["ConfirmSuccess"] = true;
                    
                    header("Location: " . $_SERVER["REQUEST_URI"]);
                    exit();
                }
            } else {
                $si_number = "SI-" . date("Ymd") . "-" . $id;
			
				$updateQuery = "UPDATE transactions SET lastupdateby_user = CURRENT_TIMESTAMP, dr_status = 2, si_number = ?, received_date = ?, receiver = ?, remarks = ? WHERE id = ? AND is_current = TRUE";
				$updateResult = executeQuery($conn, $updateQuery, "ssssi", [$si_number, date("Y-m-d"), $receiver, $remarks, $id]);
				
				if ($updateResult === false) { 
					throw new Exception("Error executing query: " . $conn->error);
				} else {
					$ConfirmSuccess = true;
				}

				mysqli_commit($conn);
				
				if ($ConfirmSuccess) 
				{
					$_SESSION["ConfirmSuccess"] = true;
					
					header("Location: " . $_SERVER["REQUEST_URI"]);
					exit();
				}
            }
        }
    } catch (Exception $e) {
        mysqli_rollback($conn);
        // Log the error message
        error_log($e->getMessage());
        // Redirect to an error page or display an error message
		echo "Error: " . $e->getMessage();
        //header("Location: error_page.php");
        //exit();
    }   
}

if (isset($_SESSION["AddedSuccess"])) 
{
    ?>
    <script>
        alert( "Added Successfully." );
    </script>
    <?php
    unset($_SESSION["AddedSuccess"]);
}

if (isset($_SESSION["UpdateSuccess"])) 
{
    ?>
    <script>
        alert( "Updated Successfully." );
    </script>
    <?php
    unset($_SESSION["UpdateSuccess"]);
}

if (isset($_SESSION["ConfirmSuccess"])) 
{
    ?>
    <script>
        alert( "Confirmed Successfully." );
    </script>
    <?php
    unset($_SESSION["ConfirmSuccess"]);
}
?>