-- ====================================================================
-- ADD ALL FOREIGN KEY CONSTRAINTS
-- ====================================================================

-- ====================================================================
-- 1. LOCATIONS related
-- ====================================================================

-- Locations table self-reference (manager_id)
ALTER TABLE locations 
  ADD CONSTRAINT fk_locations_manager 
  FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL;

-- ====================================================================
-- 2. DEPARTMENTS related
-- ====================================================================

-- Departments -> Locations
ALTER TABLE departments 
  ADD CONSTRAINT fk_departments_location 
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL;

-- Departments self-reference (parent department)
ALTER TABLE departments 
  ADD CONSTRAINT fk_departments_parent 
  FOREIGN KEY (parent_id) REFERENCES departments(id) ON DELETE SET NULL;

-- Departments -> Employees (head)
ALTER TABLE departments 
  ADD CONSTRAINT fk_departments_head 
  FOREIGN KEY (head_id) REFERENCES employees(id) ON DELETE SET NULL;

-- ====================================================================
-- 3. EMPLOYEES related (most important)
-- ====================================================================

-- Employees -> Departments
ALTER TABLE employees 
  ADD CONSTRAINT fk_employees_department 
  FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL;

-- Employees -> Designations
ALTER TABLE employees 
  ADD CONSTRAINT fk_employees_designation 
  FOREIGN KEY (designation_id) REFERENCES designations(id) ON DELETE SET NULL;

-- Employees self-reference (manager)
ALTER TABLE employees 
  ADD CONSTRAINT fk_employees_manager 
  FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL;

-- Employees -> Locations (works at)
ALTER TABLE employees 
  ADD CONSTRAINT fk_employees_location 
  FOREIGN KEY (works_at_location_id) REFERENCES locations(id) ON DELETE SET NULL;

-- ====================================================================
-- 4. ROLE PERMISSIONS related
-- ====================================================================

-- Role Permissions -> Roles
ALTER TABLE role_permissions 
  ADD CONSTRAINT fk_role_permissions_role 
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE;

-- Role Permissions -> Employee (who granted)
ALTER TABLE role_permissions 
  ADD CONSTRAINT fk_role_permissions_granted_by 
  FOREIGN KEY (granted_by) REFERENCES employees(id) ON DELETE SET NULL;

-- ====================================================================
-- 5. EMPLOYEE PERMISSION OVERRIDES related
-- ====================================================================

-- Employee Permission Overrides -> Employees
ALTER TABLE employee_permission_overrides 
  ADD CONSTRAINT fk_emp_override_employee 
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE;

-- Employee Permission Overrides -> Employee (who granted)
ALTER TABLE employee_permission_overrides 
  ADD CONSTRAINT fk_emp_override_granted_by 
  FOREIGN KEY (granted_by) REFERENCES employees(id) ON DELETE SET NULL;

-- ====================================================================
-- 6. EMPLOYEE ROLES related
-- ====================================================================

-- Employee Roles -> Employees
ALTER TABLE employee_roles 
  ADD CONSTRAINT fk_emp_roles_employee 
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE;

-- Employee Roles -> Roles
ALTER TABLE employee_roles 
  ADD CONSTRAINT fk_emp_roles_role 
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE;

-- Employee Roles -> Employee (assigned by)
ALTER TABLE employee_roles 
  ADD CONSTRAINT fk_emp_roles_assigned_by 
  FOREIGN KEY (assigned_by) REFERENCES employees(id) ON DELETE SET NULL;

-- ====================================================================
-- 7. WORK SCHEDULES related
-- ====================================================================

-- Work Schedules -> Locations
ALTER TABLE work_schedules 
  ADD CONSTRAINT fk_schedules_location 
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL;

-- ====================================================================
-- 8. EMPLOYEE SCHEDULES related
-- ====================================================================

-- Employee Schedules -> Employees
ALTER TABLE employee_schedules 
  ADD CONSTRAINT fk_emp_schedules_employee 
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE;

-- Employee Schedules -> Work Schedules
ALTER TABLE employee_schedules 
  ADD CONSTRAINT fk_emp_schedules_schedule 
  FOREIGN KEY (schedule_id) REFERENCES work_schedules(id) ON DELETE CASCADE;

-- Employee Schedules -> Employee (created by)
ALTER TABLE employee_schedules 
  ADD CONSTRAINT fk_emp_schedules_created_by 
  FOREIGN KEY (created_by) REFERENCES employees(id) ON DELETE SET NULL;

-- ====================================================================
-- 10. LEAVE BALANCES related
-- ====================================================================

-- Leave Balances -> Employees
ALTER TABLE leave_balances 
  ADD CONSTRAINT fk_leave_bal_employee 
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE;

-- Leave Balances -> Leave Policies
ALTER TABLE leave_balances 
  ADD CONSTRAINT fk_leave_bal_policy 
  FOREIGN KEY (policy_id) REFERENCES leave_policies(id) ON DELETE CASCADE;

-- ====================================================================
-- 11. LEAVE REQUESTS related
-- ====================================================================

-- Leave Requests -> Employees
ALTER TABLE leave_requests 
  ADD CONSTRAINT fk_leave_req_employee 
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE;

-- Leave Requests -> Leave Policies
ALTER TABLE leave_requests 
  ADD CONSTRAINT fk_leave_req_policy 
  FOREIGN KEY (policy_id) REFERENCES leave_policies(id) ON DELETE CASCADE;

-- Leave Requests -> Locations
ALTER TABLE leave_requests 
  ADD CONSTRAINT fk_leave_req_location 
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL;

-- Leave Requests -> Employee (reviewed by)
ALTER TABLE leave_requests 
  ADD CONSTRAINT fk_leave_req_reviewed_by 
  FOREIGN KEY (reviewed_by) REFERENCES employees(id) ON DELETE SET NULL;

-- ====================================================================
-- 12. EMPLOYEE SALARIES related
-- ====================================================================

-- Employee Salaries -> Employees
ALTER TABLE employee_salaries 
  ADD CONSTRAINT fk_emp_salaries_employee 
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE;

-- Employee Salaries -> Employee (approved by)
ALTER TABLE employee_salaries 
  ADD CONSTRAINT fk_emp_salaries_approved_by 
  FOREIGN KEY (approved_by) REFERENCES employees(id) ON DELETE SET NULL;

-- ====================================================================
-- 13. PAYROLL RUNS related
-- ====================================================================

-- Payroll Runs -> Locations
ALTER TABLE payroll_runs 
  ADD CONSTRAINT fk_payroll_runs_location 
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL;

-- Payroll Runs -> Employee (processed by)
ALTER TABLE payroll_runs 
  ADD CONSTRAINT fk_payroll_runs_processed_by 
  FOREIGN KEY (processed_by) REFERENCES employees(id) ON DELETE SET NULL;

-- Payroll Runs -> Employee (approved by)
ALTER TABLE payroll_runs 
  ADD CONSTRAINT fk_payroll_runs_approved_by 
  FOREIGN KEY (approved_by) REFERENCES employees(id) ON DELETE SET NULL;

-- ====================================================================
-- 14. PAYSLIPS related
-- ====================================================================

-- Payslips -> Payroll Runs
ALTER TABLE payslips 
  ADD CONSTRAINT fk_payslips_run 
  FOREIGN KEY (payroll_run_id) REFERENCES payroll_runs(id) ON DELETE CASCADE;

-- Payslips -> Employees
ALTER TABLE payslips 
  ADD CONSTRAINT fk_payslips_employee 
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE;

-- Payslips -> Locations
ALTER TABLE payslips 
  ADD CONSTRAINT fk_payslips_location 
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL;

-- ====================================================================
-- 15. LOAN APPLICATIONS related
-- ====================================================================

-- Loan Applications -> Employees
ALTER TABLE loan_applications 
  ADD CONSTRAINT fk_loans_employee 
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE;

-- Loan Applications -> Locations
ALTER TABLE loan_applications 
  ADD CONSTRAINT fk_loans_location 
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL;

-- Loan Applications -> Employee (reviewed by)
ALTER TABLE loan_applications 
  ADD CONSTRAINT fk_loans_reviewed_by 
  FOREIGN KEY (reviewed_by) REFERENCES employees(id) ON DELETE SET NULL;

-- ====================================================================
-- 16. LOAN INSTALLMENTS related
-- ====================================================================

-- Loan Installments -> Loan Applications
ALTER TABLE loan_installments 
  ADD CONSTRAINT fk_loan_inst_loan 
  FOREIGN KEY (loan_id) REFERENCES loan_applications(id) ON DELETE CASCADE;

-- Loan Installments -> Employees
ALTER TABLE loan_installments 
  ADD CONSTRAINT fk_loan_inst_employee 
  FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE;

-- Loan Installments -> Payslips
ALTER TABLE loan_installments 
  ADD CONSTRAINT fk_loan_inst_payslip 
  FOREIGN KEY (payslip_id) REFERENCES payslips(id) ON DELETE SET NULL;

-- ====================================================================
-- 17. EXPENSES related
-- ====================================================================

-- Expenses -> Locations
ALTER TABLE expenses 
  ADD CONSTRAINT fk_expenses_location 
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE CASCADE;

-- Expenses -> Employee (submitted by)
ALTER TABLE expenses 
  ADD CONSTRAINT fk_expenses_submitted_by 
  FOREIGN KEY (submitted_by) REFERENCES employees(id) ON DELETE CASCADE;

-- Expenses -> Departments
ALTER TABLE expenses 
  ADD CONSTRAINT fk_expenses_department 
  FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL;

-- Expenses -> Employee (approved by)
ALTER TABLE expenses 
  ADD CONSTRAINT fk_expenses_approved_by 
  FOREIGN KEY (approved_by) REFERENCES employees(id) ON DELETE SET NULL;

-- Expenses -> Projects
ALTER TABLE expenses 
  ADD CONSTRAINT fk_expenses_project 
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL;

-- Expenses -> Tasks
ALTER TABLE expenses 
  ADD CONSTRAINT fk_expenses_task 
  FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE SET NULL;

-- ====================================================================
-- 18. PROJECTS related
-- ====================================================================

-- Projects -> Locations
ALTER TABLE projects 
  ADD CONSTRAINT fk_projects_location 
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL;

-- Projects -> Departments
ALTER TABLE projects 
  ADD CONSTRAINT fk_projects_department 
  FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL;

-- Projects -> Employee (manager)
ALTER TABLE projects 
  ADD CONSTRAINT fk_projects_manager 
  FOREIGN KEY (manager_id) REFERENCES employees(id) ON DELETE SET NULL;

-- Projects -> Employee (created by)
ALTER TABLE projects 
  ADD CONSTRAINT fk_projects_created_by 
  FOREIGN KEY (created_by) REFERENCES employees(id) ON DELETE SET NULL;

-- ====================================================================
-- 19. TASKS related
-- ====================================================================

-- Tasks -> Projects
ALTER TABLE tasks 
  ADD CONSTRAINT fk_tasks_project 
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE;

-- Tasks self-reference (parent task)
ALTER TABLE tasks 
  ADD CONSTRAINT fk_tasks_parent 
  FOREIGN KEY (parent_task_id) REFERENCES tasks(id) ON DELETE CASCADE;

-- Tasks -> Locations
ALTER TABLE tasks 
  ADD CONSTRAINT fk_tasks_location 
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL;

-- Tasks -> Employee (assigned to)
ALTER TABLE tasks 
  ADD CONSTRAINT fk_tasks_assigned_to 
  FOREIGN KEY (assigned_to) REFERENCES employees(id) ON DELETE SET NULL;

-- Tasks -> Employee (assigned by)
ALTER TABLE tasks 
  ADD CONSTRAINT fk_tasks_assigned_by 
  FOREIGN KEY (assigned_by) REFERENCES employees(id) ON DELETE SET NULL;

-- ====================================================================
-- 20. TASK COMMENTS related
-- ====================================================================

-- Task Comments -> Tasks
ALTER TABLE task_comments 
  ADD CONSTRAINT fk_task_comments_task 
  FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE;

-- Task Comments -> Employee (author)
ALTER TABLE task_comments 
  ADD CONSTRAINT fk_task_comments_author 
  FOREIGN KEY (author_id) REFERENCES employees(id) ON DELETE CASCADE;

-- ====================================================================
-- 21. NOTIFICATIONS related
-- ====================================================================

-- Notifications -> Employee (recipient)
ALTER TABLE notifications 
  ADD CONSTRAINT fk_notifications_recipient 
  FOREIGN KEY (recipient_id) REFERENCES employees(id) ON DELETE CASCADE;

-- ====================================================================
-- 22. AUDIT LOGS related
-- ====================================================================

-- Audit Logs -> Employee (actor)
ALTER TABLE audit_logs 
  ADD CONSTRAINT fk_audit_actor 
  FOREIGN KEY (actor_id) REFERENCES employees(id) ON DELETE SET NULL;

-- Audit Logs -> Locations
ALTER TABLE audit_logs 
  ADD CONSTRAINT fk_audit_location 
  FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL;