app.py

import pandas as pd
import gspread
import gradio as gr
import plotly.express as px
from oauth2client.service_account import ServiceAccountCredentials

# ------------------ AUTH ------------------
VALID_USERS = {
    "[email protected]": "Pass.123",
    "[email protected]": "Pass.123",
    "[email protected]": "Pass.123",
    "[email protected]": "Pass.123"
}

# ------------------ GOOGLE SHEET SETUP ------------------
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("bid4carsappsheetdash.json", scope)
client = gspread.authorize(creds)
sheet_url = "https://docs.google.com/spreadsheets/d/1eUXhcfWd3jtNmZC6U_Dr2F7obQcK81I4YrK-fKEbkyU"

# ------------------ DATA REFRESH FUNCTION ------------------
def refresh_data():
    sheet = client.open_by_url(sheet_url).worksheet("Calls2")
    data = sheet.get_all_records()
    df = pd.DataFrame(data)

    df['Timestamp'] = pd.to_datetime(df['Date'].astype(str) + " " + df['Time'].astype(str), errors='coerce')
    df['Date'] = pd.to_datetime(df['Date'], errors='coerce').dt.date.astype(str)
    df['Time'] = pd.to_datetime(df['Time'], format='%H:%M:%S', errors='coerce').dt.time

    gps_split = df['GPS'].astype(str).str.split(',', expand=True)
    df['Latitude'] = pd.to_numeric(gps_split[0], errors='coerce')
    df['Longitude'] = pd.to_numeric(gps_split[1], errors='coerce')

    df = df.dropna(subset=['Date', 'Rep Name', 'Latitude', 'Longitude'])
    df = df[(df['Latitude'] != 0) & (df['Longitude'] != 0)]
    df = df.sort_values(by=['Rep Name', 'Timestamp'])
    df['Time Diff (min)'] = df.groupby(['Rep Name', 'Date'])['Timestamp'].diff().dt.total_seconds().div(60).fillna(0)
    df['Visit Order'] = df.groupby(['Rep Name', 'Date']).cumcount() + 1

    return df

# ------------------ SUMMARY GENERATION ------------------
def generate_summary(date_str):
    df = refresh_data()
    all_reps = sorted(df['Rep Name'].dropna().unique())
    day_df = df[df['Date'] == date_str]
    active = day_df.groupby('Rep Name').size().reset_index(name='Total Visits')
    active_list = active['Rep Name'].tolist()
    inactive_list = [rep for rep in all_reps if rep not in active_list]
    inactive_df = pd.DataFrame({'Inactive Reps': inactive_list})
    return active, inactive_df

def get_reps(date_str):
    df = refresh_data()
    reps = df[df['Date'] == date_str]['Rep Name'].dropna().unique()
    return gr.update(choices=sorted(reps))

# ------------------ MAP & TABLE VIEW ------------------
def show_map(date_str, rep):
    df = refresh_data()
    subset = df[(df['Date'] == date_str) & (df['Rep Name'] == rep)]
    if subset.empty:
        return "No valid data", None

    subset = subset.sort_values(by='Timestamp').copy()
    subset['Visit Order'] = range(1, len(subset) + 1)
    center_lat = subset['Latitude'].mean()
    center_lon = subset['Longitude'].mean()

    fig = px.line_mapbox(
        subset,
        lat="Latitude", lon="Longitude",
        hover_name="Dealership",
        hover_data={"Time": True, "Time Diff (min)": True, "Visit Order": True},
        height=700,
        zoom=13,
        center={"lat": center_lat, "lon": center_lon}
    )

    scatter = px.scatter_mapbox(
        subset,
        lat="Latitude", lon="Longitude",
        color="Visit Order",
        hover_name="Dealership",
        hover_data=["Time", "Time Diff (min)"],
        color_continuous_scale="Bluered"
    )
    for trace in scatter.data:
        fig.add_trace(trace)

    fig.add_trace(px.scatter_mapbox(
        pd.DataFrame([subset.iloc[0]]),
        lat="Latitude", lon="Longitude",
        text=["Start"], color_discrete_sequence=["green"]).data[0])
    fig.add_trace(px.scatter_mapbox(
        pd.DataFrame([subset.iloc[-1]]),
        lat="Latitude", lon="Longitude",
        text=["End"], color_discrete_sequence=["red"]).data[0])

    fig.update_layout(mapbox_style="open-street-map", title=f"📍 {rep}'s Route on {date_str}")

    table = subset[[ 
        'Visit Order', 'Dealership', 'Time', 'Time Diff (min)', 
        'Interaction Type', 'Product'
    ]].rename(columns={
        'Dealership': '🧭 Dealer',
        'Time': '🕒 Time',
        'Time Diff (min)': '⏱️ Time Spent',
        'Interaction Type': '📞 Interaction',
        'Product': '💼 Product Type'
    })

    total_time = round(table['⏱️ Time Spent'].sum(), 2)
    summary_row = pd.DataFrame([{
        'Visit Order': '',
        '🧭 Dealer': f"🧮 Total Time: {total_time} min",
        '🕒 Time': '',
        '⏱️ Time Spent': '',
        '📞 Interaction': '',
        '💼 Product Type': ''
    }])
    table = pd.concat([table, summary_row], ignore_index=True)
    return table, fig

# ------------------ GRADIO UI ------------------
with gr.Blocks() as app:
    with gr.Row():
        with gr.Column(visible=True) as login_ui:
            gr.Markdown("## 🔐 Login Required")
            email = gr.Textbox(label="Email")
            password = gr.Textbox(label="Password", type="password")
            login_btn = gr.Button("Login")
            login_msg = gr.Markdown("")

        with gr.Column(visible=False) as main_ui:
            gr.Markdown("## 🗂️ Bid4Cars FieldApp Tracker")
            df_initial = refresh_data()
            unique_dates = sorted(df_initial['Date'].unique(), reverse=True)

            with gr.Tab("📊 Summary"):
                date_summary = gr.Dropdown(label="Select Date", choices=unique_dates)
                active_table = gr.Dataframe(label="✅ Active Reps (with total visits)")
                inactive_table = gr.Dataframe(label="⚠️ Inactive Reps")
                date_summary.change(fn=generate_summary, inputs=date_summary, outputs=[active_table, inactive_table])

            with gr.Tab("👤 KAM's"):
                with gr.Row():
                    with gr.Column(scale=1):
                        date_picker = gr.Dropdown(label="Select Date", choices=unique_dates)
                        rep_picker = gr.Dropdown(label="Select Rep")
                        btn = gr.Button("Show Route")
                    with gr.Column(scale=2):
                        table = gr.Dataframe(label="Call Table")

                map_plot = gr.Plot(label="Map")
                date_picker.change(fn=get_reps, inputs=date_picker, outputs=rep_picker)
                btn.click(fn=show_map, inputs=[date_picker, rep_picker], outputs=[table, map_plot])

            with gr.Tab("📄 Reports"):
                with gr.Row():
                    report_type = gr.Radio(choices=["Daily", "Weekly", "Monthly"], label="Report Type", value="Daily")
                    report_date = gr.Textbox(label="Select Date (YYYY-MM-DD)", placeholder="2025-05-27", type="text")
                    download_btn = gr.Button("📥 Download CSV")

                report_table = gr.Dataframe(label="📋 Report Summary")
                report_info = gr.Markdown()
                download_file = gr.File(label="📁 Download Link")

                def generate_report(report_type, report_date):
                    df = refresh_data()
                    try:
                        date_obj = pd.to_datetime(report_date, errors='coerce')
                    except:
                        return pd.DataFrame(), "⚠️ Invalid date format.", None

                    if pd.isnull(date_obj):
                        return pd.DataFrame(), "⚠️ Date could not be parsed.", None

                    if report_type == "Daily":
                        title = f"📆 Report for {date_obj.strftime('%B %d, %Y')}"
                        mask = df['Date'] == str(date_obj.date())
                    elif report_type == "Weekly":
                        start = date_obj - pd.Timedelta(days=date_obj.weekday())
                        end = start + pd.Timedelta(days=6)
                        title = f"📆 Week of {start.strftime('%b %d')} – {end.strftime('%b %d')}, {start.year}"
                        mask = (pd.to_datetime(df['Date']) >= start) & (pd.to_datetime(df['Date']) <= end)
                    elif report_type == "Monthly":
                        title = f"📆 Report for {date_obj.strftime('%B %Y')}"
                        mask = pd.to_datetime(df['Date']).dt.to_period("M") == pd.to_datetime(date_obj).to_period("M")

                    filtered = df[mask]

                    if filtered.empty:
                        return pd.DataFrame(), "⚠️ No data found for that range.", None

                    summary = filtered[[ 
                        'Date', 'Rep Name', 'Dealership', 'Time', 
                        'Interaction Type', 'Product', 'Time Diff (min)'
                    ]].sort_values(by=["Rep Name", "Date"])

                    insights = f"""
### {title}
### 📊 Insights:
- **Total Visits:** {len(filtered)}
- **Unique Reps:** {filtered['Rep Name'].nunique()}
- **Most Active Rep:** {filtered['Rep Name'].value_counts().idxmax()}
- **Most Visited Dealership:** {filtered['Dealership'].value_counts().idxmax()}
- **Avg Time Between Visits:** {round(filtered['Time Diff (min)'].mean(), 2)} min
                    """

                    filename = f"Bid4Cars_Report_{report_type}_{report_date}.csv".replace(" ", "_")
                    summary.to_csv(filename, index=False)

                    return summary, insights, filename

                report_date.change(fn=generate_report, inputs=[report_type, report_date], outputs=[report_table, report_info, download_file])
                report_type.change(fn=generate_report, inputs=[report_type, report_date], outputs=[report_table, report_info, download_file])
                download_btn.click(fn=generate_report, inputs=[report_type, report_date], outputs=[report_table, report_info, download_file])

    def do_login(user, pw):
        if VALID_USERS.get(user) == pw:
            return gr.update(visible=False), gr.update(visible=True), ""
        else:
            return gr.update(visible=True), gr.update(visible=False), "❌ Invalid email or password."

    login_btn.click(fn=do_login, inputs=[email, password], outputs=[login_ui, main_ui, login_msg])

app.launch()