package com.bizofficer.apiweb.topics;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;
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.pojo.response.ListResponsePojo;
import com.bizofficer.util.module.MysqlTableNames;
import com.bizofficer.util.system.General;



@Service
public class SubjectTopicsService{
	
	private static final Logger logger = Logger.getLogger(SubjectTopicsService.class);
	
	@Autowired
	EntityManagerFactory entityManagerFactory;
	
	
	@Autowired
    DataSource appDataSource;
    Connection conn;
    PreparedStatement ps;
    ResultSet rs;
    General gen = new General();    
	
	public Object doExecute(Object obj) throws NotFoundException{
		TopicBean objBean = (TopicBean) obj; 	
		ListResponsePojo responseObj = new ListResponsePojo();
		
		try {
		
			responseObj.setResponseTxt("failed");
			
			if(objBean.getSubject()!=null && objBean.getSubject().length()>0 ) {
				List<String> dataList = new ArrayList<String>();
				dataList = this.getTopicIds(objBean.getSubject());
				if(dataList!=null && dataList.size()>0) {
					Collections.sort(dataList);
					responseObj.setList(dataList);
				}
			}
			
			responseObj.setResponseTxt("success");
			
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		////logger.info( "topic search List >>>> " + dataList);
		
		return responseObj;
	}
	
	
	
	public List<String> getTopicIds(String subject)
    {
		List<String> topicList = new ArrayList<String>();
        
        try{
        	
			EntityManager entityManager = entityManagerFactory.createEntityManager();
			entityManager.getTransaction().begin();
        	
        	conn = appDataSource.getConnection();
            if(conn != null)
            {
                ps = conn.prepareStatement("SELECT * FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE status='Active' AND parentnode_name=?");
                ps.setString(1, subject);
                logger.info("topic main query: "+ps);
                rs = ps.executeQuery();
                while(rs.next())
                {

                			topicList.add(rs.getString("childnode_name"));
    						
    						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<String> subTopicsList(EntityManager entityManager, List<String> 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.getChildnodeName());
			
			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

	
	
	
}