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

echo "<h1>📊 Survey Questions Scoring System Migration</h1>\n";
echo "<p>This script will add weighted scoring capabilities to survey questions.</p>\n";

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>✓ Database connection successful</p>\n";
    echo "<hr>\n";
    
    // Step 1: Add weight column to job_survey_questions table
    echo "<h3>Step 1: Adding weight field to survey questions</h3>\n";
    
    $stmt = $db->query("SHOW COLUMNS FROM job_survey_questions LIKE 'weight'");
    if (!$stmt->fetch()) {
        echo "<p>Adding weight column to job_survey_questions table...</p>\n";
        $db->exec("ALTER TABLE job_survey_questions ADD COLUMN weight INT DEFAULT 100 AFTER is_required");
        echo "<div style='color: green;'>✓ Added weight column</div>\n";
    } else {
        echo "<div style='color: orange;'>⚠ weight column already exists</div>\n";
    }
    
    // Step 2: Add score_mapping column for response scoring
    echo "<h3>Step 2: Adding score mapping field</h3>\n";
    
    $stmt = $db->query("SHOW COLUMNS FROM job_survey_questions LIKE 'score_mapping'");
    if (!$stmt->fetch()) {
        echo "<p>Adding score_mapping column to job_survey_questions table...</p>\n";
        $db->exec("ALTER TABLE job_survey_questions ADD COLUMN score_mapping TEXT AFTER weight");
        echo "<div style='color: green;'>✓ Added score_mapping column</div>\n";
    } else {
        echo "<div style='color: orange;'>⚠ score_mapping column already exists</div>\n";
    }
    
    // Step 3: Create application_scores table for calculated scores
    echo "<h3>Step 3: Creating application scores table</h3>\n";
    
    $stmt = $db->query("SHOW TABLES LIKE 'application_scores'");
    if (!$stmt->fetch()) {
        echo "<p>Creating application_scores table...</p>\n";
        $sql = "
            CREATE TABLE application_scores (
                id INT AUTO_INCREMENT PRIMARY KEY,
                application_id INT NOT NULL,
                question_id INT NOT NULL,
                response_text TEXT,
                score_earned INT DEFAULT 0,
                weight_applied INT DEFAULT 100,
                weighted_score DECIMAL(5,2) DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (application_id) REFERENCES job_applications(id) ON DELETE CASCADE,
                FOREIGN KEY (question_id) REFERENCES job_survey_questions(id) ON DELETE CASCADE,
                INDEX idx_application_scores (application_id),
                INDEX idx_question_scores (question_id)
            )
        ";
        $db->exec($sql);
        echo "<div style='color: green;'>✓ Created application_scores table</div>\n";
    } else {
        echo "<div style='color: orange;'>⚠ application_scores table already exists</div>\n";
    }
    
    // Step 4: Add total_score column to job_applications
    echo "<h3>Step 4: Adding total score to applications</h3>\n";
    
    $stmt = $db->query("SHOW COLUMNS FROM job_applications LIKE 'total_score'");
    if (!$stmt->fetch()) {
        echo "<p>Adding total_score column to job_applications table...</p>\n";
        $db->exec("ALTER TABLE job_applications ADD COLUMN total_score DECIMAL(5,2) DEFAULT 0 AFTER status");
        echo "<div style='color: green;'>✓ Added total_score column</div>\n";
    } else {
        echo "<div style='color: orange;'>⚠ total_score column already exists</div>\n";
    }
    
    // Step 5: Add max_possible_score column to jobs table
    echo "<h3>Step 5: Adding max possible score to jobs</h3>\n";
    
    $stmt = $db->query("SHOW COLUMNS FROM jobs LIKE 'max_possible_score'");
    if (!$stmt->fetch()) {
        echo "<p>Adding max_possible_score column to jobs table...</p>\n";
        $db->exec("ALTER TABLE jobs ADD COLUMN max_possible_score INT DEFAULT 0 AFTER job_visibility");
        echo "<div style='color: green;'>✓ Added max_possible_score column</div>\n";
    } else {
        echo "<div style='color: orange;'>⚠ max_possible_score column already exists</div>\n";
    }
    
    // Step 6: Add indexes for performance
    echo "<h3>Step 6: Adding performance indexes</h3>\n";
    
    $indexes = [
        'idx_application_total_score' => 'ALTER TABLE job_applications ADD INDEX idx_application_total_score (total_score)',
        'idx_job_max_score' => 'ALTER TABLE jobs ADD INDEX idx_job_max_score (max_possible_score)',
        'idx_survey_weight' => 'ALTER TABLE job_survey_questions ADD INDEX idx_survey_weight (weight)'
    ];
    
    foreach ($indexes as $index_name => $sql) {
        try {
            $db->exec($sql);
            echo "<div style='color: green;'>✓ Added {$index_name} index</div>\n";
        } catch (PDOException $e) {
            echo "<div style='color: orange;'>⚠ {$index_name} index might already exist</div>\n";
        }
    }
    
    // Step 7: Create scoring helper functions
    echo "<h3>Step 7: Creating scoring helper functions</h3>\n";
    
    $scoring_functions_file = 'includes/scoring_functions.php';
    if (!file_exists($scoring_functions_file)) {
        echo "<p>Creating scoring helper functions...</p>\n";
        
        $scoring_functions = '<?php
// Scoring system helper functions

// Get predefined response scores based on keywords
function getResponseScore($response, $question_type = "text") {
    $response = strtolower(trim($response));
    
    // Enhanced keyword recognition system
    $scoring_map = [
        // 100% Points (Full Weight)
        "excellent" => 100, "yes" => 100, "expert" => 100, "advanced" => 100,
        "very good" => 100, "6+" => 100, "9+" => 100, "10+" => 100,
        "10+ years" => 100, "more than 10" => 100, "over 10" => 100,
        
        // 70% Points
        "good" => 70, "satisfactory" => 70, "intermediate" => 70,
        "3-5" => 70, "4-6" => 70, "3-5 years" => 70, "4-6 years" => 70,
        
        // 50% Points
        "fair" => 50, "average" => 50, "basic" => 50, "basic knowledge" => 50,
        "1-3" => 50, "2-4" => 50, "1-3 years" => 50, "2-4 years" => 50,
        
        // 0% Points
        "no" => 0, "none" => 0, "never" => 0, "not applicable" => 0,
        "no experience" => 0, "0" => 0, "0 years" => 0
    ];
    
    // Check for exact matches first
    if (isset($scoring_map[$response])) {
        return $scoring_map[$response];
    }
    
    // Check for partial matches
    foreach ($scoring_map as $keyword => $score) {
        if (strpos($response, $keyword) !== false) {
            return $score;
        }
    }
    
    // Check for numeric years experience
    if (preg_match("/(\d+)(?:\s*(?:years?|yrs?))?/", $response, $matches)) {
        $years = intval($matches[1]);
        if ($years >= 10) return 100;
        if ($years >= 6) return 100;
        if ($years >= 3) return 70;
        if ($years >= 1) return 50;
        return 0;
    }
    
    // If response is not empty but unrecognized, give partial credit
    if (!empty($response)) {
        return 25; // Partial credit for any response
    }
    
    return 0; // Empty response
}

// Calculate weighted score for a question
function calculateWeightedScore($response_score, $weight) {
    return ($response_score * $weight) / 100;
}

// Calculate total application score
function calculateApplicationScore($application_id, $db) {
    $stmt = $db->prepare("
        SELECT ars.*, jsq.weight 
        FROM application_scores ars
        JOIN job_survey_questions jsq ON ars.question_id = jsq.id
        WHERE ars.application_id = ?
    ");
    $stmt->execute([$application_id]);
    $scores = $stmt->fetchAll();
    
    $total_weighted_score = 0;
    $total_possible_score = 0;
    
    foreach ($scores as $score) {
        $total_weighted_score += $score["weighted_score"];
        $total_possible_score += $score["weight"];
    }
    
    $percentage = $total_possible_score > 0 ? ($total_weighted_score / $total_possible_score) * 100 : 0;
    
    return [
        "total_weighted_score" => $total_weighted_score,
        "total_possible_score" => $total_possible_score,
        "percentage" => round($percentage, 2)
    ];
}

// Score categories for display
function getScoreCategory($percentage) {
    if ($percentage >= 80) return ["category" => "Excellent", "class" => "success", "icon" => "star"];
    if ($percentage >= 60) return ["category" => "Good", "class" => "primary", "icon" => "thumbs-up"];
    if ($percentage >= 40) return ["category" => "Average", "class" => "warning", "icon" => "minus-circle"];
    return ["category" => "Poor", "class" => "danger", "icon" => "times-circle"];
}
?>';
        
        if (!is_dir('includes')) {
            mkdir('includes', 0755, true);
        }
        file_put_contents($scoring_functions_file, $scoring_functions);
        echo "<div style='color: green;'>✓ Created scoring helper functions</div>\n";
    } else {
        echo "<div style='color: orange;'>⚠ Scoring functions file already exists</div>\n";
    }
    
    // Step 8: Verification
    echo "<h3>Step 8: Verification & Testing</h3>\n";
    
    // Verify all columns exist
    $required_columns = [
        'job_survey_questions' => ['weight', 'score_mapping'],
        'job_applications' => ['total_score'],
        'jobs' => ['max_possible_score']
    ];
    
    $all_columns_exist = true;
    foreach ($required_columns as $table => $columns) {
        foreach ($columns as $column) {
            $stmt = $db->query("SHOW COLUMNS FROM {$table} LIKE '{$column}'");
            if ($stmt->fetch()) {
                echo "<div style='color: green;'>✓ {$table}.{$column} exists</div>\n";
            } else {
                echo "<div style='color: red;'>✗ {$table}.{$column} missing</div>\n";
                $all_columns_exist = false;
            }
        }
    }
    
    // Test scoring function
    if (file_exists($scoring_functions_file)) {
        include $scoring_functions_file;
        
        echo "<h4>Testing Scoring Functions:</h4>\n";
        $test_responses = ["excellent", "good", "fair", "no", "5 years experience"];
        
        foreach ($test_responses as $response) {
            $score = getResponseScore($response);
            echo "<div style='color: blue;'>• '{$response}' → {$score}%</div>\n";
        }
    }
    
    if ($all_columns_exist) {
        echo "<div style='color: green; padding: 20px; background: #d4edda; border: 1px solid #c3e6cb; margin: 20px 0; border-radius: 5px;'>\n";
        echo "<h2>🎉 Survey Scoring System Setup Complete!</h2>\n";
        
        echo "<h4>✅ New Features Available:</h4>\n";
        echo "<ul>\n";
        echo "<li><strong>Weighted Questions:</strong> Assign importance weights (100-500 points)</li>\n";
        echo "<li><strong>Smart Scoring:</strong> Automatic response evaluation with keywords</li>\n";
        echo "<li><strong>Score Categories:</strong> Excellent (100%), Good (70%), Fair (50%), Poor (0%)</li>\n";
        echo "<li><strong>Application Scoring:</strong> Total weighted scores and percentages</li>\n";
        echo "<li><strong>Performance Tracking:</strong> Compare candidates with objective metrics</li>\n";
        echo "</ul>\n";
        
        echo "<h4>📊 Scoring System Features:</h4>\n";
        echo "<div style='display: grid; grid-template-columns: 1fr 1fr; gap: 20px; margin: 15px 0;'>\n";
        
        echo "<div>\n";
        echo "<h5>🎯 Response Scoring:</h5>\n";
        echo "<ul>\n";
        echo "<li><strong>100% Points:</strong> yes, excellent, expert, advanced, 6+ years</li>\n";
        echo "<li><strong>70% Points:</strong> good, satisfactory, intermediate, 3-5 years</li>\n";
        echo "<li><strong>50% Points:</strong> fair, average, basic, 1-3 years</li>\n";
        echo "<li><strong>0% Points:</strong> no, empty responses, unrecognized</li>\n";
        echo "</ul>\n";
        echo "</div>\n";
        
        echo "<div>\n";
        echo "<h5>⚖️ Weighting System:</h5>\n";
        echo "<ul>\n";
        echo "<li><strong>Critical Questions:</strong> 500 weight (5x importance)</li>\n";
        echo "<li><strong>Important Questions:</strong> 300 weight (3x importance)</li>\n";
        echo "<li><strong>Standard Questions:</strong> 100 weight (1x importance)</li>\n";
        echo "<li><strong>Bonus Questions:</strong> 50 weight (0.5x importance)</li>\n";
        echo "</ul>\n";
        echo "</div>\n";
        
        echo "</div>\n";
        
        echo "<h4>🔧 Test the System:</h4>\n";
        echo "<p>\n";
        echo "<a href='company/post-job.php' style='background: #28a745; color: white; padding: 12px 24px; text-decoration: none; border-radius: 5px; margin: 5px; display: inline-block; font-weight: bold;'>📝 Create Weighted Survey</a>\n";
        echo "<a href='company/applications.php' style='background: #007bff; color: white; padding: 12px 24px; text-decoration: none; border-radius: 5px; margin: 5px; display: inline-block; font-weight: bold;'>📊 View Application Scores</a>\n";
        echo "</p>\n";
        
        echo "</div>\n";
    }
    
} catch (Exception $e) {
    echo "<div style='color: red; padding: 15px; background: #f8d7da; border: 1px solid #f5c6cb; margin: 20px 0; border-radius: 5px;'>\n";
    echo "<h4>💥 Setup Error!</h4>\n";
    echo "<p><strong>Error:</strong> " . htmlspecialchars($e->getMessage()) . "</p>\n";
    echo "<p><strong>File:</strong> " . htmlspecialchars($e->getFile()) . "</p>\n";
    echo "<p><strong>Line:</strong> " . $e->getLine() . "</p>\n";
    echo "</div>\n";
}
?>

<style>
body { 
    font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif; 
    margin: 30px; 
    line-height: 1.6;
    color: #333;
    background-color: #f8f9fa;
}
h1 { color: #2c3e50; margin-bottom: 10px; text-align: center; }
h2 { color: #34495e; margin-top: 30px; }
h3 { color: #7f8c8d; margin-top: 25px; margin-bottom: 15px; border-bottom: 2px solid #ecf0f1; padding-bottom: 5px; }
h4 { color: #5d6d7e; margin-top: 20px; }
h5 { color: #85929e; }
p { margin: 12px 0; }
hr { margin: 25px 0; border: none; border-top: 2px solid #ecf0f1; }
ul, ol { padding-left: 25px; }
li { margin: 8px 0; }
a { text-decoration: none; transition: opacity 0.3s; }
a:hover { opacity: 0.8; }
</style>