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  # in km

# ------------------ LOAD SHEETS ------------------
def load_field_sales():
    df = load_tab("Field Sales")
    if df.empty:
        return pd.DataFrame(columns=["Date", "Rep", "Order Value", "Order Received", "Location", "KM Travelled"])
    df['Date'] = pd.to_datetime(df.get("Date", datetime.today()), errors='coerce')
    df = df.dropna(subset=["Date"])
    df["Order Value"] = pd.to_numeric(df.get("Order Value", 0), errors="coerce").fillna(0)
    df["KM Travelled"] = 0.0
    for rep in df["Rep"].unique():
        rep_df = df[df["Rep"] == rep].sort_values(by="Date")
        prev_coord = None
        for idx, row in rep_df.iterrows():
            curr_coord = row.get("Location", "")
            if prev_coord and curr_coord:
                try:
                    km = haversine(prev_coord, curr_coord)
                    df.at[idx, "KM Travelled"] = km
                except:
                    df.at[idx, "KM Travelled"] = 0
            prev_coord = curr_coord
    df["KM Travelled"] = df["KM Travelled"].round(2)
    return df

def load_telesales():
    df = load_tab("Telesales")
    df["Order Value"] = pd.to_numeric(df.get("Order Value", 0), errors="coerce").fillna(0)
    return df

def load_summary(field_df, telesales_df):
    summary = []
    reps = pd.concat([field_df["Rep"], telesales_df["Rep"]]).dropna().unique()
    for rep in reps:
        field_orders = field_df[(field_df["Rep"] == rep) & (field_df["Order Received"].str.lower() == "yes")]
        telesales_orders = telesales_df[(telesales_df["Rep"] == rep) & (telesales_df["Order Received"].str.lower() == "yes")]
        total_value = field_orders["Order Value"].sum() + telesales_orders["Order Value"].sum()
        total_orders = len(field_orders) + len(telesales_orders)
        total_km = field_df[field_df["Rep"] == rep]["KM Travelled"].sum()
        summary.append([rep, total_value, total_orders, round(total_km, 2)])
    return pd.DataFrame(summary, columns=["Rep", "Total Order Value", "Orders Received", "KM Travelled"])

# ------------------ MAIN INTERFACE ------------------
def login(email, password):
    if VALID_USERS.get(email) == password:
        field_df = load_field_sales()
        telesales_df = load_telesales()
        summary_df = load_summary(field_df, telesales_df)
        orders_df = load_tab("Orders")
        escalation_df = load_tab("Escalations")
        oem_df = load_tab("OEM Visits")
        cust_req_df = load_tab("Customer Requests")
        dealer_df = load_tab("Dealership Directory")
        users_df = load_tab("Users")

        with gr.Tab("Summary"):
            gr.Dataframe(summary_df, label="📊 Rep Summary")

        with gr.Tab("Field Sales"):
            gr.Dataframe(field_df[["Rep", "Order Value", "Order Received", "KM Travelled"]], label="Field Sales Summary")
            gr.Dataframe(field_df, label="Raw Field Sales")

        with gr.Tab("TeleSales"):
            gr.Dataframe(telesales_df, label="TeleSales Data")

        with gr.Tab("Orders"):
            gr.Dataframe(orders_df, label="Orders")

        with gr.Tab("Escalations"):
            gr.Dataframe(escalation_df, label="Escalations")

        with gr.Tab("OEM Visits"):
            gr.Dataframe(oem_df, label="OEM Visits")

        with gr.Tab("Customer Requests"):
            gr.Dataframe(cust_req_df, label="Customer Requests")

        with gr.Tab("Dealership Directory"):
            gr.Dataframe(dealer_df, label="Dealership Directory")

        with gr.Tab("Users"):
            gr.Dataframe(users_df, label="Users")

        return gr.update(visible=False), gr.update(visible=True)
    else:
        return "Invalid credentials", gr.update(visible=False)

with gr.Blocks(theme=gr.themes.Monochrome(), css="footer {visibility: hidden}") as demo:
    with gr.Column(visible=True) as login_col:
        gr.Markdown("### 🔐 CarMat Dashboard Login")
        email = gr.Textbox(label="Email")
        password = gr.Textbox(label="Password", type="password")
        login_btn = gr.Button("Login")
        login_msg = gr.Text()

    with gr.Column(visible=False) as dashboard_col:
        gr.Markdown("## 📂 CarMat Dashboard")

    login_btn.click(fn=login, inputs=[email, password], outputs=[login_msg, dashboard_col])

demo.launch()