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

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

try {
    $database = new Database();
    $db = $database->getConnection();
    
    echo "<p>Connected to database successfully.</p>\n";
    
    // First, make sure we have the basic candidate type columns
    echo "<p><strong>Step 1: Ensuring basic columns exist...</strong></p>\n";
    
    try {
        // Check if candidate_type column exists
        $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'candidate_type'");
        if (!$stmt->fetch()) {
            $db->exec("ALTER TABLE candidates ADD COLUMN candidate_type ENUM('internal', 'external') DEFAULT 'external' AFTER gender");
            echo "<div style='color: green;'>✓ Added candidate_type column</div>\n";
        } else {
            echo "<div style='color: blue;'>ℹ candidate_type column already exists</div>\n";
        }
        
        // Check if employee_code column exists
        $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'employee_code'");
        if (!$stmt->fetch()) {
            $db->exec("ALTER TABLE candidates ADD COLUMN employee_code VARCHAR(20) NULL AFTER candidate_type");
            echo "<div style='color: green;'>✓ Added employee_code column</div>\n";
        } else {
            echo "<div style='color: blue;'>ℹ employee_code column already exists</div>\n";
        }
        
        // Check if hr_approval_status column exists
        $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'hr_approval_status'");
        if (!$stmt->fetch()) {
            $db->exec("ALTER TABLE candidates ADD COLUMN hr_approval_status ENUM('pending', 'approved', 'rejected') DEFAULT 'approved' AFTER employee_code");
            echo "<div style='color: green;'>✓ Added hr_approval_status column</div>\n";
        } else {
            echo "<div style='color: blue;'>ℹ hr_approval_status column already exists</div>\n";
        }
        
        // Check if hr_approval_date column exists
        $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'hr_approval_date'");
        if (!$stmt->fetch()) {
            $db->exec("ALTER TABLE candidates ADD COLUMN hr_approval_date TIMESTAMP NULL AFTER hr_approval_status");
            echo "<div style='color: green;'>✓ Added hr_approval_date column</div>\n";
        } else {
            echo "<div style='color: blue;'>ℹ hr_approval_date column already exists</div>\n";
        }
        
        // Check if hr_approval_notes column exists
        $stmt = $db->query("SHOW COLUMNS FROM candidates LIKE 'hr_approval_notes'");
        if (!$stmt->fetch()) {
            $db->exec("ALTER TABLE candidates ADD COLUMN hr_approval_notes TEXT NULL AFTER hr_approval_date");
            echo "<div style='color: green;'>✓ Added hr_approval_notes column</div>\n";
        } else {
            echo "<div style='color: blue;'>ℹ hr_approval_notes column already exists</div>\n";
        }
        
        // Add job_applications columns
        $stmt = $db->query("SHOW COLUMNS FROM job_applications LIKE 'hr_approval_required'");
        if (!$stmt->fetch()) {
            $db->exec("ALTER TABLE job_applications ADD COLUMN hr_approval_required BOOLEAN DEFAULT FALSE AFTER status");
            echo "<div style='color: green;'>✓ Added hr_approval_required column to job_applications</div>\n";
        } else {
            echo "<div style='color: blue;'>ℹ hr_approval_required column already exists</div>\n";
        }
        
        $stmt = $db->query("SHOW COLUMNS FROM job_applications LIKE 'hr_approval_status'");
        if (!$stmt->fetch()) {
            $db->exec("ALTER TABLE job_applications ADD COLUMN hr_approval_status ENUM('pending', 'approved', 'rejected') NULL AFTER hr_approval_required");
            echo "<div style='color: green;'>✓ Added hr_approval_status column to job_applications</div>\n";
        } else {
            echo "<div style='color: blue;'>ℹ hr_approval_status column already exists</div>\n";
        }
        
    } catch (PDOException $e) {
        echo "<div style='color: orange;'>⚠ Warning in basic setup: " . $e->getMessage() . "</div>\n";
    }
    
    echo "<p><strong>Step 2: Running company approval migration...</strong></p>\n";
    
    // Read the fixed company approval SQL migration file
    $sqlFile = __DIR__ . '/company_approval_fixed_update.sql';
    if (!file_exists($sqlFile)) {
        throw new Exception("Migration file not found: $sqlFile");
    }
    
    $sql = file_get_contents($sqlFile);
    
    // Split by DELIMITER sections first
    $sections = preg_split('/DELIMITER\s+[;\\/]+/i', $sql);
    
    $executed = 0;
    $errors = 0;
    
    foreach ($sections as $section) {
        if (trim($section) == '') continue;
        
        // If section contains a trigger, handle it specially
        if (strpos($section, 'CREATE TRIGGER') !== false) {
            try {
                $db->exec(trim($section));
                $executed++;
                echo "<div style='color: green;'>✓ Created trigger successfully</div>\n";
            } catch (PDOException $e) {
                $errors++;
                echo "<div style='color: red;'>✗ Trigger error: " . $e->getMessage() . "</div>\n";
            }
        } else {
            // Split regular statements by semicolon
            $statements = array_filter(array_map('trim', explode(';', $section)));
            
            foreach ($statements as $statement) {
                if (empty($statement) || substr($statement, 0, 2) === '--') {
                    continue;
                }
                
                try {
                    $result = $db->exec($statement);
                    $executed++;
                    // Only show successful non-trivial operations
                    if (strpos($statement, 'SELECT ') === 0) {
                        // Skip showing SELECT statements used for checks
                    } else {
                        echo "<div style='color: green;'>✓ Executed: " . substr($statement, 0, 60) . "...</div>\n";
                    }
                } catch (PDOException $e) {
                    $errors++;
                    // Skip common "already exists" errors
                    if (strpos($e->getMessage(), 'already exists') !== false || 
                        strpos($e->getMessage(), 'Duplicate column') !== false ||
                        strpos($e->getMessage(), 'Duplicate key') !== false) {
                        echo "<div style='color: orange;'>⚠ Skipped (already exists): " . substr($statement, 0, 60) . "...</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";
    
    // Verification
    echo "<hr>\n";
    echo "<p><strong>Verification:</strong></p>\n";
    
    // Check all required columns
    $required_columns = [
        'candidates' => ['candidate_type', 'employee_code', 'company_id', 'hr_approval_status', 'approved_by'],
        'job_applications' => ['hr_approval_required', 'hr_approval_status', 'hr_approved_by']
    ];
    
    foreach ($required_columns as $table => $columns) {
        $stmt = $db->query("DESCRIBE $table");
        $existing_columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
        
        foreach ($columns as $col) {
            if (in_array($col, $existing_columns)) {
                echo "<div style='color: green;'>✓ $table.$col column exists</div>\n";
            } else {
                echo "<div style='color: red;'>✗ $table.$col column missing</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</div>\n";
    }
    
    // Check if trigger exists
    try {
        $stmt = $db->query("SHOW TRIGGERS LIKE 'job_applications'");
        if ($stmt->fetch()) {
            echo "<div style='color: green;'>✓ Database trigger created successfully</div>\n";
        } else {
            echo "<div style='color: orange;'>⚠ Database trigger not found</div>\n";
        }
    } catch (PDOException $e) {
        echo "<div style='color: orange;'>⚠ Could not verify trigger</div>\n";
    }
    
    echo "<hr>\n";
    echo "<p><strong>System is ready!</strong></p>\n";
    echo "<p>The company approval system has been set up. Here's how it works:</p>\n";
    echo "<ol>\n";
    echo "<li><strong>Internal candidates</strong> register and select their company</li>\n";
    echo "<li><strong>Companies</strong> approve/reject their internal employees</li>\n";
    echo "<li><strong>Approved internal employees</strong> can apply only to their company's jobs</li>\n";
    echo "<li><strong>External candidates</strong> can apply to any company's jobs</li>\n";
    echo "</ol>\n";
    
    echo "<p><strong>Test the system:</strong></p>\n";
    echo "<ul>\n";
    echo "<li><a href='auth/register.php'>Register as Internal Candidate</a></li>\n";
    echo "<li><a href='company/internal-employees.php'>Company: Manage Internal Employees</a></li>\n";
    echo "<li><a href='auth/login.php'>Login and Test Job Applications</a></li>\n";
    echo "</ul>\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";
}
?>