import importlib
import json
import logging
import os

import click
from sqlalchemy import create_engine
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import sessionmaker, scoped_session
import csv
from referralmanager.cli.models.referrals import Base, Base1, AlasccaBloodReferral, AlasccaTissueReferral, PsffBloodReferral, ProbioBloodReferral, IPCMReferral
import re
import chardet
import pandas as pd


@click.command()
@click.option('--dbcred', type=click.File('r'), required=True)
@click.option('--local-data-dir', type=str, required=True)
@click.option('--referral-type', type=click.Choice(['ProbioBloodReferral', 'PsffBloodReferral', 'AlasccaBloodReferral', 'AlasccaTissueReferral', 'IPCMReferral']), required=True)
@click.pass_context
def dbimport(ctx, dbcred, local_data_dir, referral_type):
	logging.info("Running database import from dir {}".format(local_data_dir))
	cred_conf = json.load(dbcred)
	db_conf = cred_conf['dburi_ipcm'] if referral_type == "IPCMReferral" else cred_conf['dburi']
	#db_conf = cred_conf['dburi']

	print("Database Config",db_conf)

	engine = create_tables(db_conf, referral_type)
	session = get_session(engine)

	module = importlib.import_module('referralmanager.cli.models.referrals')
	referral_class = getattr(module, referral_type)

	import_referrals(session, local_data_dir, referral_class, referral_type)

def create_tables(uri, referral_type):
	engine = create_engine(uri, echo=True)
	Session = scoped_session(sessionmaker(bind=engine))

	if referral_type == "IPCMReferral":
		Base1.query = Session.query_property()
		Base1.metadata.create_all(engine)
	else:
		Base.query = Session.query_property()
		Base.metadata.create_all(engine)
	#Base.metadata.create_all(engine)
	return engine


def get_session(engine):
	Session = sessionmaker(bind=engine)
	return Session()

def getFileEncoding(file_path):
	rawdata = open(file_path, 'rb').read()
	result = chardet.detect(rawdata)
	file_encoding = result['encoding']
	return file_encoding

def import_referrals(session, searchdir, referral_class, referral_type, fileending="csv"):
	files = [f for f in os.listdir(searchdir) if f.endswith(fileending)]
	#csv_delimiter =  ';' if referral_type != "IPCMReferral" else ','
	csv_delimiter =  ';';
	for fn in files:
		print("file_name", fn)
		#if(re.match('^\d',fn)):
		file_path = os.path.join(searchdir, fn)
		file_encoding = getFileEncoding(file_path)
		
		df = pd.read_csv(file_path, sep=csv_delimiter ,encoding=file_encoding, dtype = str)
		df.rename(columns={'Unnamed: 0':'cdk'}, inplace=True)
		dict_row = df.to_dict("records")

		for rw in dict_row:
			if referral_type == "IPCMReferral":
				rw["date_birth"] = str(rw["pnr"])[0:8]
				change_header = {"cfDNA1":"dna1", "cfDNA2":"dna2", "cfDNA3":"dna3", "tissue1":"dna1","tissue2":"dna2"}
				for ch_key,ch_val in change_header.items():
					if ch_key in rw:
						rw[ch_val] = rw.pop(ch_key)
			
			if referral_type == "ProbioBloodReferral":
				change_header = {"HormonkÃ¤nslig" :"hormonkänslig", "Kastrationsresistent": "kastrationsresistent", "StudieID" :"studieID", "EndOfStudy" : "endOfStudy"}
				for ch_key,ch_val in change_header.items():
					if ch_key in rw:
						rw[ch_val] = rw.pop(ch_key)
	
			if referral_type == "IPCMReferral":
				pk_rid = rw["rid"]
				prim_tid = rw["tid"]
				current_record = referral_class.query.filter(referral_class.rid == pk_rid, referral_class.tid == prim_tid).first()

			elif referral_type == "ProbioBloodReferral" or referral_type == "PsffBloodReferral":
				pk_rid = rw["rid"]
				prim_tid = str(rw["tid"])
				current_record = referral_class.query.filter(referral_class.crid == pk_rid, referral_class.tid == prim_tid).first()
			
			else:
				pk_crid = rw["crid"]
				current_record = referral_class.query.filter(referral_class.crid == pk_crid).first()

			if not bool(current_record):
				ref = referral_class(rw)
				session.add(ref)
				session.commit()
			else:
				print("Data already exits")
				session.rollback()

			'''
			try:
				session.add(ref)
				session.commit()
			except IntegrityError as e:
				print("e",e)
				session.rollback()
			'''

		'''
		with open(os.path.join(searchdir, fn), 'r', encoding='latin_1') as f:
			row_pointer = csv.DictReader(f, delimiter =csv_delimiter)
			print(row_pointer)
			for each_row in row_pointer:
				each_row = dict(each_row)
				print(each_row)
				if referral_type == "IPCMReferral":
					each_row["date_birth"] = each_row["pnr"][0:8]
					change_header = {"cfDNA1":"dna1", "cfDNA2":"dna2", "cfDNA3":"dna3", "tissue1":"dna1","tissue2":"dna2"}
					for ch_key,ch_val in change_header.items():
						if ch_key in each_row:
							each_row[ch_val] = each_row.pop(ch_key)
							
				ref = referral_class(each_row)
				try:
					session.add(ref)
					session.commit()
				except IntegrityError as e:
					print("e",e)
					session.rollback()
		'''