import warnings
warnings.filterwarnings("ignore", category=DeprecationWarning)
warnings.filterwarnings('ignore')

import os
import pandas as pd
pd.set_option('display.max_columns', None)
pd.set_option('display.width', 500)

import numpy as np
import requests
import scipy as sp

import seaborn as sns
import matplotlib.pyplot as plt

def flatten(x):
    return [i for c in x for i in c]


def fetch_page(url):
    rec = requests.get(url)
    return rec


def get_total_pages(url):
    data = fetch_page(url).json()
    return data['pagination']['totalPages']


def get_url(year, division, scaled):
    url_years = {
        '2018': 'v1',
        '2019': 'v1',
        '2020': 'v1',
        '2021': 'v2',
        '2022': 'v2',
        '2023': 'v2',
        '2024': 'v2'}
    url_roots = {
        'v1': 'https://games.crossfit.com/competitions/api/v1',
        'v2': 'https://c3po.crossfit.com/api/competitions/v2/competitions/open/'}

    url_search = '/leaderboards?scaled=' + str(scaled) + '&division=' + str(division) + '&page='

    url = url_roots[url_years[year]] + str(year) + url_search
    return url


DIVISION_WOMEN = 2
year = '2024'
division = DIVISION_WOMEN
scaled = 0
url = get_url(year, division, scaled)
print('url', url)
data = fetch_page(url + '1').json()

# Get athlete info
col_athlete_info = list(data['leaderboardRows'][0]['entrant'].keys())

# Get score
COL_SCORE_INFO = list(data['leaderboardRows'][0]['scores'][0].keys())
if len(COL_SCORE_INFO) < 16:  # must be 16
    if COL_SCORE_INFO[-2] != 'judge_user_id':
        COL_SCORE_INFO.insert(14, 'judge_user_id')

NUM_WODS = 5
col_score_info_total = []  # unique column for each wod
for i in range(1, NUM_WODS + 1):
    for j in range(len(COL_SCORE_INFO)):
        col_score_info_total.append(COL_SCORE_INFO[j] + "_" + str(i))

# Create columns
COLUMNS = flatten([col_athlete_info, col_score_info_total, ['overallRank', 'overallScore']])


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


def _weight(val):
    if val.endswith(' lb'):
        return int(int(val[:-3]) * 2.2)
    elif val.endswith(' kg'):
        return int(val[:-3])
    return None


def get_data(year='2024', division=DIVISION_WOMEN, scaled=0):
    url = get_url(year, division, scaled)

    num_pages = get_total_pages(url + '1')

    # ----------------------
    # threads = 4
    #
    # for i in range(1, threads + 1):
    #     start = ( num_pages * (i - 1) ) + 1
    #     end = num_pages

    # ----------------------
    all_values = []
    for i_page in range(1, num_pages + 1):
        data = fetch_page(url + str(i_page)).json()

        for row in data['leaderboardRows']:
            values = []

            # athlete info
            values.append(list(row['entrant'].values()))

            # score
            num_scores = len(row['scores'])
            scores = dict.fromkeys(COL_SCORE_INFO)
            for score in row['scores']:  # might contain missing keys
                for k in score.keys():
                    scores[k] = score[k]
                values.append(list(scores.values()))

            values = flatten(values)

            # other scores set as empty
            for i in range(num_scores + 1, NUM_WODS + 1):
                for _ in COL_SCORE_INFO:
                    values.append("")

            # extra
            values.append(row['overallRank'])
            values.append(row['overallScore'])

            all_values.append(values)

    df = pd.DataFrame(all_values, columns=COLUMNS)
    return df

def find_outliers(df, col):
    # print(df2.describe())

    # a data point is an outlier if it is more than 1.5*IQR
    # above the third quartile or below the first quartile.
    q1 = df[col].quantile(0.25)
    q3 = df[col].quantile(0.75)
    iqr = q3 - q1
    lower_limit = q1 - 1.5 * iqr
    upper_limit = q3 + 1.5 * iqr

    # dropping outliers using upper and lower limit
    df = df[(df[col] < upper_limit) & (df[col] > lower_limit)]

    print("{} min={} max={} mean={} std={}".format(col, lower_limit, upper_limit, df[col].mean(), df[col].std()))
    # print(df2.shape)

    # check for outliers with box plot
    # boxplot = df2[
    #     ['HeightCm', 'WeightKg']]
    # for i in boxplot.columns:
    #     plt.figure(figsize=(6, 3))
    #     sns.boxplot(x=df[i])
    #     plt.title(i)
    #     plt.show()

    # plot distribution
    # plt.hist(df2[col])
    # plt.show()

    return df


def clean(d, cols):
    # columns with more than 10% null values
    # print("Columns with missing values", d.columns[d.isnull().sum() * 100 / len(d) > 10] )

    d = d.dropna()  # dropping all null values

    # checking for any remaining null values
    # print(d.columns[d.isnull().sum() * 100 / len(d) > 1] )

    for col in cols:
        d = find_outliers(d, col)

    print(d.shape)
    print(d.describe())

    return d

def annotate(data, **kws):
    col = "WeightKg"
    r, p = sp.stats.pearsonr(data[col].dropna(), data['EventRank'].dropna())
    ax = plt.gca()
    ax.text(.05, .8, 'r={:.2f} r**2={:.2f}, p={:.2g}'.format(r, r ** 2, p),
            transform=ax.transAxes)


def analyze_all_rank_versus(d2, col):
    # linear plot
    # sns.regplot(data=d2,  x=col, y='EventRank', truncate=False, ax=g.ax_joint)

    g = sns.lmplot(x=col, y='EventRank', hue='AthleteGender', data=d2)
    # add pearson annotation
    # g.map_dataframe(annotate)

    plt.xticks(rotation=90)
    #plt.show()
    plt.savefig("{}.png".format(col))
    plt.close()

def height_weight(df):
    pearson, p = sp.stats.pearsonr(df["WeightKg"].dropna(), df['HeightCm'].dropna())
    pearson = round(pearson, 2)
    p = '{:.2e}'.format(p)
    # kind= ['scatter', 'hist', 'hex', 'kde', 'reg', 'resid']
    sns.jointplot(x="WeightKg", y='HeightCm', data=df, kind='scatter').ax_joint.text(
        s=f' pearsonr = {pearson}; p = {p} ', ha='left', va='top', x=5, y=10,
        bbox={'boxstyle': 'round', 'pad': 0.25, 'facecolor': 'white', 'edgecolor': 'gray'})
    plt.show()

def analyze_rank_versus(d2,  col):
    print("top/bottom 10%", col, d2[(d2["quantile"] <= 10 / 100)][col].mean(), d2[(d2["quantile"] >= 90 / 100)][col].mean())
    print("top/bottom 25%", col, d2[(d2["quantile"] <= 25 / 100)][col].mean(), d2[(d2["quantile"] >= 75 / 100)][col].mean())

    # plot
    pearson, p = sp.stats.pearsonr(d2[col].dropna(), d2['EventRank'].dropna())
    pearson = round(pearson, 2)
    p = '{:.2e}'.format(p)
    # kind= ['scatter', 'hist', 'hex', 'kde', 'reg', 'resid']
    sns.jointplot(x=col, y='EventRank', data=d2, kind='scatter').ax_joint.text(
        s=f' pearsonr = {pearson}; p = {p} ', ha='left', va='top', x=5, y=10,
        bbox={'boxstyle': 'round', 'pad': 0.25, 'facecolor': 'white', 'edgecolor': 'gray'})

    g = sns.lmplot(x=col, y='EventRank', data=d2, x_estimator=np.mean)
    #g.map_dataframe(annotate)

    plt.xticks(rotation=90)
    #plt.show()
    # plt.savefig("{}.png".format(col))
    # plt.close()

if __name__ == "__main__":
    year = 2024
    division = DIVISION_WOMEN
    file = "24.1"  # open_{}_{}".format(year, "women" if division == DIVISION_WOMEN else "men")
    path = os.path.abspath(os.getcwd()) + '\\'

    # Get data
    if 0:
        df = get_data('2024', 2)
        print(df.tail())

        # Save to CSV
        file += "_local"
        df.to_csv(path + "{}.csv".format(file), encoding="utf-8-sig", index=False)

    # Read CSV
    df = pd.read_csv(path + "{}.csv".format(file), encoding='utf-8-sig', low_memory=False)
    print(df.columns)

    # Compute quantile before cleaning for missing values
    df['quantile'] = df.groupby('AthleteGender')['EventRank'].rank(pct=True)

    # Get data for women and men
    # Clean up outliers
    df_women = df[df['AthleteGender'] == "F"]
    df_women = clean(df_women, ["HeightCm", "WeightKg"])
    df_women['quantile_clean'] = df_women['EventRank'].rank(pct=True)

    df_men = df[df['AthleteGender'] == "M"]
    df_men = clean(df_men, ["HeightCm", "WeightKg"])
    df_men['quantile_clean'] = df_men['EventRank'].rank(pct=True)

    df = clean(df, ["HeightCm", "WeightKg"])
    df['quantile_clean'] = df.groupby('AthleteGender')['EventRank'].rank(pct=True)

    # ----------
    # Analyze

    height_weight(df_women)
    #height_weight(df_men)

    analyze_rank_versus(df_women, "HeightCm")
    analyze_rank_versus(df_men, "HeightCm")
    analyze_all_rank_versus(df, "HeightCm")

    analyze_rank_versus(df_women, "WeightKg")
    analyze_rank_versus(df_men, "WeightKg")
    # analyze_all_rank_versus(df, "WeightKg")

    # ----------------------------
    # Spot athletes
    dd = df[(df['AthleteGender'] == "F") & (df["HeightCm"] > 189) & (df["quantile"] <= 5 / 100)]
    print(dd.head())
    # dd = df_clean[(df_clean['AthleteGender'] == "F") & (df_clean["HeightCm"] < 150) & (df_clean["quantile"] <= 5 / 100)]
    # print(dd.head())
    dd = df[(df['AthleteGender'] == "F") & (df["WeightKg"] > 95) & (df["quantile"] <= 5 / 100)]
    print(dd.head())
    #
    # dd = df_clean[(df_clean['AthleteGender'] == "M") & (df_clean["HeightCm"] > 189) & (df_clean["quantile"] <= 5 / 100)]
    # print(dd.head())
    # dd = df_clean[(df_clean['AthleteGender'] == "M") & (df_clean["HeightCm"] < 150) & (df_clean["quantile"] <= 5 / 100)]
    # print(dd.head())
    # dd = df_clean[(df_clean['AthleteGender'] == "M") & (df_clean["WeightKg"] > 100) & (df_clean["quantile"] <= 5 / 100)]
    # print(dd.head())

    # ----------------------------

    # # Create SQL
    # engine = create_engine('mysql+pymysql://babetgoug:pokpok@babetgoug.info/crossfit')
    # #cnx = engine.connect().execution_options(stream_results=True)  # use server side cursors
    # cnx = engine.raw_connection()
    #
    # types=['INT','INT','INT','INT','TEXT','TEXT','TEXT','BOOLEAN','BOOLEAN','INT','INT','TEXT','TEXT','INT','TEXT']*NUM_WODS
    # print(len(types), len(col_score_info_total))
    # for i in range(0,len(col_score_info_total)):
    #     print(col_score_info_total[i]+" "+types[i]+",")
    #
    #
    # create_table(cnx, "open_2024_women")
    # populate_table("open_2024_women", path + 'open_2024_women.csv', cnx)
    #
    # cnx.close()