#!/usr/bin/env python3
# -*- coding: UTF-8 -*-
#Nom :  : foncier_insert_table.py
#Description : Correspondance entre les sites et les parcelles cadastrales.
#Copyright : 2021, CEN38
#Auteur    : Colas Geier
#Version : 1.0

import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine #, func
# from sqlalchemy.orm import sessionmaker
import psycopg2
import datetime as dt
import sys
import gc
from pycen import con_fon


# Parametres généraux
epsg = '2154'
crs = 'EPSG:%s'%epsg
chunk = None
pci_annee = '2021'
matrice_annee = '2021'
start_time = dt.datetime.today()
def time_exec (init_time):
  time = dt.datetime.today() - init_time
  return str(time)

def get_data(table,schema,engine=con_fon,chunk=chunk):
    sql = "SELECT * FROM {0}.{1}".format(schema, table)
    tmp = gpd.read_postgis(
        sql = sql,
        con = con_fon,
        geom_col = 'geom',
        crs = crs,
        chunksize = chunk, )

    # Mise en forme des données
    # start_time = dt.datetime.today()
    if chunk:
        df = pd.concat(tmp, ignore_index=True)
    else:
        df = tmp.copy()
    
    return df

if __name__ == "__main__":

    ###############
    # Get sites
    tab_site = 'sites'
    sch_site = 'sites'
    tab_fon = 'cadastre_site'
    sch_fon = 'foncier'
    print('''IMPORT data from schema: '%s' , table : '%s' '''%(sch_site,tab_site))
    # sql = "SELECT * FROM {0}.{1}".format(sch_site, tab_site)
    sql = '''
        SELECT * FROM {0}.{1} 
        WHERE site_id = 'CRAS'
        --WHERE site_id NOT IN (SELECT DISTINCT site_id FROM {2}.{3})
        '''.format(sch_site, tab_site, sch_fon, tab_fon)
    site = gpd.read_postgis(
        # table_name = table_in,
        sql = sql,
        con = con_fon,
        geom_col = 'geom',
        # schema = schema_in,
        crs = crs,
        chunksize = chunk, )
    # union_site = gpd.GeoSeries(site.geom.cascaded_union)
    if site.empty:
        print('Pas de nouveaux sites à lier au cadastre ====> EXIT')
        sys.exit()

    #################
    # Get parcelles
    tab_parc = 'cadastre'
    sch_parc = 'parcelles'
    print('''IMPORT data from schema: '%s' , table : '%s' '''%(sch_parc,tab_parc))
    sql = """SELECT * FROM {sch}.{tab} WHERE ST_Intersects (geom, 'SRID={epsg};{poly}')
    AND par_id NOT IN (SELECT par_id FROM {sch}.parcelles_cen)""".format(
        sch=tab_parc, tab=sch_parc, epsg=epsg, poly=site.unary_union
    )
    parc = gpd.read_postgis(
        sql = sql,
        con = con_fon,
        geom_col = 'geom',
        crs = crs,
        chunksize = chunk, )

    parc_cent = parc.copy()
    # parc_cent.geom = parc_cent.representative_point()
    # parc_cent.geom = parc_cent.centroid
    res = gpd.sjoin(site, parc_cent, predicate='intersects')


    print('RUN fonction "import_parcelles_cen" pour {} lignes '.format(res.shape[0]))
    # lst_site = res.site_id.unique()
    start_time = dt.datetime.today()
    res['sql'] = "SELECT cadastre.import_parcelles_cen('"+res.par_id+"','"+res.site_id+"',"+str(start_time.year)+");"

    with con_fon.begin() as cnx:
        res['sql'].map(lambda x: cnx.execute(x))


    ###############
    # Get cptprop
    lst_parid = "','".join(res.par_id.unique())
    sql = '''
        SELECT * FROM cadastre.cadastre 
        JOIN cadastre.lots USING (lot_id) 
        WHERE lots.par_id IN ('{lst_parid}')'''.format(lst_parid=lst_parid)
    cptprop = pd.read_sql(
        sql = sql,
        con = con_fon,)
    cptprop.drop_duplicates('dnupro', inplace=True)

    start_time = dt.datetime.today()
    print('RUN fonction "import_cptprop_cen" pour {} lignes '.format(cptprop.shape[0]))


    cptprop['sql'] = "SELECT cadastre.import_cptprop_cen('"+cptprop.dnupro+"',"+str(start_time.year)+");"

    with con_fon.begin() as cnx:
        cptprop['sql'].map(lambda x: cnx.execute(x))
    time_exec(start_time)
    print('END fonction : import_cptprop_cen .......... %s'%time_exec(start_time))

    site_id = 'GDMA'
    with con_fon.begin() as cnx:
        cnx.execute("SELECT admin_sig.refresh_mview_foncier('{site_id}')"\
            .format(
                site_id=site_id
            )
        )