Database Queries with Slick

import slick.jdbc.PostgresProfile.api.*
import scala.concurrent.{Future, Await}
import scala.concurrent.ExecutionContext.Implicits.global
import scala.concurrent.duration.*

// Table definitions
case class User(id: Long, name: String, email: String, active: Boolean)
case class Post(id: Long, userId: Long, title: String, content: String)

class UsersTable(tag: Tag) extends Table[User](tag, "users"):
  def id     = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def name   = column[String]("name")
  def email  = column[String]("email")
  def active = column[Boolean]("active", O.Default(true))
  def * = (id, name, email, active).mapTo[User]

  // Indexes
  def emailIdx = index("idx_email", email, unique = true)

class PostsTable(tag: Tag) extends Table[Post](tag, "posts"):
  def id      = column[Long]("id", O.PrimaryKey, O.AutoInc)
  def userId  = column[Long]("user_id")
  def title   = column[String]("title")
  def content = column[String]("content")
  def * = (id, userId, title, content).mapTo[Post]

  // Foreign key
  def user = foreignKey("fk_user", userId, users)(_.id)

val users = TableQuery[UsersTable]
val posts = TableQuery[PostsTable]

object Database:
  val db = slick.jdbc.PostgresProfile.api.Database.forConfig("mydb")

  // Create tables
  def setup(): Future[Unit] =
    db.run((users.schema ++ posts.schema).createIfNotExists)

  // CRUD
  def createUser(name: String, email: String): Future[Long] =
    db.run(
      (users.map(u => (u.name, u.email))
        returning users.map(_.id)) += (name, email)
    )

  def findUser(id: Long): Future[Option[User]] =
    db.run(users.filter(_.id === id).result.headOption)

  def findByEmail(email: String): Future[Option[User]] =
    db.run(users.filter(_.email === email).result.headOption)

  def activeUsers(): Future[Seq[User]] =
    db.run(users.filter(_.active).sortBy(_.name).result)

  def updateUser(id: Long, name: String): Future[Int] =
    db.run(users.filter(_.id === id).map(_.name).update(name))

  def deactivate(id: Long): Future[Int] =
    db.run(users.filter(_.id === id).map(_.active).update(false))

  def deleteUser(id: Long): Future[Int] =
    db.run(users.filter(_.id === id).delete)

  // Queries
  def userPosts(userId: Long): Future[Seq[(User, Post)]] =
    val query = for
      u <- users if u.id === userId
      p <- posts if p.userId === u.id
    yield (u, p)
    db.run(query.result)

  def usersWithPostCount(): Future[Seq[(String, Int)]] =
    val query = users
      .joinLeft(posts).on(_.id === _.userId)
      .groupBy(_._1.name)
      .map { (name, group) =>
        (name, group.map(_._2).countDefined)
      }
    db.run(query.result)

  // Pagination
  def paginatedUsers(page: Int, pageSize: Int): Future[Seq[User]] =
    db.run(
      users.sortBy(_.id)
        .drop((page - 1) * pageSize)
        .take(pageSize)
        .result
    )

  // Transaction
  def createUserWithPost(name: String, email: String, title: String, content: String): Future[Unit] =
    val action = for
      userId <- (users.map(u => (u.name, u.email)) returning users.map(_.id)) += (name, email)
      _      <- posts.map(p => (p.userId, p.title, p.content)) += (userId, title, content)
    yield ()
    db.run(action.transactionally)