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

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

echo "<h1>CV Builder - All Available Sections</h1>";

// Check all CV-related tables and their columns
$tables_to_check = [
    'candidates' => 'Personal Information',
    'education' => 'Education History', 
    'work_experience' => 'Work Experience',
    'work_references' => 'Professional References',
    'certificates' => 'Certificates & Licenses',
    'resumes' => 'Resume/CV Summaries',
    'candidate_documents' => 'Documents & Files'
];

foreach ($tables_to_check as $table => $description) {
    echo "<h2>{$description} ({$table})</h2>";
    
    try {
        $stmt = $db->query("DESCRIBE {$table}");
        $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        echo "<table border='1' style='border-collapse: collapse; width: 100%; margin: 10px 0;'>";
        echo "<tr><th>Column</th><th>Type</th><th>Description</th></tr>";
        
        foreach ($columns as $col) {
            $description_text = '';
            switch($col['Field']) {
                case 'first_name':
                case 'last_name': $description_text = 'Name components'; break;
                case 'email': $description_text = 'Contact email'; break;
                case 'phone': $description_text = 'Phone number'; break;
                case 'address': $description_text = 'Physical address'; break;
                case 'profile_picture': $description_text = 'Photo file path'; break;
                case 'linkedin_url': $description_text = 'LinkedIn profile'; break;
                case 'portfolio_url': $description_text = 'Portfolio website'; break;
                case 'skills': $description_text = 'Skills list (from resume)'; break;
                case 'languages': $description_text = 'Language proficiencies'; break;
                case 'summary': $description_text = 'Professional summary'; break;
                case 'degree': $description_text = 'Academic degree'; break;
                case 'institution': $description_text = 'School/University'; break;
                case 'field_of_study': $description_text = 'Academic major/field'; break;
                case 'company': 
                case 'company_name': $description_text = 'Employer name'; break;
                case 'position': $description_text = 'Job title'; break;
                case 'is_current': $description_text = 'Current position/study'; break;
                case 'description': $description_text = 'Detailed description'; break;
                case 'name': $description_text = 'Name (reference/certificate)'; break;
                case 'issuing_organization': $description_text = 'Who issued certificate'; break;
                case 'credential_id': $description_text = 'Certificate ID'; break;
                case 'credential_url': $description_text = 'Verification URL'; break;
                default: $description_text = ''; break;
            }
            
            echo "<tr>";
            echo "<td><strong>{$col['Field']}</strong></td>";
            echo "<td>{$col['Type']}</td>";
            echo "<td>{$description_text}</td>";
            echo "</tr>";
        }
        echo "</table>";
        
        // Get sample data count
        $count_stmt = $db->query("SELECT COUNT(*) as count FROM {$table}");
        $count = $count_stmt->fetch()['count'];
        echo "<p><em>Records: {$count}</em></p>";
        
    } catch (PDOException $e) {
        echo "<p style='color: red;'>Table {$table} not found: " . $e->getMessage() . "</p>";
    }
}

echo "<h2>Missing CV Sections Analysis</h2>";
echo "<p>Based on the database structure, here are the sections that should be included in a comprehensive CV:</p>";

echo "<h3>✅ Available Sections:</h3>";
echo "<ul>";
echo "<li><strong>Personal Information:</strong> Name, contact details, photo, LinkedIn, portfolio</li>";
echo "<li><strong>Professional Summary:</strong> From resumes table</li>";
echo "<li><strong>Skills:</strong> From resumes table</li>";
echo "<li><strong>Languages:</strong> From resumes table</li>";
echo "<li><strong>Education:</strong> Degrees, institutions, dates, descriptions</li>";
echo "<li><strong>Work Experience:</strong> Companies, positions, dates, descriptions</li>";
echo "<li><strong>Certificates:</strong> Professional certifications with details</li>";
echo "<li><strong>References:</strong> Professional contacts</li>";
echo "</ul>";

echo "<h3>❓ Potentially Missing Sections:</h3>";
echo "<ul>";
echo "<li><strong>Projects:</strong> Personal/professional projects</li>";
echo "<li><strong>Achievements/Awards:</strong> Recognition and awards</li>";
echo "<li><strong>Publications:</strong> Articles, papers, books</li>";
echo "<li><strong>Volunteer Work:</strong> Community service</li>";
echo "<li><strong>Hobbies/Interests:</strong> Personal interests</li>";
echo "</ul>";

echo "<h2>Recommendations for Enhanced CV</h2>";
echo "<div style='background: #e7f3ff; padding: 15px; border-left: 4px solid #007bff;'>";
echo "<ol>";
echo "<li><strong>Include ALL available sections</strong> from existing tables</li>";
echo "<li><strong>Add candidate photo</strong> to PDF header</li>";
echo "<li><strong>Use proper PDF generation</strong> with TCPDF or similar library</li>";
echo "<li><strong>Professional formatting</strong> with sections, headers, and layout</li>";
echo "<li><strong>Consider adding missing tables</strong> for projects, achievements if needed</li>";
echo "</ol>";
echo "</div>";
?>