package com.bizofficer.admin.cache;



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.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.crossstore.ChangeSetPersister.NotFoundException;
import org.springframework.stereotype.Service;

import com.bizofficer.util.module.MysqlTableNames;


@Service
public class TQCAdminServicebk1{
	
	@Autowired
    DataSource appDataSource;
    Connection conn;
    PreparedStatement ps;
    ResultSet rs;
	
	public Object execute(Object obj) throws NotFoundException{
		TQCAdminResponseBean loginResponseBeanObj = new TQCAdminResponseBean();
		
		try {
			
				Integer totalQuestionCount = 0;
				String catPath="";
			
	            conn = appDataSource.getConnection();
	            if (conn != null) {

	            	    PreparedStatement psTID = conn.prepareStatement("SELECT childnode_id, childnode_name, node_position FROM "+MysqlTableNames.getPgprepAssessmentTreeviewTemp()+" WHERE LENGTH(parentnode_name)<1 ");
		                System.out.println("psTID TQCAdminService query: "+psTID);
		                ResultSet rsTID = psTID.executeQuery();
		                while(rsTID.next())
		                {
		                	System.out.println("TQCAdminService childnode_name: " + rsTID.getString("childnode_name") );
		                	
		                	if(rsTID.getString("childnode_name").length()>0) {
		                		
		                		totalQuestionCount = 0;
		                		catPath=String.valueOf(rsTID.getInt("node_position")); //node_position
		                		
		                		List<Integer> topicList = new ArrayList<Integer>();
		                		List<Integer> templateTopics = this.getSubTopicIds(rsTID.getInt("childnode_id"),rsTID.getString("childnode_name"), topicList); 
		    					if(templateTopics.size()>0) {
			        				String sqlAA = "SELECT count(1) FROM "+MysqlTableNames.getQbAssessmentItem()+" q,qb_items t WHERE q.assess_id=t.item_id AND t.qb_id IN ("+templateTopics.toString().substring(1,templateTopics.toString().length()-1)+") AND q.exam_type='objective'  ";
			        				PreparedStatement psAA = conn.prepareStatement(sqlAA);
			        				System.out.println("sqlAA Quesions query: " + psAA);
			        				ResultSet rsAA = psAA.executeQuery();
			                        if(rsAA.next()) {
				                    	
			                        	System.out.println("questionCount: " + rsAA.getInt(1));
			                        	if(rsAA.getInt(1)>0) {
			                        		totalQuestionCount = rsAA.getInt(1);
			                        	}
			                        	
				                    }
			                        rsAA.close();
				                    psAA.close();
		    					}

	                        	PreparedStatement psUpdate = conn.prepareStatement("UPDATE "+MysqlTableNames.getPgprepAssessmentTreeviewTemp()+" SET questionCount="+totalQuestionCount+",path='"+catPath+"' WHERE childnode_id="+rsTID.getInt("childnode_id") );
	                        	System.out.println("psUpdate Quesions query: " + psUpdate);
	                        	psUpdate.executeUpdate();

		    					PreparedStatement psC = conn.prepareStatement("SELECT childnode_id FROM "+MysqlTableNames.getPgprepAssessmentTreeviewTemp()+" WHERE parentnode_name=? LIMIT 1 " );
								psC.setString(1, rsTID.getString("childnode_name"));
				                System.out.println("psC topic query: "+psC);
				                ResultSet rsC = psC.executeQuery();
				                if(rsC.next()) {
				                	
									this.makeTree(rsTID.getString("childnode_name"), catPath);
								
								} // close if(myObj2
				                rsC.close();
				                psC.close();
		    					
		    				}
		                }
		                rsTID.close();
		                psTID.close();
	
	            	
		                loginResponseBeanObj.setResponseTxt("DONE");
						System.out.println("**************************************************************");
						System.out.println("TOPIC QUESTIONS COUNT ADMIN SERVICE COMPLETED SUCCESSFULLY");
						System.out.println("**************************************************************");
					
	          }		
		        
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		
		return loginResponseBeanObj;
	}
	

	private void makeTree(String topicName, String catPath) {
		
		try {
			
			Integer totalQuestionCount = 0;
			String myCatPath = "";
			
				PreparedStatement psTID = conn.prepareStatement("SELECT * FROM "+MysqlTableNames.getPgprepAssessmentTreeviewTemp()+" WHERE parentnode_name=? " );
				psTID.setString(1, topicName);
		        System.out.println("psX Topic Query: "+psTID);
		        ResultSet rsTID = psTID.executeQuery();
		        while(rsTID.next()) {
		        	
		        	System.out.println("TQCAdminService childnode_id: " + rsTID.getInt("childnode_id") );
		        	System.out.println("TQCAdminService childnode_name: " + rsTID.getString("childnode_name") );
		        	
		        	if(rsTID.getString("childnode_name").length()>0) {
		        		
		        		totalQuestionCount = 0;
		        		myCatPath = catPath+"."+String.valueOf(rsTID.getInt("node_position")); //node_position
		        		
		        		List<Integer> topicList = new ArrayList<Integer>();
                		List<Integer> templateTopics = this.getSubTopicIds(rsTID.getInt("childnode_id"),rsTID.getString("childnode_name"), topicList);
    					if(templateTopics.size()>0) {

    						String sqlAA = "SELECT count(1) FROM "+MysqlTableNames.getQbAssessmentItem()+" q,qb_items t WHERE q.assess_id=t.item_id AND t.qb_id IN ("+templateTopics.toString().substring(1,templateTopics.toString().length()-1)+") AND q.exam_type='objective'  ";
	        				PreparedStatement psAA = conn.prepareStatement(sqlAA);
	        				System.out.println("sqlAA Quesions query: " + psAA);
	        				ResultSet rsAA = psAA.executeQuery();
	                        if(rsAA.next()) {
		                    	
	                        	System.out.println("questionCount: " + rsAA.getInt(1));
	                        	if(rsAA.getInt(1)>0) {
	                        		totalQuestionCount = rsAA.getInt(1);
	                        	}
	                        	
		                    }
	                        rsAA.close();
		                    psAA.close();    						
					
    					}	
    					
    					PreparedStatement psUpdate = conn.prepareStatement("UPDATE "+MysqlTableNames.getPgprepAssessmentTreeviewTemp()+" SET questionCount="+totalQuestionCount+",path='"+myCatPath+"' WHERE childnode_id="+rsTID.getInt("childnode_id") );
                    	System.out.println("psUpdate Quesions query: " + psUpdate);
                    	psUpdate.executeUpdate();

    					PreparedStatement psC = conn.prepareStatement("SELECT childnode_id FROM "+MysqlTableNames.getPgprepAssessmentTreeviewTemp()+" WHERE parentnode_name=? LIMIT 1 " );
						psC.setString(1, rsTID.getString("childnode_name"));
		                System.out.println("psC topic query: "+psC);
		                ResultSet rsC = psC.executeQuery();
		                if(rsC.next()) {
		                	
							this.makeTree(rsTID.getString("childnode_name"),myCatPath);
						
						} // close if(myObj2
		                rsC.close();
		                psC.close();
		                
                }        
					
			} // close while(iterator
            rsTID.close();
            psTID.close();

		           
		
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	} // makeTree Method close
	
	
	public List<Integer> getSubTopicIds(Integer nodeId, String node, List<Integer> topicList)
    {
        
        try{
        	
        	if(nodeId!=null && nodeId>0 && node!=null && node.length()>0) {
        		topicList.add(nodeId);
        		//node
            	PreparedStatement psXX = conn.prepareStatement("SELECT childnode_id, childnode_name FROM "+MysqlTableNames.getPgprepAssessmentTreeviewTemp()+" WHERE parentnode_name=? ");
            	psXX.setString(1, node);
            	System.out.println("getTopicIds query: "+psXX);
                ResultSet rsXX = psXX.executeQuery();
                while(rsXX.next())
                {
                	if(rsXX.getInt("childnode_id")>0) {
    					if(topicList.indexOf(rsXX.getInt("childnode_id"))==-1) {
    						topicList.add(rsXX.getInt("childnode_id"));
    						
    						PreparedStatement psC = conn.prepareStatement("SELECT 1 FROM "+MysqlTableNames.getPgprepAssessmentTreeviewTemp()+" WHERE parentnode_name=? LIMIT 1 " );
    						psC.setString(1, rsXX.getString("childnode_name"));
    		                System.out.println("psC topic query: "+psC);
    		                ResultSet rsC = psC.executeQuery();
    		                if(rsC.next()) {
    		                	
    							this.getSubTopicIds(rsXX.getInt("childnode_id"), rsXX.getString("childnode_name"), topicList);
    						
    						} // close if(myObj2
    		                rsC.close();
    		                psC.close();
    						
    					}
    				}
                }
                rsXX.close();
                psXX.close();

                System.out.println( "getTopicIds Method Return >>>> " + topicList);
                
        	}      
			
  	    }catch(Exception ex){
    	   ex.printStackTrace();
   	    }

        
        return topicList;
        
  }

	
	
}