from typing import Optional

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Team(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    headquarters: str


class Hero(SQLModel, table=True):
    id: Optional[int] = Field(default=None, primary_key=True)
    name: str = Field(index=True)
    secret_name: str
    age: Optional[int] = Field(default=None, index=True)

    team_id: Optional[int] = Field(default=None, foreign_key="team.id")


sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"

engine = create_engine(sqlite_url, echo=True)


def create_db_and_tables():
    SQLModel.metadata.create_all(engine)


def create_heroes():
    with Session(engine) as session:
        team_preventers = Team(name="Preventers", headquarters="Sharp Tower")
        team_z_force = Team(name="Z-Force", headquarters="Sister Margaret’s Bar")
        session.add(team_preventers)
        session.add(team_z_force)
        session.commit()

        hero_deadpond = Hero(
            name="Deadpond", secret_name="<NAME>", team_id=team_z_force.id
        )
        hero_rusty_man = Hero(
            name="Rusty-Man",
            secret_name="<NAME>",
            age=48,
            team_id=team_preventers.id,
        )
        hero_spider_boy = Hero(name="Spider-Boy", secret_name="<NAME>")
        session.add(hero_deadpond)
        session.add(hero_rusty_man)
        session.add(hero_spider_boy)
        session.commit()

        session.refresh(hero_deadpond)
        session.refresh(hero_rusty_man)
        session.refresh(hero_spider_boy)

        print("Created hero:", hero_deadpond)
        print("Created hero:", hero_rusty_man)
        print("Created hero:", hero_spider_boy)


def select_heroes():
    with Session(engine) as session:
        statement = select(Hero, Team).where(Hero.team_id == Team.id)
        results = session.exec(statement)
        for hero, team in results:
            print("Hero:", hero, "Team:", team)


def main():
    create_db_and_tables()
    create_heroes()
    select_heroes()


if __name__ == "__main__":
    main()

sqlmodel.SQLModel.metadata.create_all sqlmodel.Session sqlmodel.Field sqlmodel.select sqlmodel.create_engine
[((625, 661), 'sqlmodel.create_engine', 'create_engine', (['sqlite_url'], {'echo': '(True)'}), '(sqlite_url, echo=True)\n', (638, 661), False, 'from sqlmodel import Field, Session, SQLModel, create_engine, select\n'), ((158, 195), 'sqlmodel.Field', 'Field', ([], {'default': 'None', 'primary_key': '(True)'}), '(default=None, primary_key=True)\n', (163, 195), False, 'from sqlmodel import Field, Session, SQLModel, create_engine, select\n'), ((212, 229), 'sqlmodel.Field', 'Field', ([], {'index': '(True)'}), '(index=True)\n', (217, 229), False, 'from sqlmodel import Field, Session, SQLModel, create_engine, select\n'), ((312, 349), 'sqlmodel.Field', 'Field', ([], {'default': 'None', 'primary_key': '(True)'}), '(default=None, primary_key=True)\n', (317, 349), False, 'from sqlmodel import Field, Session, SQLModel, create_engine, select\n'), ((366, 383), 'sqlmodel.Field', 'Field', ([], {'index': '(True)'}), '(index=True)\n', (371, 383), False, 'from sqlmodel import Field, Session, SQLModel, create_engine, select\n'), ((430, 461), 'sqlmodel.Field', 'Field', ([], {'default': 'None', 'index': '(True)'}), '(default=None, index=True)\n', (435, 461), False, 'from sqlmodel import Field, Session, SQLModel, create_engine, select\n'), ((492, 534), 'sqlmodel.Field', 'Field', ([], {'default': 'None', 'foreign_key': '"""team.id"""'}), "(default=None, foreign_key='team.id')\n", (497, 534), False, 'from sqlmodel import Field, Session, SQLModel, create_engine, select\n'), ((696, 732), 'sqlmodel.SQLModel.metadata.create_all', 'SQLModel.metadata.create_all', (['engine'], {}), '(engine)\n', (724, 732), False, 'from sqlmodel import Field, Session, SQLModel, create_engine, select\n'), ((765, 780), 'sqlmodel.Session', 'Session', (['engine'], {}), '(engine)\n', (772, 780), False, 'from sqlmodel import Field, Session, SQLModel, create_engine, select\n'), ((1830, 1845), 'sqlmodel.Session', 'Session', (['engine'], {}), '(engine)\n', (1837, 1845), False, 'from sqlmodel import Field, Session, SQLModel, create_engine, select\n'), ((1878, 1896), 'sqlmodel.select', 'select', (['Hero', 'Team'], {}), '(Hero, Team)\n', (1884, 1896), False, 'from sqlmodel import Field, Session, SQLModel, create_engine, select\n')]