import requests, hashlib, http.cookiejar, sqlite3, datetime, time
from fake_useragent import UserAgent
from bs4 import BeautifulSoup

WEBSITE_URL = 'http://www.gta-sarp.com/forums/login.php?do=login'
FRR_URL = 'http://www.gta-sarp.com/forums/forumdisplay.php?15-Forum-Related'
HANDLED_URL = 'http://www.gta-sarp.com/forums/forumdisplay.php?1535-Handled/page'
ua = UserAgent()


def create_table():
    conn = sqlite3.connect('sarp.db')
    c = conn.cursor()
    c.execute("CREATE TABLE IF NOT EXISTS threads_frr ("
              "thread_id text NOT NULL UNIQUE);")

    c.execute("CREATE TABLE IF NOT EXISTS threads_frr_handled ("
              "thread_id text NOT NULL UNIQUE,"
              "handled_by text NOT NULL,"
              "user_id text NOT NULL);")


def print_table():
    conn = sqlite3.connect('example.db')
    c = conn.cursor()
    c.execute("SELECT * FROM threads_frr")
    for row in c:
        print(row)


class Account:
    def __init__(self, username, password):
        self.username = username
        self.password = password
        self.headers = {'User-Agent': str(ua.random)}
        self.cookie_jar = http.cookiejar.CookieJar()

        self.session = requests.Session()
        self.soup = None

        self.log_in()

    def log_in(self):
        self.session.post(WEBSITE_URL, data={'vb_login_username': self.username,
                                             'vb_login_password': self.password,

                                             's': '',
                                             'securitytoken': 'guest',
                                             'do': 'login',
                                             'vb_login_md5password': hashlib.md5(self.password.encode()).hexdigest(),
                                             'vb_login_md5password_utf': hashlib.md5(self.password.encode()).hexdigest()
                                             })

    def is_logged_in(self):
        r = self.session.get("http://www.gta-sarp.com/forums/forumdisplay.php?2213-The-Golden-Club",
                             cookies=self.cookie_jar)
        if "You are not logged in. Fill in the form at the bottom of this page and try again" in r.text:
            return False
        else:
            return True

    def check_threads(self):
        while True:
            if datetime.datetime.now().minute % 3 == 0:
                threads = []
                self.soup = BeautifulSoup(self.session.get(FRR_URL).content, "html.parser")

                for thread in self.soup.select("ol.threads li"):
                    if thread.get('id') is not None:
                        threads.append(thread.get('id'))

                conn = sqlite3.connect('sarp.db')
                c = conn.cursor()
                for thread in threads:
                    c.execute("SELECT thread_id FROM threads_frr WHERE thread_id=?", (thread,))
                    if c.fetchone() is None:
                        c.execute("INSERT INTO threads_frr VALUES (?);", (thread,))
                        print(datetime.datetime.now(), "A forum related request has been posted!")
                    conn.commit()
            time.sleep(60)

    def collect_data(self):
        page = 1
        counter = 0
        thread_info = []
        conn = sqlite3.connect('sarp.db')
        c = conn.cursor()

        while page < 3:
            self.soup = BeautifulSoup(self.session.get(HANDLED_URL + str(page)).content, "html.parser")
            """
            for thread in self.soup.select("ol.threads li"):
                if thread.get('id') is not None:
                    thread_info[thread.get('id')] = {}
                    user_info = thread.select("dl.threadlastpost dd:nth-of-type(1)")
                    user_info = user_info.find('a').get('href')
                    user_id = user_info[user_info.index('?') + 1:user_info.index('-')]
                    user_name = user_info[user_info.index('-')+1:]
                    user_name = user_name.replace('-', ' ')
                    thread_info[thread.get('id')]['username'] = user_name
                    thread_info[thread.get('id')]['user_id'] = user_id
            """
            threads = [thread for thread in self.soup.select("ol.threads li.threadbit")]
            for thread in threads:
                thread_id = thread.get('id')
                c.execute("SELECT thread_id FROM threads_frr_handled WHERE thread_id=?", (thread_id,))
                if c.fetchone() is not None:
                    continue
                info = thread.find('a', {"class": "username", "class": "popupctrl"})
                if info is not None:
                    info = info.get('href')
                else:
                    continue
                counter = counter + 1
                user_id = info[info.index('?') + 1:info.index('-')]
                user_name = info[info.index('-') + 1:]
                this_thread = [thread_id, user_name, user_id]
                thread_info.append(this_thread)
            page = page + 1


        c.executemany("INSERT INTO threads_frr_handled VALUES (?,?,?)", thread_info)
        conn.commit()
        print(counter, "entries added.")


a = Account('X', 'X')
a.log_in()
a.collect_data()
conn = sqlite3.connect('sarp.db')
c = conn.cursor()
c.execute("SELECT handled_by, count(*) FROM threads_frr_handled GROUP BY handled_by")
rows = c.fetchall()
for row in rows:
    print(row)