from __future__ import print_function

from sqlalchemy import create_engine, Column, types
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.orm import Session

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref


engine = create_engine('sqlite:///:memory:', echo=True)
Base = declarative_base()

session = scoped_session(sessionmaker(bind=engine))

class Group(Base):
    __tablename__ = "groups"
    gid = Column(types.Integer, primary_key=True)
    name = Column(types.String(255))

    users = relationship("User", backref="group")

    def __repr__(self):
        ret =  "Group(name=%r)" % self.name
        for user in self.users:
            ret += str(user)

class User(Base):
    __tablename__ = "users"
    login = Column(types.String(50), primary_key=True)
    name = Column(types.String(255))
    group_id = Column(types.Integer, ForeignKey('groups.gid'))
    address = Column(types.String(200),
                            ForeignKey('addresses.email_address'), )
    email = relationship("Address", cascade="all")

    def __repr__(self):
        return "User(login=%r, name=%r)\n%s" % (self.login, self.name,
                str(self.email))


class Address(Base):
    __tablename__ = 'addresses'
    email_address = Column(types.String(200), nullable=False, primary_key=True)
    #user_login = Column(types.String(50), ForeignKey('users.login'))

    def __repr__(self):
        return "<Address(email_address='%s')>" % self.email_address


Base.metadata.create_all(engine)

if __name__ == '__main__':

    # this works correctly even though we reuse a unique key
    u1 = User(login='Guy', name="Some Guy")
    u1.email=Address(email_address='nameless@yahoo.com')
    u2 = User(login='Gal', name="Some Gal")
    u2.email=Address(email_address='nameless@yahoo.com')
    session.merge(u1)
    session.merge(u2)
    session.commit()

    print("**** two users with addresses")
    for u in session.query(User):
        print(u)


    # though this is similar it ends up using insertmany and throws a unique key
    # constraint even with the merge
    u3 = User(login='Mr. User', name="A dude")
    u3.email=Address(email_address='james@yahoo.com')
    u4 = User(login='Mrs. User', name="A dudette")
    u4.email=Address(email_address='jill@yahoo.com')
    u5 = User(login='Mrs. User2', name="A dudette2")
    u5.email=Address(email_address='jill@yahoo.com')
    g1 = Group(name="G1")
    g1.users.append(u3)
    g1.users.append(u4)
    g1.users.append(u5)
    print("********* MERGE")
    session.merge(g1)
    session.commit()

    print(g1)