Dropback · veilance · Nov 10, 2025 · Nov 10, 2025 · Nov 11, 2025 · Nov 11, 2025
diff --git a/apps/web/.env.example b/apps/web/.env.example
@@ -0,0 +1,4 @@
+NEXT_PUBLIC_SUPABASE_URL=
+NEXT_PUBLIC_SUPABASE_ANON_KEY=
+NEXT_PUBLIC_ESPN_CORE_API_URL=https://sports.core.api.espn.com/v3/sports
+NEXT_PUBLIC_ESPN_PLAYER_STATISTICS_API_URL=https://site.web.api.espn.com/apis/common/v3/sports
diff --git a/apps/web/.gitignore b/apps/web/.gitignore
@@ -26,7 +26,7 @@ yarn-debug.log*
 yarn-error.log*
 
 # env files (can opt-in for commiting if needed)
-.env*
+.env.local
 
 # vercel
 .vercel

diff --git a/apps/web/app/api/actions/index.ts b/apps/web/app/api/actions/index.ts
@@ -1,5 +1,6 @@
 "use server";
 
+import { NextResponse } from "next/server";
 import { createClient } from "../supabase";
 
 const supabase = createClient();
@@ -21,7 +22,7 @@ export async function getPlayers() {
     return data;
   } catch (error) {
     console.error("Error fetching players:", error);
-    return [];
+    return NextResponse.json({ error }, { status: 500 });
   }
 }
 
@@ -53,34 +54,35 @@ export async function getPlayerStats(playerIds: string[]) {
  */
 export async function getPlayerWithStats(playerId: string) {
   try {
-    // Fetch player data
-    const { data: playerData, error: playerError } = await supabase
+    const { data: player, error: playerError } = await supabase
       .from("players")
       .select("*")
-      .eq("player_id", playerId)
+      .eq("espn_player_id", playerId)
       .single();
-
+  
     if (playerError) {
-      throw playerError;
+      return NextResponse.json(
+        { error: playerError.message },
+        { status: 500 }
+      );
     }
-
-    // Fetch player stats
-    const { data: statsData, error: statsError } = await supabase
+
+    const { data: stats, error: statsError } = await supabase
       .from("player_stats")
       .select("*")
-      .eq("player_id", playerId)
-      .single();
-
-    if (statsError && statsError.code !== "PGRST116") {
-      // PGRST116 is "No rows returned"
-      throw statsError;
+      .eq("espn_player_id", playerId);
+
+    if (statsError) {
+      return NextResponse.json(
+        { error: statsError.message },
+        { status: 500 }
+      );
     }
 
-    // Combine the data
     return {
-      ...playerData,
-      stats: statsData || null,
-    };
+      ...player,
+      statistics: stats ?? [],
+    }
   } catch (error) {
     console.error(`Error fetching player data for ${playerId}:`, error);
     return null;

diff --git a/apps/web/app/api/data-ingestion/helper.ts b/apps/web/app/api/data-ingestion/helper.ts
@@ -0,0 +1,33 @@
+import { BasketballStatistics } from "./interfaces";
+
+// Define the order of keys exactly as they appear in stats[]
+const statKeys: (keyof BasketballStatistics)[] = [
+  "gamesPlayed",
+  "avgMinutes",
+  "avgFieldGoalsMadeAvgFieldGoalsAttempted",
+  "fieldGoalPct",
+  "avgThreePointFieldGoalsMadeToAvgThreePointFieldGoalsAttempted",
+  "threePointFieldGoalPct",
+  "freeThrowsMadeToAttemptedPerGame",
+  "freeThrowPercentage",
+  "avgOffensiveRebounds",
+  "avgDefensiveRebounds",
+  "avgRebounds",
+  "avgAssists",
+  "avgBlocks",
+  "avgSteals",
+  "avgFouls",
+  "avgTurnovers",
+  "avgPoints",
+];
+
+export const formatStatistics = ({ stats }: { stats: string[] }): BasketballStatistics => {
+  const result: Partial<BasketballStatistics> = {};
+
+  statKeys.forEach((key, index) => {
+    const value = stats[index];
+    result[key] = value
+  });
+
+  return result as BasketballStatistics;
+};
diff --git a/apps/web/app/api/data-ingestion/interfaces.ts b/apps/web/app/api/data-ingestion/interfaces.ts
@@ -0,0 +1,24 @@
+export interface BasketballStatistics {
+  gamesPlayed: string
+  avgMinutes: string
+  avgFieldGoalsMadeAvgFieldGoalsAttempted: string
+  fieldGoalPct: string
+  avgThreePointFieldGoalsMadeToAvgThreePointFieldGoalsAttempted: string
+  threePointFieldGoalPct: string
+  freeThrowsMadeToAttemptedPerGame: string
+  freeThrowPercentage: string
+  avgOffensiveRebounds: string
+  avgDefensiveRebounds: string
+  avgRebounds: string
+  avgAssists: string
+  avgBlocks: string
+  avgSteals: string
+  avgFouls: string
+  avgTurnovers: string
+  avgPoints: string
+}
+
+export enum SplitTypeEnum {
+  HOME = 'HOME',
+  AWAY = 'AWAY'
+}
diff --git a/apps/web/app/api/data-ingestion/route.ts b/apps/web/app/api/data-ingestion/route.ts
@@ -1,37 +1,30 @@
 
-import { createClient } from "@supabase/supabase-js";
+import { createClient } from "../supabase";
+import { formatStatistics } from "./helper";
+import { BasketballStatistics, SplitTypeEnum } from "./interfaces";
 
-// Initialize Supabase client
-// These would typically come from environment variables
-const supabaseUrl =
-  "http://localhost:54321";
-const supabaseKey =
-  "your-local-service-role-key";
-const supabase = createClient(supabaseUrl, supabaseKey);
+// Base URLs for ESPN API
+const baseESPNAPIUrl = process.env.NEXT_PUBLIC_ESPN_CORE_API_URL!
+const espnPlayerStatsUrl = process.env.NEXT_PUBLIC_ESPN_PLAYER_STATISTICS_API_URL!;
+
+// Initiate Supabase
+const supabase = createClient()
 
 // Configuration for the sport we want to fetch
 // Assignees will modify this to their chosen sport
 const SPORT_CONFIG = {
   sport: "basketball",
   league: "mens-college-basketball",
-  season: "2023", // Update this to current season
+  season: "2025",
 };
 
 /**
  * Fetches player data from ESPN API
- * @param teamId - Optional team ID to filter players
+ * NOTE: I removed the team id filter because the basketball api didn't have this
  */
-async function fetchPlayersFromESPN(teamId?: string) {
+async function fetchPlayersFromESPN() {
   try {
-    // Base URL for ESPN API
-    const baseUrl = "https://site.web.api.espn.com/apis/common/v3/sports";
-
-    // Endpoint to get players (this would be different based on the sport)
-    // For demonstration purposes, using a team endpoint that returns players
-    // Assignees will need to find the right endpoint for their chosen sport
-    const endpoint = teamId
-      ? `${baseUrl}/${SPORT_CONFIG.sport}/${SPORT_CONFIG.league}/teams/${teamId}/athletes?limit=100`
-      : `${baseUrl}/${SPORT_CONFIG.sport}/${SPORT_CONFIG.league}/athletes?limit=100`;
+    const endpoint = `${baseESPNAPIUrl}/${SPORT_CONFIG.sport}/${SPORT_CONFIG.league}/seasons/${SPORT_CONFIG.season}/athletes?limit=1000`;
 
     console.log(`Fetching players from: ${endpoint}`);
 
@@ -57,8 +50,7 @@ async function fetchPlayersFromESPN(teamId?: string) {
  */
 async function fetchPlayerStats(playerId: string) {
   try {
-    const baseUrl = "https://site.web.api.espn.com/apis/common/v3/sports";
-    const endpoint = `${baseUrl}/${SPORT_CONFIG.sport}/${SPORT_CONFIG.league}/athletes/${playerId}/splits`;
+    const endpoint = `${espnPlayerStatsUrl}/${SPORT_CONFIG.sport}/${SPORT_CONFIG.league}/athletes/${playerId}/splits?season=${SPORT_CONFIG.season}`;
 
     console.log(`Fetching stats for player ${playerId}`);
 
@@ -84,24 +76,22 @@ async function fetchPlayerStats(playerId: string) {
  */
 async function storePlayersInSupabase(players: any[]) {
   try {
-    // Process and format the data for storage
     const formattedPlayers = players.map((player) => ({
-      player_id: player.id,
+      espn_player_id: player.id,
       name: player.fullName || `${player.firstName} ${player.lastName}`,
       team: player.team?.displayName || "Unknown",
       position: player.position?.abbreviation || "N/A",
       jersey_number: player.jersey || "N/A",
       height: player.height || null,
       weight: player.weight || null,
-      year: player.class?.year || null,
+      year: player.experience?.displayValue || null,
       image_url: player.headshot?.href || null,
-      // Add more fields as needed
     }));
 
     // Insert or update players in the database
     const { data, error } = await supabase
       .from("players")
-      .upsert(formattedPlayers, { onConflict: "player_id" });
+      .upsert(formattedPlayers, { onConflict: "espn_player_id" });
 
     if (error) {
       throw error;
@@ -119,39 +109,53 @@ async function storePlayersInSupabase(players: any[]) {
  * Stores player stats in Supabase
  * @param playerId - ESPN player ID
  * @param stats - Player stats data
+ * @param splitType - Either Home or Away
  */
-async function storePlayerStatsInSupabase(playerId: string, stats: any) {
+async function storePlayerStatsInSupabase({
+  playerId,
+  stats,
+  splitType
+}: {
+  playerId: string, 
+  stats: BasketballStatistics, 
+  splitType: SplitTypeEnum
+}) {
   try {
-    // Process and format the stats for storage
-    // This will vary depending on the sport and what stats are available
-    // This is just an example structure
     const formattedStats = {
-      player_id: playerId,
+      espn_player_id: playerId,
+      split_type: splitType,
       season: SPORT_CONFIG.season,
-      games_played: stats.gamesPlayed || 0,
-      points_per_game: stats.points?.avg || 0,
-      rebounds_per_game: stats.rebounds?.avg || 0,
-      assists_per_game: stats.assists?.avg || 0,
-      field_goal_percentage: stats.fieldGoalPercent?.avg || 0,
-      three_point_percentage: stats.threePointPercent?.avg || 0,
-      free_throw_percentage: stats.freeThrowPercent?.avg || 0,
-      // Add more stat fields as needed
-      updated_at: new Date().toISOString(),
+      games_played: Number(stats.gamesPlayed) || 0,
+      average_minutes: Number(stats.avgMinutes) || 0,
+      average_field_goals_made_to_attempted: stats.avgThreePointFieldGoalsMadeToAvgThreePointFieldGoalsAttempted || '',
+      field_goal_percentage: Number(stats.fieldGoalPct) || 0,
+      average_three_point_field_goals_made_to_attempted: stats.avgThreePointFieldGoalsMadeToAvgThreePointFieldGoalsAttempted || '',
+      three_point_field_goal_percentage: Number(stats.threePointFieldGoalPct) || 0,
+      free_throws_made_to_attempted_per_game: Number(stats.freeThrowsMadeToAttemptedPerGame) || 0,
+      free_throw_percentage: Number(stats.freeThrowPercentage) || 0,
+      average_offensive_rebounds: Number(stats.avgOffensiveRebounds) || 0,
+      average_defensive_rebounds: Number(stats.avgDefensiveRebounds) || 0,
+      average_rebounds: Number(stats.avgRebounds) || 0,
+      average_assists: Number(stats.avgAssists) || 0,
+      average_steals: Number(stats.avgSteals) || 0,
+      average_fouls: Number(stats.avgFouls) || 0,
+      average_turnovers: Number(stats.avgTurnovers) || 0,
+      average_points: Number(stats.avgPoints) || 0,
     };
 
     // Insert or update stats in the database
     const { data, error } = await supabase
       .from("player_stats")
-      .upsert(formattedStats, { onConflict: "player_id" });
+      .upsert(formattedStats, { onConflict: "espn_player_id,split_type,season" });
 
     if (error) {
       throw error;
     }
 
-    console.log(`Successfully stored stats for player ${playerId}`);
+    console.log(`Successfully stored stats for player ${playerId}, split_type ${splitType}`);
     return data;
   } catch (error) {
-    console.error(`Error storing stats for player ${playerId}:`, error);
+    console.error(`Error storing stats for player ${playerId}, split_type ${splitType}:`, error);
     throw error;
   }
 }
@@ -161,7 +165,7 @@ export async function GET() {
   try {
       // Fetch players from ESPN
       const playersData = await fetchPlayersFromESPN();
-      const players = playersData.athletes || [];
+      const players = playersData.items || [];
 
       if (players.length === 0) {
         console.warn("No players found");
@@ -171,21 +175,33 @@ export async function GET() {
       // Store players in Supabase
       await storePlayersInSupabase(players);
 
-      // For each player, fetch their stats and store them
-      // Note: In a production environment, you might want to implement
-      // rate limiting or batching to avoid overwhelming the ESPN API
-      for (const player of players.slice(0, 10)) {
-        // Limiting to 10 players for demonstration
-        try {
-          const statsData = await fetchPlayerStats(player.id);
-          await storePlayerStatsInSupabase(
-            player.id,
-            statsData.splits?.categories[0].stats || {}
-          );
-        } catch (error) {
-          console.error(`Error processing player ${player.id}:`, error);
-          // Continue with the next player
-        }
+      /**
+       * For each player, fetch their stats and store them
+       * NOTE: for basketball the player has "splits" for Home and Away aka their statistics are typically
+       * differentiated by how the players statistics are at home vs away
+       */
+      const batchLimit = 10
+      for (let i = 0; i < players.length; i += batchLimit) {
+        const batch = players.slice(i, i + batchLimit);
+
+        await Promise.all(batch.map(async (player: any) => {
+          try {
+            const statsData = await fetchPlayerStats(player.id);
+            if (statsData?.splitCategories[0]?.splits?.length > 0) {
+              for (const split of statsData.splitCategories[0].splits) {
+                const statistics = formatStatistics({ stats: split.stats });
+
+                await storePlayerStatsInSupabase({
+                  playerId: player.id,
+                  stats: statistics,
+                  splitType: split.displayName?.toUpperCase() == SplitTypeEnum.AWAY ? SplitTypeEnum.AWAY : SplitTypeEnum.HOME,
+                });
+              }
+            }
+          } catch (err) {
+            console.error(`Error processing player ${player.id}:`, err);
+          }
+        }));
       }
 
     return new Response(JSON.stringify({

diff --git a/apps/web/app/api/players/[id]/route.ts b/apps/web/app/api/players/[id]/route.ts
@@ -0,0 +1,12 @@
+import { NextResponse } from "next/server";
+import { getPlayerWithStats } from "../../actions";
+
+export async function GET(
+  req: Request,
+  { params }: { params: { id: string } }
+) {
+  const { id } = params;
+  const data = await getPlayerWithStats(id)
+
+  return NextResponse.json(data);
+}