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

echo "<h1>🔧 CV Builder Tables Migration</h1>\n";
echo "<p>This script will create all missing tables required for the CV Builder functionality.</p>\n";

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>✓ Database connection successful</p>\n";
    echo "<hr>\n";
    
    // Define all CV builder tables
    $tables = [
        'education' => "
            CREATE TABLE IF NOT EXISTS education (
                id INT AUTO_INCREMENT PRIMARY KEY,
                candidate_id INT NOT NULL,
                institution VARCHAR(255) NOT NULL,
                degree VARCHAR(255) NOT NULL,
                field_of_study VARCHAR(255),
                start_date DATE,
                end_date DATE,
                is_current TINYINT(1) DEFAULT 0,
                description TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
                INDEX idx_candidate_education (candidate_id),
                INDEX idx_education_dates (start_date, end_date)
            )
        ",
        
        'work_experience' => "
            CREATE TABLE IF NOT EXISTS work_experience (
                id INT AUTO_INCREMENT PRIMARY KEY,
                candidate_id INT NOT NULL,
                company VARCHAR(255) NOT NULL,
                position VARCHAR(255) NOT NULL,
                location VARCHAR(255),
                start_date DATE,
                end_date DATE,
                is_current TINYINT(1) DEFAULT 0,
                description TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
                INDEX idx_candidate_experience (candidate_id),
                INDEX idx_experience_dates (start_date, end_date)
            )
        ",
        
        'work_references' => "
            CREATE TABLE IF NOT EXISTS work_references (
                id INT AUTO_INCREMENT PRIMARY KEY,
                candidate_id INT NOT NULL,
                name VARCHAR(255) NOT NULL,
                company VARCHAR(255),
                position VARCHAR(255),
                email VARCHAR(255),
                phone VARCHAR(50),
                relationship VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
                INDEX idx_candidate_references (candidate_id)
            )
        ",
        
        'certificates' => "
            CREATE TABLE IF NOT EXISTS certificates (
                id INT AUTO_INCREMENT PRIMARY KEY,
                candidate_id INT NOT NULL,
                name VARCHAR(255) NOT NULL,
                issuing_organization VARCHAR(255),
                issue_date DATE,
                expiry_date DATE,
                credential_id VARCHAR(255),
                credential_url TEXT,
                description TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
                INDEX idx_candidate_certificates (candidate_id),
                INDEX idx_certificate_dates (issue_date, expiry_date)
            )
        ",
        
        'candidate_documents' => "
            CREATE TABLE IF NOT EXISTS candidate_documents (
                id INT AUTO_INCREMENT PRIMARY KEY,
                candidate_id INT NOT NULL,
                document_name VARCHAR(255) NOT NULL,
                document_type ENUM('cv', 'cover_letter', 'certificate', 'id_document', 'passport', 'work_permit', 'other') DEFAULT 'other',
                file_name VARCHAR(255) NOT NULL,
                file_size INT,
                mime_type VARCHAR(100),
                uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
                INDEX idx_candidate_documents (candidate_id),
                INDEX idx_document_type (document_type)
            )
        ",
        
        'resumes' => "
            CREATE TABLE IF NOT EXISTS resumes (
                id INT AUTO_INCREMENT PRIMARY KEY,
                candidate_id INT NOT NULL,
                title VARCHAR(255) NOT NULL,
                template VARCHAR(50) DEFAULT 'modern',
                is_primary TINYINT(1) DEFAULT 0,
                summary TEXT,
                skills TEXT,
                languages TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
                INDEX idx_candidate_resumes (candidate_id),
                INDEX idx_primary_resume (candidate_id, is_primary)
            )
        "
    ];
    
    $created_tables = [];
    $existing_tables = [];
    
    foreach ($tables as $table_name => $sql) {
        echo "<h3>Creating {$table_name} table</h3>\n";
        
        // Check if table exists
        $stmt = $db->prepare("SHOW TABLES LIKE ?");
        $stmt->execute([$table_name]);
        
        if ($stmt->fetch()) {
            echo "<div style='color: orange;'>⚠ {$table_name} table already exists</div>\n";
            $existing_tables[] = $table_name;
        } else {
            try {
                $db->exec($sql);
                echo "<div style='color: green;'>✓ Created {$table_name} table</div>\n";
                $created_tables[] = $table_name;
            } catch (PDOException $e) {
                echo "<div style='color: red;'>✗ Error creating {$table_name}: " . $e->getMessage() . "</div>\n";
            }
        }
    }
    
    // Verify all tables exist
    echo "<h3>Verification</h3>\n";
    $all_tables_exist = true;
    
    foreach (array_keys($tables) as $table_name) {
        $stmt = $db->prepare("SHOW TABLES LIKE ?");
        $stmt->execute([$table_name]);
        
        if ($stmt->fetch()) {
            echo "<div style='color: green;'>✓ {$table_name} table verified</div>\n";
        } else {
            echo "<div style='color: red;'>✗ {$table_name} table missing</div>\n";
            $all_tables_exist = false;
        }
    }
    
    if ($all_tables_exist) {
        // Test basic queries
        echo "<h3>Testing Queries</h3>\n";
        try {
            foreach (array_keys($tables) as $table_name) {
                $stmt = $db->query("SELECT COUNT(*) as count FROM {$table_name}");
                $result = $stmt->fetch();
                echo "<div style='color: green;'>✓ {$table_name}: {$result['count']} records</div>\n";
            }
            
            echo "<div style='color: green; padding: 20px; background: #d4edda; border: 1px solid #c3e6cb; margin: 20px 0; border-radius: 5px;'>\n";
            echo "<h2>🎉 CV Builder Tables Setup Complete!</h2>\n";
            
            if (!empty($created_tables)) {
                echo "<h4>📋 Newly Created Tables:</h4>\n";
                echo "<ul>\n";
                foreach ($created_tables as $table) {
                    echo "<li><strong>{$table}</strong></li>\n";
                }
                echo "</ul>\n";
            }
            
            if (!empty($existing_tables)) {
                echo "<h4>⚠ Tables Already Existed:</h4>\n";
                echo "<ul>\n";
                foreach ($existing_tables as $table) {
                    echo "<li><strong>{$table}</strong></li>\n";
                }
                echo "</ul>\n";
            }
            
            echo "<h4>✅ CV Builder Features Now Available:</h4>\n";
            echo "<ul>\n";
            echo "<li><strong>Education:</strong> Add degrees, diplomas, certifications</li>\n";
            echo "<li><strong>Work Experience:</strong> Track employment history</li>\n";
            echo "<li><strong>References:</strong> Professional reference contacts</li>\n";
            echo "<li><strong>Certificates:</strong> Professional certifications and licenses</li>\n";
            echo "<li><strong>Documents:</strong> Upload CV, certificates, ID documents</li>\n";
            echo "<li><strong>Resumes:</strong> Create multiple CV versions with templates</li>\n";
            echo "</ul>\n";
            
            echo "<h4>🔧 Test CV Builder:</h4>\n";
            echo "<p>\n";
            echo "<a href='candidate/cv-builder.php' style='background: #28a745; color: white; padding: 12px 24px; text-decoration: none; border-radius: 5px; margin: 5px; display: inline-block; font-weight: bold;'>📄 Open CV Builder</a>\n";
            echo "<a href='candidate/dashboard.php' style='background: #007bff; color: white; padding: 12px 24px; text-decoration: none; border-radius: 5px; margin: 5px; display: inline-block; font-weight: bold;'>📊 Dashboard</a>\n";
            echo "</p>\n";
            
            echo "<h4>💡 Next Steps:</h4>\n";
            echo "<div style='background: #f8f9fa; padding: 15px; border-radius: 5px; margin: 15px 0;'>\n";
            echo "<ol>\n";
            echo "<li>Login as a candidate</li>\n";
            echo "<li>Navigate to CV Builder from the dashboard</li>\n";
            echo "<li>Start adding education, work experience, and references</li>\n";
            echo "<li>Create professional resumes using different templates</li>\n";
            echo "<li>Upload supporting documents and certificates</li>\n";
            echo "</ol>\n";
            echo "</div>\n";
            
            echo "</div>\n";
            
        } catch (PDOException $e) {
            echo "<div style='color: red;'>✗ Error testing queries: " . $e->getMessage() . "</div>\n";
        }
        
    } else {
        echo "<div style='color: red; padding: 15px; background: #f8d7da; border: 1px solid #f5c6cb; margin: 20px 0; border-radius: 5px;'>\n";
        echo "<h4>❌ Setup Incomplete!</h4>\n";
        echo "<p>Some tables are still missing. Please check your database permissions and try again.</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 "<p>Please check your database configuration and try again.</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; }
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; }
.container { max-width: 1000px; margin: 0 auto; background: white; padding: 30px; border-radius: 10px; box-shadow: 0 4px 6px rgba(0,0,0,0.1); }
</style>