<?php
/**
 * Safe Migration Script - Adds Statutory Columns
 * This script safely adds columns even if some already exist
 */

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

try {
    $db = getDB();
    
    $columns = [
        ['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']
    ];
    
    echo "Adding statutory columns...\n\n";
    
    foreach ($columns as $col) {
        $colName = $col[0];
        $colType = $col[1];
        $afterCol = $col[2];
        
        // Check if column exists
        $stmt = $db->query("SHOW COLUMNS FROM employees LIKE '{$colName}'");
        if ($stmt->rowCount() > 0) {
            echo "✓ {$colName} already exists\n";
            continue;
        }
        
        // Check if after column exists
        $stmt = $db->query("SHOW COLUMNS FROM employees LIKE '{$afterCol}'");
        if ($stmt->rowCount() > 0) {
            $sql = "ALTER TABLE `employees` ADD COLUMN `{$colName}` {$colType} AFTER `{$afterCol}`";
        } else {
            $sql = "ALTER TABLE `employees` ADD COLUMN `{$colName}` {$colType}";
        }
        
        try {
            $db->exec($sql);
            echo "✓ Added {$colName}\n";
        } catch (Exception $e) {
            echo "✗ Error adding {$colName}: " . $e->getMessage() . "\n";
        }
    }
    
    echo "\nDone!\n";
    
} catch (Exception $e) {
    echo "Error: " . $e->getMessage() . "\n";
}

