brackt/app/models/team-score-events.ts

import { database } from "~/database/context";
import * as schema from "~/database/schema";
import { eq, inArray, desc, sql, and } from "drizzle-orm";
import { calculateBracketPoints, type ScoringRules } from "~/models/scoring-rules";
import { findParticipantNamesByIds } from "~/models/season-participant";
import { logger } from "~/lib/logger";

/**
 * Low-level primitive: writes one team_score_events row with an explicit pointsDelta.
 * participantIds are captured at write time so attribution is accurate regardless
 * of future match results.
 *
 * When matchId is provided (bracket sports), one row is written per match using
 * a partial unique index on (teamId, seasonId, matchId). When matchId is absent
 * (non-bracket fallback), one row per (teamId, seasonId, scoringEventId) is used.
 *
 * For bracket sports, prefer calling recordMatchScoreEvents instead — it computes
 * the exact per-season delta from scoring rules rather than requiring the caller
 * to supply a pre-computed pointsDelta.
 */
export async function recordTeamScoreEvent(
  params: {
    teamId: string;
    seasonId: string;
    scoringEventId: string;
    scoringEventName: string | null;
    sportName: string | null;
    participantIds: string[];
    pointsDelta: number;
    occurredAt?: Date;
    matchId?: string;
  },
  providedDb?: ReturnType<typeof database>
): Promise<void> {
  const db = providedDb || database();

  const values = {
    teamId: params.teamId,
    seasonId: params.seasonId,
    scoringEventId: params.scoringEventId,
    scoringEventName: params.scoringEventName,
    sportName: params.sportName,
    matchId: params.matchId ?? null,
    participantIds: params.participantIds,
    pointsDelta: params.pointsDelta.toString(),
    occurredAt: params.occurredAt ?? new Date(),
  };

  if (params.matchId) {
    // Per-match path: unique on (teamId, seasonId, matchId) WHERE matchId IS NOT NULL
    await db
      .insert(schema.teamScoreEvents)
      .values(values)
      .onConflictDoUpdate({
        target: [
          schema.teamScoreEvents.teamId,
          schema.teamScoreEvents.seasonId,
          schema.teamScoreEvents.matchId,
        ],
        targetWhere: sql`match_id IS NOT NULL`,
        set: {
          participantIds: params.participantIds,
          pointsDelta: params.pointsDelta.toString(),
        },
      });
  } else {
    // Event-level fallback: unique on (teamId, seasonId, scoringEventId) WHERE matchId IS NULL
    await db
      .insert(schema.teamScoreEvents)
      .values(values)
      .onConflictDoUpdate({
        target: [
          schema.teamScoreEvents.teamId,
          schema.teamScoreEvents.seasonId,
          schema.teamScoreEvents.scoringEventId,
        ],
        targetWhere: sql`match_id IS NULL`,
        set: {
          participantIds: params.participantIds,
          pointsDelta: params.pointsDelta.toString(),
        },
      });
  }
}

/**
 * Records a score event for every fantasy season that uses the given sports season,
 * attributing the exact point delta to the specific match winner.
 *
 * Called from processMatchResult immediately after upsertParticipantResult sets the
 * winner's new floor, so the delta is computed from the exact position change rather
 * than from aggregate before/after standings totals.
 *
 * oldFloor is 0 when the participant had no prior result row (first match win).
 */
export async function recordMatchScoreEvents(
  params: {
    participantId: string;
    sportsSeasonId: string;
    oldFloor: number;
    newFloor: number;
    bracketTemplateId: string | null;
    matchId: string;
    eventId: string;
    eventName: string | null;
  },
  providedDb?: ReturnType<typeof database>
): Promise<void> {
  const db = providedDb || database();

  // Fetch sport name for display (one query, shared across all seasons)
  const sportsSeason = await db.query.sportsSeasons.findFirst({
    where: eq(schema.sportsSeasons.id, params.sportsSeasonId),
    with: { sport: { columns: { name: true } } },
  });
  const sportName = sportsSeason?.sport?.name ?? null;

  // All fantasy seasons that include this sports season
  const seasonSports = await db.query.seasonSports.findMany({
    where: eq(schema.seasonSports.sportsSeasonId, params.sportsSeasonId),
    columns: { seasonId: true },
  });
  if (seasonSports.length === 0) return;

  const seasonIds = seasonSports.map((ss) => ss.seasonId);

  // Batch fetch: which team in each season drafted this participant?
  const picks = await db.query.draftPicks.findMany({
    where: and(
      inArray(schema.draftPicks.seasonId, seasonIds),
      eq(schema.draftPicks.participantId, params.participantId)
    ),
    columns: { teamId: true, seasonId: true },
  });
  if (picks.length === 0) return;

  const teamBySeasonId = new Map(picks.map((p) => [p.seasonId, p.teamId]));

  // Batch fetch scoring rules for all seasons in one query
  const seasonRows = await db.query.seasons.findMany({
    where: inArray(schema.seasons.id, seasonIds),
    columns: {
      id: true,
      pointsFor1st: true, pointsFor2nd: true, pointsFor3rd: true,
      pointsFor4th: true, pointsFor5th: true, pointsFor6th: true,
      pointsFor7th: true, pointsFor8th: true,
    },
  });
  const rulesBySeasonId = new Map<string, ScoringRules>(
    seasonRows.map((s) => [s.id, {
      pointsFor1st: s.pointsFor1st, pointsFor2nd: s.pointsFor2nd,
      pointsFor3rd: s.pointsFor3rd, pointsFor4th: s.pointsFor4th,
      pointsFor5th: s.pointsFor5th, pointsFor6th: s.pointsFor6th,
      pointsFor7th: s.pointsFor7th, pointsFor8th: s.pointsFor8th,
    }])
  );

  for (const seasonId of seasonIds) {
    const teamId = teamBySeasonId.get(seasonId);
    if (!teamId) continue;

    const rules = rulesBySeasonId.get(seasonId);
    if (!rules) continue;

    const delta =
      calculateBracketPoints(params.newFloor, rules, params.bracketTemplateId) -
      calculateBracketPoints(params.oldFloor, rules, params.bracketTemplateId);
    if (delta <= 0) continue;

    try {
      await recordTeamScoreEvent(
        {
          teamId,
          seasonId,
          scoringEventId: params.eventId,
          scoringEventName: params.eventName,
          sportName,
          participantIds: [params.participantId],
          pointsDelta: delta,
          matchId: params.matchId,
        },
        db
      );
    } catch (err) {
      logger.error(
        `[TeamScoreEvents] Failed to record match score event for team ${teamId} match ${params.matchId}:`,
        err
      );
    }
  }
}

export interface TeamScoreEventEntry {
  id: string;
  teamId: string;
  teamName: string;
  scoringEventId: string | null;
  scoringEventName: string | null;
  sportName: string | null;
  pointsDelta: string;
  occurredAt: Date;
  participants: Array<{ id: string; name: string }>;
}

/**
 * Returns the most recent scoring events for a league season, ordered by
 * occurredAt DESC. Participant names are fetched from the stored participantIds.
 */
export async function getRecentTeamScoreEvents(
  seasonId: string,
  limit = 10,
  providedDb?: ReturnType<typeof database>
): Promise<TeamScoreEventEntry[]> {
  const db = providedDb || database();

  const rows = await db.query.teamScoreEvents.findMany({
    where: eq(schema.teamScoreEvents.seasonId, seasonId),
    with: {
      team: { columns: { id: true, name: true } },
    },
    orderBy: [desc(schema.teamScoreEvents.occurredAt)],
    limit,
  });

  if (rows.length === 0) return [];

  // Batch-fetch participant names for all stored participant IDs
  const allParticipantIds = [
    ...new Set(rows.flatMap((r) => r.participantIds ?? [])),
  ];
  const participantNameById = await findParticipantNamesByIds(db, allParticipantIds);

  return rows.map((row) => ({
    id: row.id,
    teamId: row.teamId,
    teamName: row.team.name,
    scoringEventId: row.scoringEventId,
    scoringEventName: row.scoringEventName,
    sportName: row.sportName,
    pointsDelta: row.pointsDelta,
    occurredAt: row.occurredAt,
    participants: (row.participantIds ?? [])
      .map((id) => {
        const name = participantNameById.get(id);
        return name ? { id, name } : null;
      })
      .filter((p): p is { id: string; name: string } => p !== null),
  }));
}