-- Enhanced Employee Transfers Table
-- Includes all fields from the transfer form

-- First, check and add missing columns if they don't exist
-- Note: This uses a stored procedure approach for MySQL compatibility

DROP PROCEDURE IF EXISTS AddTransferColumnIfNotExists$$

DELIMITER $$

CREATE PROCEDURE AddTransferColumnIfNotExists(
    IN colName VARCHAR(64),
    IN colType VARCHAR(64),
    IN afterCol VARCHAR(64)
)
BEGIN
    DECLARE colCount INT DEFAULT 0;
    
    SELECT COUNT(*) INTO colCount
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'employee_transfers'
      AND COLUMN_NAME = colName;
    
    IF colCount = 0 THEN
        SET @sql = CONCAT('ALTER TABLE employee_transfers ADD COLUMN ', colName, ' ', colType, 
                         IF(afterCol IS NOT NULL AND afterCol != '', CONCAT(' AFTER ', afterCol), ''));
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END$$

DELIMITER ;

-- Add missing columns
CALL AddTransferColumnIfNotExists('from_location', 'VARCHAR(255)', 'from_site_id');
CALL AddTransferColumnIfNotExists('to_location', 'VARCHAR(255)', 'to_site_id');
CALL AddTransferColumnIfNotExists('effective_branch', 'ENUM("Yes","No")', 'transfer_date');
CALL AddTransferColumnIfNotExists('effective_attendance', 'ENUM("Yes","No")', 'effective_branch');
CALL AddTransferColumnIfNotExists('esi_applicability', 'ENUM("Yes","No")', 'effective_attendance');
CALL AddTransferColumnIfNotExists('transfer_form_filled', 'ENUM("Yes","No") DEFAULT "No"', 'esi_applicability');
CALL AddTransferColumnIfNotExists('approved_by_district_head', 'ENUM("Yes","No")', 'transfer_form_filled');

-- Drop the procedure after use
DROP PROCEDURE IF EXISTS AddTransferColumnIfNotExists;

