schema.sql

-- ============================================================
-- SquaredUp MVP — Database Schema
-- Supabase (PostgreSQL)
-- ============================================================

-- ============================================================
-- ENUMS
-- ============================================================

-- User roles: both organizers and coaches authenticate via magic link (OTP)
create type user_role as enum ('organizer', 'coach');

-- All supported disciplines
create type discipline as enum (
  'bjj_gi',
  'bjj_nogi',
  'mma',
  'boxing',
  'k1_kickboxing',
  'muay_thai'
);

create type gender as enum ('male', 'female');

-- Event lifecycle: open → closed → matches_generated → confirmed
create type event_status as enum (
  'open',
  'closed',
  'matches_generated',
  'confirmed'
);

-- Traffic light system for bout quality
create type bout_confidence as enum ('green', 'amber', 'red');

-- BJJ belts — separate adult and junior systems
-- Algorithm calculates belt gap by ordinal position within the relevant system
--
-- Junior belts (13 ranks, ages 4-15):
--   white(0) → grey_white(1) → grey(2) → grey_black(3)
--   → yellow_white(4) → yellow(5) → yellow_black(6)
--   → orange_white(7) → orange(8) → orange_black(9)
--   → green_white(10) → green(11) → green_black(12)
--
-- Adult belts (5 ranks, age 16+):
--   white(0) → blue(1) → purple(2) → brown(3) → black(4)
--
create type belt_rank as enum (
  'white',
  -- Junior belts (colour/white bar → solid → colour/black bar)
  'grey_white',
  'grey',
  'grey_black',
  'yellow_white',
  'yellow',
  'yellow_black',
  'orange_white',
  'orange',
  'orange_black',
  'green_white',
  'green',
  'green_black',
  -- Adult belts
  'blue',
  'purple',
  'brown',
  'black'
);


-- ============================================================
-- TABLES
-- ============================================================

-- ----------------------------------------
-- Profiles
-- Extends Supabase auth.users. Created via a trigger on auth signup.
-- ----------------------------------------
create table profiles (
  id          uuid primary key references auth.users(id) on delete cascade,
  email       text not null,
  full_name   text not null,
  role        user_role not null,
  gym_name    text,  -- required for coaches, null for organizers
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now()
);

-- Coach must have a gym name
alter table profiles
  add constraint coach_must_have_gym
  check (role != 'coach' or gym_name is not null);


-- ----------------------------------------
-- Fighters
-- A coach's persistent roster. Survives across events.
-- ----------------------------------------
create table fighters (
  id                       uuid primary key default gen_random_uuid(),
  coach_id                 uuid not null references profiles(id) on delete cascade,
  name                     text not null,
  date_of_birth            date not null,
  gender                   gender not null,
  belt                     belt_rank,            -- null if fighter doesn't do BJJ
  is_amateur               boolean not null default false,
  is_professional          boolean not null default false,
  professional_fight_count int not null default 0,
  amateur_fight_count      int not null default 0,
  interclub_count          int not null default 0,
  weight_kg                numeric,           -- competition weight; nullable until coach sets it
  created_at               timestamptz not null default now(),
  updated_at               timestamptz not null default now()
);

-- Fight counts should not be negative
alter table fighters
  add constraint positive_fight_counts
  check (
    professional_fight_count >= 0
    and amateur_fight_count >= 0
    and interclub_count >= 0
  );

-- Weight must be positive if set
-- A fighter cannot be both amateur and professional
alter table fighters
  add constraint amateur_pro_exclusive
  check (not (is_amateur and is_professional));

-- Weight must be positive if set
alter table fighters
  add constraint positive_fighter_weight
  check (weight_kg is null or weight_kg > 0);


-- ----------------------------------------
-- Events
-- Created by an organizer. Has a shareable link via share_code.
-- ----------------------------------------
create table events (
  id                   uuid primary key default gen_random_uuid(),
  organizer_id         uuid not null references profiles(id) on delete cascade,
  name                 text not null,
  event_date           date not null,
  venue                text not null,
  address              text,
  weigh_in_time        time,
  submission_deadline  timestamptz not null,
  status               event_status not null default 'open',
  share_code           text unique not null,  -- URL: /event/{share_code}
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

-- Deadline must be before or on event date
alter table events
  add constraint deadline_before_event
  check (submission_deadline <= (event_date + interval '1 day'));


-- ----------------------------------------
-- Divisions
-- Each division = discipline + age category. Belongs to an event.
-- The algorithm runs independently per division.
-- ----------------------------------------
create table divisions (
  id                   uuid primary key default gen_random_uuid(),
  event_id             uuid not null references events(id) on delete cascade,
  discipline           discipline not null,
  age_category_name    text not null,          -- e.g. "Junior", "Adult"
  min_age              int not null,
  max_age              int not null,           -- use 99 for no upper limit
  weight_tolerance_kg  numeric not null default 3.0,
  experience_tolerance int not null default 5, -- max interclub gap before warning
  belt_tolerance       int not null default 1, -- max belt gap before warning. Suggested: 3 for junior divisions (within one colour group), 1 for adult divisions
  allow_mixed_gender   boolean not null default false,
  -- Soft constraint priority order. Top = breaks last (most protected).
  -- e.g. ["gender", "amateur_pro", "belt", "weight", "experience"]
  constraint_priority  jsonb not null default '["gender", "amateur_pro", "weight", "experience"]',
  created_at           timestamptz not null default now(),
  updated_at           timestamptz not null default now()
);

-- Age range must be valid
alter table divisions
  add constraint valid_age_range
  check (min_age >= 0 and max_age >= min_age);

-- No duplicate divisions in an event (same discipline + age category)
alter table divisions
  add constraint unique_division_per_event
  unique (event_id, discipline, age_category_name);


-- ----------------------------------------
-- Event Entries
-- A fighter entered into a specific division for a specific event.
-- This is the per-event, per-division data that can differ each time.
-- ----------------------------------------
create table event_entries (
  id                           uuid primary key default gen_random_uuid(),
  fighter_id                   uuid not null references fighters(id) on delete cascade,
  division_id                  uuid not null references divisions(id) on delete cascade,
  happy_to_fight_more_than_once boolean not null default false,
  notes                        text,            -- "first fight go easy", "happy to fight up", etc.
  withdrawn                    boolean not null default false,
  created_at                   timestamptz not null default now(),
  updated_at                   timestamptz not null default now()
);

-- A fighter can only enter a division once
alter table event_entries
  add constraint unique_fighter_per_division
  unique (fighter_id, division_id);


-- ----------------------------------------
-- Bouts
-- A matched pair of event entries within a division.
-- Generated by the algorithm, editable by the organizer.
-- ----------------------------------------
create table bouts (
  id            uuid primary key default gen_random_uuid(),
  division_id   uuid not null references divisions(id) on delete cascade,
  entry_1_id    uuid not null references event_entries(id) on delete cascade,
  entry_2_id    uuid not null references event_entries(id) on delete cascade,
  confidence    bout_confidence not null default 'green',
  -- Array of warning objects: [{"type": "weight_gap", "message": "5kg weight difference", "severity": "amber"}]
  warnings      jsonb not null default '[]',
  is_confirmed  boolean not null default false,
  created_at    timestamptz not null default now(),
  updated_at    timestamptz not null default now()
);

-- Can't fight yourself
alter table bouts
  add constraint no_self_match
  check (entry_1_id <> entry_2_id);


-- ============================================================
-- INDEXES
-- ============================================================

-- Fast lookups for common queries
create index idx_fighters_coach       on fighters(coach_id);
create index idx_divisions_event      on divisions(event_id);
create index idx_event_entries_fighter on event_entries(fighter_id);
create index idx_event_entries_division on event_entries(division_id);
create index idx_bouts_division       on bouts(division_id);
create index idx_bouts_entry_1        on bouts(entry_1_id);
create index idx_bouts_entry_2        on bouts(entry_2_id);
create index idx_events_share_code    on events(share_code);
create index idx_events_organizer     on events(organizer_id);


-- ============================================================
-- HELPER FUNCTIONS (defined before RLS policies that depend on them)
-- ============================================================

-- RLS helper: does a fighter have entries in this organizer's events?
-- SECURITY DEFINER bypasses RLS to avoid infinite recursion in fighter policies.
create or replace function public.fighter_in_organizer_event(
  p_fighter_id uuid,
  p_organizer_id uuid
)
returns boolean
language sql
security definer set search_path = ''
stable
as $$
  select exists (
    select 1 from public.event_entries ee
    join public.divisions d on d.id = ee.division_id
    join public.events e on e.id = d.event_id
    where ee.fighter_id = p_fighter_id
    and e.organizer_id = p_organizer_id
  );
$$;

-- RLS helper: does a coach have fighters entered in this organizer's events?
-- SECURITY DEFINER bypasses RLS to avoid infinite recursion in profile policies.
create or replace function public.coach_in_organizer_event(
  p_coach_id uuid,
  p_organizer_id uuid
)
returns boolean
language sql
security definer set search_path = ''
stable
as $$
  select exists (
    select 1 from public.fighters f
    join public.event_entries ee on ee.fighter_id = f.id
    join public.divisions d on d.id = ee.division_id
    join public.events e on e.id = d.event_id
    where f.coach_id = p_coach_id
    and e.organizer_id = p_organizer_id
  );
$$;


-- ============================================================
-- ROW LEVEL SECURITY (RLS)
-- Controls who can see/edit what. Critical for coach isolation.
-- ============================================================

alter table profiles enable row level security;
alter table fighters enable row level security;
alter table events enable row level security;
alter table divisions enable row level security;
alter table event_entries enable row level security;
alter table bouts enable row level security;

-- Profiles: users see own profile, organizers see coaches with fighters in their events.
-- Uses coach_in_organizer_event() SECURITY DEFINER helper to avoid infinite recursion
-- (profiles → fighters → event_entries → fighters).
-- Note: (select auth.uid()) is used instead of auth.uid() so the value is
-- evaluated once per query (initplan) rather than re-evaluated per row.
create policy "Users can view relevant profiles"
  on profiles for select using (
    id = (select auth.uid())
    or
    public.coach_in_organizer_event(id, (select auth.uid()))
  );

create policy "Users can update own profile"
  on profiles for update
  using (id = (select auth.uid()))
  with check (role = (select role from profiles where id = (select auth.uid())));

-- Fighters: coaches see own fighters, organizers see fighters entered in their events.
-- Uses fighter_in_organizer_event() SECURITY DEFINER helper to avoid infinite recursion
-- (fighters → event_entries RLS → fighters).
create policy "Users can view relevant fighters"
  on fighters for select using (
    coach_id = (select auth.uid())
    or
    public.fighter_in_organizer_event(id, (select auth.uid()))
  );

create policy "Coaches can insert own fighters"
  on fighters for insert with check (coach_id = (select auth.uid()));

create policy "Coaches can update own fighters"
  on fighters for update using (coach_id = (select auth.uid()));

create policy "Coaches can delete own fighters"
  on fighters for delete using (coach_id = (select auth.uid()));

-- NOTE: Organizers access fighter data via get_event_fighters() security definer function
-- to avoid RLS recursion (fighters → event_entries → fighters).

-- Events: anyone can view (needed for shareable link landing page)
create policy "Events are publicly readable"
  on events for select using (true);

-- Events: organizers can create and manage their own events
create policy "Organizers can insert events"
  on events for insert with check (organizer_id = (select auth.uid()));

create policy "Organizers can update own events"
  on events for update using (organizer_id = (select auth.uid()));

-- Divisions: readable by anyone (part of event info)
create policy "Divisions are publicly readable"
  on divisions for select using (true);

-- Divisions: only event organizer can manage
create policy "Organizers can insert divisions"
  on divisions for insert with check (
    exists (
      select 1 from events where events.id = event_id and events.organizer_id = (select auth.uid())
    )
  );

create policy "Organizers can update divisions"
  on divisions for update using (
    exists (
      select 1 from events where events.id = event_id and events.organizer_id = (select auth.uid())
    )
  );

-- Event Entries: single SELECT policy combines coach + organizer visibility
-- to avoid multiple permissive policies on the same action.
create policy "Users can view relevant entries"
  on event_entries for select using (
    -- Coach: owns the fighter
    exists (
      select 1 from fighters where fighters.id = fighter_id and fighters.coach_id = (select auth.uid())
    )
    or
    -- Organizer: owns the event
    exists (
      select 1 from divisions d
      join events e on e.id = d.event_id
      where d.id = division_id
      and e.organizer_id = (select auth.uid())
    )
  );

create policy "Coaches can insert own entries"
  on event_entries for insert with check (
    exists (
      select 1 from fighters where fighters.id = fighter_id and fighters.coach_id = (select auth.uid())
    )
  );

create policy "Coaches can update own entries"
  on event_entries for update using (
    exists (
      select 1 from fighters where fighters.id = fighter_id and fighters.coach_id = (select auth.uid())
    )
  );

-- Organizers can delete suspicious entries
create policy "Organizers can delete entries for their events"
  on event_entries for delete using (
    exists (
      select 1 from divisions d
      join events e on e.id = d.event_id
      where d.id = division_id
      and e.organizer_id = (select auth.uid())
    )
  );

-- Bouts: single SELECT policy combines organizer + coach visibility.
-- Separate INSERT/UPDATE/DELETE policies for organizers only.
create policy "Users can view relevant bouts"
  on bouts for select using (
    -- Organizer: owns the event
    exists (
      select 1 from divisions d
      join events e on e.id = d.event_id
      where d.id = division_id
      and e.organizer_id = (select auth.uid())
    )
    or
    -- Coach: confirmed bouts involving their fighters
    (
      is_confirmed = true
      and exists (
        select 1 from event_entries ee
        join fighters f on f.id = ee.fighter_id
        where (ee.id = entry_1_id or ee.id = entry_2_id)
        and f.coach_id = (select auth.uid())
      )
    )
  );

create policy "Organizers can insert bouts"
  on bouts for insert with check (
    exists (
      select 1 from divisions d
      join events e on e.id = d.event_id
      where d.id = division_id
      and e.organizer_id = (select auth.uid())
    )
  );

create policy "Organizers can update bouts"
  on bouts for update using (
    exists (
      select 1 from divisions d
      join events e on e.id = d.event_id
      where d.id = division_id
      and e.organizer_id = (select auth.uid())
    )
  );

create policy "Organizers can delete bouts"
  on bouts for delete using (
    exists (
      select 1 from divisions d
      join events e on e.id = d.event_id
      where d.id = division_id
      and e.organizer_id = (select auth.uid())
    )
  );


-- ============================================================
-- OTHER FUNCTIONS
-- ============================================================

-- Auto-create profile on signup (triggered by Supabase auth)
-- Always creates as coach. Organizer promotion happens via promote_to_organizer().
create or replace function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
begin
  insert into public.profiles (id, email, full_name, role, gym_name)
  values (
    new.id,
    new.email,
    '',
    'coach'::public.user_role,
    '' -- placeholder; setup form collects real gym name
  );
  return new;
end;
$$;

-- Promote a fresh account to organizer. Only works for accounts that haven't
-- been set up yet (no name, no gym). Called from the organizer-setup page.
create or replace function public.promote_to_organizer()
returns void
language plpgsql
security definer set search_path = ''
as $$
begin
  update public.profiles
  set role = 'organizer'::public.user_role, gym_name = null
  where id = (select auth.uid())
    and role = 'coach'
    and (gym_name = '' or gym_name is null)
    and full_name = '';

  if not found then
    raise exception 'Cannot promote: account already set up';
  end if;
end;
$$;

drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute function public.handle_new_user();

-- Auto-update updated_at timestamp
create or replace function update_updated_at()
returns trigger
language plpgsql
security invoker set search_path = ''
as $$
begin
  new.updated_at = now();
  return new;
end;
$$;

create trigger set_updated_at before update on profiles     for each row execute function update_updated_at();
create trigger set_updated_at before update on fighters     for each row execute function update_updated_at();
create trigger set_updated_at before update on events       for each row execute function update_updated_at();
create trigger set_updated_at before update on divisions    for each row execute function update_updated_at();
create trigger set_updated_at before update on event_entries for each row execute function update_updated_at();
create trigger set_updated_at before update on bouts        for each row execute function update_updated_at();

-- Enforce submission deadline on INSERT into event_entries
-- Blocks entries if the event is not open or the deadline has passed.
-- Only fires on INSERT — coaches can still withdraw (UPDATE) after the deadline.
create or replace function enforce_submission_deadline()
returns trigger
language plpgsql
security invoker set search_path = ''
as $$
declare
  _event_status public.event_status;
  _submission_deadline timestamptz;
begin
  select e.status, e.submission_deadline
  into _event_status, _submission_deadline
  from public.divisions d
  join public.events e on e.id = d.event_id
  where d.id = new.division_id;

  if _event_status <> 'open' then
    raise exception 'Entries are closed. This event is no longer accepting submissions.';
  end if;

  if _submission_deadline is not null and now() > _submission_deadline then
    raise exception 'The submission deadline for this event has passed.';
  end if;

  return new;
end;
$$;

create trigger check_submission_deadline
  before insert on event_entries
  for each row execute function enforce_submission_deadline();

-- Enforce valid event state transitions: open -> closed -> matches_generated -> confirmed
create or replace function enforce_event_state_transition()
returns trigger
language plpgsql
security invoker set search_path = ''
as $$
begin
  -- Only fire when status actually changes
  if OLD.status = NEW.status then
    return NEW;
  end if;

  if (OLD.status = 'open' and NEW.status = 'closed') or
     (OLD.status = 'closed' and NEW.status = 'matches_generated') or
     (OLD.status = 'matches_generated' and NEW.status = 'confirmed') then
    return NEW;
  end if;

  raise exception 'Invalid event status transition: cannot move from "%" to "%". Events must follow the sequence: open -> closed -> matches_generated -> confirmed.',
    OLD.status, NEW.status;
end;
$$;

create trigger check_event_state_transition
  before update on events
  for each row execute function enforce_event_state_transition();

-- Enforce age eligibility on INSERT into event_entries
-- Blocks entries if the fighter's age on the event date is outside the division's min_age/max_age.
create or replace function enforce_age_eligibility()
returns trigger
language plpgsql
security invoker set search_path = ''
as $$
declare
  _date_of_birth date;
  _event_date    date;
  _min_age       int;
  _max_age       int;
  _age_on_event  int;
begin
  -- Look up fighter's date of birth
  select f.date_of_birth
  into _date_of_birth
  from public.fighters f
  where f.id = new.fighter_id;

  -- Look up division age limits and the event date in one join
  select d.min_age, d.max_age, e.event_date
  into _min_age, _max_age, _event_date
  from public.divisions d
  join public.events e on e.id = d.event_id
  where d.id = new.division_id;

  -- Calculate age on event date
  _age_on_event := extract(year from age(_event_date, _date_of_birth))::int;

  -- Check lower bound
  if _min_age is not null and _age_on_event < _min_age then
    raise exception 'Fighter is too young for this division. The division requires a minimum age of %, but this fighter will be % on the event date.',
      _min_age, _age_on_event;
  end if;

  -- Check upper bound
  if _max_age is not null and _age_on_event > _max_age then
    raise exception 'Fighter is too old for this division. The division has a maximum age of %, but this fighter will be % on the event date.',
      _max_age, _age_on_event;
  end if;

  return new;
end;
$$;

create trigger check_age_eligibility
  before insert on event_entries
  for each row execute function enforce_age_eligibility();


-- Get fighters for an event (bypasses RLS for organizer submissions dashboard)
-- Only returns data if the caller is the event's organizer.
create or replace function public.get_event_fighters(p_event_id uuid)
returns table (
  entry_id uuid,
  fighter_id uuid,
  fighter_name text,
  date_of_birth date,
  gender public.gender,
  belt public.belt_rank,
  is_amateur boolean,
  is_professional boolean,
  professional_fight_count int,
  amateur_fight_count int,
  interclub_count int,
  weight_kg numeric,
  notes text,
  happy_to_fight_more_than_once boolean,
  withdrawn boolean,
  division_id uuid,
  coach_gym text
)
language plpgsql
security definer set search_path = ''
as $$
begin
  -- Verify caller is the organizer
  if not exists (
    select 1 from public.events e
    where e.id = p_event_id and e.organizer_id = auth.uid()
  ) then
    raise exception 'Not authorized';
  end if;

  return query
  select
    ee.id as entry_id,
    f.id as fighter_id,
    f.name as fighter_name,
    f.date_of_birth,
    f.gender,
    f.belt,
    f.is_amateur,
    f.is_professional,
    f.professional_fight_count,
    f.amateur_fight_count,
    f.interclub_count,
    f.weight_kg,
    ee.notes,
    ee.happy_to_fight_more_than_once,
    ee.withdrawn,
    ee.division_id,
    p.gym_name as coach_gym
  from public.event_entries ee
  join public.fighters f on f.id = ee.fighter_id
  join public.divisions d on d.id = ee.division_id
  join public.profiles p on p.id = f.coach_id
  where d.event_id = p_event_id;
end;
$$;

-- ============================================================
-- USEFUL VIEWS
-- ============================================================

-- Fighter age calculated on event date (used for division eligibility)
create or replace view fighter_event_ages
with (security_invoker = on) as
select
  ee.id as entry_id,
  f.id as fighter_id,
  f.name as fighter_name,
  e.id as event_id,
  e.event_date,
  extract(year from age(e.event_date, f.date_of_birth))::int as age_on_event_date
from event_entries ee
join fighters f on f.id = ee.fighter_id
join divisions d on d.id = ee.division_id
join events e on e.id = d.event_id;

-- Full bout details for match card view
create or replace view bout_details
with (security_invoker = on) as
select
  b.id as bout_id,
  b.confidence,
  b.warnings,
  b.is_confirmed,
  d.id as division_id,
  d.discipline,
  d.age_category_name,
  e.id as event_id,
  e.name as event_name,
  -- Fighter 1
  f1.name as fighter_1_name,
  f1.gender as fighter_1_gender,
  f1.belt as fighter_1_belt,
  f1.interclub_count as fighter_1_interclubs,
  f1.is_amateur as fighter_1_is_amateur,
  f1.is_professional as fighter_1_is_pro,
  f1.weight_kg as fighter_1_weight,
  ee1.notes as fighter_1_notes,
  ee1.withdrawn as fighter_1_withdrawn,
  ee1.happy_to_fight_more_than_once as fighter_1_happy_to_fight_again,
  p1.gym_name as fighter_1_gym,
  -- Fighter 2
  f2.name as fighter_2_name,
  f2.gender as fighter_2_gender,
  f2.belt as fighter_2_belt,
  f2.interclub_count as fighter_2_interclubs,
  f2.is_amateur as fighter_2_is_amateur,
  f2.is_professional as fighter_2_is_pro,
  f2.weight_kg as fighter_2_weight,
  ee2.notes as fighter_2_notes,
  ee2.withdrawn as fighter_2_withdrawn,
  ee2.happy_to_fight_more_than_once as fighter_2_happy_to_fight_again,
  p2.gym_name as fighter_2_gym
from bouts b
join divisions d on d.id = b.division_id
join events e on e.id = d.event_id
join event_entries ee1 on ee1.id = b.entry_1_id
join event_entries ee2 on ee2.id = b.entry_2_id
join fighters f1 on f1.id = ee1.fighter_id
join fighters f2 on f2.id = ee2.fighter_id
join profiles p1 on p1.id = f1.coach_id
join profiles p2 on p2.id = f2.coach_id;