import os
from sqlalchemy import create_engine
import pandas as pd
import numpy as np

import statsmodels.api as sm
from IPython.display import HTML, display

from plotly.express import scatter

from textblob import TextBlob

import statsmodels.api as sm

pd.options.display.max_columns = 300

pg_un, pg_pw, pg_ip, pg_port = [os.getenv(x) for x in ['PG_UN', 'PG_PW', 'PG_IP', 'PG_PORT']]

def pg_uri(un, pw, ip, port):
    return f'postgresql://{un}:{pw}@{ip}:{port}'

eng = create_engine(pg_uri(pg_un, pg_pw, pg_ip, pg_port))

sql = '''
WITH contestants_to_seasons AS (
SELECT c.contestant_id, c.first_name, 
	   c.last_name, cs.contestant_season_id, c.sex, 
	   cs.season_id, occupation, location, age, placement, 
	   days_lasted, votes_against, 
	   med_evac, quit, individual_wins, attempt_number, 
	   tribe_0, tribe_1, tribe_2, tribe_3, alliance_0, 
	   alliance_1, alliance_2,
	   challenge_wins, challenge_appearances, sitout, 
	   voted_for_bootee, votes_against_player, character_id, 
       r.role, r.description,
	   total_number_of_votes_in_episode, tribal_council_appearances, 
	   votes_at_council, number_of_jury_votes, total_number_of_jury_votes, 
	   number_of_days_spent_in_episode, days_in_exile, 
	   individual_reward_challenge_appearances, individual_reward_challenge_wins, 
	   individual_immunity_challenge_appearances, individual_immunity_challenge_wins, 
	   tribal_reward_challenge_appearances, tribal_reward_challenge_wins, 
	   tribal_immunity_challenge_appearances, tribal_immunity_challenge_wins, 
	   tribal_reward_challenge_second_of_three_place, tribal_immunity_challenge_second_of_three_place, 
	   fire_immunity_challenge, tribal_immunity_challenge_third_place, episode_id
FROM survivor.contestant c
RIGHT JOIN survivor.contestant_season cs
ON c.contestant_id = cs.contestant_id
JOIN survivor.episode_performance_stats eps
ON eps.contestant_id = cs.contestant_season_id
JOIN survivor.role r
ON cs.character_id = r.role_id
), matched_exact AS 
(
SELECT reddit.*, c.*
FROM survivor.reddit_comments reddit
JOIN contestants_to_seasons c
ON (POSITION(c.first_name IN reddit.body) > 0
OR POSITION(c.last_name IN reddit.body) > 0)
AND c.season_id = reddit.within_season
AND c.episode_id = reddit.most_recent_episode
WHERE within_season IS NOT NULL
)
SELECT * 
FROM matched_exact m
'''

reddit_df = pd.read_sql(sql, eng)

ep_df = pd.read_sql('SELECT * FROM survivor.episode', eng)

season_to_name = pd.read_sql('SELECT season_id, name AS season_name FROM survivor.season', eng)

reddit_df = reddit_df.merge(season_to_name, on='season_id')

reddit_df.rename(columns={'name': 'season_name'}, inplace=True)

reddit_df = reddit_df.merge(ep_df.drop(columns=['season_id']), on='episode_id')

reddit_df['created_dt'] = pd.to_datetime(reddit_df['created_dt'])

pd.options.display.max_columns = 100

def extract_overall_and_sentence_sentiment(row):
    body = row['body']
    first = row['first_name']
    last = row['last_name']
    
    b = TextBlob(body)
    overall_sentiment = b.sentiment
    overall_polarity = overall_sentiment.polarity
    overall_subj = overall_sentiment.subjectivity
    
    rel_sentences_sentiment = [x.sentiment 
                     for x in b.sentences 
                     if (first in x) or (last in x)]
    
    sentence_polarity = np.mean([y.polarity for y in rel_sentences_sentiment])
    sentence_subj = np.mean([y.subjectivity for y in rel_sentences_sentiment])

    metrics = [overall_polarity, overall_subj, sentence_polarity, sentence_subj]
    metric_names = ['overall_polarity', 'overall_subj', 'sentence_polarity', 'sentence_subj']
    
    
    return pd.Series(metrics, index=metric_names)
    
def add_sentiment_cols(df):
    sentiment_df = df.apply(extract_overall_and_sentence_sentiment, axis=1)
    return pd.concat([df, sentiment_df], axis=1)

reddit_df = add_sentiment_cols(reddit_df)

tribe_df = pd.read_sql('SELECT tribe_id, name FROM survivor.tribe', con=eng)
alliance_df = pd.read_sql('SELECT alliance_id, name FROM survivor.alliance', con=eng)

tribe_map = tribe_df.set_index('tribe_id')['name']
alliance_map = alliance_df.set_index('alliance_id')['name']

reddit_df['contestant_episode'] = reddit_df['first_name'] + ' ' + reddit_df['last_name'] + ' ' + reddit_df['episode_name']

def grab_first_if_exists(l):
    try:
        return l[0]
    except IndexError:
        pass

def preprocess_df(df):
    df = df.copy()
    df.loc[df['age'].str.contains('[^\d]'), 'age'] = None
    
    drop = df['sentence_polarity'].isnull() | df['age'].isnull() | df['sex'].isnull() 
    df = df[~drop].reset_index(drop=True)
    df['age'] = df['age'].astype(float)
    df['won'] = (df['placement'] == 1).astype(int)
    df['runnerup'] = (df['placement'].isin([2, 3])).astype(int)
    df['pct_overall_slot'] = df['survivor_rating'] / df['overall_slot_rating']
    df['pct_overall_slot'].fillna(df['pct_overall_slot'].mean(), inplace=True)
    
    merge_on = ['contestant_season_id', 'episode_id']
    merger_df = df.drop_duplicates(merge_on)
    
    cols_sum = ['sitout', 'days_in_exile', 
                'individual_reward_challenge_wins', 'individual_immunity_challenge_wins', 
                'tribal_reward_challenge_wins', 'tribal_immunity_challenge_wins']
    
    for col in cols_sum:
        merger_df['total_' + col] = merger_df.groupby('contestant_season_id')[col].transform(lambda x: x.sum())
    
    add_cols = ['total_' + c for c in cols_sum] + merge_on
    
    df = df.merge(merger_df[add_cols], 
                       on=merge_on)
        
    
    return df

def create_binned_df(df, col, bins, labels):
    df[col + '_bins'] = pd.cut(df[col], bins=bins, labels=labels)
    return pd.get_dummies(df[col + '_bins'], prefix=col, drop_first=True)

def create_binned_age_df(df):
    bins = [16, 25, 35, 45, 65, 150]
    labels = ['16-25', '26-35', '36-45', '46-65', '65+']
    col = 'age'
    
    return create_binned_df(df, col, bins, labels)

def create_binned_days_lasted(df):
    bins = [0, 10, 20, 30, 40]
    labels = ['0-10 days', '10-20 days', '20-30 days', '30+ days']
    col = 'days_lasted'
    
    return create_binned_df(df, col, bins, labels) 

    
def extract_features(df):
    df = df.copy()
    
    single_cols = ['days_lasted', 'age', 'votes_against', 
                   'individual_wins', 
                   'season_episode_number', 'won',
                   'runnerup', 'pct_overall_slot']
    
    agg_cols = ['sitout', 'days_in_exile', 
                'individual_reward_challenge_wins', 'individual_immunity_challenge_wins', 
                'tribal_reward_challenge_wins', 'tribal_immunity_challenge_wins']
    
    agg_cols_tot = ['total_' + x for x in agg_cols]
    
    sex_df = pd.get_dummies(df['sex'], prefix='sex',drop_first=True)
    contestant_df = pd.get_dummies(df['first_name'] + ' ' + df['last_name'], prefix='contestant', drop_first=True)
    role_df = pd.get_dummies(df['role'].astype(str), prefix='role', drop_first=True)
    season_id_df = pd.get_dummies(df['season_name'], prefix='season', drop_first=True)
    contestant_episode_df = pd.get_dummies(df['contestant_episode'], prefix='contestant_episode', drop_first=True)

    contestant_episode_df = contestant_episode_df.loc[:, contestant_episode_df.sum() > 25]
#     episode_df = pd.get_dummies(df['episode_name'], prefix='episode', drop_first=True)

    
    
    tribe_df = pd.concat([pd.get_dummies(df[c].map(tribe_map), prefix='tribe', drop_first=False)
                          for c in ['tribe_0', 'tribe_1', 'tribe_2']], axis=1, join='outer')
    alliance_df = pd.concat([pd.get_dummies(df[c].map(alliance_map), prefix='alliance', drop_first=False)
                          for c in ['alliance_0', 'alliance_1', 'alliance_2']], axis=1, join='outer')
    
    tribe_df = tribe_df.loc[: , ~tribe_df.columns.duplicated(keep='first')].iloc[:, 1:]
    alliance_df = alliance_df.loc[:, ~alliance_df.columns.duplicated(keep='first')].iloc[:, 1:]
    
    age_df = create_binned_age_df(df)
    days_lasted_df = create_binned_days_lasted(df)
    
    ret_df =  pd.concat([df[single_cols], df[agg_cols], df[agg_cols_tot], 
                      age_df, sex_df, contestant_df, role_df, days_lasted_df,
                      season_id_df, tribe_df, alliance_df,
                      contestant_episode_df], axis=1)
    
    contestant_cols = contestant_df.columns.tolist()
    remove_cols = ret_df.groupby(contestant_cols).apply(lambda x: grab_first_if_exists(x.columns[x.columns.str.contains('contestant_episode') & (x > 0).any()].tolist()))
    remove_cols = remove_cols.dropna().values.tolist()
    
    ret_df.drop(columns=remove_cols, inplace=True)
    return ret_df

def extract_y_X(df, y_name='sentence_polarity'):
    df = preprocess_df(df)
    X = extract_features(df)
    X = sm.add_constant(X)
    y = extract_dependent(df)
    return y, X

def extract_dependent(df, col_name='sentence_polarity'):
    return df[col_name]
    

y, X = extract_y_X(reddit_df)

y.head()

X.head()

full_fit = sm.OLS(y, X).fit()

full_fit.summary()