#+-------------------------------------------------------------------+
#| *** Exercises - Basic Mapping ***                                 |
#+-------------------------------------------------------------------+
#|                                                                   |
#| 1. Create a class/mapping for this table, call the class Network  |
#|                                                                   |
#| CREATE TABLE network (                                            |
#|      network_id INTEGER PRIMARY KEY,                              |
#|      name VARCHAR(100) NOT NULL,                                  |
#| )                                                                 |
#|                                                                   |
#| 2. emit Base.metadata.create_all(engine) to create the table      |
#|                                                                   |
#| 3. commit a few Network objects to the database:                  |
#|                                                                   |
#| Network(name='net1'), Network(name='net2')                        |
#+------------------------------------------------------ (25 / 72) --+

from sqlalchemy import create_engine
from sqlalchemy import MetaData
from sqlalchemy import Column, Integer, String, Numeric, func,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session,relationship
import os

metadata = MetaData()
#для выполнения задания создаём базу данных "some"
engine = create_engine("sqlite:///some.db")

Base = declarative_base()

class Network(Base):
    __tablename__ = 'network'

    network_id = Column(Integer, primary_key=True)
    name = Column(String(100), nullable=False)

    def __repr__(self):
        return "<User(%r, %r)>" % (self.name, self.fullname)

Base.metadata.create_all(engine)

session = Session(bind=engine)
session.add_all([
    Network(name='net1'),
    Network(name='net2')
])
session.commit()

#+---------------------------------------------------------------------------+
#| *** Exercises - ORM Querying ***                                          |
#+---------------------------------------------------------------------------+
#| 1. Produce a Query object representing the list of "fullname" values for  |
#|    all User objects in alphabetical order.                                |
#|                                                                           |
#| 2. call .all() on the query to make sure it works!                        |
#|                                                                           |
#| 3. build a second Query object from the first that also selects           |
#|    only User rows with the name "mary" or "ed".                           |
#|                                                                           |
#| 4. return only the second row of the Query from #3.                       |
#+-------------------------------------------------------------- (43 / 72) --+

class User(Base):
    __tablename__ = 'user'

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

    def __repr__(self):
        return "<User(%r, %r)>" % (self.name, self.fullname)

Base.metadata.create_all(engine)

session.add_all([
    User(name='wendy', fullname='Wendy Weathersmith'),
    User(name='mary', fullname='Mary Contrary'),
    User(name='fred', fullname='Fred Flinstone'),
    User(name='ed', fullname='Ed Jones')
])
session.commit()

query = session.query(User.fullname).order_by(User.fullname)
print(query.all())
query = query.filter(User.name.in_(['mary','ed']))
print(query.all())
print(query[1])

class Address(Base):
    __tablename__ = 'address'

    id = Column(Integer, primary_key=True)
    email_address = Column(String, nullable=False)
    user_id = Column(Integer, ForeignKey('user.id'))
    user = relationship("User", backref="addresses")

    def __repr__(self):
        return "<Address(%r)>" % self.email_address

Base.metadata.create_all(engine)

jack = User(name='jack', fullname='Jack Bean')
jack.addresses = [Address(email_address='jack@gmail.com'),
                  Address(email_address='j25@yahoo.com'),
                  Address(email_address='jack@hotmail.com')]
session.add(jack)
session.commit()

#+----------------------------------------------------------------------------+
#| *** Exercises - Final Exam ! ***                                           |
#+----------------------------------------------------------------------------+
#| 1. Create a class called 'Account', with table "account":                  |
#|                                                                            |
#|      id = Column(Integer, primary_key=True)                                |
#|      owner = Column(String(50), nullable=False)                            |
#|      balance = Column(Numeric, default=0)                                  |
#|                                                                            |
#| 2. Create a class "Transaction", with table "transaction":                 |
#|      * Integer primary key                                                 |
#|      * numeric "amount" column                                             |
#|      * Integer "account_id" column with ForeignKey('account.id')           |
#|                                                                            |
#| 3. Add a relationship() on Transaction named "account", which refers       |
#|    to "Account", and has a backref called "transactions".                  |
#|                                                                            |
#| 4. Create a database, create tables, then insert these objects:            |
#|                                                                            |
#|      a1 = Account(owner='Jack Jones', balance=5000)                        |
#|      a2 = Account(owner='Ed Rendell', balance=10000)                       |
#|      Transaction(amount=500, account=a1)                                   |
#|      Transaction(amount=4500, account=a1)                                  |
#|      Transaction(amount=6000, account=a2)                                  |
#|      Transaction(amount=4000, account=a2)                                  |
#|                                                                            |
#| 5. Produce a report that shows:                                            |
#|     * account owner                                                        |
#|     * summation of transaction amounts per account (should match balance)  |
#|       A column can be summed using func.sum(Transaction.amount)            |
#+--------------------------------------------------------------- (72 / 72) --+

class Account(Base):
    __tablename__ = 'account'

    id = Column(Integer, primary_key=True)
    owner = Column(String(50), nullable=False)
    balance = Column(Numeric, default=0)

    def __repr__(self):
        return "<Account(%r, %r)>" % (self.owner, self.balance)

class Transaction(Base):
    __tablename__ = 'transaction'

    id = Column(Integer, primary_key=True)
    amount = Column(Numeric, default=0)
    account_id = Column(Integer, ForeignKey('account.id'))
    account = relationship("Account", backref="transactions")

    def __repr__(self):
        return "Transaction: %r" % (self.amount)

Base.metadata.create_all(engine)

a1=Account(owner = "Jack Jones", balance = 5000)
a2=Account(owner="Ed Rendell", balance=10000)
session.add_all([
    a1,
    a2,
    Transaction(amount=500, account=a1),
    Transaction(amount=4500, account=a1),
    Transaction(amount=6000, account=a2),
    Transaction(amount=4000, account=a2)])

session.commit()

for account in session.query(Account).all():
    print(account.owner)
    print(account.balance)
    sum = 0
    for at in account.transactions:
        sum += at.amount
    print(sum)

#+------------------------------------------------------------------+
#| *** Exercises ***                                                |
#+------------------------------------------------------------------+
#| 1. Run this SQL JOIN:                                            |
#|                                                                  |
#|    SELECT user.name, address.email_address FROM user             |
#|    JOIN address ON user.id=address.user_id WHERE                 |
#|    address.email_address='j25@yahoo.com'                         |
#|                                                                  |
#| 2. Tricky Bonus!  Select all pairs of distinct user names.       |
#|    Hint: "... ON user_alias1.name < user_alias2.name"            |
#+----------------------------------------------------- (62 / 72) --+

query = session.query(User.name, Address.email_address).join(Address).filter(Address.email_address=="j25@yahoo.com")
print(query)
print (query.all())

from sqlalchemy.orm import aliased

user_alias1 = aliased(User)
user_alias2 = aliased(User)

from sqlalchemy import select

query=engine.execute(select([user_alias1.name, user_alias2.name]).distinct().where(user_alias1.name < user_alias2.name))
print (query.fetchall())

session.close()
#после успешного выполнения задания удаляем базу данных, чтобы она не занимала место на диске
os.remove("some.db")