package com.infinite.focus.server.activity;

import java.util.List;

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

import com.infinite.focus.server.auth.Student;

@Repository
public interface ActivityRepository extends JpaRepository<Activity, Long> {

	// Full query
	@Query(value = "SELECT * FROM activity WHERE activity_type_id = ?1 AND student_id != 0 ORDER BY start_date_time ASC", nativeQuery = true)
	List<Activity> findAllStudentActivityByActivityTypeIdOrderByStartDateTime(Long activity_type_id);
	
	// Full query
	@Query(value = "SELECT * FROM activity WHERE activity_type_id = ?1 AND instructor_id != 0 ORDER BY start_date_time ASC", nativeQuery = true)
	List<Activity> findAllInstructorActivityByActivityTypeIdOrderByStartDateTime(Long activity_type_id);
	
	// Full query
	@Query(value = "SELECT * FROM activity WHERE activity_type_id = ?1 AND entity_id = ?2 ORDER BY start_date_time ASC", nativeQuery = true)
	List<Activity> findAllByActivityTypeIdAndEntityIdOrderByStartDateTime(Long activity_type_id, Long entity_id);
		
	// Full query
	@Query(value = "SELECT entity_id AS id, COUNT(*) AS count FROM activity WHERE activity_type_id = 2 AND student_id != 0 GROUP BY entity_id ORDER BY count DESC", nativeQuery = true)
	List<Object[]> getVideoViewListOrderByCountDesc();
	
	@Query(value = "SELECT entity_id AS id, COUNT(*) AS count FROM activity WHERE activity_type_id = 2 AND student_id != 0 GROUP BY entity_id ORDER BY count ASC", nativeQuery = true)
	List<Object[]> getVideoViewListOrderByCountASC();
	
	@Query(value = "SELECT le.lesson_id, COALESCE((SELECT COUNT(*) AS count FROM activity a WHERE a.activity_type_id = 2 AND a.student_id != 0 AND a.entity_id = le.lesson_id GROUP BY a.entity_id ORDER BY count DESC),0) AS count, concat(round(COALESCE((((SELECT COUNT(*) AS count FROM activity a LEFT JOIN lesson l ON a.entity_id = l.lesson_id WHERE a.entity_id = le.lesson_id AND a.activity_type_id = 2 AND a.student_id != 0 AND a.watch_duration >= l.video_duration GROUP BY a.entity_id ORDER BY count DESC) / COUNT(*)) * 100),0),2),'%') AS total_watch, COALESCE((SELECT SUM(a.watch_duration * 1000) AS count FROM activity a WHERE a.activity_type_id = 2 AND a.student_id != 0 AND a.entity_id = le.lesson_id GROUP BY a.entity_id ORDER BY count DESC),0) AS time, COALESCE((SELECT (SUM(a.watch_duration * 1000)/ COUNT(*)) AS count FROM activity a WHERE a.activity_type_id = 2 AND a.student_id != 0 AND a.entity_id = le.lesson_id GROUP BY a.entity_id ORDER BY count DESC),0) AS avg_time FROM lesson le LEFT JOIN activity act ON le.lesson_id = act.entity_id GROUP BY le.lesson_id ORDER BY count DESC", nativeQuery = true)
	List<Object[]> getVideoViewListOrderByCountAndFullWatchedPercentageDesc();
	
	@Query(value = "SELECT le.lesson_id, COALESCE((SELECT COUNT(*) AS count FROM activity a WHERE a.activity_type_id = 2 AND a.student_id != 0 AND a.entity_id = le.lesson_id GROUP BY a.entity_id ORDER BY count ASC),0) AS count, concat(round(COALESCE((((SELECT COUNT(*) AS count FROM activity a LEFT JOIN lesson l ON a.entity_id = l.lesson_id WHERE a.entity_id = le.lesson_id AND a.activity_type_id = 2 AND a.student_id != 0 AND a.watch_duration >= l.video_duration GROUP BY a.entity_id ORDER BY count ASC) / COUNT(*)) * 100),0),2),'%') AS total_watch, COALESCE((SELECT SUM(a.watch_duration * 1000) AS count FROM activity a WHERE a.activity_type_id = 2 AND a.student_id != 0 AND a.entity_id = le.lesson_id GROUP BY a.entity_id ORDER BY count ASC),0) AS time, COALESCE((SELECT (SUM(a.watch_duration * 1000)/ COUNT(*)) AS count FROM activity a WHERE a.activity_type_id = 2 AND a.student_id != 0 AND a.entity_id = le.lesson_id GROUP BY a.entity_id ORDER BY count ASC),0) AS avg_time FROM lesson le LEFT JOIN activity act ON le.lesson_id = act.entity_id GROUP BY le.lesson_id ORDER BY count ASC", nativeQuery = true)
	List<Object[]> getVideoViewListOrderByCountAndFullWatchedPercentageASC();
	
	@Query(value = "SELECT * FROM activity WHERE activity_id in :ids ORDER BY activity_id", nativeQuery = true)
	List<Activity> findByActivityIds(@Param("ids") List<Long> ids);
	
	@Query(value = "SELECT COUNT(DISTINCT a.student_id) FROM activity a where activity_type_id = 2 AND student_id != 0 AND a.entity_id = ?1", nativeQuery = true)
	Long getCountOfStudentsWhoWatchedVideoByEnitityId(Long entity_id);
	
	@Query(value = "select a.student_id as repeate_count from activity a where activity_type_id = 2 AND student_id != 0 AND a.entity_id = ?1 group by a.student_id having count(a.student_id) > 1", nativeQuery = true)
	List<Long> getRepeteVideoWatchStudentIdListByEntityId(Long entity_id);
	
	@Query(value = "SELECT scr.screen_id, scr.name, (SELECT COUNT(*) FROM activity act WHERE act.activity_type_id = 3 and act.entity_id = scr.screen_id) AS view_count, COALESCE((SELECT SUM(TIMESTAMPDIFF(SECOND, act.start_date_time, act.end_date_time)) AS seconds FROM activity act WHERE act.activity_type_id = 3 and act.entity_id = scr.screen_id), 0) AS total_time_in_seconds, COALESCE(SEC_TO_TIME((SELECT SUM(TIMESTAMPDIFF(SECOND, act.start_date_time, act.end_date_time)) AS seconds FROM activity act WHERE act.activity_type_id = 3 and act.entity_id = scr.screen_id)), 'N/A') AS total_time_in_string, COALESCE(SEC_TO_TIME(ROUND((SELECT SUM(TIMESTAMPDIFF(SECOND, act.start_date_time, act.end_date_time)) AS seconds FROM activity act WHERE act.activity_type_id = 3 and act.entity_id = scr.screen_id) / (SELECT COUNT(*) FROM activity act WHERE act.activity_type_id = 3 and act.entity_id = scr.screen_id))), 'N/A') AS average_time_in_string FROM screen scr ORDER BY view_count DESC", nativeQuery = true)
	List<Object[]> getPagePerformanceAnalyticsOrderByScreenId();
	
	@Query(value = "SELECT * FROM activity act WHERE act.activity_type_id = 2 AND act.student_id != 0", nativeQuery = true)
	List<Activity> getAllVideoActivities();
	
	@Query(value = "SELECT TIMESTAMPDIFF(SECOND, act.start_date_time, act.end_date_time) AS seconds FROM activity act WHERE activity_type_id = 2 AND act.activity_id = ?1", nativeQuery = true)
	Long getDifferenceBetweenStartDateTimeAndEndDateTimeInSecondsByActivityId(Long activity_id);
}