import sqlite3
import time
import os
import hashlib
 
SECONDS_IN_DAY = 86400
CONNECT = sqlite3.connect(os.path.join(os.path.dirname(__file__), 'POSE.db'))
CURSOR = CONNECT.cursor()
SALT = 'wenj*&(^^jnwhg&^qjpif94439fomvJSDHGDF'
 
 
class Challenge():
    def __init__(self, title, image, user_id, timestamp=None, id=None):
        self.title = title
        self.image = image
        self.user_id = user_id
        self.id = id
        if timestamp is None:
            self.timestamp = time.time()
        else:
            self.timestamp = timestamp
 
    @classmethod
    def get(cls, id):
        CURSOR.execute('''SELECT challenge.id, title, image_id, timestamp,user_id FROM challenge JOIN image ON image.id = image_id WHERE challenge.id = ?''', (id,))
        x = CURSOR.fetchone()
        if x:
            img = Image.get(x[2])
            return cls(x[1], img, x[4],x[3], id=x[0])
        else:
            return None
 
    @classmethod
    def all(cls):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT challenge.id, title, image_id, user_id FROM challenge JOIN image ON image_id = image.id''')
        x = cursor.fetchone()
        while x is not None:
            img = Image.get(x[2])
            yield cls(x[1], img, x[3], id=x[0])
            x = cursor.fetchone()
 
    @classmethod
    def all_by_recent(cls):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT challenge.id, title, image_id, timestamp, challenge.user_id
                          FROM challenge JOIN image ON image_id = image.id ORDER BY timestamp DESC''')
        x = cursor.fetchone()
        while x is not None:
            img = Image.get(x[2])
            yield cls(x[1], img, x[4],x[3], id=x[0])
            x = cursor.fetchone()
 
    @classmethod
    def all_by_popular(cls):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT challenge.id, title, image_id, image.timestamp, challenge.user_id
                          FROM challenge JOIN image ON image_id = image.id JOIN ch_votes ON challenge.id = ch_votes.ch_id
                          GROUP BY challenge.id ORDER BY SUM(vote) DESC''')
        x = cursor.fetchone()
        while x is not None:
            img = Image.get(x[2])
            yield cls(x[1], img, x[4], x[3], id=x[0])
            x = cursor.fetchone()
 
    @classmethod
    def all_by_most_responses(cls):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT challenge.id, title, challenge.image_id, image.timestamp, challenge.user_id
                          FROM challenge JOIN image ON challenge.image_id = image.id JOIN response ON challenge.id = response.ch_id
                          GROUP BY challenge.id ORDER BY SUM(response.id) DESC''')
        x = cursor.fetchone()
        while x is not None:
            img = Image.get(x[2])
            yield cls(x[1], img, x[4], x[3], id=x[0])
            x = cursor.fetchone()
 
    @classmethod
    def all_by_trending(cls):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT challenge.id, title, image_id, image.timestamp, challenge.user_id
                          FROM challenge JOIN image ON image_id = image.id JOIN ch_votes ON challenge.id = ch_votes.ch_id
                          WHERE ch_votes.timestamp >= ?
                          GROUP BY challenge.id ORDER BY SUM(vote) DESC''', (int(time.time() - SECONDS_IN_DAY/4),))
        x = cursor.fetchone()
        while x is not None:
            img = Image.get(x[2])
            yield cls(x[1], img, x[4], x[3], id=x[0])
            x = cursor.fetchone()
 
 
    def vote(self, voteNum, user_id, ch_id = None, timestamp=None):
        if timestamp is None:
            timestamp = time.time()
        if ch_id is None:
            ch_id = self.id
        cursor = CONNECT.cursor()
        cursor.execute('''DELETE FROM ch_votes WHERE user_id = ? AND ch_id = ?''', (user_id, ch_id))       
        cursor.execute('''INSERT INTO ch_votes (ch_id, user_id, vote, timestamp) VALUES(?, ?, ?, ?)''', (ch_id, user_id, voteNum, timestamp))
        CONNECT.commit()
 
    def get_all_votes(self):
        CURSOR.execute('''SELECT vote, timestamp FROM ch_votes WHERE ch_id = ?''', (self.id,))
        return CURSOR.fetchall()
 
    def get_upvotes(self):
        CURSOR.execute('''SELECT SUM(vote) FROM ch_votes WHERE vote > 0 AND ch_id = ?''', (self.id,))
        votes = CURSOR.fetchone()[0]
        if votes is None:
            return 0
        return votes    
 
    def get_downvotes(self):
        CURSOR.execute('''SELECT SUM(vote) FROM ch_votes WHERE vote < 0 AND ch_id = ?''', (self.id,))
        votes = CURSOR.fetchone()[0]
        if votes is None:
            return 0
        return -votes    
 
    def __str__(self):
        CURSOR.execute('''SELECT * FROM challenge WHERE id = ?''', (self.id,))
        return CURSOR.fetchone()
 
    def save(self):
        if self.id is None:
            CURSOR.execute('''INSERT INTO challenge (title, image_id, user_id) VALUES (?, ?, ?)''', (self.title, self.image.id,self.user_id))
            CONNECT.commit()
            self.id = CURSOR.lastrowid
        else:
            CURSOR.execute('''UPDATE challenge SET title=? WHERE id=?''',(self.title, self.id))
            CONNECT.commit()
 
    def get_responses(self):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT r.id, r.ch_id, r.image_id,r.user_id, timestamp 
                            FROM response r JOIN image i ON r.image_id = i.id 
                            WHERE r.ch_id = (?) ORDER BY timestamp DESC''',(self.id,))
        x = cursor.fetchone()
        while x:
            img = Image.get(x[2])
            yield Response(x[1],img,x[3],x[4],id=x[0])
            x = cursor.fetchone()
 
    def get_responses_trending(self):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT r.id, r.ch_id, r.image_id,r.user_id, i.timestamp 
                            FROM response r JOIN image i ON r.image_id = i.id JOIN re_votes rv ON r.id = rv.re_id 
                            WHERE r.ch_id = (?) AND i.timestamp >=? 
                            GROUP BY r.id, r.ch_id, r.image_id, i.timestamp 
                            ORDER BY SUM(rv.vote) DESC''',(self.id,int(time.time())-SECONDS_IN_DAY/8))
        x = cursor.fetchone()
        while x:
            img = Image.get(x[2])
            yield Response(x[1],img,x[3],x[4],id=x[0])
            x = cursor.fetchone()
 
 
    def get_responses_popular(self):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT r.id, r.ch_id, r.image_id,r.user_id, i.timestamp 
                            FROM response r JOIN image i ON r.image_id = i.id JOIN re_votes rv ON r.id = rv.re_id 
                            WHERE r.ch_id = (?) 
                            GROUP BY r.id, r.ch_id, r.image_id, i.timestamp 
                            ORDER BY SUM(rv.vote) DESC''',(self.id,))
        x = cursor.fetchone()
        while x:
            img = Image.get(x[2])
            yield Response(x[1],img,x[3],x[4],id=x[0])
            x = cursor.fetchone()
 
    def get_user(self):
        print("Getting user",self.user_id)
        return User.get(self.user_id)
 
class Image():
    def __init__(self, extension, timestamp=None, id=None):
        self.id = id
        self.extension = extension
        if timestamp is None:
            self.timestamp = time.time()
        else:
            self.timestamp = timestamp
 
    def save(self, force=False):
        if self.id is None:
            force = True
        if force:
            CURSOR.execute('''INSERT INTO image (extension, timestamp) VALUES (?, ?)''', (self.extension, self.timestamp))
            self.id = CURSOR.lastrowid
        self.img_path = os.path.join("pose/web/static/images/uploads", str(self.id) + self.extension)
        self.url_path = "/static/images/uploads/"+str(self.id) + self.extension
 
    @classmethod
    def get(cls, id):
        CURSOR.execute('''SELECT id, extension, timestamp FROM image WHERE id = ?''', (id,))
        x = CURSOR.fetchone()
        if x:
            obj = cls(x[1], timestamp=x[2], id=x[0])
            obj.img_path = "pose/web/static/images/uploads/%s%s" % (x[0], x[1])
            obj.url_path = "/static/images/uploads/%s%s" % (x[0], x[1])
            return obj
        else:
            return None
 
class Response():
    def __init__(self, ch_id, image, user_id, timestamp = None, id = None):
        self.id = id
        self.ch_id = ch_id
        self.user_id = user_id
        self.image = image
        if timestamp is None:
            self.timestamp = time.time()
        else:
            self.timestamp = timestamp
 
 
    def save(self):
        if self.id is None:
            CURSOR.execute('''INSERT INTO response (ch_id, image_id, user_id) VALUES (?, ?, ?)''', (self.ch_id, self.image.id, self.user_id))
            CONNECT.commit()
            self.id = CURSOR.lastrowid
        else:
            CURSOR.execute('''UPDATE response SET title=? WHERE id=?''',(self.id))
            CONNECT.commit()
 
    @classmethod
    def get(cls, id):
        CURSOR.execute('''SELECT response.id, ch_id, response.image_id, user_id, timestamp FROM response JOIN image ON response.image_id = image_id WHERE response.id = ?''', (id,))
        x = CURSOR.fetchone()
        if x:
            img = Image.get(x[2])
            return cls(x[1], img, x[3], x[4], id=x[0])
        else:
            return None
 
    @classmethod
    def all(cls):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT response.id, ch_id, image_id, response.user_id, timestamp FROM response JOIN image ON image_id = image.id''')
        x = cursor.fetchone()
        while x is not None:
            img = Image.get(x[2])
            yield cls(x[1], img, x[3], x[4], id=x[0])
            x = cursor.fetchone()
 
    @classmethod
    def all_by_recent(cls):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT response.id, ch_id, image_id, response.user_id, timestamp FROM response JOIN image ON image_id = image.id ORDER BY timestamp DESC''')
        x = cursor.fetchone()
        while x is not None:
            img = Image.get(x[2])
            yield cls(x[1], img, x[3], x[4], id=x[0])
            x = cursor.fetchone()
 
    @classmethod
    def all_by_popular(cls):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT response.id, ch_id, image_id, response.user_id, image.timestamp
                          FROM response JOIN image ON image_id = image.id JOIN re_votes ON response.id = re_votes.re_id
                          GROUP BY response.id ORDER BY SUM(vote) DESC''')
        x = cursor.fetchone()
        while x is not None:
            img = Image.get(x[2])
            yield cls(x[1], img, x[3], x[4], id=x[0])
            x = cursor.fetchone()
 
    @classmethod
    def all_by_trending(cls):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT response.id, ch_id, image_id, response.user_id, image.timestamp
                          FROM response JOIN image ON image_id = image.id JOIN re_votes ON response.id = re_votes.re_id
                          WHERE re_votes.timestamp >= ?
                          GROUP BY response.id ORDER BY SUM(vote) DESC''', (int(time.time() - SECONDS_IN_DAY/8),))
        x = cursor.fetchone()
        while x is not None:
            img = Image.get(x[2])
            yield cls(x[1], img, x[3], x[4], id=x[0])
            x = cursor.fetchone()
 
    def vote(self, voteNum, user_id, ch_id = None, timestamp=None):
        if timestamp is None:
            timestamp = time.time()
        if ch_id is None:
            ch_id = self.id
        cursor = CONNECT.cursor()
        cursor.execute('''DELETE FROM re_votes WHERE user_id = ? AND re_id = ?''', (user_id, ch_id))
        cursor.execute('''INSERT INTO re_votes (re_id, user_id, vote, timestamp) VALUES(?, ?, ?, ?)''', (ch_id, user_id, voteNum, timestamp))
        CONNECT.commit()
 
    def get_all_votes(self):
        CURSOR.execute('''SELECT vote, timestamp FROM re_votes WHERE re_id = ?''', (self.id,))
        return CURSOR.fetchall()
 
    def get_upvotes(self):
        CURSOR.execute('''SELECT SUM(vote) FROM re_votes WHERE vote > 0 AND re_id = ?''', (self.id,))
        votes = CURSOR.fetchone()[0]
        if votes is None:
            return 0
        return votes
 
    def get_downvotes(self):
        CURSOR.execute('''SELECT SUM(vote) FROM re_votes WHERE vote < 0 AND re_id = ?''', (self.id,))
        votes = CURSOR.fetchone()[0]
        if votes is None:
            return 0
        return -votes
 
    def get_challenge(self):
        return Challenge.get(self.ch_id)
 
    def get_user(self):
        print("Getting user",self.user_id)
        return User.get(self.user_id)
 
 
 
class User():
    def __init__(self, email, image, username, password, id = None):
        self.email = email
        self.image = image
        self.password = password
        self.uname = username
        self.id = id
 
    def save(self):
        if self.id is None:
            CURSOR.execute('''INSERT INTO user (email_ad, image_id, uname, password) VALUES (?, ?, ?, ?)''', (self.email, self.image.id, self.uname, self.password))
            CONNECT.commit()
            self.id = CURSOR.lastrowid
        else:
            CURSOR.execute('''UPDATE user SET email_ad=?, uname = ?, password = ?, image_id = ? WHERE id=?''', (self.email, self.uname, self.password, self.image.id, self.id ))
            CONNECT.commit()
    @classmethod
    def get(cls, id):
        CURSOR.execute('''SELECT user.id, image_id, email_ad, uname, password FROM user JOIN image ON image_id = image.id WHERE user.id = ?''', (id,))
        x = CURSOR.fetchone()
        img = Image.get(x[1])
        return cls(x[2], img, x[3], x[4], id=x[0])
 
    @classmethod
    def get_by_username(cls, username):
        CURSOR.execute('''SELECT user.id, image_id, email_ad, uname, password FROM user JOIN image ON image_id = image.id WHERE user.uname = ?''', (username,))
        x = CURSOR.fetchone()
        if x is None:
            return None
        img = Image.get(x[1])
        return cls(x[2], img, x[3], x[4], id=x[0])
 
    @classmethod
    def create(cls, email, image, username, password):
        CURSOR.execute('''SELECT id FROM user WHERE uname = ?''',(username,))
        x = CURSOR.fetchone()
        if x is None:
            hashed = cls.hash(password)
            user = User(email, image, username, hashed)
            user.save()
            return user
        else:
            print("found a user")
            return None
 
    @classmethod
    def hash(cls,password):
        return hashlib.sha1((password+SALT).encode("utf-8")).hexdigest()
 
    @classmethod
    def login(cls, username, password):
        user = User.get_by_username(username)
        if user is None:
            return None
        hashed = cls.hash(password)
        if hashed == user.password:
            return user
        return None
 
    def get_all_responses(self):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT response.id, ch_id, image_id, response.user_id, timestamp FROM response JOIN image ON image_id = image.id WHERE response.user_id = ?''',(self.id,))
        x = cursor.fetchone()
        while x is not None:
            img = Image.get(x[2])
            yield Response(x[1], img, x[3], x[4], id=x[0])
            x = cursor.fetchone()
 
    def get_all_challenges(self):
        cursor = CONNECT.cursor()
        cursor.execute('''SELECT challenge.id, title, image_id, challenge.user_id, timestamp FROM challenge JOIN image ON image_id = image.id WHERE challenge.user_id = ?''',(self.id,))
        x = cursor.fetchone()
        while x is not None:
            img = Image.get(x[2])
            yield Challenge(x[1], img, x[3], x[4], id=x[0])
            x = cursor.fetchone()
 
    def get_challenge_upvotes(self, recent=False):
        cursor = CONNECT.cursor()
        if recent:
            cursor.execute('''SELECT challenge.id FROM challenge JOIN image ON challenge.image.id = image.id WHERE user_id = ? AND image.timestamp > ?''', (self.id, time.time() - 7 * SECONDS_IN_DAY,))
        else:
            cursor.execute('''SELECT challenge.id FROM challenge WHERE user_id = ?''', (self.id,))
        vote_count = 0
        x = cursor.fetchone()
        while x is not None:
            challenge = Challenge.get(x[0])
            vote_count += challenge.get_upvotes()
            x = cursor.fetchone()
        return vote_count
 
    def get_challenge_downvotes(self, recent=False):
        cursor = CONNECT.cursor()
        if recent:
            cursor.execute('''SELECT challenge.id FROM challenge JOIN image ON challenge.image.id = image.id WHERE user_id = ? AND image.timestamp > ?''', (self.id, time.time() - 7 * SECONDS_IN_DAY,))
        else:
            cursor.execute('''SELECT challenge.id FROM challenge WHERE user_id = ?''', (self.id,))
        vote_count = 0
        x = cursor.fetchone()
        while x is not None:
            challenge = Challenge.get(x[0])
            vote_count += challenge.get_downvotes()
            x = cursor.fetchone()
        return -vote_count
 
    def get_response_upvotes(self, recent=False):
        cursor = CONNECT.cursor()
        if recent:
            cursor.execute('''SELECT response.id FROM response JOIN image ON response.image.id = image.id WHERE user_id = ? AND image.timestamp > ?''', (self.id, time.time() - 7 * SECONDS_IN_DAY,))
        else:
            cursor.execute('''SELECT response.id FROM response WHERE user_id = ?''', (self.id,))
        vote_count = 0
        x = cursor.fetchone()
        while x is not None:
            response = Response.get(x[0])
            vote_count += response.get_upvotes()
            x = cursor.fetchone()
        return vote_count
 
    def get_response_downvotes(self, recent=False):
        cursor = CONNECT.cursor()
        if recent:
            cursor.execute('''SELECT response.id FROM response JOIN image ON response.image.id = image.id WHERE user_id = ? AND image.timestamp > ?''', (self.id, time.time() - 7 * SECONDS_IN_DAY,))
        else:
            cursor.execute('''SELECT response.id FROM response WHERE user_id = ?''', (self.id,))
        vote_count = 0
        x = cursor.fetchone()
        while x is not None:
            response = Response.get(x[0])
            vote_count += response.get_downvotes()
            x = cursor.fetchone()
        return -vote_count
 
 
if __name__ == '__main__':
 
    CONNECT = sqlite3.connect(':memory:')
    CURSOR = CONNECT.cursor()
    import DataBaseCreator as dbc
    dbc.create(CONNECT)
    user = 'drstevo02'
    password = 'word'
    assert not User.get_by_username(user)
    image = Image('.jpg')
    image.save()
    User.create('drstevo02@hotmail.com',image,user,password)
    assert User.get_by_username(user)
    assert User.login(user,password)
    assert not User.login(user,'greensheep')