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)

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

# -------------------- SELF SOURCED LEADS CONFIG --------------------
SHEET_MAP = {
    "Alice": "https://docs.google.com/spreadsheets/d/18qFpkbE2CwVOgiB6Xz4m2Ep7ZA29p5xV/edit?gid=26019131#gid=26019131",
    "Bob": "https://docs.google.com/spreadsheets/d/1EKngyAvq_3hzQMAOVame2nO9LKPJEV0d/edit?gid=1655961411#gid=1655961411",
    "Charlie": "https://docs.google.com/spreadsheets/d/164OTu1keBC12-5XFUDXMmLOPMkdAjBOM/edit?gid=55672436#gid=55672436",
    "Dave": "https://docs.google.com/spreadsheets/d/1m5e6YXxjK62vtBxYGkJSyHpHT7lnirg6/edit?gid=55672436#gid=55672436"
}

def load_self_sourced_leads(rep_name):
    if rep_name not in SHEET_MAP:
        return pd.DataFrame([{"Error": f"No sheet available for '{rep_name}'"}])
    try:
        sheet = client.open_by_url(SHEET_MAP[rep_name])
        worksheet = sheet.get_worksheet(0)
        data = worksheet.get_all_values()
        if not data:
            return pd.DataFrame([{"Info": "No data available"}])
        return pd.DataFrame(data[1:], columns=data[0])
    except Exception as e:
        return pd.DataFrame([{"Error": str(e)}])

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

def load_sheet_df(name):
    ws = client.open_by_url(SHEET_URL).worksheet(name)
    data = ws.get_all_values()
    if not data:
        return pd.DataFrame()
    raw_header, *rows = data
    counts = Counter()
    header = []
    for col in raw_header:
        counts[col] += 1
        header.append(f"{col}_{counts[col]}" if counts[col] > 1 else 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():
    return load_sheet_df("AllocatedLeads")

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"

    return pd.DataFrame([
        {"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")},
    ])

# -------------------- 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"
    ]
    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)
    return "✅ Users saved!"

# -------------------- GRADIO APP --------------------
with gr.Blocks(title="Graffiti Admin Dashboard") as app:
    gr.Markdown("# 📊 Graffiti Admin Dashboard")

    with gr.Tab("Calls Report"):
        rep = gr.Dropdown(choices=rep_choices("Calls"), label="Rep")
        btn = gr.Button("Load This Week")
        out = gr.Dataframe()
        btn.click(get_calls, rep, out)

    with gr.Tab("Appointments Report"):
        rep2 = gr.Dropdown(choices=rep_choices("Appointments"), label="Rep")
        btn2 = gr.Button("Load This Week")
        out2 = gr.Dataframe()
        btn2.click(get_appointments, rep2, out2)

    with gr.Tab("Allocated Leads"):
        btn3 = gr.Button("Show Leads")
        summary = gr.Dataframe()
        details = gr.Dataframe()
        btn3.click(lambda: (get_leads_summary(), get_leads_detail()), None, [summary, details])

    with gr.Tab("Insights"):
        btn4 = gr.Button("Generate Insights")
        out4 = gr.Dataframe()
        btn4.click(compute_insights, None, out4)

    with gr.Tab("User Management"):
        users_tbl = gr.Dataframe(value=load_users(), interactive=True)
        save_btn = gr.Button("Save Users")
        save_msg = gr.Textbox()
        save_btn.click(save_users, users_tbl, save_msg)

    with gr.Tab("Self Sourced Leads"):
        rep_s = gr.Dropdown(choices=list(SHEET_MAP.keys()), label="Rep")
        btn_s = gr.Button("Load Leads")
        tbl_s = gr.Dataframe()
        btn_s.click(load_self_sourced_leads, rep_s, tbl_s)

app.launch()