app.py

import gradio as gr
import pandas as pd
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from datetime import datetime

# -- Login credentials --
USER_CREDENTIALS = {"[email protected]": "Pass.123"}

# -- Load Google Sheets data --
def get_sheet_data():
    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)

    # ✅ Fixed sheet name here:
    sheet = client.open_by_title("userAccess").worksheet("Field Sales")
    data = pd.DataFrame(sheet.get_all_records())
    return data

# -- Generate report based on selected period --
def generate_report(data, period="Daily"):
    try:
        if "Date" not in data.columns:
            return "❌ Column 'Date' not found in the sheet."

        data["Date"] = pd.to_datetime(data["Date"], errors='coerce')
        data = data.dropna(subset=["Date"])
        now = pd.Timestamp.today()

        if period == "Weekly":
            data = data[data["Date"] >= now - pd.Timedelta(days=7)]
        elif period == "Monthly":
            data = data[data["Date"].dt.month == now.month]
        elif period == "Yearly":
            data = data[data["Date"].dt.year == now.year]

        total_visits = len(data)
        current = len(data[data["Current/Prospect Custor"] == "Current"])
        new = len(data[data["Current/Prospect Custor"] == "Prospect"])
        second_hand = len(data[data["Customer Type"].str.contains("Second", na=False)])
        telesales = len(data[data["Source"] == "TeleSales"])
        oem_visits = len(data[data["Source"] == "OEM Visit"])
        orders = len(data[data["Order Received"] == "Yes"])
        order_value = data["Order Value"].sum() if "Order Value" in data.columns else 0

        return (
            f"### {period} Report\n"
            f"- **Total Visits:** {total_visits}\n"
            f"- **Current Dealerships:** {current}\n"
            f"- **New Dealerships:** {new}\n"
            f"- **% Current:** {current / total_visits * 100:.1f}% | **% New:** {new / total_visits * 100:.1f}%\n"
            f"- **Second-hand Dealerships:** {second_hand}\n"
            f"- **TeleSales Calls:** {telesales}\n"
            f"- **OEM Visits:** {oem_visits}\n"
            f"- **Orders Received:** {orders}\n"
            f"- **Total Order Value:** ${order_value:,.2f}"
        )
    except Exception as e:
        return f"❌ Error during report generation: {str(e)}"

# -- Login function --
def login(email, password):
    if USER_CREDENTIALS.get(email) == password:
        return gr.update(visible=True), gr.update(visible=False), ""
    else:
        return gr.update(visible=False), gr.update(visible=True), "❌ Invalid email or password"

# -- Report trigger --
def report(period):
    try:
        data = get_sheet_data()
        if data.empty:
            return "⚠️ No data found in the sheet."
        return generate_report(data, period)
    except Exception as e:
        return f"❌ Failed to load sheet: {str(e)}"

# -- Gradio UI --
with gr.Blocks() as app:
    with gr.Row(visible=True) as login_row:
        email = gr.Textbox(label="Email")
        password = gr.Textbox(label="Password", type="password")
        login_btn = gr.Button("Login")
        login_error = gr.Textbox(label="", visible=False)

    with gr.Row(visible=False) as dashboard:
        period_dropdown = gr.Dropdown(["Daily", "Weekly", "Monthly", "Yearly"], value="Weekly", label="Select Report Period")
        report_btn = gr.Button("Generate Report")
        report_output = gr.Markdown()

    login_btn.click(fn=login, inputs=[email, password], outputs=[dashboard, login_error, login_error])
    report_btn.click(fn=report, inputs=[period_dropdown], outputs=report_output)

app.launch()