-- Migration: Add manager_id to employees table for hierarchy
-- Date: 2025-12-15
-- Description: Adds manager_id column to support HR-Employee hierarchy
-- HR will have manager_id = NULL, Employees under HR will have manager_id = HR's employee_id

ALTER TABLE `employees`
ADD COLUMN `manager_id` INT(11) NULL AFTER `designation_id`,
ADD INDEX `idx_manager_id` (`manager_id`),
ADD CONSTRAINT `fk_emp_manager` FOREIGN KEY (`manager_id`) REFERENCES `employees` (`employee_id`) ON DELETE SET NULL;

-- Update existing HR employees to have manager_id = NULL
-- (Assuming HR employees can be identified by role or designation)
-- This will be handled in application logic
