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

from sqlalchemy import create_engine #, text
from sqlalchemy.engine import URL
import geopandas as gpd
import pandas as pd
from pycen import update_to_sql
from pycen.tools import Polygons_to_MultiPolygon
from datetime import datetime as dt

path = '/home/colas/Documents/9_PROJETS/0_FONCIER/MAJ_SITE/'
site = 'ARSE'
f_zi = 'extension_PL_V2.shp'
f_zo = 'extension_PL_V2.shp'

path_file = path+site+'/'+f_zi
typ_zon = 'ZO'
id = 117 
nom_site = "Tourbière de l'Arselle et le lac Achard"

# Parametres bdd
user = 'cgeier'
pwd  = 'adm1n*bdCen'
adr  = '192.168.0.189'
port = '5432'
base = 'bd-cen-38'

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

keep_col = [
    'id','code_site','type_zonage','nom_site',
    'date_maj','surface_ha','geom'
    ]

gdf = gpd.read_file(path_file)
gdf.columns = gdf.columns.str.lower()
gdf.rename_geometry('geom', inplace=True)
if 'Polygon' in gdf.geom_type.unique():
    gdf = Polygons_to_MultiPolygon(gdf)
# gdf['geom'] = gdf.unary_union
gdf['date_maj'] = dt.now().date()
gdf['date_maj'] = pd.to_datetime(gdf['date_maj'])
gdf['surface_ha'] = (gdf.area / 10000).round(2)
gdf['type_zonage'] = typ_zon
gdf['id'] = id
gdf['nom_site'] = nom_site


kp_col = gdf.columns[gdf.columns.isin(keep_col)]
gdf = gdf[kp_col]
# gdf = gdf.drop_duplicates()

update_to_sql(
    gdf, 
    con, 
    table_name='c_sites_zonages', 
    schema_name='sites', 
    key_name='id'
)