package com.bizofficer.admin.cache;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
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.PgprepCacheTemplateTreeview;
import com.bizofficer.util.module.MysqlTableNames;


@Service
public class CacheTempTreeviewAdmService{
	
	private static final Logger logger = Logger.getLogger(CacheTempTreeviewAdmService.class);
	
	@Autowired
    DataSource appDataSource;
    
	@Autowired
	EntityManagerFactory entityManagerFactory;
	
	public Object execute(Object obj) throws NotFoundException{
		TQCAdminBean objBean = (TQCAdminBean) obj;
		TQCAdminResponseBean loginResponseBeanObj = new TQCAdminResponseBean();
		
		try {

			logger.info("**********************************************************************");
			logger.info("**** TEMPLATE CACHE STARTING ");
			logger.info("**********************************************************************");
			
			String whereSQL=" ORDER BY id ";
			if(objBean.getTemplateId()!=null && objBean.getTemplateId()>0) {
				whereSQL=" AND templateId=:templateId ";
			}else {
				Connection conn = appDataSource.getConnection();
	            if (conn != null) {
                	conn.createStatement().execute("TRUNCATE TABLE "+MysqlTableNames.getPgprepCacheTemplateTreeview());
	                conn.createStatement().execute("INSERT INTO "+MysqlTableNames.getPgprepCacheTemplateTreeview()+" (templateId,childnodeId,status) SELECT template_id,CAST(childnode_id AS UNSIGNED),status FROM "+MysqlTableNames.getTemplateAssessmentTreeview()+" WHERE status='Active' ");

	            }   	
	            conn.close();
			}

			EntityManager entityManager = entityManagerFactory.createEntityManager();
			entityManager.getTransaction().begin();

			TypedQuery<PgprepCacheTemplateTreeview> query = (TypedQuery<PgprepCacheTemplateTreeview>) entityManager.createQuery("from "+PgprepCacheTemplateTreeview.class.getName()+" where 1=1 "+whereSQL, PgprepCacheTemplateTreeview.class);
			if(objBean.getTemplateId()!=null && objBean.getTemplateId()>0) {
				query.setParameter("templateId", objBean.getTemplateId());
			}
			List<?> resultList = query.getResultList();
			Iterator<?> iterator=resultList.iterator();
			while(iterator.hasNext()){
				PgprepCacheTemplateTreeview myObj = (PgprepCacheTemplateTreeview)iterator.next();			

				logger.info("************************************************************************");
				logger.info("Topic Id: "+myObj.getChildnodeId());
				logger.info("************************************************************************");
				
				List<Integer> topicList = this.getTopicIds(myObj.getChildnodeId(), myObj.getTemplateId());
				if(topicList.size()>0) {
					logger.info( "Downline Topics: " +   topicList.toString() );
					PgprepCacheTemplateTreeview cacheOrgObj = (PgprepCacheTemplateTreeview)entityManager.find(PgprepCacheTemplateTreeview.class, myObj.getId());
					cacheOrgObj.setDownline(topicList.toString().substring(1,topicList.toString().length()-1)); 

				}
				
			}	
					
			logger.info("**********************************************************************");
			logger.info("**** TEMPLATE TREEVIEW CACHE FINISHED ");
			logger.info("**********************************************************************");
	        
			entityManager.getTransaction().commit();
			entityManager.close();
		        
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		
		return loginResponseBeanObj;
		
	}
	
	
	public List<Integer> getTopicIds(Integer topicId, Integer templateId)
    {
        List<Integer> topicList = new ArrayList<Integer>();
        
        try{
        	
        	logger.info( "SelfTestService getTopicId OKKKK >> " + topicId );
        	
			Connection connGGG = appDataSource.getConnection();
            if(connGGG != null)
            {
            	
            	String topicName="";
            	if(topicId!=null && topicId>0) {
            		topicList.add(topicId);
                	PreparedStatement psHHH = connGGG.prepareStatement("SELECT childnode_id,childnode_name FROM "+MysqlTableNames.getPgprepAssessmentTreeview()+" WHERE status='Active' AND childnode_id=? LIMIT 0,1 ");
                	psHHH.setInt(1, topicId);
                    logger.info("getTopicIds: "+psHHH);
                    ResultSet rsHHH = psHHH.executeQuery();
                    if(rsHHH.next())
                    {
                    	topicName = rsHHH.getString("childnode_name");
                    }
                    rsHHH.close();
                    rsHHH.close();
            	}
            	
            	
            	if(templateId>0 && topicName!=null && topicName.length()>0) {
					PreparedStatement psGGG = connGGG.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=?  " );
					psGGG.setInt(1, templateId);
					psGGG.setString(2, topicName);
	                logger.info("getTopicIds: "+psGGG);
	                ResultSet rsGGG = psGGG.executeQuery();
	                while(rsGGG.next())
	                {
	                	if(rsGGG.getInt("childnode_id")>0 && rsGGG.getString("childnode_name").length()>0) {
	    					if(topicList.indexOf(rsGGG.getInt("childnode_id"))==-1) {
	    						topicList.add(rsGGG.getInt("childnode_id"));
	
	    						PreparedStatement psKKK = connGGG.prepareStatement("SELECT 1 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 0,1  " );
	    		            	psKKK.setInt(1, templateId);
	    		            	psKKK.setString(2, rsGGG.getString("childnode_name"));
	    		                logger.info("getTopicIds: "+psKKK);
	    		                ResultSet rsKKK = psKKK.executeQuery();
	    		                if(rsKKK.next()) {
	    							//****************************************************************
	    							topicList = this.subTopicsList(connGGG, topicList, rsGGG.getString("childnode_name"), templateId);
	    							//****************************************************************
	    						} // close if(myObj
	    						rsKKK.close();
	    						psKKK.close();
	    					}
	    				}
	                }
	                rsGGG.close();
	                psGGG.close();
            	}
                
            }
        	connGGG.close();
        	
			logger.info( "getTopicIds topicList >>>> " + topicList.size());
			
			
  	    }catch(Exception ex){
    	   ex.printStackTrace();
   	    }

        
        return topicList;
        
  }


	private List<Integer> subTopicsList(Connection connGGG, List<Integer> topicList, String topicName, Integer templateId) {
		
		logger.info( "subTopicsList: "+topicName);
		
		try {
			
			if(templateId>0 && topicName!=null && topicName.length()>0) {
				
				PreparedStatement psMMM = connGGG.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=?  " );
				psMMM.setInt(1, templateId);
            	psMMM.setString(2, topicName);
                logger.info("subTopicsList: "+psMMM);
                ResultSet rsMMM = psMMM.executeQuery();
                while(rsMMM.next()) {
				
					if(rsMMM.getInt("childnode_id")>0 && rsMMM.getString("childnode_name").length()>0) {
						
						topicList.add(rsMMM.getInt("childnode_id"));
						
						PreparedStatement psNNN = connGGG.prepareStatement("SELECT 1 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 0,1  " );
						psNNN.setInt(1, templateId);
		            	psNNN.setString(2, rsMMM.getString("childnode_name"));
		                logger.info("subTopicsList: "+psNNN);
		                ResultSet rsNNN = psNNN.executeQuery();
		                if(rsNNN.next()) {
							//****************************************************************
						    
							this.subTopicsList(connGGG, topicList, rsMMM.getString("childnode_name"), templateId);
							
							//****************************************************************
						} // close if(myObj
		                rsNNN.close();
		                psNNN.close();
					}
					
				} // close while(iterator	
                rsMMM.close();
                psMMM.close();
                
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		
		return topicList;

	} // makeTree Method close

	
	
	
	
	
	
	
	
	
	
	
	
	

	
}