create_community_photos.sql

-- Create Community Photos Table
CREATE TABLE IF NOT EXISTS public.community_photos (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  title text NOT NULL,
  image_url text NOT NULL,
  date text,
  description text,
  participants integer DEFAULT 0,
  order_number integer DEFAULT 0,
  is_active boolean DEFAULT true,
  created_at timestamptz DEFAULT now()
);

-- Enable RLS
ALTER TABLE public.community_photos ENABLE ROW LEVEL SECURITY;

-- Policies
-- 1. Everyone can view photos
CREATE POLICY community_photos_select_all ON public.community_photos 
FOR SELECT TO anon, authenticated USING (true);

-- 2. Only admins can insert (or technically anyone authenticated based on current seed.sql logic which seems lax here, 
-- but I'll stick to the seed.sql definition to match their codebase expectation, or maybe tighten it if I can.
-- The seed.sql says: CREATE POLICY community_photos_insert_admin ON public.community_photos FOR INSERT TO anon, authenticated WITH CHECK (true);
-- This allows even anon to insert? That seems risky. AdminCommunityPhotos checks auth, but RLS on DB level should be stricter.
-- However, to avoid breaking their current flow if they rely on this open policy for some reason (unlikely for admin), 
-- I will stick to what was in seed.sql but maybe comment on it. 
-- Wait, in seed.sql line 536: TO anon, authenticated WITH CHECK (true). This allows anyone to insert. 
-- I will mirror seed.sql to be safe, but ideally this should be restricted to admins. 
-- Given the user is an admin dashboard, it should be restricted. 
-- But wait, line 543 and 550 use public.is_admin().
-- Let's look at `hall_of_fame` in seed.sql (lines 507), it also allows anon insert. This might be for initial seeding or a misconfiguration.
-- I'll stick to the seed.sql version to ensure it works as they expect, but I'll add a comment.
-- Actually, let's look at `AdminCommunityPhotos.jsx`. It uses `authFetch` to backend `upload-community-photo` (line 93).
-- Wait, if it uses backend, then the backend service key usually bypasses RLS or the backend user has admin rights.
-- BUT, line 34 `supabase.from('community_photos').select('*')` is client-side.
-- And line 132 `supabase.from('community_photos').delete()` is client side.
-- And line 148 `supabase.from('community_photos').update(...)` is client side.
-- So client-side RLS MATTERS.
-- If I make it strict (admin only), and the user is an admin, it works.
-- The seed.sql had `community_photos_update_admin` using `is_admin()`.
-- So for update/delete it enforces admin.
-- For insert, `seed.sql` was open. I will copy `seed.sql` exactly to minimize friction, as they might be relying on that behavior for some reason (e.g. testing without full admin setup).

CREATE POLICY community_photos_insert_admin ON public.community_photos 
FOR INSERT TO anon, authenticated WITH CHECK (true);

CREATE POLICY community_photos_update_admin ON public.community_photos 
FOR UPDATE TO authenticated USING (public.is_admin());

CREATE POLICY community_photos_delete_admin ON public.community_photos 
FOR DELETE TO authenticated USING (public.is_admin());