refreshSemesterRecords method

Implementation

Future<void> refreshSemesterRecords() async {
  final userId = (await _database.select(_database.users).getSingle()).id;
  final (semesters, gpas, rankings) = await _authRepository.withAuth(
    () async {
      final semestersFuture = _studentQueryService.getAcademicPerformance();
      final gpasFuture = _studentQueryService.getGpa();
      final rankingsFuture = _studentQueryService.getGradeRanking();
      return (semestersFuture, gpasFuture, rankingsFuture).wait;
    },
    sso: [.studentQueryService],
  );

  final gpaBySemester = <(int, int), double>{};
  for (final gpa in gpas) {
    if (gpa.semester case (year: final year?, term: final term?)) {
      gpaBySemester[(year, term)] = gpa.grandTotalGpa;
    }
  }

  final rankingsBySemester = <(int, int), List<GradeRankingEntryDto>>{};
  for (final ranking in rankings) {
    if (ranking.semester case (year: final year?, term: final term?)) {
      rankingsBySemester[(year, term)] = ranking.entries;
    }
  }

  // Collect all unique course codes and resolve them in parallel
  final courseCodes = semesters
      .expand((s) => s.scores)
      .map((s) => s.courseCode)
      .nonNulls
      .toSet();
  await courseCodes.map(_courseRepository.getCourse).wait;

  await _database.transaction(() async {
    final fetchedSemesterIds = <int>{};

    for (final semester in semesters) {
      if (semester.semester case (year: final year?, term: final term?)) {
        final semesterRow = await _database.getOrCreateSemester(year, term);
        final semesterId = semesterRow.id;
        fetchedSemesterIds.add(semesterId);
        final key = (year, term);

        final summaryId =
            (await _database
                    .into(_database.userSemesterSummaries)
                    .insertReturning(
                      UserSemesterSummariesCompanion.insert(
                        user: userId,
                        semester: semesterId,
                        average: Value(semester.average),
                        conduct: Value(semester.conduct),
                        totalCredits: Value(semester.totalCredits),
                        creditsPassed: Value(semester.creditsPassed),
                        note: Value(semester.note),
                        gpa: Value(gpaBySemester[key]),
                      ),
                      onConflict: DoUpdate(
                        (old) => UserSemesterSummariesCompanion(
                          average: Value(semester.average),
                          conduct: Value(semester.conduct),
                          totalCredits: Value(semester.totalCredits),
                          creditsPassed: Value(semester.creditsPassed),
                          note: Value(semester.note),
                          gpa: Value(gpaBySemester[key]),
                        ),
                        target: [
                          _database.userSemesterSummaries.user,
                          _database.userSemesterSummaries.semester,
                        ],
                      ),
                    ))
                .id;

        await (_database.delete(_database.scores)..where(
              (t) => t.user.equals(userId) & t.semester.equals(semesterId),
            ))
            .go();

        for (final score in semester.scores) {
          if (score.courseCode == null) continue;

          final course =
              await (_database.select(_database.courses)
                    ..where((c) => c.code.equals(score.courseCode!)))
                  .getSingleOrNull();
          if (course == null) {
            _firebaseService.recordNonFatal(
              'Score skipped: course ${score.courseCode} not found '
              'after pre-resolution (number=${score.number})',
            );
            continue;
          }

          final offeringId = switch (score.number) {
            final number? => (await (_database.select(
              _database.courseOfferings,
            )..where((o) => o.number.equals(number))).getSingleOrNull())?.id,
            _ => null,
          };

          await _database
              .into(_database.scores)
              .insert(
                ScoresCompanion.insert(
                  user: userId,
                  semester: semesterId,
                  course: course.id,
                  courseOffering: Value(offeringId),
                  score: Value(score.score),
                  status: Value(score.status),
                ),
              );
        }

        await (_database.delete(_database.userSemesterRankings)..where(
              (t) => t.summary.equals(summaryId),
            ))
            .go();

        for (final ranking in rankingsBySemester[key] ?? const []) {
          await _database
              .into(_database.userSemesterRankings)
              .insert(
                UserSemesterRankingsCompanion.insert(
                  summary: summaryId,
                  rankingType: ranking.type,
                  semesterRank: ranking.semesterRank,
                  semesterTotal: ranking.semesterTotal,
                  grandTotalRank: ranking.grandTotalRank,
                  grandTotalTotal: ranking.grandTotalTotal,
                ),
              );
        }
      }
    }

    // Remove scores for semesters no longer in the response
    if (fetchedSemesterIds.isEmpty) {
      await (_database.delete(
        _database.scores,
      )..where((t) => t.user.equals(userId))).go();
    } else {
      await (_database.delete(_database.scores)..where(
            (t) =>
                t.user.equals(userId) &
                t.semester.isNotIn(fetchedSemesterIds.toList()),
          ))
          .go();
    }

    await (_database.update(_database.users)
          ..where((u) => u.id.equals(userId)))
        .write(UsersCompanion(scoreDataFetchedAt: Value(DateTime.now())));
  });
}