import sqlite3
import pandas as pd
import plotly.express as px

#establish a connection
conn = sqlite3.connect("temps.db")

#read in the csv
df1 = pd.read_csv("temps_stacked.csv")

df2 = pd.read_csv("countries.csv")
#recode the columns to get rid of space
df2 = df2.rename(columns= {"FIPS 10-4": "code"})
df3 = pd.read_csv("station-metadata.csv")

df1.head()

df2.head()

df3.head()

#convert to database
df1.to_sql("temperatures", conn, if_exists="replace", index=False)

df2.to_sql("countries", conn, if_exists="replace", index=False)

df3.to_sql("stations", conn, if_exists="replace", index=False)

#close the connection
conn.close()

def query_climate_database(country, year_begin, year_end, month):
    """
    A function that takes four arguments and returns a Pandas dataframe by making a query
    """
    conn = sqlite3.connect("temps.db")
    
    #command line that gets the relevant info    
    cmd = f"SELECT S.name, S.latitude, S.longitude, C.Name, T.year, T.month, T.temp \
            FROM temperatures T \
            LEFT JOIN stations S ON T.id = S.id\
            LEFT JOIN countries C ON C.code = SUBSTRING(T.id, 1, 2)\
            WHERE C.Name='{country}' AND T.year >= {year_begin} \
            AND T.year<={year_end} AND T.month = {month}"
    
    #converts to Pandas dataframe
    df = pd.read_sql_query(cmd, conn)
    conn.close()
    return df

query_climate_database(country = "India", 
                       year_begin = 1980, 
                       year_end = 2020,
                       month = 1)

from sklearn.linear_model import LinearRegression

def coef(data_group):
    """
    A function that returns the slope of the best-fit linear regression line
    """
    X = data_group[["Year"]]
    y = data_group["Temp"]
    LR = LinearRegression()
    LR.fit(X, y)
    slope = LR.coef_[0]
    return slope

def temperature_coefficient_plot(country, year_begin, year_end, month, min_obs, **kwargs):
    """
    This function takes in country, year_begin, year_end, and month, as the previous sql function does. It also
    takes in a minimum observation argument, which dictates the minimum amount of observation for any station's 
    data to be accepted. Anything less will be filtered out. It also accepts additional keywords arguments to 
    style the plot. 
    """
    #call the query function to get the dataframe
    df = query_climate_database(country, year_begin, year_end, month)
    
    #filters out any station not meeting the min_obs requirement
    df['len'] = df.groupby(["NAME"])["Temp"].transform(len)
    #print(df.head())
    df = df[df['len'] >= min_obs]
    
    #adds esitmated yearly increase column using our coef function
    coefs = df.groupby(["NAME", "LATITUDE", "LONGITUDE"]).apply(coef)
    coefs = coefs.reset_index()
    #print(coefs.head())
    coefs.rename(columns = {0:'Estimated Yearly Increase (°C)'}, inplace = True)
    
    #plots the data
    fig = px.scatter_mapbox(coefs, # data for the points you want to plot
                        lat = "LATITUDE", # column name for latitude informataion
                        lon = "LONGITUDE", # column name for longitude information
                        hover_name = "NAME", # what's the bold text that appears when you hover over
                        color = "Estimated Yearly Increase (°C)",
                        **kwargs) # map style
    return fig

from plotly.io import write_html
color_map = px.colors.diverging.RdGy_r # choose a colormap

fig = temperature_coefficient_plot("India", 1980, 2020, 1, 
                                   min_obs = 10,
                                   zoom = 2,
                                   mapbox_style="carto-positron",
                                   color_continuous_scale=color_map,
                                   title = "Estimates of Yearly Increase in Temperature in January for Stations in India, years 1980 - 2020",
                                   color_continuous_midpoint = 0,
                                   width = 1000,
                                   height = 600
                                  )

fig.show()
#saves our plot
write_html(fig, "India.html")

color_map = px.colors.diverging.RdGy_r # choose a colormap

fig = temperature_coefficient_plot("China", 1980, 2020, 1, 
                                   min_obs = 10,
                                   zoom = 2,
                                   mapbox_style="carto-positron",
                                   color_continuous_scale=color_map,
                                   title = "Estimates of Yearly Increase in Temperature in January for Stations in China, years 1980 - 2020",
                                   color_continuous_midpoint = 0,
                                   width = 1000,
                                   height = 600
                                  )

fig.show()
#saves our plot
write_html(fig, "China.html")

def query_climate_database_2(year_begin, year_end, lat):
    """
    A function that takes three arguments and returns a Pandas dataframe by making a query
    """
    conn = sqlite3.connect("temps.db")
    
    #command line that gets the relevant info    
    cmd = f"SELECT S.name, T.year, T.month, T.temp \
            FROM temperatures T \
            LEFT JOIN stations S ON T.id = S.id\
            WHERE T.year >= {year_begin} \
            AND T.year<={year_end} AND S.latitude < {lat}"
    
    #converts to Pandas dataframe
    df = pd.read_sql_query(cmd, conn)
    conn.close()
    return df

def plot_antartica(year_begin, year_end, lat, **kwargs):
    #use the same coefs function to determine the avearge yearly change
    df = query_climate_database_2(year_begin, year_end, lat)
    coefs = df.groupby(["NAME", "Month"]).apply(coef)
    #coefs.rename(columns = {0:'Estimated Yearly Increase (°C)'}, inplace = True)
    coefs = coefs.reset_index()

    #plot the data points
    fig = px.scatter(data_frame = coefs, 
                x = 0, 
                y = "NAME",
                **kwargs
          )
    return fig

from plotly.io import write_html
#call our function with predetermined parameters
fig = plot_antartica(1980, 2020, -80, 
                     title = "Average Yearly Change in Temperature\nat Antarctic Climate Stations\n(Each dot is one month of the year)",
                     labels={
                     "NAME": "STATION NAME",
                     "0": "Average Yearly Change in Temperature",
                 },
                    )
fig.show()

#saves our plot
write_html(fig, "antartica.html")

#First, let's define a new function to select the data.
def query_climate_database_month(country, year_begin, year_end, month1, month2, month3):
    """
    A function that takes four arguments and returns a Pandas dataframe by making a query
    """
    conn = sqlite3.connect("temps.db")
    
    #command line that gets the relevant info    
    cmd = f"SELECT S.name, S.latitude, S.longitude, C.Name, T.year, T.month, T.temp \
            FROM temperatures T \
            LEFT JOIN stations S ON T.id = S.id\
            LEFT JOIN countries C ON C.code = SUBSTRING(T.id, 1, 2)\
            WHERE C.Name='{country}' AND T.year >= {year_begin} \
            AND T.year<={year_end} AND T.month = {month1} OR T.month = {month2} OR T.month = {month3} "
    
    #converts to Pandas dataframe
    df = pd.read_sql_query(cmd, conn)
    conn.close()
    return df

def temperature_coefficient_plot_month(country, year_begin, year_end, month1, month2, month3, min_obs, **kwargs):
    """
    This function takes in countries, year_begin, year_end, and month, as the previous sql function does. It also
    takes in a minimum observation argument, which dictates the minimum amount of observation for any station's 
    data to be accepted. Anything less will be filtered out. It also accepts additional keywords arguments to 
    style the plot. 
    """
    #call the query function to get the dataframe
    df = query_climate_database_month(country, year_begin, year_end, month1, month2, month3)
    
    #filters out any station not meeting the min_obs requirement
    df['len'] = df.groupby(["NAME", "Month"])["Temp"].transform(len)
    #print(df.head())
    df = df[df['len'] >= min_obs]
    
    #adds esitmated yearly increase column using our coef function
    coefs = df.groupby(["NAME", "LATITUDE", "LONGITUDE", "Month"]).apply(coef)
    coefs = coefs.reset_index()
    #print(coefs.head())
    coefs.rename(columns = {0:'Estimated Yearly Increase (°C)', "Name":"Country"}, inplace = True)
    
    #plots the data
    fig = px.box(data_frame=coefs, # data for the points you want to plot
                        y = "Estimated Yearly Increase (°C)", # column name for longitude information
                        facet_col = "Month",
                        **kwargs) # map style
    return fig

#call our function
from plotly.io import write_html
fig = temperature_coefficient_plot_month("India", 1990, 2020, 1, 6, 9, 10, 
                                   title = "Estimates of Yearly Increase in Temperature in January, June, and September for Stations in India, years 1990 - 2020",
                                   width = 1000,
                                   height = 600)

fig.show()
write_html(fig, "month.html")