from pycen import zh as ZH
import geopandas as gpd
import pandas as pd

from sqlalchemy import create_engine
from sqlalchemy.engine import URL

import os

zh = ZH()
# isin_bdd = True
# # Parametres bdd CEN38 OUT
user = 'cen_admin'
pwd  = "#CEN38@venir"
adr  = '91.134.194.221'
base = 'azalee_restore'
url  = URL.create("postgresql+psycopg2", username=user, password=pwd, host=adr, database=base)
con  = create_engine(url)

lst_table = [
    'r_sites_geom',
    'r_site_critdelim',
    'r_site_fctecosociopatri',
    'r_site_habitat',
    'r_site_reghydro',
    'r_site_sub',
    'r_site_type_connect',
    'r_site_usageprocess',
]

result = {}
for table in lst_table:
  sql_old = "SELECT max(t.id) FROM {sch}.{tab} t".format(tab=table,sch='sites' if table == 'r_sites_geom' else 'zones_humides')
  if table == 'r_sites_geom':
    sql_old += ' JOIN sites.sites ON sites.id = t.id_site AND sites.id_type_milieu = 1'
  # else:
  #   sql_old += ''' JOIN sites.sites ON sites.id = r_sites_geom.id_site AND sites.id_type_milieu = 1'''

  id_old = pd.read_sql(sql_old,con)

  sql = "SELECT t.* FROM {sch}.{tab} t".format(tab=table,sch='sites' if table == 'r_sites_geom' else 'zones_humides')
  if table == 'r_sites_geom':
    sql += ' JOIN sites.sites ON sites.id = t.id_site AND sites.id_type_milieu = 1'
  
  sql += " WHERE t.id > {id} AND id_site != '26PNRV0208'".format(id=id_old.values[0][0])
  result[table] = pd.read_sql(sql,zh.con)

  if table == 'r_sites_geom':
    sql_lot = "SELECT * FROM sites.lots WHERE id_lot IN {id}".format(id=tuple(result[table].id_lot.dropna().unique()))
    lot = pd.read_sql(sql_lot,zh.con).dropna(how='all',axis=1)
    result[table] = result[table].merge(lot, on='id_lot',how='left')

r_geo = result['r_sites_geom']

sql = '''
with lots as (
SELECT DISTINCT ON (id_site)
    id_site,lots.*
    FROM sites.r_sites_geom
    LEFT JOIN sites.lots USING (id_lot)
    ORDER  BY id_site, "date" DESC
)
SELECT v.*, lots.libelle FROM zones_humides.v_zoneshumides v
JOIN lots ON v.site_code = lots.id_site
WHERE v.site_code IN {lst_code}
'''.format(lst_code=str(tuple(r_geo.id_site.unique())))
vzh = gpd.read_postgis(sql,zh.con).dropna(subset=['libelle']).dropna(how='all',axis=1)
vzh_old = gpd.read_postgis('SELECT * FROM zones_humides.v_zoneshumides',con).dropna(how='all',axis=1)
vzh_new = gpd.read_postgis('SELECT * FROM zones_humides.v_zoneshumides',zh.con).dropna(how='all',axis=1)

date_cols = vzh.columns[vzh.columns.str.contains('date|heure')]
vzh[date_cols] = vzh[date_cols].astype(str)
vzh_old[['old_code',*date_cols]] = vzh_old[['old_code',*date_cols]].astype(str)
vzh_new[['old_code',*date_cols]] = vzh_new[['old_code',*date_cols]].astype(str)


PATH = '/home/colas/Documents/9_PROJETS/1_ZH/2024'
vzh_old.to_file(os.path.join(PATH,'20250415_avancement.gpkg'),driver='GPKG',layer='Inventaire_initiale')
vzh_new.to_file(os.path.join(PATH,'20250415_avancement.gpkg'),driver='GPKG',layer='Inv_actualisee_20250415')
for _lib in vzh.libelle.unique():
  lib = (_lib.removesuffix('2024')
    .removeprefix('ZH')
    .replace('actualisation','CENIsere'))

  tmp = vzh[vzh.libelle == _lib]
  new = tmp[~tmp.site_code.isin(vzh_old.site_code.tolist())]
  upt = tmp[tmp.site_code.isin(vzh_old.site_code.tolist())]
  if not new.empty:
    print(f'NEW {lib} : ',str(new.site_code.tolist()))
    # new.to_file(os.path.join(PATH,'20250415_avancement.gpkg'),driver='GPKG',layer='newzh'+lib+str(new.shape[0]))
  if not upt.empty:
    print(f'UPDT {lib} : ',str(upt.site_code.tolist()))
    # upt.to_file(os.path.join(PATH,'20250415_avancement.gpkg'),driver='GPKG',layer='majzh'+lib+str(upt.shape[0]))

vzh_reduc = (vzh[vzh.site_code.isin(vzh_old.site_code.tolist())].sort_values('site_code').set_index('site_code').area.sub(vzh_old[vzh_old.site_code.isin(vzh.site_code.tolist())].sort_values('site_code').set_index('site_code').area,fill_value=0))
vzh_reduc[vzh_reduc<0].sum() / 10000
vzh_reduc[vzh_reduc>0].sum() / 10000

area_maj = (vzh[vzh.site_code.isin(vzh_old.site_code.tolist())].area.sum() - vzh_old[vzh_old.site_code.isin(vzh.site_code.tolist())].area.sum()) / 10000
area_new = vzh[~vzh.site_code.isin(vzh_old.site_code.tolist())].area.sum() / 10000

cbna = vzh[vzh.libelle=='ZH_CBNA_2024']
new_cbna = cbna[(~cbna.site_code.isin(vzh_old.site_code.tolist()))&(vzh.libelle=='ZH_CBNA_2024')].area.sum() / 10000
maj_cbna = (cbna[cbna.site_code.isin(vzh_old.site_code.tolist())].area.sum() - vzh_old[vzh_old.site_code.isin(cbna.site_code.tolist())].area.sum()) / 10000

pgszh = vzh[vzh.libelle=='ZH_pgszhBelledonne_2024']
new_pgszh = pgszh[(~pgszh.site_code.isin(vzh_old.site_code.tolist()))&(vzh.libelle=='ZH_pgszhBelledonne_2024')].area.sum() / 10000
maj_pgszh = (pgszh[pgszh.site_code.isin(vzh_old.site_code.tolist())].area.sum() - vzh_old[vzh_old.site_code.isin(pgszh.site_code.tolist())].area.sum()) / 10000

evin = vzh[vzh.libelle=='ZH_EVINERUDE_2024']
new_evin = evin[(~evin.site_code.isin(vzh_old.site_code.tolist()))&(vzh.libelle=='ZH_EVINERUDE_2024')].area.sum() / 10000
maj_evin = (evin[evin.site_code.isin(vzh_old.site_code.tolist())].area.sum() - vzh_old[vzh_old.site_code.isin(evin.site_code.tolist())].area.sum()) / 10000

lst_attrs = []
r_geo = result['r_sites_geom']
for table in result.keys():
  if table == 'r_sites_geom':
    continue
  df = result[table]
  no_geo = df[~df.id_site.isin(r_geo.id_site.unique())]
  lst_attrs += no_geo.id_site.unique().tolist()

lst_attrs = [x for x in list(set(lst_attrs)) if x is not None]

r_crit = result['r_site_critdelim']
r_fct = result['r_site_fctecosociopatri']
r_hab = result['r_site_habitat']
r_hyd = result['r_site_reghydro']
r_sub = result['r_site_sub']
r_connect = result['r_site_type_connect']
r_usg = result['r_site_usageprocess']

new_r_crit = r_crit[r_crit.id_site.isin(lst_attrs)]
new_r_fct = r_fct[r_fct.id_site.isin(lst_attrs)]
new_r_hab = r_hab[r_hab.id_site.isin(lst_attrs)]
new_r_hyd = r_hyd[r_hyd.id_site.isin(lst_attrs)]
new_r_sub = r_sub[r_sub.id_site.isin(lst_attrs)]
new_r_connect = r_connect[r_connect.id_site.isin(lst_attrs)]
new_r_usg = r_usg[r_usg.id_site.isin(lst_attrs)]

len_new_crit = new_r_crit.shape[0]
len_new_fct = new_r_fct.shape[0]
len_new_hab = new_r_hab.shape[0]
len_new_hyd = new_r_hyd.shape[0]
len_new_sub = new_r_sub.shape[0]
len_new_connect = new_r_connect.shape[0]
len_new_usg = new_r_usg.shape[0]

lst_new_crit = new_r_crit.id_site.unique().tolist()
lst_new_fct = new_r_fct.id_site.unique().tolist()
lst_new_hab = new_r_hab.id_site.unique().tolist()
lst_new_hyd = new_r_hyd.id_site.unique().tolist()
lst_new_sub = new_r_sub.id_site.unique().tolist()
lst_new_connect = new_r_connect.id_site.unique().tolist()
lst_new_usg = new_r_usg.id_site.unique().tolist()

tmp = gpd.read_file('/home/colas/Documents/9_PROJETS/1_ZH/MAJ/Actu 2024/BE EPODE - LEZE/KIT INVENTAIRE38 MODIFIE/a envoyer prêt/GEOM/38CG0105_Epode2025.gpkg')
tmp_old = gpd.read_postgis('SELECT * FROM zones_humides.v_zoneshumides WHERE site_code = \'38CG0105\'',con)