CREATE TABLE public.duty_posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  client_name TEXT,
  address TEXT,
  latitude DOUBLE PRECISION,
  longitude DOUBLE PRECISION,
  geofence_radius_m INTEGER NOT NULL DEFAULT 100,
  contact_person TEXT,
  contact_phone TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  notes TEXT,
  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.duty_posts TO authenticated;
GRANT ALL ON public.duty_posts TO service_role;

ALTER TABLE public.duty_posts ENABLE ROW LEVEL SECURITY;

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

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

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

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

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