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

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $id = $_POST['id'];
    $column = $_POST['column'];
    $value = $_POST['value'];

    

    // Sanitize inputs
    $id = $connection->real_escape_string($id);
    $column = $connection->real_escape_string($column);
    $value = $connection->real_escape_string($value);

    // Update the $user_1_lk table
    $sql = "UPDATE $user_1_lk SET $column = ? WHERE tra_id = ?";
    $stmt = $connection->prepare($sql);
    if ($stmt) {
        $stmt->bind_param('si', $value, $id); // Bind parameters
        if ($stmt->execute()) {
            echo 'Update successful for user_1_lk<br>';
        } else {
            echo 'Error updating user_1_lk: ' . $stmt->error . '<br>';
        }
        $stmt->close(); // Close the statement
    } else {
        echo 'Error preparing statement for user_1_lk: ' . $connection->error . '<br>';
    }

    // Update the $sell_data table
    $sql = "UPDATE $sell_data SET $column = ? WHERE tra_id = ?";
    $stmt = $connection->prepare($sql);
    if ($stmt) {
        $stmt->bind_param('si', $value, $id); // Bind parameters
        if ($stmt->execute()) {
            echo 'Update successful for sell_data<br>';
        } else {
            echo 'Error updating sell_data: ' . $stmt->error . '<br>';
        }
        $stmt->close(); // Close the statement
    } else {
        echo 'Error preparing statement for sell_data: ' . $connection->error . '<br>';
    }

    //if rate change
    if ($column=='rate'){ 
        $sql = "SELECT * FROM ".$product." WHERE product_name='$product_name'";
        $result = $connection->query($sql);
        if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $package_price = $row['round_package_price'];
            $unit_price = $row['round_unit_price'];
            $pieces_price = $row['round_pieces_price'];

            $sql = "SELECT * FROM ".$user_1_lk." WHERE tra_id='$id'";
            $result = $connection->query($sql);
            if ($result && $result->num_rows > 0) {
            while ($row = $result->fetch_assoc()) {
                $type = $row['type'];
                $qty = $row['qty'];
                $discount = $row['discount'];
                switch ($type) {
                    case 'ဗူး/ခု':
                        $price = round($row['round_pieces_price'] / $row['rate']);
                        break;
                    case 'ကဒ်':
                        $price = round($row['round_unit_price'] / $row['rate']);
                        break;
                    case 'ဖာ':
                        $price = round($row['round_package_price'] / $row['rate']);
                        break;
                    default:
                        echo 'Unknown type: ' . $type . '<br>';
                        $price = 0;
                        break;
                }
                $total = $qty * ($price - $discount);



                }
            }


    
            $query = "UPDATE ".$user_id."  set discount='$discount',price='$price',qty='$qty', type='$type', total='$total_f' WHERE tra_id='$tra_id'";
            if (!$connection->query($query)) {
                die("ERROR: Query failed. " . $connection->error);
            }
            
            $query = "UPDATE ".$sell_data." set discount='$discount',price='$price',qty='$qty', type='$type', total='$total_f' WHERE tra_id='$tra_id'";
            if (!$connection->query($query)) {
                die("ERROR: Query failed. " . $connection->error);
            }

        }
    }
        
    }

    // Fetch and calculate prices/discounts
    $sql = "
    SELECT 
        u.tra_id, u.banking, u.product_name, u.type, u.qty, u.discount, u.rate, 
        p.round_package_price, p.round_unit_price, p.round_pieces_price
    FROM 
        $user_1_lk u
    JOIN 
        $product p 
    ON 
        u.product_name = p.product_name 
    WHERE 
        u.tra_id = ?";
    
    $stmt = $connection->prepare($sql);
    $stmt->bind_param("i", $id);
    $stmt->execute();
    $result = $stmt->get_result();

    if ($result && $result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            $type = $row['type'];
            $qty = $row['qty'];
            $discount = $row['discount'];
            $banking_data = $row['banking'];
            $product_name = $row['product_name'];
            $price = 0;

            // Fetch currency from banking table
            $currency_sql = "SELECT currency FROM $banking WHERE type = ?";
            $currency_stmt = $connection->prepare($currency_sql);
            $currency_stmt->bind_param("s", $banking_data);
            $currency_stmt->execute();
            $currency_result = $currency_stmt->get_result();

            if ($currency_result && $currency_result->num_rows > 0) {
                $currency_data = $currency_result->fetch_assoc();
                $currency = $currency_data['currency'];

                // Fetch rates
                $rate_sql = "SELECT baht, yuan, dollar FROM $rate";
                $rate_result = $connection->query($rate_sql);

                if ($rate_result && $rate_result->num_rows > 0) {
                    $rate_data = $rate_result->fetch_assoc();
                    $baht = $rate_data['baht'];
                    $yuan = $rate_data['yuan'];
                    $dollar = $rate_data['dollar'];

                    // Perform discount calculation
                    // switch ($currency) {
                    //     case 'kyat':
                    //         $discount = $discount_1;
                    //         break;
                    //     case 'baht':
                    //         $discount = $discount_1 * $baht;
                    //         break;
                    //     case 'yuan':
                    //         $discount = $discount_1 * $yuan;
                    //         break;
                    //     case 'dollar':
                    //         $discount = $discount_1 * $dollar;
                    //         break;
                    //     default:
                    //         $discount = 0; // Handle unexpected currency
                    //         break;
                    // }
                } else {
                    echo 'Error fetching rates from $rate table<br>';
                    $discount = 0;
                }
            } else {
                echo 'Error fetching currency from banking table<br>';
                $discount = 0;
            }

            // Determine price based on type
            switch ($type) {
                case 'ဗူး/ခု':
                    $price = round($row['round_pieces_price'] / $row['rate']);
                    break;
                case 'ကဒ်':
                    $price = round($row['round_unit_price'] / $row['rate']);
                    break;
                case 'ဖာ':
                    $price = round($row['round_package_price'] / $row['rate']);
                    break;
                default:
                    echo 'Unknown type: ' . $type . '<br>';
                    $price = 0;
                    break;
            }

            $total = $qty * ($price - $discount);

            // Update the first table
            $update_sql = "UPDATE $user_1_lk SET price = ?, total = ? WHERE tra_id = ?";
            $update_stmt = $connection->prepare($update_sql);
            $update_stmt->bind_param("dii", $price, $total, $id);
            if (!$update_stmt->execute()) {
                die("ERROR: Failed to update $user_1_lk. " . $update_stmt->error . '<br>');
            }

            // Update the second table
            $update_sql2 = "UPDATE $sell_data SET price = ?, total = ? WHERE tra_id = ?";
            $update_stmt2 = $connection->prepare($update_sql2);
            $update_stmt2->bind_param("dii", $price, $total, $id);
            if (!$update_stmt2->execute()) {
                die("ERROR: Failed to update $sell_data. " . $update_stmt2->error . '<br>');
            }

            $query = "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 
            WHERE product_name='$product_name'
            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)";
$connection->query($query);

// Final update 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)
            WHERE product_name='$product_name'";
if (!$connection->query($final_update_query)) {
    $connection->rollback();
    die("ERROR: Query failed. " . $connection->error);
}

            echo "Update successful for tra_id: $id<br>";
        }
    } else {
        echo "No records found for tra_id: $id.<br>";
    }

} else {
    echo 'Invalid request method. Please use POST.<br>';
}


?>