import pandas as pd
import psycopg2 as psy
import matplotlib.pyplot as plt
import seaborn as sns

co = None
try : 
    co = psy.connect(host='berlin',
        database = 'dbmalanone',
        user = 'malanone',
        password = 'azertyuiop')
    curs=co.cursor()

    #Delete all the lines which has no data
    curs.execute('''DELETE FROM CO_DEUX WHERE emission = 0''')
    curs.execute('''DELETE FROM CO_DEUX WHERE code LIKE 'NaN' ''')

    #Chart that pictures the evolution of the emissions in France
    df = pd.read_sql('''SELECT * FROM CO_DEUX WHERE code LIKE 'FRA';''',con=co)

    fig = df.plot(x='year', y='emission', legend=False, style='-')
    fig.set_title("Quantité d'émission (tonnes) en fonction des années de la France")
    fig.set_xlabel("annee")
    fig.set_ylabel("emissions (en tonnes)")
    plt.show()

    df2 = pd.read_sql('''SELECT sum(emission)emissions,entity FROM CO_DEUX WHERE code LIKE 'DEU' AND year BETWEEN 1930 AND 1945 OR code LIKE 'FRA' AND year BETWEEN 1930 AND 1945 OR code LIKE 'USA' AND year BETWEEN 1930 AND 1945 OR code LIKE 'JPN' AND year BETWEEN 1930 AND 1945 OR code LIKE 'GBR' AND year BETWEEN 1930 AND 1945 OR code LIKE 'CHN' AND year BETWEEN 1930 AND 1945 GROUP BY entity;''',con=co)
    fig = df2.plot(y='emissions', autopct='%1.1f%%', labels=df2['entity'], kind='pie', legend=False)
    fig.set_title("Emissions (en tonnes) des grandes puissances lors de la Seconde Guerre Mondiale (hors URSS)")
    fig.set_ylabel("emissions (en tonnes)")
    fig.set_ylim(0)
    plt.show()


    df3 = pd.read_sql('''SELECT sum(emission)emissions,entity FROM CO_DEUX WHERE code LIKE 'DEU' AND year BETWEEN 1960 AND 1991 OR code LIKE 'FRA' AND year BETWEEN 1960 AND 1991 OR code LIKE 'USA' AND year BETWEEN 1960 AND 1991 OR code LIKE 'RUS' AND year BETWEEN 1960 AND 1991 OR code LIKE 'JPN' AND year BETWEEN 1960 AND 1991 OR code LIKE 'GBR' AND year BETWEEN 1960 AND 1991 OR code LIKE 'CHN' AND year BETWEEN 1960 AND 1991 GROUP BY entity;''',con=co)
    fig = df3.plot(y='emissions', autopct='%1.1f%%', labels=df3['entity'], kind='pie', legend=False)
    fig.set_title("Emissions (en tonnes) des grandes puissances lors de la Guerre Froide (à partir de 1960)")
    fig.set_ylabel("emissions (en tonnes)")
    fig.set_ylim(0)
    plt.show()


    df4 = pd.read_sql('''SELECT sum(emission)emissions,entity,to_char(year,'9999')years FROM CO_DEUX WHERE code LIKE 'USA' AND year BETWEEN 1960 AND 1991 OR code LIKE 'RUS' AND year BETWEEN 1960 AND 1991 OR code LIKE 'CHN' AND year BETWEEN 1960 AND 1991 GROUP BY entity,year;''',con=co)
    sns.barplot(data=df4 , x='years', y='emissions', hue='entity')
    fig.set_title("Emissions (en tonnes) des grandes puissances lors de la Guerre Froide (à partir de 1960)")
    fig.set_xlabel("code du pays")
    fig.set_ylabel("emissions (en tonnes)")
    fig.set_xticks(df4.index)
    plt.show()




    df5 = pd.read_sql('''SELECT entity, to_char(year,'9999')years,emission FROM CO_DEUX WHERE emission = ANY(SELECT max(emission)emission FROM CO_DEUX WHERE year BETWEEN 2000 AND 2017 AND code NOT LIKE 'OWID_WRL' GROUP BY year) ORDER BY year;''',con=co)

    fig = df5.plot(x='entity', y='emission', legend=False, style='-')
    sns.barplot(data=df5 , x='years', y='emission', hue='entity')
    fig.set_title("Plus gros pollueur de chaque annee, de 2000 à 2017")
    fig.set_xlabel("code du pays")
    fig.set_ylabel("emissions de CO2(en tonnes)")
    fig.set_xticks(df5.index)
    fig.set_xticklabels(df5['years'], rotation='45')
    plt.show()

    df6 = pd.read_sql('''SELECT entity, emission FROM CO_DEUX WHERE code NOT LIKE 'OWID_WRL' AND year = 2017 AND emission > (SELECT avg(emission) FROM CO_DEUX WHERE code NOT LIKE 'OWID_WRL' AND year = 2017);''',con=co)
    fig = df6.plot(y='emission', autopct='%1.1f%%', labels=df6['entity'], kind='pie', legend=False)
    fig.set_title("Pays dont l'émission de CO2 en 2017 dépasse la moyenne mondiale")
    fig.set_ylim(0)
    plt.show()


    df7 = pd.read_sql('''SELECT *,to_char(year,'9999')years FROM CO_DEUX WHERE code LIKE 'USA' AND year BETWEEN 1980 AND 2017 OR code LIKE 'CHN' AND year BETWEEN 1980 AND 2017;''',con=co)

    #Chart that picture the evolution of the emission in China
    fig.set_title("Emissions américaines et chinoises (en tonnes) en fonction des années")
    fig.set_xlabel("annee")
    fig.set_ylabel("emissions (en tonnes)")
    sns.catplot(data=df7, kind="bar", x="years", y="emission", hue="entity")
    plt.show()


    co.commit()


    df8 = pd.read_sql('''SELECT ((sum(emission)*40)/100)emissions, to_char(year,'9999')years FROM CO_DEUX WHERE code LIKE 'USA' AND year BETWEEN 2000 AND 2017 GROUP BY year;''',con=co)
    fig = df8.plot(x='years', y='emissions', legend=False, style='-')
    fig.set_title("Emissions américaines (en tonnes) dues à la combustion de pétrole de 2000 à 2017")
    fig.set_xlabel("annee")
    fig.set_xticks(df8.index)
    fig.set_ylabel("emissions de CO2 (en tonnes)")
    fig.set_xticklabels(df8['years'], rotation='45')
    plt.show()


    #Emissions chinoises liées à la combustion de charbon
    df9 = pd.read_sql('''SELECT ((sum(emission)*73)/100)emissions, to_char(year,'9999')years FROM CO_DEUX WHERE code LIKE 'CHN' AND year BETWEEN 2000 AND 2017 GROUP BY year;''',con=co)
    fig = df9.plot(x='years', y='emissions', legend=False, style='-')
    fig.set_title("Emissions chinoises (en tonnes) dues à la combustion de charbon de 2000 à 2017")
    fig.set_xlabel("annee")
    fig.set_xticks(df9.index)
    fig.set_ylabel("emissions de CO2 (en tonnes)")
    fig.set_xticklabels(df9['years'], rotation='45')
    plt.show()


except (Exception, psy.DatabaseError) as error :
    print(error)

finally :
    if co is not None:
        co.close()

