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

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

# rename columns
countries = pd.read_csv('countries.csv')
countries = countries.rename(columns = {"FIPS 10-4": "FIPS_10-4"}) 
countries = countries.rename(columns = {"ISO 3166": "ISO_3166"})

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

# open a connection to temps.db sp that you can 'talk' to using python
conn = sqlite3.connect("temps.db")

# if the temperatures already exits in the temps.db, if_exists="replace" could replace the cvs file.
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()

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

# query the database
cursor = conn.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")

# retrieve the data
print(cursor.fetchall())

conn.close()

temps.head()

countries.head()

stations.head()

def query_climate_database(country, year_begin, year_end, month):
    # open connection to database
    conn = sqlite3.connect("temps.db")
    
    # sql command
    cmd = "SELECT S.Name, S.LATITUDE, S.LONGITUDE, C.Name AS Country, T.Year, T.Month, T.Temp \
    FROM temperatures T \
    LEFT JOIN stations S ON S.ID = T.ID \
    LEFT JOIN countries C ON SUBSTRING(S.id, 1, 2)= C.'FIPS_10-4' \
    WHERE (Year BETWEEN {} AND {}) AND (Month={})".format(year_begin, year_end, month)
    
    # excute the command
    df = pd.read_sql(cmd, conn)
    
    # subset the dataframe with the country taht user wanted
    df = df[df['Country'] == country]

    # always close the database when we are finish use it
    conn.close()
    
    return df

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

df

df["count"] = df.groupby(["NAME"])["Country"].transform('count')
df

def temperature_coefficient_plot(country, year_begin, year_end, month, min_obs = 10, **kwargs):
    # use the query function we made to make a dataframe
    df = query_climate_database(country, year_begin, year_end, month)
    
    # use transform to add the corresponding count for each rows
    df["count"] = df.groupby(["NAME"])["Country"].transform('count')
    
    # subset the dataframe by remove the data that less than min_obs
    df = df[df['count'] >= min_obs]

    # define a function to get the linear regression for input dataframe, then return it's slope
    def coef(data_group):
        X = data_group[["Year"]]
        y = data_group["Temp"]
        LR = LinearRegression()
        LR.fit(X, y)
        slope = LR.coef_[0]
        return slope
    
    df = df.groupby(["NAME", "LATITUDE", "LONGITUDE"]).apply(coef).round(4)
    df = pd.DataFrame(df)
    df = df.reset_index()
    df = df.rename(columns = {0: "Estimated_Yearly_Increase_(℃)"})
    
    fig = px.scatter_mapbox(data_frame = df, 
                            lat = "LATITUDE", 
                            lon = "LONGITUDE",
                            hover_name = "NAME", 
                            color = "Estimated_Yearly_Increase_(℃)",
                            **kwargs
                           )
    return fig

color_map = px.colors.diverging.RdGy_r

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

fig.update_layout(title="Estimates of yearly increase in temperature in January for<br>stations in India, years 1980-2020")

fig.show()

color_map = px.colors.diverging.RdGy_r

fig = temperature_coefficient_plot("Japan", 1980, 2020, 5, 
                                   min_obs = 10,
                                   zoom = 2,
                                   mapbox_style="carto-positron",
                                   color_continuous_scale=color_map
                                  )

fig.update_layout(title="Estimates of yearly increase in temperature in May for<br>stations in Japan, years 1980-2020")

fig.show()

query_climate_database("China", 1980, 2020, 2)

list(set(df["NAME"]))[0:10] # just a example to use set and print first 10 cities from it.

def scatter_plot(country, year_begin, year_end, month, cities, **kwargs):
    
    df = query_climate_database(country, year_begin, year_end, month)
    
    df_new = pd.DataFrame(columns = df.columns) # make a empty dataframe
    
    # append the data that collected from input cities
    for city in cities:
        df_new = pd.concat([ df_new, df[df["NAME"]==city] ])
    
    fig = px.scatter(data_frame=df_new, 
                     x= "Year", 
                     y="Temp",
                     color = "NAME", 
                     hover_name="NAME", 
                     hover_data=["LATITUDE", "LONGITUDE"],
                     facet_col="NAME", 
                     **kwargs
                    )
    
    return fig

cities_selected = ['HEFEI', 'GUANGZHOU', 'SHANGHAI_HONGQIAO','AN_XI']

fig = scatter_plot("China", 1980, 2020, 2, cities_selected)

fig.update_layout(title="Scatter plots with year verse temperature in Feburary with selected cities of China, years 1980-2020")

fig.show()

def query_climate_database_new(country, year_begin, year_end):
    # open connection to database
    conn = sqlite3.connect("temps.db")
    
    # sql command
    cmd = "SELECT S.Name, C.Name AS Country, T.Year, T.Month, T.Temp \
    FROM temperatures T \
    LEFT JOIN stations S ON S.ID = T.ID \
    LEFT JOIN countries C ON SUBSTRING(S.id, 1, 2)= C.'FIPS_10-4' \
    WHERE (Year BETWEEN {} AND {}) \
    ORDER BY T.Year, T.Month".format(year_begin, year_end)
    
    # excute the command
    df = pd.read_sql(cmd, conn)
    
    # subset the dataframe with the country taht user wanted
    df = df[df['Country'] == country]
    
    # make new column with date time values
    df["Date"] = pd.to_datetime(df["Year"].astype(str) + "-" + df["Month"].astype(str))

    # always close the database when we are finish use it
    conn.close()
    
    return df

# an example
df = query_climate_database_new(country = "United States", 
                                year_begin = 2000, 
                                year_end = 2020)

df

def line_plot(country, year_begin, year_end, cities, **kwargs):
    
    df = query_climate_database_new(country, year_begin, year_end)
    
    df_new = pd.DataFrame(columns = df.columns) # make a empty dataframe
    
    # append the data that collected from input cities
    for city in cities:
        df_new = pd.concat([ df_new, df[df["NAME"]==city] ])
    
    fig = px.line(data_frame=df_new, 
                  x= "Date", 
                  y="Temp",
                  color = "NAME", 
                  hover_name="NAME", 
                  facet_col="NAME", 
                  **kwargs
                 )
    
    return fig

cities_selected = ['HEFEI', 'GUANGZHOU', 'SHANGHAI_HONGQIAO']

fig = line_plot("China", 2000, 2020, cities_selected)

fig.update_layout(title="Line plots with date verse temperature with selected cities of China, years 2000-2020")

fig.show()