package dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.naming.NamingException;
import javax.naming.directory.Attributes;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.ParameterMode;
import javax.persistence.PersistenceContext;
import javax.persistence.PersistenceUnit;
import javax.persistence.StoredProcedureQuery;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.jdbc.ReturningWork;
import org.hibernate.procedure.ProcedureCall;
import org.hibernate.result.Output;
import org.hibernate.result.ResultSetOutput;
import org.hibernate.transform.AliasToBeanResultTransformer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.ldap.core.AttributesMapper;
import org.springframework.ldap.core.LdapTemplate;
import org.springframework.ldap.core.support.LdapContextSource;
import org.springframework.stereotype.Repository;
import org.springframework.util.MultiValueMap;

import bean.LogModificheBean;
import bean.SecurityLogUserBean;
import bean.UserRoleBean;
import common.dao.GenericDAO;
import common.dao.IDAO;
import sicura.entities.Utenti;

@Repository
public class UserDAO 	extends GenericDAO<Utenti, Integer> 
						implements IDAO<Utenti, Integer> {

	@Value("#{appProperties[LDAPHost] ?: 'ldaps://findomestic.local:636'}")
	String LDAPHost;
	@Value("#{appProperties[NamingContext] ?: 'dc=findomestic,dc=local'}")
	String NamingContext;
	
//	@Autowired
//	private JdbcTemplate jdbcTemplate;

//	@PersistenceUnit
//	public void setEntityManagerFactory(EntityManagerFactory emf) {
//	        this.emf = emf;
//	}
	
	public List<UserRoleBean> getRoles (String username, String password) {
		List<UserRoleBean> res = new ArrayList<UserRoleBean>();
		
		List<String> roles = getADRoles(username, password);
		
		for(String role:roles) {
			if (role.startsWith("CN="))
				res.add(new UserRoleBean(role,decodeRole(role.substring(Math.max(role.length() - 2, 0)))));
		}
	
		return res;
	}

		private String decodeRole(String code) {
			
			if ("TR".equals(code))
				return "Tesoriere Responsabile";
			if ("AS".equals(code))
				return "Amministratore di sistema";
			if ("GI".equals(code))
				return "Gestore informatico";
			if ("DF".equals(code))
				return "Delegato alla firma";
			if ("VI".equals(code))
				return "Utente visualizzatore";
			return "";
		}
	
	private Session getLDAPSession(String username, String password) {
	
		Configuration cfg = new Configuration();
		cfg.configure("hibernate.cfg.xml"); //hibernate config xml file name
		String newUserName,newPassword;//set them as per your needs
		cfg.getProperties().setProperty("hibernate.connection.username",username);
		cfg.getProperties().setProperty("hibernate.connection.password",password);
		sessionFactory = cfg.buildSessionFactory();
		return sessionFactory.getCurrentSession();
	}
	
	public List<String> getADRoles(String username, String password) {
		
		
		LdapContextSource ctxSrc = new LdapContextSource();
	    ctxSrc.setUrl(LDAPHost);
	    ctxSrc.setBase(NamingContext);
	    ctxSrc.setUserDn(username);
	    ctxSrc.setPassword(password);
	    
		ctxSrc.afterPropertiesSet(); // this method should be called.
	
		LdapTemplate tmpl = new LdapTemplate(ctxSrc);
		
		@SuppressWarnings({ "unchecked", "rawtypes" })
		List<String> memberOf = tmpl.search("",  "(objectClass=person)",
				new AttributesMapper() {

					@Override
					public Object mapFromAttributes(Attributes attr) throws NamingException {
						// TODO Auto-generated method stub
						return attr.get("memberOf");
					}
			
				}
		);

		return memberOf;
		
		
/*		
		
		SQLQuery query = getLDAPSession(username, password).createSQLQuery(arg0);		

	.doReturningWork(new ReturningWork<Connection>() {
	        @Override
	        public Connection execute(Connection conn) throws SQLException {
	            return conn;
	        }
	    });
		Hashtable env = new Hashtable();
	      env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.ldap.LdapCtxFactory");
	      env.put(Context.PROVIDER_URL, "ldap://localhost:389/dc=example,dc=com");

	      DirContext ctx;
	      try {
	         ctx = new InitialDirContext(env);
	      } catch (NamingException e) {
	         throw new RuntimeException(e);
	      }

	      List<String> list = new LinkedList<String>();
	      NamingEnumeration results = null;
	      try {
	         SearchControls controls = new SearchControls();
	         controls.setSearchScope(SearchControls.SUBTREE_SCOPE);
	         results = ctx.search("", "(objectclass=person)", controls);

	         while (results.hasMore()) {
	            SearchResult searchResult = (SearchResult) results.next();
	            Attributes attributes = searchResult.getAttributes();
	            Attribute attr = attributes.get("cn");
	            String cn = attr.get().toString();
	            list.add(cn);
	         }
	      } catch (NameNotFoundException e) {
	         // .doReturningWork(new ReturningWork<Connection>() {
	        @Override
	        public Connection execute(Connection conn) throws SQLException {
	            return conn;
	        }
	    });The base context was not found.
	         // Just clean up and exit.
	      } catch (NamingException e) {
	         throw new RuntimeException(e);
	      } finally {
	         if (results != null) {
	            try {
	               results.close();
	            } catch (Exception e) {
	               // Never mind this.
	            }
	         }
	         if (ctx != null) {
	            try {
	               ctx.close();
	            } catch (Exception e) {
	               // Never mind this.
	            }
	         }
	      }
*/	      
		
	}

	public Utenti login(String username, String password) {
		boolean tx = startTransaction();
		
		try {
			Criteria crit = currentSession().createCriteria(Utenti.class);
			crit
				.add(Restrictions.eq("userId", username))
				.add(Restrictions.eq("password", password));
			
			List<Utenti> res = crit.list();
			if (res.size()>0)
				return res.get(0);
			else
				return null;
		} finally {
			if (tx)
				commit();
		}
			
	}
	
	public Utenti getByUsername(String username, String profiloLocale) {
		//boolean tx = startTransaction();
		try {
			Criteria crit = currentSession().createCriteria(Utenti.class);
			crit
				.add(Restrictions.eq("userId", username))
				.add(Restrictions.eq("profiloLocale", profiloLocale));
			
			List<Utenti> res = crit.list();
			if (res.size()>0)
				return res.get(0);
			else
				return null;
		} finally {
//			if (tx)
//				tx = commit();
		}
	}
	
	
	public /*Map<String, Object>*/ CallableStatement getSecurityLogUser(final String username, final String salt, final String authenticationMode, final String profiloUtente) {
//		boolean.doReturningWork(new ReturningWork<Connection>() {
//	        @Override
//	        public Connection execute(Connection conn) throws SQLException {
//	            return conn;
//	        }
//	    }); tx = startTransaction();
		 	
		// Fabio --- per debug
//		executeSQL("delete from SecuritySalt where saltvalue='" + username.trim()+"'");
//		if (tx)
//			commit();
//		tx = startTransaction();
		
		// Fabio --- fine per debug
		
		
		
		int res = -1;
		Connection connection = getConnection();

			/*
			 CREATE PROCEDURE [dbo].[spSecurityLogUser]
				@UserID VARCHAR(10),
				@Password VARCHAR(120),
				@AuthenticationMode VARCHAR(3) = 'INT',
				@ProfiloLocale VARCHAR(2) = ''
				
			 */
		CallableStatement proc;
		try {
			proc = connection.prepareCall("{call spSecurityLogUser(?,?,?,?)}");
				
			proc.setString(1, username.trim());
			proc.setString(2, salt.trim());
			proc.setString(3, authenticationMode.trim());
			proc.setString(4, profiloUtente.trim());
			
			boolean isResultSet = proc.execute();
			
			return proc;
//			rs = proc.getResultSet(); 
//			
//			rs.first();
				
		} catch (Exception ex) {
			
			ex.printStackTrace();
		}
		
		return null;
/*		
		
		
		
		
		
		
		
		
		
		
		
		
		final String procedureCall = "exec spSecurityLogUser(?,?,?,?)";
		List<SqlParameter> paramList = new ArrayList<SqlParameter>();
		SqlParameter userID = new SqlParameter("@UserID", Types.VARCHAR);
		SqlParameter password = new SqlParameter("@Password", Types.VARCHAR);
		SqlParameter pAuthenticationMode = new SqlParameter("@AuthenticationMode", Types.VARCHAR);
		List<SqlParameter> paramList = new ArrayList<SqlParameter>();
		SqlParameter userID = new SqlParameter("@UserID", Types.VARCHAR);
		SqlParameter password = new SqlParameter("@Password", Types.VARCHAR);
		SqlParameter pAuthenticationMode = new SqlParameter("@AuthenticationMode", Types.VARCHAR);
		SqlParameter profiloLocale = new SqlParameter("@ProfiloLocale", Types.VARCHAR);
		
		paramList.add(userID);
		paramList.add(password);
		paramList.add(pAuthenticationMode);
		paramList.add(profiloLocale);
		
	
		Map<String, Object> resultMap = jdbcTemplate.call(new CallableStatementCreator() {
			@Override
			public CallableStatement createCallableStatement(Connection connection) throws SQLException {

				CallableStatement callableStatement = connection.prepareCall(procedureCall);
				callableStatement.setString("@UserID", username.trim());
				callableStatement.setString("@Password", salt.trim());
				callableStatement.setString("@AuthenticationMode", authenticationMode.trim());
				callableStatement.setString("@ProfiloLocale", profiloUtente.trim());
				return callableStatement;

			}
		}, paramList);
		
		return resultMap;
*/		
	/*	
		for(String str: resultMap.keySet()) {
			ArrayList<Object> submap = (ArrayList<Object>) resultMap.get(str);
			
			System.out.println(submap.size());
			
		}
		
		
//		StoredProcedureQuery spq = entityManager.createStoredProcedureQuery("spSecurityLogUser");
//		spq.registerStoredProcedureParameter("@UserID", String.class, ParameterMode.IN);
//		spq.registerStoredProcedureParameter("@Password", String.class, ParameterMode.IN);
//		spq.registerStoredProcedureParameter("@AuthenticationMode", String.class, ParameterMode.IN);
//		spq.registerStoredProcedureParameter("@ProfiloLocale", String.class, ParameterMode.IN);
//		
//		spq.setParameter("@UserID", username.trim());
//		spq.setParameter("@Password", salt.trim());
//		spq.setParameter("@AuthenticationMode", authenticationMode);
//		spq.setParameter("@ProfiloLocale", profiloUtente);
//		
//		boolean isResultSet = spq.execute(); // returns true when we have a result set from the proc
//		List results1 = spq.getResultList(); // get the first result set
//		List results2;
//		if (spq.hasMoreResults())
//		{
//		    results2 = spq.getResultList(); // get the second result set
//		}
//		
//		ProcedureCall call = currentSession().createStoredProcedureCall("spSecurityLogUser");
//		call.registerParameter("@UserID", String.class, ParameterMode.IN).bindValue(username.trim());
//		call.registerParameter("@Password", String.class, ParameterMode.IN).bindValue(salt.trim());
//		call.registerParameter("@AuthenticationMode", String.class, ParameterMode.IN).bindValue(authenticationMode);
//		call.registerParameter("@ProfiloLocale", String.class, ParameterMode.IN).bindValue(profiloUtente);
//		
//		Output output = call.getOutputs().getCurrent();
//		
//		 List<Object[]> dataset=null;;
//		
//		if (output.isResultSet()) {
//			dataset =((ResultSetOutput) output).getResultList();
//		}
//		
	

		
//		Object pippo = dataset.get(0);
		
		String sql = String.format("EXEC spSecurityLogUser '%s','%s','%s','%s'", username.trim(), salt.trim(), authenticationMode, profiloUtente);
		Query q = currentSession().createSQLQuery(sql);
		//q.setResultTransformer(new AliasToBeanResultTransformer(SecurityLogUserBean.class));
		q.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
		
		List res = q.list();
		
		if (tx)
			commit();
		
		if (res.size()>0)
			return res;
		else
			return null;
			
*/			
	}
	
	public Utenti getCurrentUser(Integer userID) {
		boolean tx = startTransaction();
		
		try {
		Criteria crit = currentSession().createCriteria(Utenti.class);
		crit.add(Restrictions.eq("id", userID));
		
		
		List<Utenti> res = crit.list();
		
		if (res.size()>0)
			return res.get(0);
		else
			return null;
		} finally {
			if (tx)
				tx = commit();
		}		
	}

	public Integer count(String filter) {
		boolean tx = startTransaction();
		
		try {
			Criteria crit = currentSession().createCriteria(Utenti.class);
			if (filter!=null && !"".equals(filter))
				crit.add(Restrictions.like("userId", "%"+filter+"%"));
			Integer res = ((Long) crit.setProjection(Projections.rowCount()).uniqueResult()).intValue();
			
			return res;
		} finally {
			if (tx)
				tx = commit();
		}
	}
	
	public List<Utenti> list(Integer startFrom, Integer length,String filter,String orderField) {
		boolean tx = startTransaction();
		
		try {
			try {
				Criteria crit = currentSession().createCriteria(Utenti.class);
				if (filter!=null && !"".equals(filter))
					crit.add(Restrictions.like("userId", "%"+filter+"%"));
				crit.setFirstResult(startFrom);
				crit.setMaxResults(length);
				crit.addOrder(getOrderBy(orderField));
				
				List<Utenti> res = crit.list();
				
				return res;
			} catch (Exception ex) {
				ex.printStackTrace();
				return null;
			}
		} finally {
			if (tx)
				tx = commit();
		}		
	}

	public Utenti newInstance() {
		return new Utenti();
	}

	public void eliminaUtente(Integer id) {
		boolean tx = startTransaction();
		
		try {
			Utenti u = readNoTransaction(id);
			if (u!=null) {
				executeSQL("DELETE FROM UtentiFunzioni WHERE UserID='"+u.getUserId()+"'");
				executeSQL("DELETE FROM Utenti WHERE ID="+id);
			}
		} finally {
			if (tx)
				tx = commit();
		}	
	}

}
