Code Editor for app.py

import streamlit as st
import pandas as pd
import plotly.express as px
from sqlalchemy import create_engine, Column, Integer, String, Float, Date
from sqlalchemy.orm import DeclarativeBase, Session
from datetime import datetime

# Database setup
class Base(DeclarativeBase):
    pass

class Product(Base):
    __tablename__ = "products"
    
    id = Column(Integer, primary_key=True)
    hs_code = Column(String)
    product_name = Column(String)
    exporting_country = Column(String)
    importing_country = Column(String)
    tariff_rate = Column(Float)
    unit_price = Column(Float)
    quantity = Column(Integer)
    date = Column(Date)

# Initialize database
engine = create_engine("sqlite:///supply_chain.sqlite")
Base.metadata.create_all(bind=engine)

# Add sample data if database is empty
def add_sample_data():
    with Session(engine) as session:
        if session.query(Product).first() is None:
            sample_data = [
                Product(
                    hs_code="8471.30",
                    product_name="Laptops",
                    exporting_country="China",
                    importing_country="USA",
                    tariff_rate=25.0,
                    unit_price=500.0,
                    quantity=1000,
                    date=datetime(2023, 1, 1)
                ),
                Product(
                    hs_code="8471.30",
                    product_name="Laptops",
                    exporting_country="Vietnam",
                    importing_country="USA",
                    tariff_rate=10.0,
                    unit_price=550.0,
                    quantity=500,
                    date=datetime(2023, 1, 1)
                ),
            ]
            session.add_all(sample_data)
            session.commit()

add_sample_data()

# Streamlit app
st.title("Supply Chain Analysis Tool")

# Sidebar for filtering
st.sidebar.header("Filters")
with Session(engine) as session:
    # Get unique values for filters
    hs_codes = [code[0] for code in session.query(Product.hs_code).distinct()]
    countries = [country[0] for country in session.query(Product.exporting_country).distinct()]

selected_hs = st.sidebar.selectbox("Select HS Code", hs_codes)
selected_country = st.sidebar.selectbox("Select Exporting Country", countries)

# Main content
tabs = st.tabs(["Product Analysis", "Supplier Comparison", "Tariff Impact"])

with tabs[0]:
    st.header("Product Analysis")
    
    with Session(engine) as session:
        query = session.query(Product).filter(
            Product.hs_code == selected_hs,
            Product.exporting_country == selected_country
        )
        df = pd.read_sql(query.statement, session.bind)
    
    if not df.empty:
        st.write("Product Details:")
        st.dataframe(df)
        
        # Price trend
        fig = px.line(df, x="date", y="unit_price", title="Price Trend")
        st.plotly_chart(fig)

with tabs[1]:
    st.header("Supplier Comparison")
    
    with Session(engine) as session:
        query = session.query(Product).filter(Product.hs_code == selected_hs)
        df_suppliers = pd.read_sql(query.statement, session.bind)
    
    if not df_suppliers.empty:
        # Compare suppliers
        fig_suppliers = px.bar(
            df_suppliers,
            x="exporting_country",
            y="unit_price",
            color="tariff_rate",
            title="Supplier Price Comparison with Tariffs"
        )
        st.plotly_chart(fig_suppliers)
        
        # Calculate total cost (unit price + tariffs)
        df_suppliers["total_cost"] = df_suppliers["unit_price"] * (1 + df_suppliers["tariff_rate"]/100)
        st.write("Total Cost Comparison (Including Tariffs):")
        st.dataframe(df_suppliers[["exporting_country", "unit_price", "tariff_rate", "total_cost"]])

with tabs[2]:
    st.header("Tariff Impact Analysis")
    
    with Session(engine) as session:
        query = session.query(Product).filter(Product.hs_code == selected_hs)
        df_tariffs = pd.read_sql(query.statement, session.bind)
    
    if not df_tariffs.empty:
        # Calculate impact
        df_tariffs["tariff_cost"] = df_tariffs["unit_price"] * (df_tariffs["tariff_rate"]/100)
        df_tariffs["total_tariff_cost"] = df_tariffs["tariff_cost"] * df_tariffs["quantity"]
        
        fig_impact = px.scatter(
            df_tariffs,
            x="tariff_rate",
            y="total_tariff_cost",
            size="quantity",
            color="exporting_country",
            title="Tariff Impact by Country"
        )
        st.plotly_chart(fig_impact)
        
        st.write("Tariff Impact Summary:")
        st.dataframe(df_tariffs[["exporting_country", "tariff_rate", "total_tariff_cost"]])

# Add new product form
st.sidebar.header("Add New Product")
with st.sidebar.form("new_product"):
    new_hs_code = st.text_input("HS Code")
    new_product_name = st.text_input("Product Name")
    new_exp_country = st.text_input("Exporting Country")
    new_imp_country = st.text_input("Importing Country")
    new_tariff = st.number_input("Tariff Rate (%)", min_value=0.0)
    new_price = st.number_input("Unit Price", min_value=0.0)
    new_quantity = st.number_input("Quantity", min_value=0)
    new_date = st.date_input("Date")
    
    if st.form_submit_button("Add Product"):
        with Session(engine) as session:
            new_product = Product(
                hs_code=new_hs_code,
                product_name=new_product_name,
                exporting_country=new_exp_country,
                importing_country=new_imp_country,
                tariff_rate=new_tariff,
                unit_price=new_price,
                quantity=new_quantity,
                date=new_date
            )
            session.add(new_product)
            session.commit()
            st.success("Product added successfully!")