ev_application_demo

# Building Interactive Applications with Snowflake and Streamlit - Part 1
# Import python packages
import streamlit as st
import pandas as pd
import altair as alt
from snowflake.snowpark.types import StructType, StructField, StringType, DateType, TimestampType, IntegerType
from snowflake.snowpark.context import get_active_session

# Custom CSS for styling
st.markdown(
    """
    <style>
        .card {
            background-color: #f8f9fa; /* Subtle light grey background */
            padding: 20px;
            border-radius: 10px; /* Rounded corners */
            box-shadow: 2px 2px 10px rgba(0, 0, 0, 0.1); /* Light shadow */
            margin-bottom: 20px;
        }
        .main-title {
            font-size: 36px;
            font-weight: bold;
            color: #451d13;
            text-align: center;
            margin-bottom: 10px;
        }
        .header {
            color: #451d13; 
            font-size: 24px;
            font-weight: bold;
        }
        .features {
            color: #5a5a5a;
            font-size: 18px;
        }
    </style>
    """,
    unsafe_allow_html=True,
)

# Display app title and icon
st.markdown(
    """
    <div style="font-size: 70px; text-align: center;">🚗</div>
    <div class="main-title">EV Sales Tracker</div>
    """,
    unsafe_allow_html=True,
)


# Displaying the content inside a styled div
st.markdown(
    """
    <div class="card">
        <div class="header">This MVP app will allow you to:</div>
        <div class="features">
            <ul>
                <li>Track EV sales over time</li>
                <li>Monitor sales by model, city, or state</li>
                <li>Understand how sales track against targets for each EV model</li>
                <li>Update the targets for each of the models</li>
                <li>Provide an interface to answer additional business questions</li>
            </ul>
        </div>
    </div>
    """,
    unsafe_allow_html=True,
)




# Define a mapping of EV model names to product IDs
model_to_product_id = {
    "Model 3": 1,
    "Model X": 2,
    "Model Y": 3,
    "Model S": 4,
    "Cybertruck": 5
}


# Get the current credentials
session = get_active_session()

# Define the SQL query for fetching EV transaction data
def ev_transactions_query():
    # Get data from the ev_transactions table
    data_query = """
        SELECT 
            date,
            product_name AS model,
            city_name AS city,
            state_name AS state,
            SUM(sales_value) AS total_sales
        FROM ev_transactions
        GROUP BY date, product_name, city_name, state_name
        ORDER BY date
    """
    return data_query

# Define the SQL query for fetching the latest sales targets
def fetch_latest_targets():
    query = """
        SELECT 
            p.product_name AS model, 
            st.target_value AS model_sales_target
        FROM sales_targets st
        JOIN dim_product p ON st.product_id = p.product_id
        WHERE st.target_id IN (
            SELECT MAX(target_id)
            FROM sales_targets
            GROUP BY product_id
        )
        """
    return query

# Fetch transaction and target data from Snowflake
df_transactions_data = session.sql(ev_transactions_query()).to_pandas()
df_model_data = session.sql(fetch_latest_targets()).to_pandas()

# Ensure the 'date' column is in datetime format
df_transactions_data['DATE'] = pd.to_datetime(df_transactions_data['DATE'])

# Display Title
st.title('EV Sales and Targets')

# Dropdown selection for data breakdown dimension
dropdown_option = st.selectbox(
    "Choose a dimension to break down the data:",
    options=["CITY", "STATE", "MODEL"],
    index=0  # Default to the first option
)

# Define interactive hover effect for Altair charts
hover = alt.selection_single(
    fields=["DATE"],
    nearest=True,
    on="mouseover",
    empty="none",
)

# Group and aggregate sales data based on user selection
grouped_data = df_transactions_data.groupby(['DATE', dropdown_option])['TOTAL_SALES'].sum().reset_index()


# Merge model sales targets with your grouped data (only when "MODEL" is selected)
if dropdown_option == "MODEL":
    grouped_data = grouped_data.merge(df_model_data, on='MODEL', how='left')

# Create the main sales trend chart
lines = alt.Chart(grouped_data, title={
    "text": f"{dropdown_option} Purchases Over Time",
    "subtitle": "Dotted lines indicate Sales Target"
}).mark_line().encode(
    x='DATE:T',  # T means temporal (time-based data)
    y='TOTAL_SALES:Q',  # Q means quantitative (numerical data)
    color=f'{dropdown_option}:N',  # Dynamic based on user selection
)


# Add another line to display the sales target for each model
target_points = alt.Chart(grouped_data).mark_square(size=100).encode(
    x=alt.X('DATE:T', title='Date', axis=alt.Axis(format='%d %b %y', labelPadding=10)),  # Increased labelPadding for better spacing
    y=alt.Y('MODEL_SALES_TARGET:Q', title='Total Sales'),
    color=alt.Color('MODEL:N', legend=alt.Legend(title=None)),  # Remove 'MODEL' from the legend title
)

# Highlight selected data points with hover effect
points = lines.transform_filter(hover).mark_square(size=65)

# Add tooltips for additional information
tooltips = alt.Chart(grouped_data).mark_rule().encode(
    x='yearmonthdate(DATE)',  # Ensure date is in year-month-date format
    y='TOTAL_SALES',
    opacity=alt.condition(hover, alt.value(0.3), alt.value(0)),
    tooltip=[
        alt.Tooltip('DATE:T', title='Date', format='%d %b %y'),  # Formatted date as 1 Jan 25
        alt.Tooltip('TOTAL_SALES:Q', title='Total Sales (AUD)', format='$~s'),  # Add $ symbol and format sales
        alt.Tooltip(f'{dropdown_option}:N', title=dropdown_option),  # Add dynamic tooltip based on the dropdown
        alt.Tooltip('MODEL_SALES_TARGET:Q', title='Model Sales Target', format='$~s')  # Add $ symbol and format target
    ]
).add_selection(hover)

# Combine all the chart layers
data_layer = lines + points + tooltips + target_points

# Display the chart with dynamic target lines
st.altair_chart(data_layer, use_container_width=True)


##### Display Targets Form


# Display the form
st.title('Update Model Sales Targets')

# Assuming the model data is already loaded as 'df_model_data' and includes 'MODEL' and 'MODEL_SALES_TARGET'

# Create a selectbox for selecting the model (e.g., "EV Model")
model_select = st.selectbox('Select Model', df_model_data['MODEL'].tolist())

# Get the current sales target for the selected model
model_sales_target = df_model_data[df_model_data['MODEL'] == model_select]['MODEL_SALES_TARGET'].values[0]

# Display current sales target with styled markdown
st.markdown(f"""
    <div style="background-color:#f0f2f6; padding:10px; border-radius:5px; border: 1px solid #d1d1d1;">
        <strong>Current Sales Target for {model_select}:</strong> <span style="font-weight:bold;">${model_sales_target}</span>
    </div>
""", unsafe_allow_html=True)

with st.form(key='target_form'):
    product_id = model_to_product_id.get(model_select, None)  # Replace None with a default value if needed
    
    # Input field for new target value
    new_target = st.text_input('Enter New Target Value')
    
    # Submit button
    submit_button = st.form_submit_button(label='Submit New Target')

    # Process submission
    if submit_button:
        # Check if a new target is entered
        if new_target:
            # Remove the '$' symbol and convert to integer
            new_target_value = new_target.replace('$', '').replace(',', '').strip()
            
            # Ensure the target value is a valid number
            try:
                new_target_value = float(new_target_value)
            except ValueError:
                st.error('Please enter a valid numeric target value.')
                new_target_value = None

            if new_target_value is not None:
                # Define the schema for the target data
                target_data = {
                    "PRODUCT_ID": product_id,
                    "TARGET_VALUE": new_target_value
                }

                # Insert the new target into the sales_targets table (using the same structure as your provided code)
                df_target = pd.DataFrame([target_data])

                # Define the schema for the DataFrame
                target_schema = StructType([
                    StructField("PRODUCT_ID", IntegerType()),
                    StructField("TARGET_VALUE", IntegerType())
                ])

                # Create DataFrame and insert into Snowflake table
                df_target_snowflake = session.create_dataframe(df_target, schema=target_schema)

                # Write DataFrame to Snowflake table (append the new target)
                df_target_snowflake.write.mode("append").save_as_table("sales_targets", column_order="name")

                st.success(f"New target for {model_select} has been successfully submitted!")
        else:
            st.error('Please enter a new target value.')