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 SyncAssessmentTreeviewService{
	
	private static final Logger logger = Logger.getLogger(SyncAssessmentTreeviewService.class);
	
	@Autowired
    DataSource appDataSource;
    Connection conn;
    PreparedStatement ps;
    ResultSet rs;
	
	public Object execute(Object obj) throws NotFoundException{
		TQCAdminResponseBean loginResponseBeanObj = new TQCAdminResponseBean();
		
		try {
			
	            conn = appDataSource.getConnection();
	            if (conn != null) {
	            		
	                	conn.createStatement().execute("TRUNCATE TABLE "+MysqlTableNames.getPgprepAssessmentTreeview());
	            	
		                conn.createStatement().execute("INSERT INTO "+MysqlTableNames.getPgprepAssessmentTreeview()+" (childnode_id,parentnode_name,childnode_name,status,node_position,questionCount,path,pathIds) SELECT  childnode_id,parentnode_name,childnode_name,status,node_position,questionCount,path,pathIds  FROM "+MysqlTableNames.getPgprepAssessmentTreeviewTemp());
		                
	                	conn.createStatement().execute("TRUNCATE TABLE "+MysqlTableNames.getPgprepAssessmentTreeviewTemp());
	                	
	                	conn.createStatement().execute("UPDATE "+MysqlTableNames.getPgprepAssessmentTreeview()+" tv SET path=(select path FROM "+MysqlTableNames.getParentTopicHierarchy()+" th where tv.childnode_id=th.topic_id)");

	                	conn.createStatement().execute("UPDATE "+MysqlTableNames.getPgprepAssessmentTreeview()+" SET path=CONCAT(path,'.') ");
	                	
		                loginResponseBeanObj.setResponseTxt("Synchronous_DONE");
						logger.info("**************************************************************");
						logger.info("TOPIC QUESTION COUNTER COMPLETED SUCCESSFULLY");
						logger.info("**************************************************************");
					
	          }		
	         conn.close();
		        
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		
		return loginResponseBeanObj;
	}
	
	
	
}