-- Add HR assignment tracking to salary_register
-- This ensures each employee's salary data is associated with their managing HR

ALTER TABLE `salary_register` 
ADD COLUMN `hr_id` INT(11) NULL COMMENT 'HR Admin who manages this employee' AFTER `site_id`,
ADD KEY `idx_hr_id` (`hr_id`);

-- Update existing records with HR from employees table
UPDATE salary_register sr
INNER JOIN employees e ON sr.employee_id = e.employee_id
SET sr.hr_id = e.created_by
WHERE sr.hr_id IS NULL;

-- Add foreign key constraint
ALTER TABLE `salary_register`
ADD CONSTRAINT `fk_salary_register_hr` 
FOREIGN KEY (`hr_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL;
