from app.main.data_model.games.hacker_mind.hacker_mind_mission import HackerMindMission
from app.main.data_model.games.hacker_mind.hacker_mind_mission_badge import HackerMindMissionBadge
from app.main.data_model.games.hacker_mind.hacker_mind_badge import HackerMindBadge
from app.main.data_model.games.hacker_mind.hacker_mind_day import HackerMindDay
from app.main.data_model.games.hacker_mind.hacker_mind_mission_day import HackerMindMissionDay
from sqlalchemy import func, text


class HackerMindMissionRepository:

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

    def get_mission_by_game_session_id_and_user_id(self, game_session_id, user_id):
        hacker_mind_mission = self.session.query(HackerMindMission).filter(
            HackerMindMission.game_session_id == game_session_id,
            HackerMindMission.user_id == user_id).first()
        return hacker_mind_mission

    def get_mission_by_id(self, mission_id):
        hacker_mind_mission = self.session.query(HackerMindMission).filter(HackerMindMission.id == mission_id).first()
        return hacker_mind_mission

    def get_mission_badges(self, mission_id):
        hacker_mind_mission_badges = self.session.query(HackerMindMissionBadge).filter(HackerMindMissionBadge.mission_id == mission_id).all()
        if len(hacker_mind_mission_badges) == 0:
            return None
        return hacker_mind_mission_badges

    def get_hacker_mind_badges(self):
        hacker_mind_badges = self.session.query(HackerMindBadge).all()
        if len(hacker_mind_badges) == 0:
            return None
        return hacker_mind_badges

    def get_hacker_mind_days(self):
        hacker_mind_days = self.session.query(HackerMindDay).all()
        if len(hacker_mind_days) == 0:
            return None
        return hacker_mind_days

    def get_missions(self, game_session_id):
        hacker_mind_missions = self.session.query(HackerMindMission).filter(HackerMindMission.game_session_id == game_session_id).all()
        if len(hacker_mind_missions) == 0:
            return None
        return hacker_mind_missions

    def get_mission_deactivated_badges(self, mission_id):
        hacker_mind_mission_badges = self.session.query(HackerMindMissionBadge).filter(
            HackerMindMissionBadge.mission_id == mission_id,
            HackerMindMissionBadge.status == 0).all()
        if len(hacker_mind_mission_badges) == 0:
            return None
        return hacker_mind_mission_badges

    def get_hacker_mind_mission_days(self, game_session_id, day_id):
        hacker_mind_mission_days = self.session.query(HackerMindMissionDay).filter(
            HackerMindMissionDay.day_id == day_id,
            HackerMindMissionDay.mission.game_session_id == game_session_id).all()
        if len(hacker_mind_mission_days) == 0:
            return None
        return hacker_mind_mission_days

    def get_number_of_finished_missions(self, game_session_id):
        number_of_finished_missions = self.session.query(func.count(HackerMindMission.id)).filter(
            HackerMindMission.game_session_id == game_session_id,
            HackerMindMission.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(HackerMindMissionDay.day_id, func.avg(func.timestampdiff(text('MINUTE'),
                            HackerMindMissionDay.start_date, HackerMindMissionDay.finish_date))).join(HackerMindMission).filter(
            HackerMindMissionDay.mission_id == HackerMindMission.id,
            HackerMindMission.game_session_id == game_session_id,
            HackerMindMission.finish_date.isnot(None)
        ).group_by(HackerMindMissionDay.day_id).all()

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

    def get_mission_levels_by_game_session_and_participant(self, game_session_id, participant_id):
        return self.session.query(HackerMindMissionDay).join(HackerMindMission).filter(
            HackerMindMission.user_id == participant_id,
            HackerMindMissionDay.mission_id == HackerMindMission.id,
            HackerMindMission.game_session_id == game_session_id).order_by(
            HackerMindMissionDay.day_id.asc()).all()