analysis.py

import datetime
import random

from database import db

# The database has a table "visits" with the following columns:
# visit_id INTEGER PRIMARY KEY
# timestamp TEXT NOT NULL
# nb_guests INTEGER NOT NULL
# pseudo TEXT NOT NULL
# action TEXT NOT NULL
# time_taken_seconds INTEGER NOT NULL
# It represents each visit in the island, nb_guests being the amount of guests that were there at the time of the visit.
# action is the destination of the guest, and time_taken_seconds is the time spent on the island by the guest.

# The database also has a table "chat" with the following columns:
# chat_id INTEGER PRIMARY KEY
# timestamp TEXT NOT NULL
# rank TEXT NOT NULL
# pseudo TEXT NOT NULL
# message TEXT NOT NULL
# It represents each message sent in the chat.

def median_time_spent():
    time = db.get("SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY time_taken_seconds) FROM visits", one=True)
    return time[0]

def visits_by_time(limit=120):
    query = f"""--sql
    with rounded_time as (SELECT round(time_taken_seconds) as rounded_s,
                      count(round(time_taken_seconds)) as nb_visits
                      from visits

                      where time_taken_seconds < {limit}
                      group by round(time_taken_seconds)
                      )

    SELECT
        rounded_s, nb_visits, --sum(nb_visits) over () as total_visits,
        ROUND(nb_visits * 100 / sum(nb_visits) over (), 2) as percent_visits
    from rounded_time
    order by rounded_s asc
    """
    return db.get(query)

def most_visited_guests(limit=1):
    return db.get(f"SELECT pseudo, COUNT(pseudo) FROM visits GROUP BY pseudo ORDER BY COUNT(pseudo) DESC LIMIT {limit}")
  
def visits_per_destination():
    query = """--sql
    with percentage_visits as (
    SELECT action, COUNT(action) AS count, COUNT(action)::float * 100 / (SELECT COUNT(*) FROM visits)::float
    AS percent FROM visits GROUP BY action ORDER BY COUNT(action) DESC)
    select action, count, ROUND(percent::numeric, 2) from percentage_visits
    """
    return db.get(query)

def top_n_guests_chat(limit=5):
    return db.get(f"SELECT pseudo, COUNT(pseudo) FROM chat GROUP BY pseudo ORDER BY COUNT(pseudo) DESC LIMIT {limit}")

# ================================

def activity_periods(return_last=False):
    query = """--sql
    with windows_times as (SELECT timestamp,
                                  lag(timestamp) over (order by timestamp asc ) as prior_timestamp

                          from visits
                          order by timestamp asc), time_delta as (
    select timestamp, extract(epoch from timestamp) - extract(epoch from prior_timestamp) as delta

    from windows_times), time_status as(
    select timestamp, delta,
          case when delta > 60 * 5 or timestamp = '2023-02-17 04:47:40.000000' then 'BEGINNING'
                WHEN lead(delta) over () > 60 * 5 then 'ENDING'
              else NULL
            end as status
    from time_delta order by timestamp), flat_windows as (
    select timestamp as start_period, lead(timestamp) over () as end_period, status from time_status where status is not NULL order by timestamp)
    select start_period, end_period from flat_windows where status = 'BEGINNING';
    """
    timestamps = db.get(query)
    if return_last:
      # Fetch the last timestamp ever recorded in visits
      last = db.get("SELECT timestamp FROM visits ORDER BY timestamp DESC LIMIT 1", one=True)
      timestamps[-1] = (timestamps[-1][0], last[0])
      
    return timestamps

# ================================

def median_time_per_destination():
    query = """--sql
    SELECT action, AVG(time_taken_seconds) as median_time
    FROM (
    SELECT action, time_taken_seconds,
        ROW_NUMBER() OVER (
        PARTITION BY action
        ORDER BY time_taken_seconds
        ) AS row_num,
        COUNT(*) OVER (PARTITION BY action) AS group_size
    FROM visits
    WHERE action != 'Spawn'
    ) t
    WHERE row_num IN (FLOOR((group_size + 1) / 2), CEIL((group_size + 1) / 2))
    GROUP BY action
    ORDER BY median_time DESC
    """
    return db.get(query)
  
def cake_soul_mentions(limit=10):
    query = f"""--sql
    SELECT message, pseudo, rank
    FROM chat
    WHERE LOWER(message) LIKE '%cake soul%'
    ORDER BY RANDOM()
    LIMIT {limit}
    """
    return db.get(query)

def visits_per_rank():
    query = """--sql
    SELECT rank, COUNT(*) as num_visits,
    ROUND((COUNT(*) * 100.0) / SUM(COUNT(*)) OVER(), 2) as percentage_visits
    FROM visits
    WHERE rank != 'UNDEFINED'
    GROUP BY rank
    ORDER BY percentage_visits DESC
    """
    return db.get(query)

def average_level_per_destination():
    query = """--sql
    SELECT action, ROUND(AVG(level)) as average_level
    FROM visits
    WHERE level != -1 AND action != 'Spawn'
    GROUP BY action
    ORDER BY average_level ASC
    """
    return db.get(query)

def average_level_per_rank():
    query = """--sql
    SELECT rank, ROUND(AVG(level)) as average_level
    FROM visits
    WHERE level != -1
    GROUP BY rank
    ORDER BY average_level ASC
    """
    return db.get(query)

def best_visitor_per_destination():
    query = """--sql 
    WITH top_visitor as (SELECT DISTINCT ON (action) action, pseudo, COUNT(*) as visits
    FROM visits
    GROUP BY action, pseudo
    ORDER BY action, visits DESC)
    SELECT pseudo, action, visits FROM top_visitor
    ORDER BY visits DESC
    """
    return db.get(query)

def most_visited_rank_per_destination():
    query = """--sql
    SELECT v.action, v.rank
    FROM (SELECT action, MAX(count) AS max_count
        FROM (SELECT action, rank, COUNT(*) AS count
                FROM visits
                WHERE action != 'Spawn' AND rank != 'UNDEFINED'
                GROUP BY action, rank) AS grouped_visits
        GROUP BY action) AS max_counts
    JOIN (SELECT action, rank, COUNT(*) AS count
        FROM visits
        WHERE action != 'Spawn' AND rank != 'UNDEFINED'
        GROUP BY action, rank) AS v
    ON v.action = max_counts.action AND v.count = max_counts.max_count
    """
    return db.get(query)

def median_time_per_rank():
    query = """--sql
    SELECT rank,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY time_taken_seconds) as median_time_taken_seconds
    FROM visits
    WHERE rank != 'UNDEFINED' AND pseudo != 'PortalHub' AND action != 'Spawn'
    GROUP BY rank
    ORDER BY median_time_taken_seconds ASC
    """
    return db.get(query)

def highest_level_per_rank():
    query = """--sql
    SELECT DISTINCT v1.rank, v1.pseudo, v1.level AS highest_level
    FROM visits v1
    INNER JOIN (
        SELECT rank, MAX(level) AS level
        FROM visits
        WHERE level != -1
        GROUP BY rank
    ) v2 ON v1.rank = v2.rank AND v1.level = v2.level
    WHERE v1.level != -1 ORDER BY highest_level DESC
    """
    return db.get(query)

def shortest_pseudo_that_visited():
    return db.get("SELECT pseudo FROM visits ORDER BY LENGTH(pseudo) ASC LIMIT 1")[0]
  
def party_friend_trade_count():
    query = """--sql
    SELECT 'Friend request' AS type, COUNT(*) AS count
    FROM other
    WHERE message LIKE '%Friend request from%'
    UNION ALL
    SELECT 'Party invite' AS type, COUNT(*) AS count
    FROM other
    WHERE message LIKE '%has invited you to join their party!%'
    UNION ALL
    SELECT 'Trade request' AS type, COUNT(*) AS count
    FROM other
    WHERE message LIKE '%sent you a trade request%'
    """
    return db.get(query)

def frequency_rank_chat_speakers():
    query = """--sql
    SELECT rank,
        COUNT(*) as count,
        (COUNT(*) * 100 /
            (SELECT COUNT(*)
            FROM chat
            WHERE rank NOT IN ('UNDEFINED', 'SPECIAL', '[YOUTUBE]', 'MVP++')
            )
        ) as percentage
    FROM chat
    WHERE rank NOT IN ('UNDEFINED', 'SPECIAL', '[YOUTUBE]', 'MVP++')
    GROUP BY rank
    ORDER BY percentage DESC
    """
    return db.get(query)
    
def special_guests():
    return db.get("SELECT DISTINCT pseudo, rank FROM visits WHERE rank NOT IN ('NON', 'VIP', 'MVP', 'MVP++', 'UNDEFINED') AND pseudo != 'PortalHub'")
  
def elapsed_days_visits():
    query = """--sql
        SELECT
            (MAX(timestamp) - MIN(timestamp))::interval DAY AS elapsed_days
        FROM visits;
    """
    result = db.get(query, one=True)
    return result[0].days

def elapsed_days_exp():
    query = """--sql
        SELECT
            (MAX(timestamp) - MIN(timestamp))::interval DAY AS elapsed_days
        FROM exp;
    """
    result = db.get(query, one=True)
    return result[0].days
    
def average_exp_per():
    days = elapsed_days_exp()
    
    query = """--sql
    SELECT MIN(timestamp) AS min_timestamp, MAX(exp) - MIN(EXP) 
    AS total_exp 
    FROM exp
    """
    
    data = db.get(query)[0]
    exp_gained = data[1]
    min_time = data[0]
    
    data = {
        "since": min_time,
        "total": exp_gained,
        "seconds": exp_gained / days / 24 / 60 / 60,
        "minutes": exp_gained / days / 24 / 60,
        "hours": exp_gained / days / 24,
        "days": exp_gained / days,
        "weeks": exp_gained / days * 7,
        "months": exp_gained / days * 30,
        "years": exp_gained / days * 365
    }
    
    for key in data:
        if key not in ["since", "total"]:
            data[key] = round(data[key], 2)
        
    return data

def average_visits_per():
    days = elapsed_days_visits()
    
    query = """--sql
    SELECT MIN(timestamp) AS min_timestamp, COUNT(*) 
    AS total_visits 
    FROM visits
    """
    
    data = db.get(query)[0]
    visits = data[1]
    min_time = data[0]
    
    data = {
        "since": min_time,
        "total": visits,
        "seconds": visits / days / 24 / 60 / 60,
        "minutes": visits / days / 24 / 60,
        "hours": visits / days / 24,
        "days": visits / days,
        "weeks": visits / days * 7,
        "months": visits / days * 30,
        "years": visits / days * 365
    }
    
    for key in data:
        if key not in ["since", "total"]:
            data[key] = round(data[key], 2)
        
    return data

def average_chat_per():
    days = elapsed_days_visits()
    
    query = """--sql
    SELECT MIN(timestamp) AS min_timestamp, COUNT(*) 
    AS total_chat 
    FROM chat
    """
    
    data = db.get(query)[0]
    chat = data[1]
    min_time = data[0]
    
    data = {
        "since": min_time,
        "total": chat,
        "seconds": chat / days / 24 / 60 / 60,
        "minutes": chat / days / 24 / 60,
        "hours": chat / days / 24,
        "days": chat / days,
        "weeks": chat / days * 7,
        "months": chat / days * 30,
        "years": chat / days * 365
    }
    
    for key in data:
        if key not in ["since", "total"]:
            data[key] = round(data[key], 2)
        
    return data
    
def get_data():
    
  data = {
    "median_time_spent": median_time_spent(),
    "median_time_per_rank": median_time_per_rank(),
    "visits_by_time": visits_by_time(limit=10),
    "most_visited_guests": most_visited_guests(limit=10),
    "visits_per_destination": visits_per_destination(),
    "top_n_guests_chat": top_n_guests_chat(limit=10),
    "average_exp_per": average_exp_per(),
    "average_visits_per": average_visits_per(),
    "special_guests": special_guests(),
    "frequency_rank_chat_speakers": frequency_rank_chat_speakers(),
    "party_friend_trade_count": party_friend_trade_count(),
    "shortest_pseudo_that_visited": shortest_pseudo_that_visited(),
    "highest_level_per_rank": highest_level_per_rank(),
    "average_chat_per": average_chat_per(),
    "most_visited_rank_per_destination": most_visited_rank_per_destination(),
    "best_visitor_per_destination": best_visitor_per_destination(),
    "average_level_per_destination": average_level_per_destination(),
    "average_level_per_rank": average_level_per_rank(),
    "visits_per_rank": visits_per_rank(),
    "cake_soul_mentions": cake_soul_mentions(limit=10),
    "median_time_per_destination": median_time_per_destination(),
  }
  
  return data