src/streamlit_app.py

import os
import streamlit as st
import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from datetime import datetime, timedelta
import time
from streamlit_autorefresh import st_autorefresh

# ==========================================
# 0. Password Authentication Function
# ==========================================
def check_password():
    """Returns True if the user had the correct password."""
    def password_entered():
        """Checks whether a password entered by the user is correct."""
        # Retrieves password from Hugging Face Space Secrets (defaults to admin if not found)
        correct_password = os.environ.get("APP_PASSWORD", "admin") 
        if st.session_state["password"] == correct_password:
            st.session_state["password_correct"] = True
            del st.session_state["password"]  # Clear session state for security
        else:
            st.session_state["password_correct"] = False

    if "password_correct" not in st.session_state:
        # Custom Dark Glassmorphism Login Box Styling
        st.markdown("""
            <style>
            .stApp { background: radial-gradient(circle at 50% 0%, #3a475a 0%, #1a202c 100%); color: #ffffff; }
            .login-box {
                background-color: rgba(255, 255, 255, 0.05);
                padding: 35px;
                border-radius: 15px;
                border: 1px solid #3498db;
                margin-top: 50px;
            }
            p, h3 { color: #ffffff !important; }
            </style>
        """, unsafe_allow_html=True)
        
        cols = st.columns([1, 2, 1])
        with cols[1]:
            st.markdown('<div class="login-box">', unsafe_allow_html=True)
            st.image("https://upload.wikimedia.org/wikipedia/commons/2/2b/Valeo_Logo.svg", width=150)
            st.subheader("🔐 Restricted Access Production Monitor")
            st.text_input("Please enter dashboard password:", type="password", on_change=password_entered, key="password")
            
            if "password_correct" in st.session_state and not st.session_state["password_correct"]:
                st.error("😕 Password incorrect. Please check your credentials.")
            st.markdown('</div>', unsafe_allow_html=True)
        return False
    return True

# --- Trigger Password Check Gateway ---
if not check_password():
    st.stop()  # Stop processing dashboard if user isn't logged in

# ==========================================
# 1. Page Configuration & Auto-Refresh
# ==========================================
st.set_page_config(
    page_title="Valeo | TASTOS FLEXILINE_1 CHO2", 
    page_icon="https://upload.wikimedia.org/wikipedia/commons/2/2b/Valeo_Logo.svg",
    layout="wide", 
    initial_sidebar_state="expanded"
)

# Auto-refresh every 30 seconds
st_autorefresh(interval=30 * 1000, limit=None, key="fproduction_counter")

st.markdown("""
    <style>
    .stApp { background: radial-gradient(circle at 50% 0%, #3a475a 0%, #1a202c 100%); color: #ffffff; }
    div[data-baseweb="select"] > div, div[data-baseweb="input"] > div,
    .stMarkdown, p, h1, h2, h3, h4, h5, h6, label { color: #ffffff !important; }
    [data-testid="stMetric"] { background: rgba(255, 255, 255, 0.05); border-radius: 12px; border: 1px solid rgba(255, 255, 255, 0.2); padding: 15px; }
    [data-testid="stMetricValue"] > div { color: #2ecc71 !important; }
    [data-testid="stMetricLabel"] > div { color: #ffffff !important; }
    .section-header { background: rgba(52, 152, 219, 0.15); padding: 10px; border-left: 5px solid #3498db; margin: 20px 0; border-radius: 0 10px 10px 0; }
    .footer { position: fixed; left: 0; bottom: 0; width: 100%; background: rgba(26, 32, 44, 0.95); color: #94a3b8; text-align: right; padding: 5px 20px; font-size: 12px; z-index: 999; }
    </style>
    """, unsafe_allow_html=True)

now_str = datetime.now().strftime("%H:%M:%S")
st.markdown(f'<div class="footer">PISSANU SRION ⚙️ | Last Update: {now_str} | Cloud Integrated Monitor</div>', unsafe_allow_html=True)

col1, col2 = st.columns([1, 4])
with col1: st.image("https://upload.wikimedia.org/wikipedia/commons/2/2b/Valeo_Logo.svg", width=220)
with col2: st.title("TASTOS FLEXILINE_1 CHO2 Dashboard")

# ==========================================
# 2. Station Configuration Constants
# ==========================================
ST_TABLES = [
    "dbo.ST010_2", "dbo.ST020_1", "dbo.ST020_2", "dbo.ST020_3", 
    "dbo.ST030_1", "dbo.ST030_2", "dbo.ST040_1", "dbo.ST040_2", 
    "dbo.ST050_2", "dbo.ST060_1", "dbo.ST060_2", "dbo.ST070_1", 
    "dbo.ST070_2", "dbo.ST070_3", "dbo.ST080_1", "dbo.ST080_2", 
    "dbo.ST080_3", "dbo.ST090_1", "dbo.ST090_2", "dbo.ST090_3", 
    "dbo.ST100_1", "dbo.ST100_2", "dbo.ST100_3", "dbo.ST110_1", "dbo.ST110_2"
]

STATION_CT_MAPPING = {
    "ST010": 21.0, "ST020": 21.0, "ST030": 21.0, "ST040": 21.0,
    "ST050": 21.0, "ST060": 21.0, "ST070": 21.0, "ST080": 21.0,
    "ST090": 21.0, "ST100": 21.0, "ST110": 21.0
}

# ==========================================
# 3. Data Loading & Reshaping (Deduplicated & Clean Pallets)
# ==========================================
def get_direct_url(url):
    if not url: return None
    ts = int(time.time()) 
    if "drive.google.com" in url:
        try:
            file_id = url.split("/d/")[1].split("/")[0]
            return f"https://drive.google.com/uc?export=download&id={file_id}&t={ts}"
        except: return url
    elif "docs.google.com/spreadsheets" in url:
        return url.split('/edit')[0] + f"/export?format=csv&t={ts}"
    return url

@st.cache_data(ttl=30)
def fetch_full_integrated_data(view_mode, sel_date, shift_mode):
    url = os.environ.get("ASSY_URL")
    if not url: return pd.DataFrame()
    direct_link = get_direct_url(url)
    
    df = None
    encodings = ['utf-8-sig', 'gbk', 'gb18030', 'utf-8']
    for enc in encodings:
        try:
            df = pd.read_csv(direct_link, sep=None, engine='python', on_bad_lines='skip', encoding=enc)
            df.columns = df.columns.str.strip()
            break
        except: continue
        
    if df is None or df.empty:
        return pd.DataFrame()
        
    # Standardize HousingCode column name dynamically
    target_name = 'Code产品条码'
    possible_cols = [c for c in df.columns if target_name in c or 'Code' in str(c) or '条码' in str(c)]
    housing_col = possible_cols[0] if possible_cols else 'HousingCode'
    if housing_col in df.columns:
        df = df.rename(columns={housing_col: 'HousingCode'})
    
    df['HousingCode'] = df['HousingCode'].astype(str).str.strip().str.upper()
    df = df.dropna(subset=['HousingCode'])
    
    # Re-synthesize separate station timestamps to map smoothly with layout analysis structure
    for table in ST_TABLES:
        t_clean = table.replace("dbo.", "")
        t_col = next((c for c in df.columns if t_clean in c and ('time' in c.lower() or '时间' in c)), None)
        if t_col:
            df[f'{t_clean}_EndTime'] = pd.to_datetime(df[t_col], errors='coerce')
        st_start_col = next((c for c in df.columns if t_clean in c and ('start' in c.lower() or 'starttime' in c)), None)
        if st_start_col:
            df[f'{t_clean}_StartTime'] = pd.to_datetime(df[st_start_col], errors='coerce')

    # --- REVISED: Safe Pallet Reconstruction Logic (Targets exact numeric Jig numbers) ---
    jig_no_cols = [c for c in df.columns if 'jig_no' in c.lower() or '夹具编号' in c]
    if jig_no_cols:
        # Clean decimals by converting numeric backfills to crisp plain integer string representations
        merged_jigs = df[jig_no_cols].apply(pd.to_numeric, errors='coerce').bfill(axis=1)
        if not merged_jigs.empty:
            df['PalletNo'] = merged_jigs.iloc[:, 0].dropna().apply(lambda x: str(int(x)))
        else:
            df['PalletNo'] = np.nan
    else:
        df['PalletNo'] = np.nan

    # Calculate overall row timestamp using the max known process end time
    time_cols_all = [c for c in df.columns if '_EndTime' in c]
    if time_cols_all:
        df['LogTime'] = df[time_cols_all].max(axis=1)
    else:
        df['LogTime'] = pd.to_datetime(datetime.now())
    df['LogTime'] = pd.to_datetime(df['LogTime'])
        
    # Date Window Selection & Shift Filtering Logic
    if view_mode == "Monthly (Days)":
        start_dt = datetime.combine(sel_date.replace(day=1), datetime.min.time())
        if sel_date.month == 12:
            end_dt = start_dt.replace(year=sel_date.year + 1, month=1)
        else:
            end_dt = start_dt.replace(month=sel_date.month + 1)
            
        df = df[(df['LogTime'] >= start_dt) & (df['LogTime'] < end_dt)]
        
        if shift_mode == "Day (09-21)":
            df = df[(df['LogTime'].dt.hour >= 9) & (df['LogTime'].dt.hour < 21)]
        elif shift_mode == "Night (21-09)":
            df = df[(df['LogTime'].dt.hour >= 21) | (df['LogTime'].dt.hour < 9)]
            
    else: # Daily Mode
        if shift_mode == "Day (09-21)":
            start_dt = datetime.combine(sel_date, datetime.min.time()).replace(hour=9)
            end_dt = start_dt + timedelta(hours=12)
        elif shift_mode == "Night (21-09)":
            start_dt = datetime.combine(sel_date, datetime.min.time()).replace(hour=21)
            end_dt = start_dt + timedelta(hours=12)
        else:
            start_dt = datetime.combine(sel_date, datetime.min.time())
            end_dt = start_dt + timedelta(days=1)
            
        df = df[(df['LogTime'] >= start_dt) & (df['LogTime'] < end_dt)]
        
    # Clean up double counting duplicates by keeping only the latest physical component sequence 
    df = df.sort_values('LogTime').drop_duplicates('HousingCode', keep='last')
    return df

# ==========================================
# 4. Sidebar Configuration Menu
# ==========================================
with st.sidebar:
    st.image("https://upload.wikimedia.org/wikipedia/commons/2/2b/Valeo_Logo.svg", width=150)
    st.markdown("### ⚙️ DASHBOARD CONFIG")
    view_mode = st.radio("🤔 View Mode:", ["Daily (Hours)", "Monthly (Days)"])
    sel_date = st.date_input("📅 Select Date:", value=datetime.now().date())
    shift_mode = st.selectbox("🕒 Shift:", ["All Day", "Day (09-21)", "Night (21-09)"])
    
    st.markdown("---")
    st.markdown("### 📊 CHOOSE DASHBOARD VIEW")
    analysis_menu = st.toggle("📈 STATION RUNTIME TIMELINE ANALYSIS", value=False)

raw_df = fetch_full_integrated_data(view_mode, sel_date, shift_mode)

if not raw_df.empty:
    res_cols = [c for c in raw_df.columns if 'Final_Result' in c or '总结果' in c]
    process_cols = [c for c in res_cols if 'ST110' not in c] 
    st110_cols = [c for c in res_cols if 'ST110' in c] 

    is_ng_process = raw_df[process_cols].apply(lambda x: x.astype(str).str.strip().str.upper() == 'NG').any(axis=1)
    raw_df['Summary_Result'] = np.where(is_ng_process, 'NG', 'OK')
    
    if st110_cols:
        ng_st110_count = raw_df[st110_cols].apply(lambda x: x.astype(str).str.strip().str.upper() == '--').any(axis=1).sum()
    else:
        ng_st110_count = 0

    if 'LogTime' in raw_df.columns and not raw_df['LogTime'].isna().all():
        raw_df['DateTime'] = pd.to_datetime(raw_df['LogTime'])
    else:
        time_cols = [c for c in raw_df.columns if '_EndTime' in str(c)]
        if time_cols:
            raw_df['DateTime'] = raw_df[time_cols].bfill(axis=1).iloc[:, 0]
        else:
            raw_df['DateTime'] = pd.to_datetime(datetime.now())

    raw_df['DateTime'] = pd.to_datetime(raw_df['DateTime']).fillna(datetime.combine(sel_date, datetime.min.time()))
    raw_df['Hour'] = raw_df['DateTime'].dt.hour
    raw_df['Day'] = raw_df['DateTime'].dt.day

    total = len(raw_df)
    ng_total = (raw_df['Summary_Result'] == 'NG').sum()
    trp_rate = ((total - ng_total) / total * 100) if total > 0 else 0
    
    avg_ct = 0
    if total > 1:
        times = raw_df['DateTime'].sort_values()
        diffs = times.diff().dt.total_seconds().dropna()
        valid_diffs = diffs[(diffs > 1) & (diffs < 500)]
        if not valid_diffs.empty: avg_ct = valid_diffs.mean()

    # ==========================================
    # VIEW MODE A: STANDARD LINE OVERVIEW
    # ==========================================
    if not analysis_menu:
        header_text = sel_date.strftime('%Y-%m') if view_mode == "Monthly (Days)" else sel_date
        st.markdown(f"<div class='section-header'><h2>📊 {view_mode.upper()} PERFORMANCE: {header_text} ({shift_mode})</h2></div>", unsafe_allow_html=True)
        
        r1, r2, r3, r4 = st.columns(4)
        r1.metric("TOTAL ASSY", f"{total:,} pcs")
        r2.metric("TOTAL NG (PROD)", f"{ng_total:,} pcs")
        r3.metric("ASSY NG", f"{ng_total:,} pcs")
        r4.metric("ST110 (LOAD to EOLT)", f"{ng_st110_count:,} pcs")
        
        r5, r6, r7, r8 = st.columns(4)
        r5.metric("TRP% [TARGET:85%]", f"{trp_rate:.2f} %")
        r6.metric("DLE%", "85.00 %")
        r7.metric("AVG CYCLE TIME", f"{avg_ct:.1f} sec" if avg_ct > 0 else "---")
        r8.metric("NG RATIO %", f"{(ng_total/total*100):.2f} %" if total > 0 else "0.00 %")

        st.markdown("<div class='section-header'><h3>🏭 PRODUCTION TREND ANALYSIS</h3></div>", unsafe_allow_html=True)
        
        if view_mode == "Monthly (Days)":
            x_col = 'Day'
            unique_days = raw_df['Day'].dropna().unique()
            target_order = sorted([int(d) for d in unique_days]) if len(unique_days) > 0 else list(range(1, 32))
        else:
            x_col = 'Hour' 
            if shift_mode == "Night (21-09)":
                target_order = [21, 22, 23, 0, 1, 2, 3, 4, 5, 6, 7, 8]
            elif shift_mode == "Day (09-21)":
                target_order = list(range(9, 21))
            else:
                target_order = list(range(24))

        if not raw_df.empty and len(raw_df.dropna(subset=[x_col])) > 0:
            grp = raw_df.groupby([x_col, 'Summary_Result']).size().unstack(fill_value=0).reset_index()
            for c in ['OK', 'NG']: 
                if c not in grp.columns: grp[c] = 0
            
            grp[x_col] = grp[x_col].astype(int)
            grp[x_col] = pd.Categorical(grp[x_col], categories=target_order, ordered=True)
            grp = grp.sort_values(x_col).dropna(subset=[x_col])
            x_axis_labels = [f"{int(i):02d}:00" if view_mode == "Daily (Hours)" else f"Day {int(i)}" for i in grp[x_col]]
            
            fig_t = go.Figure()
            fig_t.add_trace(go.Bar(x=x_axis_labels, y=grp['OK'], name='OK', marker_color='#2ecc71'))
            fig_t.add_trace(go.Bar(x=x_axis_labels, y=grp['NG'], name='NG', marker_color='#e74c3c'))
            fig_t.update_layout(template="plotly_dark", barmode='stack', height=400, paper_bgcolor='rgba(0,0,0,0)', plot_bgcolor='rgba(0,0,0,0)', xaxis=dict(type='category'))
            st.plotly_chart(fig_t, use_container_width=True)
        else:
            st.info("ℹ️ No production trend data available for this specific period calculation selection.")

        st.markdown("<div class='section-header'><h3>🎛️ LINE PALLET TRACKING & STATION NG ANALYSIS</h3></div>", unsafe_allow_html=True)
        
        # --- REVISED: Accurate Pallet Metrics Map and Sorter Setup ---
        if 'PalletNo' in raw_df.columns:
            active_pallets = raw_df['PalletNo'].dropna().astype(str).str.strip()
            active_pallets = active_pallets[(active_pallets != '') & (active_pallets != 'nan')]
            
            # Extract clean unique integers avoiding character slice logic errors
            unique_pallets = active_pallets.unique()
            active_pallets_sorted = sorted(list(unique_pallets), key=lambda x: int(x) if x.isdigit() else x)
            
            pallet_count = len(active_pallets_sorted)
            pallet_list_str = ", ".join(active_pallets_sorted) if pallet_count > 0 else "No active pallets detected"
        else:
            pallet_count = 0
            pallet_list_str = "Pallet column not found in database"

        p_stat1, p_stat2 = st.columns([1, 3])
        with p_stat1: st.metric("PALLETS ON LINE", f"{pallet_count} Units")
        with p_stat2:
            st.markdown(f"""<div style="background: rgba(255, 255, 255, 0.05); border-radius: 12px; border: 1px solid rgba(255, 255, 255, 0.2); padding: 15px; min-height: 83px;">
                <span style="color: #94a3b8; font-size: 14px; font-weight: bold; display: block; margin-bottom: 5px;">ACTIVE PALLET NUMBERS</span>
                <span style="color: #3498db; font-size: 18px; font-weight: bold; word-break: break-all;">{pallet_list_str}</span>
            </div>""", unsafe_allow_html=True)
        
        st.write("") 
        p_col1, p_col2 = st.columns(2)

        with p_col1:
            st.subheader("Station NG Ranking")
            if ng_total > 0:
                st_ng = (raw_df[raw_df['Summary_Result'] == 'NG'][process_cols].apply(lambda x: x.astype(str).str.strip().str.upper() == 'NG')).sum()
                df_p = st_ng.reset_index().rename(columns={'index': 'Station', 0: 'Count'}).sort_values('Count', ascending=False)
                df_p = df_p[df_p['Count'] > 0]
                if not df_p.empty:
                    df_p['Station'] = df_p['Station'].str.replace('_Final_Result', '').str.replace('总结果', '')
                    fig_st = px.bar(df_p, x='Station', y='Count', text='Count', color_discrete_sequence=['#e74c3c'], template="plotly_dark")
                    st.plotly_chart(fig_st, use_container_width=True)
                else: st.info("No explicit failure records flagged inside station result rows.")
            else: st.success("✅ No Process NG found.")

        with p_col2:
            st.subheader("Pallet NG Distribution")
            if 'PalletNo' in raw_df.columns and ng_total > 0:
                chart_df = raw_df[raw_df['Summary_Result'] == 'NG'].copy()
                # --- REVISED: Group directly on clean unsliced integers ---
                chart_df['Pallet_Clean'] = chart_df['PalletNo'].dropna().astype(str).str.strip()
                pallet_ng = chart_df.groupby('Pallet_Clean').size().reset_index(name='NG_Count').sort_values('NG_Count', ascending=False)
                if not pallet_ng.empty:
                    fig_pal = px.bar(pallet_ng, x='Pallet_Clean', y='NG_Count', text='NG_Count', color='NG_Count', color_continuous_scale='Reds', template="plotly_dark")
                    fig_pal.update_layout(xaxis_title="Pallet Number")
                    st.plotly_chart(fig_pal, use_container_width=True)
                else: st.info("No Pallet data linked to NG cases.")
            else: st.info("Waiting for NG data or Pallet column...")

    # ==========================================
    # VIEW MODE B: SEPARATE STATION RUNTIME TIMELINE ANALYSIS
    # ==========================================
    else:
        st.markdown("<div class='section-header'><h2>📈 SEPARATE STATION RUNTIME & TIMELINE STATUS MONITOR</h2></div>", unsafe_allow_html=True)
        
        timeline_data = []
        for table in ST_TABLES:
            t_clean = table.replace("dbo.", "")
            end_col = f"{t_clean}_EndTime"
            start_col = f"{t_clean}_StartTime"
            
            st_prefix = t_clean.split('_')[0]
            current_target_ct = STATION_CT_MAPPING.get(st_prefix, 15.0)
            
            if end_col in raw_df.columns:
                sub_df = raw_df.dropna(subset=[end_col]).copy()
                if not sub_df.empty:
                    if start_col in sub_df.columns and not sub_df[start_col].isna().all():
                        sub_df['Duration'] = (sub_df[end_col] - sub_df[start_col]).dt.total_seconds()
                    else:
                        sub_df = sub_df.sort_values(end_col)
                        sub_df['Duration'] = sub_df[end_col].diff().dt.total_seconds()
                    
                    def categorize_status(row_duration):
                        if row_duration <= current_target_ct:
                            return "🟢 Running (On Target)"
                        elif current_target_ct < row_duration <= 300:
                            return "🟡 Waiting / Slow Cycle"
                        else:
                            return "🔴 Breakdown / Stop"
                    
                    sub_df['Status'] = sub_df['Duration'].apply(categorize_status)
                    for _, row in sub_df.iterrows():
                        timeline_data.append({
                            "Station": t_clean,
                            "LogTime": row[end_col],
                            "Cycle Time (s)": round(max(0.1, row['Duration']), 1),
                            "Target CT (s)": current_target_ct,
                            "Status": row['Status'],
                            "HousingCode": row['HousingCode']
                        })

        if timeline_data:
            df_timeline = pd.DataFrame(timeline_data).sort_values(by="LogTime")
            st.markdown("### 🕒 Real-Time Station Status Timeline Map")
            
            fig_timeline = px.scatter(
                df_timeline,
                x="LogTime", y="Station", color="Status",
                hover_data=["HousingCode", "Cycle Time (s)", "Target CT (s)"],
                color_discrete_map={
                    "🟢 Running (On Target)": "#2ecc71",
                    "🟡 Waiting / Slow Cycle": "#f1c40f",
                    "🔴 Breakdown / Stop": "#e74c3c"
                },
                title="Line Part Sequencing & Flow Analysis Across All Stations",
                template="plotly_dark", height=500
            )
            fig_timeline.update_traces(marker=dict(size=12, symbol="square"))
            fig_timeline.update_layout(
                xaxis_title="Time of Day", yaxis_title="Station Code", legend_title="Machine State",
                yaxis={'categoryorder': 'array', 'categoryarray': sorted(df_timeline['Station'].unique(), reverse=True)}
            )
            st.plotly_chart(fig_timeline, use_container_width=True)
            
            st.markdown("---")
            c_break1, c_break2 = st.columns([2, 1])
            
            with c_break1:
                st.markdown("### 📊 Accumulated Time Distribution per Station")
                fig_status_distribution = px.histogram(
                    df_timeline, x="Station", color="Status", barmode="stack",
                    color_discrete_map={
                        "🟢 Running (On Target)": "#2ecc71",
                        "🟡 Waiting / Slow Cycle": "#f1c40f",
                        "🔴 Breakdown / Stop": "#e74c3c"
                    },
                    template="plotly_dark", height=380
                )
                st.plotly_chart(fig_status_distribution, use_container_width=True)
                
            with c_break2:
                st.markdown("### 🚨 Top Bottleneck / Waiting Logs")
                slow_logs = df_timeline[df_timeline['Status'] != "🟢 Running (On Target)"].sort_values(by="Cycle Time (s)", ascending=False)
                if not slow_logs.empty:
                    st.dataframe(
                        slow_logs[["Station", "LogTime", "Cycle Time (s)", "HousingCode"]].head(10),
                        use_container_width=True, hide_index=True
                    )
                else:
                    st.success("✅ Excellent! All stations currently passing parts inside clean target values.")
        else:
            st.info("ℹ️ No log time stamps discovered to parse separate station cycle sequences.")

    # ==========================================
    # 6. Global Expanders Section
    # ==========================================
    st.markdown("---")
    with st.expander("🔍 STATION ERROR DEEP-DIVE: Identify Specific Parameter NGs"):
        if view_mode == "Monthly (Days)":
            st.warning("⚠️ Parameter deep-dive is optimized for Daily view mode to maintain performance runtime limits.")
        elif ng_total > 0:
            st.markdown("##### 1. Select an NG Station to Inspect")
            ng_stations = []
            for col in process_cols:
                if col in raw_df.columns and raw_df[raw_df['Summary_Result'] == 'NG'][col].astype(str).str.strip().str.upper().eq('NG').any():
                    clean_name = col.replace('_Final_Result', '').replace('总结果', '')
                    ng_stations.append((clean_name, col))
            
            if ng_stations:
                station_mapping = dict(ng_stations)
                selected_st_clean = st.selectbox("Select Station with Failures:", list(station_mapping.keys()), key="ng_dive_st")
                target_final_col = station_mapping[selected_st_clean]
                
                st_failed_df = raw_df[raw_df[target_final_col].astype(str).str.strip().str.upper() == 'NG'].copy()
                station_prefix = selected_st_clean.split('_')[0]
                all_station_columns = [c for c in raw_df.columns if str(c).startswith(station_prefix)]
                
                core_display_cols = ['HousingCode', 'LogTime']
                if 'PalletNo' in raw_df.columns: core_display_cols.append('PalletNo')
                    
                param_cols = [c for c in all_station_columns if c not in [target_final_col, 'HousingCode', 'LogTime', 'PalletNo', 'Summary_Result', 'DateTime', 'Hour', 'Day']]
                final_display_view = core_display_cols + param_cols
                
                st.markdown(f"##### 2. Failed Parts Log for {selected_st_clean} ({len(st_failed_df)} items found)")
                available_cols = [c for c in final_display_view if c in st_failed_df.columns]
                st.dataframe(st_failed_df[available_cols].sort_values('LogTime', ascending=False), use_container_width=True)
            else: st.info("No active station records matching strict 'NG' conditions in current views.")
        else: st.success("✅ Clean Pass! No items have an NG status during this shift timeframe.")

    with st.expander("📈 CLICK TO VIEW: Process Parameter Analysis"):
        if view_mode == "Monthly (Days)": st.warning("⚠️ Distribution profiling graphs are available when using Daily mode.")
        else:
            all_cols = raw_df.columns.tolist()
            stations = sorted(list(set([str(c).split('_')[0] for c in all_cols if 'ST' in str(c)])))
            ca, cb = st.columns(2)
            with ca: sel_st = st.selectbox("📌 Select Station:", stations)
            st_cols = [c for c in all_cols if str(c).startswith(sel_st)]
            params = [c for c in st_cols if not any(k in str(c) for k in ['Result', 'Time', 'Code', 'UID', '时间', '结果'])]
            with cb: sel_param = st.selectbox("📊 Select Parameter:", params) if params else None
            if sel_param:
                pallet_col_box = next((c for c in st_cols if any(k in c for k in ['Jig_No', '编号', 'Pallet'])), None)
                plot_df = raw_df.dropna(subset=[sel_param]).copy()
                plot_df[sel_param] = pd.to_numeric(plot_df[sel_param], errors='coerce')
                fig_box = px.box(plot_df, x=pallet_col_box, y=sel_param, color=pallet_col_box, template="plotly_dark")
                st.plotly_chart(fig_box, use_container_width=True)

    with st.expander("🔍 CLICK TO VIEW: Search & Data Table"):
        search_q = st.text_input("Quick Search Housing Code:", key="bottom_search").strip().upper()
        disp_df = raw_df.copy()
        if search_q: disp_df = disp_df[disp_df['HousingCode'].str.contains(search_q, na=False)]
        st.dataframe(disp_df.sort_values('DateTime', ascending=False), use_container_width=True)

else:
    st.error("❌ ไม่พบข้อมูลสำหรับช่วงเวลาที่เลือก หรือกรุณาตรวจสอบการตั้งค่า ASSY_URL ใน Repository Secrets")