package gov.va.vinci.dart.db;

import java.sql.Timestamp;

import javax.persistence.Query;

import gov.va.vinci.dart.biz.RequestLocationDocumentSummary;
import gov.va.vinci.dart.db.util.NativeQueryDAO;

public class RequestLocationDocumentSummaryDAO  extends NativeQueryDAO<RequestLocationDocumentSummary> {

	private int requestId;
	private String reviewedBy;
	
	public RequestLocationDocumentSummaryDAO(final int requestId, final String reviewedBy) {
		this.requestId = requestId;
		this.reviewedBy = reviewedBy;
	}
	
	public String getQueryText() {
		return "select d.id as documentid,l.id as siteid,l.name as sitename,"+
		" case"+
		"  when r.primarysiteid=l.id and r.requesttype=2 then ' ( Primary Site )'"+
		"  else '' end as primarysite,"+
		"  d.name as docname,"+
		" case"+
		"  when lbl.value='CURRENT' then 'CURRENT'"+
		"  else '' end as [current],"+
		" d.sortorder,"+
		" d.head,"+
		" p1.fullname as creator,"+
		" d.createdon,"+
		" p2.fullname as updator,"+
		" d.updatedon,"+
		" d.versionnumber,"+
		" d.description,"+
		" drs.status,"+
		" drn.id as reviewnoteid, "+
		" drn.createdon as reviewcreated,"+
		" drn.text as reviewnote,"+
		" ds.id as datasourceid,"+
		" ds.name as datasourcename"+
		" from [hib].[requestlocationdocument] rld"+
		" inner join [hib].[request] r on r.id=rld.requestid"+
		" inner join [hib].[location] l on rld.locationid=l.id"+
		" inner join [hib].[document] d on (d.id=rld.documentid or d.head=rld.documentid)"+
		" left outer join [hib].[label] lbl on (lbl.documentid=d.id)"+
		" left outer join [hib].[person] p1 on (p1.name=d.createdby)"+
		" left outer join [hib].[person] p2 on (p2.name=d.updatedby)"+
		" left outer join [hib].[documentreviewstatus] drs on (drs.documentid=d.id and exists (select pg.personid from [hib].[persongroup] pg inner join [hib].[person] p3 on p3.id=pg.personid where p3.name=? and pg.groupid=drs.groupid) ) "+
		" left outer join [hib].[documentreviewnote] drn on (drn.documentid=d.id and drn.createdby=?)"+
		" inner join [hib].[researchstudydatasource] rds on rds.researchstudyid=d.requestid"+
		" inner join [hib].[datasource] ds on ds.id=rds.datasourceid"+
		" where rld.requestid=?"+
		" and rld.active=1"+
		" order by primarysite desc,sitename,sortorder,versionnumber desc, datasourceid";
	}
	
	public void populateParameters(final Query q) {
		q.setParameter(3, requestId);
		q.setParameter(1, reviewedBy); 
		q.setParameter(2, reviewedBy); 
	}

	public RequestLocationDocumentSummary buildResult(final Object obj) {
		RequestLocationDocumentSummary summary = new RequestLocationDocumentSummary();

		if (obj.getClass().isArray()) {
			Object[] column = (Object[]) obj;
			
			summary.setDocumentId((Integer)column[0]);
			summary.setSiteId((Integer)column[1]);
			summary.setSiteName((String)column[2] + (String)column[3]);
			summary.setDocumentName((String)column[4]);
//4-docname			
//5 - current
//6 - sortorder
			summary.setSortOrder((Integer)column[6]);
			summary.setHeadId((Integer)column[7]);
			summary.setCreatedBy((String)column[8]);
			summary.setCreatedOn((Timestamp)column[9]);
//8-creator
//9-createdon
			summary.setUpdatedBy((String)column[10]);
			summary.setUpdatedOn((Timestamp)column[11]);
//10-updateor
//11-updatedon
//12-versionnumber
			summary.setVersionNumber((Integer)column[12]);
			
//13-description

			if (column[13] != null) {
				summary.setDescription((String)column[13]);
			}
			else {
				summary.setDescription((String)column[4]);  // document name
			}
			
//14-status
			summary.setReviewStatus((String)column[14]);

//15-review note id
//16-reviewcreated
//17 -reviewnote
			if (column[15] != null) {
				summary.setReviewNoteId((Integer)column[15]);
			}
			if (column[16] != null) {
				summary.setReviewNoteCreatedOn((Timestamp)column[16]);
			}
			if (column[17] != null) {
				summary.setReviewNote((String)column[17]);
			}
			
//18-datasourceid
//19-datasourcename
			summary.setDataSourceId((Integer)column[18]);
			summary.setDataSourceName((String)column[19]);
			
			return summary;
		}
		else {
			throw new IllegalStateException("Cannot parse JDBC query result");
		}

	}
}
