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
from collections import Counter

# -------------------- 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)

# YOUR SPREADSHEET URL
SHEET_URL = "https://docs.google.com/spreadsheets/d/1if4KoVQvw5ZbhknfdZbzMkcTiPfsD6bz9V3a1th-bwQ"

# -------------------- UTILS --------------------
def normalize_columns(cols):
    return [c.strip().title() for c in cols]

def load_sheet_df(name):
    """
    Load a sheet into a DataFrame without confusing duplicates in
    the header row. We fetch all values, dedupe the first row,
    then build a DataFrame.
    """
    ws = client.open_by_url(SHEET_URL).worksheet(name)
    data = ws.get_all_values()
    if not data:
        return pd.DataFrame()
    raw_header, *rows = data
    # make header unique
    counts = Counter()
    header = []
    for col in raw_header:
        counts[col] += 1
        if counts[col] > 1:
            header.append(f"{col}_{counts[col]}")
        else:
            header.append(col)
    header = normalize_columns(header)
    return pd.DataFrame(rows, columns=header)

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

def filter_by_week(df, date_col, rep=None):
    if date_col not in df.columns:
        return pd.DataFrame([{"Error": f"Missing '{date_col}' column"}])
    df = df.copy()
    df[date_col] = pd.to_datetime(df[date_col], errors="coerce").dt.date
    start, end = get_current_week_range()
    m = df[date_col].between(start, end)
    if rep:
        m &= df.get("Rep", pd.Series()).astype(str) == rep
    return df[m]

def filter_by_date(df, date_col, y, m, d, rep=None):
    try:
        target = datetime(int(y), int(m), int(d)).date()
    except:
        return pd.DataFrame([{"Error": "Invalid date"}])
    if date_col not in df.columns:
        return pd.DataFrame([{"Error": f"Missing '{date_col}' column"}])
    df = df.copy()
    df[date_col] = pd.to_datetime(df[date_col], errors="coerce").dt.date
    m = df[date_col] == target
    if rep:
        m &= df.get("Rep", pd.Series()).astype(str) == rep
    return df[m]

def rep_choices(sheet, col="Rep"):
    df = load_sheet_df(sheet)
    return sorted(df[col].dropna().unique().tolist()) if col in df else []

# -------------------- REPORT FUNCTIONS --------------------
def get_calls(rep=None):
    df = load_sheet_df("Calls")
    return filter_by_week(df, "Call Date", rep)

def get_appointments(rep=None):
    df = load_sheet_df("Appointments")
    return filter_by_week(df, "Appointment Date", rep)

def search_calls(y, m, d, rep=None):
    df = load_sheet_df("Calls")
    return filter_by_date(df, "Call Date", y, m, d, rep)

def search_appointments(y, m, d, rep=None):
    df = load_sheet_df("Appointments")
    return filter_by_date(df, "Appointment Date", y, m, d, rep)

# -------------------- LEADS --------------------
def get_leads_detail():
    df = load_sheet_df("AllocatedLeads")
    return df

def get_leads_summary():
    df = get_leads_detail()
    if "Assigned Rep" not in df:
        return pd.DataFrame([{"Error": "Missing 'Assigned Rep'"}])
    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(df, col="Rep"):
        if col in df and not df.empty:
            vc = df[col].value_counts()
            return vc.idxmax() if not vc.empty else "N/A"
        return "N/A"

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

# -------------------- USER MANAGEMENT --------------------
def load_users():
    df = load_sheet_df("Users")
    # select & rename your columns as needed
    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"
    ]
    exist = [c for c in want if c in df.columns]
    return df[exist]

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

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

    # -- Calls Tab --
    with gr.Tab("Calls Report"):
        rep_c = gr.Dropdown(choices=rep_choices("Calls"), label="Filter by Rep", allow_custom_value=True)
        btn_c = gr.Button("Load This Week’s Calls")
        tbl_c = gr.Dataframe()
        btn_c.click(get_calls, rep_c, tbl_c)

        gr.Markdown("### Search Calls by Date")
        y1, m1, d1 = gr.Textbox(label="Year"), gr.Textbox(label="Month"), gr.Textbox(label="Day")
        rep_c2 = gr.Dropdown(choices=rep_choices("Calls"), label="Filter by Rep", allow_custom_value=True)
        btn_c2 = gr.Button("Search")
        tbl_c2 = gr.Dataframe()
        btn_c2.click(search_calls, [y1, m1, d1, rep_c2], tbl_c2)

    # -- Appointments Tab --
    with gr.Tab("Appointments Report"):
        rep_a = gr.Dropdown(choices=rep_choices("Appointments"), label="Filter by Rep", allow_custom_value=True)
        btn_a = gr.Button("Load This Week’s Appts")
        sum_a = gr.Dataframe(label="📊 Appts by Rep")
        tbl_a = gr.Dataframe()
        def _load_appts(r):
            df = get_appointments(r)
            return df.groupby("Rep").size().reset_index(name="Count"), df
        btn_a.click(_load_appts, rep_a, [sum_a, tbl_a])

        gr.Markdown("### Search Appts by Date")
        y2, m2, d2 = gr.Textbox(label="Year"), gr.Textbox(label="Month"), gr.Textbox(label="Day")
        rep_a2 = gr.Dropdown(choices=rep_choices("Appointments"), label="Filter by Rep", allow_custom_value=True)
        btn_a2 = gr.Button("Search")
        sum_a2 = gr.Dataframe(label="📊 Appts by Rep")
        tbl_a2 = gr.Dataframe()
        def _search_appts(y,m,d,r):
            df = search_appointments(y,m,d,r)
            return df.groupby("Rep").size().reset_index(name="Count"), df
        btn_a2.click(_search_appts, [y2,m2,d2,rep_a2], [sum_a2, tbl_a2])

    # -- Appointed Leads --
    with gr.Tab("Appointed Leads"):
        btn_l = gr.Button("View Leads")
        sum_l = gr.Dataframe(label="📊 Leads by Rep")
        det_l = gr.Dataframe(label="🔎 Details")
        btn_l.click(lambda: (get_leads_summary(), get_leads_detail()), None, [sum_l, det_l])

    # -- Insights --
    with gr.Tab("Insights"):
        btn_i = gr.Button("Generate Insights")
        out_i = gr.Dataframe()
        btn_i.click(compute_insights, None, out_i)

    # -- User Management --
    with gr.Tab("User Management"):
        gr.Markdown("## 👤 Manage Users\nEdit the grid below then click **Save Users** to push back to the sheet.")
        users_tbl = gr.Dataframe(value=load_users(), interactive=True)
        save_btn = gr.Button("Save Users")
        save_out = gr.Textbox()
        save_btn.click(save_users, users_tbl, save_out)

    app.launch()