package com.bizofficer.apiweb.testdetail;

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

import com.bizofficer.hibernate.entity.PgprepScheduleClassTest;
import com.bizofficer.hibernate.entity.PgprepTestSummary;
import com.bizofficer.module.questioncd.QuestionMapperBeanCD;
import com.bizofficer.module.questioncd.XmlToJsonCD;
import com.bizofficer.pojo.response.ListResponsePojo;
import com.bizofficer.util.module.BeanQuestion;
import com.bizofficer.util.module.MysqlTableNames;
import com.bizofficer.util.module.QuestionHtml;



@Service
public class ClassTestAPIService{
	
	@Autowired
	EntityManagerFactory entityManagerFactory;
	
	@Autowired
    DataSource appDataSource;
    Connection conn;
    PreparedStatement ps, psX;
    ResultSet rs, rsX;
    
	public Object doExecute(Object obj) throws NotFoundException{
		BeanTesting objBean = (BeanTesting) obj; 	
		ListResponsePojo responseObj = new ListResponsePojo();
		
		
		try {
			
			responseObj.setResponseTxt("failed");
			
			System.out.println("ClassTestAPIService ClassTestId: "+objBean.getClassTestId());
			System.out.println("ClassTestAPIService StudentId: "+objBean.getStudentId());

			if(objBean.getClassTestId()==null) {
				return responseObj;
			}
			
			List<TestingPageBean> dataList = new ArrayList<TestingPageBean>();
			
			List<QuestionNumbersBean> questionNumbersList = new ArrayList<QuestionNumbersBean>();
			List<SectionSetBean> sectionSetList = new ArrayList<SectionSetBean>();
			SectionSetBean sectionSetBeanObj = new SectionSetBean();
			List<QuestionSetBean> questionSetList = new ArrayList<QuestionSetBean>();
			
			TestingPageBean testingPageBeanObj = new TestingPageBean();
			
			Double correctAnswerMarks = 0.0;
			Double incorrectAnswerMarks = 0.0;
			Double notAnswerMarks = 0.0;
			
			if(objBean.getClassTestId()!=null && objBean.getClassTestId().length()>0) {
				
				EntityManager entityManager = entityManagerFactory.createEntityManager();
				entityManager.getTransaction().begin();

				if(objBean.getStudentId()!=null && objBean.getStudentId().length()>0) {
					TypedQuery<PgprepTestSummary> queryTS = (TypedQuery<PgprepTestSummary>) entityManager.createQuery("from "+PgprepTestSummary.class.getName()+" where testStatus=1 AND studentId=:studentId AND scheduleId=:scheduleId ", PgprepTestSummary.class);
					queryTS.setParameter("studentId", objBean.getStudentId());
					queryTS.setParameter("scheduleId", objBean.getClassTestId());					
					queryTS.setFirstResult(0);
					queryTS.setMaxResults(1);
					List<?> resultListTS = queryTS.getResultList();
					Iterator<?> iteratorTS=resultListTS.iterator();	
					if(iteratorTS.hasNext()){
						entityManager.getTransaction().commit();
						entityManager.close();
						testingPageBeanObj.setResponseTxt("You have already attempted this test.");
						dataList.add(testingPageBeanObj);
												
						responseObj.setList(dataList);
						return responseObj;
					}
				}
				
				TypedQuery<PgprepScheduleClassTest> query = (TypedQuery<PgprepScheduleClassTest>) entityManager.createQuery("from "+PgprepScheduleClassTest.class.getName()+" where scheduleId=?1 AND date(testDateTime)=CURDATE() AND time(testDateTime)<=CURTIME() AND ((TIME_TO_SEC(CURTIME()) - TIME_TO_SEC(testDateTime))/60)<=totalTime ", PgprepScheduleClassTest.class);
				query.setParameter(1, objBean.getClassTestId());
				query.setFirstResult(0);
				query.setMaxResults(1);
				List<?> resultListCat = query.getResultList();
				Iterator<?> iteratorCat=resultListCat.iterator();
				if(iteratorCat.hasNext()){
					PgprepScheduleClassTest myObj = (PgprepScheduleClassTest)iteratorCat.next();

					testingPageBeanObj.setTestName(myObj.getTestTitle());	
					testingPageBeanObj.setTestType(4);
					testingPageBeanObj.setTotalTime(myObj.getTotalTime());

					Integer remainingTestTimeSeconds = this.getRemainingTestTimeSeconds(objBean.getClassTestId());
					if(remainingTestTimeSeconds>0) {
						testingPageBeanObj.setRemainingTime(remainingTestTimeSeconds+3);
					}else {
						testingPageBeanObj.setRemainingTime(2);
					}
					
					correctAnswerMarks = myObj.getMarkCorrectAns();
					incorrectAnswerMarks = myObj.getMarkIncorrectAns();
					notAnswerMarks = myObj.getMarkNotAttempt();

					testingPageBeanObj.setScheduleId(myObj.getScheduleId());
					testingPageBeanObj.setSubject(myObj.getSubject());
					
					objBean.setTestId(Integer.valueOf(objBean.getClassTestId().split("-")[1]));
					System.out.println("ClassTestAPIService TopicTestId: "+objBean.getTestId());
					
				/// IN CASE TIME IS FINISHED SO NO TEST AVAILABLE	
				}else {
					entityManager.getTransaction().commit();
					entityManager.close();
					testingPageBeanObj.setResponseTxt("Timeout for this Class Test. Contact to instructor.");
					dataList.add(testingPageBeanObj);
					
					responseObj.setList(dataList);
					return responseObj;
				}
				entityManager.getTransaction().commit();
				entityManager.close();
				
			}
			
			int questionIndex = 0;

            this.conn = this.appDataSource.getConnection();
            if (this.conn != null && objBean.getTestId()!=null && objBean.getTestId()>0) {
			
	            this.psX = this.conn.prepareStatement("SELECT m.pattern_id,m.mocktest_title,m.mocktest_id,tpp.total_time,tpp.marks_correct,tpp.marks_incorrect,tpp.marks_not_attempted FROM "+MysqlTableNames.getTopicPatternMocktestAssociation()+" m, "+MysqlTableNames.getTemplateTopicPatternAssociation()+" t, "+MysqlTableNames.getTopicPatternParameter()+" tpp where m.pattern_id=t.pattern_id AND tpp.pattern_id=m.pattern_id AND m.mocktest_id=? ");
	            this.psX.setInt(1, objBean.getTestId());
	            this.rsX = this.psX.executeQuery();
	            if(this.rsX.next()) {

					testingPageBeanObj.setTestId(rsX.getInt("mocktest_id"));
					testingPageBeanObj.setModelTestId(0);
					testingPageBeanObj.setSectionLock("No_Lock");
					testingPageBeanObj.setLastQuestionIndex(0);
	        
	    	        String sql = "SELECT m.question_id, m.no_of_question, t.qb_id, q.assess_id, q.assess_title, q.assess_text, q.display_id FROM "+MysqlTableNames.getTopicMocktestGeneratedQuestion()+" m, "+MysqlTableNames.getQbItems()+" t, "+MysqlTableNames.getQbAssessmentItem()+" q WHERE m.question_id=t.item_id AND m.question_id=q.assess_id AND m.mocktest_id=?";
	            	this.ps = this.conn.prepareStatement(sql);
	                this.ps.setInt(1, objBean.getTestId());
	                System.out.println("quesions query: " + this.ps);
	                this.rs = this.ps.executeQuery();
	                while (this.rs.next()) {
	                    if (this.rs.getString("assess_title").equals("Common Data") || this.rs.getString("assess_title").equals("Common Data (5)")) {
	                       
							QuestionMapperBeanCD questionObj = (QuestionMapperBeanCD) new XmlToJsonCD().parse(rs.getString("assess_text"));
	                    	
	                    	PreparedStatement ps1 = this.conn.prepareStatement("SELECT * FROM "+MysqlTableNames.getQbGroupedItem()+" WHERE assess_id=? LIMIT " + this.rs.getInt("no_of_question"));
	                        ps1.setInt(1, rs.getInt("question_id"));
	                        ResultSet rs1 = ps1.executeQuery();
	                        while (rs1.next()) {
	                            
								//*******************************************************************************
								//	QUESTION SET START
								//*******************************************************************************
								QuestionSetBean questionSetBeanObj = new QuestionSetBean();
								
								System.out.println("Common Data Assess Id: "+rs1.getInt("item_id")*10000);
								System.out.println("Common Data Assess Title: "+rs1.getString("item_title"));
								System.out.println(" PASSING TO PARSER ");
								System.out.println("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@");						
								
								BeanQuestion bqObj = new BeanQuestion();
								bqObj.setQuestionId(rs1.getInt("item_id")*10000);
								bqObj.setItemId(rs1.getInt("item_id"));
								bqObj.setQuestionDisplayId(rs1.getString("display_id"));
								bqObj.setQuestionType(rs1.getString("item_title"));
								bqObj.setQuestionTypeLabel(rs1.getString("item_title"));
								bqObj.setQuestionBody(rs1.getString("item_text"));
								bqObj.setCommonDataDescription(questionObj.getItem().getDescription());
								////bqObj.setCommonDataDescription("Consider an ionic solid that dissolves in water according");

								questionSetBeanObj = new QuestionHtml().getXmlParser(bqObj,false);
								
								questionSetBeanObj.setTopicId(String.valueOf(rs.getInt("qb_id")));
								questionSetBeanObj.setTimeSpent(0);
								questionSetBeanObj.setCssName("qbg-not-visited");
								questionSetBeanObj.setBackgroundImg("url('/not-visited.png')");
								
//								questionSetBeanObj.setCorrectAnswerMarks(rsX.getDouble("marks_correct"));
//								questionSetBeanObj.setIncorrectAnswerMarks(rsX.getDouble("marks_incorrect"));
//								questionSetBeanObj.setNotAnswerMarks(rsX.getDouble("marks_not_attempted"));
								
								questionSetBeanObj.setCorrectAnswerMarks(correctAnswerMarks);
								questionSetBeanObj.setIncorrectAnswerMarks(incorrectAnswerMarks);
								questionSetBeanObj.setNotAnswerMarks(notAnswerMarks);

								QuestionNumbersBean questionNumbersBeanObj = new QuestionNumbersBean();
								questionNumbersBeanObj.setQuestionIndex(questionIndex);
								questionNumbersBeanObj.setQuestionId(rs1.getInt("item_id")*10000);
								questionNumbersList.add(questionNumbersBeanObj);
								
								questionSetList.add(questionSetBeanObj);
								
								System.out.println("questionIndex: "+questionIndex);
								
								questionIndex++;
								
								//*******************************************************************************
								//	QUESTION SET END
								//*******************************************************************************
	                            
	                        }
	                        rs1.close();
	                        ps1.close();
	                    } else {
	                        
							//*******************************************************************************
							//	QUESTION SET START
							//*******************************************************************************
							QuestionSetBean questionSetBeanObj = new QuestionSetBean();
							
							System.out.println("QbGroupedItem Assess Id: "+rs.getInt("question_id"));
							System.out.println("QbGroupedItem Assess Title: "+rs.getString("assess_title"));
							System.out.println(" PASSING TO PARSER ");
							System.out.println("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@");						
							
							BeanQuestion bqObj = new BeanQuestion();
							bqObj.setQuestionId(rs.getInt("question_id"));
							bqObj.setItemId(null); 
							bqObj.setQuestionDisplayId(String.valueOf(rs.getInt("display_id")));
							bqObj.setQuestionType(rs.getString("assess_title"));
							bqObj.setQuestionTypeLabel(rs.getString("assess_title"));
							bqObj.setQuestionBody(rs.getString("assess_text"));
							bqObj.setCommonDataDescription(null);

							questionSetBeanObj = new QuestionHtml().getXmlParser(bqObj,false);
							
							questionSetBeanObj.setTopicId(String.valueOf(rs.getInt("qb_id")));
							questionSetBeanObj.setTimeSpent(0);
							questionSetBeanObj.setCssName("qbg-not-visited");
							questionSetBeanObj.setBackgroundImg("url('/not-visited.png')");

							questionSetBeanObj.setCorrectAnswerMarks(rsX.getDouble("marks_correct"));
							questionSetBeanObj.setIncorrectAnswerMarks(rsX.getDouble("marks_incorrect"));
							questionSetBeanObj.setNotAnswerMarks(rsX.getDouble("marks_not_attempted"));
							
							QuestionNumbersBean questionNumbersBeanObj = new QuestionNumbersBean();
							questionNumbersBeanObj.setQuestionIndex(questionIndex);
							questionNumbersBeanObj.setQuestionId(rs.getInt("assess_id"));
							questionNumbersList.add(questionNumbersBeanObj);
							
							questionSetList.add(questionSetBeanObj);
							
							System.out.println("questionIndex: "+questionIndex);
							
							questionIndex++;							
							//*******************************************************************************
							//	QUESTION SET END
							//*******************************************************************************
	                        
	                    }
	                }
	                rs.close();
	                ps.close();
	            }    
            }

            rsX.close();
            psX.close();
            conn.close();
            
            sectionSetBeanObj.setQuestionNumbers(questionNumbersList);
			sectionSetList.add(sectionSetBeanObj);
                
            testingPageBeanObj.setSectionSet(sectionSetList);	
			testingPageBeanObj.setQuestionSet(questionSetList);
			dataList.add(testingPageBeanObj);
			
			
			System.out.println("**************************************************************************");
			
			if(questionSetList!=null) {
				System.out.println("questionSetList Count: "+questionSetList.size());
			}
			
			System.out.println("TOPIC TEST API CREATED");
			
			System.out.println("**************************************************************************");
			
			responseObj.setList(dataList);
			
			responseObj.setResponseTxt("success");
            
		}catch(Exception e) {
			e.getStackTrace();
		}
		
		////System.out.println( "dataList >>> " +  dataList );
			
		return responseObj;
	}
	

	public Integer getRemainingTestTimeSeconds(String classTestId){
		Integer remainingTestTime = 0;
		
		try {
			
	        this.conn = this.appDataSource.getConnection();
	        if (this.conn != null) {
			
	            this.ps = this.conn.prepareStatement("SELECT (totalTime*60) - ((TIME_TO_SEC(CURTIME())) - TIME_TO_SEC(testDateTime)) FROM "+MysqlTableNames.getPgprepScheduleClassTest()+" where scheduleId=? ");
	            this.ps.setString(1, classTestId);
	            this.rs = this.ps.executeQuery();
	            if(this.rs.next()) {
	            	remainingTestTime = rs.getInt(1);
	            }
	            this.ps.close();
	            this.rs.close();
	        }
	        this.conn.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	        
		return remainingTestTime;
	}
	
	
	
	
}