package gov.va.med.esr.common.persistent.person.hibernate;

import gov.va.med.esr.common.model.lookup.VOAApplicationStatus;
import gov.va.med.esr.common.model.person.VOAApplication;
import gov.va.med.esr.common.persistent.person.VOAApplicationDAO;
import gov.va.med.fw.conversion.ConversionServiceException;
import gov.va.med.fw.persistent.DAOException;
import gov.va.med.fw.persistent.hibernate.GenericDAOImpl;
import gov.va.med.esr.service.VOASearchCriteria;
import gov.va.med.esr.service.VOASearchResultBean;
import gov.va.med.esr.service.impl.LookupServiceImpl;
import gov.va.med.fw.persistent.MaxRecordsExceededException;
import gov.va.med.fw.persistent.hibernate.AbstractDAOAction;
import gov.va.med.fw.persistent.hibernate.PaginatedQueryExecutor;
import gov.va.med.fw.service.pagination.SearchQueryInfo;
import gov.va.med.esr.common.ui.conversion.CommonVOAResubmissionConversionService;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.Set;

import gov.va.med.fw.util.SortElement;
import gov.va.med.fw.util.StringUtils;

import org.apache.commons.beanutils.BeanComparator;
import org.apache.commons.collections.comparators.NullComparator;
import org.apache.commons.lang.Validate;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateSystemException;

public class VOAApplicationDAOImpl  extends GenericDAOImpl implements VOAApplicationDAO {

	private static final long serialVersionUID = 8793888127514734883L;

    private CommonVOAResubmissionConversionService voaConversionService;

	public static final String VOA_BY_PERSON_ID_QUERY = "VOAApplication_GetByPersonId";
	public static final String VOA_BY_SSN_QUERY = "VOAApplication_GetBySSN";
	public static final String VOA_BY_APPLICATION_ID_QUERY = "VOAApplication_GetByApplicationId";
	public static final String VOA_BY_FQID_QUERY = "VOAApplication_GetByFQID";
	public static final String VOA_RESUBMISSION_APPS_QUERY = "VOAApplication_GetResubmissionApps";
	public static final String PARAM_PERSON_ID = "personId";
	public static final String PARAM_SSN = "ssn";
	public static final String PARAM_APPLICATION_ID = "applicationId";
	public static final String PARAM_FQID = "fqid";
    private static final String SELECT_COUNT_CLAUSE = "SELECT COUNT(*)";
	public static final String PENDING_IDENTITY_TRAITS = ("Pending Identity Traits Update");
	public static final String PERSON_NOT_FOUND = ("Person not found");
	public static final String PROXY_PF_FAILED = ("Proxy PF Add Failed");
	public static final String PERSON_INFO_NOT_ACCEPTED = ("Person Info Not Accepted");
	public static final String CHECK_PERSON_INFO = ("person on the VOA submitted form was not accepted");
	public static final String CHECK_PROXY_PF = ("VOA Workflow Case has been created");
	public static final String PCE = ("Pending Catastrophic Edit (PCE)");
	//private LookupService lookupService = null;
	//private static BigDecimal FEMALE = new BigDecimal(109252);
	//private static BigDecimal MALE = new BigDecimal(109253);
	private static final String DEFAULT_SORT_ORDER = " order by reqRecdDate DESC";

	public static final String TXT_1010EZ = ("1010EZ");
	public static final String TXT_1010EZR = ("1010EZR");
	public static final String TXT_1010HS = ("1010HS");
	public static final String TXT_1010SH = ("1010SH");
	public static final String TXT_1010CG = ("1010CG");
	public static final String TXT_21526EZ = ("21526EZ");   //CCR13857

	public static final BigDecimal ID_1010EZ = new BigDecimal(1);
	public static final BigDecimal ID_1010EZR = new BigDecimal(2);
	public static final BigDecimal ID_1010HS = new BigDecimal(3);
	public static final BigDecimal ID_1010SH = new BigDecimal(4);
	public static final BigDecimal ID_1010CG = new BigDecimal(5);
	public static final BigDecimal ID_21526EZ = new BigDecimal(6); //CCR13857

    private static final String SELECT_VOA_CLAUSE = "SELECT voa.voa_enrollment_app_id applicationId, " +
	    "voa.fully_qualified_id fullqualifiedId, " +
	    "voa.applicant_last_name lastName, voa.applicant_first_name firstName, " +
	    "voa.applicant_ssn ssn, voa.applicant_dob dob, " +
	    "voa.std_gender_id gender, voa.request_received_timestamp reqRecdDate, " +
	    "voa.enrollment_form_type_id formType, voa.error_text errorText";

    //private static final String PENDING_SUBMISSIONS_COUNT_QRY = "voaQuery_getPendingSubmissions";

    private static final String FROM_VOA_CLAUSE = "from " +
    		"voa_enrollment_application voa, voa_resubmission resub " +
        "WHERE voa.voa_enrollment_app_status_id in (4,2) " +
        "AND voa.voa_enrollment_app_id = resub.voa_enrollment_app_id " +
        "AND resub.pending_application_text IS NOT NULL ";
        //"ORDER BY voa.response_timestamp DESC";


	private static Map uiFieldToColumnMap;
    static
    {
    	uiFieldToColumnMap = new HashMap();
    	uiFieldToColumnMap.put("result.applicationId", "applicationId");
    	uiFieldToColumnMap.put("result.fullQualifiedId", "fullQualifiedId");
    	uiFieldToColumnMap.put("result.lastName", "lastName");
    	uiFieldToColumnMap.put("result.firstName", "firstName");
    	uiFieldToColumnMap.put("result.ssn", "ssn");
    	uiFieldToColumnMap.put("result.dob", "dob");
    	uiFieldToColumnMap.put("result.gender", "gender");
    	uiFieldToColumnMap.put("result.reqRecdDate", "reqRecdDate");
    	uiFieldToColumnMap.put("result.formType", "formType");
    	uiFieldToColumnMap.put("result.formTypeValue", "formTypeValue");
    	uiFieldToColumnMap.put("result.errorText", "errorText");
    }


	/**
	 * Looks up ApplicationInProcess record by icn.
	 * @param icn The icn to lookup
	 * @return
	 * @throws DAOException
	 */
	public List getByPersonId(BigDecimal personId) throws DAOException
	{
		Validate.notNull(personId, "personId cannot be null");
		try
        {
			return findByNamedQueryAndNamedParam(VOA_BY_PERSON_ID_QUERY, PARAM_PERSON_ID, personId);
		}
        catch(Exception e)
        {
            throw new DAOException("getByPersonId failed for personId: " + personId, e);
        }

	}

	public List getBySSN(String ssn) throws DAOException
	{
		Validate.notNull(ssn, "ssn cannot be null");
		try
        {
			return findByNamedQueryAndNamedParam(VOA_BY_SSN_QUERY, PARAM_SSN, ssn);
		}
        catch(Exception e)
        {
            throw new DAOException("getByssn failed for ssn: " + ssn, e);
        }

	}

	/**
	 * Looks up a person In-Process or New Application, by ICN.
	 * @param icn The icn to lookup
	 * @return
	 * @throws DAOException
	 */
	public VOAApplication getByApplicationId(BigDecimal applicationId) throws DAOException
	{
		Validate.notNull(applicationId, "applicationId cannot be null");
		try
        {
			List results = findByNamedQueryAndNamedParam(VOA_BY_APPLICATION_ID_QUERY, PARAM_APPLICATION_ID, applicationId);
		    verifyDataIntegrity(applicationId, results);

		    return results.size()==0? null : (VOAApplication)results.get(0);
		}
        catch(Exception e)
        {
            throw new DAOException("getByApplicationId failed for applicationId: " + applicationId, e);
        }
	}

	public List getByFQID(String fqid) throws DAOException {
		Validate.notNull(fqid, "fqid cannot be null");
		try
        {
			return findByNamedQueryAndNamedParam(VOA_BY_FQID_QUERY, PARAM_FQID, fqid);

		}
        catch(Exception e)
        {
            throw new DAOException("getByFQID failed for fqid: " + fqid, e);
        }
	}


	/**
	 * Get VOA Resubmission Applications
	 * @return
	 * @throws DAOException
	 */
	public List getVOAResubmissionApplications() throws DAOException
	{
		try
        {
			return super.getHibernateTemplate().findByNamedQuery(VOA_RESUBMISSION_APPS_QUERY);
		}
        catch(Exception e)
        {
            throw new DAOException("getResubmissionApplications failed", e);
        }
	}

    /**
     * Generic Non Paginated query
     * @param searchQueryInfo
     * @return
     * @throws DAOException
     */
    public List findPendingSubmissions(VOASearchCriteria voaSearchCriteria) throws DAOException{
		Map contextData = new HashMap();
		contextData.put("voaSearchCriteria", voaSearchCriteria);
		HibernateCallback callback = new AbstractDAOAction(contextData) {
			public Object execute(Session session) {
		        Query dataQuery = getSearchSelectDataQueryString(session,
		        		(VOASearchCriteria) getContextData().get("voasearchCriteria"));
		        return dataQuery.list();
			}
		};
		List pendingSubmissions = this.getHibernateTemplate().executeFind(callback);
        //load lazy properties for these VOA Resubmissions
        for (int i=0; i<pendingSubmissions.size(); i++) {
            loadLazyProperties ((VOAApplication) pendingSubmissions.get(i));
        }

        return pendingSubmissions;

    }

    /**
     * Paginated search query
     */
    public List find(SearchQueryInfo searchQueryInfo) throws DAOException, MaxRecordsExceededException {

        VOASearchCriteria voasearchCriteria = (VOASearchCriteria) searchQueryInfo;

		Map contextData = new HashMap();
		contextData.put("voasearchCriteria", voasearchCriteria);
		HibernateCallback callback = new AbstractDAOAction(contextData) {
			public Object execute(Session session) throws DAOException {
				VOASearchCriteria targetQueryInfo = (VOASearchCriteria) getContextData().get("voasearchCriteria");
		        // Build the count and data retrieval queries
		        Query countQuery = getSearchSelectCountQueryString(session, targetQueryInfo);
		        Query dataQuery = getSearchSelectDataQueryString(session, targetQueryInfo);

		        // Execute the query to retrieve the results
		        PaginatedQueryExecutor queryExecutor = new PaginatedQueryExecutor(countQuery,dataQuery,targetQueryInfo);
	            try {
	            	List results = queryExecutor.executeQuery();
                    List pendingSubmissions = new ArrayList ();
                    List applicationids = new ArrayList();
                    for (Iterator i = results.iterator(); i.hasNext();){
                        Object[] objArray = (Object[])i.next();
                        VOASearchResultBean bean = new VOASearchResultBean();
                        try {
							voaConversionService.convert(objArray,bean);
						} catch (ConversionServiceException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
                        pendingSubmissions.add(bean);
                    }
                    return pendingSubmissions;
	            } catch(MaxRecordsExceededException e) {
	            	throw new DAOException("max records exceeded", e);
	            }
			}
		};
		try {

			return this.getHibernateTemplate().executeFind(callback);



		} catch(HibernateSystemException e) {
			MaxRecordsExceededException rootCause = (MaxRecordsExceededException) getRootExceptionOfType(e, MaxRecordsExceededException.class);
			if(rootCause != null)
				throw rootCause;
			throw e;
		}
    }


    public int getPendingSubmissionsCount(VOASearchCriteria voaSearchCriteria) throws DAOException {
		Map contextData = new HashMap();
		contextData.put("voaSearchCriteria", voaSearchCriteria);
		HibernateCallback callback = new AbstractDAOAction(contextData) {
			public Object execute(Session session) {
		        Query query = getSearchSelectCountQueryString(session,
		        		(VOASearchCriteria) getContextData().get("voaSearchCriteria"));
		        return new Integer(executeCountQuery(query));
			}
		};
		return ((Integer) this.getHibernateTemplate().execute(callback)).intValue();
    }


	/**
	 * Remove orphaned records for provided name and id by setting status to complete.
	 * Change orphaned records status to 5
	 *
	 * @param lastName
	 * @param firstName
	 * @return
	 * @throws DAOException
	 */


    /**
     * Count query
     * @param searchQueryInfo
     * @return
     */
    private Query getSearchSelectCountQueryString(Session session, VOASearchCriteria searchCriteria) {

        // Create parameter and value lists
        ArrayList paramList = new ArrayList();
        ArrayList valueList = new ArrayList();

        // Get the count query
        String queryString = SELECT_COUNT_CLAUSE +
        getSearchFromClause(searchCriteria) +
        getSearchWhereClause(searchCriteria, paramList, valueList);

        Query query = session.createSQLQuery(queryString);

        // Prepare the count query by filling in the parameters values
        for (int i = 0; i < paramList.size(); i++) {
        	Object parValue = valueList.get(i);
        	if (parValue != null && parValue instanceof List) {
        		query.setParameterList((String)paramList.get(i), (List)parValue);
        	}
        	else {
        		query.setParameter((String)paramList.get(i), valueList.get(i));
        	}
        }
        return query;
    }
    /**
     * Data retrieval query
     * @param searchQueryInfo
     * @return
     */
    private Query getSearchSelectDataQueryString(Session session, VOASearchCriteria searchCriteria) {
        // Create parameter and value lists
        ArrayList paramList = new ArrayList();
        ArrayList valueList = new ArrayList();

        // Get the count query
        String queryString = SELECT_VOA_CLAUSE + " " +
        getSearchFromClause(searchCriteria) +
        getSearchWhereClause(searchCriteria, paramList, valueList) + " ORDER BY VOA.VOA_ENROLLMENT_APP_ID DESC";
        //getSearchOrderByClause(searchCriteria.getSortElements());

        Query query = session.createSQLQuery(queryString);

        // Prepare the count query by filling in the parameters values
        for (int i = 0; i < paramList.size(); i++){
        	Object parValue = valueList.get(i);
        	if (parValue != null && parValue instanceof List) {
        		query.setParameterList((String)paramList.get(i), (List)parValue);
        	}
        	else {
        		query.setParameter((String)paramList.get(i), parValue);
        	}
        }
        return query;
    }

	private void verifyDataIntegrity(BigDecimal applicationId, List results) throws DAOException {
        if (results.size() > 1) {
     		throw new DAOException("Found more than one record in VOAApplication for applicationId): " + applicationId);
        }
    }

    //Build order by clause based on the sort elements acquired in the UI
    private String getSearchOrderByClause(List sortElements) {
    	StringBuffer clauseBuffer = new StringBuffer();
        if (sortElements != null)
        {
            for (Iterator iterator = sortElements.iterator(); iterator.hasNext();)
            {
                // Get the GUI sort column
                SortElement sortElement = (SortElement)iterator.next();

                // Get the database column associated with the GUI column
                String databaseColumn = (String)uiFieldToColumnMap.get(sortElement.getSortColumn());

                // If a database column exists for the GUI column, add it to the clause
                if (databaseColumn != null)
                {
                    if (clauseBuffer.length() > 0)
                    {
                        clauseBuffer.append(", ").append(databaseColumn).append(" ").append(sortElement.getSortDirection());
                    }
                    else
                    {
                        clauseBuffer.append(" ORDER BY ").append(databaseColumn).append(" ").append(sortElement.getSortDirection());
                    }
                }
                else
                {
                    // A GUI sort column is not supported by the database so don't implement the
                    // sort.  This will cause the PaginatedQueryExecutor to read all the records
                    // and require the GUI to handle the sorting.
                    //return "";
                	clauseBuffer.append(DEFAULT_SORT_ORDER);
                }
            }
        }
        return clauseBuffer.toString();
	}

	private String getSearchFromClause(VOASearchCriteria searchCriteria) {
        //String fromClause = " from VOAApplication v ";     FROM_VOA_CLAUSE
        return FROM_VOA_CLAUSE;
    }

    /**
     * Execute count query
     * @param query
     * @return
     * @throws HibernateException
     */
    private int executeCountQuery(Query query) throws HibernateException {
        List results = query.list();
        int count = 0;
        if (results.size() > 0) {
            Object resultCount = results.get(0);
            if (BigDecimal.class.isAssignableFrom(resultCount.getClass())){
                count = ((BigDecimal) resultCount).intValue();
            }
            else {
                count = ((Integer) resultCount).intValue();
            }
        }
        return count;
    }

    private String getSearchWhereClause(VOASearchCriteria searchCriteria, List paramList, List valueList) {
        List criteriaList = new ArrayList();

       //VOA Submissions
        if (StringUtils.isNotEmpty(searchCriteria.getApplicationId())) {
            criteriaList.add("voa.voa_enrollment_app_id like :applicationId");
            paramList.add("applicationId");
            valueList.add(searchCriteria.getApplicationId());
        }

        Date reqRecdDateFrom = searchCriteria.getReqRecdDateFrom();
        Date reqRecdDateTo = searchCriteria.getReqRecdDateTo();
        Date reqRecdDate = searchCriteria.getReqRecdDate();

        if (reqRecdDate != null) {
        	reqRecdDateFrom = reqRecdDate;
        	reqRecdDateTo = reqRecdDate;
        }

        if (reqRecdDateFrom != null) {
            criteriaList.add(" voa.request_received_timestamp >= :reqRecdDateFrom");
            paramList.add("reqRecdDateFrom");
            valueList.add(reqRecdDateFrom);
        }
        if (reqRecdDateTo != null) {
            criteriaList.add(" voa.request_received_timestamp < :reqRecdDateTo");
            paramList.add("reqRecdDateTo");
            valueList.add(nextDay(reqRecdDateTo));
        }

        if (StringUtils.isNotEmpty(searchCriteria.getFormTypeValue())) {
        	BigDecimal formType = null;
        	String formTypeValue = (searchCriteria.getFormTypeValue());

        	if (TXT_1010EZ.equals(formTypeValue)){
        		formType = ID_1010EZ;
        	}
        	else if (TXT_1010EZR.equalsIgnoreCase(formTypeValue)){
        		formType = ID_1010EZR;
        	}
        	else if (TXT_1010HS.equalsIgnoreCase(formTypeValue)){
        		formType = ID_1010HS;
        	}
        	else if (TXT_1010SH.equalsIgnoreCase(formTypeValue)){
        		formType = ID_1010SH;
        	}
        	else if (TXT_1010CG.equalsIgnoreCase(formTypeValue)){
        		formType = ID_1010CG;
        	}
        	else if (TXT_21526EZ.equals(formTypeValue)){  //CCR13857-new form type for EVSS
        		formType = ID_21526EZ;
        	}
            criteriaList.add("voa.enrollment_form_type_id = :formType");
            paramList.add("formType");
            valueList.add(formType);
        }

        if (StringUtils.isNotEmpty(searchCriteria.getFormPendingReason())) {
        	String errorText = null;
        	String formPendingReason = (searchCriteria.getFormPendingReason());

        	if (PENDING_IDENTITY_TRAITS.equalsIgnoreCase(formPendingReason)){
        		errorText = "%PCE%";
        		searchCriteria.setErrorText(errorText);
        	}
        	if (PERSON_NOT_FOUND.equalsIgnoreCase(formPendingReason)){
        		errorText = "%Person not found%";
        		searchCriteria.setErrorText(errorText);
        	}
        	if (PROXY_PF_FAILED.equalsIgnoreCase(formPendingReason)){
        		errorText = "%VOA Workflow Case has been created%";
        		searchCriteria.setErrorText(errorText);
        	}
        	if (CHECK_PERSON_INFO.equalsIgnoreCase(formPendingReason)){
        		errorText = "%Person Info Not Accepted%";
        		searchCriteria.setErrorText(errorText);
        	}
            criteriaList.add("voa.error_text like :errorText");
            paramList.add("errorText");
            valueList.add(errorText);
        }

        StringBuffer whereClause = new StringBuffer();
        if(!criteriaList.isEmpty()) {
            for(int index = 0; index < criteriaList.size(); index++) {
                whereClause.append(" and ").append(criteriaList.get(index));
            }
        }

        return whereClause.toString();

    }

    private void loadLazyProperties (VOAApplication voaApp){
        //get comments
        //accessSet(voaApp.getApplicationId());
    }

    /**
     * Get next dat
     * @param date
     * @return
     */
    private Date nextDay(Date date) {
        Calendar calendar = Calendar.getInstance();
        if (date != null)
        	calendar.setTime(date);
        //Add a day for the date
        calendar.add(Calendar.DATE, 1);
        return calendar.getTime();
    }

        /**
         * Access the set to initialize the lazy properties
         * @param set
         */
      private void accessSet (Set set){
            for (Iterator i= set.iterator(); i.hasNext();) {
                Object obj = i.next();
                break;
            }
        }

      public CommonVOAResubmissionConversionService getConversionService() {
          return voaConversionService;
      }

      public void setConversionService(CommonVOAResubmissionConversionService voaConversionService) {
          this.voaConversionService = voaConversionService;
      }

}
