from sqlalchemy import *
from sqlalchemy.orm import *

#engine = create_engine("sqlite://", echo=True)
engine = create_engine("mysql://foo:foo@127.0.0.1/foo", echo=True)

#meta = MetaData(engine)
#Session = sessionmaker(bind=engine)
#session = Session()

meta = MetaData()
Session = scoped_session(sessionmaker(autoflush=False, transactional=False))
mapper = Session.mapper
Session.configure(bind=engine)
session = Session

# Tables
channel_table = Table('channels', meta,
    Column('id', Integer, primary_key=True),
    Column('name', String(30), nullable=False),
)

playlist_table = Table('playlists', meta,
    Column('id', Integer, primary_key=True),
    Column('type', Unicode(8), nullable=False), # Longest: len('playlist')
    Column('name', Unicode(30), nullable=False),
    Column('description', Unicode(40)),
)

playlist_channel_table = Table('channel_playlists', meta,
    Column('id', None, ForeignKey('playlists.id'), primary_key=True),
    Column('id_channel', None, ForeignKey('channels.id')),
)

catalog_table = Table('catalogs', meta,
    Column('id', Integer, primary_key=True),
    Column('name', String(30), nullable=False),
    Column('type', String(7)), # catalog, channel, site, channel
)

catalog_channel_table = Table('catalog_channels', meta,
    Column('id', None, ForeignKey('catalogs.id'), primary_key=True),
    Column('id_channel', None, ForeignKey('channels.id'), primary_key=True),
)

media_table = Table('medias', meta,
    Column('id', Integer, primary_key=True),
    Column('name', String(30), nullable=False),
    Column('id_catalog', None, ForeignKey('catalogs.id'), primary_key=True),
)

class Channel(object):
    def __repr__(self):
        return "<%s %s (%d)>" % (self.__class__.__name__, repr(self.name), self.id)

class Playlist(object):
    def __repr__(self):
        return "<%s %s (%d)>" % (self.__class__.__name__, repr(self.name), self.id)

class PlaylistChannel(Playlist):
    pass

class Catalog(object):
    def __repr__(self):
        return "<%s %s (%d)>" % (self.__class__.__name__, repr(self.name), self.id)

class CatalogChannel(Catalog):
    pass

class Media(object):
    def __repr__(self):
        return "<%s %s (%d)>" % (self.__class__.__name__, repr(self.name), self.id)

# Mappers
channel_mapper = mapper(Channel, channel_table)
playlist_mapper = mapper(Playlist, playlist_table, polymorphic_on=playlist_table.c.type, polymorphic_identity='playlist')
playlist_channel_mapper = mapper(PlaylistChannel, playlist_channel_table, inherits=Playlist, polymorphic_identity='channel',
    properties={
        'channel':relation(Channel,
                      backref=backref("playlists", cascade="all"),
                  ),
    },
)

media_mapper = mapper(Media, media_table)

catalog_mapper = mapper(Catalog, catalog_table, polymorphic_on=catalog_table.c.type, polymorphic_identity="catalog",
    properties={
        "medias":relation(Media,
                     backref="catalog",
                     cascade="all, delete-orphan",
                 ),
    },
)

catalog_channel_mapper = mapper(CatalogChannel, catalog_channel_table, inherits=Catalog, polymorphic_identity="channel",
    properties={
        "channel":relation(Channel,
                      backref="catalogs",
                      cascade="save-update",
                  ),
    },
)

#meta.drop_all()
#meta.create_all()
meta.drop_all(engine)
meta.create_all(engine)

# RELATIONS
# Channel one-to-many PlaylistChannel(Playlist)
# Channel one-to-many CatalogChannel(Catalog)
# Catalog one-to-many Media
#
# Given a PlaylistChannel(Playlist), we need to find all the Medias of a Channel via it's CatalogChannel(Catalog)

# Create a channel
chan = Channel()
chan.name = "channel"

# Create a playlist for the channel and append to it playlists.
playlist = PlaylistChannel() # id == 1
playlist.name = "playlist_channel"
chan.playlists.append(playlist)

# Create a catalog for the channel
catalog_chan = CatalogChannel()
catalog_chan.name = "catalog_channel"
chan.catalogs.append(catalog_chan)

# Populate catalog_chan with medias
media1 = Media()
media1.name = "media1_for_catalog_chan"
media2 = Media()
media2.name = "media2_for_catalog_chan"
catalog_chan.medias.append(media1)
catalog_chan.medias.append(media2)

# And create a "standalone" catalog (Medias from this Catalog should not be included in the result of the query)
catalog = Catalog()
catalog.name = "catalog"

# Populate catalog with medias
media3 = Media()
media3.name = "media1_for_catalog"
media4 = Media()
media4.name = "media2_for_catalog"
catalog.medias.append(media3)
catalog.medias.append(media4)

session.flush()
session.clear() # Let's forget about all that.

def hr():
    return "\n" + "#" * 80

# Now, let's query.
print hr()
print "GET THE PLAYLIST:"
print ">>> PlaylistChannel.query.one()"
playlist = PlaylistChannel.query.one()
print playlist

print hr()
print "WE SHOULD ONLY HAVE 4 MEDIAS, 2 FOR CATALOG AND 2 OTHER FOR CATALOGCHANNEL, WHICH IS NORMAL:"
print ">>> Media.query.count()"
print Media.query.count()

print hr()
print "NOW WE WANT TO QUERY ALL MEDIAS OF A CHANNEL FROM IT'S CATALOGCHANNEL, GIVEN A PLAYLIST, USING ONLY FILTERS (OLD STYLE):"
print ">>> Media.query.filter(Media.c.id_catalog==Catalog.c.id).filter(Catalog.c.id==catalog_channel_table.c.id).filter(CatalogChannel.c.id_channel==playlist.id_channel).all()"
print Media.query.filter(Media.c.id_catalog==Catalog.c.id).filter(Catalog.c.id==catalog_channel_table.c.id).filter(CatalogChannel.c.id_channel==playlist.id_channel).all()

print hr()
print "WE HAVE ALREADY SEEN THAT THE PREVIOUS RESULTS NEED TO USE A TABLE RATHER THAN A CLASS AS FOLLOWED (RETURNS 4 MEDIAS, WHICH IS NOT WHAT WE WANT):"
print ">>> Media.query.filter(Media.c.id_catalog==Catalog.c.id).filter(Catalog.c.id==CatalogChannel.c.id).filter(CatalogChannel.c.id_channel==playlist.id_channel).all()"
print Media.query.filter(Media.c.id_catalog==Catalog.c.id).filter(Catalog.c.id==CatalogChannel.c.id).filter(CatalogChannel.c.id_channel==playlist.id_channel).all()

print hr()
print "NOW WE WANT THE SAME PREVIOUS VALUD RESULTS (2 MEDIAS), BUT USING JOINS (NEW STYLE, r4060)"
print ">>> Media.query.join(['catalog', CatalogChannel.channel]).filter(CatalogChannel.c.id_channel==playlist.id_channel).all()"
print Media.query.join(['catalog', CatalogChannel.channel]).filter(CatalogChannel.c.id_channel==playlist.id_channel).all()