package fr.ekium.cartographie.services.imports;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import fr.ekium.cartographie.domain.Branch;
import fr.ekium.cartographie.domain.Function;
import fr.ekium.cartographie.domain.InterviewTeam;
import fr.ekium.cartographie.domain.Job;
import fr.ekium.cartographie.domain.Person;
import fr.ekium.cartographie.domain.PersonHasRoleType;
import fr.ekium.cartographie.domain.Pole;
import fr.ekium.cartographie.domain.Profil;
import fr.ekium.cartographie.domain.RoleType;

public class ExcelDataLoader {

	String pathFile;
	int startLine;

	Workbook workbook;
	Sheet syntheseSheet;
	Connection con;





	/**
	 * @param pathFile of file contain data for Ekium employees
	 * @throws BiffException
	 * @throws IOException
	 * @throws SQLException
	 */
	public ExcelDataLoader(String pathFile, int startLine) throws BiffException, IOException, SQLException {
		this.pathFile = pathFile;
		this.startLine = startLine;

		this.workbook = Workbook.getWorkbook(new File(pathFile));
		this.syntheseSheet = workbook.getSheet("SYNTHESE");

		String url = "jdbc:mysql://localhost:3306/cartographie_ekium";
		String user = "root";
		String password = null;
		con = DriverManager.getConnection(url, user, password);
	}


	/**
	 * Load Ekium roles data in database
	 * @throws SQLException
	 */
	public void loadRoleData() throws SQLException{
		ArrayList<String> rolessName = new ArrayList<String>();

		for (Cell cell : syntheseSheet.getColumn(11)) {
			if((cell.getRow()>startLine) && (!cell.getContents().equals(""))){
				String content = cell.getContents();
				if(!rolessName.contains(content)){
					rolessName.add(content);
				}
			}
		}

		int j = 1;
		for(String role : rolessName){
//			if(RoleType.findRoleTypesByNameEquals(role).getResultList().size() == 0){
//				RoleType rt = new RoleType();
//				rt.setName(role);
//				rt.setPeople(new HashSet<Person>());
//				rt.persist();
//			}

			String queryString = "INSERT INTO `cartographie_ekium`.`role_type` (`id`, `name`) VALUES ('"+ j++ +"', '"+role+"');";
			System.out.println(queryString);
			Statement stmt = con.createStatement();
			stmt.execute(queryString);
		}
	}


	/**
	 * Load Ekium profil data in database
	 * @throws SQLException
	 */
	public void loadProfilData() throws SQLException{
		ArrayList<String> profilsName = new ArrayList<String>();

		for (Cell cell : syntheseSheet.getColumn(10)) {
			if((cell.getRow()>startLine) && (!cell.getContents().equals(""))){
				String content = cell.getContents();
				if(!profilsName.contains(content)){
					profilsName.add(content);
				}
			}
		}

		int j = 2;
		int i = 1;
		for(String profil : profilsName){
//			if(Profil.findProfilsByNameEquals(profil).getResultList().size() == 0){
//				Profil p = new Profil();
//				p.setName(profil);
//				p.setCompModelId(null);
//				p.setPeople(new HashSet<Person>());
//				p.persist();
//			}

			String queryString = "INSERT INTO `cartographie_ekium`.`profil` (`id`, `name`, `comp_model_id`) VALUES ('"+ i++ +"', '"+profil+"', NULL);";
			System.out.println(queryString);
			Statement stmt = con.createStatement();
			stmt.execute(queryString);
		}
	}

	/**
	 * Load Ekium pole data in database
	 * @throws SQLException
	 */
	public void loadPoleData() throws SQLException{

		ArrayList<String> polesName = new ArrayList<String>();

		for (Cell cell : syntheseSheet.getColumn(9)) {
			if((cell.getRow()>startLine) && (!cell.getContents().equals(""))){
				String content = cell.getContents();
				if(!polesName.contains(content)){
					polesName.add(content);
				}
			}
		}
		int j = 1;
		for(String pole : polesName){
//			if(Pole.findPolesByNameEquals(pole).getResultList().size() == 0){
//				Pole p = new Pole();
//				p.setName(pole);
//				p.setPeople(new HashSet<Person>());
//				p.persist();
//			}

			String queryString = "INSERT INTO `cartographie_ekium`.`pole` (`id`, `name`) VALUES ('"+ j++ +"', '"+pole+"');";
			System.out.println(queryString);
			Statement stmt = con.createStatement();
			stmt.execute(queryString);
		}
	}


	/**
	 * Load Ekium branch data in database
	 * @throws SQLException
	 */
	public void loadBranchData() throws SQLException{
		ArrayList<String> branchsName = new ArrayList<String>();

		for (Cell cell : syntheseSheet.getColumn(8)) {
			if((cell.getRow()>startLine) && (!cell.getContents().equals(""))){
				String content = cell.getContents();
				if(!branchsName.contains(content)){
					branchsName.add(content);
				}
			}
		}
		int j = 1;
		for(String branch : branchsName){
//			if(Branch.findBranchesByNameEquals(branch).getResultList().size() == 0){
//				Branch b = new Branch();
//				b.setName(branch);
//				b.setPeople(new HashSet<Person>());
//				b.persist();
//			}

			String queryString = "INSERT INTO `cartographie_ekium`.`branch` (`id`, `name`) VALUES ('"+ j++ +"', '"+branch+"');";
			System.out.println(queryString);
			Statement stmt = con.createStatement();
			stmt.execute(queryString);
		}
	}


	/**
	 * Load Ekium job data in database
	 * @throws SQLException
	 */
	public void loadJobData() throws SQLException{
		ArrayList<String> jobsName = new ArrayList<String>();

		for (Cell cell : syntheseSheet.getColumn(7)) {
			if((cell.getRow()>startLine) && (!cell.getContents().equals(""))){
				String content = cell.getContents();
				if(!jobsName.contains(content)){
					jobsName.add(content);
				}
			}
		}

		int j = 1;
		for(String job : jobsName){
//			if(Job.findJobsByNameEquals(job).getResultList().size() == 0){
//				Job j = new Job();
//				j.setName(job);
//				j.setPeople(new HashSet<Person>());
//				j.persist();
//			}

			String queryString = "INSERT INTO `cartographie_ekium`.`job` (`id`, `name`) VALUES ('"+ j++ +"', '"+job+"');";
			System.out.println(queryString);
			Statement stmt = con.createStatement();
			stmt.execute(queryString);
		}
	}


	/**
	 * Load Ekium function data in database
	 * @throws SQLException
	 */
	public void loadFunctionData() throws SQLException{
		ArrayList<String> functionsName = new ArrayList<String>();

		for (Cell cell : syntheseSheet.getColumn(6)) {
			if((cell.getRow()>startLine) && (!cell.getContents().equals(""))){
				String content = cell.getContents();
				if(!functionsName.contains(content)){
					functionsName.add(content);
				}
			}
		}

		int j = 1;
		for(String function : functionsName){
//			if(Function.findFunctionsByNameEquals(function).getResultList().size() == 0){
//				Function f = new Function();
//				f.setName(function);
//				f.setPeople(new HashSet<Person>());
//				f.persist();
//			}

			String queryString = "INSERT INTO `cartographie_ekium`.`function` (`id`, `name`) VALUES ('"+ j++ +"', '"+function+"');";
			System.out.println(queryString);
			Statement stmt = con.createStatement();
			stmt.execute(queryString);
		}
	}

	public Connection getCon() {
		return con;
	}


	/**
	 * @param startLine index of begin treatement
	 * @param endLine	index of end treatement
	 * @throws SQLException
	 *
	 * Method to load person data in database line by line
	 * TODO Regler le probeme des dates
	 */
	public void loadPersonDataLineByLine() throws SQLException{

		SimpleDateFormat dfIn = new SimpleDateFormat("dd/MM/yyyy" );
		SimpleDateFormat dfOut = new SimpleDateFormat("yy-MM-dd" );

		int cpt = 2;
		int i = startLine;
		//Loop to load data person line by line
		while(!syntheseSheet.getCell(2, i).getContents().equals("")){

			System.out.println("#################################################");
			HashMap<String, Object> personData = new HashMap<String, Object>();

			//Loop to load data person column by column
			for (int j = 1; j < 12; j++) {
				Cell cell = syntheseSheet.getCell(j, i);
				String content = cell.getContents();
				Cell titleCell = syntheseSheet.getCell(j, 13);
				String titleContent = titleCell.getContents();

				System.out.println(titleContent + " (" + j + " - " + i + ") : " + content);

				//if(titleContent.equals("Nom")){
//					personData.put(titleContent, content);
				//}
				//else if(titleContent.equals("Prénom")){
					//personData.put(titleContent, content);
//				}
				//Nom and Prenom are handled by the else statement
				if(titleContent.equals("Sexe")){
					if(content.equals("H")){
						personData.put(titleContent, 1);
					}else{
						personData.put(titleContent, 0);
					}
				}
				else if(titleContent.equals("Date de naissance")){
					try {
						Date date = dfIn.parse(content);
						System.out.println("Date naissance : " + dfOut.format(date));
						//personData.put(titleContent, dfOut.format(date));
						personData.put(titleContent, dfOut.format(date));
					} catch (Exception e) {
						System.out.println("Error while parsing date" );
						e.printStackTrace();
					}
				}
				else if(titleContent.equals("Date d'entrée")){
					try {
						Date date = dfIn.parse(content);
						System.out.println("Date d'entrée : " + dfOut.format(date));
						//personData.put(titleContent, dfOut.format(date));
						personData.put(titleContent, dfOut.format(date));
					} catch (Exception e) {
						System.out.println("Error while parsing date" );
						e.printStackTrace();
					}
				}else{
					personData.put(titleContent, content);
				}

			}

			String login = personData.get("Prénom")+"."+personData.get("Nom");
			login = login.toLowerCase();

			String mail = login+"@ekium.eu";

			System.out.println("Login : " + login);
			System.out.println("Mail : " + mail);

			/////////////////////////////////////////////////////////////////////////

			String profilName = (String)personData.get("Profil");
			String queryProfilString = "SELECT id FROM profil WHERE name LIKE '"+profilName+"';";
			System.out.println(queryProfilString);
			Statement stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(queryProfilString);
			long profilId = 0;
			while (rs.next()) {
				profilId = rs.getLong("id");
				System.out.println("Intger ID :  " + profilId);
			}

			String poleName = (String)personData.get("Pôle");
			String queryPoleString = "SELECT id FROM pole WHERE name LIKE '"+poleName+"';";
			System.out.println(queryPoleString);
			stmt = con.createStatement();
			rs = stmt.executeQuery(queryPoleString);
			long poleId = 0;
			while (rs.next()) {
				poleId = rs.getLong("id");
				System.out.println("Intger ID :  " + poleId);
			}

			String jobName = (String)personData.get("Métier");
			String queryJobString = "SELECT id FROM job WHERE name LIKE '"+jobName+"';";
			System.out.println(queryJobString);
			stmt = con.createStatement();
			rs = stmt.executeQuery(queryJobString);
			long jobId = 0;
			while (rs.next()) {
				jobId = rs.getLong("id");
				System.out.println("Intger ID :  " + jobId);
			}

			String functionName = (String)personData.get("Poste");
			String queryFunctionString = "SELECT id FROM function WHERE name LIKE '"+functionName+"';";
			System.out.println(queryFunctionString);
			stmt = con.createStatement();
			rs = stmt.executeQuery(queryFunctionString);
			long functionId = 0;
			while (rs.next()) {
				functionId = rs.getLong("id");
				System.out.println("Intger ID :  " + functionId);
			}

			String branchName = (String)personData.get("Branche");
			String queryBranchString = "SELECT id FROM branch WHERE name LIKE '"+branchName+"';";
			System.out.println(queryBranchString);
			stmt = con.createStatement();
			rs = stmt.executeQuery(queryBranchString);
			long branchId = 0;
			while (rs.next()) {
				branchId = rs.getLong("id");
				System.out.println("Intger ID :  " + branchId);
			}

			String queryRoleIdString = "SELECT id FROM role_type WHERE name LIKE '"+personData.get("Droit")+"';";
			System.out.println(queryRoleIdString);
			stmt = con.createStatement();
			rs = stmt.executeQuery(queryRoleIdString);
			long roleId = 0;
			while (rs.next()) {
				roleId = rs.getLong("id");
				System.out.println("Intger ID :  " + roleId);
			}


//			Person p = new Person();
//			p.setAddress("address");
//			p.setBeginWorkDate((Date) personData.get("Date d'entrée"));
//			p.setBirth((Date) personData.get("Date de naissance"));
//			p.setCellPhone(0);
//			p.setFirstname((String) personData.get("Prénom"));
//			p.setIsMale((Boolean) (personData.get("Sexe").equals("male")));
//			p.setLastEvalDate((Date) personData.get("Date d'entrée"));
//			p.setLastname((String) personData.get("Nom"));
//			p.setLogin(login);
//			p.setMail(mail);
//			p.setOfficePhone(0);
//			p.setPassword("user");
//			p.setPostalCode(0);
//			p.setTown("town");
//			System.out.println("looking for branch named : "+personData.get("Branche"));
//			p.setBranchId(Branch.findBranchesByNameEquals((String)personData.get("Branche")).getSingleResult());
//			p.setFunctionId(Function.findFunctionsByNameEquals((String)personData.get("Poste")).getSingleResult());
//			p.setJobId(Job.findJobsByNameEquals((String)personData.get("Métier")).getSingleResult());
//			p.setPoleId(Pole.findPolesByNameEquals((String)personData.get("Pôle")).getSingleResult());
//			p.setProfilId(Profil.findProfilsByNameEquals((String)personData.get("Profil")).getSingleResult());
//			p.persist();

			String queryString = "INSERT INTO `cartographie_ekium`.`person` (`id`, `address`, `begin_work_date`, `birth`, `cell_phone`, `firstname`, `is_male`, `last_eval_date`, `lastname`, `login`, `mail`, `office_phone`, `password`, `postal_code`, `town`, `branch_id`, `function_id`, `job_id`, `pole_id`, `profil_id`, `role_type_id`) VALUES ('"+ cpt++ +"', 'address', '"+personData.get("Date d'entrée")+"', '"+personData.get("Date de naissance")+"', '0', '"+personData.get("Prénom")+"', b'"+personData.get("Sexe")+"', '"+personData.get("Date d'entrée")+"', '"+personData.get("Nom")+"', '"+login+"', '"+mail+"', '0', 'user', '0', 'town', '"+branchId+"', '"+functionId+"', '"+jobId+"', '"+poleId+"', '"+profilId+"','"+roleId+"');";
			System.out.println(queryString);
			Statement insertStmt = con.createStatement();
			insertStmt.execute(queryString);

			String queryPersonIdString = "SELECT id FROM person WHERE login LIKE '"+login+"';";
			System.out.println(queryPersonIdString);
			stmt = con.createStatement();
			rs = stmt.executeQuery(queryPersonIdString);
			long personId = 0;
			while (rs.next()) {
				personId = rs.getLong("id");
				System.out.println("Intger ID :  " + personId);
			}

//			PersonHasRoleType type = new PersonHasRoleType();
//			System.out.println(Person.findPeopleByLoginEquals(login));
//			type.setPersonId(Person.findPeopleByLoginEquals(login).getSingleResult());
//			System.out.println(personData.get("Droit"));
//			type.setRoleTypeId(RoleType.findRoleTypesByNameEquals((String)personData.get("Droit")).getSingleResult());
//			type.persist();

			String roleQueryString = "INSERT INTO `cartographie_ekium`.`person_has_role_type` (`id`, `person_id`, `role_type_id`) VALUES ('"+cpt+"', '"+personId+"', '"+roleId+"');";
			System.out.println(roleQueryString);
			Statement roleInsertStmt = con.createStatement();
			roleInsertStmt.execute(roleQueryString);

//			InterviewTeam interviewTeam = new InterviewTeam();
//			interviewTeam.setEmployee(Person.findPeopleByLoginEquals(login).getSingleResult());
//			interviewTeam.persist();

			String interviewTeamQueryString = "INSERT INTO `cartographie_ekium`.`interview_team` (`id` ,`version` ,`employee` ,`interlocutor` ,`manager` ) VALUES ('"+cpt+"', '0', '"+personId+"', NULL, NULL);";
			System.out.println(interviewTeamQueryString);
			Statement interviewTeamInsertStmt = con.createStatement();
			interviewTeamInsertStmt.execute(interviewTeamQueryString);

		}


	}


	/**
	 * @param args
	 * @throws IOException
	 * @throws BiffException
	 * @throws SQLException
	 */
	public static void main(String[] args) throws ParseException {
		ExcelDataLoader dataLoader = null;

		try {
			dataLoader = new ExcelDataLoader("Cartographie Paris Le Havre.xls", 41);

//			dataLoader.loadPoleData();
//			dataLoader.loadProfilData();
//			dataLoader.loadBranchData();
//			dataLoader.loadJobData();
//			dataLoader.loadFunctionData();
//			dataLoader.loadRoleData();

			dataLoader.loadPersonDataLineByLine();

		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
//		}finally{
//			try {
//				//dataLoader.getCon().close();
//			} catch (SQLException e) {
//				// TODO Auto-generated catch block
//				e.printStackTrace();
//			}
//		}

	}

}