<?php 
include '../database/database.php';
include '../database/data.php';



$b_id = $browserId;


$connection->begin_transaction(); 


$sql = "SELECT * FROM ".$cargo." WHERE browser_id='$b_id'";
$result = $connection->query($sql);
if ($result->num_rows > 0) {
    $row = $result->fetch_assoc(); // Fetch the row here
    $cargo_data = $row["cargo_name"];

    // Step 2: Reset values in the cargo_data table
    $reset_query = "UPDATE ".$cargo_data." 
                    SET package_remain='0', unit_remain='0', pieces_remain='0',
                        package_sell='0', unit_sell='0', pieces_sell='0',
                        package_damage='0', unit_damage='0', pieces_damage='0',
                        package_change='0', unit_change='0', pieces_change='0'";
    if (!$connection->query($reset_query)) {
        $connection->rollback();
        die("ERROR: Query failed. " . $connection->error . " | Query: " . $reset_query);
    }

    // Step 3: Update pieces_sell, unit_sell, package_sell from Trans
    $trans_types = [
        'ဗူး/ခု' => 'pieces_sell',
        'ကဒ်' => 'unit_sell',
        'ဖာ' => 'package_sell'
    ];

    foreach ($trans_types as $type => $column) {
        $sql = "SELECT SUM(qty) AS total_qty, product_name FROM ".$cargo_data."_trans WHERE type = '$type' GROUP BY product_name";
        $result = $connection->query($sql);
        if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $total_qty = $row['total_qty'];
                $product_name = $row['product_name'];
                $update_query = "UPDATE ".$cargo_data." SET $column='$total_qty' WHERE product_name='$product_name'";
                if (!$connection->query($update_query)) {
                    $connection->rollback();
                    die("ERROR: Query failed. " . $connection->error . " | Query: " . $update_query);
                }
            }
        }
    }

    // Step 4: Update package_damage, unit_damage, pieces_damage from Product_damage
    $damage_types = [
        'ဗူး/ခု' => 'pieces_damage',
        'ကဒ်' => 'unit_damage',
        'ဖာ' => 'package_damage'
    ];

    foreach ($damage_types as $type => $column) {
        $sql = "SELECT SUM(qty) AS total_qty, product_name FROM ".$cargo_data."_damage WHERE type = '$type' GROUP BY product_name";
        $result = $connection->query($sql);
        if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $total_qty = $row['total_qty'];
                $product_name = $row['product_name'];
                $update_query = "UPDATE ".$cargo_data." SET $column='$total_qty' WHERE product_name='$product_name'";
                if (!$connection->query($update_query)) {
                    $connection->rollback();
                    die("ERROR: Query failed. " . $connection->error . " | Query: " . $update_query);
                }
            }
        }
    }

    // Step 5: Update pieces_remain, unit_remain, package_remain from Refill
    $refill_types = [
        'ဗူး/ခု' => 'pieces_remain',
        'ကဒ်' => 'unit_remain',
        'ဖာ' => 'package_remain'
    ];

    foreach ($refill_types as $type => $column) {
        $sql = "SELECT SUM(qty) AS total_qty, product_name FROM ".$cargo_data."_refill WHERE type = '$type' GROUP BY product_name";
        $result = $connection->query($sql);
        if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $total_qty = $row['total_qty'];
                $product_name = $row['product_name'];
                $update_query = "UPDATE ".$cargo_data." SET $column='$total_qty' WHERE product_name='$product_name'";
                if (!$connection->query($update_query)) {
                    $connection->rollback();
                    die("ERROR: Query failed. " . $connection->error . " | Query: " . $update_query);
                }
            }
        }
    }

    // Step 6: Update unit_change, pieces_change from Product_change
    $change_types = [
        'unit' => 'unit_change',
        'pieces' => 'pieces_change'
    ];

    foreach ($change_types as $type => $column) {
        $sql = "SELECT SUM($type) AS total_change, name FROM ".$cargo_data."_change GROUP BY name";
        $result = $connection->query($sql);
        if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $total_change = $row['total_change'];
                $product_name = $row['name'];
                $update_query = "UPDATE ".$cargo_data." SET $column='$total_change' WHERE product_name='$product_name'";
                if (!$connection->query($update_query)) {
                    $connection->rollback();
                    die("ERROR: Query failed. " . $connection->error . " | Query: " . $update_query);
                }
            }
        }
    }

    // Step 7: Final calculation of exist values
    $final_update_query = "UPDATE ".$cargo_data." SET 
                    package_exist=package_remain - (package_sell + package_damage + unit_change),
                    unit_exist=unit_remain - (unit_sell + unit_damage) + (unit_change * unit) - pieces_change,
                    pieces_exist=pieces_remain - (pieces_sell + pieces_damage) + (pieces_change * pieces)";
    if (!$connection->query($final_update_query)) {
        $connection->rollback();
        die("ERROR: Query failed. " . $connection->error . " | Query: " . $final_update_query);
    }

    $connection->commit(); // Commit the transaction
    // echo "Update successful.";
}

                ?>


