supabase-migration.sql

-- LeagueUp Database Migration for Supabase
-- Run this script in your Supabase SQL Editor

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Create users table
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  first_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  age INTEGER CHECK (age >= 13 AND age <= 100),
  phone VARCHAR(50),
  bio TEXT CHECK (char_length(bio) <= 500),
  profile_photo TEXT,
  sports_interests TEXT[] DEFAULT ARRAY[]::TEXT[],
  competitiveness VARCHAR(50) DEFAULT 'casual' CHECK (competitiveness IN ('casual', 'competitive', 'very-competitive')),
  location JSONB DEFAULT '{}'::JSONB,
  teams UUID[] DEFAULT ARRAY[]::UUID[],
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create pickup_games table
CREATE TABLE IF NOT EXISTS pickup_games (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  title VARCHAR(100) NOT NULL,
  sport VARCHAR(50) NOT NULL CHECK (sport IN ('soccer', 'basketball', 'tennis', 'volleyball', 'softball', 'baseball', 'football', 'hockey', 'golf', 'swimming', 'running', 'cycling', 'other')),
  description TEXT CHECK (char_length(description) <= 1000),
  location JSONB NOT NULL,
  date_time TIMESTAMP WITH TIME ZONE NOT NULL,
  duration INTEGER NOT NULL CHECK (duration >= 30 AND duration <= 480),
  max_players INTEGER NOT NULL CHECK (max_players >= 2 AND max_players <= 50),
  current_players INTEGER DEFAULT 0 CHECK (current_players >= 0),
  competitiveness VARCHAR(50) NOT NULL CHECK (competitiveness IN ('casual', 'competitive', 'very-competitive')),
  skill_level VARCHAR(50) NOT NULL CHECK (skill_level IN ('beginner', 'intermediate', 'advanced', 'mixed')),
  cost NUMERIC(10, 2) DEFAULT 0 CHECK (cost >= 0),
  equipment_provided BOOLEAN DEFAULT FALSE,
  creator_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  players UUID[] DEFAULT ARRAY[]::UUID[],
  waitlist UUID[] DEFAULT ARRAY[]::UUID[],
  status VARCHAR(50) DEFAULT 'upcoming' CHECK (status IN ('upcoming', 'in-progress', 'completed', 'cancelled')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create leagues table (for future use)
CREATE TABLE IF NOT EXISTS leagues (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255) NOT NULL,
  sport VARCHAR(50) NOT NULL,
  description TEXT,
  season VARCHAR(100),
  start_date DATE,
  end_date DATE,
  max_teams INTEGER,
  current_teams INTEGER DEFAULT 0,
  registration_fee NUMERIC(10, 2) DEFAULT 0,
  creator_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  teams UUID[] DEFAULT ARRAY[]::UUID[],
  status VARCHAR(50) DEFAULT 'registration' CHECK (status IN ('registration', 'active', 'completed', 'cancelled')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create teams table (for future use)
CREATE TABLE IF NOT EXISTS teams (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name VARCHAR(255) NOT NULL,
  sport VARCHAR(50) NOT NULL,
  captain_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  league_id UUID REFERENCES leagues(id) ON DELETE SET NULL,
  players UUID[] DEFAULT ARRAY[]::UUID[],
  wins INTEGER DEFAULT 0,
  losses INTEGER DEFAULT 0,
  ties INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create games table (for future use)
CREATE TABLE IF NOT EXISTS games (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  league_id UUID NOT NULL REFERENCES leagues(id) ON DELETE CASCADE,
  home_team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  away_team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  scheduled_time TIMESTAMP WITH TIME ZONE NOT NULL,
  location JSONB,
  referee_id UUID REFERENCES users(id) ON DELETE SET NULL,
  home_score INTEGER DEFAULT 0,
  away_score INTEGER DEFAULT 0,
  status VARCHAR(50) DEFAULT 'scheduled' CHECK (status IN ('scheduled', 'in-progress', 'completed', 'cancelled', 'postponed')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create game_stats table (for future use)
CREATE TABLE IF NOT EXISTS game_stats (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  game_id UUID NOT NULL REFERENCES games(id) ON DELETE CASCADE,
  player_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
  goals INTEGER DEFAULT 0,
  assists INTEGER DEFAULT 0,
  yellow_cards INTEGER DEFAULT 0,
  red_cards INTEGER DEFAULT 0,
  notes TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_pickup_games_date_time ON pickup_games(date_time);
CREATE INDEX IF NOT EXISTS idx_pickup_games_sport ON pickup_games(sport);
CREATE INDEX IF NOT EXISTS idx_pickup_games_creator ON pickup_games(creator_id);
CREATE INDEX IF NOT EXISTS idx_pickup_games_status ON pickup_games(status);
CREATE INDEX IF NOT EXISTS idx_pickup_games_location_city ON pickup_games((location->>'city'));
CREATE INDEX IF NOT EXISTS idx_pickup_games_location_state ON pickup_games((location->>'state'));
CREATE INDEX IF NOT EXISTS idx_leagues_creator ON leagues(creator_id);
CREATE INDEX IF NOT EXISTS idx_teams_captain ON teams(captain_id);
CREATE INDEX IF NOT EXISTS idx_teams_league ON teams(league_id);
CREATE INDEX IF NOT EXISTS idx_games_league ON games(league_id);
CREATE INDEX IF NOT EXISTS idx_games_home_team ON games(home_team_id);
CREATE INDEX IF NOT EXISTS idx_games_away_team ON games(away_team_id);
CREATE INDEX IF NOT EXISTS idx_game_stats_game ON game_stats(game_id);
CREATE INDEX IF NOT EXISTS idx_game_stats_player ON game_stats(player_id);

-- Create function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create triggers to automatically update updated_at
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_pickup_games_updated_at BEFORE UPDATE ON pickup_games
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_leagues_updated_at BEFORE UPDATE ON leagues
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_teams_updated_at BEFORE UPDATE ON teams
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_games_updated_at BEFORE UPDATE ON games
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_game_stats_updated_at BEFORE UPDATE ON game_stats
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Enable Row Level Security (RLS) for all tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE pickup_games ENABLE ROW LEVEL SECURITY;
ALTER TABLE leagues ENABLE ROW LEVEL SECURITY;
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE games ENABLE ROW LEVEL SECURITY;
ALTER TABLE game_stats ENABLE ROW LEVEL SECURITY;

-- Create policies for public access (we're using JWT authentication separately)
-- Note: You may want to adjust these policies based on your security requirements

-- Users: Allow all operations for now (JWT handles auth in API)
CREATE POLICY "Allow all operations on users" ON users FOR ALL USING (true) WITH CHECK (true);

-- Pickup Games: Allow all operations
CREATE POLICY "Allow all operations on pickup_games" ON pickup_games FOR ALL USING (true) WITH CHECK (true);

-- Leagues: Allow all operations
CREATE POLICY "Allow all operations on leagues" ON leagues FOR ALL USING (true) WITH CHECK (true);

-- Teams: Allow all operations
CREATE POLICY "Allow all operations on teams" ON teams FOR ALL USING (true) WITH CHECK (true);

-- Games: Allow all operations
CREATE POLICY "Allow all operations on games" ON games FOR ALL USING (true) WITH CHECK (true);

-- Game Stats: Allow all operations
CREATE POLICY "Allow all operations on game_stats" ON game_stats FOR ALL USING (true) WITH CHECK (true);

-- Insert some sample data for testing (optional)
-- You can remove this section if you don't want sample data

-- Note: Sample data has been omitted. You can add test users and games manually after migration.