CREATE OR REPLACE FUNCTION public.add_city(_name text)
RETURNS uuid LANGUAGE plpgsql SECURITY DEFINER SET search_path = public AS $$
DECLARE _clean text := initcap(btrim(_name)); _cid uuid; _rid uuid; _id uuid;
BEGIN
  IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Not signed in'; END IF;
  IF _clean IS NULL OR length(_clean) < 2 OR length(_clean) > 80 THEN RAISE EXCEPTION 'Invalid city name'; END IF;
  SELECT id INTO _id FROM public.cities WHERE lower(name) = lower(_clean) LIMIT 1;
  IF _id IS NOT NULL THEN RETURN _id; END IF;
  SELECT id INTO _cid FROM public.countries WHERE code = 'AF' LIMIT 1;
  IF _cid IS NULL THEN INSERT INTO public.countries(name, code) VALUES ('Africa','AF') RETURNING id INTO _cid; END IF;
  SELECT id INTO _rid FROM public.regions WHERE country_id = _cid LIMIT 1;
  IF _rid IS NULL THEN INSERT INTO public.regions(country_id, name) VALUES (_cid,'Africa') RETURNING id INTO _rid; END IF;
  INSERT INTO public.cities(name, country_id, region_id) VALUES (_clean, _cid, _rid) RETURNING id INTO _id;
  RETURN _id;
END; $$;
REVOKE EXECUTE ON FUNCTION public.add_city(text) FROM anon, public;
GRANT EXECUTE ON FUNCTION public.add_city(text) TO authenticated;

CREATE OR REPLACE FUNCTION public.cities_with_events()
RETURNS TABLE(id uuid, name text, timezone text, country_id uuid)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public AS $$
  SELECT c.id, c.name, c.timezone, c.country_id FROM public.cities c
  WHERE EXISTS (SELECT 1 FROM public.events e WHERE e.city_id = c.id AND e.status = 'approved'
    AND coalesce(e.ends_at, e.starts_at) >= now())
  ORDER BY c.name;
$$;
GRANT EXECUTE ON FUNCTION public.cities_with_events() TO anon, authenticated;

CREATE POLICY "event covers read" ON storage.objects FOR SELECT TO authenticated USING (bucket_id = 'event-covers');
CREATE POLICY "event covers user upload" ON storage.objects FOR INSERT TO authenticated
  WITH CHECK (bucket_id = 'event-covers' AND (storage.foldername(name))[1] = auth.uid()::text);