-- Fix employee_master_sheet table - Add AUTO_INCREMENT to master_id

-- Step 1: Check current table structure
SHOW COLUMNS FROM employee_master_sheet;

-- Step 2: Update any NULL or 0 master_id values first
SET @row_num = (SELECT COALESCE(MAX(master_id), 0) FROM employee_master_sheet WHERE master_id > 0);

UPDATE employee_master_sheet 
SET master_id = (@row_num := @row_num + 1)
WHERE master_id IS NULL OR master_id = 0;

-- Step 3: Modify the column to add AUTO_INCREMENT
ALTER TABLE employee_master_sheet MODIFY COLUMN `master_id` int(11) NOT NULL AUTO_INCREMENT;

-- Step 4: Verify the fix
SHOW COLUMNS FROM employee_master_sheet LIKE 'master_id';

-- If above doesn't work, try this alternative approach:
-- DROP the primary key and recreate it

-- ALTER TABLE employee_master_sheet DROP PRIMARY KEY;
-- ALTER TABLE employee_master_sheet ADD PRIMARY KEY (master_id);
-- ALTER TABLE employee_master_sheet MODIFY COLUMN `master_id` int(11) NOT NULL AUTO_INCREMENT;
