<?php
// scripts/payment_reminder.php
// This script is intended for daily execution via a secure cron job (CLI).
// It checks for students whose course access is about to expire or has already expired, 
// and sends a notification and an email if one hasn't been sent today.

// =========================================================================
// Configuration & Setup
// =========================================================================
if (php_sapi_name() !== 'cli' && !isset($_SERVER['REMINDER_KEY'])) {
    die("Access denied. This script is intended for command-line execution (cron job).");
}

require_once dirname(__DIR__) . '/config/database.php'; 
require_once dirname(__DIR__) . '/vendor/autoload.php'; // Path to PHPMailer autoload file

use PHPMailer\PHPMailer\PHPMailer;
use PHPMailer\PHPMailer\SMTP;
use PHPMailer\PHPMailer\Exception;

date_default_timezone_set('Asia/Colombo'); 

$MAX_REMINDER_DAYS = 7;
$current_date = date('Y-m-d');
$reminder_cutoff_date = date('Y-m-d', strtotime("+$MAX_REMINDER_DAYS days"));

$log = [];

try {
    $log[] = "--- Persistent Payment Reminder Script Started: " . date('Y-m-d H:i:s') . " ---";
    $log[] = "Checking for access expiring between {$current_date} and {$reminder_cutoff_date} (or already expired).";

    $sql = "
        SELECT
            la.enrollment_id,
            la.access_end_date,
            ce.student_id,
            u.email AS student_email,      /* Corrected to fetch from the 'users' table */
            u.full_name AS student_name,   /* Corrected to fetch from the 'users' table */
            b.batch_name
        FROM lms_access la
        JOIN course_enrollments ce ON la.enrollment_id = ce.id
        JOIN users u ON ce.student_id = u.id /* Join with the users table */
        JOIN batches b ON ce.batch_id = b.id
        WHERE 
            la.lifetime_access = 0 AND 
            ce.status = 'enrolled' AND 
            la.access_end_date IS NOT NULL AND
            (
                la.access_end_date BETWEEN ? AND ?
                OR la.access_end_date < ?
            )
        ORDER BY la.access_end_date ASC
    ";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([$current_date, $reminder_cutoff_date, $current_date]);
    $students_to_notify = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $log[] = "Found " . count($students_to_notify) . " students with payments due soon or past due.";

    $notification_count = 0;
    $email_count = 0;

    if (!empty($students_to_notify)) {
        $pdo->beginTransaction();

        // Prepare the notification insertion query
        $sql_notify = "
            INSERT INTO notifications (user_id, type, title, message, related_id)
            VALUES (?, 'payment_reminder', ?, ?, ?)
        ";
        $stmt_notify = $pdo->prepare($sql_notify);

        // Prepare the notification check query
        $stmt_check = $pdo->prepare("
            SELECT COUNT(id) FROM notifications 
            WHERE user_id = ? AND type = 'payment_reminder' 
            AND related_id = ? AND DATE(created_at) = CURDATE()
        ");

        // PHPMailer setup
        $mail = new PHPMailer(true);
        $mail->isSMTP();
        $mail->Host       = 'smtp.gmail.com';
        $mail->SMTPAuth   = true;
        $mail->Username   = 'bazithali95@gmail.com';
        $mail->Password   = 'sqkt opow cvao fiac';
        $mail->SMTPSecure = PHPMailer::ENCRYPTION_STARTTLS;
        $mail->Port       = 587;
        $mail->SMTPOptions = [
            'ssl' => ['verify_peer' => false, 'verify_peer_name' => false, 'allow_self_signed' => true]
        ];
        $mail->setFrom('bazithali95@gmail.com', 'Orinsto LMS Enrollment');

        foreach ($students_to_notify as $student) {
            $expiry_date = $student['access_end_date'];
            $days_remaining = floor((strtotime($expiry_date) - strtotime($current_date)) / (60 * 60 * 24));
            $student_id = $student['student_id'];
            $enrollment_id = $student['enrollment_id'];

            // Check if a reminder has already been sent TODAY for this specific enrollment.
            $stmt_check->execute([$student_id, $enrollment_id]);
            $already_notified_today = $stmt_check->fetchColumn();

            if ($already_notified_today > 0) {
                $log[] = "Skipping Student ID {$student_id}: Already notified today for Enrollment ID {$enrollment_id}.";
                continue;
            }

            // Define Notification & Email Content
            $formatted_date = date('F d, Y', strtotime($expiry_date));
            $batch_name = htmlspecialchars($student['batch_name']);

            if ($days_remaining > 0) {
                // Payment is DUE IN X DAYS
                $notif_title = "🔔 Payment Due Soon!";
                $notif_message = "Your course access for batch **{$batch_name}** expires in **{$days_remaining} days** on {$formatted_date}. Please make your monthly payment.";
                $email_subject = "Payment Reminder: Access to {$batch_name} Expires Soon!";
                $email_body = "Hello {$student['student_name']},<br><br>Your course access for the batch **{$batch_name}** is set to expire in **{$days_remaining} days** on {$formatted_date}.<br><br>To continue your learning without interruption, please make your monthly payment at your earliest convenience.<br><br>Thank you,<br>Orinsto LMS";
            } else {
                // Payment is TODAY or PAST DUE (days_remaining <= 0)
                $notif_title = "⚠️ ACCESS EXPIRED / URGENT ACTION NEEDED!";
                $notif_message = "Your access for batch **{$batch_name}** expired on {$formatted_date}. Access may be revoked soon. **Submit your payment slip NOW.**";
                $email_subject = "URGENT: Your Access to {$batch_name} Has Expired!";
                $email_body = "Hello {$student['student_name']},<br><br>This is an urgent notification regarding your access to the batch **{$batch_name}**.<br><br>Your access expired on {$formatted_date}. To prevent your account from being fully revoked, please submit your payment slip **immediately**.<br><br>Thank you,<br>Orinsto LMS";
            }

            // Insert in-app notification
            if ($stmt_notify->execute([
                $student_id, 
                $notif_title, 
                $notif_message, 
                $enrollment_id
            ])) {
                $notification_count++;
            }

            // Send email
            try {
                $mail->clearAllRecipients();
                $mail->addAddress($student['student_email'], $student['student_name']);
                $mail->isHTML(true);
                $mail->Subject = $email_subject;
                $mail->Body    = $email_body;
                $mail->send();
                $email_count++;
            } catch (Exception $e) {
                // Log email errors but don't stop the script
                $log[] = "Email to {$student['student_email']} failed: {$mail->ErrorInfo}";
            }
        }
        
        $pdo->commit();
        $log[] = "Successfully inserted {$notification_count} payment reminder notifications and sent {$email_count} emails.";

    } else {
        $log[] = "No matching students found for notification criteria.";
    }

} catch (Exception $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    $log[] = "FATAL ERROR: " . $e->getMessage();
    error_log("Payment Reminder Cron Error: " . $e->getMessage()); 
}

$log[] = "--- Script Finished ---";

foreach ($log as $line) {
    echo $line . "\n";
}

?>