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 numbered 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.isNotNull() &
              o.number.isNotIn(freshNumbers),
        ))
        .go();

    // Delete all special entries (null number) — they're re-inserted below.
    await (_database.delete(_database.courseOfferings)..where(
          (o) => o.semester.equals(semester.id) & o.number.isNull(),
        ))
        .go();

    for (final dto in dtos) {
      final courseCode = dto.course?.id;
      final courseNameZh = dto.course?.nameZh;

      if (courseNameZh == null) {
        _firebaseService.recordNonFatal(
          'Skipped offering with no name: '
          'number=${dto.number}, courseCode=$courseCode',
        );
        continue;
      }

      final offeringId = await _database.upsertCourseOffering(
        courseCode: courseCode,
        semesterId: semester.id,
        number: dto.number,
        nameZh: courseNameZh,
        nameEn: dto.course?.nameEn,
        credits: dto.credits,
        hours: dto.hours,
        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();

      // Teachers
      if (dto.teachers case final teachers?) {
        for (final t in teachers) {
          if (t case LocalizedRefDto(:final id?, :final nameZh?)) {
            final teacherSemesterId = await _database.upsertTeacherSemester(
              code: id,
              semesterId: semester.id,
              nameZh: nameZh,
              nameEn: t.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)'),
                .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()),
      ),
    );
  });
}