from sqlobject import *
from optparse import OptionParser


class Artist(SQLObject):
    name = StringCol(length=100)
    albums = MultipleJoin('Album')
    genres = RelatedJoin('Genre')

class Genre(SQLObject):
    name = StringCol(length=100)
    artists = RelatedJoin('Artist')
    
class Album(SQLObject):
    name = StringCol(length=100)
    artist = ForeignKey('Artist')
    songs = MultipleJoin('Song')

class Song(SQLObject):
    name = StringCol(length=100)
    album = ForeignKey('Album')
    
def create_tables(option, opt_str, value, parser):
    Artist.createTable(ifNotExists=True)
    Genre.createTable(ifNotExists=True)
    Album.createTable(ifNotExists=True)
    Song.createTable(ifNotExists=True)

def add_artist(option, opt_str, value, parser):
    artists = Artist.select(Artist.q.name==value)
    artist_exists = len(list(artists)) != 0
    if not artist_exists:
        artist = Artist(name=value)

def del_artist(option, opt_str, value, parser):
    artist = Artist.get(value)
    artist.destroySelf()

def add_genre(option, opt_str, value, parser):
    genres = list(Genre.select(Genre.q.name==value))
    genre_exists = len(genres) != 0
    genre = None
    if not genre_exists:
        genre = Genre(name=value)
    else:
        genre = genres[0]
    artist = Artist.get(parser.values.artist_id)
    artist.addGenre(genre)

def add_album(option, opt_str, value, parser):
    artist = Artist.get(parser.values.artist_id)
    album = Album(name=value, artist=artist)

def list_artists(option, opt_str, value, parser):
    artists = Artist.select()
    for artist in artists:
        print '''[%d] %s:
        Album(s): %s
        Genre(s): %s''' % (artist.id, artist.name, ','.join([i.name for i in artist.albums]),
                           ','.join([i.name for i in artist.genres]))

def find_artist_by_album(option, str, value, parser):
    album = list(Album.select(Album.q.name==value))[0]
    print 'Album artist:', album.artist.name

def find_artists_by_genre(option, str, value, parser):
    genre = list(Genre.select(Genre.q.name==value))[0]
    print 'Artists of Genre %s:\n%s' % (value, '\n'.join([i.name for i in genre.artists]))

if __name__ == '__main__':
    connection = connectionForURI('sqlite:///home/michael/musikdb/sqlobject_example.sql')
    sqlhub.processConnection = connection

    parser = OptionParser()
    parser.add_option('-c', '--create', action='callback', callback=create_tables, 
                      help='Create necessary tables in the database')
    parser.add_option('--add-artist', action='callback', callback=add_artist, type='string',
                      help='Add new artist to the database')
    parser.add_option('--del-artist', action='callback', callback=del_artist, type='int',
                      help='Delete artist with the specified ID')
    parser.add_option('--artist-id', dest='artist_id',
                      help='The artist ID for the command following the ID parameter')
    parser.add_option('--add-genre', action='callback', callback=add_genre, type='string',
                      help='Add a genre to an existing artist')
    parser.add_option('--add-album', action='callback', callback=add_album, type='string',
                      help='Add an album to an existing artist')

    parser.add_option('-l', '--list', action='callback', callback=list_artists,
                      help='List artists, their genres and albums')
    parser.add_option('--artist-by-album', action='callback', callback=find_artist_by_album,
                      type='string', help='Find the artist of a given album')
    parser.add_option('--artists-by-genre', action='callback', callback=find_artists_by_genre,
                      type='string', help='Find artists of a given genre')

    (options, args) = parser.parse_args()