app.py

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

# -------------------- AUTH --------------------
scope = [
    "https://spreadsheets.google.com/feeds",
    "https://www.googleapis.com/auth/drive"
]
creds = ServiceAccountCredentials.from_json_keyfile_name(
    "deep-mile-461309-t8-0e90103411e0.json",
    scope
)
client = gspread.authorize(creds)
sheet_url = "https://docs.google.com/spreadsheets/d/1if4KoVQvw5ZbhknfdZbzMkcTiPfsD6bz9V3a1th-bwQ"

# -------------------- UTILS --------------------
def normalize_header(raw_header):
    # strip and titleize
    return [h.strip().title() for h in raw_header]

def load_sheet(sheet_name: str) -> pd.DataFrame:
    ws = client.open_by_url(sheet_url).worksheet(sheet_name)
    all_vals = ws.get_all_values()
    if not all_vals or len(all_vals) < 2:
        return pd.DataFrame()
    header = normalize_header(all_vals[0])
    rows = all_vals[1:]
    df = pd.DataFrame(rows, columns=header)
    return df

def get_current_week_range():
    today = datetime.now()
    start = today - timedelta(days=today.weekday())
    end = start + timedelta(days=6)
    return start.date(), end.date()

# -------------------- CALLS --------------------
def get_calls(rep=None):
    df = load_sheet("Calls")
    if "Call Date" not in df:
        return pd.DataFrame([{"Error": "Missing 'Call Date' column"}])
    df["Call Date"] = pd.to_datetime(df["Call Date"], errors="coerce").dt.date
    start, end = get_current_week_range()
    filtered = df[(df["Call Date"] >= start) & (df["Call Date"] <= end)]
    if rep:
        filtered = filtered[filtered["Rep"] == rep]
    return filtered

def search_calls_by_date(y, m, d, rep):
    df = load_sheet("Calls")
    if "Call Date" not in df:
        return pd.DataFrame([{"Error": "Missing 'Call Date' column"}])
    try:
        target = datetime(int(y), int(m), int(d)).date()
    except:
        return pd.DataFrame([{"Error": "Invalid date input"}])
    df["Call Date"] = pd.to_datetime(df["Call Date"], errors="coerce").dt.date
    filtered = df[df["Call Date"] == target]
    if rep:
        filtered = filtered[filtered["Rep"] == rep]
    return filtered

# -------------------- APPOINTMENTS --------------------
def appointments_detail(rep=None):
    df = load_sheet("Appointments")
    if "Appointment Date" not in df:
        return pd.DataFrame([{"Error": "Missing 'Appointment Date' column"}])
    df["Appointment Date"] = pd.to_datetime(df["Appointment Date"], errors="coerce").dt.date
    start, end = get_current_week_range()
    filtered = df[(df["Appointment Date"] >= start) & (df["Appointment Date"] <= end)]
    if rep:
        filtered = filtered[filtered["Rep"] == rep]
    return filtered

def appointments_summary(rep=None):
    det = appointments_detail(rep)
    if "Error" in det.columns:
        return det
    return det.groupby("Rep") \
              .size() \
              .reset_index(name="Appointment Count")

def search_appointments_by_date(y, m, d, rep):
    df = load_sheet("Appointments")
    if "Appointment Date" not in df:
        return pd.DataFrame([{"Error": "Missing 'Appointment Date' column"}])
    try:
        target = datetime(int(y), int(m), int(d)).date()
    except:
        return pd.DataFrame([{"Error": "Invalid date input"}])
    df["Appointment Date"] = pd.to_datetime(df["Appointment Date"], errors="coerce").dt.date
    filtered = df[df["Appointment Date"] == target]
    if rep:
        filtered = filtered[filtered["Rep"] == rep]
    return filtered

# -------------------- LEADS --------------------
def get_leads_detail():
    df = load_sheet("AllocatedLeads")
    if "Assigned Rep" not in df or "Company Name" not in df:
        return pd.DataFrame([{"Error": "Missing 'Assigned Rep' or 'Company Name' column"}])
    return df

def get_leads_summary():
    df = get_leads_detail()
    if "Error" in df.columns:
        return df
    return df.groupby("Assigned Rep") \
             .size() \
             .reset_index(name="Leads Count")

# -------------------- INSIGHTS --------------------
def compute_insights():
    calls = get_calls()
    appt  = appointments_detail()
    leads = get_leads_detail()

    def top(df, col):
        return df[col].value_counts().idxmax() if not df.empty else "N/A"

    return pd.DataFrame([
        {"Metric": "Most Calls This Week",        "Rep": top(calls, "Rep")},
        {"Metric": "Most Appointments This Week", "Rep": top(appt,  "Rep")},
        {"Metric": "Most Leads Allocated",        "Rep": top(leads, "Assigned Rep")},
    ])

# -------------------- DROPDOWN OPTIONS --------------------
def rep_options(sheet_name, rep_col):
    df = load_sheet(sheet_name)
    return sorted(df[rep_col].dropna().unique().tolist()) if rep_col in df.columns else []

# -------------------- UI LAYOUT --------------------
with gr.Blocks(title="Graffiti Admin Dashboard") as app:
    gr.Markdown("# 📆 Graffiti Admin Dashboard")

    # Calls Report
    with gr.Tab("Calls Report"):
        rep_calls = gr.Dropdown("Optional Rep Filter",
                                choices=rep_options("Calls", "Rep"),
                                allow_custom_value=True)
        calls_btn   = gr.Button("Load Current Week Calls")
        calls_table = gr.Dataframe()
        calls_btn.click(fn=get_calls, inputs=rep_calls, outputs=calls_table)

        gr.Markdown("### 🔍 Search Calls by Specific Date")
        y1, m1, d1 = gr.Textbox("Year"), gr.Textbox("Month"), gr.Textbox("Day")
        rep1 = gr.Dropdown("Optional Rep Filter",
                           choices=rep_options("Calls", "Rep"),
                           allow_custom_value=True)
        calls_date_btn   = gr.Button("Search Calls by Date")
        calls_date_table = gr.Dataframe()
        calls_date_btn.click(fn=search_calls_by_date,
                             inputs=[y1, m1, d1, rep1],
                             outputs=calls_date_table)

    # Appointments Report
    with gr.Tab("Appointments Report"):
        rep_appt = gr.Dropdown("Optional Rep Filter",
                               choices=rep_options("Appointments", "Rep"),
                               allow_custom_value=True)
        load_btn = gr.Button("Load Current Week Appointments")
        appt_sum = gr.Dataframe(label="📊 Weekly Appointments Summary by Rep")
        appt_det = gr.Dataframe(label="🔎 Detailed Appointments")
        load_btn.click(
            fn=lambda rep: (appointments_summary(rep), appointments_detail(rep)),
            inputs=rep_appt,
            outputs=[appt_sum, appt_det]
        )

        gr.Markdown("### 🔍 Search Appointments by Specific Date")
        y2, m2, d2 = gr.Textbox("Year"), gr.Textbox("Month"), gr.Textbox("Day")
        rep2 = gr.Dropdown("Optional Rep Filter",
                           choices=rep_options("Appointments", "Rep"),
                           allow_custom_value=True)
        date_btn = gr.Button("Search Appointments by Date")
        date_sum = gr.Dataframe(label="📊 Appointments Summary for Date by Rep")
        date_det = gr.Dataframe(label="🔎 Detailed Appointments")
        def by_date(y, m, d, rep):
            df = search_appointments_by_date(y, m, d, rep)
            if "Error" in df.columns:
                return df, df
            return (
                df.groupby("Rep").size().reset_index(name="Appointment Count"),
                df
            )
        date_btn.click(fn=by_date,
                       inputs=[y2, m2, d2, rep2],
                       outputs=[date_sum, date_det])

    # Appointed Leads
    with gr.Tab("Appointed Leads"):
        leads_btn = gr.Button("View Appointed Leads")
        leads_sum = gr.Dataframe(label="📊 Leads Count by Rep")
        leads_det = gr.Dataframe(label="🔎 Detailed Leads")
        leads_btn.click(
            fn=lambda: (get_leads_summary(), get_leads_detail()),
            outputs=[leads_sum, leads_det]
        )

    # Insights
    with gr.Tab("Insights"):
        insights_btn = gr.Button("Generate Insights")
        insights_tbl = gr.Dataframe()
        insights_btn.click(fn=compute_insights, outputs=insights_tbl)

app.launch()