<?php
include 'includes/config.php';
if (isset($_GET['supplier_id'])) {
    $supplier_id = $_GET['supplier_id'];

    // Fetch the total purchase amount
$sql_purchase = "
    SELECT SUM(sub.total_per_ref) AS total_purchase
    FROM (
        SELECT 
            SUM(pl.quantity * pl.purchase_price) + MAX(pl.shipping_charges) AS total_per_ref
        FROM purchase_lines pl
        WHERE pl.supplier_id = :supplier_id
          AND pl.ref_no IS NOT NULL
          AND pl.ref_no != ''
          AND pl.ref_no != '0'
        GROUP BY pl.ref_no
    ) AS sub
";

    $stmt_purchase = $conn->prepare($sql_purchase);
    $stmt_purchase->bindParam(':supplier_id', $supplier_id);
    $stmt_purchase->execute();
    $purchase_data = $stmt_purchase->fetch(PDO::FETCH_ASSOC);
    $total_purchase = $purchase_data['total_purchase'] ?? 0;

    // Fetch the total debit amount
    $sql_debit = "
        SELECT SUM(bd.amount) AS total_debit
        FROM bank_debit_amount bd
        WHERE bd.supplier_id = :supplier_id
    ";
    $stmt_debit = $conn->prepare($sql_debit);
    $stmt_debit->bindParam(':supplier_id', $supplier_id);
    $stmt_debit->execute();
    $debit_data = $stmt_debit->fetch(PDO::FETCH_ASSOC);
    $total_debit = $debit_data['total_debit'] ?? 0;

    // Fetch opening balance from contacts table
    $sql_opening_balance = "
        SELECT opening_balance FROM contacts WHERE id = :supplier_id
    ";
    $stmt_opening_balance = $conn->prepare($sql_opening_balance);
    $stmt_opening_balance->bindParam(':supplier_id', $supplier_id);
    $stmt_opening_balance->execute();
    $opening_balance_data = $stmt_opening_balance->fetch(PDO::FETCH_ASSOC);
    $opening_balance = $opening_balance_data['opening_balance'] ?? 0;

    // Fetch total paid amount from supplier_purchase_manual table
    $sql_paid_amount = "
        SELECT SUM(amount) AS total_paid
        FROM supplier_purchase_manual
        WHERE supplier_id = :supplier_id
    ";
    $stmt_paid_amount = $conn->prepare($sql_paid_amount);
    $stmt_paid_amount->bindParam(':supplier_id', $supplier_id);
    $stmt_paid_amount->execute();
    $paid_data = $stmt_paid_amount->fetch(PDO::FETCH_ASSOC);
    $total_purchse = $paid_data['total_paid'] ?? 0;

    // Calculate the remaining amount, adding opening balance and subtracting debit and paid amounts
    $remaining_amount = $total_purchase + $opening_balance + $total_purchse - $total_debit;

    // Return the remaining amount as JSON
    echo json_encode(['remaining_amount' => number_format($remaining_amount, 2)]);
}
?>