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

// Only allow this script to run in development/testing environment
// Remove this check in production if needed
if ($_SERVER['SERVER_NAME'] !== 'localhost' && $_SERVER['SERVER_NAME'] !== '127.0.0.1') {
    die('This script can only be run in development environment.');
}

echo "<h1>Clear All Job Applications</h1>";
echo "<div style='font-family: Arial, sans-serif; max-width: 800px; margin: 20px;'>";

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

// Show current application count before deletion
try {
    $stmt = $db->prepare("SELECT COUNT(*) as total FROM job_applications");
    $stmt->execute();
    $result = $stmt->fetch();
    $total_applications = $result['total'];
    
    echo "<div style='background: #e8f4fd; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
    echo "<h3>📊 Current Status</h3>";
    echo "<p><strong>Total Job Applications:</strong> $total_applications</p>";
    echo "</div>";
    
    if ($total_applications == 0) {
        echo "<div style='background: #d4edda; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
        echo "<h3>✅ No Applications to Clear</h3>";
        echo "<p>The job_applications table is already empty.</p>";
        echo "</div>";
    } else {
        // Show breakdown by status
        $stmt = $db->prepare("
            SELECT status, COUNT(*) as count 
            FROM job_applications 
            GROUP BY status 
            ORDER BY count DESC
        ");
        $stmt->execute();
        $status_breakdown = $stmt->fetchAll();
        
        echo "<div style='background: #fff3cd; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
        echo "<h3>📈 Applications by Status</h3>";
        echo "<table border='1' style='border-collapse: collapse; width: 100%;'>";
        echo "<tr style='background-color: #f2f2f2;'><th style='padding: 8px;'>Status</th><th style='padding: 8px;'>Count</th></tr>";
        
        foreach ($status_breakdown as $row) {
            echo "<tr>";
            echo "<td style='padding: 8px;'>" . ucfirst($row['status']) . "</td>";
            echo "<td style='padding: 8px; text-align: center;'>" . $row['count'] . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        echo "</div>";
        
        // Confirmation form
        if (!isset($_POST['confirm_clear'])) {
            echo "<div style='background: #f8d7da; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
            echo "<h3>⚠️ Warning: Destructive Action</h3>";
            echo "<p><strong>This action will permanently delete ALL job applications from the database.</strong></p>";
            echo "<p>This includes:</p>";
            echo "<ul>";
            echo "<li>All application records</li>";
            echo "<li>Application status history</li>";
            echo "<li>Survey responses (if any)</li>";
            echo "<li>Interview schedules related to these applications</li>";
            echo "</ul>";
            echo "<p><strong>This action cannot be undone!</strong></p>";
            echo "</div>";
            
            echo "<form method='POST' style='text-align: center;'>";
            echo "<div style='margin: 20px 0;'>";
            echo "<label style='display: flex; align-items: center; justify-content: center; margin-bottom: 15px;'>";
            echo "<input type='checkbox' name='confirm_understand' required style='margin-right: 10px;'>";
            echo "I understand that this action will permanently delete all job applications";
            echo "</label>";
            echo "</div>";
            echo "<button type='submit' name='confirm_clear' value='yes' ";
            echo "style='background: #dc3545; color: white; padding: 15px 30px; border: none; border-radius: 5px; font-size: 16px; cursor: pointer;'>";
            echo "🗑️ DELETE ALL APPLICATIONS";
            echo "</button>";
            echo "<br><br>";
            echo "<a href='index.php' style='color: #007bff; text-decoration: none; padding: 10px 20px; border: 1px solid #007bff; border-radius: 5px;'>";
            echo "Cancel and Return to Home";
            echo "</a>";
            echo "</form>";
            
        } else {
            // Perform the deletion
            try {
                $db->beginTransaction();
                
                // Delete related records first to maintain referential integrity
                
                // 1. Delete job application survey responses
                $stmt = $db->prepare("DELETE FROM job_application_survey_responses");
                $deleted_surveys = $stmt->execute();
                $survey_count = $stmt->rowCount();
                
                // 2. Delete interviews related to these applications
                $stmt = $db->prepare("DELETE FROM interviews WHERE application_id IS NOT NULL");
                $deleted_interviews = $stmt->execute();
                $interview_count = $stmt->rowCount();
                
                // 3. Delete the job applications themselves
                $stmt = $db->prepare("DELETE FROM job_applications");
                $deleted_applications = $stmt->execute();
                $application_count = $stmt->rowCount();
                
                $db->commit();
                
                echo "<div style='background: #d4edda; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
                echo "<h3>✅ Applications Cleared Successfully!</h3>";
                echo "<p><strong>Deletion Summary:</strong></p>";
                echo "<ul>";
                echo "<li>Job Applications Deleted: <strong>$application_count</strong></li>";
                echo "<li>Survey Responses Deleted: <strong>$survey_count</strong></li>";
                echo "<li>Interview Records Deleted: <strong>$interview_count</strong></li>";
                echo "</ul>";
                echo "<p>All job applications have been permanently removed from the system.</p>";
                echo "</div>";
                
                // Reset auto-increment counters
                try {
                    $db->exec("ALTER TABLE job_applications AUTO_INCREMENT = 1");
                    $db->exec("ALTER TABLE job_application_survey_responses AUTO_INCREMENT = 1");
                    $db->exec("ALTER TABLE interviews AUTO_INCREMENT = 1");
                    echo "<p style='color: green;'>✅ Database auto-increment counters reset.</p>";
                } catch (Exception $e) {
                    echo "<p style='color: orange;'>⚠️ Note: Could not reset auto-increment counters: " . $e->getMessage() . "</p>";
                }
                
                echo "<div style='background: #e8f4fd; padding: 15px; border-radius: 5px; margin-top: 20px;'>";
                echo "<h3>🔄 What's Next?</h3>";
                echo "<ul>";
                echo "<li>Job postings remain active and can receive new applications</li>";
                echo "<li>Candidate profiles are preserved</li>";
                echo "<li>Company data is unchanged</li>";
                echo "<li>You can start fresh with new applications</li>";
                echo "</ul>";
                echo "</div>";
                
            } catch (Exception $e) {
                $db->rollBack();
                echo "<div style='background: #f8d7da; padding: 15px; border-radius: 5px; margin-bottom: 20px;'>";
                echo "<h3>❌ Error Clearing Applications</h3>";
                echo "<p><strong>Error:</strong> " . htmlspecialchars($e->getMessage()) . "</p>";
                echo "<p>The database has been rolled back to its previous state.</p>";
                echo "</div>";
            }
            
            echo "<div style='text-align: center; margin-top: 30px;'>";
            echo "<a href='index.php' style='background: #007bff; color: white; padding: 15px 30px; text-decoration: none; border-radius: 5px; font-size: 16px;'>";
            echo "🏠 Return to Home";
            echo "</a>";
            echo "</div>";
        }
    }
    
} catch (Exception $e) {
    echo "<div style='background: #f8d7da; padding: 15px; border-radius: 5px;'>";
    echo "<h3>❌ Database Error</h3>";
    echo "<p>Could not connect to database or retrieve application count.</p>";
    echo "<p><strong>Error:</strong> " . htmlspecialchars($e->getMessage()) . "</p>";
    echo "</div>";
}

echo "<hr>";
echo "<div style='background: #f8f9fa; padding: 15px; border-radius: 5px; margin-top: 20px;'>";
echo "<h3>🛡️ Safety Notes</h3>";
echo "<ul>";
echo "<li>This script only deletes job applications and related data</li>";
echo "<li>Jobs, candidates, and companies remain unchanged</li>";
echo "<li>This action is logged for audit purposes</li>";
echo "<li>Make sure you have a database backup before running this</li>";
echo "</ul>";
echo "</div>";

echo "</div>";
?>

<style>
body { 
    font-family: Arial, sans-serif; 
    margin: 20px; 
    line-height: 1.6;
    background-color: #f8f9fa;
}
table { 
    border-collapse: collapse; 
    margin: 10px 0; 
    font-size: 14px;
}
th, td { 
    padding: 8px; 
    text-align: left; 
    border: 1px solid #ddd;
}
th { 
    background-color: #f2f2f2; 
    font-weight: bold;
}
h1 { color: #dc3545; }
h3 { margin-top: 0; }
button:hover, a:hover { opacity: 0.9; }
</style>