import pymysql
import subprocess
import subreddit
import time
import datetime
import logging
import re

class Database:
    def __enter__(self):
        self.__connection = pymysql.connect(
            **subreddit.CONFIG["mysql"],
            charset = "utf8mb4",
            # cursorclass = pymysql.cursors.DictCursor
        )
        return self

    def __exit__(self, type, value, traceback):
        self.__connection.close()

    def append_log(self, line, permalink = None, commit = True):
        """Function for adding a log file line to the database. Switched to
        use the database for logging at the same time as switched to MySQL.

        Args:
            line (str): a line of a log
            permalink (str, optional): a url about which the log line converns. Defaults to None.
            commit (bool, optional): autocommit. Defaults to True.
        """        
        def get_date(stri):
            # strip microseconds
            stri = stri.split(",")[0]
            try:
                return datetime.datetime.strptime(stri, "%Y-%m-%d %H:%M:%S")
            except ValueError:
                return datetime.datetime.strptime(stri, "%b %d %Y %H:%M:%S")

        addFlag = False    
        s = line.split("\t")
        if len(s) == 3:
            pid = int(s[0])
            date = get_date(s[1][1:-1])
            misc = s[2].rstrip()
            addFlag = True
 
        elif len(s) == 1:
            s = s[0].rstrip()
            result = re.search("\[(.*)\]", s)
            if result is not None:
                pid = None
                date = get_date(result.group(1))
                misc = s.replace("[%s]" % result.group(1), "")
                addFlag = True

        if addFlag:
            if re.search(r"{ERROR", misc) is not None:
                category = "ERROR"
            else:
                category = "INFO"

            with self.__connection.cursor() as cursor:
                cursor.execute("""
                INSERT INTO log (pid, datetime_, category, data_, reddit_id) VALUES (
                    %s, %s, %s, %s, %s
                );""", (pid, date, category, misc, permalink))

            if commit:
                self.__connection.commit()

    def set_lambda(self, user, toset):
        with self.__connection.cursor() as cursor:
            cursor.execute("UPDATE users SET lambda = %s WHERE user_name = %s;", (toset, user))
        self.__connection.commit()

    def change_lambda(self, user, changeby):
        with self.__connection.cursor() as cursor:
        #this will make it go negative. You must check this operation is allowed.
            cursor.execute("""
            UPDATE users SET lambda = (
                (SELECT lambda FROM users WHERE user_name = %s) + %s
            ) WHERE user_name = %s;
            """, (user, changeby, user))
        
        self.__connection.commit()

    def give_lambda(self, user, link, timestamp = int(time.time()), op = None):
        def give(user, link = None):
            with self.__connection.cursor() as cursor:
                #check if the user has an entry in the database
                cursor.execute("SELECT userID FROM users WHERE user_name = %s;", (user, ))
                try:
                    id_ = cursor.fetchone()[0]
                except TypeError:
                    #the user isn't in the database
                    cursor.execute("""
                    INSERT INTO users (user_name, lambda) VALUES (%s, 1);
                    """, (user, ))
                    if link is not None:
                        cursor.execute("""
                        INSERT INTO lambdas (userID, permalink, created) VALUES ((
                            SELECT userID FROM users WHERE user_name = %s
                        ), %s, %s);
                        """, (user, link, timestamp))
                else:
                    #update their lambda and add to lambdas
                    self.change_lambda(user, 1)
                    if link is not None:
                        cursor.execute("""
                        INSERT INTO lambdas (userID, permalink, created) VALUES (%s, %s, %s);
                        """, (id_, link, timestamp))
        
            self.__connection.commit()

        #give one lambda to both the user and the OP
        give(user, link)
        if op is not None:
            give(op)

    def get_lambda(self, user):
        with self.__connection.cursor() as cursor:
            cursor.execute("SELECT lambda FROM users WHERE user_name = %s", (user, ))
            try:
                lambda_ = cursor.fetchone()[0]
            except TypeError:
                #the user isn't in the database, and therefore has no lambda 
                return 0, []
            else:
                cursor.execute("SELECT permalink FROM lambdas WHERE userID = (SELECT userID FROM users WHERE user_name = %s);", (user, ))
                links = [i[0] for i in cursor.fetchall()]

                return lambda_, links

    def link_in_db(self, link):
        with self.__connection.cursor() as cursor:
            cursor.execute("SELECT permalink FROM lambdas WHERE permalink = %s;", (link, ))
            try:
                links = [i[0] for i in cursor.fetchall()]
            except TypeError:
                links = []

            return link in links

    def add_to_blacklist(self, id):
        with self.__connection.cursor() as cursor:
            cursor.execute("INSERT INTO blacklist (prawID) VALUES (%s);", (id, ))
        self.__connection.commit()

    def id_in_blacklist(self, id):
        with self.__connection.cursor() as cursor:
            cursor.execute("SELECT prawID FROM blacklist WHERE prawID = %s;", (id, ))
            try:
                ids = [i[0] for i in cursor.fetchall()]
            except TypeError:
                ids = []

            return id in ids

    def get_scores(self):
        with self.__connection.cursor() as cursor:
            cursor.execute("""
            SELECT users.user_name, users.lambda, COUNT(users.user_name) 
            FROM lambdas INNER JOIN users ON users.userID = lambdas.userID 
            GROUP BY users.user_name;
            """)
            return cursor.fetchall()

    def update_stats(self):
        with self.__connection.cursor() as cursor:
            cursor.execute("""
            INSERT INTO stats (lambdaCount, helpGiven, uniqueUsers, date) VALUES (
                (SELECT SUM(lambda) FROM users),
                (SELECT COUNT(lambdaID) FROM lambdas),
                (SELECT COUNT(user_name) FROM users),
            (SELECT DATE_FORMAT(NOW(), "%Y-%m-%d")));
            """)
            self.__connection.commit()

    def get_stats(self):
        with self.__connection.cursor() as cursor:
            cursor.execute("SELECT * FROM stats;")
            return cursor.fetchall()

    def user_given_lambda(self, user, permalink):
        links = self.get_lambda(user)[1]
        return permalink in links or permalink.replace("https://www.reddit.com", "") in links

    def get_all_lambdas(self):
        with self.__connection.cursor() as cursor:
            cursor.execute("""
            SELECT lambdas.lambdaID, lambdas.permalink, users.user_name, lambdas.created 
            FROM lambdas INNER JOIN users ON lambdas.userID = users.userID;
            """)
            return cursor.fetchall()

    def add_date_to_permalink(self, permalink, date):
        with self.__connection.cursor() as cursor:
            cursor.execute("UPDATE lambdas SET created = %s WHERE permalink = %s;", (date, permalink))
        self.__connection.commit()

    def get_lambda_leaderboard(self):
        with self.__connection.cursor() as cursor:
            cursor.execute("""
            SELECT users.user_name, COUNT(lambdas.userID) AS times_helped, users.lambda 
            FROM lambdas INNER JOIN users ON users.userID = lambdas.userID 
            WHERE created > (UNIX_TIMESTAMP() - (60 * 60 * 24 * 30)) 
            GROUP BY lambdas.userID ORDER BY times_helped DESC LIMIT 10;
            """)
            return cursor.fetchall()

if __name__ == "__main__":
    print(subreddit.format_monthly_leaderboard())