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

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


Base = declarative_base()


class UserOrgRole(Base):
    __tablename__ = "user_org_role"

    user_id = Column(
        ForeignKey("user.id", ondelete="CASCADE"), primary_key=True
    )
    org_id = Column(ForeignKey("org.id", ondelete="CASCADE"), primary_key=True)
    # Does it make sense ?
    role_id = Column(
        ForeignKey("role.id", ondelete="CASCADE"), primary_key=True
    )

    # I do not know what the relationship could back populate, org or role ???
    user = relationship("User")
    org = relationship("Org")
    role = relationship("Role")


class User(Base):
    __tablename__ = "user"

    id = Column(Integer, primary_key=True)
    username = Column(Text, index=True, nullable=False)
    fullname = Column(Text, nullable=False)
    account_type = Column(Text, nullable=False)

    def __repr__(self):
        return (
            f"<User (username={self.username}, fullname={self.fullname}, "
            f"account_type={self.account_type})>"
        )


class Org(Base):
    __tablename__ = "org"

    id = Column(Integer, primary_key=True)
    name = Column(Text, index=True, nullable=False)
    slug = Column(Text, index=True, nullable=False)
    created_at = Column(DateTime)

    def __repr__(self):
        return (
            f"<Org (name={self.name}, slug={self.slug}, "
            f"created_at={self.created_at})>"
        )


class Role(Base):
    __tablename__ = "role"

    id = Column(Integer, primary_key=True)
    name = Column(Text, index=True, nullable=False)

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


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

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

    with Session(engine) as session:
        # create parent, append a child via association
        u1 = User(
            username="jlondon",
            fullname="Jack London",
            account_type="member",
        )
        o1 = Org(name="o1", slug="o1", created_at=datetime.utcnow())
        owner = Role(name="owner")

        uor1 = UserOrgRole()
        uor1.user = u1
        uor1.org = o1
        uor1.role = owner

        with session.begin():
            session.add(u1)
            session.add(o1)
            session.add(owner)

            session.add(uor1)