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)

# -------------------- WORKSHEET HELPERS --------------------
def open_ws(name_substr):
    """
    Try to open a worksheet:
    1. exact match on title
    2. first sheet whose title contains name_substr (case-insensitive)
    """
    sh = client.open_by_url(SHEET_URL)
    # 1) exact
    try:
        return sh.worksheet(name_substr)
    except gspread.WorksheetNotFound:
        pass

    # 2) contains substring
    for ws in sh.worksheets():
        if name_substr.lower() in ws.title.lower():
            return ws
    raise gspread.WorksheetNotFound(f"No tab matching '{name_substr}'")

def load_sheet_df(tab_name):
    """Load & normalize the sheet named by tab_name (substring)."""
    try:
        ws = open_ws(tab_name)
        df = pd.DataFrame(ws.get_all_records())
        df.columns = df.columns.str.strip().str.title()
        return df
    except Exception as e:
        return pd.DataFrame([{"Error": str(e)}])

def find_rep_column(df):
    """Find the first column whose header contains 'rep' (case-insensitive)."""
    for c in df.columns:
        if "rep" in c.lower():
            return c
    return None

def rep_options(tab_name):
    """Return a list of all unique reps in the given tab (or empty list)."""
    df = load_sheet_df(tab_name)
    rep_col = find_rep_column(df)
    if rep_col:
        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, rep):
    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

# -------------------- CALLS --------------------
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", find_rep_column(df), rep)

def get_calls_summary(rep=None):
    df = get_calls(rep)
    if "Error" in df.columns or df.empty:
        return df
    col = find_rep_column(df)
    return df.groupby(col).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' column"}])
    return filter_date(df, "Call Date", find_rep_column(df), y,m,d, rep)

# -------------------- APPOINTMENTS --------------------
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", find_rep_column(df), rep)

def get_appointments_summary(rep=None):
    df = get_appointments(rep)
    if "Error" in df.columns or df.empty:
        return df
    col = find_rep_column(df)
    return df.groupby(col).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' column"}])
    return filter_date(df, "Appointment Date", find_rep_column(df), y,m,d, rep)

# -------------------- APPOINTED LEADS --------------------
def get_leads_detail():
    return load_sheet_df("Leads")

def get_leads_summary():
    df = get_leads_detail()
    col = find_rep_column(df) or "Assigned Rep"
    if col not in df.columns:
        return pd.DataFrame([{"Error":"Missing rep column in leads"}])
    return df.groupby(col).size().reset_index(name="Leads Count")

# -------------------- INSIGHTS --------------------
def compute_insights():
    calls = get_calls()
    appts = get_appointments()
    leads = get_leads_detail()

    def top(df):
        col = find_rep_column(df)
        if "Error" in df.columns or df.empty or not col:
            return "N/A"
        s = df.groupby(col).size()
        return s.idxmax() if not s.empty else "N/A"

    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("Users")
    want = [
        "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 want if c in df.columns]
    return df[cols]

def save_users(df):
    ws = open_ws("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 ---
    with gr.Tab("Calls Report"):
        rc = rep_options("Calls")
        rep_calls = gr.Dropdown(rc or ["(no reps found)"],
                                label="Optional Rep Filter",
                                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(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(label="Year"), gr.Textbox(label="Month"), gr.Textbox(label="Day")
        rep1      = gr.Dropdown(rc or ["(no reps found)"],
                                label="Optional Rep Filter", allow_custom_value=True)
        calls_dt_btn = gr.Button("Search Calls by Date")
        calls_dt_tbl = gr.Dataframe()
        calls_dt_btn.click(search_calls_by_date,
                           inputs=[y1,m1,d1,rep1], outputs=calls_dt_tbl)

    # --- Appointments ---
    with gr.Tab("Appointments Report"):
        ra = rep_options("Appointments")
        rep_appt = gr.Dropdown(ra or ["(no reps found)"],
                               label="Optional Rep Filter",
                               allow_custom_value=True)
        appt_btn  = gr.Button("Load Current Week Appointments")
        appt_sum  = gr.Dataframe(label="📊 Appts by Rep")
        appt_det  = gr.Dataframe(label="🔎 Detailed Appts")
        appt_btn.click(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(label="Year"), gr.Textbox(label="Month"), gr.Textbox(label="Day")
        rep2     = gr.Dropdown(ra or ["(no reps found)"],
                               label="Optional Rep Filter", allow_custom_value=True)
        appt_dt_btn = gr.Button("Search Appts by Date")
        appt_dt_sum = gr.Dataframe(label="📊 Appts by Rep")
        appt_dt_det = gr.Dataframe(label="🔎 Detailed Appts")
        appt_dt_btn.click(search_appointments_by_date,
                          inputs=[y2,m2,d2,rep2],
                          outputs=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(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(compute_insights, outputs=ins_tbl)

    # --- User Management ---
    with gr.Tab("User Management"):
        gr.Markdown("## 👤 Manage Users — edit/add/remove, then Save")
        users_df = gr.Dataframe(load_users(), interactive=True)
        save_btn = gr.Button("Save Users")
        save_out = gr.Textbox()
        save_btn.click(save_users, inputs=users_df, outputs=save_out)

app.launch()