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

// Step 1: Fetch all product_names from sell_data table

$b_id = $browserId;
// $b_id = "9419ebb50608d2b9fb7f92b4c3c6bee61722859299";



// $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"];

//     $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($query)) {
//         die("ERROR: Query failed. " . $connection->error);
//     }


// //From Trans
// $sql = "SELECT SUM(qty) AS total_pieces_qty,product_name FROM ".$cargo_data."_trans WHERE type = 'ဗူး/ခု' GROUP BY product_name";
// $result = $connection->query($sql);
// if ($result && $result->num_rows > 0) {
//     while ($row = $result->fetch_assoc()) {
//         $pieces_sell = $row['total_pieces_qty'];
//         $product_name = $row['product_name'];
//         $query = "UPDATE ".$cargo_data." set pieces_sell='$pieces_sell' WHERE product_name='$product_name'";
//         if (!$connection->query($query)) {
//             die("ERROR: Query failed. " . $connection->error);
//         }
//     }
// }

// $sql = "SELECT SUM(qty) AS total_unit_qty,product_name FROM ".$cargo_data."_trans WHERE type = 'ကဒ်' GROUP BY product_name";
// $result = $connection->query($sql);
// if ($result && $result->num_rows > 0) {
//     while ($row = $result->fetch_assoc()) {
//         $unit_sell = $row['total_unit_qty'];
//         $product_name = $row['product_name'];
//         $query = "UPDATE ".$cargo_data." set unit_sell='$unit_sell' WHERE product_name='$product_name'";
//         if (!$connection->query($query)) {
//             die("ERROR: Query failed. " . $connection->error);
//         }
//     }
// }

// $sql = "SELECT SUM(qty) AS total_package_qty,product_name FROM ".$cargo_data."_trans WHERE type = 'ဖာ' GROUP BY product_name";
// $result = $connection->query($sql);
// if ($result && $result->num_rows > 0) {
//     while ($row = $result->fetch_assoc()) {
//         $package_sell = $row['total_package_qty'];
//         $product_name = $row['product_name'];
//         $query = "UPDATE ".$cargo_data." set package_sell='$package_sell' WHERE product_name='$product_name'";
//         if (!$connection->query($query)) {
//             die("ERROR: Query failed. " . $connection->error);
//         }
//     }
// }

// //From Proudct_damage
// $sql = "SELECT SUM(qty) AS total_package_qty,product_name FROM ".$cargo_data."_damage WHERE type = 'ဖာ' GROUP BY product_name";
// $result = $connection->query($sql);
// if ($result && $result->num_rows > 0) {
//     while ($row = $result->fetch_assoc()) {
//         $package_damage = $row['total_package_qty'];
//         $product_name = $row['product_name'];
//         $query = "UPDATE ".$cargo_data." set package_damage='$package_damage' WHERE product_name='$product_name'";
//         if (!$connection->query($query)) {
//             die("ERROR: Query failed. " . $connection->error);
//         }
//     }
// }

// $sql = "SELECT SUM(qty) AS total_unit_qty,product_name FROM ".$cargo_data."_damage WHERE type = 'ကဒ်' GROUP BY product_name";
// $result = $connection->query($sql);
// if ($result && $result->num_rows > 0) {
//     while ($row = $result->fetch_assoc()) {
//         $unit_damage = $row['total_unit_qty'];
//         $product_name = $row['product_name'];
//         $query = "UPDATE ".$cargo_data." set unit_damage='$unit_damage' WHERE product_name='$product_name'";
//         if (!$connection->query($query)) {
//             die("ERROR: Query failed. " . $connection->error);
//         }
//     }
// }

// $sql = "SELECT SUM(qty) AS total_pieces_qty,product_name FROM ".$cargo_data."_damage WHERE type = 'ဗူး/ခု' GROUP BY product_name";
// $result = $connection->query($sql);
// if ($result && $result->num_rows > 0) {
//     while ($row = $result->fetch_assoc()) {
//         $pieces_sell = $row['total_pieces_qty'];
//         $product_name = $row['product_name'];
//         $query = "UPDATE ".$cargo_data." set pieces_damage='$pieces_sell' WHERE product_name='$product_name'";
//         if (!$connection->query($query)) {
//             die("ERROR: Query failed. " . $connection->error);
//         }
//     }
// }

// //Remain from Refill
// $sql = "SELECT SUM(qty) AS total_pieces_qty,product_name FROM ".$cargo_data."_refill WHERE type = 'ဗူး/ခု' GROUP BY product_name";
// $result = $connection->query($sql);
// if ($result && $result->num_rows > 0) {
//     while ($row = $result->fetch_assoc()) {
//         $pieces_sell = $row['total_pieces_qty'];
//         $product_name = $row['product_name'];
        
//         $query = "UPDATE ".$cargo_data." set pieces_remain='$pieces_sell' WHERE product_name='$product_name'";
//         if (!$connection->query($query)) {
//             die("ERROR: Query failed. " . $connection->error);
//         }
//     }
// }

// $sql = "SELECT SUM(qty) AS total_unit_qty,product_name FROM ".$cargo_data."_refill WHERE type = 'ကဒ်' GROUP BY product_name";
// $result = $connection->query($sql);
// if ($result && $result->num_rows > 0) {
//     while ($row = $result->fetch_assoc()) {
//         $unit_sell = $row['total_unit_qty'];
//         $product_name = $row['product_name'];
//         $query = "UPDATE ".$cargo_data." set unit_remain='$unit_sell' WHERE product_name='$product_name'";
//         if (!$connection->query($query)) {
//             die("ERROR: Query failed. " . $connection->error);
//         }
//     }
// }

// $sql = "SELECT SUM(qty) AS total_package_qty,product_name FROM ".$cargo_data."_refill WHERE type = 'ဖာ' GROUP BY product_name";
// $result = $connection->query($sql);
// if ($result && $result->num_rows > 0) {
//     while ($row = $result->fetch_assoc()) {
//         $package_sell = $row['total_package_qty'];
//         $product_name = $row['product_name'];
//         $query = "UPDATE ".$cargo_data." set package_remain='$package_sell' WHERE product_name='$product_name'";
//         if (!$connection->query($query)) {
//             die("ERROR: Query failed. " . $connection->error);
//         }
//     }
// }

// //from product_change
// $sql = "SELECT SUM(unit) AS total_change_unit,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_unit = $row['total_change_unit'];
//         $product_name = $row['name'];
        
//         $query = "UPDATE ".$cargo_data." set unit_change='$total_change_unit' WHERE product_name='$product_name'";
//         if (!$connection->query($query)) {
//             die("ERROR: Query failed. " . $connection->error);
//         }
//     }
// }

// $sql = "SELECT SUM(pieces) AS total_change_pieces,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_pieces = $row['total_change_pieces'];
//         $product_name = $row['name'];
//         $query = "UPDATE ".$cargo_data." set pieces_change='$total_change_pieces' WHERE product_name='$product_name'";
//         if (!$connection->query($query)) {
//             die("ERROR: Query failed. " . $connection->error);
//         }
//     }
// }



// $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($query)) {
//     die("ERROR: Query failed. " . $connection->error);
// }

//                 }

$connection->begin_transaction(); // Start a transaction

// 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
    $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.";
}

                // function generateUniqueBrowserId()
                // {
                //     $randomString = bin2hex(random_bytes(16));
                //     $timestamp = time();
                
                //     return $randomString . $timestamp;
                // }
                ?>


