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

import geopandas as gpd
import pandas as pd
from geoalchemy2 import Geometry
from sqlalchemy.engine import URL
from sqlalchemy import create_engine 
import sys


# Liste des sites
lst_site = ['GOUT']

# Suppression des champs souhaités
delete = True
lst_cols_del = ['geometrie','date_debut_obs']

# Format d'extraction ['xls', 'xlsx', 'csv']
FORMAT = 'xlsx'
# Localistation du fichier de sortie
OUTPUT = '~/Documents/tmp/Jean_Luc/'
# Nom du fichier de sortie
NAME_OUT = 'observation_site_GOUT'


# Parametres bdd HOMER (out)
user_hom   = 'cen_admin'
pwd_hom    = '#CEN38@venir'
# user_hom   = 'cgeier'
# pwd_hom    = 'adm1n*sIcen'
adr_hom    = '91.134.194.221'
port_hom   = '5432'
base_hom   = 'sicen2'
schema_hom = 'saisie'
table_hom  = 'saisie_observation'
url_hom = URL.create('postgresql+psycopg2', 
  username=user_hom,
  password=pwd_hom,
  host=adr_hom,
  database=base_hom,
)
engine_hom = create_engine(url_hom)
# engine_hom = create_engine('postgresql+psycopg2://{0}:{1}@{2}:{3}/{4}'.format(user_hom,pwd_hom,adr_hom,port_hom,base_hom), echo=False)

# Parametres bdd BART (in)
user_bart = 'cen_admin'
pwd_bart  = '#CEN38@venir'
adr_bart  = '192.168.0.3'
port_bart = '5432'
base_bart = 'bd_cen38'
schema_bart = 'sites'
table_bart = "c_sites_zonages"
url_bart = URL.create('postgresql+psycopg2', 
  username=user_bart,
  password=pwd_bart,
  host=adr_bart,
  database=base_bart,
)
con_bart = create_engine(url_bart)
# con_bart = create_engine('postgresql+psycopg2://{0}:{1}@{2}:{3}/{4}'.format(user_bart,pwd_bart,adr_bart,port_bart,base_bart), echo=False)

epsg = '2154'
crs  = 'EPSG:%s'%epsg



sql = "SELECT geom FROM {sch}.{tab} WHERE code_site in ('{lst_site}') AND type_zonage = 'ZO'".format(sch=schema_bart, tab=table_bart, lst_site="','".join(lst_site))
gdf_site = gpd.read_postgis(
    sql = sql,
    con = con_bart,
)
if gdf_site.shape[0] > 1 :
    union  = gdf_site.geom.cascaded_union
    Sunion = gpd.GeoSeries(union)
    gdf_union = gpd.GeoDataFrame(Sunion, geometry='geom', columns=['geom'], crs=crs)
    gdf_site = gdf_union


sql = '''SELECT * FROM {sch}.{tab} WHERE ST_Intersects (geometrie, 'SRID={epsg};{poly}')'''.format(sch=schema_hom, tab=table_hom, epsg=epsg, poly=gdf_site.geom[0])
gdf_saisie = gpd.read_postgis(
    sql = sql,
    con = engine_hom,
    geom_col = 'geometrie'
)

df = pd.DataFrame(gdf_saisie)
if delete:
    df.drop(columns= lst_cols_del, inplace=True)

if FORMAT in ['xls', 'xlsx']:
    df.to_excel(OUTPUT + NAME_OUT + '.' + FORMAT)
if FORMAT == 'csv':
    df.to_file(OUTPUT + NAME_OUT + '.' + FORMAT)

sys.exit('END')