<?php
/**
 * weekly_report.php — Relatório semanal de uptime por usuário
 * Executado pelo worker cron toda segunda-feira (ou via CLI)
 */
require_once __DIR__ . '/db.php';
require_once __DIR__ . '/includes/functions.php';

$pdo = db();

// Busca todos os usuários ativos com e-mail verificado
$users = $pdo->query("
    SELECT u.id, u.name, u.email, u.timezone
    FROM users u
    WHERE u.active = TRUE AND u.email_verified = TRUE
")->fetchAll();

$sent = 0;
$failed = 0;

foreach ($users as $user) {
    try {
        // Estatísticas da última semana
        $statsStmt = $pdo->prepare("
            SELECT
                COUNT(DISTINCT mi.id) AS total_hosts,
                SUM(CASE WHEN mi.last_status = 'online' THEN 1 ELSE 0 END) AS online_now,
                SUM(CASE WHEN mi.last_status = 'offline' THEN 1 ELSE 0 END) AS offline_now,
                ROUND(
                    100.0 * SUM(CASE WHEN pl.status = 'online' THEN 1 ELSE 0 END) /
                    NULLIF(COUNT(pl.id), 0), 2
                ) AS uptime_pct,
                ROUND(AVG(pl.latency_ms) FILTER (WHERE pl.status='online'), 1) AS avg_latency
            FROM monitored_ips mi
            LEFT JOIN ping_logs pl ON pl.monitored_ip_id = mi.id
                AND pl.checked_at >= NOW() - INTERVAL '7 days'
            WHERE mi.user_id = ? AND mi.active = TRUE
        ");
        $statsStmt->execute([$user['id']]);
        $stats = $statsStmt->fetch();

        if (!$stats || (int)$stats['total_hosts'] === 0) continue;

        // TOP 5 hosts com mais tempo offline na semana
        $worstStmt = $pdo->prepare("
            SELECT
                mi.label,
                mi.host,
                ROUND(
                    100.0 * SUM(CASE WHEN pl.status='online' THEN 1 ELSE 0 END) /
                    NULLIF(COUNT(pl.id), 0), 2
                ) AS uptime_pct,
                ROUND(AVG(pl.latency_ms) FILTER (WHERE pl.status='online'), 1) AS avg_lat,
                SUM(CASE WHEN pl.status='offline' THEN 1 ELSE 0 END) AS offline_count
            FROM monitored_ips mi
            JOIN ping_logs pl ON pl.monitored_ip_id = mi.id
                AND pl.checked_at >= NOW() - INTERVAL '7 days'
            WHERE mi.user_id = ? AND mi.active = TRUE
            GROUP BY mi.id, mi.label, mi.host
            ORDER BY uptime_pct ASC NULLS LAST
            LIMIT 5
        ");
        $worstStmt->execute([$user['id']]);
        $worstHosts = $worstStmt->fetchAll();

        // Conta alertas enviados na semana
        $alertsStmt = $pdo->prepare("
            SELECT COUNT(*) FROM alerts_sent a
            JOIN monitored_ips mi ON mi.id = a.monitored_ip_id
            WHERE mi.user_id = ? AND a.sent_at >= NOW() - INTERVAL '7 days'
        ");
        $alertsStmt->execute([$user['id']]);
        $weekAlerts = (int)$alertsStmt->fetchColumn();

        $tz = $user['timezone'] ?: 'America/Sao_Paulo';
        $weekEnd = (new DateTimeImmutable('now', new DateTimeZone($tz)))->format('d/m/Y');
        $weekStart = (new DateTimeImmutable('-7 days', new DateTimeZone($tz)))->format('d/m/Y');

        $uptimePct = $stats['uptime_pct'] !== null ? number_format((float)$stats['uptime_pct'], 2) : '—';
        $avgLat = $stats['avg_latency'] !== null ? number_format((float)$stats['avg_latency'], 1) . ' ms' : '—';
        $uptimeColor = (float)($stats['uptime_pct'] ?? 0) >= 99 ? '#22c55e' : ((float)($stats['uptime_pct'] ?? 0) >= 95 ? '#f59e0b' : '#ef4444');

        // Monta tabela de piores hosts no HTML
        $worstRows = '';
        foreach ($worstHosts as $h) {
            $hUp = $h['uptime_pct'] !== null ? number_format((float)$h['uptime_pct'], 2) . '%' : '—';
            $hLat = $h['avg_lat'] !== null ? number_format((float)$h['avg_lat'], 1) . ' ms' : '—';
            $hColor = (float)($h['uptime_pct'] ?? 0) >= 99 ? '#22c55e' : ((float)($h['uptime_pct'] ?? 0) >= 95 ? '#f59e0b' : '#ef4444');
            $worstRows .= "
            <tr>
              <td style='padding:8px 10px;border-bottom:1px solid #e2e8f0;'>" . htmlspecialchars($h['label']) . "</td>
              <td style='padding:8px 10px;border-bottom:1px solid #e2e8f0;font-size:12px;color:#64748b;'>" . htmlspecialchars($h['host']) . "</td>
              <td style='padding:8px 10px;border-bottom:1px solid #e2e8f0;font-weight:600;color:{$hColor};'>{$hUp}</td>
              <td style='padding:8px 10px;border-bottom:1px solid #e2e8f0;'>{$hLat}</td>
            </tr>";
        }

        $userName = htmlspecialchars($user['name']);
        $html = <<<HTML
<!DOCTYPE html>
<html lang="pt-BR">
<body style="margin:0;padding:0;background:#f5f7fb;">
  <table role="presentation" width="100%" cellpadding="0" cellspacing="0" style="background:#f5f7fb;padding:24px 0;">
    <tr><td align="center">
      <table role="presentation" width="560" cellpadding="0" cellspacing="0" style="background:#ffffff;border-radius:18px;padding:32px;font-family:'Inter',Arial,sans-serif;color:#0f172a;border:1px solid #e2e8f0;">
        <!-- Logo -->
        <tr><td align="center" style="padding-bottom:8px;">
          <div style="background:#2563eb;border-radius:12px;width:48px;height:48px;display:inline-flex;align-items:center;justify-content:center;">
            <span style="font-size:24px;">📡</span>
          </div>
        </td></tr>
        <tr><td align="center" style="padding-bottom:4px;">
          <h2 style="margin:0;font-size:20px;">Relatório Semanal</h2>
        </td></tr>
        <tr><td align="center" style="padding-bottom:24px;">
          <small style="color:#64748b;">{$weekStart} a {$weekEnd}</small>
        </td></tr>

        <!-- Saudação -->
        <tr><td style="padding-bottom:20px;font-size:15px;color:#334155;">
          <p style="margin:0;">Olá, <strong>{$userName}</strong>! Aqui está o resumo de monitoramento da última semana.</p>
        </td></tr>

        <!-- Cards de estatísticas -->
        <tr><td style="padding-bottom:24px;">
          <table width="100%" cellspacing="8" cellpadding="0">
            <tr>
              <td style="background:#f8fafc;border:1px solid #e2e8f0;border-radius:12px;padding:16px;text-align:center;width:25%;">
                <div style="font-size:24px;font-weight:700;color:{$uptimeColor};">{$uptimePct}%</div>
                <div style="font-size:12px;color:#64748b;margin-top:4px;">Uptime geral</div>
              </td>
              <td style="background:#f8fafc;border:1px solid #e2e8f0;border-radius:12px;padding:16px;text-align:center;width:25%;">
                <div style="font-size:24px;font-weight:700;color:#2563eb;">{$stats['total_hosts']}</div>
                <div style="font-size:12px;color:#64748b;margin-top:4px;">Hosts monitorados</div>
              </td>
              <td style="background:#f8fafc;border:1px solid #e2e8f0;border-radius:12px;padding:16px;text-align:center;width:25%;">
                <div style="font-size:24px;font-weight:700;color:#f59e0b;">{$avgLat}</div>
                <div style="font-size:12px;color:#64748b;margin-top:4px;">Latência média</div>
              </td>
              <td style="background:#f8fafc;border:1px solid #e2e8f0;border-radius:12px;padding:16px;text-align:center;width:25%;">
                <div style="font-size:24px;font-weight:700;color:#ef4444;">{$weekAlerts}</div>
                <div style="font-size:12px;color:#64748b;margin-top:4px;">Alertas enviados</div>
              </td>
            </tr>
          </table>
        </td></tr>

        <!-- Tabela de hosts -->
        <tr><td>
          <p style="margin:0 0 8px;font-weight:600;font-size:14px;">Hosts com menor uptime na semana:</p>
          <table width="100%" style="border:1px solid #e2e8f0;border-radius:10px;border-collapse:collapse;font-size:13px;">
            <thead>
              <tr style="background:#f8fafc;">
                <th style="padding:8px 10px;text-align:left;border-bottom:1px solid #e2e8f0;">Host</th>
                <th style="padding:8px 10px;text-align:left;border-bottom:1px solid #e2e8f0;">Endereço</th>
                <th style="padding:8px 10px;text-align:left;border-bottom:1px solid #e2e8f0;">Uptime</th>
                <th style="padding:8px 10px;text-align:left;border-bottom:1px solid #e2e8f0;">Latência</th>
              </tr>
            </thead>
            <tbody>{$worstRows}</tbody>
          </table>
        </td></tr>

        <tr><td style="padding-top:24px;text-align:center;font-size:13px;color:#94a3b8;">
          <p>MonitorPing — Relatório automático semanal</p>
        </td></tr>
      </table>
    </td></tr>
  </table>
</body>
</html>
HTML;

        $subject = "[MonitorPing] Relatório semanal ({$weekStart} a {$weekEnd}) — Uptime: {$uptimePct}%";
        $err = null;
        if (send_email_message($user['email'], $subject, strip_tags($html), $err, $html)) {
            $sent++;
            mp_log('info', "Relatório semanal enviado para {$user['email']}");
        } else {
            $failed++;
            mp_log('warning', "Relatório semanal FALHOU para {$user['email']}: {$err}");
        }

    } catch (Throwable $e) {
        $failed++;
        mp_log('error', "Relatório semanal erro para user {$user['id']}: " . $e->getMessage());
    }
}

echo "Relatório semanal: {$sent} enviados, {$failed} falhas.\n";
