from pycen import con_gn, con_sicen
import geopandas as gpd
from datetime import datetime as dt
import os

cols_sicen_out = {
    'id_origine':'id_origine', 
    '':'organisme_gestionnaire', 
    'cd_nom':'cd_nom',
    'nom_complet':'info_nomcomplet',
    'nom_vern':'info_nomvern',
    'date_debut_obs':'date_debut_obs',
    'date_fin_obs':'date_fin_obs',
    'date_obs':'date_obs',
    'date_textuelle':'date_textuelle',
    'effectif':'effectif',
    'effectif_min':'effectif_min',
    'effectif_max':'effectif_max',
    'effectif_textuel':'effectif_textuel',
    'remarque_obs':'remarque_obs',
    'localisation':'remarque_loc',
    'determination':'determination',
    'type_effectif':'type_effectif',
    'phenologie_age':'phenologie_age',
    'observateur':'observateur',
    'structure':'structure',
    'etude':'nom_etude',
    'protocole':'nom_protocole',
    '':'id_mnhn',
    '':'info_nomznieff',
    '':'statut',
}
cols_geom_out = 'geometrie'

def get_zonage(type_code):
    sql = '''
    SELECT a.* FROM ref_geo.l_areas a
    JOIN ref_geo.bib_areas_types b USING (id_type)
    WHERE b.type_code = 'DEP' AND a.area_code = '38'
    '''
    return gpd.read_postgis(sql,con_gn)


def get_obs_gn(date_min = None):
    from shapely.geometry import Point
    sql = '''
    SELECT * FROM gn_synthese.v_synthese_for_export 
    '''
    if date_min:
        sql += " WHERE date_debut > '{date_min}'".format(date_min=date_min)
    res = gpd.pd.read_sql_query(sql,con_gn)
    XY = tuple(zip(res.x_centroid_4326,res.y_centroid_4326))
    res['geom'] = [Point(xy) for xy in XY]

    return res.set_geometry('geom',crs=4326)\
        .to_crs(2154)


def get_obs_sicen(date_min = None,intersects=False):
    sql = '''
    SELECT v.* FROM saisie.v_saisie_observation v
        JOIN saisie.suivi_saisie_observation s USING (id_obs)
    '''
    if date_min or intersects:
        sql += ' WHERE '
    if date_min:
        dtm = """
            v.date_obs > '{date_min}'
            -- OR s.date_operation > '{date_min}') AND s.operation = 'INSERT'
        """.format(
            date_min=date_min
        )
        sql += dtm
    if intersects : 
        ''

    sql += ';'
    return gpd.read_postgis(
        sql,con_sicen,geom_col='geometrie',#parse_dates=['date_obs','date_debut_obs','date_fin_obs'],
        )\
        .rename_geometry('geom')

if __name__ == "__main__":

    today = dt.today().date().strftime('%Y%m%d')
    path = '/home/colas/Documents/tmp'
    file_name = os.path.join(path,'export_znieff1_cen38_%s.gpkg'%today)

    date_min = '2021-12-31' # Extraction des données observées ou intégrées après cette date.
    date_max = '2023-01-01' # Extraction des données observées avant cette date.

    zon = get_zonage('ZNIEFF1').unary_union
    gn = get_obs_gn(date_min=date_min)
    sicen = get_obs_sicen(date_min=date_min)
    sicen['type_effectif'] = sicen['strate_flore'] + sicen['age_faune']
    sicen['phenologie_age'] = sicen['phenologie_flore'] + sicen['sexe_faune']
    
    gn.date_debut  = gpd.pd.to_datetime(gn.date_debut)
    sicen.date_obs = gpd.pd.to_datetime(sicen.date_obs)

    obs_gn = gn[gn.date_debut < dt.strptime(date_max,'%Y-%m-%d')]
    obs_sicen = sicen[sicen.date_obs < dt.strptime(date_max,'%Y-%m-%d')]

    gn_date = obs_gn.columns[obs_gn.columns.str.contains('date_|time|heure|uuid')]
    sicen_date = obs_sicen.columns[obs_sicen.columns.str.contains('date_|time|heure')]

    obs_gn[gn_date] = obs_gn[gn_date].astype(str)
    obs_sicen[sicen_date] = obs_sicen[sicen_date].astype(str)

    obs_sicen.rename(columns=cols_sicen_out,inplace=True)
    obs_sicen.rename_geometry(cols_geom_out,inplace=True)
    keep_col = cols_sicen_out.values()
    si_cols = obs_sicen.columns[obs_sicen.columns.isin([*keep_col,cols_geom_out])]

    obs_sicen.loc[obs_sicen.intersects(zon),si_cols].to_file(file_name,driver='GPKG',layer='sicen_obs')
    obs_gn[obs_gn.intersects(zon)].to_file(file_name,driver='GPKG',layer='geonature_obs')