<?php
function gp_project_financial_summary(PDO $pdo, int $projectId): array
{
    $projectId = (int)$projectId;

    $equipment = 0.0;
    $installation = 0.0;
    $expenses = 0.0;
    $payments = 0.0;
    $budget = 0.0;
    $labor = 0.0;
    $employeeExpenses = 0.0;
    $projectPartnerAgreed = 0.0;
    $partnerQuotes = 0.0;

    try {
        $stmt = $pdo->prepare("
            SELECT
                (SELECT COALESCE(SUM(line_total),0) FROM project_equipment WHERE project_id = :pid) AS equipment,
                (SELECT COALESCE(installation_cost,0) FROM projects WHERE id = :pid) AS installation,
                (SELECT COALESCE(SUM(amount),0) FROM project_expenses WHERE project_id = :pid) AS expenses,
                (SELECT COALESCE(SUM(amount),0) FROM project_payments WHERE project_id = :pid) AS payments,
                (SELECT COALESCE(budget,0) FROM projects WHERE id = :pid) AS budget,
                (SELECT COALESCE(SUM((COALESCE(duration_minutes, 0) / 60) * hourly_rate),0) FROM work_sessions WHERE project_id = :pid AND end_time IS NOT NULL) AS labor,
                (SELECT COALESCE(SUM(amount),0) FROM employee_project_expenses WHERE project_id = :pid) AS employeeExpenses,
                (SELECT COALESCE(SUM(agreed_cost),0) FROM project_partners WHERE project_id = :pid) AS projectPartnerAgreed,
                (SELECT COALESCE(SUM(quote_amount),0) FROM partner_project_costings WHERE project_id = :pid AND status IN ('submitted','approved')) AS partnerQuotes
        ");
        $stmt->execute([':pid' => $projectId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($row) {
            $equipment = (float)$row['equipment'];
            $installation = (float)$row['installation'];
            $expenses = (float)$row['expenses'];
            $payments = (float)$row['payments'];
            $budget = (float)$row['budget'];
            $labor = (float)$row['labor'];
            $employeeExpenses = (float)$row['employeeExpenses'];
            $projectPartnerAgreed = (float)$row['projectPartnerAgreed'];
            $partnerQuotes = (float)$row['partnerQuotes'];
        }
    } catch (Throwable $e) {
        echo "<div style='background:red;color:white;padding:10px;margin:10px;border-radius:5px;'>SQL ERROR: " . $e->getMessage() . "</div>";
    }

    $partnerTotal = $partnerQuotes > 0 ? $partnerQuotes : $projectPartnerAgreed;
    $directCost = $equipment + $installation + $partnerTotal + $expenses + $labor + $employeeExpenses;
    $balance = $budget - $payments;
    $profit = $budget - $directCost;
    $remainingAfterCosts = $payments - $directCost;

    return [
        'equipment_cost' => $equipment,
        'installation_cost' => $installation,
        'partner_cost' => $partnerTotal,
        'partner_quote_cost' => $partnerQuotes,
        'partner_agreed_cost' => $projectPartnerAgreed,
        'expense_cost' => $expenses,
        'employee_expense_cost' => $employeeExpenses,
        'labor_cost' => $labor,
        'direct_cost' => $directCost,
        'budget' => $budget,
        'payments_in' => $payments,
        'balance' => $balance,
        'profit' => $profit,
        'remaining_after_costs' => $remainingAfterCosts,
        'estimated_revenue' => $budget,
        'margin' => $profit,
    ];
}
