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

from sqlalchemy import create_engine, text
from sqlalchemy.engine import URL
from datetime import datetime as dt
from pycen import update_to_sql
import pandas as pd
import geopandas as gpd
from sqlalchemy.sql.expression import column


nom_numerateur = 'GEIER'
pnom_numerator = 'Colas'
today = dt.now().date().isoformat()
# Parametres bdd
user = 'cen_admin'
pwd  = '#CEN38@venir'
adr  = '91.134.194.221'
port = '5432'
base = 'sicen2'

url = URL.create('postgresql+psycopg2', 
  username=user,
  password=pwd,
  host=adr,
  database=base,
)
con = create_engine(url)

# FONCTION
def get_numerator(nom,pnom,id_structure=1):
  nom_numerateur = nom.upper()
  pnom_numerator = pnom[0].upper() + pnom[1:]
  query_get_numerator = """
      SELECT * FROM md.personne
      WHERE nom = '%s' 
      AND prenom = '%s' 
      AND id_structure = %i
  """ % (nom_numerateur,pnom_numerator,id_structure)
  numerateur = pd.read_sql_query(
      sql = query_get_numerator,
      con = con
  )
  return numerateur

# SELECT OBSERVATION
sql = 'SELECT * FROM saisie.saisie_observation'
saisie = gpd.read_postgis(
    sql,
    con,
    'geometrie')
saisie.sort_values('id_obs', inplace=True)

# SELECT COMMUNE
sql = 'SELECT * FROM ign_bd_topo.commune'
com = gpd.read_postgis(
    sql,
    con,
    'geometrie')


# JOINTURE spatiale
df = gpd.sjoin(saisie[['id_obs','code_insee','geometrie']],com[['code_insee','geometrie']],op='within')
df.sort_values('id_obs', inplace=True)
tmp = df[~df.code_insee_left.eq(df.code_insee_right)].copy()
tmp.rename(columns={'code_insee_right':'code_insee'}, inplace=True)
# UPDATE SET new code_insee
update_to_sql( 
  tmp[['id_obs','code_insee']],
  con=con,
  table_name='saisie_observation',
  schema_name='saisie',
  key_name='id_obs',
  geom_col='geometrie'
)


# UPDATE utilisateur OF saisie.suivi_saisie_observation
numerateur = get_numerator(nom_numerateur,pnom_numerator)
update_structure_name = """
UPDATE saisie.suivi_saisie_observation a
SET utilisateur = REPLACE(utilisateur, 'inconnu', '{email_numerator}')
WHERE date_operation > '{today}'
;
""".format(
    email_numerator=numerateur.email.item(),
    today = today
    )
with con.begin() as cnx:
    cnx.execute(update_structure_name)