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

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

// Library Classes
import org.apache.commons.lang.Validate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateSystemException;

// Framework Classes
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.util.StringUtils;

// ESR Classes
import gov.va.med.esr.common.model.comms.CommsLogEntry;
import gov.va.med.esr.common.model.comms.CommsLogSearchQueryInfo;
import gov.va.med.esr.common.model.comms.HecRejectReason;
import gov.va.med.esr.common.model.comms.MailingStatusLink;
import gov.va.med.esr.common.model.lookup.ComMailingStatusType;
import gov.va.med.esr.common.persistent.comms.CommsLogEntryDAO;
import gov.va.med.esr.service.LookupService;
import gov.va.med.esr.service.UniqueIdentifierGenerator;
import gov.va.med.esr.service.UnknownLookupCodeException;
import gov.va.med.esr.service.UnknownLookupTypeException;

// 3.6 CCR 10960 - removed all references to HANDBOOK.  They have been moved the HandBookDAO 


/**
 * The DAO for CommsLogEntry.
 *
 * @author DNS   TSAIG
 */
public class CommsLogEntryDAOImpl extends GenericDAOImpl implements CommsLogEntryDAO
{
    private static final String FIND_BY_BARCODE = "commsLogQuery_FindByBarcode";
    private static final String FIND_BY_PERSON_ID = "commsLogQuery_FindByPersonId";
    private static final String[] barcodeParamFields = {"barcode"};
    private static final String[] personIdParamFields = {"personId"};

    private UniqueIdentifierGenerator generator = null;

    /**
     * An instance of lookupService
     */
    protected LookupService lookupService = null;

    private static final long serialVersionUID = 7401171707522279133L;

    public CommsLogEntryDAOImpl()
    {
        super();
    }

    /**
     * @param lookupService The lookupService to set.
     */
    public void setLookupService(LookupService lookupService) {
       this.lookupService = lookupService;
    }
    
    public CommsLogEntry findCommsLogEntryByBarcode(String barcode) throws DAOException
    {
        // Ensure case-insensitive lookup
        barcode = barcode.trim().toUpperCase();

        // Build the parameter values
        String[] barcodeParamValues = new String[]{barcode};
        try
        {
            // Get the results
            List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(FIND_BY_BARCODE,
                barcodeParamFields, barcodeParamValues);

            // Return the first result if any results are present.
            if (results != null && results.size() > 0)
            {
                return (CommsLogEntry)results.get(0);
            }

        }
        catch (Exception ex)
        {
            throw new DAOException("Failed to find CommsLogEntry by barcode: " + barcode, ex);
        }

        // Return null if no results were returned.
        return null;
    }

    public List findCommsLogListByPersonId(String personId) throws DAOException
    {
        BigDecimal[] personIdParamValues = new BigDecimal[]{new BigDecimal(personId)};
        try
        {
            return super.getHibernateTemplate().findByNamedQueryAndNamedParam(FIND_BY_PERSON_ID,
                personIdParamFields, personIdParamValues);
        }
        catch (Exception ex)
        {
            throw new DAOException("Failed to find CommsLogEntries based on person Id: " + personId, ex);
        }
    }
 
    public void update(CommsLogEntry log) throws DAOException
    {
        super.getHibernateTemplate().update(log);
    }

    public void merge(CommsLogEntry log) throws DAOException
    {
        super.getHibernateTemplate().merge(log);
    }
    
    /**
     * Inserts a new CommsLogEntry into the database.  A barcode will automatically be generated.
     * @param log The comms log entry to insert.
     * @throws DAOException if any database problems were encountered.
     */
    public void insert(CommsLogEntry log) throws DAOException
    {
        // Set the barcode
        log.setBarcode(generateBarcode(log));

        // Persist the entry
        saveObject(log);
    }

    /**
     * Generates the barcode.  The barcode has the form of ABBC where "A" is the first character of the
     * letter type (e.g. I for IVM letter type), "BB" is the 2 digit year (e.g. 06 is for 2006), and
     * "C" is a database sequence number that will reset each year.
     *
     * @param log The comms log entry
     * @return The barcode
     */
    protected String generateBarcode(CommsLogEntry log)
    {
        // Ensure we have the required objects to generate a barcode
        Validate.notNull(log, "A CommsLogEntry is required to generate a barcode.");
        Validate.notNull(log.getLetterType(), "A letter type is required to generate a barcode.");
        Validate.notEmpty(log.getLetterType().getDescription(), "A letter type description is required to generate a barcode.");

        // Get the current year
        String year = String.valueOf(Calendar.getInstance().get(Calendar.YEAR));

        // Return the barcode
        return log.getLetterType().getDescription().substring(0, 1) +
            year.substring(year.length()-2, year.length()) +
            generator.generate().toString();
    }

    public void insertMailingStatus(MailingStatusLink mailStat) throws DAOException
    {
        insertObject(mailStat);
    }

    public void insertHecRejectReason(HecRejectReason rejReason) throws DAOException
    {
        insertObject(rejReason);
    }

    /**
     * Finds a list of CommsLogEntry objects based on the passed in search criteria.
     *
     * @param searchQueryInfo The search query information
     *
     * @return The list of CommsLogEntry objects
     * @throws DAOException if there were any problems retrieving the data
     * @throws MaxRecordsExceededException if too many records matches the criteria
     */
    public List find(CommsLogSearchQueryInfo searchQueryInfo) throws DAOException,
        MaxRecordsExceededException
    {
		Map contextData = new HashMap();
		contextData.put("searchQueryInfo", searchQueryInfo);				        		        	
		HibernateCallback callback = new AbstractDAOAction(contextData) { 
			public Object execute(Session session) throws DAOException {
	            // Build the count and data retrieval queries
	            List paramList = new ArrayList();
	            List valueList = new ArrayList();
				CommsLogSearchQueryInfo targetQueryInfo = (CommsLogSearchQueryInfo) getContextData().get("searchQueryInfo");
				
	            String whereClause;
				try {
					whereClause = createWhereClause(targetQueryInfo,
							paramList, valueList);
				} catch (Exception e) {
					throw new DAOException(e.getMessage(), e);
				}
				
	            Query countQuery = session.createQuery(
	                "select count(*) from CommsLogEntry log join log.mailingStatusLinks status" + whereClause);
	            Query dataQuery = session.createQuery(
	                "select log from CommsLogEntry log join log.mailingStatusLinks status" + whereClause);

/*
  FROM com_mailing a, com_mailing_status_detail b
 WHERE a.com_mailing_id = b.com_mailing_id
   AND b.com_mailing_status_type_id = 1615183
   AND UPPER (a.last_name) = UPPER ('wisden')
   AND EXISTS (
          SELECT   NULL
              FROM com_mailing_status_detail d
             WHERE d.com_mailing_id = b.com_mailing_id
          GROUP BY d.com_mailing_id
            HAVING MAX (d.com_mailing_status_detail_id) =
                                                b.com_mailing_status_detail_id);

*/
                // Set the parameters in the where clause
	            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, targetQueryInfo);
	            try {
	            	return queryExecutor.executeQuery();
	            } 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;
		}
    }
    

    /**
     * Builds the where clause for the CommsLogEntry search.
     *
     * @param searchQueryInfo The search query info
     * @param paramList The parameter list
     * @param valueList The values list
     *
     * @return The where clause
     * @throws UnknownLookupCodeException 
     * @throws UnknownLookupTypeException 
     */
    private String createWhereClause(CommsLogSearchQueryInfo searchQueryInfo, List paramList,
        List valueList) throws UnknownLookupTypeException, UnknownLookupCodeException
    {
        List criteriaList = new ArrayList();

        // Add a filter to ensure the last status is "mailed by AAC"
    	ComMailingStatusType statusType = this.lookupService.getComMailingStatusTypeByCode(ComMailingStatusType.MAILED_BY_AAC.getName());  	
        criteriaList.add("status.mailingStatus.identifier = " + statusType.getIdentifier());

        // Barcode
        if (!StringUtils.isEmpty(searchQueryInfo.getBarcode()))
        {
            // Ensure case-insensitive lookup
            String barcode = searchQueryInfo.getBarcode().trim().toUpperCase();

            criteriaList.add("log.barcode = :barcode");
            paramList.add("barcode");
            valueList.add(barcode);
        }

        // SSN
        if (!StringUtils.isEmpty(searchQueryInfo.getSSN()))
        {
            criteriaList.add("log.ssn.ssnText = :ssn");
            paramList.add("ssn");
            valueList.add(searchQueryInfo.getSSN());
        }

        // Names
        if (!StringUtils.isEmpty(searchQueryInfo.getGivenName()))
        {
            criteriaList.add("upper(log.name.givenName) = upper(:givenName)");
            paramList.add("givenName");
            valueList.add(searchQueryInfo.getGivenName());
        }
        if (!StringUtils.isEmpty(searchQueryInfo.getMiddleName()))
        {
            criteriaList.add("upper(log.name.middleName) = upper(:middleName)");
            paramList.add("middleName");
            valueList.add(searchQueryInfo.getMiddleName());
        }
        if (!StringUtils.isEmpty(searchQueryInfo.getFamilyName()))
        {
            criteriaList.add("upper(log.name.familyName) = upper(:familyName)");
            paramList.add("familyName");
            valueList.add(searchQueryInfo.getFamilyName());
        }

        // Form Number
        if (searchQueryInfo.getFormNumber() != null &&
            !StringUtils.isEmpty(searchQueryInfo.getFormNumber().getCode()))
        {
            criteriaList.add("upper(log.template.commsTemplateFormNumber) = upper(:formNumber)");
            paramList.add("formNumber");
            valueList.add(searchQueryInfo.getFormNumber().getCode());
        }

        // Case Number
        if (searchQueryInfo.getCaseNumber() != null)
        {
            criteriaList.add("log.workflowCaseId = :caseNumber");
            paramList.add("caseNumber");
            valueList.add(searchQueryInfo.getCaseNumber());
        }

        // Date Mailed
        if (searchQueryInfo.getDateMailed() != null)
        {
            criteriaList.add("trunc(log.mailingDate) = :dateMailed");
            paramList.add("dateMailed");
            valueList.add(searchQueryInfo.getDateMailed());
        }

        // Address
        if (!StringUtils.isEmpty(searchQueryInfo.getAddressLine1()))
        {
            criteriaList.add("upper(log.address.line1) = upper(:addressLine1)");
            paramList.add("addressLine1");
            valueList.add(searchQueryInfo.getAddressLine1());
        }
        if (!StringUtils.isEmpty(searchQueryInfo.getAddressLine2()))
        {
            criteriaList.add("upper(log.address.line2) = upper(:addressLine2)");
            paramList.add("addressLine2");
            valueList.add(searchQueryInfo.getAddressLine2());
        }
        if (!StringUtils.isEmpty(searchQueryInfo.getAddressLine3()))
        {
            criteriaList.add("upper(log.address.line3) = upper(:addressLine3)");
            paramList.add("addressLine3");
            valueList.add(searchQueryInfo.getAddressLine3());
        }
        if (!StringUtils.isEmpty(searchQueryInfo.getCity()))
        {
            criteriaList.add("upper(log.address.city) = upper(:city)");
            paramList.add("city");
            valueList.add(searchQueryInfo.getCity());
        }
        if (searchQueryInfo.getState() != null &&
            !StringUtils.isEmpty(searchQueryInfo.getState().getCode()))
        {
            criteriaList.add("upper(log.address.state) = upper(:state)");
            paramList.add("state");
            valueList.add(searchQueryInfo.getState().getCode());
        }
        if (!StringUtils.isEmpty(searchQueryInfo.getCounty()))
        {
            criteriaList.add("upper(log.address.county) = upper(:county)");
            paramList.add("county");
            valueList.add(searchQueryInfo.getCounty());
        }
        if (!StringUtils.isEmpty(searchQueryInfo.getProvince()))
        {
            criteriaList.add("upper(log.address.province) = upper(:province)");
            paramList.add("province");
            valueList.add(searchQueryInfo.getProvince());
        }
        if (!StringUtils.isEmpty(searchQueryInfo.getPostalCode()))
        {
            criteriaList.add("upper(log.address.postalCode) = upper(:postalCode)");
            paramList.add("postalCode");
            valueList.add(searchQueryInfo.getPostalCode());
        }
        if (!StringUtils.isEmpty(searchQueryInfo.getZipPlus4()))
        {
            criteriaList.add("upper(log.address.zipPlus4) = upper(:zipPlus4)");
            paramList.add("zipPlus4");
            valueList.add(searchQueryInfo.getZipPlus4());
        }
        if (!StringUtils.isEmpty(searchQueryInfo.getCountry()))
        {
            criteriaList.add("upper(log.address.country) = upper(:country)");
            paramList.add("country");
            valueList.add(searchQueryInfo.getCountry());
        }
        if (!StringUtils.isEmpty(searchQueryInfo.getZipCode()))
        {
            criteriaList.add("upper(log.address.zipCode) = upper(:zipCode)");
            paramList.add("zipCode");
            valueList.add(searchQueryInfo.getZipCode());
        }

        // Finally, add a filter to only get the last status of all the statuses
        criteriaList.add("EXISTS " +
        		"(SELECT 1 FROM MailingStatusLink status1 " +
        		"WHERE status1.commsLog.identifier = status.commsLog.identifier " +
        		"GROUP BY status1.commsLog.identifier " +
        		"HAVING MAX (status1.identifier) = status.identifier)");

        StringBuffer whereClause = new StringBuffer();
        if (!criteriaList.isEmpty())
        {
            whereClause.append(" where ").append(criteriaList.get(0));

            for (int index = 1; index < criteriaList.size(); index++)
            {
                whereClause.append(" and ").append(criteriaList.get(index));
            }
        }

       return whereClause.toString();
    }

    public UniqueIdentifierGenerator getGenerator()
    {
        return generator;
    }

    public void setGenerator(UniqueIdentifierGenerator generator)
    {
        this.generator = generator;
    }
}
