from sqlalchemy import func, text

from app.main.data_model.games.eccbc_onboarding.eccbc_onboarding_mission import ECCBCOnboardingMission
from app.main.data_model.games.eccbc_onboarding.eccbc_onboarding_mission_badge import ECCBCOnboardingMissionBadge
from app.main.data_model.games.eccbc_onboarding.eccbc_onboarding_badge import ECCBCOnboardingBadge
from app.main.data_model.games.eccbc_onboarding.eccbc_onboarding_day import ECCBCOnboardingDay
from app.main.data_model.games.eccbc_onboarding.eccbc_onboarding_mission_day import ECCBCOnboardingMissionDay
from app.main.data_model.games.eccbc_onboarding.eccbc_onboarding_mission_story_steps import \
    ECCBCOnboardingMissionStorySteps


class ECCBCOnboardingMissionRepository:

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

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

    def get_mission_by_user_id(self, user_id):
        return self.session.query(ECCBCOnboardingMission).filter(ECCBCOnboardingMission.user_id == user_id).order_by(ECCBCOnboardingMission.id.desc()).first()

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

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

    def get_eccbc_onboarding_badges(self):
        eccbc_onboarding_badges = self.session.query(ECCBCOnboardingBadge).all()
        if len(eccbc_onboarding_badges) == 0:
            return None
        return eccbc_onboarding_badges

    def get_eccbc_onboarding_days(self):
        eccbc_onboarding_days = self.session.query(ECCBCOnboardingDay).all()
        if len(eccbc_onboarding_days) == 0:
            return None
        return eccbc_onboarding_days

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

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

    def get_eccbc_onboarding_mission_days(self, game_session_id, day_id):
        eccbc_onboarding_mission_days = self.session.query(ECCBCOnboardingMissionDay).filter(
            ECCBCOnboardingMissionDay.day_id == day_id,
            ECCBCOnboardingMissionDay.mission.game_session_id == game_session_id).all()
        if len(eccbc_onboarding_mission_days) == 0:
            return None
        return eccbc_onboarding_mission_days

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

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

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

    def search_for_existing_records(self, eccbc_onboarding_mission_story_steps_list):
        existing_records = []
        for story_step in eccbc_onboarding_mission_story_steps_list:
            record = self.session.query(ECCBCOnboardingMissionStorySteps).filter_by(
                story_steps_id=story_step.story_steps_id,
                mission_id=story_step.mission_id
            ).first()
            if record:
                existing_records.append(record)
        return existing_records