package com.bizofficer.apiweb.treeview;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.crossstore.ChangeSetPersister.NotFoundException;
import org.springframework.stereotype.Service;

import com.bizofficer.api.beans.TopicBean;
import com.bizofficer.util.module.MysqlTableNames;
import com.bizofficer.util.system.General;


@Service
public class TopicTestTreeviewPageService{
	
	private static final Logger logger = Logger.getLogger(TopicTestTreeviewPageService.class);
	
	@Autowired
    DataSource appDataSource;
    Connection conn;
    PreparedStatement ps;
    ResultSet rs;
    General gen = new General();
    
	@Autowired
	EntityManagerFactory entityManagerFactory;
    
	public List<?> doExecute(Object obj) throws NotFoundException{
		TreeviewBean objBean = (TreeviewBean) obj; 	

		List<TreeviewPageBean> dataList = new ArrayList<TreeviewPageBean>();
		
		try {
			
			if(objBean.getTemplateId()==null) {
				return dataList;
			}
			
			////objBean.setTemplateId(16);  //// FOR TESTING ONLY
			
			TreeviewPageBean treeviewPageBeanObj;
			
			
        	conn = appDataSource.getConnection();
            if(conn != null)
            {
	                ps = conn.prepareStatement("SELECT a.* FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateAssessmentTreeview()+" b ON a.childnode_id=b.childnode_id WHERE b.template_id=? AND b.status='Active' AND LENGTH(a.parentnode_name)=0 " ); // HUMAN BIOLOGY LENGTH(a.parentnode_name)=0
	                ps.setInt(1, objBean.getTemplateId());
	                logger.info("topic main query: "+ps);
	                rs = ps.executeQuery();
	                while(rs.next()) {
	                	
						treeviewPageBeanObj = new TreeviewPageBean(); 
						treeviewPageBeanObj.setTopicId(rs.getInt("childnode_id"));
						treeviewPageBeanObj.setTopicName(rs.getString("childnode_name"));			
						
						
						PreparedStatement psA = conn.prepareStatement("SELECT a.* FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateAssessmentTreeview()+" b ON a.childnode_id=b.childnode_id WHERE b.status='Active' AND b.template_id=? AND a.parentnode_name=? LIMIT 1 " );
						psA.setInt(1, objBean.getTemplateId());
						psA.setString(2, rs.getString("childnode_name"));
		                logger.info("topic query: "+psA);
		                ResultSet rsA = psA.executeQuery();
		                if(rsA.next()) {
	
									List<TopicBean> childList = new ArrayList<TopicBean>();
			
									PreparedStatement psB = conn.prepareStatement("SELECT a.* FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateAssessmentTreeview()+" b ON a.childnode_id=b.childnode_id WHERE b.status='Active' AND b.template_id=? AND a.parentnode_name=?  " );
									psB.setInt(1, objBean.getTemplateId());
									psB.setString(2, rs.getString("childnode_name"));
					                logger.info("topic query: "+psB);
					                ResultSet rsB = psB.executeQuery();
					                while(rsB.next()) {
					                	
											TopicBean topicBeanObj2 = new TopicBean();
											topicBeanObj2.setTopicId(rsB.getInt("childnode_id"));
											topicBeanObj2.setTopicName(rsB.getString("childnode_name"));
											
											if(topicTestExists(objBean.getTemplateId(), rsB.getInt("childnode_id"))) {
						                		logger.info("Found Test "+rsB.getString("childnode_name")+"______"+rsB.getInt("childnode_id"));
												childList.add(topicBeanObj2);
											}
					
											PreparedStatement psC = conn.prepareStatement("SELECT a.* FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateAssessmentTreeview()+" b ON a.childnode_id=b.childnode_id WHERE b.status='Active' AND b.template_id=? AND a.parentnode_name=? LIMIT 1 " );
											psC.setInt(1, objBean.getTemplateId());
											psC.setString(2, rsB.getString("childnode_name"));
							                logger.info("topic query: "+psC);
							                ResultSet rsC = psC.executeQuery();
							                if(rsC.next()) {
							                	
						                		this.makeTree(objBean, rsB.getString("childnode_name"), topicBeanObj2);
											
											} // close if(myObj2
							                rsC.close();
							                psC.close();
										
									} 	/// close while(iterator2.hasNext())
					                rsB.close();
					                psB.close();
									
									treeviewPageBeanObj.setChildren(childList);
									
							
						} /// close if(myObj
		                rsA.close();
		                psA.close();
					
		                Boolean contentFound = true;
		                if(treeviewPageBeanObj.getChildren()==null || treeviewPageBeanObj.getChildren().size()==0) {
		                	if(!topicTestExists(objBean.getTemplateId(), rs.getInt("childnode_id"))) {		                		
		                		contentFound = false;
		                	}else {
		                		logger.info("Found Test "+rs.getInt("childnode_name")+"______"+rs.getInt("childnode_id"));
		                	}
		                }
		                
		                if(Boolean.TRUE.equals(contentFound)) {
		                	dataList.add(treeviewPageBeanObj);
		                }
		                
					
					
				} /// close while(iterator	
            }       
				
			rs.close();
	        ps.close();
	        conn.close();
			
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		////logger.info( "dataList >>> " +  dataList );
			
		return dataList;
	}
	
	
	private void makeTree(TreeviewBean objBean, String topicName, TopicBean topicBeanChainObj) {
		
		try {
			
				List<TopicBean> childList = new ArrayList<TopicBean>();
		
				PreparedStatement psX = conn.prepareStatement("SELECT a.* FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateAssessmentTreeview()+" b ON a.childnode_id=b.childnode_id WHERE b.status='Active' AND b.template_id=? AND a.parentnode_name=?  " );
				psX.setInt(1, objBean.getTemplateId());
				psX.setString(2, topicName);
		        logger.info("topic query: "+psX);
		        ResultSet rsX = psX.executeQuery();
		        while(rsX.next()) {
					
						TopicBean topicBeanObj = new TopicBean();
						topicBeanObj.setTopicId(rsX.getInt("childnode_id"));
						topicBeanObj.setTopicName(rsX.getString("childnode_name"));			
	
						if(topicTestExists(objBean.getTemplateId(), rsX.getInt("childnode_id"))) {
	                		logger.info("Found Test "+rsX.getString("childnode_name")+"______"+rsX.getInt("childnode_id"));
							childList.add(topicBeanObj);
						}
	
						PreparedStatement psY = conn.prepareStatement("SELECT a.* FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateAssessmentTreeview()+" b ON a.childnode_id=b.childnode_id WHERE b.status='Active' AND b.template_id=? AND a.parentnode_name=? LIMIT 1 " );
						psY.setInt(1, objBean.getTemplateId());
						psY.setString(2, rsX.getString("childnode_name"));
		                logger.info("topic query: "+psY);
		                ResultSet rsY = psY.executeQuery();
		                if(rsY.next()) {
		                	
		                		this.makeTree(objBean, rsX.getString("childnode_name"), topicBeanObj);
							
							//****************************************************************
						} // close if(myObj
		                rsY.close();
		                psY.close();
	                
				} // close while(iterator

		        
		        if(childList.size()>0) {
		        	topicBeanChainObj.setChildren(childList);
		        }
		        
		        
		        rsX.close();
                psX.close();
				
				
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	} // makeTree Method close

	private Boolean topicTestExists(Integer templateId, Integer topicId) {
		Boolean exists=false;
		try {
			
			if(templateId!=null && topicId!=null && topicId>0) {
					 PreparedStatement psAA = conn.prepareStatement("SELECT 1 FROM "+MysqlTableNames.getTopicPatternMocktestAssociation()+" m, "+MysqlTableNames.getTemplateTopicPatternAssociation()+" t where m.pattern_id=t.pattern_id AND t.template_id=? AND t.topic_id=?");
					 psAA.setInt(1, templateId);
					 psAA.setInt(2, topicId);
					 ResultSet rsAA = psAA.executeQuery();
	                 if(rsAA.next()) {
	             		exists=true;
	                 }	 
			}
        	
		}catch(Exception e) {
			e.printStackTrace();
		}
		
		return exists;
	}
	
//	private Boolean topicTestExistsXXX(Integer templateId, Integer topicId) {
//		Boolean exists=false;
//		try {
//			
//			if(templateId!=null && topicId!=null && topicId>0) {
//				List<Integer> templateTopics = this.getTopicIds(templateId, topicId);
//				if(templateTopics.size()>0) {
//					String whereQry=" AND t.topic_id IN ("+templateTopics.toString().substring(1,templateTopics.toString().length()-1)+") ";
//					 PreparedStatement psAA = conn.prepareStatement("SELECT m.pattern_id,m.mocktest_title,m.mocktest_id FROM "+MysqlTableNames.getTopicPatternMocktestAssociation()+" m, "+MysqlTableNames.getTemplateTopicPatternAssociation()+" t where m.pattern_id=t.pattern_id AND t.template_id=? "+whereQry);
//					 psAA.setInt(1, templateId);
//					 ResultSet rsAA = psAA.executeQuery();
//	                 if(rsAA.next()) {
//	             		exists=true;
//	                 }	 
//				}	
//			}
//        	
//		}catch(Exception e) {
//			e.printStackTrace();
//		}
//		
//		return exists;
//	}
	
	
//	public List<Integer> getTopicIds(Integer templateId, Integer topicId)
//    {
//        List<Integer> topicList = new ArrayList<Integer>();
//        
//        try{
//        	
//			EntityManager entityManager = entityManagerFactory.createEntityManager();
//			entityManager.getTransaction().begin();
//        	
//            	String topicName="";
//            	
//            	if(topicId!=null && topicId>0) {
//            		topicList.add(topicId);            		
//    				TypedQuery<AssessmentTreeview> query = (TypedQuery<AssessmentTreeview>) entityManager.createQuery("from "+AssessmentTreeview.class.getName()+" where status='Active' and childnodeId=:childnodeId  ", AssessmentTreeview.class);
//    				query.setParameter("childnodeId", topicId);				
//    				query.setFirstResult(0);
//    				query.setMaxResults(1);				
//    				List<?> resultList = query.getResultList();
//    				Iterator<?> iterator=resultList.iterator();
//    				if(iterator.hasNext()){
//    					AssessmentTreeview myObj = (AssessmentTreeview)iterator.next();	
//    					topicName = myObj.getChildnodeName();
//    				}
//            	}
//            	
//            	PreparedStatement psBB = conn.prepareStatement("SELECT a.* FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateAssessmentTreeview()+" b ON a.childnode_id=b.childnode_id WHERE b.template_id=? AND b.status='Active' AND a.parentnode_name=? ");
//            	psBB.setInt(1, templateId);    
//            	psBB.setString(2, topicName);
//                logger.info("topic main query: "+psBB);
//                ResultSet rsBB = psBB.executeQuery();
//                while(rsBB.next())
//                {
//                	if(rsBB.getInt("childnode_id")>0) {
//    					if(topicList.indexOf(rsBB.getInt("childnode_id"))==-1) {
//    						topicList.add(rsBB.getInt("childnode_id"));
//    						
//    						TypedQuery<AssessmentTreeview> queryCheckSubTopic1 = (TypedQuery<AssessmentTreeview>) entityManager.createQuery("from "+AssessmentTreeview.class.getName()+" where status='Active' and parentnodeName=:childnodeName ", AssessmentTreeview.class);
//    						queryCheckSubTopic1.setParameter("childnodeName", rsBB.getString("childnode_name"));
//    						queryCheckSubTopic1.setFirstResult(0);
//    						queryCheckSubTopic1.setMaxResults(1);
//    						List<?> resultListCheckSubTopic1 = queryCheckSubTopic1.getResultList();
//    						Iterator<?> iteratorCheckSubTopic1=resultListCheckSubTopic1.iterator();
//    						if(iteratorCheckSubTopic1.hasNext()){
//    							//****************************************************************
//    							topicList = this.subTopicsList(entityManager, topicList, rsBB.getString("childnode_name"));
//    							//****************************************************************
//    						} // close if(myObj
//    						
//    					}
//    				}
//                }
//                rsBB.close();
//                psBB.close();
//        	
//			logger.info( "topicList >>>> " + topicList);
//			
//			entityManager.getTransaction().commit();
//			entityManager.close();
//			
//  	    }catch(Exception ex){
//    	   ex.printStackTrace();
//   	    }
//
//        
//        return topicList;
//        
//  }


//	private List<Integer> subTopicsList(EntityManager entityManager, List<Integer> topicList, String topicName) {
//		
//		TypedQuery<AssessmentTreeview> query = (TypedQuery<AssessmentTreeview>) entityManager.createQuery("from "+AssessmentTreeview.class.getName()+" where status='Active' and parentnodeName='"+topicName+"'  ", AssessmentTreeview.class);
//		List<?> resultList = query.getResultList();
//		Iterator<?> iterator=resultList.iterator();
//		while(iterator.hasNext()){
//			AssessmentTreeview myObj = (AssessmentTreeview)iterator.next();				
//			
//			topicList.add(myObj.getChildnodeId());
//			
//			TypedQuery<AssessmentTreeview> queryCheckSubTopic1 = (TypedQuery<AssessmentTreeview>) entityManager.createQuery("from "+AssessmentTreeview.class.getName()+" where status='Active' and parentnodeName='"+myObj.getChildnodeName()+"' ", AssessmentTreeview.class);
//			queryCheckSubTopic1.setFirstResult(0);
//			queryCheckSubTopic1.setMaxResults(1);
//			List<?> resultListCheckSubTopic1 = queryCheckSubTopic1.getResultList();
//			Iterator<?> iteratorCheckSubTopic1=resultListCheckSubTopic1.iterator();
//			if(iteratorCheckSubTopic1.hasNext()){
//				//****************************************************************
//			    
//				this.subTopicsList(entityManager, topicList, myObj.getChildnodeName());
//				
//				//****************************************************************
//			} // close if(myObj
//			
//		} // close while(iterator
//		
//		return topicList;
//
//	} // makeTree Method close

	
	
}