-- Fix employee_transfers table - Add AUTO_INCREMENT to transfer_id
-- Run this in phpMyAdmin if the PHP fix script doesn't work

-- Step 1: Check current structure
DESCRIBE employee_transfers;

-- Step 2: Update any NULL or 0 transfer_id values (if any exist)
-- First, find the max existing ID
SET @max_id = (SELECT COALESCE(MAX(transfer_id), 0) FROM employee_transfers WHERE transfer_id > 0);

-- Step 3: Add AUTO_INCREMENT to transfer_id column
ALTER TABLE employee_transfers MODIFY COLUMN `transfer_id` int(11) NOT NULL AUTO_INCREMENT;

-- Step 4: Verify the fix
DESCRIBE employee_transfers;

-- Alternative: If table doesn't exist, create it fresh
-- DROP TABLE IF EXISTS employee_transfers;

CREATE TABLE IF NOT EXISTS `employee_transfers` (
  `transfer_id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_id` int(11) NOT NULL,
  `from_site_id` int(11) NOT NULL,
  `to_site_id` int(11) NOT NULL,
  `transfer_date` date NOT NULL,
  `transfer_reason` text,
  `employee_category` varchar(50) DEFAULT NULL,
  `from_location` varchar(255) DEFAULT NULL,
  `to_location` varchar(255) DEFAULT NULL,
  `effective_branch` enum('Yes','No') DEFAULT NULL,
  `effective_attendance` enum('Yes','No') DEFAULT NULL,
  `esi_applicability` enum('Yes','No') DEFAULT NULL,
  `transfer_form_filled` enum('Yes','No') DEFAULT 'No',
  `approved_by_district_head` enum('Yes','No') DEFAULT NULL,
  `transfer_status` enum('pending','approved','completed','cancelled') DEFAULT 'pending',
  `created_by` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transfer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
