import urllib
import urllib2
import string
import sys
from bs4 import BeautifulSoup
import sqlite3
from datetime import date
import codecs

conn = sqlite3.connect('Rotten.sqlite')
c = conn.cursor()

c.execute("CREATE TABLE IF NOT EXISTS Rotten('Movie' VARCHAR, 'Critic' TEXT, 'Audience' TEXT, 'date_added' DATETIME, 'Seen' Text, 'top50' BOOL)")

rows = c.execute("Update Rotten Set top50=0 Where top50=1")

user_agent = 'Mozilla/5.0 (Windows NT 6.1; rv:13.0) Gecko/20100101 Firefox/13.0'
headers = { 'User-Agent' : user_agent }
request=urllib2.Request("http://www.rottentomatoes.com/dvd/top_rentals.php",None ,headers)
response = urllib2.urlopen(request)
the_page = response.read()
soup= BeautifulSoup(the_page)

table = soup.find("table",{"class": "center movie_list rt_table"})
links = table.findAll('a')

pages = []

today = date.today().strftime("%x")

for link in links:
    try:
        movie = link.string
        rows = c.execute('select exists( select * from Rotten Where Movie Like \"' + movie + '%\")' )        
        exists = rows.fetchone()

        if (exists[0]==1):
            c.execute("UPDATE Rotten Set top50=1 Where Movie LIKE \""+ movie +"%\"")            
        else:
            print movie + " Not in Database."
            linkhref = link.get('href')            
            pages.append(linkhref)            
    except:
        continue

print("\n\n")

for page in pages:
    request=urllib2.Request("http://www.rottentomatoes.com"+page,None ,headers)
    response = urllib2.urlopen(request)
    the_page = response.read()
    soup= BeautifulSoup(the_page)

    
    movie = soup.find("span",{"itemprop":"name"})
    critic = soup.find("span",{"id":"all-critics-meter"})
    
    audience = soup.find("span",{"class":"meter popcorn numeric "})
    if audience is None:
        audience = soup.find("span",{"class":"meter spilled numeric "})
    if audience is None:
        audience = soup.find("span",{"class":"meter wts numeric "})
        
    print(movie.string+" "+critic.string+" "+audience.string)
    c.execute("Insert into Rotten ('Movie','Critic','Audience','date_added','top50') "
              +"Values (?, ?, ?,?,?)",(movie.string,critic.string,audience.string,today,"1"))


print("\n\n")

rows = c.execute("SELECT Movie, Critic, Audience "
                 +"FROM Rotten "
                 +"WHERE seen is null AND top50==1 "
                 +"ORDER BY cast(Audience as int) DESC")

print string.ljust("MOVIE",50)+string.ljust("CRITIC",10)+string.ljust("AUDIENCE",10)

for row in rows:    
    print string.ljust(row[0],50)+string.ljust(row[1],10)+string.ljust(row[2],10)
    

conn.commit()    

print "Press Enter to exit"
raw_input()