refreshCourseTable method

Implementation

Future<void> refreshCourseTable({required int semesterId}) async {
  final user = await _database.select(_database.users).getSingle();
  final semester = await (_database.select(
    _database.semesters,
  )..where((s) => s.id.equals(semesterId))).getSingle();

  final dtos = await _authRepository.withAuth(
    () => _courseService.getCourseTable(
      username: user.studentId,
      semester: (year: semester.year, term: semester.term),
    ),
    sso: [.courseService],
  );

  final freshNumbers = dtos.map((d) => d.number).nonNulls.toSet();

  // Deduplicate Crashlytics reports for unknown classroom prefixes,
  // since the same classroom can appear in multiple schedule slots.
  final reportedUnknownClassrooms = <String>{};

  // Persist to database
  await _database.transaction(() async {
    // Remove offerings no longer in the response (e.g. dropped courses).
    // Junction/child rows are cascade-deleted by FK constraints.
    await (_database.delete(_database.courseOfferings)..where(
          (o) =>
              o.semester.equals(semester.id) & o.number.isNotIn(freshNumbers),
        ))
        .go();

    for (final dto in dtos) {
      if (dto.number == null) continue;
      final courseId = dto.course?.id;
      final courseNameZh = dto.course?.nameZh;
      if (courseId == null || courseNameZh == null) {
        _firebaseService.recordNonFatal(
          'Skipped offering with incomplete course data: '
          'number=${dto.number}, courseId=$courseId, '
          'courseNameZh=$courseNameZh',
        );
        continue;
      }

      if (dto.credits == null || dto.hours == null) {
        _firebaseService.recordNonFatal(
          'Course $courseId missing credits/hours: '
          'credits=${dto.credits}, hours=${dto.hours}',
        );
      }

      final dbCourseId = await _database.upsertCourse(
        code: courseId,
        credits: dto.credits ?? 0,
        hours: dto.hours ?? 0,
        nameZh: courseNameZh,
        nameEn: dto.course?.nameEn,
      );

      final offeringId = await _database.upsertCourseOffering(
        courseId: dbCourseId,
        semesterId: semester.id,
        number: dto.number!,
        phase: dto.phase,
        status: dto.status,
        language: dto.language,
        remarks: dto.remarks,
        syllabusId: dto.syllabusId,
      );

      // Clear old junctions and schedules for this offering
      await (_database.delete(
        _database.courseOfferingTeachers,
      )..where((t) => t.courseOffering.equals(offeringId))).go();
      await (_database.delete(
        _database.courseOfferingClasses,
      )..where((t) => t.courseOffering.equals(offeringId))).go();
      await (_database.delete(
        _database.schedules,
      )..where((t) => t.courseOffering.equals(offeringId))).go();

      // Teacher
      if (dto.teacher case LocalizedRefDto(:final id?, :final nameZh?)) {
        final teacherSemesterId = await _database.upsertTeacherSemester(
          code: id,
          semesterId: semester.id,
          nameZh: nameZh,
          nameEn: dto.teacher?.nameEn,
        );
        await _database
            .into(_database.courseOfferingTeachers)
            .insert(
              CourseOfferingTeachersCompanion.insert(
                courseOffering: offeringId,
                teacherSemester: teacherSemesterId,
              ),
              mode: .insertOrIgnore,
            );
      }

      // Classes
      if (dto.classes case final classes?) {
        for (final c in classes) {
          if (c case LocalizedRefDto(:final id?, :final nameZh?)) {
            final classId = await _database.upsertClass(
              code: id,
              semesterId: semester.id,
              nameZh: nameZh,
              nameEn: c.nameEn,
            );
            await _database
                .into(_database.courseOfferingClasses)
                .insert(
                  CourseOfferingClassesCompanion.insert(
                    courseOffering: offeringId,
                    classEntity: classId,
                  ),
                  mode: .insertOrIgnore,
                );
          }
        }
      }

      // Schedules
      if (dto.schedule case final slots?) {
        for (final slot in slots) {
          int? classroomId;
          if (slot.classroom case (id: final id?, name: final name?)) {
            final nameEn = translateClassroomName(name);
            if (nameEn == null && reportedUnknownClassrooms.add(id)) {
              _firebaseService.crashlytics?.recordError(
                Exception('Unknown classroom prefix: $name (code: $id)'),
                StackTrace.current,
                fatal: false,
              );
            }
            classroomId = await _database.upsertClassroom(
              code: id,
              nameZh: name,
              nameEn: nameEn,
            );
          }
          await _database
              .into(_database.schedules)
              .insert(
                SchedulesCompanion.insert(
                  courseOffering: offeringId,
                  dayOfWeek: slot.day,
                  period: slot.period,
                  classroom: Value(classroomId),
                ),
                mode: .insertOrReplace,
              );
        }
      }
    }

    // Update the fetch timestamp on the semester
    await (_database.update(
      _database.semesters,
    )..where((s) => s.id.equals(semester.id))).write(
      SemestersCompanion(
        courseTableFetchedAt: Value(DateTime.now()),
      ),
    );
  });
}