# Building  1-to-Many  Relationship
# https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#one-to-many
# back_populates()  targets are  class attribute names.
# The example is made clearer using my data type prefix notation  and  specifically  o_  as class attributes (but  o_  are not table columns!)
# Note the difference between  parent_id (integer)  and  o_parent_obj (sqla object)
# Note:  l_children_list  is a list of sqla objects.
# Read back_populates() as  "this relationship back populates as the following class attribute on the opposing class"
class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    l_children_list = relationship("Child", back_populates="o_parent_obj")   # not a table column

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'))
    o_parent_obj = relationship("Parent", back_populates="l_children_list")   # not a table column



# Building  1-to-1  Relationship
# https://docs.sqlalchemy.org/en/14/orm/basic_relationships.html#one-to-one
# Two changes:
# To convert this to “one-to-one”, the “one-to-many” or “collection” side is converted into a scalar relationship using the uselist=False flag.
# Add unique constraint (optional)
#
# Child.o_parent_obj  will be 1-to-1 because there is only 1 value in the  Child.parent_id  column. 
#
# Parent.o_first_child  will be 1-to-1 at the ORM level, because ORM forces the value to be a scalar via  uselist=False  flag.
# Tip in docs: 1-to-1 enforcement at the db level is also possible and can be considered:
# This is a db design decision because it's a trade off: it provides referential integrity at the db level but at the cost of an additional db index.
# Enforce 1-to-1 for  Parent.o_first_child  at the db level as follows:
# put unique constraint on  Child.parent_id  column to make sure all  Child  rows point to different  Parent  rows.
# Note: this unique constraint is different from the foreign key designation because foreign key is uniqueness on the Parent table (not the Child table). 
class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    o_first_child = relationship("Child", back_populates="o_parent_obj",  uselist=False )   # uselist=False  enforces 1-to-1 at ORM level

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent_id = Column(Integer, ForeignKey('parent.id'),  unique=True )   # unique constraint enforces 1-to-1 at db level.  Optional. Creates db index.
    o_parent_obj = relationship("Parent", back_populates="o_first_child")     



# Building  1-way bookkeeping properties  Relationship
"""
In the db schema design, sometimes there are "bookkeeping" property fields whose value is a row in another table.
These are typically less important fields that are not part of the core db design.
Rather, they are more like bookkeeping properites and the data type for this field is another table.
For example,  created_by  field to track which user created the data entry.
The python code might want to get the user who created the data, but it won't start from the user to get all the data entries that he created.
Thus, these are called "1-way bookkeeping properties".

How to build a "1-way bookkeeping property"?
"""
class UserAccount(Base):
    __tablename__ = 'user_account'
    id = Column(Integer, primary_key=True)
    # UserAccount does not track any of the bookkeeping properties that point to it.

class SomeData(Base):
    __tablename__ = 'some_data'
    id = Column(Integer, primary_key=True)
    i_user_who_created_the_data = Column(Integer, ForeignKey('user_account.id'))
    o_user_who_created_the_data = relationship("UserAccount", foreign_keys="[SomeData.i_user_who_created_the_data]")    # 1-way ORM ability: from SomeData to UserAccount
    i_user_who_last_viewed_the_data = Column(Integer, ForeignKey('user_account.id'))
    o_user_who_last_viewed_the_data = relationship("UserAccount", foreign_keys="[SomeData.i_user_who_last_viewed_the_data]")    # 1-way ORM ability: from SomeData to UserAccount