import MySQLdb
import time

ROOM_VIDEO_LOG_SIZE = 50
ROOM_CHAT_LOG_SIZE = 50
ROOM_TOP_LOG_SIZE = 50

IMAGE_EXTENSIONS = ('gif','jpg','peg','png')
TIME_ZONE = 7

def now ():
        return int(time.mktime(time.localtime()))
def is_image (url):
        return url[-3:].lower() in IMAGE_EXTENSIONS

class db(object):
        def __init__ (self):
                self.conn = None
                self.cursor = None
                self.connect()

        def connect (self):
                self.conn = MySQLdb.connect (host = "localhost",
                                        #read_default_file = "~/.my.cnf",
					user = "scannerjammer",
					passwd = "bs~fZY71dk",
                                        db = "scannerjammer")
                self.cursor = self.conn.cursor ()

        def execute (self,sql,args=()):
                try:
                        self.cursor.execute(sql,args)
                except MySQLdb.Error, e:
                        print "Error %d: %s" % (e.args[0], e.args[1])
                        # sys.exit (1)
                        self.connect()
                        self.cursor.execute(sql,args)

        def lastinsertid (self):
                return DB.conn.insert_id()

	def getRooms (self):
		self.execute ("SELECT * FROM sj_room")
		return self.cursor.fetchall ()
	def getNewVideoCounts (self):
		self.execute ("SELECT roomid,count(*) FROM sj_video WHERE date > %s GROUP BY roomid", (now() - 86400))
		rows = self.cursor.fetchall ()
		try:
			lookup = {}
			for pair in rows:
				lookup[int(pair[0])] = pair[1]
			return lookup
		except: 
			return {}
	def getRoomByName (self, name):
		self.execute ("SELECT * FROM sj_room WHERE name=%s LIMIT 1", (name))
		rows = self.cursor.fetchall ()
		try:
			return rows[0]
		except:
			return None

	def getTokboxSessionForRoom (self, roomname):
		self.execute ("SELECT tokbox FROM sj_room WHERE name=%s", (roomname))
		rows = self.cursor.fetchall ()
		try:
			return rows[0][0]
		except:
			return None
	def saveTokboxSessionForRoom (self, roomname, sessionid):
		self.execute ("UPDATE sj_room SET tokbox=%s WHERE name=%s", (sessionid, roomname))

	def getUrlsForUser (self,userid):
		self.execute ("SELECT * FROM sj_url WHERE userid=%s ORDER BY date DESC LIMIT %s", (userid,ROOM_CHAT_LOG_SIZE))
		rows = self.cursor.fetchall ()
		urlz = []
		for row in rows:
			url = (row[0],row[1],row[4],row[5])
			urlz.append(url)
		return urlz

	def getImagesForUser (self,userid):
		self.execute ("SELECT * FROM sj_url WHERE userid=%s ORDER BY date DESC LIMIT %s", (userid,ROOM_CHAT_LOG_SIZE))
		rows = self.cursor.fetchall ()
		urlz = []
		for row in rows:
			if not is_image(row[5]):
				continue
			url = (row[0],row[1],row[4],row[5])
			urlz.append(url)
		return urlz

	def getTopVideos (self):
		self.execute ("SELECT * FROM sj_video WHERE removed != 1 ORDER BY likes DESC LIMIT %s", (ROOM_TOP_LOG_SIZE))
		rows = self.cursor.fetchall ()
		vidz = []
		for row in rows:
			vid = (row[0],row[1],row[2],row[4],row[5],row[6],row[7])
			vidz.append(vid)
		return vidz

	def getTopVideosToday (self):
		n = int ( now() ) - 3600 * TIME_ZONE
		today = n - (n % 86400)
		self.execute ("SELECT * FROM sj_video WHERE date > %s AND removed != 1 ORDER BY likes DESC LIMIT %s", (today, ROOM_TOP_LOG_SIZE))
		rows = self.cursor.fetchall ()
		vidz = []
		for row in rows:
			vid = (row[0],row[1],row[2],row[4],row[5],row[6],row[7])
			vidz.append(vid)
		return vidz

	def getTopVideosYesterday (self):
		n = int ( now() ) - 3600 * TIME_ZONE
		today = n - (n % 86400)
		yesterday = today - 86400
		self.execute ("SELECT * FROM sj_video WHERE date > %s AND date < %s AND removed != 1 ORDER BY likes DESC LIMIT %s", (yesterday, today, ROOM_TOP_LOG_SIZE))
		rows = self.cursor.fetchall ()
		vidz = []
		for row in reversed(rows):
			vid = (row[0],row[1],row[2],row[4],row[5],row[6],row[7])
			vidz.append(vid)
		return vidz

	def getTopVideosForUser (self, userid):
		self.execute ("SELECT * FROM sj_video WHERE userid=%s ORDER BY likes DESC LIMIT %s", (userid,ROOM_TOP_LOG_SIZE))
		rows = self.cursor.fetchall ()
		vidz = []
		for row in rows:
			vid = (row[0],row[1],row[2],row[4],row[5],row[6],row[7])
			vidz.append(vid)
		return vidz

	def getLikedVideosForUser (self,userid):
		self.execute ("SELECT videoid FROM sj_likes WHERE userid=%s ORDER BY date DESC LIMIT %s", (userid,ROOM_VIDEO_LOG_SIZE))
		rows = self.cursor.fetchall ()
		if not len(rows):
			return
		# LIKES:  0 userid  1 videoid  2 date
		videoids = ",".join([ str(x[0]) for x in rows ])
		self.execute ("SELECT * FROM sj_video WHERE id IN (%s)" % videoids)
		rows = self.cursor.fetchall ()
		vidz = []
		for row in reversed(rows):
			vid = (row[0],row[1],row[2],row[4],row[5],row[6],row[7])
			vidz.append(vid)
		return vidz

	def getVideosForUser (self,userid):
		self.execute ("SELECT * FROM sj_video WHERE userid=%s ORDER BY date DESC LIMIT %s", (userid,ROOM_VIDEO_LOG_SIZE))
		rows = self.cursor.fetchall ()
		vidz = []
		# 0 id  1 date  2 userid  3 user  4 url  5 title
		for row in reversed(rows):
			vid = (row[0],row[1],row[2],row[4],row[5],row[6],row[7])
			vidz.append(vid)
		return vidz

	def getAllLikes (self):
		self.execute ("SELECT * FROM sj_likes", ())
		return self.cursor.fetchall ()
	def getAllVideos (self):
		self.execute ("SELECT * FROM sj_video", ())
		return self.cursor.fetchall ()