107 lines
3.4 KiB
PL/PgSQL
107 lines
3.4 KiB
PL/PgSQL
-- Add location quality metadata columns to attendance_logs.
|
|
-- These are populated by the extended RPCs and used for admin audit of
|
|
-- suspicious check-ins (mock location, low accuracy).
|
|
|
|
ALTER TABLE attendance_logs
|
|
ADD COLUMN IF NOT EXISTS check_in_accuracy double precision,
|
|
ADD COLUMN IF NOT EXISTS check_in_is_mocked boolean NOT NULL DEFAULT false,
|
|
ADD COLUMN IF NOT EXISTS check_out_accuracy double precision,
|
|
ADD COLUMN IF NOT EXISTS check_out_is_mocked boolean NOT NULL DEFAULT false;
|
|
|
|
-- Extend attendance_check_in to accept and store location metadata.
|
|
-- New params have defaults so existing callers without them continue to work.
|
|
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;
|
|
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;
|
|
|
|
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;
|
|
$$;
|
|
|
|
-- Extend attendance_check_out to accept and store location metadata.
|
|
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;
|
|
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;
|
|
|
|
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;
|
|
$$;
|