60 lines
2.1 KiB
SQL
60 lines
2.1 KiB
SQL
-- Fix Leave Requests RLS to allow managers to approve/reject
|
|
-- Enable UPDATE for managers belonging to the same company as the request
|
|
|
|
DROP POLICY IF EXISTS "Managers can update leave requests in their company" ON public.leave_requests;
|
|
|
|
CREATE POLICY "Managers can update leave requests in their company"
|
|
ON public.leave_requests
|
|
FOR UPDATE TO authenticated
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM public.employees manager_emp
|
|
JOIN public.roles r ON manager_emp.role_id = r.id
|
|
WHERE manager_emp.user_id = auth.uid()
|
|
AND r.name IN ('admin', 'manager')
|
|
AND manager_emp.company_id = (
|
|
SELECT company_id FROM public.employees target_emp
|
|
WHERE target_emp.id = public.leave_requests.employee_id
|
|
)
|
|
)
|
|
)
|
|
WITH CHECK (
|
|
EXISTS (
|
|
SELECT 1 FROM public.employees manager_emp
|
|
JOIN public.roles r ON manager_emp.role_id = r.id
|
|
WHERE manager_emp.user_id = auth.uid()
|
|
AND r.name IN ('admin', 'manager')
|
|
AND manager_emp.company_id = (
|
|
SELECT company_id FROM public.employees target_emp
|
|
WHERE target_emp.id = public.leave_requests.employee_id
|
|
)
|
|
)
|
|
);
|
|
|
|
-- Ensure managers can also view everything in their company
|
|
DROP POLICY IF EXISTS "Managers can view all leave requests in their company" ON public.leave_requests;
|
|
CREATE POLICY "Managers can view all leave requests in their company"
|
|
ON public.leave_requests
|
|
FOR SELECT TO authenticated
|
|
USING (
|
|
EXISTS (
|
|
SELECT 1 FROM public.employees manager_emp
|
|
JOIN public.roles r ON manager_emp.role_id = r.id
|
|
WHERE manager_emp.user_id = auth.uid()
|
|
AND r.name IN ('admin', 'manager')
|
|
AND manager_emp.company_id = (
|
|
SELECT company_id FROM public.employees target_emp
|
|
WHERE target_emp.id = public.leave_requests.employee_id
|
|
)
|
|
)
|
|
OR
|
|
employee_id IN (
|
|
SELECT id FROM public.employees WHERE user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
-- Update existing records with NULL total_days
|
|
UPDATE public.leave_requests
|
|
SET total_days = public.calculate_leave_days(start_date, end_date)
|
|
WHERE total_days IS NULL;
|