def get_users():
    data_connection = sqlite3.connect(app.config['DATASTORE_PATH'])
    data_connection.row_factory = sqlite3.Row

    cursor = data_connection.cursor()

    statement = 'SELECT * FROM users'
    cursor.execute(statement)

    data = cursor.fetchall()

    return datautil.get_named_entities(data)
def get_snapshots(name):
    data_connection = sqlite3.connect(app.config['DATASTORE_PATH'])
    data_connection.row_factory = sqlite3.Row

    cursor = data_connection.cursor()

    statement = 'SELECT * FROM user_snapshots WHERE user_id = (SELECT id FROM users WHERE screen_name COLLATE NOCASE = ?)'
    cursor.execute(statement, (name, ))

    data = cursor.fetchall()

    return datautil.get_named_entities(data)
def get_tweet_snapshots(id):
    data_connection = sqlite3.connect(app.config['DATASTORE_PATH'])
    data_connection.row_factory = sqlite3.Row

    cursor = data_connection.cursor()

    statement = f"""SELECT * FROM tweet_snapshots 
                        WHERE id= ? 
                        ORDER BY time_of_capture ASC"""
    cursor.execute(statement, (id, ))

    data = cursor.fetchall()

    return datautil.get_named_entities(data)
def get_monthly_tweets(name):
    data_connection = sqlite3.connect(app.config['DATASTORE_PATH'])
    data_connection.row_factory = sqlite3.Row

    cursor = data_connection.cursor()

    statement = f"""SELECT count(*) total_tweets, strftime("%m-%Y", time_of_tweet) aggregatedate  FROM (
                        SELECT time_of_tweet FROM tweets 
                        WHERE author_id=(SELECT id FROM users WHERE screen_name COLLATE NOCASE = ?)
                            AND text NOT LIKE 'RT @%'
                        ORDER BY time_of_tweet DESC)
                    GROUP BY aggregatedate"""
    cursor.execute(statement, (name, ))

    data = cursor.fetchall()

    return datautil.get_named_entities(data)
def get_like_volume(name):
    data_connection = sqlite3.connect(app.config['DATASTORE_PATH'])
    data_connection.row_factory = sqlite3.Row

    cursor = data_connection.cursor()

    statement = f"""SELECT sum(likes) likes, case cast (strftime('%w', local_time) as integer)
                    when 0 then 'Sunday'
                    when 1 then 'Monday'
                    when 2 then 'Tuesday'
                    when 3 then 'Wednesday'
                    when 4 then 'Thursday'
                    when 5 then 'Friday'
                    else 'Saturday' end as day_of_week, strftime('%H:00', local_time) tweet_hour FROM (
                        SELECT time_of_tweet, likes, datetime(time_of_tweet, 'localtime') local_time FROM tweets 
                        WHERE author_id= (SELECT id FROM users WHERE screen_name COLLATE NOCASE = ?)
                            AND text NOT LIKE 'RT @%'
                        ORDER BY likes DESC)
                    GROUP BY day_of_week, tweet_hour"""
    cursor.execute(statement, (name, ))

    data = cursor.fetchall()

    return datautil.get_named_entities(data)