seperate route logic from actual flask route

1 files changed, 171 insertions, 0 deletions

diff --git a/routes.py b/routes.py
new file mode 100644
index 0000000..06c68e9
--- /dev/null
+++ b/routes.py
@@ -0,0 +1,171 @@
+import datetime
+import pandas
+from sklearn.linear_model import Ridge
+import numpy as np
+import os
+from member_colors import member_groups
+
+# Pure logic for creating a database connection
+def create_database_connection():
+    from sql.pg_handler import PostgresHandler
+    hostname = os.environ.get("POSTGRES_HOST")
+    user = os.environ.get("POSTGRES_USER")
+    password = os.environ.get("POSTGRES_PASSWORD")
+    database = os.environ.get("POSTGRES_DATABASE")
+    return PostgresHandler(host_name=hostname, username=user, password=password, database=database, port=5432)
+
+def get_subscribers_data():
+    server = create_database_connection()
+    query = 'SELECT sd.*, h.* FROM subscriber_data sd INNER JOIN "24h_historical" h ON sd.channel_id = h.channel_id ORDER BY sd.subscriber_count DESC'
+    data = server.execute_query(query)
+    channel_data_list = [{
+        "channel_name": row[3],
+        "profile_pic": row[2],
+        "subscribers": row[4],
+        "sub_org": row[5],
+        "video_count": row[6],
+        "views": row[8],
+        "day_diff": int(row[4] - int(row[11]))
+    } for row in data]
+    return {"timestamp": datetime.datetime.now(), "channel_data": channel_data_list}
+
+def get_twitch_data():
+    server = create_database_connection()
+    query = '''
+        SELECT sd.*, h.*, ts.follower_count
+        FROM subscriber_data sd
+        INNER JOIN "24h_historical" h ON sd.channel_id = h.channel_id
+        LEFT JOIN twitch_stats ts ON sd.channel_id = ts.channel_id
+        ORDER BY sd.subscriber_count DESC
+    '''
+    data = server.execute_query(query)
+    channel_data_list = []
+    for row in data:
+        youtube_subs = row[4]
+        twitch_followers = row[-1] if row[-1] is not None else 0
+        total_followers = youtube_subs + twitch_followers
+        channel_data_list.append({
+            "channel_name": row[3],
+            "profile_pic": row[2],
+            "subscribers": youtube_subs,
+            "sub_org": row[5],
+            "twitch_followers": twitch_followers,
+            "total_sum": total_followers,
+        })
+    return {"timestamp": datetime.datetime.now(), "channel_data": channel_data_list}
+
+def get_channel_timeseries(channel_name):
+    server = create_database_connection()
+    query = "SELECT * FROM subscriber_data_historical WHERE name = %s AND timestamp > %s ORDER BY TO_CHAR(timestamp, 'YYYY-MM-DD')"
+    data = server.execute_query(query, (channel_name, os.environ.get("START_DATE"),))
+    labels = []
+    data_points = []
+    seen_dates = set()
+    for row in data:
+        date_string = row[5].strftime("%Y-%m-%d")
+        if date_string in seen_dates:
+            continue
+        labels.append(date_string)
+        data_points.append(row[4])
+        seen_dates.add(date_string)
+    return {"labels": labels, "datasets": data_points}
+
+def get_channel_7d(channel_name):
+    server = create_database_connection()
+    query = "SELECT * FROM subscriber_data_historical WHERE name = %s ORDER BY TO_CHAR(timestamp, 'YYYY-MM-DD')"
+    data = server.execute_query(query, (channel_name,))
+    labels = []
+    data_points = []
+    seen_dates = set()
+    for row in data:
+        date_string = row[5].strftime("%Y-%m-%d")
+        if date_string in seen_dates:
+            continue
+        labels.append(date_string)
+        data_points.append(row[4])
+        seen_dates.add(date_string)
+    return {"labels": labels[-7:], "datasets": data_points[-7:]}
+
+def get_channel_milestones(channel_name, milestone_increment=10000):
+    server = create_database_connection()
+    current_milestone = 10000
+    query = """
+    SELECT subscriber_count, MIN(timestamp)
+    FROM subscriber_data_historical
+    WHERE name = %s
+    GROUP BY subscriber_count
+    ORDER BY subscriber_count ASC
+    """
+    data = server.execute_query(query, (channel_name,))
+    dates = []
+    milestones = []
+    for row in data:
+        subscriber_count = row[0]
+        while subscriber_count >= current_milestone:
+            dates.append(row[1].strftime("%Y-%m-%d"))
+            milestones.append(current_milestone)
+            current_milestone += milestone_increment
+    return {"milestones": milestones, "dates": dates}
+
+def get_channel_diffs(channel_name):
+    server = create_database_connection()
+    query = "SELECT * FROM subscriber_data_historical WHERE name = %s ORDER BY timestamp DESC"
+    data = server.execute_query(query, (channel_name,))
+    if not data:
+        return {"diff_1d": None, "diff_7d": None, "diff_30d": None}
+    now = datetime.datetime.now()
+    latest = data[0][4]
+    sub_1d = next((r[4] for r in data if (now - r[5]).days >= 1), None)
+    sub_7d = next((r[4] for r in data if (now - r[5]).days >= 7), None)
+    sub_30d = next((r[4] for r in data if (now - r[5]).days >= 30), None)
+    return {
+        "diff_1d": latest - sub_1d if sub_1d is not None else None,
+        "diff_7d": latest - sub_7d if sub_7d is not None else None,
+        "diff_30d": latest - sub_30d if sub_30d is not None else None,
+    }
+
+def get_channel_info(channel_name):
+    def find_next_milestone(sc):
+        return ((sc // 10000) + 1) * 10000 if sc < 100000 else ((sc // 100000) + 1) * 100000 if sc < 1000000 else ((sc // 1000000) + 1) * 1000000
+    server = create_database_connection()
+    data = server.execute_query("SELECT * FROM subscriber_data WHERE name = %s", (channel_name,))[0]
+    historical = server.execute_query("SELECT * FROM subscriber_data_historical WHERE name = %s ORDER BY timestamp DESC", (channel_name,))
+    current = historical[0][4]
+    result = {
+        "channel_id": data[1], "channel_name": data[3], "profile_pic": data[2], "subscribers": data[4],
+        "sub_org": data[5], "video_count": data[6], "view_count": data[8],
+        "diff_1d": None, "diff_7d": None, "diff_30d": None
+    }
+    now = datetime.datetime.now()
+    for days, key in [(1, 'diff_1d'), (7, 'diff_7d'), (30, 'diff_30d')]:
+        past_val = next((r[4] for r in historical if (now - r[5]).days >= days), None)
+        result[key] = current - past_val if past_val is not None else None
+    subs, dates, seen = [], [], set()
+    for r in historical:
+        ds = r[5].strftime("%Y-%m-%d")
+        if ds in seen: continue
+        subs.append(r[4])
+        dates.append(ds)
+        seen.add(ds)
+    df = pandas.DataFrame({"subscribers": subs, "dates": pandas.to_datetime(dates)}).set_index("dates").sort_index()
+    df = df[df.index > (now - datetime.timedelta(days=90))]
+    try:
+        model = Ridge(alpha=100)
+        X = np.arange(len(df)).reshape(-1, 1)
+        model.fit(X, df["subscribers"])
+        next_m = find_next_milestone(current)
+        days_left = int(((next_m - model.intercept_) / model.coef_)[0])
+        result.update({
+            "next_milestone": str(next_m),
+            "days_until_next_milestone": str(days_left),
+            "next_milestone_date": str((now.date() + datetime.timedelta(days=days_left))) if days_left >= 0 else "N/A"
+        })
+    except:
+        result.update({"next_milestone": "N/A", "days_until_next_milestone": "N/A", "next_milestone_date": "N/A"})
+    return result
+
+def get_group_mappings():
+    group_mappings = {}
+    for name, group in member_groups.items():
+        group_mappings.setdefault(group, []).append(name)
+    return group_mappings