Refactored entire codebase

- Replaced most code with HTML templates
- Fixed some janky SQL queries
- Replaced config.py with ini and json

4 files changed, 304 insertions, 0 deletions

diff --git a/html_builders/builder.py b/html_builders/builder.py
new file mode 100644
index 0000000..dea2e8c
--- /dev/null
+++ b/html_builders/builder.py
@@ -0,0 +1,109 @@
+from jinja2 import Environment, FileSystemLoader
+import html_builders.elements as elements
+import html_builders.graphs as graphs
+import html_builders.tables as tables
+import os
+import time
+
+MENU_ITEMS = [
+    ("Nijitracker", "https://www.nijitracker.com"),
+    ("Pettantracker", "https://nijitracker.com/pettantrack")
+]
+
+
+def build_ranking_page(server, CONFIG: dict, exclude_channels: list = []):
+    page_path = os.path.join(CONFIG["PATH"]["root_html"], "index.html")
+    if not os.path.exists(page_path):
+        os.makedirs(os.path.dirname(page_path), exist_ok=True)
+    file_loader = FileSystemLoader("templates")
+    env = Environment(loader=file_loader)
+    template = env.get_template("ranking.html")
+
+    input_dict = {
+        "meta_image_url": CONFIG["WEBSITE"]["icon"],
+        "meta_description": CONFIG["WEBSITE"]["description"],
+        "meta_title": CONFIG["WEBSITE"]["title"],
+        "title_banner": elements.build_title_banner(
+            CONFIG["WEBSITE"]["title"],
+            MENU_ITEMS
+        ),
+        "ranking_graph": graphs.plot_subscriber_count_over_time(server, CONFIG["TABLES"]["historical"], exclude_channels=exclude_channels),
+        "divider": "Last Updated: " + time.strftime('%Y-%m-%d %H:%M:%S') + " " + CONFIG["WEBSITE"]["timezone"],
+        "ranking_table": tables.generate_html_table(server, CONFIG["TABLES"]["live"], CONFIG["TABLES"]["daily"]),
+        "footer": CONFIG["WEBSITE"]["footer_message"]
+    }
+    output = template.render(input_dict)
+    with open(page_path, "w", encoding="utf-8") as f:
+        f.write(output)
+
+def build_individual_page(server, CONFIG: dict, channel_data: str):
+    def transform_sql_string(string: str) -> str:
+        return string.encode("ascii", "ignore").decode().replace("'", "''")
+    channel_id = channel_data["id"]
+    desc = channel_data["description"]
+    pfp = channel_data["photo"]
+    sub_count = channel_data["subscriber_count"]
+    channel_name = channel_data["english_name"]
+    if channel_name is None:
+        channel_name = channel_data["name"]
+    channel_name = transform_sql_string(channel_name)
+    sub_count_str = "{:,.0f}".format(int(sub_count))
+    page_path = os.path.join(CONFIG["PATH"]["root_html"], channel_name + ".html")
+    file_loader = FileSystemLoader("templates")
+    env = Environment(loader=file_loader)
+    template = env.get_template("individual.html")
+    input_dict = {
+        "meta_image_url": CONFIG["WEBSITE"]["icon"],
+        "meta_description": CONFIG["WEBSITE"]["description"],
+        "meta_title": channel_name + " - " + CONFIG["WEBSITE"]["title"],
+        "homepage_url": CONFIG["WEBSITE"]["homepage"],
+        "sub_text": sub_count_str + " Subscribers",
+        "name": channel_name,
+        "profile_pic": pfp,
+        "description": desc,
+        "channel_id": channel_id,
+        "projection_card": elements.build_projection_card(server, CONFIG["TABLES"]["historical"], int(sub_count), channel_name, timezone = CONFIG["WEBSITE"]["timezone"]),
+        "subscriber_trend": graphs.plot_subscriber_count_over_time(server, CONFIG["TABLES"]["historical"], gtitle = "Subscriber Count Over Time for " + channel_name, overrideQuery = f"SELECT name, subscriber_count, timestamp, channel_id FROM {CONFIG['TABLES']['historical']} WHERE channel_id = '{channel_id}' ORDER by timestamp DESC", markers = "lines+markers"),
+        "divider": "Recent Subscriber Data:",
+        "weekly_trend": graphs.plot_subscriber_count_over_time(server, CONFIG["TABLES"]["historical"], gtitle = "Weekly Subscriber Count Over Time for " + channel_name, overrideQuery = f"SELECT name, subscriber_count, timestamp, channel_id FROM {CONFIG['TABLES']['historical']} WHERE channel_id = '{channel_id}' AND timestamp >= DATE_SUB(NOW(), INTERVAL 7 DAY) ORDER by timestamp DESC", markers = "lines+markers"),
+        "full_table_url": CONFIG["WEBSITE"]["homepage"] + "/tables/"+channel_name
+    }
+    output = template.render(input_dict)
+    with open(page_path, "w", encoding="utf-8") as f:
+        f.write(output)
+
+def build_table_page(server, CONFIG: dict, channel_data: str):
+    def transform_sql_string(string: str) -> str:
+        return string.encode("ascii", "ignore").decode().replace("'", "''")
+    channel_id = channel_data["id"]
+    desc = channel_data["description"]
+    pfp = channel_data["photo"]
+    sub_count = channel_data["subscriber_count"]
+    channel_name = channel_data["english_name"]
+    if channel_name is None:
+        channel_name = channel_data["name"]
+    channel_name = transform_sql_string(channel_name)
+
+    file_loader = FileSystemLoader("templates")
+    env = Environment(loader=file_loader)
+    template = env.get_template("full_table.html")
+  
+    input_dict = {
+        "meta_image_url": CONFIG["WEBSITE"]["icon"],
+        "meta_description": CONFIG["WEBSITE"]["description"],
+        "meta_title": channel_name + " - " + CONFIG["WEBSITE"]["title"],
+        "homepage_url": CONFIG["WEBSITE"]["homepage"],
+        "sub_text": channel_name,
+        "full_table": tables.generate_individual_table(server, CONFIG["TABLES"]["historical"], channel_name),
+        "name": channel_name,
+        "profile_pic": pfp,
+        "description": desc,
+        "channel_id": channel_id,
+    }
+    output = template.render(input_dict)
+    page_path = os.path.join("tables", channel_name + ".html")
+    with open(page_path, "w", encoding="utf-8") as f:
+        f.write(output)
+
+
+    
\ No newline at end of file
diff --git a/html_builders/elements.py b/html_builders/elements.py
new file mode 100644
index 0000000..41c8525
--- /dev/null
+++ b/html_builders/elements.py
@@ -0,0 +1,91 @@
+import math
+import pandas as pd
+import warnings
+from datetime import datetime, timedelta
+
+def build_title_banner(text: str, menu_items):
+    banner_html = f"""
+    <div class="banner-container">
+        <div class="banner-text">{text}</div>
+    </div>
+    <div class="menu-bar">
+    """
+    for item in menu_items:
+        banner_html += f"""
+            <a href="{item[1]}">{item[0]}</a>
+
+        """
+    banner_html += "</div>"
+    return banner_html
+
+def build_projection_card(server, table_name, curr_subscribers, channel_name,
+                        timezone = "Pacific Standard Time"):
+    def get_next_milestone(subscriber_count):
+        num_digits = len(str(subscriber_count))
+        if num_digits <= 4:
+            milestone_interval = 10000
+        else:
+            milestone_interval = 10 ** (num_digits - 1)
+        next_milestone = math.ceil(
+            subscriber_count / milestone_interval) * milestone_interval
+
+        return next_milestone
+
+    warnings.filterwarnings('ignore')  # Ignore pandas warning regarding pyodbc
+    def create_milestone_card(time_until_milestone, next_milestone, not_enough_data = False,
+                              declining = False):
+        now = datetime.now()
+        milestone_date = (
+                now + timedelta(seconds = time_until_milestone)).strftime('%Y-%m-%d')
+        relative_time = now + timedelta(seconds = time_until_milestone) - now
+        next_milestone_str = "{:,}".format(next_milestone)
+        if relative_time.days > 1:
+            relative_time_str = f"In {relative_time.days} days"
+        elif relative_time.days == 1:
+            relative_time_str = f"In {relative_time.days} day"
+        elif relative_time.days < 0:
+            relative_time_str = f"{-relative_time.days} days ago"
+        elif not_enough_data:
+            relative_time_str = "Not enough data"
+        elif declining:
+            relative_time_str = "Declining"
+        else:
+            relative_time_str = "Today"
+        card = f"""
+        <div style="background-color: #ffffff; padding: 20px; border-radius: 10px; box-shadow: 0 0 10px rgba(0, 0, 0, 0.2); font-family: 'Poppins', sans-serif;">
+            <div style="font-size: 24px; font-weight: bold; margin-bottom: 10px;">Next Milestone</div>
+            <div style="font-size: 18px; margin-bottom: 10px;">{next_milestone_str}</div>
+            <div style="display: flex; flex-direction: row; justify-content: space-between; margin-bottom: 10px;">
+                <div style="font-size: 16px;">Estimated Date:</div>
+                <div style="font-size: 16px; font-weight: bold;">{milestone_date}</div>
+            </div>
+            <div style="display: flex; flex-direction: row; justify-content: space-between; color: #888;">
+                <div style="font-size: 16px;">{relative_time_str}</div>
+                <div style="font-size: 16px;">{timezone}</div>
+            </div>
+        </div>
+        <br>
+        """
+        return card
+    query = f"SELECT name, subscriber_count, timestamp FROM {table_name} WHERE timestamp >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) AND name = \"{channel_name}\" ORDER BY timestamp DESC"
+    df = pd.read_sql_query(query, server.get_connection())
+    df = df.sort_values(by = 'timestamp')
+    # get the rate of change from first data point to last data point
+    first_data = df.iloc[0]
+    last_data = df.iloc[-1]
+    delta_sub_count = last_data['subscriber_count'] - first_data['subscriber_count']
+    delta_time = (last_data['timestamp'] - first_data['timestamp']).total_seconds()
+
+    # Calculate the average rate of change of subscriber_count over time
+    avg_rate_of_change = delta_sub_count / delta_time
+    next_milestone = get_next_milestone(curr_subscribers)
+    if avg_rate_of_change == 0 or math.isnan(avg_rate_of_change) or math.isinf(avg_rate_of_change):
+        return create_milestone_card(0, next_milestone, not_enough_data = True)
+    if avg_rate_of_change < 0:
+        return create_milestone_card(0, next_milestone, declining = True)
+    time_to_next_milestone = (
+                                     next_milestone - curr_subscribers) / avg_rate_of_change
+    return create_milestone_card(time_to_next_milestone, next_milestone)
+
+
+
diff --git a/html_builders/graphs.py b/html_builders/graphs.py
new file mode 100644
index 0000000..2770880
--- /dev/null
+++ b/html_builders/graphs.py
@@ -0,0 +1,32 @@
+import plotly.graph_objs as go
+import pandas as pd
+import warnings
+import math
+from datetime import datetime, timedelta
+import numpy as np
+import mysql.connector
+from mysql.connector import errorcode
+
+def plot_subscriber_count_over_time(server, table_name, gtitle = "Subscriber Count Over Time for Nijisanji Members",
+                                    overrideQuery = None, markers = "lines", exclude_channels = []):
+    warnings.filterwarnings('ignore')  # Ignore pandas warning regarding pyodbc
+    query = f"SELECT name, subscriber_count, timestamp, channel_id FROM {table_name} ORDER by timestamp DESC" if overrideQuery is None else overrideQuery
+    df = pd.read_sql_query(query, server.get_connection())
+    groups = df.groupby("name")
+    fig = go.Figure()
+    config = dict({'responsive': True, 'displaylogo': False, 'modeBarButtonsToAdd': ['pan2d', 'zoomIn2d', 'zoomOut2d']})
+    for channel, group in groups:
+        if len(exclude_channels) != 0 and group['channel_id'].iloc[0] in exclude_channels:
+            continue
+        fig.add_trace(go.Scattergl(
+            x = group["timestamp"], y = group["subscriber_count"], name = channel, mode = markers,
+            showlegend = True))
+    fig.update_layout(
+        title = {'text': gtitle, 'x': 0.5, 'xanchor': 'center',
+                 'yanchor': 'top', 'font': {'family': 'Arial', 'size': 30}},
+        xaxis_title = "Timestamp",
+        yaxis_title = "Subscribers",
+        legend = dict(font = dict(size = 16), title = dict(text = "Channels")),
+        height = 950,
+    )
+    return fig.to_html(config = config)
\ No newline at end of file
diff --git a/html_builders/tables.py b/html_builders/tables.py
new file mode 100644
index 0000000..87d2b4a
--- /dev/null
+++ b/html_builders/tables.py
@@ -0,0 +1,72 @@
+def generate_html_table(server, table, diff_table, headers=["Rank", "Liver", "Subscriber", "Difference (24hr)"], root_url="https://www.nijitracker.com"):
+    def get_daily_difference_subs(sub_count_str: str):
+        diff_cursor = server.get_connection().cursor()
+        diff_cursor.execute(query)
+        diff_data = diff_cursor.fetchall()
+        old_sub_count = int(diff_data[0][0])
+        current_sub_count = int(sub_count_str)
+        if old_sub_count > current_sub_count:
+            return f"-{old_sub_count - current_sub_count}"
+        else:
+            return f"+{current_sub_count - old_sub_count}"
+    cursor = server.get_connection().cursor()
+    query = f"SELECT id, channel_id, name, subscriber_count, timestamp, profile_pic FROM {table} ORDER by subscriber_count DESC"
+    cursor.execute(query)
+    data = cursor.fetchall()
+    header = "<tr>"
+    for h in headers:
+        header += f"<th>{h}</th>"
+    header += "</tr>"
+    table_html = "<table>"
+    table_html += header
+    rank = 1
+    for row in data:
+        table_row = "<tr>"
+        table_row += f"<td>{rank}</td>"
+        rank += 1
+        for i, col in enumerate(row):
+            if cursor.description[i][0] == "name":
+                channel_url = f"{root_url}/stats/{row[2]}"
+                profile_pic_url = row[5]
+                table_row += f"<td><a href='{channel_url}'><img src='{profile_pic_url}' height='50px' width='50px'>{col}</a></td>"
+            elif cursor.description[i][0] == "subscriber_count":
+                formatted_sub_count = "{:,.0f}".format(int(col))
+                table_row += f"<td>{formatted_sub_count}</td>"
+            elif cursor.description[i][0] == "timestamp":
+                query = f"SELECT sub_diff FROM {diff_table} WHERE channel_id = '{row[1]}'"
+                try:
+                    table_row += f"<td>{get_daily_difference_subs(row[3])}</td>"
+                except IndexError:
+                    raise Exception("Are you trying to use a new set of channels?\nPlease delete last_refresh.txt in data folder first!")
+            elif cursor.description[i][0] not in ["id", "channel_id", "profile_pic"]:
+                table_row += f"<td>{col}</td>"
+        table_row += "</tr>"
+        table_html += table_row
+    table_html += "</table>"
+    cursor.close()
+    return table_html
+
+def generate_individual_table(server, table_name, channel_name, param="LIMIT 7"):
+    cursor = server.get_connection().cursor()
+    query = f"SELECT subscriber_count, timestamp FROM {table_name} WHERE name=\"{channel_name}\" GROUP BY DATE(timestamp) ORDER by timestamp DESC " + param
+    cursor.execute(query)
+    data = cursor.fetchall()
+    table = "<table>"
+    header = "<tr>"
+    header_cols = ["Subscribers", "Timestamp"]
+    for col in header_cols:
+        header += f"<th>{col}</th>"
+    header += "</tr>"
+    table += header
+    for row in data:
+        table += "<tr>"
+        for i, col in enumerate(row):
+            if cursor.description[i][0] == "subscriber_count":
+                formatted_sub_count = "{:,.0f}".format(int(col))
+                table += f"<td>{formatted_sub_count}</td>"
+            else:
+                table += f"<td>{col}</td>"
+        table += "</tr>"
+    table += "</table>"
+    cursor.close()
+    return table
\ No newline at end of file