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 LOADING --------------------
def normalize_columns(df):
    df.columns = df.columns.str.strip().str.title()
    return df

def load_sheet_df(tab_name):
    """Load a worksheet into a normalized DataFrame."""
    try:
        ws = client.open_by_url(SHEET_URL).worksheet(tab_name)
        df = pd.DataFrame(ws.get_all_records())
        return normalize_columns(df)
    except Exception as e:
        return pd.DataFrame([{"Error": str(e)}])

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

def rep_options(tab_name):
    """Build a dropdown list of all reps in the given sheet."""
    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 REPORT --------------------
def get_calls(rep=None):
    df = load_sheet_df("Calls")
    if "Call Date" not in df.columns:
        return pd.DataFrame([{"Error":"Missing 'Call Date'"}])
    rep_col = find_rep_column(df)
    return filter_week(df, "Call Date", rep_col, rep)

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

# -------------------- APPOINTMENTS REPORT --------------------
def get_appointments(rep=None):
    df = load_sheet_df("Appointments")
    if "Appointment Date" not in df.columns:
        return pd.DataFrame([{"Error":"Missing 'Appointment Date'"}])
    rep_col = find_rep_column(df)
    return filter_week(df, "Appointment Date", rep_col, rep)

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

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

def get_leads_summary():
    df = get_leads_detail()
    rep_col = find_rep_column(df) or "Assigned Rep"
    if rep_col not in df.columns:
        return pd.DataFrame([{"Error":"Missing rep column in leads"}])
    return df.groupby(rep_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):
        if "Error" in df.columns or df.empty or col not in df.columns:
            return "N/A"
        s = df.groupby(col).size()
        return s.idxmax() if not s.empty else "N/A"

    rep_calls = find_rep_column(calls)
    rep_appts = find_rep_column(appts)
    rep_leads = find_rep_column(leads)

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

# -------------------- USER MANAGEMENT --------------------
def load_users():
    df = load_sheet_df("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 LAYOUT --------------------
with gr.Blocks(title="Graffiti Admin Dashboard") as app:
    gr.Markdown("# 📆 Graffiti Admin Dashboard")

    # Calls Tab
    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(lambda r: (get_calls_summary(r), get_calls(r)),
                        inputs=rep_calls, outputs=[calls_sum, calls_det])

        gr.Markdown("### 🔍 Search Calls by Specific 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 by Date")
        calls_dt_tbl = gr.Dataframe()
        calls_dt_btn.click(fn=search_calls_by_date,
                           inputs=[y1,m1,d1,rep1], outputs=calls_dt_tbl)

    # Appointments Tab
    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 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 Specific 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 Appointments by Date")
        appt_dt_sum = gr.Dataframe(label="📊 Appts Summary by Rep")
        appt_dt_det = gr.Dataframe(label="🔎 Detailed Appts")
        appt_dt_btn.click(
            lambda y,m,d,r: (
                (lambda df: df.groupby(find_rep_column(df)).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 Tab
    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 Tab
    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 Tab
    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()