<?php
header("Content-Type: application/json; charset=UTF-8");
ini_set('display_errors', 1);
error_reporting(E_ALL);
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

require_once "db.php";

// استقبال PID من الجافاسكريبت
$data = json_decode(file_get_contents("php://input"), true);
$PID = intval($data['PID'] ?? 0);

// الاستعلام مع فلتر المشروع لو موجود
$sql = "
    SELECT 
        MAX(c.id) AS id,
        c.PID,
        p.project_name,
        p.id AS project_id,
        c.EmpID,
        u.id AS employee_id,
        u.username AS employee_name,
        MAX(c.CDate) AS last_custody_date,
        SUM(c.CAmount) AS total_custody,
        COALESCE(SUM(CASE WHEN e.payment_method = 'عهده' THEN e.total_price ELSE 0 END), 0) AS total_spent,
        GROUP_CONCAT(DISTINCT c.CNotes SEPARATOR ' | ') AS notes,
        GROUP_CONCAT(DISTINCT c.AttachmentPath SEPARATOR ',') AS attachments
    FROM custodyMH c
    JOIN PrjectsMH p ON c.PID = p.id
    JOIN USEUSERSMH u ON c.EmpID = u.id
    LEFT JOIN expensesMH e 
        ON e.project_id = c.PID 
        AND e.EmpID = c.EmpID
    " . ($PID > 0 ? "WHERE c.PID = ?" : "") . "
    GROUP BY c.PID, c.EmpID
    ORDER BY last_custody_date DESC
";

$stmt = $conn->prepare($sql);
if ($PID > 0) {
    $stmt->bind_param("i", $PID);
}

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

$custodies = [];
while ($row = $result->fetch_assoc()) {
    $amount = (float) $row['total_custody'];     // إجمالي العهد
    $spent  = (float) $row['total_spent'];       // إجمالي المصروفات من العهدة
    $balance = $amount - $spent;                 // الرصيد = العهد - المصروفات

    $custodies[] = [
        "id" => $row['id'],
        "project_id" => $row['project_id'],
        "project_name" => $row['project_name'],
        "employee_id" => $row['employee_id'],
        "employee" => $row['employee_name'],
        "date" => $row['last_custody_date'],

        // قيم مالية واضحة
        "amount" => $amount,          // إجمالي العهد
        "total_spent" => $spent,      // اسم صريح للمصروفات
        "expense" => $spent,          // للتوافق مع الواجهة الحالية إن كانت تقرأ 'expense'
        "balance" => $balance,        // الرصيد

        "notes" => $row['notes'],
        "attachments" => $row['attachments']
    ];
}

echo json_encode(["status" => "success", "data" => $custodies], JSON_UNESCAPED_UNICODE);

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