-- ExposedGays v3: 20 photos, videos, profile media order, album visibility
-- Run on SUPABASE_01 after migration_v2.sql

-- Photo limit 20
CREATE OR REPLACE FUNCTION public.enforce_gallery_photo_limit()
RETURNS TRIGGER AS $$
BEGIN
  IF (SELECT COUNT(*) FROM public.gallery_photos WHERE user_id = NEW.user_id) >= 20 THEN
    RAISE EXCEPTION 'Maximum 20 photos per user';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Album visibility + sort
ALTER TABLE public.gallery_albums
  ADD COLUMN IF NOT EXISTS is_public BOOLEAN NOT NULL DEFAULT true,
  ADD COLUMN IF NOT EXISTS sort_order INTEGER NOT NULL DEFAULT 0;

-- Photo profile curation
ALTER TABLE public.gallery_photos
  ADD COLUMN IF NOT EXISTS show_on_profile BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN IF NOT EXISTS profile_sort_order INTEGER NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS sort_order INTEGER NOT NULL DEFAULT 0;

-- Videos (max 5 per user, 30s)
CREATE TABLE IF NOT EXISTS public.gallery_videos (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  album_id UUID REFERENCES public.gallery_albums(id) ON DELETE SET NULL,
  url TEXT NOT NULL,
  thumbnail_url TEXT,
  duration_sec NUMERIC(6,2),
  is_explicit BOOLEAN NOT NULL DEFAULT true,
  show_on_profile BOOLEAN NOT NULL DEFAULT false,
  profile_sort_order INTEGER NOT NULL DEFAULT 0,
  sort_order INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_gallery_videos_user ON public.gallery_videos (user_id);

CREATE OR REPLACE FUNCTION public.enforce_gallery_video_limit()
RETURNS TRIGGER AS $$
BEGIN
  IF (SELECT COUNT(*) FROM public.gallery_videos WHERE user_id = NEW.user_id) >= 5 THEN
    RAISE EXCEPTION 'Maximum 5 videos per user';
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_gallery_video_limit ON public.gallery_videos;
CREATE TRIGGER trg_gallery_video_limit
  BEFORE INSERT ON public.gallery_videos
  FOR EACH ROW EXECUTE FUNCTION public.enforce_gallery_video_limit();

ALTER TABLE public.gallery_videos ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Videos viewable when album public or owner"
  ON public.gallery_videos FOR SELECT USING (
    auth.uid() = user_id
    OR album_id IS NULL
    OR EXISTS (
      SELECT 1 FROM public.gallery_albums a
      WHERE a.id = album_id AND a.is_public = true
    )
  );

CREATE POLICY "Users manage own videos"
  ON public.gallery_videos FOR ALL USING (auth.uid() = user_id);