-- Motorcycles
CREATE TABLE public.motorcycles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  registration_number TEXT NOT NULL UNIQUE,
  model TEXT,
  assigned_guard_id UUID REFERENCES public.security_guards(id) ON DELETE SET NULL,
  status TEXT NOT NULL DEFAULT 'available',
  odometer_km NUMERIC NOT NULL DEFAULT 0,
  notes TEXT,
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.motorcycles TO authenticated;
GRANT ALL ON public.motorcycles TO service_role;
ALTER TABLE public.motorcycles ENABLE ROW LEVEL SECURITY;
CREATE POLICY "auth read motorcycles" ON public.motorcycles FOR SELECT TO authenticated USING (true);
CREATE POLICY "sup manage motorcycles" ON public.motorcycles FOR INSERT TO authenticated
  WITH CHECK (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager','supervisor','security_officer']::app_role[]));
CREATE POLICY "sup update motorcycles" ON public.motorcycles 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 POLICY "admin delete motorcycles" ON public.motorcycles FOR DELETE TO authenticated
  USING (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin']::app_role[]));
CREATE TRIGGER motorcycles_updated BEFORE UPDATE ON public.motorcycles FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- Patrol routes
CREATE TABLE public.patrol_routes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  description TEXT,
  checkpoints JSONB NOT NULL DEFAULT '[]'::jsonb,
  estimated_duration_minutes INTEGER,
  active BOOLEAN NOT NULL DEFAULT true,
  created_by UUID,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.patrol_routes TO authenticated;
GRANT ALL ON public.patrol_routes TO service_role;
ALTER TABLE public.patrol_routes ENABLE ROW LEVEL SECURITY;
CREATE POLICY "auth read routes" ON public.patrol_routes FOR SELECT TO authenticated USING (true);
CREATE POLICY "sup insert routes" ON public.patrol_routes FOR INSERT TO authenticated
  WITH CHECK (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager','supervisor','security_officer']::app_role[]));
CREATE POLICY "sup update routes" ON public.patrol_routes 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 POLICY "admin delete routes" ON public.patrol_routes FOR DELETE TO authenticated
  USING (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin']::app_role[]));
CREATE TRIGGER patrol_routes_updated BEFORE UPDATE ON public.patrol_routes FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

-- Patrol logs
CREATE TABLE public.patrol_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  motorcycle_id UUID NOT NULL REFERENCES public.motorcycles(id) ON DELETE CASCADE,
  route_id UUID REFERENCES public.patrol_routes(id) ON DELETE SET NULL,
  guard_id UUID REFERENCES public.security_guards(id) ON DELETE SET NULL,
  created_by UUID NOT NULL,
  started_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  ended_at TIMESTAMPTZ,
  start_odometer NUMERIC,
  end_odometer NUMERIC,
  distance_km NUMERIC,
  fuel_liters NUMERIC,
  status TEXT NOT NULL DEFAULT 'in_progress',
  incidents TEXT,
  notes TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
GRANT SELECT, INSERT, UPDATE, DELETE ON public.patrol_logs TO authenticated;
GRANT ALL ON public.patrol_logs TO service_role;
ALTER TABLE public.patrol_logs ENABLE ROW LEVEL SECURITY;
CREATE POLICY "read patrol logs" ON public.patrol_logs FOR SELECT TO authenticated
  USING (created_by = auth.uid() OR public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager','supervisor','security_officer']::app_role[]));
CREATE POLICY "insert patrol logs" ON public.patrol_logs FOR INSERT TO authenticated
  WITH CHECK (created_by = auth.uid());
CREATE POLICY "update patrol logs" ON public.patrol_logs FOR UPDATE TO authenticated
  USING (created_by = auth.uid() OR public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin','ops_manager','supervisor','security_officer']::app_role[]));
CREATE POLICY "admin delete patrol logs" ON public.patrol_logs FOR DELETE TO authenticated
  USING (public.has_any_role(auth.uid(), ARRAY['super_admin','admin','company_admin']::app_role[]));
CREATE TRIGGER patrol_logs_updated BEFORE UPDATE ON public.patrol_logs FOR EACH ROW EXECUTE FUNCTION public.tg_set_updated_at();

CREATE INDEX idx_patrol_logs_motorcycle ON public.patrol_logs(motorcycle_id);
CREATE INDEX idx_patrol_logs_guard ON public.patrol_logs(guard_id);
CREATE INDEX idx_patrol_logs_started ON public.patrol_logs(started_at DESC);