from sqlalchemy import func, text

from app.main.data_model.games.pv_game.pv_center import PVCenter
from app.main.data_model.games.pv_game.pv_center_badge import PVCenterBadge
from app.main.data_model.games.pv_game.pv_badge import PVBadge
from app.main.data_model.games.pv_game.pv_day import PVDay
from app.main.data_model.games.pv_game.pv_center_day import PVCenterDay


class PVCenterRepository:

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

    def get_center_by_game_session_id_and_user_id(self, game_session_id, user_id):
        pv_center = self.session.query(PVCenter).filter(PVCenter.game_session_id == game_session_id).\
            filter(PVCenter.user_id == user_id).all()
        if len(pv_center) == 0:
            return None
        return pv_center[0]

    def get_center_by_id(self, center_id):
        pv_center = self.session.query(PVCenter).filter(PVCenter.id == center_id).all()
        if len(pv_center) == 0:
            return None
        return pv_center[0]

    def get_center_badges(self, center_id):
        pv_center_badges = self.session.query(PVCenterBadge).filter(PVCenterBadge.center_id == center_id).all()
        if len(pv_center_badges) == 0:
            return None
        return pv_center_badges

    def get_pv_badges(self):
        pv_badges = self.session.query(PVBadge).all()
        if len(pv_badges) == 0:
            return None
        return pv_badges

    def get_pv_days(self):
        pv_days = self.session.query(PVDay).all()
        if len(pv_days) == 0:
            return None
        return pv_days

    def get_centers(self, game_session_id):
        pv_centers = self.session.query(PVCenter).filter(PVCenter.game_session_id == game_session_id).all()
        if len(pv_centers) == 0:
            return None
        return pv_centers

    def get_center_deactivated_badges(self, center_id):
        pv_center_badges = self.session.query(PVCenterBadge).filter(PVCenterBadge.center_id == center_id)\
            .filter(PVCenterBadge.status == 0).all()
        if len(pv_center_badges) == 0:
            return None
        return pv_center_badges

    def get_center_imputability_badge(self, center_id):
        pv_center_imputability_badge = self.session.query(PVCenterBadge).\
            filter(PVCenterBadge.center_id == center_id).\
            filter(PVCenterBadge.badge_id == 2).all()
        if len(pv_center_imputability_badge) == 0:
            return None
        return pv_center_imputability_badge[0]

    def get_pv_center_days(self, game_session_id, day_id):
        pv_center_days = self.session.query(PVCenterDay).join(PVCenter).filter(PVCenterDay.day_id == day_id).\
            filter(PVCenterDay.center_id == PVCenter.id).filter(PVCenter.game_session_id == game_session_id).all()
        if len(pv_center_days) == 0:
            return None
        return pv_center_days

    def get_number_of_finished_missions(self, game_session_id):
        number_of_finished_missions = self.session.query(func.count(PVCenter.id)).filter(
            PVCenter.game_session_id == game_session_id,
            PVCenter.finish_date.isnot(None)
        ).fisrt()
        return number_of_finished_missions[0]

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

    def get_mean_retention_score(self, game_session_id):
        return self.session.query(PVCenterDay.day_id, func.avg(PVCenterDay.score1 + PVCenterDay.score2 + PVCenterDay.score3)).join(PVCenter).filter(
            PVCenterDay.center_id == PVCenter.id,
            PVCenter.game_session_id == game_session_id,
            PVCenter.finish_date.isnot(None)
        ).group_by(PVCenterDay.day_id).all()

    def get_center_levels_by_game_session_and_participant(self, game_session_id, participant_id):
        return self.session.query(PVCenterDay).join(PVCenter).filter(
            PVCenter.user_id == participant_id,
            PVCenterDay.center_id == PVCenter.id,
            PVCenter.game_session_id == game_session_id).order_by(
            PVCenterDay.day_id.asc()).all()