%%sql
DROP TABLE IF EXISTS orders;

CREATE TABLE IF NOT EXISTS orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    amount DECIMAL(10, 2),
    name VARCHAR(50)
);

%%sql
INSERT INTO orders (order_id, order_date, amount, name) VALUES
(1, '2024-01-01', 150.00, "Laptop"),
(2, '2024-01-01', 20.00, "Speaker"),
(3, '2024-01-01', 60.00, "Monitor"),
(4, '2024-01-02', 300.00, "Laptop"),
(5, '2024-01-02', 100.00, "Laptop"),
(6, '2024-01-02', 100.00, "Laptop"),
(7, '2024-01-02', 25.00, "Speaker"),
(8, '2024-01-02', 20.00, "Speaker"),
(9, '2024-01-02', 75.00, "Monitor"),
(10, '2024-01-03', 350.00, "Laptop"),
(11, '2024-01-03', 150.00, "Laptop"),
(12, '2024-01-03', 25.00, "Speaker"),
(13, '2024-01-03', 35.00, "Speaker"),
(14, '2024-01-03', 55.00, "Monitor"),
(15, '2024-01-04', 120.00, "Laptop"),
(16, '2024-01-04', 120.00, "Laptop"),
(17, '2024-01-04', 30.00, "Speaker"),
(18, '2024-01-04', 40.00, "Speaker"),
(19, '2024-01-04', 25.00, "Speaker"),
(20, '2024-01-04', 50.00, "Monitor"),
(21, '2024-01-04', 70.00, "Monitor");

import logging

from sqlalchemy import create_engine, text
import requests

ca_cert_url = "https://portal.singlestore.com/static/ca/singlestore_bundle.pem"
ca_cert_path = "/tmp/singlestore_bundle.pem"

response = requests.get(ca_cert_url)
with open(ca_cert_path, "wb") as f:
    f.write(response.content)

sql_connection_string = connection_url.replace("singlestoredb", "mysql+pymysql")

engine = create_engine(
    f"{sql_connection_string}?ssl_ca={ca_cert_path}",
    pool_size=10,           # Maximum number of connections in the pool is 10
    max_overflow=5,         # Allow up to 5 additional connections (temporary overflow)
    pool_timeout=30         # Wait up to 30 seconds for a connection from the pool
)
logging.info("Connection to workspace established successfully.")

def execute_query(query: str):
    logging.info(f"Executing query: {query}")
    with engine.connect() as connection:
        return pd.read_sql_query(query, connection)

import pandas as pd
import plotly.express as px
import plotly.graph_objects as go

def generate_line_chart(type):
    if type == 'Count':
        df = execute_query("SELECT order_date, name, COUNT(*) as sales from orders group by order_date, name order by order_date")
    elif type == 'Total Value':
        df = execute_query("SELECT order_date, name, SUM(amount) as sales from orders group by order_date, name order by order_date")
    fig = px.line(df, x='order_date', y='sales', color='name', markers=True,
              labels={'sales': 'Sales', 'date': 'Order Date'},
              title='Sales Over Time')
    fig.update_layout(
        font_family="Roboto",
        font_color="gray",
        title_font_family="Roboto",
        title_font_color="Black",
        legend_title_font_color="gray"
    )
    return fig

line_chart = generate_line_chart("Count")
line_chart.show()

def generate_pie_chart(date):
    df = execute_query(f"SELECT name, SUM(amount) as sales from orders where order_date = '{date}' group by name")
    fig = px.pie(df,
            names='name',
            values='sales',
            hover_data=['sales'],
            labels={'sales': 'Total Sales', 'name': 'Type'},
            title='Total Cost by Item Type')
    return fig

pie_chart = generate_pie_chart("2024-01-01")
pie_chart.show()

from singlestoredb import apps
from dash import Dash, callback, html, dcc, Input, Output

def get_order_dates():
    df = execute_query("select distinct order_date from orders order by order_date")
    return df['order_date']

initial_dates = get_order_dates()

# Create the Dash App
app = Dash("Sales Report", requests_pathname_prefix=os.environ['SINGLESTOREDB_APP_BASE_PATH'])

# Define the Layout of the Dash App. We will be defining
# - A line chart depicting a time series of sales
# - A dropdown that shows 'Count'/'Total Value' options, which is used to render different line charts
# - An interval counter to keep pinging the Dash App server to get the latest dashboard
# - A pie chart depicting the total proportion of sales for a day by item type
# - A drop down showing the different dates, which is used to render different pie charts

app.layout = html.Div([
    html.P('Sales Dashboard', style={'textAlign':'center', 'marginTop': 50, 'color': '#8800cc', 'fontSize': '32px', 'fontFamily':'Roboto'} ),
    html.Div([
        dcc.Interval(
            id='interval-component',
            interval=2 * 5000,  # Update every second
            n_intervals=0  # Start at 0
        ),
        html.Div(
            dcc.Dropdown(['Count', 'Total Value'], 'Count', id='category-dropdown', style={'width': '200px', 'marginRight':'32px' }),
            style={'display': 'flex', 'justifyContent': 'flex-end'}
        ),
        dcc.Loading(
            id="loading-spinner",
            type="circle",  # Type of spinner: 'circle', 'dot', 'cube', etc.
            children=[
                dcc.Graph(figure = line_chart, id='line-chart'),
            ]
        ),
        html.Div(
            dcc.Dropdown(initial_dates, initial_dates[0], id='date-dropdown', style={'width': '200px', 'marginRight':'32px' }),
            style={'display': 'flex', 'justifyContent': 'flex-end'}
        ),
        dcc.Graph(figure = pie_chart, id='pie-chart'),
    ], style={'margin': '32px'})
])

# Define a callback to update the bar chart based on the category dropdown selection
@app.callback(
    Output("line-chart", "figure"),
    Input("category-dropdown", "value"))  # Use the stored value
def update_bar_chart(type):
    return generate_line_chart(type)

# Define a callback to update the pie chart based on the date dropdown selection
@app.callback(
    Output("pie-chart", "figure"),
    Input("date-dropdown", "value"),
    Input('interval-component', 'n_intervals'))
def update_pie_chart(date, n_intervals):
    return generate_pie_chart(date)

# Define a callback to update the date dropdown periodically
@app.callback(
    Output('date-dropdown', 'options'),
    Input('interval-component', 'n_intervals'))
def update_date_dropdown(n_intervals):
    return get_order_dates()

connectionInfo = await apps.run_dashboard_app(app)