<?php
/**
 * Check and Fix Statutory Columns in Employees Table
 * This script checks if PAN, Aadhar, and Remarks columns exist and adds them if missing
 */

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

echo "<h2>Checking and Fixing Statutory Columns in Employees Table</h2>";
echo "<pre>";

try {
    $db = getDB();
    
    // Check current columns
    echo "Checking existing columns in employees table...\n\n";
    $stmt = $db->query("SHOW COLUMNS FROM employees");
    $existingColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    echo "Existing columns: " . implode(', ', $existingColumns) . "\n\n";
    
    $columnsToAdd = [];
    
    // Check for pan_number
    if (!in_array('pan_number', $existingColumns)) {
        $columnsToAdd[] = [
            'name' => 'pan_number',
            'type' => 'varchar(50)',
            'after' => 'uan_number'
        ];
        echo "❌ pan_number column is MISSING\n";
    } else {
        echo "✓ pan_number column exists\n";
    }
    
    // Check for aadhar_number
    if (!in_array('aadhar_number', $existingColumns)) {
        $columnsToAdd[] = [
            'name' => 'aadhar_number',
            'type' => 'varchar(50)',
            'after' => 'pan_number'
        ];
        echo "❌ aadhar_number column is MISSING\n";
    } else {
        echo "✓ aadhar_number column exists\n";
    }
    
    // Check for remarks
    if (!in_array('remarks', $existingColumns)) {
        $columnsToAdd[] = [
            'name' => 'remarks',
            'type' => 'text',
            'after' => 'aadhar_number'
        ];
        echo "❌ remarks column is MISSING\n";
    } else {
        echo "✓ remarks column exists\n";
    }
    
    // Check for esic_number
    if (!in_array('esic_number', $existingColumns)) {
        $columnsToAdd[] = [
            'name' => 'esic_number',
            'type' => 'varchar(50)',
            'after' => 'pf_number'
        ];
        echo "❌ esic_number column is MISSING\n";
    } else {
        echo "✓ esic_number column exists\n";
    }
    
    // Check for uan_number
    if (!in_array('uan_number', $existingColumns)) {
        $columnsToAdd[] = [
            'name' => 'uan_number',
            'type' => 'varchar(50)',
            'after' => 'esic_number'
        ];
        echo "❌ uan_number column is MISSING\n";
    } else {
        echo "✓ uan_number column exists\n";
    }
    
    // Check for pf_number
    if (!in_array('pf_number', $existingColumns)) {
        $columnsToAdd[] = [
            'name' => 'pf_number',
            'type' => 'varchar(50)',
            'after' => 'bank_ifsc'
        ];
        echo "❌ pf_number column is MISSING\n";
    } else {
        echo "✓ pf_number column exists\n";
    }
    
    echo "\n";
    
    // Add missing columns
    if (!empty($columnsToAdd)) {
        echo "Adding missing columns...\n\n";
        
        foreach ($columnsToAdd as $column) {
            try {
                // Find the position to add column
                $afterColumn = $column['after'];
                
                // Check if after column exists
                if (!in_array($afterColumn, $existingColumns)) {
                    // If after column doesn't exist, add at the end
                    $sql = "ALTER TABLE `employees` ADD COLUMN `{$column['name']}` {$column['type']}";
                    echo "Adding {$column['name']} at the end (after column '{$afterColumn}' not found)...\n";
                } else {
                    $sql = "ALTER TABLE `employees` ADD COLUMN `{$column['name']}` {$column['type']} AFTER `{$afterColumn}`";
                    echo "Adding {$column['name']} after {$afterColumn}...\n";
                }
                
                $db->exec($sql);
                echo "✓ Successfully added {$column['name']} column\n\n";
                
                // Update existing columns list
                $existingColumns[] = $column['name'];
                
            } catch (Exception $e) {
                echo "✗ Error adding {$column['name']}: " . $e->getMessage() . "\n\n";
            }
        }
    } else {
        echo "All required columns already exist!\n";
    }
    
    // Final verification
    echo "\n=== Final Verification ===\n";
    $stmt = $db->query("SHOW COLUMNS FROM employees");
    $finalColumns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
    $requiredColumns = ['pan_number', 'aadhar_number', 'remarks', 'esic_number', 'uan_number', 'pf_number'];
    $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✅ All required columns are present in the employees table!\n";
    } else {
        echo "\n⚠️ Some columns are still missing. Please check the errors above.\n";
    }
    
    // Show sample data
    echo "\n=== Sample Data Check ===\n";
    $stmt = $db->query("SELECT employee_id, employee_code, first_name, last_name, pan_number, aadhar_number, remarks FROM employees LIMIT 5");
    $samples = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    if (empty($samples)) {
        echo "No employees found in database.\n";
    } else {
        echo "Sample employee data:\n";
        foreach ($samples as $emp) {
            echo "ID: {$emp['employee_id']}, Code: {$emp['employee_code']}, Name: {$emp['first_name']} {$emp['last_name']}\n";
            echo "  PAN: " . ($emp['pan_number'] ?? 'NULL') . "\n";
            echo "  Aadhar: " . ($emp['aadhar_number'] ?? 'NULL') . "\n";
            echo "  Remarks: " . (substr($emp['remarks'] ?? 'NULL', 0, 50)) . "\n";
            echo "\n";
        }
    }
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
    echo "Stack trace:\n" . $e->getTraceAsString() . "\n";
}

echo "</pre>";
echo "<p><a href='" . BASE_URL . "/modules/employee/new_joinee_statutory.php'>Go to New Joinee Statutory</a></p>";

