
<?php 

include "database/database.php";
include "database/data.php";

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

// // Fetch product names in sell_data table
// $product_name_list = [];
// $sql = "SELECT product_name FROM " . $sell_data;
// $result = $connection->query($sql);

// if ($result && $result->num_rows > 0) {
//     while ($row = $result->fetch_assoc()) {
//         $product_name_list[] = $row['product_name'];
//     }
// }

// // Prepare the product name list string for the IN clause
// if (!empty($product_name_list)) {
//     $product_name_list_string = "'" . implode("','", array_map([$connection, 'real_escape_string'], $product_name_list)) . "'";
//     $update_query = "
//         UPDATE $product
//         SET package_sell = '0', unit_sell = '0', pieces_sell = '0',
//             package_exist = '0', unit_exist = '0', pieces_exist = '0',
//             package_remain = '0', unit_remain = '0', pieces_remain = '0',
//             package_damage = '0', unit_damage = '0', pieces_damage = '0',
//             package_change = '0', unit_change = '0', pieces_change = '0',
//             package_trans = '0', unit_trans = '0', pieces_trans = '0'
//         WHERE product_name NOT IN ($product_name_list_string)
//     ";

//     if (!$connection->query($update_query)) {
//         $connection->rollback();
//         die("ERROR: Update query failed. " . $connection->error . " | Query: " . $update_query);
//     }
// } else {
//     $update_query = "
//         UPDATE $product
//         SET package_sell = '0', unit_sell = '0', pieces_sell = '0',
//             package_exist = '0', unit_exist = '0', pieces_exist = '0',
//             package_remain = '0', unit_remain = '0', pieces_remain = '0',
//             package_damage = '0', unit_damage = '0', pieces_damage = '0',
//             package_change = '0', unit_change = '0', pieces_change = '0',
//             package_trans = '0', unit_trans = '0', pieces_trans = '0'
//     ";

//     if (!$connection->query($update_query)) {
//         $connection->rollback();
//         die("ERROR: Update query failed. " . $connection->error . " | Query: " . $update_query);
//     }
// }

// // Commit the transaction for this section
// $connection->commit();
// $connection->begin_transaction(); // Start a new transaction for the next set of queries

// // Optimize by batching multiple updates into a single query per case if possible
// $queries = [
//     // Update pieces_sell, unit_sell, package_sell
//     "UPDATE $product p
//      LEFT JOIN (SELECT product_name, SUM(CASE WHEN type='ဗူး/ခု' THEN qty ELSE 0 END) AS pieces_sell,
//                         SUM(CASE WHEN type='ကဒ်' THEN qty ELSE 0 END) AS unit_sell,
//                         SUM(CASE WHEN type='ဖာ' THEN qty ELSE 0 END) AS package_sell
//                  FROM $sell_data
//                  GROUP BY product_name) s 
//      ON p.product_name = s.product_name
//      SET p.pieces_sell = COALESCE(s.pieces_sell, 0),
//          p.unit_sell = COALESCE(s.unit_sell, 0),
//          p.package_sell = COALESCE(s.package_sell, 0)",

//     // Update package_damage, unit_damage, pieces_damage
//     "UPDATE $product p
//      LEFT JOIN (SELECT product_name, SUM(CASE WHEN type='ဖာ' THEN qty ELSE 0 END) AS package_damage,
//                         SUM(CASE WHEN type='ကဒ်' THEN qty ELSE 0 END) AS unit_damage,
//                         SUM(CASE WHEN type='ဗူး' THEN qty ELSE 0 END) AS pieces_damage
//                  FROM $product_damage
//                  GROUP BY product_name) d 
//      ON p.product_name = d.product_name
//      SET p.package_damage = COALESCE(d.package_damage, 0),
//          p.unit_damage = COALESCE(d.unit_damage, 0),
//          p.pieces_damage = COALESCE(d.pieces_damage, 0)",

//     // Update pieces_remain, unit_remain, package_remain
//     "UPDATE $product p
//      LEFT JOIN (SELECT product_name, SUM(CASE WHEN type='pieces' THEN qty ELSE 0 END) AS pieces_remain,
//                         SUM(CASE WHEN type='unit' THEN qty ELSE 0 END) AS unit_remain,
//                         SUM(CASE WHEN type='package' THEN qty ELSE 0 END) AS package_remain
//                  FROM $product_refill
//                  GROUP BY product_name) r 
//      ON p.product_name = r.product_name
//      SET p.pieces_remain = COALESCE(r.pieces_remain, 0),
//          p.unit_remain = COALESCE(r.unit_remain, 0),
//          p.package_remain = COALESCE(r.package_remain, 0)",

//     // Update unit_change, pieces_change
//     "UPDATE $product p
//      LEFT JOIN (SELECT name, SUM(unit) AS total_unit, SUM(pieces) AS total_pieces 
//                  FROM $product_change
//                  GROUP BY name) c 
//      ON p.product_name = c.name
//      SET p.unit_change = COALESCE(c.total_unit, 0),
//          p.pieces_change = COALESCE(c.total_pieces, 0)",

//     // Update pieces_trans, unit_trans, package_trans
//     "UPDATE $product p
//      LEFT JOIN (SELECT product_name, SUM(CASE WHEN type='ဗူး/ခု' THEN qty ELSE 0 END) AS pieces_trans,
//                         SUM(CASE WHEN type='ကဒ်' THEN qty ELSE 0 END) AS unit_trans,
//                         SUM(CASE WHEN type='ဖာ' THEN qty ELSE 0 END) AS package_trans
//                  FROM {$cargo_1}_trans
//                  WHERE accept != 'none'
//                  GROUP BY product_name) t 
//      ON p.product_name = t.product_name
//      SET p.pieces_trans = COALESCE(t.pieces_trans, 0),
//          p.unit_trans = COALESCE(t.unit_trans, 0),
//          p.package_trans = COALESCE(t.package_trans, 0)"
// ];

// foreach ($queries as $query) {
//     if (!$connection->query($query)) {
//         $connection->rollback(); // Rollback transaction on failure
//         die("ERROR: Query failed. " . $connection->error . " | Query: " . $query);
//     }
// }

// $final_update_query = "UPDATE $product SET 
//     package_exist = (package_remain + package_trans) - (package_sell + package_damage + unit_change),
//     unit_exist = (unit_remain + unit_trans) - (unit_sell + unit_damage) + (unit_change * unit) - pieces_change,
//     pieces_exist = (pieces_remain + pieces_trans) - (pieces_sell + pieces_damage) + (pieces_change * pieces)";

// if (!$connection->query($final_update_query)) {
//     $connection->rollback(); // Rollback transaction on failure
//     die("ERROR: Final Update Query failed. " . $connection->error . " | Query: " . $final_update_query);
// }

// $connection->commit(); // Final commit


?>
