class ActivityReportRow(db.Model):
__tablename__ = 'activity_report_rows'
id = db.Column(db.Integer, primary_key=True)
datetime_utc = db.Column(db.DateTime, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
slack_conversation_read_count = db.Column(db.Integer, nullable=True)
slack_user_event_count = db.Column(db.Integer, nullable=True)
google_calendar_event_id = db.Column(db.Integer, db.ForeignKey('google_calendar_events.id', \
ondelete='SET NULL'), nullable=True)
google_calendar_event_count = db.Column(db.Integer, nullable=True)
is_workday_time = db.Column(db.Boolean, nullable=True, default=False)
focused_work_period_start_utc = db.Column(db.DateTime, nullable=True)
is_focus_time = db.Column(db.Boolean, nullable=True)
is_refocus_time = db.Column(db.Boolean, nullable=True)
is_collaborative_time = db.Column(db.Boolean, nullable=True)
__table_args__ = ( \
Index('activity_report_unique_user_datetime', 'user_id', \
'datetime_utc', unique=True),)
def __repr__(self):
return f'ActivityReportRow({self.id}, {self.datetime_utc}, {self.user_id})'
def to_json(self):
props = self.__dict__
props.pop('_sa_instance_state')
props['datetime_utc'] = props['datetime_utc'].isoformat()
return props
class SlackUser(db.Model, EnhancedDBModel):
__tablename__ = 'slack_users'
id = db.Column(db.Integer, primary_key=True)
slack_user_api_id = db.Column(db.String(100), nullable=False)
slack_team_id = db.Column(db.Integer, db.ForeignKey('slack_teams.id'), nullable=False)
slack_email_address = db.Column(db.String(300))
first_name = db.Column(db.String(100))
last_name = db.Column(db.String(100))
slack_username = db.Column(db.String(100), nullable=False)
is_authenticated = db.Column(db.Boolean, nullable=False, default=False)
authentication_oauth_access_token = db.Column(db.String(200))
is_deleted_on_slack = db.Column(db.Boolean, nullable=False, default=False)
created_date = db.Column(db.DateTime, nullable=False)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False)
slack_timezone_label = db.Column(db.String(100))
slack_timezone_offset = db.Column(db.Integer)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
def slack_team(self):
return SlackTeam.query.filter(SlackTeam.id == self.slack_team_id).one()
def __repr__(self):
return 'SlackUser(%s, %s, %s)' % (self.first_name + self.last_name, self.id, self.slack_user_api_id)
def slack_client(self):
if self.is_authenticated:
return slack.WebClient(self.authentication_oauth_access_token)
else:
return None
class SlackConversationRead(db.Model):
__tablename__ = 'slack_conversation_reads'
id = db.Column(db.Integer, primary_key=True)
slack_conversation_query_id = db.Column(db.Integer, db.ForeignKey('slack_conversation_queries.id'), nullable=False, unique=True)
slack_user_id = db.Column(db.Integer, db.ForeignKey('slack_users.id'), nullable=False)
slack_conversation_id = db.Column(db.Integer, db.ForeignKey('slack_conversations.id'), nullable=False)
period_start_datetime = db.Column(db.DateTime, nullable=False)
period_end_datetime = db.Column(db.DateTime, nullable=False)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False)
class SlackConversationQuery(db.Model):
__tablename__ = 'slack_conversation_queries'
id = db.Column(db.Integer, primary_key=True)
slack_user_id = db.Column(db.Integer, db.ForeignKey('slack_users.id'), nullable=False)
slack_conversation_id = db.Column(db.Integer, db.ForeignKey('slack_conversations.id'), nullable=False)
query_datetime = db.Column(db.DateTime, nullable=False)
last_read_datetime = db.Column(db.DateTime, nullable=False)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False)
slack_conversation_query_run_id = db.Column(db.Integer, db.ForeignKey('slack_conversation_query_runs.id'), nullable=False)
class SlackUserEvent(db.Model, EnhancedDBModel):
__tablename__ = 'slack_user_events'
id = db.Column(db.Integer, primary_key=True)
slack_user_id = db.Column(db.Integer, db.ForeignKey('slack_users.id'), nullable=False)
raw_slack_event_id = db.Column(db.Integer, db.ForeignKey('raw_slack_events.id')) # may be null if polled from Slack API
slack_event_api_id = db.Column(db.String(100), nullable=False)
event_datetime = db.Column(db.DateTime, nullable=False)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False)
slack_event_type = db.Column(db.String(100), nullable=False)
slack_event_subtype = db.Column(db.String(100))
def __repr__(self):
return "SlackUserEvent(id: %s, slack_user_id: %s, slack_event_api_id: %s, slack_event_type: %s, slack_event_subtype: %s)" % \
(self.id, self.slack_user_id, self.slack_event_api_id, self.slack_event_type, self.slack_event_subtype or '')
class GoogleCalendarEvent(db.Model, EnhancedDBModel):
__tablename__ = 'google_calendar_events'
id = db.Column(db.Integer, primary_key=True)
google_id = db.Column(db.String(200), nullable=False)
ical_uid = db.Column(db.String(200), nullable=False)
start_time = db.Column(db.DateTime, nullable=False)
end_time = db.Column(db.DateTime, nullable=False)
summary = db.Column(db.String(300), nullable=False)
description = db.Column(db.Text)
organizer_email = db.Column(db.String(200), nullable=False)
organizer_self = db.Column(db.Boolean, default=False)
json_data = db.Column(db.JSON)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow)
google_calendar_user_id = db.Column(db.Integer, db.ForeignKey('google_calendar_users.id'))
FOCUS_TIME_EVENT_SUMMARY_NAME = 'Focus Time'
@hybrid_property
def marked_busy(self):
if (not self.json_data): return False
json_data = json.loads(self.json_data) if type(self.json_data) == str else self.json_data
if bool(json_data.get('transparency')) & (json_data.get('transparency') == 'transparent'):
return False
return True
@hybrid_property
def is_focus_time_event(self):
return self.summary == self.FOCUS_TIME_EVENT_SUMMARY_NAME
def __repr__(self):
return "GoogleCalendarEvent('%s','%s','%s','%s')" % (self.organizer_email, self.start_time, self.end_time, self.google_calendar_user_id)
class SlackConversation(db.Model, EnhancedDBModel):
__tablename__ = 'slack_conversations'
id = db.Column(db.Integer, primary_key=True)
slack_conversation_api_id = db.Column(db.String(100), nullable=False)
slack_team_id = db.Column(db.Integer, db.ForeignKey('slack_teams.id'), nullable=False)
conversation_type = db.Column(db.String(100), nullable=False) # 'im', 'mpim', 'channel', 'private_channel'
conversation_name = db.Column(db.String(100)) # can be null for IM conversation
is_deleted = db.Column(db.Boolean, nullable=False, default=False)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=False)
class Post(db.Model):
__tablename__ = 'posts' # follows general table name paradigm in database (plural)
id = db.Column(db.Integer, primary_key=True)
title = db.Column(db.String(100), nullable=False)
date_posted = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
content = db.Column(db.Text, nullable=False)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'), nullable=False)
last_updated = db.Column(db.DateTime, onupdate=datetime.utcnow, default=datetime.utcnow)
def __repr__(self):
return "Post('%s', '%s')" % (self.title, self.date_posted)
def date_to_string(self):
return self.date_posted.strftime("%a %b %d %Y %l:%M%p")
class GitHubUser(db.Model, EnhancedDBModel):
__tablename__ = 'github_users'
id = db.Column(db.Integer, primary_key=True)
github_api_user_id = db.Column(db.Integer, nullable=False)
github_oauth_access_token = db.Column(db.String(200))
github_email_address = db.Column(db.String(300))
github_username = db.Column(db.String(100), nullable=True)
is_authenticated = db.Column(db.Boolean, nullable=False, default=False)
is_deleted_on_github = db.Column(db.Boolean, nullable=False, default=False)
created_at = db.Column(db.DateTime, nullable=True)
updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow, nullable=True)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
def __repr__(self):
return 'GitHubUser(%s, %s, %s)' % (self.github_username, self.id, self.user_id)
class GoogleCalendarUser(db.Model, EnhancedDBModel):
__tablename__ = 'google_calendar_users'
id = db.Column(db.Integer, primary_key=True)
google_email = db.Column(db.String(300), nullable=False)
auth_token = db.Column(db.String(300), nullable=False)
refresh_token = db.Column(db.String(300))
scopes = db.Column(db.String(300), nullable=False)
primary_timeZone = db.Column(db.String(300), nullable=False)
primary_etag = db.Column(db.String(300), nullable=False)
primary_color_id = db.Column(db.Integer, nullable=False)
created_at = db.Column(db.DateTime, nullable=False, default=datetime.utcnow)
updated_at = db.Column(db.DateTime, onupdate=datetime.utcnow)
user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
google_calendar_events = db.relationship('GoogleCalendarEvent', backref='google_calendar_user', lazy=True)
def __repr__(self):
return "GoogleCalendarUser('%s','%s','%s')" % (self.google_email, self.primary_timeZone, self.created_at)
class SlackConversationQueryRun(db.Model, EnhancedDBModel):
__tablename__ = 'slack_conversation_query_runs'
id = db.Column(db.Integer, primary_key=True)
query_start_time = db.Column(db.DateTime, nullable=False)
query_end_time = db.Column(db.DateTime)
slack_user_id = db.Column(db.Integer, db.ForeignKey('slack_users.id'), nullable=False)
