<?php
/**
 * Sends one reminder email per pending coin purchase older than 5 minutes (still pending).
 * Requires cron (e.g. every 5 minutes):
 *   php /path/to/members/cron/remind_pending_coin_purchases.php
 *
 * Enable template "coin_purchase_pending_reminder" + SMTP in admin → Email settings.
 */
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../includes/email_service.php';

emailEnsureDefaults($pdo);

$stmt = $pdo->query("SELECT setting_value FROM app_settings WHERE setting_key = 'app_name' LIMIT 1");
$appName = $stmt->fetchColumn();
$appName = $appName !== false && $appName !== null && $appName !== '' ? (string) $appName : 'MemberX';

$sql = "
    SELECT cp.id, cp.coins, cp.price, cp.currency, cp.created_at,
           u.email, u.full_name, u.username,
           pkg.name AS package_name
    FROM coin_purchases cp
    INNER JOIN users u ON u.id = cp.user_id
    INNER JOIN coin_packages pkg ON pkg.id = cp.package_id
    WHERE cp.status = 'pending'
      AND cp.created_at <= DATE_SUB(NOW(), INTERVAL 5 MINUTE)
      AND cp.pending_reminder_sent_at IS NULL
    ORDER BY cp.id ASC
    LIMIT 50
";

$stmt = $pdo->query($sql);
$rows = $stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : [];

$markStmt = $pdo->prepare('UPDATE coin_purchases SET pending_reminder_sent_at = NOW() WHERE id = ?');

$defaultOffer = 'Complete your purchase today and contact support from the app — we may add a small bonus to your balance for finishing this order.';

foreach ($rows as $row) {
    $purchaseId = (int) $row['id'];
    $email = isset($row['email']) ? trim((string) $row['email']) : '';

    if ($email === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
        $markStmt->execute([$purchaseId]);
        echo date('c') . " purchase #{$purchaseId}: skip (no valid email)\n";
        continue;
    }

    $vars = [
        'full_name' => $row['full_name'] ?? 'User',
        'username' => $row['username'] ?? '',
        'package_name' => $row['package_name'] ?? '',
        'coins' => (string) (int) ($row['coins'] ?? 0),
        'price' => number_format((float) ($row['price'] ?? 0), 2),
        'currency' => $row['currency'] ?? 'EUR',
        'purchase_id' => (string) $purchaseId,
        'app_name' => $appName,
        'offer_cta' => $defaultOffer,
    ];

    try {
        sendSystemEmail(
            $pdo,
            'coin_purchase_pending_reminder',
            $email,
            $vars,
            [
                'triggered_by' => 'cron_remind_pending_coin_purchases',
                'context' => ['purchase_id' => $purchaseId],
            ]
        );
    } catch (Throwable $e) {
        error_log('remind_pending_coin_purchases: ' . $e->getMessage());
    }

    $markStmt->execute([$purchaseId]);
    echo date('c') . " purchase #{$purchaseId}: reminder processed\n";
}

if ($rows === []) {
    echo date('c') . " no pending purchases need reminder\n";
}
