create_dynamic_tables.sql

-- 1. EVENTS TABLE
create table if not exists public.events (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  date text not null, -- Keeping as text for flexibility (e.g. "5 October") or use date type
  time text,
  description text,
  speaker text,
  banner_url text,
  status text check (status in ('upcoming', 'past')) default 'upcoming',
  created_at timestamptz default now()
);
alter table public.events enable row level security;

-- 2. PROJECTS TABLE
create table if not exists public.projects (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  description text,
  screenshot_url text,
  github_url text,
  live_url text,
  tech_stack text[], -- Array of strings
  student_name text,
  is_featured boolean default false,
  created_at timestamptz default now()
);
alter table public.projects enable row level security;

-- 3. TEAM MEMBERS TABLE
create table if not exists public.team_members (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  role text not null,
  photo_url text,
  contribution_score integer default 0,
  category text check (category in ('Initiator', 'Family')) default 'Family',
  created_at timestamptz default now()
);
alter table public.team_members enable row level security;


-- POLICIES (RLS)

-- Public Access (Read Only)
create policy "Events are viewable by everyone" on events for select using (true);
create policy "Projects are viewable by everyone" on projects for select using (true);
create policy "Team members are viewable by everyone" on team_members for select using (true);

-- Admin Access (Full Control)
-- WARNING: These policies assume you have a 'roles' table setup from previous steps with role='admin'

-- Events
create policy "Admins can insert events" on events for insert with check (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);
create policy "Admins can update events" on events for update using (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);
create policy "Admins can delete events" on events for delete using (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);

-- Projects
create policy "Admins can insert projects" on projects for insert with check (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);
create policy "Admins can update projects" on projects for update using (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);
create policy "Admins can delete projects" on projects for delete using (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);

-- Team Members
create policy "Admins can insert team_members" on team_members for insert with check (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);
create policy "Admins can update team_members" on team_members for update using (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);
create policy "Admins can delete team_members" on team_members for delete using (
  exists (select 1 from roles where user_id = auth.uid() and role = 'admin')
);


-- STORAGE BUCKET POLICIES (Run this if you can't create policies in UI)
-- Note: It's often easier to set storage policies in the Supabase Dashboard > Storage > Policies
-- But here is the SQL equivalent if the "images" bucket exists.

-- Allow public access to images
-- create policy "Public Access" on storage.objects for select using ( bucket_id = 'images' );

-- Allow authenticated users to upload images (for profile photos)
-- create policy "Authenticated users can upload" on storage.objects for insert with check ( bucket_id = 'images' and auth.role() = 'authenticated' );