<?php
/**
 * Run Statutory Columns Migration
 * This script adds all missing statutory columns to employees table
 */

require_once __DIR__ . '/../includes/init.php';

header('Content-Type: text/html; charset=utf-8');
?>
<!DOCTYPE html>
<html>
<head>
    <title>Statutory Columns Migration</title>
    <style>
        body { font-family: Arial, sans-serif; margin: 20px; background: #f5f5f5; }
        .container { background: white; padding: 20px; border-radius: 5px; max-width: 800px; margin: 0 auto; }
        .success { color: #28a745; font-weight: bold; }
        .error { color: #dc3545; font-weight: bold; }
        .info { color: #17a2b8; }
        pre { background: #f8f9fa; padding: 15px; border-radius: 3px; overflow-x: auto; }
        .btn { display: inline-block; padding: 10px 20px; background: #007bff; color: white; text-decoration: none; border-radius: 3px; margin-top: 10px; }
        .btn:hover { background: #0056b3; }
    </style>
</head>
<body>
    <div class="container">
        <h2>Statutory Columns Migration</h2>
        <pre>
<?php

try {
    $db = getDB();
    
    echo "Starting migration...\n\n";
    
    $columnsToAdd = [
        ['pf_number', 'varchar(50)', 'bank_ifsc'],
        ['esic_number', 'varchar(50)', 'pf_number'],
        ['uan_number', 'varchar(50)', 'esic_number'],
        ['pan_number', 'varchar(50)', 'uan_number'],
        ['aadhar_number', 'varchar(50)', 'pan_number'],
        ['remarks', 'text', 'aadhar_number']
    ];
    
    // Get existing columns
    $stmt = $db->query("SHOW COLUMNS FROM employees");
    $existingColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    echo "Existing columns: " . count($existingColumns) . " found\n";
    echo "Required columns: " . implode(', ', array_column($columnsToAdd, 0)) . "\n\n";
    
    $added = 0;
    $skipped = 0;
    
    foreach ($columnsToAdd as $column) {
        $colName = $column[0];
        $colType = $column[1];
        $afterCol = $column[2];
        
        if (in_array($colName, $existingColumns)) {
            echo "✓ {$colName} - already exists\n";
            $skipped++;
            continue;
        }
        
        // Check if 'after' column exists
        if (!in_array($afterCol, $existingColumns)) {
            // Add at the end if after column doesn't exist
            $sql = "ALTER TABLE `employees` ADD COLUMN `{$colName}` {$colType}";
            echo "Adding {$colName} at the end (after column '{$afterCol}' not found)...\n";
        } else {
            $sql = "ALTER TABLE `employees` ADD COLUMN `{$colName}` {$colType} AFTER `{$afterCol}`";
            echo "Adding {$colName} after {$afterCol}...\n";
        }
        
        try {
            $db->exec($sql);
            echo "  ✓ Successfully added {$colName}\n\n";
            $added++;
            $existingColumns[] = $colName; // Update list
        } catch (Exception $e) {
            echo "  ✗ Error: " . $e->getMessage() . "\n\n";
        }
    }
    
    echo "\n=== Summary ===\n";
    echo "Columns added: {$added}\n";
    echo "Columns skipped: {$skipped}\n\n";
    
    // Final verification
    echo "=== Final Verification ===\n";
    $stmt = $db->query("SHOW COLUMNS FROM employees");
    $finalColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    $requiredColumns = ['pf_number', 'esic_number', 'uan_number', 'pan_number', 'aadhar_number', 'remarks'];
    $allPresent = true;
    
    foreach ($requiredColumns as $col) {
        if (in_array($col, $finalColumns)) {
            echo "✓ {$col} - EXISTS\n";
        } else {
            echo "✗ {$col} - MISSING\n";
            $allPresent = false;
        }
    }
    
    if ($allPresent) {
        echo "\n<span class='success'>✅ SUCCESS: All required columns are present!</span>\n";
    } else {
        echo "\n<span class='error'>⚠️ WARNING: Some columns are still missing.</span>\n";
    }
    
    // Show table structure
    echo "\n=== Current Table Structure ===\n";
    $stmt = $db->query("SHOW COLUMNS FROM employees");
    $columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $statutoryCols = ['pf_number', 'esic_number', 'uan_number', 'pan_number', 'aadhar_number', 'remarks'];
    foreach ($columns as $col) {
        if (in_array($col['Field'], $statutoryCols)) {
            echo "{$col['Field']} ({$col['Type']})\n";
        }
    }
    
} catch (Exception $e) {
    echo "<span class='error'>Error: " . htmlspecialchars($e->getMessage()) . "</span>\n";
    echo "Stack trace:\n" . htmlspecialchars($e->getTraceAsString()) . "\n";
}

?>
        </pre>
        
        <a href="<?php echo BASE_URL; ?>/modules/employee/new_joinee_statutory.php" class="btn">Go to New Joinee Statutory</a>
        <a href="<?php echo BASE_URL; ?>/database/check_and_fix_statutory_columns.php" class="btn">Check Columns Again</a>
    </div>
</body>
</html>

