package com.bizofficer.apiweb.studymaterials;

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.PgprepTopicContent;
import com.bizofficer.util.module.MysqlTableNames;


@Service
public class TopicContentExistsService{
	
	private static final Logger logger = Logger.getLogger(TopicContentExistsService.class);
	
	@Autowired
	EntityManagerFactory entityManagerFactory;
	
	@Autowired
	DataSource appDataSource;
	
	public Boolean checkExists(Object obj) throws NotFoundException{
		BeanTC objBean = (BeanTC) obj; 	
		Boolean returnFlag = false;
		
		try {
			
			if(objBean.getTemplateId()==null || objBean.getTemplateId()<1) {
				return returnFlag;
			}
			
			logger.info( "TopicContentExistsService******************************************************************************************************************************");
			
			EntityManager entityManager = entityManagerFactory.createEntityManager();
			entityManager.getTransaction().begin();
			
//			logger.info( "Content Search By Template Id >> " + objBean.getTemplateId() );
			logger.info( "TopicContentExistsService Topic Id >> " + objBean.getTopicId() );
//			logger.info( "Content Search Query >> " + objBean.getSearchQuery() );
//			logger.info( "Content Search Flag >> " + objBean.getSearchFlag() );
			
			List<Integer> templateTopics = new ArrayList<Integer>();
			
			if(objBean.getTopicId()!=null && objBean.getTopicId()>0) {
				templateTopics = this.getTopicIds(objBean.getTemplateId(), objBean.getTopicId());
				
			}
			
			logger.info( "TopicContentExistsService whereQry XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX >> ");

			TypedQuery<PgprepTopicContent> query = (TypedQuery<PgprepTopicContent>) entityManager.createQuery("from "+PgprepTopicContent.class.getName()+" where topicid IN (:topicIds) ", PgprepTopicContent.class);
			query.setParameter("topicIds", templateTopics);	
			query.setFirstResult(0);
			query.setMaxResults(1);
			List<?> resultList = query.getResultList();
			Iterator<?> iterator=resultList.iterator();			
			if(iterator.hasNext()){
				PgprepTopicContent myContent = (PgprepTopicContent)iterator.next();
				
				returnFlag = true;
				
				logger.info( "TopicContentExistsService Found Content >> "+myContent.getId()+" ==> "+myContent.getContentName());
				
			}
            
			logger.info( "TopicContentExistsService******************************************************************************************************************************");
			
			entityManager.getTransaction().commit();
			entityManager.close();
			
			
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		return returnFlag;
		
	}

	
	public List<Integer> getTopicIds(Integer templateId, Integer topicId)
    {
        List<Integer> topicList = new ArrayList<Integer>();
        
        try{
        	
        	logger.info( "TopicContentExistsService getTopicId START >> " + topicId );
        	
			Connection connGGG = appDataSource.getConnection();
            if(connGGG != null)
            {
            	
            	String topicName="";
            	
            	if(topicId!=null && topicId>0) {
            		topicList.add(topicId);
                	PreparedStatement psHHH = 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.childnode_id=?  LIMIT 0,1 ");
                	psHHH.setInt(1, templateId);    
                	psHHH.setInt(2, topicId);
                    logger.info("TopicContentExistsService getTopicIds: "+psHHH);
                    ResultSet rsHHH = psHHH.executeQuery();
                    if(rsHHH.next())
                    {
                    	topicName = rsHHH.getString("childnode_name");
                    }
            	}
            	
            	PreparedStatement psGGG = connGGG.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.childnode_name)>0 AND a.parentnode_name=? ");
            	psGGG.setInt(1, templateId);    
            	psGGG.setString(2, topicName);
                logger.info("TopicContentExistsService getTopicIds: "+psGGG);
                ResultSet rsGGG = psGGG.executeQuery();
                while(rsGGG.next())
                {
                	if(rsGGG.getInt("childnode_id")>0) {
    					if(topicList.indexOf(rsGGG.getInt("childnode_id"))==-1) {
    						topicList.add(rsGGG.getInt("childnode_id"));

    		            	PreparedStatement psKKK = connGGG.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.childnode_name)>0 AND a.parentnode_name=? LIMIT 0,1 ");
    		            	psKKK.setInt(1, templateId);    
    		            	psKKK.setString(2, rsGGG.getString("childnode_name"));
    		                logger.info("TopicContentExistsService 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( "TopicContentExistsService getTopicIds topicList Size >>>> " + topicList.size());
			
			
  	    }catch(Exception ex){
    	   ex.printStackTrace();
   	    }

        
        return topicList;
        
  }


	private List<Integer> subTopicsList(Connection connGGG, List<Integer> topicList, String topicName, Integer templateId) {
		
		logger.info( "TopicContentExistsService subTopicsList: "+topicName);
		
		try {
			
			if(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.template_id=? AND b.status='Active' AND LENGTH(a.childnode_name)>0 AND a.parentnode_name=? ");
            	psMMM.setInt(1, templateId);    
            	psMMM.setString(2, topicName);
                logger.info("TopicContentExistsService subTopicsList: "+psMMM);
                ResultSet rsMMM = psMMM.executeQuery();
                while(rsMMM.next()) {
				
					topicList.add(rsMMM.getInt("childnode_id"));

	            	PreparedStatement psNNN = connGGG.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.childnode_name)>0 AND a.parentnode_name=? LIMIT 0,1 ");
	            	psNNN.setInt(1, templateId);    
	            	psNNN.setString(2, rsMMM.getString("childnode_name"));
	                logger.info("TopicContentExistsService 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

	
	
}