Schema Definition

class MetaData:
    """
    Collection of table definitions and schema information.
    
    Serves as a registry for all table definitions and provides
    methods for creating/dropping tables and managing schema.
    """
    
    def create_all(self, bind: Engine, checkfirst: bool = True) -> None:
        """Create all tables defined in this metadata."""
    
    def drop_all(self, bind: Engine, checkfirst: bool = True) -> None:
        """Drop all tables defined in this metadata."""
    
    def reflect(self, bind: Engine, schema: Optional[str] = None) -> None:
        """Load table definitions from existing database."""

class Table:
    """
    Represents a database table with columns and constraints.
    
    Usually not used directly with SQLModel, as table definitions
    are created automatically from SQLModel class definitions.
    """

BLANK_SCHEMA: str
    """Constant representing a blank schema name."""

# Metadata is automatically created by SQLModel
from sqlmodel import SQLModel

# Access the shared metadata
metadata = SQLModel.metadata

# Create all tables
metadata.create_all(engine)

# Drop all tables  
metadata.drop_all(engine)

# Reflect existing database schema
metadata.reflect(engine)
for table_name in metadata.tables:
    print(f"Table: {table_name}")

class Column:
    """
    Represents a database table column.
    
    Usually not used directly with SQLModel, as columns are
    defined through Field() specifications in model classes.
    """

class ColumnDefault:
    """Represents a default value for a column."""

class DefaultClause:
    """SQL expression used as a column default."""

class Computed:
    """
    Computed column expression (calculated by database).
    
    Represents columns whose values are computed by the database
    based on other columns or expressions.
    """

class FetchedValue:
    """
    Marker for values that are fetched after INSERT/UPDATE.
    
    Used for columns that have their values generated by
    the database (like auto-incrementing IDs or timestamps).
    """

class Identity:
    """
    Identity column specification for auto-incrementing values.
    
    Modern alternative to sequences, supported by newer databases.
    """

# These are typically used indirectly through Field() in SQLModel
class Hero(SQLModel, table=True):
    # Primary key with auto-increment
    id: Optional[int] = Field(default=None, primary_key=True)
    
    # Column with computed value (database-calculated)
    full_name: Optional[str] = Field(
        sa_column=Column(String, Computed("first_name || ' ' || last_name"))
    )
    
    # Column with default value
    created_at: datetime = Field(
        default_factory=datetime.utcnow,
        sa_column_kwargs={"server_default": func.now()}
    )

class Constraint:
    """Base class for all database constraints."""

class CheckConstraint(Constraint):
    """
    Check constraint that enforces a boolean condition.
    
    Ensures that all rows satisfy a specified condition.
    """

class ForeignKey:
    """
    Foreign key reference to another table's column.
    
    Creates a link between tables and enforces referential integrity.
    """

class ForeignKeyConstraint(Constraint):
    """
    Multi-column foreign key constraint.
    
    References multiple columns in another table.
    """

class PrimaryKeyConstraint(Constraint):
    """
    Primary key constraint for one or more columns.
    
    Ensures uniqueness and non-null values for primary key columns.
    """

class UniqueConstraint(Constraint):
    """
    Unique constraint ensuring distinct values.
    
    Prevents duplicate values across specified columns.
    """

# Foreign key relationships
class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(unique=True)  # Unique constraint
    
class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    team_id: Optional[int] = Field(
        default=None, 
        foreign_key="team.id"  # Foreign key constraint
    )
    
    # Check constraint (using sa_column_kwargs)
    age: Optional[int] = Field(
        default=None,
        sa_column_kwargs={
            "check": "age >= 0 AND age <= 200"  # Age must be reasonable
        }
    )

# Composite unique constraint (table-level)
class UserProfile(SQLModel, table=True):
    __table_args__ = (
        UniqueConstraint("user_id", "profile_type", name="unique_user_profile"),
    )
    
    id: Optional[int] = Field(default=None, primary_key=True)
    user_id: int
    profile_type: str
    data: dict

class Index:
    """
    Database index for improving query performance.
    
    Can be created on one or more columns to speed up
    searches and sorting operations.
    """

# Single column index
class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)  # Creates index on name column
    email: str = Field(unique=True)  # Unique constraint also creates index
    
# Composite index (table-level)
class SearchLog(SQLModel, table=True):
    __table_args__ = (
        Index("idx_user_timestamp", "user_id", "timestamp"),
    )
    
    id: Optional[int] = Field(default=None, primary_key=True)
    user_id: int
    search_term: str
    timestamp: datetime

class Sequence:
    """
    Database sequence for generating unique numeric values.
    
    Used primarily for auto-incrementing primary keys
    in databases that support sequences (PostgreSQL, Oracle).
    """

# Explicit sequence usage (advanced)
hero_id_seq = Sequence('hero_id_seq', start=1000)

class Hero(SQLModel, table=True):
    id: Optional[int] = Field(
        default=None,
        primary_key=True,
        sa_column_kwargs={"server_default": hero_id_seq.next_value()}
    )
    name: str

class DDL:
    """
    Raw DDL (Data Definition Language) statement.
    
    Allows execution of custom SQL DDL commands
    for advanced schema operations.
    """

# Custom DDL operations
from sqlalchemy import event, DDL

# Execute custom SQL after table creation
custom_ddl = DDL("""
    CREATE TRIGGER update_hero_timestamp 
    BEFORE UPDATE ON hero 
    FOR EACH ROW 
    EXECUTE FUNCTION update_timestamp()
""")

# Attach DDL to table creation
event.listen(Hero.__table__, 'after_create', custom_ddl)

# Single table inheritance
class Person(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str
    type: str = Field()  # Discriminator column
    
    __mapper_args__ = {
        "polymorphic_identity": "person",
        "polymorphic_on": "type"
    }

class Hero(Person, table=False):  # Inherits from Person table
    secret_name: Optional[str] = None
    
    __mapper_args__ = {
        "polymorphic_identity": "hero"
    }

class HeroTeamAssignment(SQLModel, table=True):
    hero_id: int = Field(foreign_key="hero.id", primary_key=True)
    team_id: int = Field(foreign_key="team.id", primary_key=True)
    role: str
    start_date: date

class AuditLog(SQLModel, table=True):
    __tablename__ = "audit_log"
    __table_args__ = {"schema": "audit"}
    
    id: Optional[int] = Field(default=None, primary_key=True)
    table_name: str
    operation: str
    timestamp: datetime
    user_id: int

# All SQLModel classes share the same metadata
assert Hero.metadata is SQLModel.metadata
assert Team.metadata is SQLModel.metadata

# Create all tables at once
SQLModel.metadata.create_all(engine)

# Drop all tables
SQLModel.metadata.drop_all(engine)

# Get information about defined tables
for table_name, table in SQLModel.metadata.tables.items():
    print(f"Table: {table_name}")
    for column in table.columns:
        print(f"  Column: {column.name} ({column.type})")