<?php 
include "../config.php";

$customer_code_filter = $_GET['customer']; // <-- get the customer code from URL


$age_buckets = [
    '0-30' => 0,
    '31-60' => 0,
    '61-90' => 0,
    '91+' => 0
];
$agewise_detailed = [];
$today = new DateTime();

// Step 1: Fetch transactions from both tables for a specific customer
$transactions_query = "
    SELECT salescode, ticket, vehicleno, customer, delivery, product, price, priceori, date, time,
           unitvalue, netweight, netqty, netqtyori, cft, totalamount, modeof, remark, tablename,
           ('0') AS driver_amount, ('-') AS driver_name, editby AS user
    FROM second_transaction
    WHERE status != 'deleted' AND status != 'cancel' AND customer like '%$customer_code_filter%'

    UNION

    SELECT salescode, ticket, vehicleno, customer, delivery, product, price, priceori, date, time,
           unitvalue, netweight, netqty, netqtyori, cft, totalamount, modeof, remark, tablename,
           ('0') AS driver_amount, ('-') AS driver_name, editby AS user
    FROM second_transaction_est
    WHERE status != 'deleted' AND status != 'cancel' AND customer like '%$customer_code_filter%'
";

$transactions_result = mysqli_query($connect, $transactions_query);

// Step 2: Fetch all credit payments for that customer
$credit_query = "
    SELECT sales_code, amount,otheramount
    FROM creditdetails
    WHERE status != 'deleted' AND customer = '$customer_code_filter'
";
$credit_result = mysqli_query($connect, $credit_query);
$creditMap = [];

while ($row = mysqli_fetch_assoc($credit_result)) {
    $sales_code = $row['sales_code'];
    $amount = floatval($row['amount']+$row['otheramount']);
    if (!isset($creditMap[$sales_code])) {
        $creditMap[$sales_code] = 0;
    }
    $creditMap[$sales_code] += $amount;
}

// Step 3: Process each transaction
while ($row = mysqli_fetch_assoc($transactions_result)) {
    $salescode = $row['salescode'];
    $bill_date = new DateTime($row['date']);
    $age_days = $bill_date->diff($today)->days;
    $total_amount = floatval($row['totalamount']);
    $paid_amount = isset($creditMap[$salescode]) ? $creditMap[$salescode] : 0;
    $pending_amount = $total_amount - $paid_amount;

    if ($pending_amount <= 0) continue; // Skip fully paid

    // Assign to age bucket
    if ($age_days <= 30) {
        $age_buckets['0-30'] += $pending_amount;
    } elseif ($age_days <= 60) {
        $age_buckets['31-60'] += $pending_amount;
    } elseif ($age_days <= 90) {
        $age_buckets['61-90'] += $pending_amount;
    } else {
        $age_buckets['91+'] += $pending_amount;
    }

    // Fetch customer name using code
    $customer_code = $row['customer'];
    $customer_name = '';
    $cus = mysqli_query($connect, "SELECT code, name FROM customerdetails WHERE status != 'deleted' AND code = '$customer_code'");
    if ($cusrow = mysqli_fetch_assoc($cus)) {
        $customer_name = $cusrow['name'];
        $customer_code = $cusrow['code'];
    }

    // Add to detailed array
    $agewise_detailed[] = (object)[
        'salescode' => $salescode,
        'customer-code' => $customer_code,
        'customer-name' => $customer_name,
        'bill-date' => $bill_date->format("Y-m-d"),
        'age-days' => $age_days,
        'total-amount' => number_format($total_amount, 2, '.', ''),
        'paid-amount' => number_format($paid_amount, 2, '.', ''),
        'pending-amount' => number_format($pending_amount, 2, '.', '')
    ];
}

// Final response
$final_report = (object)[
    'agewise_summary' => $age_buckets,
    'agewise_detailed' => $agewise_detailed
];

echo json_encode($final_report);
?>
