"""
https://docs.sqlalchemy.org/en/14/tutorial/index.html#unified-tutorial

https://docs.sqlalchemy.org/en/14/tutorial/data_select.html

For both Core and ORM, the select() function generates a Select construct which
is used for all SELECT queries. Passed to methods like Connection.execute() in
Core and Session.execute() in ORM, a SELECT statement is emitted in the current
transaction and the result rows available via the returned Result object.
"""


from sqlalchemy import (
    Column,
    ForeignKey,
    Integer,
    String,
    and_,
    bindparam,
    create_engine,
    desc,
    func,
    insert,
    or_,
    select,
)
from sqlalchemy.orm import Session, aliased, registry, relationship

mapper_registry = registry()

# The above registry, when constructed, automatically includes a MetaData
# object that will store a collection of Table objects.
print(mapper_registry.metadata)

# Instead of declaring Table objects directly, we will now declare them
# indirectly through directives applied to our mapped classes. In the most
# common approach, each mapped class descends from a common base class known
# as the declarative base. We get a new declarative base from the registry
# using the registry.generate_base() method.
Base = mapper_registry.generate_base()


class User(Base):
    __tablename__ = "user_account"

    # Column objects are assigned to class-level attributes within the classes
    # These Column objects can usually be declared without an explicit “name”
    # field inside the constructor, as the Declarative process will name them
    # automatically based on the attribute name that was used.
    id = Column(Integer, primary_key=True)
    name = Column(String(30))
    fullname = Column(String)

    addresses = relationship("Address", back_populates="user")

    def __repr__(self):
        return f"User(id={self.id!r}, name={self.name!r}, fullname={self.fullname!r})"


class Address(Base):
    __tablename__ = "address"

    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey("user_account.id"))

    user = relationship("User", back_populates="addresses")

    def __repr__(self):
        return f"Address(id={self.id!r}, email_address={self.email_address!r})"


def show_objects():
    print(f"{User.__table__!r}")


def emit_database_DDL(engine):
    # Emit CREATE statements given ORM registry
    mapper_registry.metadata.create_all(engine)


def insert_data(engine):
    """The tutorial show data selection with ORM without explaining insert
    first as it is done for Core !!!
    """
    with Session(engine) as session:
        with session.begin():
            bob = User(name="spongebob", fullname="Spongebob Squarepants")
            session.add(bob)

            sandy = User(name="sandy", fullname="Sandy Cheeks")
            session.add(sandy)

            patrick = User(name="patrick", fullname="Patrick Star")
            session.add(patrick)

        with session.begin():
            # Commit the users to database to get their generated id
            # TODO use a better way
            bob_addr = Address(
                user_id=bob.id,
                email_address="spongebob@sqlalchemy.org",
            )
            session.add(bob_addr)

            sandy_addr1 = Address(
                user_id=sandy.id, email_address="sandy@sqlalchemy.org"
            )
            session.add(sandy_addr1)

            sandy_addr2 = Address(
                user_id=sandy.id, email_address="sandy@aol.com"
            )
            session.add(sandy_addr2)

            patrick_addr1 = Address(
                user_id=patrick.id, email_address="patrick@gmail.com"
            )
            session.add(patrick_addr1)

            patrick_addr2 = Address(
                user_id=patrick.id, email_address="patrick@aol.com"
            )
            session.add(patrick_addr2)

        # inner context calls session.commit(), if there were no exceptions
    # outer context calls session.close()


def select_stmt(engine):
    stmt = select(User).where(User.name == "spongebob")
    print(f"stmt: {stmt}")

    # When using the ORM, particularly with a select() construct that’s
    # composed against ORM entities, we will want to execute it using the
    # Session.execute() method on the Session; using this approach, we
    # continue to get Row objects from the result, however these rows are
    # now capable of including complete entities, such as instances of the
    # User class, as individual elements within each row
    with Session(engine) as session:

        for row in session.execute(stmt):
            # While the SQL generated in these examples looks the same whether
            # we invoke select(user_table) or select(User), in the more general
            # case they do not necessarily render the same thing, as an
            # ORM-mapped class may be mapped to other kinds of “selectables”
            # besides tables. The select() that’s against an ORM entity also
            # indicates that ORM-mapped instances should be returned in a result,
            # which is not the case when SELECTing from a Table object.
            print(row)


def select_orm_entities_and_columns(engine):
    # ORM entities, such our User class as well as the column-mapped attributes
    # upon it such as User.name, also participate in the SQL Expression Language
    # system representing tables and columns.
    print(select(User))

    # When executing a statement like the above using the ORM Session.execute()
    # method, there is an important difference when we select from a full entity
    # such as User, as opposed to user_table, which is that the entity itself
    # is returned as a single element within each row. That is, when we fetch
    # rows from the above statement, as there is only the User entity in the
    # list of things to fetch, we get back Row objects that have only one element,
    # which contain instances of the User class.
    with Session(engine) as session:
        row = session.execute(select(User)).first()

        print(f"row: {row}")
        print(f"row[0]: {row[0]}")

    # Alternatively, we can select individual columns of an ORM entity as distinct
    # elements within result rows, by using the class-bound attributes; when these
    # are passed to a construct such as select(), they are resolved into the Column
    # or other SQL expression represented by each attribute.
    print(select(User.name, User.fullname))

    with Session(engine) as session:
        #  When we invoke this statement using Session.execute(), we now receive
        # rows that have individual elements per value, each corresponding to a
        # separate column or other SQL expression.
        row = session.execute(select(User.name, User.fullname)).first()

        print(f"row: {row}")

    # The approaches can also be mixed, as below where we SELECT the name
    # attribute of the User entity as the first element of the row, and combine
    # it with full Address entities in the second element.
    with Session(engine) as session:
        rows = session.execute(
            select(User.name, Address)
            .where(User.id == Address.user_id)
            .order_by(Address.id)
        ).all()

        print(f"rows: {rows}")


def where_clause(engine):
    pass
    # “AND” and “OR” conjunctions are both available directly using the and_()
    # and or_() functions
    print(
        select(Address.email_address).where(
            and_(
                or_(
                    User.name == "squidward",
                    User.name == "sandy",
                ),
                Address.user_id == User.id,
            )
        )
    )

    # For simple “equality” comparisons against a single entity, there’s also a
    # popular method known as Select.filter_by() which accepts keyword arguments
    # that match to column keys or ORM attribute names. It will filter against
    # the leftmost FROM clause or the last entity joined.
    print(
        select(User).filter_by(
            name="spongebob", fullname="Spongebob Squarepants"
        )
    )


def order_by_stmt(engine):
    # Ascending / descending is available from the ColumnElement.asc() and
    # ColumnElement.desc() modifiers, which are present from ORM-bound attributes
    # as well.
    print(select(User).order_by(User.fullname.desc()))


def group_by_having_stmt(engine):
    # The GROUP BY clause is constructed similarly to the ORDER BY clause, and has
    # the purpose of sub-dividing the selected rows into specific groups upon which
    # aggregate functions may be invoked. The HAVING clause is usually used with
    # GROUP BY and is of a similar form to the WHERE clause, except that it’s
    # applied to the aggregated functions used within groups.
    # TUTORIAL ERROR : engine.connect() with ORM usage

    # Why can't we use aliases in having clause :
    # https://stackoverflow.com/a/38978689/481719
    with Session(engine) as session:
        result = session.execute(
            select(User.name, func.count(Address.id).label("count"))
            .join(Address)
            .group_by(User.name)
            .having(func.count(Address.id) > 1)
        )
        print(result.all())


def ordering_grouping_by_label(engine):
    stmt = (
        select(Address.user_id, func.count(Address.id).label("num_addresses"))
        .group_by("user_id")
        .order_by("user_id", desc("num_addresses"))
    )
    print(f"stmt: {stmt}")

    with Session(engine) as session:
        result = session.execute(stmt)
        print(result.all())


def orm_entity_aliases(engine):
    # The ORM equivalent of the FromClause.alias() method is the ORM aliased()
    # function, which may be applied to an entity such as User and Address.
    address_alias_1 = aliased(Address)
    address_alias_2 = aliased(Address)
    stmt = (
        select(User)
        .join_from(User, address_alias_1)
        .where(address_alias_1.email_address == "patrick@aol.com")
        .join_from(User, address_alias_2)
        .where(address_alias_2.email_address == "patrick@gmail.com")
    )
    print(f"stmt: {stmt}")

    with Session(engine) as session:
        result = session.execute(stmt)
        print(result.all())


def orm_entity_subqueries(engine):
    # In the ORM, the aliased() construct may be used to associate an ORM
    # entity, such as our User or Address class, with any FromClause concept
    # that represents a source of rows. The preceding section ORM Entity
    subq = (
        select(Address)
        .where(~Address.email_address.like("%@aol.com"))
        .subquery()
    )
    # Aliases illustrates using aliased() to associate the mapped class with
    # an Alias of its mapped Table. Here we illustrate aliased() doing the
    # same thing against both a Subquery as well as a CTE generated against a
    # Select construct, that ultimately derives from that same mapped Table.
    address_subq = aliased(Address, subq)
    stmt = (
        select(User, address_subq)
        .join_from(User, address_subq)
        .order_by(User.id, address_subq.id)
    )
    with Session(engine) as session:
        for user, address in session.execute(stmt):
            print(f"{user} {address}")


def orm_entity_cte(engine):
    # Another example follows, which is exactly the same except it makes use
    # of the CTE construct instead.
    cte = select(Address).where(~Address.email_address.like("%@aol.com")).cte()
    address_cte = aliased(Address, cte)
    stmt = (
        select(User, address_cte)
        .join_from(User, address_cte)
        .order_by(User.id, address_cte.id)
    )
    with Session(engine) as session:
        for user, address in session.execute(stmt):
            print(f"{user} {address}")


if __name__ == "__main__":
    show_objects()

    dbname = "sqlite+pysqlite:///:memory:"
    engine = create_engine(dbname, echo=True, future=True)

    emit_database_DDL(engine)

    insert_data(engine)

    """
    select_stmt(engine)

    select_orm_entities_and_columns(engine)

    where_clause(engine)

    order_by_stmt(engine)
    group_by_having_stmt(engine)
    ordering_grouping_by_label(engine)

    orm_entity_aliases(engine)

    orm_entity_subqueries(engine)
    """

    orm_entity_cte(engine)