Fresh initial commit to remove large history

3 files changed, 520 insertions, 0 deletions

diff --git a/sql/graph_builder.py b/sql/graph_builder.py
new file mode 100644
index 0000000..df108b3
--- /dev/null
+++ b/sql/graph_builder.py
@@ -0,0 +1,128 @@
+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
new file mode 100644
index 0000000..d349f64
--- /dev/null
+++ b/sql/sql_handler.py
@@ -0,0 +1,140 @@
+import mysql.connector
+from mysql.connector import Error, errorcode
+
+
+class SQLHandler:
+    def __init__(self, host_name: str, user_name: str, user_password: str, database_name: str):
+        self.host_name = host_name
+        self.username = user_name
+        self.password = user_password
+        self.database_name = database_name
+        self.connection = self._create_server_connection(
+            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:
+        connection = None
+        try:
+            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 _create_database(self, cursor: str, database_name: str):
+        try:
+            cursor.execute(
+                f"CREATE DATABASE {database_name} DEFAULT CHARACTER SET 'utf8'")
+        except Error as err:
+            print(f"Failed creating database: {err}")
+            exit(1)
+
+    def _load_database(self, database_name: str):
+        cursor = self.connection.cursor()
+        try:
+            cursor.execute(f"USE {database_name}")
+            print(f"Database {database_name} loaded successfully")
+        except Error as err:
+            print(f"Database {database_name} does not exist")
+            if err.errno == errorcode.ER_BAD_DB_ERROR:
+                self._create_database(cursor, database_name)
+                print(f"Database {database_name} created successfully")
+                self.connection.database = database_name
+            else:
+                print(err)
+                exit(1)
+
+    def create_table(self, table_name: str, table_columns: str):
+        cursor = self.connection.cursor()
+        try:
+            cursor.execute(f"CREATE TABLE {table_name} ({table_columns})")
+            print(f"Table {table_name} created successfully")
+        except Error as err:
+            print(err)
+
+    def insert_data(self, table_name: str, table_columns: str, data: str):
+        cursor = self.connection.cursor()
+        try:
+            cursor.execute(
+                f"INSERT INTO {table_name} ({table_columns}) VALUES ({data})")
+            self.connection.commit()
+            print("Data inserted successfully")
+        except Error as err:
+            print("Error inserting data")
+            print(err)
+
+    def clear_table(self, table_name: str):
+        cursor = self.connection.cursor()
+        try:
+            cursor.execute(f"DELETE FROM {table_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):
+        cursor = self.connection.cursor()
+        try:
+            cursor.execute(f"ALTER TABLE {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):
+        cursor = self.connection.cursor()
+        try:
+            cursor.execute(
+                f"INSERT INTO {new_table_name} ({table_columns}) SELECT {table_columns} FROM {table_name}")
+            cursor.execute(
+                f"ALTER TABLE {new_table_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):
+        cursor = self.connection.cursor()
+        try:
+            cursor.execute(f"DROP TABLE {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):
+        """
+        Checks if a row exists in a table
+        """
+        cursor = self.connection.cursor()
+        try:
+            cursor.execute(f"SELECT * FROM {table_name} WHERE {column_name} = '{value}'")
+            result = cursor.fetchone()
+            if result:
+                return True
+            else:
+                return False
+        except Error as err:
+            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):
+        """
+        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}'")
+            self.connection.commit()
+            print("Row updated successfully")
+        except Error as err:
+            print("Error updating row")
+            print(err)
\ No newline at end of file
diff --git a/sql/table_builder.py b/sql/table_builder.py
new file mode 100644
index 0000000..e5fbb57
--- /dev/null
+++ b/sql/table_builder.py
@@ -0,0 +1,252 @@
+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