87 lines
3.5 KiB
PL/PgSQL
87 lines
3.5 KiB
PL/PgSQL
-- Trigger to replicate attendance_check_in RPC side-effects when a new
|
|
-- attendance_log row is inserted directly (e.g. from Brick's offline queue
|
|
-- after sync). The RPC path is unchanged; this trigger only fires on plain
|
|
-- INSERT and is a no-op when the RPC already handled the duty_schedule update
|
|
-- (idempotent: ON CONFLICT (id) DO NOTHING is handled by Brick).
|
|
--
|
|
-- Side-effects replicated from attendance_check_in RPC:
|
|
-- 1. duty_schedules.check_in_at ← attendance_log.check_in_at
|
|
-- 2. duty_schedules.check_in_location ← lat/lng
|
|
-- 3. duty_schedules.status ← 'arrival' if on time, 'late' if past start
|
|
--
|
|
-- For check-out, the trigger replicates attendance_check_out side-effects:
|
|
-- 1. duty_schedules.check_out_at ← attendance_log.check_out_at
|
|
-- 2. duty_schedules.check_out_location ← lat/lng
|
|
-- 3. duty_schedules.status ← 'completed'
|
|
|
|
CREATE OR REPLACE FUNCTION public.attendance_log_after_upsert()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
DECLARE
|
|
v_schedule duty_schedules%ROWTYPE;
|
|
BEGIN
|
|
-- Skip if we have no duty_schedule_id (shouldn't happen, but be defensive)
|
|
IF NEW.duty_schedule_id IS NULL THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
SELECT * INTO v_schedule FROM duty_schedules WHERE id = NEW.duty_schedule_id;
|
|
IF NOT FOUND THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
-- ── Check-in side-effects ────────────────────────────────────────────────
|
|
-- Only apply when check_in_at changed or this is a new row.
|
|
IF (TG_OP = 'INSERT') OR
|
|
(TG_OP = 'UPDATE' AND
|
|
(OLD.check_in_at IS DISTINCT FROM NEW.check_in_at OR
|
|
OLD.check_in_lat IS DISTINCT FROM NEW.check_in_lat)) THEN
|
|
|
|
-- Skip if duty_schedules already has a check_in_at (RPC already ran)
|
|
IF v_schedule.check_in_at IS NULL THEN
|
|
UPDATE duty_schedules
|
|
SET
|
|
check_in_at = NEW.check_in_at,
|
|
check_in_location = jsonb_build_object(
|
|
'latitude', NEW.check_in_lat,
|
|
'longitude', NEW.check_in_lng
|
|
),
|
|
status = CASE
|
|
WHEN NEW.check_in_at <= v_schedule.start_time
|
|
THEN 'arrival'
|
|
ELSE 'late'
|
|
END
|
|
WHERE id = NEW.duty_schedule_id;
|
|
END IF;
|
|
END IF;
|
|
|
|
-- ── Check-out side-effects ───────────────────────────────────────────────
|
|
IF NEW.check_out_at IS NOT NULL AND
|
|
(TG_OP = 'INSERT' OR OLD.check_out_at IS DISTINCT FROM NEW.check_out_at) THEN
|
|
|
|
UPDATE duty_schedules
|
|
SET
|
|
check_out_at = NEW.check_out_at,
|
|
check_out_location = jsonb_build_object(
|
|
'latitude', NEW.check_out_lat,
|
|
'longitude', NEW.check_out_lng
|
|
),
|
|
status = 'completed'
|
|
WHERE id = NEW.duty_schedule_id
|
|
AND check_out_at IS NULL; -- idempotent: only if not already checked out
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
-- Fire AFTER INSERT and AFTER UPDATE so both offline-queued inserts and
|
|
-- subsequent Brick upserts (when check_out_at is filled in) are handled.
|
|
DROP TRIGGER IF EXISTS attendance_logs_after_upsert ON attendance_logs;
|
|
CREATE TRIGGER attendance_logs_after_upsert
|
|
AFTER INSERT OR UPDATE ON attendance_logs
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.attendance_log_after_upsert();
|