<?php
if (!isset($conn)) {
    die('DB connection missing');
}

date_default_timezone_set('Asia/Kolkata');

$res = $conn->query("SELECT COUNT(*) AS c FROM vms");
$stats['total_vms'] = (int)$res->fetch_assoc()['c'];

$res = $conn->query("
    SELECT COUNT(*) AS c FROM vms
    WHERE last_heartbeat IS NOT NULL
      AND last_heartbeat >= NOW() - INTERVAL 5 MINUTE
");
$stats['online_vms'] = (int)$res->fetch_assoc()['c'];

$res = $conn->query("
    SELECT COUNT(*) AS c FROM vms
    WHERE LOWER(status) LIKE '%running%'
");
$stats['app_online'] = (int)$res->fetch_assoc()['c'];

$today_res = $conn->query("
    SELECT IFNULL(SUM(GREATEST(0, t.today_max - IFNULL(y.prev_max, 0))), 0) AS total
    FROM (
        SELECT vm_id, MAX(CAST(balance AS DECIMAL(10,4))) AS today_max
        FROM earnapp_history WHERE DATE(recorded_at) = CURDATE()
        GROUP BY vm_id
    ) t
    LEFT JOIN (
        SELECT vm_id, MAX(CAST(balance AS DECIMAL(10,4))) AS prev_max
        FROM earnapp_history WHERE DATE(recorded_at) = CURDATE() - INTERVAL 1 DAY
        GROUP BY vm_id
    ) y ON y.vm_id = t.vm_id
");
$stats['earning_today'] = (float)($today_res->fetch_assoc()['total'] ?? 0);

$yesterday_res = $conn->query("
    SELECT IFNULL(SUM(GREATEST(0, y.yest_max - IFNULL(p.prev_max, 0))), 0) AS total
    FROM (
        SELECT vm_id, MAX(CAST(balance AS DECIMAL(10,4))) AS yest_max
        FROM earnapp_history WHERE DATE(recorded_at) = CURDATE() - INTERVAL 1 DAY
        GROUP BY vm_id
    ) y
    LEFT JOIN (
        SELECT vm_id, MAX(CAST(balance AS DECIMAL(10,4))) AS prev_max
        FROM earnapp_history WHERE DATE(recorded_at) = CURDATE() - INTERVAL 2 DAY
        GROUP BY vm_id
    ) p ON p.vm_id = y.vm_id
");
$stats['earning_yesterday'] = (float)($yesterday_res->fetch_assoc()['total'] ?? 0);

$week_start = date('Y-m-d', strtotime('monday this week'));

$week_res = $conn->query("
    SELECT IFNULL(SUM(daily_earned), 0) AS total
    FROM (
        SELECT t.vm_id, t.dt,
               GREATEST(0, t.day_max - IFNULL(p.prev_day_max, 0)) AS daily_earned
        FROM (
            SELECT vm_id, DATE(recorded_at) AS dt,
                   MAX(CAST(balance AS DECIMAL(10,4))) AS day_max
            FROM earnapp_history
            WHERE DATE(recorded_at) BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE()
            GROUP BY vm_id, DATE(recorded_at)
        ) t
        LEFT JOIN (
            SELECT vm_id, DATE(recorded_at) AS dt,
                   MAX(CAST(balance AS DECIMAL(10,4))) AS prev_day_max
            FROM earnapp_history
            GROUP BY vm_id, DATE(recorded_at)
        ) p ON p.vm_id = t.vm_id AND p.dt = DATE_SUB(t.dt, INTERVAL 1 DAY)
    ) x
");
$stats['earning_week'] = (float)($week_res->fetch_assoc()['total'] ?? 0);

$month_start = date('Y-m-01');
$month_res = $conn->query("
    SELECT IFNULL(SUM(daily_earned), 0) AS total
    FROM (
        SELECT t.vm_id, t.dt,
               GREATEST(0, t.day_max - IFNULL(p.prev_day_max, 0)) AS daily_earned
        FROM (
            SELECT vm_id, DATE(recorded_at) AS dt,
                   MAX(CAST(balance AS DECIMAL(10,4))) AS day_max
            FROM earnapp_history
            WHERE DATE(recorded_at) BETWEEN '$month_start' AND CURDATE()
            GROUP BY vm_id, DATE(recorded_at)
        ) t
        LEFT JOIN (
            SELECT vm_id, DATE(recorded_at) AS dt,
                   MAX(CAST(balance AS DECIMAL(10,4))) AS prev_day_max
            FROM earnapp_history
            GROUP BY vm_id, DATE(recorded_at)
        ) p ON p.vm_id = t.vm_id AND p.dt = DATE_SUB(t.dt, INTERVAL 1 DAY)
    ) x
");
$stats['earning_month'] = (float)($month_res->fetch_assoc()['total'] ?? 0);

$res = $conn->query("SELECT IFNULL(SUM(balance),0) AS total FROM vms");
$total_lifetime = (float)$res->fetch_assoc()['total'];
?>

<div class="row mb-4 flex-nowrap">
    <div class="col">
        <div class="card bg-dark text-white p-3 border-danger text-center">
            <h6>Total VMs</h6>
            <h3><?= $stats['total_vms'] ?></h3>
        </div>
    </div>
    <div class="col">
        <div class="card bg-dark text-white p-3 border-success text-center">
            <h6>Online</h6>
            <h3><?= $stats['online_vms'] ?></h3>
        </div>
    </div>
    <div class="col">
        <div class="card bg-dark text-white p-3 border-success text-center">
            <h6>App Online</h6>
            <h3><?= $stats['app_online'] ?></h3>
        </div>
    </div>
    <div class="col">
        <div class="card bg-dark text-white p-3 border-warning text-center">
            <h6>Today</h6>
            <h3>$<?= number_format($stats['earning_today'],2) ?></h3>
        </div>
    </div>
    <div class="col">
        <div class="card bg-dark text-white p-3 border-secondary text-center">
            <h6>Yesterday</h6>
            <h3>$<?= number_format($stats['earning_yesterday'],2) ?></h3>
        </div>
    </div>
    <div class="col">
        <div class="card bg-dark text-white p-3 border-primary text-center">
            <h6>This Week</h6>
            <h3>$<?= number_format($stats['earning_week'],2) ?></h3>
        </div>
    </div>
    <div class="col">
        <div class="card bg-dark text-white p-3 border-info text-center">
            <h6>This Month</h6>
            <h3>$<?= number_format($stats['earning_month'],2) ?></h3>
        </div>
    </div>
</div>