<?php
header("Content-Type: application/json");
include "config.php"; // ✅ DB connection

// Default filters
$search    = $_GET['search'] ?? '';
$status    = $_GET['status'] ?? 'all';
$startDate = $_GET['start_date'] ?? '';
$endDate   = $_GET['end_date'] ?? '';

// Base query
$query = "
    SELECT 
        dr.id,
        dr.created_at AS datetime,
        la.account_name AS client_name,
        dr.trade_id,
        dr.comment AS notes,
        CASE WHEN dr.type = 'deposit' THEN dr.amount ELSE 0 END AS deposit,
        CASE WHEN dr.type = 'withdraw' THEN dr.amount ELSE 0 END AS withdraw,
        dr.type AS payment_method,
        dr.status,
        la.email
    FROM deposit_requests dr
    INNER JOIN liveaccounts la ON dr.trade_id = la.trade_id
    WHERE 1
";

$params = [];

// Search by client name or trade id
if (!empty($search)) {
    $query .= " AND (la.account_name LIKE ? OR dr.trade_id LIKE ?)";
    $searchTerm = "%$search%";
    $params[] = $searchTerm;
    $params[] = $searchTerm;
}

// Filter by status
if ($status !== 'all') {
    $query .= " AND dr.status = ?";
    $params[] = $status;
}

// Date range filter
if (!empty($startDate) && !empty($endDate)) {
    $query .= " AND DATE(dr.created_at) BETWEEN ? AND ?";
    $params[] = $startDate;
    $params[] = $endDate;
}

$query .= " ORDER BY dr.created_at DESC";

$stmt = $conn->prepare($query);

// Bind params dynamically
if ($params) {
    $types = str_repeat("s", count($params));
    $stmt->bind_param($types, ...$params);
}

$stmt->execute();
$result = $stmt->get_result();

$ledgerData = [];
$totalDeposits = 0;
$totalWithdrawals = 0;

while ($row = $result->fetch_assoc()) {
    $ledgerData[] = [
        "id"            => $row['id'],
        "datetime"      => $row['datetime'],
        "client"        => $row['client_name'],
        "trade_id"      => $row['trade_id'],
        "notes"         => $row['notes'],
        "deposit"       => (float)$row['deposit'],
        "withdraw"      => (float)$row['withdraw'],
        "payment_method"=> $row['payment_method'],
        "status"        => $row['status'],
        "email"         => $row['email'],
    ];

    $totalDeposits += (float)$row['deposit'];
    $totalWithdrawals += (float)$row['withdraw'];
}

$response = [
    "status" => "success",
    "summary" => [
        "deposits"     => $totalDeposits,
        "withdrawals"  => $totalWithdrawals,
        "net_deposit"  => $totalDeposits - $totalWithdrawals
    ],
    "data" => $ledgerData
];

echo json_encode($response);

$stmt->close();
$conn->close();
