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

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

// Simple test for our specific candidates
function getCVBuilderData($db, $candidate_id) {
    $cv_data = ['has_data' => false];
    
    try {
        // Get education data
        $stmt = $db->prepare("SELECT * FROM education WHERE candidate_id = ? ORDER BY start_date DESC");
        $stmt->execute([$candidate_id]);
        $cv_data['education'] = $stmt->fetchAll();
        
        // Get work experience data
        $stmt = $db->prepare("SELECT * FROM work_experience WHERE candidate_id = ? ORDER BY start_date DESC");
        $stmt->execute([$candidate_id]);
        $cv_data['experience'] = $stmt->fetchAll();
        
        // Get references data (try both possible table names)
        try {
            $stmt = $db->prepare("SELECT * FROM work_references WHERE candidate_id = ? ORDER BY created_at DESC");
            $stmt->execute([$candidate_id]);
            $cv_data['references'] = $stmt->fetchAll();
        } catch (PDOException $e) {
            // Try alternative table name
            try {
                $stmt = $db->prepare("SELECT * FROM references WHERE candidate_id = ? ORDER BY created_at DESC");
                $stmt->execute([$candidate_id]);
                $cv_data['references'] = $stmt->fetchAll();
            } catch (PDOException $e2) {
                $cv_data['references'] = [];
            }
        }
        
        // Check if we have any data
        if (!empty($cv_data['education']) || !empty($cv_data['experience']) || !empty($cv_data['references'])) {
            $cv_data['has_data'] = true;
        }
        
    } catch (PDOException $e) {
        error_log("CV Builder data error for candidate $candidate_id: " . $e->getMessage());
    }
    
    return $cv_data;
}

// Test CV builder data for both candidates
$candidates = [12, 13]; // Rammy=12, Sisanda=13

foreach ($candidates as $candidate_id) {
    echo "=== Candidate ID: $candidate_id ===\n";
    
    // Get candidate name
    $stmt = $db->prepare("SELECT first_name, last_name FROM candidates WHERE id = ?");
    $stmt->execute([$candidate_id]);
    $candidate = $stmt->fetch();
    echo "Name: " . $candidate['first_name'] . " " . $candidate['last_name'] . "\n";
    
    // Get CV builder data
    $cv_data = getCVBuilderData($db, $candidate_id);
    echo "Has CV builder data: " . ($cv_data['has_data'] ? 'YES' : 'NO') . "\n";
    
    if ($cv_data['has_data']) {
        echo "Education records: " . count($cv_data['education']) . "\n";
        echo "Experience records: " . count($cv_data['experience']) . "\n";
        echo "References records: " . count($cv_data['references']) . "\n";
    }
    
    // Check if there's a resume record
    $stmt = $db->prepare("SELECT r.* FROM job_applications ja 
                          JOIN resumes r ON ja.resume_id = r.id 
                          WHERE ja.candidate_id = ? AND ja.job_id = 16");
    $stmt->execute([$candidate_id]);
    $resume = $stmt->fetch();
    
    if ($resume) {
        echo "Resume found - ID: {$resume['id']}, Title: {$resume['title']}\n";
        echo "File path: " . ($resume['file_path'] ? $resume['file_path'] : 'EMPTY') . "\n";
    } else {
        echo "No resume found\n";
    }
    
    echo "\n";
}
?>