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 TreeviewFullService{
	
	private static final Logger logger = Logger.getLogger(TreeviewFullService.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 {
			
			TreeviewPageBean treeviewPageBeanObj;
			
        	conn = appDataSource.getConnection();
            if(conn != null)
            {
	                ps = conn.prepareStatement("SELECT * FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE status='Active' AND LENGTH(parentnode_name)=0 " );
	                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 * FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE status='Active' AND parentnode_name=? LIMIT 1 " );
						psA.setString(1, 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 * FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE status='Active' AND parentnode_name=?  " );
							psB.setString(1, 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 * FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE status='Active' AND parentnode_name=? LIMIT 1 " );
								psC.setString(1, 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 * FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE status='Active' AND parentnode_name=?  " );
				psX.setString(1, 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 * FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE status='Active' AND parentnode_name=? LIMIT 1 " );
					psY.setString(1, 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
	
	

	
	
}