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.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 TreeviewPageService{
	
	private static final Logger logger = Logger.getLogger(TreeviewPageService.class);
	
	@Autowired
    DataSource appDataSource;
    Connection conn;
    PreparedStatement ps;
    ResultSet rs;
    General gen = new General();
	
	public List<?> doExecute(Object obj) throws NotFoundException{
		TreeviewBean objBean = (TreeviewBean) obj; 	

		List<TreeviewPageBean> dataList = new ArrayList<TreeviewPageBean>();
		
		try {
			
			if(objBean.getTemplateId()==null) {
				return dataList;
			}
			
			TreeviewPageBean treeviewPageBeanObj;
			
			String WhereQry=" ";
			if(objBean.getTemplateId()!=null && objBean.getTemplateId()>0 ) {
				List<Integer> templateTopics = this.getTopicIds(objBean.getTemplateId());
				if(templateTopics.size()>0) {
					WhereQry+=" and a.childnode_id IN ("+templateTopics.toString().substring(1,templateTopics.toString().length()-1)+") ";
				}				
			}
			
			logger.info("WhereQry >>> " + WhereQry);
			
        	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 "+WhereQry );
	                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"));			
						
						if(rs.getInt("questionCount")>0) {
							treeviewPageBeanObj.setQuestionCount(rs.getInt("questionCount"));
						}else {
							treeviewPageBeanObj.setQuestionCount(0);
						}
						
						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(rsB.getInt("questionCount")>0) {
									topicBeanObj2.setQuestionCount(rsB.getInt("questionCount"));
								}else {
									topicBeanObj2.setQuestionCount(0);
								}							
								
								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();
					
					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(rsX.getInt("questionCount")>0) {
						topicBeanObj.setQuestionCount(rsX.getInt("questionCount"));
					}else {
						topicBeanObj.setQuestionCount(0);
					}							
					
					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
		        rsX.close();
                psX.close();
				
				topicBeanChainObj.setChildren(childList);				
		
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	} // makeTree Method close
	
	
	public List<Integer> getTopicIds(Integer templateId)
    {
        List<Integer> topicList = new ArrayList<Integer>();
        
        try{
        	
        	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) {
    						topicList.add(rs.getInt("childnode_id"));
    					}
    				}
                }
                rs.close();
                ps.close();
                conn.close();
            }
        	
			logger.info( "topicList >>>> " + topicList);
			
  	    }catch(Exception ex){
    	   ex.printStackTrace();
   	    }

        
        return topicList;
        
    }

	
	
}