package com.bankaudit.template.rest.dao;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.bankaudit.dao.AbstractDao;
import com.bankaudit.dao.SequenceAppenderDao;
import com.bankaudit.dao.SequenceAppenderDaoImpl;
import com.bankaudit.model.SequenceAppender;
import com.bankaudit.service.SequenceAppenderService;
import com.bankaudit.service.SequenceAppenderServiceImpl;
import com.bankaudit.template.model.FileUploadTemplateV;
import com.bankaudit.util.BankAuditUtil;

@Repository("fileUploadDao")
public class FileUploadDaoImpl extends AbstractDao implements FileUploadDao {
	
	@Autowired 
	SequenceAppenderService sequenceAppenderService;
	
	@Override
	public List<FileUploadTemplateV> createTemplate(String tableName) {

		Session session=getSession();		
		String hql =" select column_name,data_type,col_length,is_nullable from `upload_functionality` where table_name=:tableName   ";

		@SuppressWarnings("unchecked")
		List<Object[]> list = session.createSQLQuery(hql)
					.addScalar("column_name", StandardBasicTypes.STRING)
					.addScalar("data_type", StandardBasicTypes.STRING)
					.addScalar("col_length", StandardBasicTypes.INTEGER)
					.addScalar("is_nullable", StandardBasicTypes.STRING)				
					.setParameter("tableName", tableName).list();
		
		List<FileUploadTemplateV> futList = null;
		try {
			futList=new ArrayList<>();
			FileUploadTemplateV fupt=null;
			for (Object[] objects : list) {
				
				fupt=new FileUploadTemplateV();
				fupt.setColumnName(objects[0].toString());
				fupt.setDataType(objects[1].toString());
				fupt.setColLength(objects[2].toString());
				fupt.setIsNullable(objects[3].toString());
				
				futList.add(fupt);
				
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		return futList;
	}
	
	@SuppressWarnings("unchecked")
	public List<Object[]> getUploadType() {
		List<Object[]> list=null;
		try {
		Session session=getSession();	
		String hql = "SELECT " + 
				"CONCAT(table_name,'-',UCASE(SUBSTRING_INDEX(SUBSTRING_INDEX(table_name, '_', -2), '_', 1)),' UPLOAD') " + 
				"FROM   information_schema.TABLES " + 
				"WHERE  table_name LIKE 'upload_template%_v' " + 
				"AND    table_schema = DATABASE( ) " + 
				"ORDER  BY SUBSTRING_INDEX(table_name, '_', -2) ";
		list = session.createSQLQuery(hql).list();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public ArrayList<Map<String, String>> getTableColumns(String tableName) {
		ArrayList<Map<String, String>> list=null;
		Map<String, String> hp=null;
		try 
		{    
			List<FileUploadTemplateV> futList = this.createTemplate(tableName);
			list=new ArrayList<>();
			for (FileUploadTemplateV fileUploadTemplateV : futList) {
				hp=new HashMap<>();
				hp.put("column",fileUploadTemplateV.getColumnName());
				hp.put("columnType",fileUploadTemplateV.getDataType());
				hp.put("columnLenght",fileUploadTemplateV.getColLength());
				list.add(hp);
			}
			return list;
		}catch (Exception exception){
			exception.printStackTrace();
			throw exception;
		}   
	}

	@Override
	public String uploadExcelData(String tableName, List uploadList, String maker,Date businessDate) {
        
		String message="";
		if(tableName!=null && "upload_template_audit_v".equals(tableName.trim())) {
			message=saveAuditExcelData(uploadList,tableName,maker,businessDate);			
		}else if(tableName!=null && "upload_template_entity_v".equals(tableName.trim())) {
			message=saveAuditExcelData(uploadList,tableName,maker,businessDate);
		}else if(tableName!=null && "upload_template_mapping_v".equals(tableName.trim())) {
			message=saveAuditExcelData(uploadList,tableName,maker,businessDate);
		}else if(tableName!=null && "upload_template_user_v".equals(tableName.trim())){
			message=saveAuditExcelData(uploadList,tableName,maker,businessDate);
		}else {
			message = "Invalied template selection to upload.Please contact Adminstrator.";
		}
		
		return message;
	}

	
	@SuppressWarnings("unchecked")
	public List<Map<String,String>> excelToJavaMapping(String tableName) {
		Session session=getSession();
		Map<String,String> mp=null;

		String sql="select mapping,object_type from "+tableName+"_mapping ";
		//System.out.println(sql);
		List<Object[]> list =session.createSQLQuery(sql)		
				.addScalar("mapping", StandardBasicTypes.STRING)
				.addScalar("object_type", StandardBasicTypes.STRING)				
				.list();

		List<Map<String,String>> mpList = null;
		
		try {
			mpList=new ArrayList<>();
			for (Object[] objects : list) {
				mp=new HashMap<>();
				mp.put("mapping",objects[0].toString());
				mp.put("objectType",objects[1].toString());
				mpList.add(mp);
			}
		}catch (Exception e) {
			e.printStackTrace();
		}
		
		return mpList;
	}
	
	@Override
	@SuppressWarnings("unused")
	public String saveAuditExcelData(List<Map<String, String>> uploadList,String tableName,String maker,Date businessDate) {
		
		String message="";
		List<Map<String,String>> excelToJavaMapLst = this.excelToJavaMapping(tableName);
		String mapping="";
		String objectType="";
		
		Map<String,String> excelToJavaMapping = null;
		Map<String,String> excelDataMap = null;
		
		Session session = getSession();
		
		for (Map<String, String> map : excelToJavaMapLst) {

			try {

				if(map.get("mapping")!=null && !mapping.equals(map.get("mapping"))) {
					
					mapping=map.get("mapping");
					objectType=map.get("objectType");

				//	System.out.println("Mapping ....................................... : "+mapping);
				//System.out.println("Object Type ................................... :"+objectType);
					
					excelToJavaMapping=BankAuditUtil.getMap(mapping);
					System.out.println("Excel to Entity Mapping :"+excelToJavaMapping);
					
					
					for (Map<String, String> excelData : uploadList) {

						Set<String> keys=excelData.keySet();
						System.out.println("keys ......................... :"+keys);

						// Get Class instance for each row
						Class<?> clazz = Class.forName(objectType);
						// Get the private constructor.
						Constructor<?> cons = clazz.getDeclaredConstructor();
						// Since it is private, make it accessible.
						cons.setAccessible(true);
						// Create new object. 
						Object obj = cons.newInstance();

						//isDataSetToObject
						boolean isDataSetToObject = false;

						for (String key : keys) {

							//set fields 
							String value="";
							String fieldName="";
							Field field = null;

							if( excelToJavaMapping.get(key)!=null && !"null".equals((String)excelToJavaMapping.get(key)) ) {

								fieldName=excelToJavaMapping.get(key);
								value = excelData.get(key);

								//for loop
								try {
									
									
									field = clazz.getDeclaredField(fieldName);
									field.setAccessible(true);
									
									if("unitOpenDate".equals(fieldName))
									System.out.print("Mapping FieldName : "+fieldName+", value :"+value+",      Bean->Field type"+field.getType());// + " name :"+field.getType().getName());
									
									if (field.getType().isPrimitive() && field.getType()!=char.class) {
										//field.set(obj,new Integer(value));
										field.set(obj,Integer.valueOf(value));
										isDataSetToObject = true;
									}else if(field.getType()==Date.class){
										field.set(obj, new Date(value));
									    isDataSetToObject = true;
								    }else {
										field.set(obj, value);
										isDataSetToObject = true;
									}
									
									if("unitOpenDate".equals(fieldName))
									System.out.println(" value :"+field.get(obj));
								}catch (NoSuchFieldException e) {
									message=e.getMessage();
									e.printStackTrace();
								}catch (SecurityException e) {
									message=e.getMessage();
									e.printStackTrace();
								} catch (IllegalAccessException e) {
									message=e.getMessage();
									e.printStackTrace();
								} catch (Exception e) {
									message=e.getMessage();
									e.printStackTrace();
								}
							}

						}

						if(obj!=null && isDataSetToObject) {
							try {

								Field field = clazz.getDeclaredField("id");
								field.setAccessible(true);
								/*field.set(obj,new Integer(sequenceAppenderService.getAutoSequenceId())); */
								field.set(obj,Integer.parseInt(sequenceAppenderService.getAutoSequenceId())); 

							    //System.out.println("Object Data ............. :"+obj.toString());
																
								//########Save data#########
								
								session.save(obj);
								session.flush();
								session.clear();
								

							}catch (Exception e) {

								message=e.getMessage();
								System.out.println(message);
								//session.close();
							}
						}
					}
					
				}
			 }catch (InstantiationException e) {
		          System.out.println(e);
		      } catch (IllegalAccessException e) {
		          System.out.println(e);
		      } catch (ClassNotFoundException e) {
		          System.out.println(e);
		      }catch (Exception e) {
				//e.printStackTrace();
		    	 // e.getMessage();
		    	  session.close();
			}
		}
		return message;
	}

	public String saveData(Session session,Object obj) throws Exception{
		String message="";
		try {
			session.save(obj);
		}catch (Exception e) {
			//e.printStackTrace();
			message=e.getMessage();
			//session.close();
		}
		return message;
	} 
	
	
	
}
