#!/usr/bin/env python3
# -*- coding: UTF-8 -*-

import geopandas as gpd
from pycen import con

path0 = '/home/colas/Documents/9_PROJETS/2_PS/TO IMPORT/'
file  = 'CDisère_PS2016_4Montagnes.shp'
dic = {
    'identifian':'ident',
    'habitat1' : 'code_hab1',
    'partpoly1': 'n_hab1',
    'habitat2' : 'code_hab2',
    'partpoly2': 'n_hab2',
    'habitat3' : 'code_hab3',
    'partpoly3': 'n_hab3',
    'habitat4' : 'code_hab4',
    'partpoly4': 'n_hab4',
    'légende':'legende',
    'embrou_cod':'niv_embrous',
    'embrous':'%_embrous',
    'sol nu_cod':'recouvmnt',
    'sol nu':'%_recouvmnt',
    'orchidées':'rmq_phyto',
    'alliance':'rmq_phytosocio',
    'maec':'rmq_contract_agri',
}
dic_auth = {
    'Anne-Lyse Murro':'MURRO Anne-Lyse',
    'Lucas Berardi'  : 'BERARDI Lucas'
}
dic_prat = {
    'Mixte' : 'Fauche, Pâturage',
    'Adandon':'Abandon',
    'P�turage':'Pâturage'
}
dic_stat = {
    'Aucun' : 'Non communautaire',
    'Prioriataire' : 'Prioritaire'
}
dic_date = {
    'mai 2016'  : '2016-05-01',
    'juin 2016' : '2016-06-01',
    'juillet 2016' : '2016-07-01',
    'aout 2016' : '2016-08-01',
    'août 2016' : '2016-08-01',
}
keep_cols = ['auteur','date',*dic.values(),'rosette','pratique','statut','remarques','geom']

def emb_correct(df):
    l1 = ['GIR3','AU7']
    l2 = ['BOI1','VIL10','VIL8a']
    l3 = ['MEA4a','ME']
    l4 = ['MO3','MA3','VIL14','BD1','VF','CHA2','CHA5','ROM3']
    l5 = ['BEL1','JA5','AU4']
    df.loc[df.index.isin(l1),'niv_embrous'] = 1
    df.loc[df.index.isin(l2),'%_embrous'] = 4
    df.loc[df.index.isin(l3),'%_embrous'] = 29
    df.loc[df.index.isin(l4),'%_embrous'] = 30
    df.loc[df.index.isin(l5),'%_embrous'] = 60
    return df

def sol_correct(df):
    # df.loc[df.index=='GEY1','%_recouvmnt'] = 19
    df.loc[df.index=='CHA5','%_recouvmnt'] = 21
    # df.loc[df.index=='GEY2','%_recouvmnt'] = 19
    # df.loc[df.index=='SN1','%_recouvmnt']  = 19
    df.loc[df.index=='AU1','%_recouvmnt']  = 21
    df.loc[(df.recouvmnt==1)&(df['%_recouvmnt']>0),'%_recouvmnt'] = 0
    df.loc[(df.recouvmnt==2)&(df['%_recouvmnt']>10),'%_recouvmnt'] = 10
    df.loc[(df.recouvmnt==3)&(df['%_recouvmnt']>20),'%_recouvmnt'] = 20
    df.loc[(df.recouvmnt==4)&(df['%_recouvmnt']<21),'%_recouvmnt'] = 21
    return df


# récupération des données
sql  = 'SELECT site_code,geom FROM ps.v_pelouseseches'
v_ps = gpd.read_postgis(sql, con)
df0   = gpd.read_file(path0 + file)
df0.columns = df0.columns.str.lower()
df0.rename(columns=dic, inplace=True)
df0.rename_geometry('geom', inplace=True)
df = df0[keep_cols].copy()
df.set_index('ident', inplace=True)
df.pratique.replace(dic_prat,inplace=True)
df.statut.replace(dic_stat,inplace=True)
df.auteur.replace(dic_auth,inplace=True,regex=True)
df.date = df.date.str.lower()
df.date.replace(dic_date,inplace=True)
df.date = gpd.pd.to_datetime(df.date)
df = emb_correct(df)
df = sol_correct(df)

# Correction code Corine Biotope
df.loc[df.code_hab2=='41D','code_hab2'] = '41.D'

# Concaténation des remarques
df.loc[~df.rmq_phyto.isna(),'remarques'] = df.loc[~df.rmq_phyto.isna(),'remarques'] +\
    '; rmq_phyto : ' + df.loc[~df.rmq_phyto.isna(),'rmq_phyto']
df.loc[~df.rosette.isna(),'remarques'] = df.loc[~df.rosette.isna(),'remarques'] +\
    '; rosette : ' + df.loc[~df.rosette.isna(),'rosette']
df.loc[~df.rmq_phytosocio.isna(),'remarques'] = df.loc[~df.rmq_phytosocio.isna(),'remarques'] +\
    '; rmq_phytosocio : ' + df.loc[~df.rmq_phytosocio.isna(),'rmq_phytosocio']
df.loc[~df.rmq_contract_agri.isna(),'remarques'] = df.loc[~df.rmq_contract_agri.isna(),'remarques'] +\
    '; rmq_contract_agri : ' + df.loc[~df.rmq_contract_agri.isna(),'rmq_contract_agri']
del df['rmq_phyto']
del df['rosette']
del df['rmq_phytosocio']
del df['rmq_contract_agri']


df.reset_index(drop=False,inplace=True)
df.rename(columns={'ident':'id_origine'}, inplace=True)
df['table_org'] = file
df['structure'] = 'CD Isère'

# END SCRIPT #


## tri des géométries avant insertion
# Identification des superpositions new_site/old_site
df_inters = gpd.sjoin(df,v_ps, how='left')
del df_inters['index_right']
lst_old_site = df_inters.site_code.unique()
v_ps = v_ps[v_ps.site_code.isin(lst_old_site)].copy()
# Calcul du recouvrement
df.reset_index(drop=False,inplace=True)
v_ps.loc[:,'surf'] = v_ps.area
tmp = gpd.overlay(v_ps,df[['ident','geom']],how='intersection',make_valid=True)
tmp['perc_rcvmt'] = 100*tmp.area/tmp['surf']
tmp_sitemaj = tmp[(tmp.perc_rcvmt > 10)&(~tmp.ident.duplicated(keep=False))].ident
tmp_sitenew = tmp[tmp.perc_rcvmt <= 10].ident.unique()


site_newvsold = {
    'old' : [*tmp.site_code.unique()],
    'new' : [*tmp.ident.unique()],
}

df_new = df_inters[df_inters.site_code.isna()]
df_inters.drop(df_new.index,inplace=True)
df_maj = df_inters[~df_inters.site_code.duplicated(keep=False)]
df_inters.drop(df_maj.index,inplace=True)
df_rep = df_inters.copy()

df_new = gpd.pd.concat([df_new,df_rep],axis=0)