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

echo "<h2>Company Approval System Migration</h2>\n";

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>Connected to database successfully.</p>\n";
    
    // Read the company approval SQL migration file
    $sqlFile = __DIR__ . '/company_approval_update.sql';
    if (!file_exists($sqlFile)) {
        throw new Exception("Migration file not found: $sqlFile");
    }
    
    $sql = file_get_contents($sqlFile);
    echo "<p>Migration file loaded successfully.</p>\n";
    
    // Split SQL statements and execute them
    $statements = array_filter(array_map('trim', explode(';', $sql)));
    
    $executed = 0;
    $errors = 0;
    
    foreach ($statements as $statement) {
        if (empty($statement) || substr($statement, 0, 2) === '--' || substr($statement, 0, 9) === 'DELIMITER') {
            continue;
        }
        
        try {
            $db->exec($statement);
            $executed++;
            echo "<div style='color: green;'>✓ Executed: " . substr($statement, 0, 80) . "...</div>\n";
        } catch (PDOException $e) {
            $errors++;
            // Check if it's just a "table already exists" or "column already exists" error
            if (strpos($e->getMessage(), 'already exists') !== false || 
                strpos($e->getMessage(), 'Duplicate column') !== false ||
                strpos($e->getMessage(), 'Duplicate key') !== false ||
                strpos($e->getMessage(), 'Cannot delete or update a parent row') !== false) {
                echo "<div style='color: orange;'>⚠ Skipped (constraint/already exists): " . substr($statement, 0, 80) . "...</div>\n";
            } else {
                echo "<div style='color: red;'>✗ Error: " . $e->getMessage() . "</div>\n";
                echo "<div style='color: red;'>Statement: " . substr($statement, 0, 100) . "...</div>\n";
            }
        }
    }
    
    echo "<hr>\n";
    echo "<p><strong>Migration Summary:</strong></p>\n";
    echo "<p>✓ Executed: $executed statements</p>\n";
    echo "<p>⚠ Errors/Skipped: $errors statements</p>\n";
    
    // Verify key columns were added
    echo "<hr>\n";
    echo "<p><strong>Verification:</strong></p>\n";
    
    // Check if candidates table has company_id column
    try {
        $stmt = $db->query("DESCRIBE candidates");
        $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        if (in_array('company_id', $columns)) {
            echo "<div style='color: green;'>✓ Candidates table has company_id column</div>\n";
        } else {
            echo "<div style='color: red;'>✗ Candidates table missing company_id column</div>\n";
        }
        
    } catch (PDOException $e) {
        echo "<div style='color: red;'>✗ Could not verify candidates table: " . $e->getMessage() . "</div>\n";
    }
    
    // Check if view was created
    try {
        $stmt = $db->query("SELECT COUNT(*) FROM company_internal_candidates LIMIT 1");
        echo "<div style='color: green;'>✓ company_internal_candidates view created successfully</div>\n";
    } catch (PDOException $e) {
        echo "<div style='color: orange;'>⚠ company_internal_candidates view not created or has issues</div>\n";
    }
    
    echo "<hr>\n";
    echo "<p><strong>How the Company Approval System Works:</strong></p>\n";
    echo "<ol>\n";
    echo "<li><strong>Registration:</strong> Internal candidates select their company and enter employee code</li>\n";
    echo "<li><strong>Company Approval:</strong> Company users can approve/reject their internal employees</li>\n";
    echo "<li><strong>Job Applications:</strong> Internal employees can only apply to jobs within their own company</li>\n";
    echo "<li><strong>Restrictions:</strong> Internal employees cannot apply to external company jobs</li>\n";
    echo "</ol>\n";
    
    echo "<hr>\n";
    echo "<p><strong>Next Steps:</strong></p>\n";
    echo "<ol>\n";
    echo "<li>Test internal candidate registration at <a href='auth/register.php'>Register</a></li>\n";
    echo "<li>Login as a company user and go to <a href='company/internal-employees.php'>Company > Internal Employees</a></li>\n";
    echo "<li>Approve internal candidates from your company</li>\n";
    echo "<li>Test that internal employees can only apply to their company's jobs</li>\n";
    echo "</ol>\n";
    
} catch (Exception $e) {
    echo "<div style='color: red;'><strong>Fatal Error:</strong> " . $e->getMessage() . "</div>\n";
    echo "<p>Please check your database configuration and try again.</p>\n";
}
?>