package gov.va.vinci.dart.dms.db.impl;

import gov.va.vinci.dart.db.util.HibernateDAO;
import gov.va.vinci.dart.dms.biz.Document;
import gov.va.vinci.dart.dms.db.DocumentDAO;

import java.util.List;

import javax.persistence.NoResultException;
import javax.persistence.Query;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class DocumentDAOImpl extends HibernateDAO implements DocumentDAO {
	private static Log log = LogFactory.getLog(DocumentDAOImpl.class);
	
	@Override
	public Document findById(final int documentId) {
		Query q = createQuery("from Document where id=:did");
		q.setParameter("did", documentId);
		
		Document doc = null;
		try {
			doc = (Document)q.getSingleResult();
		} catch( Exception e ) {
			log.error("Exception retrieving documentId:  " + documentId);
			//log.error( e.getStackTrace() );
		}
		
		return doc;
	}

	@Override
	@SuppressWarnings("unchecked")
	public List<Document> listAll() {
		Query q = createQuery("from Document");
		try {
			return (List<Document>)q.getResultList();
		} catch (NoResultException e) {
			return null;
		}
	}
		
	
	@Override
	@SuppressWarnings("unchecked")
	public List<Document> listVersionsById(final int documentId) {
		Query q = createQuery("from Document doc where (doc.id=:docid or doc.head=:docid)");
		q.setParameter("docid", documentId);
		try {
			return (List<Document>)q.getResultList();
		} catch (NoResultException e) {
			return null;
		}
	}
	
	@Override
	@SuppressWarnings("unchecked")
	public List<Document> listVersionsByReqIdandTemplIdandContId(final int requestId, final int templateId, final int contentId) {
		Query q = createQuery("from Document doc where doc.request.id=:reqid and doc.template.id=:tempid and content.id=:cid)");
		q.setParameter("reqid", requestId);
		q.setParameter("tempid", templateId);
		q.setParameter("cid", contentId);
		try {
			return (List<Document>)q.getResultList();
		} catch (NoResultException e) {
			return null;
		}
	}		
	
	@Override
	@SuppressWarnings("unchecked")
	public List<Document> listVersionsByReqIdandTemplId(final int requestId, final int templateId) {
		Query q = createQuery("from Document doc where doc.request.id=:reqid and doc.template.id=:tempid)");
		q.setParameter("reqid", requestId);
		q.setParameter("tempid", templateId);
		try {
			return (List<Document>)q.getResultList();
		} catch (NoResultException e) {
			return null;
		}
	}	
	
	@Override
	@SuppressWarnings("unchecked")
	public List<Document> listByContentId(final int contentId) {
		Query q = createQuery("from Document doc where content.id=:cid");
		q.setParameter("cid", contentId);
		try {
			return (List<Document>)q.getResultList();
		} catch (NoResultException e) {
			return null;
		}
	}
	
	@Override
	@SuppressWarnings("unchecked")
	public List<Document> listByRepositoryId(final int repositoryId) {
		Query q = createQuery("from Document doc where (content is not null and content.repository is not null and content.repository.id=:rid)");
		q.setParameter("rid", repositoryId);
		try {
			return (List<Document>)q.getResultList();
		} catch (NoResultException e) {
			return null;
		}
	}
	
	/**
	 * Returns a list of Documents for only this specific request (NOT the whole request chain) and this specific contentId.
	 * 
	 * Returns null if no Documents are retrieved.
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<Document> listByRequestAndNonNullContentId(final int requestId, final int contentId) {
		Query q = createQuery("from Document doc where doc.request.id=:rid and (content is not null and content.id=:cid)");
		q.setParameter("cid", contentId);
		q.setParameter("rid", requestId);
		try {
			return (List<Document>)q.getResultList();
		} catch (NoResultException e) {
			return null;
		}
	}

	
	@Override
	@SuppressWarnings("unchecked")
	public Document findCurrentVersion(final int documentId) {
		Query q = createQuery("from Document doc"+ 
			" where (doc.head=(select d2.head from Document d2 where d2.id=:docid))"+ 
			" and exists(select lbl.value from Label lbl where lbl.document.id=doc.id and value like 'CURRENT')"+
			" order by doc.id desc");
		q.setParameter("docid", documentId);
		
		List<Document> tmpList;
		try {
			tmpList = (List<Document>)q.getResultList();
		} catch (NoResultException e) {
			return null;
		}
		
		if (tmpList != null && tmpList.size() > 0) {
			return tmpList.get(0);
		}
		
		return null;
	}
	
	// TODO- this won't return the correct result if the most recent version has a null updatedOn value
	@Override
	@SuppressWarnings("unchecked")
	public Document findMostRecentVersion(final int documentId) {
		Query q = createQuery("from Document d where (d.id=:docid or d.head=:docid) and d.updatedOn=(select max(doc.updatedOn) FROM Document doc where (doc.id=:docid or doc.head=:docid)) order by d.id desc");
		q.setParameter("docid", documentId);
		
		List<Document> tmpList;
		
		try {
			tmpList = (List<Document>)q.getResultList();
		} catch (NoResultException e) {
			return null;
		}
		
		if (tmpList != null && tmpList.size() > 0) {
			return tmpList.get(0);
		}

		return null;
	}	
	
	@Override
	@SuppressWarnings("unchecked")
	public Document findMostRecentSortorder(final int requestId, final int sortorderId, final int contentId) {
		Query q = createQuery("from Document d where d.request.id=:reqid and d.sortOrder=:sortid and d.content.id=:contid order by d.id desc");
		q.setParameter("reqid", requestId);
		q.setParameter("sortid", sortorderId);
		q.setParameter("contid", contentId);
		
		List<Document> tmpList;
		
		try {
			tmpList = (List<Document>)q.getResultList();
		} catch (NoResultException e) {
			return null;
		}
		
		if (tmpList != null && tmpList.size() > 0) {
			return tmpList.get(0);
		}
		return null;
	}	
	
	// check the RequestAdminLocationDocument, RequestAdminParticipantDocument, RequestLocationDocument, RequestParticipantDocument
	// in that order for a location name associated with the document
	@Override
	public String findLocationByDocId(final int documentId) {
		Query q = createNativeQuery("select case when rald.DocumentID is not null then admLoc.Name "+
				" when rapd.documentID is not null then AdmPartloc.Name "+
				" when rld.DocumentID is not null then l.Name "+
				" when rpd.DocumentID is not null then l2.Name "+
				" else null end as DocumentLocation"+
				" from hib.Document doc "+
				" left join [hib].[requestadminlocationdocument] rald on doc.ID = rald.DocumentID "+
				" left join hib.Location AdmLoc on rald.locationid = AdmLoc.ID "+
				" left join [hib].[requestadminparticipantdocument] rapd on doc.ID = rapd.documentid "+
				" left join hib.participant admPart on rapd.participantid  = admPart.ID "+
				" left join hib.Location AdmPartloc on admPart.LocationID = AdmPartLoc.ID "+
				" left join  [hib].[requestlocationdocument] rld on doc.ID = rld.documentid "+
				" left join hib.Location l on rld.LocationID = l.ID "+
				" left join [hib].[requestparticipantdocument] rpd on doc.ID = rpd.documentid "+
				" left join hib.Participant p on rpd.ParticipantID = p.ID "+
				" left join hib.Location l2 on p.locationid = l2.id "+
				" where doc.ID =:docid");
		q.setParameter("docid", documentId);
		try {
			return (String)q.getSingleResult();
		} catch (NoResultException e) {
			return null;
		}
	}
	
	// check the RequestAdminParticipantDocument, and RequestParticipantDocument in that order for the name of the participant associated with a document
	@Override
	public String findParticipantNameByDocId(final int documentId) {
		Query q = createNativeQuery("select case when rapd.documentID is not null then pers1.FullName "+
				" when rpd.DocumentID is not null then pers2.Fullname end as PersonFullName "+
				" from hib.Document doc "+
				" left join [hib].[requestadminlocationdocument] rald on doc.ID = rald.DocumentID "+
				" left join hib.Location AdmLoc on rald.locationid = AdmLoc.ID "+
				" left join [hib].[requestadminparticipantdocument] rapd on doc.ID = rapd.documentid "+
				" left join hib.participant admPart on rapd.participantid = admPart.ID "+
				" left join hib.Location AdmPartloc on admPart.LocationID = AdmPartLoc.ID "+
				" left join hib.Person pers1 on admpart.PersonID = pers1.ID "+
				" left join  [hib].[requestlocationdocument] rld on doc.ID = rld.documentid "+
				" left join hib.Location l on rld.LocationID = l.ID "+
				" left join [hib].[requestparticipantdocument] rpd on doc.ID = rpd.documentid "+
				" left join hib.Participant p on rpd.ParticipantID = p.ID "+
				" left join hib.Location l2 on p.locationid = l2.id "+
				" left join hib.Person pers2 on p.PersonID = pers2.ID "+
				" where doc.ID=:docid");
		q.setParameter("docid", documentId);
		try {
			return (String)q.getSingleResult();
		} catch (NoResultException e) {
			return null;
		}
	}

	@Override
	public void save(Document document) {
		if (document == null) {
			throw new IllegalArgumentException();
		}
		
		HibernateDAO.save(document);
	}

	@Override
	public void delete(Document document) {
		if (document == null) {
			throw new IllegalArgumentException();
		}
		
		HibernateDAO.delete(document);
	}
	
    @Override
    public int countRequestAndNonNullContentId(final int requestId,final int contentId) {
        Query q = createNativeQuery("SELECT count(*) FROM [hib].[document] doc where doc.requestid=:rid and doc.contentid=:cid");
		q.setParameter("cid", contentId);
		q.setParameter("rid", requestId);
        int iVal = (Integer) q.getSingleResult();

        return iVal;
    }
	
}
