from pycen import con_gn
from pycen.wfs import list_layer,get_wfs
import geopandas as gpd

def extract_synthese_gn(table='v_synthese_for_export',con=con_gn,date_min=None,date_max=None,polygon=None,filter_obscen=True):
    sql = 'SELECT *,ST_GeomFromText(geometrie_wkt_4326,4326) geom FROM gn_synthese.{tab}'.format(tab=table)
    if any([i is not None for i in [date_min,date_max,polygon]]) or filter_obscen:
        sql += ' WHERE '
        if date_min is not None:
            sql += "date_debut > '%s' AND " % date_min
        if date_max is not None:
            sql += "date_debut < '%s' AND " % date_max
        if polygon is not None:
            sql += "ST_Intersects(ST_Transform(ST_GeomFromText(geometrie_wkt_4326,4326),2154),'SRID={epsg};{poly}') AND ".format(epsg=2154,poly=polygon)
        if filter_obscen:
            sql += """(
                unaccent(observateurs) LIKE ANY (array['%%Conservatoire d%%Espaces Naturels Isere%%', '%%Conservatoire d%%Espaces Naturels d%%Isere%%', '%%CEN Isere%%', '%%Association des Amis de l%%Ile de la Platiere%%']) 
                OR observateurs NOT LIKE '%%(%%)%%'
            ) AND observateurs NOT IN ('', 'Benoit Dodelin',', ','%%DELOCHE Denis%%')
            AND jdd_id NOT IN (185,377,236)
            AND ca_id NOT IN (53,65,66,1,2,6)
            """

    return gpd.read_postgis(sql,con_gn)

def format_forexport(df):
    is_date = df.columns.str.contains('date|heure',case=False,regex=True)
    is_uuid = df.columns.str.contains('uuid',case=False,regex=True)
    col_date = df.columns[is_uuid|is_date|(df.dtypes == 'datetime64[ns]')]
    df[col_date] = df[col_date].astype(str)


if __name__ == "__main__":

    url = 'http://ws.carmencarto.fr/WFS/94/ENS_CG38'
    list_layer(url)
    layers = ['zone_d_observation_01','Zone_d_observation_02']

    gdf = gpd.pd.concat([
        get_wfs(url,layers[0]),
        get_wfs(url,layers[1])
    ])

    df_max20210720 = extract_synthese_gn(
        table='v_synthese_for_export',
        con=con_gn,
        date_max='2021-07-21',
        polygon=gdf.unary_union
    )
    
    df_min20210721 = extract_synthese_gn(
        table='v_synthese_for_export',
        con=con_gn,
        date_min='2021-07-20',
        date_max='2024-01-01',
        polygon=gdf.unary_union
    )


    format_forexport(df_max20210720)
    format_forexport(df_min20210721)

    df_max20210720.to_file('/home/colas/Documents/9_PROJETS/6_GEONATURE/EXPORT/export_for_dept.gpkg',driver='GPKG',layername='observations_max_20210720')
    df_min20210721.to_file('/home/colas/Documents/9_PROJETS/6_GEONATURE/EXPORT/export_for_dept.gpkg',driver='GPKG',layername='observations_min_20210721')

    df_max20210720.drop('geom',axis=1).to_csv('/home/colas/Documents/9_PROJETS/6_GEONATURE/EXPORT/observationsENS_max_20210720.csv')
    df_min20210721.drop('geom',axis=1).to_csv('/home/colas/Documents/9_PROJETS/6_GEONATURE/EXPORT/observationsENS_min_20210721.csv')