# -*- coding: utf-8 -*-

import psycopg2 as psypg
import time as time
import sys as sys
import psycopg2.extras as psyex
import datetime as dtime
import json
import glob as glob
import pandas as pd
import os as os
import argparse as agp


def connectdb():
	print(os.environ)
	connDb=psypg.connect(dbname=os.environ['PG_DATABASE'], user=os.environ['PG_USERNAME'],password=os.environ['PG_PASSWORD'],host=os.environ['PG_HOST'],port=os.environ['PG_PORT'])
	return connDb

def disconnectdb(connexion):
	connexion.close()

def createTempTable(curInsert,nameTable):
	dropTempTable(curInsert,nameTable)
	if nameTable=="temp_data_pcv":
		sql="CREATE TABLE "+nameTable+ \
		"""(identifiant_cvi CHARACTER VARYING(15), commune_id_cvi CHARACTER VARYING(20), section_cvi CHARACTER VARYING(10), prefix_cvi INTEGER,
		numero_cvi INTEGER, dsf_cvi INTEGER, owner_name CHARACTER VARYING(256), lieudit_id CHARACTER VARYING(10), contenance_cvi NUMERIC,
		commune_id_cvi_insee CHARACTER VARYING(20),identifiant_cadastre CHARACTER VARYING(14),
		commune_id_cadastre CHARACTER VARYING(10), prefix_cadastre CHARACTER VARYING(10), section_cadastre CHARACTER VARYING(10), numero_cadastre INTEGER,
		contenance_cadastre NUMERIC, date_created TIMESTAMP, date_updated TIMESTAMP, geom_4326 CHARACTER VARYING, commune_id INTEGER);"""
	elif nameTable=="temp_data_spcv":
		sql="CREATE table "+nameTable+ \
		""" (identifiant_pcv_cvi CHARACTER VARYING(15), num_spcv INTEGER, exploit_id INTEGER, cepage CHARACTER VARYING(8),
       portegreffe CHARACTER VARYING(8), bailleur_nature_id INTEGER, owner_farmer_bailleur_id INTEGER,
       plant_year INTEGER, superficie NUMERIC, ecart_rang NUMERIC, ecart_pied NUMERIC,id INTEGER,
       code CHARACTER VARYING(25), EVV CHARACTER VARYING(25), name CHARACTER VARYING(255), email CHARACTER VARYING(255), parcel_id INTEGER, farmer_id INTEGER);"""
	elif nameTable=="temp_data_exploit":
		sql="CREATE TABLE "+nameTable+ \
		""" (id INTEGER, code CHARACTER VARYING(25), EVV CHARACTER VARYING(25), name CHARACTER VARYING(255), email CHARACTER VARYING(255),
			civilite CHARACTER VARYING(10), address_name CHARACTER VARYING(255), address_name2 CHARACTER VARYING(255), address CHARACTER VARYING(255), address_suppl CHARACTER VARYING(255),
			code_insee INTEGER, city CHARACTER VARYING(255));"""

	curInsert.execute(sql)

def dropTempTable(curInsert,nameTable):
	sql="DROP TABLE IF EXISTS "+nameTable+";"
	curInsert.execute(sql)

def insertExploit(dataExploit,curInsert):
	time_start=time.time()
	createTempTable(curInsert,"temp_data_exploit")
	sql = """INSERT INTO temp_data_exploit  VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"""

	psyex.execute_batch(curInsert,sql,dataExploit.values)
	print("process of insert into temp temp_data_exploit in " + str(time.time()-time_start) +"s")

	#TODO update information
	time_start=time.time()
	sql = """INSERT INTO users (id,username,num_evv,firstname,email,settings,role,address,address_suppl,code_insee,city,address_name, address_name2, civilite)
	SELECT nextval('users_id_seq'), LPAD(code,5,'0'), EVV, name,email,'[]','ROLE_USER',address,address_suppl,code_insee,city,address_name, address_name2, civilite from temp_data_exploit
	ON CONFLICT (username) DO UPDATE SET email=excluded.email,
	        firstname = excluded.firstname,
			address =  excluded.address ,
			address_suppl = excluded.address_suppl,
			code_insee=excluded.code_insee,
			city=excluded.city,
			address_name=excluded.address_name,
            address_name2=excluded.address_name2,
            civilite=excluded.civilite;"""

	curInsert.execute(sql)


	dropTempTable(curInsert,"temp_data_exploit")

	print("process of exploit in " + str(time.time()-time_start) +"s")


def checkCsvFiles(listCsvFiles):
	if len(listCsvFiles)>1:
		print('too many csv files to process')
		sys.exit()
	listTest=[]
	for row in listCsvFiles:
		listTest.append(row.split('/')[-1].split('_')[1])
	print(listTest)
	if 'EXPL' not in listTest:
		return False
	else:
		return True

if __name__ == '__main__':

	parser=agp.ArgumentParser('sentinel process')
	parser.add_argument("-d", "--dir", help="directory with files to import",default='', type=str)

	args = parser.parse_args()
	print(args)

	if args.dir=='':
		print('you must define directory that contains csv files')
		sys.exit()

	listCsvFiles=glob.glob(args.dir+'/*.csv')
	if not checkCsvFiles(listCsvFiles):
		print('you must have three files PCV, SPCV, EXPLOIT')
	else:
		print('three files will be processed : ',listCsvFiles)

	for row in listCsvFiles:
		print(row)
		if 'EXPL' in row:
			dfExploit=pd.read_csv(row,delimiter=';')
			dfExploit=dfExploit.fillna(psypg.extensions.AsIs('NULL'))
			print(dfExploit.columns)


	connDb=connectdb()
	print("Database connexion ok")
	curInsert= connDb.cursor()
	insertExploit(dfExploit,curInsert)

	connDb.commit()
	curInsert.close()
	disconnectdb(connDb)