Schema Modeling

case class Model(
  tables: Seq[Table],
  options: Set[ModelOption[_]] = Set.empty
) {
  // Convenience methods
  def tablesByName: Map[String, Table]
  def table(name: String): Option[Table] 
  def table(qualifiedName: QualifiedName): Option[Table]
  
  // Filter operations
  def filter(f: Table => Boolean): Model
  def filterSchemas(schemas: Set[String]): Model
  def filterTables(tableNames: Set[String]): Model
}

// Model options
trait ModelOption[T] {
  def value: T
}

case class Table(
  name: QualifiedName,
  columns: Seq[Column],
  primaryKey: Option[PrimaryKey],
  foreignKeys: Seq[ForeignKey] = Seq.empty,
  indices: Seq[Index] = Seq.empty, 
  options: Set[TableOption[_]] = Set.empty
) {
  // Convenience methods
  def column(name: String): Option[Column]
  def columnsByName: Map[String, Column]
  
  // Primary key helpers
  def primaryKeyColumns: Seq[Column]
  def hasPrimaryKey: Boolean
  
  // Foreign key helpers
  def foreignKeysByName: Map[String, ForeignKey]
  def referencingForeignKeys(otherTable: QualifiedName): Seq[ForeignKey]
  
  // Index helpers
  def indexesByName: Map[String, Index]
  def uniqueIndices: Seq[Index]
}

// Table options
trait TableOption[T] {
  def value: T  
}

// Common table options
object TableOption {
  case class Comment(comment: String) extends TableOption[String] {
    def value = comment
  }
  
  case class Engine(engine: String) extends TableOption[String] {
    def value = engine
  }
}

case class Column(
  name: String,
  table: QualifiedName, 
  tpe: String,
  nullable: Boolean,
  options: Set[ColumnOption[_]] = Set.empty
) {
  // Type helpers
  def jdbcType: Int
  def isNumeric: Boolean
  def isText: Boolean
  def isDate: Boolean
  def isBinary: Boolean
  
  // Constraint helpers  
  def isAutoInc: Boolean
  def hasDefault: Boolean
  def defaultValue: Option[Any]
  def length: Option[Int]
}

// Column options
trait ColumnOption[T] {
  def value: T
}

// Standard column options
object ColumnOption {
  case class Default(value: String) extends ColumnOption[String]
  case class SqlType(value: String) extends ColumnOption[String] 
  case class Length(value: Int) extends ColumnOption[Int]
  case object AutoInc extends ColumnOption[Boolean] {
    def value = true
  }
  case object PrimaryKey extends ColumnOption[Boolean] {
    def value = true  
  }
  case class Comment(comment: String) extends ColumnOption[String] {
    def value = comment
  }
}

case class QualifiedName(
  table: String,
  schema: Option[String] = None,
  catalog: Option[String] = None  
) {
  // String representations
  override def toString: String
  def quotedString: String
  def unquotedString: String
  
  // Comparison helpers
  def matches(other: QualifiedName): Boolean
  def matchesCaseInsensitive(other: QualifiedName): Boolean
}

object QualifiedName {
  def apply(table: String): QualifiedName = QualifiedName(table, None, None)
  def apply(schema: String, table: String): QualifiedName = QualifiedName(table, Some(schema), None)
}

case class PrimaryKey(
  name: Option[String],
  columns: Seq[String]
) {
  def columnSet: Set[String] = columns.toSet
  def isCompound: Boolean = columns.length > 1
}

case class ForeignKey(
  name: Option[String],
  referencingTable: QualifiedName,
  referencingColumns: Seq[String], 
  referencedTable: QualifiedName,
  referencedColumns: Seq[String],
  onUpdate: ForeignKeyAction,
  onDelete: ForeignKeyAction
) {
  def isCompound: Boolean = referencingColumns.length > 1
  def columnPairs: Seq[(String, String)] = referencingColumns.zip(referencedColumns)
}

// 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")
  
  // Parse from database metadata
  def fromString(action: String): ForeignKeyAction = action.toUpperCase match {
    case "CASCADE" => Cascade
    case "RESTRICT" => Restrict  
    case "NO ACTION" => NoAction
    case "SET NULL" => SetNull
    case "SET DEFAULT" => SetDefault
    case _ => NoAction
  }
}

case class Index(
  name: Option[String],
  table: QualifiedName,
  on: Seq[String], 
  unique: Boolean
) {
  def isCompound: Boolean = on.length > 1
  def columnSet: Set[String] = on.toSet
}

// Model creation from existing database
trait JdbcModelComponent { self: JdbcProfile =>
  
  class ModelBuilder(mTables: Seq[MTable], ignoreInvalidDefaults: Boolean = true) {
    def buildModel(): Model
    def buildTableModel(table: MTable): Table
  }
  
  // Create model from database metadata
  def createModel(implicit session: Session): Model
  def createModel(
    tables: Option[Seq[String]] = None,
    schemas: Option[Seq[String]] = None,  
    catalogs: Option[Seq[String]] = None,
    ignoreInvalidDefaults: Boolean = true
  )(implicit session: Session): Model
}

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

Database.forURL("jdbc:h2:mem:test", driver="org.h2.Driver") withSession { implicit session =>
  // Create some tables first
  val users = TableQuery[Users]
  val orders = TableQuery[Orders]
  
  users.ddl.create
  orders.ddl.create
  
  // Generate model from existing database
  val model: Model = H2Driver.createModel()
  
  println(s"Found ${model.tables.length} tables:")
  model.tables.foreach { table =>
    println(s"  ${table.name.table}: ${table.columns.length} columns")
  }
}

import scala.slick.jdbc.meta.MTable

Database.forURL("...") withSession { implicit session =>
  // Generate model for specific tables
  val model = H2Driver.createModel(
    tables = Some(Seq("users", "orders", "products")),
    schemas = Some(Seq("public")),
    ignoreInvalidDefaults = true
  )
  
  // Filter model after generation
  val publicTables = model.filterSchemas(Set("public"))
  val userTables = model.filter(_.name.table.startsWith("user"))
  
  // Access specific tables
  val usersTable = model.table("users")
  val ordersTable = model.table(QualifiedName("public", "orders"))
}

def generateTableCode(model: Model): String = {
  model.tables.map(generateTableClass).mkString("\n\n")
}

def generateTableClass(table: Table): String = {
  val className = table.name.table.capitalize
  val columns = table.columns.map(generateColumnDef).mkString("\n  ")
  val projection = generateProjection(table)
  
  s"""class ${className}(tag: Tag) extends Table[${generateRowType(table)}](tag, "${table.name.table}") {
  $columns
  
  def * = $projection
}

val ${table.name.table} = TableQuery[${className}]"""
}

def generateColumnDef(column: Column): String = {
  val scalaType = mapJdbcTypeToScala(column.tpe, column.nullable)
  val options = generateColumnOptions(column)
  
  s"""def ${column.name} = column[$scalaType]("${column.name}"${options})"""
}

def generateColumnOptions(column: Column): String = {
  val opts = scala.collection.mutable.ListBuffer[String]()
  
  if (column.options.exists(_.isInstanceOf[ColumnOption.PrimaryKey.type])) {
    opts += "O.PrimaryKey"
  }
  if (column.options.exists(_.isInstanceOf[ColumnOption.AutoInc.type])) {
    opts += "O.AutoInc"
  }
  column.options.find(_.isInstanceOf[ColumnOption.Default]).foreach { default =>
    opts += s"O.Default(${default.value})"
  }
  
  if (opts.nonEmpty) s", ${opts.mkString(", ")}" else ""
}

def mapJdbcTypeToScala(jdbcType: String, nullable: Boolean): String = {
  val baseType = jdbcType.toUpperCase match {
    case "INTEGER" | "INT" => "Int"
    case "BIGINT" | "LONG" => "Long" 
    case "VARCHAR" | "TEXT" => "String"
    case "BOOLEAN" | "BOOL" => "Boolean"
    case "DOUBLE" | "REAL" => "Double"
    case "DECIMAL" | "NUMERIC" => "BigDecimal"
    case "DATE" => "java.sql.Date"
    case "TIMESTAMP" => "java.sql.Timestamp"
    case _ => "String" // Default fallback
  }
  
  if (nullable) s"Option[$baseType]" else baseType
}

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

// Generate complete Slick code from existing database
def generateSlickCode(databaseUrl: String): String = {
  Database.forURL(databaseUrl, driver="org.h2.Driver") withSession { implicit session =>
    val model = H2Driver.createModel()
    
    val imports = """import scala.slick.driver.H2Driver.simple._
import scala.slick.lifted.{ProvenShape, ForeignKeyQuery}

"""
    
    val tableDefinitions = model.tables.map { table =>
      generateCompleteTableDef(table, model)
    }.mkString("\n\n")
    
    val tableQueries = model.tables.map { table =>
      s"val ${table.name.table} = TableQuery[${table.name.table.capitalize}]"
    }.mkString("\n")
    
    imports + tableDefinitions + "\n\n" + tableQueries
  }
}

def generateCompleteTableDef(table: Table, model: Model): String = {
  val className = table.name.table.capitalize
  val rowType = generateCaseClass(table)
  val columns = table.columns.map(col => generateColumnDef(col, table)).mkString("\n  ")
  val constraints = generateConstraints(table, model)
  val projection = generateProjection(table)
  
  s"""$rowType

class $className(tag: Tag) extends Table[$className](tag, "${table.name.table}") {
  $columns
  
  $constraints
  
  def * = $projection
}"""
}

def generateCaseClass(table: Table): String = {
  val className = table.name.table.capitalize
  val fields = table.columns.map { col =>
    s"${col.name}: ${mapJdbcTypeToScala(col.tpe, col.nullable)}"
  }.mkString(", ")
  
  s"case class $className($fields)"
}

def generateConstraints(table: Table, model: Model): String = {
  val constraints = scala.collection.mutable.ListBuffer[String]()
  
  // Primary key (if compound)
  table.primaryKey.filter(_.columns.length > 1).foreach { pk =>
    val cols = pk.columns.map(c => s"$c").mkString(", ")
    constraints += s"""def pk = primaryKey("${pk.name.getOrElse("pk_" + table.name.table)}", ($cols))"""
  }
  
  // Foreign keys
  table.foreignKeys.foreach { fk =>
    val sourceCols = fk.referencingColumns.mkString(", ")
    val targetTable = fk.referencedTable.table
    val targetCols = fk.referencedColumns.mkString(", ")
    
    constraints += s"""def ${fk.name.getOrElse("fk_" + targetTable)} = foreignKey("${fk.name.getOrElse("")}", ($sourceCols), $targetTable)(_.$targetCols)"""
  }
  
  // Indices
  table.indices.foreach { idx =>
    val cols = idx.on.mkString(", ")
    val unique = if (idx.unique) ", unique = true" else ""
    constraints += s"""def ${idx.name.getOrElse("idx_" + idx.on.head)} = index("${idx.name.getOrElse("")}", ($cols)$unique)"""
  }
  
  constraints.mkString("\n  ")
}

def generateProjection(table: Table): String = {
  val columnRefs = table.columns.map(_.name).mkString(", ")
  s"($columnRefs) <> (${table.name.table.capitalize}.tupled, ${table.name.table.capitalize}.unapply)"
}

// Usage example
val generatedCode = generateSlickCode("jdbc:h2:~/mydb")
println(generatedCode)

def analyzeSchema(model: Model): SchemaAnalysis = {
  SchemaAnalysis(
    tableCount = model.tables.length,
    totalColumns = model.tables.map(_.columns.length).sum,
    tablesWithPrimaryKeys = model.tables.count(_.primaryKey.isDefined),
    foreignKeyCount = model.tables.map(_.foreignKeys.length).sum,
    indexCount = model.tables.map(_.indices.length).sum,
    relationships = findRelationships(model)
  )
}

case class SchemaAnalysis(
  tableCount: Int,
  totalColumns: Int, 
  tablesWithPrimaryKeys: Int,
  foreignKeyCount: Int,
  indexCount: Int,
  relationships: Seq[TableRelationship]
)

case class TableRelationship(
  fromTable: String,
  toTable: String, 
  relationshipType: String, // "one-to-many", "many-to-many", etc.
  foreignKey: ForeignKey
)

def findRelationships(model: Model): Seq[TableRelationship] = {
  model.tables.flatMap { table =>
    table.foreignKeys.map { fk =>
      TableRelationship(
        fromTable = table.name.table,
        toTable = fk.referencedTable.table,
        relationshipType = determineRelationshipType(table, fk, model),
        foreignKey = fk
      )
    }
  }
}

def compareModels(oldModel: Model, newModel: Model): ModelDiff = {
  val addedTables = newModel.tables.filterNot(t => oldModel.tablesByName.contains(t.name.table))
  val removedTables = oldModel.tables.filterNot(t => newModel.tablesByName.contains(t.name.table))
  val modifiedTables = findModifiedTables(oldModel, newModel)
  
  ModelDiff(
    addedTables = addedTables,
    removedTables = removedTables, 
    modifiedTables = modifiedTables
  )
}

case class ModelDiff(
  addedTables: Seq[Table],
  removedTables: Seq[Table],
  modifiedTables: Seq[TableDiff]
)

case class TableDiff(
  tableName: String,
  addedColumns: Seq[Column],
  removedColumns: Seq[Column],
  modifiedColumns: Seq[ColumnDiff],
  addedConstraints: Seq[String],
  removedConstraints: Seq[String]
)

case class ColumnDiff(
  columnName: String,
  oldType: String,
  newType: String,
  oldNullable: Boolean, 
  newNullable: Boolean
)

def validateModel(model: Model): Seq[ValidationError] = {
  val errors = scala.collection.mutable.ListBuffer[ValidationError]()
  
  model.tables.foreach { table =>
    // Check for primary key
    if (table.primaryKey.isEmpty) {
      errors += ValidationError(s"Table ${table.name.table} has no primary key")
    }
    
    // Check foreign key references  
    table.foreignKeys.foreach { fk =>
      val referencedTable = model.table(fk.referencedTable)
      if (referencedTable.isEmpty) {
        errors += ValidationError(s"Foreign key ${fk.name} references non-existent table ${fk.referencedTable.table}")
      } else {
        // Check referenced columns exist
        val refTable = referencedTable.get
        fk.referencedColumns.foreach { colName =>
          if (!refTable.columnsByName.contains(colName)) {
            errors += ValidationError(s"Foreign key ${fk.name} references non-existent column $colName in table ${fk.referencedTable.table}")
          }
        }
      }
    }
    
    // Check for naming conventions
    if (!table.name.table.matches("[a-z][a-z0-9_]*")) {
      errors += ValidationError(s"Table ${table.name.table} does not follow naming convention")
    }
    
    table.columns.foreach { column =>
      if (!column.name.matches("[a-z][a-z0-9_]*")) {
        errors += ValidationError(s"Column ${column.name} in table ${table.name.table} does not follow naming convention")
      }
    }
  }
  
  errors.toSeq
}

case class ValidationError(message: String)

// Export model to various formats
def exportModelToJson(model: Model): String = {
  // Convert to JSON representation
  // (implementation would use JSON library)
  ???
}

def exportModelToXML(model: Model): String = {
  // Convert to XML schema
  // (implementation would use XML library)  
  ???
}

def exportModelToGraphviz(model: Model): String = {
  val relationships = findRelationships(model)
  
  val nodes = model.tables.map { table =>
    s"""  "${table.name.table}" [label="${table.name.table}\\n${table.columns.length} columns"];"""
  }.mkString("\n")
  
  val edges = relationships.map { rel =>
    s"""  "${rel.fromTable}" -> "${rel.toTable}" [label="${rel.relationshipType}"];"""
  }.mkString("\n")
  
  s"""digraph schema {
$nodes

$edges
}"""
}