import requests
from bs4 import BeautifulSoup
from urllib.parse import urljoin
from SQL import builder
import numpy as np
import sqlite3
import concurrent.futures
import threading
from sqlite_worker import SqliteWorker

MAX_THREADS = 30

async def async_request(root, url, session):
    for i in range(1, 51):
        page_url = urljoin(url, f'?page={str(i)}&s=-total')
        print(page_url)
        response = await session.get(url)
        page = response.text
        page_soup = BeautifulSoup(page, 'html.parser')
        # Our data is in the tr tags so we find all
        trs = page_soup.find_all('tr')
        count = 0
        for tr in trs[1:]:
            root.parse_tr(tr)
            root.write_dict['url'] = url
            # # Make sure this entry doesn't already exist so we can stop and start:
            # # If it exists, we'll just skip it
            # if self.sql.select('main',
            #                    table='salaries',
            #                    columns=self.columns,
            #                    where=self.write_dict,
            #                    where_and=True):
            #     print('skipping', self.write_dict['employee_name'], self.write_dict['year'])
            # otherwise, we will insert the data
            # else:
            # root.sql.insert('main', table='salaries', data=root.write_dict)
            print('inserted', 'name:', root.write_dict['employee_name'],
                  'year:', root.write_dict['year'],
                  'county:', root.write_dict['county'],
                  'entity:', root.write_dict['entity'])
            # increment the count
            count += 1
        # if count is zero, we stop increasing
        if count == 0:
            break

    root.completed.append(url + '\n')
    return response

async def make_async_requests(root, urls):
    async with AsyncClient() as session:
        tasks = [asyncio.create_task(async_request(root, url, session)) for url in urls]
        responses = await asyncio.gather(*tasks)
    return responses

class MainApp:
    most_recent = None
    write_dict = {}

    def __init__(self, start_url, base_url):
        # Key should be the location of our db, value should be the alias, in this case main
        db_info = {'SQL/salary_db.db': 'main'}
        # instantiate our builder, pass in db_info. Create=False because we want to create the table ourselves this time
        # If the table isn't there, we want it to fail so we can make it correctly
        self.sql = builder.Build(db_info=db_info, create=False)
        # We want to set up a dictionary where all columns but ID are set up as the keys that we can put values into later
        # We start by making a list of the columns in the db and dropping the first element (ID).
        self.columns = list(self.sql.column_names('main', table='salaries'))[1:]

        self.worker = SqliteWorker('SQL/salary_db.db')
        self.completed = []

        # Our URL to start with
        self.start_url = start_url
        self.base_url = base_url

    def go(self):
        # Get a beautiful soup of the base url
        base_soup = self.get_soup(self.start_url)
        # Find all td tags in the page
        tds = base_soup.find_all('td')
        most_recent = self.get_most_recent_entry()
        start = False
        # Loop through our td tags
        for i, td in enumerate(tds):
            # We've enumerated so we can skip the evens (the districts are doubled up and we only need one)
            if i % 2 == 0:
                continue
            # Write_dict is the columns in our database (ID has already been removed because we don't need it for this)
            self.write_dict = {x: None for x in self.columns}
            # The links are just the endings (where it delineates, at the date portion of the url), so we'll get them
            # by searching the a tags
            ending_list = td.find_all('a')
            for ending in ending_list:
                this_ending = ending['href']
                if not start:
                    start = self.check_start(this_ending, most_recent)
                if start:
                    self.parse_url_info(this_ending)
                    this_url = urljoin(self.base_url, this_ending)
                    print(this_url)
                    # The site only actually shows entries for the first 50 pages, then asks you to download files
                    # We'll check if it's more so we can skip ones with more and download them
                    # If the function returns true, the page is full so we should skip it
                    # The function logs it so we can download it later
                    if self.check_page50_full(this_url):
                        continue
                    # If the page50 check didn't jump out of this, we'll land here
                    # We want to loop through up to page 50
                    for j in range(1, 51):
                        # Add page number to the URL
                        page_url = urljoin(this_url, f'?page={str(j)}')
                        print(page_url)
                        # request the html from the site
                        page_soup = self.get_soup(page_url)
                        # Our data is in the tr tags so we find all
                        trs = page_soup.find_all('tr')
                        # Now loop through them (the first one is the header, so we skip it)
                        # Count is so we can jump out if count is zero
                        count = 0
                        for tr in trs[1:]:
                            self.parse_tr(tr)
                            self.write_dict['url'] = this_url
                            # Make sure this entry doesn't already exist so we can stop and start:
                            # If it exists, we'll just skip it
                            if self.sql.select('main',
                                               table='salaries',
                                               columns=self.columns,
                                               where=self.write_dict,
                                               where_and=True):
                                print('skipping', self.write_dict['employee_name'], self.write_dict['year'])
                            # otherwise, we will insert the data
                            else:
                                self.sql.insert('main', table='salaries', data=self.write_dict)
                                # print('inserted', 'name:', self.write_dict['employee_name'],
                                #       'year:', self.write_dict['year'],
                                #       'county:', self.write_dict['county'],
                                #       'entity:', self.write_dict['entity'])
                            # increment the count
                            count += 1
                        # If there is only one page, there will not be page numbers, which means until 51, every page
                        # number you supply, it will just repeat the one page of names.
                        # So we check for pagination, and if it's not there, we stop here after the first page
                        find = page_soup.find('div', class_='pagination pagination-centered')
                        if not find:
                            break
                        # if count is zero, we stop increasing
                        if count == 0:
                            break

    def go_small(self):
        try:
            # Open and read the file
            with open('more_than_50.txt', 'r') as f:
                urls = f.readlines()
            f.close()
            # Replace the line breaks; also, I'm using the more_than_50.txt to keep track of what I've downloaded by putting an
            # X before. So replacing that so they match the url  in the db and get removed as well.
            url_list = [f"{x.replace('\n', '')}" for x in urls if 'X' not in x]
            url_list = [x for x in url_list if x != '']
            self.write_dict = {x: None for x in self.columns}
            completed = []
            for i, url in enumerate(url_list):
                print(url)
                this_ending = url[32:]
                self.parse_url_info(this_ending)
                for i in range(1, 51):
                    page_url = urljoin(url, f'?page={str(i)}&s=total')
                    print(page_url)
                    # request the html from the site
                    page_soup = self.get_soup(page_url)
                    # Our data is in the tr tags so we find all
                    trs = page_soup.find_all('tr')
                    count = 0
                    for tr in trs[1:]:
                        self.parse_tr(tr)
                        self.write_dict['url'] = url
                        self.sql.insert('main', table='salaries', data=self.write_dict)
                        # print('inserted', 'name:', self.write_dict['employee_name'],
                        #       'year:', self.write_dict['year'],
                        #       'county:', self.write_dict['county'],
                        #       'entity:', self.write_dict['entity'])
                        # increment the count
                        count += 1
                    # if count is zero, we stop increasing
                    if count == 0:
                        break

                completed.append(url + '\n')
        except KeyboardInterrupt:
            print("Interrupt detected, wrapping up")
            for item in completed:
                urls[urls.index(item)] = 'X' + item

            with open('more_than_50.txt', 'w+') as f:
                f.writelines(urls)
            f.close()

        for item in completed:
            urls[urls.index(item)] = 'X' + item

        with open('more_than_50.txt', 'w+') as f:
            f.writelines(urls)
        f.close()

    def go_big_concurrent(self):
        # Open and read the file
        with open('more_than_50.txt', 'r') as f:
            urls = f.readlines()
        f.close()
        # Replace the line breaks; also, I'm using the more_than_50.txt to keep track of what I've downloaded by putting an
        # X before. So replacing that so they match the url  in the db and get removed as well.
        url_list = [f"{x.replace('\n', '')}" for x in urls if 'X' not in x]
        url_list = [x for x in url_list if x != '']
        url_batch_list = [[f'{x}?page={str(i)}&s=total' for i in range(1, 51)] for x in url_list]
        threads = []
        for url_batch in url_batch_list:
            t = threading.Thread(target=self.do_thing, args=(url_batch,))
            t.start()
            threads.append(t)

        for t in threads:
            t.join()

        token = self.worker.execute('SELECT COUNT(*) FROM salaries')
        count = self.worker.fetch_results(token)[0][0]
        print(f'Scraped {count} records')

        self.worker.close()

    def do_thing(self, url_batch):
        for page_url in url_batch:
            write_dict = {}
            page_soup = self.get_soup(page_url)
            url = page_url[:-16]
            print(url)
            this_ending = url[32:]
            # When we split the endings, some are entity and not county
            this_split = this_ending.split('/')
            if len(this_split) == 7:
                write_dict['county'] = this_split[4].strip()
                write_dict['entity'] = this_split[5].strip()
            elif len(this_split) == 4:
                write_dict['county'] = ''
                write_dict['entity'] = this_split[3].strip()
            elif len(this_split) == 6:
                write_dict['county'] = this_split[4].strip()
                write_dict['entity'] = this_split[5].strip()
            write_dict['year'] = this_split[2]
            # Our data is in the tr tags so we find all
            trs = page_soup.find_all('tr')
            for tr in trs[1:]:
                tds = tr.find_all('td')
                all_texts = [x.text.strip() for x in tds]

                all_texts[1] = all_texts[1].split('\n')[0]

                write_dict['employee_name'] = all_texts[0].replace("'", '')
                write_dict['job_title'] = all_texts[1].replace("'", '')
                # All except blocks below are because if there is text in one of these fields, we don't care about it, they're
                # numeric. So whatever that is, we turn to NaN
                try:
                    write_dict['regular_pay'] = float(all_texts[2].replace('$', '').replace(',', ''))
                except ValueError:
                    write_dict['regular_pay'] = np.nan
                try:
                    write_dict['overtime_pay'] = float(all_texts[3].replace('$', '').replace(',', ''))
                except ValueError:
                    write_dict['overtime_pay'] = np.nan
                try:
                    write_dict['other_pay'] = float(all_texts[4].replace('$', '').replace(',', ''))
                except ValueError:
                    write_dict['other_pay'] = np.nan
                try:
                    write_dict['total_pay'] = float(all_texts[5].replace('$', '').replace(',', ''))
                except ValueError:
                    write_dict['total_pay'] = np.nan
                try:
                    write_dict['benefits'] = float(all_texts[6].replace('$', '').replace(',', ''))
                except ValueError:
                    write_dict['benefits'] = np.nan
                try:
                    write_dict['total_pay_and_benefits'] = float(all_texts[7].replace('$', '').replace(',', ''))
                except ValueError:
                    write_dict['total_pay_and_benefits'] = np.nan
                write_dict['url'] = url
                try:
                    self.worker.execute('INSERT INTO salaries (employee_name, job_title, county, entity, year, regular_pay,'
                                        ' overtime_pay, other_pay, total_pay, benefits, total_pay_and_benefits, url) VALUES '
                                        '(:employee_name, :job_title, :county, :entity, :year, :regular_pay, :overtime_pay,'
                                        ' :other_pay, :total_pay, :benefits, '
                                        ':total_pay_and_benefits, :url)', write_dict)
                except Exception as e:
                    print(f'Error writing {url}: {e}')
                self.completed.append(url + '\n')
                # print('inserted', 'name:', write_dict['employee_name'],
                #   'year:', self.write_dict['year'],
                #   'county:', self.write_dict['county'],
                #   'entity:', self.write_dict['entity'])

    def check_pages(self):
        more_than_100 = []
        try:
            # Open and read the file
            with open('more_than_50.txt', 'r') as f:
                urls = f.readlines()
            f.close()
            with open('more_than_100.txt', 'r') as f:
                lines = f.readlines()
            f.close()
            last_entry = lines[-1].split(': ')[0]

            # Replace the line breaks; also, I'm using the more_than_50.txt to keep track of what I've downloaded by putting an
            # X before. So replacing that so they match the url  in the db and get removed as well.
            url_list = [f"{x.replace('\n', '').replace('X', '')}" for x in urls]
            url_list = [x for x in url_list if x != '']
            try:
                start_idx = url_list.index(last_entry)
            except ValueError:
                start_idx = 0

            for i, url in enumerate(url_list[start_idx + 1:]):
                print(url)
                page_soup = self.get_soup(url)
                # Our data is in the tr tags so we find all
                txt = page_soup.find('span', class_='page_subtitle').text.strip()
                find_page_nums = page_soup.find('div', class_='pagination pagination-centered')
                if not find_page_nums:
                    continue
                pages = int(txt.split('Page')[1].split('\xa0')[-1].replace(',', ''))

                if pages > 100:
                    more_than_100.append(f'{url}: {pages} pages\n')
        except:
            print(txt)
            with open('more_than_100.txt', 'w+') as f:
                f.writelines(more_than_100)
            f.close()
            exit()
        with open('more_than_100.txt', 'w+') as f:
            f.writelines(more_than_100)
        f.close()

    def check_page50_full(self, this_url):
        # The site only actually shows entries for the first 50 pages
        # Adding this code to check if it's more so we can skip ones with more
        page50_url = urljoin(this_url, f'?page=50')
        page50 = requests.get(page50_url)
        this_soup = BeautifulSoup(page50.content, 'html.parser')
        trs50 = this_soup.find_all('tr')
        # if page 50 is full, we assume it has more than 50
        if len(trs50) == 51:
            with open('more_than_50.txt', 'a+') as f:
                f.write(this_url + '\n')
            f.close()
            return True
        return False

    def check_start(self, this_ending, most_recent):
        # If the most recent url endswith this ending, we'll go ahead and start parsing again
        # But first we check that it's not None. If it's None, there were no entries so no most recent exists
        if most_recent and most_recent.endswith(this_ending):
            return True
        elif not most_recent:
            return True
        else:
            return False

    def get_most_recent_entry(self):
        most_recent = self.sql.select('main',
                                      table='salaries',
                                      columns=['url'],
                                      where='ID=(SELECT MAX(ID) FROM salaries)')
        if most_recent:
            return most_recent[0][0]
        else:
            return None

    def get_soup(self, url):
        # Request the HTML from the page
        page = requests.get(url)
        # Turn it into soup and return the soup
        return BeautifulSoup(page.content, 'html.parser')

    def parse_tr(self, tr):
        tds = tr.find_all('td')
        all_texts = [x.text.strip() for x in tds]

        all_texts[1] = all_texts[1].split('\n')[0]

        self.write_dict['employee_name'] = all_texts[0].replace("'", '')
        self.write_dict['job_title'] = all_texts[1].replace("'", '')
        # All except blocks below are because if there is text in one of these fields, we don't care about it, they're
        # numeric. So whatever that is, we turn to NaN
        try:
            self.write_dict['regular_pay'] = float(all_texts[2].replace('$', '').replace(',', ''))
        except ValueError:
            self.write_dict['regular_pay'] = np.nan
        try:
            self.write_dict['overtime_pay'] = float(all_texts[3].replace('$', '').replace(',', ''))
        except ValueError:
            self.write_dict['overtime_pay'] = np.nan
        try:
            self.write_dict['other_pay'] = float(all_texts[4].replace('$', '').replace(',', ''))
        except ValueError:
            self.write_dict['other_pay'] = np.nan
        try:
            self.write_dict['total_pay'] = float(all_texts[5].replace('$', '').replace(',', ''))
        except ValueError:
            self.write_dict['total_pay'] = np.nan
        try:
            self.write_dict['benefits'] = float(all_texts[6].replace('$', '').replace(',', ''))
        except ValueError:
            self.write_dict['benefits'] = np.nan
        try:
            self.write_dict['total_pay_and_benefits'] = float(all_texts[7].replace('$', '').replace(',', ''))
        except ValueError:
            self.write_dict['total_pay_and_benefits'] = np.nan

    def parse_url_info(self, this_ending):
        # When we split the endings, some are entity and not county
        this_split = this_ending.split('/')

        if len(this_split) == 7:
            self.write_dict['county'] = this_split[4].strip()
            self.write_dict['entity'] = this_split[5].strip()
        elif len(this_split) == 5:
            self.write_dict['county'] = None
            self.write_dict['entity'] = this_split[3].strip()
        self.write_dict['year'] = this_split[2]