class UserProfile(ModelMixin, db.Model):
"""
Model that describes the 'user_profiles' SQL table
"""
def __init__(self, *args, **kwargs):
"""
Initializes the Model
:param args: The constructor arguments
:param kwargs: The constructor keyword arguments
"""
super().__init__(*args, **kwargs)
__tablename__ = "user_profiles"
user_id: int = db.Column(db.Integer,
db.ForeignKey("users.id", ondelete="CASCADE"),
primary_key=True)
favourite_team_abbreviation: Optional[str] = db.Column(
db.String(3), db.ForeignKey("teams.abbreviation"), nullable=True)
description: Optional[str] = db.Column(db.String(255), nullable=True)
country: Optional[str] = db.Column(db.String(255), nullable=True)
user: User = db.relationship(
"User",
backref=db.backref("profile", cascade="all, delete", uselist=False),
)
favourite_team: Team = db.relationship("Team")
class SeasonWinner(ModelMixin, db.Model):
"""
Model that describes the 'season_winners' SQL table
"""
def __init__(self, *args, **kwargs):
"""
Initializes the Model
:param args: The constructor arguments
:param kwargs: The constructor keyword arguments
"""
super().__init__(*args, **kwargs)
__tablename__ = "season_winners"
league: str = db.Column(db.String(255), primary_key=True)
season: int = db.Column(db.Integer, primary_key=True)
user_id: int = db.Column(db.Integer,
db.ForeignKey("users.id"),
nullable=False)
user: User = db.relationship("User",
backref=db.backref("season_winners",
cascade="all, delete"))
@property
def season_string(self) -> str:
"""
:return: The season string, e.g. Bundesliga 2019/20
"""
return Config.league_string(self.league, self.season)
class DisplayBotsSettings(ModelMixin, db.Model):
"""
Database model that specifies whether a user wants to see bots or not
"""
def __init__(self, *args, **kwargs):
"""
Initializes the Model
:param args: The constructor arguments
:param kwargs: The constructor keyword arguments
"""
super().__init__(*args, **kwargs)
__tablename__ = "display_bot_settings"
user_id: int = db.Column(db.Integer,
db.ForeignKey("users.id"),
primary_key=True)
display_bots = db.Column(db.Boolean, nullable=False, default=True)
user: User = db.relationship("User",
backref=db.backref("display_bot_settings",
cascade="all, delete"))
@classmethod
def get_state(cls, user: User):
"""
Retrieves the state of the settings for a give user
:param user: The user for which to retrieve the seetings
:return: True if active, False otherwise
"""
bot_setting = DisplayBotsSettings.query.filter_by(
user_id=user.id).first()
return bot_setting is not None and bot_setting.display_bots
@staticmethod
def bot_symbol() -> str:
"""
:return: "The bot unicode symbol"
"""
return "🤖"
class MatchdayWinner(ModelMixin, db.Model):
"""
Model that describes the 'matchday_winners' SQL table
"""
def __init__(self, *args, **kwargs):
"""
Initializes the Model
:param args: The constructor arguments
:param kwargs: The constructor keyword arguments
"""
super().__init__(*args, **kwargs)
__tablename__ = "matchday_winners"
league: str = db.Column(db.String(255), primary_key=True)
season: int = db.Column(db.Integer, primary_key=True)
matchday: int = db.Column(db.Integer, primary_key=True)
user_id: int = db.Column(db.Integer,
db.ForeignKey("users.id"),
nullable=True)
user: User = db.relationship("User",
backref=db.backref("matchday_winners",
cascade="all, delete"))
class NotificationSetting(ModelMixin, db.Model):
"""
Database model that stores notification settings for a user
"""
__tablename__ = "notification_settings"
"""
The name of the database table
"""
def __init__(self, *args, **kwargs):
"""
Initializes the Model
:param args: The constructor arguments
:param kwargs: The constructor keyword arguments
"""
super().__init__(*args, **kwargs)
user_id: int = db.Column(db.Integer,
db.ForeignKey("users.id",
ondelete="CASCADE",
onupdate="CASCADE"),
nullable=False)
"""
The ID of the user associated with this notification setting
"""
user: User = db.relationship("User",
backref=db.backref("notification_settings",
lazy=True,
cascade="all,delete"))
"""
The user associated with this notification setting
"""
notification_type: str = \
db.Column(db.Enum(NotificationType), nullable=False)
"""
The notification type
"""
minimum_score: int = db.Column(db.Integer, default=0, nullable=False)
"""
The minimum score for notification items
"""
value: bool = db.Column(db.Boolean, nullable=False, default=False)
"""
Whether or not the notification is active or not
"""
@property
def identifier_tuple(self) -> Tuple[int]:
"""
:return: A tuple that uniquely identifies this database entry
"""
return self.user_id,
def update(self, new_data: "NotificationSetting"):
"""
Updates the data in this record based on another object
:param new_data: The object from which to use the new values
:return: None
"""
self.user_id = new_data.user_id
self.notification_type = new_data.notification_type
self.value = new_data.value
self.minimum_score = new_data.minimum_score
class Bet(ModelMixin, db.Model):
"""
Model that describes the 'bets' SQL table
"""
MAX_POINTS: int = 15
POSSIBLE_POINTS: List[int] = [0, 3, 7, 10, 12, 15]
def __init__(self, *args, **kwargs):
"""
Initializes the Model
:param args: The constructor arguments
:param kwargs: The constructor keyword arguments
"""
super().__init__(*args, **kwargs)
__tablename__ = "bets"
__table_args__ = (db.ForeignKeyConstraint(
("home_team_abbreviation", "away_team_abbreviation", "league",
"season", "matchday"),
(Match.home_team_abbreviation, Match.away_team_abbreviation,
Match.league, Match.season, Match.matchday)), )
league: str = db.Column(db.String(255), primary_key=True)
season: int = db.Column(db.Integer, primary_key=True)
matchday: int = db.Column(db.Integer, primary_key=True)
home_team_abbreviation: str = db.Column(db.String(3), primary_key=True)
away_team_abbreviation: str = db.Column(db.String(3), primary_key=True)
user_id: int = db.Column(db.Integer,
db.ForeignKey("users.id"),
primary_key=True)
home_score: int = db.Column(db.Integer, nullable=False)
away_score: int = db.Column(db.Integer, nullable=False)
points: int = db.Column(db.Integer, nullable=True)
user: User = db.relationship("User",
backref=db.backref("bets",
cascade="all, delete"))
match: Match = db.relationship("Match", overlaps="bets")
def __repr__(self) -> str:
"""
:return: A string with which the object may be generated
"""
params = ""
for key, val in self.__json__().items():
if key == "points":
continue
params += "{}={}, ".format(key, repr(val))
params = params.rsplit(",", 1)[0]
return "{}({})".format(self.__class__.__name__, params)
def __eq__(self, other: Any) -> bool:
"""
Checks the model object for equality with another object
:param other: The other object
:return: True if the objects are equal, False otherwise
"""
if isinstance(other, Bet):
return self.user_id == other.user_id \
and self.home_team_abbreviation == \
other.home_team_abbreviation \
and self.away_team_abbreviation == \
other.away_team_abbreviation \
and self.home_score == other.home_score \
and self.away_score == other.away_score
else:
return False # pragma: no cover
def evaluate(self) -> Optional[int]:
"""
Evaluates the current points score on this bet
:return: The calculated points (or None if the math hasn't started yet)
"""
if not self.match.has_started:
return None
points = 0
bet_diff = self.home_score - self.away_score
match_diff = \
self.match.home_current_score - self.match.away_current_score
if bet_diff == match_diff: # Correct goal difference
points += 5
if bet_diff * match_diff > 0: # Correct winner
points += 7
elif bet_diff == 0 and match_diff == 0: # Draw
points += 7
if self.home_score == self.match.home_current_score \
or self.away_score == self.match.away_current_score:
points += 3
return points
class MediaListItem(ModelMixin, db.Model):
"""
Database model for media list items.
This model maps MediaLists and MediaUserStates
"""
__tablename__ = "media_list_items"
"""
The name of the database table
"""
__table_args__ = (db.UniqueConstraint("media_list_id",
"media_user_state_id",
name="unique_media_list_item"), )
"""
Makes sure that objects that should be unique are unique
"""
def __init__(self, *args, **kwargs):
"""
Initializes the Model
:param args: The constructor arguments
:param kwargs: The constructor keyword arguments
"""
super().__init__(*args, **kwargs)
media_list_id: int = db.Column(db.Integer,
db.ForeignKey("media_lists.id"),
nullable=False)
"""
The ID of the media list this list item is a part of
"""
media_list: MediaList = db.relationship("MediaList",
back_populates="media_list_items")
"""
The media list this list item is a part of
"""
media_user_state_id: int = db.Column(db.Integer,
db.ForeignKey("media_user_states.id",
ondelete="CASCADE",
onupdate="CASCADE"),
nullable=False)
"""
The ID of the media user state this list item references
"""
media_user_state: MediaUserState = db.relationship(
"MediaUserState",
backref=db.backref("media_list_items", lazy=True,
cascade="all,delete"))
"""
The media user state this list item references
"""
@property
def identifier_tuple(self) -> Tuple[int, int]:
"""
:return: A tuple that uniquely identifies this database entry
"""
return self.media_list_id, self.media_user_state_id
def update(self, new_data: "MediaListItem"):
"""
Updates the data in this record based on another object
:param new_data: The object from which to use the new values
:return: None
"""
self.media_list_id = new_data.media_list_id
self.media_user_state_id = new_data.media_user_state_id
class LeaderboardEntry(ModelMixin, db.Model):
"""
Model that describes the 'leaderboard_entries' SQL table
"""
def __init__(self, *args, **kwargs):
"""
Initializes the Model
:param args: The constructor arguments
:param kwargs: The constructor keyword arguments
"""
super().__init__(*args, **kwargs)
__tablename__ = "leaderboard_entries"
league: int = db.Column(db.String(255), primary_key=True)
season: int = db.Column(db.Integer, primary_key=True)
matchday: int = db.Column(db.Integer, primary_key=True)
user_id: int = db.Column(db.Integer,
db.ForeignKey("users.id"),
primary_key=True)
points: int = db.Column(db.Integer, nullable=False)
position: int = db.Column(db.Integer, nullable=False)
no_bot_position: int = db.Column(db.Integer, nullable=False)
previous_position: int = db.Column(db.Integer, nullable=False)
no_bot_previous_position: int = db.Column(db.Integer, nullable=False)
user: User = db.relationship("User",
backref=db.backref("leaderboard_entries",
cascade="all, delete"))
def get_position_info(self, include_bots: bool) -> Tuple[int, int]:
"""
Retrieves position info
:param include_bots: Whether or not to include bots in the ranking
:return: Current Position, Previous Position
"""
current = self.position if include_bots else self.no_bot_position
previous = self.previous_position \
if include_bots else self.no_bot_previous_position
return current, previous
def get_tendency(self, include_bots: bool) -> str:
"""
Calculates the position tendency
:param include_bots: Whether or not to include bots
:return: The tendency as a string (example '+2')
"""
current, previous = self.get_position_info(include_bots)
tendency = previous - current
if tendency < 0:
return str(tendency)
elif tendency > 0:
return f"+{tendency}"
else:
return "-"
def get_tendency_class(self, include_bots: bool) -> str:
"""
Calculates the tendency and returns the corrsponding CSS class
:param include_bots: Whether or not to include bots
:return: The tendency CSS class name
"""
current, previous = self.get_position_info(include_bots)
if current < previous:
return "chevron-circle-up"
elif current > previous:
return "chevron-circle-down"
else:
return "minus-circle"
@classmethod
def load_history(cls, league: str, season: int, matchday: int) -> \
List[Tuple[User, List["LeaderboardEntry"]]]:
"""
Loads the history for the previous matches in a season for each user
:param league: The league for which to retrieve the history
:param season: The season for which to retrieve the history
:param matchday: The matchday for which to retrieve the history
:return: The history as a list of tuples of users and a list of
the corresponding LeaderboardEntry objects.
Sorted by current position
"""
entries: List[LeaderboardEntry] = [
x for x in LeaderboardEntry.query.filter_by(
league=league, season=season).options(
db.joinedload(LeaderboardEntry.user)).all()
if x.matchday <= matchday
]
entries.sort(key=lambda x: x.matchday)
history_dict: Dict[int, List[LeaderboardEntry]] = {}
for entry in entries:
if entry.user_id not in history_dict:
history_dict[entry.user_id] = []
history_dict[entry.user_id].append(entry)
history_list = [(history[-1].user, history)
for _, history in history_dict.items()]
history_list.sort(key=lambda x: x[1][-1].position)
return history_list
class ChatMessage(ModelMixin, db.Model):
"""
Model that describes the 'chat_messages' SQL table
"""
def __init__(self, *args, **kwargs):
"""
Initializes the Model
:param args: The constructor arguments
:param kwargs: The constructor keyword arguments
"""
super().__init__(*args, **kwargs)
__tablename__ = "chat_messages"
id: int = db.Column(db.Integer, primary_key=True, autoincrement=True)
user_id: int = db.Column(db.Integer,
db.ForeignKey("users.id"),
nullable=True)
parent_id: int = db.Column(db.Integer,
db.ForeignKey("chat_messages.id"),
nullable=True)
text: str = db.Column(db.String(255), nullable=False)
creation_time: float = db.Column(db.Float,
nullable=False,
default=time.time)
last_edit: float = db.Column(db.Float, nullable=False, default=time.time)
edited: bool = db.Column(db.Boolean, nullable=False, default=False)
deleted: bool = db.Column(db.Boolean, nullable=False, default=False)
user: Optional[User] = db.relationship("User",
backref=db.backref("chat_messages"))
parent: "ChatMessage" = db.relationship("ChatMessage",
back_populates="children",
remote_side=[id],
uselist=False)
children: List["ChatMessage"] = db.relationship("ChatMessage",
back_populates="parent",
uselist=True)
def get_text(self) -> Optional[str]:
"""
:return: The text of the chat message if the user still exists and the
message has not been deleted
"""
if self.deleted or self.user is None:
return None
else:
return self.text
def edit(self, new_text: str):
"""
Edits the message
:param new_text: The new message text
:return: None
"""
self.text = new_text
self.edited = True
self.last_edit = time.time()
def delete(self):
"""
Marks the message as deleted
:return: None
"""
self.text = ""
self.deleted = True
def __json__(self,
include_children: bool = False,
ignore_keys: Optional[List[str]] = None) -> Dict[str, Any]:
"""
Makes sure to include children and parents
:param include_children: Whether or not to include child objects
:param ignore_keys: Which keys to ignore
:return: The JSON data
"""
data = super().__json__(include_children, ignore_keys)
# TODO Add child and parent relations
return data
class ReminderSettings(ModelMixin, db.Model):
"""
Database model that keeps track of reminder settings
"""
def __init__(self, *args, **kwargs):
"""
Initializes the Model
:param args: The constructor arguments
:param kwargs: The constructor keyword arguments
"""
super().__init__(*args, **kwargs)
__tablename__ = "reminder_settings"
user_id: int = db.Column(
db.Integer,
db.ForeignKey("users.id"),
primary_key=True
)
reminder_type = db.Column(db.Enum(ReminderType), primary_key=True)
active = db.Column(db.Boolean, nullable=False, default=True)
reminder_time: int = db.Column(db.Integer, nullable=False, default=86400)
last_reminder: str = db.Column(db.String(19), nullable=False,
default="1970-01-01:01-01-01")
user: User = db.relationship(
"User",
backref=db.backref("reminder_settings", cascade="all, delete")
)
@property
def reminder_time_delta(self) -> timedelta:
"""
:return: The 'reminder_time' parameter as a datetime timedelta
"""
return timedelta(seconds=self.reminder_time)
@property
def last_reminder_datetime(self) -> datetime:
"""
:return: The 'last_reminder' parameter as a datetime object
"""
return datetime.strptime(self.last_reminder, "%Y-%m-%d:%H-%M-%S")
def set_reminder_time(self, reminder_time: int):
"""
Sets the reminder time and resets the time stored as the last reminder
:param reminder_time: the new reminder time
:return: None
"""
self.reminder_time = reminder_time
self.last_reminder = "1970-01-01:01-01-01"
db.session.commit()
def get_due_matches(self) -> List[Match]:
"""
Checks if the reminder is due and returns a list of matches that the
user still needs to bet on.
:return: The matches for which the reminder is due
"""
now = datetime.utcnow()
start = max(now, self.last_reminder_datetime)
start_str = start.strftime("%Y-%m-%d:%H-%M-%S")
then = now + self.reminder_time_delta
then_str = then.strftime("%Y-%m-%d:%H-%M-%S")
due_matches: List[Match] = [
x for x in Match.query.filter_by(
season=Config.season(),
league=Config.OPENLIGADB_LEAGUE
).all()
if start_str < x.kickoff < then_str
]
user_bet_matches = [
(bet.match.home_team_abbreviation,
bet.match.away_team_abbreviation,
bet.match.season)
for bet in Bet.query.filter_by(
user_id=self.user_id,
season=Config.season(),
league=Config.OPENLIGADB_LEAGUE
).options(db.joinedload(Bet.match)).all()
]
to_remind = []
for match in due_matches:
identifier = (match.home_team_abbreviation,
match.away_team_abbreviation,
match.season)
if identifier not in user_bet_matches:
to_remind.append(match)
return to_remind
def send_reminder(self):
"""
Sends a reminder message if it's due
:return: None
"""
due = self.get_due_matches()
if len(due) < 1:
return
else:
app.logger.debug("Sending reminder to {}.".format(self.user.email))
message = render_template(
"email/reminder.html",
user=self.user,
matches=due,
hours=int(self.reminder_time / 3600)
)
self.send_reminder_message(message)
last_match = max(due, key=lambda x: x.kickoff)
self.last_reminder = last_match.kickoff
db.session.commit()
def send_reminder_message(self, message: str):
"""
Sends a reminder message using the appropriate method of delivery
:param message: The message to send
:return: None
"""
if self.reminder_type == ReminderType.EMAIL:
try:
send_email(
self.user.email,
"Tippspiel Erinnerung",
message,
Config.SMTP_HOST,
Config.SMTP_ADDRESS,
Config.SMTP_PASSWORD,
Config.SMTP_PORT
)
except SMTPAuthenticationError:
app.logger.error("Invalid SMTP settings, failed to send email")
elif self.reminder_type == ReminderType.TELEGRAM:
telegram = TelegramChatId.query.filter_by(user=self.user).first()
if telegram is not None:
message = BeautifulSoup(message, "html.parser").text
message = "\n".join([x.strip() for x in message.split("\n")])
telegram.send_message(message)
class MediaUserState(ModelMixin, db.Model):
"""
Database model that keeps track of a user's entries on external services
for a media item
"""
__tablename__ = "media_user_states"
"""
The name of the database table
"""
__table_args__ = (db.UniqueConstraint("media_id_id",
"user_id",
name="unique_media_user_state"), )
"""
Makes sure that objects that should be unique are unique
"""
def __init__(self, *args, **kwargs):
"""
Initializes the Model
:param args: The constructor arguments
:param kwargs: The constructor keyword arguments
"""
super().__init__(*args, **kwargs)
media_id_id: int = db.Column(db.Integer,
db.ForeignKey("media_ids.id"),
nullable=False)
"""
The ID of the media ID referenced by this user state
"""
media_id: MediaId = db.relationship("MediaId",
back_populates="media_user_states")
"""
The media ID referenced by this user state
"""
user_id: int = db.Column(db.Integer,
db.ForeignKey("users.id",
ondelete="CASCADE",
onupdate="CASCADE"),
nullable=False)
"""
The ID of the user associated with this user state
"""
user: User = db.relationship("User",
backref=db.backref("media_user_states",
lazy=True,
cascade="all,delete"))
"""
The user associated with this user state
"""
progress: Optional[int] = db.Column(db.Integer, nullable=True)
"""
The user's current progress consuming the media item
"""
volume_progress: Optional[int] = db.Column(db.Integer, nullable=True)
"""
The user's current 'volume' progress.
"""
score: Optional[int] = db.Column(db.Integer, nullable=True)
"""
The user's score for the references media item
"""
consuming_state: ConsumingState \
= db.Column(db.Enum(ConsumingState), nullable=False)
"""
The current consuming state of the user for this media item
"""
media_notification: Optional["MediaNotification"] = db.relationship(
"MediaNotification",
uselist=False,
back_populates="media_user_state",
cascade="all, delete")
"""
Notification object for this user state
"""
@property
def identifier_tuple(self) -> Tuple[int, int]:
"""
:return: A tuple that uniquely identifies this database entry
"""
return self.media_id_id, self.user_id
def update(self, new_data: "MediaUserState"):
"""
Updates the data in this record based on another object
:param new_data: The object from which to use the new values
:return: None
"""
self.media_id_id = new_data.media_id_id
self.user_id = new_data.user_id
self.progress = new_data.progress
self.volume_progress = new_data.volume_progress
self.score = new_data.score
self.consuming_state = new_data.consuming_state
class ServiceUsername(ModelMixin, db.Model):
"""
Database model that stores an external service username for a user
"""
__tablename__ = "service_usernames"
"""
The name of the database table
"""
__table_args__ = (db.UniqueConstraint("user_id",
"username",
"service",
name="unique_service_username"), )
"""
Makes sure that objects that should be unique are unique
"""
def __init__(self, *args, **kwargs):
"""
Initializes the Model
:param args: The constructor arguments
:param kwargs: The constructor keyword arguments
"""
super().__init__(*args, **kwargs)
user_id: int = db.Column(db.Integer,
db.ForeignKey("users.id",
ondelete="CASCADE",
onupdate="CASCADE"),
nullable=False)
"""
The ID of the user associated with this service username
"""
user: User = db.relationship("User",
backref=db.backref("service_usernames",
lazy=True,
cascade="all,delete"))
"""
The user associated with this service username
"""
username: str = db.Column(db.String(255), nullable=False)
"""
The service username
"""
service: ListService = db.Column(db.Enum(ListService), nullable=False)
"""
The external service this item is a username for
"""
@property
def identifier_tuple(self) -> Tuple[int, str, ListService]:
"""
:return: A tuple that uniquely identifies this database entry
"""
return self.user_id, self.username, self.service
def update(self, new_data: "ServiceUsername"):
"""
Updates the data in this record based on another object
:param new_data: The object from which to use the new values
:return: None
"""
self.user_id = new_data.user_id
self.username = new_data.username
self.service = new_data.service
class MediaList(ModelMixin, db.Model):
"""
Database model for user-specific media lists.
"""
__tablename__ = "media_lists"
"""
The name of the database table
"""
__table_args__ = (
db.UniqueConstraint(
"name",
"user_id",
"service",
"media_type",
name="unique_media_list"
),
)
"""
Makes sure that objects that should be unique are unique
"""
def __init__(self, *args, **kwargs):
"""
Initializes the Model
:param args: The constructor arguments
:param kwargs: The constructor keyword arguments
"""
super().__init__(*args, **kwargs)
user_id: int = db.Column(
db.Integer,
db.ForeignKey(
"users.id", ondelete="CASCADE", onupdate="CASCADE"
),
nullable=False
)
"""
The ID of the user associated with this list
"""
user: User = db.relationship(
"User",
backref=db.backref("media_lists", lazy=True, cascade="all,delete")
)
"""
The user associated with this list
"""
name: str = db.Column(db.Unicode(255), nullable=False)
"""
The name of this list
"""
service: ListService = db.Column(db.Enum(ListService), nullable=False)
"""
The service for which this list applies to
"""
media_type: MediaType = db.Column(db.Enum(MediaType), nullable=False)
"""
The media type for this list
"""
media_list_items: List["MediaListItem"] = db.relationship(
"MediaListItem", back_populates="media_list", cascade="all, delete"
)
"""
Media List Items that are a part of this media list
"""
@property
def identifier_tuple(self) -> Tuple[str, int, ListService, MediaType]:
"""
:return: A tuple that uniquely identifies this database entry
"""
return self.name, self.user_id, self.service, self.media_type
def update(self, new_data: "MediaList"):
"""
Updates the data in this record based on another object
:param new_data: The object from which to use the new values
:return: None
"""
self.user_id = new_data.user_id
self.name = new_data.name
self.service = new_data.service
self.media_type = new_data.media_type
