def team_invite():
	params = utils.flat_multi(request.form)
	_user = user.get_user().first()
	_team = get_team(tid=_user.tid).first()
	if _user.uid != _team.owner:
		raise WebException("You must be the captain of your team to invite members!")
	if _team.finalized:
		raise WebException("This team is finalized.")

	new_member = params.get("new_member")
	if new_member is None:
		raise WebException("Please provide a username!")
	_user2 = user.get_user(username_lower=new_member.lower()).first()
	if _user2 is None:
		raise WebException("User doesn't exist!")
	if _user2.tid > 0:
		raise WebException("This user is already a part of a team!")

	if _team.get_pending_invitations(toid=_user2.uid) is not None:
		raise WebException("You've already invited this member!")

	req = TeamInvitations(0, _team.tid, _user2.uid)
	with app.app_context():
		db.session.add(req)
		db.session.commit()
		db.session.close()

	return { "success": 1, "message": "Success!" }

def team_accept_invite_request():
	params = utils.flat_multi(request.form)
	_user = user.get_user().first()
	if not user.in_team(_user):
		raise WebException("You must be in a team!")
	_team = get_team(tid=_user.tid).first()
	tid = _team.tid
	if _user.uid != _team.owner:
		raise WebException("You must be the captain of your team to rescind invitations!")
	if _team.finalized:
		raise WebException("This team is finalized.")

	if len(_team.get_members()) >= utils.get_config("team_size"):
		raise WebException("Your team is full.")

	uid = params.get("uid")
	_user2 = user.get_user(uid=uid).first()
	if user.in_team(_user2):
		raise WebException("This user is already in a team!")

	invitation = TeamInvitations.query.filter_by(rtype=1, frid=_user2.uid, toid=tid).first()
	if invitation is None:
		raise WebException("Invitation doesn't exist.")

	with app.app_context():
		_user2 = Users.query.filter_by(uid=_user2.uid).first()
		_user2.tid = tid
		db.session.delete(invitation)
		invitation2 = TeamInvitations.query.filter_by(rtype=0, frid=tid, toid=_user2.uid).first()
		if invitation2 is not None:
			db.session.delete(invitation2)
		db.session.commit()
		db.session.close()

	return { "success": 1, "message": "Success!" }

def send_mail(from_id, to_id, title, body):
    sender_info = user.get_user(from_id)
    receiver_info = user.get_user(to_id)
    if not sender_info[0]:
        return (False, _('INVALID_SENDER'))
    if not receiver_info[0]:
        return (False, _('INVALID_RECEIVER'))
    if title.strip() == '':
        return (False, _('EMPTY_TITLE'))
    if body.strip() == '':
        return (False, _('EMPTY_BODY'))

    t = db.transaction()
    try:
        db.insert('Mails', mSenderSerial = from_id,
                mReceiverSerial = to_id,
                mSenderId = sender_info[1].uId,
                mSenderNick = sender_info[1].uNick,
                mDatetime = web.SQLLiteral('NOW()'),
                mTitle = title,
                mContent = body)
    except:
        t.rollback()
        return (False, _('DATABASE_ERROR'))
    else:
        t.commit()
    return (True, _('SUCCESS'))

def query_show(query_id):
    query = g.conn.session.query(Query).filter(Query.id == query_id).one()
    can_edit = get_user() is not None and get_user().id == query.user_id
    is_starred = False
    if get_user():
        is_starred = g.conn.session.query(func.count(Star.id))\
            .filter(Star.user_id == get_user().id)\
            .filter(Star.query_id == query_id).scalar() == 1
    jsvars = {
        'query_id': query.id,
        'can_edit': can_edit,
        'is_starred': is_starred,
        'published': query.published
    }

    if query.latest_rev and query.latest_rev.latest_run_id:
        jsvars['qrun_id'] = query.latest_rev.latest_run_id

    return render_template(
        "query/view.html",
        user=get_user(),
        query=query,
        jsvars=jsvars,
        latest_rev=query.latest_rev
    )

    def modify_post(self, current_uid = -1):
        user_id = current_uid
        usr = user.get_user(user_id)[1]

        data = web.input(profile_image = {})
        if data.newpass1 and not user.verify_password(user_id, data.oldpass):
            return util.render().error(error_message=_('INVALID_PASSWORD'), help_context='error')
        if data.newpass1 != data.newpass2:
            return util.render().error(error_message = _('PASSWORD_DO_NOT_MATCH'), help_context='error')
        if len(data.newpass1) > 0 and len(data.newpass1) < 6:
            return util.render().error(error_message = _('PASSWORD_TOO_SHORT'), help_context='error')
        if len(data.newpass1) == 0:
            password = data.oldpass
        else:
            password = data.newpass1
        nick = data.nick
        email = data.email
        homepage = data.homepage
        sig = data.sig
        introduction = data.introduction
        language = data.language
        theme = data.theme
        user_info = user.get_user(user_id)
        change_lang = False
        if language != user_info[1].language:
            change_lang = True
        profile_image = data.profile_image.value
        delete_profile_image = data.has_key('delete_profile_image')

        ret = user.modify_user(user_id, locals())
        if change_lang:
            web.ctx.session.lang = language
        raise web.seeother(util.link('/+u'))

def new_query():
    if get_user() is None:
        return redirect("/login?next=/query/new")
    query = Query()
    query.user = get_user()
    g.conn.session.add(query)
    g.conn.session.commit()
    return redirect(url_for('query_show', query_id=query.id))

    def modify_get(self, current_uid = -1):
        user_id = current_uid
        usr = user.get_user(user_id)[1]

        referer = util.link('/+u')
        return util.render().myinfo_edit(user = user.get_user(user_id)[1],
                user_id = user_id,
                title = _('Edit My Information'),
                board_desc = _('Edit My Information'),
                referer = web.ctx.env.get('HTTP_REFERER', referer),
                help_context = 'myinfo')

def star_query():
    if get_user() is None:
        return "Unauthorized access", 403
    query = g.conn.session.query(Query).get(request.form['query_id'])
    if query:
        star = Star()
        star.user = get_user()
        star.query = query
        g.conn.session.add(star)
        g.conn.session.commit()
        return ""
    else:
        return "Query not found", 404

def unstar_query():
    if get_user() is None:
        return "Unauthorized access", 403
    query = g.conn.session.query(Query).get(request.form['query_id'])
    if query:
        star = g.conn.session.query(Star)\
            .filter(Star.query_id == request.form['query_id'])\
            .filter(Star.user_id == get_user().id)\
            .one()
        g.conn.session.delete(star)
        g.conn.session.commit()
        return ""
    else:
        return "Query not found", 404

def update_association(user_id, client_id, refresh_token_str):
    client = get_client(client_id)
    user = get_user(user_id)
    logging.warn('update_associations 1: ' + str(refresh_token_str))
    refresh_token = get_token(client_id, client.secret, refresh_token_str)
    #always check to see if it is confidential or not.
    #it shouldn't be if it's using update_association, but you never know
    #and it's good to have a log message to possible alert the admin that
    #this is going on.
    if client.type.lower() != 'confidential':
        raise ConfidentailError('Client ' + client_id + \
                                ' is not a confidentail client')

    db = DB()
    try:
        key = 'client_association_' + str(user.id)
        if db.contains(key):
            association = db.get(key)
            if client.id in association.clients:
                logging.warn('update_associations 2: ' + str(association.clients[client.id]))
                old_refresh = get_token(client.id, client.secret, association.clients[client.id])
                delete_token(old_refresh.access_token)
                delete_token(old_refresh.code)
                association.clients[client.id] = refresh_token.code
                logging.warn('update_associations 3: ' + str(refresh_token.code) + ', ' + str(association.clients[client.id]))
                db.update(key, association)
                db.commit()
    #except Exception, e:
    #    logging.error('update_associations: ' + str(e))
    #    db.abort()
    finally:
        db.close()

    return False

def team_accept_invite():
	params = utils.flat_multi(request.form)
	_user = user.get_user().first()
	if user.in_team(_user):
		raise WebException("You're already in a team!")

	tid = params.get("tid")
	_team = get_team(tid=tid).first()
	if _team is None:
		raise WebException("Team not found.")

	if len(_team.get_members()) >= utils.get_config("team_size"):
		raise WebException("This team is full.")

	invitation = TeamInvitations.query.filter_by(rtype=0, frid=tid, toid=_user.uid).first()
	if invitation is None:
		raise WebException("Invitation doesn't exist.")

	with app.app_context():
		_user = Users.query.filter_by(uid=_user.uid).first()
		_user.tid = tid
		db.session.delete(invitation)
		invitation2 = TeamInvitations.query.filter_by(rtype=1, frid=_user.uid, toid=tid).first()
		if invitation2 is not None:
			db.session.delete(invitation2)
		db.session.add(Activity(_user.uid, 6, _team.tid, -1))
		db.session.commit()
		db.session.close()

	return { "success": 1, "message": "Success!" }

def api_run_query():
    if get_user() is None:
        return "Authentication required", 401
    text = request.form['text']
    query = g.conn.session.query(Query).filter(Query.id == request.form['query_id']).one()

    if query.latest_rev and query.latest_rev.latest_run:
        result = worker.run_query.AsyncResult(query.latest_rev.latest_run.task_id)
        if not result.ready():
            result.revoke(terminate=True)
            query.latest_rev.latest_run.status = QueryRun.STATUS_SUPERSEDED
            g.conn.session.add(query.latest_rev.latest_run)
            g.conn.session.commit()

    query_rev = QueryRevision(query_id=query.id, text=text)
    query.latest_rev = query_rev

    # XXX (phuedx, 2014/08/08): This deviates from the pre-existing
    # QueryRevision interface, but I'm not confident that SQLAlchemy would
    # invalidate a cached result for a relationship if a property changed.
    query_run = QueryRun()
    query_run.rev = query_rev
    query_run.status = QueryRun.STATUS_QUEUED

    g.conn.session.add(query_run)
    g.conn.session.add(query)
    g.conn.session.commit()
    query_rev.latest_run = query_run
    query_run.task_id = worker.run_query.delay(query_run.id).task_id
    g.conn.session.add(query_rev)
    g.conn.session.add(query_run)
    g.conn.session.commit()
    return json.dumps({
        'qrun_id': query_run.id
    })

def create(request):
    user = get_user(request)
    
    if 'project_key' in request.params:
        # User submitted a project.
        # TODO: Validation.
        try: 
            new_project = request.context.newProject(
                title=request.params['project_title'],
                key=request.params['project_key'],
                description=request.params['project_description'],
                url_name=request.params['project_url'],
                creator_id=user.id)
                
            participation = Participation(user=user, project=new_project)
            session = DBSession()
            session.add(participation)
            session.flush()
            note.new_project(request, new_project, user)
        except IntegrityError:
            # url_name collision.
            raise HTTPBadRequest(explanation="Sorry! That URL has already been taken!")
        return HTTPFound(location=request.root.project_url(new_project)+'/invite')
    else:
        return {'user' : user_dict(request, user)}

def show_userpage(request):
    session = DBSession()
    
    other_user = request.context
        
    current_user = get_user(request)
    shared_projects = []
    for project in current_user.projects:
        if project in other_user.projects:
            shared_projects += [project]
    
    project_data = []
    for project in shared_projects:
        data = {}
        data['title'] = project.title
        # How many ideas and comments other_user has posted to this project.
        data['idea_count'] = project.ideas.filter(Idea.author==other_user).count()
        data['comment_count'] = project.comments.filter(Comment.author==other_user).count()
        data['url'] = request.root.project_url(project)
        project_data += [data]

    return {
        'first_name' : other_user.first_name, 
        'last_name' : other_user.last_name, 
        'profile_picture' : other_user.profile_picture,
        'user' : current_user,
        'shared_project_count' : len(project_data),
        'projects' : project_data
    }

def display_project_people(request):
    cur_user = get_user(request)
    session = DBSession()
        
    project = request.context
    
    participants = session.query(Participation, User).outerjoin(User).filter(Participation.project == project).all()
    people_data = []
    email_data = []
    for participant, user in participants:
        person_data = {}
        if user:
            person_data['first_name'] = user.first_name
            person_data['last_name'] = user.last_name
            person_data['profile_url'] = request.root.user_url(user)
            person_data['invite_accepted'] = participant.access_time is not None
            person_data['profile_picture'] = user.profile_picture
            people_data += [person_data]
        else:
            email_data += [participant.user_email]
    
    # Sort by first last name
    people_data.sort(key=lambda user: user['first_name'] + u' ' + user['last_name'])
    
    
    return template_permissions(request, {
        'people' : people_data,
        'invited_emails' : email_data,
        'project_id' : project.id,
        'project' : project, 
        'creator' : user_dict(request, project.creator),
        'user' : user_dict(request, cur_user), 
        'ideas_count': project.ideas.count(), 
        'people_count': len(active_users(project)),
    })

def project(request):
    user = get_user(request)
    session = DBSession()
    project = request.context
        
    updates = session.query(ProjectUpdate)\
        .filter(ProjectUpdate.project_id==project.id)\
        .filter(ProjectUpdate.when >= utcnow() - timedelta(days=10))\
        .order_by(ProjectUpdate.when.desc()).limit(10).all()
    
    # Update user access time:
    if user:
        part = session.query(Participation).filter(Participation.user_email==user.email).filter(Participation.project_id==project.id).first()
        if not part or not part.access_time:
            note.new_participant(request, request.context, user)  
        part.access_time = utcnow()
        session.flush()
              
    return template_permissions(request, {
        'project_id' : project.id,
        'project' : project, 
        'creator' : user_dict(request, project.creator),
        'updates' : updates,
        'ideas': project.ideas.all(), 
        'user' : user_dict(request, user), 
        'can_update' : user == project.creator,
        'ideas_count': project.ideas.count(), 
        'people_count': 1
    })

    def get_user(self, nick):
        """Returns a user object for the specified nick.

        @returns user.User()
        """

        return user.get_user(nick)

def user_mgr():
    if session.get("user") == "admin":
        users = user.get_user()
        # print users
        return render_template("/user_mgr.html", users=users)
    else:
        return redirect("/index")

def ideas(request):
    # Optional parameters: sort="user" | "rating" | "date"
    user = get_user(request)
    session = DBSession()
    project = request.context
    # Create list of ideas with User's rating added:
    if user:
        idea_query = session.query(Idea, AggregateRating, UserRating)\
            .outerjoin(AggregateRating, (AggregateRating.idea_id==Idea.id))\
            .outerjoin(UserRating, (Idea.id==UserRating.idea_id) & (UserRating.user_id==user.id))\
            .join(User, (Idea.author_id==User.id))\
            .filter(Idea.project_id == project.id)
    else:
         # Just fill third column with Nones.
        idea_query = session.query(Idea, AggregateRating, UserRating)\
            .outerjoin(AggregateRating, (AggregateRating.idea_id==Idea.id))\
            .outerjoin(UserRating, (UserRating.idea_id==0))\
            .join(User, (Idea.author_id==User.id))\
            .filter(Idea.project_id == project.id)
            
    
    sort = request.params.get('sort', 'rating') 
    if sort == 'user':
        # Use Python to sort, so we sort Anonymous posts properly.
        pass
    elif sort == 'rating':
        # Use Python to sort, so we can use our total_rating algorithm..
        pass
    elif sort == 'date':
        idea_query = idea_query.order_by(Idea.creation_time.desc()) # Most recent first
    else:
        logging.warn('Unrecognized sort: ' + str(sort))
    
    idea_data = idea_query.all()
    # Do python sorting.
    if sort == 'user':
        idea_data.sort(key=lambda el:el[0].author.first_name + ' ' + el[0].author.last_name if not el[0].anonymous else 'Anonymous')
    elif sort == 'rating':
        if project.rating_type == 'like/love':
            idea_data.sort(key=lambda el: el[0].aggregate_rating.total_rating if el[0].aggregate_rating else 0, reverse=True)
        else:
            idea_data.sort(key=lambda el: el[0].aggregate_rating.average_stars if el[0].aggregate_rating else 0, reverse=True)
    
    # Turn it all into dicts for our templating engine.
    for i in range(len(idea_data)):
        idea, aggregate_rating, rating = idea_data[i]
        idea_data[i] = idea_dict(request, idea, rating, aggregate_rating, include_comments=True)
        
    
    # Idea.user_rating will be used to determine the initial state of the Like/Love/Stars
    return template_permissions(request, {
        'project' : project, 
        'creator' : user_dict(request, project.creator),
        'project_id' : project.id,
        'idea_data': idea_data, 
        'user' : user_dict(request, user), 
        'ideas_count': len(idea_data), 
        'people_count': 1,
        'sort' : sort
    })

def present_main():
	# check for a cookie, if present, then extract value
	username = login_check()
	if (username == None):
		print "main: can't identify user...redirecting to signup"
		bottle.redirect("/login")
	
	connection = pymongo.Connection(connection_string, safe=True)
	me = user.get_user(connection, username)
	friends = me['friends']
	friends_requests = me['friends_requests']
	challenges_ids = me['challenges']
	challenges = []
	for challenge_id in challenges_ids:
		challenges.append(achievement.find_achievement_by_id(connection, challenge_id))
	
	achievements_ids = me['achievements']
	achievements = []
	for achievement_id in achievements_ids:
		challenges.append(achievement.find_achievement_by_id(connection, achievement_id))
		
	return bottle.template("main", 
						{'username':username, 
						  'friends':friends,
						  'friends_requests':friends_requests,
						  'challenges':challenges,
						  'achievements':achievements,
						})