import pandas as pd
from pickling import unpickle_data
from matplotlib import pyplot as plt
import seaborn as sns
from config import box_param, sqft_range, yr_built_range
from map import create_map
import timeit


#%matplotlib inline #or qt

df_l = unpickle_data('data/df_listing.pickle')
df_s = unpickle_data('data/df_sold.pickle')
df_s = df_s.rename(columns = {'price':'price_range','avg_p':'price'})
#df_l = df_l[df_l.yr_built>2012]
#df_s = df_s[df_s.yr_built>2012]

NH = ['Timbergrove','Heights','Rice Military', 'Cottage Grove', 'Washington East/Sabine',\
      'Memorial Park', 'Greenway Plaza', 'Briar Hollow', 'Highland Village',\
          'River Oaks', 'Royden Oaks', 'Midtown', 'Montrose', 'Oaks Shopping',\
              'Memorial Villages', 'Memorial Close', 'Rice/Museum District', '9.1',\
                  'Memorial West', 'Galleria', 'Tanglewood','Willow Meadows', \
                     'Knollwood', 'Braeswood','West University','Medical Center','Hobby','Downtown',\
                         'Bellaire','Upper Kirby','Milroy','Dart']

def filter_(df, filter_range):
    '''  filter_range is a dictionary with kye=column header, and range in a list [min, max]'''
    key = list(filter_range.keys())[0]
    df1 = df[ (df[key] >= filter_range[key][0]) & (df[key] <= filter_range[key][1])]
    return df1


def create_box(x, y, hue, df_l, df_s, filter_range):

    if hue == 'listing_sold':
        df_l[hue] = 'listing'
        df_s[hue] = 'sold'
    
    key = list(filter_range.keys())[0]
    lower_bound = filter_range[key][0]
    upper_bound = filter_range[key][1]
    
    df = pd.concat([df_l[[x, y, hue, key]], df_s[[x, y, hue, key]]], ignore_index=True)
    df = df[ (df[key] >= lower_bound) & (df[key] <= upper_bound)]
    

    chart = sns.catplot(x=x,y=y,hue=hue,kind='box',palette='pastel',data=df, height=7, aspect=3)

    title = 'Houston Real Estate | '+key+' '+str(lower_bound)+' - '+str(upper_bound)
    chart.fig.suptitle(title)
    chart.set_xticklabels(rotation=90)
    
    chart.savefig('./graphs/box_plot_by_'+x+'.png', dpi=600)

    return chart


def create_heatmaps():

    # TODO: add realtor activity heatmap
    
    # Heatmap of average sold prices - ZIP Codes
    df = df_s[['zip', 'sqft', 'price','min_p','neighborhood']]
    df = df.dropna()
    
    df['sqft_binned'] = pd.cut(df['sqft'],[2000,2250,2500,2750,3000,3250,3500,3750,4000])
    df = filter_(df, {'sqft': [2000,4000]})
    
    pivot = pd.pivot_table(df, values='price', index='zip', columns='sqft_binned', aggfunc='mean')
    
    f, ax = plt.subplots(figsize=(12, 6))
    hm = sns.heatmap(pivot, annot=True, linewidths=.1, ax=ax, cmap="YlGnBu")
    
    title = 'Houston Real Estate | Avg Sold Prices'
    ax.set_title(title)

    figure = hm.get_figure()
    figure.savefig('./graphs/heatmap1.png', dpi=600)
    
    # Heatmap of average sold prices - Neighborhood
    pivot = pd.pivot_table(df, values='price', index='neighborhood', columns='sqft_binned', aggfunc='mean')
    
    f, ax = plt.subplots(figsize=(12, 6))
    sns.heatmap(pivot, annot=True, linewidths=.1, ax=ax, cmap="YlGnBu")
    
    title = 'Houston Real Estate | Avg Sold Prices'
    ax.set_title(title)
    
    figure = hm.get_figure()
    figure.savefig('./graphs/heatmap2.png', dpi=600) 
    
    # Heatmap of mean min sold prices - ZIP condes
    pivot = pd.pivot_table(df, values='min_p', index='zip', columns='sqft_binned', aggfunc='mean')
    
    f, ax = plt.subplots(figsize=(12, 6))
    sns.heatmap(pivot, annot=True, linewidths=.1, ax=ax, cmap="YlGnBu")
    
    title = 'Houston Real Estate | Avg Min Sold Prices'
    ax.set_title(title)
    
    figure = hm.get_figure()
    figure.savefig('./graphs/heatmap3.png', dpi=600)
    
    
    # Heatmap of mean min sold prices - Neighborhood
    pivot = pd.pivot_table(df, values='min_p', index='neighborhood', columns='sqft_binned', aggfunc='mean')
    
    f, ax = plt.subplots(figsize=(12, 6))
    sns.heatmap(pivot, annot=True, linewidths=.1, ax=ax, cmap="YlGnBu")
    
    title = 'Houston Real Estate | Avg Min Sold Prices'
    ax.set_title(title)
    
    figure = hm.get_figure()
    figure.savefig('./graphs/heatmap4.png', dpi=600)
    
    # Heatmap of mean listed prices - ZIP Code
    df = df_l[['zip', 'sqft', 'price','neighborhood']]
    df = df.dropna()
    
    df['sqft_binned'] = pd.cut(df['sqft'],[2000,2250,2500,2750,3000,3250,3500,3750,4000])
    df = filter_(df, {'sqft': [2000,4000]})
    
    pivot = pd.pivot_table(df, values='price', index='zip', columns='sqft_binned', aggfunc='mean')
    
    f, ax = plt.subplots(figsize=(12, 6))
    sns.heatmap(pivot, annot=True, linewidths=.1, ax=ax, cmap="YlGnBu")
    
    title = 'Houston Real Estate | Listed Prices'
    ax.set_title(title)
    
    figure = hm.get_figure()
    figure.savefig('./graphs/heatmap5.png', dpi=600)
    
    # Heatmap of mean listed prices - Neighborhood
    pivot = pd.pivot_table(df, values='price', index='neighborhood', columns='sqft_binned', aggfunc='mean')
    
    f, ax = plt.subplots(figsize=(12, 6))
    sns.heatmap(pivot, annot=True, linewidths=.1, ax=ax, cmap="YlGnBu")
    
    title = 'Houston Real Estate | Listed Prices'
    ax.set_title(title)
    
    figure = hm.get_figure()
    figure.savefig('./graphs/heatmap6.png', dpi=600)
    

def undervalued():

    # get sold data
    df_sold = df_s[['zip', 'sqft', 'min_p', 'neighborhood','yr_built', 'price']]
    df_sold = df_sold.dropna()
    
    # categorize sqft
    bins = list(range(sqft_range[0],sqft_range[1]+250,250))
    labels = bins.copy()
    labels.pop(0)
    df_sold['sqft_binned'] = pd.cut(df_sold['sqft'], bins, True, labels)
    
    # filter for houses based on user input (e.g. 2000-4000 sqft)
    df_sold = filter_(df_sold, {'sqft': sqft_range})
    df_sold = filter_(df_sold, {'yr_built': yr_built_range})
    
    # create 3 pivots to categorize "undervalue" amount
    pivot_minp_minf = pd.pivot_table(df_sold, values='min_p', index='neighborhood', columns='sqft_binned', aggfunc='min')
    pivot_minp_minf = pivot_minp_minf.fillna(-1)

    pivot_minp_meanf = pd.pivot_table(df_sold, values='min_p', index='neighborhood', columns='sqft_binned', aggfunc='mean')
    pivot_minp_meanf = pivot_minp_meanf.fillna(-1)

    pivot_meanp_minf = pd.pivot_table(df_sold, values='price', index='neighborhood', columns='sqft_binned', aggfunc='min')
    pivot_meanp_minf = pivot_meanp_minf.fillna(-1)

    # get listing data
    df_listing = df_l[['mls', 'price', 'address', 'zip', 'sqft', 'yr_built','neighborhood']]
    df_listing = df_listing.dropna()
  
    # categorize sqft
    df_listing['sqft_binned'] = pd.cut(df_listing['sqft'], bins, True, labels)

    # filter for houses based on user input and to match filtering on "sold data"
    df_listing = filter_(df_listing, {'sqft': sqft_range})
    df_listing = filter_(df_listing, {'yr_built': yr_built_range})

    df_listing['undervalued'] = False
    df_listing['tag_color'] = "Red"
    df_listing = df_listing.dropna()
    
    for i in list(df_listing.index):
        
        #zip_ = str(df_listing.loc[i,'zip'])
        nh = df_listing.loc[i,'neighborhood']
        sqft_= int(df_listing.loc[i,'sqft_binned'])

        if nh in list(pivot_meanp_minf.index) and df_listing.loc[i,'price'] < pivot_meanp_minf.loc[nh,sqft_]:
            df_listing.loc[i,'undervalued'] = pivot_meanp_minf.loc[nh,sqft_]
            df_listing.loc[i, 'tag_color'] = 'orange'

            if nh in list(pivot_minp_meanf.index) and df_listing.loc[i,'price'] < pivot_minp_meanf.loc[nh,sqft_]:
                df_listing.loc[i,'tag_color'] = 'lightgreen'

                if nh in list(pivot_minp_minf.index) and df_listing.loc[i,'price'] < pivot_minp_minf.loc[nh,sqft_]:
                    df_listing.loc[i,'tag_color'] = 'green'
            
    df_listing = df_listing[df_listing.undervalued != False]
    df_listing['undervalue_delta'] = df_listing['undervalued'] - df_listing['price']
    df_listing['undervalue_delta'].astype(int)

    print(df_listing.head())
    print(df_listing.shape)
    create_map(df_listing)

def get_neighborhood():

    df_l.loc[:,'neighborhood'] = None
    
    for i in list(df_l.index):
        
        for nh in NH:
            if nh in df_l.at[i,'style']:   df_l.at[i,'neighborhood'] = nh
        if df_l.at[i,'neighborhood'] not in NH: df_l.at[i,'neighborhood'] = 'other'
                
    df_s.loc[:,'neighborhood'] = None
    
    for i in list(df_s.index):
        
        for nh in NH:
            if nh in df_s.at[i,'style']:   df_s.at[i,'neighborhood'] = nh
        if df_s.at[i,'neighborhood'] not in NH: df_s.at[i,'neighborhood'] = 'other'


def run_analysis():
    print('in run_analysis')
    get_neighborhood()

    create_box('neighborhood','price','listing_sold', df_l, df_s, box_param)
    create_box('zip','price','listing_sold', df_l, df_s, box_param)

    create_heatmaps()

    undervalued()


#overall = timeit.default_timer()
#run_analysis()
#print('overall took:',timeit.default_timer() - overall)