import pandas as pd
import sqlite3
import numpy as np
from plotly import express as px
from sklearn.linear_model import LinearRegression
import calendar
import plotly.graph_objects as go
from plotly.io import write_html

temps = pd.read_csv("temps_stacked.csv")
temps.head()

countries = pd.read_csv('countries.csv')
countries.head()

countries = countries.rename(columns= {"FIPS 10-4": "FIPS_10-4"})
countries.head()

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

conn = sqlite3.connect("temps.db")
temps.to_sql("temperatures", conn, if_exists="replace", index=False)
countries.to_sql("countries", conn, if_exists="replace", index=False)
stations.to_sql("stations", conn, if_exists="replace", index=False)
# always close your connection
conn.close()

def query_climate_database(country, year_begin, year_end, month):
    #Input: three variables
    #Country: the country we want to investigate
    #year_begin,year_end: the year period starting at year_begin, ending at year_end
    #month: the month that we want to investigate
    #Output: the data frame with all the desired data
    conn = sqlite3.connect("temps.db")
    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."FIPS_10-4" =substring(T.id,1,2)\
              WHERE C.Name = '{country}' AND T.year >= {year_begin} AND T.year<={year_end} AND T.month ={month}"""
    
    df = pd.read_sql(cmd, conn)
    conn.close()
    return df    

df = query_climate_database("India",1980,2020,1)
df.head()

def coef(data_group):
    #Input:a data frame
    #output: the slope of the temperature
    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):
    #Input: six variables
    #Country: the country we want to investigate
    #year_begin,year_end: the year period starting at year_begin, ending at year_end
    #month: the month that we want to investigate
    #Output: the plot we generate
    df = query_climate_database(country, year_begin, year_end, month)
    df['len']=df.groupby(["NAME"])["Temp"].transform(len)
    df=df[df['len']>= min_obs]
    
    coefs = df.groupby(["NAME"]).apply(coef)
    coefs = coefs.reset_index()
    coefs[0]=coefs[0].round(4)
    df =pd.merge(df,coefs,how='right')
    df.rename(columns={0:'Estimated Yearly Increase (°C)'},inplace = True)
    fig = px.scatter_mapbox(df, # 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
                            zoom = 1, # how much you want to zoom into the map
                            height = 500, # control aspect ratio
                            mapbox_style="carto-positron", # map style
                            opacity=0.2,# opacity for each data point
                            color = "Estimated Yearly Increase (°C)",# represent temp increase using color
                            color_continuous_midpoint = 0,
                            title = f"Estimated Yearly Increase in temperature in {calendar.month_name[month]} for stations in {country}, years {year_begin}-{year_end}"
                           )
    return fig

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

fig_india = temperature_coefficient_plot("India", 1980, 2020, 1, 
                                   min_obs = 10,
                                   zoom = 2,
                                   mapbox_style="carto-positron",
                                   color_continuous_scale=color_map)

write_html(fig_india, "fig_india.html")

fig_China = temperature_coefficient_plot("China", 1985, 2015, 1, 
                                   min_obs = 10,
                                   zoom = 2,
                                   mapbox_style="carto-positron")
write_html(fig_China, "fig_China.html")

def query_climate_database2(year_begin,year_end, month, long_begin, long_end):
    #Input: five variables
    #year_begin,year_end: the year period starting at year_begin, ending at year_end
    #month: the month that we want to investigate
    #long_begin, long_end: the longitude range starting at long_begin, ending at long_end
    #Output: the data frame with all the desired data
    conn = sqlite3.connect("temps.db")
    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."FIPS_10-4" =substring(T.id,1,2)\
              WHERE S.longitude >={long_begin} AND S.longitude <={long_end} \
              AND T.year >= {year_begin} AND T.year <= {year_end} AND T.month ={month}"""
    
    df = pd.read_sql(cmd, conn)
    conn.close()
    return df

df2 = query_climate_database2(2018,2020, 1, 30, 32)
df2.head()

def scatter_plot(year_begin,year_end, month, long_begin, long_end, **kwags):
    #Input: five variables
    #year_begin,year_end: the year period starting at year_begin, ending at year_end
    #month: the month that we want to investigate
    #long_begin, long_end: the longitude range starting at long_begin, ending at long_end
    #Kwags: optional argument for graph
    #Output: the scatter plot
    df = query_climate_database2(year_begin,year_end, month, long_begin, long_end)


    fig = px.scatter(data_frame = df,
                      x = "LATITUDE",
                      y = "Temp",
                      hover_name = "NAME",
                      hover_data =["Name","LONGITUDE"],
                      color ="Name",
                      facet_row = "Year"
                      )
    return fig

fig_scatter = scatter_plot(2018,2020, 1, 30, 32)
write_html(fig_scatter, "fig_scatter.html")

def query_climate_database3(year,long_begin, long_end, lat_begin,lat_end):
    #Input: five variables
    #year: the year to be analyzed
    #long_begin, long_end: the longitude range starting at long_begin, ending at long_end
    #lat_begin, lat_end: the latitude range starting at lat_begin, ending at lat_end
    #Output: the data frame with all the desired data
    conn = sqlite3.connect("temps.db")
    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."FIPS_10-4" =substring(T.id,1,2)\
              WHERE T.year={year} AND (T.month=1 or T.month=7) AND S.longitude >={long_begin} \
              AND S.longitude <={long_end} AND S.latitude >={lat_begin} AND S.latitude <={lat_end}
              """
    
    df = pd.read_sql(cmd, conn)
    conn.close()
    return df

def heat_density_plot(year,long_begin, long_end, lat_begin,lat_end,**kwags):
    #Input: five variables
    #year: the year to be analyzed
    #long_begin, long_end: the longitude range starting at long_begin, ending at long_end
    #lat_begin, lat_end: the latitude range starting at lat_begin, ending at lat_end
    #Kwags: optional argument for graph
    #Output: the heat density plot
    df = query_climate_database3(year,long_begin, long_end, lat_begin,lat_end)
    fig = px.density_heatmap(df, 
                             x = "LATITUDE",
                             y = "LONGITUDE",
                             z = "Temp",
                             facet_row = "Month",
                             hover_name = "NAME",
                             nbinsx = 25,
                             nbinsy = 25)
    return fig

fig_heat = heat_density_plot(2019,80,110,30,50)
write_html(fig_heat, "fig_heat.html")