Table Definitions

/**
 * Base class for database table definitions
 * @param tag Table tag for identification
 * @param tableName Name of the database table
 */
abstract class AbstractTable[T](tag: Tag, tableName: String)

/**
 * Concrete table class for defining database tables
 * @param tag Table tag for identification  
 * @param tableName Name of the database table
 */
class Table[T](tag: Tag, tableName: String) extends AbstractTable[T] {
  /** Default projection - defines which columns make up the table's row type */
  def * : ProvenShape[T]
}

/**
 * Table tag for identifying table instances
 */
final class Tag(val taggedAs: Any)

// Simple table definition
class Users(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)
}

// Table with case class mapping
final case class Coffee(name: String, price: Double)

class Coffees(tag: Tag) extends Table[Coffee](tag, "coffees") {
  def name = column[String]("name")
  def price = column[Double]("price")
  def * = (name, price).mapTo[Coffee]
}

/**
 * Define a table column with type T
 * @param name Column name in the database
 * @param options Column options like primary key, auto increment, etc.
 */
def column[T](name: String, options: ColumnOption[T]*): Rep[T]

/**
 * Represents a column or expression that can be used in queries
 */
trait Rep[T] {
  /** Column equality comparison */
  def ===[P2, R](e: P2)(implicit om: o.=:=[Rep[P2], Rep[Option[T]]], sh: Shape[_ <: FlatShapeLevel, Rep[P2], T, Rep[P2]]): Rep[Boolean]
  
  /** Column inequality comparison */
  def =!=[P2, R](e: P2)(implicit om: o.=:=[Rep[P2], Rep[Option[T]]], sh: Shape[_ <: FlatShapeLevel, Rep[P2], T, Rep[P2]]): Rep[Boolean]
}

/**
 * Column options for specifying constraints and behavior
 */
sealed trait ColumnOption[+T]

object O {
  /** Primary key constraint */
  case object PrimaryKey extends ColumnOption[Nothing]
  
  /** Auto-increment column */
  case object AutoInc extends ColumnOption[Nothing]
  
  /** Unique constraint */  
  case object Unique extends ColumnOption[Nothing]
  
  /** Not null constraint */
  case object NotNull extends ColumnOption[Nothing]
  
  /** Column length specification */
  case class Length(length: Int) extends ColumnOption[Nothing]
  
  /** Default value for column */
  case class Default[T](defaultValue: T) extends ColumnOption[T]
  
  /** Foreign key constraint */
  case class ForeignKey[T](name: String, sourceColumns: Any, targetTableQuery: Any, targetColumns: Any, onUpdate: ForeignKeyAction, onDelete: ForeignKeyAction) extends ColumnOption[T]
}

class Products(tag: Tag) extends Table[Product](tag, "products") {
  def id = column[Int]("id", O.PrimaryKey, O.AutoInc)
  def name = column[String]("name", O.Length(100))
  def description = column[Option[String]]("description")
  def price = column[BigDecimal]("price")
  def categoryId = column[Int]("category_id")
  def isActive = column[Boolean]("is_active", O.Default(true))
  def createdAt = column[Timestamp]("created_at", O.Default(new Timestamp(System.currentTimeMillis())))
  
  def * = (id, name, description, price, categoryId, isActive, createdAt).mapTo[Product]
}

/**
 * Represents a database table that can be queried
 */
abstract class TableQuery[E <: AbstractTable[_]] extends Query[E, E#TableElementType, Seq] {
  /** Get the table's schema DDL */
  def schema: SchemaDescription
  
  /** Insert a single row */
  def += (value: E#TableElementType): ProfileAction[Int, NoStream, Write]
  
  /** Insert multiple rows */
  def ++= (values: Iterable[E#TableElementType]): ProfileAction[Int, NoStream, Write]
  
  /** Insert or update a row */  
  def insertOrUpdate(value: E#TableElementType): ProfileAction[Int, NoStream, Write]
}

object TableQuery {
  /**
   * Create a TableQuery for the given table class
   */
  def apply[E <: AbstractTable[_]](cons: Tag => E): TableQuery[E]
}

// Create TableQuery instances
val users = TableQuery[Users]
val coffees = TableQuery[Coffees]
val products = TableQuery[Products]

// Insert operations
val insertUser = users += (0, "Alice")
val insertCoffee = coffees += Coffee("Latte", 2.50)
val insertMultiple = coffees ++= Seq(
  Coffee("Espresso", 1.80),
  Coffee("Cappuccino", 2.20)
)

/**
 * Schema description for DDL operations
 */
trait SchemaDescription {
  /** Create the schema if it doesn't exist */
  def createIfNotExists: ProfileAction[Unit, NoStream, Write]
  
  /** Create the schema */
  def create: ProfileAction[Unit, NoStream, Write]
  
  /** Drop the schema if it exists */
  def dropIfExists: ProfileAction[Unit, NoStream, Write]
  
  /** Drop the schema */  
  def drop: ProfileAction[Unit, NoStream, Write]
  
  /** Truncate all tables in the schema */
  def truncate: ProfileAction[Unit, NoStream, Write]
}

// Create schema for single table
val createUsersSchema = users.schema.create

// Create schema for multiple tables
val schema = users.schema ++ coffees.schema ++ products.schema
val createAllSchema = schema.create

// Schema management
val setupDb = DBIO.seq(
  schema.createIfNotExists,
  users += (0, "admin"),
  coffees ++= Seq(
    Coffee("Americano", 2.00),
    Coffee("Mocha", 3.50)
  )
)

/**
 * Primary key definition
 */
def primaryKey(name: String, columns: Any): PrimaryKey

/**
 * Foreign key definition  
 */
def foreignKey(name: String, sourceColumns: Any, targetTableQuery: Any)(targetColumns: Any => Any): ForeignKeyQuery[_, _]

/**
 * Index definition
 */
def index(name: String, columns: Any, unique: Boolean = false): Index

class Orders(tag: Tag) extends Table[Order](tag, "orders") {
  def id = column[Int]("id", O.AutoInc)
  def userId = column[Int]("user_id")
  def productId = column[Int]("product_id")
  def quantity = column[Int]("quantity")
  def orderDate = column[Date]("order_date")
  
  def * = (id, userId, productId, quantity, orderDate).mapTo[Order]
  
  // Composite primary key
  def pk = primaryKey("pk_orders", (userId, productId, orderDate))
  
  // Foreign key constraints
  def userFk = foreignKey("fk_order_user", userId, users)(_.id, onUpdate=ForeignKeyAction.Restrict, onDelete=ForeignKeyAction.Cascade)
  def productFk = foreignKey("fk_order_product", productId, products)(_.id)
  
  // Indexes
  def userIndex = index("idx_order_user", userId)
  def dateIndex = index("idx_order_date", orderDate)
  def uniqueUserProductIndex = index("idx_unique_user_product", (userId, productId), unique = true)
}

/**
 * Database sequence definition
 */
class Sequence[T](name: String, start: Long = 1, inc: Long = 1, min: Option[Long] = None, max: Option[Long] = None, cycle: Boolean = false)(implicit tpe: ColumnType[Long], integral: Integral[T])

/**
 * Sequence operations
 */
trait SequenceOps {
  /** Get the next value from the sequence */
  def nextValue: Rep[T]
  
  /** Get the current value of the sequence */  
  def currValue: Rep[T]
}

// Define sequences
val userIdSeq = Sequence[Long]("user_id_seq")
val orderIdSeq = Sequence[Long]("order_id_seq", start = 1000, inc = 1)

class UsersWithSequence(tag: Tag) extends Table[User](tag, "users") {
  def id = column[Long]("id", O.Default(userIdSeq.nextValue))
  def name = column[String]("name")
  def * = (id, name).mapTo[User]
}

// Use sequence in queries
val nextUserId = userIdSeq.nextValue
val insertWithSequence = users += User(0L, "Bob") // ID will be auto-generated

trait ColumnOption[+T]
case class Sequence[T](name: String, start: Long, inc: Long, min: Option[Long], max: Option[Long], cycle: Boolean)
trait SchemaDescription
case class PrimaryKey(name: String, columns: IndexedSeq[Node])  
case class ForeignKey(name: String, sourceTable: String, sourceColumns: Seq[String], targetTable: String, targetColumns: Seq[String], onUpdate: ForeignKeyAction, onDelete: ForeignKeyAction)
case class Index(name: String, table: String, on: Seq[(String, SortDirection)], unique: Boolean)

trait ForeignKeyAction
object ForeignKeyAction {
  case object Cascade extends ForeignKeyAction
  case object Restrict extends ForeignKeyAction  
  case object NoAction extends ForeignKeyAction
  case object SetNull extends ForeignKeyAction
  case object SetDefault extends ForeignKeyAction
}