import sqlite3
from idlelib.configdialog import font_sample_text

import pandas as pd
import plotly.express as px
from dash import Dash, dcc, html
from dash.dependencies import Input, Output
import dash_bootstrap_components as dbc

conn = sqlite3.connect("sales.txt")
query = """
SELECT
    product,
    region,
    sales_amount,
    sales_date
FROM    
    sales_data
"""

df = pd.read_sql(query, conn)

df['sales_date'] = pd.to_datetime(df['sales_date'])

app = Dash(__name__, external_stylesheets=[dbc.themes.CYBORG])
app.title = "Shiny Sales Dashboard"

app.layout = dbc.Container([
    html.H1("📈 Sales Dashboard", className="text-center my-4 text-primary"),

    dbc.Row([
        dbc.Col([
            html.Label("Select Product:", style={'font-weight': 'bold'}),
            dcc.Dropdown(
                options=[{'label': p, 'value': p} for p in df['product'].unique()],
                value=df['product'].unique()[0],
                id='product-dropdown',
                clearable=False
            )
        ], width=6),
    ]),

    dbc.Row([
        dbc.Col([
            dcc.Graph(id='sales-by-region')
        ], width=6),

        dbc.Col([
            dcc.Graph(id='sales-over-time')
        ], width=6)
    ])
], fluid=True)

@app.callback(
    Output('sales-by-region', 'figure'),
    Output('sales-over-time', 'figure'),
    Input('product-dropdown', 'value')
)
def update_charts(selected_product):
    filtered_df = df[df['product'] == selected_product]
    # Pie Chart
    fig_region = px.pie(
        filtered_df,
        names='region',
        values='sales_amount',
        title=f"Sales by Region - {selected_product}",
        color_discrete_sequence=px.colors.sequential.RdBu
    )

    fig_region.update_traces(
        textinfo = 'percent+label',
        pull=[0.05] * len(filtered_df['region'].unique())
    )
    fig_region.update_layout(
        paper_bgcolor='rgba(0,0,0,0)',
        font_color='white',
    )


    # Bar Chart
    monthly_df = filtered_df.copy()

    monthly_df['month'] = monthly_df['sales_date'].dt.to_period('M').astype(str)

    monthly_summary = monthly_df.groupby('month', as_index=False )['sales_amount'].sum()

    fig_time = px.bar(
        monthly_summary,
        x='month',
        y='sales_amount',
        title=f"Monthly Sales - {selected_product}",
        text_auto=True,
        color='sales_amount',
        color_continuous_scale="Plasma"
    )
    fig_time.update_layout(
        plot_bgcolor ='rgba(0,0,0,0)',
        paper_bgcolor='rgba(0,0,0,0)',
        font_color="white",
        hovermode='x',
        xaxis_title="Month",
        yaxis_title="Total Sales",
    )

    return fig_region, fig_time


app.run(debug=True)