CREATE TABLE public.sos_alerts (
  id UUID NOT NULL DEFAULT gen_random_uuid() PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  lat DOUBLE PRECISION,
  lng DOUBLE PRECISION,
  accuracy DOUBLE PRECISION,
  message TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  resolved_at TIMESTAMPTZ,
  resolved_by UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

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

ALTER TABLE public.sos_alerts ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users insert their own SOS" ON public.sos_alerts
  FOR INSERT TO authenticated WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users view their own SOS" ON public.sos_alerts
  FOR SELECT TO authenticated USING (auth.uid() = user_id);

CREATE POLICY "Supervisors view all SOS" ON public.sos_alerts
  FOR SELECT TO authenticated USING (
    public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager','supervisor','security_officer']::app_role[])
  );

CREATE POLICY "Supervisors update SOS" ON public.sos_alerts
  FOR UPDATE TO authenticated USING (
    public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager','supervisor','security_officer']::app_role[])
  );

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

ALTER PUBLICATION supabase_realtime ADD TABLE public.sos_alerts;
ALTER TABLE public.sos_alerts REPLICA IDENTITY FULL;