<?php
require_once 'config/config.php';

// Test script for new job notifications
$database = new Database();
$db = $database->getConnection();

echo "<h2>Testing New Job Notification System</h2>\n";

try {
    // First, let's check if we have the notification tables
    echo "<h3>1. Checking Database Tables</h3>\n";
    
    $stmt = $db->prepare("SHOW TABLES LIKE 'notifications'");
    $stmt->execute();
    if ($stmt->rowCount() > 0) {
        echo "✅ Notifications table exists<br>\n";
    } else {
        echo "❌ Notifications table missing<br>\n";
        // Create the table
        $db->exec("
        CREATE TABLE IF NOT EXISTS notifications (
            id INT PRIMARY KEY AUTO_INCREMENT,
            user_type ENUM('candidate', 'company', 'admin') NOT NULL,
            user_id INT NOT NULL,
            title VARCHAR(255) NOT NULL,
            message TEXT NOT NULL,
            type ENUM('info', 'success', 'warning', 'error') DEFAULT 'info',
            related_type ENUM('application', 'job', 'interview', 'job_opportunity', 'general') DEFAULT 'general',
            related_id INT NULL,
            is_read BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_user_notifications (user_type, user_id, is_read),
            INDEX idx_created_at (created_at)
        )");
        echo "✅ Notifications table created<br>\n";
    }
    
    $stmt = $db->prepare("SHOW TABLES LIKE 'email_notifications'");
    $stmt->execute();
    if ($stmt->rowCount() > 0) {
        echo "✅ Email notifications table exists<br>\n";
    } else {
        echo "❌ Email notifications table missing<br>\n";
        // Create the table
        $db->exec("
        CREATE TABLE IF NOT EXISTS email_notifications (
            id INT PRIMARY KEY AUTO_INCREMENT,
            to_email VARCHAR(255) NOT NULL,
            subject VARCHAR(255) NOT NULL,
            message TEXT NOT NULL,
            status ENUM('pending', 'sent', 'failed') DEFAULT 'pending',
            sent_at TIMESTAMP NULL,
            error_message TEXT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_status (status),
            INDEX idx_created_at (created_at)
        )");
        echo "✅ Email notifications table created<br>\n";
    }
    
    // Check for candidates who have applied to jobs
    echo "<h3>2. Checking Test Data</h3>\n";
    
    $stmt = $db->prepare("
        SELECT COUNT(DISTINCT c.id) as candidate_count, COUNT(DISTINCT ja.job_id) as job_count
        FROM candidates c
        INNER JOIN job_applications ja ON c.id = ja.candidate_id
        INNER JOIN jobs j ON ja.job_id = j.id
        WHERE j.category_id IS NOT NULL
    ");
    $stmt->execute();
    $test_data = $stmt->fetch();
    
    echo "📊 Found {$test_data['candidate_count']} candidates with applications across {$test_data['job_count']} jobs<br>\n";
    
    if ($test_data['candidate_count'] == 0) {
        echo "⚠️ No test data available. You need candidates with job applications to test notifications.<br>\n";
        return;
    }
    
    // Find a recent job to use for testing
    echo "<h3>3. Finding Test Job</h3>\n";
    
    $stmt = $db->prepare("
        SELECT j.id, j.title, j.category_id, cat.name as category_name, c.name as company_name
        FROM jobs j
        LEFT JOIN job_categories cat ON j.category_id = cat.id
        LEFT JOIN companies c ON j.company_id = c.id
        WHERE j.category_id IS NOT NULL 
        AND j.status = 'active'
        ORDER BY j.created_at DESC 
        LIMIT 1
    ");
    $stmt->execute();
    $test_job = $stmt->fetch();
    
    if (!$test_job) {
        echo "❌ No active jobs with categories found for testing<br>\n";
        return;
    }
    
    echo "🎯 Using test job: {$test_job['title']} (ID: {$test_job['id']}) in {$test_job['category_name']} category<br>\n";
    
    // Test the notification system
    echo "<h3>4. Testing Notification System</h3>\n";
    
    require_once 'includes/notification_system.php';
    $notification_system = new NotificationSystem($db);
    
    // Find candidates who would be notified
    $stmt = $db->prepare("
        SELECT DISTINCT c.id, c.email, c.first_name, c.last_name
        FROM candidates c
        INNER JOIN job_applications ja ON c.id = ja.candidate_id
        INNER JOIN jobs prev_jobs ON ja.job_id = prev_jobs.id
        WHERE prev_jobs.category_id = ?
        AND c.id NOT IN (
            SELECT candidate_id 
            FROM job_applications 
            WHERE job_id = ?
        )
        AND c.email IS NOT NULL
        AND c.email != ''
        LIMIT 3
    ");
    $stmt->execute([$test_job['category_id'], $test_job['id']]);
    $eligible_candidates = $stmt->fetchAll();
    
    echo "📧 Found " . count($eligible_candidates) . " eligible candidates to notify<br>\n";
    
    if (empty($eligible_candidates)) {
        echo "⚠️ No eligible candidates found for this job category.<br>\n";
        
        // Show what candidates and categories we do have
        $stmt = $db->prepare("
            SELECT DISTINCT cat.name as category_name, COUNT(DISTINCT c.id) as candidate_count
            FROM candidates c
            INNER JOIN job_applications ja ON c.id = ja.candidate_id
            INNER JOIN jobs j ON ja.job_id = j.id
            INNER JOIN job_categories cat ON j.category_id = cat.id
            GROUP BY cat.id, cat.name
        ");
        $stmt->execute();
        $categories = $stmt->fetchAll();
        
        echo "<strong>Available categories with candidates:</strong><br>\n";
        foreach ($categories as $category) {
            echo "- {$category['category_name']}: {$category['candidate_count']} candidates<br>\n";
        }
        return;
    }
    
    foreach ($eligible_candidates as $candidate) {
        echo "- {$candidate['first_name']} {$candidate['last_name']} ({$candidate['email']})<br>\n";
    }
    
    // Test the notification function
    echo "<h3>5. Sending Test Notifications</h3>\n";
    
    $result = $notification_system->notifyPreviousApplicants($test_job['id']);
    
    if ($result) {
        echo "✅ Notification process completed successfully!<br>\n";
        
        // Check what notifications were created
        $stmt = $db->prepare("
            SELECT COUNT(*) as notification_count 
            FROM notifications 
            WHERE related_type = 'job_opportunity' 
            AND related_id = ?
        ");
        $stmt->execute([$test_job['id']]);
        $notification_count = $stmt->fetch()['notification_count'];
        
        echo "📨 Created {$notification_count} in-app notifications<br>\n";
        
        // Check email log
        $stmt = $db->prepare("
            SELECT COUNT(*) as email_count 
            FROM email_notifications 
            WHERE subject LIKE ? 
            AND created_at > DATE_SUB(NOW(), INTERVAL 1 MINUTE)
        ");
        $stmt->execute(["%{$test_job['title']}%"]);
        $email_count = $stmt->fetch()['email_count'];
        
        echo "📧 Attempted to send {$email_count} emails<br>\n";
        
    } else {
        echo "❌ Notification process failed<br>\n";
    }
    
    echo "<h3>6. Recent Notifications</h3>\n";
    $stmt = $db->prepare("
        SELECT n.title, n.message, n.type, c.first_name, c.last_name, n.created_at
        FROM notifications n
        LEFT JOIN candidates c ON n.user_id = c.id AND n.user_type = 'candidate'
        WHERE n.related_type = 'job_opportunity'
        ORDER BY n.created_at DESC
        LIMIT 5
    ");
    $stmt->execute();
    $recent_notifications = $stmt->fetchAll();
    
    if (empty($recent_notifications)) {
        echo "No recent job opportunity notifications found.<br>\n";
    } else {
        echo "<ul>\n";
        foreach ($recent_notifications as $notification) {
            echo "<li><strong>{$notification['title']}</strong> - {$notification['first_name']} {$notification['last_name']} ({$notification['created_at']})</li>\n";
        }
        echo "</ul>\n";
    }
    
    echo "<h3>✅ Test Complete</h3>\n";
    echo "<p><strong>Summary:</strong> The new job notification system is now integrated and ready to automatically notify candidates when jobs are posted in their areas of interest!</p>\n";
    
} catch (Exception $e) {
    echo "❌ Error during testing: " . $e->getMessage() . "<br>\n";
    echo "Stack trace: " . $e->getTraceAsString() . "<br>\n";
}
?>