# Data base set up. Copied from:
# https://sqlmodel.tiangolo.com/tutorial/relationship-attributes/back-populates/

from typing import List, Optional

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


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

    heroes: List["Hero"] = Relationship(back_populates="team")


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

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


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

engine = create_engine(sqlite_url, echo=False)


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")

        hero_deadpond = Hero(
            name="Deadpond", secret_name="Dive Wilson", team=team_z_force
        )
        hero_rusty_man = Hero(
            name="Rusty-Man", secret_name="Tommy Sharp", age=48, team=team_preventers
        )
        hero_spider_boy = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
        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)


        hero_spider_boy.team = team_preventers
        session.add(hero_spider_boy)
        session.commit()
        session.refresh(hero_spider_boy)


        hero_black_lion = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
        hero_sure_e = Hero(name="Princess Sure-E", secret_name="Sure-E")
        team_wakaland = Team(
            name="Wakaland",
            headquarters="Wakaland Capital City",
            heroes=[hero_black_lion, hero_sure_e],
        )
        session.add(team_wakaland)
        session.commit()
        session.refresh(team_wakaland)

        hero_tarantula = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
        hero_dr_weird = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
        hero_cap = Hero(
            name="Captain North America", secret_name="Esteban Rogelios", age=93
        )

        team_preventers.heroes.append(hero_tarantula)
        team_preventers.heroes.append(hero_dr_weird)
        team_preventers.heroes.append(hero_cap)
        session.add(team_preventers)
        session.commit()
        session.refresh(hero_tarantula)
        session.refresh(hero_dr_weird)
        session.refresh(hero_cap)


def main():
    create_db_and_tables()
    create_heroes()


main()


# Within session I can access heroes.
with Session(engine) as session:
    team = session.exec(select(Team)).first()
    print(team.heroes)

# [Hero(id=1, age=None, name='Deadpond', secret_name='Dive Wilson', team_id=1)]


# Outside of session I cannot.
with Session(engine) as session:
    team = session.exec(select(Team)).first()
    
print(team.heroes)

# ---------------------------------------------------------------------------
# DetachedInstanceError                     Traceback (most recent call last)
# /var/folders/38/ccm_21tj43v1ntn9ks9vyy740000gn/T/ipykernel_7846/3037874887.py in <module>
#       3     team = session.exec(select(Team)).first()
#       4 
# ----> 5 print(team.heroes)
# 
# ~/Library/Caches/pypoetry/virtualenvs/flask-webapp-VRI2aZnU-py3.9/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py in __get__(self, instance, owner)
#     479                     replace_context=err,
#     480                 )
# --> 481             return self.impl.get(state, dict_)
#     482 
#     483 
# 
# ~/Library/Caches/pypoetry/virtualenvs/flask-webapp-VRI2aZnU-py3.9/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py in get(self, state, dict_, passive)
#     924                     return PASSIVE_NO_RESULT
#     925 
# --> 926                 value = self._fire_loader_callables(state, key, passive)
#     927 
#     928                 if value is PASSIVE_NO_RESULT or value is NO_VALUE:
# 
# ~/Library/Caches/pypoetry/virtualenvs/flask-webapp-VRI2aZnU-py3.9/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py in _fire_loader_callables(self, state, key, # passive)
#     960             return callable_(state, passive)
#     961         elif self.callable_:
# --> 962             return self.callable_(state, passive)
#     963         else:
#     964             return ATTR_EMPTY
# 
# ~/Library/Caches/pypoetry/virtualenvs/flask-webapp-VRI2aZnU-py3.9/lib/python3.9/site-packages/sqlalchemy/orm/strategies.py in _load_for_state(self, state, passive, loadopt, # extra_criteria)
#     841                 return attributes.PASSIVE_NO_RESULT
#     842 
# --> 843             raise orm_exc.DetachedInstanceError(
#     844                 "Parent instance %s is not bound to a Session; "
#     845                 "lazy load operation of attribute '%s' cannot proceed"
# 
# DetachedInstanceError: Parent instance <Team at 0x1162a8400> is not bound to a Session; lazy load operation of attribute 'heroes' cannot proceed (Background on this error at: https://sqlalche.me/e/14/bhk3)

# Outside of session I cannot.
with Session(engine) as session:
    team = session.exec(select(Team)).first()
    
print(team.heroes)