from sqlalchemy import func, text

from app.main.data_model.games.pv_challenge.pv_challenge_challenge import PVChallengeChallenge
from app.main.data_model.games.pv_challenge.pv_challenge_challenge_badge import PVChallengeChallengeBadge
from app.main.data_model.games.pv_challenge.pv_challenge_badge import PVChallengeBadge
from app.main.data_model.games.pv_challenge.pv_challenge_day import PVChallengeDay
from app.main.data_model.games.pv_challenge.pv_challenge_challenge_day import PVChallengeChallengeDay


class PVChallengeChallengeRepository:

    def __init__(self, session):
        self.session = session

    def get_challenge_by_game_session_id_and_user_id(self, game_session_id, user_id):
        pv_challenge_challenge = self.session.query(PVChallengeChallenge).filter(
            PVChallengeChallenge.game_session_id == game_session_id,
            PVChallengeChallenge.user_id == user_id).first()
        return pv_challenge_challenge

    def get_challenge_by_id(self, challenge_id):
        pv_challenge_challenge = self.session.query(PVChallengeChallenge).filter(PVChallengeChallenge.id == challenge_id).first()
        return pv_challenge_challenge

    def get_challenge_badges(self, challenge_id):
        pv_challenge_challenge_badges = self.session.query(PVChallengeChallengeBadge).filter(
            PVChallengeChallengeBadge.challenge_id == challenge_id).all()
        if len(pv_challenge_challenge_badges) == 0:
            return None
        return pv_challenge_challenge_badges

    def get_pv_challenge_badges(self):
        pv_challenge_badges = self.session.query(PVChallengeBadge).all()
        if len(pv_challenge_badges) == 0:
            return None
        return pv_challenge_badges

    def get_pv_challenge_days(self):
        pv_challenge_days = self.session.query(PVChallengeDay).all()
        if len(pv_challenge_days) == 0:
            return None
        return pv_challenge_days

    def get_challenges(self, game_session_id):
        pv_challenge_challenges = self.session.query(PVChallengeChallenge).filter(
            PVChallengeChallenge.game_session_id == game_session_id
        ).all()
        if len(pv_challenge_challenges) == 0:
            return None
        return pv_challenge_challenges

    def get_challenges_deactivated_badges(self, challenge_id):
        pv_challenge_challenge_badges = self.session.query(PVChallengeChallengeBadge).filter(
            PVChallengeChallengeBadge.challenge_id == challenge_id,
            PVChallengeChallengeBadge.status == 0).all()
        if len(pv_challenge_challenge_badges) == 0:
            return None
        return pv_challenge_challenge_badges

    def get_pv_challenge_challenge_days(self, game_session_id, day_id):
        pv_challenge_challenge_days = self.session.query(PVChallengeChallengeDay).filter(
            PVChallengeChallengeDay.day_id == day_id,
            PVChallengeChallengeDay.challenge.game_session_id == game_session_id).all()
        if len(pv_challenge_challenge_days) == 0:
            return None
        return pv_challenge_challenge_days

    def get_challenge_levels_by_game_session_and_participant(self, game_session_id, participant_id):
        return self.session.query(PVChallengeChallengeDay).join(PVChallengeChallenge).filter(
            PVChallengeChallenge.user_id == participant_id,
            PVChallengeChallengeDay.challenge_id == PVChallengeChallenge.id,
            PVChallengeChallenge.game_session_id == game_session_id).order_by(PVChallengeChallengeDay.day_id.asc()).all()

    def get_number_of_finished_missions(self, game_session_id):
        number_of_finished_missions = self.session.query(func.count(PVChallengeChallenge.id)).filter(
            PVChallengeChallenge.game_session_id == game_session_id,
            PVChallengeChallenge.finish_date.isnot(None)
        ).first()

        return number_of_finished_missions[0]

    def get_average_time_duration_by_day(self, game_session_id):
        return self.session.query(PVChallengeChallengeDay.day_id, func.avg(func.timestampdiff(text('MINUTE'),
                            PVChallengeChallengeDay.start_date, PVChallengeChallengeDay.finish_date))).join(PVChallengeChallenge).filter(
            PVChallengeChallengeDay.challenge_id == PVChallengeChallenge.id,
            PVChallengeChallenge.game_session_id == game_session_id,
            PVChallengeChallengeDay.finish_date.isnot(None)
        ).group_by(PVChallengeChallengeDay.day_id).all()

    def get_mean_retention_score(self, game_session_id):
        return self.session.query(PVChallengeChallengeDay.day_id, func.avg(PVChallengeChallengeDay.score1 + PVChallengeChallengeDay.score2)).join(PVChallengeChallenge).filter(
            PVChallengeChallengeDay.challenge_id == PVChallengeChallenge.id,
            PVChallengeChallenge.game_session_id == game_session_id,
            PVChallengeChallenge.finish_date.isnot(None)
        ).group_by(PVChallengeChallengeDay.day_id).all()