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

from pycen.tools import Polygons_to_MultiPolygon
from pycen import con
import geopandas as gpd
from sqlalchemy import create_engine, text
from geoalchemy2 import Geometry

user_cad   = 'cgeier'                 # utilisateur de connexion à la bdd
pwd_cad    = 'adm1n*bdCen'            # mot de passe de la bdd
adr_cad    = '91.134.194.221'               # adresse ip de la bdd
port_cad   = '5432'                     # port de la bdd
base_cad   = 'rhomeo'                 # nom de la bdd
schema_cad = 'bdtopo3'        
con = create_engine('postgresql+psycopg2://{0}:{1}@{2}:{3}/{4}'.format(user_cad,pwd_cad,adr_cad,port_cad,base_cad), echo=False)



file = '/media/colas/Disk2/5_BDD/RPG/1_DONNEES_LIVRAISON_2021/RPG_2-0_SHP_LAMB93_R84_2021-01-01/ILOTS_ANONYMES.shp'
dic = {
  'CD_SSBV': 'cdssbv',
  'LIB_SSBV': 'nom',
  'CD_COMGEO': 'cd_comgeo',
  'CD_CTB': 'cd_ctb',
  'CD_': 'cd_',
  'CD_SUBUNIT': 'cd_subunit',
  'geometry': 'geom',
}

name_table = 'ilots_anonymes_2021'
name_schema = 'rpgnew'
# Parametres bdd OUT
# user_zh = 'postgres'
# pwd_zh  = 'tutu'
# adr_zh  = '192.168.60.10'
# base_zh = 'bd_cen'
# con_zh  = create_engine('postgresql+psycopg2://{0}:{1}@{2}/{3}'.format(user_zh,pwd_zh,adr_zh,base_zh), echo=False)


select_cols = list(dic.values())

df = gpd.read_file(file)
# df = df.set_geometry('geom')
df.rename_geometry('geom', inplace=True)
df.columns = df.columns.str.lower()

if not df.crs:
  df.set_crs(epsg=2154, inplace=True)

if df.crs.srs != 'epsg:2154':
  df.to_crs(epsg=2154, inplace=True)

# df.rename(columns=dic, inplace=True)
# df = df[select_cols]


if 'id' not in df.columns:
  df.index.name = 'id'
  df.reset_index(drop=False, inplace=True) 


if 'Polygon' in df.geom_type.unique() and 'MultiPolygon' in df.geom_type.unique():
  df = Polygons_to_MultiPolygon(df)
  # from shapely.geometry.multipolygon import MultiPolygon
  # tmp  = df.loc[df.geom_type == 'Polygon'].copy()
  # geom = [MultiPolygon([x]) for x in tmp.loc[tmp.geom_type == 'Polygon','geom']]
  # tmp  = tmp.set_geometry(geom)
  # df = gpd.pd.concat([df.drop(tmp.index), tmp]).sort_values('id').reset_index(drop=True)
    
if 'LineString' in df.geom_type.unique() and 'MultiLineString' in df.geom_type.unique():
  from shapely.geometry.multilinestring import MultiLineString
  tmp  = df.loc[df.geom_type == 'LineString'].copy()
  geom = [MultiLineString([x]) for x in tmp.loc[tmp.geom_type == 'LineString','geom']]
  tmp  = tmp.set_geometry(geom)
  df = gpd.pd.concat([df.drop(tmp.index), tmp]).sort_values('id').reset_index(drop=True)

con.begin()
df.to_postgis(
  name = name_table,
  con  = con,
  schema = name_schema,
  if_exists = 'replace',
  chunksize = 100000,
  dtype={'geom':Geometry(srid=2154)}
  )

sql = """ALTER TABLE {sch}.{tab} OWNER TO cen_admin;
GRANT ALL ON TABLE {sch}.{tab} TO cen_admin;
GRANT ALL ON TABLE {sch}.{tab} TO grp_admin;
--GRANT SELECT ON TABLE {sch}.{tab} TO grp_consult;
""".format(sch=name_schema,tab=name_table)
with con.begin() as cnx:
  cnx.execute(sql)

sql = """
REVOKE SELECT ON TABLE {sch}.{tab} FROM grp_consult;
""".format(sch=name_schema,tab=name_table)
with con.begin() as cnx:
  cnx.execute(sql)

if 'rpg' in name_table:

  v_tab = 'v_'+name_table.replace('reg','')+'isere'
  lst_col = ','.join(tuple('s.'+df.columns))

  sql = """CREATE OR REPLACE VIEW {sch}.{v_tab}
  AS SELECT {cols}
    FROM {sch}.{tab} s,
      ref_territoire.dept_isere reg
    WHERE st_intersects(s.geom, reg.geom);

  ALTER TABLE {sch}.{v_tab} OWNER TO cen_admin;
  GRANT ALL ON TABLE {sch}.{v_tab} TO cen_admin;
  GRANT ALL ON TABLE {sch}.{v_tab} TO grp_admin;
  GRANT SELECT ON TABLE {sch}.{v_tab} TO grp_consult;""".format(sch=name_schema,tab=name_table,v_tab=v_tab,cols=lst_col)
  with con.begin() as cnx:
    cnx.execute(sql)