<?php
ob_start();
require_once 'includes/header.php';

$roleRaw = $_SESSION['user_role'] ?? 'guest';
$canAccess = false;
if (function_exists('isFinanceTier') && isFinanceTier($roleRaw)) { $canAccess = true; }
if (function_exists('isAdminTier') && isAdminTier($roleRaw)) { $canAccess = true; }
if (!$canAccess) {
    echo "<div class='container py-4'><div class='alert alert-danger'>Access Denied</div></div>";
    require_once 'includes/footer.php';
    exit;
}

if (function_exists('ensureFinanceAccountsTable')) {
    ensureFinanceAccountsTable();
}

$companyId = function_exists('getCurrentCompanyId') ? getCurrentCompanyId() : null;

$startDate = trim((string)($_GET['start_date'] ?? date('Y-m-01')));
$endDate = trim((string)($_GET['end_date'] ?? date('Y-m-t')));
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $startDate)) { $startDate = date('Y-m-01'); }
if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $endDate)) { $endDate = date('Y-m-t'); }

$paymentsIn = 0.0;
$expensesOut = 0.0;
$paymentsBefore = 0.0;
$expensesBefore = 0.0;
$bookBalance = 0.0;

try {
    global $pdo;
    $q = "SELECT COALESCE(SUM(balance),0) FROM finance_accounts WHERE status = 'active'";
    $p = [];
    if ($companyId && function_exists('tableHasColumn') && tableHasColumn('finance_accounts', 'company_id')) {
        $q .= " AND (company_id = ? OR company_id IS NULL)";
        $p[] = $companyId;
    }
    $st = $pdo->prepare($q);
    $st->execute($p);
    $bookBalance = (float)($st->fetchColumn() ?: 0);
} catch (Throwable $e) { $bookBalance = 0.0; }

try {
    global $pdo;
    $hasPayments = $pdo->query("SHOW TABLES LIKE 'payments'")->rowCount() > 0;
    if ($hasPayments) {
        $dateCol = function_exists('kpiPaymentDateColumn') ? kpiPaymentDateColumn('payments') : 'created_at';
        $dateCol = $dateCol ?: (function_exists('tableHasColumn') && tableHasColumn('payments','date') ? 'date' : 'created_at');
        $statusSql = function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentFinalizedStatuses()) : "('verified','approved','paid','completed','success')";
        $sql = "SELECT COALESCE(SUM(amount),0) FROM payments WHERE status IN $statusSql AND $dateCol BETWEEN ? AND ?";
        $params = [$startDate . " 00:00:00", $endDate . " 23:59:59"];
        if ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments','company_id')) {
            $sql .= " AND (company_id = ? OR company_id IS NULL)";
            $params[] = $companyId;
        }
        $st = $pdo->prepare($sql);
        $st->execute($params);
        $paymentsIn = (float)($st->fetchColumn() ?: 0);
    }
} catch (Throwable $e) { $paymentsIn = 0.0; }

try {
    global $pdo;
    $params = [];
    $union = function_exists('buildExpensesUnionSql')
        ? buildExpensesUnionSql(['start_date' => $startDate, 'end_date' => $endDate], $params, false)
        : "(SELECT 'manual' AS source, 0 AS reference_id, 0 AS amount, NULL AS date, NULL AS recorded_by_user_id, NULL AS recorded_by, '' AS category, '' AS status, '' AS account_head, '' AS account_sub_head, '' AS account_code, NULL AS paid_through_account_id, NULL AS vendor_id, NULL AS reference_number, NULL AS estate_name_manual, NULL AS estate_id, NULL AS project_id WHERE 1=0)";
    $sql = "SELECT COALESCE(SUM(amount),0) FROM $union e";
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $expensesOut = (float)($st->fetchColumn() ?: 0);
} catch (Throwable $e) { $expensesOut = 0.0; }

$prevEnd = date('Y-m-d', strtotime($startDate . ' -1 day'));
if (preg_match('/^\d{4}-\d{2}-\d{2}$/', $prevEnd)) {
    try {
        global $pdo;
        $hasPayments = $pdo->query("SHOW TABLES LIKE 'payments'")->rowCount() > 0;
        if ($hasPayments) {
            $dateCol = function_exists('kpiPaymentDateColumn') ? kpiPaymentDateColumn('payments') : 'created_at';
            $dateCol = $dateCol ?: (function_exists('tableHasColumn') && tableHasColumn('payments','date') ? 'date' : 'created_at');
            $statusSql = function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentFinalizedStatuses()) : "('verified','approved','paid','completed','success')";
            $sql = "SELECT COALESCE(SUM(amount),0) FROM payments WHERE status IN $statusSql AND $dateCol < ?";
            $params = [$startDate . " 00:00:00"];
            if ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments','company_id')) {
                $sql .= " AND (company_id = ? OR company_id IS NULL)";
                $params[] = $companyId;
            }
            $st = $pdo->prepare($sql);
            $st->execute($params);
            $paymentsBefore = (float)($st->fetchColumn() ?: 0);
        }
    } catch (Throwable $e) { $paymentsBefore = 0.0; }

    try {
        global $pdo;
        $params = [];
        $union = function_exists('buildExpensesUnionSql')
            ? buildExpensesUnionSql(['end_date' => $prevEnd], $params, false)
            : "(SELECT 'manual' AS source, 0 AS reference_id, 0 AS amount, NULL AS date, NULL AS recorded_by_user_id, NULL AS recorded_by, '' AS category, '' AS status, '' AS account_head, '' AS account_sub_head, '' AS account_code, NULL AS paid_through_account_id, NULL AS vendor_id, NULL AS reference_number, NULL AS estate_name_manual, NULL AS estate_id, NULL AS project_id WHERE 1=0)";
        $sql = "SELECT COALESCE(SUM(amount),0) FROM $union e";
        $st = $pdo->prepare($sql);
        $st->execute($params);
        $expensesBefore = (float)($st->fetchColumn() ?: 0);
    } catch (Throwable $e) { $expensesBefore = 0.0; }
}

$net = $paymentsIn - $expensesOut;
$openingBalance = $paymentsBefore - $expensesBefore;
$closingBalance = $openingBalance + $paymentsIn - $expensesOut;

$monthsBack = 11;
$trendLabels = [];
$trendIn = [];
$trendOut = [];
$trendNet = [];
for ($i = $monthsBack; $i >= 0; $i--) {
    $k = date('Y-m', strtotime("-$i months"));
    $trendLabels[$k] = date('M Y', strtotime($k . '-01'));
    $trendIn[$k] = 0.0;
    $trendOut[$k] = 0.0;
    $trendNet[$k] = 0.0;
}

try {
    global $pdo;
    $hasPayments = $pdo->query("SHOW TABLES LIKE 'payments'")->rowCount() > 0;
    if ($hasPayments) {
        $dateCol = function_exists('kpiPaymentDateColumn') ? kpiPaymentDateColumn('payments') : 'created_at';
        $dateCol = $dateCol ?: (function_exists('tableHasColumn') && tableHasColumn('payments','date') ? 'date' : 'created_at');
        $statusSql = function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentFinalizedStatuses()) : "('verified','approved','paid','completed','success')";
        $sql = "SELECT DATE_FORMAT($dateCol, '%Y-%m') AS ym, COALESCE(SUM(amount),0) AS total
                FROM payments
                WHERE status IN $statusSql
                  AND $dateCol >= DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-01'), INTERVAL $monthsBack MONTH)";
        $params = [];
        if ($companyId && function_exists('tableHasColumn') && tableHasColumn('payments','company_id')) {
            $sql .= " AND (company_id = ? OR company_id IS NULL)";
            $params[] = $companyId;
        }
        $sql .= " GROUP BY ym ORDER BY ym ASC";
        $st = $pdo->prepare($sql);
        $st->execute($params);
        $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
        foreach ($rows as $r) {
            $ym = (string)($r['ym'] ?? '');
            if ($ym !== '' && array_key_exists($ym, $trendIn)) {
                $trendIn[$ym] = (float)($r['total'] ?? 0);
            }
        }
    }
} catch (Throwable $e) {}

try {
    global $pdo;
    $params = [];
    $union = function_exists('buildExpensesUnionSql')
        ? buildExpensesUnionSql(['start_date' => date('Y-m-01', strtotime("-$monthsBack months")), 'end_date' => date('Y-m-t')], $params, false)
        : "(SELECT 'manual' AS source, 0 AS reference_id, 0 AS amount, NULL AS date, NULL AS recorded_by_user_id, NULL AS recorded_by, '' AS category, '' AS status, '' AS account_head, '' AS account_sub_head, '' AS account_code, NULL AS paid_through_account_id, NULL AS vendor_id, NULL AS reference_number, NULL AS estate_name_manual, NULL AS estate_id, NULL AS project_id WHERE 1=0)";
    $sql = "SELECT DATE_FORMAT(date, '%Y-%m') AS ym, COALESCE(SUM(amount),0) AS total
            FROM $union e
            GROUP BY ym ORDER BY ym ASC";
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
    foreach ($rows as $r) {
        $ym = (string)($r['ym'] ?? '');
        if ($ym !== '' && array_key_exists($ym, $trendOut)) {
            $trendOut[$ym] = (float)($r['total'] ?? 0);
        }
    }
} catch (Throwable $e) {}

foreach (array_keys($trendLabels) as $ym) {
    $trendNet[$ym] = (float)$trendIn[$ym] - (float)$trendOut[$ym];
}

$outByEstate = [];
$outByCategory = [];

try {
    global $pdo;
    $params = [];
    $union = function_exists('buildExpensesUnionSql')
        ? buildExpensesUnionSql(['start_date' => $startDate, 'end_date' => $endDate], $params, false)
        : "(SELECT 'manual' AS source, 0 AS reference_id, 0 AS amount, NULL AS date, NULL AS recorded_by_user_id, NULL AS recorded_by, '' AS category, '' AS status, '' AS account_head, '' AS account_sub_head, '' AS account_code, NULL AS paid_through_account_id, NULL AS vendor_id, NULL AS reference_number, NULL AS estate_name_manual, NULL AS estate_id, NULL AS project_id WHERE 1=0)";
    $hasEst = false;
    try { $hasEst = $pdo->query("SHOW TABLES LIKE 'estates'")->rowCount() > 0; } catch (Throwable $e) { $hasEst = false; }
    $join = $hasEst ? " LEFT JOIN estates es ON es.id = e.estate_id" : "";
    $estateSel = $hasEst ? "es.name AS estate_name" : "NULL AS estate_name";
    $sql = "SELECT e.amount, e.category, e.estate_id, e.estate_name_manual, $estateSel
            FROM $union e$join";
    $st = $pdo->prepare($sql);
    $st->execute($params);
    $rows = $st->fetchAll(PDO::FETCH_ASSOC) ?: [];
    foreach ($rows as $r) {
        $amt = (float)($r['amount'] ?? 0);
        $estate = trim((string)($r['estate_name'] ?? ''));
        if ($estate === '') { $estate = trim((string)($r['estate_name_manual'] ?? '')); }
        if ($estate === '') { $estate = 'Unassigned'; }
        $cat = trim((string)($r['category'] ?? ''));
        if ($cat === '') { $cat = 'Uncategorised'; }
        if (!isset($outByEstate[$estate])) { $outByEstate[$estate] = 0.0; }
        if (!isset($outByCategory[$cat])) { $outByCategory[$cat] = 0.0; }
        $outByEstate[$estate] += $amt;
        $outByCategory[$cat] += $amt;
    }
} catch (Throwable $e) { $outByEstate = []; $outByCategory = []; }

arsort($outByEstate);
arsort($outByCategory);

$inByEstate = [];
$profitByEstate = [];
try {
    global $pdo;
    $hasPayments = $pdo->query("SHOW TABLES LIKE 'payments'")->rowCount() > 0;
    $hasAllocations = $pdo->query("SHOW TABLES LIKE 'allocations'")->rowCount() > 0;
    $hasProperties = $pdo->query("SHOW TABLES LIKE 'properties'")->rowCount() > 0;
    $hasEstates = $pdo->query("SHOW TABLES LIKE 'estates'")->rowCount() > 0;
    if ($hasPayments && $hasAllocations && $hasProperties && $hasEstates && function_exists('tableHasColumn') && tableHasColumn('properties', 'estate_id')) {
        $dateCol = function_exists('kpiPaymentDateColumn') ? kpiPaymentDateColumn('payments') : 'created_at';
        $dateCol = $dateCol ?: (tableHasColumn('payments', 'date') ? 'date' : 'created_at');
        $statusSql = function_exists('kpiSqlList') ? kpiSqlList(kpiPaymentFinalizedStatuses()) : "('verified','approved','paid','completed','success')";
        $companyClause = '';
        $companyParams = [];
        if ($companyId && tableHasColumn('payments', 'company_id')) {
            $companyClause = " AND (pm.company_id = ? OR pm.company_id IS NULL)";
            $companyParams[] = $companyId;
        }

        $baseParams = [$startDate . " 00:00:00", $endDate . " 23:59:59"];
        $noAllocClause = " AND (pm.allocation_id IS NULL OR pm.allocation_id = 0)";
        if (tableHasColumn('payments', 'invoice_id')) { $noAllocClause .= " AND (pm.invoice_id IS NULL OR pm.invoice_id = 0)"; }

        // 1) Allocation-linked payments (including invoice-linked allocation id)
        if (tableHasColumn('payments', 'allocation_id') && tableHasColumn('allocations', 'property_id')) {
            $joinInv = '';
            $allocExpr = 'pm.allocation_id';
            $hasInvoices = $pdo->query("SHOW TABLES LIKE 'invoices'")->rowCount() > 0;
            if ($hasInvoices && tableHasColumn('payments', 'invoice_id') && tableHasColumn('invoices', 'allocation_id')) {
                $joinInv = " LEFT JOIN invoices inv ON inv.id = pm.invoice_id";
                $allocExpr = "COALESCE(pm.allocation_id, inv.allocation_id)";
            }
            $sql = "SELECT es.name AS estate_name, COALESCE(SUM(pm.amount),0) AS total
                    FROM payments pm
                    $joinInv
                    JOIN allocations a ON a.id = $allocExpr
                    JOIN properties pr ON pr.id = a.property_id
                    JOIN estates es ON es.id = pr.estate_id
                    WHERE pm.status IN $statusSql
                      AND pm.$dateCol BETWEEN ? AND ?$companyClause
                    GROUP BY es.id, es.name";
            $st = $pdo->prepare($sql);
            $st->execute(array_merge($baseParams, $companyParams));
            foreach ($st->fetchAll(PDO::FETCH_ASSOC) ?: [] as $r) {
                $name = trim((string)($r['estate_name'] ?? ''));
                if ($name === '') continue;
                $inByEstate[$name] = (float)($inByEstate[$name] ?? 0) + (float)($r['total'] ?? 0);
            }
        }

        // 2) Deal-linked payments (when allocation_id is not set)
        if (tableHasColumn('payments', 'deal_id') && tableHasColumn('allocations', 'deal_id') && tableHasColumn('allocations', 'property_id')) {
            $sql = "SELECT es.name AS estate_name, COALESCE(SUM(pm.amount),0) AS total
                    FROM payments pm
                    JOIN (
                        SELECT deal_id, MIN(property_id) AS property_id
                        FROM allocations
                        WHERE deal_id IS NOT NULL AND deal_id <> 0
                        GROUP BY deal_id
                    ) a ON a.deal_id = pm.deal_id
                    JOIN properties pr ON pr.id = a.property_id
                    JOIN estates es ON es.id = pr.estate_id
                    WHERE pm.status IN $statusSql
                      AND pm.$dateCol BETWEEN ? AND ?
                      $noAllocClause$companyClause
                    GROUP BY es.id, es.name";
            $st = $pdo->prepare($sql);
            $st->execute(array_merge($baseParams, $companyParams));
            foreach ($st->fetchAll(PDO::FETCH_ASSOC) ?: [] as $r) {
                $name = trim((string)($r['estate_name'] ?? ''));
                if ($name === '') continue;
                $inByEstate[$name] = (float)($inByEstate[$name] ?? 0) + (float)($r['total'] ?? 0);
            }
        }

        // 3) Property-linked payments (when allocation_id is not set)
        if (tableHasColumn('payments', 'property_id')) {
            $sql = "SELECT es.name AS estate_name, COALESCE(SUM(pm.amount),0) AS total
                    FROM payments pm
                    JOIN properties pr ON pr.id = pm.property_id
                    JOIN estates es ON es.id = pr.estate_id
                    WHERE pm.status IN $statusSql
                      AND pm.$dateCol BETWEEN ? AND ?
                      $noAllocClause
                      AND (pm.deal_id IS NULL OR pm.deal_id = 0)$companyClause
                    GROUP BY es.id, es.name";
            $st = $pdo->prepare($sql);
            $st->execute(array_merge($baseParams, $companyParams));
            foreach ($st->fetchAll(PDO::FETCH_ASSOC) ?: [] as $r) {
                $name = trim((string)($r['estate_name'] ?? ''));
                if ($name === '') continue;
                $inByEstate[$name] = (float)($inByEstate[$name] ?? 0) + (float)($r['total'] ?? 0);
            }
        }
    }
} catch (Throwable $e) { $inByEstate = []; }

$assignedInSum = 0.0;
foreach ($inByEstate as $v) { $assignedInSum += (float)$v; }
$unassignedIn = (float)$paymentsIn - $assignedInSum;
if ($unassignedIn > 0.005) { $inByEstate['Unassigned'] = $unassignedIn; }
arsort($inByEstate);

$keys = array_unique(array_merge(array_keys($inByEstate), array_keys($outByEstate)));
foreach ($keys as $k) {
    $inV = (float)($inByEstate[$k] ?? 0);
    $outV = (float)($outByEstate[$k] ?? 0);
    $profitByEstate[] = ['estate' => (string)$k, 'in' => $inV, 'out' => $outV, 'profit' => $inV - $outV];
}
usort($profitByEstate, function($a, $b) {
    $pa = (float)($a['profit'] ?? 0);
    $pb = (float)($b['profit'] ?? 0);
    if ($pa === $pb) return 0;
    return $pa < $pb ? 1 : -1;
});

$labels = array_values($trendLabels);
$seriesIn = array_values(array_map('floatval', $trendIn));
$seriesOut = array_values(array_map('floatval', $trendOut));
$seriesNet = array_values(array_map('floatval', $trendNet));
?>

<style>
    .cf-card { border: 0; border-radius: 1rem; overflow: hidden; }
    .cf-card .card-body { padding: 1.15rem 1.25rem; }
    .cf-label { font-size: .85rem; color: #64748b; }
    .cf-value { font-size: 1.25rem; font-weight: 800; color: #0f172a; }
    .cf-muted { color: #64748b; font-size: .85rem; }
    .cf-chart { height: 320px; }
    .cf-table td, .cf-table th { vertical-align: middle; }
</style>

<div class="container-fluid px-4 pb-4">
    <div class="d-flex flex-wrap justify-content-between align-items-center mt-4 mb-3 gap-2">
        <div>
            <h2 class="fw-bold mb-1">Cashflow Dashboard</h2>
            <div class="cf-muted">Approved-only inflows (payments) and outflows (expenses)</div>
        </div>
    </div>

    <div class="card shadow-sm mb-3">
        <div class="card-body">
            <form class="row g-3 align-items-end" method="GET">
                <div class="col-6 col-md-4">
                    <label class="form-label">Start Date</label>
                    <input type="date" class="form-control" name="start_date" value="<?= htmlspecialchars($startDate) ?>">
                </div>
                <div class="col-6 col-md-4">
                    <label class="form-label">End Date</label>
                    <input type="date" class="form-control" name="end_date" value="<?= htmlspecialchars($endDate) ?>">
                </div>
                <div class="col-12 col-md-4 d-grid">
                    <button class="btn btn-primary" type="submit">Apply</button>
                </div>
            </form>
        </div>
    </div>

    <div class="row g-3 mb-3">
        <div class="col-12 col-md-3">
            <div class="card cf-card shadow-sm">
                <div class="card-body">
                    <div class="cf-label">Opening Balance</div>
                    <div class="cf-value">₦<?= number_format($openingBalance, 2) ?></div>
                </div>
            </div>
        </div>
        <div class="col-12 col-md-3">
            <div class="card cf-card shadow-sm">
                <div class="card-body">
                    <div class="cf-label">Total Inflow</div>
                    <div class="cf-value text-success">₦<?= number_format($paymentsIn, 2) ?></div>
                </div>
            </div>
        </div>
        <div class="col-12 col-md-3">
            <div class="card cf-card shadow-sm">
                <div class="card-body">
                    <div class="cf-label">Total Outflow</div>
                    <div class="cf-value text-danger">₦<?= number_format($expensesOut, 2) ?></div>
                </div>
            </div>
        </div>
        <div class="col-12 col-md-3">
            <div class="card cf-card shadow-sm">
                <div class="card-body">
                    <div class="cf-label">Closing Balance</div>
                    <div class="cf-value">₦<?= number_format($closingBalance, 2) ?></div>
                </div>
            </div>
        </div>
    </div>

    <div class="row g-3">
        <div class="col-12 col-lg-8">
            <div class="card shadow-sm mb-3">
                <div class="card-header bg-white">
                    <div class="fw-bold">Monthly Trend</div>
                    <div class="cf-muted">Inflow, outflow and net (last 12 months)</div>
                </div>
                <div class="card-body">
                    <div class="cf-chart">
                        <canvas id="cashflowTrend"></canvas>
                    </div>
                </div>
            </div>
        </div>

        <div class="col-12 col-lg-4">
            <div class="card shadow-sm mb-3">
                <div class="card-header bg-white">
                    <div class="fw-bold">Net Summary</div>
                    <div class="cf-muted"><?= htmlspecialchars($startDate) ?> to <?= htmlspecialchars($endDate) ?></div>
                </div>
                <div class="card-body">
                    <div class="d-flex justify-content-between align-items-center mb-2">
                        <div class="cf-muted">Net Cashflow</div>
                        <div class="fw-bold"><?= ($net >= 0 ? '₦' . number_format($net, 2) : '-₦' . number_format(abs($net), 2)) ?></div>
                    </div>
                    <div class="d-flex justify-content-between align-items-center">
                        <div class="cf-muted">Book Balance (All Accounts)</div>
                        <div class="fw-bold">₦<?= number_format($bookBalance, 2) ?></div>
                    </div>
                </div>
            </div>

            <div class="card shadow-sm">
                <div class="card-header bg-white">
                    <div class="fw-bold">Outflow Breakdown</div>
                    <div class="cf-muted">Largest drivers this period</div>
                </div>
                <div class="card-body">
                    <div class="row g-3">
                        <div class="col-12">
                            <div class="fw-semibold mb-2">By Estate</div>
                            <div class="table-responsive">
                                <table class="table table-sm cf-table mb-0">
                                    <thead>
                                        <tr>
                                            <th>Estate</th>
                                            <th class="text-end">Amount</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php if (empty($outByEstate)): ?>
                                            <tr><td colspan="2" class="text-center text-muted py-3">No data</td></tr>
                                        <?php else: ?>
                                            <?php $i = 0; foreach ($outByEstate as $k => $v): $i++; if ($i > 8) break; ?>
                                                <tr>
                                                    <td><?= htmlspecialchars((string)$k) ?></td>
                                                    <td class="text-end">₦<?= number_format((float)$v, 2) ?></td>
                                                </tr>
                                            <?php endforeach; ?>
                                        <?php endif; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                        <div class="col-12">
                            <div class="fw-semibold mb-2">By Category</div>
                            <div class="table-responsive">
                                <table class="table table-sm cf-table mb-0">
                                    <thead>
                                        <tr>
                                            <th>Category</th>
                                            <th class="text-end">Amount</th>
                                        </tr>
                                    </thead>
                                    <tbody>
                                        <?php if (empty($outByCategory)): ?>
                                            <tr><td colspan="2" class="text-center text-muted py-3">No data</td></tr>
                                        <?php else: ?>
                                            <?php $i = 0; foreach ($outByCategory as $k => $v): $i++; if ($i > 8) break; ?>
                                                <tr>
                                                    <td><?= htmlspecialchars((string)$k) ?></td>
                                                    <td class="text-end">₦<?= number_format((float)$v, 2) ?></td>
                                                </tr>
                                            <?php endforeach; ?>
                                        <?php endif; ?>
                                    </tbody>
                                </table>
                            </div>
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <div class="card shadow-sm">
        <div class="card-header bg-white">
            <div class="fw-bold">Profit by Estate</div>
            <div class="cf-muted">Inflow (approved payments linked to an estate) minus outflow (approved expenses linked to an estate)</div>
        </div>
        <div class="card-body">
            <div class="table-responsive">
                <table class="table table-sm cf-table mb-0">
                    <thead>
                        <tr>
                            <th>Estate</th>
                            <th class="text-end">Inflow</th>
                            <th class="text-end">Outflow</th>
                            <th class="text-end">Profit</th>
                        </tr>
                    </thead>
                    <tbody>
                        <?php if (empty($profitByEstate)): ?>
                            <tr><td colspan="4" class="text-center text-muted py-3">No data</td></tr>
                        <?php else: ?>
                            <?php $i = 0; foreach ($profitByEstate as $r): $i++; if ($i > 15) break; ?>
                                <?php $p = (float)($r['profit'] ?? 0); ?>
                                <tr>
                                    <td><?= htmlspecialchars((string)($r['estate'] ?? '')) ?></td>
                                    <td class="text-end text-success">₦<?= number_format((float)($r['in'] ?? 0), 2) ?></td>
                                    <td class="text-end text-danger">₦<?= number_format((float)($r['out'] ?? 0), 2) ?></td>
                                    <td class="text-end <?= $p >= 0 ? 'text-success' : 'text-danger' ?>">
                                        <?= $p >= 0 ? '₦' . number_format($p, 2) : '-₦' . number_format(abs($p), 2) ?>
                                    </td>
                                </tr>
                            <?php endforeach; ?>
                        <?php endif; ?>
                    </tbody>
                </table>
            </div>
        </div>
    </div>
</div>

<script>
(function(){
    var el = document.getElementById('cashflowTrend');
    if (!el || !window.Chart) return;
    var labels = <?= json_encode($labels) ?>;
    var inflow = <?= json_encode($seriesIn) ?>;
    var outflow = <?= json_encode($seriesOut) ?>;
    var net = <?= json_encode($seriesNet) ?>;
    new Chart(el.getContext('2d'), {
        type: 'line',
        data: {
            labels: labels,
            datasets: [
                { label: 'Inflow', data: inflow, borderColor: '#16a34a', backgroundColor: 'rgba(22,163,74,0.12)', fill: true, tension: 0.35, pointRadius: 2 },
                { label: 'Outflow', data: outflow, borderColor: '#dc2626', backgroundColor: 'rgba(220,38,38,0.10)', fill: true, tension: 0.35, pointRadius: 2 },
                { label: 'Net', data: net, borderColor: '#2563eb', backgroundColor: 'rgba(37,99,235,0.08)', fill: false, tension: 0.35, pointRadius: 2 }
            ]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: { legend: { position: 'bottom' } },
            scales: {
                y: { beginAtZero: false, grid: { color: 'rgba(226,232,240,0.6)' } },
                x: { grid: { display: false } }
            }
        }
    });
})();
</script>

<?php require_once 'includes/footer.php'; ?>