"""
The Session.commit() operation unconditionally issues Session.flush() before emitting COMMIT on relevant database

There are only 2 scenarios I see when session.flush() could be call manually:
- `autoflush = False`: then flush might been needed if the object need to be query in the Session
- to get the AUTOINCREMENT ID which are EVIL (🔪🔪🔪🐱)

I wrote a few examples to illustrate this.
"""
from typing import List
from dataclasses import dataclass
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.orm import DeclarativeBase, sessionmaker, relationship, Mapped, mapped_column


engine = create_engine("sqlite:///:memory:", echo=True)

class Base(DeclarativeBase):
    pass

@dataclass
class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(255))
    fullname: Mapped[str] = mapped_column(String(255))
    nickname: Mapped[str] = mapped_column(String(255))
    companies: Mapped[List["Company"]] = relationship(back_populates="user")

@dataclass
class Company(Base):
    __tablename__ = 'company'

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(255))
    parent_id: Mapped[int] = mapped_column(ForeignKey("users.id"))
    user: Mapped["User"] = relationship(back_populates="companies")


Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)


with Session() as session:
    ed_user = User(name="ed", fullname="Ed Jones", nickname="edsnickname")
    session.add(ed_user)
    ed = session.query(User).filter(User.name == "ed").one() # would return `None` if autoflush was set to False
    print(ed) # User(id=1, name='ed', fullname='Ed Jones', nickname='edsnickname', companies=[])
    session.commit()

with Session() as session:
    ed = session.query(User).filter(User.name == "ed").one()
    ed.name = "Robert"
    session.commit()

with Session() as session:
    robert = session.query(User).filter(User.name == "Robert").one()
    print(robert) # User(id= '1', name='Robert', fullname='Ed Jones', nickname='edsnickname')

with Session() as session:
    new_johns = [User(name=f"john_{i}", fullname="John Smith", nickname="jojo") for i in range(10)]
    session.add_all(new_johns)
    session.commit()

with Session() as session:
    all_johns = session.query(User).filter(User.fullname == "John Smith").all()
    print(len(all_johns)) # 10
    print(all_johns) # [User(id= '2', name='john_0', fullname='John Smith', nickname='jojo'), User(id= '3', name='john_1', fullname='John Smith', nickname='jojo'), User(id= '4', name='john_2', fullname='John Smith', nickname='jojo'), User(id= '5', name='john_3', fullname='John Smith', nickname='jojo'), User(id= '6', name='john_4', fullname='John Smith', nickname='jojo'), User(id= '7', name='john_5', fullname='John Smith', nickname='jojo'), User(id= '8', name='john_6', fullname='John Smith', nickname='jojo'), User(id= '9', name='john_7', fullname='John Smith', nickname='jojo'), User(id= '10', name='john_8', fullname='John Smith', nickname='jojo'), User(id= '11', name='john_9', fullname='John Smith', nickname='jojo')]


with Session() as session:
    for i in range(10):
        laura = User(name=f"laura_{i}", fullname="Laura Glane", nickname="glagla")
        session.add(laura)
    for i in range(3):
        julia = User(name=f"julia_{i}", fullname="Julia Roberts", nickname="juju")
        session.add(laura)

    session.commit()

with Session() as session:
    all = session.query(User).all()

    for user in all:
        user.name = "Whocares"
        user.fullname = "Whocares"
        user.nickname = "Whocares"
    
    session.commit()

with Session() as session:
    all = session.query(User).all()
    print(all) # [User(id= '1', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '2', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '3', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '4', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '5', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '6', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '7', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '8', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '9', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '10', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '11', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '12', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '13', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '14', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '15', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '16', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '17', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '18', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '19', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '20', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '21', name='Whocares', fullname='Whocares', nickname='Whocares')]


"""
Exact same code handling all of it without closing the Session each time and with a single commit.
"""
with Session() as session:
    ed_user = User(name="ed", fullname="Ed Jones", nickname="edsnickname")
    session.add(ed_user)
    ed = session.query(User).filter(User.name == "ed").one()
    print(ed) # User(id= '1', name='ed', fullname='Ed Jones', nickname='edsnickname')

    ed = session.query(User).filter(User.name == "ed").one()
    ed.name = "Robert"

    robert = session.query(User).filter(User.name == "Robert").one()
    print(robert) # User(id= '1', name='Robert', fullname='Ed Jones', nickname='edsnickname')

    new_johns = [User(name=f"john_{i}", fullname="John Smith", nickname="jojo") for i in range(10)]
    session.add_all(new_johns)

    all_johns = session.query(User).filter(User.fullname == "John Smith").all()
    print(len(all_johns)) # 10
    print(all_johns) # [User(id= '2', name='john_0', fullname='John Smith', nickname='jojo'), User(id= '3', name='john_1', fullname='John Smith', nickname='jojo'), User(id= '4', name='john_2', fullname='John Smith', nickname='jojo'), User(id= '5', name='john_3', fullname='John Smith', nickname='jojo'), User(id= '6', name='john_4', fullname='John Smith', nickname='jojo'), User(id= '7', name='john_5', fullname='John Smith', nickname='jojo'), User(id= '8', name='john_6', fullname='John Smith', nickname='jojo'), User(id= '9', name='john_7', fullname='John Smith', nickname='jojo'), User(id= '10', name='john_8', fullname='John Smith', nickname='jojo'), User(id= '11', name='john_9', fullname='John Smith', nickname='jojo')]

    for i in range(10):
        laura = User(name=f"laura_{i}", fullname="Laura Glane", nickname="glagla")
        session.add(laura)
    for i in range(3):
        julia = User(name=f"julia_{i}", fullname="Julia Roberts", nickname="juju")
        session.add(laura)

    all = session.query(User).all()

    for user in all:
        user.name = "Whocares"
        user.fullname = "Whocares"
        user.nickname = "Whocares"
    
    all = session.query(User).all()
    print(all) # [User(id= '1', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '2', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '3', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '4', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '5', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '6', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '7', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '8', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '9', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '10', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '11', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '12', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '13', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '14', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '15', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '16', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '17', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '18', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '19', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '20', name='Whocares', fullname='Whocares', nickname='Whocares'), User(id= '21', name='Whocares', fullname='Whocares', nickname='Whocares')]

    session.commit()

"""
With relationships
"""
with Session() as session:
    user = User(name="Joe", fullname="Joe Louis", nickname="The Brown Bomber")
    company = Company(name="Walmart", user=user)
    session.add_all([user, company])
    session.commit()

with Session() as session:
    all_companies = session.query(Company).all()
    print(all_companies) # [Company(id=1, name='Walmart', parent_id=43, user=User(id=43, name='Joe', fullname='Joe Louis', nickname='The Brown Bomber', companies=[...]))]

with Session() as session:
    companies = [Company(name=f"Monsanto_{i}") for i in range(5)]
    user = User(name="Mohamed", fullname="Mohamed Ali", nickname="The Champ", companies=companies)
    session.add(user)
    session.commit()
    mohamed = session.query(User).filter(User.name == "Mohamed").one()
    print(mohamed.companies) # [Company(id=2, name='Monsanto_0', parent_id=44, user=User(id=44, name='Mohamed', fullname='Mohamed Ali', nickname='The Champ', companies=[...])), Company(id=3, name='Monsanto_1', parent_id=44, user=User(id=44, name='Mohamed', fullname='Mohamed Ali', nickname='The Champ', companies=[...])), Company(id=4, name='Monsanto_2', parent_id=44, user=User(id=44, name='Mohamed', fullname='Mohamed Ali', nickname='The Champ', companies=[...])), Company(id=5, name='Monsanto_3', parent_id=44, user=User(id=44, name='Mohamed', fullname='Mohamed Ali', nickname='The Champ', companies=[...])), Company(id=6, name='Monsanto_4', parent_id=44, user=User(id=44, name='Mohamed', fullname='Mohamed Ali', nickname='The Champ', companies=[...]))]

with Session() as session:
    users = session.query(User).all()

    for user in users:
        user.name = "Bob"
        user.fullname = "SpongeBob"
        user.nickname = "SquarePants"
        for company in user.companies:
            company.name = "SpongeBob Company"

    session.commit()

    users = session.query(User).all()

    print(users) # [User(id=1, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=2, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=3, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=4, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=5, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=6, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=7, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=8, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=9, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=10, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=11, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=12, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=13, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=14, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=15, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=16, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=17, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=18, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=19, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=20, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=21, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=22, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=23, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=24, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=25, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=26, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=27, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=28, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=29, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=30, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=31, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=32, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=33, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=34, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=35, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=36, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=37, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=38, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=39, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=40, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=41, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=42, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[]), User(id=43, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[Company(id=1, name='SpongeBob Company', parent_id=43, user=...)]), User(id=44, name='Bob', fullname='SpongeBob', nickname='SquarePants', companies=[Company(id=2, name='SpongeBob Company', parent_id=44, user=...), Company(id=3, name='SpongeBob Company', parent_id=44, user=...), Company(id=4, name='SpongeBob Company', parent_id=44, user=...), Company(id=5, name='SpongeBob Company', parent_id=44, user=...), Company(id=6, name='SpongeBob Company', parent_id=44, user=...)])]