def update_page(page, chapter):
    print "Calling %s" % page.page_link
    response = urllib2.urlopen(page.page_link)
    if not (response.code >= 200 and response.code < 300):
        raise Exception("Could not retrieve the page for link . %s" % page.page_link)
    print "Response %s" % response.code
    content = response.read()
    (next_link, image) = get_image_and_next_link(content, page.page_link)
    while next_link is not None:
        if image is None:
            raise Exception("Something went wrong with the lack of image for given page")
        page.image_link = image
        next_page = Page(next_link, chapter)
        session.add(next_page)
        session.commit()
        print "Added Page[%d] %s" % (next_page.id, next_page.page_link)
        page.next_page_id = next_page.id
        session.add(page)
        session.commit()
        print "Update page %d with image %s" % (page.id, page.image_link)
        page = next_page
        response = urllib2.urlopen(page.page_link)
        if not (response.code >= 200 and response.code < 300):
            raise Exception("Could not retrieve the page for link . %s" % page.page_link)
        content = response.read()
        (next_link, image) = get_image_and_next_link(content, page.page_link)

def delete_tutorial(id):
	if not g.user_id:
		return redirect(url_for("index"))
	tutorial = db_session.query(Tutorial).get(id)
	db_session.delete(tutorial)
	db_session.commit()
	return redirect(url_for("list_tutorials"))

def close_trade(id):
	trade = Trade.query.filter_by(id=id).one()
	trade.close_date = datetime.datetime.utcnow()
	db_session.add(trade)
	db_session.commit()
	flash("Your trade has been marked as complete.", "success")
	return redirect("/trade_history")

def sync_photo(id, flickr, check_dirty=False):    
    print id
    db_photo = session.query(Photo).filter(Photo.flickr_id == id).first()
    if db_photo and not check_dirty:
        print 'Photo is already local.'
        return db_photo
    photo = simplejson.loads(flickr.photos_getInfo(photo_id=id, nojsoncallback=1))
    p = photo['photo'] 
    (id, title) = (int(p['id']), p['title']['_content'])
    url = url_for_photo(p)
    page_url = p['urls']['url'][0]['_content']
    description = """%s\n
%s
Taken: %s in %s
Flickr: %s""" % (p['title']['_content'], p['description']['_content'], p['dates']['taken'], loc_to_string(p), page_url)

    if db_photo:
        print "Photo %s already exists" % id
        if db_photo.title == title and db_photo.description == description:
           return db_photo 
        db_photo.dirty = True   
        db_photo.title = title
        db_photo.description = description
    else:    
        url = url_for_photo(p)
        db_photo = Photo(title= title, description=description, flickr_id=id, dirty=False, url=url) 
        if not p['visibility']['ispublic']:
            db_photo.private = True
        session.add(db_photo)
    sync_tags(db_photo, p)
      
    session.commit()

    return db_photo

 def commit(self):
     self.commit_collection(self.batters, Batter)
     self.commit_collection(self.pitchers, Pitcher)
     self.commit_collection(self.teams, Team)
     session.add_all(self.events)
     session.add_all(self.games)
     session.commit()

def load_localcounts(lyrics_data, list_of_wordcounts):
    """
    Adds local wordcounts for each song.
    """
    # i = 0

    for song_dictionary in lyrics_data:
        # if i < 5:
        url = song_dictionary['url']
        # put on your counting shoes
        for k, v in song_dictionary.iteritems():
            lyrics = song_dictionary['lyrics']
            unique_words = {}

            for line in lyrics:
                line = line.lower()
                words = re.findall('\w+', line)

                # unique words for each song
                for word in words:
                    if unique_words.get(word):
                        unique_words[word] += 1
                    else:
                        unique_words[word] = 1

        # make all the localcount rows for that song
        for word, localcount in unique_words.iteritems():
            new_row = LocalCount(song_id = url, term = word, count = localcount)
            print "Adding %r with count of %r" % (new_row.term, new_row.count)
            session.add(new_row)
            # i += 1
        session.commit()
        list_of_wordcounts.append(unique_words)

    return list_of_wordcounts

def create_tables():
    Base.metadata.create_all(engine)
    u = User(email='[email protected]', username='steph')
    u.set_password('unicorn')
    session.add(u)
    u2 = User(email='[email protected]', username='stroud')
    u2.set_password('unicorn')
    session.add(u2)
    b = Book(
        title='The Book of Steph',
        amazon_url='www.smstroud.com',
        owner_id=1
    )
    session.add(b)
    b2 = Book(
        title='Stroud\'s Story',
        amazon_url='www.smstroud.com',
        owner_id=1,
        current_borrower=2
    )
    b_h = BorrowHistory(book_id=2, borrower_id=2, date_borrowed=datetime.now)
    # p = Post(title='test post', body='body of a test post.')
    # u.posts.append(p)
    session.add(b)
    session.add(b2)
    b2.borrow_history.append(b_h)
    session.commit()

def recreate_index():
    '''
    This function indexes the book_info table of the database.
    I'm implimenting tf-idf functionality, so I save the number
    of documents in which the term shows up, and I also save a
    record of the specific documents that contain the term.
    '''

    book_infos = BookInfo.query.all()
    freq_by_id_by_token = defaultdict(Counter)

    for info in book_infos:
        tokens = get_tokens_from_book_info(info)

        for token in tokens:
            freq_by_id_by_token[token][info.id] += 1

    # deletes all search terms before recreating index
    SearchTerm.query.delete()

    for token, frequency_by_id in freq_by_id_by_token.items():

        search_term = SearchTerm(
            token=token,
            num_results=len(frequency_by_id),
            # creates a json string from the `frequency_by_id` dict
            document_ids=json.dumps(frequency_by_id),
        )

        session.add(search_term)

    session.commit()

def index_new_book_info(book_info):
    '''
    This function updates a dictionary containing all tokens for a book.
    New search terms are saved to the SearchTerm table. The key is the
    token, the value is a list of document IDs that contain the token.
    '''

    book_info_ids_by_token = {}

    tokens = get_tokens_from_book_info(book_info)

    for token in tokens:
        if not token in book_info_ids_by_token:
            book_info_ids_by_token[token] = []
        book_info_ids_by_token[token].append(book_info.id)

    for token, book_ids in book_info_ids_by_token.items():

        # TODO: check the DB first before creating new search term
        search_term = SearchTerm(
            token=token,
            num_results=len(book_ids),
            # creates a json string from the book_ids array
            document_ids=json.dumps(book_ids),
        )

        session.add(search_term)

    session.commit()

    return book_info_ids_by_token

 def execute(self):
     if not self.task:
         self.no_active_tasks()
         return
     self.task.add_tomato(self.is_whole)
     session.commit()
     self.task.show_progress()

def login(provider_name):
	response = make_response()
	result = authomatic.login(WerkzeugAdapter(request, response), provider_name)

	if result:
		# If we've received a user from Facebook...
		if result.user:
			# Get the user's profile data and look for it in our database
			result.user.update()
			facebook_id = result.user.id
			user = dbsession.query(User).filter_by(facebook_id = facebook_id).first()

			# If we don't find the user in our database, add it!
			if not user:
				user = User(facebook_id = facebook_id, email=result.user.email, name=result.user.name)
				dbsession.add(user)
				dbsession.commit()

			# Store the user in our session, logging them in 
			login_user(user)

		# Redirect somewhere after log in. In this case, the homepage
		return redirect('/')

	return response

def load_users(session):
    with open("seed_data/u.user", "rb") as user_file:
        reader = csv.reader(user_file, delimiter="|")
        for row in reader:
            user = User(id=row[0], age=row[1], zipcode=row[4])
            session.add(user)
    session.commit()

def load_ratings(session):
    with open("seed_data/u.data", "rb") as ratings_file:
        reader = csv.reader(ratings_file, delimiter="\t")
        for row in reader:
            rating = Rating(user_id=row[0], movie_id=row[1], rating=row[2])
            session.add(rating)
    session.commit()

def load_globalcounts(list_of_wordcounts):
    """
    Adds wordcounts for all unique words. There should only be one row per unique word.
    """
    # i = 0

    for localcount_dict in list_of_wordcounts:
        # if i < 5:
        for word, count in localcount_dict.iteritems():
            item = session.query(GlobalCount).filter(GlobalCount.term == word).first()
            if item:
                print "%r is already in globalcounts. Updating count..." % word
                # update the global count for this word, because we have added new songs with more occurrences of this word
                q = session.query(LocalCount.term, func.sum(LocalCount.count))
                q = q.group_by(LocalCount.term)
                q = q.filter(LocalCount.term == word)
                results = q.all()

                # print "Current count for %r is %d" % (item.term, item.count)
                item.count = results[0][1]
                print "Updating %r's count to %d" % (item.term, item.count)
                session.commit()

            else:
                print "%r not in globalcounts table, creating new row" % word
                qq = session.query(LocalCount.term, func.sum(LocalCount.count))
                qq = qq.group_by(LocalCount.term)
                qq = qq.filter(LocalCount.term == word)
                resultsresults = qq.all()

                countcount = resultsresults[0][1]
                new_row = GlobalCount(term = word, count = countcount)
                session.add(new_row)
                # you must commit before you query the same word/item again!
                session.commit()

def load_artwork(session):
    f2 = unicode_csv_reader(open("artwork_data.csv"), delimiter = ",")
    f2.next()

    for row in f2:
        artwork = model.Artwork()
        artwork.artworkId = int(row[0])
        artwork.artistRole = row[3]
        if int(row[4])!= 19232:
            if int(row[4])!= 5265:
                if int(row[4])!= 3462:
                    if int(row[4])!= 12951:
                        artwork.artistId = int(row[4])
        artwork.title = row[5]
        artwork.dateText = row[6]
        artwork.medium = row[7]
        if row[9].isdigit():
            artwork.year = row[9]
        artwork.dimensions = row[11] 
        if row[12].isdigit():
            artwork.width = row[12]
        if row[13].isdigit():
            artwork.height = row[13]
        if row[15].isdigit():
            artwork.units = row[15]    
        artwork.inscription = row[16]
        artwork.thumbnailCopyright = row[17]
        artwork.thumbnailURL = row[18]
        artwork.url = row[19]

        session.add(artwork)
    session.commit()

def load_songs(lyrics_data):
    """
    Add songs to the songs table.
    """
    # i = 0

    # go through each song dictionary and extract data
    for song_dictionary in lyrics_data:
        # if i < 5:
            # check whether the song already exists in the database
        if session.query(Song).filter(Song.url == song_dictionary['url']).first():
            print "%r is already in the database!" % song_dictionary['songname']
        else:
            # let's turn this song... into a Song!
            # make a new row in the songs table
            url = song_dictionary['url']
            artist = song_dictionary['artist']
            songname = song_dictionary['songname']

            new_song = Song(url = url,
                            artist = artist,
                            songname = songname)

            session.add(new_song)
            print "SUCCESS! %r is such a jam." % new_song.songname
                # i += 1
    session.commit()

def load_rss():
	# query the db: how long is it? Use this number later to empty db of old stories
    exstories = db_session.query(Stories).all()
    last_id = exstories[-1].id
    sources = {"NPR News": 'http://www.npr.org/rss/rss.php?id=1001', "BBC": 'http://feeds.bbci.co.uk/news/rss.xml'}
    for source in sources:
        print source
        # use feedparser to grab & parse the rss feed
        parsed = feedparser.parse(sources[source])
        print "parsed"
        # go through each entry in the RSS feed to pull out elements for Stories
        for i in range(len(parsed.entries)):
            title = parsed.entries[i].title
            url = parsed.entries[i].link
            source = source
            # pull abstract, parse out extra crap that is sometimes included
            abstract = (parsed.entries[i].description.split('<'))[0]
            print abstract

            # connect with db
            story = db_session.Stories(title=title, url=url, abstract=abstract, source=source)
            print "connected with db model??"
            # add story to db
            db_session.add(story)
            print "added story to db"
            # commit
        db_session.commit()
        print "committed"
    # delete from db old stories
    for l in range(1,last_id+1):
        db_session.query(Stories).filter_by(id=l).delete()
    db_session.commit()    

def add_page_pair_to_database(from_page, to_page, limit):

    with db_lock:
        cou = session.query(Page.id).filter(Page.url == from_page).scalar()
        cou1 = session.query(Page.id).filter(Page.url == to_page).scalar()

        if cou is None:
            new_page_from = Page(url=from_page, text="", rank=0)
            session.add(new_page_from)
            session.flush()
            id0 = new_page_from.id
        else:
            id0 = cou

        if cou1 is None:
            allowed = limit < 1 or limit > session.query(Page).count()
            if not allowed:
                return
            new_page_to = Page(url=to_page, text="", rank=0)
            session.add(new_page_to)
            session.flush()
            id1 = new_page_to.id
        else:
            id1 = cou1

        new_relation = Relation(page_id = id0, destination_id = id1)
        # print(new_relation.page_id.id)
        session.add(new_relation)
        session.commit()

def save_assets():
    """
    Pulls assets from user input (as a post request), save to
    database, and routes to next question (/results will perform
    the calculations).
    """
    form = AssetsForm(request.form)
    if form.validate_on_submit():
        assets = float(request.form["assets"])

        # Checks that user's assets are getting updated each time they change
        # their input, and not getting added to the database.
        user_assets = m_session.query(model.UserBanking).filter_by(
            user_id=g.user.id).first()
        if user_assets is not None:
            update_assets = m_session.query(model.UserBanking).filter_by(
                user_id=g.user.id).update(
                {model.UserBanking.inputted_assets: assets})
        else:
            new_account = model.UserBanking(
                user_id=g.user.id, inputted_assets=assets, checking_amt=0,
                savings_amt=0, IRA_amt=0, comp401k_amt=0, investment_amt=0)
            m_session.add(new_account)
        m_session.commit()
        return redirect("/input/income")
    else:
        flash("Please enter an integer. No commas or symbols.")
        return redirect("/input/assets")

def create_secondary_facebook_album(set, facebook):
    title = "%s (#%s)" % (set.title, len(set.fb_albums) + 2) 
    print "Created %s" % title
    data = facebook.photos.createAlbum(name=title, description=set.description, visible="everyone")
    set.fb_albums.append(FBAlbum(facebook_id=int(data['aid'])))
    session.commit()
    return int(data['aid'])