<?php
require_once __DIR__ . '/../includes/header.php';
require_admin();
$pdo = db();

// ── Receita estimada por plano ─────────────────────────────────────────────────
$revenueByPlan = $pdo->query("
    SELECT p.name, p.price,
           COUNT(s.id) AS subscribers,
           COUNT(s.id) * p.price AS monthly_revenue
    FROM plans p
    LEFT JOIN subscriptions s ON s.plan_id = p.id
        AND (s.expires_at IS NULL OR s.expires_at > NOW())
    WHERE p.active = TRUE
    GROUP BY p.id, p.name, p.price
    ORDER BY p.price DESC
")->fetchAll();

$totalMRR = array_sum(array_column($revenueByPlan, 'monthly_revenue'));

// ── Usuários por plano ─────────────────────────────────────────────────────────
$totalActive   = (int)$pdo->query("SELECT COUNT(*) FROM users WHERE active = TRUE")->fetchColumn();
$totalFree     = (int)$pdo->query("
    SELECT COUNT(DISTINCT s.user_id) FROM subscriptions s
    JOIN plans p ON p.id = s.plan_id WHERE p.price = 0
")->fetchColumn();
$totalPaid     = (int)$pdo->query("
    SELECT COUNT(DISTINCT s.user_id) FROM subscriptions s
    JOIN plans p ON p.id = s.plan_id WHERE p.price > 0
    AND (s.expires_at IS NULL OR s.expires_at > NOW())
")->fetchColumn();

// ── Atividade dos últimos 30 dias ──────────────────────────────────────────────
$activeUsers30 = (int)$pdo->query("
    SELECT COUNT(DISTINCT user_id) FROM monitored_ips
    WHERE created_at >= NOW() - INTERVAL '30 days'
       OR last_check_at >= NOW() - INTERVAL '30 days'
")->fetchColumn();

// ── Checks por dia (últimos 14 dias) ──────────────────────────────────────────
$checksStmt = $pdo->query("
    SELECT date_trunc('day', checked_at)::date AS day, COUNT(*) AS checks
    FROM ping_logs
    WHERE checked_at >= NOW() - INTERVAL '14 days'
    GROUP BY day ORDER BY day
");
$checksRows = $checksStmt->fetchAll();
$checksLabels = array_column($checksRows, 'day');
$checksValues = array_map('intval', array_column($checksRows, 'checks'));

// ── Top 5 usuários mais ativos ─────────────────────────────────────────────────
$topUsers = $pdo->query("
    SELECT u.email, u.name, p.name AS plan_name,
           COUNT(mi.id) AS ip_count,
           SUM(CASE WHEN mi.last_status = 'offline' THEN 1 ELSE 0 END) AS offline_count
    FROM users u
    LEFT JOIN monitored_ips mi ON mi.user_id = u.id AND mi.active = TRUE
    LEFT JOIN subscriptions s ON s.user_id = u.id
    LEFT JOIN plans p ON p.id = s.plan_id
    WHERE u.active = TRUE
    GROUP BY u.id, u.email, u.name, p.name
    ORDER BY ip_count DESC
    LIMIT 10
")->fetchAll();

// ── IPs com SSL expirando em breve ─────────────────────────────────────────────
$sslExpiring = $pdo->query("
    SELECT mi.label, mi.host, mi.ssl_expires_at, u.email
    FROM monitored_ips mi
    JOIN users u ON u.id = mi.user_id
    WHERE mi.ssl_check_enabled = TRUE
      AND mi.ssl_expires_at IS NOT NULL
      AND mi.ssl_expires_at <= CURRENT_DATE + INTERVAL '30 days'
    ORDER BY mi.ssl_expires_at ASC
    LIMIT 20
")->fetchAll();

// ── Webhooks em retry ─────────────────────────────────────────────────────────
$pendingRetries = (int)$pdo->query("SELECT COUNT(*) FROM webhook_retries WHERE attempts < 3")->fetchColumn();

// ── Canais de alerta por tipo ─────────────────────────────────────────────────
$channelDist = $pdo->query("
    SELECT type, COUNT(*) AS cnt
    FROM alert_channels WHERE active = TRUE
    GROUP BY type ORDER BY cnt DESC
")->fetchAll();
?>

<div class="card-grid stats-grid">
    <div class="stats-card">
        <small>MRR Estimado</small>
        <strong style="color:var(--success);">R$ <?php echo number_format($totalMRR, 2, ',', '.'); ?></strong>
        <p>Receita mensal recorrente</p>
    </div>
    <div class="stats-card">
        <small>Usuários pagantes</small>
        <strong><?php echo $totalPaid; ?> / <?php echo $totalActive; ?></strong>
        <p><?php echo $totalFree; ?> no plano gratuito</p>
    </div>
    <div class="stats-card">
        <small>Usuários ativos (30d)</small>
        <strong><?php echo $activeUsers30; ?></strong>
        <p>Com atividade recente</p>
    </div>
    <div class="stats-card">
        <small>Webhooks em retry</small>
        <strong style="color:<?php echo $pendingRetries > 0 ? 'var(--warning)' : 'var(--success)'; ?>;"><?php echo $pendingRetries; ?></strong>
        <p>Falhas aguardando reenvio</p>
    </div>
</div>

<!-- MRR por plano -->
<div class="card">
    <h2>💰 Receita por Plano</h2>
    <p>Estimativa de MRR baseada nas assinaturas ativas.</p>
    <table>
        <thead>
            <tr><th>Plano</th><th>Preço</th><th>Assinantes</th><th>MRR</th><th>% da receita</th></tr>
        </thead>
        <tbody>
            <?php foreach ($revenueByPlan as $plan):
                $planPct = $totalMRR > 0 ? round($plan['monthly_revenue'] / $totalMRR * 100, 1) : 0;
            ?>
            <tr>
                <td><strong><?php echo h($plan['name']); ?></strong></td>
                <td>R$ <?php echo number_format($plan['price'], 2, ',', '.'); ?>/mês</td>
                <td><?php echo $plan['subscribers']; ?></td>
                <td><strong style="color:var(--success);">R$ <?php echo number_format($plan['monthly_revenue'], 2, ',', '.'); ?></strong></td>
                <td>
                    <div style="display:flex;align-items:center;gap:8px;">
                        <div style="flex:1;background:var(--border);border-radius:4px;height:8px;">
                            <div style="width:<?php echo $planPct; ?>%;background:var(--primary);height:8px;border-radius:4px;"></div>
                        </div>
                        <span style="font-size:12px;"><?php echo $planPct; ?>%</span>
                    </div>
                </td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>

<!-- Checks por dia -->
<div class="card chart-card">
    <div class="card-head">
        <h2>📈 Checks por dia (últimos 14 dias)</h2>
        <p>Volume de verificações realizadas pelo sistema.</p>
    </div>
    <canvas id="checksChart" style="max-height:200px;"></canvas>
</div>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
new Chart(document.getElementById('checksChart'), {
    type: 'line',
    data: {
        labels: <?php echo json_encode($checksLabels); ?>,
        datasets: [{
            label: 'Checks/dia',
            data: <?php echo json_encode($checksValues); ?>,
            borderColor: '#2563eb',
            backgroundColor: 'rgba(37,99,235,0.1)',
            fill: true,
            tension: 0.3,
            pointRadius: 4
        }]
    },
    options: { plugins: { legend: { display: false } }, scales: { y: { beginAtZero: true } } }
});
</script>

<!-- Distribuição de canais de alerta -->
<div class="card">
    <h2>📣 Canais de alerta ativos</h2>
    <div style="display:flex;gap:12px;flex-wrap:wrap;">
        <?php foreach ($channelDist as $ch): ?>
        <div class="stats-card" style="flex:1;min-width:120px;">
            <small><?php echo strtoupper(h($ch['type'])); ?></small>
            <strong><?php echo $ch['cnt']; ?></strong>
            <p>canais ativos</p>
        </div>
        <?php endforeach; ?>
    </div>
</div>

<!-- Top usuários -->
<div class="card">
    <h2>👤 Top usuários por IPs monitorados</h2>
    <table>
        <thead>
            <tr><th>Usuário</th><th>Plano</th><th>IPs</th><th>Offline agora</th></tr>
        </thead>
        <tbody>
            <?php foreach ($topUsers as $u): ?>
            <tr>
                <td>
                    <div style="font-weight:600;"><?php echo h($u['name']); ?></div>
                    <div style="font-size:12px;color:var(--text-secondary);"><?php echo h($u['email']); ?></div>
                </td>
                <td><?php echo h($u['plan_name'] ?? '—'); ?></td>
                <td><?php echo $u['ip_count']; ?></td>
                <td>
                    <?php if ($u['offline_count'] > 0): ?>
                    <span class="status-offline"><?php echo $u['offline_count']; ?> offline</span>
                    <?php else: ?>
                    <span class="status-online">Todos online</span>
                    <?php endif; ?>
                </td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>

<!-- SSL expirando -->
<?php if (!empty($sslExpiring)): ?>
<div class="card">
    <h2>🔒 Certificados SSL expirando em breve</h2>
    <table>
        <thead>
            <tr><th>Host</th><th>Usuário</th><th>Expira em</th><th>Dias restantes</th></tr>
        </thead>
        <tbody>
            <?php foreach ($sslExpiring as $ssl):
                $exp = new DateTimeImmutable($ssl['ssl_expires_at']);
                $days = (int)(new DateTimeImmutable())->diff($exp)->days;
                $expired = $exp < new DateTimeImmutable();
                $color = $expired ? '#ef4444' : ($days <= 7 ? '#ef4444' : ($days <= 14 ? '#f97316' : '#f59e0b'));
            ?>
            <tr>
                <td>
                    <div><?php echo h($ssl['label']); ?></div>
                    <div style="font-size:12px;color:var(--text-secondary);"><?php echo h($ssl['host']); ?></div>
                </td>
                <td><?php echo h($ssl['email']); ?></td>
                <td><?php echo $exp->format('d/m/Y'); ?></td>
                <td style="color:<?php echo $color; ?>;font-weight:700;">
                    <?php echo $expired ? 'EXPIRADO' : $days . ' dias'; ?>
                </td>
            </tr>
            <?php endforeach; ?>
        </tbody>
    </table>
</div>
<?php endif; ?>

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