data class Grade(
    val studentId: UUID,
    val value: Int? = null,
    val date: LocalDate? = null,
)

data class Course(
    val name: String,
    val grades: List<Grade> = emptyList(),
    val id: UUID? = UUID.randomUUID()
)

object CourseTable : IdTable<UUID>("courses") {
    override val id = uuid("id").entityId().uniqueIndex()
    val name = varchar("name", 512)
}

object GradeTable : IdTable<UUID>("grades") {
    override val id = uuid("id").entityId().uniqueIndex()
    val value = integer("value").nullable()
    val date = date("date").nullable()
    val course: Column<EntityID<UUID>> = 
      reference("course", CourseTable)
    val student: Column<EntityID<UUID>> = 
      reference("students", StudentTable)
}

fun UpdateBuilder<Number>.save(course: Course) {
    this[CourseTable.id] = course.id ?: UUID.randomUUID()
    this[CourseTable.name] = course.name
}

fun ResultRow.toCourse() = Course(
    id = this[CourseTable.id].value,
    name = this[CourseTable.name]
)

fun Course.addGrade(grade: Grade) {
    check(id != null)
    GradeTable.insert {
        it[id] = UUID.randomUUID()
        it[value] = grade.value
        it[date] = grade.date
        it[course] = this@addGrade.id
        it[student] = grade.studentId
    }
}

fun readCourse(id: UUID? = null): List<Course> {
    val query = (CourseTable innerJoin GradeTable)
        .selectAll()

    id?.let {
        query.andWhere {
            CourseTable.id eq it
        }
    }

return query.groupBy { it[CourseTable.id] }
  .map {
    val grades = it.value.map {
    Grade(
      it[GradeTable.student].value,
      it[GradeTable.value],
      it[GradeTable.date])}
    Course(
      it.value[0][CourseTable.name],
      grades,
      it.key.value)}

fun setGrade(courseId: UUID, studentId: UUID, newValue: Int) {
  GradeTable.update({
    GradeTable.course eq courseId and
    (GradeTable.student eq studentId)
  }) {
    it[value] = newValue
    it[date] = LocalDate.now()
  }
}

val courses = listOf("Math", "Phys", "History").map {
  Course(it)
}
CourseTable.batchInsert(courses) {
  save(it)
}

dbCourse.map { course ->
  dbStudents.map { student ->
    check(student.id != null)
    course.addGrade(Grade(student.id))
  }
}

val pennyId = dbStudents.find { it.name == "Penny" }?.id!!
val mathId = dbCourse.find { it.name == "Math" }?.id!!
setGrade(mathId, pennyId, 5)

println(readCourse(mathId))