<?php 

include('db_connection.php');
$Mainclass=new DB_Controller();

$today = date('Y-m-d');


// 1. Fetch all gold rates for the day in one query
$gold_rates = [];
$rate_sql = mysqli_query($Mainclass->dbh, "SELECT Karat, Selling_Rate FROM gold_rate_master WHERE DATE(Entry_Date) = '".$today."'");
while ($rate_row = mysqli_fetch_assoc($rate_sql)) {
    $gold_rates[$rate_row['Karat']] = $rate_row['Selling_Rate'];
}



// 2. Fetch all products in one query
$sql = mysqli_query($Mainclass->dbh, "SELECT SLN, GoldPurity, GoldWeight, MakingChargesType, MakingChargesValue FROM gold_products_master WHERE Active='Yes' AND Stock>0 AND Metal_Type='GOLD'");

$updates = [];
while ($Ft_Product = mysqli_fetch_assoc($sql)) {
    $SLN = $Ft_Product['SLN'];
    $Gold_Purity = $Ft_Product['GoldPurity'];
    $Gold_Weight = $Ft_Product['GoldWeight'];
    $MakingChargesType = $Ft_Product['MakingChargesType'];
    $MakingChargesValue = $Ft_Product['MakingChargesValue'];

    $Making_Charge = 0;

    // Check if the gold rate for the purity is available
    if (isset($gold_rates[$Gold_Purity])) {
        $Gold_rate_value = $gold_rates[$Gold_Purity];

        if ($MakingChargesType == 'Flat') {
            $Making_Charge = $MakingChargesValue;
        } elseif ($MakingChargesType == 'Percentage') {
            $Total_Gold_Value = $Gold_rate_value * $Gold_Weight;
            $Making_Charge = ($Total_Gold_Value * $MakingChargesValue) / 100;
        } elseif ($MakingChargesType == 'Rate per gram') {
            $Making_Charge = $Gold_Weight * $MakingChargesValue;
        }
    }
//$Making_Charge."-".$MakingChargesType;

    // Add the update to an array to be processed in a single query
    $updates[] = "WHEN SLN = '".$SLN."' THEN '".$Making_Charge."'";
}
 //echo "<pre>";
 //print_r($updates);
 //echo "</pre>";
// 3. Construct and execute a single bulk UPDATE query using CASE
if (!empty($updates)) {
    $update_query = "UPDATE gold_products_master SET current_making_charge = CASE " . implode(" ", $updates) . " ELSE current_making_charge END";
    $update_query .= " WHERE SLN IN (" . implode(", ", array_map(function($update) {
        preg_match("/SLN = '(\d+)'/", $update, $matches);
        return "'" . $matches[1] . "'";
    }, $updates)) . ")";

    // You would use a prepared statement here in a production environment for security
    $Update_Product = mysqli_query($Mainclass->dbh, $update_query);

    if ($Update_Product) {
        echo "Successfully updated making charges for " . mysqli_affected_rows($Mainclass->dbh) . " products.";
    } else {
        echo "Error updating records: " . mysqli_error($Mainclass->dbh);
    }
} else {
    echo "No products to update.";
}

echo "Done";

?>