-- Migration: Create Employee Master Change Table (Simple Version)
-- Date: 2025-01-XX
-- Description: Creates employee_master_change table with all required columns for CRUD operations
--              This is a simpler version that can be run directly in MySQL

-- Create the employee_master_change table if it doesn't exist
CREATE TABLE IF NOT EXISTS `employee_master_change` (
  `change_id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `employee_id` int(11) DEFAULT NULL,
  `sl_no` int(11) DEFAULT NULL,
  `emp_id` varchar(50) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `designation` varchar(255) DEFAULT NULL,
  `site_name` varchar(255) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `present_address` text,
  `permanent_address` text,
  `pay_mode` enum('Bank','Hold') DEFAULT 'Bank',
  `bank_name` varchar(255) DEFAULT NULL,
  `bank_account_number` varchar(100) DEFAULT NULL,
  `bank_ref_number` varchar(100) DEFAULT NULL,
  `bank_ifsc_code` varchar(50) DEFAULT NULL,
  `esic_number` varchar(50) DEFAULT NULL,
  `pf_number` varchar(50) DEFAULT NULL,
  `remarks` text,
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`change_id`),
  KEY `fk_emc_site` (`site_id`),
  KEY `fk_emc_emp` (`employee_id`),
  KEY `fk_emc_created_by` (`created_by`),
  KEY `idx_emp_id` (`emp_id`),
  KEY `idx_name` (`name`),
  KEY `idx_sl_no` (`sl_no`),
  KEY `idx_site_id` (`site_id`),
  CONSTRAINT `fk_emc_site` FOREIGN KEY (`site_id`) REFERENCES `sites` (`site_id`) ON DELETE RESTRICT,
  CONSTRAINT `fk_emc_emp` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`employee_id`) ON DELETE SET NULL,
  CONSTRAINT `fk_emc_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add missing columns (run these individually if you get errors about columns already existing)
-- Uncomment and run the ALTER statements for columns that are missing

-- ALTER TABLE `employee_master_change` ADD COLUMN IF NOT EXISTS `sl_no` int(11) DEFAULT NULL;
-- Note: MySQL doesn't support IF NOT EXISTS for ALTER TABLE ADD COLUMN
-- So check manually or use the prepared statement version

-- Alternative: Use these ALTER statements with error handling
-- Run each one individually and ignore errors if column already exists

-- ALTER TABLE `employee_master_change` ADD COLUMN `sl_no` int(11) DEFAULT NULL;
-- ALTER TABLE `employee_master_change` ADD COLUMN `emp_id` varchar(50) DEFAULT NULL;
-- ALTER TABLE `employee_master_change` ADD COLUMN `name` varchar(255) DEFAULT NULL;
-- ALTER TABLE `employee_master_change` ADD COLUMN `designation` varchar(255) DEFAULT NULL;
-- ALTER TABLE `employee_master_change` ADD COLUMN `site_name` varchar(255) DEFAULT NULL;
-- ALTER TABLE `employee_master_change` ADD COLUMN `category` varchar(100) DEFAULT NULL;
-- ALTER TABLE `employee_master_change` ADD COLUMN `present_address` text;
-- ALTER TABLE `employee_master_change` ADD COLUMN `permanent_address` text;
-- ALTER TABLE `employee_master_change` ADD COLUMN `pay_mode` enum('Bank','Hold') DEFAULT 'Bank';
-- ALTER TABLE `employee_master_change` ADD COLUMN `bank_name` varchar(255) DEFAULT NULL;
-- ALTER TABLE `employee_master_change` ADD COLUMN `bank_account_number` varchar(100) DEFAULT NULL;
-- ALTER TABLE `employee_master_change` ADD COLUMN `bank_ref_number` varchar(100) DEFAULT NULL;
-- ALTER TABLE `employee_master_change` ADD COLUMN `bank_ifsc_code` varchar(50) DEFAULT NULL;
-- ALTER TABLE `employee_master_change` ADD COLUMN `esic_number` varchar(50) DEFAULT NULL;
-- ALTER TABLE `employee_master_change` ADD COLUMN `pf_number` varchar(50) DEFAULT NULL;
-- ALTER TABLE `employee_master_change` ADD COLUMN `remarks` text;

-- Add indexes if they don't exist
-- CREATE INDEX IF NOT EXISTS `idx_sl_no` ON `employee_master_change` (`sl_no`);
-- CREATE INDEX IF NOT EXISTS `idx_site_id` ON `employee_master_change` (`site_id`);
-- Note: MySQL 5.7+ supports IF NOT EXISTS for CREATE INDEX

