package com.bizofficer.apiweb.topics;

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.apiweb.studymaterials.BeanTC;
import com.bizofficer.apiweb.studymaterials.TopicContentBean;
import com.bizofficer.apiweb.studymaterials.TopicContentService;
import com.bizofficer.util.module.MysqlTableNames;



@Service
public class LearningTopicsService{
	
	private static final Logger logger = Logger.getLogger(LearningTopicsService.class);
	
	@Autowired
	EntityManagerFactory entityManagerFactory;
	
	@Autowired
    DataSource appDataSource;

    @Autowired
	private TopicContentService cclsObjTC;
	
	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(topicContentExists(templateId, rs.getInt("childnode_id"))) {
                			
	    					if(topicList.indexOf(rs.getInt("childnode_id"))==-1) {
	    						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(topicContentExists(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(topicContentExists(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(topicContentExists(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

	
	
	private Boolean topicContentExists(Integer templateId, Integer topicId) {
		Boolean exists=false;
		try {
			
        	BeanTC objBeanTC = new BeanTC();
        	objBeanTC.setTemplateId(templateId);
        	objBeanTC.setSearchFlag("Topic");
        	objBeanTC.setTopicId(topicId);
        	@SuppressWarnings("unchecked")
			List<TopicContentBean> list = (List<TopicContentBean>) (Object) cclsObjTC.doExecute(objBeanTC);
			
        	if(list.size()>0) {
        		exists=true;
        	}
        	
		}catch(Exception e) {
			e.printStackTrace();
		}
		
		return exists;
	}

	
	
}