import track


class serverAlbum:
    def __init__(self,db,loggedInUsers,locations):
        self.db=db
        self.loggedInUsers=loggedInUsers
        self.locations=locations

    ### *******************************************************************************
    ### getAlbumLongInfo --> albumname, copyrightYear, rate,
    ###    ...genres[id, name], artists[id, name]
    ###    [recordname, volume, recordid [trackids]]
    ### *******************************************************************************
    def getAlbumLongInfo(self, sid, albumIds):
        try:
            locationId=self.loggedInUsers[int(sid)][2]
        except KeyError:
            return ("error","no such sid")

        try:
            machine=self.locations[locationId]
        except KeyError:
            return ("error","no such location")

        
        errorAlbumIds=[]
        albums=[]
        for albumId in albumIds:
            if not int(albumId)==0:
                c=self.db.cursor()
                albumExist=c.execute("""SELECT albumId,name,copyrightYear,collection FROM Album WHERE albumId=%s""", (albumId,))
                if (albumExist<>1):
                    errorAlbumIds.append(albumId)
                else:
                    result=list(c.fetchone())

                    rate=self.getAlbumRate(sid,albumId)
                    result.append( rate[1] )
                    
                    # get genreinfo
                    genreQuery=c.execute("""SELECT Genre.name, Genre.genreId FROM """+
                                         """  Genre, GenreMapping WHERE """+
                                         """  Genre.genreId = GenreMapping.genreId AND"""+
                                         """  GenreMapping.referenceType='album' AND """+
                                         """  GenreMapping.referenceId = %s""", (albumId,))
                    
                    genreResult=c.fetchall()
                    if len(genreResult)>0:
                        result.append(genreResult)
                    else:
                        result.append([])

                    # get artistinfo
                    genreQuery=c.execute("""SELECT Artist.name, Artist.artistId """+
                                         """  FROM Artist, AlbumArtist """+
                                         """  WHERE Artist.artistId = AlbumArtist.artistId """+
                                         """  AND AlbumArtist.albumId = %s""", (albumId,))
                    
                    artistResult=c.fetchall()
                    if len(artistResult)>0:
                        result.append(artistResult)
                    else:
                        result.append([])

                    recordList=[]
                    recordsExist=c.execute("""SELECT recordId,name,volume FROM Record WHERE albumId=%s order by Record.volume""", (albumId,))
                    if (recordsExist>0):
                        recordsResult=c.fetchall()
                        for record in recordsResult:
                            recordId,recordName,recordVolume=record
                            if recordName==None:
                                recordName=""
                            if recordVolume==None:
                                recordVolume=0
                                
                            tracksExist=c.execute("""SELECT trackId FROM Track WHERE recordId=%s ORDER BY trackno""",(recordId))
                            trackList=[]
                            if tracksExist>0:
                                tracks=c.fetchall()
                                for track in tracks:
                                    trackList.append(track[0])
                            recordList.append([recordId,recordName,recordVolume,trackList])
                        result.append(recordList)
                    albums.append(result)
                c.close()

        if len(errorAlbumIds)>0:
            return ("error","albumId problem",errorAlbumIds,albums)

        return ("ok",albums)
    ### *******************************************************************************
    def getAlbumShortInfo(self, sid, albumIds):
        try:
            locationId=self.loggedInUsers[int(sid)][2]
        except KeyError:
            return ("error","no such sid")

        try:
            machine=self.locations[locationId]
        except KeyError:
            return ("error","no such location")

        
        errorAlbumIds=[]
        albums=[]
        for albumId in albumIds:
            if not int(albumId)==0:
                c=self.db.cursor()
                albumExist=c.execute("""SELECT albumId,name FROM Album WHERE albumId=%s""", (albumId,))
                if (albumExist<>1):
                    errorAlbumIds.append(albumId)
                else:
                    result=list(c.fetchone())

                    rate=self.getAlbumRate(sid,albumId)
                    result.append( rate[1] )

                    recordList=[]
                    recordsExist=c.execute("""SELECT recordId,name,volume FROM Record WHERE albumId=%s order by Record.volume""", (albumId,))
                    if (recordsExist>0):
                        recordsResult=c.fetchall()
                        for record in recordsResult:
                            recordId,recordName,recordVolume=record
                            if recordName==None:
                                recordName=""
                            if recordVolume==None:
                                recordVolume=0
                                
                            tracksExist=c.execute("""SELECT trackId FROM Track WHERE recordId=%s ORDER BY trackno""",(recordId))
                            trackList=[]
                            if tracksExist>0:
                                tracks=c.fetchall()
                                for track in tracks:
                                    trackList.append(track[0])
                            recordList.append([recordId,recordName,recordVolume,trackList])
                        result.append(recordList)
                    albums.append(result)
                c.close()

        if len(errorAlbumIds)>0:
            return ("error","albumId problem",errorAlbumIds,albums)

        return ("ok",albums)

    ### *******************************************************************************
    def setAlbumRate(self, sid, albumId, rate):
        try:
            locationId=self.loggedInUsers[int(sid)][2]
            userId=self.loggedInUsers[int(sid)][0]
        except KeyError:
            return ("error","no such sid")

        try:
            machine=self.locations[locationId]
        except KeyError:
            return ("error","no such location")
                  
        int_Rate=int(rate)
        if (int_Rate<-2 or int_Rate>2):
            return ("error","invalid rate")

        c=self.db.cursor()
        ratings=c.execute("""SELECT userRateId FROM UserRate WHERE referenceType='album' and referenceId=%s and userId=%s""", (albumId,userId))
        if (ratings==0):
            ### ___FIX: check error return
            c.execute("""INSERT INTO UserRate (userId,referenceType,referenceId,rate) VALUES (%s,%s,%s,%s)""",(userId,"album",albumId,int_Rate))
        else:
            result=c.fetchone()
            c.execute("""UPDATE UserRate SET rate=%s WHERE userRateId=%s""",(int_Rate,result[0]))
        self.db.commit()
        c.close()
        return ("ok",)

    ### *******************************************************************************
    def getAlbumRate(self, sid, albumId):
        try:
            locationId=self.loggedInUsers[int(sid)][2]
            userId=self.loggedInUsers[int(sid)][0]
        except KeyError:
            return ("error","no such sid")

        try:
            machine=self.locations[locationId]
        except KeyError:
            return ("error","no such location")

        value=0
        c=self.db.cursor()
        ratings=c.execute("""SELECT rate FROM UserRate WHERE referenceType='album' and referenceId=%s and userId=%s""", (albumId,userId))
        if (ratings==0):
            value=0
        elif (ratings==1):
            result=c.fetchone()
            value=result[0]
        else:
            print "getAlbumRate:","should not get this many ratings"
            value=0

        c.close()

        return ("ok",value)

    ### *******************************************************************************
    def getAlbumGenre(self, sid, albumId):
        try:
            locationId=self.loggedInUsers[int(sid)][2]
            userId=self.loggedInUsers[int(sid)][0]
        except KeyError:
            return ("error","no such sid")

        try:
            machine=self.locations[locationId]
        except KeyError:
            return ("error","no such location")

        c=self.db.cursor()

        globalAlbumGenreExist=c.execute("""SELECT GenreMapping.genreId,Genre.name FROM GenreMapping,Genre WHERE GenreMapping.genreId=Genre.genreId and referenceId=%s and userId is NULL and referenceType='album'""", (albumId,))
        globalAlbumGenre=c.fetchall()

        albumGenreExist=c.execute("""SELECT GenreMapping.genreId,Genre.name,genreMappingId FROM GenreMapping,Genre WHERE GenreMapping.genreId=Genre.genreId and referenceId=%s and userId=%s and referenceType='album'""", (albumId,userId))
        userAlbumGenre=c.fetchall()
        

        c.close()

        
        return ("ok",(globalAlbumGenre,userAlbumGenre))



    ### *******************************************************************************
    def setAlbumGenre(self, sid, albumId, genreId):
        try:
            locationId=self.loggedInUsers[int(sid)][2]
            userId=self.loggedInUsers[int(sid)][0]
        except KeyError:
            return ("error","no such sid")

        try:
            machine=self.locations[locationId]
        except KeyError:
            return ("error","no such location")

        c=self.db.cursor()

        albumGenreExist=c.execute("""SELECT genreMappingId FROM GenreMapping WHERE genreId=%s and referenceId=%s and userId=%s and referenceType='album'""", (genreId,albumId,userId))

        if (albumGenreExist==0):
            Exist=c.execute("""INSERT INTO GenreMapping (genreId,referenceType,referenceId,userId) values (%s,'album',%s,%s)""", (genreId,albumId,userId))

        self.db.commit()
        c.close()
        
        return ("ok",)

    ### *******************************************************************************
    def deleteAlbumGenre(self, sid, genreMappingId):
        try:
            locationId=self.loggedInUsers[int(sid)][2]
            userId=self.loggedInUsers[int(sid)][0]
        except KeyError:
            return ("error","no such sid")

        try:
            machine=self.locations[locationId]
        except KeyError:
            return ("error","no such location")

        c=self.db.cursor()

        albumGenreExist=c.execute("""SELECT genreId FROM GenreMapping WHERE genreMappingId=%s and userId=%s""", (genreMappingId,userId))

        if (albumGenreExist==0):
            c.close()
            return ("error","Genre don't exist or are not create by you")

        genreId=c.fetchone()
        
        rateExist=c.execute("""SELECT userId from UserRate where referenceType='album' and referenceId=%s and not userId=%s""",(genreId,userId))
        if rateExist is not None:
            c.close()
            return ("error","Genre is rated by another user")
        
        Exist=c.execute("""DELETE FROM GenreMapping WHERE genreMappingId=%s""", (genreMappingId,))
        self.db.commit()
        
        c.close()

        
        return ("ok",)