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.apiweb.studymaterials.BeanTC;
import com.bizofficer.apiweb.studymaterials.TopicContentExistsService;
import com.bizofficer.util.module.MysqlTableNames;
import com.bizofficer.util.system.General;


@Service
public class LearningTreeviewPageService{
	
	private static final Logger logger = Logger.getLogger(LearningTreeviewPageService.class);
	
	@Autowired
    DataSource appDataSource;
    
    General gen = new General();
    
    @Autowired
	private TopicContentExistsService cclsObjTCAAA;
    
	public List<?> doExecute(Object obj) throws NotFoundException{
		TreeviewBean objBean = (TreeviewBean) obj; 	

		List<TreeviewPageBean> dataList = new ArrayList<TreeviewPageBean>();
		
		try {
			
			if(objBean.getTemplateId()==null) {
				return dataList;
			}
			
			////objBean.setTemplateId(16); // For Testing Only
			
			TreeviewPageBean treeviewPageBeanObj;
			
			
			Connection connKKK = appDataSource.getConnection();
            if(connKKK != null)
            {
            		PreparedStatement ps = connKKK.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 " ); // GENERAL TOPICS/ GENERAL KNOWLEDGE - WORLD CONTEXT LENGTH(a.parentnode_name)=0
	                ps.setInt(1, objBean.getTemplateId());
	                logger.info("LearningTreeviewPageService: "+ps);
	                ResultSet rs = ps.executeQuery();
	                while(rs.next()) {
	                	
						treeviewPageBeanObj = new TreeviewPageBean(); 
						treeviewPageBeanObj.setTopicId(rs.getInt("childnode_id"));
						treeviewPageBeanObj.setTopicName(rs.getString("childnode_name"));			
						
						
						PreparedStatement psA = connKKK.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("LearningTreeviewPageService: "+psA);
		                ResultSet rsA = psA.executeQuery();
		                if(rsA.next()) {
	
									List<TopicBean> childList = new ArrayList<TopicBean>();
			
									PreparedStatement psB = connKKK.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("LearningTreeviewPageService: "+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(topicContentExists(objBean.getTemplateId(), rsB.getInt("childnode_id"))) {
												childList.add(topicBeanObj2);
											}
											
					
											PreparedStatement psC = connKKK.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("LearningTreeviewPageService: "+psC);
							                ResultSet rsC = psC.executeQuery();
							                if(rsC.next()) {
							                	
							                		this.makeTree(connKKK, objBean, rsB.getString("childnode_name"), topicBeanObj2);
											
											} // close if(myObj2
							                rsC.close();
							                psC.close();
					                	   
										
									} 	/// close while(iterator2.hasNext())
					                rsB.close();
					                psB.close();
									
					                if(childList.size()>0) {
					                	treeviewPageBeanObj.setChildren(childList);
					                }
									
							
						} /// close if(myObj
		                rsA.close();
		                psA.close();
					
		                Boolean contentFound = true;
		                if(treeviewPageBeanObj.getChildren()==null) {
		                	if(!topicContentExists(objBean.getTemplateId(), rs.getInt("childnode_id"))) {
		                		contentFound = false;
		                	}
		                }
		                
		                if(Boolean.TRUE.equals(contentFound)) {
		                	dataList.add(treeviewPageBeanObj);
		                }
	                	
					
				} /// close while(iterator	
	            rs.close();
	    	    ps.close();
	    	    connKKK.close();
            }       
				
			
			
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		////logger.info( "dataList >>> " +  dataList );
			
		return dataList;
	}
	
	
	private void makeTree(Connection connQQQ, TreeviewBean objBean, String topicName, TopicBean topicBeanChainObj) {
		
		try {
			
				List<TopicBean> childList = new ArrayList<TopicBean>();
		
				PreparedStatement psXX = connQQQ.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=?  " );
				psXX.setInt(1, objBean.getTemplateId());
				psXX.setString(2, topicName);
		        logger.info("LearningTreeviewPageService: "+psXX);
		        ResultSet rsXX = psXX.executeQuery();
		        while(rsXX.next()) {
					
		        		
						TopicBean topicBeanObj = new TopicBean();
						topicBeanObj.setTopicId(rsXX.getInt("childnode_id"));
						topicBeanObj.setTopicName(rsXX.getString("childnode_name"));			
	
						if(topicContentExists(objBean.getTemplateId(), rsXX.getInt("childnode_id"))) {
							childList.add(topicBeanObj);
						}
						
						PreparedStatement psYY = connQQQ.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 " );
						psYY.setInt(1, objBean.getTemplateId());
						psYY.setString(2, rsXX.getString("childnode_name"));
		                logger.info("LearningTreeviewPageService: "+psYY);
		                ResultSet rsYY = psYY.executeQuery();
		                if(rsYY.next()) {
		                	
		                		
		                		this.makeTree(connQQQ, objBean, rsXX.getString("childnode_name"), topicBeanObj);
							
							//****************************************************************
						} // close if(myObj
		                rsYY.close();
		                psYY.close();
	                
		        	
					
				} // close while(iterator
		        
		        if(childList.size()>0) {
		        	topicBeanChainObj.setChildren(childList);
		        }
		        
		        rsXX.close();
		        psXX.close();
                
                
					
				
			
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	} // 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);
        	Boolean found = (Boolean) cclsObjTCAAA.checkExists(objBeanTC);
			
        	if(Boolean.TRUE.equals(found)) {
        		exists=true;
        		logger.info("Childnode: "+ topicId+"______________ Yesssssssssss");
        	}else {
        		logger.info("Childnode: "+ topicId+"______________ NOOOOOOOOO");
        	}
        	
		}catch(Exception e) {
			e.printStackTrace();
		}
		
		return exists;
	}
	
	
}