def getCommentsByFile(id_source, uid, after):
    names_location = __NAMES["location_v_comment2"]
    names_comment = __NAMES["comment2"]
    ensembles_im_admin = M.Ensemble.objects.filter(membership__in=M.Membership.objects.filter(user__id=uid).filter(admin=True))
    locations_im_admin = M.Location.objects.filter(ensemble__in=ensembles_im_admin)
    comments = M.Comment.objects.extra(select={"admin": 'select cast(admin as integer) from base_membership where base_membership.user_id=base_comment.author_id and base_membership.ensemble_id = base_location.ensemble_id'}).select_related("location", "author").filter(location__source__id=id_source, deleted=False, moderated=False).filter(Q(location__in=locations_im_admin, type__gt=1) | Q(author__id=uid) | Q(type__gt=2))
    membership = M.Membership.objects.get(user__id=uid, ensemble__ownership__source__id=id_source)
    if membership.section is not None:
        comments = comments.filter(Q(location__section=membership.section)|Q(location__section=None)) 
    threadmarks = M.ThreadMark.objects.filter(location__in=comments.values_list("location_id", flat=True))
    if after is not None: 
        comments = comments.filter(ctime__gt=after)
        threadmarks = threadmarks.filter(ctime__gt=after)
    if after is not None: 
        comments = comments.filter(ctime__gt=after)    
    locations_dict = UR.qs2dict(comments, names_location, "ID")
    comments_dict =  UR.qs2dict(comments, names_comment, "ID")
    threadmarks_dict = UR.qs2dict(threadmarks, __NAMES["threadmark"], "id")
    #Anonymous comments
    ensembles_im_admin_ids = [o.id for o in ensembles_im_admin]
    for k,c in comments_dict.iteritems(): 
        #if c["type"] < 3 and not (locations_dict[c["ID_location"]]["id_ensemble"] in  ensembles_im_admin_ids or uid==c["id_author"]): 
        if not c["signed"] and not (locations_dict[c["ID_location"]]["id_ensemble"] in  ensembles_im_admin_ids or uid==c["id_author"]): 
            c["fullname"]="Anonymous"
            c["id_author"]=0             
    return locations_dict, comments_dict, threadmarks_dict

def getPending(uid, payload):
    #reply requested threadmarks:    
    questions = M.ThreadMark.objects.filter(location__ensemble__membership__user__id=uid, type=1, active=True).exclude(user__id=uid)
    comments = M.Comment.objects.filter(location__threadmark__in=questions, parent__id=None, type=3, deleted=False, moderated=False)
    locations = M.Location.objects.filter(comment__in=comments)
    all_comments = M.Comment.objects.filter(location__in=locations)
    unrated_replies = all_comments.extra(tables=["base_threadmark"], where=["base_threadmark.location_id=u0.location_id and base_threadmark.ctime<u0.ctime"]).exclude(replyrating__status=M.ReplyRating.TYPE_UNRESOLVED) 
    questions = questions.exclude(location__comment__in=unrated_replies)
    comments =  M.Comment.objects.filter(location__threadmark__in=questions, parent__id=None, type=3, deleted=False, moderated=False)
    locations = M.Location.objects.filter(comment__in=comments)
    locations = locations.filter(Q(section__membership__user__id=uid)|Q(section=None)|Q(ensemble__in=M.Ensemble.objects.filter(membership__section=None, membership__user__id=uid)))

    #now items where action required: 
    my_questions =  M.ThreadMark.objects.filter(type=1, active=True, user__id=uid)#extra(select={"pending": "false"})
    my_unresolved = M.ReplyRating.objects.filter(threadmark__in=my_questions, status = M.ReplyRating.TYPE_UNRESOLVED)
    my_comments_unresolved =M.Comment.objects.filter(replyrating__in=my_unresolved) 
    recent_replies = M.Comment.objects.extra(where=["base_threadmark.ctime<base_comment.ctime"]).filter(location__threadmark__in=my_questions).exclude(id__in=my_comments_unresolved)
    recent_replies_base = M.Comment.objects.extra(where=["base_threadmark.comment_id=base_comment.id or (base_threadmark.comment_id is null and base_comment.parent_id is null)"]).filter(location__threadmark__in=my_questions).exclude(id__in=my_comments_unresolved)     
    #list() makes sure this gets evaluated. 
    #otherwise we get errors since other aliases are used in subsequent queries, that aren't compatibles with the names we defined in extra()
    recent_replies_ids = list(recent_replies.values_list("id", flat=True))    
    recent_locations = M.Location.objects.filter(comment__in=recent_replies_ids)
    recent_locations = recent_locations.filter(Q(section__membership__user__id=uid)|Q(section=None)|Q(ensemble__in=M.Ensemble.objects.filter(membership__section=None, membership__user__id=uid)))
    replied_questions = my_questions.filter(location__in=recent_locations)
    replied_questions = replied_questions.extra(select={"pending": "true"})    
    output = {}    
    output["questions"] = UR.qs2dict(questions|replied_questions)#, {"id":None, "type": None, "active":None, "location_id":None, "comment_id":None, "ctime":None, "pending":None, "user_id":None})    
    output["locations"] = UR.qs2dict(locations|recent_locations)
    output["comments"]  = UR.qs2dict(comments)
    output["comments"].update(UR.qs2dict(recent_replies)) #same reason as the list() above: we don't want a query to produce an error if reevaluated in a different context
    output["basecomments"] = UR.qs2dict(recent_replies_base)    
    return output

def get_guestfileinfo(id_source): 
    ownership = M.Ownership.objects.select_related("source", "ensemble", "folder").filter(source__id=id_source, deleted=False)    
    o = {
         "files": UR.qs2dict(ownership, __NAMES["files2"] , "ID"),
         "ensembles": UR.qs2dict(ownership, __NAMES["ensembles2"] , "ID") ,
         "folders": UR.qs2dict(ownership, __NAMES["folders2"] , "ID") ,
         }
    return o

def get_guestfileinfo(id_source): 
    ownership = M.Ownership.objects.select_related("source", "ensemble", "folder").filter(source__id=id_source, deleted=False)    
    o = {
         "files": UR.qs2dict(ownership, __NAMES["files2"] , "ID"),
         "ensembles": UR.qs2dict(ownership, __NAMES["ensembles2"] , "ID") ,
         "folders": UR.qs2dict(ownership, __NAMES["folders2"] , "ID") ,
         }
    if len(ownership)==1:
        if ownership[0].source.type == M.Source.TYPE_YOUTUBE: 
            o["youtubeinfos"]= UR.model2dict(ownership[0].source.youtubeinfo, None, "id")        
    return o

def getPublicCommentsByFile(id_source):
    names_location = __NAMES["location_v_comment2"]
    names_comment = __NAMES["comment2"]
    comments = M.Comment.objects.extra(select={"admin": 'select cast(admin as integer) from base_membership where base_membership.user_id=base_comment.author_id and base_membership.ensemble_id = base_location.ensemble_id'}).select_related("location", "author").filter(location__source__id=id_source, deleted=False, moderated=False, type__gt=2)   
    locations_dict = UR.qs2dict(comments, names_location, "ID")
    comments_dict =  UR.qs2dict(comments, names_comment, "ID")
    #Anonymous comments
    for k,c in comments_dict.iteritems(): 
        if c["type"] < 3: 
            c["fullname"]="Anonymous"
            c["id_author"]=0             
    return locations_dict, comments_dict

def getCommentsByFile(id_source, uid, after):
    names_location = __NAMES["location_v_comment2"]
    names_comment = __NAMES["comment2"]
    ensembles_im_admin = M.Ensemble.objects.filter(
        membership__in=M.Membership.objects.filter(user__id=uid).filter(admin=True)
    )
    locations_im_admin = M.Location.objects.filter(ensemble__in=ensembles_im_admin)
    comments = (
        M.Comment.objects.extra(
            select={
                "admin": "select cast(admin as integer) from base_membership where base_membership.user_id=base_comment.author_id and base_membership.ensemble_id = base_location.ensemble_id"
            }
        )
        .select_related("location", "author")
        .filter(location__source__id=id_source, deleted=False, moderated=False)
        .filter(Q(location__in=locations_im_admin, type__gt=1) | Q(author__id=uid) | Q(type__gt=2))
    )
    membership = M.Membership.objects.filter(user__id=uid, ensemble__ownership__source__id=id_source, deleted=False)[0]

    if membership.section is not None:
        seen = M.CommentSeen.objects.filter(comment__location__source__id=id_source, user__id=uid)
        # idea: we let you see comments
        # - that are in your current section
        # - that aren't in any section
        # - that you've seen before
        # - that you've authored.
        comments = comments.filter(
            Q(location__section=membership.section)
            | Q(location__section=None)
            | Q(location__comment__in=seen.values_list("comment_id"))
            | Q(author__id=uid)
        )
    threadmarks = M.ThreadMark.objects.filter(location__in=comments.values_list("location_id"))
    if after is not None:
        comments = comments.filter(ctime__gt=after)
        threadmarks = threadmarks.filter(ctime__gt=after)

    html5locations = M.HTML5Location.objects.filter(location__comment__in=comments)
    locations_dict = UR.qs2dict(comments, names_location, "ID")
    comments_dict = UR.qs2dict(comments, names_comment, "ID")
    html5locations_dict = UR.qs2dict(html5locations, __NAMES["html5location"], "ID")
    threadmarks_dict = UR.qs2dict(threadmarks, __NAMES["threadmark"], "id")
    # Anonymous comments
    ensembles_im_admin_ids = [o.id for o in ensembles_im_admin]
    for k, c in comments_dict.iteritems():
        if not c["signed"] and not (
            locations_dict[c["ID_location"]]["id_ensemble"] in ensembles_im_admin_ids or uid == c["id_author"]
        ):
            c["fullname"] = "Anonymous"
            c["id_author"] = 0
    return locations_dict, html5locations_dict, comments_dict, threadmarks_dict

def get_files(uid, payload):
    id = payload["id"] if "id" in payload else None
    names = __NAMES["files2"]
    my_memberships = M.Membership.objects.filter(user__id=uid, deleted=False)
    my_ensembles = M.Ensemble.objects.filter(membership__in=my_memberships)
    my_ownerships = M.Ownership.objects.select_related("source").filter(ensemble__in=my_ensembles, deleted=False)
    return UR.qs2dict(my_ownerships, names, "ID")

def get_stats_ensemble(payload):    
    import db
    id_ensemble = payload["id_ensemble"] 
    names = {
        "ID": "record_id",
        "cnt": None
        }
    from_clause = """(select count(id) as cnt,  author_id || '_' || source_id as record_id from base_v_comment where type>1 and ensemble_id=? group by author_id, source_id) as v1"""
    retval={"stats":  db.Db().getIndexedObjects(names, "ID", from_clause, "true" , (id_ensemble,))}
    grades = M.AssignmentGrade.objects.filter(source__ownership__ensemble__id=id_ensemble)
    ownerships = M.Ownership.objects.select_related("source", "ensemble", "folder").filter(ensemble__id=id_ensemble, deleted=False)
    memberships = M.Membership.objects.select_related("user").filter(ensemble__id=id_ensemble, deleted=False)    
    retval["users"] =  UR.qs2dict(memberships, __NAMES["members"] , "ID")
    retval ["files"] =  UR.qs2dict(ownerships, __NAMES["files2"] , "ID")
    retval["ensembles"] = UR.qs2dict(ownerships, __NAMES["ensembles2"] , "ID") 
    retval["grades"] = UR.qs2dict(grades, __NAMES["assignment_grade"], "id")
    return retval

def get_comments_collection(uid, P):
    output = {}
    comments_refs = M.Comment.objects.filter(id__in=P["comments"], deleted=False, moderated=False)
    locations= M.Location.objects.filter(comment__in=comments_refs)
    html5locations = M.HTML5Location.objects.filter(location__in=locations)
    locations_im_admin = locations.filter(ensemble__in= M.Ensemble.objects.filter(membership__in=M.Membership.objects.filter(user__id=uid).filter(admin=True)))                                        
    comments =  M.Comment.objects.extra(select={"admin": 'select cast(admin as integer) from base_membership,  base_location where base_membership.user_id=base_comment.author_id and base_membership.ensemble_id = base_location.ensemble_id and base_location.id = base_comment.location_id'}).select_related("location", "author").filter(deleted=False, moderated=False, location__in=locations).filter(Q(location__in=locations_im_admin, type__gt=1) | Q(author__id=uid) | Q(type__gt=2))
    ensembles = M.Ensemble.objects.filter(location__in=locations)
    files = M.Source.objects.filter(location__in=locations)
    ownerships = M.Ownership.objects.select_related("source", "ensemble", "folder").filter(source__in=files, ensemble__in=ensembles)
    seen = M.CommentSeen.objects.select_related("comment").filter(comment__in=comments).filter(user__id=uid)
    output["ensembles"]=UR.qs2dict(ownerships, __NAMES["ensembles2"] , "ID") 
    output["files"]=UR.qs2dict(ownerships, __NAMES["files2"] , "ID") 
    output["folders"]=UR.qs2dict(ownerships, __NAMES["folders2"] , "ID") 
    output["locations"] = UR.qs2dict( comments, __NAMES["location_v_comment2"], "ID")
    output["html5locations"] = UR.qs2dict( html5locations, __NAMES["html5locations"], "ID")    
    comments_dict =  UR.qs2dict( comments, __NAMES["comment2"] , "ID")
    #Anonymous comments
    for k,c in comments_dict.iteritems(): 
        if c["type"] < 3: 
            c["fullname"]="Anonymous"
            c["id_author"]=0
    output["comments"] = comments_dict
    output["seen"] = UR.qs2dict(seen, {"id": None, "id_location": "comment.location_id"}, "id")    
    return output   

def get_folders(uid, payload):
    id = payload["id"] if "id" in payload else None
    names = {"ID": "id", "id_parent": "parent_id", "id_ensemble": "ensemble_id", "name": None}
    my_memberships = M.Membership.objects.filter(user__id=uid, deleted=False)
    my_ensembles = M.Ensemble.objects.filter(membership__in=my_memberships)
    my_folders = M.Folder.objects.filter(ensemble__in=my_ensembles)
    if id is not None:
        my_folders = my_folders.filter(id=id)
    return UR.qs2dict(my_folders, names, "ID")

def getMark(uid, payload):
    names = {
        "ID": "id",
        "type": None,
        }    
    comment = M.Comment.objects.get(pk=int(payload["id_comment"]))
    user = M.User.objects.get(pk=uid)
    o = M.Mark.objects.filter(comment=comment, user=user)
    return UR.qs2dict(o, names, "ID")    

def get_stats_ensemble(payload):
    import db

    id_ensemble = payload["id_ensemble"]
    names = {"ID": "record_id", "cnt": None, "numchars": None, "numwords": None}
    from_clause = """(select count(v.id) as cnt,  v.author_id || '_' || v.source_id as record_id, sum(length(c.body)) as numchars, sum(array_length(regexp_split_to_array(c.body, E'\\\\s+'), 1)) as numwords from base_v_comment v, base_comment c where v.type>1 and v.ensemble_id=? and v.id=c.id group by v.author_id, v.source_id) as v1"""
    retval = {"stats": db.Db().getIndexedObjects(names, "ID", from_clause, "true", (id_ensemble,))}
    grades = M.AssignmentGrade.objects.filter(source__ownership__ensemble__id=id_ensemble)
    ownerships = M.Ownership.objects.select_related("source", "ensemble", "folder").filter(
        ensemble__id=id_ensemble, deleted=False
    )
    memberships = M.Membership.objects.select_related("user").filter(ensemble__id=id_ensemble, deleted=False)
    sections = M.Section.objects.filter(membership__in=memberships)
    retval["users"] = UR.qs2dict(memberships, __NAMES["members"], "ID")
    retval["files"] = UR.qs2dict(ownerships, __NAMES["files2"], "ID")
    retval["ensembles"] = UR.qs2dict(ownerships, __NAMES["ensembles2"], "ID")
    retval["grades"] = UR.qs2dict(grades, __NAMES["assignment_grade"], "id")
    retval["sections"] = UR.qs2dict(sections)
    return retval

def getLocation(id):
    """Returns an "enriched" location"""    
    o = M.Comment.objects.select_related("location").filter(location__id=id, parent__id=None, deleted=False)
    loc_dict = UR.qs2dict(o, __NAMES["location_v_comment2"], "ID")
    h5l = None
    try: 
        h5l = o[0].location.html5location if len(o) else None
    except M.HTML5Location.DoesNotExist: 
        pass
    h5l_dict = UR.model2dict(h5l, __NAMES["html5location"], "ID") if h5l else {}
    return (loc_dict, h5l_dict)

def get_comments_auth(uid, P):
    output = {}
    id_ensemble = False
    id_source = False
    if "id_ensemble" in P:
        id_ensemble = P["id_ensemble"]
    if "id_source" in P:
        id_source = P["id_source"]
    comments_authored = M.Comment.objects.filter(author__id=uid, deleted=False, moderated=False)
    if id_ensemble:
        comments_authored = comments_authored.filter(location__ensemble__id=id_ensemble)
    if id_source:
        comments_authored = comments_authored.filter(location__source__id=id_source)
    locations = M.Location.objects.filter(comment__in=comments_authored)
    locations_im_admin = locations.filter(
        ensemble__in=M.Ensemble.objects.filter(
            membership__in=M.Membership.objects.filter(user__id=uid).filter(admin=True)
        )
    )
    comments = (
        M.Comment.objects.extra(
            select={
                "admin": "select cast(admin as integer) from base_membership,  base_location where base_membership.user_id=base_comment.author_id and base_membership.ensemble_id = base_location.ensemble_id and base_location.id = base_comment.location_id"
            }
        )
        .select_related("location", "author")
        .filter(deleted=False, moderated=False, location__in=locations)
        .filter(Q(location__in=locations_im_admin, type__gt=1) | Q(author__id=uid) | Q(type__gt=2))
    )
    ensembles = M.Ensemble.objects.filter(location__in=locations)
    files = M.Source.objects.filter(location__in=locations)
    ownerships = M.Ownership.objects.select_related("source", "ensemble", "folder").filter(
        source__in=files, ensemble__in=ensembles
    )
    seen = M.CommentSeen.objects.select_related("comment").filter(comment__in=comments).filter(user__id=uid)
    sequence = comments_authored.values_list("id", flat=True).order_by("-id")
    output["ensembles"] = UR.qs2dict(ownerships, __NAMES["ensembles2"], "ID")
    output["files"] = UR.qs2dict(ownerships, __NAMES["files2"], "ID")
    output["folders"] = UR.qs2dict(ownerships, __NAMES["folders2"], "ID")
    output["locations"] = UR.qs2dict(comments, __NAMES["location_v_comment2"], "ID")
    comments_dict = UR.qs2dict(comments, __NAMES["comment2"], "ID")
    # Anonymous comments
    for k, c in comments_dict.iteritems():
        if c["type"] < 3:
            c["fullname"] = "Anonymous"
            c["id_author"] = 0
    output["comments"] = comments_dict
    output["seen"] = UR.qs2dict(seen, {"id": None, "id_location": "comment.location_id"}, "id")
    description = "My Notes "
    if id_ensemble and ownerships.count():
        description += "on %s " % (ownerships[0].ensemble.name,)
    if id_source and ownerships.count():
        description += "on %s " % (ownerships[0].source.title,)
    description += "(%s comments)" % (comments_authored.count(),)
    output["sequence"] = {"type": "comment", "data": list(sequence), "description": description}
    return output

def get_ensembles(uid, payload):
    id = payload["id"] if "id" in payload else None
    names = {
        "ID": "ensemble_id",
        "name": "ensemble.name",
        "admin": None,
        "description": "ensemble.description",
        "allow_staffonly": "ensemble.allow_staffonly",
        "allow_anonymous": "ensemble.allow_anonymous",
        "allow_guest": "ensemble.allow_guest", 
        "allow_download": "ensemble.allow_download", 
         }
    my_memberships = M.Membership.objects.select_related("ensemble").filter(user__id=uid, deleted=False)
    if id is not None: 
        my_memberships = my_memberships.filter(ensemble__id=id)
    return UR.qs2dict(my_memberships, names, "ID")

def get_comments_auth_admin(uid, P):
    output = {}
    id_ensemble = P.get("id_ensemble", False)
    id_source   = P.get("id_source", False)
    id_author   = P.get("id_author", False)
    unread      = P.get("unread", False)
        
    locations_im_admin = M.Location.objects.filter(ensemble__in= M.Ensemble.objects.filter(membership__in=M.Membership.objects.filter(user__id=uid).filter(admin=True)))
    if id_author: 
        locations_im_admin = locations_im_admin.filter(id__in=M.Location.objects.filter(comment__in=M.Comment.objects.filter(author__id=id_author, deleted=False, moderated=False)))        
    if id_ensemble: 
        locations_im_admin=locations_im_admin.filter(ensemble__id=id_ensemble)
    if id_source: 
        locations_im_admin=locations_im_admin.filter(source__id=id_source)
   
    comments =  M.Comment.objects.extra(select={"admin": 'select max(cast(admin as integer)) from base_membership,  base_location where base_membership.user_id=base_comment.author_id and base_membership.ensemble_id = base_location.ensemble_id and base_location.id = base_comment.location_id'}).select_related("location", "author").filter(deleted=False, moderated=False, location__in=locations_im_admin).filter(Q(type__gt=1) | Q(author__id=uid))
    seen = M.CommentSeen.objects.select_related("comment").filter(comment__in=comments).filter(user__id=uid)
    if unread: 
        comments_unread = comments.exclude(commentseen__in=seen)
        locations_im_admin = locations_im_admin.filter(comment__in=comments_unread)
        comments =  M.Comment.objects.extra(select={"admin": 'select max(cast(admin as integer)) from base_membership,  base_location where base_membership.user_id=base_comment.author_id and base_membership.ensemble_id = base_location.ensemble_id and base_location.id = base_comment.location_id'}).select_related("location", "author").filter(deleted=False, moderated=False, location__in=locations_im_admin).filter(Q(type__gt=1) | Q(author__id=uid))
        seen = M.CommentSeen.objects.select_related("comment").filter(comment__in=comments).filter(user__id=uid)        
    ensembles = M.Ensemble.objects.filter(location__in=locations_im_admin)
    files = M.Source.objects.filter(location__in=locations_im_admin)
    ownerships = M.Ownership.objects.select_related("source", "ensemble", "folder").filter(source__in=files, ensemble__in=ensembles)
        
    sequence = comments.values_list('id', flat=True).order_by('-id')
    output["ensembles"]=UR.qs2dict(ownerships, __NAMES["ensembles2"] , "ID") 
    output["files"]=UR.qs2dict(ownerships, __NAMES["files2"] , "ID") 
    output["folders"]=UR.qs2dict(ownerships, __NAMES["folders2"] , "ID") 
    output["locations"] = UR.qs2dict( comments, __NAMES["location_v_comment2"], "ID")
    output["comments"] =  UR.qs2dict( comments, __NAMES["comment2"] , "ID")
    output["seen"] = UR.qs2dict(seen, {"id": None, "id_location": "comment.location_id"}, "id")
    description =  "Notes "
    if id_author:
       author = M.User.objects.get(pk=id_author)
       description += "from %s %s " % (author.firstname, author.lastname)         
    if id_ensemble and ownerships.count(): 
       description += "on %s " % (ownerships[0].ensemble.name,)
    if id_source and ownerships.count(): 
       description += "on %s " % (ownerships[0].source.title,)
    if unread: 
        description = "Unread "+description
    description += "(%s comments)" % (comments.count(),)    
    output["sequence"] = {"type": "comment", "data": list(sequence), "description": description}
    return output

def getLocation(id):
    """Returns an "enriched" location"""    
    o = M.Comment.objects.select_related("location").filter(location__id=id, parent__id=None, deleted=False) 
    return UR.qs2dict(o, __NAMES["location_v_comment2"], "ID")

def getSeenByFile(id_source, uid):
    names = {"id": "comment.id", "id_location": "comment.location_id"}
    locations = M.Location.objects.filter(source__id=id_source)
    comments = M.Comment.objects.filter(location__in=locations)
    seen = M.CommentSeen.objects.select_related("comment").filter(comment__in=comments).filter(user__id=uid)
    return UR.qs2dict(seen, names, "id")

def get_settings(uid, payload):
    ds = M.DefaultSetting.objects.all()
    us = M.UserSetting.objects.filter(user__id=uid)
    sl = M.SettingLabel.objects.all()
    retval =  {"ds": UR.qs2dict(ds), "us":UR.qs2dict(us), "sl":UR.qs2dict(sl)}
    return retval