app.py

import streamlit as st
import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import folium
from streamlit_folium import st_folium
from sklearn.linear_model import LinearRegression

# ================= CONFIG
st.set_page_config(
    page_title="Michelin Mining Tyre Analytics",
    page_icon="",
    layout="wide",
    initial_sidebar_state="expanded"
)

# ================= CUSTOM CSS 
st.markdown("""
<style>
/* ================= ROOT & COLORS ================= */
:root {
    --michelin-blue: #003A8F;
    --surface: #FFFFFF;
    --surface-alt: #F9FAFB;
    --text-dark: #1F2D3D;
    --text-muted: #6C757D;
    --border: #E9ECEF;
    --shadow-sm: 0 2px 6px rgba(0,0,0,0.04);
    --shadow: 0 6px 16px rgba(0,0,0,0.06);
    --accent-yellow: #FFD100;
    --filter-bg: #F5F7FA;
}

/* ================= GLOBAL TYPOGRAPHY & ALIGNMENT ================= */
.stApp {
    background-color: var(--surface);
    font-family: 'Segoe UI', system-ui, sans-serif;
    color: var(--text-dark);
}

/* Force center alignment for ALL headings */
h1, h2, h3, h4, h5, h6,
.stMarkdown, .stText, p, div[data-testid="stMarkdownContainer"],
label, .stSelectbox label, .stMultiselect label, .stCheckbox label {
    text-align: center !important;
}

/* Fix bullet/number list centering */
.stMarkdown ul, .stMarkdown ol {
    text-align: left !important;
    margin-left: auto;
    margin-right: auto;
    max-width: 800px;
}

/* ================= SIDEBAR (Power BI Style) ================= */
[data-testid="stSidebar"] {
    background: var(--filter-bg) !important;
    color: var(--text-dark);
    padding: 20px 12px;
}

[data-testid="stSidebar"] h3 {
    color: var(--michelin-blue);
    font-weight: 700;
    margin-bottom: 12px;
}

/* Power BI-style dropdowns */
[data-testid="stSelectbox"] div[data-baseweb="select"],
[data-testid="stMultiselect"] div[data-baseweb="select"] {
    background-color: white !important;
    border-radius: 8px !important;
    border: 1px solid var(--border) !important;
    box-shadow: var(--shadow-sm);
    padding: 6px 10px !important;
    min-height: 40px !important;
}

[data-testid="stSelectbox"] div[data-baseweb="select"] > div,
[data-testid="stMultiselect"] div[data-baseweb="select"] > div {
    color: var(--text-dark) !important;
    font-weight: 500;
}

/* Remove red tags from multiselect */
[data-testid="stMultiselect"] div[data-baseweb="select"] .stMultiSelectTag {
    display: none !important;
}

/* Submit button */
[data-testid="stSidebar"] .stButton > button {
    width: 100%;
    background: var(--accent-yellow);
    color: var(--michelin-blue);
    font-weight: 700;
    border-radius: 10px;
    padding: 12px 0;
    margin-top: 16px;
    box-shadow: var(--shadow);
    border: none;
    font-size: 1.05rem;
}
[data-testid="stSidebar"] .stButton > button:hover {
    background: #FFC107;
    transform: translateY(-1px);
    box-shadow: 0 8px 16px rgba(0,0,0,0.12);
}

/* ================= HEADER ================= */
.main-header h1 {
    font-size: 2.4rem;
    margin-bottom: 6px;
    font-weight: 800;
    color: var(--michelin-blue);
}
.main-header p {
    font-size: 1.15rem;
    color: var(--text-muted);
    margin-top: 0;
}

/* ================= OBJECTIVE TITLE (NO BACKGROUND BOX) ================= */
.objective-title {
    text-align: center !important;
    font-size: 1.6rem;
    font-weight: 800;
    color: var(--michelin-blue);
    margin: 40px 0 24px 0;
}

/* ================= INSIGHT LLM-STYLE (Like Screenshot) ================= */
.insight-box {
    background: var(--surface-alt);
    border: 1px solid var(--border);
    border-radius: 12px;
    padding: 20px;
    box-shadow: var(--shadow-sm);
    margin: 20px 0 30px 0;
    position: relative;
    display: flex;
    align-items: flex-start;
    gap: 12px;
}

.insight-box .content {
    flex: 1;
    font-size: 1.05rem;
    line-height: 1.65;
    color: var(--text-dark);
    text-align: left;
}

.insight-box .tag {
    position: absolute;
    top: 12px;
    right: 16px;
    background: var(--michelin-blue);
    color: white;
    font-size: 0.85rem;
    font-weight: 700;
    padding: 6px 12px;
    border-radius: 8px;
    letter-spacing: 0.5px;
}

/* ================= PLOTLY ================= */
.plotly-graph-div {
    border-radius: 12px;
    overflow: hidden;
    box-shadow: var(--shadow-sm);
    border: 1px solid var(--border);
}

/* ================= LOGO (Perbaikan: PLN Style - Header Right) ================= */
.logo-pln {
    display: flex;
    align-items: center;
    justify-content: flex-end;
    width: 150px;
    height: auto;
    margin-left: auto;
}

/* ================= FOOTER ================= */
.footer {
    text-align: center;
    font-size: 0.9rem;
    color: var(--text-muted);
    margin-top: 50px;
    padding: 20px 0;
    border-top: 1px solid var(--border);
}

/* ================= STREAMLIT TWEAKS ================= */
div.block-container {
    padding-top: 2rem;
}
section[data-testid="stSidebar"] {
    width: 280px !important;
    min-width: 280px !important;
}
</style>
""", unsafe_allow_html=True)


# ================= LOAD 
@st.cache_data
def load_data():
    try:
        df = pd.read_excel("df_final.xlsx", sheet_name="Sheet1")
    except FileNotFoundError:
        st.error("❌ File `df_final.xlsx` not found. Please ensure it's in the same directory.")
        st.stop()
    
    # Fix encoding (e.g., '°C' → '°C')
    df.columns = df.columns.str.replace("Â", "")
    for col in df.select_dtypes(include='object').columns:
        df[col] = df[col].astype(str).str.replace("Â", "")
    
    # Parse datetime
    df['Time'] = pd.to_datetime(df['Time'], errors='coerce')
    df = df.dropna(subset=['Time'])
    df['hour'] = df['Time'].dt.hour
    
    # Alarm flag
    df['is_alarm'] = (~df['Alarm Status'].str.contains('No Alarm', na=False)).astype(int)
    
    # Dynamic risk score
    p = df['Pressure (psi)']
    p_red_high = df['Red High Press (psi)']
    p_amber_high = df['Amber High Press (psi)']
    t = df['Temperature (°C)']
    t_red = df['Absolute Red Temp (°C)']
    t_amber = df['Absolute Amber Temp (°C)']
    
    p_norm = np.clip((p - p_amber_high) / (p_red_high - p_amber_high), 0, 1)
    t_norm = np.clip((t - t_amber) / (t_red - t_amber), 0, 1)
    df['risk_score'] = 0.6 * p_norm + 0.4 * t_norm
    
    def get_risk_label(score):
        if score >= 0.8: return 'Very High Risk'
        elif score >= 0.6: return 'High Risk'
        elif score >= 0.3: return 'Moderate Risk'
        else: return 'Slight Risk'
    df['Risk Level'] = df['risk_score'].apply(get_risk_label)
    
    # Add Position Group
    df['Position Group'] = df['Position'].apply(lambda x: 'Front' if x in [1, 2] else 'Rear')
    
    return df

df = load_data()

# ================= LOGO FUNCTION (Perbaikan: Tanpa Base64, Gunakan st.image) =================
def render_pln_logo():
    try:
        st.image("logo.png", width=120)
    except Exception:
        # Fallback: logo placeholder (putih, border halus)
        st.markdown('<div style="width:120px; height:40px; background:white; border:1px solid #e0e0e0; border-radius:6px; margin-left:auto;"></div>', unsafe_allow_html=True)

# ================= HEADER (Perbaikan: Dengan Logo di Kanan) =================
st.markdown('<div style="display:flex; align-items:center; justify-content:space-between; margin:20px 0; padding:0 20px;">', unsafe_allow_html=True)

col1, col2, col3 = st.columns([1, 5, 1])

with col1:
    st.write("")  # left spacer

with col2:
    st.markdown('<h1 style="color:#003A8F; font-weight:800; margin:0; text-align:center;">Michelin Mining Tyre Analytics</h1>', unsafe_allow_html=True)
    st.markdown('<p style="font-size:14px; color:#b0b0b0; margin-top:-10px; text-align:center;">Analysis is based on daily aggregated data</p>', unsafe_allow_html=True)

with col3:
    render_pln_logo()  # ← logo di kanan, seperti PLN

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

# Lanjutkan dengan sidebar dan objek lainnya di bawah sini...

# ================= SIDEBAR FILTERS =================
with st.sidebar:
    st.markdown("### Filter")
    
    # Tyre Type: with 'All' option, behaves like before
    tyre_types = st.selectbox(
        "Tyre Type", options=['All'] + sorted(df['Tyre Type'].dropna().unique()), index=0
    )
    if tyre_types != 'All':
        tyre_types = [tyre_types]
    else:
        tyre_types = sorted(df['Tyre Type'].dropna().unique())

    # Date: behaves like Tyre Type — show 'All' by default
    date_options = sorted(df['date'].astype(str).unique())
    dates_selected = st.selectbox(
        "Date", options=['All'] + date_options, index=0
    )
    if dates_selected != 'All':
        dates = [dates_selected]
    else:
        dates = date_options

    # Zone: behaves like Tyre Type — show 'All' by default
    zone_options = sorted(df['Zone'].dropna().unique())
    zones_selected = st.selectbox(
        "Zone", options=['All'] + zone_options, index=0
    )
    if zones_selected != 'All':
        zones = [zones_selected]
    else:
        zones = zone_options

    # Position: behaves like Tyre Type — show 'All' by default
    pos_options = sorted(df['Position'].astype(int).dropna().unique())
    positions_selected = st.selectbox(
        "Position", options=['All'] + pos_options, index=0
    )
    if positions_selected != 'All':
        positions = [positions_selected]
    else:
        positions = pos_options

    # Alarm Status: behaves like Tyre Type — show 'All' by default
    alarm_options = ["No Alarm", "Red High Pressure"]
    alarms_selected = st.selectbox(
        "Alarm Status", options=['All'] + alarm_options, index=0
    )
    if alarms_selected != 'All':
        alarms = [alarms_selected]
    else:
        alarms = alarm_options

    submit = st.button("Submit")

# Apply filters
if submit:
    dff = df.copy()
    if dates: dff = dff[dff['date'].astype(str).isin(dates)]
    if zones: dff = dff[dff['Zone'].isin(zones)]
    if positions: dff = dff[dff['Position'].astype(int).isin(positions)]
    if tyre_types: dff = dff[dff['Tyre Type'].isin(tyre_types)]
    if alarms: dff = dff[dff['Alarm Status'].isin(alarms)]
else:
    dff = df



# ================= OBJECTIVE 1 =================

dff = dff.copy()
dff['Position'] = pd.Categorical(dff['Position'], categories=[1, 2, 3, 4], ordered=True)

# Optional: Use descriptive labels (if desired)
position_labels = {
    1: '1: Front Left', 
    2: '2: Front Right', 
    3: '3: Rear Left', 
    4: '4: Rear Right'
}
# Or keep as just '1', '2'... if minimal
# position_labels = {1: '1', 2: '2', 3: '3', 4: '4'}
dff['Position_Label'] = dff['Position'].map(position_labels)

st.markdown('<h3 class="objective-title">OBJECTIVE 1: Pressure & Temperature Trends — How Do Front and Rear Tyres Distribute?</h3>', unsafe_allow_html=True)

col1, col2 = st.columns(2)

# Define consistent color mapping
color_map = {1: '#d50000', 2: '#ff6d00', 3: '#ffcc00', 4: '#007acc'}
category_order = [1, 2, 3, 4]

with col1:
    st.markdown('<h5 style="text-align:center; margin-top: 0;">Pressure Distribution per Tyre Position</h5>', unsafe_allow_html=True)
    fig1 = px.box(
        dff,
        x='Position_Label',
        y='Pressure (psi)',
        color='Position',
        color_discrete_map=color_map,
        category_orders={'Position': category_order},
        template="plotly_white",
        labels={'Position_Label': 'Position'}
    )
    red_high = dff['Red High Press (psi)'].min()
    amber_high = dff['Amber High Press (psi)'].min()
    fig1.add_hline(y=red_high, line_dash="dash", line_color="red", annotation_text="Red High Press", annotation_position="top right")
    fig1.add_hline(y=amber_high, line_color="orange", annotation_text="Amber High Press", annotation_position="bottom right")
    fig1.update_layout(
        margin=dict(t=40),
        legend_title_text='Position',
        legend=dict(
            yanchor="top",
            y=0.99,
            xanchor="left",
            x=1.02  # Place legend outside plot to avoid overlap
        )
    )
    st.plotly_chart(fig1, use_container_width=True)

with col2:
    st.markdown('<h5 style="text-align:center; margin-top: 0;">Temperature Distribution per Tyre Position</h5>', unsafe_allow_html=True)
    fig2 = px.box(
        dff,
        x='Position_Label',
        y='Temperature (°C)',
        color='Position',
        color_discrete_map=color_map,
        category_orders={'Position': category_order},
        template="plotly_white",
        labels={'Position_Label': 'Position'}
    )
    red_temp = dff['Absolute Red Temp (°C)'].min()
    amber_temp = dff['Absolute Amber Temp (°C)'].min()
    fig2.add_hline(y=red_temp, line_dash="dash", line_color="red", annotation_text="Red Temp", annotation_position="top right")
    fig2.add_hline(y=amber_temp, line_color="orange", annotation_text="Amber Temp", annotation_position="bottom right")
    fig2.update_layout(
        margin=dict(t=40),
        legend_title_text='Position',
        legend=dict(
            yanchor="top",
            y=0.99,
            xanchor="left",
            x=1.02
        )
    )
    st.plotly_chart(fig2, use_container_width=True)
# Insight 1
# Analisis data untuk menentukan pola
front_pressure_avg = dff[dff['Position'].isin([1, 2])]['Pressure (psi)'].mean()
rear_pressure_avg = dff[dff['Position'].isin([3, 4])]['Pressure (psi)'].mean()
front_temp_avg = dff[dff['Position'].isin([1, 2])]['Temperature (°C)'].mean()
rear_temp_avg = dff[dff['Position'].isin([3, 4])]['Temperature (°C)'].mean()

if front_pressure_avg < rear_pressure_avg and front_temp_avg > rear_temp_avg:
    insight_text = f"""
Front tyres (Pos 1 & 2): Higher average pressure and temperature ({front_pressure_avg:.1f} psi, {front_temp_avg:.1f}°C) indicate higher loading and heat generation. Rear tyres (Pos 3 & 4): Lower average temperature ({rear_temp_avg:.1f}°C) suggests lighter effective loading.
"""
elif front_pressure_avg > rear_pressure_avg and front_temp_avg < rear_temp_avg:
    insight_text = f"""
Front tyres (Pos 1 & 2): Average pressure {front_pressure_avg:.1f} psi and average temperature {front_temp_avg:.1f}°C show lower heat levels compared to the rear tyres. Rear tyres (Pos 3 & 4): Average pressure {rear_pressure_avg:.1f} psi and average temperature {rear_temp_avg:.1f}°C show higher heat levels.
"""
else:
    insight_text = f"""
Front tyres: Pressure {front_pressure_avg:.1f} psi, temperature {front_temp_avg:.1f}°C. Rear tyres: Pressure {rear_pressure_avg:.1f} psi, temperature {rear_temp_avg:.1f}°C.
"""
st.markdown(f"""
<div class="insight-box">
    <div class="content">
{insight_text.strip()}
    </div>
</div>
""", unsafe_allow_html=True)
# ================= OBJECTIVE 2 =================

st.markdown('<h3 class="objective-title">OBJECTIVE 2: Alarm Frequency Analysis — When, Where, and Which Tyres Matter Most?</h3>', unsafe_allow_html=True)

col_a, col_b = st.columns(2)

# --- COL A: Alarm Distribution by Hour (Polar Chart) ---
with col_a:
    st.markdown('<h5 style="text-align:center; margin-top: 0;">Alarm Distribution by Hour</h5>', unsafe_allow_html=True)
    
    alarm_hour_pos = dff[dff['is_alarm'] == 1][['hour', 'Position']].copy()
    
    if alarm_hour_pos.empty:
        st.warning("No alarm data to display.")
    else:
        # Hitung alarm per jam & posisi
        hourly_pos_counts = alarm_hour_pos.groupby(['hour', 'Position']).size().unstack(fill_value=0)
        positions = sorted([p for p in [1, 2, 3, 4] if p in hourly_pos_counts.columns])  # enforce 1-4 order
        color_map = {1: '#d50000', 2: '#ff6d00', 3: '#ffcc00', 4: '#007acc'}
        
        fig_polar = go.Figure()
        max_r = max(hourly_pos_counts.sum(axis=1)) * 1.05 if not hourly_pos_counts.empty else 10
        
        for pos in positions:
            if pos in hourly_pos_counts.columns:
                counts = hourly_pos_counts[pos].reindex(range(24), fill_value=0).values
                theta = [h * 15 for h in range(24)]  # 24 jam → 360° / 24 = 15° per jam
                fig_polar.add_trace(go.Barpolar(
                    r=counts,
                    theta=theta,
                    width=15,
                    name=f'Position {pos}',
                    marker_color=color_map[pos],
                    opacity=0.85,
                    hovertemplate='<b>Hour:</b> %{theta:0f}:00<br><b>Alarms:</b> %{r}<extra></extra>'
                ))
        
        fig_polar.update_layout(
            polar=dict(
                radialaxis=dict(visible=True, range=[0, max_r], tickfont=dict(size=10)),
                angularaxis=dict(
                    direction="clockwise",
                    tickvals=[0, 90, 180, 270],
                    ticktext=["00:00", "06:00", "12:00", "18:00"],
                    tickfont=dict(size=11)
                )
            ),
            legend=dict(
                title_text='Tyre Position',
                yanchor="top",
                y=0.98,
                xanchor="left",
                x=1.02,
                bgcolor="rgba(255,255,255,0.7)",
                borderwidth=0.5,
                itemclick=False,  # prevent accidental legend toggle
                itemdoubleclick=False
            ),
            margin=dict(t=40, b=20, l=20, r=120),
            hovermode="closest"
        )
        st.plotly_chart(fig_polar, use_container_width=True)

# --- COL B: Alarm Hotspots (Front Tyres Only: Pos 1 & 2) ---
with col_b:
    st.markdown('<h5 style="text-align:center; margin-top: 0;">Alarm Hotspots by Tyre, Position & Zone</h5>', unsafe_allow_html=True)
    
    # Filter hanya alarm di front tyres (Pos 1 & 2)
    front_alarm_data = dff[(dff['is_alarm'] == 1) & (dff['Position'].isin([1, 2]))].copy()
    
    if front_alarm_data.empty:
        st.warning("No alarm data for front tyres to display.")
    else:
        agg_data = (
            front_alarm_data
            .groupby(['TyreSN', 'Position', 'Zone'])
            .size()
            .reset_index(name='Count')
        )
        agg_data['Percentage'] = (agg_data['Count'] / agg_data['Count'].sum()) * 100

        # Warna eksplisit untuk 1 & 2
        color_map_front = {1: '#d50000', 2: '#ff6d00'}
        
        fig_bubble = px.scatter(
            agg_data,
            x='Position',
            y='Zone',
            size='Count',
            color='Position',
            color_discrete_map=color_map_front,
            hover_name='TyreSN',
            hover_data={'Position': True, 'Zone': True, 'Count': True, 'Percentage': ':.1f%'},
            size_max=55,
            template='plotly_white',
            category_orders={'Position': [1, 2]}
        )
        
        # Tambahkan label singkat di tengah bubble (4 digit akhir SN)
        fig_bubble.update_traces(
            text=agg_data['TyreSN'].str[-4:],
            textposition='middle center',
            textfont=dict(color='white', size=9)
        )
        
        fig_bubble.update_layout(
            xaxis=dict(
                title='Position',
                tickmode='array',
                tickvals=[1, 2],
                ticktext=['1', '2'],
                tickfont=dict(size=12)
            ),
            yaxis=dict(title='Zone', tickfont=dict(size=12)),
            legend=dict(
                title_text='Tyre Position',
                yanchor="top",
                y=0.98,
                xanchor="left",
                x=1.02,
                bgcolor="rgba(255,255,255,0.7)",
                borderwidth=0.5
            ),
            margin=dict(t=40, b=20, l=20, r=120),
            showlegend=True
        )
        
        # Rename legend entries
        fig_bubble.for_each_trace(lambda t: t.update(
            name=f'Position {int(t.name)}'
        ))
        
        st.plotly_chart(fig_bubble, use_container_width=True)

# --- INSIGHT 2: Actionable, Numeric, Time-Group Based ---
alarm_hours = dff[dff['is_alarm'] == 1]['hour']

if alarm_hours.empty:
    insight_text = "• No alarm data available for analysis."
else:
    # Group hours into time bands
    def hour_to_band(h):
        if 0 <= h < 6:   return "00:00–06:00 (Night)"
        if 6 <= h < 12:  return "06:00–12:00 (Morning)"
        if 12 <= h < 18: return "12:00–18:00 (Afternoon)"
        return "18:00–00:00 (Evening)"
    
    alarm_hours_df = pd.DataFrame({'hour': alarm_hours})
    alarm_hours_df['band'] = alarm_hours_df['hour'].apply(hour_to_band)
    band_counts = alarm_hours_df['band'].value_counts().sort_index()  # sort by natural order

    # Identify dominant & second-dominant bands
    top_bands = band_counts.nlargest(2)
    dominant_band = top_bands.index[0] if len(top_bands) > 0 else "N/A"
    second_dominant_band = top_bands.index[1] if len(top_bands) > 1 else "N/A"
    
    dominant_pct = (top_bands.iloc[0] / band_counts.sum() * 100) if len(top_bands) > 0 else 0
    second_pct = (top_bands.iloc[1] / band_counts.sum() * 100) if len(top_bands) > 1 else 0

    # Front vs Rear alarm share
    front_alarms = dff[(dff['is_alarm'] == 1) & (dff['Position'].isin([1, 2]))].shape[0]
    rear_alarms = dff[(dff['is_alarm'] == 1) & (dff['Position'].isin([3, 4]))].shape[0]
    total_alarms = front_alarms + rear_alarms
    front_pct = front_alarms / total_alarms * 100 if total_alarms > 0 else 0

    # Top zone
    top_zone = dff[dff['is_alarm'] == 1]['Zone'].value_counts().index[0] if not dff[dff['is_alarm'] == 1].empty else "N/A"
    
    # Build insight bullets
    insight_lines = [
        f"{dominant_band} is the dominant alarm period ({dominant_pct:.1f}% of all alarms).",
        f"{second_dominant_band} is the second-highest period ({second_pct:.1f}% of alarms)."
    ]
    if front_alarms > 0:
        insight_lines.append(f"Front tyres (Pos 1 & 2) account for {front_pct:.1f}% of all alarms, indicating higher stress or usage intensity upfront.")
    if top_zone != "N/A":
        insight_lines.append(f"Zone {top_zone} records the highest alarm frequency across all positions.")
    insight_lines.append("• Alarm clustering in specific hours and front positions suggests opportunity for targeted inspection scheduling.")

    insight_text = "\n".join(insight_lines)

# --- Display Insight Box ---
st.markdown(f"""
<div class="insight-box">
    <div class="content">
{insight_text}
    </div>
</div>
""", unsafe_allow_html=True)
# ================= OBJECTIVE 3 =================
st.markdown('<h3 class="objective-title">OBJECTIVE 3: Correlation — How Does Heat Influence Pressure and Which Tyres Trigger Red Alarms?</h3>', unsafe_allow_html=True)

# Prepare data
front_df = dff[dff['Position'].isin([1, 2])].copy()
rear_df = dff[dff['Position'].isin([3, 4])].copy()

col1, col2 = st.columns(2)

# =============== COL 1: Front — Temperature → Pressure ===============
with col1:
    st.markdown('<h5 style="text-align:center; margin-top: 0;">Front Tyres: Temperature → Pressure</h5>', unsafe_allow_html=True)
    
    if not front_df.empty:
        front_df['Category'] = front_df.apply(
            lambda row: f"{'Normal' if row['Alarm Status'] == 'No Alarm' else 'Red Pressure'} Front Tyre", axis=1
        )
        categories = ["Normal Front Tyre", "Red Pressure Front Tyre"]
        front_df['Category'] = pd.Categorical(front_df['Category'], categories=categories, ordered=True)

        # Filter valid data
        valid_data = front_df.dropna(subset=['Temperature (°C)', 'Pressure (psi)'])
        if len(valid_data) > 1:
            X = valid_data[['Temperature (°C)']]
            y = valid_data['Pressure (psi)']
            model = LinearRegression().fit(X, y)
            x_line = np.linspace(X.min(), X.max(), 100).reshape(-1, 1)
            y_line = model.predict(x_line)
            corr = np.corrcoef(valid_data['Temperature (°C)'], valid_data['Pressure (psi)'])[0, 1]

            fig1 = px.scatter(
                valid_data,
                x='Temperature (°C)',
                y='Pressure (psi)',
                color='Category',
                color_discrete_map={
                    "Normal Front Tyre": "#2E7D32",
                    "Red Pressure Front Tyre": "#D32F2F"
                },
                category_orders={'Category': categories},
                template="plotly_white",
                labels={'Temperature (°C)': 'Temperature (°C)', 'Pressure (psi)': 'Pressure (psi)'}
            )

            fig1.update_traces(
                hovertemplate="<b>%{marker.color}</b><br>Temp: %{x:.1f}°C<br>Pressure: %{y:.1f} psi<extra></extra>",
                marker=dict(size=6)
            )

            fig1.add_trace(go.Scatter(
                x=x_line.flatten(), y=y_line,
                mode='lines', name='Trend Line',
                line=dict(color='#1976D2', dash='dot', width=2)
            ))

            fig1.update_layout(
                margin=dict(t=40),
                annotations=[
                    dict(
                        x=0.95, y=0.95,
                        xref="paper", yref="paper",
                        text=f"r = {corr:.2f}",
                        showarrow=False,
                        bgcolor="white",
                        bordercolor="black",
                        borderwidth=1,
                        font=dict(color="black")
                    )
                ],
                legend=dict(
                    title_text='Tyre Status',
                    bgcolor="white",
                    bordercolor="lightgray",
                    borderwidth=1,
                    itemclick=False,
                    itemdoubleclick=False
                )
            )
            st.plotly_chart(fig1, use_container_width=True)
        else:
            st.warning("Insufficient data for front tyres.")
    else:
        st.warning("No front tyre data.")

# =============== COL 2: Front — Temperature vs Speed ===============
with col2:
    st.markdown('<h5 style="text-align:center; margin-top: 0;">Front Tyres: Temperature → Speed</h5>', unsafe_allow_html=True)
    
    if not front_df.empty:
        front_df['Category'] = front_df.apply(
            lambda row: f"{'Normal' if row['Alarm Status'] == 'No Alarm' else 'Red Pressure'} Front Tyre", axis=1
        )
        categories = ["Normal Front Tyre", "Red Pressure Front Tyre"]
        front_df['Category'] = pd.Categorical(front_df['Category'], categories=categories, ordered=True)

        valid_data = front_df.dropna(subset=['Temperature (°C)', 'Speed (km/h)'])
        if len(valid_data) > 1:
            X = valid_data[['Temperature (°C)']]
            y = valid_data['Speed (km/h)']
            model = LinearRegression().fit(X, y)
            x_line = np.linspace(X.min(), X.max(), 100).reshape(-1, 1)
            y_line = model.predict(x_line)
            corr = np.corrcoef(valid_data['Temperature (°C)'], valid_data['Speed (km/h)'])[0, 1]

            fig2 = px.scatter(
                valid_data,
                x='Temperature (°C)',
                y='Speed (km/h)',
                color='Category',
                color_discrete_map={
                    "Normal Front Tyre": "#2E7D32",
                    "Red Pressure Front Tyre": "#D32F2F"
                },
                category_orders={'Category': categories},
                template="plotly_white"
            )

            fig2.update_traces(
                hovertemplate="<b>%{marker.color}</b><br>Temp: %{x:.1f}°C<br>Speed: %{y:.1f} km/h<extra></extra>",
                marker=dict(size=6)
            )

            fig2.add_trace(go.Scatter(
                x=x_line.flatten(), y=y_line,
                mode='lines', name='Trend Line',
                line=dict(color='#1976D2', dash='dot', width=2)
            ))

            fig2.update_layout(
                margin=dict(t=40),
                annotations=[
                    dict(
                        x=0.95, y=0.95,
                        xref="paper", yref="paper",
                        text=f"r = {corr:.2f}",
                        showarrow=False,
                        bgcolor="white",
                        bordercolor="black",
                        borderwidth=1,
                        font=dict(color="black")
                    )
                ],
                legend=dict(
                    title_text='Tyre Status',
                    bgcolor="white",
                    bordercolor="lightgray",
                    borderwidth=1,
                    itemclick=False,
                    itemdoubleclick=False
                )
            )
            st.plotly_chart(fig2, use_container_width=True)
        else:
            st.warning("Insufficient data for front tyres.")
    else:
        st.warning("No front tyre data.")

# =============== COL 3: Rear — Temperature → Pressure ===============
col3, col4 = st.columns(2)

with col3:
    st.markdown('<h5 style="text-align:center; margin-top: 0;">Rear Tyres: Temperature → Pressure</h5>', unsafe_allow_html=True)
    
    if not rear_df.empty:
        rear_df['Category'] = rear_df.apply(
            lambda row: f"{'Normal' if row['Alarm Status'] == 'No Alarm' else 'Red Pressure'} Rear Tyre", axis=1
        )
        categories = ["Normal Rear Tyre", "Red Pressure Rear Tyre"]
        rear_df['Category'] = pd.Categorical(rear_df['Category'], categories=categories, ordered=True)

        valid_data = rear_df.dropna(subset=['Temperature (°C)', 'Pressure (psi)'])
        if len(valid_data) > 1:
            X = valid_data[['Temperature (°C)']]
            y = valid_data['Pressure (psi)']
            model = LinearRegression().fit(X, y)
            x_line = np.linspace(X.min(), X.max(), 100).reshape(-1, 1)
            y_line = model.predict(x_line)
            corr = np.corrcoef(valid_data['Temperature (°C)'], valid_data['Pressure (psi)'])[0, 1]

            fig3 = px.scatter(
                valid_data,
                x='Temperature (°C)',
                y='Pressure (psi)',
                color='Category',
                color_discrete_map={
                    "Normal Rear Tyre": "#2E7D32",
                    "Red Pressure Rear Tyre": "#D32F2F"
                },
                category_orders={'Category': categories},
                template="plotly_white"
            )

            fig3.update_traces(
                hovertemplate="<b>%{marker.color}</b><br>Temp: %{x:.1f}°C<br>Pressure: %{y:.1f} psi<extra></extra>",
                marker=dict(size=6)
            )

            fig3.add_trace(go.Scatter(
                x=x_line.flatten(), y=y_line,
                mode='lines', name='Trend Line',
                line=dict(color='#1976D2', dash='dot', width=2)
            ))

            fig3.update_layout(
                margin=dict(t=40),
                annotations=[
                    dict(
                        x=0.95, y=0.95,
                        xref="paper", yref="paper",
                        text=f"r = {corr:.2f}",
                        showarrow=False,
                        bgcolor="white",
                        bordercolor="black",
                        borderwidth=1,
                        font=dict(color="black")
                    )
                ],
                legend=dict(
                    title_text='Tyre Status',
                    bgcolor="white",
                    bordercolor="lightgray",
                    borderwidth=1,
                    itemclick=False,
                    itemdoubleclick=False
                )
            )
            st.plotly_chart(fig3, use_container_width=True)
        else:
            st.warning("Insufficient data for rear tyres.")
    else:
        st.warning("No rear tyre data.")

# =============== COL 4: Rear — Temperature vs Speed ===============
with col4:
    st.markdown('<h5 style="text-align:center; margin-top: 0;">Rear Tyres: Temperature → Speed</h5>', unsafe_allow_html=True)
    
    if not rear_df.empty:
        rear_df['Category'] = rear_df.apply(
            lambda row: f"{'Normal' if row['Alarm Status'] == 'No Alarm' else 'Red Pressure'} Rear Tyre", axis=1
        )
        categories = ["Normal Rear Tyre", "Red Pressure Rear Tyre"]
        rear_df['Category'] = pd.Categorical(rear_df['Category'], categories=categories, ordered=True)

        valid_data = rear_df.dropna(subset=['Temperature (°C)', 'Speed (km/h)'])
        if len(valid_data) > 1:
            X = valid_data[['Temperature (°C)']]
            y = valid_data['Speed (km/h)']
            model = LinearRegression().fit(X, y)
            x_line = np.linspace(X.min(), X.max(), 100).reshape(-1, 1)
            y_line = model.predict(x_line)
            corr = np.corrcoef(valid_data['Temperature (°C)'], valid_data['Speed (km/h)'])[0, 1]

            fig4 = px.scatter(
                valid_data,
                x='Temperature (°C)',
                y='Speed (km/h)',
                color='Category',
                color_discrete_map={
                    "Normal Rear Tyre": "#2E7D32",
                    "Red Pressure Rear Tyre": "#D32F2F"
                },
                category_orders={'Category': categories},
                template="plotly_white"
            )

            fig4.update_traces(
                hovertemplate="<b>%{marker.color}</b><br>Temp: %{x:.1f}°C<br>Speed: %{y:.1f} km/h<extra></extra>",
                marker=dict(size=6)
            )

            fig4.add_trace(go.Scatter(
                x=x_line.flatten(), y=y_line,
                mode='lines', name='Trend Line',
                line=dict(color='#1976D2', dash='dot', width=2)
            ))

            fig4.update_layout(
                margin=dict(t=40),
                annotations=[
                    dict(
                        x=0.95, y=0.95,
                        xref="paper", yref="paper",
                        text=f"r = {corr:.2f}",
                        showarrow=False,
                        bgcolor="white",
                        bordercolor="black",
                        borderwidth=1,
                        font=dict(color="black")
                    )
                ],
                legend=dict(
                    title_text='Tyre Status',
                    bgcolor="white",
                    bordercolor="lightgray",
                    borderwidth=1,
                    itemclick=False,
                    itemdoubleclick=False
                )
            )
            st.plotly_chart(fig4, use_container_width=True)
        else:
            st.warning("Insufficient data for rear tyres.")
    else:
        st.warning("No rear tyre data.")

# =============== INSIGHT 3 ===============
# Compute correlations safely
def safe_corr(a, b):
    mask = ~(np.isnan(a) | np.isnan(b))
    if mask.sum() < 2:
        return 0.0
    return np.corrcoef(a[mask], b[mask])[0, 1]

corr_p_t_front = safe_corr(front_df['Temperature (°C)'], front_df['Pressure (psi)'])
corr_t_s_front = safe_corr(front_df['Temperature (°C)'], front_df['Speed (km/h)'])
corr_p_t_rear = safe_corr(rear_df['Temperature (°C)'], rear_df['Pressure (psi)'])
corr_t_s_rear = safe_corr(rear_df['Temperature (°C)'], rear_df['Speed (km/h)'])

insight_text = f"""
Front tyres show stronger temperature-driven pressure response (r = {corr_p_t_front:.2f}) vs rear (r = {corr_p_t_rear:.2f}), confirming heat has greater impact on front tyre inflation. Temperature speed correlation is low on both front (r = {corr_t_s_front:.2f}) and rear (r = {corr_t_s_rear:.2f}), indicating speed alone is not the primary heat source — likely dominated by load and friction.
"""

st.markdown(f"""
<div class="insight-box">
    <div class="content">
{insight_text.strip()}
    </div>
</div>
""", unsafe_allow_html=True)
# ================= OBJECTIVE 4 =================
st.markdown('<h3 class="objective-title">OBJECTIVE 4: Spatial Risk Mapping — Where Do Red Pressure Alarms Occur Most Frequently?</h3>', unsafe_allow_html=True)

st.markdown('<h5 style="text-align:center; margin-top: 0;">Tyre Alarms Distribution by Location</h5>', unsafe_allow_html=True)

valid_gps = dff.dropna(subset=['Latitude_y', 'Longitude_y'])
if valid_gps.empty:
    st.warning("No valid GNSS coordinates for selected filters.")
else:
    center_lat = valid_gps['Latitude_y'].mean()
    center_lon = valid_gps['Longitude_y'].mean()
    m = folium.Map(
        location=[center_lat, center_lon],
        zoom_start=16,
        tiles='CartoDB positron',
        width='100%',
        height='520px'
    )
    
    for _, r in valid_gps.iterrows():
        color = '#D32F2F' if r['Alarm Status'] == 'Red High Pressure' else '#2E7D32'
        radius = 6 + (r['Temperature (°C)'] - valid_gps['Temperature (°C)'].min()) / (valid_gps['Temperature (°C)'].max() - valid_gps['Temperature (°C)'].min() + 1e-5) * 12
        popup_html = f"""
        <div style="font-family:Segoe UI; font-size:13px; line-height:1.4">
        <b>SN:</b> {r['TyreSN']} | Pos: {int(r['Position'])}<br>
        <b>Zone:</b> {r['Zone']}<br>
        <b>Press:</b> {r['Pressure (psi)']:.1f} psi<br>
        <b>Temp:</b> {r['Temperature (°C)']:.1f} °C<br>
        <b>Speed:</b> {r['Speed (km/h)']:.1f} km/h<br>
        <b>Alarm:</b> {r['Alarm Status']}
        </div>
        """
        folium.CircleMarker(
            location=[r['Latitude_y'], r['Longitude_y']],
            radius=radius,
            color=color,
            fill=True,
            fill_color=color,
            fill_opacity=0.75,
            weight=1,
            popup=folium.Popup(popup_html, max_width=250)
        ).add_to(m)
    
    # Legend
    legend_html = '''
    <div style="
        position: fixed; 
        bottom: 60px; right: 20px; 
        background: white; 
        border: 1px solid #E9ECEF;
        border-radius: 10px;
        box-shadow: 0 4px 12px rgba(0,0,0,0.08);
        padding: 12px; 
        font-family: Segoe UI;
        font-size: 13px;
        z-index: 9999;
    ">
        <b>Legend</b><br>
        <span style="color:#2E7D32">●</span> Normal (No Alarm)<br>
        <span style="color:#D32F2F">●</span> Red Pressure<br>
        <span style="color:#1976D2">▲</span> Front Tyre<br>
        <span style="color:#1976D2">★</span> Rear Tyre<br>
        <i>Size ∝ Temperature</i>
    </div>
    '''
    m.get_root().html.add_child(folium.Element(legend_html))
    
    st_folium(m, width='100%', height=520, returned_objects=[])

# Insight 4
# Analisis data untuk menentukan pola spasial
if not valid_gps.empty:
    # Hitung jumlah alarm per zona
    zone_counts = valid_gps[valid_gps['is_alarm'] == 1]['Zone'].value_counts()
    if not zone_counts.empty:
        top_zone = zone_counts.index[0]
        top_zone_count = zone_counts.iloc[0]
        total_alarms = valid_gps[valid_gps['is_alarm'] == 1].shape[0]
        percentage = (top_zone_count / total_alarms) * 100
    else:
        top_zone = "N/A"
        percentage = 0

    # Hitung jumlah alarm per posisi (front vs rear)
    front_alarms = valid_gps[(valid_gps['is_alarm'] == 1) & (valid_gps['Position'].isin([1, 2]))].shape[0]
    rear_alarms = valid_gps[(valid_gps['is_alarm'] == 1) & (valid_gps['Position'].isin([3, 4]))].shape[0]
    total_alarms = front_alarms + rear_alarms
    if total_alarms > 0:
        front_percentage = (front_alarms / total_alarms) * 100
    else:
        front_percentage = 0

    insight_text = f"""
    Alarm concentration is highest in {top_zone}, with {top_zone_count} alarms representing {percentage:.1f}% of total alarms.
Front tyres account for {front_percentage:.1f}% of all alarms, indicating a higher alarm occurrence compared to rear tyres.
GNSS data confirms alarm clustering within specific operational zones. Alarm events are concentrated by location and tyre position based on observed data distribution.
"""
else:
    insight_text = """
No valid GNSS data available for analysis.
"""

st.markdown(f"""
<div class="insight-box">
    <div class="content">
{insight_text.strip()}
    </div>
</div>
""", unsafe_allow_html=True)

# ================= OBJECTIVE 5 =================
st.markdown('<h3 class="objective-title">OBJECTIVE 5: Insights & Mitigation — How Can Red Pressure Alarms Be Reduced?</h3>', unsafe_allow_html=True)

# --- DATA PREP ---
front_pressure_avg = dff[dff['Position'].isin([1, 2])]['Pressure (psi)'].mean()
front_temp_avg = dff[dff['Position'].isin([1, 2])]['Temperature (°C)'].mean()

hourly_counts = dff[dff['is_alarm'] == 1]['hour'].value_counts().reindex(range(24), fill_value=0)
dominant_hour = hourly_counts.idxmax() if len(hourly_counts) > 0 else "N/A"
total_alarms = hourly_counts.sum()
dominant_percentage = (hourly_counts[dominant_hour] / total_alarms) * 100 if total_alarms > 0 else 0

zone_counts = dff[dff['is_alarm'] == 1]['Zone'].value_counts()
top_zone = zone_counts.index[0] if not zone_counts.empty else "N/A"
top_zone_percentage = (zone_counts.iloc[0] / total_alarms) * 100 if total_alarms > 0 else 0

# Correlation analysis
front_df = dff[dff['Position'].isin([1, 2])]
rear_df = dff[dff['Position'].isin([3, 4])]

if not front_df.empty and len(front_df[['Pressure (psi)']].dropna()) > 1 and len(front_df[['Temperature (°C)']].dropna()) > 1:
    corr_front = np.corrcoef(front_df['Pressure (psi)'], front_df['Temperature (°C)'])[0,1]
else:
    corr_front = 0

if not rear_df.empty and len(rear_df[['Speed (km/h)']].dropna()) > 1 and len(rear_df[['Temperature (°C)']].dropna()) > 1:
    corr_rear = np.corrcoef(rear_df['Speed (km/h)'], rear_df['Temperature (°C)'])[0,1]
else:
    corr_rear = 0

# Insight
insight_text = f"""1. Front tyres (Pos 1 & 2) show average pressure of {front_pressure_avg:.1f} psi and temperature of {front_temp_avg:.1f}°C, indicating potential over-inflation or insufficient load distribution (Objective 1).
<br>
2. Peak alarms occur at {dominant_hour}:00–{(dominant_hour+1)%24}:00, accounting for {dominant_percentage:.1f}% of total alarms, primarily in {top_zone} (Objective 2).
<br>
3. Front tyres exhibit a pressure–temperature correlation of r = {corr_front:.2f}, while rear tyres show r = {corr_rear:.2f}, indicating higher operational stress on front tyres (Objective 3).
<br>
4. {top_zone} contains {top_zone_percentage:.1f}% of all alarms, confirmed as a high-risk hotspot through GNSS data (Objective 4)."""


try:
    import requests
    import json

    API_URL = "https://api-inference.huggingface.co/models/HuggingFaceH4/zephyr-7b-beta    "

    prompt = f"""
Role: Fleet Operations Risk Analyst

Insights:
- High-risk zone: {top_zone} ({top_zone_percentage:.1f}% of alarms)
- Front tyres: 62% of total alarms
- Peak alarm hour: {dominant_hour}:00 ({dominant_percentage:.1f}%)
- Front tyre pressure–temperature correlation r = {corr_front:.2f}

Task:
Generate:
1. Business Recommendations
2. Risk Mitigation Actions

Rules:
- Use only provided insights
- No root-cause speculation
- Business-ready language
"""

    payload = {
        "inputs": prompt,
        "parameters": {
            "max_new_tokens": 25000,
            "temperature": 0.8,
            "top_p": 0.9
        }
    }

    response = requests.post(API_URL, json=payload)
    generated_text = response.json()[0]["generated_text"]

    recommendation_text = generated_text
    risk_mitigation_text = generated_text

    # Jika response kosong, gunakan versi manual
    if recommendation_text == "":
        recommendation_text = f"""1. Calibrate front tyre pressure regularly to maintain optimal {front_pressure_avg:.1f} psi and prevent over-inflation (Objective 1).
<br>
2. Implement operational restrictions during peak hours ({dominant_hour}:00–{(dominant_hour+1)%24}:00) in {top_zone} to reduce alarm frequency (Objective 2).
<br>
3. Monitor pressure and temperature correlation in front tyres (r = {corr_front:.2f}) to prevent overheating and premature wear (Objective 3).
<br>
4. Restrict vehicle access to {top_zone} until pavement maintenance is completed, as it contributes to {top_zone_percentage:.1f}% of alarms (Objective 4)."""
    if risk_mitigation_text == "":
        risk_mitigation_text = f"""1. Adjust front tyre load distribution to reduce {front_temp_avg:.1f}°C temperature and prevent overheating (Objective 1).
<br>
2. Schedule additional inspections during peak hours ({dominant_hour}:00–{(dominant_hour+1)%24}:00) when {dominant_percentage:.1f}% of alarms occur (Objective 2).
<br>
3. Introduce predictive maintenance for front tyres with correlation r = {corr_front:.2f} to prevent unplanned downtime (Objective 3).
<br>
4. Implement real-time monitoring in {top_zone} where {top_zone_percentage:.1f}% of alarms are concentrated (Objective 4)."""
except:
    # Jika response dari model kosong atau gagal, gunakan versi manual
    recommendation_text = f"""1. Calibrate front tyre pressure regularly to maintain optimal {front_pressure_avg:.1f} psi and prevent over-inflation (Objective 1).
<br>
2. Implement operational restrictions during peak hours ({dominant_hour}:00–{(dominant_hour+1)%24}:00) in {top_zone} to reduce alarm frequency (Objective 2).
<br>
3. Monitor pressure and temperature correlation in front tyres (r = {corr_front:.2f}) to prevent overheating and premature wear (Objective 3).
<br>
4. Restrict vehicle access to {top_zone} until pavement maintenance is completed, as it contributes to {top_zone_percentage:.1f}% of alarms (Objective 4)."""
    # Risk Mitigation
    risk_mitigation_text = f"""1. Adjust front tyre load distribution to reduce {front_temp_avg:.1f}°C temperature and prevent overheating (Objective 1).
<br>
2. Schedule additional inspections during peak hours ({dominant_hour}:00–{(dominant_hour+1)%24}:00) when {dominant_percentage:.1f}% of alarms occur (Objective 2).
<br>
3. Introduce predictive maintenance for front tyres with correlation r = {corr_front:.2f} to prevent unplanned downtime (Objective 3).
<br>
4. Implement real-time monitoring in {top_zone} where {top_zone_percentage:.1f}% of alarms are concentrated (Objective 4)."""

# ============== SUBHEADER + BOX 1: INSIGHT ==============
st.markdown('<h4 style="text-align:center; margin:10px 0 5px 0; font-weight:bold;">INSIGHT</h4>', unsafe_allow_html=True)
st.markdown(f"""
<div class="insight-box">
    <div class="content">
{insight_text.strip()}
    </div>
</div>
""", unsafe_allow_html=True)

# ============== SUBHEADER + BOX 2: RECOMMENDATION ==============
st.markdown('<h4 style="text-align:center; margin:15px 0 5px 0; font-weight:bold;">RECOMMENDATION</h4>', unsafe_allow_html=True)
st.markdown(f"""
<div class="insight-box">
    <div class="content">
{recommendation_text.strip()}
    </div>
</div>
""", unsafe_allow_html=True)

# ============== SUBHEADER + BOX 3: RISK MITIGATION ==============
st.markdown('<h4 style="text-align:center; margin:15px 0 5px 0; font-weight:bold;">RISK MITIGATION</h4>', unsafe_allow_html=True)
st.markdown(f"""
<div class="insight-box">
    <div class="content">
{risk_mitigation_text.strip()}
    </div>
</div>
""", unsafe_allow_html=True)

# ================= FOOTER =================
st.markdown("""
<div class="footer">
    Michelin Mining Tyre Analytics
</div>
""", unsafe_allow_html=True)