-- Direct SQL Script to Add Statutory Columns
-- Run this in phpMyAdmin or MySQL command line
-- Note: If column already exists, you'll get an error - that's OK, just continue

-- Step 1: Add pf_number (run only if it doesn't exist)
-- ALTER TABLE `employees` ADD COLUMN `pf_number` varchar(50) AFTER `bank_ifsc`;

-- Step 2: Add esic_number (run only if it doesn't exist)
-- ALTER TABLE `employees` ADD COLUMN `esic_number` varchar(50) AFTER `pf_number`;

-- Step 3: Add uan_number (run only if it doesn't exist)
-- ALTER TABLE `employees` ADD COLUMN `uan_number` varchar(50) AFTER `esic_number`;

-- Step 4: Add pan_number (run only if it doesn't exist)
-- ALTER TABLE `employees` ADD COLUMN `pan_number` varchar(50) AFTER `uan_number`;

-- Step 5: Add aadhar_number (run only if it doesn't exist)
-- ALTER TABLE `employees` ADD COLUMN `aadhar_number` varchar(50) AFTER `pan_number`;

-- Step 6: Add remarks (run only if it doesn't exist)
-- ALTER TABLE `employees` ADD COLUMN `remarks` text AFTER `aadhar_number`;

-- OR use this procedure-based approach (uncomment to use):
DELIMITER $$

DROP PROCEDURE IF EXISTS AddColumnIfNotExists$$
CREATE PROCEDURE AddColumnIfNotExists(
    IN tableName VARCHAR(128),
    IN columnName VARCHAR(128),
    IN columnDefinition TEXT,
    IN afterColumn VARCHAR(128)
)
BEGIN
    DECLARE columnExists INT DEFAULT 0;
    
    SELECT COUNT(*) INTO columnExists
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = tableName
    AND COLUMN_NAME = columnName;
    
    IF columnExists = 0 THEN
        SET @sql = CONCAT('ALTER TABLE `', tableName, '` ADD COLUMN `', columnName, '` ', columnDefinition);
        IF afterColumn != '' THEN
            SET @sql = CONCAT(@sql, ' AFTER `', afterColumn, '`');
        END IF;
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        SELECT CONCAT('Added column: ', columnName) AS result;
    ELSE
        SELECT CONCAT('Column already exists: ', columnName) AS result;
    END IF;
END$$

DELIMITER ;

-- Now add columns using the procedure
CALL AddColumnIfNotExists('employees', 'pf_number', 'varchar(50)', 'bank_ifsc');
CALL AddColumnIfNotExists('employees', 'esic_number', 'varchar(50)', 'pf_number');
CALL AddColumnIfNotExists('employees', 'uan_number', 'varchar(50)', 'esic_number');
CALL AddColumnIfNotExists('employees', 'pan_number', 'varchar(50)', 'uan_number');
CALL AddColumnIfNotExists('employees', 'aadhar_number', 'varchar(50)', 'pan_number');
CALL AddColumnIfNotExists('employees', 'remarks', 'text', 'aadhar_number');

-- Clean up
DROP PROCEDURE IF EXISTS AddColumnIfNotExists;

-- Verify columns exist
SELECT 
    COLUMN_NAME, 
    DATA_TYPE, 
    CHARACTER_MAXIMUM_LENGTH,
    COLUMN_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = DATABASE() 
AND TABLE_NAME = 'employees' 
AND COLUMN_NAME IN ('pf_number', 'esic_number', 'uan_number', 'pan_number', 'aadhar_number', 'remarks')
ORDER BY ORDINAL_POSITION;

