-- Migration: Add All Statutory Columns to Employees Table
-- Run this script to add PAN, Aadhar, Remarks, ESIC, UAN, and PF columns
-- Date: 2025-01-XX

-- Check and add pf_number if missing
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'employees' 
    AND COLUMN_NAME = 'pf_number');

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `employees` ADD COLUMN `pf_number` varchar(50) AFTER `bank_ifsc`',
    'SELECT "pf_number column already exists" AS message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and add esic_number if missing
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'employees' 
    AND COLUMN_NAME = 'esic_number');

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `employees` ADD COLUMN `esic_number` varchar(50) AFTER `pf_number`',
    'SELECT "esic_number column already exists" AS message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and add uan_number if missing
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'employees' 
    AND COLUMN_NAME = 'uan_number');

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `employees` ADD COLUMN `uan_number` varchar(50) AFTER `esic_number`',
    'SELECT "uan_number column already exists" AS message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and add pan_number if missing
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'employees' 
    AND COLUMN_NAME = 'pan_number');

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `employees` ADD COLUMN `pan_number` varchar(50) AFTER `uan_number`',
    'SELECT "pan_number column already exists" AS message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and add aadhar_number if missing
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'employees' 
    AND COLUMN_NAME = 'aadhar_number');

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `employees` ADD COLUMN `aadhar_number` varchar(50) AFTER `pan_number`',
    'SELECT "aadhar_number column already exists" AS message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Check and add remarks if missing
SET @col_exists = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = 'employees' 
    AND COLUMN_NAME = 'remarks');

SET @sql = IF(@col_exists = 0,
    'ALTER TABLE `employees` ADD COLUMN `remarks` text AFTER `aadhar_number`',
    'SELECT "remarks column already exists" AS message');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Show final status
SELECT 'Migration completed! All statutory columns checked.' AS status;

