package com.bizofficer.subscription.courses;


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.PgprepOrganisationMenu;
import com.bizofficer.hibernate.entity.PgprepStudentCourses;
import com.bizofficer.hibernate.entity.SubscriptionCourses;
import com.bizofficer.hibernate.repository.PgprepStudentCoursesRepository;
import com.bizofficer.module.util.MysqlTableNames;
import com.bizofficer.system.util.MakeDate;


@Service
public class SelectMyCourseService{
	
	private static final Logger logger = Logger.getLogger(SelectMyCourseService.class);
	
	@Autowired
    DataSource appDataSource;
    Connection conn;
	
    @Autowired
	EntityManagerFactory entityManagerFactory;
    
    @Autowired
    PgprepStudentCoursesRepository pscRepo;
    

	public Object execute(Object obj) throws NotFoundException{
		ACoursesBean objBean = (ACoursesBean) obj; 	
		SelectMyCourseResponseBean respObj = new SelectMyCourseResponseBean();
		
		try {
			
			respObj.setResponseTxt("failed");
			
			logger.info("StudentId: "+objBean.getStudentId() );
			logger.info("CourseId: "+objBean.getCourseId());
			
			if(objBean.getStudentId()==null && objBean.getCourseId()==null) {
				return respObj;
			}
			
			EntityManager entityManager = entityManagerFactory.createEntityManager();
			entityManager.getTransaction().begin();
			
			MakeDate dateObj = new MakeDate();
			

			TypedQuery<PgprepStudentCourses> query = (TypedQuery<PgprepStudentCourses>) entityManager.createQuery("from "+PgprepStudentCourses.class.getName()+" WHERE studentId=:studentId AND courseId=:courseId ", PgprepStudentCourses.class);
			query.setParameter("studentId", objBean.getStudentId() );
			query.setParameter("courseId", objBean.getCourseId() );			
			query.setFirstResult(0);
			query.setMaxResults(1);
			List<?> resultList = query.getResultList();
			Iterator<?> iterator=resultList.iterator();
			if(!iterator.hasNext()){
				
				/// IF COURSE IS NOT SELECTED *******************************************
				TypedQuery<SubscriptionCourses> queryCors = (TypedQuery<SubscriptionCourses>) entityManager.createQuery("from "+SubscriptionCourses.class.getName()+" WHERE courseId=:courseId ", SubscriptionCourses.class);
				queryCors.setParameter("courseId", objBean.getCourseId() );			
				queryCors.setFirstResult(0);
				queryCors.setMaxResults(1);
				List<?> resultCors = queryCors.getResultList();
				Iterator<?> iteratorCors=resultCors.iterator();
				if(iteratorCors.hasNext()){
					SubscriptionCourses myCors = (SubscriptionCourses)iteratorCors.next();
					
					// CHECK THIS ORGANIZATION MENU HAS BEEN CREATED OR NOT
					TypedQuery<PgprepOrganisationMenu> queryChk = (TypedQuery<PgprepOrganisationMenu>) entityManager.createQuery("from "+PgprepOrganisationMenu.class.getName()+" WHERE organisationId=:organisationId ", PgprepOrganisationMenu.class);
					queryChk.setParameter("organisationId", myCors.getFreeOrganisationId() );			
					queryChk.setFirstResult(0);
					queryChk.setMaxResults(1);
					List<?> resultChk = queryChk.getResultList();
					Iterator<?> iteratorChk=resultChk.iterator();
					if(iteratorChk.hasNext()){
					
						PgprepStudentCourses saveObj = new PgprepStudentCourses();
						saveObj.setSid(objBean.getSid());
						saveObj.setCourseId(objBean.getCourseId());
						saveObj.setCourseTitle(myCors.getCourseTitle());					
						saveObj.setStudentId(objBean.getStudentId());
						saveObj.setSubscriptionType("FREE");
						
						if(myCors.getValidityNumber()!=null && myCors.getValidityNumber()>0) {
							saveObj.setValidTillDate(dateObj.getAfterDaysDate(myCors.getValidityNumber(), "yyyy-MM-dd"));
						}else {
							saveObj.setValidTillDate(dateObj.getAfterDaysDate(2, "yyyy-MM-dd"));
						}
						
						saveObj.setOrganisationId(myCors.getFreeOrganisationId());
						
						pscRepo.save(saveObj);						
						
					}	
					
				}
				
				
			}
				
			
				SelectedOrganisationBean orgBeanObj = new SelectedOrganisationBean();
                List<SelectedOrganisationBean> organisationList = new ArrayList<SelectedOrganisationBean>();
                
                this.conn = this.appDataSource.getConnection();
                if (this.conn != null && objBean.getStudentId()!=null && objBean.getStudentId().length()>0) {

                	///AND s.validTillDate>=CURDATE()
                    PreparedStatement stmtA = conn.prepareStatement("SELECT m.*, ot.template_id, s.validTillDate, s.courseTitle, s.courseId, s.subscriptionType, s.id as validityId FROM "+MysqlTableNames.getPgprepStudentCourses()+" s, "+MysqlTableNames.getOrganisationTemplateAssociation()+" ot, "+MysqlTableNames.getPgprepOrganisationMenu()+" m WHERE s.organisationId = ot.organisation_id AND s.organisationId = m.organisationId AND s.studentId=? group by s.courseId order by s.id ");
                    stmtA.setString(1, objBean.getStudentId());
                    logger.info("LoginService AAA: " + stmtA);
                    ResultSet myRow = stmtA.executeQuery();
                    while (myRow.next()) {
                    	orgBeanObj = new SelectedOrganisationBean();
                    	
                    	orgBeanObj.setValidityId(myRow.getInt("validityId"));
                    	orgBeanObj.setCourseId(myRow.getInt("courseId"));
                    	orgBeanObj.setOrganisationId(myRow.getInt("organisationId"));
                    	orgBeanObj.setOrganisationName(myRow.getString("courseTitle"));
                    	orgBeanObj.setTemplateId(myRow.getInt("template_id"));
                    	orgBeanObj.setValidTillDate(myRow.getString("validTillDate"));
                    	orgBeanObj.setSubscriptionType(myRow.getString("subscriptionType"));
                    	
                    	orgBeanObj.setDashboard(myRow.getInt("dashboard"));
                    	orgBeanObj.setReport(myRow.getInt("report"));
                    	orgBeanObj.setSmartLearning(myRow.getInt("smartLearning"));
                    	orgBeanObj.setSelfTest(myRow.getInt("selfTest"));
                    	orgBeanObj.setTopicTest(myRow.getInt("topicTest")); 
                    	orgBeanObj.setMockTest(myRow.getInt("mockTest")); 
                    	orgBeanObj.setClassTest(myRow.getInt("classTest"));
                    	
                    	organisationList.add(orgBeanObj);
                    }
                	
                }
                
                respObj.setOrganisations(organisationList);
                this.conn.close();
                
                respObj.setResponseTxt("success");
               
            entityManager.getTransaction().commit();
			entityManager.close();
			
		}catch(Exception e) {
			e.getStackTrace();			
		}
		
		
		return respObj;
	}
	
	
	
	
}