from .app import db, login_manager
import yaml, os.path
from flask.ext.login import UserMixin

################
#### Artists ###
################

class Artist(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True)
    albums_played = db.relationship(
                "Album",
                foreign_keys = 'Album.artist_id',
                backref=db.backref("player", lazy='joined'),
                lazy='dynamic'
    )
    albums_composed = db.relationship(
                "Album",
                foreign_keys = 'Album.composer_id',
                backref=db.backref("composer", lazy='joined'),
                lazy='dynamic'
    )
    def __repr__(self):
        return "<Artist (%d) %s" % (self.id, self.name)

    def serialize(self):
        return {
            'id': self.id,
            'name': self.name
        }

def get_artist(id):
    return Artist.query.get(id)

def get_artists():
    return Artist.query.filter(Artist.id == Album.artist_id).all()

def get_artists_by_name(message):
    try:
        return Artist.query.filter(Artist.name.like("%"+message.lower()+"%")).all()
    except NoResultFound:
        return []

def set_artist_name(id,message):
    res = Artist.query.filter(Artist.id == id).update({'name': message.lower()})
    db.session.commit()
    return res


genrewithalbum = db.Table('genrewithalbum',
    db.Column('genre_id', db.Integer, db.ForeignKey('genre.id'), primary_key=True),
    db.Column('album_id', db.Integer, db.ForeignKey('album.id'), primary_key=True)
)

playlistwithalbum = db.Table('playlistwithalbum',
    db.Column('playlist_id', db.Integer, db.ForeignKey('playlist.id'), primary_key=True),
    db.Column('album_id', db.Integer, db.ForeignKey('album.id'), primary_key=True)
)

################
#### Albums ####
################

class Album(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    img = db.Column(db.String(200))
    release_date = db.Column(db.Integer)
    url = db.Column(db.String(100))
    artist_id = db.Column(db.Integer, db.ForeignKey("artist.id"))
    composer_id = db.Column(db.Integer, db.ForeignKey("artist.id"))
    genres = db.relationship(
                "Genre",
                secondary=genrewithalbum,
                backref=db.backref("albums", lazy="dynamic")
    )

    def __repr__(self):
        return "<Album (%d) %s>" % (self.id, self.title)

    def serialize(self):
        return {
            'id': self.id,
            'title': self.title,
            'img' : self.img,
            'release_date' :self.release_date,
            'artist_id': self.artist_id,
            'composer_id': self.composer_id
        }

def get_album(id):
    return Album.query.get(id)

def get_albums():
    return Album.query.all()

def get_albums_by_title(message):
    try:
        return Album.query.filter(Album.title.like("%"+message.lower()+"%")).all()
    except NoResultFound:
        return []

def get_albums_by_date(message):
    try:
        return Album.query.filter(Album.release_date.like("%"+str(message)+"%")).all()
    except NoResultFound:
        return []

def get_albums_by_artist_id(id):
        albums=Album.query.all()
        return [ album for album in albums if id == album.player.id]


def get_albums_by_artist(message):
    try:
        albums=Album.query.all()
        return [ album for album in albums if message.lower() in album.player.name]
    except NoResultFound:
        return []

    #return Album.query.filter(Album.player.name.like("%"+message+"%"))
def get_albums_by_composer_id(id):
    albums=Album.query.all()
    return [ album for album in albums if id == album.composer.id]

def get_albums_by_composer(message):
    albums=Album.query.all()
    return [ album for album in albums if message.lower() in album.composer.name]
    #return Album.query.filter(Album.composer_id == artist.id).filter(Artist.name.like("%"+message+"%")).all()

def get_albums_by_genre(message):
    def l_contient(l,m):
        for ll in l:
            if m in ll.name:
                return True
        return False
    try:
        albums=Album.query.all()
        return [ album for album in albums if l_contient(album.genres, message.lower())]
    except NoResultFound:
        return []

def update_album(id,message,annee,artist,composer):
    res = Album.query.filter(Album.id == id).update({'title': message.lower(), 'release_date': annee, 'artist_id': artisit, 'composer_id':composer})
    db.session.commit()
    return res

def add_genre_in_album(album_id,genre_id):
    a = get_album(album_id)
    g= get_genre(genre_id)
    a.genres.append(g)
    db.session.add(a)
    db.session.commit()

def delete_genre_in_album(album_id,genre_id):
    a = get_album(album_id)
    g= get_genre(genre_id)
    a.genres.remove(g)
    db.session.add(a)
    db.session.commit()

######################
#### Compositeurs ####
######################

def get_composers():
        return Artist.query.filter(Artist.id == Album.composer_id)

def get_composer(id):
        return Artist.query.get(id)

def get_composers_by_name(message):
    try:
        return Artist.query.filter(Artist.id == Album.composer_id).filter(Artist.name.like("%"+message.lower()+"%"))
    except NoResultFound:
        return []


################
#### Genres ####
################

class Genre(db.Model):
    id = db.Column(db.Integer, primary_key= True)
    name = db.Column(db.String(100), unique=True)

    def serialize(self):
        return {
            'id': self.id,
            'name': self.name
        }

def get_genres():
    return Genre.query.all()

def get_genre(id):
    return Genre.query.get(id)

def get_genres_by_name(message):
    try:
        return Genre.query.filter(Genre.name.like("%"+message.lower()+"%")).all()
    except NoResultFound:
        return []


###################
#### Playlists ####
###################

class Playlist(db.Model):
    id = db.Column(db.Integer, primary_key= True)
    name = db.Column(db.String(100))
    user_name = db.Column(db.String(50), db.ForeignKey("user.username"))
    albums = db.relationship(
                "Album",
                secondary=playlistwithalbum,
                backref=db.backref("playlists", lazy="dynamic")
    )

    def serialize(self):
        return {
            'id': self.id,
            'name': self.name,
            'user_name': self.user_name
        }

def get_playlist(id):
    return Playlist.query.get(id)

def get_playlists(username):
    return Playlist.query.filter(Playlist.user_name==username).all()

def create_playlist(name,user_name):
    o=Playlist(name=name,user_name=user_name)
    db.session.add(o)
    db.session.commit()

def delete_playlist(id):
    o=get_playlist(id)
    db.session.delete(o)
    db.session.commit()

def add_album_to_playlist(playlist_id,album_id):
    p = get_playlist(playlist_id)
    a= get_album(album_id)
    p.albums.append(a)
    db.session.add(p)
    db.session.commit()

def delete_album_from_playlist(playlist_id,album_id):
    p = get_playlist(playlist_id)
    a= get_album(album_id)
    p.albums.remove(a)
    db.session.add(p)
    db.session.commit()


##############
#### User ####
##############

class User(db.Model, UserMixin):
    username = db.Column( db.String(50), primary_key=True)
    password = db.Column( db.String(64) )
    playlists = db.relationship(
                "Playlist",
                backref=db.backref("users", lazy="dynamic", uselist=True)
    )
    def get_id(self):
        return self.username

@login_manager.user_loader
def load_user(username):
    return User.query.get(username)