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

import static gov.va.vinci.dart.common.ESAPIValidationType.ACCESS_CONTROL_DB;
import static gov.va.vinci.dart.common.ESAPIValidator.validateStringInput;
import static gov.va.vinci.dart.common.ESAPIValidator.validateIntegerInput;

import java.util.ArrayList;
import java.util.List;

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

import gov.va.vinci.dart.biz.DocumentReviewNote;
import gov.va.vinci.dart.db.DocumentReviewNoteDAO;
import gov.va.vinci.dart.db.util.HibernateDAO;

public class DocumentReviewNoteDAOImpl extends HibernateDAO implements DocumentReviewNoteDAO {

	@Override
	public DocumentReviewNote findById(final int documentId, final String createdBy) {
		Query query = createQuery("from DocumentReviewNote where document.id=:did and createdBy=:cby");
		query.setParameter("did", documentId);
		query.setParameter("cby", createdBy);
		return (DocumentReviewNote)query.getSingleResult(); 
	}
	
	@Override
	@SuppressWarnings("unchecked")
	public List<DocumentReviewNote> listByReviewerId(final String createdBy, final int documentId, final int groupId) {
		Query query = createQuery("from DocumentReviewNote where document.id=:did and createdBy=:cby and group.id=:gid order by createdOn desc");
		query.setParameter("did", documentId);
		query.setParameter("cby", createdBy);
		query.setParameter("gid", groupId);
		try {
			return (List<DocumentReviewNote>)query.getResultList();
		} catch (NoResultException e) {
			return new ArrayList<DocumentReviewNote>();
		}
	}
	
	@Override
	@SuppressWarnings("unchecked")
	public List<DocumentReviewNote> listByGroupId(final int documentId, final int groupId) {
		Query query = createQuery("from DocumentReviewNote where document.id=:did and group.id=:gid order by createdOn desc");
		query.setParameter("did", documentId);
		//query.setParameter("cby", createdBy);
		query.setParameter("gid", groupId);
		try {
			return (List<DocumentReviewNote>)query.getResultList();
		} catch (NoResultException e) {
			return new ArrayList<DocumentReviewNote>();
		}
	}	

	@Override
	@SuppressWarnings("unchecked")
	public List<DocumentReviewNote> listByGroupIdForDocumentVersions(final int documentHeadId, final int groupId) {
		Query query = createQuery("from DocumentReviewNote where (document.id=:did or document.head=:did) and group.id=:gid order by createdOn desc");
		query.setParameter("did", documentHeadId);
		query.setParameter("gid", groupId);
		try {
			return (List<DocumentReviewNote>)query.getResultList();
		} catch (NoResultException e) {
			return new ArrayList<DocumentReviewNote>();
		}
	}
	
    @Override
    public int countDocumentVersions(final int documentHeadId, final int groupId) {
    	int validDocumentHeadId = validateIntegerInput(String.valueOf(documentHeadId));
    	int validGroupId = validateIntegerInput(String.valueOf(groupId));
        Query q = createNativeQuery("SELECT count(*) FROM [hib].[documentreviewnote] note, [hib].[group] grp where note.documentid=:did and grp.id=:gid");
        q.setParameter("did", validDocumentHeadId);
		q.setParameter("gid", validGroupId);
        int iVal = (Integer) q.getSingleResult();

        return iVal;
    }

//	@Override
//	@SuppressWarnings("unchecked")
//	public List<DocumentReviewNote> listByGroupIdForRequestAndDocumentContent(final int requestId, final int documentId, final int contentId, final int groupId) {
//		
//		//	Amendment:
//		//		for all requests in the chain up to and including this amendment (based on head and tracking number), get the document review notes
//		//			when creating an amendment, we copy the most recent version of the document (new requestID, new documentID), (same documenttemplateid, same contentID)
//		//				when creating an amendment, documentID changes, but contentID stays the same (should probably ignore NULL content values)
//		//			when uploading a new document, we create a new content entry (new contentID), (same headID)
//		
//		Query query = createQuery("from DocumentReviewNote where document.request.id=:rid and group.id=:gid and ((document.id=:did or document.head=:did) or (document.content.id=:cid)) order by createdOn desc");
//		
//		query.setParameter("rid", requestId);
//		query.setParameter("gid", groupId);
//		query.setParameter("did", documentId);
//		query.setParameter("cid", contentId);
//		
//		try {
//			return (List<DocumentReviewNote>)query.getResultList();
//		} catch (NoResultException e) {
//			return new ArrayList<DocumentReviewNote>();
//		}
//	}		
	
//select * from hib.requestlocationdocument rld
//	left join hib.document d on rld.documentid=d.id 
//	left join hib.documentreviewnote rn on rn.documentid=d.id
//	left join hib.location l on rld.locationid=l.id
//where d.requestid in (select id from hib.request where trackingnumber like '%2011-05-004-D%') 
//	 and rld.active=1	--active location document
//	 and rn.id is not null
//	 and l.id=106
//order by rld.locationid,rn.documentid
	@Override
	public Integer countNotesForActiveLocationDocsAndGroupId(final int requestHeadId, final int locationId, final int groupId) {
		Query query = createNativeQuery("select count(*) from hib.requestlocationdocument rld " +
											" left join hib.document d on rld.documentid=d.id " +
											" left join hib.documentreviewnote rn on rn.documentid=d.id " +
											" left join hib.location l on rld.locationid=l.id " +
										" where d.requestid in (select id from hib.request where id=:hid or headid=:hid) " + 
											 " and rld.active=1 " +
											 " and rn.id is not null " +
											 " and l.id=:locid " +
											 " and rn.roleid=:gid");

		query.setParameter("hid", requestHeadId);
		query.setParameter("locid", locationId);
		query.setParameter("gid", groupId);
		
		return (Integer)query.getSingleResult();
	}
	
	
//select count(*) from hib.requestparticipantdocument rpd
//	left join hib.document d on rpd.documentid=d.id 
//	left join hib.documentreviewnote rn on rn.documentid=d.id
//	left join hib.participant part on rpd.participantid=part.id
//where d.requestid in (select id from hib.request where id=218 or headid=218) 
//	 and rpd.active=1	--active participant document
//	 and rn.id is not null
//	 and part.id=27631
//	 and rn.roleid=1
	@Override
	public Integer countNotesForActiveParticipantDocsAndGroupId(final int requestHeadId, final int participantId, final int groupId) {
		Query query = createNativeQuery("select count(*) from hib.requestparticipantdocument rpd " + 
											" left join hib.document d on rpd.documentid=d.id  " + 
											" left join hib.documentreviewnote rn on rn.documentid=d.id " + 
											" left join hib.participant part on rpd.participantid=part.id " + 
										" where d.requestid in (select id from hib.request where id=:hid or headid=:hid) " + 
											" and rpd.active=1 " + 
											" and rn.id is not null " + 
											" and part.id=:pid " + 
											" and rn.roleid=:gid");

		query.setParameter("hid", requestHeadId);
		query.setParameter("pid", participantId);
		query.setParameter("gid", groupId);
		
		return (Integer)query.getSingleResult();
	}
	
	
	@Override
	public void save(DocumentReviewNote note) {
		if (note == null) {
			throw new IllegalArgumentException();
		}
		
		HibernateDAO.save(note);
	}
}
