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

echo "\n=== CLEAR ALL JOB APPLICATIONS ===\n\n";

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

try {
    // Show current application count
    $stmt = $db->prepare("SELECT COUNT(*) as total FROM job_applications");
    $stmt->execute();
    $result = $stmt->fetch();
    $total_applications = $result['total'];
    
    echo "Current Status:\n";
    echo "- Total Job Applications: $total_applications\n\n";
    
    if ($total_applications == 0) {
        echo "✅ No applications to clear. The job_applications table is already empty.\n";
        exit(0);
    }
    
    // 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 "Applications by Status:\n";
    foreach ($status_breakdown as $row) {
        echo "- " . ucfirst($row['status']) . ": " . $row['count'] . "\n";
    }
    echo "\n";
    
    // Show what will be deleted
    echo "⚠️  WARNING: This will permanently delete:\n";
    echo "- All job applications ($total_applications records)\n";
    echo "- Related survey responses\n";
    echo "- Related interview schedules\n";
    echo "- This action CANNOT be undone!\n\n";
    
    // Confirmation prompt
    echo "Are you sure you want to delete ALL applications? (type 'YES' to confirm): ";
    $confirmation = trim(fgets(STDIN));
    
    if ($confirmation !== 'YES') {
        echo "\n❌ Operation cancelled. No data was deleted.\n";
        exit(0);
    }
    
    echo "\n🗑️  Deleting applications...\n";
    
    // Perform the deletion with transaction
    $db->beginTransaction();
    
    try {
        // Delete related records first
        
        // 1. Delete survey responses
        $stmt = $db->prepare("DELETE FROM job_application_survey_responses");
        $stmt->execute();
        $survey_count = $stmt->rowCount();
        echo "- Deleted $survey_count survey responses\n";
        
        // 2. Delete interviews
        $stmt = $db->prepare("DELETE FROM interviews WHERE application_id IS NOT NULL");
        $stmt->execute();
        $interview_count = $stmt->rowCount();
        echo "- Deleted $interview_count interview records\n";
        
        // 3. Delete applications
        $stmt = $db->prepare("DELETE FROM job_applications");
        $stmt->execute();
        $application_count = $stmt->rowCount();
        echo "- Deleted $application_count job applications\n";
        
        $db->commit();
        
        // 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 "- Reset database auto-increment counters\n";
        } catch (Exception $e) {
            echo "- Warning: Could not reset auto-increment counters\n";
        }
        
        echo "\n✅ SUCCESS: All applications have been cleared!\n";
        echo "\nSummary:\n";
        echo "- Job Applications: $application_count deleted\n";
        echo "- Survey Responses: $survey_count deleted\n";
        echo "- Interview Records: $interview_count deleted\n";
        echo "\n📝 Notes:\n";
        echo "- Job postings remain active\n";
        echo "- Candidate profiles are preserved\n";
        echo "- Company data is unchanged\n";
        echo "- System is ready for new applications\n\n";
        
    } catch (Exception $e) {
        $db->rollBack();
        echo "\n❌ ERROR: Failed to delete applications\n";
        echo "Error: " . $e->getMessage() . "\n";
        echo "Database has been rolled back to previous state.\n\n";
        exit(1);
    }
    
} catch (Exception $e) {
    echo "\n❌ DATABASE ERROR\n";
    echo "Could not connect to database or retrieve data.\n";
    echo "Error: " . $e->getMessage() . "\n\n";
    exit(1);
}

echo "=== OPERATION COMPLETE ===\n\n";
?>