Profile System

trait BasicProfile extends Aliases {
  // Core types
  type Backend <: DatabaseComponent
  type Database = Backend#DatabaseDef
  type Session = Backend#SessionDef
  
  // Query language
  type SimpleQL <: SimpleQLApi
  val simple: SimpleQL
  
  // Implicit support
  type Implicits <: ImplicitsApi  
  val Implicit: Implicits
  
  // Schema description
  type SchemaDescription <: SchemaDescriptionApi
  val schemaDescription: SchemaDescription
  
  // Capabilities
  final val capabilities: Set[Capability]
  def hasCapability(cap: Capability): Boolean = capabilities.contains(cap)
  
  // DDL support
  type DDL <: DDLApi
  def buildTableSchemaDescription(table: Table[_]): DDL
}

// Basic driver implementation
trait BasicDriver extends BasicProfile

trait RelationalProfile extends BasicProfile {
  // Relational capabilities
  val capabilities = BasicProfile.capabilities ++ RelationalProfile.capabilities
  
  // Component traits
  trait RelationalTableComponent extends BasicProfile.TableComponent
  trait RelationalSequenceComponent  
  trait RelationalTypesComponent extends BasicProfile.TypesComponent
  trait RelationalKeysComponent
  
  // Extended types
  type Table[T] <: AbstractTable[T] with RelationalTableComponent#Table[T]
  type Sequence[T] <: RelationalSequenceComponent#Sequence[T]  
  type ColumnType[T] <: RelationalTypesComponent#ColumnType[T]
  type BaseColumnType[T] <: RelationalTypesComponent#BaseColumnType[T]
  
  // Keys and constraints
  type PrimaryKey <: RelationalKeysComponent#PrimaryKey
  type ForeignKey[TT <: AbstractTable[_], U] <: RelationalKeysComponent#ForeignKey[TT, U]
  type Index <: RelationalKeysComponent#Index
}

// Relational driver implementation  
trait RelationalDriver extends BasicDriver with RelationalProfile

trait SqlProfile extends RelationalProfile {
  // SQL capabilities
  val capabilities = RelationalProfile.capabilities ++ SqlProfile.capabilities
  
  // SQL-specific components
  trait SqlTableComponent extends RelationalProfile.RelationalTableComponent
  trait SqlSequenceComponent extends RelationalProfile.RelationalSequenceComponent
  trait SqlTypesComponent extends RelationalProfile.RelationalTypesComponent
  
  // Extended SQL support
  type SchemaDescription <: SqlSchemaDescriptionApi
  type QueryTemplate <: SqlQueryTemplateApi
  type InsertTemplate <: SqlInsertTemplateApi
  type UpdateTemplate <: SqlUpdateTemplateApi  
  type DeleteTemplate <: SqlDeleteTemplateApi
}

// SQL driver implementation
trait SqlDriver extends RelationalDriver with SqlProfile

trait JdbcProfile extends SqlProfile {
  // JDBC backend
  type Backend = JdbcBackend
  val backend: Backend = JdbcBackend
  
  // JDBC capabilities
  val capabilities = SqlProfile.capabilities ++ JdbcProfile.capabilities
  
  // JDBC components
  trait JdbcTableComponent extends SqlProfile.SqlTableComponent
  trait JdbcTypesComponent extends SqlProfile.SqlTypesComponent  
  trait JdbcInvokerComponent
  trait JdbcInsertInvokerComponent
  trait JdbcStatementBuilderComponent
  trait JdbcExecutorComponent
  trait JdbcModelComponent
  
  // JDBC-specific types
  type InsertInvoker[T] <: JdbcInsertInvokerComponent#InsertInvoker[T]
  type QueryInvoker[R] <: JdbcInvokerComponent#QueryInvoker[R]  
  type UpdateInvoker[T] <: JdbcInvokerComponent#UpdateInvoker[T]
  type DeleteInvoker <: JdbcInvokerComponent#DeleteInvoker
  type DDLInvoker <: JdbcInvokerComponent#DDLInvoker
}

// JDBC driver implementation
trait JdbcDriver extends SqlDriver with JdbcProfile

// Capability marker
case class Capability(name: String) {
  override def toString = name
}

// Capability checking
trait CapabilityApi {
  def capabilities: Set[Capability]
  def hasCapability(cap: Capability): Boolean
}

object BasicProfile {
  val capabilities = Set(
    Capability("basic.select"),
    Capability("basic.insert"), 
    Capability("basic.update"),
    Capability("basic.delete")
  )
}

object RelationalProfile {
  val capabilities = Set(
    Capability("relational.table"),
    Capability("relational.sequence"),
    Capability("relational.foreignKey"),
    Capability("relational.primaryKey"),
    Capability("relational.index"),
    Capability("relational.join"),
    Capability("relational.joinFull"), // May not be supported by all databases
    Capability("relational.union"),
    Capability("relational.groupBy"),
    Capability("relational.orderBy"),
    Capability("relational.reverse") // String reverse function
  )
}

object SqlProfile {
  val capabilities = Set(
    Capability("sql.sequence"),
    Capability("sql.sequenceMin"),
    Capability("sql.sequenceMax"), 
    Capability("sql.sequenceCycle"),
    Capability("sql.sequenceRestart"),
    Capability("sql.booleanLiteral"),
    Capability("sql.likeEscape")
  )
}

object JdbcProfile {
  val capabilities = Set(
    Capability("jdbc.batch"),
    Capability("jdbc.returnInsertKey"),
    Capability("jdbc.returnInsertOther"), // Return non-key columns from INSERT
    Capability("jdbc.createModel"),
    Capability("jdbc.insertOrUpdate"),
    Capability("jdbc.nullableNoDefault"),
    Capability("jdbc.supportsByte"),
    Capability("jdbc.supportsBoolean")
  )
}

import scala.slick.driver.H2Driver
import scala.slick.profile.Capability

// Check capabilities at runtime
val hasFullJoin = H2Driver.hasCapability(Capability("relational.joinFull"))
val supportsSequences = H2Driver.hasCapability(Capability("sql.sequence"))
val supportsBatch = H2Driver.hasCapability(Capability("jdbc.batch"))

// Conditional feature usage
def createSequence[P <: BasicProfile](profile: P, name: String) = {
  import profile.simple._
  
  if (profile.hasCapability(Capability("sql.sequence"))) {
    // Use native sequences
    Sequence[Int](name)
  } else {
    // Use table-based sequence emulation
    throw new UnsupportedOperationException("Sequences not supported")
  }
}

trait TableComponent { self: BasicProfile =>
  abstract class Table[T](val tableTag: Tag, val tableName: String) extends AbstractTable[T] {
    // Component-specific table functionality
    final def tableIdentitySymbol: TableIdentitySymbol
    def create_*(implicit session: Session): Unit
    def * : ProvenShape[T]
  }
}

trait RelationalTableComponent extends TableComponent { self: RelationalProfile =>
  abstract class Table[T](tag: Tag, tableName: String) extends super.Table[T](tag, tableName) {
    // Relational-specific functionality
    def foreignKey[P, PU, TT <: AbstractTable[_], U](name: String, sourceColumns: P, targetTable: TableQuery[TT]): ForeignKey[TT, U]
    def primaryKey[T](name: String, sourceColumns: T): PrimaryKey  
    def index[T](name: String, on: T, unique: Boolean = false): Index
  }
}

trait TypesComponent { self: BasicProfile =>
  // Base column type
  abstract class ColumnType[T] extends TypedType[T] {
    def sqlType: Int
    def sqlTypeName(sym: Option[FieldSymbol]): String  
  }
  
  abstract class BaseColumnType[T] extends ColumnType[T] with BaseTypedType[T] {
    val hasLiteralForm: Boolean
    def valueToSQLLiteral(value: T): String
  }
}

trait JdbcTypesComponent extends TypesComponent { self: JdbcProfile =>
  // JDBC-specific type mappings
  abstract class JdbcType[T] extends ColumnType[T] {
    def getValue(r: ResultSet, idx: Int): T
    def setValue(v: T, p: PreparedStatement, idx: Int): Unit  
    def updateValue(v: T, r: ResultSet, idx: Int): Unit
  }
}

trait InvokerComponent { self: BasicProfile =>
  // Query execution interface
  abstract class Invoker[+R] {
    def run()(implicit session: Session): R
  }
}

trait JdbcInvokerComponent extends InvokerComponent { self: JdbcProfile =>
  // JDBC query execution
  class QueryInvoker[R] extends Invoker[List[R]] {
    def list()(implicit session: Session): List[R]
    def first()(implicit session: Session): R
    def firstOption()(implicit session: Session): Option[R]  
    def foreach[U](f: R => U)(implicit session: Session): Unit
    def foldLeft[B](z: B)(op: (B, R) => B)(implicit session: Session): B
  }
  
  class UpdateInvoker[T] extends Invoker[Int] {
    def update(value: T)(implicit session: Session): Int
    def updateAll(values: Iterable[T])(implicit session: Session): Int
  }
  
  class InsertInvoker[T] extends Invoker[Unit] {
    def +=(value: T)(implicit session: Session): Unit
    def ++=(values: Iterable[T])(implicit session: Session): Unit  
    def insert(value: T)(implicit session: Session): Int
    def insertAll(values: T*)(implicit session: Session): Option[Int]
  }
  
  class DeleteInvoker extends Invoker[Int] {
    def delete()(implicit session: Session): Int
  }
}

// Write database-agnostic code
class UserService[P <: JdbcProfile](val profile: P) {
  import profile.simple._
  
  class Users(tag: Tag) extends Table[(Int, String, String)](tag, "users") {
    def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
    def name = column[String]("name")
    def email = column[String]("email")  
    def * = (id, name, email)
  }
  
  val users = TableQuery[Users]
  
  def createSchema()(implicit session: Session): Unit = {
    users.ddl.create
  }
  
  def addUser(name: String, email: String)(implicit session: Session): Int = {
    (users.map(u => (u.name, u.email)) returning users.map(_.id)) += (name, email)
  }
  
  def findUserById(id: Int)(implicit session: Session): Option[(Int, String, String)] = {
    users.filter(_.id === id).firstOption
  }
  
  def updateUserEmail(id: Int, newEmail: String)(implicit session: Session): Int = {
    users.filter(_.id === id).map(_.email).update(newEmail)
  }
  
  // Use capabilities for conditional features
  def createSequenceIfSupported()(implicit session: Session): Option[Sequence[Int]] = {
    if (profile.hasCapability(Capability("sql.sequence"))) {
      val seq = Sequence[Int]("user_id_seq")
      seq.ddl.create
      Some(seq)
    } else {
      None
    }
  }
}

// Use with different drivers
import scala.slick.driver.{H2Driver, MySQLDriver, PostgresDriver}

val h2Service = new UserService(H2Driver)
val mysqlService = new UserService(MySQLDriver)  
val postgresService = new UserService(PostgresDriver)

// Abstract over profile type
trait DatabaseAccess {
  val profile: JdbcProfile
  import profile.simple._
  
  // Define tables using the profile
  class Users(tag: Tag) extends Table[User](tag, "users") {
    def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
    def name = column[String]("name")
    def email = column[String]("email")
    def * = (id, name, email) <> (User.tupled, User.unapply)
  }
  
  val users = TableQuery[Users]
  val database: Database
  
  def withSession[T](f: Session => T): T = database.withSession(f)
}

// Concrete implementations
class H2DatabaseAccess extends DatabaseAccess {
  val profile = H2Driver
  import profile.simple._
  
  val database = Database.forURL("jdbc:h2:mem:test", driver="org.h2.Driver")
}

class PostgresDatabaseAccess(dbUrl: String, user: String, password: String) extends DatabaseAccess {
  val profile = PostgresDriver
  import profile.simple._
  
  val database = Database.forURL(dbUrl, user=user, password=password, driver="org.postgresql.Driver")
}

// Support multiple databases in the same application
trait DatabaseProvider {
  type Profile <: JdbcProfile
  val profile: Profile
  val database: profile.Backend#DatabaseDef
}

object DatabaseProvider {
  def apply(dbType: String, config: DatabaseConfig): DatabaseProvider = dbType match {
    case "h2" => new H2Provider(config)
    case "mysql" => new MySQLProvider(config) 
    case "postgres" => new PostgresProvider(config)
    case _ => throw new IllegalArgumentException(s"Unsupported database type: $dbType")
  }
}

class H2Provider(config: DatabaseConfig) extends DatabaseProvider {
  type Profile = H2Driver.type
  val profile = H2Driver
  val database = Database.forConfig("h2", config)
}

class MySQLProvider(config: DatabaseConfig) extends DatabaseProvider {
  type Profile = MySQLDriver.type  
  val profile = MySQLDriver
  val database = Database.forConfig("mysql", config)
}

class PostgresProvider(config: DatabaseConfig) extends DatabaseProvider {
  type Profile = PostgresDriver.type
  val profile = PostgresDriver  
  val database = Database.forConfig("postgres", config)
}

// Usage
class ApplicationService(provider: DatabaseProvider) {
  import provider.profile.simple._
  
  // Define schemas using the provider's profile
  class Users(tag: Tag) extends Table[User](tag, "users") {
    def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
    def name = column[String]("name") 
    def * = (id, name) <> (User.tupled, User.unapply)
  }
  
  val users = TableQuery[Users]
  
  def getUsers(): List[User] = {
    provider.database.withSession { implicit session =>
      users.list
    }
  }
}

// Implement features based on available capabilities
class AdvancedUserService[P <: JdbcProfile](profile: P) {
  import profile.simple._
  
  // Batch operations if supported
  def insertUsersBatch(userList: List[User])(implicit session: Session): Unit = {
    if (profile.hasCapability(Capability("jdbc.batch"))) {
      // Use efficient batch insert
      users ++= userList
    } else {
      // Fall back to individual inserts
      userList.foreach(user => users += user)
    }
  }
  
  // Use sequences if available
  def createUserSequence()(implicit session: Session): Option[Int] = {
    if (profile.hasCapability(Capability("sql.sequence"))) {
      val seq = Sequence[Int]("user_seq")
      seq.ddl.create
      Some(seq.next)
    } else {
      // Use auto-increment or manual ID generation
      None
    }
  }
  
  // Full outer joins if supported
  def getUsersWithAllOrders()(implicit session: Session): Query[(Users, Option[Orders]), _, Seq] = {
    if (profile.hasCapability(Capability("relational.joinFull"))) {
      users.joinFull(orders).on(_.id === _.userId)
    } else {
      // Fall back to left join
      users.joinLeft(orders).on(_.id === _.userId)
    }
  }
  
  // Return inserted keys if supported  
  def insertUserWithId(user: User)(implicit session: Session): Option[Int] = {
    if (profile.hasCapability(Capability("jdbc.returnInsertKey"))) {
      Some((users returning users.map(_.id)) += user)
    } else {
      users += user
      None
    }
  }
}