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

mysqli_begin_transaction($conn);

if (isset($_POST["AddNew_PL"])) 
{
    try
    {
        $keys = [
            "pl_effectivity_date",
            "pl_customer_name",
            "pl_eggtray_charge",
			"pl_shipping_charge",
			"pl_other_charges",
			"pl_other_charges_remark",
            "pl_JMB",
            "pl_XLR",
            "pl_LRG",
            "pl_MED",
            "pl_SML",
            "pl_PUL",
            "pl_PWE",
            "pl_SPW",
            "pl_B1",
            "pl_B2",
            "pl_B3",
            "pl_Dirty"
        ];
        $data = getDataFromPost($keys, $_POST);
		
        $checkQuery = "SELECT * FROM pricelist WHERE pl_customer_name = ? AND pl_effectivity_date = ? AND is_deleted = FALSE AND is_current = TRUE";
        $checkResult = executeQuery($conn, $checkQuery, "ss", [$data["pl_customer_name"], $data["pl_effectivity_date"]]);

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

		if ($checkResult->num_rows > 0) { 
            ?>
			<script>
				alert( "This Pricelist already exists in our system. Please check" );
			</script>
			<?php
			exit();
        }

        $result = executeQuery($conn, "INSERT INTO pricelist (addedby_user,pl_effectivity_date,pl_customer_name,pl_eggtray_charge,pl_shipping_charge,pl_other_charges,pl_other_charges_remark,pl_JMB,pl_XLR,pl_LRG,pl_MED,pl_SML,pl_PUL,pl_PWE,pl_SPW,pl_B1,pl_B2,pl_B3,pl_Dirty) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", "sssssssiiiiiiiiiiii", [$userName, $data["pl_effectivity_date"], $data["pl_customer_name"], $data["pl_eggtray_charge"], $data["pl_shipping_charge"], $data["pl_other_charges"], $data["pl_other_charges_remark"], $data["pl_JMB"], $data["pl_XLR"], $data["pl_LRG"], $data["pl_MED"], $data["pl_SML"], $data["pl_PUL"], $data["pl_PWE"], $data["pl_SPW"], $data["pl_B1"], $data["pl_B2"], $data["pl_B3"], $data["pl_Dirty"]]);
        
		if ($result === false) { 
			throw new Exception("Error executing query: " . $conn->error);
		} 
    
        mysqli_commit($conn);
		
		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
        //header("Location: error_page.php");
        //exit();
    }
}

if (isset($_POST["Update_PL"])) 
{
    try
    {
        $keys = [
            "pl_effectivity_date",
            "pl_customer_name",
            "pl_eggtray_charge",
			"pl_shipping_charge",
			"pl_other_charges",
			"pl_other_charges_remark",
            "pl_JMB",
            "pl_XLR",
            "pl_LRG",
            "pl_MED",
            "pl_SML",
            "pl_PUL",
            "pl_PWE",
            "pl_SPW",
            "pl_B1",
            "pl_B2",
            "pl_B3",
            "pl_Dirty"
        ];
        $data = getDataFromPost($keys, $_POST);
		$result = executeQuery($conn, "SELECT * FROM pricelist WHERE id = ?", "s", [$_POST["pl_id"]]);
		
		if ($result === false) { 
			throw new Exception("Error executing query: " . $conn->error);
		}
		
		if ($result->num_rows > 0)
		{
			$currentRecord = $result->fetch_assoc();
            $id = $currentRecord["id"];
			$currentVersion = $currentRecord["version"];
			
			// Mark the current version as outdated
            $updateOldVersionQuery = "UPDATE pricelist 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 pricelist (addedby_user,pl_effectivity_date,pl_customer_name,pl_eggtray_charge,pl_shipping_charge,pl_other_charges,pl_other_charges_remark,pl_JMB,pl_XLR,pl_LRG,pl_MED,pl_SML,pl_PUL,pl_PWE,pl_SPW,pl_B1,pl_B2,pl_B3,pl_Dirty,version,is_current) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, TRUE)";
            $updateResult = executeQuery($conn, $insertNewVersionQuery, "sssssssiiiiiiiiiiiii", [$userName, $data["pl_effectivity_date"], $data["pl_customer_name"], $data["pl_eggtray_charge"], $data["pl_shipping_charge"], $data["pl_other_charges"], $data["pl_other_charges_remark"], $data["pl_JMB"], $data["pl_XLR"], $data["pl_LRG"], $data["pl_MED"], $data["pl_SML"], $data["pl_PUL"], $data["pl_PWE"], $data["pl_SPW"], $data["pl_B1"], $data["pl_B2"], $data["pl_B3"], $data["pl_Dirty"], $newVersion]);
			
			if ($updateResult === false) { 
				throw new Exception("Error executing query: " . $conn->error);
			} 
			
			mysqli_commit($conn);

			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();
    }
}
?>