package com.infinite.focus.server.auth;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

//Handles CRUD operations for users table

@Repository
public interface InstructorRepository extends JpaRepository<Instructor, Long> {

	// Full query
	@Query(value = "SELECT * FROM instructor ORDER BY instructor_id", nativeQuery = true)
	List<Instructor> findAllOrderById();
		
	// Get row by account_id
	@Query(value = "SELECT * FROM instructor WHERE account_id = ?1 ", nativeQuery = true)
	Instructor findByAccountId(Long account_id);

	// Get row by instructor_id
	@Query(value = "SELECT * FROM instructor WHERE instructor_id = ?1 ", nativeQuery = true)
	Instructor findByInstructorId(Long instructor_id);

	// Get row by account_id
	@Query(value = "SELECT * FROM instructor WHERE school_id = ?1 ORDER BY first_name", nativeQuery = true)
	List<Instructor> findBySchoolId(Long school_id);

	@Query(value = "SELECT * FROM instructor WHERE registration_code = ?1", nativeQuery = true)
	Instructor findByRegistrationCode(String registration_code);

	@Query(value = "SELECT * FROM instructor WHERE registration_code = ?1 AND grade_id = ?2", nativeQuery = true)
	Instructor findByRegistrationCodeAndGradeId(String registration_code, Integer grade_id);

	@Query(value = "SELECT * FROM instructor WHERE license_key = ?1", nativeQuery = true)
	List<Instructor> findByLicenseKey(String license_key);
	
	@Query(value = "SELECT COUNT(*) FROM instructor WHERE grade_id = ?1", nativeQuery = true)
	Long getCountByGradeId(Long grade_id);
	
	@Query(value = "SELECT COUNT(*) FROM instructor WHERE standard_id = ?1", nativeQuery = true)
	Long getCountByStandardId(Long standard_id);
	
	@Query(value = "SELECT a.username FROM instructor i LEFT JOIN account a ON a.account_id = i.account_id", nativeQuery = true)
	List<String> getAllInstructorsEmailAddresses();
	
	@Query(value ="SELECT COALESCE(inst.operating_system, 'N/A'), count(*), round((count(*) * 100 / (SELECT count(*) FROM instructor)),2) as percentage FROM instructor inst group by inst.operating_system", nativeQuery = true )
	List<Object[]> getInstructorCountAndPercentageByUseOfOS();
	
	@Query(value ="SELECT COALESCE(inst.browser, 'N/A'), count(*), round((count(*) * 100 / (SELECT count(*) FROM instructor)),2) as percentage FROM instructor inst group by inst.browser", nativeQuery = true )
	List<Object[]> getInstructorCountAndPercentageByUseOfBrowser();
}