Table Definitions and Schema

abstract class AbstractTable[T](val tableTag: Tag, val schemaName: Option[String], val tableName: String) extends Rep[T] {
  // Type member for table element type
  type TableElementType = T
  
  // Required default projection - maps between table columns and Scala type T
  def * : ProvenShape[T]
  
  // Column definition
  final def column[C](n: String, options: ColumnOption[C]*)(implicit tt: TypedType[C]): Column[C]
  
  // Constraint definitions
  def primaryKey[T](name: String, sourceColumns: T)(implicit shape: Shape[_ <: FlatShapeLevel, T, _, _]): PrimaryKey
  def foreignKey[P, PU, TT <: AbstractTable[_], U](name: String, sourceColumns: P, targetTable: TableQuery[TT])(implicit unpack: Shape[_ <: FlatShapeLevel, TT, U, _], unpackp: Shape[_ <: FlatShapeLevel, P, PU, _]): ForeignKeyQuery[TT, U]
  def index[T](name: String, on: T, unique: Boolean = false)(implicit shape: Shape[_ <: FlatShapeLevel, T, _, _]): Index
  
  // DDL generation
  def ddl: DDL
  
  // Table metadata
  def tableName: String
  def tableIdentitySymbol: TableIdentitySymbol
}

// Tag marks table instances 
sealed trait Tag {
  def taggedAs[U <: AbstractTable[_]]: Tag
}

// Column options
sealed trait ColumnOption[+T]

object ColumnOption {
  case object PrimaryKey extends ColumnOption[Nothing]
  case object AutoInc extends ColumnOption[Nothing]  
  case object NotNull extends ColumnOption[Nothing]
  case class Default[T](defaultValue: T) extends ColumnOption[T]
  case class DBType(typeName: String) extends ColumnOption[Nothing]
  case class Length(length: Int, varying: Boolean = true) extends ColumnOption[Nothing] 
}

// Convenient option aliases
object O {
  val PrimaryKey = ColumnOption.PrimaryKey
  val AutoInc = ColumnOption.AutoInc
  val NotNull = ColumnOption.NotNull
  def Default[T](v: T) = ColumnOption.Default(v)
  def DBType(typeName: String) = ColumnOption.DBType(typeName)
  def Length(length: Int, varying: Boolean = true) = ColumnOption.Length(length, varying)
}

import scala.slick.driver.H2Driver.simple._

// Simple table with basic columns
class Users(tag: Tag) extends Table[(Int, String, Option[String])](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name", O.NotNull) 
  def email = column[Option[String]]("email")
  
  // Default projection - required method
  def * = (id, name, email)
}

val users = TableQuery[Users]

case class User(id: Int, name: String, email: Option[String])

class Users(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name", O.NotNull)
  def email = column[Option[String]]("email")
  
  // Bidirectional mapping using case class companion object
  def * = (id, name, email) <> (User.tupled, User.unapply)
}

val users = TableQuery[Users]

// Usage with case classes
Database.forURL("...") withSession { implicit session =>
  users.ddl.create
  
  // Insert case class instances
  users += User(0, "John", Some("john@example.com"))
  users += User(0, "Jane", None)
  
  // Query returns case class instances
  val allUsers: List[User] = users.list
  val johnUsers: List[User] = users.filter(_.name === "John").list
}

import java.sql.{Date, Timestamp}
import java.util.UUID

class AdvancedTable(tag: Tag) extends Table[(UUID, String, Date, Timestamp, BigDecimal)](tag, "advanced") {
  def id = column[UUID]("id", O.PrimaryKey)
  def name = column[String]("name", O.Length(100))
  def birthDate = column[Date]("birth_date") 
  def createdAt = column[Timestamp]("created_at", O.Default(new Timestamp(System.currentTimeMillis)))
  def salary = column[BigDecimal]("salary", O.DBType("DECIMAL(10,2)"))
  
  def * = (id, name, birthDate, createdAt, salary)
}

// Custom column type example
sealed trait UserRole
case object Admin extends UserRole
case object Member extends UserRole  

implicit val userRoleColumnType = MappedColumnType.base[UserRole, String](
  {
    case Admin => "admin"
    case Member => "member" 
  },
  {
    case "admin" => Admin
    case "member" => Member
  }
)

class UsersWithRoles(tag: Tag) extends Table[(Int, String, UserRole)](tag, "users_with_roles") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def role = column[UserRole]("role") // Uses custom mapping
  
  def * = (id, name, role)
}

// Primary key definition
case class PrimaryKey(name: String, columns: IndexedSeq[Node]) extends Constraint

// In table definition
class Users(tag: Tag) extends Table[(Int, String)](tag, "users") {
  def id = column[Int]("id")
  def name = column[String]("name")
  
  // Single column primary key (most common)
  def pk = primaryKey("pk_users", id)
  
  // Composite primary key  
  def compositePk = primaryKey("pk_users_composite", (id, name))
  
  def * = (id, name)
}

// Alternative: Use O.PrimaryKey option
class SimpleUsers(tag: Tag) extends Table[(Int, String)](tag, "simple_users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc) // More common approach
  def name = column[String]("name")  
  def * = (id, name)
}

// Foreign key query type  
class ForeignKeyQuery[E <: AbstractTable[_], U](
  val fkName: String,
  val sourceTable: Node, 
  val targetTable: E,
  val sourceColumns: Node,
  val targetColumns: Node,
  val onUpdate: ForeignKeyAction,
  val onDelete: ForeignKeyAction
) extends Query[E, U, Seq]

// Foreign key actions
sealed abstract class ForeignKeyAction(val action: String)
object ForeignKeyAction {
  case object Cascade extends ForeignKeyAction("CASCADE")
  case object Restrict extends ForeignKeyAction("RESTRICT") 
  case object NoAction extends ForeignKeyAction("NO ACTION")
  case object SetNull extends ForeignKeyAction("SET NULL")
  case object SetDefault extends ForeignKeyAction("SET DEFAULT")
}

class Orders(tag: Tag) extends Table[(Int, Int, String)](tag, "orders") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def userId = column[Int]("user_id")
  def product = column[String]("product")
  
  // Foreign key constraint
  def user = foreignKey("fk_order_user", userId, users)(_.id, onUpdate=ForeignKeyAction.Restrict, onDelete=ForeignKeyAction.Cascade)
  
  def * = (id, userId, product)
}

val orders = TableQuery[Orders]

// Usage - foreign key provides query access
Database.forURL("...") withSession { implicit session =>
  // Join through foreign key
  val orderWithUser = for {
    order <- orders
    user <- order.user
  } yield (order.product, user.name)
}

// Index definition
case class Index(name: String, table: Node, on: IndexedSeq[Node], unique: Boolean) extends Constraint

class Products(tag: Tag) extends Table[(Int, String, String, Double)](tag, "products") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def category = column[String]("category") 
  def price = column[Double]("price")
  
  // Single column index
  def nameIndex = index("idx_product_name", name, unique = true)
  
  // Composite index
  def categoryPriceIndex = index("idx_category_price", (category, price), unique = false)
  
  // Unique constraint (unique index)
  def uniqueNameCategory = index("idx_unique_name_cat", (name, category), unique = true)
  
  def * = (id, name, category, price)
}

class Users(tag: Tag) extends Table[(Int, String, Option[String])](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)  
  def name = column[String]("name")
  def email = column[Option[String]]("email")
  def createdAt = column[Timestamp]("created_at")
  
  // Default projection
  def * = (id, name, email)
  
  // Alternative projections for specific use cases
  def forInsert = (name, email) // For insert without ID
  def summary = (id, name) // Just ID and name  
  def withTimestamp = (id, name, email, createdAt) // Include timestamp
}

val users = TableQuery[Users]

// Usage with different projections
Database.forURL("...") withSession { implicit session =>
  // Insert using forInsert projection
  (users returning users.map(_.id)) += ("John", Some("john@example.com"))
  
  // Query using summary projection  
  val userSummaries: List[(Int, String)] = users.map(_.summary).list
  
  // Query with timestamp
  val usersWithTime = users.map(_.withTimestamp).list
}

case class UserProfile(id: Int, fullName: String, contactInfo: String, isActive: Boolean)

class Users(tag: Tag) extends Table[UserProfile](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def firstName = column[String]("first_name") 
  def lastName = column[String]("last_name")
  def email = column[Option[String]]("email")
  def phone = column[Option[String]]("phone")
  def active = column[Boolean]("active", O.Default(true))
  
  // Complex projection with computed fields
  def * = (
    id, 
    firstName ++ " " ++ lastName, // Concatenated full name
    email.getOrElse("") ++ "|" ++ phone.getOrElse(""), // Combined contact
    active
  ) <> (UserProfile.tupled, UserProfile.unapply)
}

case class Email(value: String) {
  require(value.contains("@"), "Invalid email format")
}

case class ValidatedUser(id: Int, name: String, email: Email)

class Users(tag: Tag) extends Table[ValidatedUser](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def emailStr = column[String]("email")
  
  // Custom projection with validation
  def * = (id, name, emailStr) <> (
    // Construct with validation  
    { case (id, name, emailStr) => ValidatedUser(id, name, Email(emailStr)) },
    // Deconstruct
    { u: ValidatedUser => Some((u.id, u.name, u.email.value)) }
  )
}

// DDL (Data Definition Language) operations
trait DDL {
  def create(implicit session: Session): Unit
  def createIfNotExists(implicit session: Session): Unit
  def drop(implicit session: Session): Unit
  def dropIfExists(implicit session: Session): Unit
  
  // Combine DDL operations
  def ++(other: DDL): DDL
  
  // Get SQL statements
  def createStatements: Iterator[String]
  def dropStatements: Iterator[String]
}

val users = TableQuery[Users]
val orders = TableQuery[Orders]
val products = TableQuery[Products]

Database.forURL("...") withSession { implicit session =>
  // Create individual table
  users.ddl.create
  
  // Create multiple tables (handles dependencies)
  (users.ddl ++ orders.ddl ++ products.ddl).create
  
  // Create only if not exists  
  users.ddl.createIfNotExists
  
  // Drop tables
  (orders.ddl ++ products.ddl ++ users.ddl).drop // Reverse order for FK constraints
  
  // Get SQL for review
  val createSQL = users.ddl.createStatements.mkString(";\n")
  println(createSQL)
}

// Version 1 of schema
class UsersV1(tag: Tag) extends Table[(Int, String)](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def * = (id, name)
}

// Version 2 adds email column  
class UsersV2(tag: Tag) extends Table[(Int, String, Option[String])](tag, "users") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def email = column[Option[String]]("email") // New optional column
  def * = (id, name, email)
}

// Migration example
Database.forURL("...") withSession { implicit session =>
  // Check if email column exists, add if needed
  try {
    sql"ALTER TABLE users ADD COLUMN email VARCHAR(255)".execute
  } catch {
    case _: SQLException => // Column already exists
  }
}

// Base trait for common columns
trait BaseTable[T] { self: Table[T] =>
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc) 
  def createdAt = column[Timestamp]("created_at", O.Default(new Timestamp(System.currentTimeMillis)))
  def updatedAt = column[Timestamp]("updated_at", O.Default(new Timestamp(System.currentTimeMillis)))
}

// Concrete tables using the base
class Users(tag: Tag) extends Table[(Int, String, Timestamp, Timestamp)](tag, "users") with BaseTable[(Int, String, Timestamp, Timestamp)] {
  def name = column[String]("name")
  def * = (id, name, createdAt, updatedAt)
}

class Products(tag: Tag) extends Table[(Int, String, Double, Timestamp, Timestamp)](tag, "products") with BaseTable[(Int, String, Double, Timestamp, Timestamp)] {
  def name = column[String]("name") 
  def price = column[Double]("price")
  def * = (id, name, price, createdAt, updatedAt)
}

// Generic table for different ID types
abstract class EntityTable[ID, T](tag: Tag, tableName: String)(implicit idType: TypedType[ID]) 
  extends Table[T](tag, tableName) {
  def id = column[ID]("id", O.PrimaryKey)
}

// String ID table
class StringUsers(tag: Tag) extends EntityTable[String, (String, String)](tag, "string_users") {
  def name = column[String]("name")
  def * = (id, name)
}

// UUID ID table  
class UuidUsers(tag: Tag) extends EntityTable[UUID, (UUID, String)](tag, "uuid_users") {
  def name = column[String]("name") 
  def * = (id, name)
}

trait SoftDeleteTable[T] { self: Table[T] =>
  def deletedAt = column[Option[Timestamp]]("deleted_at")
  
  // Helper methods
  def isDeleted = deletedAt.isDefined
  def isActive = deletedAt.isEmpty
}

class Users(tag: Tag) extends Table[(Int, String, Option[Timestamp])](tag, "users") with SoftDeleteTable[(Int, String, Option[Timestamp])] {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name")
  def * = (id, name, deletedAt) 
}

val users = TableQuery[Users]

// Usage with soft delete
Database.forURL("...") withSession { implicit session =>
  // Query only active users
  val activeUsers = users.filter(_.isActive)
  
  // Soft delete (mark as deleted)
  users.filter(_.id === 1).map(_.deletedAt).update(Some(new Timestamp(System.currentTimeMillis)))
  
  // Include deleted users when needed
  val allUsers = users // Includes soft-deleted
}