/********************************************************************
 * Copyright © 2005 VHA. All rights reserved
 ********************************************************************/
package gov.va.med.esr.common.persistent.registry.hibernate;

// Java Classes
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

// Library classes
import org.apache.commons.lang.Validate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateSystemException;
import org.springframework.orm.hibernate3.HibernateTemplate;

// Framework classes
import gov.va.med.fw.model.EntityKey;
import gov.va.med.fw.persistent.DAOException;
import gov.va.med.fw.persistent.MaxRecordsExceededException;
import gov.va.med.fw.persistent.hibernate.AbstractDAOAction;
import gov.va.med.fw.persistent.hibernate.GenericDAOImpl;
import gov.va.med.fw.persistent.hibernate.PaginatedQueryExecutor;
import gov.va.med.fw.service.pagination.SearchQueryInfo;
import gov.va.med.fw.util.StringUtils;

// Common classes
import gov.va.med.esr.common.model.ee.PrisonerOfWar;
import gov.va.med.esr.common.model.ee.PurpleHeart;
import gov.va.med.esr.common.model.ee.SHAD;
import gov.va.med.esr.common.model.lookup.RegistryType;
import gov.va.med.esr.common.model.person.Name;
import gov.va.med.esr.common.model.person.Person;
import gov.va.med.esr.common.model.registry.RegistryLoadStatistics;
import gov.va.med.esr.common.model.registry.Registry;
import gov.va.med.esr.common.model.registry.RegistryTrait;
import gov.va.med.esr.common.persistent.registry.RegistryDAO;
import gov.va.med.esr.service.RegistrySearchCriteria;

/**
 * DAO implementation for Registry.
 * 
 * @author Muddaiah Ranga
 * @version 3.0
 */
public class RegistryDAOImpl extends GenericDAOImpl implements RegistryDAO {

    private static final long serialVersionUID = -6542039226053051792L;
    
    public final static String CLASS_NAME_PH_REGISTRY       = "PurpleHeart";
    public final static String CLASS_NAME_POW_REGISTRY      = "PrisonerOfWar";
    public final static String CLASS_NAME_SHAD_REGISTRY     = "SHAD";
    public final static String CLASS_NAME_MS_SITE_RECORD    = "MilitaryServiceSiteRecord";
    public final static String CLASS_NAME_MS_EPISODE        = "MilitaryServiceEpisode";
    
    public final static String SELECT_CLAUSE_COUNT_QUERY    = "select count(distinct r.identifier)";
    public final static String SELECT_CLAUSE_DATA_QUERY     = "select distinct new gov.va.med.esr.service.RegistrySearchResultBean(r.identifier,rt.ssn,rt.firstName,rt.middleName,rt.lastName,rt.registryType,r.person.identifier)";

    public final static String GET_REGISTRY_TRAIT           = "from RegistryTrait as rt where upper(rt.firstName) = upper(:firstName) and upper(rt.lastName) = upper(:lastName) and rt.registryType = :registryType";
    public final static String GET_LATEST_BATCH_NUMBER = "registrySummary_Latest";
    
    /* (non-Javadoc)
     * @see gov.va.med.esr.common.persistent.registry.RegistryDAO#getById(java.math.BigDecimal, gov.va.med.esr.common.model.lookup.RegistryType)
     */
    public Registry getById(EntityKey entityKey, RegistryType registryType) throws DAOException {
        Registry registry = null;
        if(StringUtils.equals(RegistryType.CODE_PH_REGISTRY.getName(),registryType.getCode())) {
            registry = this.getPHRegistryById(entityKey);
        } else if(StringUtils.equals(RegistryType.CODE_POW_REGISTRY.getName(),registryType.getCode())) {
            registry = this.getPOWRegistryById(entityKey);
        } else if(StringUtils.equals(RegistryType.CODE_SHAD_REGISTRY.getName(),registryType.getCode())) {
            registry = this.getSHADRegistryById(entityKey);
        }
        return registry;
    }

    /* (non-Javadoc)
     * @see gov.va.med.esr.common.persistent.registry.RegistryDAO#getPHRegistryById(java.math.BigDecimal)
     */
    public PurpleHeart getPHRegistryById(EntityKey entityKey) throws DAOException {
        try {
            HibernateTemplate tpl = this.getHibernateTemplate();
            List results = tpl.findByNamedQueryAndNamedParam(FIND_PH_BY_IDENTIFIER,PARAM_IDENTIFIER,entityKey.getKeyValue());

            // Should only return one value
            return results.isEmpty() ? null : (PurpleHeart) results.get(0);
        } 
        catch (DataAccessException e) {
            throw new DAOException("Failed to get the purple heart registry by identifier", e);
        }
    }
    
    /* (non-Javadoc)
     * @see gov.va.med.esr.common.persistent.registry.RegistryDAO#getPOWRegistryById(java.math.BigDecimal)
     */
    public PrisonerOfWar getPOWRegistryById(EntityKey entityKey) throws DAOException {
        try {
            HibernateTemplate tpl = this.getHibernateTemplate();
            List results = tpl.findByNamedQueryAndNamedParam(FIND_POW_BY_IDENTIFIER,PARAM_IDENTIFIER,entityKey.getKeyValue());

            // Should only return one value
            return results.isEmpty() ? null : (PrisonerOfWar) results.get(0);
        } 
        catch (DataAccessException e) {
            throw new DAOException("Failed to get the prisoner of war registry by identifier", e);
        }
    }
    
    /* (non-Javadoc)
     * @see gov.va.med.esr.common.persistent.registry.RegistryDAO#getPOWRegistryByIcn(java.lang.String)
     */
    public PrisonerOfWar getPOWRegistryByIcn(String icn) throws DAOException
    {
        try {
            HibernateTemplate tpl = this.getHibernateTemplate();
            List results = tpl.findByNamedQueryAndNamedParam(FIND_POW_BY_ICN,PARAM_ICN, icn);

            // If there are multiple results, return null
            return (results.isEmpty() || results.size() > 1 )? null : (PrisonerOfWar) results.get(0);
        } 
        catch (DataAccessException e) {
            throw new DAOException("Failed to get the prisoner of war registry by icn", e);
        }
    }    
    
    /* (non-Javadoc)
     * @see gov.va.med.esr.common.persistent.registry.RegistryDAO#getSHADRegistryById(java.math.BigDecimal)
     */
    public SHAD getSHADRegistryById(EntityKey entityKey) throws DAOException {
        try {
            HibernateTemplate tpl = this.getHibernateTemplate();
            List results = tpl.findByNamedQueryAndNamedParam(FIND_SHAD_BY_IDENTIFIER,PARAM_IDENTIFIER,entityKey.getKeyValue());

            // Should only return one value
            return results.isEmpty() ? null : (SHAD) results.get(0);
        } 
        catch (DataAccessException e) {
            throw new DAOException("Failed to get the shad registry by identifier", e);
        }
    }
    
    public Registry getRegistry(Person person, RegistryType type) throws DAOException {
        Registry registry = null;
        try {
            if(person != null && type != null) {
                String ssn = (person.getOfficialSsn() != null) ? person.getOfficialSsn().getFormattedSsnText() : null;
                Name name = person.getLegalName();
                String lastName = (name != null) ? name.getFamilyName() : null;
                String firstName = (name != null) ? name.getGivenName() : null;
            
                if(StringUtils.isNotEmpty(ssn) &&
                   StringUtils.isNotEmpty(lastName) &&
                   StringUtils.isNotEmpty(firstName) ) {
                    String className = getRegistryClassName(type.getCode());
                    String query = " from " + className + " as r where r.registryTrait.ssn = :ssn and upper(r.registryTrait.lastName) = upper(:lastName) and upper(r.registryTrait.firstName) like upper(:firstName)";
                    String paramName[] = {"ssn","lastName","firstName"};
                    String paramValue[] = new String[3];
                    paramValue[0] = ssn;
                    paramValue[1] = lastName;
                    paramValue[2] = firstName;
                
                    HibernateTemplate tpl = this.getHibernateTemplate();
                    List results = tpl.findByNamedParam(query,paramName,paramValue);
            
                    // Should only return one value
                    //With the change for 9116 it can return more than 1 result
                    registry = results.size() > 0 ? (Registry) results.get(0) : null;
                }
            }
        } 
        catch (DataAccessException e) {
            throw new DAOException("Failed to get the shad registry by criteria", e);
        }
        return registry;
    }
    
    
    /* (non-Javadoc)
     * @see gov.va.med.esr.common.persistent.registry.RegistryDAO#save(gov.va.med.esr.common.model.registry.Registry)
     */
    public void save(Registry registry) throws DAOException {
        try {
            HibernateTemplate tmplt = this.getHibernateTemplate();
            tmplt.saveOrUpdate(registry);
        } 
        catch (DataAccessException e) {
            throw new DAOException("Failed to save registry", e);
        }
    }
    
    /* 
     * @see gov.va.med.esr.common.persistent.registry.RegistryDAO#isRegistryExists(gov.va.med.esr.common.model.registry.RegistryTrait)
     */
    public RegistryTrait getRegistryTrait(Registry registry) throws DAOException {
        Validate.notNull(registry,"Registry must not be null");     
        RegistryTrait trait = registry.getRegistryTrait();
        Validate.notNull(trait,"RegistryTrait must not be null");
                        
		Map contextData = new HashMap();
		contextData.put("trait", trait);				        		        	
		HibernateCallback callback = new AbstractDAOAction(contextData) { 
			public Object execute(Session session) {
				RegistryTrait targetTrait = (RegistryTrait) this.getContextData().get("trait");
				boolean isPOW = StringUtils.equals(targetTrait.getRegistryType().getCode(),RegistryType.CODE_POW_REGISTRY.getCode()) ? true : false;
                String hqlQuery = hqlQuery = GET_REGISTRY_TRAIT + " and rt.ssn = :ssn";
                boolean setSsn = true;
                if(isPOW && StringUtils.isEmpty(targetTrait.getSsn())) {
                    hqlQuery = GET_REGISTRY_TRAIT;
                    setSsn = false;
                }
		        Query query = session.createQuery(hqlQuery);
		        query.setString("firstName",targetTrait.getFirstName());
		        query.setString("lastName",targetTrait.getLastName());
		        query.setEntity("registryType",targetTrait.getRegistryType());
		        if(setSsn) query.setString("ssn",targetTrait.getSsn());
                
		        return query.list();				
			}
		};
		List results = this.getHibernateTemplate().executeFind(callback);
        return (results != null && !results.isEmpty()) ? (RegistryTrait)results.get(0) : null;
    }
    
    /* (non-Javadoc)
     * @see gov.va.med.esr.common.persistent.registry.RegistryDAO#find(gov.va.med.fw.service.pagination.SearchQueryInfo)
     */
    public List find(SearchQueryInfo criteria) throws DAOException, MaxRecordsExceededException {        
        RegistrySearchCriteria searchCriteria = (RegistrySearchCriteria)criteria;
        String registryType = (searchCriteria.getRegistryType() != null) ? searchCriteria.getRegistryType().getCode() : null;
        
		Map contextData = new HashMap();
		contextData.put("registryType", registryType);
		contextData.put("searchCriteria", searchCriteria);
		HibernateCallback callback = new AbstractDAOAction(contextData) { 
			public Object execute(Session session) throws DAOException {
				List results = new ArrayList();
				String targetRegistryType = (String) this.getContextData().get("registryType");
				RegistrySearchCriteria targetQueryInfo = (RegistrySearchCriteria) this.getContextData().get("searchCriteria");
		        List paramList = new ArrayList();
		        List valueList = new ArrayList();				
				String whereClause = createWhereClause(targetQueryInfo,paramList,valueList);
				try {
			        if(targetRegistryType != null) {
			            String fromClause = buildFromCaluse(targetQueryInfo,targetRegistryType);               
			            results = find(session, targetQueryInfo,buildCountQuery(fromClause,whereClause),buildDataQuery(fromClause,whereClause,targetRegistryType),paramList,valueList);
			        } else {
			            // Search all the 3 registries
			            
			            // Search PH Registry
			            String fromClause = buildFromCaluse(targetQueryInfo,RegistryType.CODE_PH_REGISTRY.getCode());
			            List phResults = find(session, targetQueryInfo,buildCountQuery(fromClause,whereClause),buildDataQuery(fromClause,whereClause,RegistryType.CODE_PH_REGISTRY.getCode()),paramList,valueList);
			            if(phResults != null && !phResults.isEmpty()) {
			                results.addAll(phResults);
			            }
			            
			            // Search POW Registry
			            fromClause = buildFromCaluse(targetQueryInfo,RegistryType.CODE_POW_REGISTRY.getCode());
			            List powResults = find(session, targetQueryInfo,buildCountQuery(fromClause,whereClause),buildDataQuery(fromClause,whereClause,RegistryType.CODE_POW_REGISTRY.getCode()),paramList,valueList);
			            if(powResults != null && !powResults.isEmpty()) {
			                results.addAll(powResults);
			            }
			            
			            // Search SHAd Registry
			            fromClause = buildFromCaluse(targetQueryInfo,RegistryType.CODE_SHAD_REGISTRY.getCode());
			            List shadResults = find(session, targetQueryInfo,buildCountQuery(fromClause,whereClause),buildDataQuery(fromClause,whereClause,RegistryType.CODE_SHAD_REGISTRY.getCode()),paramList,valueList);
			            if(shadResults != null && !shadResults.isEmpty()) {
			                results.addAll(shadResults);
			            }
			        }
				} catch(MaxRecordsExceededException e) {
					throw new DAOException("max records exceeded", e);
				}
		        return results;
			}
		};
		List results = null;
		try {
			results = this.getHibernateTemplate().executeFind(callback);
		} catch(HibernateSystemException e) {
			MaxRecordsExceededException rootCause = (MaxRecordsExceededException) getRootExceptionOfType(e, MaxRecordsExceededException.class);
			if(rootCause != null)
				throw rootCause;
			throw e;
		}    		

        if(results != null) {
            int maxAllowedRecords = criteria.getMaxAllowedRecords();
            int totalResults = results.size();
            if(totalResults > criteria.getMaxAllowedRecords()) {
                throw new MaxRecordsExceededException(totalResults,maxAllowedRecords);
            }
            criteria.setTotalNumberOfEntries(totalResults);
        }
        return results;
    } 
    
    /* (non-Javadoc)
     * @see gov.va.med.esr.common.persistent.registry.RegistryDAO#getLatestRegistrySummaryByType(gov.va.med.esr.common.model.lookup.RegistryType)
     */
    public RegistryLoadStatistics getMostRecentRegistrySummaryByType(
            RegistryType registryType) throws DAOException
    {
        try
        {
            List results = null;
            HibernateTemplate tpl = this.getHibernateTemplate();
            if (registryType != null) {
                results = tpl.findByNamedQueryAndNamedParam(
                    FIND_LATEST_SUMMARY_BY_REGISTRY_TYPE, PARAM_REGISTRY_TYPE,
                    registryType);
            }else {
                results = tpl.findByNamedQuery(FIND_LATEST_SUMMARY);
            }
            // Should only return one value
            return results.isEmpty() ? null : (RegistryLoadStatistics) results
                    .get(0);
        } catch (DataAccessException e)
        {
            throw new DAOException(
                    "Failed to get Latest Registry Summary By Registry Type "
                            + registryType.getCode(), e);
        }
    }
    
    // ********************************** Private Methods ****************************************//
    
    private List find(Session session, RegistrySearchCriteria searchCriteria,String strCountQuery, String strDataQuery, List paramList, List valueList) 
    throws DAOException, MaxRecordsExceededException {
        
        List results = null;
        try {
            // Build the count and data retrieval queries
            Query countQuery = session.createQuery(strCountQuery);
            Query dataQuery = session.createQuery(strDataQuery);

            for(int i=0; i<paramList.size(); i++) {
                countQuery.setParameter((String)paramList.get(i),valueList.get(i));
                dataQuery.setParameter((String)paramList.get(i),valueList.get(i));
            }
            // Execute the query to retrieve the results
            PaginatedQueryExecutor queryExecutor = new PaginatedQueryExecutor(countQuery,dataQuery,searchCriteria);
            results = queryExecutor.executeQuery();
            
        } catch (HibernateException ex) {
            throw new DAOException("An error occurred while getting message registry entries.", ex);
        }
        return results;
    }

    private String buildCountQuery(String fromClause, String whereClause) {
        return SELECT_CLAUSE_COUNT_QUERY + fromClause +  whereClause;
    }
    
    private String buildDataQuery(String fromClause, String whereClause, String registryType) {
        return buildSelectClause(registryType) + fromClause +  whereClause;
    }
    
    private String buildSelectClause(String registryType) {
        if(StringUtils.equals(RegistryType.CODE_PH_REGISTRY.getName(),registryType)) {
            return "select distinct new gov.va.med.esr.service.RegistrySearchResultBean(r.identifier,rt.ssn,rt.firstName,rt.middleName,rt.lastName,rt.registryType,r.person.identifier,r.status)";
        } if(StringUtils.equals(RegistryType.CODE_POW_REGISTRY.getName(),registryType)) {
            return "select distinct new gov.va.med.esr.service.RegistrySearchResultBean(r.identifier,rt.ssn,rt.firstName,rt.middleName,rt.lastName,rt.registryType,r.person.identifier,r.powIndicator)";
        } if(StringUtils.equals(RegistryType.CODE_SHAD_REGISTRY.getName(),registryType)) {
            return "select distinct new gov.va.med.esr.service.RegistrySearchResultBean(r.identifier,rt.ssn,rt.firstName,rt.middleName,rt.lastName,rt.registryType,r.person.identifier,r.shadIndicator)";
        }
        return SELECT_CLAUSE_DATA_QUERY;
    }
    
    private String buildFromCaluse(RegistrySearchCriteria searchCriteria,String registryType) {
        String registryClassName = getRegistryClassName(registryType);
        String fromClause = " from " + registryClassName + " as r JOIN r.registryTrait as rt ";
        
        //Do an explict left join for indicator and status. Or else hibernate will default it to a inner join
        if(StringUtils.equals(RegistryType.CODE_PH_REGISTRY.getName(),registryType)) {
        	fromClause = fromClause + " LEFT JOIN r.status as st ";
        } if(StringUtils.equals(RegistryType.CODE_POW_REGISTRY.getName(),registryType)) {
        	fromClause = fromClause + " LEFT JOIN r.powIndicator as ind ";
        } if(StringUtils.equals(RegistryType.CODE_SHAD_REGISTRY.getName(),registryType)) {
        	fromClause = fromClause + " LEFT JOIN r.shadIndicator as ind ";
        }        
        
        if(StringUtils.isNotEmpty(searchCriteria.getMilitaryServiceNumber())) {
            fromClause = fromClause + " LEFT JOIN r.registryTrait.internalRegistryTraitDetails as rtd ";
            fromClause = fromClause + " LEFT JOIN r.person as p";
            fromClause = fromClause + " LEFT JOIN p.militaryService.internalMilitaryServiceSiteRecords as mssr";
            fromClause = fromClause + " LEFT JOIN mssr.internalMilitaryServiceEpisodes as mse";
        }
        return fromClause;
    }
    
    private String getRegistryClassName(String registryType) {
        String registryClassName = null;
        if(StringUtils.equals(RegistryType.CODE_PH_REGISTRY.getName(),registryType)) {
            registryClassName = CLASS_NAME_PH_REGISTRY; 
        } if(StringUtils.equals(RegistryType.CODE_POW_REGISTRY.getName(),registryType)) {
            registryClassName = CLASS_NAME_POW_REGISTRY;
        } if(StringUtils.equals(RegistryType.CODE_SHAD_REGISTRY.getName(),registryType)) {
            registryClassName = CLASS_NAME_SHAD_REGISTRY;
        }
        return registryClassName;
    }
    
    private String createWhereClause(RegistrySearchCriteria searchCriteria, List paramList, List valueList) {
        
        List criteriaList = new ArrayList();

        if(StringUtils.isNotEmpty(searchCriteria.getSsn())) {
            criteriaList.add("rt.ssn = :ssn");
            paramList.add("ssn");
            valueList.add(searchCriteria.getSsn());
        }
        
        if(StringUtils.isNotEmpty(searchCriteria.getLastName())) {
            criteriaList.add("upper(rt.lastName) = upper(:lastName)");
            paramList.add("lastName");
            valueList.add(searchCriteria.getLastName());
        }
        
        if(StringUtils.isNotEmpty(searchCriteria.getFirstName())) {
            String firstName = searchCriteria.getFirstName();
            if(firstName.length() == 1) {
                criteriaList.add("upper(rt.firstName) like upper(:firstName)");
                paramList.add("firstName");
                valueList.add(searchCriteria.getFirstName() + "%");
            } else {
                criteriaList.add("upper(rt.firstName) = upper(:firstName)");
                paramList.add("firstName");
                valueList.add(searchCriteria.getFirstName());
            }
        }
        
        if(StringUtils.isNotEmpty(searchCriteria.getMilitaryServiceNumber())) {
            criteriaList.add("(rtd.militaryServiceNumber = :militaryServiceNumber or mse.serviceNumber = :militaryServiceNumber)");
            paramList.add("militaryServiceNumber");
            valueList.add(searchCriteria.getMilitaryServiceNumber());
        }
        
        StringBuffer whereClause = new StringBuffer();
        if(!criteriaList.isEmpty()) {
            whereClause.append(" where ").append(criteriaList.get(0));
            for(int index = 0; index < criteriaList.size(); index++) {
                whereClause.append(" and ").append(criteriaList.get(index));
            }
        }
        return whereClause.toString();
    }


}