from sqlalchemy import create_engine
import pandas as pd
import pytz

# Database connection configuration
DB_CONFIG = {
    'host': '8.155.23.172',
    'port': 3306,
    'user': 'root2',
    'password': 'tG0f6PVYh18le41BCb',
    'database': 'elonX'
}
TABLE_NAME = 'elon_tweets'
db_uri = f"mysql+pymysql://{DB_CONFIG['user']}:{DB_CONFIG['password']}@{DB_CONFIG['host']}:{DB_CONFIG['port']}/{DB_CONFIG['database']}"
engine = create_engine(db_uri)
# Time zone
eastern = pytz.timezone('America/New_York')
pacific = pytz.timezone('America/Los_Angeles')
central = pytz.timezone('America/Chicago')
# CSV update file path
INPUT_FILE = 'cache/elonmusk.csv'
OUTPUT_FILE = 'cache/fixed.csv'
# Time interval and days options
interval_options = [
    {'label': '1 minute', 'value': 1},
    {'label': '5 minutes', 'value': 5},
    {'label': '10 minutes', 'value': 10},
    {'label': '30 minutes', 'value': 30},
    {'label': '1 hour', 'value': 60},
    {'label': '6 hours(1/4 day)', 'value': 360},
    {'label': '8 hours(1/3 day)', 'value': 480},
    {'label': '12 hours(1/2 day)', 'value': 720},
    {'label': '1 day', 'value': 1440}
]
days_options = [
    {'label': '7 days', 'value': 7},
    {'label': '14 days', 'value': 14},
    {'label': '30 days', 'value': 30},
    {'label': '90 days', 'value': 90},
    {'label': '120 days', 'value': 120},
    {'label': '240 days', 'value': 240}
]

# Global render data
class RenderData:
    def __init__(self):
        self.global_df = None
        self.global_agg_df = None
        self.all_dates = []
        self.default_date = []
        self.load_data()

    def load_data(self):
        df = pd.read_sql(f'SELECT timestamp FROM {TABLE_NAME}', con=engine)
        df['datetime'] = pd.to_datetime(df['timestamp'], unit='s')
        df['datetime_est'] = df['datetime'].dt.tz_localize('UTC').dt.tz_convert(eastern)
        df['date'] = df['datetime_est'].dt.date
        df['minute_of_day'] = df['datetime_est'].dt.hour * 60 + df['datetime_est'].dt.minute
        agg_df = df.groupby(['date', 'minute_of_day']).size().reset_index(name='tweet_count')

        self.global_df = df.copy()
        self.global_agg_df = agg_df.copy()
        self.all_dates = sorted(self.global_agg_df['date'].unique(), reverse=True)
        self.default_date = [str(self.all_dates[0])]

render_data = RenderData()