"""
https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#association-object

The association object pattern is a variant on many-to-many: it’s used when
your association table contains additional columns beyond those which are
foreign keys to the left and right tables. Instead of using the
relationship.secondary argument, you map a new class directly to the
association table. The left side of the relationship references the association
object via one-to-many, and the association class references the right side
via many-to-one.

As always, the bidirectional version makes use of relationship.back_populates
or relationship.backref.

WARNING : A EXPLICITER

The association object pattern does not coordinate changes with a separate
relationship that maps the association table as “secondary”.
"""

from sqlalchemy import create_engine
from sqlalchemy import Column, ForeignKey, Integer, String
from sqlalchemy.orm import declarative_base, relationship
from sqlalchemy.orm.session import Session


Base = declarative_base()


class ParentChild(Base):
    __tablename__ = "parent_child"

    parent_id = Column(
        ForeignKey("parent.id", ondelete="CASCADE"), primary_key=True
    )
    child_id = Column(
        ForeignKey("child.id", ondelete="CASCADE"), primary_key=True
    )
    # First child, ...
    rank = Column(Integer)

    # and the association class references the right side via many-to-one
    # As always, the bidirectional version makes use of
    # relationship.back_populates or relationship.backref
    child = relationship("Child", back_populates="parents")
    parent = relationship("Parent", back_populates="children")


class Parent(Base):
    __tablename__ = "parent"

    id = Column(Integer, primary_key=True)
    name = Column(String)

    # The left side of the relationship references the association object via
    # one-to-many
    children = relationship("ParentChild", back_populates="parent")

    def __repr__(self):
        return f"<Parent (name={self.name}, children={self.children})>"


class Child(Base):
    __tablename__ = "child"

    id = Column(Integer, primary_key=True)
    name = Column(String)

    parents = relationship("ParentChild", back_populates="child")

    def __repr__(self):
        return f"<Child (name={self.name})>"


if __name__ == "__main__":
    engine = create_engine("sqlite:///association_object.db", echo=False)

    Base.metadata.drop_all(engine)
    Base.metadata.create_all(engine)

    # cf ../basic-session.1.4.py
    with Session(engine) as session:
        # Working with the association pattern in its direct form requires that
        # child objects are associated with an association instance before
        # being appended to the parent; similarly, access from parent to child
        # goes through the association object

        # create parent, append a child via association
        jack = Parent(name="Jack")
        john = Child(name="John")

        pc = ParentChild(rank=1)
        pc.child = john
        # jack.children.append(pc)
        pc.parent = jack

        with session.begin():
            session.add(jack)
            session.add(john)
            session.add(pc)

        # iterate through child objects via association, including association
        # attributes
        for assoc in jack.children:
            print(f"{assoc.rank=}")
            print(f"{assoc.child=}")

        print(jack)