Plain SQL

/**
 * SQL string interpolation context
 */
implicit class ActionBasedSQLInterpolation(val sc: StringContext) {
  /** Create a SQL query with interpolated parameters */
  def sql(args: Any*): SQLActionBuilder
  
  /** Create a SQL update/DDL statement with interpolated parameters */
  def sqlu(args: Any*): SQLActionBuilder
}

/**
 * Builder for SQL actions with parameter binding
 */
trait SQLActionBuilder {
  /** Execute as a query returning typed results */
  def as[R](implicit getResult: GetResult[R]): StreamingDBIO[Vector[R], R]
  
  /** Execute as an update/insert/delete returning affected row count */
  def asUpdate: DBIO[Int]
  
  /** Execute as DDL statement */
  def asDDL: DBIO[Unit]
}

import slick.jdbc.PostgresProfile.api._

// Simple parameterized query
val name = "Latte"
val maxPrice = 3.50
val query = sql"SELECT * FROM coffees WHERE name = $name AND price <= $maxPrice".as[Coffee]

// SQL update with parameters
val updatePrice = sqlu"UPDATE coffees SET price = price * 1.1 WHERE category = 'premium'"

// SQL insert
val newCoffeeId = 123
val coffeeName = "New Blend"
val price = 4.25
val insert = sqlu"INSERT INTO coffees (id, name, price) VALUES ($newCoffeeId, $coffeeName, $price)"

// DDL statements
val createTable = sqlu"""
  CREATE TABLE temp_analysis (
    id SERIAL PRIMARY KEY,
    coffee_name VARCHAR(100),
    analysis_date DATE
  )
"""

// Complex query with joins
val userId = 42
val complexQuery = sql"""
  SELECT u.name, c.name, o.quantity, o.order_date
  FROM users u
  JOIN orders o ON u.id = o.user_id  
  JOIN coffees c ON o.coffee_id = c.id
  WHERE u.id = $userId
  ORDER BY o.order_date DESC
""".as[(String, String, Int, Date)]

/**
 * Type class for mapping SQL result sets to Scala types
 */
trait GetResult[T] {
  /** Extract a value of type T from the current position in a PositionedResult */
  def apply(pr: PositionedResult): T
}

/**
 * Positioned result set reader
 */
trait PositionedResult {
  /** Read next column as specific type */
  def nextBoolean(): Boolean
  def nextByte(): Byte
  def nextShort(): Short
  def nextInt(): Int
  def nextLong(): Long
  def nextFloat(): Float
  def nextDouble(): Double
  def nextString(): String
  def nextDate(): Date
  def nextTime(): Time
  def nextTimestamp(): Timestamp
  def nextBigDecimal(): BigDecimal
  def nextBytes(): Array[Byte]
  def nextBlob(): Blob
  def nextClob(): Clob
  
  /** Read optional values */
  def nextBooleanOption(): Option[Boolean]
  def nextIntOption(): Option[Int]
  def nextStringOption(): Option[String]
  // ... other optional types
  
  /** Skip the next column */
  def skip(): Unit
}

object GetResult {
  /** Implicit GetResult instances for basic types */
  implicit val GetBoolean: GetResult[Boolean]
  implicit val GetInt: GetResult[Int]
  implicit val GetLong: GetResult[Long]
  implicit val GetString: GetResult[String]
  implicit val GetDate: GetResult[Date]
  implicit val GetTimestamp: GetResult[Timestamp]
  implicit val GetBigDecimal: GetResult[BigDecimal]
  
  /** GetResult for Option types */
  implicit def GetOption[T](implicit base: GetResult[T]): GetResult[Option[T]]
  
  /** GetResult for tuples */
  implicit def GetTuple2[T1, T2](implicit g1: GetResult[T1], g2: GetResult[T2]): GetResult[(T1, T2)]
  implicit def GetTuple3[T1, T2, T3](implicit g1: GetResult[T1], g2: GetResult[T2], g3: GetResult[T3]): GetResult[(T1, T2, T3)]
  // ... up to Tuple22
}

// Built-in type mappings
val stringQuery = sql"SELECT name FROM coffees".as[String]
val tupleQuery = sql"SELECT name, price FROM coffees".as[(String, Double)]
val optionQuery = sql"SELECT description FROM coffees".as[Option[String]]

// Custom case class mapping
case class CoffeeInfo(name: String, price: Double, category: Option[String])

implicit val getCoffeeInfo = GetResult(r => CoffeeInfo(
  r.nextString(),
  r.nextDouble(), 
  r.nextStringOption()
))

val coffeeQuery = sql"SELECT name, price, category FROM coffees".as[CoffeeInfo]

// Complex type mapping
case class OrderSummary(
  orderId: Int,
  customerName: String,
  coffeeName: String,
  quantity: Int,
  totalPrice: BigDecimal,
  orderDate: Timestamp
)

implicit val getOrderSummary = GetResult(r => OrderSummary(
  r.nextInt(),
  r.nextString(),
  r.nextString(),
  r.nextInt(),
  r.nextBigDecimal(),
  r.nextTimestamp()
))

val orderQuery = sql"""
  SELECT o.id, u.name, c.name, o.quantity, o.total_price, o.order_date
  FROM orders o
  JOIN users u ON o.user_id = u.id
  JOIN coffees c ON o.coffee_id = c.id
  WHERE o.order_date >= CURRENT_DATE - INTERVAL '30 days'
""".as[OrderSummary]

/**
 * Type class for binding Scala values to SQL parameters
 */
trait SetParameter[T] {
  /** Set a parameter value in a PositionedParameters */
  def apply(v: T, pp: PositionedParameters): Unit
}

/**
 * Positioned parameter setter
 */
trait PositionedParameters {
  /** Set parameters of various types */
  def setBoolean(v: Boolean): Unit
  def setInt(v: Int): Unit
  def setLong(v: Long): Unit
  def setString(v: String): Unit
  def setDate(v: Date): Unit
  def setTimestamp(v: Timestamp): Unit
  def setBigDecimal(v: BigDecimal): Unit
  def setBytes(v: Array[Byte]): Unit
  
  /** Set optional parameters */
  def setBooleanOption(v: Option[Boolean]): Unit
  def setIntOption(v: Option[Int]): Unit
  def setStringOption(v: Option[String]): Unit
  // ... other optional types
  
  /** Set null parameter */
  def setNull(sqlType: Int): Unit
}

object SetParameter {
  /** Implicit SetParameter instances for basic types */
  implicit val SetBoolean: SetParameter[Boolean]
  implicit val SetInt: SetParameter[Int] 
  implicit val SetLong: SetParameter[Long]
  implicit val SetString: SetParameter[String]
  implicit val SetDate: SetParameter[Date]
  implicit val SetTimestamp: SetParameter[Timestamp]
  implicit val SetBigDecimal: SetParameter[BigDecimal]
  
  /** SetParameter for Option types */
  implicit def SetOption[T](implicit base: SetParameter[T]): SetParameter[Option[T]]
}

// Basic parameter binding (automatic)
val minPrice = 2.50
val category = "espresso"
val query = sql"SELECT * FROM coffees WHERE price >= $minPrice AND category = $category"

// Custom parameter binding
case class PriceRange(min: Double, max: Double)

implicit val setPriceRange = SetParameter[PriceRange]((range, pp) => {
  pp.setDouble(range.min)
  pp.setDouble(range.max)
})

val priceRange = PriceRange(2.0, 4.0)
val rangeQuery = sql"SELECT * FROM coffees WHERE price BETWEEN ? AND ?" + priceRange

// Option parameter binding
val optionalCategory: Option[String] = Some("latte")
val categoryQuery = sql"SELECT * FROM coffees WHERE category = $optionalCategory OR $optionalCategory IS NULL"

// Date/time parameters
val startDate = Date.valueOf("2023-01-01")
val endDate = Timestamp.from(Instant.now())
val dateRangeQuery = sql"""
  SELECT * FROM orders 
  WHERE order_date >= $startDate AND order_date <= $endDate
"""

/**
 * Static query with parameter and result types
 */
case class StaticQuery[P, R](query: String)(implicit pconv: SetParameter[P], rconv: GetResult[R]) {
  /** Apply parameters to create an executable action */
  def apply(param: P): DBIO[Vector[R]]
  
  /** Create a streaming action */
  def stream(param: P): StreamingDBIO[Vector[R], R]
}

object StaticQuery {
  /** Create a static query with parameters */
  def query[P, R](sql: String)(implicit pconv: SetParameter[P], rconv: GetResult[R]): StaticQuery[P, R]
  
  /** Create a static update query */  
  def update[P](sql: String)(implicit pconv: SetParameter[P]): StaticQuery[P, Int]
  
  /** Create a query with no parameters */
  def queryNA[R](sql: String)(implicit rconv: GetResult[R]): StaticQuery[Unit, R]
  
  /** Create an update with no parameters */
  def updateNA(sql: String): StaticQuery[Unit, Int]
}

// Static query with parameters
val findCoffeesByPrice = StaticQuery.query[(Double, Double), Coffee](
  "SELECT * FROM coffees WHERE price BETWEEN ? AND ? ORDER BY name"
)

// Usage
val affordableCoffees = findCoffeesByPrice((2.0, 4.0))
val results = db.run(affordableCoffees)

// Static update query
val updateCoffeePrice = StaticQuery.update[Int](
  "UPDATE coffees SET price = price * 1.1 WHERE id = ?"
)

val updateAction = updateCoffeePrice(123)
val affectedRows = db.run(updateAction)

// Query with no parameters
val getAllUsers = StaticQuery.queryNA[User](
  "SELECT id, name, email FROM users ORDER BY name"
)

val allUsers = db.run(getAllUsers(()))

// Complex static query
val orderSummaryQuery = StaticQuery.query[(Int, Date), OrderSummary]("""
  SELECT o.id, u.name, c.name, o.quantity, o.total_price, o.order_date
  FROM orders o
  JOIN users u ON o.user_id = u.id  
  JOIN coffees c ON o.coffee_id = c.id
  WHERE u.id = ? AND o.order_date >= ?
  ORDER BY o.order_date DESC
""")

val userOrders = orderSummaryQuery((userId, startDate))

/**
 * Call database functions and procedures
 */
object DatabaseFunctions {
  /** Call a function returning a single value */
  def function[R](name: String, args: Any*)(implicit getResult: GetResult[R]): DBIO[R]
  
  /** Call a function returning multiple rows */
  def tableFunction[R](name: String, args: Any*)(implicit getResult: GetResult[R]): StreamingDBIO[Vector[R], R]
  
  /** Call a stored procedure */
  def procedure(name: String, args: Any*): DBIO[Int]
}

// Database-specific functions (PostgreSQL examples)
val currentTime = sql"SELECT CURRENT_TIMESTAMP".as[Timestamp].head
val randomValue = sql"SELECT RANDOM()".as[Double].head

// Stored procedures
val analyzeTable = sqlu"CALL analyze_coffee_sales(${startDate}, ${endDate})"

// Custom functions
val coffeeRanking = sql"""
  SELECT name, price, 
         RANK() OVER (ORDER BY price DESC) as price_rank
  FROM coffees
""".as[(String, Double, Int)]

// JSON operations (PostgreSQL)
val jsonData = """{"type": "espresso", "strength": "strong"}"""
val jsonQuery = sql"""
  SELECT * FROM coffees 
  WHERE metadata @> ${jsonData}::jsonb
""".as[Coffee]

// Window functions
val salesAnalysis = sql"""
  SELECT 
    DATE_TRUNC('month', order_date) as month,
    SUM(total_price) as monthly_total,
    LAG(SUM(total_price)) OVER (ORDER BY DATE_TRUNC('month', order_date)) as prev_month_total
  FROM orders
  GROUP BY DATE_TRUNC('month', order_date)
  ORDER BY month
""".as[(Date, BigDecimal, Option[BigDecimal])]

/**
 * Batch execution utilities
 */
object BatchOperations {
  /** Execute multiple statements in a batch */
  def batch[T](statements: Seq[String]): DBIO[Seq[Int]]
  
  /** Execute parameterized statements in a batch */
  def batchUpdate[P](sql: String, params: Seq[P])(implicit setParam: SetParameter[P]): DBIO[Seq[Int]]
}

// Batch updates
val priceUpdates = Seq(
  (1, 2.75),
  (2, 3.25), 
  (3, 1.95)
)

val batchUpdate = DBIO.sequence(priceUpdates.map { case (id, price) =>
  sqlu"UPDATE coffees SET price = $price WHERE id = $id"
})

// Batch inserts using plain SQL
val newCoffees = Seq(
  ("Americano", 2.50, "regular"),
  ("Macchiato", 3.75, "specialty"),
  ("Cold Brew", 3.25, "cold")
)

val batchInsert = DBIO.sequence(newCoffees.map { case (name, price, category) =>
  sqlu"INSERT INTO coffees (name, price, category) VALUES ($name, $price, $category)"
})

// Large batch operation with transaction
val largeBatchUpdate = (for {
  _ <- sqlu"BEGIN"
  results <- DBIO.sequence(priceUpdates.map { case (id, price) =>
    sqlu"UPDATE coffees SET price = $price WHERE id = $id"
  })
  _ <- sqlu"COMMIT"
} yield results).transactionally

implicit class SQLInterpolation(val sc: StringContext)
trait SQLActionBuilder {
  def as[R](implicit getResult: GetResult[R]): StreamingDBIO[Vector[R], R]
  def asUpdate: DBIO[Int]
  def asDDL: DBIO[Unit]
}

trait GetResult[T] {
  def apply(pr: PositionedResult): T
}

trait SetParameter[T] {
  def apply(v: T, pp: PositionedParameters): Unit
}

trait PositionedResult {
  def nextInt(): Int
  def nextString(): String
  def nextDouble(): Double
  def nextBoolean(): Boolean
  def nextDate(): Date
  def nextTimestamp(): Timestamp
  def nextBigDecimal(): BigDecimal
  // ... and optional variants
}

trait PositionedParameters {
  def setInt(v: Int): Unit
  def setString(v: String): Unit  
  def setDouble(v: Double): Unit
  def setBoolean(v: Boolean): Unit
  def setDate(v: Date): Unit
  def setTimestamp(v: Timestamp): Unit
  def setBigDecimal(v: BigDecimal): Unit
  // ... and optional variants
}

case class StaticQuery[P, R](query: String)(implicit pconv: SetParameter[P], rconv: GetResult[R]) {
  def apply(param: P): DBIO[Vector[R]]
  def stream(param: P): StreamingDBIO[Vector[R], R]
}