schema_v2.sql

-- ============================================
-- SehaSport Schema v2 - Architecture Update
-- ============================================
-- Fresh tables (run after dropping old schema if needed)

-- ============================================
-- 1. PROFILES (Auth-linked, with roles)
-- ============================================
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 timestamptz default now() not null
);

-- ============================================
-- 2. VENUES (Owned by venue-role users)
-- ============================================
create table venues (
  id bigint generated by default as identity primary key,
  name text not null,
  location text not null,
  lat double precision,
  lng double precision,
  sports text,
  description text,
  image_url text,
  owner_id uuid references auth.users,
  created_at timestamptz default now() not null
);

-- ============================================
-- 3. COURTS (Venue has many Courts)
-- ============================================
create table courts (
  id bigint generated by default as identity primary key,
  venue_id bigint references venues(id) on delete cascade not null,
  name text not null, -- e.g., "Court A", "Field 1"
  sport text not null,
  capacity int default 4,
  is_active boolean default true,
  created_at timestamptz default now() not null
);

-- ============================================
-- 4. COURT SCHEDULES (Availability slots)
-- ============================================
create table court_schedules (
  id bigint generated by default as identity primary key,
  court_id bigint references courts(id) on delete cascade not null,
  day_of_week int check (day_of_week between 0 and 6), -- 0=Sunday, 6=Saturday
  start_time time not null,
  end_time time not null,
  is_available boolean default true,
  created_at timestamptz default now() not null
);

-- ============================================
-- 5. COMMUNITIES (Standalone aggregate)
-- ============================================
create table communities (
  id bigint generated by default as identity primary key,
  name text not null,
  description text,
  sport text not null,
  image_url text,
  is_public boolean default true,
  has_membership boolean default false,
  created_at timestamptz default now() not null
);

-- ============================================
-- 6. COMMUNITY MEMBERS (Governance: LEADER/ADMIN/MEMBER)
-- ============================================
create table community_members (
  id bigint generated by default as identity primary key,
  community_id bigint references communities(id) on delete cascade not null,
  user_id uuid references auth.users not null,
  role text check (role in ('LEADER', 'ADMIN', 'MEMBER')) not null default 'MEMBER',
  joined_at timestamptz default now() not null,
  unique(community_id, user_id)
);

-- ============================================
-- 7. EVENTS (First-class aggregate, USER or COMMUNITY owned)
-- ============================================
create table events (
  id bigint generated by default as identity primary key,
  title text not null,
  sport text not null,
  owner_type text check (owner_type in ('USER', 'COMMUNITY')) default 'USER' not null,
  owner_id text not null, -- UUID string for USER, bigint as text for COMMUNITY
  venue_id bigint references venues(id),
  court_id bigint references courts(id),
  date date not null,
  start_time time not null,
  end_time time not null,
  location text, -- Fallback if no venue selected
  price text,
  level text,
  participants_count int default 1,
  max_participants int default 4,
  image_url text,
  description text,
  is_paid boolean default false,
  is_featured boolean default false,
  created_at timestamptz default now() not null
);

-- ============================================
-- 8. BOOKINGS (Court reservation, auto-created with events)
-- ============================================
create table bookings (
  id bigint generated by default as identity primary key,
  court_id bigint references courts(id) on delete cascade not null,
  user_id uuid references auth.users not null,
  event_id bigint references events(id) on delete cascade,
  date date not null,
  start_time time not null,
  end_time time not null,
  status text check (status in ('pending', 'confirmed', 'cancelled')) default 'confirmed',
  created_at timestamptz default now() not null
);

-- Unique constraint to prevent overlapping bookings
create unique index bookings_no_overlap on bookings (court_id, date, start_time, end_time) 
  where status != 'cancelled';

-- ============================================
-- 9. PARTICIPANTS (Event participation, Many-to-Many)
-- ============================================
create table participants (
  id bigint generated by default as identity primary key,
  event_id bigint references events(id) on delete cascade not null,
  user_id uuid references auth.users not null,
  status text check (status in ('interested', 'joined', 'cancelled')) default 'joined',
  joined_at timestamptz default now() not null,
  unique(event_id, user_id)
);

-- ============================================
-- ROW LEVEL SECURITY
-- ============================================
alter table profiles enable row level security;
alter table venues enable row level security;
alter table courts enable row level security;
alter table court_schedules enable row level security;
alter table communities enable row level security;
alter table community_members enable row level security;
alter table events enable row level security;
alter table bookings enable row level security;
alter table participants enable row level security;

-- ============================================
-- POLICIES: Profiles
-- ============================================
create policy "Profiles viewable by everyone" on profiles for select using (true);
create policy "Users can insert own profile" on profiles for insert with check (auth.uid() = id);
create policy "Users can update own profile" on profiles for update using (auth.uid() = id);

-- ============================================
-- POLICIES: Venues
-- ============================================
create policy "Venues viewable by everyone" on venues for select using (true);
create policy "Venue owners can insert" on venues for insert with check (auth.uid() = owner_id);
create policy "Venue owners can update" on venues for update using (auth.uid() = owner_id);
create policy "Venue owners can delete" on venues for delete using (auth.uid() = owner_id);

-- ============================================
-- POLICIES: Courts
-- ============================================
create policy "Courts viewable by everyone" on courts for select using (true);
create policy "Venue owners can manage courts" on courts for all 
  using (exists (select 1 from venues where venues.id = courts.venue_id and venues.owner_id = auth.uid()));

-- ============================================
-- POLICIES: Court Schedules
-- ============================================
create policy "Court schedules viewable by everyone" on court_schedules for select using (true);
create policy "Venue owners can manage schedules" on court_schedules for all 
  using (exists (
    select 1 from courts 
    join venues on venues.id = courts.venue_id 
    where courts.id = court_schedules.court_id and venues.owner_id = auth.uid()
  ));

-- ============================================
-- POLICIES: Communities
-- ============================================
create policy "Public communities viewable" on communities for select using (is_public = true);
create policy "Members can view their communities" on communities for select 
  using (exists (select 1 from community_members where community_id = communities.id and user_id = auth.uid()));
create policy "Authenticated can create communities" on communities for insert with check (auth.role() = 'authenticated');
create policy "Leaders can update communities" on communities for update 
  using (exists (select 1 from community_members where community_id = communities.id and user_id = auth.uid() and role = 'LEADER'));
create policy "Leaders can delete communities" on communities for delete 
  using (exists (select 1 from community_members where community_id = communities.id and user_id = auth.uid() and role = 'LEADER'));

-- ============================================
-- POLICIES: Community Members
-- ============================================
create policy "Community members viewable" on community_members for select using (true);
create policy "Users can join public communities" on community_members for insert 
  with check (
    auth.uid() = user_id and 
    role = 'MEMBER' and 
    exists (select 1 from communities where id = community_id and is_public = true)
  );
create policy "Leaders/Admins can manage members" on community_members for all 
  using (exists (
    select 1 from community_members cm 
    where cm.community_id = community_members.community_id 
    and cm.user_id = auth.uid() 
    and cm.role in ('LEADER', 'ADMIN')
  ));

-- ============================================
-- POLICIES: Events
-- ============================================
create policy "Events viewable by everyone" on events for select using (true);
create policy "Authenticated can create events" on events for insert with check (auth.role() = 'authenticated');
create policy "Event owners can update" on events for update 
  using (
    (owner_type = 'USER' and owner_id = auth.uid()::text) or
    (owner_type = 'COMMUNITY' and exists (
      select 1 from community_members 
      where community_id = events.owner_id::bigint 
      and user_id = auth.uid() 
      and role in ('LEADER', 'ADMIN')
    ))
  );
create policy "Event owners can delete" on events for delete 
  using (
    (owner_type = 'USER' and owner_id = auth.uid()::text) or
    (owner_type = 'COMMUNITY' and exists (
      select 1 from community_members 
      where community_id = events.owner_id::bigint 
      and user_id = auth.uid() 
      and role in ('LEADER', 'ADMIN')
    ))
  );

-- ============================================
-- POLICIES: Bookings
-- ============================================
create policy "Bookings viewable by everyone" on bookings for select using (true);
create policy "Authenticated can create bookings" on bookings for insert with check (auth.uid() = user_id);
create policy "Users can update own bookings" on bookings for update using (auth.uid() = user_id);

-- ============================================
-- POLICIES: Participants
-- ============================================
create policy "Participants viewable by everyone" on participants for select using (true);
create policy "Authenticated can join events" on participants for insert with check (auth.uid() = user_id);
create policy "Users can update own participation" on participants for update using (auth.uid() = user_id);
create policy "Users can leave events" on participants for delete using (auth.uid() = user_id);

-- ============================================
-- HELPER FUNCTION: Check booking availability
-- ============================================
create or replace function check_court_availability(
  p_court_id bigint,
  p_date date,
  p_start_time time,
  p_end_time time
) returns boolean as $$
begin
  return not exists (
    select 1 from bookings 
    where court_id = p_court_id 
    and date = p_date 
    and status != 'cancelled'
    and (
      (start_time < p_end_time and end_time > p_start_time) -- Overlapping check
    )
  );
end;
$$ language plpgsql;

-- ============================================
-- TRIGGER: Ensure single LEADER per community
-- ============================================
create or replace function enforce_single_leader()
returns trigger as $$
begin
  if NEW.role = 'LEADER' then
    -- Demote existing leader to ADMIN
    update community_members 
    set role = 'ADMIN' 
    where community_id = NEW.community_id 
    and role = 'LEADER' 
    and id != NEW.id;
  end if;
  return NEW;
end;
$$ language plpgsql;

create trigger ensure_single_leader
  after insert or update on community_members
  for each row execute function enforce_single_leader();