-- 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;