schema.sql

-- Create Profiles table (linked to Auth)
create table profiles (
  id uuid references auth.users not null primary key,
  email text,
  full_name text,
  avatar_url text,
  role text check (role in ('seeker', 'community', 'venue')) default 'seeker',
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Create Venues table
create table venues (
  id bigint generated by default as identity primary key,
  name text not null,
  location text not null,
  sports text,
  description text,
  image_url text,
  owner_id uuid references auth.users,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null
);

-- Create Events table
create table events (
  id bigint generated by default as identity primary key,
  title text not null,
  sport text not null,
  date text not null, -- Storing as text for MVP simplicity
  location text not null,
  price text,
  level text,
  participants_count int default 1,
  max_participants int default 4,
  image_url text,
  creator_id uuid references auth.users,
  venue_id bigint references venues(id),
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  is_featured boolean default false
);

-- Create Participants table (Many-to-Many)
create table participants (
  id bigint generated by default as identity primary key,
  event_id bigint references events(id) not null,
  user_id uuid references auth.users not null,
  joined_at timestamp with time zone default timezone('utc'::text, now()) not null,
  unique(event_id, user_id)
);

-- Enable Row Level Security (RLS)
alter table profiles enable row level security;
alter table venues enable row level security;
alter table events enable row level security;
alter table participants enable row level security;

-- Create Policies (Open for MVP)
create policy "Public profiles are viewable by everyone." on profiles for select using (true);
create policy "Users can insert their own profile." on profiles for insert with check (auth.uid() = id);

create policy "Venues are viewable by everyone." on venues for select using (true);
create policy "Events are viewable by everyone." on events for select using (true);
create policy "Participants are viewable by everyone." on participants for select using (true);

create policy "Authenticated users can create events." on events for insert with check (auth.role() = 'authenticated');
create policy "Authenticated users can join events." on participants for insert with check (auth.role() = 'authenticated');