import sqlite3
import pandas as pd
import numpy as np
from plotly import express as px
from sklearn.linear_model import LinearRegression
import calendar
conn = sqlite3.connect("temps.db") 

countries = pd.read_csv("https://raw.githubusercontent.com/mysociety/gaze/master/data/fips-10-4-to-iso-country-codes.csv")
# new table: countries
countries = countries.rename(columns = {"FIPS 10-4"  : "FIPS"})
countries.to_sql("countries", conn, if_exists = "replace", index = False)
countries.head()

url = "https://raw.githubusercontent.com/PhilChodrow/PIC16B/master/datasets/noaa-ghcn/station-metadata.csv"
stations = pd.read_csv(url)
# new table stations
stations.to_sql("stations", conn, if_exists = "replace", index = False)
stations.head()

def prepare_df(df):
    df["FIPS"] = df["ID"].str[0:2] #Creates a new column corresponding to the station's country's code
    df = df.set_index(keys=["ID", "Year", "FIPS"]) #Sets our indexing
    df = df.stack() #Creates a multi-level index dependent on the above
    df = df.reset_index() #Reverts to normal column indexing in the same order as above
    df = df.rename(columns = {"level_3"  : "Month" , 0 : "Temp"}) #Renames column appropriately
    df["Month"] = df["Month"].str[5:].astype(int) #Change the month column to reflect the integer value of the month
    df["Temp"]  = df["Temp"] / 100 #Converts from a hundreths of a Celsius to whole Celsius
    return(df) #Returns our dataframe

df_iter = pd.read_csv("temps.csv", chunksize = 100000)
for df in df_iter:
    df = prepare_df(df)
    df.to_sql("temperatures", conn, if_exists = "append", index = False)

df.head()

cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
print(cursor.fetchall())
#fetchall() returns a list containing all the items returned

def query_climate_database(country, year_begin, year_end, month):
    conn = sqlite3.connect("temps.db") #Connects to our database
    cmd = \
    """
    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 T.fips = C.fips
    WHERE C.name = ? AND T.year >= ? AND T.year <= ? AND T.month = ?
    """
    param = (country, year_begin, year_end, month,) 
    df = pd.read_sql_query(cmd, conn, params = param)
    conn.close() #we must close connection to database to prevent data corruption
    
    return df

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

# this coef function computes the first coefficient of a linear regression model at each station
# the result helps to determine the color of the point
# the color reflects an estimate of the yearly change in temperature during the specified month and time period at that station
def coef(data_group):
    x = data_group[["Year"]]
    y = data_group["Temp"]  
    LR = LinearRegression()
    LR.fit(x, y)
    return LR.coef_[0]

def temperature_coefficient_plot(country, year_begin, year_end, month, min_obs, **kwargs):
    df = query_climate_database(country, year_begin, year_end, month)
        
    count_year = df.groupby(["NAME"])["Year"].transform(len)
    df = df[count_year >= min_obs]

    coefs = df.groupby(["NAME", "LATITUDE", "LONGITUDE"]).apply(coef)
    coefs = coefs.reset_index()
    coefs = coefs.rename(columns = {0 : "Estimated Yearly Increase (°C)"})
    coefs["Estimated Yearly Increase (°C)"] = coefs["Estimated Yearly Increase (°C)"].round(4)
    

    
    fig = px.scatter_mapbox(coefs, 
                            lat = "LATITUDE", 
                            lon = "LONGITUDE", 
                            hover_name = "NAME",
                            hover_data = ["Estimated Yearly Increase (°C)"],
                            color = "Estimated Yearly Increase (°C)",
                            title="Estimates of yearly temperature increases in " + calendar.month_name[month] + f" for stations in {country}, years {year_begin}-{year_end}",
                            **kwargs)
    return fig

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)

fig.show()

count_year = df.groupby(["NAME"])["Year"].transform(len)
df = df[count_year >= min_obs]

coefs = df.groupby(["NAME", "LATITUDE", "LONGITUDE"]).apply(coef)
coefs = coefs.reset_index()
coefs = coefs.rename(columns = {0 : "Estimated Yearly Increase (°C)"})
 coefs["Estimated Yearly Increase (°C)"] = coefs["Estimated Yearly Increase (°C)"].round(5)

fig = px.scatter_mapbox(coefs, 
                        lat = "LATITUDE", 
                        lon = "LONGITUDE", 
                        hover_name = "NAME",
                        hover_data = ["Estimated Yearly Increase (°C)"],
                        color = "Estimated Yearly Increase (°C)",
                        title="Estimates of yearly temperature increases in " + calendar.month_name[month] + f" for stations in {country}, years {year_begin}-{year_end}",
                        **kwargs)

def temperature_changes_country(country,begin_month,end_month,begin_year,end_year):
    conn = sqlite3.connect("temps.db") #Connects to our database
    cmd = \
    """
    SELECT 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 T.fips = C.fips
    WHERE C.name = ? AND  T.month >= ? AND T.month <= ? AND T.year >= ? AND T.year <= ? 
    """
    param = (country,begin_month,end_month,begin_year,end_year) 
    df = pd.read_sql_query(cmd, conn, params = param)
    df = df.groupby(["Year"])["Temp"].aggregate([np.median])
    df = df.reset_index()
    conn.close() #we must close connection to database to prevent data corruption
    fig = px.line(df, 
                     x = "Year", 
                     y = "median", 
                     labels = {"Temp" : "Median Temperatures(in given periods, years, and country)"}, 
                     title = f"Median Temperatures(C) in {country} between" + 
                             calendar.month_name[begin_month] + " and " + calendar.month_name[end_month] + 
                             ", " + str(begin_year) + "-" + str(end_year))
    
    return fig

fig = temperature_changes_country(country = "India",
                                  begin_month = 1,
                                  end_month = 12,
                                  begin_year = 1990,
                                  end_year = 2010)
fig.show()

def temperatures_variation(country,begin_month,end_month,begin_year,end_year):
    conn = sqlite3.connect("temps.db") #Connects to our database
    cmd = \
    """
    SELECT C.name, T.year, T.month, T.temp, S.latitude, S.longitude
    FROM temperatures T
    LEFT JOIN stations S ON T.id = S.id
    LEFT JOIN countries C on T.fips = C.fips
    WHERE C.name = ? AND  T.month >= ? AND T.month <= ? AND T.year >= ? AND T.year <= ? 
    """
    para = (country,begin_month,end_month,begin_year,end_year) 
    df = pd.read_sql_query(cmd, conn, params = para)
    df = df.groupby(["Year","LATITUDE","LONGITUDE"])["Temp"].aggregate([np.var])
    df = df.reset_index()
    conn.close()
    
    fig = px.scatter_mapbox(df, 
                            lat = "LATITUDE", 
                            lon = "LONGITUDE", 
                            hover_name = "Year",
                            hover_data = ["var"],
                            color = "var",
                            zoom = 1,
                            height = 300,
                            mapbox_style="carto-positron",
                            opacity = 0.2)
#grey data points are na values
    fig.update_layout(margin={"r":0,"t":0,"l":0,"b":0})
    return fig

fig = temperatures_variation("India", 2, 3, 1990, 1995)
fig.show()