package com.bizofficer.admin.cache;



import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

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.util.module.MysqlTableNames;


@Service
public class TQCAdminService{
	
	private static final Logger logger = Logger.getLogger(TQCAdminService.class);
	
	@Autowired
    DataSource appDataSource;
    Connection conn;
    PreparedStatement ps;
    ResultSet rs;
	
	public Object execute(Object obj) throws NotFoundException{
		TQCAdminResponseBean loginResponseBeanObj = new TQCAdminResponseBean();
		
		try {
			
				String pathNP="";
				String pathID="";
				Integer questionCount = 0;
			
	            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 AND childnode_name NOT IN ('CBSE NCERT TOPICS')  ORDER BY childnode_id ");
		                logger.info("psTID TQCAdminService query: "+psTID);
		                ResultSet rsTID = psTID.executeQuery();
		                while(rsTID.next())
		                {
		                	logger.info("TQCAdminService childnode_name: " + rsTID.getString("childnode_name") );
		                	
		                	if(rsTID.getString("childnode_name").length()>0) {
		                		
		                		questionCount = 0;
		                		PreparedStatement psQC = conn.prepareStatement("SELECT any_type FROM "+MysqlTableNames.getTopicQuestionAssociation()+" where node_id=? and diff_level=0");
		                		psQC.setInt(1, rsTID.getInt("childnode_id"));
		                        logger.info("check availability query: " + psQC);
		                        ResultSet rsQC = psQC.executeQuery();
		                        if (rsQC.next()) {
		                        	if(rsQC.getInt(1)>0) {
		                        		questionCount = rsQC.getInt(1);
		                        	}
		                        }
		                        rsQC.close();
		                        psQC.close();
		     
		                   		pathNP=String.valueOf(rsTID.getInt("node_position")); 
		                		pathID=String.valueOf(rsTID.getInt("childnode_id")); 		     
		                        
	                        	PreparedStatement psUpdate = conn.prepareStatement("UPDATE "+MysqlTableNames.getPgprepAssessmentTreeviewTemp()+" SET questionCount="+questionCount+",path='"+pathNP+"',pathIds=concat('"+pathID+"','.') WHERE childnode_id="+rsTID.getInt("childnode_id") );
	                        	logger.info("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"));
				                logger.info("psC topic query: "+psC);
				                ResultSet rsC = psC.executeQuery();
				                if(rsC.next()) {
				                	
									this.makeTree(rsTID.getString("childnode_name"), pathNP, pathID);
								
								} // close if(myObj2
				                rsC.close();
				                psC.close();
		    					
		    				}
		                }
		                rsTID.close();
		                psTID.close();
	
	            	
		                loginResponseBeanObj.setResponseTxt("DONE");
						logger.info("**************************************************************");
						logger.info("TOPIC QUESTIONS COUNT ADMIN SERVICE COMPLETED SUCCESSFULLY");
						logger.info("**************************************************************");
					
	          }		
		        
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		
		return loginResponseBeanObj;
	}
	

	private void makeTree(String topicName, String pathNP, String pathID) {
		
		try {
			
			String myPathNP = "";
			String myPathID = "";
			Integer questionCount = 0;
			
				PreparedStatement psTID = conn.prepareStatement("SELECT childnode_id,childnode_name,node_position FROM "+MysqlTableNames.getPgprepAssessmentTreeviewTemp()+" WHERE parentnode_name=? ORDER BY childnode_id " );
				psTID.setString(1, topicName);
		        logger.info("psX Topic Query: "+psTID);
		        ResultSet rsTID = psTID.executeQuery();
		        while(rsTID.next()) {
		        	
		        	logger.info("TQCAdminService childnode_id: " + rsTID.getInt("childnode_id") );
		        	logger.info("TQCAdminService childnode_name: " + rsTID.getString("childnode_name") );
		        	
		        	if(rsTID.getString("childnode_name").length()>0) {
		        		
                		questionCount = 0;
                		PreparedStatement psQC = conn.prepareStatement("SELECT any_type FROM "+MysqlTableNames.getTopicQuestionAssociation()+" where node_id=? and diff_level=0");
                		psQC.setInt(1, rsTID.getInt("childnode_id"));
                        logger.info("check availability query: " + psQC);
                        ResultSet rsQC = psQC.executeQuery();
                        if (rsQC.next()) {
                        	if(rsQC.getInt(1)>0) {
                        		questionCount = rsQC.getInt(1);
                        	}
                        }
                        rsQC.close();
                        psQC.close();

		        		myPathNP = pathNP+"."+String.valueOf(rsTID.getInt("node_position")); 
		        		myPathID = pathID+"."+String.valueOf(rsTID.getInt("childnode_id")); 
                        
                    	PreparedStatement psUpdate = conn.prepareStatement("UPDATE "+MysqlTableNames.getPgprepAssessmentTreeviewTemp()+" SET questionCount="+questionCount+",path='"+myPathNP+"',pathIds=concat('"+myPathID+"','.') WHERE childnode_id="+rsTID.getInt("childnode_id") );
                    	logger.info("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"));
		                logger.info("psC topic query: "+psC);
		                ResultSet rsC = psC.executeQuery();
		                if(rsC.next()) {
		                	
							this.makeTree(rsTID.getString("childnode_name"),myPathNP,myPathID);
						
						} // close if(myObj2
		                rsC.close();
		                psC.close();
		                
                }        
					
			} // close while(iterator
            rsTID.close();
            psTID.close();

		           
		
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	} // makeTree Method close
	
	

	
	
}