package com.infinite.focus.server.students.service;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.persistence.criteria.CriteriaBuilder.In;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.infinite.focus.server.auth.Account;
import com.infinite.focus.server.auth.Account_;
import com.infinite.focus.server.auth.Instructor;
import com.infinite.focus.server.auth.InstructorRepository;
import com.infinite.focus.server.students.Class;
import com.infinite.focus.server.auth.Student;
import com.infinite.focus.server.auth.StudentRepository;
import com.infinite.focus.server.dashboard.GetDataDashBoardRequest;
import com.infinite.focus.server.grade.GradeRepository;
import com.infinite.focus.server.standard.StandardRepository;
import com.infinite.focus.server.students.ClassRepository;
import com.infinite.focus.server.students.Class_;
import com.infinite.focus.server.students.SearchStudentRequest;
import com.infinite.focus.server.students.Student_;
import com.infinite.focus.server.utils.AppUtils;
import com.infinite.focus.server.utils.DateUtils;
import com.infinite.focus.server.utils.UnitExpression;

@Service
public class StudentServiceImpl implements StudentService{

	@Autowired
	EntityManager entityManager;

	@Autowired
	GradeRepository gradeRepository;

	@Autowired
	StandardRepository standardRepository;

	@Autowired
	StudentRepository studentRepository;

	@Autowired
	InstructorRepository instructorRepository;

	@Autowired
	ClassRepository classRepository;

	String forwardSlash = "/";
	
	@Override
	public List<Long> getStudentByDemographics(GetDataDashBoardRequest request) {
		CriteriaBuilder qb = entityManager.getCriteriaBuilder();
		CriteriaQuery<Long> c = qb.createQuery(Long.class);
		Root<Student> s = c.from(Student.class);

		// create a new predicate list
		List<Predicate> predicates = new ArrayList<>();

		if (AppUtils.isNotNullOrEmpty(request.getGenders())) {
			Predicate genderPredicate = qb.in(s.get(Student_.GENDER)).value(request.getGenders());
			predicates.add(genderPredicate);
		}
		if (AppUtils.isNotNullOrEmpty(request.getAges())) {
			Expression<String> year = new UnitExpression(null, String.class, "YEAR");
		    Expression<Integer> timeInYears = qb.function(
		        "TIMESTAMPDIFF",
		        Integer.class,
		        year ,
		        s.<Timestamp>get(Student_.DATE_OF_BIRTH),
		        qb.literal(Timestamp.valueOf(LocalDateTime.now())));
			In<Integer> inClause = qb.in(timeInYears);
			for (Integer age : request.getAges()) {
			    inClause.value(age);
			}
			//Predicate agePredicate = qb.in(s.get(Student_.AGE)).value(request.getAges());
			predicates.add(inClause);
		}
		if (AppUtils.isNotNullOrEmpty(request.getEthnicity())) {
			Predicate ethnicityPredicate = qb.in(s.get(Student_.ETHNICITY)).value(request.getEthnicity());
			predicates.add(ethnicityPredicate);
		}
		if (AppUtils.isNotNullOrEmpty(request.getGradeIds())) {
			Predicate gradePredicate = qb.in(s.get(Student_.GRADE_ID)).value(request.getGradeIds());
			predicates.add(gradePredicate);
		}
		if (AppUtils.isNotNullOrEmpty(request.getClassIds())) {
			Predicate classesPredicate = qb.in(s.get(Student_.CLASS_ID)).value(request.getClassIds());
			predicates.add(classesPredicate);
		}

		c.select(s.get(Student_.STUDENT_ID)).where(predicates.toArray(new Predicate[0]));

		TypedQuery<Long> typedQuery = entityManager.createQuery(c);
		List<Long> student_ids = typedQuery.getResultList();
//		if (AppUtils.isNotNullOrEmpty(request.getAges())) {
//			List<Long> student_ids_with_age = new ArrayList<>();
//			for(Student student : studentRepository.findByStudentIds(student_ids)) {
//				if(request.getAges().contains(DateUtils.getAge(student.getDate_of_birth()))) {
//					student_ids_with_age.add(student.getStudent_id());
//				}
//			}
//			System.out.println("Student Count = " + student_ids_with_age.size());
//			return student_ids_with_age;
//		}
		System.out.println("Student Count = " + student_ids.size());
		return student_ids;
	}
	
	
	@Override
	public List<Student> searchStudentByInstructorAndDemographic(Instructor instructor, SearchStudentRequest request) {
		CriteriaBuilder qb = entityManager.getCriteriaBuilder();
		CriteriaQuery<Student> c = qb.createQuery(Student.class);
		Root<Student> s = c.from(Student.class);
		Join<Student, Account> ac = s.join("account", JoinType.LEFT);
		//Join<Student, Class> class_ = s.join("class", JoinType.LEFT);
		
		// create a new predicate list
		List<Predicate> predicates = new ArrayList<>();

		if(instructor != null) {
			Predicate genderPredicate = qb.equal(s.get(Student_.INSTRUCTOR_ID), instructor.getInstructor_id());
			predicates.add(genderPredicate);
		}
		
		if (AppUtils.isNotNullOrEmpty(request.getFirst_name())) {
			Predicate genderPredicate = qb.like(s.get(Student_.FIRST_NAME), "%" + request.getFirst_name() + "%");
			predicates.add(genderPredicate);
		}
		
		if (AppUtils.isNotNullOrEmpty(request.getLast_name())) {
			Predicate genderPredicate = qb.like(s.get(Student_.LAST_NAME), "%" + request.getLast_name() + "%");
			predicates.add(genderPredicate);
		}
		
		if (AppUtils.isNotNullOrEmpty(request.getEmail())) {
			Predicate genderPredicate = qb.like(ac.get(Account_.USERNAME), "%" + request.getEmail() + "%");
			predicates.add(genderPredicate);
		}
		
//		if (AppUtils.isNotNullOrEmpty(request.getClass_name())) {
//			Predicate genderPredicate = qb.like(class_.get(Class_.CLASS_NAME), "%" + request.getClass_name() + "%");
//			predicates.add(genderPredicate);
//		}
		
		if (AppUtils.isNotNullOrEmpty(request.getClass_name())) {
			
			List<BigInteger> classIds = classRepository.findClassIdsWhereClassNameLike(request.getClass_name());
			
			if(AppUtils.isNotNullOrEmpty(classIds)) {
				List<Long> ids = new ArrayList<Long>();
				for(int i = 0; i < classIds.size(); i++) {
					ids.add(classIds.get(i).longValue());
				}
				Predicate classesPredicate = qb.in(s.get(Student_.CLASS_ID)).value(ids);
				predicates.add(classesPredicate);
			} else {
				Predicate classesPredicate = qb.in(s.get(Student_.CLASS_ID)).value(-1);
				predicates.add(classesPredicate);
			}
		}

		c.where(predicates.toArray(new Predicate[0]));

		TypedQuery<Student> typedQuery = entityManager.createQuery(c);
		List<Student> students = typedQuery.getResultList();
		System.out.println("Student Count = " + students.size());
		return students;
	}
}