from pycen import con_fon, con_gn
import geopandas as gpd

zh = gpd.read_postgis('SELECT * FROM gn_exports.v_synthese_zh_rhomeosite', con_gn)

_ens = gpd.read_postgis('SELECT * FROM sites.sites', con_fon,geom_col='geom')
_ens.sort_values('site_id',inplace=True)
_ens.site_id = _ens.site_id.str.replace('_ZI','')
site = gpd.read_postgis('SELECT * FROM _tdbfcen.vm_sites_cen_2026_shp',con_fon)
site['area_mastery'] = site.area / 10000


ens = _ens[_ens.site_id.isin(site.id_site_cen.tolist())]
# ens = gpd.pd.concat([ens,_ens[(_ens.site_id.str.strip('_ZO').isin(site[~site.id_site_cen.isin(ens.site_id)].id_site_cen.tolist()))]])
# ens.site_id = ens.site_id.str.strip('_ZO')
ens['area_total'] = ens.area / 10000
zh_over_ens = zh.overlay(ens).dissolve('site_id')
zh_over_ens['area_zh_total'] = zh_over_ens.area / 10000
zh_over_ens.to_file('/home/cgeier/Téléchargements/foret_sitecen/overlay_ens_zh.geojson',driver='GeoJSON')

foret = gpd.read_file('/home/cgeier/Téléchargements/foret_sitecen/ign_masque_foret.geojson')
over = foret.overlay(site).dissolve('id_site_cen')
over['area_forest_master'] = over.area / 10000
over.to_file('/home/cgeier/Téléchargements/foret_sitecen/overlay_sites_foret.geojson',driver='GeoJSON')

peup = gpd.read_file('/home/cgeier/Téléchargements/foret_sitecen/peuplier_onsites.geojson')
p_over = peup.overlay(site).dissolve('id_site_cen')
p_over['area_peuplier_master'] = p_over.area / 10000
p_over.to_file('/home/cgeier/Téléchargements/foret_sitecen/overlay_sites_peupleraie.geojson',driver='GeoJSON')

zh_over = zh.overlay(site).dissolve('id_site_cen')
zh_over['area_zh_master'] = zh_over.area / 10000
zh_over.to_file('/home/cgeier/Téléchargements/foret_sitecen/overlay_site_maitrise_zh.geojson',driver='GeoJSON')

df = (
    ens[['site_id','site_nom','geom','area_total']]
        .merge(over[['area_mastery','area_forest_master']].reset_index(drop=False), how='left', right_on='id_site_cen',left_on='site_id')
        .drop(columns='id_site_cen')
        .merge(p_over[['area_peuplier_master']].reset_index(drop=False), how='left', right_on='id_site_cen',left_on='site_id')
        .drop(columns='id_site_cen')
        .merge(zh_over_ens[['area_zh_total']].reset_index(drop=False), on='site_id', how='left')
        .merge(zh_over[['area_zh_master']].reset_index(drop=False), how='left', right_on='id_site_cen',left_on='site_id')
        .drop(columns='id_site_cen')
        .sort_values('site_id')
        .fillna(0)
)
df = df[~df.site_id.isin(['CACH','N2GL','N2IP','RNGL','RNIP','NDDM'])]

df.to_file('/home/cgeier/Téléchargements/foret_sitecen/sites_foret.geojson',driver='GeoJSON')