CREATE TYPE public.attendance_method AS ENUM ('qr', 'gps', 'manual');
CREATE TYPE public.attendance_status AS ENUM ('checked_in', 'checked_out', 'flagged');

CREATE TABLE public.attendance (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  guard_id UUID NOT NULL REFERENCES public.security_guards(id) ON DELETE CASCADE,
  check_in_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  check_out_at TIMESTAMPTZ,
  check_in_lat DOUBLE PRECISION,
  check_in_lng DOUBLE PRECISION,
  check_out_lat DOUBLE PRECISION,
  check_out_lng DOUBLE PRECISION,
  method public.attendance_method NOT NULL DEFAULT 'gps',
  qr_code TEXT,
  location TEXT,
  notes TEXT,
  status public.attendance_status NOT NULL DEFAULT 'checked_in',
  created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.attendance TO authenticated;
GRANT ALL ON public.attendance TO service_role;

ALTER TABLE public.attendance ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Authenticated can view attendance"
  ON public.attendance FOR SELECT TO authenticated USING (true);

CREATE POLICY "Staff can insert attendance"
  ON public.attendance FOR INSERT TO authenticated
  WITH CHECK (public.has_any_role(auth.uid(), ARRAY['guard','supervisor','ops_manager','company_admin','super_admin']::public.app_role[]));

CREATE POLICY "Managers can update attendance"
  ON public.attendance FOR UPDATE TO authenticated
  USING (public.has_any_role(auth.uid(), ARRAY['supervisor','ops_manager','company_admin','super_admin']::public.app_role[]));

CREATE POLICY "Managers can delete attendance"
  ON public.attendance FOR DELETE TO authenticated
  USING (public.has_any_role(auth.uid(), ARRAY['supervisor','ops_manager','company_admin','super_admin']::public.app_role[]));

CREATE INDEX idx_attendance_guard ON public.attendance(guard_id);
CREATE INDEX idx_attendance_check_in_at ON public.attendance(check_in_at DESC);

CREATE TRIGGER set_attendance_updated_at
  BEFORE UPDATE ON public.attendance
  FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();