exposedgays/supabase/migration_v6_hosting_events.sql

-- ExposedGays v6: Sniffies-style user hosting events (map parties)
-- Run on SUPABASE_01 after migration_v5_privacy.sql

CREATE TABLE IF NOT EXISTS public.hosting_events (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  host_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  title TEXT NOT NULL CHECK (char_length(title) <= 80),
  description TEXT NOT NULL DEFAULT '' CHECK (char_length(description) <= 2000),
  event_type TEXT NOT NULL DEFAULT 'custom'
    CHECK (event_type IN ('cum-dump', 'orgy', 'group', 'pnp', 'vanilla', 'gloryhole', 'custom')),
  status TEXT NOT NULL DEFAULT 'scheduled'
    CHECK (status IN ('scheduled', 'live', 'ended', 'cancelled')),
  starts_at TIMESTAMPTZ NOT NULL,
  ends_at TIMESTAMPTZ NOT NULL,
  location GEOGRAPHY(POINT, 4326) NOT NULL,
  city TEXT,
  auto_accept BOOLEAN NOT NULL DEFAULT false,
  max_guests INTEGER CHECK (max_guests IS NULL OR max_guests BETWEEN 1 AND 500),
  tags TEXT[] NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK (ends_at > starts_at)
);

CREATE INDEX IF NOT EXISTS idx_hosting_events_host ON public.hosting_events (host_id);
CREATE INDEX IF NOT EXISTS idx_hosting_events_status ON public.hosting_events (status);
CREATE INDEX IF NOT EXISTS idx_hosting_events_starts ON public.hosting_events (starts_at);
CREATE INDEX IF NOT EXISTS idx_hosting_events_location ON public.hosting_events USING GIST (location);

CREATE TABLE IF NOT EXISTS public.hosting_event_media (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES public.hosting_events(id) ON DELETE CASCADE,
  media_type TEXT NOT NULL CHECK (media_type IN ('photo', 'video')),
  url TEXT NOT NULL,
  thumbnail_url TEXT,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_hosting_event_media_event ON public.hosting_event_media (event_id);

CREATE TABLE IF NOT EXISTS public.hosting_event_attendees (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_id UUID NOT NULL REFERENCES public.hosting_events(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  status TEXT NOT NULL DEFAULT 'requested'
    CHECK (status IN ('invited', 'requested', 'accepted', 'declined', 'cancelled')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (event_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_hosting_event_attendees_event ON public.hosting_event_attendees (event_id);
CREATE INDEX IF NOT EXISTS idx_hosting_event_attendees_user ON public.hosting_event_attendees (user_id);

-- Max 2 photos + 1 video per event
CREATE OR REPLACE FUNCTION public.enforce_hosting_event_media_limit()
RETURNS TRIGGER AS $$
DECLARE
  photo_count INTEGER;
  video_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO photo_count
  FROM public.hosting_event_media
  WHERE event_id = NEW.event_id AND media_type = 'photo';

  SELECT COUNT(*) INTO video_count
  FROM public.hosting_event_media
  WHERE event_id = NEW.event_id AND media_type = 'video';

  IF NEW.media_type = 'photo' AND photo_count >= 2 THEN
    RAISE EXCEPTION 'Maximum 2 photos per hosting event';
  END IF;

  IF NEW.media_type = 'video' AND video_count >= 1 THEN
    RAISE EXCEPTION 'Maximum 1 video per hosting event';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_hosting_event_media_limit ON public.hosting_event_media;
CREATE TRIGGER trg_hosting_event_media_limit
  BEFORE INSERT ON public.hosting_event_media
  FOR EACH ROW EXECUTE FUNCTION public.enforce_hosting_event_media_limit();

CREATE OR REPLACE FUNCTION public.touch_hosting_event_updated()
RETURNS TRIGGER AS $$
BEGIN
  UPDATE public.hosting_events SET updated_at = now() WHERE id = NEW.event_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_hosting_event_attendee_touch ON public.hosting_event_attendees;
CREATE TRIGGER trg_hosting_event_attendee_touch
  AFTER INSERT OR UPDATE ON public.hosting_event_attendees
  FOR EACH ROW EXECUTE FUNCTION public.touch_hosting_event_updated();

ALTER TABLE public.hosting_events ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.hosting_event_media ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.hosting_event_attendees ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Hosting events viewable by authenticated users"
  ON public.hosting_events FOR SELECT
  USING (auth.role() = 'authenticated' AND status != 'cancelled');

CREATE POLICY "Users can create hosting events"
  ON public.hosting_events FOR INSERT
  WITH CHECK (auth.uid() = host_id);

CREATE POLICY "Hosts can update own events"
  ON public.hosting_events FOR UPDATE
  USING (auth.uid() = host_id);

CREATE POLICY "Hosts can delete own events"
  ON public.hosting_events FOR DELETE
  USING (auth.uid() = host_id);

CREATE POLICY "Event media viewable by authenticated users"
  ON public.hosting_event_media FOR SELECT
  USING (auth.role() = 'authenticated');

CREATE POLICY "Hosts manage event media"
  ON public.hosting_event_media FOR ALL
  USING (
    EXISTS (
      SELECT 1 FROM public.hosting_events e
      WHERE e.id = event_id AND e.host_id = auth.uid()
    )
  );

CREATE POLICY "Attendees viewable by authenticated users"
  ON public.hosting_event_attendees FOR SELECT
  USING (auth.role() = 'authenticated');

CREATE POLICY "Users manage own attendance"
  ON public.hosting_event_attendees FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users update own attendance"
  ON public.hosting_event_attendees FOR UPDATE
  USING (auth.uid() = user_id OR EXISTS (
    SELECT 1 FROM public.hosting_events e
    WHERE e.id = event_id AND e.host_id = auth.uid()
  ));

CREATE POLICY "Hosts delete attendees"
  ON public.hosting_event_attendees FOR DELETE
  USING (
    auth.uid() = user_id OR EXISTS (
      SELECT 1 FROM public.hosting_events e
      WHERE e.id = event_id AND e.host_id = auth.uid()
    )
  );

ALTER PUBLICATION supabase_realtime ADD TABLE public.hosting_events;
ALTER PUBLICATION supabase_realtime ADD TABLE public.hosting_event_attendees;

INSERT INTO storage.buckets (id, name, public)
VALUES ('hosting-events', 'hosting-events', true)
ON CONFLICT (id) DO NOTHING;