<?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 COALESCE(SUM(line_total),0) FROM project_equipment WHERE project_id = :pid");
        $stmt->execute([':pid' => $projectId]);
        $equipment = (float)$stmt->fetchColumn();
    } catch (Throwable $e) {}

    try {
        $stmt = $pdo->prepare("SELECT COALESCE(installation_cost,0) FROM projects WHERE id = :pid");
        $stmt->execute([':pid' => $projectId]);
        $installation = (float)$stmt->fetchColumn();
    } catch (Throwable $e) {}

    try {
        $stmt = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM project_expenses WHERE project_id = :pid");
        $stmt->execute([':pid' => $projectId]);
        $expenses = (float)$stmt->fetchColumn();
    } catch (Throwable $e) {}

    try {
        $stmt = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM project_payments WHERE project_id = :pid");
        $stmt->execute([':pid' => $projectId]);
        $payments = (float)$stmt->fetchColumn();
    } catch (Throwable $e) {}

    try {
        $stmt = $pdo->prepare("SELECT COALESCE(budget,0) FROM projects WHERE id = :pid");
        $stmt->execute([':pid' => $projectId]);
        $budget = (float)$stmt->fetchColumn();
    } catch (Throwable $e) {}

    try {
        $stmt = $pdo->prepare("SELECT COALESCE(SUM(cost_total),0) FROM work_sessions WHERE project_id = :pid AND status = 'closed'");
        $stmt->execute([':pid' => $projectId]);
        $labor = (float)$stmt->fetchColumn();
    } catch (Throwable $e) {}

    try {
        $stmt = $pdo->prepare("SELECT COALESCE(SUM(amount),0) FROM employee_project_expenses WHERE project_id = :pid");
        $stmt->execute([':pid' => $projectId]);
        $employeeExpenses = (float)$stmt->fetchColumn();
    } catch (Throwable $e) {}

    try {
        $stmt = $pdo->prepare("SELECT COALESCE(SUM(agreed_cost),0) FROM project_partners WHERE project_id = :pid");
        $stmt->execute([':pid' => $projectId]);
        $projectPartnerAgreed = (float)$stmt->fetchColumn();
    } catch (Throwable $e) {}

    try {
        $stmt = $pdo->prepare("SELECT COALESCE(SUM(quote_amount),0) FROM partner_project_costings WHERE project_id = :pid AND status IN ('submitted','approved')");
        $stmt->execute([':pid' => $projectId]);
        $partnerQuotes = (float)$stmt->fetchColumn();
    } catch (Throwable $e) {}

    $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,
    ];
}
