<?php
header("Content-Type: application/json; charset=UTF-8");
require_once "db.php";

// جلب البيانات بدون مرفقات
$result = $conn->query("SELECT id, data_name, start_date, value, due_date, notes 
                        FROM financialMH  
                        WHERE `AttachmentPath` IS NULL
                        ORDER BY start_date DESC");

// حساب مجموع القيم فقط للبيانات بدون مرفقات
$totalResult = $conn->query("SELECT SUM(value) AS total 
                             FROM financialMH 
                             WHERE `AttachmentPath` IS NULL");
$totalRow = $totalResult->fetch_assoc();
$totalValue = floatval($totalRow['total']);

$funds = [];

while ($row = $result->fetch_assoc()) {
    $startDate = $row['start_date'];
    $dueDate = $row['due_date'];

    // 🔹 حساب المصروفات في الفترة الزمنية
    $expensesQuery = $conn->prepare("SELECT SUM(total_price) AS total_expenses 
                                     FROM expensesMH 
                                     WHERE date BETWEEN ? AND ?");
    $expensesQuery->bind_param("ss", $startDate, $dueDate);
    $expensesQuery->execute();
    $expensesResult = $expensesQuery->get_result()->fetch_assoc();
    $totalExpenses = floatval($expensesResult['total_expenses']);

    // 🔹 حساب الإيرادات في نفس الفترة
    $revenuesQuery = $conn->prepare("SELECT SUM(value) AS total_revenues 
                                     FROM revenuesMH 
                                     WHERE date BETWEEN ? AND ?");
    $revenuesQuery->bind_param("ss", $startDate, $dueDate);
    $revenuesQuery->execute();
    $revenuesResult = $revenuesQuery->get_result()->fetch_assoc();
    $totalRevenues = floatval($revenuesResult['total_revenues']);

    // 🔹 حساب نسبة البيان كـ float
    $percentageFloat = ($totalValue > 0) ? ($row['value'] / $totalValue * 99) : 0;

    // 🔹 حساب الربح الخاص بالبيان حسب النسبة
    $profit = ($totalRevenues - $totalExpenses) * $percentageFloat / 100;

    // 🔹 حساب نسبة الربح مقارنة بقيمة البيان
    $profitRatio = ($row['value'] > 0) ? ($profit / $row['value'] * 100) : 0;

    // ✅ إضافة البيانات بصيغة منسقة
    $funds[] = [
        'id' => $row['id'],
        'data_name' => $row['data_name'],
        'start_date' => $row['start_date'],
        'value' => number_format(floatval($row['value']), 2), // ✅ تنسيق القيمة مثل 1,000,000.00
        'percentage' => number_format($percentageFloat, 2),
        'due_date' => $row['due_date'],
        'profit' => number_format($profit, 2) . 'ريال -  ' . number_format($profitRatio, 2) . '%',
        'notes' => $row['notes']
    ];
}

echo json_encode($funds);
$conn->close();
?>
