streamlit_app.py

import streamlit as st
import pandas as pd
import matplotlib
import matplotlib.pyplot  as plt
import plotly.express as px
import numpy as np
import plotly.graph_objects as go
import sqlite3
from typing import Optional, Dict, Any
from datetime import datetime, timedelta
import re
from pathlib import Path
# from blend_logic import run_dummy_prediction

##---- fucntions ------
# Load fuel data from CSV (create this file if it doesn't exist)
FUEL_CSV_PATH = "fuel_properties.csv"

def load_fuel_data():
    """Load fuel data from CSV or create default if not exists"""
    try:
        df = pd.read_csv(FUEL_CSV_PATH, index_col=0)
        return df.to_dict('index')
    except FileNotFoundError:
        # Create default fuel properties if file doesn't exist
        default_fuels = {
            "Gasoline": {f"Property{i+1}": round(0.7 + (i*0.02), 1) for i in range(10)},
            "Diesel": {f"Property{i+1}": round(0.8 + (i*0.02), 1) for i in range(10)},
            "Ethanol": {f"Property{i+1}": round(0.75 + (i*0.02), 1) for i in range(10)},
            "Biodiesel": {f"Property{i+1}": round(0.85 + (i*0.02), 1) for i in range(10)},
            "Jet Fuel": {f"Property{i+1}": round(0.78 + (i*0.02), 1) for i in range(10)}
        }
        pd.DataFrame(default_fuels).T.to_csv(FUEL_CSV_PATH)
        return default_fuels

# Initialize or load fuel data
if 'FUEL_PROPERTIES' not in st.session_state:
    st.session_state.FUEL_PROPERTIES = load_fuel_data()

def save_fuel_data():
    """Save current fuel data to CSV"""
    pd.DataFrame(st.session_state.FUEL_PROPERTIES).T.to_csv(FUEL_CSV_PATH)

# FUEL_PROPERTIES = st.session_state.FUEL_PROPERTIES

# ---------------------- Page Config ----------------------
st.set_page_config(
    layout="wide",
    page_title="Eagle Blend Optimizer",
    page_icon="🦅",
    initial_sidebar_state="expanded"
)

# ---------------------- Custom Styling ---------------------- ##e0e0e0;

st.markdown("""
    <style>
            
    .block-container {
        padding-top: 1rem;
    }
    /* Main app background */
    .stApp {
        background-color: #f8f5f0;
        overflow: visible;
        padding-top: 0
           
    }
        /* Remove unnecessary space at the top */     
   /* Remove any fixed headers */
    .stApp > header {
        position: static !important;
    }           
    
    /* Header styling */
    .header {
        background: linear-gradient(135deg, #654321 0%, #8B4513 100%);
        color: white;
        padding: 2rem 1rem;
        margin-bottom: 2rem;
        border-radius: 0 0 15px 15px;
        box-shadow: 0 4px 6px rgba(0, 0, 0, 0.1);
    }
    
    /* Metric card styling */
    .metric-card {
        background: #ffffff;  /* Pure white cards for contrast */
        border-radius: 10px;
        padding: 1.5rem;
        box-shadow: 0 2px 6px rgba(0, 0, 0, 0.15);
        height: 100%;
        transition: all 0.3s ease;
        border: 1px solid #CFB53B;
    }
    
    .metric-card:hover {
        transform: translateY(-3px);
        background: #FFF8E1;  /* Very light blue tint on hover */
        box-shadow: 0 4px 12px rgba(0, 0, 0, 0.2);
        border-color: #8B4513;
    }
    
    /* Metric value styling */
    .metric-value {
        color: #8B4513 !important;  /* Deep, vibrant blue */
        font-weight: 700;
        font-size: 1.8rem;
        text-shadow: 0 1px 2px rgba(0, 82, 204, 0.1);
    }
    
    /* Metric label styling */
    .metric-label {
        color: #654321;  /* Navy blue-gray */
        font-weight: 600;
        letter-spacing: 0.5px;
    }
    
    
    /* Metric delta styling */
    .metric-delta {
        color: #A67C52;  /* Medium blue-gray */
        font-size: 0.9rem;
        font-weight: 500;
    }
    
    /* Tab styling */
    /* Main tab container */
    .stTabs [data-baseweb="tab-list"] {
        display: flex;
        justify-content: center;
        gap: 6px;
        padding: 8px;
        margin: 0 auto;
        width: 95% !important;
    }
    
    /* Individual tabs */
    .stTabs [data-baseweb="tab"] {
        flex: 1;  /* Equal width distribution */
        min-width: 0;  /* Allows flex to work */
        height: 60px;  /* Fixed height or use aspect ratio */
        padding: 0 12px;
        margin: 0;
        font-weight: 600;
        font-size: 1rem;
        color: #654321;
        background: #FFF8E1;
        border: 2px solid #CFB53B;
        border-radius: 12px;
        transition: all 0.3s ease;
        display: flex;
        align-items: center;
        justify-content: center;
        text-align: center;
    }
    
    /* Hover state */
    .stTabs [data-baseweb="tab"]:hover {
        background: #FFE8A1;
        transform: translateY(-2px);
    }
    
    
    /* Active tab */
    .stTabs [aria-selected="true"] {
        background: #654321;
        color: #FFD700 !important;
        border-color: #8B4513;
        font-size: 1.05rem;
    }
    
    /* Icon sizing */
    .stTabs [data-baseweb="tab"] svg {
        width: 24px !important;
        height: 24px !important;
        margin-right: 8px !important;
    }
    
    /* Button styling */
    .stButton>button {
        background-color: #654321;
        color: #FFD700 !important;
        border-radius: 8px;
        padding: 0.5rem 1rem;
        transition: all 0.3s ease;
    }
    
    .stButton>button:hover {
        background-color: #8B4513;
        color: white;
    }
    
    /* Dataframe styling */
    .table-container {
            display: flex;
            justify-content: center;
            margin-top: 30px;
    }
    .table-inner {
            width: 50%;
    }


    @media only screen and (max-width: 768px) {
        .table-inner {
            width: 90%; /* For mobile */
        }
    }
            
    .stDataFrame {
        border-radius: 10px;
        box-shadow: 0 2px 4px rgba(0, 0, 0, 0.05);
        background-color:white !important;
        border: #CFB53B !important;
    }
    

    
    /* Section headers */
    .st-emotion-cache-16txtl3 {
        padding-top: 1rem;
    }
    
    /* Custom hr style */
    .custom-divider {
        border: 0;
        height: 1px;
        background: linear-gradient(90deg, transparent, #dee2e6, transparent);
        margin: 2rem 0;
    }
            
    /* Consistent chart styling --- THIS IS THE FIX --- */
    .stPlotlyChart {
        border-radius: 10px;

        padding: 15px;
        box-shadow: 0 2px 4px rgba(0, 0, 0, 0.05);
        margin-bottom: 25px;
    }

    
    /* Match number inputs */
    # .stNumberInput > div {
    #     padding: 0.25rem 0.5rem !important;
    # }

    #/* Better select widget alignment */
    # .stSelectbox > div {
    #     margin-bottom: -15px;
    # }
            
    
    .custom-uploader > label div[data-testid="stFileUploadDropzone"] {
        border: 2px solid #4CAF50;
        background-color: #4CAF50;
        color: white;
        padding: 0.6em 1em;
        border-radius: 0.5em;
        text-align: center;
        cursor: pointer;
    }
    .custom-uploader > label div[data-testid="stFileUploadDropzone"]:hover {
        background-color: #45a049;
    }

    

            
    
    /* Color scale adjustments */
    .plotly .colorbar {
        padding: 10px !important;
            color: #654321 !important;
    }   

    </style>
""", unsafe_allow_html=True)

# ---------------------- App Header ----------------------
st.markdown("""
    <div class="header">
        <h1 style='text-align: center; margin-bottom: 0.5rem;'>🦅 Eagle Blend Optimizer</h1>
        <h4 style='text-align: center; font-weight: 400; margin-top: 0;'>
            AI-Powered Fuel Blend Property Prediction & Optimization
        </h4>
    </div>
""", unsafe_allow_html=True)
#------ universal variables
 

# ---------------------- Tabs ----------------------
tabs = st.tabs([
    "📊 Dashboard",
    "🎛️ Blend Designer",
    "⚙️ Optimization Engine",
    "📤 Blend Comparison",
    "📚 Fuel Registry",
    "🧠 Model Insights"
])


def explode_blends_to_components(blends_df: pd.DataFrame,
                                 n_components: int = 5,
                                 keep_empty: bool = False,
                                 blend_name_col: str = "blend_name") -> pd.DataFrame:
    """
    Convert a blends DataFrame into a components DataFrame.

    Parameters
    ----------
    blends_df : pd.DataFrame
        DataFrame with columns following the pattern:
        Component1_fraction, Component1_Property1..Property10, Component1_unit_cost, ...
    n_components : int
        Number of components per blend (default 5).
    blend_name_col : str
        Column name in blends_df that stores the blend name.

    Returns
    -------
    pd.DataFrame
        components_df with columns:
        ['blend_name', 'component_name', 'component_fraction',
         'property1', ..., 'property10', 'unit_cost']
    """

    components_rows = []
    prop_names = [f"property{i}" for i in range(1, 11)]

    for _, blend_row in blends_df.iterrows():
        blend_name = blend_row.get(blend_name_col)
        # Fallback if blend_name is missing/empty - keep index-based fallback
        if not blend_name or str(blend_name).strip() == "":
            # use the dataframe index + 1 to create a fallback name
            blend_name = f"blend{int(blend_row.name) + 1}"

        for i in range(1, n_components + 1):
            # Build column keys
            frac_col = f"Component{i}_fraction"
            unit_cost_col = f"Component{i}_unit_cost"
            prop_cols = [f"Component{i}_Property{j}" for j in range(1, 11)]

            # Safely get values (if column missing, get NaN)
            comp_frac = blend_row.get(frac_col, np.nan)
            comp_unit_cost = blend_row.get(unit_cost_col, np.nan)
            comp_props = [blend_row.get(pc, np.nan) for pc in prop_cols]

            row = {
                "blend_name": blend_name,
                "component_name": f"{blend_name}_Component_{i}",
                "component_fraction": comp_frac,
                "unit_cost": comp_unit_cost
            }
            # add property1..property10
            for j, v in enumerate(comp_props, start=1):
                row[f"property{j}"] = v

            components_rows.append(row)

    components_df = pd.DataFrame(components_rows)

    return components_df

# --- Updated add_blends (now also populates components) ---
def add_blends(df, db_path="eagleblend.db", n_components=5):
    df = df.copy()

    # 1) Ensure blend_name column
    for col in list(df.columns):
        low = col.strip().lower()
        if low in ("blend_name", "blend name", "blendname"):
            if col != "blend_name":
                df = df.rename(columns={col: "blend_name"})
            break
    if "blend_name" not in df.columns:
        df["blend_name"] = pd.NA

    conn = sqlite3.connect(db_path)
    cur = conn.cursor()

    # 2) Determine next blend number
    cur.execute("SELECT blend_name FROM blends WHERE blend_name LIKE 'blend%'")
    nums = [int(m.group(1)) for (b,) in cur.fetchall() if (m := re.match(r"blend(\d+)$", str(b)))]
    start_num = max(nums) if nums else 0

    # 3) Fill missing blend_name
    mask = df["blend_name"].isna() | (df["blend_name"].astype(str).str.strip() == "")
    df.loc[mask, "blend_name"] = [f"blend{i}" for i in range(start_num + 1, start_num + 1 + mask.sum())]

    # 4) Safe insert into blends
    cur.execute("PRAGMA table_info(blends)")
    db_cols = [r[1] for r in cur.fetchall()]
    safe_df = df[[c for c in df.columns if c in db_cols]]
    if not safe_df.empty:
        safe_df.to_sql("blends", conn, if_exists="append", index=False)

    # 5) Explode blends into components and insert into components table
    components_df = explode_blends_to_components(df, n_components=n_components, keep_empty=False)
    cur.execute("PRAGMA table_info(components)")
    comp_cols = [r[1] for r in cur.fetchall()]
    safe_components_df = components_df[[c for c in components_df.columns if c in comp_cols]]
    if not safe_components_df.empty:
        safe_components_df.to_sql("components", conn, if_exists="append", index=False)

    conn.commit()
    conn.close()

    return {
        "blends_inserted": int(safe_df.shape[0]),
        "components_inserted": int(safe_components_df.shape[0])
    }


# --- add_components function ---
def add_components(df, db_path="eagleblend.db"):
    df = df.copy()

    # Ensure blend_name exists
    for col in list(df.columns):
        low = col.strip().lower()
        if low in ("blend_name", "blend name", "blendname"):
            if col != "blend_name":
                df = df.rename(columns={col: "blend_name"})
            break
    if "blend_name" not in df.columns:
        df["blend_name"] = pd.NA

    # Ensure component_name exists
    if "component_name" not in df.columns:
        df["component_name"] = pd.NA

    conn = sqlite3.connect(db_path)
    cur = conn.cursor()

    # Fill missing component_name
    mask = df["component_name"].isna() | (df["component_name"].astype(str).str.strip() == "")
    df.loc[mask, "component_name"] = [
        f"{bn}_Component_{i+1}"
        for i, bn in enumerate(df["blend_name"].fillna("blend_unknown"))
    ]

    # Safe insert into components
    cur.execute("PRAGMA table_info(components)")
    db_cols = [r[1] for r in cur.fetchall()]
    safe_df = df[[c for c in df.columns if c in db_cols]]
    if not safe_df.empty:
        safe_df.to_sql("components", conn, if_exists="append", index=False)

    conn.commit()
    conn.close()

    return int(safe_df.shape[0])

def get_blends_overview(db_path: str = "eagleblend.db", last_n: int = 5) -> Dict[str, Any]:
    """
    Returns:
      {
        "max_saving": float | None,          # raw numeric (PreOpt_Cost - Optimized_Cost)
        "last_blends": pandas.DataFrame,     # last_n rows of selected columns
        "daily_counts": pandas.Series        # counts per day, index = 'YYYY-MM-DD' (strings)
      }
    """
    last_n = int(last_n)
    comp_cols = [
        "blend_name", "Component1_fraction", "Component2_fraction", "Component3_fraction",
        "Component4_fraction", "Component5_fraction", "created_at"
    ]
    blend_props = [f"BlendProperty{i}" for i in range(1, 11)]
    select_cols = comp_cols + blend_props
    cols_sql = ", ".join(select_cols)

    with sqlite3.connect(db_path) as conn:
        # 1) scalar: max saving
        max_saving = conn.execute(
            "SELECT MAX(PreOpt_Cost - Optimized_Cost) "
            "FROM blends "
            "WHERE PreOpt_Cost IS NOT NULL AND Optimized_Cost IS NOT NULL"
        ).fetchone()[0]

        # 2) last N rows (only selected columns)
        q_last = f"""
            SELECT {cols_sql}
            FROM blends
            ORDER BY id DESC
            LIMIT {last_n}
        """
        df_last = pd.read_sql_query(q_last, conn)

        # 3) daily counts (group by date)
        q_counts = """
            SELECT date(created_at) AS day, COUNT(*) AS cnt
            FROM blends
            WHERE created_at IS NOT NULL
            GROUP BY day
            ORDER BY day DESC
        """
        df_counts = pd.read_sql_query(q_counts, conn)

    # Convert counts to a Series with day strings as index (fast, small memory)
    if not df_counts.empty:
        daily_counts = pd.Series(df_counts["cnt"].values, index=df_counts["day"].astype(str))
        daily_counts.index.name = "day"
        daily_counts.name = "count"
    else:
        daily_counts = pd.Series(dtype=int, name="count")

    return {"max_saving": max_saving, "last_blends": df_last, "daily_counts": daily_counts}


def get_activity_logs(db_path="eagleblend.db", timeframe="today", activity_type=None):
    """
    Get counts of activities from the activity_log table within a specified timeframe.

    Args:
        db_path (str): Path to the SQLite database file.
        timeframe (str): Time period to filter ('today', 'this_week', 'this_month', or 'custom').
        activity_type (str): Specific activity type to return count for. If None, return all counts.
    
    Returns:
        dict: Dictionary with counts per activity type OR a single integer if activity_type is specified.
    """
    # Calculate time filter
    now = datetime.now()
    if timeframe == "today":
        start_time = now.replace(hour=0, minute=0, second=0, microsecond=0)
    elif timeframe == "this_week":
        start_time = now - timedelta(days=now.weekday())  # Monday of this week
        start_time = start_time.replace(hour=0, minute=0, second=0, microsecond=0)
    elif timeframe == "this_month":
        start_time = now.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
    else:
        raise ValueError("Invalid timeframe. Use 'today', 'this_week', or 'this_month'.")

    # Query database
    conn = sqlite3.connect(db_path)
    query = f"""
        SELECT activity_type, COUNT(*) as count
        FROM activity_log
        WHERE timestamp >= ?
        GROUP BY activity_type
    """
    df_counts = pd.read_sql_query(query, conn, params=(start_time.strftime("%Y-%m-%d %H:%M:%S"),))
    conn.close()

    # Convert to dictionary
    counts_dict = dict(zip(df_counts["activity_type"], df_counts["count"]))

    # If specific activity requested
    if activity_type:
        return counts_dict.get(activity_type, 0)
    
    return counts_dict

# print(get_activity_logs(timeframe="today"))          # All activities today
# print(get_activity_logs(timeframe="this_week"))      # All activities this week
# print(get_activity_logs(timeframe="today", activity_type="optimization"))  # Only optimization count today

# result = get_activity_logs(timeframe="this_week")
# result['optimization']
# result['prediction']


def get_model(db_path="eagleblend.db"):
    """
    Fetch the last model from the models_registry table.
    
    Returns:
        pandas.Series: A single row containing the last model's data.
    """
    conn = sqlite3.connect(db_path)
    query = "SELECT * FROM models_registry ORDER BY id DESC LIMIT 1"
    df_last = pd.read_sql_query(query, conn)
    conn.close()
    
    if not df_last.empty:
        return df_last.iloc[0]  # Return as a Series so you can access columns easily
    else:
        return None


# last_model = get_model()
# if last_model is not None:
#     print("R2 Score:", last_model["R2_Score"])


# ----------------------------------------------------------------------------------------------------------------------------------------------
#                                                       Dashboard Tab
# ----------------------------------------------------------------------------------------------------------------------------------------------
with tabs[0]:
    import math
    import plotly.graph_objects as go

    # NOTE: Assuming these functions are defined elsewhere in your application
    # from your_utils import get_model, get_activity_logs, get_blends_overview

    # ---------- formatting helpers ----------
    def fmt_int(x):
        try:
            return f"{int(x):,}"
        except Exception:
            return "0"

    def fmt_pct_from_r2(r2):
        if r2 is None:
            return "—"
        try:
            v = float(r2)
            if v <= 1.5:
                v *= 100.0
            return f"{v:.1f}%"
        except Exception:
            return "—"

    def fmt_currency(x):
        try:
            return f"${float(x):,.2f}"
        except Exception:
            return "—"

    # ---------- pull live data (this_week only) ----------
    # This block is assumed to be correct and functional
    try:
        last_model = get_model()
    except Exception as e:
        last_model = None
        st.warning(f"Model lookup failed: {e}")

    try:
        activity_counts = get_activity_logs(timeframe="this_week")
    except Exception as e:
        activity_counts = {}
        st.warning(f"Activity log lookup failed: {e}")

    try:
        overview = get_blends_overview(last_n=5)
    except Exception as e:
        overview = {"max_saving": None, "last_blends": pd.DataFrame(), "daily_counts": pd.Series(dtype=int)}
        st.warning(f"Blends overview failed: {e}")


    r2_display = fmt_pct_from_r2(None if last_model is None else last_model.get("R2_Score"))
    preds = fmt_int(activity_counts.get("prediction", 0))
    opts = fmt_int(activity_counts.get("optimization", 0))
    max_saving_display = fmt_currency(overview.get("max_saving", None))

    # ---------- KPI cards ----------
    # FIXED: Replaced st.subheader with styled markdown for consistent color
    st.markdown('<h2 style="color:#4a2f1f; font-size:1.75rem;">Performance Summary</h2>', unsafe_allow_html=True)
    k1, k2, k3, k4 = st.columns(4)
    with k1:
        st.markdown(f"""
            <div class="metric-card" style="padding:10px;">
                <div class="metric-label">Model Accuracy</div>
                <div class="metric-value" style="font-size:1.3rem;">{r2_display}</div>
                <div class="metric-delta">R² (latest)</div>
            </div>
        """, unsafe_allow_html=True)
    with k2:
        st.markdown(f"""
            <div class="metric-card" style="padding:10px;">
                <div class="metric-label">Predictions Made</div>
                <div class="metric-value" style="font-size:1.3rem;">{preds}</div>
                <div class="metric-delta">This Week</div>
            </div>
        """, unsafe_allow_html=True)
    with k3:
        st.markdown(f"""
            <div class="metric-card" style="padding:10px;">
                <div class="metric-label">Optimizations</div>
                <div class="metric-value" style="font-size:1.3rem;">{opts}</div>
                <div class="metric-delta">This Week</div>
            </div>
        """, unsafe_allow_html=True)
    with k4:
        st.markdown(f"""
            <div class="metric-card" style="padding:10px;">
                <div class="metric-label">Highest Cost Savings</div>
                <div class="metric-value" style="font-size:1.3rem;">{max_saving_display}</div>
                <div class="metric-delta">Per unit fuel</div>
            </div>
        """, unsafe_allow_html=True)

    st.markdown('<div style="height:8px;"></div>', unsafe_allow_html=True)

    # ---------- Floating "How to Use" (bigger button + inline content) + compact CSS ----------
    st.markdown("""
    <style>
    /* Floating help - larger button and panel */
    #help-toggle{display:none;}
    .help-button{
        position:fixed; right:25px; bottom:25px; z-index:9999;
        background:#8B4513; color:#FFD700; padding:16px 22px; font-size:17px;
        border-radius:18px; font-weight:900; box-shadow:0 8px 22px rgba(0,0,0,0.2); cursor:pointer;
        border:0;
    }
    .help-panel{
        position:fixed; right:25px; bottom:100px; z-index:9998;
        width:520px; max-height:70vh; overflow-y:auto;
        background: linear-gradient(135deg, #FFFDF5 0%, #F8EAD9 100%);
        border:1px solid #CFB53B; border-radius:12px; padding:20px; box-shadow:0 14px 34px rgba(0,0,0,0.22);
        color:#4a2f1f; transform: translateY(12px); opacity:0; visibility:hidden; transition: all .22s ease-in-out;
    }
    #help-toggle:checked + label.help-button + .help-panel{
        opacity:1; visibility:visible; transform: translateY(0);
    }
    .help-panel .head{display:flex; justify-content:space-between; align-items:center; margin-bottom:12px}
    .help-panel .title{font-weight:900; color:#654321; font-size:16px}
    .help-close{background:#8B4513; color:#FFD700; padding:6px 10px; border-radius:8px; cursor:pointer; font-weight:800}
    .help-body{font-size:14.5px; color:#4a2f1f; line-height:1.5}
    .help-body b {color: #654321;}

    /* compact recent blends styles - improved font sizes */
    .recent-compact { padding-left:6px; padding-right:6px; }
    .compact-card{
        background: linear-gradient(180deg,#FFF8E1 0%, #FFF6EA 100%);
        border:1px solid #E3C77A; border-radius:8px; padding:10px; margin-bottom:8px; color:#654321;
        box-shadow: 0 2px 6px rgba(0,0,0,0.05);
    }
    .compact-top{display:flex; justify-content:space-between; align-items:center; margin-bottom:8px}
    .compact-name{font-weight:800; font-size:15px}
    .compact-ts{font-size:12px; color:#8B4513; opacity:0.95; font-weight:700}
    .comp-pills{font-size:12.5px; margin-bottom:8px}
    .comp-pill{
        display:inline-block; padding:3px 8px; margin-right:6px; margin-bottom: 4px; border-radius:999px;
        background:rgba(139,69,19,0.06); border:1px solid rgba(139,69,19,0.12);
        font-weight:700; color:#654321;
    }
    .props-inline{
        font-size:12px; color:#4a2f1f; white-space:nowrap; overflow:hidden; text-overflow:ellipsis;
    }
    .props-inline small{ font-size:11px; color:#4a2f1f; opacity:0.95; margin-right:8px; }
    </style>

    <input id="help-toggle" type="checkbox" />
    <label for="help-toggle" class="help-button">💬 How to Use</label>

    <div class="help-panel" aria-hidden="true">
        <div class="head">
            <div class="title">How to Use the Optimizer</div>
            <label for="help-toggle" class="help-close">Close</label>
        </div>
        <div class="help-body">
            <p><b>Performance Cards:</b> These show key metrics at a glance. "Model Accuracy" is the latest R² score. "Predictions" and "Optimizations" cover this week's activity. If a card shows "—", the underlying data may be missing.</p>
            <p><b>Blend Entries Chart:</b> This chart tracks how many new blends are created each day. Spikes can mean heavy usage or batch imports, while gaps might point to data ingestion issues.</p>
            <p><b>Recent Blends:</b> This is a live list of the newest blends. Each card displays the blend's name, creation time, component mix (C1-C5), and key properties (P1-P10). You can use the name and timestamp to find the full record in the database.</p>
            <p><b>Operational Tips:</b> For best results, use consistent naming for your blends. Ensure your data includes cost fields for savings to be calculated correctly. Consider retraining your model if its accuracy drops.</p>
        </div>
    </div>
    """, unsafe_allow_html=True)

    # ---------- Main split (adjusted for better balance) ----------
    left_col, right_col = st.columns([0.55, 0.45])

    # --- LEFT: Blend entries line chart ---
    with left_col:
        # FIXED: Replaced st.subheader with styled markdown for consistent color
        st.markdown('<h2 style="color:#4a2f1f; font-size:1.75rem;">Blend Entries Per Day</h2>', unsafe_allow_html=True)

        # Using DUMMY DATA as per original snippet for illustration
        today = pd.Timestamp.today().normalize()
        dates = pd.date_range(end=today, periods=14)
        ddf = pd.DataFrame({"day": dates, "Blends": np.array([2,3,1,5,6,2,4,9,3,4,2,1,5,6])})

        fig_daily = go.Figure()
        fig_daily.add_trace(go.Scatter(
            x=ddf["day"], y=ddf["Blends"],
            mode="lines+markers", line=dict(width=3, color="#8B4513"),
            marker=dict(size=6), name="Blends"
        ))
        fig_daily.add_trace(go.Scatter(
            x=ddf["day"], y=ddf["Blends"],
            mode="lines", line=dict(width=0), fill="tozeroy",
            fillcolor="rgba(207,181,59,0.23)", showlegend=False
        ))
        fig_daily.update_layout(
            title="Recent Blend Creation (preview)",
            xaxis_title="Date", yaxis_title="Number of Blends",
            plot_bgcolor="white", paper_bgcolor="white", # Set background to white
            margin=dict(t=40, r=10, b=36, l=50), # Tighter margins
            font=dict(color="#4a2f1f") # Ensure text color is not white
        )
        fig_daily.update_xaxes(gridcolor="rgba(139,69,19,0.12)", tickfont=dict(color="#654321"))
        fig_daily.update_yaxes(gridcolor="rgba(139,69,19,0.12)", tickfont=dict(color="#654321"))
        st.plotly_chart(fig_daily, use_container_width=True)

        # st.caption("Chart preview uses dummy data. To show live counts, uncomment the LIVE DATA block in the code.")

    # --- RIGHT: Compact Recent Blends (with larger fonts and clear timestamp) ---
    with right_col:
        st.markdown('<div class="recent-compact">', unsafe_allow_html=True)
        st.markdown('<div style="font-size: 1.15rem; font-weight:800; color:#654321; margin-bottom:12px;">🗒️ Recent Blends</div>', unsafe_allow_html=True)

        df_recent = overview['last_blends']   #get("last_blends", pd.DataFrame())
        if df_recent is None or df_recent.empty:
            st.info("No blends yet. Start blending today!")
        else:
            if "created_at" in df_recent.columns and not pd.api.types.is_datetime64_any_dtype(df_recent["created_at"]):
                with pd.option_context('mode.chained_assignment', None):
                    df_recent["created_at"] = pd.to_datetime(df_recent["created_at"], errors="coerce")

            for _, row in df_recent.iterrows():
                name = str(row.get("blend_name", "Untitled"))
                created = row.get("created_at", "")
                ts = "" if pd.isna(created) else pd.to_datetime(created).strftime("%Y-%m-%d %H:%M:%S")

                comp_html = ""
                for i in range(1, 6):
                    key = f"Component{i}_fraction"
                    val = row.get(key)
                    if val is None or (isinstance(val, float) and math.isnan(val)) or val == 0:
                        continue
                    comp_html += f'<span class="comp-pill">C{i}: {float(val)*100:.0f}%</span>'

                props = []
                for j in range(1, 11):
                    pj = row.get(f"BlendProperty{j}")
                    if pj is not None and not (isinstance(pj, float) and math.isnan(pj)):
                        props.append(f"P{j}:{float(pj):.3f}")
                props_html = " · ".join(props) if props else "No properties available."


                st.markdown(f"""
                    <div class="compact-card">
                        <div class="compact-top">
                            <div class="compact-name">{name}</div>
                            <div class="compact-ts">{ts}</div>
                        </div>
                        <div class="comp-pills">{comp_html}</div>
                        <div class="props-inline"><small>{props_html}</small></div>
                    </div>
                """, unsafe_allow_html=True)

        st.markdown('</div>', unsafe_allow_html=True)

# ----------------------------------------------------------------------------------------------------------------------------------------------
#                                                            Blend Designer Tab                                                  
# ----------------------------------------------------------------------------------------------------------------------------------------------

from inference import EagleBlendPredictor # Add this import at the top of your main script

# --- Add these new functions to your functions section ---

@st.cache_data
def get_components_from_db(db_path="eagleblend.db") -> pd.DataFrame:
    """Fetches component data, sorted by the most recent entries."""
    with sqlite3.connect(db_path) as conn:
        # Assuming 'id' or a timestamp column indicates recency. Let's use 'id'.
        query = "SELECT * FROM components ORDER BY id DESC"
        df = pd.read_sql_query(query, conn)
    return df

def log_activity(activity_type: str, details: str = "", db_path="eagleblend.db"):
    """Logs an activity to the activity_log table."""
    try:
        with sqlite3.connect(db_path) as conn:
            cur = conn.cursor()
            timestamp = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            cur.execute(
                "INSERT INTO activity_log (timestamp, activity_type) VALUES (?, ?)",
                (timestamp, activity_type)
            )
            conn.commit()
    except Exception as e:
        st.error(f"Failed to log activity: {e}")

# Instantiate the predictor once
if 'predictor' not in st.session_state:
    st.session_state.predictor = EagleBlendPredictor()

with tabs[1]:
    # --- State Initialization ---
    if 'prediction_made' not in st.session_state:
        st.session_state.prediction_made = False
    if 'prediction_results' not in st.session_state:
        st.session_state.prediction_results = None
    if 'preopt_cost' not in st.session_state:
        st.session_state.preopt_cost = 0.0
    if 'last_input_data' not in st.session_state:
        st.session_state.last_input_data = {}

    # --- Prediction & Saving Logic ---
    def handle_prediction():
        """
        Gathers data from UI, formats it, runs prediction, and stores results.
        """
        log_activity("prediction", "User ran a new blend prediction.")
        
        fractions = []
        properties_by_comp = [[] for _ in range(5)]
        unit_costs = []

        # 1. Gather all inputs from session state
        for i in range(5):
            frac = st.session_state.get(f"c{i}_fraction", 0.0)
            fractions.append(frac)
            unit_costs.append(st.session_state.get(f"c{i}_cost", 0.0))
            for j in range(1, 11):
                prop = st.session_state.get(f"c{i}_prop{j}", 0.0)
                properties_by_comp[i].append(prop)

        # 2. Validate weights
        if abs(sum(fractions) - 1.0) > 0.01:
            st.warning("⚠️ Total of component fractions must sum to 1.0.")
            st.session_state.prediction_made = False
            return
            
        # 3. Format DataFrame for the model
        model_input_data = {"blend_name": [st.session_state.get("blend_name", "Untitled Blend")]}
        # Add fractions first
        for i in range(5):
            model_input_data[f'Component{i+1}_fraction'] = [fractions[i]]
        # Add properties in the required order (interleaved)
        for j in range(10): # Property1, Property2, ...
            for i in range(5): # Component1, Component2, ...
                col_name = f'Component{i+1}_Property{j+1}'
                model_input_data[col_name] = [properties_by_comp[i][j]]

        df_model = pd.DataFrame(model_input_data)
        
        # 4. Run prediction
        predictor = st.session_state.predictor
        results = predictor.predict_all(df_model.drop(columns=['blend_name']))
        st.session_state.prediction_results = results[0]  # Get the first (and only) row of results
        
        # 5. Calculate cost
        st.session_state.preopt_cost = sum(f * c for f, c in zip(fractions, unit_costs))
        
        # 6. Store inputs for saving/downloading
        st.session_state.last_input_data = model_input_data
        
        st.session_state.prediction_made = True
        st.success("Prediction complete!")

    def handle_save_prediction():
        """Formats the last prediction's data and saves it to the database."""
        if not st.session_state.get('prediction_made', False):
            st.error("Please run a prediction before saving.")
            return

        # Prepare DataFrame in the format expected by `add_blends`
        save_df_data = st.session_state.last_input_data.copy()
        
        # Add blend properties and cost
        for i, prop_val in enumerate(st.session_state.prediction_results, 1):
            save_df_data[f'BlendProperty{i}'] = [prop_val]
        
        save_df_data['PreOpt_Cost'] = [st.session_state.preopt_cost]
        
        # Add unit costs
        for i in range(5):
            save_df_data[f'Component{i+1}_unit_cost'] = st.session_state.get(f'c{i}_cost', 0.0)
            
        save_df = pd.DataFrame(save_df_data)

        try:
            result = add_blends(save_df)
            log_activity("save_prediction", f"Saved blend: {save_df['blend_name'].iloc[0]}")
            st.success(f"Successfully saved blend '{save_df['blend_name'].iloc[0]}' to the database!")
        except Exception as e:
            st.error(f"Failed to save blend: {e}")


    # --- UI Rendering ---
    col_header = st.columns([0.8, 0.2])
    with col_header[0]:
        st.subheader("🎛️ Blend Designer")
    with col_header[1]:
        batch_blend = st.checkbox("Batch Blend Mode", value=False, key="batch_blend_mode")

    if batch_blend:
        st.subheader("📤 Batch Processing")
        uploaded_file = st.file_uploader("Upload CSV File", type=["csv"], key="Batch_upload")
        if uploaded_file:
            st.info("Batch processing functionality can be implemented here.")
            # Add batch processing logic here
    else:
        # --- Manual Blend Designer UI ---
        all_components_df = get_components_from_db()
        # st.text_input("Blend Name", "My New Blend", key="blend_name", help="Give your blend a unique name before saving.")
        # st.markdown("---")

        for i in range(5):
            # Unique keys for each widget within the component expander
            select_key = f"c{i}_select"
            name_key = f"c{i}_name"
            frac_key = f"c{i}_fraction"
            cost_key = f"c{i}_cost"

            # Check if a selection from dropdown was made
            if select_key in st.session_state and st.session_state[select_key] != "---":
                selected_name = st.session_state[select_key]
                comp_data = all_components_df[all_components_df['component_name'] == selected_name].iloc[0]
                
                # Auto-populate session state values
                st.session_state[name_key] = comp_data['component_name']
                st.session_state[frac_key] = comp_data.get('component_fraction', 0.2)
                st.session_state[cost_key] = comp_data.get('unit_cost', 0.0)
                for j in range(1, 11):
                    prop_key = f"c{i}_prop{j}"
                    st.session_state[prop_key] = comp_data.get(f'property{j}', 0.0)
                
                # Reset selectbox to avoid re-triggering
                st.session_state[select_key] = "---"

            with st.expander(f"**Component {i+1}**", expanded=(i==0)):
                # --- This is the placeholder for your custom filter ---
                # Example: Only show components ending with a specific number
                # filter_condition = all_components_df['component_name'].str.endswith(str(i + 1))
                # For now, we show all components
                filter_condition = pd.Series([True] * len(all_components_df), index=all_components_df.index)
                
                filtered_df = all_components_df[filter_condition]
                #component_options = ["---"] + filtered_df['component_name'].tolist() 
                component_options = ["---"] + [m for m in filtered_df['component_name'].tolist() if  m.endswith(f"Component_{i+1}") ]  

                st.selectbox(
                    "Load from Registry",
                    options=component_options,
                    key=select_key,
                    help="Select a saved component to auto-populate its properties."
                )

                c1, c2, c3 = st.columns([1.5, 2, 2])
                with c1:
                    st.text_input("Component Name", key=name_key)
                    st.number_input("Fraction", min_value=0.0, max_value=1.0, step=0.01, key=frac_key, format="%.3f")
                    st.number_input("Unit Cost ($)", min_value=0.0, step=0.01, key=cost_key, format="%.2f")
                with c2:
                    for j in range(1, 6):
                        st.number_input(f"Property {j}", key=f"c{i}_prop{j}", format="%.4f")
                with c3:
                    for j in range(6, 11):
                        st.number_input(f"Property {j}", key=f"c{i}_prop{j}", format="%.4f")
        
        st.markdown('<div style="height:10px;"></div>', unsafe_allow_html=True)
        # st.button("🧪 Predict Blended Properties", on_click=handle_prediction, use_container_width=True, type="primary")
        # --- FIX: Changed button call to prevent page jumping ---
        if st.button("🧪 Predict Blended Properties", use_container_width=False, type="primary"):
            handle_prediction()

        # --- Results Section ---
        if st.session_state.get('prediction_made', False):
            st.markdown('<hr class="custom-divider">', unsafe_allow_html=True)
            st.subheader("📈 Prediction Results")

            # KPI Cards for Cost and Blend Properties
            cost_val = st.session_state.get('preopt_cost', 0.0)
            results_array = st.session_state.get('prediction_results', np.zeros(10))

            st.markdown(f"""
                <div class="metric-card" style="border-color: #8B4513; background: #FFF8E1;">
                    <div class="metric-label">Predicted Blend Cost</div>
                    <div class="metric-value" style="color: #654321;">${cost_val:,.2f}</div>
                    <div class="metric-delta">Per unit fuel</div>
                </div>
            """, unsafe_allow_html=True)
            st.markdown('<div style="height:15px;"></div>', unsafe_allow_html=True)
            
            kpi_cols = st.columns(5)
            for i in range(10):
                with kpi_cols[i % 5]:
                    st.markdown(f"""
                        <div class="metric-card" style="margin-bottom: 10px;">
                            <div class="metric-label">Blend Property {i+1}</div>
                            <div class="metric-value">{results_array[i]:.4f}</div>
                        </div>
                    """, unsafe_allow_html=True)

            st.markdown('<hr class="custom-divider">', unsafe_allow_html=True)
            st.subheader("📊 Visualizations")
            
            v1, v2 = st.columns(2)
            with v1:
                # Pie Chart for fractions
                fractions = [st.session_state.get(f"c{i}_fraction", 0.0) for i in range(5)]
                labels = [st.session_state.get(f"c{i}_name", f"Component {i+1}") for i in range(5)]
                pie_fig = px.pie(
                    values=fractions, names=labels, title="Component Fractions",
                    hole=0.4, color_discrete_sequence=px.colors.sequential.YlOrBr_r
                )
                pie_fig.update_traces(textposition='inside', textinfo='percent+label')
                st.plotly_chart(pie_fig, use_container_width=True)

            with v2:
                # Bar Chart for property comparison
                prop_to_view = st.selectbox(
                    "Select Property to Visualize",
                    options=[f"Property{j}" for j in range(1, 11)],
                    key="viz_property_select"
                )
                prop_idx = int(prop_to_view.replace("Property", "")) - 1

                bar_values = [st.session_state.get(f"c{i}_prop{prop_idx+1}", 0.0) for i in range(5)]
                blend_prop_value = results_array[prop_idx]
                
                bar_labels = [f"Comp {i+1}" for i in range(5)] + ["Blend"]
                all_values = bar_values + [blend_prop_value]
                
                bar_df = pd.DataFrame({"Component": bar_labels, "Value": all_values})
                
                bar_fig = px.bar(
                    bar_df, x="Component", y="Value", title=f"Comparison for {prop_to_view}",
                    color="Component",
                    color_discrete_map={"Blend": "#654321"} # Highlight the blend property
                )
                bar_fig.update_layout(showlegend=False)
                st.plotly_chart(bar_fig, use_container_width=True)
            
            # --- Save and Download Buttons ---


            # --- FIX: New layout for saving and downloading ---
            save_col, download_col = st.columns(2)

            with save_col:
                # Move Blend Name input here
                st.text_input(
                    "Blend Name for Saving", 
                    "My New Blend", 
                    key="blend_name", 
                    help="Give your blend a unique name before saving."
                )
                st.button(
                    "💾 Save Prediction to Database", 
                    on_click=handle_save_prediction, 
                    use_container_width=True
                )

            with download_col:
                # Prepare CSV for download
                download_df = pd.DataFrame(st.session_state.last_input_data)
                # Use the blend_name from the input field for the file name
                file_name = st.session_state.get('blend_name', 'blend_results').replace(' ', '_')
                for i in range(5): # Add unit costs
                    download_df[f'Component{i+1}_unit_cost'] = st.session_state.get(f'c{i}_cost', 0.0)
                for i, res in enumerate(results_array, 1): # Add results
                    download_df[f'BlendProperty{i}'] = res
                
                csv_data = download_df.to_csv(index=False).encode('utf-8')
                
                st.download_button(
                    label="📥 Download Results as CSV",
                    data=csv_data,
                    file_name=f"{file_name}.csv",
                    mime='text/csv',
                    use_container_width=True,
                    # Move download button down slightly to align with save button
                    help="Download all inputs and predicted outputs to a CSV file."
    )
    # This empty markdown is a trick to add vertical space
    st.markdown('<div style="height: 36px;"></div>', unsafe_allow_html=True)

    # --- Floating "How to Use" button ---
    st.markdown("""
        <style>
            #help-toggle-designer{display:none;}
            .help-button-designer{
                position:fixed; right:25px; bottom:25px; z-index:999;
                background:#8B4513; color:#FFD700; padding:12px 18px;
                border-radius:50px; font-weight:bold; box-shadow:0 4px 12px rgba(0,0,0,0.2);
                cursor:pointer; border:0;
            }
            .help-panel-designer{
                display:none; position:fixed; right:25px; bottom:90px; z-index:998;
                width:450px; background: #FFFDF5; border:1px solid #CFB53B;
                border-radius:12px; padding:20px; box-shadow:0 8px 24px rgba(0,0,0,0.2);
                color:#4a2f1f;
            }
            #help-toggle-designer:checked ~ .help-panel-designer{display:block;}
        </style>
        <input id="help-toggle-designer" type="checkbox" />
        <label for="help-toggle-designer" class="help-button-designer">💬 How to Use</label>
        <div class="help-panel-designer">
            <h4 style="color:#654321; margin-top:0;">Using the Blend Designer</h4>
            <p><b>1. Name Your Blend:</b> Start by giving your new blend a unique name.</p>
            <p><b>2. Configure Components:</b> For each of the 5 components, you can either:</p>
            <ul>
                <li><b>Load from Registry:</b> Select a pre-saved component from the dropdown to automatically fill in all its properties.</li>
                <li><b>Manual Entry:</b> Manually type in the component name, its fraction in the blend, its unit cost, and its 10 physical properties.</li>
            </ul>
            <p><b>3. Predict:</b> Once all components are defined and their fractions sum to 1.0, click the <b>Predict</b> button. This will calculate the final blend's properties and cost.</p>
            <p><b>4. Analyze Results:</b> Review the KPI cards for the predicted properties and cost. Use the charts to visualize the blend's composition and compare component properties against the final blend.</p>
            <p><b>5. Save & Download:</b> If you are satisfied with the result, you can save the complete blend recipe to the database or download all the input and output data as a CSV file.</p>
        </div>
    """, unsafe_allow_html=True)

# ----------------------------------------------------------------------------------------------------------------------------------------------
#                                                   Optimization Engine Tab
# ----------------------------------------------------------------------------------------------------------------------------------------------

with tabs[2]:
    st.subheader("⚙️ Optimization Engine")
    
    # Pareto frontier demo
    st.markdown("#### Cost vs Performance Trade-off")
    np.random.seed(42)
    optimization_data = pd.DataFrame({
        'Cost ($/ton)': np.random.uniform(100, 300, 50),
        'Performance Score': np.random.uniform(70, 95, 50)
    })
    
    fig3 = px.scatter(
        optimization_data,
        x='Cost ($/ton)',
        y='Performance Score',
        title="Potential Blend Formulations",
        color='Performance Score',
        color_continuous_scale='YlOrBr'
    )
    
    # Add dummy pareto frontier
    x_pareto = np.linspace(100, 300, 10)
    y_pareto = 95 - 0.1*(x_pareto-100)
    fig3.add_trace(px.line(
        x=x_pareto,
        y=y_pareto,
        color_discrete_sequence= ['#8B4513', '#CFB53B', '#654321']
    ).data[0])
    
    fig3.update_layout(
        showlegend=False,
        annotations=[
            dict(
                x=200,
                y=88,
                text="Pareto Frontier",
                showarrow=True,
                arrowhead=1,
                ax=-50,
                ay=-30
            )
        ]
    )
    st.plotly_chart(fig3, use_container_width=True)
    
    # Blend optimization history
    st.markdown("#### Optimization Progress")
    iterations = np.arange(20)
    performance = np.concatenate([np.linspace(70, 85, 10), np.linspace(85, 89, 10)])
    
    fig4 = px.line(
        x=iterations,
        y=performance,
        title="Best Performance by Iteration",
        markers=True
    )
    fig4.update_traces(
        line_color='#1d3b58',
        marker_color='#2c5282',
        line_width=2.5
    )
    fig4.update_layout(
        yaxis_title="Performance Score",
        xaxis_title="Iteration"
    )
    st.plotly_chart(fig4, use_container_width=True)

# -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
#                                                   Blend Comparison Tab
# ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

with tabs[3]:
    st.subheader("📤 Nothing FOr NOw")
    # uploaded_file = st.file_uploader("Upload CSV File", type=["csv"])

    # if uploaded_file:
    #     df = pd.read_csv(uploaded_file)
    #     st.success("File uploaded successfully")
    #     st.dataframe(df.head())

    #     if st.button("⚙️ Run Batch Prediction"):
    #         result_df = df.copy()
    #         # result_df["Predicted_Property"] = df.apply(
    #         #     lambda row: run_dummy_prediction(row.values[:5], row.values[5:10]), axis=1
    #         # )
    #         st.success("Batch prediction completed")
    #         st.dataframe(result_df.head())
    #         csv = result_df.to_csv(index=False).encode("utf-8")
    #         st.download_button("Download Results", csv, "prediction_results.csv", "text/csv")

# ----------------------------------------------------------------------------------------------------------------------------------------------
#                                                   Fuel Registry Tab
# ---------------------------------------------------------------------------------------------------------------------------------------------


def load_data(table_name: str, db_path="eagleblend.db") -> pd.DataFrame:
    """Loads data from a specified table in the database."""
    try:
        conn = sqlite3.connect(db_path)
        # Assuming each table has a unique ID column as the first column
        query = f"SELECT * FROM {table_name}"
        df = pd.read_sql_query(query, conn)
        return df
    except Exception as e:
        st.error(f"Failed to load data from table '{table_name}': {e}")
        return pd.DataFrame()

def delete_records(table_name: str, ids_to_delete: list, id_column: str, db_path="eagleblend.db"):
    """Deletes records from a table based on a list of IDs."""
    if not ids_to_delete:
        return
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()
    try:
        placeholders = ','.join('?' for _ in ids_to_delete)
        query = f"DELETE FROM {table_name} WHERE {id_column} IN ({placeholders})"
        cur.execute(query, ids_to_delete)
        conn.commit()
    finally:
        conn.close()

@st.cache_data
def get_template(file_path):
    """Loads a template file into bytes for downloading."""
    with open(file_path, 'rb') as f:

        return f.read()

with tabs[4]:
    st.subheader("📚 Fuel Registry")
    st.write("Manage fuel components and blends. Add new entries manually, upload in batches, or download templates.")
    
    # --- State Initialization ---
    if 'components' not in st.session_state:
        st.session_state.components = load_data('components')
    if 'blends' not in st.session_state:
        st.session_state.blends = load_data('blends')

    # --- Section 1: Data Management (Uploads & Manual Entry) ---
    col1, col2 = st.columns(2)

    with col1:
        with st.container(border=True):
            st.markdown("#### ➕ Add Components")
            
            # Manual entry for a single component
            with st.expander("Add a Single Component Manually"):
                with st.form("new_component_form", clear_on_submit=True):
                    component_name = st.text_input("Component Name", placeholder="e.g., Reformate")
                    # Add inputs for other key properties of a component
                    # This example assumes a few common properties. Adjust as needed.
                    c_cols = st.columns(2)
                    component_fraction = c_cols[1].number_input("Component Fraction", value=0.0, step=0.1, format="%.2f")
                    property1 = c_cols[0].number_input("Property1", value=0.0, step=0.1, format="%.2f")
                    property2 = c_cols[1].number_input("Property2", value=0.0, step=0.1, format="%.2f")
                    property3 = c_cols[0].number_input("Property3", value=0.0, step=0.1, format="%.2f")
                    property4 = c_cols[1].number_input("Property4", value=0.0, step=0.1, format="%.2f")
                    property5 = c_cols[0].number_input("Property5", value=0.0, step=0.1, format="%.2f")
                    property6 = c_cols[1].number_input("Property6", value=0.0, step=0.1, format="%.2f")
                    property7 = c_cols[0].number_input("Property 7", value=0.0, step=0.1, format="%.2f")
                    property8 = c_cols[1].number_input("Property 8", value=0.0, step=0.1, format="%.2f")
                    property9 = c_cols[0].number_input("Property 9", value=0.0, step=0.1, format="%.2f")
                    property10 = c_cols[1].number_input("Property 10", value=0.0, step=0.1, format="%.2f")
                    unit_cost = c_cols[0].number_input("unit_cost", value=0.0, step=0.1, format="%.2f")
                    # property4 = c_cols[1].number_input("Unit Cost", value=0.0, step=0.1, format="%.2f")

                    if st.form_submit_button("💾 Save Component", use_container_width=True):
                        if not component_name.strip():
                            st.warning("Component Name cannot be empty.")
                        else:
                            new_component_df = pd.DataFrame([{
                                "component_name": component_name,
                                "RON": ron, "MON": mon, "RVP": rvp, "Cost": cost
                                # Add other properties here
                            }])
                            rows_added = add_components(new_component_df)
                            if rows_added > 0:
                                st.success(f"Component '{component_name}' added successfully!")
                                # Clear cache and rerun
                                del st.session_state.components
                                st.rerun()
            
            # Batch upload for components
            st.markdown("---")
            st.markdown("**Batch Upload Components**")
            uploaded_components = st.file_uploader(
                "Upload Components CSV", type=['csv'], key="components_uploader",
                help="Upload a CSV file with component properties."
            )
            if uploaded_components:
                try:
                    df = pd.read_csv(uploaded_components)
                    rows_added = add_components(df)
                    st.success(f"Successfully added {rows_added} new components to the registry!")
                    del st.session_state.components # Force reload
                    st.rerun()
                except Exception as e:
                    st.error(f"Error processing file: {e}")

            st.download_button(
                label="📥 Download Component Template",
                data=get_template('assets/components_template.csv'),
                file_name='components_template.csv',
                mime='text/csv',
                use_container_width=True
            )

    with col2:
        with st.container(border=True):
            st.markdown("#### 🧬 Add Blends")
            st.info("Upload blend compositions via CSV. Manual entry is not supported for blends.", icon="ℹ️")

            # Batch upload for blends
            uploaded_blends = st.file_uploader(
                "Upload Blends CSV", type=['csv'], key="blends_uploader",
                help="Upload a CSV file defining blend recipes."
            )
            if uploaded_blends:
                try:
                    df = pd.read_csv(uploaded_blends)
                    rows_added = add_blends(df) # Assumes you have an add_blends function
                    st.success(f"Successfully added {rows_added} new blends to the registry!")
                    del st.session_state.blends # Force reload
                    st.rerun()
                except Exception as e:
                    st.error(f"Error processing file: {e}")

            st.download_button(
                label="📥 Download Blend Template",
                data=get_template('assets/blends_template.csv'),
                file_name='blends_template.csv',
                mime='text/csv',
                use_container_width=True
            )
    
    st.divider()

    # --- Section 2: Data Display & Deletion ---
    st.markdown("#### 🔍 View & Manage Registry Data")
    
    view_col1, view_col2 = st.columns([1, 2])
    
    with view_col1:
        table_to_show = st.selectbox(
            "Select Table to View",
            ("Components", "Blends"),
            label_visibility="collapsed"
        )

    with view_col2:
        search_query = st.text_input(
            "Search Table",
            placeholder=f"Type to search in {table_to_show}...",
            label_visibility="collapsed"
        )
        
    # Determine which DataFrame to use
    if table_to_show == "Components":
        df_display = st.session_state.components.copy()
        id_column = "component_id" # Change if your ID column is named differently
    else:
        df_display = st.session_state.blends.copy()
        id_column = "blend_id" # Change if your ID column is named differently

    # Apply search filter if query is provided
    if search_query:
        # A simple search across all columns
        df_display = df_display[df_display.apply(
            lambda row: row.astype(str).str.contains(search_query, case=False).any(),
            axis=1
        )]
    
    if df_display.empty:
        st.warning(f"No {table_to_show.lower()} found matching your criteria.")
    else:
        # Add a "Select" column for deletion
        df_display.insert(0, "Select", False)
        
        # Use data_editor to make the checkboxes interactive
        edited_df = st.data_editor(
            df_display,
            hide_index=True,
            use_container_width=True,
            disabled=df_display.columns.drop("Select"), # Make all columns except "Select" read-only
            key=f"editor_{table_to_show}"
        )
        
        selected_rows = edited_df[edited_df["Select"]]
        
        if not selected_rows.empty:
            if st.button(f"❌ Delete Selected {table_to_show} ({len(selected_rows)})", use_container_width=True, type="primary"):
                ids_to_del = selected_rows[id_column].tolist()
                delete_records(table_to_show.lower(), ids_to_del, id_column)
                st.success(f"Deleted {len(ids_to_del)} records from {table_to_show}.")
                # Force a data refresh
                if table_to_show == "Components":
                    del st.session_state.components
                else:
                    del st.session_state.blends
                st.rerun()
    

# ----------------------------------------------------------------------------------------------------------------------------------------------
#                                               Model Insights Tab
# ----------------------------------------------------------------------------------------------------------------------------------------------
with tabs[5]:

    model_metrics = last_model[
        [f"BlendProperty{i}_Score" for i in range(1, 11)]
    ]

    # --- UI Rendering Starts Here ---

    # Inject CSS for consistent styling with the rest of the app
    st.markdown("""
    <style>
    /* Metric card styles */
    .metric-card {
        background: linear-gradient(180deg, #FFF8E1 0%, #FFF6EA 100%);
        border: 1px solid #E3C77A;
        border-radius: 8px;
        padding: 15px;
        text-align: center;
        color: #654321;
        box-shadow: 0 2px 6px rgba(0,0,0,0.05);
    }
    .metric-label {
        font-size: 14px;
        font-weight: 700;
        color: #8B4513;
        margin-bottom: 5px;
    }
    .metric-value {
        font-size: 1.8rem;
        font-weight: 900;
        color: #4a2f1f;
    }
    /* Floating help button and panel styles */
    #help-toggle{display:none;}
    .help-button{
        position:fixed; right:25px; bottom:25px; z-index:9999;
        background:#8B4513; color:#FFD700; padding:16px 22px; font-size:17px;
        border-radius:18px; font-weight:900; box-shadow:0 8px 22px rgba(0,0,0,0.2); cursor:pointer;
        border:0;
    }
    .help-panel{
        position:fixed; right:25px; bottom:100px; z-index:9998;
        width:520px; max-height:70vh; overflow-y:auto;
        background: linear-gradient(135deg, #FFFDF5 0%, #F8EAD9 100%);
        border:1px solid #CFB53B; border-radius:12px; padding:20px; box-shadow:0 14px 34px rgba(0,0,0,0.22);
        color:#4a2f1f; transform: translateY(12px); opacity:0; visibility:hidden; transition: all .22s ease-in-out;
    }
    #help-toggle:checked + label.help-button + .help-panel{
        opacity:1; visibility:visible; transform: translateY(0);
    }
    .help-panel .head{display:flex; justify-content:space-between; align-items:center; margin-bottom:12px}
    .help-panel .title{font-weight:900; color:#654321; font-size:16px}
    .help-close{background:#8B4513; color:#FFD700; padding:6px 10px; border-radius:8px; cursor:pointer; font-weight:800}
    .help-body{font-size:14.5px; color:#4a2f1f; line-height:1.5}
    .help-body b {color: #654321;}
    </style>
    """, unsafe_allow_html=True)

    # --- Floating "How to Use" Button and Panel ---
    st.markdown("""
    <input id="help-toggle" type="checkbox" />
    <label for="help-toggle" class="help-button">💬 How to Use</label>

    <div class="help-panel" aria-hidden="true">
        <div class="head">
            <div class="title">Interpreting Model Insights</div>
            <label for="help-toggle" class="help-close">Close</label>
        </div>
        <div class="help-body">
            <p><b>KPI Cards:</b> These four cards give you a quick summary of the model's overall health.</p>
            <ul>
                <li><b>Overall R² Score:</b> Think of this as the model's accuracy grade. A score of 92.4% means the model's predictions are highly accurate.</li>
                <li><b>MSE (Mean Squared Error):</b> This measures the average size of the model's mistakes. A smaller number is better.</li>
                <li><b>MAPE (Mean Absolute % Error):</b> This tells you the average error in percentage terms. A value of 0.112 means predictions are off by about 11.2% on average.</li>
            </ul>
            <p><b>R² Score by Blend Property Chart:</b> This chart shows how well the model predicts each specific property.</p>
            <p>A <b>longer bar</b> means the model is very good at predicting that property. A <b>shorter bar</b> indicates a property that is harder for the model to predict accurately. This helps you trust predictions for some properties more than others.</p>
        </div>
    </div>
    """, unsafe_allow_html=True)

    # --- Main Title ---
    st.markdown('<h2 style="color:#4a2f1f; font-size:1.75rem;">🧠 Model Insights</h2>', unsafe_allow_html=True)

    # --- Fetch Model Data ---
    latest_model = get_model()
    model_name = latest_model.get("model_name", "N/A")
    r2_score = f'{latest_model.get("R2_Score", 0) * 100:.1f}%'
    mse = f'{latest_model.get("MSE", 0):.3f}'
    mape = f'{latest_model.get("MAPE", 0):.3f}'

    # --- KPI Cards Section ---
    k1, k2, k3, k4 = st.columns(4)
    with k1:
        st.markdown(f"""
            <div class="metric-card">
                <div class="metric-label">Model Name</div>
                <div class="metric-value" style="font-size: 1.2rem; white-space: nowrap; overflow: hidden; text-overflow: ellipsis;">{model_name}</div>
            </div>
        """, unsafe_allow_html=True)
    with k2:
        st.markdown(f"""
            <div class="metric-card">
                <div class="metric-label">Overall R² Score</div>
                <div class="metric-value">{r2_score}</div>
            </div>
        """, unsafe_allow_html=True)
    with k3:
        st.markdown(f"""
            <div class="metric-card">
                <div class="metric-label">Mean Squared Error</div>
                <div class="metric-value">{mse}</div>
            </div>
        """, unsafe_allow_html=True)
    with k4:
        st.markdown(f"""
            <div class="metric-card">
                <div class="metric-label">Mean Absolute % Error</div>
                <div class="metric-value">{mape}</div>
            </div>
        """, unsafe_allow_html=True)

    st.markdown('<div style="height:20px;"></div>', unsafe_allow_html=True) # Spacer

    # --- R2 Score by Property Chart ---
    st.markdown('<h3 style="color:#4a2f1f; font-size:1.5rem;">R² Score by Blend Property</h3>', unsafe_allow_html=True)

    # Create the horizontal bar chart
    fig_r2 = go.Figure()

    fig_r2.add_trace(go.Bar(
        y=model_metrics.index,
        x=model_metrics.values,
        orientation='h',
        marker=dict(
            color=model_metrics.values,
            colorscale='YlOrBr',
            colorbar=dict(title="R² Score", tickfont=dict(color="#4a2f1f")),
        ),
        text=[f'{val:.2f}' for val in model_metrics.values],
        textposition='inside',
        insidetextanchor='middle',
        textfont=dict(color='#4a2f1f', size=12, family='Arial, sans-serif', weight='bold')
    ))

    # This corrected block resolves the ValueError
    fig_r2.update_layout(
        xaxis_title="R² Score (Higher is Better)",
        yaxis_title="Blend Property",
        plot_bgcolor='rgba(0,0,0,0)',
        paper_bgcolor='rgba(0,0,0,0)',
        margin=dict(l=10, r=10, t=20, b=50),
        font=dict(
            family="Segoe UI, Arial, sans-serif",
            size=12,
            color="#4a2f1f"
        ),
        yaxis=dict(
            tickfont=dict(size=12, weight='bold'),
            automargin=True,
            # FIX: The title font styling is now correctly nested here
            title_font=dict(size=14)
        ),
        xaxis=dict(
            gridcolor="rgba(139, 69, 19, 0.2)",
            zerolinecolor="rgba(139, 69, 19, 0.3)",
            # FIX: The title font styling is now correctly nested here
            title_font=dict(size=14)
        )
    )

    st.plotly_chart(fig_r2, use_container_width=True)




#     st.markdown("""
#     <style>
#     /* Consistent chart styling */
#     .stPlotlyChart {
#         border-radius: 10px;
#         background: white;
#         padding: 15px;
#         box-shadow: 0 2px 4px rgba(0, 0, 0, 0.05);
#         margin-bottom: 25px;
#     }
    
#     /* Better select widget alignment */
#     .stSelectbox > div {
#         margin-bottom: -15px;
#     }
    
#     /* Color scale adjustments */
#     .plotly .colorbar {
#         padding: 10px !important;
#     }
#     </style>
# """, unsafe_allow_html=True)