/****************************************************************
 * Copyright (C) 2005 LAMS Foundation (http://lamsfoundation.org)
 * =============================================================
 * License Information: http://lamsfoundation.org/licensing/lams/2.0/
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License version 2.0
 * as published by the Free Software Foundation.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301
 * USA
 *
 * http://www.gnu.org/licenses/gpl.txt
 * ****************************************************************
 */

package org.lamsfoundation.lams.lesson.dao.hibernate;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.lamsfoundation.lams.dao.hibernate.LAMSBaseDAO;
import org.lamsfoundation.lams.learningdesign.Activity;
import org.lamsfoundation.lams.lesson.LearnerProgress;
import org.lamsfoundation.lams.lesson.LearnerProgressArchive;
import org.lamsfoundation.lams.lesson.dao.ILearnerProgressDAO;
import org.lamsfoundation.lams.usermanagement.User;
import org.springframework.stereotype.Repository;

/**
 * Hibernate implementation of ILearnerProgressDAO
 *
 * @author chris
 */
@Repository
public class LearnerProgressDAO extends LAMSBaseDAO implements ILearnerProgressDAO {

    protected Logger log = Logger.getLogger(LearnerProgressDAO.class);

    private final static String LOAD_PROGRESS_BY_LEARNER = "from LearnerProgress p where p.user.id = :learnerId "
	    + "and p.lesson.id = :lessonId";

    private final static String LOAD_PROGRESS_REFFERING_TO_ACTIVITY = "from LearnerProgress p "
	    + "where p.previousActivity = :activity or p.currentActivity = :activity or p.nextActivity = :activity ";

    private final static String LOAD_COMPLETED_PROGRESS_BY_LESSON = "FROM LearnerProgress p WHERE p.lessonComplete > 0 "
	    + "AND p.lesson.id = :lessonId ORDER BY p.user.firstName <ORDER>, p.user.lastName <ORDER>, p.user.login <ORDER>";

    private final static String LOAD_LEARNERS_LATEST_COMPLETED_BY_LESSON = "SELECT p.user FROM LearnerProgress p WHERE "
	    + "p.lessonComplete > 0 and p.lesson.id = :lessonId ORDER BY p.finishDate DESC";

    private final static String LOAD_LEARNERS_ATTEMPTED_ACTIVITY = "SELECT prog.user FROM LearnerProgress prog, "
	    + " Activity act join prog.attemptedActivities attAct where act.id = :activityId and index(attAct) = act";

    private final static String LOAD_LEARNERS_COMPLETED_ACTIVITY = "SELECT prog.user FROM LearnerProgress prog, "
	    + " Activity act join prog.completedActivities compAct where act.id = :activityId and index(compAct) = act";

    private final static String COUNT_COMPLETED_PROGRESS_BY_LESSON = "select count(*) from LearnerProgress p "
	    + " where p.lessonComplete > 0 and p.lesson.id = :lessonId";

    private final static String COUNT_ATTEMPTED_ACTIVITY = "select count(*) from LearnerProgress prog, "
	    + " Activity act join prog.attemptedActivities attAct where act.id = :activityId and "
	    + " index(attAct) = act";

    private final static String COUNT_COMPLETED_ACTIVITY = "select count(*) from LearnerProgress prog, "
	    + " Activity act join prog.completedActivities compAct where act.id = :activityId and "
	    + " index(compAct) = act";

    private final static String COUNT_CURRENT_ACTIVITY = "select prog.currentActivity.activityId, count(prog) "
	    + "from LearnerProgress prog WHERE prog.currentActivity.activityId IN (:activityIds) "
	    + "GROUP BY prog.currentActivity.activityId";

    private final static String COUNT_SINGLE_CURRENT_ACTIVITY = "select count(*) from LearnerProgress prog "
	    + "WHERE prog.currentActivity.activityId = :activityId ";

    private final static String LOAD_PROGRESS_BY_LESSON = "from LearnerProgress p "
	    + " where p.lesson.id = :lessonId order by p.user.lastName, p.user.firstName, p.user.userId";

    private final static String LOAD_PROGRESS_BY_LESSON_AND_USER_IDS = "from LearnerProgress p "
	    + " where p.lesson.id = :lessonId AND p.user.userId IN (:userIds) order by p.user.lastName, p.user.firstName, p.user.userId";

    private final static String LOAD_PROGRESSES_BY_LESSON_LIST = "FROM LearnerProgress progress WHERE "
	    + " progress.lesson.lessonId IN (:lessonIds)";

    private final static String LOAD_LEARNERS_LATEST_BY_ACTIVITY = "SELECT u.* FROM lams_learner_progress AS prog "
	    + "JOIN lams_progress_attempted AS att USING (learner_progress_id) "
	    + "JOIN lams_user AS u USING (user_id) "
	    + "WHERE prog.current_activity_id = :activityId AND att.activity_id = :activityId "
	    + "ORDER BY att.start_date_time DESC";

    private final static String LOAD_LEARNERS_BY_ACTIVITIES = "SELECT prog.user FROM LearnerProgress prog WHERE "
	    + " prog.currentActivity.id IN (:activityIds) "
	    + "ORDER BY prog.user.firstName <ORDER>, prog.user.lastName <ORDER>, prog.user.login <ORDER>";

    private final static String COUNT_LEARNERS_BY_LESSON = "COUNT(*) FROM LearnerProgress prog WHERE prog.lesson.id = :lessonId";
    private final static String COUNT_LEARNERS_BY_LESSON_ORDER_CLAUSE = " ORDER BY prog.user.firstName ASC, prog.user.lastName ASC, prog.user.login ASC";

    // find Learners for the given Lesson first, then see if they have Progress, i.e. started the lesson
    private final static String LOAD_LEARNERS_BY_MOST_PROGRESS = "SELECT u.*, COUNT(comp.activity_id) AS comp_count FROM lams_lesson AS lesson "
	    + "JOIN lams_grouping AS grping ON lesson.class_grouping_id = grping.grouping_id "
	    + "JOIN lams_group AS g USING (grouping_id) JOIN lams_user_group AS ug USING (group_id) "
	    + "JOIN lams_user AS u ON ug.user_id = u.user_id "
	    + "LEFT JOIN lams_learner_progress AS prog ON prog.lesson_id = lesson.lesson_id AND prog.user_id = u.user_id "
	    + "LEFT JOIN lams_progress_completed AS comp USING (learner_progress_id) "
	    + "WHERE lesson.lesson_id = :lessonId AND g.group_name NOT LIKE '%Staff%'";
    private final static String LOAD_LEARNERS_BY_MOST_PROGRESS_ORDER_CLAUSE = " GROUP BY u.user_id "
	    + "ORDER BY prog.lesson_completed_flag DESC, comp_count DESC, u.first_name ASC, u.last_name ASC, u.login ASC";

    private final static String FIND_PROGRESS_ARCHIVE_MAX_ATTEMPT = "SELECT MAX(p.attemptId) FROM LearnerProgressArchive p "
	    + "WHERE p.user.id = :learnerId AND p.lesson.id = :lessonId";

    private final static String FIND_PROGRESS_ARCHIVE_BY_DATE = "FROM LearnerProgressArchive a "
	    + "WHERE a.lesson.lessonId = :lessonId AND a.user.userId = :learnerId AND a.archiveDate = :archiveDate";

    @Override
    public LearnerProgress getLearnerProgress(Long learnerProgressId) {
	return (LearnerProgress) getSession().get(LearnerProgress.class, learnerProgressId);
    }

    @Override
    public void saveLearnerProgress(LearnerProgress learnerProgress) {
	getSession().save(learnerProgress);
    }

    @Override
    public void deleteLearnerProgress(LearnerProgress learnerProgress) {
	getSession().delete(learnerProgress);
    }

    @Override
    public LearnerProgress getLearnerProgressByLearner(Integer learnerId, Long lessonId) {

	return (LearnerProgress) getSession().createQuery(LearnerProgressDAO.LOAD_PROGRESS_BY_LEARNER)
		.setInteger("learnerId", learnerId).setLong("lessonId", lessonId).uniqueResult();
    }

    @Override
    public void updateLearnerProgress(LearnerProgress learnerProgress) {
	this.getSession().update(learnerProgress);
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<LearnerProgress> getLearnerProgressReferringToActivity(Activity activity) {
	return getSession().createQuery(LearnerProgressDAO.LOAD_PROGRESS_REFFERING_TO_ACTIVITY)
		.setEntity("activity", activity).list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<User> getLearnersLatestByActivity(Long activityId, Integer limit, Integer offset) {
	Query query = getSession().createSQLQuery(LearnerProgressDAO.LOAD_LEARNERS_LATEST_BY_ACTIVITY)
		.addEntity(User.class).setLong("activityId", activityId);
	if (limit != null) {
	    query.setMaxResults(limit);
	}
	if (offset != null) {
	    query.setFirstResult(offset);
	}
	return query.list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<User> getLearnersByActivities(Long[] activityIds, Integer limit, Integer offset,
	    boolean orderAscending) {
	Query query = getSession().createQuery(
		LearnerProgressDAO.LOAD_LEARNERS_BY_ACTIVITIES.replaceAll("<ORDER>", orderAscending ? "ASC" : "DESC"))
		.setParameterList("activityIds", activityIds);
	if (limit != null) {
	    query.setMaxResults(limit);
	}
	if (offset != null) {
	    query.setFirstResult(offset);
	}
	return query.list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<User> getLearnersLatestCompletedForLesson(Long lessonId, Integer limit, Integer offset) {
	Query query = getSession().createQuery(LearnerProgressDAO.LOAD_LEARNERS_LATEST_COMPLETED_BY_LESSON)
		.setLong("lessonId", lessonId);
	if (limit != null) {
	    query.setMaxResults(limit);
	}
	if (offset != null) {
	    query.setFirstResult(offset);
	}
	return query.list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<User> getLearnersByMostProgress(Long lessonId, String searchPhrase, Integer limit, Integer offset) {
	StringBuilder queryText = new StringBuilder(LearnerProgressDAO.LOAD_LEARNERS_BY_MOST_PROGRESS);
	// find the search phrase parts in any of name parts of the user
	if (!StringUtils.isBlank(searchPhrase)) {
	    String[] tokens = searchPhrase.trim().split("\\s+");
	    for (String token : tokens) {
		queryText.append(" AND (u.first_name LIKE '%").append(token).append("%' OR u.last_name LIKE '%")
			.append(token).append("%' OR u.login LIKE '%").append(token).append("%')");
	    }
	}
	queryText.append(LearnerProgressDAO.LOAD_LEARNERS_BY_MOST_PROGRESS_ORDER_CLAUSE);

	Query query = getSession().createSQLQuery(queryText.toString()).addEntity(User.class).setLong("lessonId",
		lessonId);
	if (limit != null) {
	    query.setMaxResults(limit);
	}
	if (offset != null) {
	    query.setFirstResult(offset);
	}
	return query.list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<LearnerProgress> getCompletedLearnerProgressForLesson(Long lessonId, Integer limit, Integer offset,
	    boolean orderAscending) {
	Query query = getSession().createQuery(LearnerProgressDAO.LOAD_COMPLETED_PROGRESS_BY_LESSON
		.replaceAll("<ORDER>", orderAscending ? "ASC" : "DESC")).setLong("lessonId", lessonId);
	if (limit != null) {
	    query.setMaxResults(limit);
	}
	if (offset != null) {
	    query.setFirstResult(offset);
	}
	return query.list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<LearnerProgress> getLearnerProgressForLesson(Long lessonId) {
	return getSession().createQuery(LearnerProgressDAO.LOAD_PROGRESS_BY_LESSON).setLong("lessonId", lessonId)
		.list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<LearnerProgress> getLearnerProgressForLesson(Long lessonId, List<Integer> userIds) {
	return getSession().createQuery(LearnerProgressDAO.LOAD_PROGRESS_BY_LESSON_AND_USER_IDS)
		.setLong("lessonId", lessonId).setParameterList("userIds", userIds).list();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<LearnerProgress> getLearnerProgressForLessons(List<Long> lessonIds) {
	return getSession().createQuery(LearnerProgressDAO.LOAD_PROGRESSES_BY_LESSON_LIST)
		.setParameterList("lessonIds", lessonIds).list();
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<User> getLearnersAttemptedOrCompletedActivity(Activity activity) {
	List<User> users = getSession().createQuery(LearnerProgressDAO.LOAD_LEARNERS_ATTEMPTED_ACTIVITY)
		.setLong("activityId", activity.getActivityId().longValue()).list();
	users.addAll(getSession().createQuery(LearnerProgressDAO.LOAD_LEARNERS_COMPLETED_ACTIVITY)
		.setLong("activityId", activity.getActivityId().longValue()).list());

	return users;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<User> getLearnersAttemptedActivity(Activity activity) {
	List<User> users = getSession().createQuery(LearnerProgressDAO.LOAD_LEARNERS_ATTEMPTED_ACTIVITY)
		.setLong("activityId", activity.getActivityId().longValue()).list();

	return users;
    }

    @Override
    @SuppressWarnings("unchecked")
    public List<User> getLearnersCompletedActivity(final Activity activity) {
	List<User> users = getSession().createQuery(LearnerProgressDAO.LOAD_LEARNERS_COMPLETED_ACTIVITY)
		.setLong("activityId", activity.getActivityId().longValue()).list();

	return users;
    }

    @Override
    public Integer getNumUsersAttemptedActivity(Activity activity) {
	Object value = getSession().createQuery(LearnerProgressDAO.COUNT_ATTEMPTED_ACTIVITY)
		.setLong("activityId", activity.getActivityId().longValue()).uniqueResult();
	Integer attempted = new Integer(((Number) value).intValue());
	return new Integer(attempted.intValue() + getNumUsersCompletedActivity(activity).intValue());
    }

    @Override
    public Integer getNumUsersCompletedActivity(Activity activity) {
	Object value = getSession().createQuery(LearnerProgressDAO.COUNT_COMPLETED_ACTIVITY)
		.setLong("activityId", activity.getActivityId().longValue()).uniqueResult();
	return new Integer(((Number) value).intValue());
    }

    @Override
    public Integer getNumUsersByLesson(Long lessonId, String searchPhrase) {
	StringBuilder queryText = new StringBuilder(LearnerProgressDAO.COUNT_LEARNERS_BY_LESSON);
	// find the search phrase parts in any of name parts of the user
	if (!StringUtils.isBlank(searchPhrase)) {
	    String[] tokens = searchPhrase.trim().split("\\s+");
	    for (String token : tokens) {
		queryText.append(" AND (prog.user.firstName LIKE '%").append(token)
			.append("%' OR prog.user.lastName LIKE '%").append(token)
			.append("%' OR prog.user.login LIKE '%").append(token).append("%')");
	    }
	}
	queryText.append(LearnerProgressDAO.COUNT_LEARNERS_BY_LESSON_ORDER_CLAUSE);

	Object value = getSession().createQuery(queryText.toString()).setLong("lessonId", lessonId.longValue())
		.uniqueResult();
	return ((Number) value).intValue();
    }

    @Override
    public Integer getNumUsersCompletedLesson(Long lessonId) {
	Object value = getSession().createQuery(LearnerProgressDAO.COUNT_COMPLETED_PROGRESS_BY_LESSON)
		.setLong("lessonId", lessonId).uniqueResult();
	return ((Number) value).intValue();
    }

    @SuppressWarnings("unchecked")
    @Override
    public Map<Long, Integer> getNumUsersCurrentActivities(Long[] activityIds) {
	List<Object[]> resultQuery = getSession().createQuery(LearnerProgressDAO.COUNT_CURRENT_ACTIVITY)
		.setParameterList("activityIds", activityIds).list();
	Map<Long, Integer> result = new TreeMap<Long, Integer>();
	// put all requested activity IDs into the result
	for (Long activityId : activityIds) {
	    result.put(activityId, 0);
	}
	// update only the existing ones
	for (Object[] entry : resultQuery) {
	    // for some reason entry can be null
	    if (entry != null) {
		result.put((Long) entry[0], ((Long) entry[1]).intValue());
	    }
	}
	return result;
    }

    @Override
    public Integer getNumUsersCurrentActivity(Activity activity) {
	Object value = getSession().createQuery(LearnerProgressDAO.COUNT_SINGLE_CURRENT_ACTIVITY)
		.setLong("activityId", activity.getActivityId().longValue()).uniqueResult();
	return new Integer(((Number) value).intValue());
    }

    @Override
    public Integer getLearnerProgressArchiveMaxAttemptID(Integer userId, Long lessonId) {
	Object value = getSession().createQuery(LearnerProgressDAO.FIND_PROGRESS_ARCHIVE_MAX_ATTEMPT)
		.setInteger("learnerId", userId).setLong("lessonId", lessonId).uniqueResult();
	return value == null ? null : ((Number) value).intValue();
    }

    @Override
    public LearnerProgressArchive getLearnerProgressArchive(Long lessonId, Integer userId, Date archiveDate) {
	return (LearnerProgressArchive) getSession().createQuery(LearnerProgressDAO.FIND_PROGRESS_ARCHIVE_BY_DATE)
		.setInteger("learnerId", userId).setLong("lessonId", lessonId).setTimestamp("archiveDate", archiveDate)
		.uniqueResult();
    }
}