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)
        df = pd.DataFrame(ws.get_all_records())
        return normalize_columns(df)
    except Exception as e:
        return pd.DataFrame([{"Error": str(e)}])

def rep_options(sheet_name, rep_col="Rep"):
    df = load_sheet_df(sheet_name)
    if rep_col in df.columns:
        return sorted(df[rep_col].dropna().unique().tolist())
    return []

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

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

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

# -------------------- REPORT FUNCTIONS --------------------
def get_calls(rep=None):
    df = load_sheet_df("Calls")
    if "Call Date" not in df.columns:
        return pd.DataFrame([{"Error":"Missing 'Call Date'"}])
    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")

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'"}])
    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'"}])
    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")

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'"}])
    return filter_date(df, "Appointment Date", "Rep", y,m,d, rep)

def get_leads_detail():
    df = load_sheet_df("AllocatedLeads")
    if "Assigned Rep" not in df.columns:
        return pd.DataFrame([{"Error":"Missing 'Assigned Rep'"}])
    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():
    def top_rep(df, col):
        if "Error" in df.columns or df.empty:
            return "N/A"
        counts = df.groupby(col).size()
        return counts.idxmax() if not counts.empty else "N/A"

    calls = get_calls()
    appts = get_appointments()
    leads = get_leads_detail().rename(columns={"Assigned Rep":"Rep"})

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

# -------------------- USER MANAGEMENT --------------------
def load_users():
    df = load_sheet_df("Users")  # your sheet tab is named "Users"
    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"
    ]
    cols  = [c for c in wanted if c in df.columns]
    return df[cols]

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

# -------------------- GRADIO UI --------------------
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"), allow_custom_value=True)
        calls_btn = gr.Button("Load Current Week Calls")
        calls_sum = gr.Dataframe(label="📊 Calls by Rep")
        calls_det = gr.Dataframe(label="🔎 Detailed Calls")
        calls_btn.click(fn=lambda r: (get_calls_summary(r), get_calls(r)),
                        inputs=rep_calls, outputs=[calls_sum, calls_det])

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

    # ─── Appointments Report ────────────
    with gr.Tab("Appointments Report"):
        rep_appt = gr.Dropdown("Optional Rep Filter", choices=rep_options("Appointments"), allow_custom_value=True)
        appt_btn = gr.Button("Load Current Week Appts")
        appt_sum = gr.Dataframe(label="📊 Appts by Rep")
        appt_det = gr.Dataframe(label="🔎 Detailed Appts")
        appt_btn.click(fn=lambda r: (get_appointments_summary(r), get_appointments(r)),
                       inputs=rep_appt, outputs=[appt_sum, appt_det])

        gr.Markdown("### 🔍 Search Appts by Date")
        y2,m2,d2 = gr.Textbox("Year"), gr.Textbox("Month"), gr.Textbox("Day")
        rep2     = gr.Dropdown("Optional Rep Filter", choices=rep_options("Appointments"), allow_custom_value=True)
        appt_dt_btn  = gr.Button("Search")
        appt_dt_sum  = gr.Dataframe(label="📊 Appts Summary by Rep")
        appt_dt_det  = gr.Dataframe(label="🔎 Detailed Appts")
        appt_dt_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_dt_sum, appt_dt_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"):
        ins_btn = gr.Button("Generate Insights")
        ins_tbl = gr.Dataframe()
        ins_btn.click(fn=compute_insights, outputs=ins_tbl)

    # ─── User Management ────────────────
    with gr.Tab("User Management"):
        gr.Markdown("## 👤 Manage Users\nEdit/add/remove rows, then click **Save Users**.")
        users_df  = gr.Dataframe(load_users(), interactive=True)
        save_btn  = gr.Button("Save Users")
        save_stat = gr.Textbox()
        save_btn.click(fn=save_users, inputs=users_df, outputs=save_stat)

app.launch()