from random import randint
from click import Path
from flask import *
from werkzeug.utils import secure_filename
import json
import re
import requests
from flask import Flask, render_template
from shapely import wkt
import wepmapping as carte
from credentials import *  # Assurez-vous que ce fichier contient vos configurations correctement

from datetime import datetime, date
from flask_mysqldb import MySQL

from sqlalchemy import create_engine, sql
import pandas as pd
import geopandas as gpd
from shapely import wkt
import folium
from io import BytesIO

app = Flask(__name__)



# Configuration
app.config['UPLOAD_FOLDER'] = 'static/uploads'
app.config['SECRET_KEY'] = my_token
app.config['MYSQL_HOST'] = my_host
app.config['MYSQL_USER'] = my_user
app.config['MYSQL_PASSWORD'] = my_password
app.config['MYSQL_DB'] = my_db
app.config['MYSQL_CURSORCLASS'] = ''  
mysql = MySQL(app)  # Initialisation de MySQL avec app

date_now = date.today().strftime("%Y-%m-%d")


# Fonction pour créer une connexion avec la base de données


# Fonction pour sélectionner les données dans une table MySQL
def select_table(cursor, table_name):
    query = f"SELECT * FROM `{table_name}`"
    cursor.execute(query)
    data = cursor.fetchall()
    # Transformation des données en DataFrame Pandas
    columns = [desc[0] for desc in cursor.description]
    df = pd.DataFrame(list(data), columns=columns)
    return df

# Fonction pour convertir les données en format géospatial
def convert_to_geodataframe(data):
    # Conversion de la colonne 'geometry' en WKT
    data['geometry'] = data['geometry'].apply(wkt.loads)
    gdf = gpd.GeoDataFrame(data, geometry='geometry')
    gdf = gdf.set_crs(4326)  # Définir le CRS comme WGS 84
    return gdf




# def convertion_geo(data):
#     database=data.copy()
#     database['geometry'] = database['geometry'].apply(wkt.loads)
#     database = gpd.GeoDataFrame(database, geometry='geometry')
#     database=database.set_crs(4326) ### WGS 84 / UTM zone 31N (32631)
#     database=database.to_crs(4326) ### en degré
#     return database


# # modifé






def def_jounture(data_final, data):
    data_final=data_final.to_crs(4326)
    data=data.to_crs(4326)
    dfb_fin=data_final.sjoin(data, how='left', lsuffix="collecte", rsuffix="")
    return dfb_fin


# def select_table_recherche(engine, variable):
#     with engine.begin() as conn:
#         query = sql.text("""SELECT * FROM `aire_protegee` WHERE nom_Aire_protegee like '%"""+str(variable)+"""%'""")
#         data = pd.read_sql_query(query, conn)
#     return data

def select_table_recherche(cursor, variable):
    query = """SELECT * FROM `aire_protegee` WHERE nom_Aire_protegee like '%"""+str(variable)+"""%'"""
    cursor.execute(query)
    data = cursor.fetchall()
    columns = [desc[0] for desc in cursor.description]
    df = pd.DataFrame(list(data), columns=columns)
    return df




# Fonction pour générer la carte avec Folium sans sauvegarde
def create_map(aire_protegee_gdf, commune_gdf):
    # Créer une carte centrée sur une région spécifique (par exemple, Togo)
    folium_map = folium.Map(location=[6.1375, 1.2125], zoom_start=10)

    # Ajouter les aires protégées à la carte
    for _, row in aire_protegee_gdf.iterrows():
        folium.GeoJson(row['geometry'], name='Aire Protegee').add_to(folium_map)

    # Ajouter les communes à la carte
    for _, row in commune_gdf.iterrows():
        folium.GeoJson(row['geometry'], name='Commune').add_to(folium_map)

    # Ajouter un contrôle pour activer/désactiver les couches
    folium.LayerControl().add_to(folium_map)

    # Retourner la carte sous forme de string HTML
    return folium_map._repr_html_()


@app.route("/")
def index():
    return render_template('home/index.html')
    




@app.route('/test', methods=['GET'])
def test_db():
    try:
        cur = mysql.connection.cursor()
        
        # Sélectionner les données des tables
        aire_protegee_data = select_table(cur, 'aire_protegee')
        commune_data = select_table(cur, 'commune')
        
        # Conversion en GeoDataFrame
        aire_protegee_gdf = convert_to_geodataframe(aire_protegee_data)
        commune_gdf = convert_to_geodataframe(commune_data)
        
        # Créer la carte avec Folium sans sauvegarde
        folium_map = create_map(aire_protegee_gdf, commune_gdf)

        # Retourner la carte sous forme HTML dans la réponse Flask
        return folium_map
        
    except Exception as e:
        return str(e), 500


@app.route("/actualites", methods=['GET'])
def actualites():
    return render_template('home/actualites.html')


@app.route("/a-propos-de-nous", methods=['GET'])
def about():
    return render_template('home/about.html')


@app.route("/bienvenue-au-accueil-backend")
def accueil_backend():
    try:
        if 'email_citoyens' in session:
            cur = mysql.connection.cursor()
            loggedIn_admin, name_admin = getLogin("email_citoyens", "citoyens")
            aire_protegee = convert_to_geodataframe(select_table(cur, 'aire_protegee'))
            commune = convert_to_geodataframe(select_table(cur, 'commune'))
            data = def_jounture(aire_protegee, commune)
            hydro_df = convert_to_geodataframe(select_table(cur, 'hydrographie'))
            point_df = convert_to_geodataframe(select_table(cur, 'point_feux'))
            route_df = convert_to_geodataframe(select_table(cur, 'route'))
            point_df = point_df.drop(["ACQ_DATE"], axis=1)
            region = convert_to_geodataframe(select_table(cur, 'region'))
    
            initialize_map = carte.carte_aire_proteger(data, aire_protegee, hydro_df, point_df, region)
            #######--------------------
    
            initialize_map.get_root().width = "100%"
            initialize_map.get_root().height = "200px"
            iframe = initialize_map.get_root()._repr_html_()
    
            data_anim = is_request_all_autres("""with tab as (select nom_scientifique_Espece_animale, nom_Aire_protegee
            				from  aire_protegee  a
            				left join  espece_animale c on a.id_aire_protegee=c.id_aire_protegee
                            ) select nom_Aire_protegee, 
                                     count( distinct nom_scientifique_Espece_animale) count_nom_scientifique_Espece_vegetale
                              from tab 
                              group by 1""")
    
            data_vege = is_request_all_autres("""with tab as (select b.nom_scientifique_Espece_vegetale , a.id_Aire_protegee
    			from trouver a
    			left join  especes_vegetales b on a.id_Espece_vegetale=b.id_Espece_vegetale 
                ) select nom_Aire_protegee, 
                         count( distinct nom_scientifique_Espece_vegetale) count_nom_scientifique_Espece_vegetale
                  from aire_protegee   a 
                  left join  tab     c on a.id_aire_protegee  =c.id_aire_protegee
                  group by 1""")
    
            return render_template('geoforest/accueil_backend.html',
                                   loggedIn_admin=loggedIn_admin,
                                   name_admin=name_admin.upper(),
                                   iframe=iframe,
                                   data_anim=data_anim,
                                   data_vege=data_vege)
        else:
            return render_template('home/connexion.html') # modifé
    except Exception as e:
        return str(e), 500

# Modifié
@app.route("/login-cadastre-aire", methods=['POST', 'GET'])
def login_public_togo():
    if request.method == 'POST':
        email = request.form['email']
        cur = mysql.connection.cursor()

        # Vérifier si le numéro existe déjà
        cur.execute("SELECT 1 FROM citoyens WHERE email_citoyens = %s", (email,))
        email_existe = cur.fetchone()

        if email_existe:
            session['email_citoyens'] = email
            # flash("❌❌❌ Erreur, l'email incorecte ")
            return redirect(url_for('accueil_backend'))

        # Insérer le nouveau numéro dans la base de données
        cur.execute("INSERT INTO citoyens (email_citoyens, date_inscription) VALUES (%s, %s)", (email, date_now))
        mysql.connection.commit()
        cur.close()


        # Enregistrer le numéro dans la session
        session['email_citoyens'] = email
        return redirect(url_for('accueil_backend'))
    else :

        return redirect(url_for('accueil_backend'))

#rajout



@app.route("/resulat-trouvers", methods=['POST', 'GET'])
def aire_recherche():
    try:
        if request.method == 'POST':
            if 'email_citoyens' in session:
                recherche = request.form['recherche']
                cur = mysql.connection.cursor()
                loggedIn_admin, name_admin = getLogin("email_citoyens", "citoyens")
                aire_protegee = convert_to_geodataframe(select_table(cur, 'aire_protegee'))
                commune = convert_to_geodataframe(select_table(cur, 'commune'))
                data = def_jounture(aire_protegee, commune)
    
                data_resultats=def_jounture(convert_to_geodataframe(select_table_recherche(cur, recherche)), commune)
    
                if data_resultats.shape[0]==0:
                    return render_template('geoforest/pas_resultat.html',
                                           loggedIn_admin=loggedIn_admin,
                                           name_admin=name_admin.upper(),
                                           nbr=0)
                else :
                    nbr = len(set(data_resultats.nom_Aire_protegee.to_list()))
                    initialize_map = carte.carte_aire_proteger_rech(data, aire_protegee,data_resultats)
                    initialize_map.get_root().width = "100%"
                    initialize_map.get_root().height = "500px"
                    iframe = initialize_map.get_root()._repr_html_()
                    return render_template('geoforest/accueil_recherche.html',
                                           loggedIn_admin=loggedIn_admin,
                                           name_admin=name_admin.upper(),
                                           iframe=iframe,
                                           nbr=nbr)
            else:
                return render_template('home/connexion.html')
    
        else :
            return redirect(url_for('accueil_backend'))

    except Exception as e:
        return str(e), 500





@app.route("/bienvenue-espace-citoyens")
def admin_citoyens():
    if 'email_citoyens' in session:
        cur = mysql.connection.cursor()
        loggedIn_admin, name_admin = getLogin("email_citoyens", "citoyens")
        aire_protegee = convert_to_geodataframe(select_table(cur, 'aire_protegee'))
        commune = convert_to_geodataframe(select_table(cur, 'commune'))
        data = def_jounture(aire_protegee, commune)

        hydro_df = convert_to_geodataframe(select_table(cur, 'hydrographie'))
        point_df = convert_to_geodataframe(select_table(cur, 'point_feux'))
        route_df = convert_to_geodataframe(select_table(cur, 'route'))
        point_df = point_df.drop(["ACQ_DATE"], axis=1)
        region = convert_to_geodataframe(select_table(cur, 'region'))

        ifn_data = convert_to_geodataframe(select_table(cur, 'ifn_aire_protee')) ##
        rbt_data = convert_to_geodataframe(select_table(cur, 'rbt'))  ##
        data_sacre =convert_to_geodataframe(select_table(cur, 'forets_sacres'))  ##



        initialize_map = carte.carte_aire_proteger_global(data, aire_protegee, hydro_df, point_df, region,
                                                   rbt_data, data_sacre, ifn_data)
        #######--------------------
        #######--------------------

        initialize_map.get_root().width = "100%"
        initialize_map.get_root().height = "700px"
        iframe = initialize_map.get_root()._repr_html_()
        return render_template('geoforest/data-visualization.html', loggedIn_admin=loggedIn_admin,name_admin=name_admin.upper() , iframe=iframe )
    else:
        return render_template('home/connexion.html')  # modifé


@app.route("/statistique")
def fun_statistique():
    if 'email_citoyens' in session:
        loggedIn_admin, name_admin = getLogin("email_citoyens", "citoyens")

        #######--------------------
        data_pref=is_request_all("""with tab as (select nom_Prefecture, nom_Aire_protegee
        from placer a
            left join  prefecture b on a.id_Prefecture=b.id_Prefecture
            left join   aire_protegee c on a.id_aire_protegee=c.id_aire_protegee
            ) select nom_Prefecture, count( distinct nom_Aire_protegee) count_nom_Aire_protegee
              from tab 
              group by 1
              """)
        prefectures = data_pref[0]
        values_pref = data_pref[1]

        data_commune=is_request_all("""with tab as (select nom_commune, nom_Aire_protegee
			from positionner a
			left join  commune b on a.id_commune=b.id_commune
			left join  aire_protegee c on a.id_aire_protegee=c.id_aire_protegee
            ) select nom_commune, 
                     count( distinct nom_Aire_protegee) count_nom_Aire_protegee
              from tab 
              group by 1""")
        communes =data_commune[0]
        values_comm =  data_commune[1]

        data_aire = is_request_all(""" with tab as (select b.nom_scientifique_Espece_vegetale , a.id_Aire_protegee
			from trouver a
			left join  especes_vegetales b on a.id_Espece_vegetale=b.id_Espece_vegetale 
                ) select nom_Aire_protegee, 
                         count( distinct nom_scientifique_Espece_vegetale) count_nom_scientifique_Espece_vegetale
                  from aire_protegee   a 
                  left join  tab     c on a.id_aire_protegee  =c.id_aire_protegee
                  group by 1""")
        aires = data_aire[0]
        aire_values = data_aire[1]

        data_anim = is_request_all("""with tab as (select nom_scientifique_Espece_animale, nom_Aire_protegee
				from  aire_protegee  a
				left join  espece_animale c on a.id_aire_protegee=c.id_aire_protegee
                ) select nom_Aire_protegee, 
                         count( distinct nom_scientifique_Espece_animale) count_nom_scientifique_Espece_vegetale
                  from tab 
                  group by 1""")
        aires_animale = data_anim[0]
        aires_animale_values = data_anim[1]


        return render_template('geoforest/statistiques.html',
                               loggedIn_admin=loggedIn_admin,
                               name_admin=name_admin.upper(),
                               prefectures=prefectures,
                               values_pref=values_pref,
                               communes=communes,
                               values_comm=values_comm,
                               aires=aires,
                               aire_values=aire_values,
                               aires_animale_values=aires_animale_values,
                               aires_animale=aires_animale)
    else:
        return render_template('home/connexion.html')



# @app.route("/dash", methods=['GET'])
# def dash():
#     return render_template('geoforest/accueil_backend.html')
#


@app.route("/logout")
def logout():
    session.pop('email_citoyens', None)
    return redirect(url_for('index'))



def getLogin(email, table):
    cur = mysql.connection.cursor()
    if email not in session:
        loggedIn = False
        firstName = ''
    else:
        loggedIn = True
        cur.execute("SELECT id, email_citoyens FROM  " + table + " WHERE " + email + " = '" + session[email] + "'")
        useradminID, firstName = cur.fetchone()

    cur.close()
    return (loggedIn, firstName)






def is_valid(email, email_t, table, false=False):
    cur = mysql.connection.cursor()
    cur.execute('SELECT ' + email_t + ' FROM ' + table)
    data = cur.fetchall()
    for row in data:
        if row[0] == str(email) :
            return True
    return false


def is_request_all(requet):
    cur = mysql.connection.cursor()
    cur.execute(requet)
    count_pref = cur.fetchall()
    cat = [row[0] for row in count_pref]
    values = [int(row[1]) for row in count_pref]
    return cat, values

def is_request_all_autres(requet):
    cur = mysql.connection.cursor()
    cur.execute(requet)
    count_pref = cur.fetchall()
    return count_pref



if __name__ == "__main__":
    app.run(debug=True)
    
    
    
    