CREATE TABLE public.duty_rosters (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  guard_id UUID NOT NULL REFERENCES public.security_guards(id) ON DELETE CASCADE,
  shift_date DATE NOT NULL,
  shift_start TIME NOT NULL,
  shift_end TIME NOT NULL,
  location TEXT,
  notes TEXT,
  status TEXT NOT NULL DEFAULT 'scheduled',
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

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

ALTER TABLE public.duty_rosters ENABLE ROW LEVEL SECURITY;

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

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

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

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

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

CREATE INDEX idx_duty_rosters_date ON public.duty_rosters(shift_date);
CREATE INDEX idx_duty_rosters_guard ON public.duty_rosters(guard_id);