import streamlit as st
import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
from scipy import stats
import holidays
from sqlalchemy import create_engine, exc
import os
import tempfile

# =====================
# PAGE CONFIG
# =====================
st.set_page_config(
    page_title="Sales Insight Dashboard",
    page_icon="📊",
    layout="wide",
    initial_sidebar_state="expanded"
)

# =====================
# CUSTOM CSS
# =====================
st.markdown("""
<style>
    /* Main background */
    .stApp {
        background: linear-gradient(135deg, #1a1a2e 0%, #16213e 50%, #0f3460 100%);
    }
    
    /* Metric cards */
    div[data-testid="metric-container"] {
        background: linear-gradient(135deg, rgba(255,255,255,0.1) 0%, rgba(255,255,255,0.05) 100%);
        border: 1px solid rgba(255,255,255,0.1);
        border-radius: 15px;
        padding: 20px;
        backdrop-filter: blur(10px);
        box-shadow: 0 8px 32px rgba(0,0,0,0.3);
    }
    
    div[data-testid="metric-container"] label {
        color: #a0aec0 !important;
        font-size: 0.9rem !important;
    }
    
    div[data-testid="metric-container"] div[data-testid="stMetricValue"] {
        color: #ffffff !important;
        font-size: 1.8rem !important;
        font-weight: 700 !important;
    }
    
    div[data-testid="metric-container"] div[data-testid="stMetricDelta"] {
        font-size: 0.9rem !important;
    }
    
    /* Headers */
    h1, h2, h3 {
        color: #ffffff !important;
        font-weight: 700 !important;
    }
    
    /* Sidebar */
    section[data-testid="stSidebar"] {
        background: linear-gradient(180deg, #1a1a2e 0%, #16213e 100%);
        border-right: 1px solid rgba(255,255,255,0.1);
    }
    
    section[data-testid="stSidebar"] .stMarkdown {
        color: #e2e8f0;
    }
    
    /* Cards/Containers */
    .stExpander {
        background: rgba(255,255,255,0.05);
        border: 1px solid rgba(255,255,255,0.1);
        border-radius: 10px;
    }
    
    /* Dataframes */
    .stDataFrame {
        border-radius: 10px;
        overflow: hidden;
    }
    
    /* Tabs */
    .stTabs [data-baseweb="tab-list"] {
        gap: 8px;
        background: rgba(255,255,255,0.05);
        border-radius: 10px;
        padding: 5px;
    }
    
    .stTabs [data-baseweb="tab"] {
        background: transparent;
        border-radius: 8px;
        color: #a0aec0;
        padding: 10px 20px;
    }
    
    .stTabs [aria-selected="true"] {
        background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
        color: white !important;
    }
    
    /* Info boxes */
    .info-box {
        background: linear-gradient(135deg, rgba(102, 126, 234, 0.2) 0%, rgba(118, 75, 162, 0.2) 100%);
        border: 1px solid rgba(102, 126, 234, 0.3);
        border-radius: 15px;
        padding: 20px;
        margin: 10px 0;
    }
    
    .success-box {
        background: linear-gradient(135deg, rgba(72, 187, 120, 0.2) 0%, rgba(56, 161, 105, 0.2) 100%);
        border: 1px solid rgba(72, 187, 120, 0.3);
        border-radius: 15px;
        padding: 20px;
        margin: 10px 0;
    }
    
    .warning-box {
        background: linear-gradient(135deg, rgba(237, 137, 54, 0.2) 0%, rgba(221, 107, 32, 0.2) 100%);
        border: 1px solid rgba(237, 137, 54, 0.3);
        border-radius: 15px;
        padding: 20px;
        margin: 10px 0;
    }
    
    /* Section headers */
    .section-header {
        background: linear-gradient(90deg, rgba(102, 126, 234, 0.3) 0%, transparent 100%);
        border-left: 4px solid #667eea;
        padding: 10px 20px;
        margin: 20px 0;
        border-radius: 0 10px 10px 0;
    }
    
    /* File uploader */
    .stFileUploader {
        background: rgba(255,255,255,0.05);
        border: 2px dashed rgba(255,255,255,0.2);
        border-radius: 15px;
        padding: 20px;
    }
</style>
""", unsafe_allow_html=True)

# =====================
# AUTO COLUMN FINDER
# =====================
def find_column(df, keys):
    cols = {c.lower(): c for c in df.columns}
    for k in keys:
        for c in cols:
            if k in c:
                return cols[c]
    return None

def auto_insight_engine(df, date_col=None, sales_col=None, inventory_col=None):
    insights = []

    # ---------- SALES TREND ----------
    if date_col and sales_col:
        monthly = df.groupby(df[date_col].dt.to_period("M"))[sales_col].sum()

        if len(monthly) > 1:
            mom = monthly.pct_change() * 100
            last = mom.iloc[-1]

            if last > 10:
                insights.append(("success", "📈 Strong Growth",
                                 f"Sales increased {last:.1f}% last month."))
            elif last < -10:
                insights.append(("warning", "📉 Sharp Decline",
                                 f"Sales dropped {abs(last):.1f}% last month."))

        if len(monthly) > 2:
            vol = monthly.pct_change().std() * 100
            if vol > 25:
                insights.append(("warning", "⚠️ High Volatility",
                                 f"Monthly sales volatility is {vol:.1f}%."))

    # ---------- PROFIT ----------
    if "Profit_Margin" in df:
        avg = df["Profit_Margin"].mean()

        if avg < 15:
            insights.append(("warning", "🔴 Low Margins",
                             f"Average margin only {avg:.1f}%."))

        elif avg > 40:
            insights.append(("success", "🟢 Strong Margins",
                             f"Healthy margin at {avg:.1f}%."))

    # ---------- MARKETING ----------
    if "Ad_ROI" in df:
        roi = df["Ad_ROI"].mean()

        if roi < 1:
            insights.append(("warning", "📢 Ads Losing Money",
                             "ROI below 1 — campaigns are unprofitable."))

        elif roi > 2:
            insights.append(("success", "🚀 Efficient Advertising",
                             f"Average ROI is {roi:.2f}."))

    # ---------- INVENTORY ----------
    if inventory_col and "Days_of_Inventory" in df:
        over = (df["Days_of_Inventory"] > 60).sum()

        if over:
            insights.append(("warning", "📦 Overstock Risk",
                             f"{over} SKUs have more than 60 days stock."))

    # ---------- ANOMALIES ----------
    if date_col and sales_col:
        daily = df.groupby(df[date_col].dt.date)[sales_col].sum()

        if len(daily) > 10:
            # Handle constant values to avoid division by zero in zscore
            if daily.std() > 0:
                z = np.abs(stats.zscore(daily))
                spikes = (z > 3).sum()

                if spikes:
                    insights.append(("warning", "🚨 Sales Anomalies",
                                     f"{spikes} abnormal sales days detected."))

    # ---------- WEEKEND vs WEEKDAY ----------
    if date_col and sales_col:
        df_temp = df.copy()
        df_temp['is_weekend'] = df_temp[date_col].dt.dayofweek >= 5
        weekend_sales = df_temp[df_temp['is_weekend']][sales_col].mean()
        weekday_sales = df_temp[~df_temp['is_weekend']][sales_col].mean()
        
        if weekend_sales > weekday_sales * 1.2:
            insights.append(("success", "🎉 Weekend Surge",
                             f"Weekend sales are {weekend_sales/weekday_sales:.1f}x higher than weekdays."))
    
    # ---------- HOLIDAY ANALYSIS ----------
    if date_col and sales_col:
        us_holidays = holidays.US()
        df_temp = df.copy()
        df_temp['is_holiday'] = df_temp[date_col].dt.date.apply(lambda x: x in us_holidays)
        
        holiday_sales = df_temp[df_temp['is_holiday']][sales_col].mean()
        normal_sales = df_temp[~df_temp['is_holiday']][sales_col].mean()
        
        if pd.notnull(holiday_sales) and pd.notnull(normal_sales) and normal_sales > 0:
            if holiday_sales > normal_sales * 1.3:
                 insights.append(("success", "🎆 Holiday Boom",
                                 f"Sales on holidays are {holiday_sales/normal_sales:.1f}x higher than average!"))
            elif holiday_sales < normal_sales * 0.7:
                 insights.append(("info", "💤 Holiday Slump",
                                 f"Sales tend to dip by {100 - (holiday_sales/normal_sales*100):.0f}% on holidays."))

    return insights

def apply_chart_style(fig, num_items=None, is_horizontal=False):
    """Apply consistent dark theme styling to charts"""
    layout_args = {
        'template': 'plotly_dark',
        'paper_bgcolor': 'rgba(0,0,0,0)',
        'plot_bgcolor': 'rgba(0,0,0,0)',
        'showlegend': False,
        'margin': dict(l=120, t=50, r=20, b=50)
    }
    if is_horizontal and num_items:
        layout_args['height'] = max(400, num_items * 35)
    fig.update_layout(**layout_args)
    return fig

# =====================
# SIDEBAR
# =====================
with st.sidebar:
    st.markdown("# 📊 Sales Insight")
    st.markdown("---")
    
    source_type = st.radio("Data Source", ["📂 CSV File", "🗄️ Database"], horizontal=True)
    
    df = None
    
    if source_type == "📂 CSV File":
        uploaded_files = st.file_uploader(
            "Upload CSV File(s)",
            type=['csv'],
            accept_multiple_files=True,
            help="Upload one or more sales data CSV files"
        )
        
        if not uploaded_files:
            st.info("Using default dataset")
            try:
                df = pd.read_csv("6_months_sells.csv")
            except:
                st.error("No default file found. Please upload a CSV.")
                st.stop()
        else:
            try:
                dfs = []
                for f in uploaded_files:
                    dfs.append(pd.read_csv(f))
                df = pd.concat(dfs, ignore_index=True)
                if len(uploaded_files) > 1:
                    st.success(f"✅ Successfully merged {len(uploaded_files)} files into one dataset ({len(df):,} rows)")
            except Exception as e:
                st.error(f"Error merging files: {e}")
                st.stop()
            
    else:
        st.markdown("### 🔌 Database Connection")
        
        with st.expander("📝 Connection URI Guide"):
            st.markdown("""
            **Google Cloud SQL / AWS RDS / Azure SQL**
            Support for standard PostgreSQL, MySQL, and other drivers.
            
            **Templates:**
            - **PostgreSQL**: `postgresql+psycopg2://USER:PASSWORD@IP:5432/DB_NAME`
            - **MySQL**: `mysql+pymysql://USER:PASSWORD@IP:3306/DB_NAME`
            - **SQL Server**: `mssql+pyodbc://USER:PASSWORD@IP/DB_NAME`
            - **SQLite**: `sqlite:///data.db`
            
            *Note: Ensure your database allows public IP access or use a proxy.*
            """)

        with st.expander("Connection Details", expanded=True):
            conn_method = st.tabs(["🧱 Form Builder", "🔗 Raw URI String"])
            
            db_uri = ""
            
            with conn_method[0]:
                col1, col2 = st.columns(2)
                with col1:
                    db_type = st.selectbox("Database Type", ["PostgreSQL", "MySQL", "SQL Server", "SQLite"])
                with col2:
                    if db_type == "SQLite":
                        db_host = ""
                        db_port = ""
                    elif db_type == "PostgreSQL":
                        db_port = st.text_input("Port", "5432")
                    elif db_type == "MySQL":
                        db_port = st.text_input("Port", "3306")
                    elif db_type == "SQL Server":
                        db_port = st.text_input("Port", "1433")
                
                if db_type != "SQLite":
                    db_host = st.text_input("Host IP/Address", placeholder="34.12.34.56 or db.example.com")
                    db_name = st.text_input("Database Name", placeholder="sales_db")
                    
                    col1, col2 = st.columns(2)
                    with col1:
                        db_user = st.text_input("Username", placeholder="admin")
                    with col2:
                        db_pass = st.text_input("Password", type="password")
                    
                    # Construct URI
                    if st.checkbox("Show Generated URI"):
                        # Preview without password
                        preview_uri = f"{db_type.lower()}://{db_user}:****@{db_host}:{db_port}/{db_name}"
                        st.caption(f"URI Preview: `{preview_uri}`")

                    if db_type == "PostgreSQL":
                        db_uri = f"postgresql+psycopg2://{db_user}:{db_pass}@{db_host}:{db_port}/{db_name}"
                    elif db_type == "MySQL":
                        db_uri = f"mysql+pymysql://{db_user}:{db_pass}@{db_host}:{db_port}/{db_name}"
                    elif db_type == "SQL Server":
                        db_uri = f"mssql+pyodbc://{db_user}:{db_pass}@{db_host}:{db_port}/{db_name}?driver=ODBC+Driver+17+for+SQL+Server"
                else:
                    # SQLite
                    sqlite_path = st.text_input("Database Path", "data.db")
                    db_uri = f"sqlite:///{sqlite_path}"

            with conn_method[1]:
                db_uri_input = st.text_input(
                    "Connection String", 
                    placeholder="postgresql+psycopg2://user:pass@service-account.aivencloud.com:5432/db",
                    help="Enter your SQLAlchemy connection string directly"
                )
                if db_uri_input:
                    db_uri = db_uri_input.strip()
                    # Auto-correction
                    if db_uri.startswith("postgres://"):
                        db_uri = db_uri.replace("postgres://", "postgresql+psycopg2://")

            query = st.text_area(
                "SQL Query",
                value="SELECT * FROM sales_data LIMIT 5000",
                help="Enter your SQL query to fetch data"
            )
            
            # SSL Configuration
            st.markdown("#### 🔐 Security (Optional)")
            use_ssl = st.checkbox("Enable SSL/TLS (Required for Aiven, Azure, etc.)")
            ssl_ca = None
            if use_ssl:
                ssl_ca = st.file_uploader("Upload CA Certificate (.pem)", type=["pem", "crt", "ca"])
            
            if st.button("🔌 Connect & Load"):
                if not db_uri or not query:
                    st.warning("Please provide Connection Details and Query.")
                else:
                    tmp_ca_path = None
                    try:
                        connect_args = {}
                        
                        # Handle SSL Cert creation
                        if use_ssl and ssl_ca:
                            with tempfile.NamedTemporaryFile(delete=False, suffix='.pem') as tmp:
                                tmp.write(ssl_ca.getvalue())
                                tmp_ca_path = tmp.name
                            
                            # Configure based on dialect
                            if "postgres" in db_uri:
                                connect_args = {
                                    "sslmode": "require",
                                    "sslrootcert": tmp_ca_path
                                }
                            elif "mysql" in db_uri:
                                connect_args = {
                                    "ssl": {"ca": tmp_ca_path}
                                }
                        
                        # Add a spinner for better UX on remote connections
                        with st.spinner("Connecting to database..."):
                            engine = create_engine(db_uri, connect_args=connect_args)
                            with engine.connect() as conn:
                                df = pd.read_sql(query, conn)
                                
                    except exc.NoSuchModuleError as e:
                        st.error("🚫 Missing Driver Error")
                        st.warning(f"The required database driver is missing: {e}")
                        st.info("💡 Tip: Standard Google Cloud connections require specific libraries. \n\n"
                               "- For Postgres: `pip install psycopg2-binary`\n"
                               "- For MySQL: `pip install pymysql`\n"
                               "- For SQL Server: `pip install pyodbc`")
                    
                    except Exception as e:
                        st.error(f"Connection Error: {e}")
                        st.markdown("Try checking: 1. IP Whitelisting 2. SSL Configuration 3. Correct Credentials")
                    
                    finally:
                        # Cleanup temp file
                        if tmp_ca_path and os.path.exists(tmp_ca_path):
                            os.unlink(tmp_ca_path)

        
        if df is None:
            st.info("👈 Connect to a database to proceed")
            st.stop()
    
    # Data cleaning - User Configurable
    nan_count = df.isnull().sum().sum()
    
    with st.expander("🧹 Data Cleaning & Handling", expanded=(nan_count > 0)):
        if nan_count > 0:
            st.warning(f"⚠️ Found {nan_count:,} missing values in your data.")
            
            # Show breakdown
            null_cols = df.columns[df.isnull().any()].tolist()
            st.write("Columns with missing data:", null_cols)
            
            clean_mode = st.radio(
                "Choose how to handle missing values:",
                ["✨ Auto-Fill (Recommended)", "🗑️ Drop Rows", "🛑 Keep Raw Data"],
                help="Auto-Fill replaces numbers with 0 and text with 'Unknown'. Drop Rows removes the entire record."
            )
            
            if clean_mode == "✨ Auto-Fill (Recommended)":
                for col in df.columns:
                    if df[col].dtype in ['float64', 'int64']:
                        df[col] = df[col].fillna(0)
                    else:
                        df[col] = df[col].fillna('Unknown')
                st.success("✅ Applied: Numeric -> 0, Text -> 'Unknown'")
                
            elif clean_mode == "🗑️ Drop Rows":
                initial_rows = len(df)
                df = df.dropna()
                st.success(f"✅ Dropped {initial_rows - len(df)} rows")
                
            elif clean_mode == "🛑 Keep Raw Data":
                st.info("⚠️ Keeping data as-is. Some charts may show errors.")
                
        else:
            st.success("✅ Data is clean! No missing values found.")
    
    st.success(f"✅ Loaded {len(df):,} records")
    
    st.markdown("---")
    # =====================
    # COLUMN MAPPING
    # =====================
    st.markdown("### ⚙️ Column Mapping")
    
    # helper to get index for selectbox
    def get_index(options, target):
        try:
            return options.index(target)
        except ValueError:
            return 0

    all_cols = ["None"] + list(df.columns)
    
    # Auto-detect defaults
    default_date = find_column(df, ["date", "month", "time", "day", "period"])
    default_sales = find_column(df, ["sales", "revenue", "amount", "value", "turnover"])
    default_inventory = find_column(df, ["inventory", "stock", "quantity", "volume"])
    default_ad = find_column(df, ["adcost", "ad_cost", "marketing", "spend", "ads"])
    default_clicks = find_column(df, ["click", "visits"])
    default_impr = find_column(df, ["impr", "views", "reach"])
    default_brand = find_column(df, ["brand", "manufacturer", "maker", "vendor"])
    default_cat = find_column(df, ["subcategory", "category", "type", "segment"])
    default_price = find_column(df, ["price", "mrp", "selling_price"])
    default_cost = find_column(df, ["mfrcost", "cost", "mfr_cost", "buying_price"])
    default_cpc = find_column(df, ["avgcpc", "cpc", "avg_cpc", "cost_per_click"])
    default_pos = find_column(df, ["position", "our position", "rank", "ranking"])
    default_cart = find_column(df, ["abdcarts", "abandoned", "cart", "basket"])

    with st.expander("⚙️ Column Configuration", expanded=True):
        st.caption("Map your CSV columns to the dashboard fields.")
        
        st.markdown("#### 📌 Core Columns")
        date_col = st.selectbox("Date Column", all_cols, index=get_index(all_cols, default_date))
        if date_col == "None": date_col = None
        
        sales_col = st.selectbox("Sales/Revenue Column", all_cols, index=get_index(all_cols, default_sales))
        if sales_col == "None": sales_col = None

        brand_col = st.selectbox("Brand Column", all_cols, index=get_index(all_cols, default_brand))
        if brand_col == "None": brand_col = None
        
        cat_col = st.selectbox("Category Column", all_cols, index=get_index(all_cols, default_cat))
        if cat_col == "None": cat_col = None

        st.markdown("#### 📦 Product & Inventory")
        inventory_col = st.selectbox("Inventory/Stock Column", all_cols, index=get_index(all_cols, default_inventory))
        if inventory_col == "None": inventory_col = None
        
        price_col = st.selectbox("Unit Price Column", all_cols, index=get_index(all_cols, default_price))
        if price_col == "None": price_col = None
        
        cost_col = st.selectbox("Unit Cost Column", all_cols, index=get_index(all_cols, default_cost))
        if cost_col == "None": cost_col = None

        st.markdown("#### 📢 Marketing & Metrics")
        ad_col = st.selectbox("Ad Cost Column", all_cols, index=get_index(all_cols, default_ad))
        if ad_col == "None": ad_col = None
        
        clicks_col = st.selectbox("Clicks Column", all_cols, index=get_index(all_cols, default_clicks))
        if clicks_col == "None": clicks_col = None
        
        impr_col = st.selectbox("Impressions Column", all_cols, index=get_index(all_cols, default_impr))
        if impr_col == "None": impr_col = None
        
        avgcpc_col = st.selectbox("CPC Column", all_cols, index=get_index(all_cols, default_cpc))
        if avgcpc_col == "None": avgcpc_col = None
        
        position_col = st.selectbox("Search Position Column", all_cols, index=get_index(all_cols, default_pos))
        if position_col == "None": position_col = None
        
        cart_col = st.selectbox("Abandoned Carts Column", all_cols, index=get_index(all_cols, default_cart))
        if cart_col == "None": cart_col = None

    # Validation
    if not date_col or not sales_col:
        st.warning("⚠️ 'Date' and 'Sales' columns are recommended for full functionality.")

    detected = {
        "📅 Date": date_col,
        "💰 Sales": sales_col,
        "📦 Inventory": inventory_col,
        "📢 Ad Cost": ad_col,
        "🖱️ Clicks": clicks_col,
        "👁️ Impressions": impr_col,
        "🏷️ Brand": brand_col,
        "📂 Category": cat_col,
        "💵 Price": price_col,
        "🏭 Mfr Cost": cost_col,
        "📊 Position": position_col,
        "🛒 Abnd Carts": cart_col,
    }
    
    st.markdown("### 📋 Active Columns")
    active_cols = []
    for name, col in detected.items():
        if col:
            active_cols.append(f"<small>{name}: `{col}`</small>")
    
    if active_cols:
        st.markdown("<br>".join(active_cols), unsafe_allow_html=True)
    else:
        st.info("No columns mapped yet.")
    
    # =====================
    # FILTERS
    # =====================
    st.markdown("---")
    st.markdown("### 🔍 Filters")
    
    # Convert date column first (before storing original)
    if date_col:
        df[date_col] = pd.to_datetime(df[date_col], dayfirst=True)
    
    # Store original df (with datetime converted)
    df_original = df.copy()
    
    # Brand filter
    if brand_col:
        brands = ["All"] + sorted(df[brand_col].dropna().unique().tolist())
        selected_brand = st.selectbox("Select Brand", brands)
        if selected_brand != "All":
            df = df[df[brand_col] == selected_brand]
    
    # Category filter
    if cat_col:
        categories = ["All"] + sorted(df[cat_col].dropna().unique().tolist())
        selected_category = st.selectbox("Select Category", categories)
        if selected_category != "All":
            df = df[df[cat_col] == selected_category]
    
    # Date range filter
    if date_col:
        df[date_col] = pd.to_datetime(df[date_col])
        min_date = df[date_col].min().date()
        max_date = df[date_col].max().date()
        
        date_range = st.date_input(
            "Date Range",
            value=(min_date, max_date),
            min_value=min_date,
            max_value=max_date
        )
        
        if len(date_range) == 2:
            df = df[(df[date_col].dt.date >= date_range[0]) & (df[date_col].dt.date <= date_range[1])]
    
    # Show filtered count
    if len(df) != len(df_original):
        st.info(f"📊 Showing {len(df):,} of {len(df_original):,} records")
    
    st.markdown("---")
    
    # Quick stats
    st.markdown("### 📈 Quick Stats")
    if sales_col:
        st.metric("Filtered Sales", f"{df[sales_col].sum():,.0f}")
    if brand_col:
        st.metric("Brands", f"{df[brand_col].nunique()}")
    if cat_col:
        st.metric("Categories", f"{df[cat_col].nunique()}")

# =====================
# PROCESS DATA
# =====================
# Date conversion already done in filters section

# Calculate derived metrics
if price_col and cost_col:
    df["Profit_Margin"] = (df[price_col] - df[cost_col]) / df[price_col].replace(0, np.nan) * 100

if clicks_col and impr_col:
    df["CTR"] = df[clicks_col] / df[impr_col].replace(0, np.nan) * 100

if sales_col and clicks_col:
    df["Conversion_Rate"] = df[sales_col] / df[clicks_col].replace(0, np.nan) * 100

if inventory_col and sales_col:
    df["Inventory_Turnover"] = df[sales_col] / df[inventory_col].replace(0, np.nan)

if ad_col and sales_col:
    df["Cost_per_Sale"] = df[ad_col] / df[sales_col].replace(0, np.nan)
    df["Ad_ROI"] = df[sales_col] / df[ad_col].replace(0, np.nan)

if inventory_col and sales_col:
    df["IS_Ratio"] = df[inventory_col] / df[sales_col].replace(0, np.nan)

# =====================
# HEADER
# =====================
st.markdown("""
<div style="text-align: center; padding: 20px 0;">
    <h1 style="font-size: 2.5rem; margin-bottom: 10px;">
        📊 <span style="background: linear-gradient(90deg, #667eea, #764ba2, #f093fb);
               -webkit-background-clip: text; -webkit-text-fill-color: transparent;">Sales Insight Dashboard</span>
    </h1>
    <p style="color: #a0aec0; font-size: 1.1rem;">Comprehensive Business Intelligence & Analytics</p>
</div>
""", unsafe_allow_html=True)

# =====================
# KPI METRICS ROW
# =====================
st.markdown('<div class="section-header"><h3>🎯 Executive KPIs</h3></div>', unsafe_allow_html=True)

col1, col2, col3, col4, col5 = st.columns(5)

with col1:
    if sales_col:
        st.metric("Total Sales", f"{df[sales_col].sum():,.0f}", "Revenue")
        
with col2:
    if inventory_col:
        st.metric("Total Inventory", f"{df[inventory_col].sum():,.0f}", "Units")

with col3:
    if ad_col:
        st.metric("Ad Spend", f"${df[ad_col].sum():,.0f}", "Marketing")

with col4:
    if clicks_col:
        st.metric("Total Clicks", f"{df[clicks_col].sum():,.0f}", "Engagement")

with col5:
    if impr_col:
        st.metric("Impressions", f"{df[impr_col].sum()/1e6:.1f}M", "Reach")

# Second row of KPIs
col1, col2, col3, col4, col5 = st.columns(5)

with col1:
    if "Profit_Margin" in df.columns:
        st.metric("Avg Margin", f"{df['Profit_Margin'].mean():.1f}%", "Profitability")

with col2:
    if "CTR" in df.columns:
        st.metric("Avg CTR", f"{df['CTR'].mean():.2f}%", "Click Rate")

with col3:
    if "Conversion_Rate" in df.columns:
        st.metric("Conversion", f"{df['Conversion_Rate'].mean():.4f}%", "Sales/Clicks")

with col4:
    if "Ad_ROI" in df.columns:
        st.metric("Ad ROI", f"{df['Ad_ROI'].mean():.2f}", "Sales/$")

with col5:
    st.metric("Records", f"{len(df):,}", "Data Points")

# =====================
# TABS FOR DIFFERENT ANALYSES
# =====================
st.markdown("<br>", unsafe_allow_html=True)

tab1, tab2, tab3, tab4, tab5, tab6, tab7, tab8, tab9, tab10, tab11, tab12 = st.tabs([
    "📈 Trends", "🏆 Performance", "💰 Profitability", 
    "📢 Marketing", "📦 Inventory", "🔮 Forecast", "📊 Advanced",
    "🔄 Comparison", "🔍 Data Explorer", "🎯 Goals", "🛒 Conversion",
    "🤖 Insights"
])

# =====================
# TAB 1: TRENDS
# =====================
with tab1:
    st.markdown('<div class="section-header"><h3>📈 Sales Trends & Patterns</h3></div>', unsafe_allow_html=True)
    
    if date_col and sales_col:
        col1, col2 = st.columns(2)
        
        with col1:
            # Monthly trend
            monthly = df.groupby(df[date_col].dt.to_period("M"))[sales_col].sum()
            monthly.index = monthly.index.astype(str)
            
            fig = px.bar(
                x=monthly.index, y=monthly.values,
                title="Monthly Sales Trend",
                labels={'x': 'Month', 'y': 'Sales'},
                color=monthly.values,
                color_continuous_scale='Viridis'
            )
            fig.update_layout(
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)',
                showlegend=False
            )
            st.plotly_chart(fig, use_container_width=True)
            
        with col2:
            # Daily sales with moving average
            daily = df.groupby(df[date_col].dt.date)[sales_col].sum().reset_index()
            daily.columns = ['Date', 'Sales']
            
            # --- Auto-Pattern Detection ---
            # Calculate autocorrelation to find best window
            sales_series = pd.Series(daily['Sales'].values)
            lag_7_corr = sales_series.autocorr(lag=7)
            lag_30_corr = sales_series.autocorr(lag=30)
            
            optimal_window = 7 # default
            pattern_msg = "Defaulting to weekly trend."
            
            if lag_30_corr > lag_7_corr and lag_30_corr > 0.3:
                optimal_window = 30
                pattern_msg = f"✨ Detected strong monthly seasonality (Corr: {lag_30_corr:.2f}). Applied 30-Day MA."
            elif lag_7_corr > 0.3:
                optimal_window = 7
                pattern_msg = f"✨ Detected strong weekly seasonality (Corr: {lag_7_corr:.2f}). Applied 7-Day MA."
            else:
                optimal_window = 7
                pattern_msg = "No strong pattern detected. Using 7-Day MA for smoothing."

            daily['MA_Optimal'] = daily['Sales'].rolling(optimal_window, min_periods=1).mean()
            daily['MA_7'] = daily['Sales'].rolling(7, min_periods=1).mean()
            daily['MA_30'] = daily['Sales'].rolling(30, min_periods=1).mean()
            
            # View Toggle
            view_mode = st.radio("Daily Chart View", 
                               ["✨ Smart MA (Auto)", "Detailed (All)", "Manual Comparison (7 vs 30)"], 
                               horizontal=True, label_visibility="collapsed")
            
            if view_mode == "✨ Smart MA (Auto)":
                st.caption(pattern_msg)
            
            fig = go.Figure()
            
            if view_mode == "Detailed (All)":
                fig.add_trace(go.Scatter(x=daily['Date'], y=daily['Sales'], 
                                        name='Daily Sales', mode='lines', 
                                        line=dict(color='#667eea', width=1), opacity=0.4))
                fig.add_trace(go.Scatter(x=daily['Date'], y=daily['MA_7'], 
                                        name='7-Day MA', mode='lines',
                                        line=dict(color='#f093fb', width=2)))

            elif view_mode == "Manual Comparison (7 vs 30)":
                fig.add_trace(go.Scatter(x=daily['Date'], y=daily['MA_7'], 
                                        name='7-Day MA', mode='lines',
                                        line=dict(color='#f093fb', width=2)))
                fig.add_trace(go.Scatter(x=daily['Date'], y=daily['MA_30'], 
                                        name='30-Day MA', mode='lines',
                                        line=dict(color='#48bb78', width=2)))
            
            else: # Smart MA
                fig.add_trace(go.Scatter(x=daily['Date'], y=daily['Sales'], 
                                        name='Daily Sales', mode='lines', 
                                        line=dict(color='#667eea', width=1), opacity=0.2))
                fig.add_trace(go.Scatter(x=daily['Date'], y=daily['MA_Optimal'], 
                                        name=f'{optimal_window}-Day Optimal MA', mode='lines',
                                        line=dict(color='#f093fb', width=3)))
                                    
            fig.update_layout(
                title='Daily Sales Trends',
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)',
                legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1)
            )
            st.plotly_chart(fig, use_container_width=True)
        
        # Holiday Analysis
        st.markdown("#### 🎆 Holiday Effects")
        us_holidays = holidays.US()
        df['Is_Holiday'] = df[date_col].dt.date.apply(lambda x: x in us_holidays)
        df['Holiday_Name'] = df[date_col].dt.date.apply(lambda x: us_holidays.get(x))
        
        col1, col2 = st.columns(2)
        
        with col1:
             holiday_stats = df.groupby('Is_Holiday')[sales_col].mean()
             if len(holiday_stats) > 1:
                 fig = px.bar(
                     x=['Normal Days', 'Holidays'], 
                     y=[holiday_stats.get(False, 0), holiday_stats.get(True, 0)],
                     title="Average Sales: Normal vs Holidays",
                     color=['Normal', 'Holiday'],
                     color_discrete_sequence=['#667eea', '#f093fb']
                 )
                 apply_chart_style(fig)
                 st.plotly_chart(fig, use_container_width=True)
             else:
                 st.info("Not enough holiday data for comparison.")
                 
        with col2:
             holiday_breakdown = df[df['Is_Holiday'] == True].groupby('Holiday_Name')[sales_col].sum().sort_values(ascending=True)
             if not holiday_breakdown.empty:
                 fig = px.bar(
                     x=holiday_breakdown.values, y=holiday_breakdown.index,
                     orientation='h',
                     title="Total Sales by Holiday",
                     color=holiday_breakdown.values,
                     color_continuous_scale='RdYlGn'
                 )
                 apply_chart_style(fig, len(holiday_breakdown), is_horizontal=True)
                 st.plotly_chart(fig, use_container_width=True)
             else:
                 st.info("No sales occurred on holidays in this dataset.")
        
        # Weekly pattern
        col1, col2 = st.columns(2)
        
        with col1:
            df["DayOfWeek"] = df[date_col].dt.day_name()
            weekly = df.groupby("DayOfWeek")[sales_col].mean().reindex([
                "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"
            ])
            
            fig = px.bar(
                x=weekly.index, y=weekly.values,
                title="Average Sales by Day of Week",
                color=weekly.values,
                color_continuous_scale='RdYlGn'
            )
            fig.update_layout(
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)',
                showlegend=False
            )
            st.plotly_chart(fig, use_container_width=True)
            
        with col2:
            # Seasonal Index
            monthly_vals = df.groupby(df[date_col].dt.to_period("M"))[sales_col].sum()
            seasonal_idx = (monthly_vals / monthly_vals.mean() * 100).round(1)
            seasonal_idx.index = seasonal_idx.index.astype(str)
            
            fig = go.Figure(go.Bar(
                x=seasonal_idx.index,
                y=seasonal_idx.values - 100,
                base=100,
                marker_color=['#48bb78' if v > 100 else '#f56565' for v in seasonal_idx.values]
            ))
            fig.add_hline(y=100, line_dash="dash", line_color="white", opacity=0.5)
            fig.update_layout(
                title='Seasonal Index (100 = Average)',
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)',
                yaxis_title='Index'
            )
            st.plotly_chart(fig, use_container_width=True)

# =====================
# TAB 2: PERFORMANCE
# =====================
with tab2:
    st.markdown('<div class="section-header"><h3>🏆 Brand & Category Performance</h3></div>', unsafe_allow_html=True)
    
    col1, col2 = st.columns(2)
    
    with col1:
        if brand_col and sales_col:
            brand_sales = df.groupby(brand_col)[sales_col].sum().sort_values(ascending=True)
            
            fig = px.bar(
                x=brand_sales.values, y=brand_sales.index,
                orientation='h',
                title="Sales by Brand",
                color=brand_sales.values,
                color_continuous_scale='Viridis'
            )
            fig.update_layout(
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)',
                showlegend=False,
                yaxis_title='',
                xaxis_title='Sales',
                height=max(400, len(brand_sales) * 30),  # Dynamic height
                margin=dict(l=100, t=50)  # Left margin for labels
            )
            st.plotly_chart(fig, use_container_width=True)
    
    with col2:
        if cat_col and sales_col:
            cat_sales = df.groupby(cat_col)[sales_col].sum()
            
            fig = px.pie(
                values=cat_sales.values, names=cat_sales.index,
                title="Sales by Category",
                hole=0.4,
                color_discrete_sequence=px.colors.qualitative.Set3
            )
            fig.update_layout(
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)'
            )
            st.plotly_chart(fig, use_container_width=True)
    
    # Brand x Category Matrix
    if brand_col and cat_col and sales_col:
        st.markdown("#### 📊 Brand × Category Sales Matrix")
        matrix = pd.pivot_table(df, values=sales_col, index=brand_col, columns=cat_col, aggfunc='sum')
        
        fig = px.imshow(
            matrix,
            title="Sales Heatmap: Brand vs Category",
            color_continuous_scale='Viridis',
            aspect='auto'
        )
        fig.update_layout(
            template='plotly_dark',
            paper_bgcolor='rgba(0,0,0,0)',
            plot_bgcolor='rgba(0,0,0,0)'
        )
        st.plotly_chart(fig, width="stretch")
    
    # Top/Bottom performers
    col1, col2 = st.columns(2)
    
    with col1:
        st.markdown("#### 🥇 Top 10 Best Sellers")
        top_cols = [sales_col]
        if brand_col: top_cols.insert(0, brand_col)
        if cat_col: top_cols.insert(0, cat_col)
        st.dataframe(df.nlargest(10, sales_col)[top_cols], use_container_width=True)
    
    with col2:
        st.markdown("#### 📉 Bottom 10 Performers")
        st.dataframe(df.nsmallest(10, sales_col)[top_cols], use_container_width=True)

# =====================
# TAB 3: PROFITABILITY
# =====================
with tab3:
    st.markdown('<div class="section-header"><h3>💰 Profitability Analysis</h3></div>', unsafe_allow_html=True)
    
    if "Profit_Margin" in df.columns:
        col1, col2, col3 = st.columns(3)
        
        with col1:
            st.metric("Average Margin", f"{df['Profit_Margin'].mean():.1f}%")
        with col2:
            st.metric("Min Margin", f"{df['Profit_Margin'].min():.1f}%")
        with col3:
            st.metric("Max Margin", f"{df['Profit_Margin'].max():.1f}%")
        
        col1, col2 = st.columns(2)
        
        with col1:
            if brand_col:
                brand_margin = df.groupby(brand_col)["Profit_Margin"].mean().sort_values(ascending=True)
                
                fig = px.bar(
                    x=brand_margin.values, y=brand_margin.index,
                    orientation='h',
                    title="Profit Margin by Brand",
                    color=brand_margin.values,
                    color_continuous_scale='RdYlGn'
                )
                apply_chart_style(fig, len(brand_margin), is_horizontal=True)
                st.plotly_chart(fig, use_container_width=True)
        
        with col2:
            # Margin distribution
            fig = px.histogram(
                df, x="Profit_Margin",
                title="Profit Margin Distribution",
                nbins=50,
                color_discrete_sequence=['#667eea']
            )
            fig.update_layout(
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)'
            )
            st.plotly_chart(fig, use_container_width=True)
        
        # Price Tier Analysis
        if price_col:
            st.markdown("#### 💵 Price Tier Analysis")
            df["Price_Tier"] = pd.qcut(df[price_col], q=4, labels=["Budget", "Economy", "Premium", "Luxury"])
            
            tier_data = df.groupby("Price_Tier", observed=True).agg({
                sales_col: ['sum', 'mean', 'count']
            }).round(0)
            tier_data.columns = ['Total Sales', 'Avg Sales', 'Products']
            
            col1, col2 = st.columns(2)
            
            with col1:
                st.dataframe(tier_data, use_container_width=True)
            
            with col2:
                tier_sales = df.groupby("Price_Tier", observed=True)[sales_col].sum()
                fig = px.pie(
                    values=tier_sales.values, names=tier_sales.index,
                    title="Revenue by Price Tier",
                    hole=0.4,
                    color_discrete_sequence=['#667eea', '#764ba2', '#f093fb', '#48bb78']
                )
                fig.update_layout(
                    template='plotly_dark',
                    paper_bgcolor='rgba(0,0,0,0)',
                    plot_bgcolor='rgba(0,0,0,0)'
                )
                st.plotly_chart(fig, use_container_width=True)
    else:
        st.warning("Price and Cost columns required for profitability analysis")

# =====================
# TAB 4: MARKETING
# =====================
with tab4:
    st.markdown('<div class="section-header"><h3>📢 Marketing Performance</h3></div>', unsafe_allow_html=True)
    
    col1, col2, col3, col4, col5 = st.columns(5)
    
    with col1:
        if "CTR" in df.columns:
            st.metric("Avg CTR", f"{df['CTR'].mean():.2f}%")
    with col2:
        if "Conversion_Rate" in df.columns:
            st.metric("Avg Conversion", f"{df['Conversion_Rate'].mean():.4f}%")
    with col3:
        if "Ad_ROI" in df.columns:
            st.metric("Avg ROI", f"{df['Ad_ROI'].mean():.2f}")
    with col4:
        if avgcpc_col:
            st.metric("Avg CPC", f"${df[avgcpc_col].mean():.2f}")
    with col5:
        if position_col:
            st.metric("Avg Position", f"{df[position_col].mean():.1f}", help="Lower is better (0 = Top Rank)")
    
    col1, col2 = st.columns(2)
    
    with col1:
        if brand_col and "CTR" in df.columns:
            brand_ctr = df.groupby(brand_col)["CTR"].mean().sort_values(ascending=True)
            
            fig = px.bar(
                x=brand_ctr.values, y=brand_ctr.index,
                orientation='h',
                title="CTR by Brand",
                color=brand_ctr.values,
                color_continuous_scale='Blues'
            )
            apply_chart_style(fig, len(brand_ctr), is_horizontal=True)
            st.plotly_chart(fig, use_container_width=True)
    
    with col2:
        if brand_col and "Ad_ROI" in df.columns:
            brand_roi = df.groupby(brand_col)["Ad_ROI"].mean().sort_values(ascending=True)
            
            fig = px.bar(
                x=brand_roi.values, y=brand_roi.index,
                orientation='h',
                title="Ad ROI by Brand",
                color=brand_roi.values,
                color_continuous_scale='Greens'
            )
            apply_chart_style(fig, len(brand_roi), is_horizontal=True)
            st.plotly_chart(fig, use_container_width=True)
    
    # Cost per Sale
    if brand_col and "Cost_per_Sale" in df.columns:
        st.markdown("#### 💸 Marketing Cost per Sale")
        cps = df.groupby(brand_col)["Cost_per_Sale"].mean().sort_values()
        
        fig = px.bar(
            x=cps.index, y=cps.values,
            title="Cost per Sale by Brand (Lower is Better)",
            color=cps.values,
            color_continuous_scale='RdYlGn_r'
        )
        fig.update_layout(
            template='plotly_dark',
            paper_bgcolor='rgba(0,0,0,0)',
            plot_bgcolor='rgba(0,0,0,0)',
            showlegend=False
        )
        st.plotly_chart(fig, width="stretch")

    # Search Position Analysis
    if position_col:
        st.markdown('<div class="section-header"><h3>🔍 Search Visibility & Position</h3></div>', unsafe_allow_html=True)
        col1, col2 = st.columns(2)
        
        with col1:
             # Avg Position by Brand (Lower is better)
             if brand_col:
                 brand_pos = df.groupby(brand_col)[position_col].mean().sort_values(ascending=True) # Ascending because lower is better
                 
                 fig = px.bar(
                     x=brand_pos.values, y=brand_pos.index,
                     orientation='h',
                     title="Avg Search Position by Brand (Lower is Better)",
                     color=brand_pos.values,
                     color_continuous_scale='RdYlGn_r' # Red for high numbers (bad), Green for low (good)
                 )
                 apply_chart_style(fig, len(brand_pos), is_horizontal=True)
                 st.plotly_chart(fig, use_container_width=True)
                 
        with col2:
            # Position Distribution
            fig = px.histogram(
                df, x=position_col,
                title="Search Position Distribution",
                nbins=20,
                color_discrete_sequence=['#f093fb']
            )
            fig.update_layout(
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)'
            )
            st.plotly_chart(fig, use_container_width=True)
            
        # Daily Position Trend
        if date_col:
            st.markdown("#### 📉 Daily Average Position Trend")
            daily_pos = df.groupby(df[date_col].dt.date)[position_col].mean().reset_index()
            daily_pos.columns = ['Date', 'Avg Position']
            
            fig = go.Figure()
            fig.add_trace(go.Scatter(
                x=daily_pos['Date'], y=daily_pos['Avg Position'],
                mode='lines',
                line=dict(color='#f093fb', width=3),
                name='Avg Position',
                fill='tozeroy',
                fillcolor='rgba(240, 147, 251, 0.1)'
            ))
            fig.update_layout(
                title="Average Search Position (Inverted Axis - Lower is Better)",
                yaxis=dict(autorange="reversed", title="Avg Position"), # Invert Y axis so 0 is at top
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)'
            )
            st.plotly_chart(fig, use_container_width=True)

# =====================
# TAB 5: INVENTORY
# =====================
with tab5:
    st.markdown('<div class="section-header"><h3>📦 Inventory Analysis</h3></div>', unsafe_allow_html=True)
    
    if inventory_col and sales_col:
        # Inventory Health
        daily_sales_rate = df[sales_col].sum() / 90
        df["Days_of_Inventory"] = df[inventory_col] / daily_sales_rate
        
        overstocked = df[df["Days_of_Inventory"] > 60]
        understocked = df[df["Days_of_Inventory"] < 7]
        
        col1, col2, col3 = st.columns(3)
        
        with col1:
            st.metric("Avg Days of Inventory", f"{df['Days_of_Inventory'].mean():.0f} days")
        with col2:
            st.metric("Overstocked (>60 days)", f"{len(overstocked):,}", f"{len(overstocked)/len(df)*100:.1f}%")
        with col3:
            st.metric("Understocked (<7 days)", f"{len(understocked):,}", f"{len(understocked)/len(df)*100:.1f}%")
        
        col1, col2 = st.columns(2)
        
        with col1:
            if brand_col:
                brand_turnover = df.groupby(brand_col)["Inventory_Turnover"].mean().sort_values(ascending=True)
                
                fig = px.bar(
                    x=brand_turnover.values, y=brand_turnover.index,
                    orientation='h',
                    title="Inventory Turnover by Brand",
                    color=brand_turnover.values,
                    color_continuous_scale='Viridis'
                )
                apply_chart_style(fig, len(brand_turnover), is_horizontal=True)
                st.plotly_chart(fig, use_container_width=True)
        
        with col2:
            if brand_col:
                brand_is = df.groupby(brand_col)["IS_Ratio"].mean().sort_values()
                
                fig = px.bar(
                    x=brand_is.index, y=brand_is.values,
                    title="Inventory-to-Sales Ratio (Lower is Better)",
                    color=brand_is.values,
                    color_continuous_scale='RdYlGn_r'
                )
                fig.update_layout(
                    template='plotly_dark',
                    paper_bgcolor='rgba(0,0,0,0)',
                    plot_bgcolor='rgba(0,0,0,0)',
                    showlegend=False
                )
                st.plotly_chart(fig, use_container_width=True)
        
        # Low turnover items
        st.markdown("#### ⚠️ Low Inventory Turnover (Risk)")
        worst = df.sort_values("Inventory_Turnover").head(10)
        display_cols = ["Inventory_Turnover"]
        if cat_col: display_cols.insert(0, cat_col)
        if brand_col: display_cols.insert(0, brand_col)
        st.dataframe(worst[display_cols], use_container_width=True)

# =====================
# TAB 6: FORECAST
# =====================
with tab6:
    st.markdown('<div class="section-header"><h3>🔮 Forecasting & Predictions</h3></div>', unsafe_allow_html=True)
    
    if date_col and sales_col:
        monthly = df.groupby(df[date_col].dt.to_period("M"))[sales_col].sum()
        monthly_values = monthly.values
        
        if len(monthly_values) >= 2:
            forecast_model = st.radio("Forecast Model", 
                                    ["Linear Growth (Momentum)", "Moving Average (3-Month)"],
                                    horizontal=True)

            # Common variables
            last_month = monthly_values[-1]

            if forecast_model == "Moving Average (3-Month)":
                # Rolling Moving Average Forecast
                history = list(monthly_values)
                forecasted = []
                for _ in range(3):
                    # Average of last 3 periods
                    next_val = np.mean(history[-3:])
                    forecasted.append(next_val)
                    history.append(next_val)
                
                forecast_next, forecast_next_2, forecast_next_3 = forecasted
                avg_change = forecast_next - last_month # Implicit change
                
            else:
                # Original Linear Momentum Logic
                avg_change = np.mean(np.diff(monthly_values))
                forecast_next = last_month + avg_change
                forecast_next_2 = last_month + (avg_change * 2)
                forecast_next_3 = last_month + (avg_change * 3)
            
            col1, col2, col3, col4 = st.columns(4)
            
            trend = "📈 GROWING" if avg_change > 0 else "📉 DECLINING" if avg_change < 0 else "➡️ STABLE"
            
            with col1:
                st.metric("Last Month", f"{last_month:,.0f}")
            with col2:
                st.metric("Avg Monthly Change", f"{avg_change:+,.0f}")
            with col3:
                st.metric("Next Month Forecast", f"{forecast_next:,.0f}")
            with col4:
                st.metric("Trend", trend)
            
            # Forecast visualization
            historical = list(monthly.values)
            forecasted = [forecast_next, forecast_next_2, forecast_next_3]
            
            all_periods = list(monthly.index.astype(str)) + ['M+1', 'M+2', 'M+3']
            all_values = historical + forecasted
            colors = ['#667eea'] * len(historical) + ['#f093fb'] * 3
            
            fig = go.Figure()
            fig.add_trace(go.Bar(
                x=all_periods,
                y=all_values,
                marker_color=colors
            ))
            fig.add_vline(x=len(historical)-0.5, line_dash="dash", line_color="white", opacity=0.5)
            fig.update_layout(
                title='Sales Forecast (Historical + Projected)',
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)'
            )
            st.plotly_chart(fig, use_container_width=True)
            
            # Growth analysis
            col1, col2 = st.columns(2)
            
            with col1:
                weekly = df.groupby(df[date_col].dt.isocalendar().week)[sales_col].sum()
                wow = weekly.pct_change() * 100
                
                st.markdown("#### 📊 Growth Rate Analysis")
                st.metric("Avg WoW Growth", f"{wow.mean():.2f}%")
                st.metric("Best Week", f"+{wow.max():.2f}%")
                st.metric("Worst Week", f"{wow.min():.2f}%")
            
            with col2:
                # Price elasticity
                if price_col:
                    price_corr = df[price_col].corr(df[sales_col])
                    
                    if price_corr < -0.3:
                        elasticity = "🔴 ELASTIC (Price Sensitive)"
                    elif price_corr > 0.3:
                        elasticity = "🟢 POSITIVE (Premium Effect)"
                    else:
                        elasticity = "🟡 INELASTIC (Price Insensitive)"
                    
                    st.markdown("#### 📈 Price Elasticity")
                    st.metric("Price-Sales Correlation", f"{price_corr:.4f}")
                    st.info(f"Demand Type: {elasticity}")

# =====================
# TAB 7: ADVANCED
# =====================
with tab7:
    st.markdown('<div class="section-header"><h3>📊 Advanced Analytics</h3></div>', unsafe_allow_html=True)
    
    col1, col2 = st.columns(2)
    
    with col1:
        # Pareto Analysis
        if sales_col:
            st.markdown("#### 📊 Pareto Analysis (80/20 Rule)")
            
            sorted_sales = df.sort_values(sales_col, ascending=False).reset_index(drop=True)
            sorted_sales["Cumulative_Sales"] = sorted_sales[sales_col].cumsum()
            sorted_sales["Cumulative_Pct"] = sorted_sales["Cumulative_Sales"] / sorted_sales[sales_col].sum() * 100
            
            products_for_80 = (sorted_sales["Cumulative_Pct"] <= 80).sum()
            pct_products = products_for_80 / len(df) * 100
            
            st.metric("Products for 80% Revenue", f"{products_for_80:,}", f"{pct_products:.1f}% of total")
            
            fig = go.Figure()
            fig.add_trace(go.Scatter(
                y=sorted_sales["Cumulative_Pct"].values[:1000],
                mode='lines',
                fill='tozeroy',
                line=dict(color='#667eea')
            ))
            fig.add_hline(y=80, line_dash="dash", line_color="#f093fb")
            fig.update_layout(
                title='Cumulative Sales Distribution',
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)',
                yaxis_title='Cumulative %',
                xaxis_title='Products (ranked)'
            )
            st.plotly_chart(fig, use_container_width=True)
    
    with col2:
        # ABC Classification
        if sales_col:
            st.markdown("#### 🔤 ABC Classification")
            
            abc_df = df.groupby(df.index)[sales_col].sum().sort_values(ascending=False)
            abc_cumsum = abc_df.cumsum() / abc_df.sum() * 100
            
            total = len(df)
            a_count = (abc_cumsum <= 70).sum()
            b_count = ((abc_cumsum > 70) & (abc_cumsum <= 90)).sum()
            c_count = (abc_cumsum > 90).sum()
            
            abc_data = pd.DataFrame({
                'Class': ['A (Top 70%)', 'B (Next 20%)', 'C (Bottom 10%)'],
                'Items': [a_count, b_count, c_count],
                'Percentage': [a_count/total*100, b_count/total*100, c_count/total*100]
            })
            
            fig = px.pie(
                abc_data, values='Items', names='Class',
                title='ABC Classification',
                color_discrete_sequence=['#48bb78', '#ecc94b', '#f56565']
            )
            fig.update_layout(
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)'
            )
            st.plotly_chart(fig, use_container_width=True)
    
    # Variance Analysis
    if sales_col:
        st.markdown("#### 📉 Variance Analysis")
        
        col1, col2 = st.columns(2)
        
        with col1:
            mean_val = df[sales_col].mean()
            overall_cv = (df[sales_col].std() / mean_val) * 100 if mean_val != 0 else 0
            st.metric("Coefficient of Variation", f"{overall_cv:.1f}%", "Sales consistency")
            st.metric("Std Deviation", f"{df[sales_col].std():.2f}")
        
        with col2:
            if brand_col:
                def safe_cv(x):
                    if x.mean() == 0:
                        return 0
                    return (x.std() / x.mean()) * 100
                
                brand_cv = df.groupby(brand_col)[sales_col].apply(safe_cv).sort_values()
                
                fig = px.bar(
                    x=brand_cv.index, y=brand_cv.values,
                    title="Variance by Brand (CV% - Lower = More Consistent)",
                    color=brand_cv.values,
                    color_continuous_scale='RdYlGn_r'
                )
                fig.update_layout(
                    template='plotly_dark',
                    paper_bgcolor='rgba(0,0,0,0)',
                    plot_bgcolor='rgba(0,0,0,0)',
                    showlegend=False
                )
                st.plotly_chart(fig, use_container_width=True)
    
    # Correlation matrix
    st.markdown("#### 🔗 Correlation Analysis")
    num_cols = df.select_dtypes(np.number).columns[:10]  # Limit to first 10 numeric columns
    corr_matrix = df[num_cols].corr()
    
    fig = px.imshow(
        corr_matrix,
        title="Correlation Heatmap",
        color_continuous_scale='RdBu_r',
        aspect='auto'
    )
    fig.update_layout(
        template='plotly_dark',
        paper_bgcolor='rgba(0,0,0,0)',
        plot_bgcolor='rgba(0,0,0,0)'
    )
    st.plotly_chart(fig, width="stretch")

# =====================
# TAB 8: COMPARISON
# =====================
with tab8:
    st.markdown('<div class="section-header"><h3>🔄 Comparison Analysis</h3></div>', unsafe_allow_html=True)
    
    comparison_type = st.radio(
        "Compare by:",
        ["Brand vs Brand", "Category vs Category", "Time Period vs Time Period"],
        horizontal=True
    )
    
    if comparison_type == "Brand vs Brand" and brand_col and sales_col:
        col1, col2 = st.columns(2)
        
        brands_list = sorted(df_original[brand_col].unique().tolist())
        
        with col1:
            brand1 = st.selectbox("Select Brand 1", brands_list, key="brand1")
        with col2:
            brand2 = st.selectbox("Select Brand 2", brands_list, index=1 if len(brands_list) > 1 else 0, key="brand2")
        
        df1 = df_original[df_original[brand_col] == brand1]
        df2 = df_original[df_original[brand_col] == brand2]
        
        # Comparison metrics
        col1, col2 = st.columns(2)
        
        with col1:
            st.markdown(f"### 🏷️ {brand1}")
            st.metric("Total Sales", f"{df1[sales_col].sum():,.0f}")
            st.metric("Avg Sale", f"{df1[sales_col].mean():.2f}")
            if "Profit_Margin" in df1.columns:
                st.metric("Avg Margin", f"{df1['Profit_Margin'].mean():.1f}%")
            if "CTR" in df1.columns:
                st.metric("Avg CTR", f"{df1['CTR'].mean():.2f}%")
            if "Ad_ROI" in df1.columns:
                st.metric("Avg ROI", f"{df1['Ad_ROI'].mean():.2f}")
        
        with col2:
            st.markdown(f"### 🏷️ {brand2}")
            st.metric("Total Sales", f"{df2[sales_col].sum():,.0f}")
            st.metric("Avg Sale", f"{df2[sales_col].mean():.2f}")
            if "Profit_Margin" in df2.columns:
                st.metric("Avg Margin", f"{df2['Profit_Margin'].mean():.1f}%")
            if "CTR" in df2.columns:
                st.metric("Avg CTR", f"{df2['CTR'].mean():.2f}%")
            if "Ad_ROI" in df2.columns:
                st.metric("Avg ROI", f"{df2['Ad_ROI'].mean():.2f}")
        
        # Comparison chart
        if date_col:
            comp_data = df_original[df_original[brand_col].isin([brand1, brand2])]
            monthly_comp = comp_data.groupby([comp_data[date_col].dt.to_period("M"), brand_col])[sales_col].sum().unstack()
            monthly_comp.index = monthly_comp.index.astype(str)
            
            fig = go.Figure()
            for brand in [brand1, brand2]:
                if brand in monthly_comp.columns:
                    fig.add_trace(go.Scatter(
                        x=monthly_comp.index, y=monthly_comp[brand],
                        name=brand, mode='lines+markers'
                    ))
            fig.update_layout(
                title=f"Sales Trend: {brand1} vs {brand2}",
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                plot_bgcolor='rgba(0,0,0,0)'
            )
            st.plotly_chart(fig, use_container_width=True)
    
    elif comparison_type == "Category vs Category" and cat_col and sales_col:
        col1, col2 = st.columns(2)
        
        cats_list = sorted(df_original[cat_col].unique().tolist())
        
        with col1:
            cat1 = st.selectbox("Select Category 1", cats_list, key="cat1")
        with col2:
            cat2 = st.selectbox("Select Category 2", cats_list, index=1 if len(cats_list) > 1 else 0, key="cat2")
        
        df1 = df_original[df_original[cat_col] == cat1]
        df2 = df_original[df_original[cat_col] == cat2]
        
        # Comparison metrics
        metrics_df = pd.DataFrame({
            'Metric': ['Total Sales', 'Avg Sale', 'Records'],
            cat1: [df1[sales_col].sum(), df1[sales_col].mean(), len(df1)],
            cat2: [df2[sales_col].sum(), df2[sales_col].mean(), len(df2)]
        })
        
        st.dataframe(metrics_df, use_container_width=True)
        
        # Side by side bar chart
        fig = go.Figure(data=[
            go.Bar(name=cat1, x=['Total Sales', 'Records'], y=[df1[sales_col].sum(), len(df1)]),
            go.Bar(name=cat2, x=['Total Sales', 'Records'], y=[df2[sales_col].sum(), len(df2)])
        ])
        fig.update_layout(
            title=f"{cat1} vs {cat2}",
            barmode='group',
            template='plotly_dark',
            paper_bgcolor='rgba(0,0,0,0)',
            plot_bgcolor='rgba(0,0,0,0)'
        )
        st.plotly_chart(fig, width="stretch")
    
    elif comparison_type == "Time Period vs Time Period" and date_col and sales_col:
        st.markdown("#### Select two time periods to compare")
        
        col1, col2 = st.columns(2)
        
        with col1:
            st.markdown("**Period 1**")
            p1_start = st.date_input("Start", value=df_original[date_col].min().date(), key="p1s")
            p1_end = st.date_input("End", value=df_original[date_col].min().date() + pd.Timedelta(days=30), key="p1e")
        
        with col2:
            st.markdown("**Period 2**")
            p2_start = st.date_input("Start", value=df_original[date_col].max().date() - pd.Timedelta(days=30), key="p2s")
            p2_end = st.date_input("End", value=df_original[date_col].max().date(), key="p2e")
        
        df1 = df_original[(df_original[date_col].dt.date >= p1_start) & (df_original[date_col].dt.date <= p1_end)]
        df2 = df_original[(df_original[date_col].dt.date >= p2_start) & (df_original[date_col].dt.date <= p2_end)]
        
        col1, col2 = st.columns(2)
        
        with col1:
            st.markdown(f"### 📅 Period 1")
            st.caption(f"{p1_start} to {p1_end}")
            st.metric("Total Sales", f"{df1[sales_col].sum():,.0f}")
            st.metric("Daily Avg", f"{df1[sales_col].sum() / max(1, (p1_end - p1_start).days):,.0f}")
            st.metric("Records", f"{len(df1):,}")
        
        with col2:
            st.markdown(f"### 📅 Period 2")
            st.caption(f"{p2_start} to {p2_end}")
            p2_sales = df2[sales_col].sum()
            p1_sales = df1[sales_col].sum()
            change = ((p2_sales - p1_sales) / p1_sales * 100) if p1_sales > 0 else 0
            st.metric("Total Sales", f"{p2_sales:,.0f}", f"{change:+.1f}%")
            st.metric("Daily Avg", f"{df2[sales_col].sum() / max(1, (p2_end - p2_start).days):,.0f}")
            st.metric("Records", f"{len(df2):,}")

# =====================
# TAB 9: DATA EXPLORER
# =====================
with tab9:
    st.markdown('<div class="section-header"><h3>🔍 Data Explorer</h3></div>', unsafe_allow_html=True)
    
    # Search and filter
    col1, col2, col3 = st.columns(3)
    
    with col1:
        search_col = st.selectbox("Search in column", df.columns.tolist())
    
    with col2:
        search_term = st.text_input("Search term", "")
    
    with col3:
        rows_to_show = st.slider("Rows to display", 10, 500, 100)
    
    # Filter data
    if search_term:
        display_df = df[df[search_col].astype(str).str.contains(search_term, case=False, na=False)]
    else:
        display_df = df
    
    st.markdown(f"**Showing {min(rows_to_show, len(display_df)):,} of {len(display_df):,} records**")
    st.dataframe(display_df.head(rows_to_show), use_container_width=True)
    
    # Column statistics
    st.markdown("#### 📊 Column Statistics")
    
    stat_col = st.selectbox("Select column for statistics", df.columns.tolist())
    
    col1, col2 = st.columns(2)
    
    with col1:
        if df[stat_col].dtype in ['int64', 'float64']:
            stats = df[stat_col].describe()
            st.dataframe(stats, use_container_width=True)
        else:
            value_counts = df[stat_col].value_counts().head(10)
            st.markdown("**Top 10 Values**")
            st.dataframe(value_counts, use_container_width=True)
    
    with col2:
        if df[stat_col].dtype in ['int64', 'float64']:
            fig = px.histogram(df, x=stat_col, title=f"Distribution of {stat_col}", 
                             color_discrete_sequence=['#667eea'])
        else:
            value_counts = df[stat_col].value_counts().head(10)
            fig = px.bar(x=value_counts.index, y=value_counts.values, 
                        title=f"Top 10 {stat_col}", color_discrete_sequence=['#667eea'])
        
        fig.update_layout(
            template='plotly_dark',
            paper_bgcolor='rgba(0,0,0,0)',
            plot_bgcolor='rgba(0,0,0,0)'
        )
        st.plotly_chart(fig, width="stretch")
    
    # Correlation explorer
    st.markdown("#### 🔗 Quick Correlation Check")
    
    numeric_cols = df.select_dtypes(include=[np.number]).columns.tolist()
    
    if len(numeric_cols) >= 2:
        col1, col2 = st.columns(2)
        
        with col1:
            corr_col1 = st.selectbox("Column X", numeric_cols, key="corr1")
        with col2:
            corr_col2 = st.selectbox("Column Y", numeric_cols, index=1 if len(numeric_cols) > 1 else 0, key="corr2")
        
        corr_value = df[corr_col1].corr(df[corr_col2])
        st.metric(f"Correlation: {corr_col1} vs {corr_col2}", f"{corr_value:.4f}")
        
        # Scatter plot (sample for performance)
        sample_df = df.sample(min(1000, len(df)))
        fig = px.scatter(sample_df, x=corr_col1, y=corr_col2, 
                        title=f"Scatter: {corr_col1} vs {corr_col2}",
                        opacity=0.5)
        fig.update_layout(
            template='plotly_dark',
            paper_bgcolor='rgba(0,0,0,0)',
            plot_bgcolor='rgba(0,0,0,0)'
        )
        st.plotly_chart(fig, width="stretch")

# =====================
# TAB 10: GOALS & TARGETS
# =====================
with tab10:
    st.markdown('<div class="section-header"><h3>🎯 Goals & Target Tracking</h3></div>', unsafe_allow_html=True)
    
    st.markdown("Set your business targets and track progress")
    
    col1, col2 = st.columns(2)
    
    with col1:
        st.markdown("#### 📊 Set Targets")
        
        if sales_col:
            sales_target = st.number_input("Sales Target", value=int(df[sales_col].sum() * 1.2), step=10000)
        
        margin_target = st.number_input("Profit Margin Target (%)", value=50.0, step=1.0)
        
        ctr_target = st.number_input("CTR Target (%)", value=12.0, step=0.5)
        
        roi_target = st.number_input("Ad ROI Target", value=1.0, step=0.1)
    
    with col2:
        st.markdown("#### 📈 Current Progress")
        
        if sales_col:
            current_sales = df[sales_col].sum()
            sales_progress = min(100, (current_sales / sales_target) * 100) if sales_target > 0 else 0
            
            # Gauge chart for sales
            fig = go.Figure(go.Indicator(
                mode="gauge+number+delta",
                value=current_sales,
                delta={'reference': sales_target},
                title={'text': "Sales Progress", 'font': {'color': 'white'}},
                gauge={
                    'axis': {'range': [0, sales_target * 1.2]},
                    'bar': {'color': "#667eea"},
                    'bgcolor': "rgba(255,255,255,0.1)",
                    'steps': [
                        {'range': [0, sales_target * 0.5], 'color': "#f56565"},
                        {'range': [sales_target * 0.5, sales_target * 0.8], 'color': "#ecc94b"},
                        {'range': [sales_target * 0.8, sales_target], 'color': "#48bb78"}
                    ],
                    'threshold': {
                        'line': {'color': "white", 'width': 4},
                        'thickness': 0.75,
                        'value': sales_target
                    }
                }
            ))
            fig.update_layout(
                template='plotly_dark',
                paper_bgcolor='rgba(0,0,0,0)',
                height=250
            )
            st.plotly_chart(fig, use_container_width=True)
    
    # Progress bars for all targets
    st.markdown("#### 📋 Target Summary")
    
    targets_data = []
    
    if sales_col:
        targets_data.append({
            'Metric': 'Sales',
            'Target': f"{sales_target:,.0f}",
            'Current': f"{df[sales_col].sum():,.0f}",
            'Progress': f"{min(100, (df[sales_col].sum() / sales_target) * 100):.1f}%" if sales_target > 0 else "0%",
            'Status': "✅" if df[sales_col].sum() >= sales_target else "⏳"
        })
    
    if "Profit_Margin" in df.columns:
        current_margin = df['Profit_Margin'].mean()
        targets_data.append({
            'Metric': 'Profit Margin',
            'Target': f"{margin_target:.1f}%",
            'Current': f"{current_margin:.1f}%",
            'Progress': f"{min(100, (current_margin / margin_target) * 100):.1f}%" if margin_target > 0 else "0%",
            'Status': "✅" if current_margin >= margin_target else "⏳"
        })
    
    if "CTR" in df.columns:
        current_ctr = df['CTR'].mean()
        targets_data.append({
            'Metric': 'CTR',
            'Target': f"{ctr_target:.2f}%",
            'Current': f"{current_ctr:.2f}%",
            'Progress': f"{min(100, (current_ctr / ctr_target) * 100):.1f}%" if ctr_target > 0 else "0%",
            'Status': "✅" if current_ctr >= ctr_target else "⏳"
        })
    
    if "Ad_ROI" in df.columns:
        current_roi = df['Ad_ROI'].mean()
        targets_data.append({
            'Metric': 'Ad ROI',
            'Target': f"{roi_target:.2f}",
            'Current': f"{current_roi:.2f}",
            'Progress': f"{min(100, (current_roi / roi_target) * 100):.1f}%" if roi_target > 0 else "0%",
            'Status': "✅" if current_roi >= roi_target else "⏳"
        })
    
    if targets_data:
        targets_df = pd.DataFrame(targets_data)
        st.dataframe(targets_df, use_container_width=True)
    
    # Recommendations based on gaps
    st.markdown("#### 💡 Recommendations to Hit Targets")
    
    recommendations = []
    
    if sales_col and df[sales_col].sum() < sales_target:
        gap = sales_target - df[sales_col].sum()
        recommendations.append(f"📊 **Sales Gap**: Need {gap:,.0f} more sales. Consider increasing ad spend or running promotions.")
    
    if "Profit_Margin" in df.columns and df['Profit_Margin'].mean() < margin_target:
        recommendations.append("💰 **Margin Gap**: Focus on higher-margin products or reduce costs.")
    
    if "CTR" in df.columns and df['CTR'].mean() < ctr_target:
        recommendations.append("🖱️ **CTR Gap**: Improve ad creative and targeting to boost click-through rates.")
    
    if "Ad_ROI" in df.columns and df['Ad_ROI'].mean() < roi_target:
        recommendations.append("📢 **ROI Gap**: Optimize ad spend allocation - shift budget to better-performing campaigns.")
    
    if recommendations:
        for rec in recommendations:
            st.markdown(rec)
    else:
        st.success("🎉 Congratulations! You're meeting or exceeding all targets!")

# =====================
# TAB 11: CONVERSION & POSITION
# =====================
with tab11:
    st.markdown('<div class="section-header"><h3>🛒 Conversion & Position Analysis</h3></div>', unsafe_allow_html=True)
    
    if position_col:
        st.info("Search Position analysis has been moved to the Marketing tab.")

    if cart_col:
        st.markdown("---")
        st.markdown("#### 🛒 Cart Abandonment Analysis")
        
        total_carts = df[cart_col].sum()
        
        col1, col2, col3 = st.columns(3)
        with col1:
            st.metric("Total Abandoned Carts", f"{total_carts:,.0f}")
        
        with col2:
            avg_carts = df[cart_col].mean()
            st.metric("Avg Carts/Product", f"{avg_carts:.2f}")

        with col3:
            if price_col:
                lost_revenue = (df[cart_col] * df[price_col]).sum()
                st.metric("Est. Lost Revenue", f"${lost_revenue:,.0f}", help="Total Abandoned Carts * Price")
        
        # Sales vs Abandoned Carts Scatter
        if sales_col:
            sample_size = min(1000, len(df))
            chart_df = df.sample(sample_size)
            
            fig = px.scatter(
                chart_df, x=cart_col, y=sales_col,
                title=f"Sales vs Abandoned Carts (Sample {sample_size})",
                opacity=0.6,
                trendline="ols" if sample_size > 10 else None
            )
            apply_chart_style(fig)
            st.plotly_chart(fig, use_container_width=True)
            
            # Correlation
            corr = df[sales_col].corr(df[cart_col])
            st.info(f"💡 Correlation between Sales and Abandoned Carts: **{corr:.2f}**")

# =====================
# TAB 12: AUTO INSIGHTS
# =====================
with tab12:
    st.markdown('<div class="section-header"><h3>🤖 Auto Insight Engine</h3></div>', unsafe_allow_html=True)
    st.markdown("AI-powered analysis of your sales performance.")

    if st.button("🚀 Generate Insights"):
        with st.spinner("Analyzing data patterns..."):
            insights = auto_insight_engine(df, date_col, sales_col, inventory_col)
            
            if not insights:
                st.info("No significant anomalies or strong trends detected at this time.")
            else:
                for type_, title, msg in insights:
                    if type_ == "success":
                        st.success(f"**{title}**\n\n{msg}")
                    elif type_ == "warning":
                        st.warning(f"**{title}**\n\n{msg}")
                    elif type_ == "error":
                        st.error(f"**{title}**\n\n{msg}")
                    else:
                        st.info(f"**{title}**\n\n{msg}")

# =====================
# EXECUTIVE SUMMARY
# =====================
st.markdown('<div class="section-header"><h3>📝 Executive Summary</h3></div>', unsafe_allow_html=True)

col1, col2 = st.columns(2)

with col1:
    st.markdown("""
    <div class="info-box">
        <h4 style="color: #667eea; margin-top: 0;">🎯 Key Insights</h4>
        <ul style="color: #e2e8f0;">
    """, unsafe_allow_html=True)
    
    insights = []
    if sales_col:
        insights.append(f"<li>Total Sales Volume: <strong>{df[sales_col].sum():,.0f}</strong></li>")
    if "Profit_Margin" in df.columns:
        insights.append(f"<li>Average Profit Margin: <strong>{df['Profit_Margin'].mean():.1f}%</strong></li>")
    if "Ad_ROI" in df.columns:
        insights.append(f"<li>Average Ad ROI: <strong>{df['Ad_ROI'].mean():.2f}</strong> sales per dollar</li>")
    if date_col and sales_col:
        monthly = df.groupby(df[date_col].dt.to_period("M"))[sales_col].sum()
        volatility = monthly.pct_change().abs().mean() * 100
        insights.append(f"<li>Monthly Volatility: <strong>{volatility:.1f}%</strong></li>")
    
    st.markdown("".join(insights) + "</ul></div>", unsafe_allow_html=True)

with col2:
    st.markdown("""
    <div class="success-box">
        <h4 style="color: #48bb78; margin-top: 0;">✅ Recommendations</h4>
        <ol style="color: #e2e8f0;">
            <li>Focus on high-margin brands for profitability</li>
            <li>Reallocate marketing budget based on ROI</li>
            <li>Address low inventory turnover items</li>
            <li>Optimize pricing for price-sensitive segments</li>
            <li>Focus on Class A products for maximum impact</li>
        </ol>
    </div>
    """, unsafe_allow_html=True)

# =====================
# DATA EXPORT
# =====================
st.markdown('<div class="section-header"><h3>📥 Export Data</h3></div>', unsafe_allow_html=True)

col1, col2, col3 = st.columns(3)

with col1:
    if st.button("📊 Download Summary CSV", use_container_width=True):
        summary_data = {
            "Metric": ["Total Sales", "Total Records", "Avg Profit Margin", "Avg CTR", "Avg Ad ROI"],
            "Value": [
                df[sales_col].sum() if sales_col else 0,
                len(df),
                df["Profit_Margin"].mean() if "Profit_Margin" in df.columns else 0,
                df["CTR"].mean() if "CTR" in df.columns else 0,
                df["Ad_ROI"].mean() if "Ad_ROI" in df.columns else 0
            ]
        }
        summary_df = pd.DataFrame(summary_data)
        st.download_button(
            "⬇️ Download",
            summary_df.to_csv(index=False),
            "analysis_summary.csv",
            "text/csv"
        )

with col2:
    if brand_col and sales_col:
        if st.button("🏷️ Download Brand Report", use_container_width=True):
            brand_report = df.groupby(brand_col).agg({sales_col: 'sum'}).round(2)
            if "Profit_Margin" in df.columns:
                brand_report["Avg_Margin"] = df.groupby(brand_col)["Profit_Margin"].mean()
            if "Ad_ROI" in df.columns:
                brand_report["Avg_ROI"] = df.groupby(brand_col)["Ad_ROI"].mean()
            st.download_button(
                "⬇️ Download",
                brand_report.to_csv(),
                "brand_analysis.csv",
                "text/csv"
            )

with col3:
    if st.button("📋 Download Full Data", use_container_width=True):
        st.download_button(
            "⬇️ Download",
            df.to_csv(index=False),
            "full_data_with_metrics.csv",
            "text/csv"
        )

# Footer
st.markdown("""
<div style="text-align: center; padding: 40px 0 20px 0; color: #718096;">
    <p>Built with ❤️ using Streamlit | Sales Insight Dashboard v1.0</p>
</div>
""", unsafe_allow_html=True)