from pycen import con


url = 'https://wxs.ign.fr/agriculture/geoportail/wfs?SERVICE=WFS&VERSION=2.0.0&REQUEST=GetCapabilities'
layer = 'RPG.2020:parcelles_graphiques.title'
wpr_name = 'rpgwfs'
tab_name = 'rpg2020_parcellaire_graphique'

drop_fgn = 'DROP SERVER IF EXISTS %s CASCADE;'%wpr_name
fgn_data_wrapper = '''
CREATE SERVER IF NOT EXISTS %s
	FOREIGN DATA WRAPPER ogr_fdw
	OPTIONS (datasource 'WFS:%s', format 'WFS')
'''%(wpr_name,url)

table = '''
DROP FOREIGN TABLE IF EXISTS {table};
CREATE FOREIGN TABLE IF NOT EXISTS flux_geo.{table} (
	id_parcel varchar,
	surf_parcel float,
	code_cultu varchar,
	code_group varchar,
	culture_d1 varchar,
	culture_d2 varchar,
	geom public.geometry(multipolygon, 4326)
)
SERVER {srv}
OPTIONS (layer '{layer}');
'''.format(table=tab_name,srv=wpr_name,layer=layer)

with con.begin() as cnx:
	cnx.execute(drop_fgn)
with con.begin() as cnx:
	cnx.execute(fgn_data_wrapper)

with con.begin() as cnx:
	cnx.execute(table)

#######
#######
from pycen import con_anm as con
from sqlalchemy import text
db = 'bd_cen'
host = '91.134.194.221'
port = '5432'
srv_name = 'fdw_bd_cen'
tab_name = 'communes'
sch_name = 'territoire'
view_name = 'v_'+tab_name
mview_name = 'vm_'+tab_name
user = 'cen_admin'
pwd = '#CEN38@venir'


create_ext = 'CREATE EXTENSION IF NOT EXISTS postgres_fdw;'
drop_fgn = 'DROP SERVER IF EXISTS %s CASCADE;'%srv_name
fgn_data_wrapper = '''
CREATE SERVER IF NOT EXISTS %s
	FOREIGN DATA WRAPPER postgres_fdw
	OPTIONS (dbname '%s', host '%s', port '%s')
'''%(srv_name,db,host,port)

fgn_user_wrapper = '''
CREATE USER MAPPING IF NOT EXISTS FOR %s
        SERVER %s
        OPTIONS (user '%s', password '%s');
'''%(user,srv_name,user,pwd)

table = '''
CREATE FOREIGN TABLE IF NOT EXISTS {sch_name}.{tab_name} (
	code_insee varchar(5),
	id varchar(24),
	prec_plani float8,
	nom varchar(45),
	statut varchar(20),
	canton varchar(45),
	arrondisst varchar(45),
	depart varchar(30),
	region varchar(30),
	popul int4,
	multican varchar(3),
	actif bool,
	epfl varchar(10),
	geom geometry(geometry,2154)
)
	SERVER {fgn_server}
	OPTIONS (schema_name 'administratif', table_name 'communes');
'''.format(
sch_name=sch_name, tab_name=tab_name, fgn_server=srv_name
)

vm = '''
CREATE MATERIALIZED VIEW {sch_name}.{mview_name} AS
SELECT * FROM {sch_name}.{tab_name};

GRANT SELECT ON {sch_name}.{mview_name} TO grp_consult;
GRANT SELECT ON {sch_name}.{mview_name} TO cen_user;
'''.format(sch_name=sch_name, mview_name=mview_name, tab_name=tab_name)
with con.begin() as cnx:
	# cnx.execute(create_ext)
	# cnx.execute(drop_fgn)
	# cnx.execute(fgn_data_wrapper)
	# cnx.execute(fgn_user_wrapper)
	cnx.execute(text(table))

with con.begin() as cnx:
	cnx.execute(text(vm))