package com.bizofficer.apiweb.topics;

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

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.TypedQuery;
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.hibernate.entity.AssessmentTreeview;
import com.bizofficer.util.module.MysqlTableNames;



@Service
public class TopicTestTopicsService{
	
	private static final Logger logger = Logger.getLogger(TopicTestTopicsService.class);
	
	@Autowired
	EntityManagerFactory entityManagerFactory;
	
	
	@Autowired
    DataSource appDataSource;
	
	public List<?> doExecute(Object obj) throws NotFoundException{
		TopicBean objBean = (TopicBean) obj; 	
		List<CoursesTopicBean> dataList = new ArrayList<CoursesTopicBean>();
		
		try {
		
			if(objBean.getTemplateId()!=null) {
				dataList = this.getTopicIds(objBean.getTemplateId());
			}
			
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		////logger.info( "topic search List >>>> " + dataList);
		
		return dataList;
	}
	
	
	
	@SuppressWarnings("unlikely-arg-type")
	public List<CoursesTopicBean> getTopicIds(Integer templateId)
    {
		List<CoursesTopicBean> topicList = new ArrayList<CoursesTopicBean>();
        
        try{
        	
        	Connection conn = appDataSource.getConnection();
            if(conn != null)
            {
            	PreparedStatement ps = conn.prepareStatement("SELECT a.* FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateTopicPatternAssociation()+" b ON a.childnode_id=b.topic_id WHERE b.template_id=? AND a.parentnode_name=? group by a.childnode_id ");
                ps.setInt(1, templateId);
                ps.setString(2, "");
                logger.info("topic main query: "+ps);
                ResultSet rs = ps.executeQuery();
                while(rs.next())
                {
                	if(rs.getInt("childnode_id")>0) {
    					if(topicList.indexOf(rs.getInt("childnode_id"))==-1) {
    						
    						if(topicTestExists(conn, templateId, rs.getInt("childnode_id"))) {
	    						CoursesTopicBean beanObj = new CoursesTopicBean();
	    						beanObj.setId(rs.getInt("childnode_id"));
	   							beanObj.setQuestionCount(rs.getInt("questionCount"));
	    						beanObj.setTitle(rs.getString("childnode_name"));    						
	    						topicList.add(beanObj);
	    						
	    						PreparedStatement psA = conn.prepareStatement("SELECT a.* FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateTopicPatternAssociation()+" b ON a.childnode_id=b.topic_id WHERE b.template_id=? AND a.parentnode_name=? LIMIT 1 " );
	    						psA.setInt(1, templateId);
	    						psA.setString(2, rs.getString("childnode_name"));
	    		                logger.info("topic query: "+psA);
	    		                ResultSet rsA = psA.executeQuery();
	    		                if(rsA.next()) {
	    							//****************************************************************
	    		                	if(topicTestExists(conn, templateId, rsA.getInt("childnode_id"))) {
	    		                		topicList = this.subTopicsList(conn, templateId, topicList, rs.getString("childnode_name"));
	    		                	}
	    							//****************************************************************
	    						} // close if(myObj
	    		                rsA.close();
	    		                psA.close();
    						}
    						
    					}
    				}
                }
                rs.close();
                ps.close();
                conn.close();
            }
        	
			///logger.info( "topicList >>>> " + topicList);
			
			
  	    }catch(Exception ex){
    	   ex.printStackTrace();
   	    }

        
        return topicList;
        
  }


	private List<CoursesTopicBean> subTopicsList(Connection conn, Integer templateId, List<CoursesTopicBean> topicList, String topicName) {

        try{
		
        	PreparedStatement psC = conn.prepareStatement("SELECT a.* FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateTopicPatternAssociation()+" b ON a.childnode_id=b.topic_id WHERE b.template_id=? AND a.parentnode_name=? group by a.childnode_id ");
        	psC.setInt(1, templateId);
        	psC.setString(2, topicName);
	        logger.info("topic main query: "+psC);
	        ResultSet rsC = psC.executeQuery();
	        while(rsC.next())
	        {
	        	if(topicTestExists(conn, templateId, rsC.getInt("childnode_id"))) {
					CoursesTopicBean beanObj = new CoursesTopicBean();
					beanObj.setId(rsC.getInt("childnode_id"));
					beanObj.setQuestionCount(rsC.getInt("questionCount"));			
					beanObj.setTitle(rsC.getString("childnode_name"));
					topicList.add(beanObj);
		
					PreparedStatement psD = conn.prepareStatement("SELECT a.* FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" a LEFT JOIN "+MysqlTableNames.getTemplateTopicPatternAssociation()+" b ON a.childnode_id=b.topic_id WHERE b.template_id=? AND a.parentnode_name=? LIMIT 1 " );
					psD.setInt(1, templateId);
					psD.setString(2, rsC.getString("childnode_name"));
		            logger.info("topic query: "+psD);
		            ResultSet rsD = psD.executeQuery();
		            if(rsD.next()) {
						//****************************************************************

			        	if(topicTestExists(conn, templateId, rsD.getInt("childnode_id"))) {
			        		
			        		this.subTopicsList(conn, templateId, topicList, rsC.getString("childnode_name"));
			        		
			        	}
			        	
						//****************************************************************
					} // close if(myObj
		            rsD.close();
		            psD.close();
	        	}
				
			} // close while(iterator
	        rsC.close();
	        psC.close();

  	    }catch(Exception ex){
     	   ex.printStackTrace();
   	    }
	        
		return topicList;

	} // makeTree Method close

	
	/*************************************************
	 * 
	 * 		CHECK TOPIC TEST EXISTS 
	 * 
	 *************************************************/
	
	private Boolean topicTestExists(Connection conn, Integer templateId, Integer topicId) {
		Boolean exists=false;
		try {
			
			if(templateId!=null && topicId!=null && topicId>0) {
				List<Integer> templateTopics = this.getTopicIdsForTopicTestExists(conn, 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> getTopicIdsForTopicTestExists(Connection conn, 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.subTopicsListForTopicTestExists(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> subTopicsListForTopicTestExists(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.subTopicsListForTopicTestExists(entityManager, topicList, myObj.getChildnodeName());
				
				//****************************************************************
			} // close if(myObj
			
		} // close while(iterator
		
		return topicList;

	} // makeTree Method close

	
	
	
}