tasq/supabase/migrations/20260504010000_restore_server_geofence.sql

-- Restore server-side geofence validation in attendance_check_in and
-- attendance_check_out, with support for both polygon and circle geofences
-- and the IT service request outside-premise override.
--
-- Context: geofence validation was added in 20260316120000 (polygon only)
-- then stripped in 20260317100000 because circle geofences weren't handled,
-- causing false-rejections for offices configured with a radius. This migration
-- restores it with full polygon + circle support using PostGIS geography.
--
-- Security: without server-side validation anyone with a valid session can
-- call the RPC directly with arbitrary coordinates and bypass the geofence.

CREATE OR REPLACE FUNCTION public.attendance_check_in(
  p_duty_id    uuid,
  p_lat        double precision,
  p_lng        double precision,
  p_accuracy   double precision DEFAULT NULL,
  p_is_mocked  boolean          DEFAULT false
) RETURNS uuid
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
  v_schedule     duty_schedules%ROWTYPE;
  v_log_id       uuid;
  v_now          timestamptz := now();
  v_status       text;
  v_geofence     jsonb;
  v_in_premise   boolean := true;
  v_override     boolean := false;
  -- polygon ray-casting
  v_polygon      jsonb;
  v_point_count  int;
  v_i            int;
  v_j            int;
  v_xi           double precision;
  v_yi           double precision;
  v_xj           double precision;
  v_yj           double precision;
  v_inside       boolean := false;
  -- circle
  v_center_lat   double precision;
  v_center_lng   double precision;
  v_radius_m     double precision;
  v_dist_m       double precision;
BEGIN
  SELECT * INTO v_schedule FROM duty_schedules WHERE id = p_duty_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Duty schedule not found';
  END IF;
  IF v_schedule.user_id != auth.uid() THEN
    RAISE EXCEPTION 'Not your duty schedule';
  END IF;

  -- Geofence validation
  SELECT value INTO v_geofence FROM app_settings WHERE key = 'geofence';
  IF v_geofence IS NOT NULL THEN
    v_polygon := COALESCE(v_geofence->'polygon', v_geofence->'points');

    IF v_polygon IS NOT NULL AND jsonb_array_length(v_polygon) > 2 THEN
      -- Polygon: ray-casting point-in-polygon
      v_point_count := jsonb_array_length(v_polygon);
      v_j := v_point_count - 1;
      FOR v_i IN 0..(v_point_count - 1) LOOP
        v_xi := (v_polygon->v_i->>'lng')::double precision;
        v_yi := (v_polygon->v_i->>'lat')::double precision;
        v_xj := (v_polygon->v_j->>'lng')::double precision;
        v_yj := (v_polygon->v_j->>'lat')::double precision;
        IF ((v_yi > p_lat) != (v_yj > p_lat)) AND
           (p_lng < (v_xj - v_xi) * (p_lat - v_yi) / (v_yj - v_yi) + v_xi) THEN
          v_inside := NOT v_inside;
        END IF;
        v_j := v_i;
      END LOOP;
      v_in_premise := v_inside;

    ELSIF (v_geofence->>'lat') IS NOT NULL
      AND (v_geofence->>'lng') IS NOT NULL
      AND (v_geofence->>'radius_m') IS NOT NULL THEN
      -- Circle: PostGIS ST_Distance on geography gives metres directly
      v_center_lat := (v_geofence->>'lat')::double precision;
      v_center_lng := (v_geofence->>'lng')::double precision;
      v_radius_m   := (v_geofence->>'radius_m')::double precision;
      SELECT ST_Distance(
        ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)::geography,
        ST_SetSRID(ST_MakePoint(v_center_lng, v_center_lat), 4326)::geography
      ) INTO v_dist_m;
      v_in_premise := (v_dist_m <= v_radius_m);
    END IF;
  END IF;

  -- IT service request outside-premise override
  IF NOT v_in_premise THEN
    SELECT EXISTS (
      SELECT 1
      FROM it_service_request_assignments a
      JOIN it_service_requests r ON r.id = a.request_id
      WHERE a.user_id = auth.uid()
        AND r.outside_premise_allowed
        AND r.status IN ('scheduled', 'in_progress_dry_run', 'in_progress')
    ) INTO v_override;
    IF NOT v_override THEN
      RAISE EXCEPTION 'Outside geofence';
    END IF;
  END IF;

  -- Time window check
  IF v_now < (v_schedule.start_time - interval '2 hours') THEN
    RAISE EXCEPTION 'Too early to check in (2-hour window)';
  END IF;
  IF v_now > v_schedule.end_time THEN
    RAISE EXCEPTION 'Duty has already ended';
  END IF;

  IF v_now <= v_schedule.start_time THEN
    v_status := 'arrival';
  ELSE
    v_status := 'late';
  END IF;

  INSERT INTO attendance_logs (
    user_id, duty_schedule_id, check_in_at, check_in_lat, check_in_lng,
    check_in_accuracy, check_in_is_mocked
  )
  VALUES (
    auth.uid(), p_duty_id, v_now, p_lat, p_lng,
    p_accuracy, p_is_mocked
  )
  RETURNING id INTO v_log_id;

  UPDATE duty_schedules
  SET check_in_at       = COALESCE(check_in_at, v_now),
      check_in_location = ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)::geography,
      status            = CASE
                            WHEN check_in_at IS NULL THEN v_status::duty_status
                            ELSE status
                          END
  WHERE id = p_duty_id;

  RETURN v_log_id;
END;
$$;

CREATE OR REPLACE FUNCTION public.attendance_check_out(
  p_attendance_id uuid,
  p_lat           double precision,
  p_lng           double precision,
  p_justification text             DEFAULT NULL,
  p_accuracy      double precision DEFAULT NULL,
  p_is_mocked     boolean          DEFAULT false
) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER
AS $$
DECLARE
  v_log          attendance_logs%ROWTYPE;
  v_geofence     jsonb;
  v_in_premise   boolean := true;
  v_override     boolean := false;
  v_polygon      jsonb;
  v_point_count  int;
  v_i            int;
  v_j            int;
  v_xi           double precision;
  v_yi           double precision;
  v_xj           double precision;
  v_yj           double precision;
  v_inside       boolean := false;
  v_center_lat   double precision;
  v_center_lng   double precision;
  v_radius_m     double precision;
  v_dist_m       double precision;
BEGIN
  SELECT * INTO v_log FROM attendance_logs WHERE id = p_attendance_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Attendance log not found';
  END IF;
  IF v_log.user_id != auth.uid() THEN
    RAISE EXCEPTION 'Not your attendance log';
  END IF;
  IF v_log.check_out_at IS NOT NULL THEN
    RAISE EXCEPTION 'Already checked out';
  END IF;

  -- Geofence validation on checkout (same logic as check-in)
  SELECT value INTO v_geofence FROM app_settings WHERE key = 'geofence';
  IF v_geofence IS NOT NULL THEN
    v_polygon := COALESCE(v_geofence->'polygon', v_geofence->'points');

    IF v_polygon IS NOT NULL AND jsonb_array_length(v_polygon) > 2 THEN
      v_point_count := jsonb_array_length(v_polygon);
      v_j := v_point_count - 1;
      FOR v_i IN 0..(v_point_count - 1) LOOP
        v_xi := (v_polygon->v_i->>'lng')::double precision;
        v_yi := (v_polygon->v_i->>'lat')::double precision;
        v_xj := (v_polygon->v_j->>'lng')::double precision;
        v_yj := (v_polygon->v_j->>'lat')::double precision;
        IF ((v_yi > p_lat) != (v_yj > p_lat)) AND
           (p_lng < (v_xj - v_xi) * (p_lat - v_yi) / (v_yj - v_yi) + v_xi) THEN
          v_inside := NOT v_inside;
        END IF;
        v_j := v_i;
      END LOOP;
      v_in_premise := v_inside;

    ELSIF (v_geofence->>'lat') IS NOT NULL
      AND (v_geofence->>'lng') IS NOT NULL
      AND (v_geofence->>'radius_m') IS NOT NULL THEN
      v_center_lat := (v_geofence->>'lat')::double precision;
      v_center_lng := (v_geofence->>'lng')::double precision;
      v_radius_m   := (v_geofence->>'radius_m')::double precision;
      SELECT ST_Distance(
        ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)::geography,
        ST_SetSRID(ST_MakePoint(v_center_lng, v_center_lat), 4326)::geography
      ) INTO v_dist_m;
      v_in_premise := (v_dist_m <= v_radius_m);
    END IF;
  END IF;

  IF NOT v_in_premise THEN
    SELECT EXISTS (
      SELECT 1
      FROM it_service_request_assignments a
      JOIN it_service_requests r ON r.id = a.request_id
      WHERE a.user_id = auth.uid()
        AND r.outside_premise_allowed
        AND r.status IN ('scheduled', 'in_progress_dry_run', 'in_progress')
    ) INTO v_override;
    IF NOT v_override THEN
      RAISE EXCEPTION 'Outside geofence';
    END IF;
  END IF;

  UPDATE attendance_logs
  SET check_out_at        = now(),
      check_out_lat       = p_lat,
      check_out_lng       = p_lng,
      justification       = COALESCE(p_justification, justification),
      check_out_accuracy  = p_accuracy,
      check_out_is_mocked = p_is_mocked
  WHERE id = p_attendance_id;
END;
$$;