<?php
// Include database connection
require_once 'db_connection.php';

header('Content-Type: application/json');

if (!isset($_GET['buyer_id']) || !isset($_GET['date'])) {
    echo json_encode(['error' => 'Buyer ID and date are required']);
    exit;
}

$buyerId = intval($_GET['buyer_id']);
$date = $_GET['date'];

// Verify date format
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $date)) {
    echo json_encode(['error' => 'Invalid date format']);
    exit;
}

try {
    // Get total sales amount
    $salesQuery = "SELECT SUM(final_amount) as total_sales 
                  FROM sales 
                  WHERE buyer_id = ? AND date <= ?";
    $salesStmt = $conn->prepare($salesQuery);
    $salesStmt->bind_param("is", $buyerId, $date);
    $salesStmt->execute();
    $salesResult = $salesStmt->get_result();
    $totalSales = $salesResult->fetch_assoc()['total_sales'] ?? 0;

    // Get total payments
    $paymentsQuery = "SELECT SUM(amount_paid) as total_payments 
                     FROM payments 
                     WHERE buyer_id = ? AND date <= ?";
    $paymentsStmt = $conn->prepare($paymentsQuery);
    $paymentsStmt->bind_param("is", $buyerId, $date);
    $paymentsStmt->execute();
    $paymentsResult = $paymentsStmt->get_result();
    $totalPayments = $paymentsResult->fetch_assoc()['total_payments'] ?? 0;

    // Get initial values
    $query = "SELECT * FROM initial_values LIMIT 1";
    $result = $conn->query($query);
    $initialValues = $result->fetch_assoc();

    // Get buyer name
    $buyerQuery = "SELECT name FROM buyers WHERE id = ?";
    $buyerStmt = $conn->prepare($buyerQuery);
    $buyerStmt->bind_param("i", $buyerId);
    $buyerStmt->execute();
    $buyerResult = $buyerStmt->get_result();
    $buyer = $buyerResult->fetch_assoc();

    // Add initial balance if it's one of the special buyers
    $initialBalance = 0;
    if ($buyer && $initialValues) {
        if ($buyer['name'] == 'Madhu') {
            $initialBalance = $initialValues['madhu_balance'] ?? 0;
        } elseif ($buyer['name'] == 'Rathna') {
            $initialBalance = $initialValues['rathna_balance'] ?? 0;
        } elseif ($buyer['name'] == 'Jambukkutti') {
            $initialBalance = $initialValues['jambukkutti_balance'] ?? 0;
        }
    }

    // Calculate balance
    $balance = $totalSales - $totalPayments + $initialBalance;

    echo json_encode(['balance' => $balance]);
} catch (Exception $e) {
    echo json_encode(['error' => $e->getMessage()]);
}
?>