### Global imports
import dill
from cse6040_devkit import plugins, utils

from pprint import pprint, pformat

import numpy as np
import pandas as pd
import sqlite3
import random
from sklearn.linear_model import LinearRegression
from sklearn.feature_selection import SequentialFeatureSelector

# Load starting data and define a database connection
stock_data = utils.load_object_from_publicdata('raw_stock_data_nflx.dill')

conn = sqlite3.connect('resource/asnlib/publicdata/NetflixDb.sqlite_db')

stock_subsample_df_demo_CORRECT = utils.load_object_from_publicdata('stock_subsample_df_demo.dill')
convert_timezone_types          = utils.load_object_from_publicdata('convert_timezone_types.dill')
display(stock_subsample_df_demo_CORRECT)

stock_data[:10]

### Solution - Exercise 0  
def load_data(records: list) -> pd.DataFrame:
    
    '''
    Steps:
    1. Examine the input (records) to get a sense of what the data looks like.
    2. Create a dataframe with the list of dictionaries.
    3. Convert the date column to the data type "datetime64[ns, America/New_York]".
    4. Rename the 'Stock' column to be 'Firm'.
    5. Return the dataframe.
    
    Note:
    If you want to not use the built in function, this is how you would convert the dates:
    my_df['Date'] = my_df['Date'].apply(lambda x: pd.to_datetime(x).tz_convert('America/New_York'))
    '''
    
    my_df = pd.DataFrame(records)
    my_df['Date'] = convert_timezone_types(my_df['Date'])
    my_df = my_df.rename(columns={'Stock': 'Firm'})
        
    return my_df

### Demo function call
### `convert_timezone_types` demo ---------------------------------------------
# This helper function will help you change the data-type
# -- Input:  pd.Series, with dtype "string"
# -- Output: pd.Series, with dtype "datetime64[ns, America/New_York]"
type_conversion_demo = pd.DataFrame({
    'Date': ['2017-11-15 00:00:00-05:00', '2021-01-15 00:00:00-05:00'],
    'DemoColumn': [1, 2]
})
# Convert the type!
type_conversion_demo['Date'] = convert_timezone_types(type_conversion_demo['Date'])
assert type_conversion_demo['Date'].dtype == 'datetime64[ns, America/New_York]' # It works!


### `load_data` demo ----------------------------------------------------------
# We'll sample the dataset to 20 records, just to make debugging easier.
random.seed(6040)
stock_data_subsample = random.sample(stock_data, k=10)
# Here's what your solution produces!
print('Here is what your loaded data looks like in Pandas:')
stock_subsample_df = load_data(stock_data_subsample)
display(stock_subsample_df)

### Test Cell - Exercise 0  

# Load testing utility
with open('resource/asnlib/publicdata/execute_tests', 'rb') as f:
    execute_tests = dill.load(f)

# Execute test
passed, test_case_vars = execute_tests(func=plugins.sqlite_blocker(load_data),
              ex_name='load_data',
              key=b'a9pptsbNJew33Kjoo3e2RkFk0FT2N1lXclu3U9zq8Aw=', 
              n_iter=100)
# Assign test case vars for debugging
input_vars, original_input_vars, returned_output_vars, true_output_vars = test_case_vars

assert passed, 'The solution to load_data did not pass the test.'

###
### AUTOGRADER TEST - DO NOT REMOVE
###
print('Passed! Please submit.')

### Run Me!!!
stock_df = utils.load_object_from_publicdata('stock_df')

### Solution - Exercise 1  
def preview_data(records: list, conn: sqlite3.Connection) -> tuple:
    # Filter out any records beyond the second element
    records_preview = records.head(n=10)

    # Select everything from nflx_originals and randomly sample 5 rows
    nflx_originals_preview = pd\
        .read_sql('SELECT * FROM nflx_originals', conn)\
        .sample(5)

    # Select everything from nflx_top and randomly sample 5 rows
    nflx_top_preview = pd\
        .read_sql('SELECT * FROM nflx_top', conn)\
        .sample(5)

    return records_preview, nflx_originals_preview, nflx_top_preview

### Demo function call
records_preview, nflx_originals, nflx_top = preview_data(stock_df, conn)
print('Preview of Stock Data')
display(records_preview)
print('------------------------------------------------------------------------')
print('Preview of Netflix Originals Table')
display(nflx_originals)
print('------------------------------------------------------------------------')
print('Preview of Netflix Top Programs Table')
display(nflx_top)

### Test Cell - Exercise 1  


print('Passed! Please submit.')

### Run Me!!!
calculate_daily_growth_demo_input = utils.load_object_from_publicdata('calculate_daily_growth_demo_input')

print("Demo output for Netflix:")

daily_growth_df_CORRECT = utils.load_object_from_publicdata('daily_growth_df.dill')
display(daily_growth_df_CORRECT)

calculate_daily_growth_demo_input.head()

### Solution - Exercise 2  
def calculate_daily_growth(stock_records: pd.DataFrame, firm: str) -> pd.DataFrame:
    
    '''
    Steps:
    1. Examine the input dataframe, stock_records, to understand what the data looks like. For us, that is 
        calculate_daily_growth_demo_input.
    2. Create a new dataframe called df.
        a. Create a copy of the stock_records df, so we don't accidentally modify the original dataframe.
        b. Filter so we are only looking at the firm specified in the "firm" input string.
        c. Create a new column called Day_Growth. I decided to initialize it so every value was 0.0, since this will
            help with the days with NaN Open and Close values.
    3. Isolate the rows that have valid open / close prices.
        a. Create one dataframe that only contains rows with NaN valus for Open. We want to be able to merge these
            back with our other dataframe in the end.
        b. Create another dataframe with NaN rows dropped (for the columns open and close).
                Note: for this, I looked at the Pandas documentation for dropna to remember the syntax for looking
                    at a subset.
    4. Utilize the shift() function to easily get the last closing value.
        a. Look at the linked documentation for shift and some examples.
        b. Use the shift function and print the results to confirm that it is doing what we want it to.
        c. Create a new column called "last_close" to hold these shifted values.
            Note: I kept getting the SettingWithCopyWarning when doing this. I ignored it until the end and came
                back to it. There were two ways that I found to fix it: either adding a .copy() at the end of the
                statement creating df_no_na or by using an "assign" function that I found when looking through
                StackOverflow. I have included both below.
    5. Calculate the Day_Growth column using the formula above: (close0/close-1)-1.
        a. Since we will want to use multiple columns, I automatically knew that we would want to use an apply
            function along the non-default axis. I always forget which axis to use, so I typically try both to
            determine which is correct. In this case, it would be axis=1.
        b. Use the apply function and then set the Day_Growth column equal to these calculated values.
    6. Combine the NaN rows with the rows that we just used (df_no_na).
        a. Delete the column "last_close" that we created for temporary storage.
        b. Use the concat function to add the na_rows to our dataframe, df_no_na.
        c. Sort the values by date to fix the order.
    7. Reset the index of stock_growth and return.
    
    When I followed these steps, I had an error. I compared my output with the expected output to realize that the
    first row of my df_no_na dataframe had a Day_Growth of NaN due to the nature of the shift function. So, I filled
    the NaN rows in df_no_na to fix this problem.
    '''
    
    # step 2
    df = stock_records.copy()
    df = df[df['Firm']==firm]
    df['Day_Growth'] = 0.000000
    
    # step 3
    na_mask = df['Open'].isna()
    na_rows = df[na_mask]
    # print(na_rows.head())
    
    df_no_na = df.dropna(subset=['Open', 'Close']).copy()
    
    # step 4
    shifted_vals = df_no_na['Close'].shift(periods=1)
    # print(shifted_vals)
    df_no_na = df_no_na.assign(last_close = shifted_vals.values)
    # if using .copy(), could just say df_no_na['last_close'] = list(shifted_vals.values)
    
    # step 5
    new_day_growth = df_no_na.apply(lambda x: (x['Close']/x['last_close'])-1, axis=1).copy()
    df_no_na = df_no_na.assign(Day_Growth = new_day_growth.values)
    # if using .copy(), could just say df_no_na['Day_Growth'] = list(new_day_growth.values)
    
    # step 6
    del df_no_na['last_close']
    df_no_na.fillna(0, inplace=True)        # step 8

    stock_growth = pd.concat([na_rows, df_no_na])
    stock_growth = stock_growth.sort_values(by='Date')
    
    # step 7
    return stock_growth.reset_index(drop=True)
    
    

### Demo function call
print("Demo daily growth for Netflix's stock:")
daily_growth_nflx_demo = calculate_daily_growth(
    stock_records=calculate_daily_growth_demo_input,
    firm='NFLX'
)
display(daily_growth_nflx_demo)

### Test Cell - Exercise 2  

# Load testing utility
with open('resource/asnlib/publicdata/execute_tests', 'rb') as f:
    execute_tests = dill.load(f)

# Execute test
passed, test_case_vars = execute_tests(func=plugins.sqlite_blocker(calculate_daily_growth),
              ex_name='calculate_daily_growth',
              key=b'a9pptsbNJew33Kjoo3e2RkFk0FT2N1lXclu3U9zq8Aw=', 
              n_iter=100)
# Assign test case vars for debugging
input_vars, original_input_vars, returned_output_vars, true_output_vars = test_case_vars

assert passed, 'The solution to calculate_daily_growth did not pass the test.'

###
### AUTOGRADER TEST - DO NOT REMOVE
###
print('Passed! Please submit.')

### Run Me!!!
stock_daily_growths_df = utils.load_object_from_publicdata('stock_daily_growths_df')
stock_daily_growths_reduced_df = utils.load_object_from_publicdata('stock_daily_growths_reduced_df')

print("Demo annual growth:")

annual_growth_demo_df_CORRECT = utils.load_object_from_publicdata('annual_growth_demo_df.dill')
display(annual_growth_demo_df_CORRECT)

### Uncomment these lines for additional information!
# print("Annual growth, grouped together:")

# annual_growth_grouped_df_demo = utils.load_object_from_publicdata('annual_growth_grouped_df_demo.dill')
# display(annual_growth_grouped_df_demo)

### Solution - Exercise 3  
def calculate_annual_growth(stock_records: pd.DataFrame) -> pd.DataFrame:
    
    '''
    Steps:
    1. Examine the stock_records dataframe to remind us visually the difference between our input and expected
        output.
    2. Create a new dataframe called annual_growth with a new "Year" column.
        a. Create a copy of the input dataframe, stock_records.
        b. Extract the year from the Date column.
            Googled: "get year from datetime pandas"
            Referenced: https://stackoverflow.com/questions/25146121/extracting-just-month-and-year-separately-from-pandas-datetime-column
        c. Convert Year to a string, as instructed above.
    3. Look at the instructions to get an understanding of how to calculate Annual Growth. The below instruction helps
        break it down into steps:
            "The Annual_Growth is calculated by adding 1 to each Day_Growth observation. Multiplying all of them 
             together. Then subtracting 1 from the product."
    4. Calculate Annual_Growth.
        a. Create a new column called "updated_day" that holds the value of Day_Growth + 1.
        b. Group by Firm and Year, multiplying the values of updated_day together.
            Googled: "pandas groupby multiply values from group set to new column"
            Referenced: https://stackoverflow.com/questions/69471371/pandas-multiply-row-value-by-groupby-of-another-column-as-a-new-column
        c. Delete the temporary updated_day column.
        d. Subtract 1 from the Annual_Growth.
    5. Return the final dataframe after reordering the columns and resetting the index.

    '''
    
    # step 2
    annual_growth = stock_records.copy()
    annual_growth['Year'] = annual_growth['Date'].dt.year
    annual_growth['Year'] = annual_growth['Year'].astype(str)
    
    # step 4
    annual_growth['updated_day'] = annual_growth.apply(lambda x: x['Day_Growth']+1, axis=1)
    annual_growth['Annual_Growth'] = annual_growth.groupby(by=['Firm', 'Year'])['updated_day'].transform('prod')
    del annual_growth['updated_day']
    annual_growth['Annual_Growth'] = annual_growth['Annual_Growth'].apply(lambda x: x-1)
    
    # step 5
    annual_growth = annual_growth[['Date', 'Year', 'Firm', 'Open', 'Close', 'Volume', 'Day_Growth', 'Annual_Growth']]
    return annual_growth.reset_index(drop=True)

### Demo function call
annual_growth_demo_df = calculate_annual_growth(stock_records=stock_daily_growths_reduced_df)
display(annual_growth_demo_df)

### Test Cell - Exercise 3  

# Load testing utility
with open('resource/asnlib/publicdata/execute_tests', 'rb') as f:
    execute_tests = dill.load(f)

# Execute test
passed, test_case_vars = execute_tests(func=plugins.sqlite_blocker(calculate_annual_growth),
              ex_name='calculate_annual_growth',
              key=b'a9pptsbNJew33Kjoo3e2RkFk0FT2N1lXclu3U9zq8Aw=', 
              n_iter=100)
# Assign test case vars for debugging
input_vars, original_input_vars, returned_output_vars, true_output_vars = test_case_vars

assert passed, 'The solution to calculate_annual_growth did not pass the test.'

###
### AUTOGRADER TEST - DO NOT REMOVE
###
print('Passed! Please submit.')

### Run Me!!!
annual_growth_df = utils.load_object_from_publicdata('annual_growth_df')

print("Example Golden Cross Demo:")

golden_cross_demo_df_CORRECT = utils.load_object_from_publicdata('golden_cross_demo_df.dill')
display(golden_cross_demo_df_CORRECT.head(15))

### Solution - Exercise 4  
def golden_cross_strategy(stockdf: pd.DataFrame, firm: str, short_average: int, long_average: int) -> pd.DataFrame:
    
    '''
    Steps:
    1. Filter the DataFrame to include records for the firm parameter.
        a. Create a copy of the stockdf dataframe.
        b. Only include rows where the 'Firm' is equal to the input string firm.
    2. Some Close values are recorded as NaN. These should be filtered out before calculating the moving averages.
        a. Use dropna to filter out rows where Close values are NaN.
    3. Add the columns Short_Average and Long_Average to the Pandas DataFrame.
        a. Look at the linked documentation for how to use the rolling function and look at some examples.
        b. Since a Rolling object has no average attribute that I found, manually calculate the average values.
        c. Use the assign method to avoid the SettingWithCopyWarning.
    4. Add the column 'Golden_Cross', a boolean, to the Pandas DataFrame.
        Method 1:
            a. Create a Series where values are True if the Short_Average is greater than the Long_Average.
            b. Create a new column called 'Golden_Cross' and assign these values to it.
        Method 2:
            Googled: "pandas new column based on condition of other columns"
            Referenced: https://stackoverflow.com/questions/21702342/creating-a-new-column-based-on-if-elif-else-condition
            a. Use the example from StackOverflow to set the Golden_Cross column to be True if Short_Average >
                Long_Average and False if Short_Average <= Long_Average.
            b. When checking this, rows where Short_Average and/or Long_Average still have NaN.
            Googled: "fill na for one column pandas"
            Referenced: https://stackoverflow.com/questions/38134012/pandas-dataframe-fillna-only-some-columns-in-place
            c. Fill the NaN values in the Golden_Cross column with False.
    5. Finally, reset the indexes before returning the resulting dataframe.
    '''

    # step 1
    df = stockdf.copy()
    df = df[df['Firm']==firm]
    
    # step 2
    df_notna = df.dropna(subset=['Close'])
    
    # step 3
    short_window = (df_notna['Close'].rolling(short_average).sum())/short_average
    long_window = (df_notna['Close'].rolling(long_average).sum())/long_average
    
    df_notna = df_notna.assign(Short_Average = short_window.values)
    df_notna = df_notna.assign(Long_Average = long_window.values)

    
    # step 4, method 1
    golden_cross = df_notna['Short_Average']>df_notna['Long_Average']
    df_notna = df_notna.assign(Golden_Cross = golden_cross.values)
    
    # step 4, method 2
    # df_notna.loc[df_notna['Short_Average']>df_notna['Long_Average'], 'Golden_Cross'] = True
    # df_notna.loc[df_notna['Short_Average']<=df_notna['Long_Average'], 'Golden_Cross'] = False
    # df_notna = df_notna.fillna({'Golden_Cross': False})

    # step 5
    golden_cross_results = df_notna.copy()
    return golden_cross_results.reset_index(drop=True)



### Demo function call
golden_cross_demo_df = golden_cross_strategy(
    stockdf=annual_growth_df,
    firm='NFLX',
    short_average=3,
    long_average=7)
# Let's only look at the first 15 days for our input
display(golden_cross_demo_df.head(15))

### Test Cell - Exercise 4  

# Load testing utility
with open('resource/asnlib/publicdata/execute_tests', 'rb') as f:
    execute_tests = dill.load(f)

# Execute test
passed, test_case_vars = execute_tests(func=plugins.sqlite_blocker(golden_cross_strategy),
              ex_name='golden_cross_strategy',
              key=b'a9pptsbNJew33Kjoo3e2RkFk0FT2N1lXclu3U9zq8Aw=', 
              n_iter=100)
# Assign test case vars for debugging
input_vars, original_input_vars, returned_output_vars, true_output_vars = test_case_vars

assert passed, 'The solution to golden_cross_strategy did not pass the test.'

###
### AUTOGRADER TEST - DO NOT REMOVE
###
print('Passed! Please submit.')

### Run Me!!!
golden_crosses_df = utils.load_object_from_publicdata('golden_crosses_df')
golden_crosses_reduced_df = utils.load_object_from_publicdata('golden_crosses_reduced_df')

print('Properly Averaged Industry Growth:')

normalized_returns_demo_df_CORRECT = utils.load_object_from_publicdata('normalized_returns_demo_df.dill')
display(normalized_returns_demo_df_CORRECT)

### Solution - Exercise 5  
def normalize_stock_growths(daily_growth_by_firm: pd.DataFrame, firm: str) -> pd.DataFrame:
    # Create a list of every firm, except for the one we specified in the inputs
    non_chosen_firms = set(daily_growth_by_firm['Firm']) - set([firm])
    non_chosen_firms = list(non_chosen_firms)

    # Pivot the data for easy manipulation
    pivoted_data = daily_growth_by_firm.pivot(
        index='Date', 
        columns='Firm',
        values='Day_Growth'
        ).reset_index()

    # Calculate the total growth for every firm that isn't our firm
    pivoted_data[f'Non_{firm}_Average_Growth'] = pivoted_data[non_chosen_firms]\
        .sum(axis=1) / len(non_chosen_firms)
    
    return pivoted_data

### Demo function call
normalized_returns_demo_df = normalize_stock_growths(golden_crosses_reduced_df, 'NFLX')
display(normalized_returns_demo_df)

### Test Cell - Exercise 5  


print('Passed! Please submit.')

print('A proper summary of genres for the demo:')

netflix_original_genre_summary_df_CORRECT = utils.load_object_from_publicdata('netflix_original_genre_summary_df.dill')
display(netflix_original_genre_summary_df_CORRECT)

### Solution - Exercise 6  
def summarize_netflix_original_genres()-> str:
    
    '''
    Steps:
    1. Look at the database table in question: 'nflx_originals'. This helps us understand the structure of the
        table that we are working with.
    2. Look at the linked documentation for STRFTIME and test a small query with it to make sure it works.
    3. Add in the group by and count() statements.
        Count the number of Netflix original shows, grouped by year and by genre.
    4. Add in the order by statements.
        Order them by:
            genre_count (descending)
            year (ascending)
            genre (ascending)
    '''
    
    query_1 = 'SELECT * FROM nflx_originals LIMIT 10'
    
    query_2 = f'''SELECT premiere, STRFTIME('%Y', premiere) AS year FROM nflx_originals LIMIT 10'''
    
    query_3 = f'''
        SELECT STRFTIME('%Y', premiere) AS year, genre, COUNT(*) AS genre_count
        FROM nflx_originals
        GROUP BY year, genre
        LIMIT 10
    '''
    
    query_final = f'''
        SELECT STRFTIME('%Y', premiere) AS year, genre, COUNT(*) AS genre_count
        FROM nflx_originals
        GROUP BY year, genre
        ORDER BY genre_count DESC, year, genre
        LIMIT(10)
    '''
    
    return query_final

### Demo function call
summary_query = summarize_netflix_original_genres()
netflix_original_genres = pd.read_sql(summary_query, conn)
display(netflix_original_genres)

### Test Cell - Exercise 6  

# Load testing utility
with open('resource/asnlib/publicdata/execute_tests', 'rb') as f:
    execute_tests = dill.load(f)

# Execute test
passed, test_case_vars = execute_tests(func=plugins.sql_executor(summarize_netflix_original_genres),
              ex_name='summarize_netflix_original_genres',
              key=b'a9pptsbNJew33Kjoo3e2RkFk0FT2N1lXclu3U9zq8Aw=', 
              n_iter=100)
# Assign test case vars for debugging
input_vars, original_input_vars, returned_output_vars, true_output_vars = test_case_vars

assert passed, 'The solution to summarize_netflix_original_genres did not pass the test.'

###
### AUTOGRADER TEST - DO NOT REMOVE
###
print('Passed! Please submit.')

print('A proper summary of show score metrics:')

netflix_top_10_scores_df_CORRECT = utils.load_object_from_publicdata('netflix_top_10_scores_df.dill')
display(netflix_top_10_scores_df_CORRECT)

### Solution - Exercise 7  
def calc_netflix_top10_scores() -> str:
    
    '''
    Steps:
    1. Examine the nflx_top table.
    2. Calculate scores for each title.
        Note: I did this very inefficiently. The better way to do this (without a CASE WHEN statement) is:
            (11 - rank) AS scores
    3. Get the sum and average of all of the scores by title.
    4. Get the number of times a show appeared in the top 10.
        Note: If using the CASE WHEN method, we need to use COUNT(scores>0) to count the number of times that
            scores is greater than 0. If we use the 11-rank way, we would just need to COUNT(scores)
    5. Add in the order by statements:
        total_score in descending order
        title in descending order
        
    Note: I include LIMIT 10 from the beginning to avoid excess printing, since the final query will be limited to 10.
    '''
    
    
    query_1 = 'SELECT * FROM nflx_top LIMIT 10'
    
    query_2 = f'''
        SELECT
            title,
            CASE 
                WHEN rank=1 THEN 10
                WHEN rank=2 THEN 9
                WHEN rank=3 THEN 8
                WHEN rank=4 THEN 7
                WHEN rank=5 THEN 6
                WHEN rank=6 THEN 5
                WHEN rank=7 THEN 4
                WHEN rank=8 THEN 3
                WHEN rank=9 THEN 2
                WHEN rank=10 THEN 1
                ELSE 0
            END AS scores

        FROM nflx_top
    '''
    
    query_3 = f'''
        SELECT title, SUM(scores) AS total_score, AVG(scores) AS avg_score
        FROM (
            SELECT
                title,
                CASE 
                    WHEN rank=1 THEN 10
                    WHEN rank=2 THEN 9
                    WHEN rank=3 THEN 8
                    WHEN rank=4 THEN 7
                    WHEN rank=5 THEN 6
                    WHEN rank=6 THEN 5
                    WHEN rank=7 THEN 4
                    WHEN rank=8 THEN 3
                    WHEN rank=9 THEN 2
                    WHEN rank=10 THEN 1
                    ELSE 0
                END AS scores

            FROM nflx_top
        )
        GROUP BY title
        LIMIT 10
    '''
    
    query_4 = f'''
        SELECT title, SUM(scores) AS total_score, COUNT(scores>0) AS occurrence, AVG(scores) AS avg_score
        FROM (
            SELECT
                title,
                CASE 
                    WHEN rank=1 THEN 10
                    WHEN rank=2 THEN 9
                    WHEN rank=3 THEN 8
                    WHEN rank=4 THEN 7
                    WHEN rank=5 THEN 6
                    WHEN rank=6 THEN 5
                    WHEN rank=7 THEN 4
                    WHEN rank=8 THEN 3
                    WHEN rank=9 THEN 2
                    WHEN rank=10 THEN 1
                    ELSE 0
                END AS scores

            FROM nflx_top
        )
        GROUP BY title
        LIMIT 10
    '''
    
    
    
    query_final = f'''
        
        SELECT title, SUM(scores) AS total_score, COUNT(scores>0) AS occurrence, AVG(scores) AS avg_score
        FROM (
            SELECT
                title,
                CASE 
                    WHEN rank=1 THEN 10
                    WHEN rank=2 THEN 9
                    WHEN rank=3 THEN 8
                    WHEN rank=4 THEN 7
                    WHEN rank=5 THEN 6
                    WHEN rank=6 THEN 5
                    WHEN rank=7 THEN 4
                    WHEN rank=8 THEN 3
                    WHEN rank=9 THEN 2
                    WHEN rank=10 THEN 1
                    ELSE 0
                END AS scores

            FROM nflx_top
        )
        GROUP BY title
        ORDER BY total_score DESC, title DESC
        LIMIT 10
    '''
    
    return query_final

### Demo function call
scores_query = calc_netflix_top10_scores()
netflix_top10_scores = pd.read_sql(scores_query, conn)
display(netflix_top10_scores)

### Test Cell - Exercise 7  

# Load testing utility
with open('resource/asnlib/publicdata/execute_tests', 'rb') as f:
    execute_tests = dill.load(f)

# Execute test
passed, test_case_vars = execute_tests(func=plugins.sql_executor(calc_netflix_top10_scores),
              ex_name='calc_netflix_top10_scores',
              key=b'a9pptsbNJew33Kjoo3e2RkFk0FT2N1lXclu3U9zq8Aw=', 
              n_iter=100)
# Assign test case vars for debugging
input_vars, original_input_vars, returned_output_vars, true_output_vars = test_case_vars

assert passed, 'The solution to calc_netflix_top10_scores did not pass the test.'

###
### AUTOGRADER TEST - DO NOT REMOVE
###
print('Passed! Please submit.')

longterm_avg_vikings_breakout_df_CORRECT = utils.load_object_from_publicdata('longterm_avg_vikings_breakout_df.dill')
display(longterm_avg_vikings_breakout_df_CORRECT)

longterm_avg_scores_df = utils.load_object_from_publicdata('longterm_avg_scores_df.dill')
display(longterm_avg_scores_df)

# NOTE: This is my original query before the recommended approach was added.

query_final = f'''
    WITH total_score_calc AS (
        SELECT nflx_originals.title, SUM(scores) AS total_score, nflx_release_date
        FROM (
            SELECT
                title,
                (11 - rank) AS scores,
                nflx_release_date
            FROM nflx_top
        ) AS top_scores
        JOIN nflx_originals ON nflx_originals.title = top_scores.title
        GROUP BY nflx_originals.title, nflx_release_date
    ),
    poss_days AS (
        SELECT 
            premiere, 
            COUNT(DISTINCT date) AS num_poss
        FROM nflx_top
        CROSS JOIN nflx_originals
        WHERE date >= premiere
        GROUP BY premiere
        ORDER BY premiere DESC
    )
    SELECT title, total_score, num_poss AS date_count, 
        CAST(total_score AS float) / CAST(num_poss AS float) AS longterm_avg_score
    FROM total_score_calc 
    JOIN poss_days ON premiere = nflx_release_date
    ORDER BY longterm_avg_score DESC, title ASC

    LIMIT 10
'''

### Solution - Exercise 8  
def longterm_avg_score():
    
    '''
    Steps:
    1. Look at the two tables that we will be querying: nflx_originals and nflx_top.
    2. Obtain <distinct_dates>: Identify all distinct date values in nflx_top.
    3. Obtain <title_dates>: For each title in nflx_originals, identify any dates in <distinct_dates> which are 
        greater than or equal to the title's premiere.
    4. Obtain <title_date_score>: For each title/date pair in <title_dates>, identify any records from nflx_top 
        which have the same title/date pair.
        a. Use the rank field from nflx_top to calculate a given score.
        b. Any title/date pairs which are in <title_date_score> but not in nflx_top should not be dropped and should 
            have a NULL (score) value in <title_date_score>.
    5. Calculate the final result.
        a. For each title in <title_date_score>, calculate:
            total_score - compute the sum of all the score values sharing a common title.
            date_count - compute the count of all date values sharing a common title.
            longterm_avg_score - compute the quotient of the total_score over date_count.
        b. Filter out all records with longterm_avg_score that are NULL.
        c. Finally, the results should be ordered by:
            longterm_avg_score in descending order
            The show's title in ascending order
        d. Return the top 10 rows
    '''
    
    query_1a = 'SELECT * FROM nflx_top LIMIT 10'
    query_1b = 'SELECT * FROM nflx_originals LIMIT 10'
    
    query_2 = f''' SELECT DISTINCT date FROM nflx_top '''
    
    query_3 = f'''
        WITH distinct_dates AS (
            SELECT DISTINCT date FROM nflx_top
        )
        SELECT
            title,
            premiere,
            date
        FROM nflx_originals
        CROSS JOIN distinct_dates
        WHERE premiere <= date
        LIMIT 10
    '''

    query_4 = f'''
        WITH title_dates AS (
            SELECT
                title,
                date
            FROM nflx_originals
            CROSS JOIN (SELECT DISTINCT date FROM nflx_top)
            WHERE premiere <= date
        )
        SELECT 
            title_dates.title,
            title_dates.date,
            (11 - rank) AS scores
        FROM title_dates
        LEFT JOIN nflx_top ON title_dates.title = nflx_top.title AND title_dates.date = nflx_top.date
    '''
    
    query_5a = f'''
        WITH title_date_score AS (
            SELECT 
                title_dates.title,
                title_dates.date,
                (11 - rank) AS scores
            FROM (
                SELECT
                    title,
                    date
                FROM nflx_originals
                CROSS JOIN (SELECT DISTINCT date FROM nflx_top)
                WHERE premiere <= date
            ) title_dates
            LEFT JOIN nflx_top ON title_dates.title = nflx_top.title AND title_dates.date = nflx_top.date
        )
        
        SELECT
            title,
            SUM(scores) AS total_score,
            COUNT(date) AS date_count,
            CAST(SUM(scores) AS DECIMAL)/COUNT(date) AS longterm_avg_score
        FROM title_date_score
        GROUP BY title
    '''
    
    query_5b = f'''
        WITH title_date_score AS (
            SELECT 
                title_dates.title,
                title_dates.date,
                (11 - rank) AS scores
            FROM (
                SELECT
                    title,
                    date
                FROM nflx_originals
                CROSS JOIN (SELECT DISTINCT date FROM nflx_top)
                WHERE premiere <= date
            ) title_dates
            LEFT JOIN nflx_top ON title_dates.title = nflx_top.title AND title_dates.date = nflx_top.date
        )
        
        SELECT
            title,
            SUM(scores) AS total_score,
            COUNT(date) AS date_count,
            CAST(SUM(scores) AS DECIMAL)/COUNT(date) AS longterm_avg_score
        FROM title_date_score
        GROUP BY title
        HAVING longterm_avg_score IS NOT NULL
    '''
    
    
    query_5c = f'''
        WITH title_date_score AS (
            SELECT 
                title_dates.title,
                title_dates.date,
                (11 - rank) AS scores
            FROM (
                SELECT
                    title,
                    date
                FROM nflx_originals
                CROSS JOIN (SELECT DISTINCT date FROM nflx_top)
                WHERE premiere <= date
            ) title_dates
            LEFT JOIN nflx_top ON title_dates.title = nflx_top.title AND title_dates.date = nflx_top.date
        )
        
        SELECT
            title,
            SUM(scores) AS total_score,
            COUNT(date) AS date_count,
            CAST(SUM(scores) AS float)/COUNT(date) AS longterm_avg_score
        FROM title_date_score
        GROUP BY title
        HAVING longterm_avg_score IS NOT NULL
        ORDER BY longterm_avg_score DESC, title ASC
        LIMIT 10
    '''
    
    return query_5c

### Demo function call
query = longterm_avg_score()
longterm_avg_scores = pd.read_sql(query, conn)
display(longterm_avg_scores)

### Test Cell - Exercise 8  

# Load testing utility
with open('resource/asnlib/publicdata/execute_tests', 'rb') as f:
    execute_tests = dill.load(f)

# Execute test
passed, test_case_vars = execute_tests(func=plugins.sql_executor(longterm_avg_score),
              ex_name='longterm_avg_score',
              key=b'a9pptsbNJew33Kjoo3e2RkFk0FT2N1lXclu3U9zq8Aw=', 
              n_iter=100)
# Assign test case vars for debugging
input_vars, original_input_vars, returned_output_vars, true_output_vars = test_case_vars

assert passed, 'The solution to longterm_avg_score did not pass the test.'

###
### AUTOGRADER TEST - DO NOT REMOVE
###
print('Passed! Please submit.')

### Run Me!!!
nflx_scores_demo_df = utils.load_object_from_publicdata('nflx_scores_demo_df')
normalized_growths_df = utils.load_object_from_publicdata('normalized_growths_df')

print('A proper model matrix for the demo:')

model_matrix_demo_df_CORRECT = utils.load_object_from_publicdata('model_matrix_demo_df.dill')
display(model_matrix_demo_df_CORRECT)

### Solution - Exercise 9  
def construct_model_inputs(normalized_growths_df: pd.DataFrame, show_scores_df: pd.DataFrame) -> pd.DataFrame:
    
    '''
    Steps:
    1. Examine the two input dataframes and read the linked documentation for pivot.
    2. Pivot the show_scores_df as described above.
        a. Each show is represented as its own column. The index should reflect the dates. The values should be set 
            to the show's score.
                columns: shows (title)
                index: dates
                values: score
        b. Fill in the NaN values with 0s.
        c. Reset the index of the pivoted dataframe keeping the index as a column.
    3. Create the dataframe "model_matrix".
        a. Keep only the Date and Relative Growth values from the normalized_growths_df dataframe.
        b. Join the pivoted dataframe with normalized_growths_df on date.
        b. Delete the unnecessary columns of date and Date
    4. Return the final dataframe.    
    '''
    
    # step 2
    pivoted_df = show_scores_df.pivot(index='date', columns='title', values='score')
    pivoted_df.fillna(0, inplace=True)
    pivoted_df = pivoted_df.reset_index(drop=False)
    
    # step 3    
    normalized_small_df = normalized_growths_df[['Date', 'Relative_Growth']]
    model_matrix = pd.merge(normalized_small_df, pivoted_df, left_on = 'Date', right_on='date')
    
    del model_matrix['date']
    del model_matrix['Date']
    
    # step 4
    return model_matrix

### Demo function call
model_matrix = construct_model_inputs(normalized_growths_df, nflx_scores_demo_df)
display(model_matrix)

### Test Cell - Exercise 9  

# Load testing utility
with open('resource/asnlib/publicdata/execute_tests', 'rb') as f:
    execute_tests = dill.load(f)

# Execute test
passed, test_case_vars = execute_tests(func=construct_model_inputs,
              ex_name='construct_model_inputs',
              key=b'a9pptsbNJew33Kjoo3e2RkFk0FT2N1lXclu3U9zq8Aw=', 
              n_iter=100)
# Assign test case vars for debugging
input_vars, original_input_vars, returned_output_vars, true_output_vars = test_case_vars

assert passed, 'The solution to construct_model_inputs did not pass the test.'

###
### AUTOGRADER TEST - DO NOT REMOVE
###
print('Passed! Please submit.')