#!/usr/bin/env python3
# -*- coding: UTF-8 -*-.
from pycen import con
# import pycen
drop  = '''
DROP TRIGGER IF EXISTS trigger_insert_new_zh ON gn_pr_zh.t_zh;
DROP FUNCTION IF EXISTS zones_humides.insert_new_zh();
'''

s2 = '''CREATE OR REPLACE FUNCTION zones_humides.insert_new_zh()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN

		INSERT into sites.sites (id,nom,date_deb,id_typo_sdage,remarques)
		VALUES (new.code,new.main_name,new.create_date,(SELECT cd_nomenclature FROM gn_pr_zh.t_nomenclatures WHERE id = new.id_sdage),NULL);
		INSERT into sites.r_sites_geom (id_site,geom,date,id_lot,id_origine)
		VALUES (new.code,ST_TRANSFORM(new.geom,2154),new.create_date,NULL,new.zh_uuid::text);

   	RETURN NULL;
END;
$function$
;'''

s1 = '''CREATE TRIGGER trigger_insert_new_zh 
AFTER INSERT OR UPDATE
ON gn_pr_zh.t_zh 
FOR EACH ROW 
EXECUTE FUNCTION zones_humides.insert_new_zh();
'''

with con.begin() as cnx:
    cnx.execute(drop)
    cnx.execute(s2)
    cnx.execute(s1)