Refactored entire codebase

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

3 files changed, 49 insertions, 407 deletions

diff --git a/sql/graph_builder.py b/sql/graph_builder.py
deleted file mode 100644
index df108b3..0000000
--- a/sql/graph_builder.py
+++ /dev/null
@@ -1,128 +0,0 @@
-import plotly.graph_objs as go
-import pandas as pd
-import mysql.connector
-from mysql.connector import errorcode
-import warnings
-import math
-from datetime import datetime, timedelta
-import numpy as np
-
-
-def plot_subscriber_count_over_time(host, username, password, database_name, table_name,
-                                    gtitle = "Subscriber Count Over Time for Nijisanji Members",
-                                    overrideQuery = None, markers = "lines", exclude_channels = []):
-    warnings.filterwarnings('ignore')  # Ignore pandas warning regarding pyodbc
-    try:
-        cnx = mysql.connector.connect(user = username, password = password,
-                                      host = host, database = database_name)
-    except mysql.connector.Error as err:
-        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
-            print("Something is wrong with your user name or password")
-        elif err.errno == errorcode.ER_BAD_DB_ERROR:
-            print("Database does not exist")
-        else:
-            print(err)
-    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, cnx)
-    groups = df.groupby("name")
-    fig = go.Figure()
-    config = dict({'responsive': True, 'displaylogo': False})
-    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)
-
-
-def generate_projection(host, username, password, database_name, table_name, curr_subscribers,
-                        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
-    try:
-        cnx = mysql.connector.connect(user = username, password = password,
-                                      host = host, database = database_name)
-    except mysql.connector.Error as err:
-        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
-            print("Something is wrong with your user name or password")
-        elif err.errno == errorcode.ER_BAD_DB_ERROR:
-            print("Database does not exist")
-        else:
-            print(err)
-
-    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) ORDER BY timestamp DESC"
-    df = pd.read_sql_query(query, cnx)
-    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/sql/sql_handler.py b/sql/sql_handler.py
index d349f64..a02db52 100644
--- a/sql/sql_handler.py
+++ b/sql/sql_handler.py
@@ -12,19 +12,18 @@ class SQLHandler:
             host_name, user_name, user_password)
         self._load_database(database_name)
 
-    def _create_server_connection(self, host_name: str, user_name: str, user_password: str, exclude=None) -> mysql.connector:
+    def _create_server_connection(self, host_name: str, user_name: str, user_password: str) -> mysql.connector:
         connection = None
         try:
-            connection = mysql.connector.connect(
-                host=host_name,
-                user=user_name,
-                passwd=user_password
-            )
+            connection = mysql.connector.connect(host=host_name, user=user_name, passwd=user_password)
             print("MySQL Database connection successful")
         except Error as err:
             print(f"Error: '{err}'")
         return connection
 
+    def get_connection(self):
+        return self.connection
+
     def _create_database(self, cursor: str, database_name: str):
         try:
             cursor.execute(
@@ -48,75 +47,77 @@ class SQLHandler:
                 print(err)
                 exit(1)
 
-    def create_table(self, table_name: str, table_columns: str):
+    def create_table(self, name: str, column: str):
         cursor = self.connection.cursor()
         try:
-            cursor.execute(f"CREATE TABLE {table_name} ({table_columns})")
-            print(f"Table {table_name} created successfully")
+            cursor.execute(f"CREATE TABLE {name} ({column})")
+            print(f"Table {name} created successfully")
         except Error as err:
             print(err)
 
-    def insert_data(self, table_name: str, table_columns: str, data: str):
+    def insert_row(self, name: str, column: str, data: tuple):
         cursor = self.connection.cursor()
         try:
-            cursor.execute(
-                f"INSERT INTO {table_name} ({table_columns}) VALUES ({data})")
+            placeholders = ', '.join(['%s'] * len(data))
+            query = f"INSERT INTO {name} ({column}) VALUES ({placeholders})"
+            cursor.execute(query, data)
             self.connection.commit()
-            print("Data inserted successfully")
+            print("Data Inserted:", data)
         except Error as err:
             print("Error inserting data")
             print(err)
 
-    def clear_table(self, table_name: str):
+
+    def clear_table(self, name: str):
         cursor = self.connection.cursor()
         try:
-            cursor.execute(f"DELETE FROM {table_name}")
+            cursor.execute(f"DELETE FROM {name}")
             self.connection.commit()
             print("Table cleared successfully")
         except Error as err:
             print("Error clearing table")
             print(err)
 
-    def reset_auto_increment(self, table_name: str):
+    def reset_auto_increment(self, name: str):
         cursor = self.connection.cursor()
         try:
-            cursor.execute(f"ALTER TABLE {table_name} AUTO_INCREMENT = 1")
+            cursor.execute(f"ALTER TABLE {name} AUTO_INCREMENT = 1")
             self.connection.commit()
             print("Table reset successfully")
         except Error as err:
             print("Error resetting table")
             print(err)
 
-    def copy_rows_to_new_table(self, table_name: str, new_table_name: str, table_columns: str):
+    def copy_rows_to_new_table(self, name: str, new_name: str, column: str):
         cursor = self.connection.cursor()
         try:
             cursor.execute(
-                f"INSERT INTO {new_table_name} ({table_columns}) SELECT {table_columns} FROM {table_name}")
+                f"INSERT INTO {new_name} ({column}) SELECT {column} FROM {name}")
             cursor.execute(
-                f"ALTER TABLE {new_table_name} MODIFY COLUMN id INT AUTO_INCREMENT")
+                f"ALTER TABLE {new_name} MODIFY COLUMN id INT AUTO_INCREMENT")
             self.connection.commit()
             print("Rows copied successfully")
         except Error as err:
             print("Error copying rows")
             print(err)
 
-    def drop_table(self, table_name: str):
+    def drop_table(self, name: str):
         cursor = self.connection.cursor()
         try:
-            cursor.execute(f"DROP TABLE {table_name}")
+            cursor.execute(f"DROP TABLE {name}")
             self.connection.commit()
             print("Table dropped successfully")
         except Error as err:
             print("Error dropping table")
             print(err)
     
-    def check_row_exists(self, table_name: str, column_name: str, value: str):
+    def check_row_exists(self, name: str, column_name: str, value: str):
         """
         Checks if a row exists in a table
         """
         cursor = self.connection.cursor()
         try:
-            cursor.execute(f"SELECT * FROM {table_name} WHERE {column_name} = '{value}'")
+            cursor.execute(f"SELECT * FROM {name} WHERE {column_name} = '{value}'")
             result = cursor.fetchone()
             if result:
                 return True
@@ -126,15 +127,36 @@ class SQLHandler:
             print("Error checking row")
             print(err)
 
-    def update_row(self, table_name: str, column_name: str, search_val: str, replace_col:str, new_value: str):
+    def update_row(self, name: str, column_name: str, search_val: str, replace_col:str, new_value: str):
         """
         Updates a row in a table
         """
         cursor = self.connection.cursor()
         try:
-            cursor.execute(f"UPDATE {table_name} SET {replace_col} = '{new_value}' WHERE {column_name} = '{search_val}'")
+            cursor.execute(f"UPDATE {name} SET {replace_col} = '{new_value}' WHERE {column_name} = '{search_val}'")
             self.connection.commit()
             print("Row updated successfully")
         except Error as err:
             print("Error updating row")
-            print(err)
\ No newline at end of file
+            print(err)
+    
+    def execute_query(self, query: str):
+        cursor = self.connection.cursor()
+        try:
+            cursor.execute(query)
+            result = cursor.fetchall()
+            return result
+        except Error as err:
+            print("Error executing query")
+            print(err)
+    
+    def get_query_result(self, query: str):
+        cursor = self.connection.cursor()
+        try:
+            cursor.execute(query)
+            result = cursor.fetchall()
+            return result
+        except Error as err:
+            print("Error executing query")
+            print(err)
+    
\ No newline at end of file
diff --git a/sql/table_builder.py b/sql/table_builder.py
deleted file mode 100644
index e5fbb57..0000000
--- a/sql/table_builder.py
+++ /dev/null
@@ -1,252 +0,0 @@
-import mysql.connector
-from mysql.connector import errorcode
-
-def sql_to_html_table(host, username, password, database_name, table_name,
-                      diff_table="24h_historical", headers=["Rank", "Liver", "Subscribers", "Difference (24hr)"], root_url="https://nijitracker.com"):
-    try:
-        cnx = mysql.connector.connect(user=username, password=password,
-                                      host=host, database=database_name)
-    except mysql.connector.Error as err:
-        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
-            print("Something is wrong with your user name or password")
-        elif err.errno == errorcode.ER_BAD_DB_ERROR:
-            print("Database does not exist")
-        else:
-            print(err)
-
-    cursor = cnx.cursor()
-    query = f"SELECT id, channel_id, name, subscriber_count, timestamp, profile_pic FROM {table_name} ORDER by subscriber_count DESC"
-    cursor.execute(query)
-    data = cursor.fetchall()
-    table = "<table>"
-    header = "<tr>"
-    header_cols = headers
-    for col in header_cols:
-        header += f"<th>{col}</th>"
-    header += "</tr>"
-    table += header
-    rank = 1
-    for row in data:
-        table += "<tr>"
-        table += 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 += 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 += 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:
-                    diff_cursor = cnx.cursor()
-                    diff_cursor.execute(query)
-                    diff_data = diff_cursor.fetchall()
-                    old_sub_count = int(diff_data[0][0])
-                    current_sub_count = int(row[3])
-                    if old_sub_count > current_sub_count:
-                        difference = f"-{old_sub_count - current_sub_count}"
-                    else:
-                        difference = f"+{current_sub_count - old_sub_count}"
-                    table += f"<td>{difference}</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 += f"<td>{col}</td>"
-
-                
-        table += "</tr>"
-    table += "</table>"
-    style = "<style>\
-            table {\
-                font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;\
-                font-size: 16px;\
-                border-collapse: separate;\
-                border-spacing: 0;\
-                width: 100%;\
-                max-width: 1570px;\
-                margin: 0 auto;\
-                background-color: #fff;\
-                border-radius: 5px;\
-                overflow: hidden;\
-                box-shadow: 0px 0px 20px rgba(0, 0, 0, 0.1);\
-            }\
-            th, td {\
-                text-align: left;\
-                padding: 12px 15px;\
-                font-size: 18px;\
-                border-bottom: 1px solid #ddd;\
-            }\
-            th {\
-                background-color: #2a4b71;\
-                color: #fff;\
-                font-weight: bold;\
-                text-transform: uppercase;\
-                letter-spacing: 0.03em;\
-            }\
-            td:nth-child(3), td:nth-child(4) {\
-                text-align: left;\
-                padding: 12px 15px;\
-                font-size: 18px;\
-                border-bottom: 1px solid #ddd;\
-            }\
-            tbody tr:nth-child(even) {\
-                background-color: #f2f2f2;\
-            }\
-            tbody tr:hover {\
-                background-color: #ddd;\
-            }\
-            a {\
-                color: #3c8dbc;\
-                text-decoration: none;\
-            }\
-            </style>"
-    style += "<style>\
-        @media screen and (max-width: 1024px) {\
-            table {\
-                font-size: 14px;\
-            }\
-            th, td {\
-                padding: 8px 10px;\
-                font-size: 16px;\
-            }\
-        }\
-        @media screen and (max-width: 768px) {\
-            th, td {\
-                padding: 5px 8px;\
-                font-size: 14px;\
-            }\
-        }\
-        @media screen and (max-width: 600px) {\
-            th, td {\
-                padding: 3px 5px;\
-                font-size: 10px;\
-            }\
-        }\
-        @media screen and (max-width: 400px) {\
-            th, td {\
-                padding: 2px 4px;\
-                font-size: 8px;\
-            }\
-        }\
-        </style>"
-
-
-    cursor.close()
-    cnx.close()
-    return table + style
-
-
-def generate_individual_table(host, username, password, database_name, table_name, param="LIMIT 7"):
-    try:
-        cnx = mysql.connector.connect(user=username, password=password,
-                                      host=host, database=database_name)
-    except mysql.connector.Error as err:
-        if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
-            print("Something is wrong with your user name or password")
-        elif err.errno == errorcode.ER_BAD_DB_ERROR:
-            print("Database does not exist")
-        else:
-            print(err)
-
-    cursor = cnx.cursor()
-    query = f"SELECT subscriber_count, timestamp FROM {table_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>"
-    style = "<style>\
-            table {\
-                font-family: 'Helvetica Neue', Helvetica, Arial, sans-serif;\
-                font-size: 16px;\
-                border-collapse: separate;\
-                border-spacing: 0;\
-                width: 100%;\
-                max-width: 1570px;\
-                margin: 0 auto;\
-                background-color: #fff;\
-                border-radius: 5px;\
-                overflow: hidden;\
-                box-shadow: 0px 0px 20px rgba(0, 0, 0, 0.1);\
-            }\
-            th, td {\
-                text-align: left;\
-                padding: 12px 15px;\
-                font-size: 18px;\
-                border-bottom: 1px solid #ddd;\
-            }\
-            th {\
-                background-color: #2a4b71;\
-                color: #fff;\
-                font-weight: bold;\
-                text-transform: uppercase;\
-                letter-spacing: 0.03em;\
-            }\
-            td:nth-child(3), td:nth-child(4) {\
-                text-align: left;\
-                padding: 12px 15px;\
-                font-size: 18px;\
-                border-bottom: 1px solid #ddd;\
-            }\
-            tbody tr:nth-child(even) {\
-                background-color: #f2f2f2;\
-            }\
-            tbody tr:hover {\
-                background-color: #ddd;\
-            }\
-            a {\
-                color: #3c8dbc;\
-                text-decoration: none;\
-            }\
-            </style>"
-    style += "<style>\
-        @media screen and (max-width: 1024px) {\
-            table {\
-                font-size: 14px;\
-            }\
-            th, td {\
-                padding: 8px 10px;\
-                font-size: 16px;\
-            }\
-        }\
-        @media screen and (max-width: 768px) {\
-            th, td {\
-                padding: 5px 8px;\
-                font-size: 14px;\
-            }\
-        }\
-        @media screen and (max-width: 600px) {\
-            th, td {\
-                padding: 3px 5px;\
-                font-size: 12px;\
-            }\
-        }\
-        @media screen and (max-width: 400px) {\
-            th, td {\
-                padding: 2px 4px;\
-                font-size: 10px;\
-            }\
-        }\
-        </style>"
-
-    cursor.close()
-    cnx.close()
-    return table + style