class BaseController:
    def __init__(self, request):
        self.request = request
        self.build_cache_id = static_cache.build_cache_id

        layout_render = pyramid.renderers.get_renderer(
            "nflpool:templates/shared/_layout.pt"
        )
        impl = layout_render.implementation()
        self.layout = impl.macros["layout"]

        log_name = "Ctrls/" + type(self).__name__.replace("Controller", "")
        self.log = logbook.Logger(log_name)

    @property
    def is_logged_in(self):
        return cookie_auth.get_user_id_via_auth_cookie(self.request) is not None

    # noinspection PyMethodMayBeStatic
    @suppress()
    def redirect(self, to_url, permanent=False):
        if permanent:
            raise exc.HTTPMovedPermanently(to_url)
        raise exc.HTTPFound(to_url)

    @property
    def data_dict(self):
        data = dict()
        data.update(self.request.GET)
        data.update(self.request.POST)
        data.update(self.request.matchdict)

        return data

    @property
    def logged_in_user_id(self):
        user_id = cookie_auth.get_user_id_via_auth_cookie(self.request)
        return user_id

    @property
    def logged_in_user(self):
        uid = self.logged_in_user_id
        if not uid:
            return None

        return AccountService.find_account_by_id(uid) class NewInstallViewModel(ViewModelBase):
    def __init__(self):
        self.team_id = None
        self.name = None
        self.city = None
        self.team_abbr = None
        self.conference = None
        self.division = None
        self.division_abbr = None
        self.division_id = None

    def from_dict(self, data_dict):
        self.team_id = data_dict.get("team_id")
        self.name = data_dict.get("name")
        self.city = data_dict.get("city")
        self.team_abbr = data_dict.get("team_abbr")
        self.conference = data_dict.get("conference")
        self.division = data_dict.get("division")
        self.division_abbr = data_dict.get("division_abbr")
        self.division_id = data_dict.get("division_id") class NewSeasonViewModel(ViewModelBase):
    def __init__(self):
        self.new_season_input = None
        self.season_start_date_input = None
        self.season_start_time = None
        self.home_team = None
        self.away_team = None

    def from_dict(self, data_dict):
        self.new_season_input = data_dict.get("new_season_input")
        self.season_start_date_input = data_dict.get("season_start_date_input")
        self.season_start_time = data_dict.get("season_start_time")
        self.home_team = data_dict.get("home_team")
        self.away_team = data_dict.get("away_team") class UpdateNFLPlayersViewModel(ViewModelBase):
    def __init__(self):
        self.firstname = None
        self.lastname = None
        self.player_id = None
        self.team_id = None
        self.position = None
        self.season = None

    def from_dict(self, data_dict):
        self.firstname = data_dict.get("firstname")
        self.lastname = data_dict.get("lastname")
        self.player_id = data_dict.get("player_id")
        self.team_id = data_dict.get("team_id")
        self.position = data_dict.get("position")
        self.season = data_dict.get("season") class NewInstallService:
    @staticmethod
    def get_install():
        return []

    """From MySportsFeeds get the team name, team city, team ID and abbreviation.  Loop through
    the AFC teams (0 in the API) and NFC (1) in the API.  The Division IDs are self created.  This method
    will fill the TeamInfo table in the database."""

    @staticmethod
    def get_team_info():

        session = DbSessionFactory.create_session()
        season_query = session.query(SeasonInfo.current_season).first()
        season = season_query[0]

        x = 0

        response = requests.get(
            "https://api.mysportsfeeds.com/v2.0/pull/nfl/"
            + str(season)
            + "-regular/standings.json",
            auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw),
        )

        data = response.json()

        teamlist = data["teams"]

        # Create a loop to extract all team info and insert into the database

        for team_list in teamlist:

            team_name = teamlist[x]["team"]["name"]
            team_city = teamlist[x]["team"]["city"]
            team_id = int(teamlist[x]["team"]["id"])
            team_abbr = teamlist[x]["team"]["abbreviation"]
            conference_name = teamlist[x]["conferenceRank"]["conferenceName"]

            if team_id <= 55:
                division_id = 1
            elif team_id <= 63:
                division_id = 2
            elif team_id <= 71:
                division_id = 3
            else:
                division_id = 4

            if conference_name == "AFC":
                conference_id = 0
            else:
                conference_id = 1

            x += 1

            team_info = TeamInfo(
                city=team_city,
                team_id=team_id,
                team_abbr=team_abbr,
                name=team_name,
                conference_id=conference_id,
                division_id=division_id,
            )

            session.add(team_info)

            session.commit()

    """Create the DivisionInfo table with the division IDs and name them to match NFL division names."""

    @classmethod
    def create_division_info(cls):
        for x in range(1, 5):
            division_id = x
            if x == 1:
                division = "East"
            elif x == 2:
                division = "North"
            elif x == 3:
                division = "South"
            else:
                division = "West"

            session = DbSessionFactory.create_session()

            division_info = DivisionInfo(division=division, division_id=division_id)

            session.add(division_info)
            session.commit()

    # Fill out the needed data in the ConferenceInfo table
    @classmethod
    def create_conference_info(cls):
        for x in range(1, 3):
            if x == 1:
                conference_id = 0
                conference = "AFC"
            else:
                conference_id = 1
                conference = "NFC"

            session = DbSessionFactory.create_session()

            conference_info = ConferenceInfo(
                conference=conference, conf_id=conference_id
            )

            session.add(conference_info)
            session.commit()

    """Create the pick types used for when a user submits picks, displays their picks and for calculating
    player scores.  Type 2 is not used at this time, instead player stats have their own type (passing, etc.)"""

    @classmethod
    def create_pick_types(cls):
        for x in range(1, 11):
            if x == 1:
                name = "team"
            elif x == 2:
                name = "player"
            elif x == 3:
                name = "points_for"
            elif x == 4:
                name = "passing"
            elif x == 5:
                name = "rushing"
            elif x == 6:
                name = "receiving"
            elif x == 7:
                name = "sacks"
            elif x == 8:
                name = "interceptions"
            elif x == 9:
                name = "wildcard"
            else:
                name = "tiebreaker"

            session = DbSessionFactory.create_session()

            pick_type_info = PickTypes(name=name)
            session.add(pick_type_info)
            session.commit()

    """Create the points for each pick type for first, second or third place if applicable.  Used for calculating
    player scores.  Type 2 is not used at this time, instead player stats have their own type (passing, etc.)"""

    @staticmethod
    def create_pick_type_points():
        for x in range(1, 11):
            pick_type_id = x
            if x == 1:
                for y in range(1, 5):
                    place = y

                    if y == 1:
                        points = 50
                    elif y == 2:
                        points = 30
                    elif y == 4:
                        points = 20
                    else:
                        points = 0

                    if points != 0:
                        session = DbSessionFactory.create_session()
                        pick_type_points = PickTypePoints(
                            pick_type_id=pick_type_id, place=place, points=points
                        )
                        session.add(pick_type_points)
                        session.commit()

            elif x == 2:
                continue

            elif x == 3:
                place = 1
                points = 20

                pick_type_points = PickTypePoints(
                    pick_type_id=pick_type_id, place=place, points=points
                )
                session.add(pick_type_points)
                session.commit()

            elif 3 < x < 9:
                for y in range(1, 4):
                    place = y
                    if y == 1:
                        points = 50
                    elif y == 2:
                        points = 30
                    else:
                        points = 20

                    session = DbSessionFactory.create_session()

                    pick_type_points = PickTypePoints(
                        pick_type_id=pick_type_id, place=place, points=points
                    )
                    session.add(pick_type_points)
                    session.commit()

            elif x == 9:
                place = 1
                points = 25
                session = DbSessionFactory.create_session()

                pick_type_points = PickTypePoints(
                    pick_type_id=pick_type_id, place=place, points=points
                )
                session.add(pick_type_points)
                session.commit()

            else:
                place = 1
                points = 1000
                session = DbSessionFactory.create_session()

                pick_type_points = PickTypePoints(
                    pick_type_id=pick_type_id, place=place, points=points
                )
                session.add(pick_type_points)
                session.commit() class NewSeasonService:
    @staticmethod
    def get_install():
        return []

    """After first time installation or before a new season starts, this will update the season year
    in the database.  This is used to for the MySportsFeeds API to get the correct year of stats needed."""
    # TODO Add logging
    @classmethod
    def create_season(cls, season):
        session = DbSessionFactory.create_session()
        season_row = session.query(SeasonInfo)

        if season_row.count() == 0:
            print("New install, adding a season")

            response = requests.get(
                "https://api.mysportsfeeds.com/v2.0/pull/nfl/"
                + str(season)
                + "-regular/games.json",
                auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw),
            )

            gameday_json = response.json()
            gameday_data = gameday_json["games"][0]

            first_game_date = gameday_data["schedule"]["startTime"]
            home_team = gameday_data["schedule"]["homeTeam"]["id"]
            away_team = gameday_data["schedule"]["awayTeam"]["id"]

            first_game_calc = pendulum.parse(first_game_date)

            new_season = SeasonInfo(
                season_start_date=first_game_date,
                home_team=home_team,
                away_team=away_team,
                current_season=season,
            )

            session.add(new_season)
            session.commit()

        else:
            print("Existing season found, updating to new year")

            response = requests.get(
                "https://api.mysportsfeeds.com/v2.0/pull/nfl/"
                + str(season)
                + "-regular/games.json",
                auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw),
            )

            gameday_json = response.json()
            gameday_data = gameday_json["games"][0]

            first_game_date = gameday_data["startTime"]
            home_team = gameday_data["homeTeam"]["id"]
            away_team = gameday_data["awayTeam"]["id"]

            first_game_calc = pendulum.parse(first_game_date)

            update_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
            update_row.current_season = season
            update_row.season_start_date = pendulum.instance(first_game_calc)
            update_row.away_team = away_team
            update_row.home_team = home_team

            session.commit() class ActivePlayersService:
    @classmethod
    def add_active_nflplayers(
        cls,
        season: int,
        team_id: int,
        firstname: str,
        lastname: str,
        position: str,
        player_id: int,
    ):

        session = DbSessionFactory.create_session()

        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
        season = season_row.current_season

        response = requests.get(
            "https://api.mysportsfeeds.com/v2.0/pull/nfl/players.json?season="
            + str(season)
            + "&rosterstatus=ROSTER",
            auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw),
        )

        player_info = response.json()
        player_list = player_info["players"]

        for players in player_list:
            try:
                firstname = players["player"]["firstName"]
                lastname = players["player"]["lastName"]
                player_id = players["player"]["id"]
                team_id = players["teamAsOfDate"]["id"]
                position = players["player"]["primaryPosition"]
            except KeyError:
                continue

            active_players = ActiveNFLPlayers(
                firstname=firstname,
                lastname=lastname,
                player_id=player_id,
                team_id=team_id,
                position=position,
                season=season,
            )

            session.add(active_players)

            session.commit() class UpdateNFLScheduleViewModel(ViewModelBase):
    def __init__(self):
        self.game_id = None
        self.game_date = None
        self.away_team = None
        self.home_team = None
        self.week = None
        self.season = None

    def from_dict(self, data_dict):
        self.game_id = data_dict.get("game_id")
        self.game_date = data_dict.get("game_date")
        self.away_team = data_dict.get("away_team")
        self.home_team = data_dict.get("home_team")
        self.week = data_dict.get("week")
        self.season = data_dict.get("season") class UpdateScheduleService:
    @classmethod
    def update_nflschedule(
        cls,
        season: int,
        game_id: int,
        game_date: str,
        away_team: int,
        home_team: int,
        week: int,
    ):

        session = DbSessionFactory.create_session()

        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
        season = season_row.current_season

        response = requests.get(
            "https://api.mysportsfeeds.com/v2.0/pull/nfl/"
            + str(season)
            + "-regular/games.json",
            auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw),
        )

        schedule_query = response.json()
        team_schedule = schedule_query["games"]
        print(type(team_schedule), team_schedule)

        x = 0

        for schedule in team_schedule:

            game_id = team_schedule[x]["schedule"]["id"]
            week = team_schedule[x]["schedule"]["week"]
            game_time = team_schedule[x]["schedule"]["startTime"]
            away_team = team_schedule[x]["schedule"]["awayTeam"]["id"]
            home_team = team_schedule[x]["schedule"]["homeTeam"]["id"]

            game_date = pendulum.parse(game_time)

            x = x + 1

            season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
            season = season_row.current_season

            add_schedule = NFLSchedule(
                game_id=game_id,
                game_date=game_date,
                away_team=away_team,
                home_team=home_team,
                week=week,
                season=season,
            )

            session.add(add_schedule)

            session.commit() class Account(SqlAlchemyBase):
    __tablename__ = "Account"

    id = Column(
        String, primary_key=True, default=lambda: str(uuid.uuid4()).replace("-", "")
    )

    email = Column(String, index=True, unique=True, nullable=False)
    first_name = Column(String, nullable=False)
    last_name = Column(String, nullable=False)
    password_hash = Column(String)
    created = Column(DateTime, default=datetime.datetime.now)
    email_confirmed = Column(Boolean, nullable=False, default=False)
    is_super_user = Column(Boolean, nullable=False, default=False)
    twitter = Column(String, nullable=True)
    paid = Column(Integer, default=0) class DbSessionFactory:
    factory = None

    # Start a database session at app startup
    @staticmethod
    def global_init(db_file):
        if DbSessionFactory.factory:
            return

        if not db_file or not db_file.strip():
            raise Exception("You must specify a data file.")

        conn_str = "sqlite:///" + db_file
        print("Connecting to db with conn string: {}".format(conn_str))

        engine = sqlalchemy.create_engine(conn_str, echo=False)

        SqlAlchemyBase.metadata.create_all(engine)
        DbSessionFactory.factory = sqlalchemy.orm.sessionmaker(bind=engine)

    @staticmethod
    def create_session():
        return DbSessionFactory.factory() class AccountService:
    @staticmethod
    def get_all_accounts():
        session = DbSessionFactory.create_session()
        account_list = session.query(Account).all()

        return account_list

    @classmethod
    def update_paid(cls, user_id: str):

        session = DbSessionFactory.create_session()

        for player in session.query(Account.id).filter(Account.id == user_id):
            session.query(Account.id).filter(Account.id == user_id).update({"paid": 1})

        session.commit()
        session.close()

    @staticmethod
    def reset_paid():

        session = DbSessionFactory.create_session()

        for player in session.query(Account):
            session.query(Account.paid).update({"paid": 0})

        session.commit()

    @classmethod
    def update_admin(cls, user_id: str):

        session = DbSessionFactory.create_session()

        for player in session.query(Account.id).filter(Account.id == user_id):
            session.query(Account.id).filter(Account.id == user_id).update(
                {"is_super_user": 1}
            )

        session.commit() class UpdateWeeklyStats(ViewModelBase):
    def __init__(self):
        self.player_id = None
        self.passyds = None
        self.rushyds = None
        self.recyds = None
        self.sacks = None
        self.interceptions = None
        self.week = None
        self.season = None

    def from_dict(self, data_dict):
        self.player_id = data_dict.get("player_id")
        self.passyds = data_dict.get("passyds")
        self.rushyds = data_dict.get("rushyds")
        self.recyds = data_dict.get("recyds")
        self.sacks = data_dict.get("sacks")
        self.interceptions = data_dict.get("interceptions")
        self.week = data_dict.get("week")
        self.season = data_dict.get("season") class WeeklyStatsService:
    # Open a connection to the database to get the current season year from the SeasonInfo table
    # Get weekly stats for each player for yards (passing, receiving, rushing), sacks and interceptions
    @staticmethod
    def get_qb_stats():

        week = TimeService.get_week()

        session = DbSessionFactory.create_session()

        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
        season = season_row.current_season

        response = requests.get(
            "https://api.mysportsfeeds.com/v2.0/pull/nfl/"
            + str(season)
            + "-regular/player_stats_totals.json?position=QB&stats=Yds",
            auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw),
        )

        player_json = response.json()
        player_data = player_json["playerStatsTotals"]

        for players in player_data:
            try:
                player_id = players["player"]["id"]
                passyds = players["stats"]["passing"]["passYards"]

            except KeyError:
                continue

            weekly_player_stats = WeeklyNFLPlayerStats(
                player_id=player_id, season=season, passyds=passyds, week=week
            )

            session.add(weekly_player_stats)

            session.commit()

    @staticmethod
    def get_rb_stats():

        session = DbSessionFactory.create_session()

        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
        season = season_row.current_season

        response = requests.get(
            "https://api.mysportsfeeds.com/v2.0/pull/nfl/"
            + str(season)
            + "-regular/player_stats_totals.json?position=RB&stats=Yds",
            auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw),
        )

        player_json = response.json()
        player_data = player_json["playerStatsTotals"]

        for players in player_data:
            try:
                player_id = players["player"]["id"]
                rushyds = players["stats"]["rushing"]["rushYards"]

            except KeyError:
                continue

            week = TimeService.get_week()

            weekly_player_stats = WeeklyNFLPlayerStats(
                player_id=player_id, season=season, rushyds=rushyds, week=week
            )

            session.add(weekly_player_stats)

            session.commit()

    @staticmethod
    def get_rec_stats():

        session = DbSessionFactory.create_session()

        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
        season = season_row.current_season

        response = requests.get(
            "https://api.mysportsfeeds.com/v2.0/pull/nfl/"
            + str(season)
            + "-regular/player_stats_totals.json?position=WR,TE&stats=Yds",
            auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw),
        )

        player_json = response.json()
        player_data = player_json["playerStatsTotals"]

        for players in player_data:
            try:
                player_id = players["player"]["id"]
                recyds = players["stats"]["receiving"]["recYards"]

            except KeyError:
                continue

            week = TimeService.get_week()

            weekly_team_stats = WeeklyNFLPlayerStats(
                player_id=player_id, season=season, recyds=recyds, week=week
            )

            session.add(weekly_team_stats)

            session.commit()

    @staticmethod
    def get_sack_stats():

        session = DbSessionFactory.create_session()

        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
        season = season_row.current_season

        response = requests.get(
            "https://api.mysportsfeeds.com/v2.0/pull/nfl/"
            + str(season)
            + "-regular/player_stats_totals.json?position=DE,DT,ILB,LB,MLB,NT,OLB&stats=Sacks",
            auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw),
        )

        player_json = response.json()
        player_data = player_json["playerStatsTotals"]

        for players in player_data:
            try:
                player_id = players["player"]["id"]
                sacks = players["stats"]["tackles"]["sacks"]

            except KeyError:
                continue

            week = TimeService.get_week()

            weekly_player_stats = WeeklyNFLPlayerStats(
                player_id=player_id, season=season, sacks=sacks, week=week
            )

            session.add(weekly_player_stats)

            session.commit()

    @staticmethod
    def get_interception_stats():

        session = DbSessionFactory.create_session()

        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
        season = season_row.current_season

        response = requests.get(
            "https://api.mysportsfeeds.com/v2.0/pull/nfl/"
            + str(season)
            + "-regular/player_stats_totals.json?position=LB,SS,DT,DE,CB,FS,SS,ILB,MLB,OLB&"
            "stats=Int",
            auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw),
        )

        player_json = response.json()
        player_data = player_json["playerStatsTotals"]

        for players in player_data:
            try:
                player_id = players["player"]["id"]
                interceptions = players["stats"]["interceptions"]["interceptions"]

            except KeyError:
                continue

            week = TimeService.get_week()

            weekly_player_stats = WeeklyNFLPlayerStats(
                player_id=player_id,
                season=season,
                interceptions=interceptions,
                week=week,
            )

            session.add(weekly_player_stats)

            session.commit()

    @staticmethod
    def get_rankings():

        session = DbSessionFactory.create_session()

        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
        season = season_row.current_season

        response = requests.get(
            "https://api.mysportsfeeds.com/v2.0/pull/nfl/"
            + str(season)
            + "-regular/standings.json",
            auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw),
        )

        standings_json = response.json()
        standings_data = standings_json["teams"]

        x = 0

        # print(type(standings_data), standings_data)

        for teams in standings_data:
            team_id = standings_data[x]["team"]["id"]
            division_rank = standings_data[x]["divisionRank"]["rank"]
            playoff_rank = standings_data[x]["playoffRank"]["rank"]

            week = TimeService.get_week()

            weekly_team_stats = WeeklyTeamStats(
                team_id=team_id,
                season=season,
                division_rank=division_rank,
                conference_rank=playoff_rank,
                week=week,
            )

            x += 1

            session.add(weekly_team_stats)
            session.commit()

    @staticmethod
    def get_points_for():
        session = DbSessionFactory.create_session()

        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
        season = season_row.current_season

        response = requests.get(
            "https://api.mysportsfeeds.com/v2.0/pull/nfl/"
            + str(season)
            + "-regular/team_stats_totals.json?stats=pointsFor",
            auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw),
        )

        points_for_json = response.json()
        points_for_data = points_for_json["teamStatsTotals"]

        x = 0

        for teams in points_for_data:
            team_id = int(points_for_data[x]["team"]["id"])
            points_for = points_for_data[x]["stats"]["standings"]["pointsFor"]

            week = TimeService.get_week()

            x += 1

            session.query(WeeklyTeamStats).filter(
                WeeklyTeamStats.team_id == team_id
            ).filter(WeeklyTeamStats.season == season).filter(
                WeeklyTeamStats.week == week
            ).update(
                {"points_for": points_for}
            )

            session.commit()

    @staticmethod
    def get_tiebreaker():
        session = DbSessionFactory.create_session()

        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
        season = season_row.current_season

        response = requests.get(
            "https://api.mysportsfeeds.com/v2.0/pull/nfl/"
            + str(season)
            + "-regular/team_stats_totals.json?stats=TD",
            auth=HTTPBasicAuth(secret.msf_api, secret.msf_v2pw),
        )

        x = 0

        tiebreaker_json = response.json()
        tiebreaker_data = tiebreaker_json["teamStatsTotals"]

        for teams in tiebreaker_data:
            team_id = tiebreaker_data[x]["team"]["id"]
            kr_td = tiebreaker_data[x]["stats"]["kickoffReturns"]["krTD"]
            pr_td = tiebreaker_data[x]["stats"]["puntReturns"]["prTD"]

            tiebreaker_td = int(kr_td) + int(pr_td)

            week = TimeService.get_week()

            x += 1

            session.query(WeeklyTeamStats).filter(
                WeeklyTeamStats.team_id == team_id
            ).filter(WeeklyTeamStats.season == season).filter(week == week).update(
                {"tiebreaker_td": tiebreaker_td}
            )
            session.query(WeeklyTeamStats).filter(
                WeeklyTeamStats.team_id == team_id
            ).filter(WeeklyTeamStats.season == season).filter(week == week).update(
                {"tiebreaker_team": team_id}
            )

            session.commit() class UniquePicksViewModel(ViewModelBase):
    def __init__(self):
        self.season = None

    def from_dict(self, data_dict):
        self.season = data_dict.get("season") class UniquePicksService:
    @classmethod
    def unique_team_picks(cls, pick_type, conf=None, div=None, rank=None):
        session = DbSessionFactory.create_session()

        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
        current_season = season_row.current_season

        txtstr = "UPDATE PlayerPicks SET multiplier=2 WHERE team_id IN "
        txtstr += "(SELECT team_id FROM (select DISTINCT(team_id), COUNT(team_id) AS ct FROM PlayerPicks WHERE "
        midstr = " GROUP BY team_id) WHERE ct=1) "

        condstr = "pick_type=" + str(pick_type) + " AND season=" + str(current_season)

        if conf is not None:
            condstr += " AND conf_id=" + str(conf)
            if div is not None:
                condstr += " AND division_id=" + str(div)
                if rank is not None:
                    condstr += " AND rank=" + str(rank)

        txtstr += condstr + midstr + "AND " + condstr

        #        print(txtstr)

        session.execute(txtstr)
        session.commit()
        session.close()

    @classmethod
    def unique_player_picks(cls, pick_type, conf):
        session = DbSessionFactory.create_session()

        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
        current_season = season_row.current_season

        txtstr = "UPDATE PlayerPicks SET multiplier=2 WHERE player_id IN "
        txtstr += "(SELECT player_id FROM (select DISTINCT(player_id), COUNT(player_id) AS ct FROM PlayerPicks WHERE "
        midstr = " GROUP BY player_id) WHERE ct=1) "

        condstr = " pick_type=" + str(pick_type) + " AND season=" + str(current_season)

        condstr += " AND conf_id=" + str(conf)

        txtstr += condstr + midstr + "AND " + condstr

        #  print(txtstr)

        session.execute(txtstr)
        session.commit()
        session.close() class StandingsService:
    @staticmethod
    def display_player_standings(player_id, season=None):
        if season is None:
            season = get_seasons()

        sqlstr = (
            "SELECT coalesce(w.points_earned,0) as points, a.first_name, a.last_name, w.pick_id, p.pick_type, "
            "p.rank, p.multiplier, t.name, "
        )
        sqlstr += "c.conference, d.division, ap.firstname, ap.lastname "
        sqlstr += "FROM PlayerPicks p, Account a "
        sqlstr += "LEFT JOIN WeeklyPlayerResults w on p.pick_id = w.pick_id "
        sqlstr += (
            "AND w.week = (SELECT MAX(week) from WeeklyPlayerResults  WHERE season="
            + str(season)
            + ") "
        )
        sqlstr += "LEFT JOIN  DivisionInfo d on p.division_id=d.division_id "
        sqlstr += "LEFT JOIN ConferenceInfo c ON p.conf_id= c.conf_id "
        sqlstr += "LEFT JOIN TeamInfo t ON p.team_id = t.team_id "
        sqlstr += "LEFT JOIN ActiveNFLPlayers ap ON p.player_id = ap.player_id AND p.season = ap.season "
        sqlstr += "WHERE "
        sqlstr += "p.user_id = a.id "
        sqlstr += "AND p.season = " + str(season) + " "
        sqlstr += "AND p.user_id = '" + player_id + "'"

        session = DbSessionFactory.create_session()
        standings = session.execute(sqlstr)

        dict_standings = [dict(row) for row in standings]

        session.close()
        return dict_standings

    @staticmethod
    def display_weekly_standings(season=None):

        # return list that contains player standings for most recent week in results table
        if season is None:
            season = get_seasons()

        sqlstr = (
            "SELECT SUM(w.points_earned) as total_points, a.first_name, a.last_name, a.id "
            "from WeeklyPlayerResults w, PlayerPicks p, Account a "
        )
        sqlstr += "WHERE w.pick_id = p.pick_id AND p.user_id = a.id "
        sqlstr += "AND w.season = " + str(season) + " "
        sqlstr += "AND p.season = " + str(season) + " "
        sqlstr += (
            "AND w.week = (SELECT MAX(week) from WeeklyPlayerResults WHERE season = "
            + str(season)
            + ") "
        )
        sqlstr += "GROUP BY p.user_id "
        sqlstr += "ORDER BY total_points DESC"

        session = DbSessionFactory.create_session()
        standings = session.execute(sqlstr)

        dict_standings = [dict(row) for row in standings]

        session.close()
        return dict_standings

    @staticmethod
    def update_player_pick_points():
        season = get_seasons()
        week = TimeService.get_week()

        session = DbSessionFactory.create_session()

        # starting values
        conf = 0
        i = 4

        while conf < 2:
            # start with pick type 4 and continue through 8

            if i == 4:
                cattype = "passyds"
            elif i == 5:
                cattype = "rushyds"
            elif i == 6:
                cattype = "recyds"
            elif i == 7:
                cattype = "sacks"
            elif i == 8:
                cattype = "interceptions"

            sqlstr = "INSERT INTO WeeklyPlayerResults (pick_id, season, week, points_earned) "
            sqlstr += (
                "SELECT t1.pick_id as pick_id, t1.season as season, t1.week as week, "
                "pts.points*t1.multiplier as points_earned "
            )
            sqlstr += "FROM "
            sqlstr += "(SELECT p.pick_id, p.user_id, p.multiplier, p.player_id, "
            sqlstr += "(SELECT count(*) from WeeklyNFLPlayerStats as w2, ActiveNFLPlayers as ap, "
            sqlstr += "TeamInfo as t "
            sqlstr += "WHERE "
            sqlstr += "w2.week = " + str(week) + " "
            sqlstr += "AND w2.season = " + str(season) + " "
            sqlstr += "AND ap.season = w2.season "
            sqlstr += "AND w2.player_id = ap.player_id "
            sqlstr += "AND ap.team_id = t.team_id "
            sqlstr += "AND t.conference_id = " + str(conf) + " "
            sqlstr += "AND W2." + cattype + ">w." + cattype + ")+1 as rank, "
            sqlstr += "w.week, "
            sqlstr += "w.season "
            sqlstr += "FROM WeeklyNFLPlayerStats w, PlayerPicks p "
            sqlstr += "WHERE w.player_id = p.player_id "
            sqlstr += "AND  w.season = " + str(season) + " "
            sqlstr += "AND w.week = " + str(week) + " "
            sqlstr += "AND p.pick_type = " + str(i) + " "
            sqlstr += "AND p.conf_id = " + str(conf) + " "
            sqlstr += "AND w." + cattype + " not null "
            sqlstr += "ORDER BY rank) as t1, PickTypePoints pts "
            sqlstr += "WHERE "
            sqlstr += "pts.pick_type_id = " + str(i) + " "
            sqlstr += "AND t1.rank = pts.place"

            session.execute(sqlstr)
            session.commit()

            # increment counters
            if i == 8:
                conf += 1
                i = 0

            i += 1

        session.close()

    @staticmethod
    def update_team_pick_points():
        session = DbSessionFactory.create_session()

        season = get_seasons()
        week = TimeService.get_week()

        # this does all type 1 points
        sqlstr = (
            "INSERT INTO WeeklyPlayerResults(pick_id, season, week, points_earned) "
        )
        sqlstr += "SELECT pp.pick_id, w.season, w.week, p.points * pp.multiplier as points_earned "
        sqlstr += "FROM PlayerPicks pp "
        sqlstr += "LEFT JOIN WeeklyTeamStats w on pp.rank=w.division_rank and pp.team_id=w.team_id "
        sqlstr += "LEFT JOIN TeamInfo t on pp.team_id= t.team_id "
        sqlstr += "LEFT JOIN PickTypePoints p on pp.pick_type = p.pick_type_id "
        sqlstr += "WHERE pp.pick_type = 1 "
        sqlstr += "AND w.season = " + str(season) + " "
        sqlstr += "AND w.week = " + str(week) + " "
        sqlstr += "AND pp.conf_id = t.conference_id "
        sqlstr += "AND pp.division_id = t.division_id "
        sqlstr += "AND p.place = w.division_rank "
        sqlstr += "ORDER BY pp.user_id"

        session.execute(sqlstr)
        session.commit()

        # type 3 points:
        conf = 0
        while conf < 2:
            sqlstr = (
                "INSERT INTO WeeklyPlayerResults(pick_id, season, week, points_earned) "
            )
            sqlstr += (
                "SELECT t1.pick_id as pick_id, t1.season as season, t1.week as week, "
                "pts.points * t1.multiplier as points_earned "
            )
            sqlstr += "FROM (SELECT p.pick_id, p.user_id, p.multiplier, p.team_id, "
            sqlstr += "(SELECT count(*) FROM WeeklyTeamStats as w2, TeamInfo as t "
            sqlstr += "WHERE w2.team_id = t.team_id "
            sqlstr += "AND w2.week = " + str(week) + " "
            sqlstr += "AND w2.season = " + str(season) + " "
            sqlstr += "AND t.conference_id = " + str(conf) + " "
            sqlstr += "AND w2.points_for > w.points_for)+1 as rank, "
            sqlstr += "w.week, w.season "
            sqlstr += "FROM WeeklyTeamStats w, PlayerPicks p "
            sqlstr += "WHERE w.team_id = p.team_id "
            sqlstr += "AND w.season = " + str(season) + " "
            sqlstr += "AND w.week = " + str(week) + " "
            sqlstr += "AND p.pick_type = 3 "
            sqlstr += "AND p.conf_id = " + str(conf) + " "
            sqlstr += "AND w.points_for not null "
            sqlstr += "ORDER BY rank) as t1, PickTypePoints pts "
            sqlstr += "WHERE "
            sqlstr += "pts.pick_type_id = 3 "
            sqlstr += "AND t1.rank = pts.place"

            session.execute(sqlstr)
            session.commit()
            conf += 1

        # type 9 points - wildcard
        sqlstr = (
            "INSERT INTO WeeklyPlayerResults (pick_id, season, week, points_earned) "
        )
        sqlstr += (
            "SELECT p.pick_id, w.season, w.week, pts.points*p.multiplier as points_earned "
            "from PlayerPicks p, WeeklyTeamStats w, PickTypePoints pts "
        )
        sqlstr += "WHERE p.pick_type = 9 "
        sqlstr += "AND p.pick_type = pts.pick_type_id "
        sqlstr += "AND w.conference_rank in (5,6) "
        sqlstr += "AND w.team_id = p.team_id "
        sqlstr += "AND w.season = " + str(season) + " "
        sqlstr += "AND w.week = " + str(week)

        session.execute(sqlstr)
        session.commit()

        session.close()

    @staticmethod
    def all_seasons_played():
        """This method is used to get a list of all seasons played and display on the Standings index page
        for players to click through to see the season standings / points scored by player"""
        session = DbSessionFactory.create_session()

        seasons_played = (
            session.query(PlayerPicks.season)
            .distinct(PlayerPicks.season)
            .order_by(PlayerPicks.season.desc())
        )

        session.close()

        return seasons_played class AdminViewModel(ViewModelBase):
    def __init__(self):
        self.user_id = None
        self.is_super_user = None

    def from_dict(self, data_dict):
        self.user_id = data_dict.get("user_id")
        self.is_super_user = data_dict.get("is_super_user") class SeasonInfo(SqlAlchemyBase):
    __tablename__ = "SeasonInfo"
    id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True)
    current_season = sqlalchemy.Column(sqlalchemy.Integer)
    season_start_date = sqlalchemy.Column(sqlalchemy.String)
    home_team = sqlalchemy.Column(sqlalchemy.String)
    away_team = sqlalchemy.Column(sqlalchemy.String) class TeamInfo(SqlAlchemyBase):
    __tablename__ = "TeamInfo"
    team_id = sqlalchemy.Column(sqlalchemy.Integer, index=True, primary_key=True)
    name = sqlalchemy.Column(sqlalchemy.String)
    city = sqlalchemy.Column(sqlalchemy.String)
    team_abbr = sqlalchemy.Column(sqlalchemy.String)
    conference_id = sqlalchemy.Column(sqlalchemy.Integer)
    division_id = sqlalchemy.Column(sqlalchemy.Integer) class WeeklyTeamStats(SqlAlchemyBase):
    __tablename__ = "WeeklyTeamStats"
    primary_key = sqlalchemy.Column(
        sqlalchemy.Integer, primary_key=True, autoincrement=True
    )
    season = sqlalchemy.Column(sqlalchemy.Integer, index=True)
    week = sqlalchemy.Column(sqlalchemy.Integer, index=True)
    team_id = sqlalchemy.Column(
        sqlalchemy.Integer, sqlalchemy.ForeignKey("TeamInfo.team_id")
    )
    division_rank = sqlalchemy.Column(sqlalchemy.Integer)
    conference_rank = sqlalchemy.Column(sqlalchemy.Integer)
    points_for = sqlalchemy.Column(sqlalchemy.Integer)
    tiebreaker_td = sqlalchemy.Column(sqlalchemy.Integer)
    tiebreaker_team = sqlalchemy.Column(sqlalchemy.Integer) class TimeService:
    @staticmethod
    def get_time():
        """Create a service to get the time - there were too many instances of getting the current time in
        the codebase making testing difficult."""
        # Change now_time for testing
        # Use this one for production:
        now_time = pendulum.now(tz=pendulum.timezone("America/New_York"))
        # Use this one for testing:
        # now_time = pendulum.datetime(2018, 9, 18, 6, 21, tz='America/New_York')

        return now_time

    @staticmethod
    def get_week():
        session = DbSessionFactory.create_session()
        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()

        season_start = pendulum.parse(season_row.season_start_date)

        diff = TimeService.get_time() - season_start
        week = int((diff.days / 7) + 1)

        return week
import pyramid_handlers
from nflpool.controllers.base_controller import BaseController
from nflpool.viewmodels.newinstallviewmodel import NewInstallViewModel
from nflpool.viewmodels.newseasonviewmodel import NewSeasonViewModel
from nflpool.viewmodels.update_nflplayers_viewmodel import UpdateNFLPlayersViewModel
from nflpool.services.new_install_service import NewInstallService
from nflpool.services.new_season_service import NewSeasonService
from nflpool.services.activeplayers_service import ActivePlayersService
from nflpool.viewmodels.update_nflschedule_viewmodel import UpdateNFLScheduleViewModel
from nflpool.services.update_nflschedule_service import UpdateScheduleService
from nflpool.data.account import Account
from nflpool.data.dbsession import DbSessionFactory
from nflpool.services.admin_service import AccountService
from nflpool.viewmodels.update_weekly_stats_viewmodel import UpdateWeeklyStats
from nflpool.services.weekly_msf_data import WeeklyStatsService
from nflpool.viewmodels.update_unique_picks_viewmodel import UniquePicksViewModel
from nflpool.services.unique_picks_service import UniquePicksService
from nflpool.services.standings_service import StandingsService
from nflpool.viewmodels.admin_update_viewmodel import AdminViewModel
from nflpool.data.seasoninfo import SeasonInfo
from nflpool.data.teaminfo import TeamInfo
from nflpool.data.weekly_team_stats import WeeklyTeamStats
from nflpool.services.time_service import TimeService
        session = DbSessionFactory.create_session()
        su__query = (
            session.query(Account.id)
            .filter(Account.is_super_user == 1)
            .filter(Account.id == self.logged_in_user_id)
            .first()
        )

        if su__query is None:
            print("You must be an administrator to view this page")
            self.redirect("/home")

        vm = UpdateNFLPlayersViewModel()
        return vm.to_dict()

    @pyramid_handlers.action(
        renderer="templates/admin/update_nflplayers.pt",
        request_method="POST",
        name="update_nflplayers",
    )
    def update_nfl_players_post(self):
        vm = UpdateNFLPlayersViewModel()
        vm.from_dict(self.request.POST)

        # Insert NFLPlayer info
        ActivePlayersService.add_active_nflplayers(
            vm.firstname, vm.lastname, vm.player_id, vm.team_id, vm.position, vm.season
        )

        # redirect
        self.redirect("/admin/update_nflschedule")

    @pyramid_handlers.action(
        renderer="templates/admin/update_nflschedule.pt",
        request_method="GET",
        name="update_nflschedule",
    )
    def update_nfl_schedule(self):
        session = DbSessionFactory.create_session()
        su__query = (
            session.query(Account.id)
            .filter(Account.is_super_user == 1)
            .filter(Account.id == self.logged_in_user_id)
            .first()
        )

        if su__query is None:
            print("You must be an administrator to view this page")
            self.redirect("/home")

        vm = UpdateNFLScheduleViewModel()
        return vm.to_dict()

    @pyramid_handlers.action(
        renderer="templates/admin/update_nflschedule.pt",
        request_method="POST",
        name="update_nflschedule",
    )
    def update_nfl_schedule_post(self):
        vm = UpdateNFLScheduleViewModel()
        vm.from_dict(self.request.POST)

        # Insert NFL Schedule
        UpdateScheduleService.update_nflschedule(
            vm.game_id, vm.game_date, vm.away_team, vm.home_team, vm.week, vm.season
        )

        # redirect
        self.redirect("/admin")

    @pyramid_handlers.action(
        renderer="templates/admin/account-list.pt",
        request_method="GET",
        name="account-list",
    )
    def list_accounts(self):

        # Show list of accounts
        account_list = AccountService.get_all_accounts()

        return {"account_list": account_list}

    @pyramid_handlers.action(
        renderer="templates/admin/update-weekly-stats.pt",
        request_method="GET",
        name="update-weekly-stats",
    )
    def update_weekly_stats(self):
        session = DbSessionFactory.create_session()
        su__query = (
            session.query(Account.id)
            .filter(Account.is_super_user == 1)
            .filter(Account.id == self.logged_in_user_id)
            .first()
        )

        if su__query is None:
            print("You must be an administrator to view this page")
            self.redirect("/home")

        vm = UpdateWeeklyStats()
        return vm.to_dict()

    @pyramid_handlers.action(
        renderer="templates/admin/update-weekly-stats.pt",
        request_method="POST",
        name="update-weekly-stats",
    )
    def update_weekly_stats_post(self):
        vm = UpdateWeeklyStats()
        vm.from_dict(self.request.POST)

        session = DbSessionFactory.create_session()

        week = TimeService.get_week()

        season_row = session.query(SeasonInfo).filter(SeasonInfo.id == "1").first()
        season = season_row.current_season

        row = (

            session.query(WeeklyTeamStats.week)