CREATE OR REPLACE FUNCTION public.auto_checkout_expired_shifts()
RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  v_count integer;
  v_tz text := 'Asia/Dhaka';
BEGIN
  WITH expired AS (
    SELECT id,
      CASE shift
        WHEN 'A' THEN (date_trunc('day', check_in_at AT TIME ZONE v_tz) + interval '14 hours') AT TIME ZONE v_tz
        WHEN 'B' THEN (date_trunc('day', check_in_at AT TIME ZONE v_tz) + interval '22 hours') AT TIME ZONE v_tz
        WHEN 'C' THEN (date_trunc('day', check_in_at AT TIME ZONE v_tz) + interval '30 hours') AT TIME ZONE v_tz
        ELSE check_in_at + interval '8 hours'
      END AS shift_end
    FROM public.attendance
    WHERE status = 'checked_in'
  )
  UPDATE public.attendance a
  SET status = 'checked_out',
      check_out_at = LEAST(now(), e.shift_end + interval '8 hours')
  FROM expired e
  WHERE a.id = e.id AND e.shift_end < now();
  GET DIAGNOSTICS v_count = ROW_COUNT;
  RETURN v_count;
END;
$$;

REVOKE ALL ON FUNCTION public.auto_checkout_expired_shifts() FROM public;
GRANT EXECUTE ON FUNCTION public.auto_checkout_expired_shifts() TO service_role;

CREATE EXTENSION IF NOT EXISTS pg_cron;