import pandas as pd
import sqlite3
import numpy as np
from plotly import express as px
from sklearn.linear_model import LinearRegression
import seaborn as sns

conn = sqlite3.connect("hw1_database.db")  # create a database in current directory called "hw1_database.db"

df_iter = pd.read_csv("temps.csv", chunksize =10000)

df = df_iter.__next__()  

df

def prepare_temps_df(df):
    """
    This function will clean the `temps.csv` data 
    before incorporating this data into my database
    
    Parameters
    ----------
    df: datafram; temps.csv
    
    Return
    ----------
    No return value
    """
    df = df.set_index(keys=["ID","Year"])
    df = df.stack()
    df = df.reset_index()
    df = df.rename(columns = {"level_2": "Month", 0 : "Temp"})
    df["Month"] = df["Month"].str[5:].astype(int)  # Value1 into 1 as month
    df["FIPS"] = df["ID"].str[0:2]
    df["Temp"]=df["Temp"]/100                      #change to degree C
    return (df)

df = prepare_temps_df(df)
df.head()

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

stations = pd.read_csv("station-metadata.csv")
stations.head()

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

countries = pd.read_csv("countries.csv")
countries["FIPS"]=countries["FIPS 10-4"]  # I will use 'FIPS' instead of 'FIPS 10-4' for convenience
countries.head()

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

cursor = conn.cursor()

cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
print(cursor.fetchall())

cursor.execute("SELECT sql FROM sqlite_master WHERE type='table';")
for result in cursor.fetchall():
    print(result[0])

conn.close()  # close database

def query_climate_database (country, year_begin, year_end, month):
    """
    This function will create a dataframe from a user-specified country,
    with associated station name, the station's latitude, 
    the station's longitude, the year in which the reading was taken,
    and the month in which the reading was taken.
    
    Parameters
    ----------
    country: string; giving the name of a country for which data should be returned
    year_begin: int;  giving the earliest year for which should be returned
    year_end: int;  giving the latest year for which should be returned
    month: int;  giving the month of the year for which should be returned
    
    Return
    ----------
    a Pandas dataframe of temperature readings for the specified country,
    in the specified date range, in the specified month of the year.
    """
    
    where_condition = "WHERE year>=" + str(year_begin) + " AND " + "year<=" + str(year_end)+ " AND month=" +str(month)+ " AND C.name ='" + country+ "'"  
    
    cmd =\
    """
    SELECT  S.name, S.latitude, S.longitude, C.name, T.year, T.month, T.temp
    FROM temperatures T
    JOIN stations S ON T.id = S.id
    JOIN countries C ON T.fips = C.fips
    """
    cmd += where_condition 
      
    return pd.read_sql_query(cmd, conn)

conn = sqlite3.connect("hw1_database.db")               # reopen the connection 
query_climate_database(country="India", year_begin=1980, year_end=2020, month=1)

conn.close()  # close database

def coef(data_group):
    """
    This function computes the first coefficient of a linear regression model
    at the user-specified station
    
    Parameters
    ----------
    data_group: datafram; giving the name of a country for which data should be returned

    Return
    ----------
    An array of the first coefficient
    """
    
    x = data_group[["Year"]] # x is df
    y = data_group["Temp"]   # y is a series
    LR = LinearRegression()
    LR.fit(x, y)
    return LR.coef_[0]
    

def temperature_coefficient_plot(country, year_begin, year_end, month, min_obs, **kwargs):
    """
    This function will create a Geographic Scatter plot for yearly temperature increases
    from the user-specified country and between a specified year range with a specified 
    month. The year should have more than a user-specified minimum required the number 
    of years.
    
    Parameters
    ----------
    country: string; giving the name of a country for which data should be returned
    year_begin: int;  giving the earliest year for which should be returned
    year_end: int;  giving the latest year for which should be returned
    month: int;  giving the month of the year for which should be returned
    min_obs: int; the minimum required number of years of data for any given station
    **kwargs: additional keyword arguments passed to px.scatter_mapbox()
    
    Return
    ----------
    a Pandas dataframe of the average yearly change in temperature vary within a given country
    """

    
    month_mapper ={1 : "January",  2 : "February",  3 : "March",
                   4 : "April",    5 : "May",       6 : "Jun",
                   7 : "July",     8 : "August",    9 : "September",
                  10 : "October", 11 : "November", 12 : "December" }
    
    # retrieve the data from function query_climate_database()
    df_1 = query_climate_database(country=country, year_begin=year_begin, year_end=year_end, month=month)
    
    df_2 = df_1.groupby(["NAME","LATITUDE","LONGITUDE","Month"]).apply(coef)
    
    df_2 = df_2.reset_index()

    df_2["Estimated Yearly Increase (\N{DEGREE SIGN}C)"] = df_2[0].round(4) # round estimates
    
    df_2 = df_2.drop([0], axis =1)

    # make sure we have the minimum required number of years of data for any given station.
    if (len(df_1["Year"].unique()) >=  min_obs):
        fig = px.scatter_mapbox(df_2,
                                lat = "LATITUDE",
                                lon = "LONGITUDE",
                                hover_name="NAME",
                                height = 300,
                                color ="Estimated Yearly Increase (\N{DEGREE SIGN}C)",
                                color_continuous_midpoint = 0,  # make colorbar is centered at 0
                                **kwargs)
        
        title_name = "Estimates of yearly increase in temperature in " + month_mapper[1]  + "<br>for stations in " + country + ", years " + str(year_begin) + "-" +str(year_end)
        fig.update_layout(margin={"r":0,"t":50,"l":10,"b":0},
                          title = title_name)
        fig.layout.coloraxis.colorbar.title ="Estimated Yearly <br>Increase (\N{DEGREE SIGN}C)"
      
        return fig
    
    else:
        print("We do not have enough " + str(min_obs) + " years of data")
        

conn = sqlite3.connect("hw1_database.db")  # connect the database

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

fig = temperature_coefficient_plot("India", 1980, 2020, 1, 
                                   min_obs = 10,
                                   zoom = 3,
                                   mapbox_style="carto-positron",
                                   color_continuous_scale=color_map)
fig.show()

conn.close()  # close the database

def query_function_4_1 (country, year_begin, year_end):
    """
    This function will extracting data and create a datafram
    from user specified country, with associated station name,
    latitude of the station, longitude of the station, year in which the reading was taken,
    and month in which the reading was taken.
    
    Parameters
    ----------
    country: string; giving the name of a country for which data should be returned
    year_begin: int;  giving the earliest year for which should be returned
    year_end: int;  giving the latest year for which should be returned
    
    Return
    ----------
    a Pandas dataframe of the user specified country with associated informations
    """
    
 
    where_condition = "WHERE year>=" + str(year_begin) + " AND " + "year<=" + str(year_end)+ " AND C.name ='" + country+ "'" 
    
    cmd =\
    """
    SELECT S.name, S.latitude, S.longitude, C.name, T.year, T.month, T.temp
    FROM temperatures T
    JOIN stations S ON T.id = S.id
    JOIN countries C ON T.fips = C.fips
    """

    cmd += where_condition 
    
    return pd.read_sql_query(cmd, conn)

def plot4_1 (country, year_begin, year_end, starting_letter):
    """
    This function make scatterplot of the station name starting with "G" 
    in the user specified country within a giving range of years
    
    Parameters
    ----------
    country: string; giving the name of a country for which data should be returned
    year_begin: int;  giving the earliest year for which should be returned
    year_end: int;  giving the latest year for which should be returned
    starting_letter: char; Stations name which starting with starting_letter.
    
    Return
    ----------
    No return value
    
    """
    
    # extracting data
    df_4 = query_function_4_1(country=country, year_begin=year_begin, year_end=year_end)
    # station name starting with starting_letter 
    begins_with = df_4[df_4["NAME"].str[0]== starting_letter]
    
    begins_with = begins_with.rename(columns ={"Temp" : "Temperatures (\N{DEGREE SIGN}C)"})
    fig = px.scatter(data_frame=begins_with,
                     x = "Month",
                     y = "Temperatures (\N{DEGREE SIGN}C)",
                     facet_col = "Year",
                     facet_row ="NAME")
    
    
    # make and center the title
    fig.update_layout(title_text="Scatterplot of Temperatures (\N{DEGREE SIGN}C) Against Month", title_x=0.5)
    fig.show()
  

conn = sqlite3.connect("hw1_database.db")

# stations from Japan with name starting with "G", in year 2000-2001
plot4_1("Japan",2000,2001, starting_letter="G")

conn.close()  # close the database

def query_function_4_2 (latitude_at):
    """
    This function will extracting data and create a datafram
    for stations where its latitude = latitude_at.
    The returned data has columns 
    id, year, month, temp, name, latitude, longitude
    
    Parameters
    ----------
    latitude_at: int; a user specified latitude 
    
    Return
    ----------
    a Pandas dataframe of the ave
    
    
    
    """
    where_condition = "WHERE S.latitude=" + str(latitude_at)
    
    cmd =\
    """
    SELECT  T.id, T.year, T.month, T.temp, S.name, S.latitude, S.longitude
    FROM temperatures T
    JOIN stations S ON T.id = S.id
    WHERE S.latitude=0
    """

    return pd.read_sql_query(cmd, conn)

def plot4_2 (latitude_at):
    """
    This function will make a line graph of the stations at
    the user specified latitude.
    
    Parameters
    ----------
    latitude_at: int; stations' latitude
    
    ----------
    Return 
    No return value
    """
    
    df_4_2 = query_function_4_2(latitude_at=latitude_at)  # get the data where stations at LATITUDE=0
    
    df_4_2["Date"]= df_4_2["Year"].astype(str)+ "-" + df_4_2["Month"].astype(str)
    
    # create datetime columnfrom
    df_4_2["Date"]= pd.to_datetime(df_4_2["Date"])
    
    # compute the average temperature across all stations where LATITUDE=0 in each month
    averges = df_4_2.groupby(["NAME", "Date"])[["Temp"]].mean()
    
    # recover the "NAME","Date" columns using reset_index()
    averges=averges.reset_index()
    averges = averges.rename(columns ={"NAME" : "Station Name"})
    
    fig = px.line(data_frame=averges,
              x = "Date" ,
              y = "Temp",
              color= "Station Name")
    
    # make and center the title
    fig.update_layout(title_text="Line Graph of Stations at Latitude at "+str(latitude_at), title_x=0.5)

    fig.show()
    
    

conn = sqlite3.connect("hw1_database.db")
# make a line graph of the stations at latitude = 0
plot4_2(latitude_at=0)
conn.close()