import requests
import json
import mysql.connector
from mysql.connector.pooling import MySQLConnectionPool
from multiprocessing import Pool, TimeoutError, Value, Array
import hashlib


db = mysql.connector.connect(
  host='localhost',
  user='babetgoug',
  password='pokpok',
  database='crossfit_open',
  auth_plugin='mysql_native_password',
  pool_size = 5,
)

database_schema = {
    'Id': 's',
    'Year': 's',
    'CompetitionType': 's',
    'Event': 's',
    'AthleteId': 's',
    'AthleteName': 's',
    'AthleteGender': 's',
    'CountryCode': 's',
    'RegionId': 's',
    'DivisionId': 's',
    'AffiliateId': 's',
    'AffiliateName': 's',
    'Age': 's',
    'HeightCm': 's',
    'WeightKg': 's',
    'Score': 's',
    'EventRank': 's',
    'ScoreDisplay': 's',
    'Scaled': 's',
    'Breakdown': 's',
    'AffiliateScore': 's',
    'JudgeId': 's',
    'JudgeName': 's',
}

def GenerateId(year, type, event, athlete):
    h = hashlib.new('sha256')
    h.update(str(year))
    h.update(type)
    h.update(str(event))
    h.update(athlete)
    return h.hexdigest()

class PageFetcher:
    def __init__(self, year, competition_type, division, page):
        self.year = year
        self.competition_type = competition_type
        self.division = division 
        self.page = page

    def num_pages(self):
        url = ('https://c3po.crossfit.com/api/leaderboards/v2/competitions/%s/%d/leaderboards?view=0&division=%d&region=0&scaled=0&sort=0&page=%d' %
               (self.competition_type, self.year, self.division, 1))
        req = requests.get(url)
        j = json.loads(req.text)
        return j['pagination']['totalPages']

    def fetch(self):
        url = ('https://c3po.crossfit.com/api/leaderboards/v2/competitions/%s/%d/leaderboards?view=0&division=%d&region=0&scaled=0&sort=0&page=%d' %
               (self.competition_type, self.year, self.division, self.page))
        req = requests.get(url)
        j = json.loads(req.text)

        batch_queue = []
        for row in j['leaderboardRows']:
            entrant = row['entrant']
            affiliateId = entrant.get('affiliateId', '0')
            if affiliateId == 'None':
                affiliateId = '0'
            regionId = entrant.get('regionId', '0')
            if regionId == 'None':
                regionId = '0'
            age = entrant.get('age', '0')
            if not age:
                age = '0'
            base_score = {
                'Year': self.year,
                'CompetitionType': self.competition_type,
                'AthleteId': int(entrant['competitorId']),
                'AthleteName': entrant['competitorName'],
                'AthleteGender': entrant['gender'],
                'CountryCode': entrant['countryOfOriginCode'],
                'RegionId': int(regionId),
                'DivisionId': int(entrant['divisionId']),
                'AffiliateId': int(affiliateId),
                'AffiliateName': entrant['affiliateName'],
                'Age': int(age),
                'HeightCm': self._height(entrant['height']),
                'WeightKg': self._weight(entrant['weight']),
            }
            for score in row['scores']:
                if score['scoreIdentifier'] is None:
                    score['scoreIdentifier'] = GenerateId(self.year, self.competition_type, score['ordinal'], entrant['competitorId'])
                current_score = {
                    'Id': score['scoreIdentifier'],
                    'Event': score['ordinal'],
                    'EventRank': int(score['rank']),
                    'Score': int(score['score']),
                    'ScoreDisplay': score['scoreDisplay'],
                    'Scaled': score['scaled'] != '0',
                    'Breakdown': score['breakdown'][:99] if len(score.get('breakdown', '')) > 99 else score.get('breakdown', ''),
                    'AffiliateScore': score.get('affiliate', ''),
                    'JudgeId': int(score.get('judge_user_id', '0')),
                    'JudgeName': score['judge'][:99] if len(score.get('judge', '')) > 99 else score.get('judge', '')
                }
                current_score.update(base_score)
                batch_queue.append(self._row_values(current_score))
            
        # db = sql_pool.get_connection()
        cursor = db.cursor()
        r = cursor.executemany(self._query(), batch_queue)
        db.commit()
        print(self.page)
        cursor.close()

    def _height(self, val):
        if val.endswith(' in'):
            return int(int(val[:-3]) * 2.54)
        elif val.endswith(' cm'):
            return int(val[:-3])
        return None

    def _weight(self, val):
        if val.endswith(' lbs'):
            return int(int(val[:-4]) * 2.2)
        elif val.endswith(' kg'):
            return int(val[:-3])
        return None
    
    def _query(self):
        return ('REPLACE INTO Leaderboard(' + ','.join(database_schema.keys()) + 
            ') VALUES (' + ','.join(['%' + s for s in database_schema.values()]) +
            ');')
    
    def _row_values(self, d):
        result = []
        for k in database_schema.keys():
            result.append(d[k])
        return result


def fetch_page(division, page):
    return PageFetcher(2023, 'open', 1, page).fetch()

def fetch_all(year, division, page=1):
    print(year, division)
    fetcher = PageFetcher(year, 'open', division, 1)
    num_pages = fetcher.num_pages()
    p = Pool(2)
    for i in range(page, num_pages):
        PageFetcher(year, 'open', division, i).fetch()
        # p.apply_async(fetch_page, (division, i))

    p.close()
    p.join()

for y in reversed([2018, 2017, 2016, 2015, 2014, 2013, 2012, 2011]):
    fetch_all(y, 1)
    fetch_all(y, 2)