from sqlalchemy import text
from pycen import con_fon

v_veillefonciere = """
DROP VIEW IF EXISTS _tdb_f6cen.v_veillefonciere;
CREATE OR REPLACE VIEW _tdb_f6cen.v_veillefonciere
AS 
SELECT 
    row_number()  OVER (ORDER BY SPLIT_PART(site_id,'_',1) ASC) AS gid,
    SPLIT_PART(site_id,'_',1) AS site_id,
    SPLIT_PART(site_nom,' - Z',1) AS site_nom,
    enjeu_lib,
    'CONSERVATOIRE D''ESPACES NATURELS ISERE'::text AS cen,
    38::int AS dept,
    st_area(st_union(geom_vf)) AS superficie,
    st_union(geom_vf) geom
FROM sites.sites
JOIN sites.d_enjeux USING (enjeu_id)
WHERE veillefonciere
GROUP BY 2,3,4
ORDER BY 1
; 
"""

with con_fon.begin() as cnx:
    cnx.execute(text(v_veillefonciere))