app.py

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

# -------------------- CONFIG --------------------
SHEET_URL = "https://docs.google.com/spreadsheets/d/1if4KoVQvw5ZbhknfdZbzMkcTiPfsD6bz9V3a1th-bwQ"
CREDS_JSON = "deep-mile-461309-t8-0e90103411e0.json"

# -------------------- AUTH --------------------
scope = [
    "https://spreadsheets.google.com/feeds",
    "https://www.googleapis.com/auth/drive"
]
creds  = ServiceAccountCredentials.from_json_keyfile_name(CREDS_JSON, scope)
client = gspread.authorize(creds)

# -------------------- SHEET LOAD/UTILS --------------------
def normalize_columns(df):
    df.columns = df.columns.str.strip().str.title()
    return df

def load_sheet_df(sheet_name):
    try:
        ws     = client.open_by_url(SHEET_URL).worksheet(sheet_name)
        records= ws.get_all_records()
        df     = pd.DataFrame(records)
        return normalize_columns(df)
    except Exception as e:
        # return a one-row DF with an Error column
        return pd.DataFrame([{"Error": str(e)}])

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

def filter_week(df, date_column, rep_column=None, rep=None):
    df[date_column] = pd.to_datetime(df[date_column], errors="coerce").dt.date
    start,end      = get_current_week_range()
    out = df[(df[date_column] >= start) & (df[date_column] <= end)]
    if rep and rep in out.columns:
        out = out[out[rep_column] == rep]
    return out

def filter_date(df, date_column, rep_column, y,m,d, rep):
    try:
        target = datetime(int(y), int(m), int(d)).date()
    except:
        return pd.DataFrame([{"Error":"Invalid date"}])
    df[date_column] = pd.to_datetime(df[date_column], errors="coerce").dt.date
    out = df[df[date_column] == target]
    if rep and rep in out.columns:
        out = out[out[rep_column] == rep]
    return out

# -------------------- REPORT DATA --------------------
def get_calls(rep=None):
    df = load_sheet_df("Calls")
    if "Call Date" not in df.columns:
        return pd.DataFrame([{"Error":"Missing 'Call Date' column"}])
    return filter_week(df, "Call Date", "Rep", rep)

def get_calls_summary(rep=None):
    df = get_calls(rep)
    if "Error" in df.columns or df.empty:
        return df
    return (
        df.groupby("Rep")
          .size()
          .reset_index(name="Count")
          .sort_values("Count", ascending=False)
    )

def search_calls_by_date(y,m,d, rep):
    df = load_sheet_df("Calls")
    if "Call Date" not in df.columns:
        return pd.DataFrame([{"Error":"Missing 'Call Date' column"}])
    return filter_date(df, "Call Date", "Rep", y,m,d, rep)

def get_appointments(rep=None):
    df = load_sheet_df("Appointments")
    if "Appointment Date" not in df.columns:
        return pd.DataFrame([{"Error":"Missing 'Appointment Date' column"}])
    return filter_week(df, "Appointment Date", "Rep", rep)

def get_appointments_summary(rep=None):
    df = get_appointments(rep)
    if "Error" in df.columns or df.empty:
        return df
    return (
        df.groupby("Rep")
          .size()
          .reset_index(name="Count")
          .sort_values("Count", ascending=False)
    )

def search_appointments_by_date(y,m,d, rep):
    df = load_sheet_df("Appointments")
    if "Appointment Date" not in df.columns:
        return pd.DataFrame([{"Error":"Missing 'Appointment Date' column"}])
    return filter_date(df, "Appointment Date", "Rep", y,m,d, rep)

def get_leads_detail():
    df = load_sheet_df("AllocatedLeads")
    # rename if needed
    df = df.rename(columns={"Assigned Rep":"Assigned Rep"})
    if "Assigned Rep" not in df.columns:
        return pd.DataFrame([{"Error":"Missing 'Assigned Rep' col"}])
    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()
    appts = get_appointments()
    leads = get_leads_detail()

    def top_rep(df, col):
        if "Error" in df.columns or df.empty:
            return "N/A"
        counts = df.groupby(col).size()
        if counts.empty:
            return "N/A"
        return counts.idxmax()

    top_calls = top_rep(calls, "Rep")
    top_appts = top_rep(appts, "Rep")
    # unify column name for leads
    leads = leads.rename(columns={"Assigned Rep":"Rep"})
    top_leads = top_rep(leads, "Rep")

    return pd.DataFrame([
        {"Metric":"Most Calls This Week",        "Rep":top_calls},
        {"Metric":"Most Appointments This Week", "Rep":top_appts},
        {"Metric":"Most Leads Allocated",        "Rep":top_leads},
    ])

# -------------------- USER MANAGEMENT --------------------
def load_users():
    df = load_sheet_df("userAccess")  # your actual tab name
    # pick & title-case only the cols you want
    wanted = [
        "Id","Email","Name","Business","Role",
        "Daily Phone Call Target","Daily Phone Appointment Target",
        "Daily Quote Number Target","Daily Quote Revenue Target",
        "Weekly Phone Call Target","Weekly Phone Appointment Target",
        "Weekly Quote Number Target","Weekly Quote Revenue Target",
        "Monthly Phone Call Target","Monthly Phone Appointment Target",
        "Monthly Quote Number Target","Monthly Quote Revenue Target",
        "Monthly Sales Revenue Target"
    ]
    exist = [c for c in wanted if c in df.columns]
    return df[exist]

def save_users(df):
    ws = client.open_by_url(SHEET_URL).worksheet("userAccess")
    ws.clear()
    set_with_dataframe(ws, df)
    return "✅ Users saved!"

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

    # ─── Calls Report ─────────────────────────────
    with gr.Tab("Calls Report"):
        rep_calls = gr.Dropdown(
            label="Optional Rep Filter",
            choices=load_sheet_df("Calls")["Rep"].dropna().unique().tolist(),
            allow_custom_value=True
        )
        calls_btn     = gr.Button("Load Current Week Calls")
        calls_summary = gr.Dataframe(label="📊 Calls by Rep")
        calls_table   = gr.Dataframe(label="🔎 Detailed Calls")

        calls_btn.click(
            fn=lambda r: (get_calls_summary(r), get_calls(r)),
            inputs=rep_calls,
            outputs=[calls_summary, calls_table]
        )

        gr.Markdown("### 🔍 Search Calls by Specific Date")
        y1,m1,d1 = gr.Textbox(label="Year"), gr.Textbox(label="Month"), gr.Textbox(label="Day")
        rep1     = gr.Dropdown(
            label="Optional Rep Filter",
            choices=load_sheet_df("Calls")["Rep"].dropna().unique().tolist(),
            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(
            label="Optional Rep Filter",
            choices=load_sheet_df("Appointments")["Rep"].dropna().unique().tolist(),
            allow_custom_value=True
        )
        appt_btn      = gr.Button("Load Current Week Appointments")
        appt_summary  = gr.Dataframe(label="📊 Appts by Rep")
        appt_table    = gr.Dataframe(label="🔎 Detailed Appointments")

        appt_btn.click(
            fn=lambda r: (get_appointments_summary(r), get_appointments(r)),
            inputs=rep_appt,
            outputs=[appt_summary, appt_table]
        )

        gr.Markdown("### 🔍 Search Appointments by Specific Date")
        y2,m2,d2 = gr.Textbox(label="Year"), gr.Textbox(label="Month"), gr.Textbox(label="Day")
        rep2     = gr.Dropdown(
            label="Optional Rep Filter",
            choices=load_sheet_df("Appointments")["Rep"].dropna().unique().tolist(),
            allow_custom_value=True
        )
        appt_date_btn     = gr.Button("Search Appts by Date")
        appt_date_summary = gr.Dataframe(label="📊 Appts Summary by Rep")
        appt_date_table   = gr.Dataframe()

        appt_date_btn.click(
            fn=lambda y,m,d,r: (
                (lambda df: df.groupby("Rep").size().reset_index(name="Count"))(search_appointments_by_date(y,m,d,r)),
                search_appointments_by_date(y,m,d,r)
            ),
            inputs=[y2,m2,d2,rep2],
            outputs=[appt_date_summary, appt_date_table]
        )

    # ─── Appointed Leads ──────────────────────────
    with gr.Tab("Appointed Leads"):
        leads_btn     = gr.Button("View Appointed Leads")
        leads_summary = gr.Dataframe(label="📊 Leads Count by Rep")
        leads_detail  = gr.Dataframe(label="🔎 Detailed Leads")

        leads_btn.click(
            fn=lambda: (get_leads_summary(), get_leads_detail()),
            outputs=[leads_summary, leads_detail]
        )

    # ─── Insights ─────────────────────────────────
    with gr.Tab("Insights"):
        insights_btn = gr.Button("Generate Insights")
        insights_tbl = gr.Dataframe()

        insights_btn.click(fn=compute_insights, outputs=insights_tbl)

    # ─── User Management ──────────────────────────
    with gr.Tab("User Management"):
        gr.Markdown("## 👤 Manage Users\nEdit/add/remove rows below, then click **Save Users**.")
        users_tbl = gr.Dataframe(value=load_users(), interactive=True)
        save_btn  = gr.Button("Save Users")
        status    = gr.Textbox()

        save_btn.click(fn=save_users, inputs=users_tbl, outputs=status)

    # end Blocks
app.launch()