package com.infinite.focus.server.auth;

import java.math.BigInteger;
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

//Handles CRUD operations for users table

@Repository
public interface StudentRepository extends JpaRepository<Student, Long> {

	// Get row by account_id
	@Query(value = "SELECT * FROM student WHERE account_id = ?1", nativeQuery = true)
	Student findByAccountId(Long account_id);

	// Get row by student_id
	@Query(value = "SELECT * FROM student WHERE student_id = ?1", nativeQuery = true)
	Student findByStudenttId(Long student_id);
		
	// Get row by instructor_id
	@Query(value = "SELECT * FROM student WHERE instructor_id = ?1 ORDER BY first_name", nativeQuery = true)
	List<Student> findByInstructorId(Long instructor_id);

	// Get row by class_id
	@Query(value = "SELECT * FROM student WHERE class_id = ?1 ORDER BY first_name", nativeQuery = true)
	List<Student> findByClassId(Long class_id);

	// Full query
	@Query(value = "SELECT * FROM student ORDER BY student_id", nativeQuery = true)
	List<Student> findAllOrderById();

	@Query(value = "SELECT * FROM student WHERE student_id in :ids ORDER BY student_id", nativeQuery = true)
	List<Student> findByStudentIds(@Param("ids") List<Long> ids);
	
	@Query(value ="SELECT gender FROM student GROUP BY gender", nativeQuery = true )
	List<String> groupByGender();
	
	@Query(value ="SELECT TIMESTAMPDIFF(YEAR, date_of_birth, NOW()) AS age FROM student GROUP BY age", nativeQuery = true )
	List<Integer> groupByAge();
	
	@Query(value ="SELECT ethnicity FROM student GROUP BY ethnicity", nativeQuery = true )
	List<String> groupByEthnicity();
	
	@Query(value ="SELECT grade_id FROM student GROUP BY grade_id", nativeQuery = true )
	List<BigInteger> groupByGradeId();
	
	@Query(value ="SELECT standard_id FROM student GROUP BY standard_id", nativeQuery = true )
	List<BigInteger> groupByStandardId();
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE grade_id = ?1", nativeQuery = true)
	Long getCountByGradeId(Long grade_id);
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE standard_id = ?1", nativeQuery = true)
	Long getCountByStandardId(Long standard_id);
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE standard_id IS NULL", nativeQuery = true)
	Long getCountByStandardIdIsNull();
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE grade_id = ?1 AND gender = ?2", nativeQuery = true)
	Long getCountByGradeIdAndGender(Long grade_id, String gender);
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE grade_id = ?1 AND ethnicity = ?2", nativeQuery = true)
	Long getCountByGradeIdAndEthnicity(Long grade_id, String ethnicity);
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE grade_id = ?1 AND class_id = ?2", nativeQuery = true)
	Long getCountByGradeIdAndClassId(Long grade_id, Long class_id);
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE gender = ?1", nativeQuery = true)
	Long getCountByGender(String gender);
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE ethnicity = ?1", nativeQuery = true)
	Long getCountByEthnicity(String ethnicity);
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE TIMESTAMPDIFF(YEAR, date_of_birth, NOW()) = ?1", nativeQuery = true)
	Long getCountByAge(Integer age);
	
	@Query(value ="SELECT class_id FROM student GROUP BY class_id", nativeQuery = true )
	List<BigInteger> groupByClassId();
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE class_id = ?1", nativeQuery = true)
	Long getCountByClassId(Long class_id);
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE standard_id = ?1 AND gender = ?2", nativeQuery = true)
	Long getCountByStandardIdAndGender(Long standard_id, String gender);
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE standard_id = ?1 AND TIMESTAMPDIFF(YEAR, date_of_birth, NOW()) = ?2", nativeQuery = true)
	Long getCountByStandardIdAndAge(Long standard_id, Integer age);
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE standard_id = ?1 AND ethnicity = ?2", nativeQuery = true)
	Long getCountByStandardIdAndEthnicity(Long standard_id, String ethnicity);
	
	@Query(value = "SELECT COUNT(*) FROM student WHERE standard_id = ?1 AND class_id = ?2", nativeQuery = true)
	Long getCountByStandardIdAndClassId(Long standard_id, Long class_id);
	
	@Query(value ="SELECT COALESCE(stu.operating_system, 'N/A'), count(*), round((count(*) * 100 / (SELECT count(*) FROM student)),2) as percentage FROM student stu group by stu.operating_system", nativeQuery = true )
	List<Object[]> getStudentCountAndPercentageByUseOfOS();
	
	@Query(value ="SELECT COALESCE(stu.browser, 'N/A'), count(*), round((count(*) * 100 / (SELECT count(*) FROM student)),2) as percentage FROM student stu group by stu.browser", nativeQuery = true )
	List<Object[]> getStudentCountAndPercentageByUseOfBrowser();
	
	@Transactional
	@Modifying
	@Query(value = "insert into student (account_id, avatar_id, class_id, ethnicity, first_name, gender, grade_id, instructor_id, last_name, is_verified, standard_id, fcm_token, should_force_socio_emotional_assessment, operating_system, browser) values (:account_id, :avatar_id, :class_id, :ethnicity, :first_name, :gender, :grade_id, :instructor_id, :last_name, :is_verified, :standard_id, :fcm_token, :should_force_socio_emotional_assessment, :operating_system, :browser)", nativeQuery = true)
	void insertStudent(@Param("account_id") long account_id, @Param("avatar_id") long avatar_id, @Param("class_id") long class_id, @Param("ethnicity") String ethnicity, @Param("first_name") String first_name, @Param("gender") String gender, @Param("grade_id") long grade_id, @Param("instructor_id") long instructor_id, @Param("last_name") String last_name, @Param("is_verified") boolean is_verified, @Param("standard_id") long standard_id, @Param("fcm_token") String fcm_token,  @Param("should_force_socio_emotional_assessment") boolean should_force_socio_emotional_assessment, @Param("operating_system") String operating_system, @Param("browser") String browser);

	@Query(value = "SELECT * FROM student ORDER BY student_id DESC LIMIT 1", nativeQuery = true)
	Student getLastRegisteredStudent();
	
	@Query(value = "SELECT * FROM student stu WHERE stu.first_name = ?1 AND stu.last_name = ?2 ORDER BY student_id ASC", nativeQuery = true)
	List<Student> findStudentByFirstNameAndLastName(String first_name, String last_name);
}