import streamlit as st
import pandas as pd
import plotly.express as px
from simple_salesforce import Salesforce
from map_visualization import show_fault_map

# 🔐 Connect to Salesforce
sf = Salesforce(
    username="greenenergy@vedavathi.com",
    password="Vedavathi@04",
    security_token="jqe4His8AcuFJucZz5NBHfGU"
)

# 📄 Query to get Pole records
query = """
SELECT Id,
       Name,
       Power_Required__c,
       Power_Sufficient__c,
       RFID_Tag__c,
       Site__c,
       Solar_Generation__c,
       Wind_Generation__c,
       Alert_Level__c,
       Camera_Status__c,
       Health_Score__c,
       Location_Latitude__c,
       Location_Longitude__c
FROM Pole__c
LIMIT 1000
"""

# 📦 Fetch and flatten records
result = sf.query_all(query)
df = pd.json_normalize(result['records'])

# 🧼 Remove 'attributes' column if present
df = df.drop(columns='attributes', errors='ignore')

# 🖥️ Streamlit App Layout
st.set_page_config(page_title="VIEP Dashboard", layout="wide")
st.title("🔌 Vedavathi Intelligent Energy Poles Dashboard")

# 📋 Show Full Table
st.subheader("📋 Pole Data Table")
st.dataframe(df)

# 🔋 Bar Chart: Solar vs Wind Generation
st.subheader("🔋 Solar vs Wind Generation")
fig_bar = px.bar(
    df,
    x="Name",
    y=["Solar_Generation__c", "Wind_Generation__c"],
    barmode="group",
    labels={"value": "Power (kW)", "Name": "Pole"},
    title="Power Generation per Pole"
)
st.plotly_chart(fig_bar, use_container_width=True)

# 🎥 Pie Chart: Camera Status
st.subheader("🎥 Camera Status Distribution")
fig_cam = px.pie(
    df,
    names="Camera_Status__c",
    title="Camera Status",
    hole=0.3
)
st.plotly_chart(fig_cam, use_container_width=True)

# 🚨 Pie Chart: Alert Level
st.subheader("🚨 Alert Level Distribution")
fig_alert = px.pie(
    df,
    names="Alert_Level__c",
    title="Alert Level",
    hole=0.3
)
st.plotly_chart(fig_alert, use_container_width=True)

# 📈 Health Score by Site
st.subheader("📈 Health Score by Site")
fig_health = px.box(
    df,
    x="Site__c",
    y="Health_Score__c",
    points="all",
    title="Health Score Distribution",
    labels={"Site__c": "Site", "Health_Score__c": "Health Score"}
)
st.plotly_chart(fig_health, use_container_width=True)

# 🗺️ Map of Pole Locations
st.subheader("🗺️ Pole Locations Map")
df_map = df.dropna(subset=["Location_Latitude__c", "Location_Longitude__c"])
if not df_map.empty:
    fig_map = px.scatter_mapbox(
        df_map,
        lat="Location_Latitude__c",
        lon="Location_Longitude__c",
        hover_name="Name",
        color="Alert_Level__c",
        zoom=4,
        mapbox_style="open-street-map"
    )
    st.plotly_chart(fig_map, use_container_width=True)
else:
    st.warning("No location data available to show the map.")

# 📥 Download CSV
st.download_button(
    label="⬇️ Download Data as CSV",
    data=df.to_csv(index=False),
    file_name="pole_data.csv",
    mime="text/csv"
)