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

$connection->begin_transaction(); // Start a transaction
$b_id = $browserId;
// $b_id = '5012dda47785271947c14dd0d47950701731213137';
// Step 1: Retrieve cargo data
$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 (using JOIN)
    $sql = "UPDATE ".$cargo_data." c
        JOIN (
            SELECT type, product_name, SUM(qty) AS total_qty
            FROM ".$cargo_data."_trans 
            WHERE type IN ('ဗူး/ခု', 'ကဒ်', 'ဖာ') 
            GROUP BY type, product_name
        ) t ON c.product_name = t.product_name
        SET c.pieces_sell = IF(t.type = 'ဗူး/ခု', t.total_qty, c.pieces_sell),
            c.unit_sell = IF(t.type = 'ကဒ်', t.total_qty, c.unit_sell),
            c.package_sell = IF(t.type = 'ဖာ', t.total_qty, c.package_sell)";

if (!$connection->query($sql)) {
    // Show detailed error message
    echo "ERROR: Query failed. " . $connection->error . "<br>";
    echo "Query: " . $sql . "<br>";
    echo "Error Code: " . $connection->errno;
    $connection->rollback();
    die();
}


    // Step 4: Update package_damage, unit_damage, pieces_damage from Product_damage (using JOIN)
    $sql = "UPDATE ".$cargo_data." c
            JOIN (
                SELECT type, product_name, SUM(qty) AS total_qty
                FROM ".$cargo_data."_damage
                WHERE type IN ('ဗူး/ခု', 'ကဒ်', 'ဖာ') 
                GROUP BY type, product_name
            ) d ON c.product_name = d.product_name
            SET c.pieces_damage = IF(d.type = 'ဗူး/ခု', d.total_qty, c.pieces_damage),
                c.unit_damage = IF(d.type = 'ကဒ်', d.total_qty, c.unit_damage),
                c.package_damage = IF(d.type = 'ဖာ', d.total_qty, c.package_damage)";
    if (!$connection->query($sql)) {
        $connection->rollback();
        die("ERROR: Query failed. " . $connection->error . " | Query: " . $sql);
    }

    // Step 5: Update pieces_remain, unit_remain, package_remain from Refill (using JOIN)
    $sql = "UPDATE ".$cargo_data." c
            JOIN (
                SELECT type, product_name, SUM(qty) AS total_qty
                FROM ".$cargo_data."_refill
                WHERE type IN ('ဗူး/ခု', 'ကဒ်', 'ဖာ') 
                GROUP BY type, product_name
            ) r ON c.product_name = r.product_name
            SET c.pieces_remain = IF(r.type = 'ဗူး/ခု', r.total_qty, c.pieces_remain),
                c.unit_remain = IF(r.type = 'ကဒ်', r.total_qty, c.unit_remain),
                c.package_remain = IF(r.type = 'ဖာ', r.total_qty, c.package_remain)";
    if (!$connection->query($sql)) {
        $connection->rollback();
        die("ERROR: Query failed. " . $connection->error . " | Query: " . $sql);
    }

    // Step 6: Update unit_change, pieces_change from Product_change (using JOIN)
    $sql = "UPDATE ".$cargo_data." c
            JOIN (
                SELECT name, SUM(unit) AS total_unit_change, SUM(pieces) AS total_pieces_change
                FROM ".$cargo_data."_change
                GROUP BY name
            ) ch ON c.product_name = ch.name
            SET c.unit_change = ch.total_unit_change,
                c.pieces_change = ch.total_pieces_change";
    if (!$connection->query($sql)) {
        $connection->rollback();
        die("ERROR: Query failed. " . $connection->error . " | Query: " . $sql);
    }

    // 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.";
} else {
    $connection->rollback();
    die("ERROR: No data found for cargo.");
}

                ?>


