Database Access with Doobie

import doobie.*
import doobie.implicits.*
import cats.effect.{IO, IOApp, Resource}
import cats.syntax.all.*
import doobie.hikari.HikariTransactor

case class User(id: Long, name: String, email: String, active: Boolean)
case class Order(id: Long, userId: Long, amount: Double, status: String)

object Database extends IOApp.Simple:

  // Connection
  val transactor: Resource[IO, HikariTransactor[IO]] =
    HikariTransactor.newHikariTransactor[IO](
      driverClassName = "org.postgresql.Driver",
      url = "jdbc:postgresql://localhost:5432/myapp",
      user = "postgres",
      pass = "password",
      connectEC = runtime.compute
    )

  // Queries
  def findUser(id: Long): Query0[User] =
    sql"SELECT id, name, email, active FROM users WHERE id = $id".query[User]

  def findActiveUsers: Query0[User] =
    sql"SELECT id, name, email, active FROM users WHERE active = true".query[User]

  def findUsersByName(pattern: String): Query0[User] =
    sql"SELECT id, name, email, active FROM users WHERE name LIKE $pattern".query[User]

  // Insert
  def insertUser(name: String, email: String): Update0 =
    sql"INSERT INTO users (name, email, active) VALUES ($name, $email, true)".update

  // Update
  def deactivateUser(id: Long): Update0 =
    sql"UPDATE users SET active = false WHERE id = $id".update

  // Delete
  def deleteUser(id: Long): Update0 =
    sql"DELETE FROM users WHERE id = $id".update

  // Batch insert
  def insertMany(users: List[(String, String)]): ConnectionIO[Int] =
    val sql = "INSERT INTO users (name, email) VALUES (?, ?)"
    Update[(String, String)](sql).updateMany(users)

  // Transaction
  def transfer(fromId: Long, toId: Long, amount: Double): ConnectionIO[Unit] =
    for
      _ <- sql"UPDATE accounts SET balance = balance - $amount WHERE user_id = $fromId".update.run
      _ <- sql"UPDATE accounts SET balance = balance + $amount WHERE user_id = $toId".update.run
      _ <- sql"INSERT INTO transfers (from_id, to_id, amount) VALUES ($fromId, $toId, $amount)".update.run
    yield ()

  // Join query
  def userOrders(userId: Long): Query0[(String, Double, String)] =
    sql"""
      SELECT u.name, o.amount, o.status
      FROM users u
      JOIN orders o ON u.id = o.user_id
      WHERE u.id = $userId
      ORDER BY o.amount DESC
    """.query[(String, Double, String)]

  // Fragment composition
  def search(nameOpt: Option[String], activeOnly: Boolean): Query0[User] =
    val base = fr"SELECT id, name, email, active FROM users WHERE 1=1"
    val nameFilter = nameOpt.map(n => fr"AND name LIKE $n").getOrElse(fr"")
    val activeFilter = if activeOnly then fr"AND active = true" else fr""
    (base ++ nameFilter ++ activeFilter).query[User]

  def run: IO[Unit] = transactor.use { xa =>
    for
      // Single result
      user <- findUser(1).option.transact(xa)
      _ <- IO.println(s"User: $user")

      // List results
      users <- findActiveUsers.to[List].transact(xa)
      _ <- IO.println(s"Active users: ${users.size}")

      // Insert with generated key
      id <- insertUser("Alice", "alice@test.com").withUniqueGeneratedKeys[Long]("id").transact(xa)
      _ <- IO.println(s"Inserted user ID: $id")

      // Transaction
      _ <- transfer(1, 2, 100.0).transact(xa)
      _ <- IO.println("Transfer complete")
    yield ()
  }