libraryDependencies += "io.scalaland" %% "ocdquery-core" % "0.5.0"

import java.time.LocalDate
import java.util.UUID

final case class TicketF[F[_], C[_]](
  id:      C[UUID],
  name:    F[String],
  surname: F[String],
  from:    F[String],
  to:      F[String],
  date:    F[LocalDate]
)

import cats.Id
import com.softwaremill.quicklens._
import doobie.h2.implicits._
import io.scalaland.ocdquery._

// only have to do it once!
val TicketRepo: Repo.EntityRepo[TicketF] = {
  // I have no idea why shapeless cannot find this Generic on its own :/
  // if you figure it out, please PR!!!
  implicit val ticketRead: doobie.Read[Repo.ForEntity[TicketF]#Entity] =
    QuasiAuto.read(shapeless.Generic[TicketF[Id, Id]])
    
  Repo.forEntity[TicketF](
    "tickets".tableName,
    // I suggest using quicklens or monocle's extension methods
    // as they are more reliable than .copy
    DefaultColumnNames.forEntity[TicketF].modify(_.from).setTo("from_".columnName)
  )
}

// build these in you services with type safety!

TicketRepo.insert(
  // no need to pass "empty" fields like "id = Unit"!
  Create.fromTuple(("John", "Smith", "London", "Paris", LocalDate.now))
).run

import io.scalaland.ocdquery.sql._ // common filter syntax like `=`, `<>`

TicketRepo.update.withFilter { columns =>
  (columns.name `=` "John") and (columns.surname `=` "Smith")
}(
  TicketRepo.emptyUpdate.modify(_.data).setTo(LocalDate.now)
).run

TicketRepo.fetch.withSort(_.name, Sort.Ascending).withLimit(5) {
 _.from `=` "London"
}.to[List]

TicketRepo.delete(_.id `=` deletedId).run

val joiner = TicketRepo
  .join(TicketRepo).on(_._1.id, _._2.id) // after .join() we have a tuple!
  .join(TicketRepo).on(_._2.id, _._3.id) // and now triple!
  .fetch.withSort(_._1.name, Sort.Ascending).withLimit(5) { columns =>
    columns._1.name `=` "John"
  }.to[List] // ConnectionIO[(Entity, Entity, Entity)]