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

$database = new Database();
$db = $database->getConnection();

echo "<h2>🔍 Check Job Closing Date System</h2>\n";

// 1. Check if jobs table has closing date fields
echo "<h3>1. Database Structure Check</h3>\n";
try {
    $stmt = $db->query("DESCRIBE jobs");
    $columns = $stmt->fetchAll();
    $column_names = array_column($columns, 'Field');
    
    $date_related_columns = [];
    foreach ($columns as $col) {
        if (stripos($col['Field'], 'date') !== false || stripos($col['Field'], 'close') !== false || stripos($col['Field'], 'deadline') !== false) {
            $date_related_columns[] = $col;
        }
    }
    
    echo "<p><strong>Date/Closing related columns found:</strong></p>\n";
    if ($date_related_columns) {
        echo "<table border='1' cellpadding='5'>\n";
        echo "<tr><th>Column</th><th>Type</th><th>Null</th><th>Default</th></tr>\n";
        foreach ($date_related_columns as $col) {
            echo "<tr>";
            echo "<td>{$col['Field']}</td>";
            echo "<td>{$col['Type']}</td>";
            echo "<td>{$col['Null']}</td>";
            echo "<td>{$col['Default']}</td>";
            echo "</tr>\n";
        }
        echo "</table>\n";
    } else {
        echo "<p>❌ No closing date or deadline columns found!</p>\n";
        echo "<p>Available columns: " . implode(', ', $column_names) . "</p>\n";
    }
} catch (Exception $e) {
    echo "<p>❌ Error checking table structure: " . $e->getMessage() . "</p>\n";
}

// 2. Check current job data for any date patterns
echo "<h3>2. Sample Job Data</h3>\n";
try {
    $stmt = $db->query("SELECT id, title, status, created_at, updated_at FROM jobs ORDER BY created_at DESC LIMIT 5");
    $jobs = $stmt->fetchAll();
    
    if ($jobs) {
        echo "<table border='1' cellpadding='5'>\n";
        echo "<tr><th>ID</th><th>Title</th><th>Status</th><th>Created</th><th>Updated</th></tr>\n";
        foreach ($jobs as $job) {
            echo "<tr>";
            echo "<td>{$job['id']}</td>";
            echo "<td>" . substr($job['title'], 0, 40) . "...</td>";
            echo "<td>{$job['status']}</td>";
            echo "<td>{$job['created_at']}</td>";
            echo "<td>{$job['updated_at']}</td>";
            echo "</tr>\n";
        }
        echo "</table>\n";
    }
} catch (Exception $e) {
    echo "<p>❌ Error getting job data: " . $e->getMessage() . "</p>\n";
}

// 3. Check current job application logic
echo "<h3>3. Current Application Logic Check</h3>\n";

// Look for job application files
$application_files = [
    'job-details.php',
    'candidate/apply.php',
    'apply.php'
];

foreach ($application_files as $file) {
    if (file_exists($file)) {
        echo "<p>✅ Found application file: $file</p>\n";
    } else {
        echo "<p>❌ File not found: $file</p>\n";
    }
}

// 4. Analyze what we need to implement
echo "<h3>4. Implementation Requirements</h3>\n";
echo "<div style='background: #fff3cd; color: #856404; padding: 15px; border-radius: 5px; margin: 10px 0;'>\n";
echo "<h4>📋 What Needs To Be Done:</h4>\n";
echo "<ol>\n";
echo "<li><strong>Add closing_date column</strong> to jobs table (if not exists)</li>\n";
echo "<li><strong>Update job listings</strong> to show closing dates</li>\n";
echo "<li><strong>Filter expired jobs</strong> from job listings</li>\n";
echo "<li><strong>Block applications</strong> to expired jobs</li>\n";
echo "<li><strong>Show clear messaging</strong> for expired jobs</li>\n";
echo "</ol>\n";
echo "</div>\n";

// 5. Check current job statuses
echo "<h3>5. Current Job Status Distribution</h3>\n";
try {
    $stmt = $db->query("SELECT status, COUNT(*) as count FROM jobs GROUP BY status");
    $status_stats = $stmt->fetchAll();
    
    echo "<table border='1' cellpadding='5'>\n";
    echo "<tr><th>Status</th><th>Count</th></tr>\n";
    foreach ($status_stats as $stat) {
        echo "<tr><td>{$stat['status']}</td><td>{$stat['count']}</td></tr>\n";
    }
    echo "</table>\n";
} catch (Exception $e) {
    echo "<p>❌ Error getting status stats: " . $e->getMessage() . "</p>\n";
}

// 6. Propose the solution
echo "<h3>6. Proposed Solution</h3>\n";
echo "<div style='background: #e8f5e8; color: #2e7d32; padding: 15px; border-radius: 5px; margin: 10px 0;'>\n";
echo "<h4>🎯 Implementation Plan:</h4>\n";
echo "<p><strong>Database Changes:</strong></p>\n";
echo "<ul>\n";
echo "<li>Add <code>closing_date</code> column to jobs table</li>\n";
echo "<li>Add <code>application_deadline</code> column (optional - for different deadline than closing)</li>\n";
echo "</ul>\n";
echo "<p><strong>Job Listing Changes:</strong></p>\n";
echo "<ul>\n";
echo "<li>Filter out jobs where <code>closing_date < NOW()</code></li>\n";
echo "<li>Show closing date in job listings</li>\n";
echo "<li>Add visual indicators for jobs closing soon</li>\n";
echo "</ul>\n";
echo "<p><strong>Application Process Changes:</strong></p>\n";
echo "<ul>\n";
echo "<li>Check closing date before showing apply button</li>\n";
echo "<li>Validate closing date in application submission</li>\n";
echo "<li>Show appropriate error messages for expired jobs</li>\n";
echo "</ul>\n";
echo "</div>\n";

?>

<style>
body { font-family: Arial, sans-serif; margin: 20px; line-height: 1.6; }
table { width: 100%; margin: 20px 0; border-collapse: collapse; }
th, td { padding: 8px; text-align: left; border: 1px solid #ddd; }
th { background-color: #f2f2f2; }
h1, h2, h3, h4 { color: #333; }
</style>