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.AssessmentTreeview;
import com.bizofficer.hibernate.entity.PgprepKeywordContent;
import com.bizofficer.hibernate.entity.PgprepTopicContent;
import com.bizofficer.pojo.response.ListResponsePojo;
import com.bizofficer.util.module.MysqlTableNames;


@Service
public class TopicContentService{
	
	private static final Logger logger = Logger.getLogger(TopicContentService.class);
	
	@Autowired
	EntityManagerFactory entityManagerFactory;
	
	@Autowired
	DataSource appDataSource;
	
	public Object doExecute(Object obj) throws NotFoundException{
		BeanTC objBean = (BeanTC) obj; 	
		ListResponsePojo responseObj = new ListResponsePojo();
				
		try {

			responseObj.setResponseTxt("failed");
			
			logger.info("Content Search By Template Id >> " + objBean.getTemplateId() );
			logger.info("Content Search By Treeview Topic Id >> " + objBean.getTopicId() );
			logger.info("Content Search Query >> " + objBean.getSearchQuery() );
			logger.info("Content Search Flag >> " + objBean.getSearchFlag() );
			
			if(objBean.getTemplateId()==null || objBean.getTemplateId()<1) {
				return responseObj;
			}
			
			List<TopicContentBean> dataList = new ArrayList<TopicContentBean>();
			
			EntityManager entityManager = entityManagerFactory.createEntityManager();
			entityManager.getTransaction().begin();
			
			// in case content search by keyword
			if(objBean.getSearchFlag()!=null && "Keyword".equals(objBean.getSearchFlag()) && objBean.getSearchQuery()!=null && objBean.getSearchQuery().length()>0 ) {
				dataList = this.searchByKeyword(objBean, entityManager);				
				entityManager.getTransaction().commit();
				entityManager.close();				
				
				responseObj.setList(dataList);				
				responseObj.setResponseTxt("success");
				
				return responseObj;
				
			}	
			
			String whereQry=" where 1=1 ";
			
			if(objBean.getTopicId()!=null && objBean.getTopicId()>0) {
				List<Integer> templateTopics = this.getTopicIds(objBean.getTemplateId(), objBean.getTopicId());
				if(templateTopics.size()>0) {
					whereQry+=" and topicId IN ("+templateTopics.toString().substring(1,templateTopics.toString().length()-1)+") ";
				}	
				
			}else if(objBean.getSearchFlag()!=null && "Topic".equals(objBean.getSearchFlag()) && objBean.getSearchQuery()!=null && objBean.getSearchQuery().length()>0 ) {
				TypedQuery<AssessmentTreeview> query = (TypedQuery<AssessmentTreeview>) entityManager.createQuery("from "+AssessmentTreeview.class.getName()+" where status='Active' and childnodeName=:childnodeName  ", AssessmentTreeview.class);
				query.setParameter("childnodeName", objBean.getSearchQuery());				
				query.setFirstResult(0);
				query.setMaxResults(1);				
				List<?> resultList = query.getResultList();
				Iterator<?> iterator=resultList.iterator();
				if(iterator.hasNext()){
					AssessmentTreeview myObj = (AssessmentTreeview)iterator.next();	
					List<Integer> templateTopics = this.getTopicIds(objBean.getTemplateId(), myObj.getChildnodeId());
					if(templateTopics.size()>0) {
						whereQry+=" and topicId IN ("+templateTopics.toString().substring(1,templateTopics.toString().length()-1)+") ";
					}
				}
				
			}else if(objBean.getTemplateId()!=null && objBean.getTemplateId()>0) {
				List<Integer> templateTopics = this.getTopicIds(objBean.getTemplateId(),null);
				if(templateTopics.size()>0) {
					whereQry+=" and topicId IN ("+templateTopics.toString().substring(1,templateTopics.toString().length()-1)+") ";
				}	
			}
			
			logger.info("TopicContentService whereQryAAA >> " + whereQry );

			TopicContentBean topicContentBeanObj;
			
			TypedQuery<PgprepTopicContent> query = (TypedQuery<PgprepTopicContent>) entityManager.createQuery("from "+PgprepTopicContent.class.getName()+whereQry, PgprepTopicContent.class);
			query.setFirstResult(0);
			query.setMaxResults(200);
			List<?> resultList = query.getResultList();
			Iterator<?> iterator=resultList.iterator();			
			while(iterator.hasNext()){
				PgprepTopicContent myObjCors = (PgprepTopicContent)iterator.next();
				
				topicContentBeanObj = new TopicContentBean();
				topicContentBeanObj.setId(myObjCors.getId());
				topicContentBeanObj.setContentTitle(myObjCors.getContentName());
				topicContentBeanObj.setContentType(myObjCors.getContentType());
				topicContentBeanObj.setContentUrl(myObjCors.getContentUrl());
				topicContentBeanObj.setDescription(myObjCors.getDescription());
				
				dataList.add(topicContentBeanObj);
				
			}
            
			logger.info("FINISHED whereQryAAA >> ");
			
			entityManager.getTransaction().commit();
			entityManager.close();
			
			responseObj.setList(dataList);
			
			responseObj.setResponseTxt("success");
			
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		return responseObj;
		
	}
	
	public List<TopicContentBean> searchByKeyword(BeanTC objBean, EntityManager entityManager) throws NotFoundException{
		List<TopicContentBean> dataList = new ArrayList<TopicContentBean>();
		
		try {
			
			logger.info("TopicContentService searchByKeyword >> " );
			
			TypedQuery<PgprepKeywordContent> query = (TypedQuery<PgprepKeywordContent>) entityManager.createQuery("from "+PgprepKeywordContent.class.getName()+" where keywordName=:keywordName ", PgprepKeywordContent.class);
			query.setParameter("keywordName", objBean.getSearchQuery() );
			query.setFirstResult(0);
			query.setMaxResults(200);
			TopicContentBean topicContentBeanObj;
			List<?> resultList = query.getResultList();
			Iterator<?> iterator=resultList.iterator();			
			while(iterator.hasNext()){
				PgprepKeywordContent myObjCors = (PgprepKeywordContent)iterator.next();
				
				topicContentBeanObj = new TopicContentBean();
				topicContentBeanObj.setId(myObjCors.getId());
				topicContentBeanObj.setContentTitle(myObjCors.getContentName());
				topicContentBeanObj.setContentType(myObjCors.getContentType());
				topicContentBeanObj.setContentUrl(myObjCors.getContentUrl());
				topicContentBeanObj.setDescription(myObjCors.getDescription());
				
				dataList.add(topicContentBeanObj);
				
			}
            
            
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		return dataList;
		
	}
	

	
	public List<Integer> getTopicIds(Integer templateId, Integer topicId)
    {
        List<Integer> topicList = new ArrayList<Integer>();
        
        try{
        	
        	logger.info("TopicContentService 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 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);
                    System.out.println("TopicContentService 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);
                System.out.println("TopicContentService 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"));
    		                System.out.println("TopicContentService 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("TopicContentService 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("TopicContentService 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);
                System.out.println("TopicContentService 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"));
	                System.out.println("TopicContentService 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

	
	
}