app.py

import pandas as pd
import gspread
import gradio as gr
from oauth2client.service_account import ServiceAccountCredentials
from datetime import datetime
from math import radians, cos, sin, asin, sqrt

# ------------------ AUTH ------------------
VALID_USERS = {
    "[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("credentials.json", scope)
client = gspread.authorize(creds)
sheet_file = client.open("userAccess")

# ------------------ HELPERS ------------------
def load_tab(sheet_name):
    try:
        df = pd.DataFrame(sheet_file.worksheet(sheet_name).get_all_records())
        return df
    except:
        return pd.DataFrame([["⚠️ Could not load sheet."]], columns=["Error"])

def haversine(coord1, coord2):
    lon1, lat1 = map(radians, map(float, coord1.split(',')[::-1]))
    lon2, lat2 = map(radians, map(float, coord2.split(',')[::-1]))
    dlon = lon2 - lon1
    dlat = lat2 - lat1
    a = sin(dlat/2)**2 + cos(lat1) * cos(lat2) * sin(dlon/2)**2
    c = 2 * asin(sqrt(a))
    return 6371 * c

# ------------------ FIELD SALES ------------------
def load_field_sales():
    df = load_tab("Field Sales")
    if df.empty or "Date" not in df.columns:
        return pd.DataFrame()

    df['Date'] = pd.to_datetime(df['Date'], errors='coerce')
    df = df.dropna(subset=["Date"])
    df['DateStr'] = df['Date'].dt.date.astype(str)

    if "Order Value" not in df.columns:
        df["Order Value"] = 0
    else:
        df["Order Value"] = pd.to_numeric(df["Order Value"], errors="coerce").fillna(0)

    # Distance calc
    distances = [0]
    for i in range(1, len(df)):
        try:
            prev = df.loc[i-1, 'Location']
            curr = df.loc[i, 'Location']
            if pd.notna(prev) and pd.notna(curr):
                distances.append(round(haversine(prev, curr), 2))
            else:
                distances.append(0)
        except:
            distances.append(0)
    df["Distance Travelled (km)"] = distances

    return df

# ------------------ TELESALES ------------------
def load_telesales():
    df = load_tab("TeleSales")
    if df.empty or "Rep Email" not in df.columns:
        return pd.DataFrame()

    df["Date"] = pd.to_datetime(df.get("Date", datetime.today()), errors='coerce')
    df["DateStr"] = df["Date"].dt.date.astype(str)
    return df

# ------------------ OEM ------------------
def load_oem():
    df = load_tab("OEM Visit")
    if df.empty or "Rep" not in df.columns:
        return pd.DataFrame()
    df["Date"] = pd.to_datetime(df.get("Date", datetime.today()), errors='coerce')
    df["DateStr"] = df["Date"].dt.date.astype(str)
    return df

# ------------------ SUMMARY ------------------
def generate_summary(date_str):
    df = load_field_sales()
    if df.empty:
        return pd.DataFrame([["No Field Sales data"]], columns=["Message"])*5

    df_day = df[df['DateStr'] == date_str.strip()]
    all_reps = sorted(df['Rep'].dropna().unique())
    col = "Current/Prospect Customer"

    # --- Visits Breakdown
    total_visits = df_day.groupby("Rep").size().reset_index(name="Total Visits")
    current = df_day[df_day[col] == "Current"]
    prospect = df_day[df_day[col] == "Prospect"]
    breakdown = pd.DataFrame({
        "Rep": all_reps,
        "Current": [len(current[current["Rep"] == rep]) for rep in all_reps],
        "Prospect": [len(prospect[prospect["Rep"] == rep]) for rep in all_reps]
    })
    inactive = pd.DataFrame({'Inactive Reps': [rep for rep in all_reps if rep not in total_visits["Rep"].tolist()]})

    # --- Field Summary per Rep
    rep_summary = df_day.groupby("Rep").agg({
        "Order Value": "sum",
        "Order Received": lambda x: (x == "Yes").sum(),
        "Current/Prospect Customer": lambda x: (x == "Current").sum(),
        "Distance Travelled (km)": "sum"
    }).rename(columns={
        "Order Value": "Total Order Value",
        "Order Received": "Orders Received",
        "Current/Prospect Customer": "Current Customers",
        "Distance Travelled (km)": "Total Distance (km)"
    }).reset_index()

    # --- TeleSales Summary
    df_ts = load_telesales()
    df_ts_day = df_ts[df_ts['DateStr'] == date_str.strip()]
    ts_summary = df_ts_day.groupby("Rep Email").size().reset_index(name="Total Calls Made") if not df_ts_day.empty else pd.DataFrame([["No Telesales"]], columns=["Info"])

    # --- OEM Summary
    df_oem = load_oem()
    df_oem_day = df_oem[df_oem['DateStr'] == date_str.strip()]
    oem_summary = df_oem_day.groupby("Rep").size().reset_index(name="Total OEM Visits") if not df_oem_day.empty else pd.DataFrame([["No OEM Visits"]], columns=["Info"])

    return total_visits, breakdown, inactive, rep_summary, ts_summary, oem_summary

# ------------------ GRADIO APP ------------------
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("## 🗂️ CarMat Dashboard")

            df_initial = load_field_sales()
            unique_dates = sorted(df_initial["DateStr"].unique(), reverse=True) if not df_initial.empty else []

            # --- Summary Tab ---
            with gr.Tab("📊 Summary"):
                date_summary = gr.Dropdown(label="Select Date", choices=unique_dates)
                visits = gr.Dataframe(label="✅ Total Visits")
                breakdown = gr.Dataframe(label="🏷️ Current vs. Prospect")
                inactive = gr.Dataframe(label="⚠️ Inactive Reps")
                field_summary = gr.Dataframe(label="🚗 Field Sales Summary")
                ts_summary = gr.Dataframe(label="📞 Telesales Summary")
                oem_summary = gr.Dataframe(label="🏭 OEM Visit Summary")

                date_summary.change(fn=generate_summary, inputs=date_summary,
                                    outputs=[visits, breakdown, inactive, field_summary, ts_summary, oem_summary])

    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 login."

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

app.launch()