<?php
// ── cleanup_expired.php — Remove expired vouchers after grace period ──────────
//
// HOW TO SET UP THE CRON JOB on your hosting:
// --------------------------------------------
// 1. Log into cPanel → Cron Jobs
// 2. Set schedule: Daily at 2:00 AM
//    Minute: 0  Hour: 2  Day: *  Month: *  Weekday: *
// 3. Command (replace the path with your actual server path):
//    php /home/yourusername/public_html/voucher-system/cron/cleanup_expired.php >> /home/yourusername/logs/voucher_cleanup.log 2>&1
//
// OR if your host provides SSH access, add to crontab manually:
//    crontab -e
//    0 2 * * * php /home/yourusername/public_html/voucher-system/cron/cleanup_expired.php >> /home/yourusername/logs/voucher_cleanup.log 2>&1
//
// Grace period: 7 days after expiry (matches the countdown shown in the UI)

define('GRACE_DAYS', 7);

// Locked to CLI only — cannot be triggered via browser URL
if (php_sapi_name() !== 'cli') {
    http_response_code(403);
    exit("Access denied. This script must be run from the command line only.\n");
}

require_once __DIR__ . '/../config/db.php';

$cutoff = date('Y-m-d', strtotime('-' . GRACE_DAYS . ' days'));

echo "[" . date('Y-m-d H:i:s') . "] Running expired voucher cleanup (cutoff: $cutoff)\n";

// Find expired voucher templates whose own claims and generated user copies have
// no pending claims. parent_voucher_id is used by the voucher v4 fix.
$result = $conn->query(
    "SELECT v.id, v.code, v.event_name, v.end_date,
            COUNT(vc.id) AS total_claims,
            SUM(vc.status = 'claimed') AS pending_claims
     FROM vouchers v
     LEFT JOIN vouchers cv ON (cv.id = v.id OR cv.parent_voucher_id = v.id)
     LEFT JOIN voucher_claims vc ON vc.voucher_id = cv.id
     WHERE v.parent_voucher_id IS NULL
       AND v.end_date != '0000-00-00'
       AND v.end_date < '$cutoff'
     GROUP BY v.id
     HAVING pending_claims = 0 OR pending_claims IS NULL"
);

if (!$result) {
    echo "Query error: " . $conn->error . "\n";
    exit(1);
}

$rows = $result->fetch_all(MYSQLI_ASSOC);
$count = count($rows);

if ($count === 0) {
    echo "No vouchers to clean up.\n";
    exit(0);
}

echo "Found $count voucher(s) to remove:\n";

$conn->begin_transaction();
try {
    foreach ($rows as $v) {
        echo "  - [{$v['id']}] {$v['code']} | {$v['event_name']} | expired {$v['end_date']}\n";
    }

    $ids = array_column($rows, 'id');
    $placeholders = implode(',', array_fill(0, count($ids), '?'));
    $types = str_repeat('i', count($ids));

    // Delete generated user-specific copies first. Their claims are removed by FK.
    $s1 = $conn->prepare("DELETE FROM vouchers WHERE parent_voucher_id IN ($placeholders)");
    $s1->bind_param($types, ...$ids);
    $s1->execute();
    echo "  Deleted {$s1->affected_rows} generated voucher copy/copies.\n";

    // Delete templates. Direct legacy claims are removed by FK.
    $s2 = $conn->prepare("DELETE FROM vouchers WHERE id IN ($placeholders)");
    $s2->bind_param($types, ...$ids);
    $s2->execute();
    echo "  Deleted {$s2->affected_rows} voucher(s).\n";

    $conn->commit();
    echo "[" . date('Y-m-d H:i:s') . "] Cleanup complete.\n";

} catch (Exception $e) {
    $conn->rollback();
    echo "ERROR: " . $e->getMessage() . "\n";
    exit(1);
}
