<?php
/**
 * Envío de reportes semanales por email.
 *
 * Ejecutar cada 5 minutos via cPanel Cron Jobs:
 *   *\/5 * * * *   php /home/kuos/public_html/zerotrustdns.pcassi.ar/report_cron.php
 *
 * También puede forzarse via URL:
 *   https://tu-dominio.com/report_cron.php?token=CAMBIAR_SECRET_CRON&force=1
 */

if (PHP_SAPI !== 'cli') {
    require_once __DIR__ . '/config.php';
    $token = $_GET['token'] ?? '';
    if ($token !== CRON_SECRET) { http_response_code(403); die('Acceso denegado'); }
} else {
    require_once __DIR__ . '/config.php';
}

require_once __DIR__ . '/db.php';
require_once __DIR__ . '/helpers.php';
require_once __DIR__ . '/report.php';
require_once __DIR__ . '/mailer.php';

$pdo   = db();
$force = !empty($_GET['force']) || in_array('--force', $argv ?? []);

$log = function(string $msg) {
    $ts = date('[Y-m-d H:i:s]');
    if (PHP_SAPI === 'cli') echo $ts . ' ' . $msg . PHP_EOL;
    error_log('report_cron: ' . $msg);
};

// Auto-crear tabla report_config si no existe
try {
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS report_config (
            id           INT AUTO_INCREMENT PRIMARY KEY,
            user_id      INT NOT NULL UNIQUE,
            emails       TEXT NOT NULL DEFAULT '',
            day_of_week  TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '0=Dom,1=Lun,...,6=Sab',
            send_hour    TINYINT UNSIGNED NOT NULL DEFAULT 8,
            send_minute  TINYINT UNSIGNED NOT NULL DEFAULT 0,
            active       TINYINT(1) NOT NULL DEFAULT 0,
            last_sent_at DATETIME NULL,
            created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ");
} catch (PDOException $e) {
    $log("WARN tabla report_config: " . $e->getMessage());
}

// Migrar tablas existentes que no tengan la columna send_minute
// Usamos ADD COLUMN sin IF NOT EXISTS (compatible con MySQL 5.x/8.x y MariaDB)
try {
    $pdo->exec("ALTER TABLE report_config ADD COLUMN send_minute TINYINT UNSIGNED NOT NULL DEFAULT 0");
    $log("Columna send_minute creada.");
} catch (PDOException $e) {
    // Error 1060 = "Duplicate column name" → columna ya existe, ignorar
    if (strpos($e->getMessage(), '1060') === false && strpos($e->getMessage(), 'Duplicate column') === false) {
        $log("WARN ALTER TABLE send_minute: " . $e->getMessage());
    }
}

$currentDow  = (int)date('w'); // 0=Sun, 1=Mon, ... 6=Sat
$currentHour = (int)date('G'); // 0-23
// Redondear al múltiplo de 5 más cercano para tolerar leves desfases del cron
$currentMin  = (int)round((int)date('i') / 5) * 5;
if ($currentMin >= 60) $currentMin = 55;

$log("=== Iniciando report_cron — DOW=$currentDow HOUR=$currentHour MIN=$currentMin" . ($force ? " [FORZADO]" : "") . " ===");

// Buscar configs activas que correspondan al día/hora/minuto actual
$queryWithMin = $force
    ? "SELECT rc.*, u.display_name, u.email AS user_email FROM report_config rc JOIN users u ON u.id = rc.user_id WHERE rc.active = 1"
    : "SELECT rc.*, u.display_name, u.email AS user_email FROM report_config rc JOIN users u ON u.id = rc.user_id WHERE rc.active = 1 AND rc.day_of_week = ? AND rc.send_hour = ? AND rc.send_minute = ?";

try {
    $stmt = $pdo->prepare($queryWithMin);
    $force ? $stmt->execute() : $stmt->execute([$currentDow, $currentHour, $currentMin]);
    $configs = $stmt->fetchAll();
} catch (PDOException $e) {
    // send_minute column may still not exist — fall back to hour-only matching
    $log("WARN query con send_minute falló, usando fallback hora-solamente: " . $e->getMessage());
    $queryFallback = $force
        ? "SELECT rc.*, u.display_name, u.email AS user_email FROM report_config rc JOIN users u ON u.id = rc.user_id WHERE rc.active = 1"
        : "SELECT rc.*, u.display_name, u.email AS user_email FROM report_config rc JOIN users u ON u.id = rc.user_id WHERE rc.active = 1 AND rc.day_of_week = ? AND rc.send_hour = ?";
    $stmt = $pdo->prepare($queryFallback);
    $force ? $stmt->execute() : $stmt->execute([$currentDow, $currentHour]);
    $configs = $stmt->fetchAll();
}

$log("Configs a procesar: " . count($configs));

foreach ($configs as $cfg) {
    $userId   = (int)$cfg['user_id'];
    $userName = $cfg['display_name'];
    $emails   = array_filter(array_map('trim', explode(',', $cfg['emails'])));

    if (empty($emails)) {
        $log("  SKIP usuario $userName (sin emails configurados)");
        continue;
    }

    $log("  Generando reporte para: $userName (IDs: " . implode(',', array_map(fn($e) => $e, $emails)) . ")");

    $html = generateReport($pdo, $userId, 7);
    if (!$html) {
        $log("  ERROR: no se pudo generar el reporte para $userName");
        continue;
    }

    $subject = "Informe Semanal DNS – $userName – " . date('d/m/Y');

    $sent = 0;
    foreach ($emails as $email) {
        if (!filter_var($email, FILTER_VALIDATE_EMAIL)) {
            $log("  SKIP email inválido: $email");
            continue;
        }
        try {
            sendMail($pdo, $email, $subject, $html);
            $log("  OK email enviado a $email");
            $sent++;
        } catch (Throwable $e) {
            $log("  ERROR enviando a $email: " . $e->getMessage());
        }
    }

    if ($sent > 0) {
        $pdo->prepare("UPDATE report_config SET last_sent_at = NOW() WHERE user_id = ?")
            ->execute([$userId]);
    }
}

$log("=== report_cron completado ===");
