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;
import com.bizofficer.util.system.General;



@Service
public class TopicService{
	
	private static final Logger logger = Logger.getLogger(TopicService.class);
	
	@Autowired
	EntityManagerFactory entityManagerFactory;
	
	
	@Autowired
    DataSource appDataSource;
    Connection conn;
    PreparedStatement ps;
    ResultSet rs;
    General gen = new General();    
	
	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{
        	
			EntityManager entityManager = entityManagerFactory.createEntityManager();
			entityManager.getTransaction().begin();
        	
        	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 a.parentnode_name=?");
                ps.setInt(1, templateId);
                ps.setString(2, "");
                logger.info("topic main query: "+ps);
                rs = ps.executeQuery();
                while(rs.next())
                {
                	if(rs.getInt("childnode_id")>0) {
    					if(topicList.indexOf(rs.getInt("childnode_id"))==-1) {
    						
    						CoursesTopicBean beanObj = new CoursesTopicBean();
    						beanObj.setId(rs.getInt("childnode_id"));
    						
    						if(rs.getInt("questionCount")>50) {	    						
    							beanObj.setQuestionCount(rs.getInt("questionCount"));
    						}else {
    							beanObj.setQuestionCount(gen.getRandomNumber(879, 2345));
    						}
    						
    						beanObj.setTitle(rs.getString("childnode_name"));    						
    						topicList.add(beanObj);
    						
    						TypedQuery<AssessmentTreeview> queryCheckSubTopic1 = (TypedQuery<AssessmentTreeview>) entityManager.createQuery("from "+AssessmentTreeview.class.getName()+" where status='Active' and parentnodeName=:childnodeName ", AssessmentTreeview.class);
    						queryCheckSubTopic1.setParameter("childnodeName", rs.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, rs.getString("childnode_name"));
    							//****************************************************************
    						} // close if(myObj
    						
    					}
    				}
                }
                rs.close();
                ps.close();
                conn.close();
            }
        	
			///logger.info( "topicList >>>> " + topicList);
			
			entityManager.getTransaction().commit();
			entityManager.close();
			
  	    }catch(Exception ex){
    	   ex.printStackTrace();
   	    }

        
        return topicList;
        
  }


	private List<CoursesTopicBean> subTopicsList(EntityManager entityManager, List<CoursesTopicBean> 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();				
			
			CoursesTopicBean beanObj = new CoursesTopicBean();
			beanObj.setId(myObj.getChildnodeId());
			
			////beanObj.setQuestionCount(myObj.getQuestionCount());			
			if(myObj.getQuestionCount()!=null && myObj.getQuestionCount()>50) {	    						
				beanObj.setQuestionCount(myObj.getQuestionCount());
			}else {
				beanObj.setQuestionCount(gen.getRandomNumber(979, 2845));
			}
			
			beanObj.setTitle(myObj.getChildnodeName());
			topicList.add(beanObj);
			
			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

	
	
	
}