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

//include "login_check.php";


$sql = "DELETE FROM ".$voucher."";
if ($connection->query($sql) === TRUE) {
    //echo "All records deleted successfully";
} else {
    //echo "Error deleting records: " . $conn->error;
}


// $sql_insert = "
//     INSERT INTO ".$voucher." (voucher_no, customer_name, total,discount, pay, payable, banking, rate)
//     SELECT 
//         voucher,
//         customer,
//         SUM(total/rate) AS total_buy,
//         SUM(discount/rate) AS total_discount,
//         '0',  
//         '0',
//         banking,
//         '0' 
//     FROM ".$sell_data."
//     WHERE customer != 'imv_customer' and buy_type ='အကြွေး'
//     GROUP BY voucher;
// ";

$sql_insert = "
    INSERT INTO ".$voucher." (voucher_no, customer_name, total,discount, pay, payable, banking, rate)
    SELECT 
        voucher,
        customer,
        SUM(total/rate) AS total_buy,
        '0',
        '0',  
        '0',
        banking,
        '0' 
    FROM ".$sell_data."
    WHERE customer != 'imv_customer' and buy_type ='အကြွေး'
    GROUP BY voucher;
";


if ($connection->query($sql_insert) === TRUE) {
    //echo "Records inserted successfully";
} else {
    echo "Error inserting records: " . $connection->error;
}

$sql = "SELECT customer, voucher, SUM((discount/rate)*qty) AS total_discount FROM ".$sell_data." WHERE discount !='0' GROUP BY voucher";
$result = $connection->query($sql);
if ($result === false) {
    // Query failed
    echo "Error: " . $connection->error;
} elseif ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $customer = $row['customer'];
        $voucher_data = $row['voucher'];
        $total_discount = round($row['total_discount'],2);

        $update_sql = "UPDATE ".$voucher." SET discount='$total_discount' WHERE voucher_no='$voucher_data'";
        $insertResult = $connection->query($update_sql);
        if ($insertResult) {
            // echo 'Update Success Browser ID';
            // echo '<script>alert("Login Success") ;</script>';
            // echo '<script>window.location = "admin_dashboard_index.php";</script>';
        } else {
            // echo 'Update Failed Browser ID';
            // echo '<script>window.location = "admin_login.html";</script>';
        }

    }
} else {
    // No rows found
    // echo "No results found.";
}


//Select Discount
$sql = "SELECT name, voucher, SUM(discount/rate) AS total_discount FROM ".$customer_payment." where discount !='0' GROUP BY voucher";
$result = $connection->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $name = $row['name'];
        $voucher_data = $row['voucher'];
        $total_pay = $row['total_pay'];

        // Uncomment and correct the update query
        $update_sql = "UPDATE ".$voucher." SET pay='$total_pay' WHERE voucher_no='$voucher_data'";
        $insertResult = $connection->query($update_sql);
        if ($insertResult) {
            // echo 'Update Success Browser ID';
            // echo '<script>alert("Login Success") ;</script>';
            // echo '<script>window.location = "admin_dashboard_index.php";</script>';
        } else {
            // echo 'Update Failed Browser ID';
            // echo '<script>window.location = "admin_login.html";</script>';
        }
    }
} else {
    // echo 'No results found.';
}


// Correct the SQL query
$sql = "SELECT name, voucher, SUM(amount) AS total_pay FROM ".$customer_payment." GROUP BY voucher";
$result = $connection->query($sql);

if ($result && $result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $name = $row['name'];
        $voucher_data = $row['voucher'];
        $total_pay = $row['total_pay'];

        // Uncomment and correct the update query
        $update_sql = "UPDATE ".$voucher." SET pay='$total_pay' WHERE voucher_no='$voucher_data'";
        $insertResult = $connection->query($update_sql);
        if ($insertResult) {
            // echo 'Update Success Browser ID';
            // echo '<script>alert("Login Success") ;</script>';
            // echo '<script>window.location = "admin_dashboard_index.php";</script>';
        } else {
            // echo 'Update Failed Browser ID';
            // echo '<script>window.location = "admin_login.html";</script>';
        }
    }
} else {
    // echo 'No results found.';
}

// Always check for SQL query errors
if ($connection->error) {
    echo 'SQL Error: ' . $connection->error;
}



$sql = "UPDATE ".$voucher." SET payable = total - pay;"; //update Brower ID
$insertResult = $connection->query($sql);

?>