example.sc

import usql.*
import usql.dao.*
import usql.profiles.H2Profile.given


import java.sql.{Connection, DriverManager}
import scala.util.{Try, Using}

Class.forName("org.h2.Driver")

val jdbcUrl = "jdbc:h2:mem:hello;DB_CLOSE_DELAY=-1"
given cp: ConnectionProvider with {
  override def withConnection[T](f: Connection ?=> T): T = {
    Using.resource(DriverManager.getConnection(jdbcUrl)) { c =>
      f(using c)
    }
  }
}

// Simple Actions

sql"CREATE TABLE person (id INT PRIMARY KEY, name TEXT)"
  .execute()

sql"INSERT INTO person (id, name) VALUES (${1}, ${"Alice"})"
  .execute()

sql"INSERT INTO person (id, name) VALUES (${2}, ${"Bob"})"
  .execute()

// Simple Queries

val all: Vector[(Int, String)] = sql"SELECT id, name FROM person".query.all[(Int, String)]()
println(s"All=${all}")

// Constant Parts of the query

val one: Option[(Int, String)] = sql"SELECT id, name FROM #${"person"} WHERE id = ${1}".query.one[(Int, String)]()
println(s"One=${one}")

// Inserts

sql"INSERT INTO person (id, name) VALUES(?, ?)".one((3, "Charly")).update.run()
sql"INSERT INTO person (id, name) VALUES(?, ?)"
  .batch(
    Seq(
      4 -> "Dave",
      5 -> "Emil"
    )
  )
  .run()

sql"SELECT COUNT(*) FROM person".query.one[Int]().get

// Reusable Parts
val select      = sql"SELECT id, name FROM person"
val selectAlice = (select + sql" WHERE id = ${1}").query.one[(Int, String)]()
println(s"Alice: ${selectAlice}")

// Transactions

Try {
  transaction {
    sql"INSERT INTO person(id, name) VALUES(${100}, ${"Duplicate"})".execute()
    sql"INSERT INTO person(id, name) VALUES(${100}, ${"Duplicate 2"})".execute()
  }
}

// Dao

case class Person(
    id: Int,
    name: String
) derives SqlTabular

object Person extends KeyedCrudBase[Int, Person] {
  override val keyColumn: SqlIdentifier = "id"

  override def keyOf(value: Person): Int = value.id

  override lazy val tabular: SqlTabular[Person] = summon
}

println(s"All Persons: ${Person.findAll()}")

Person.insert(Person(6, "Fritz"))
Person.update(Person(6, "Franziska"))
println(Person.findByKey(6)) // Person(6, Franziska)