<?php
// api/billing-cron.php — cron diario 8am: grace period, desactivaciones, digest equipo
// Cron: 0 8 * * * /usr/local/bin/php /home/dimark93/public_html/supercerebro/api/billing-cron.php >> logs/billing-cron.log 2>&1

if (php_sapi_name() !== 'cli' && ($_GET['secret'] ?? '') !== CRON_SECRET) exit('Forbidden');
require_once __DIR__ . '/../config.php';
require_once __DIR__ . '/../db.php';
require_once __DIR__ . '/../helpers/brevo.php';
require_once __DIR__ . '/../helpers/tracking.php';

$now   = new DateTime();
$today = $now->format('Y-m-d');
$log   = [];

// ── 1. Trials que terminan hoy → enviar email + marcar expirado ──
$expiring = $pdo->query("SELECT user_id FROM dm_subscriptions WHERE status='trial' AND DATE(trial_ends_at) <= '$today'")->fetchAll();
foreach ($expiring as $r) {
    $uid = (int)$r['user_id'];
    $pdo->prepare("UPDATE dm_subscriptions SET status='canceled', updated_at=NOW() WHERE user_id=? AND status='trial'")->execute([$uid]);
    brevo_send($uid, 'trial_expired');
    dm_track_event($uid, DM_EVT_TRIAL_EXPIRED);
    $log[] = "Trial expirado uid=$uid";
}

// ── 2. Trials día 1 → email bienvenida ───────────────────────
$day1 = $pdo->query("SELECT user_id FROM dm_subscriptions WHERE status='trial' AND DATE(created_at)='$today'")->fetchAll();
foreach ($day1 as $r) {
    brevo_send((int)$r['user_id'], 'welcome');
    $log[] = "Bienvenida uid={$r['user_id']}";
}

// ── 3. Trials día 3 → email ayuda BYOK ──────────────────────
$d3 = date('Y-m-d', strtotime('-3 days'));
$day3 = $pdo->query("SELECT user_id FROM dm_subscriptions WHERE status='trial' AND DATE(created_at)='$d3'")->fetchAll();
foreach ($day3 as $r) {
    // Solo si no conectó BYOK aún
    $byok = $pdo->prepare("SELECT id FROM dm_events WHERE user_id=? AND event_type='byok_connected' LIMIT 1");
    $byok->execute([(int)$r['user_id']]);
    if (!$byok->fetch()) {
        brevo_send((int)$r['user_id'], 'trial_day3');
        $log[] = "Day3 BYOK uid={$r['user_id']}";
    }
}

// ── 4. Trials día 7 → email "expira hoy" ────────────────────
$d7 = date('Y-m-d', strtotime('-7 days'));
$day7 = $pdo->query("SELECT user_id FROM dm_subscriptions WHERE status='trial' AND DATE(created_at)='$d7'")->fetchAll();
foreach ($day7 as $r) {
    brevo_send((int)$r['user_id'], 'trial_day7');
    $log[] = "Day7 uid={$r['user_id']}";
}

// ── 5. Grace period: past_due > 3 días → modo bloqueado ──────
$grace = $pdo->query("SELECT user_id FROM dm_subscriptions WHERE status='past_due' AND updated_at < DATE_SUB(NOW(), INTERVAL 3 DAY)")->fetchAll();
foreach ($grace as $r) {
    $pdo->prepare("UPDATE dm_subscriptions SET status='blocked', updated_at=NOW() WHERE user_id=?")->execute([(int)$r['user_id']]);
    $log[] = "Bloqueado por grace uid={$r['user_id']}";
}

// ── 6. Renewal reminders para clientes activos (7/3/1 días) ──
$_amounts = ['starter' => 49, 'pro' => 200, 'agency' => 280, 'enterprise' => 500];
foreach ([7, 3, 1] as $_days) {
    $_dt = date('Y-m-d', strtotime("+{$_days} days"));
    $_renew = $pdo->query("SELECT user_id, plan FROM dm_subscriptions WHERE status='active' AND DATE(current_period_end)='$_dt'")->fetchAll();
    foreach ($_renew as $_r) {
        $_uid  = (int)$_r['user_id'];
        $_plan = $_r['plan'];
        brevo_send($_uid, 'renewal_reminder', [
            'days'       => $_days,
            'date'       => date('d/m/Y', strtotime("+{$_days} days")),
            'amount'     => $_amounts[$_plan] ?? 49,
            'portal_url' => 'https://dimark.co/supercerebro/',
        ]);
        $log[] = "Aviso renovacion {$_days}d uid={$_uid}";
    }
}

// ── 7. Recalcular engagement scores ──────────────────────────
$trials = $pdo->query("SELECT user_id FROM dm_subscriptions WHERE status='trial'")->fetchAll();
foreach ($trials as $r) _dm_update_engagement_score((int)$r['user_id']);

// ── 8. Digest diario al equipo ────────────────────────────────
$stats = [
    'new_today'    => (int)$pdo->query("SELECT COUNT(*) FROM dm_subscriptions WHERE DATE(created_at)='$today'")->fetchColumn(),
    'trials_active'=> (int)$pdo->query("SELECT COUNT(*) FROM dm_subscriptions WHERE status='trial'")->fetchColumn(),
    'active_paid'  => (int)$pdo->query("SELECT COUNT(*) FROM dm_subscriptions WHERE status='active'")->fetchColumn(),
    'past_due'     => (int)$pdo->query("SELECT COUNT(*) FROM dm_subscriptions WHERE status='past_due'")->fetchColumn(),
    'expiring_3d'  => (int)$pdo->query("SELECT COUNT(*) FROM dm_subscriptions WHERE status='trial' AND trial_ends_at BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 3 DAY)")->fetchColumn(),
    'high_score'   => (int)$pdo->query("SELECT COUNT(*) FROM dm_subscriptions WHERE status='trial' AND engagement_score >= 70")->fetchColumn(),
];
$mrr_row = $pdo->query("SELECT SUM(CASE plan WHEN 'starter' THEN 49 WHEN 'pro' THEN 200 WHEN 'agency' THEN 280 WHEN 'enterprise' THEN 500 END) as mrr FROM dm_subscriptions WHERE status='active'")->fetch();
$stats['mrr'] = (float)($mrr_row['mrr'] ?? 0);

$digest_body = "
<h2>☀️ Digest SuperCerebro — $today</h2>
<table cellpadding='8' style='border-collapse:collapse;width:100%'>
<tr style='background:#f5f5f5'><td><strong>MRR actual</strong></td><td><strong>\${$stats['mrr']}</strong></td></tr>
<tr><td>Nuevos registros hoy</td><td>{$stats['new_today']}</td></tr>
<tr><td>Trials activos</td><td>{$stats['trials_active']}</td></tr>
<tr><td>🔥 Trials con score ≥70</td><td>{$stats['high_score']}</td></tr>
<tr><td>Trials expiran en 3 días</td><td>{$stats['expiring_3d']}</td></tr>
<tr><td>Cuentas activas (pago)</td><td>{$stats['active_paid']}</td></tr>
<tr style='background:#fff3cd'><td>⚠ Pagos fallidos</td><td>{$stats['past_due']}</td></tr>
</table>
<p><a href='https://dimark.co/supercerebro/admin/growth.php'>Ver panel admin →</a></p>
";

$ch = curl_init('https://api.brevo.com/v3/smtp/email');
curl_setopt_array($ch, [
    CURLOPT_RETURNTRANSFER => true, CURLOPT_POST => true,
    CURLOPT_POSTFIELDS => json_encode([
        'sender'      => ['name' => 'SuperCerebro', 'email' => BREVO_SENDER_EMAIL ?: 'info@dimark.co'],
        'to'          => [['email' => DIMARK_EMAIL, 'name' => 'Equipo SuperCerebro']],
        'subject'     => "☀️ Digest $today — MRR \${$stats['mrr']} · {$stats['trials_active']} trials activos",
        'htmlContent' => $digest_body,
    ]),
    CURLOPT_HTTPHEADER => ['api-key: ' . (BREVO_API_KEY ?: ''), 'Content-Type: application/json'],
    CURLOPT_TIMEOUT => 10,
]);
curl_exec($ch); curl_close($ch);
$log[] = "Digest enviado MRR={$stats['mrr']}";

echo date('Y-m-d H:i:s') . " | billing-cron OK | " . implode(' | ', $log) . "\n";
