DO $$ BEGIN
  CREATE TYPE public.visit_status AS ENUM ('ok','issue','critical');
EXCEPTION WHEN duplicate_object THEN NULL; END $$;

ALTER TABLE public.duty_posts
  ADD COLUMN IF NOT EXISTS supervisor_id UUID REFERENCES auth.users(id) ON DELETE SET NULL;

CREATE TABLE public.supervisor_visits (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  duty_post_id UUID NOT NULL REFERENCES public.duty_posts(id) ON DELETE CASCADE,
  supervisor_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
  visited_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  status public.visit_status NOT NULL DEFAULT 'ok',
  rating INTEGER,
  notes TEXT,
  latitude NUMERIC,
  longitude NUMERIC,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT supervisor_visits_rating_range CHECK (rating IS NULL OR (rating BETWEEN 1 AND 5))
);

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

ALTER TABLE public.supervisor_visits ENABLE ROW LEVEL SECURITY;

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

CREATE POLICY "Supervisors/admins can create visits"
  ON public.supervisor_visits FOR INSERT TO authenticated
  WITH CHECK (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','supervisor']::app_role[]));

CREATE POLICY "Supervisors/admins can update visits"
  ON public.supervisor_visits FOR UPDATE TO authenticated
  USING (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','supervisor']::app_role[]))
  WITH CHECK (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','supervisor']::app_role[]));

CREATE POLICY "Admins can delete visits"
  ON public.supervisor_visits FOR DELETE TO authenticated
  USING (public.has_any_role(auth.uid(), ARRAY['super_admin','admin']::app_role[]));

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

CREATE INDEX supervisor_visits_post_idx ON public.supervisor_visits(duty_post_id);
CREATE INDEX supervisor_visits_supervisor_idx ON public.supervisor_visits(supervisor_id);
CREATE INDEX supervisor_visits_time_idx ON public.supervisor_visits(visited_at DESC);