def mod():
    global __HEXDIGEST__
    if request.method == "GET":
        mess = session.query(EmailMessage).get(__HEXDIGEST__)
        if mess is None or mess.approved == True:
            flash('No new messages to edit')
            return render_template('edit.html')
        dct = json.loads(mess.everything)
        form = EditForm()
        form.subject.data = dct.get('subject')
        form.text.data = dct.get('body-plain')
        form.approved.data = mess.approved
        return render_template('edit.html', form=form) 
    else:
        form = EditForm(request.form)
        mess = session.query(EmailMessage).get(__HEXDIGEST__)
        if form.approved.data == True:
            send_message(form.data)
            mess.approved = True
            session.add(mess)
            session.commit()
            flash('message sent')
            return render_template('edit.html')
        flash("bad form")
        return "bad form"

def terminal_visual():
    print_line_count = 0
    print colored('TIMELORD v.0.0.1', 'white', attrs=['bold'])
    print_line_count += 1

    # work
    todays_work = session.query(func.sum(WorkTime.work)).filter(WorkTime.datetime >= datetime.date.today()).one()
    if todays_work:
        todays_work = todays_work[0]
    if not todays_work:
        todays_work = 0

    print colored('work: ', 'cyan') + \
            colored(str(todays_work / 60.), 'green') + \
            colored(' hrs', 'cyan')
    print_line_count += 1

    # tasks
    print colored('task progress:', 'cyan')
    print_line_count += 1
    todays_progress = session.query(Milestone).filter(Milestone.datetime >= datetime.date.today()).all()
    if todays_progress:
        for tp in todays_progress:
            print colored('    ' + str(tp), 'yellow')
            print_line_count += 1
    else:
        print colored('    none', 'red')
        print_line_count += 1

    for i in range(print_line_count):
        print '\033[1A' + '\r',
        sys.stdout.flush()

def wemo(request, command=None, device=None):
    if not command:
        raise NoCommandSpecified()
    if device:
        device = urllib.unquote(device)

    if command == 'ls':
        devices = session.query(distinct(WemoTimeSeries.device_name)).all()
        response = '<p>'
        for d in devices:
            response += "'" + d[0] + "'</br>"
        response += '</p>'
    
    elif command == 'on':
        wemo = WemoCC().on(device)
        response = '<p>' + device + ' turned on.</p>'
    
    elif command == 'off':
        wemo = WemoCC().off(device)
        response = '<p>' + device + ' turned off.</p>'

    elif command == 'completion':
        completion = []
        devices = session.query(distinct(WemoTimeSeries.device_name)).all()
        for d in devices:
            completion.append(d.lower().replace(' ', '_'))
        response = json.dumps(completion)

    return HttpResponse(response)

    def addEdit(self, _thread_id, _content, _submitted=False):
        existingList = (
            session.query(bot_comments_anetpool)
            .filter_by(thread_id=_thread_id)
            .order_by(desc(bot_comments_anetpool.edit_id))
            .first()
        )

        if existingList is None:
            row = bot_comments_anetpool()
            row.thread_id = _thread_id
            _from_template = self.anetpool_template.split("	", 1)
            row.content = _from_template[0] + _content + "	" + _from_template[1]
            row.submitted = _submitted
            session.add(row)
        else:
            if len(existingList.content) + len(_content) >= 10000:
                row = bot_comments_anetpool()
                row.thread_id = _thread_id
                _from_template = self.anetpool_template.split("	", 1)
                row.content = _from_template[0] + _content + "	" + _from_template[1]
                row.submitted_id = "e" + str(existingList.edit_id)
                row.submitted = _submitted
                session.add(row)
            else:
                _from_save = existingList.content.split("	", 1)
                session.query(bot_comments_anetpool).filter_by(edit_id=existingList.edit_id).update(
                    {"content": _from_save[0] + _content + "	" + _from_save[1], "submitted": False}
                )
        session.commit()

def item_edit(item_title):
    categories = session.query(Category).all()
    item = session.query(Item).filter_by(
        title=item_title).one()
    user = session.query(User).filter_by(
        id=login_session.get('user_id')).one()
    if item.user is not None and item.user.id != user.id:
        return redirect(url_for('home'))
    if request.method == 'POST':
        form = vh.ItemForm(request.form, item)
        file_storage_err = vh.store_item_pic(
            item, request.files['picture'])
        if (not form.validate() or file_storage_err is not None):
            return render_template('item_edit.html',
                                   form=form,
                                   file_err=file_storage_err)
        form.populate_obj(item)
        try:
            session.add(item)
            session.commit()
        except ValueError as e:
            return "Database validation error: " + str(e)
        except SQLAlchemyError as e:
            # todo: log error, but don't display detailed message
            # for security reasons
            return "Database error: " + str(e)
        return redirect(url_for('home'))
    else:
        form = vh.ItemForm(obj=item)
        return render_template('item_edit.html',
                               form=form,
                               file_err=None)

    def project_change(self, name, index, mode):
        project = session.query(Project).filter(Project.name == self.early_p_id.get())[0]
        # What tasks does this project have?
        tasks = session.query(Task).filter(Task.project == project.id)

        if tasks.count() > 0:
            self._fill_tasks(tasks)

def list_responses(account_name):
    a = session.query(Account).filter_by(name=account_name).first()
    responses = session.query(Response).filter_by(from_email=a.email).all()
    if len(responses) == 0:
        message = "This User has made no public statements."
        return render_template('list_posts.html', responses=responses, account=a, message=message)
    return render_template('list_posts.html', responses=responses, account=a)

def home():
    categories = session.query(Category).all()
    # todo: most recent
    items = session.query(Item).order_by(desc(Item.last_update))
    return render_template('home.html',
                           session=login_session,
                           categories=categories,
                           items=items)

 def test_add_tag(self):
     p = session.query(Photo).filter_by(filename='bee.jpg').one()
     self.assertEqual(p.tags, [])
     p.add_tag('Family')
     self.assertEqual(p.tags, [session.query(Tag).filter_by(name='Family').one()])
     self.assertEqual(p.tagnames, ['Family'])
     p.add_tag('family')
     self.assertEqual(len(p.tags), 1)

def delete_test():
    tom = session.query(Teacher).get(1)
    print tom.students
    session.delete(tom)
    # lucy = session.query(Student).get(1)
    # session.delete(lucy)
    session.commit()
    print session.query(Teacher).all()
    print session.query(Student).all()

 def get_members(remove = None):
     """ 获取所有成员
     Arguments:
         `remove`    -   排除成员
     """
     remove_email = get_email(remove)
     if remove:
         return session.query(Member).filter(Member.email != remove_email).all()
     return session.query(Member).all()

def serialize_catalog():
    categories = session.query(Category).all()
    categories_json = [c.serialize for c in categories]
    for category_json in categories_json:
        items = session.query(Item).filter_by(
            category_id=category_json['id']).all()
        if len(items) > 0:
            category_json['Item'] = [
                i.serialize for i in items]
    return {'Category': categories_json}

 def __delete_old_data(self, pdbs):
     """When recalculate=True, delete all data from pdb_redundant_nucleotides
     and set pdb_analysis_status to None in the redundant_nts column"""
     session.query(RedundantNucleotide).filter(RedundantNucleotide.pdb_id.in_(pdbs)).delete(
         synchronize_session="fetch"
     )
     for statusObj in session.query(PdbAnalysisStatus).filter(PdbAnalysisStatus.id.in_(pdbs)).all():
         statusObj.redundant_nts = None
         session.merge(statusObj)
     session.commit()

 def __delete_old_data(self, pdbs):
     """When recalculate=True, delete all data from pdb_best_chains_and_models
     and set pdb_analysis_status to None in the best_chains_and_models column"""
     session.query(PdbBestChainsAndModels).filter(PdbBestChainsAndModels.pdb_id.in_(pdbs)).delete(
         synchronize_session="fetch"
     )
     for statusObj in session.query(PdbAnalysisStatus).filter(PdbAnalysisStatus.id.in_(pdbs)).all():
         statusObj.best_chains_and_models = None
         session.merge(statusObj)
     session.commit()

    def list(self):
        address_list = []
        if "limit" in self.data:
            query = session.query(Endereco).limit(self.data["limit"])
        else:
            query = query = session.query(Endereco)

        for q in query:
            address_list.append(to_json(q))

        return address_list

def items_list(category_name):
    category = session.query(Category).filter_by(
        name=category_name).one()
    categories = session.query(Category).all()
    items = session.query(Item).filter_by(
        category_id=category.id).all()
    return render_template('items.html',
                           session=login_session,
                           categories=categories,
                           category=category,
                           items=items)

 def updateLatestObjectIds(self, _last_comment_id, _last_submission_id):
     lastIds = session.query(subreddit).filter_by(website="reddit").first()
     if lastIds == None:
         row = subreddit()
         row.website = "reddit"
         row.last_comment = _last_comment_id
         row.last_submission = _last_submission_id
         session.add(row)
     else:
         session.query(subreddit).filter_by(website="reddit").update(
             {"last_submission": _last_submission_id, "last_comment": _last_comment_id}
         )

def home(request):

    session.rollback()
    session.commit()

    filter_date = datetime.datetime.utcnow() - datetime.timedelta(seconds=60)
    
    wemo_device_count = session.query(func.count(distinct(WemoTimeSeries.device_name))).first()[0]
    wemo = session.query(WemoTimeSeries).order_by(WemoTimeSeries.datetime.desc()).limit(wemo_device_count).all()

    hue_device_count = session.query(func.count(distinct(HueTimeSeries.device_name))).first()[0]
    hue = session.query(HueTimeSeries).order_by(HueTimeSeries.datetime.desc()).limit(hue_device_count).all()

    nest = session.query(NestTimeSeries).order_by(NestTimeSeries.datetime.desc()).limit(1).first()

    apex = session.query(ApexTimeSeries).filter(ApexTimeSeries.value != None).filter(ApexTimeSeries.datetime>filter_date).all()
   
    roomba_device_count = session.query(func.count(distinct(RoombaTimeSeries.device_name))).first()[0]
    roomba = session.query(RoombaTimeSeries).order_by(RoombaTimeSeries.datetime.desc()).limit(roomba_device_count).all()
    
    f = Flower()
    flower = f.get_data(.001)[-1]
    
    return render(request, template_name='home.html', dictionary={'wemo': wemo, 
                                                                  'hue': hue, 
                                                                  'nest': nest,
                                                                  'apex': apex,
                                                                  'roomba': roomba,
                                                                  'flower': flower,
                                                                 })

def get_create_user(name, email):
    """ get or create user record """
    try:
        user = session.query(User).filter_by(
            email=email).one()
    # todo: catch specific sqlalchemy exception
    except:
        new_user = User(name=name,
                        email=email)
        session.add(new_user)
        session.commit()
        user = session.query(User).filter_by(
            email=email).one()
    return user.id

def login():
    if request.method == 'POST':
        if request.form.get('sign-in') is not None:
            try:
                user = session.query(User).filter_by(
                    email=request.form.get('email')).one()
            except NoResultFound:
                return ("no user record found for email '%s'" %
                        request.form.get('email'))
            if user.password is None:
                return ("user account created with third-party service"
                        "<br>"
                        "sign up locally or sign in with third-party")
            if check_password_hash(user.password,
                                   request.form.get('password')):
                login_session['user_id'] = user.id
            else:
                return "bad password"
        else:
            # request.form.get('sign-up') is not None
            if ((request.form.get('password') !=
                 request.form.get('password-confirm'))):
                return "passwords don't match"
            try:
                user = session.query(User).filter_by(
                    email=request.form.get('email')).one()
            except NoResultFound:
                user = User(email=request.form.get('email'))
            if user.password is not None:
                return "user already registered"
            user.password = generate_password_hash(
                request.form.get('password'))
            user.name = request.form.get('name')
            session.add(user)
            session.commit()
            user = session.query(User).filter_by(
                email=request.form.get('email')).one()
            login_session['user_id'] = user.id
        return redirect(url_for('home'))
    else:
        state = ''.join(
            random.choice(string.ascii_uppercase + string.digits)
            for _ in range(32)
        )
        login_session['state'] = state
        return render_template(
            'login.html',
            state=state,
            G_CLIENT_ID=G_CLIENT_ID,
            GH_CLIENT_ID=app.config['GITHUB_CLIENT_ID'])