app.py

import gradio as gr
import pandas as pd
import gspread
from gspread.exceptions import WorksheetNotFound
from oauth2client.service_account import ServiceAccountCredentials
from datetime import datetime, timedelta
from gspread_dataframe import set_with_dataframe

# -------------------- CONFIG --------------------
SHEET_URL        = "https://docs.google.com/spreadsheets/d/1if4KoVQvw5ZbhknfdZbzMkcTiPfsD6bz9V3a1th-bwQ"
USER_SHEET_NAME  = "Users"   # <-- must match (or contain) your “Users” tab

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

# -------------------- FUZZY WORKSHEET LOOKUP --------------------
def open_ws_by_substring(substr: str):
    """
    Try exact match, then fall back to the first worksheet
    whose title contains `substr` (case-insensitive).
    """
    sh = client.open_by_url(SHEET_URL)
    try:
        return sh.worksheet(substr)
    except WorksheetNotFound:
        for ws in sh.worksheets():
            if substr.lower() in ws.title.lower():
                return ws
    raise WorksheetNotFound(f"No tab matching '{substr}'")

# -------------------- SHEET UTILS --------------------
def normalize_columns(df: pd.DataFrame) -> pd.DataFrame:
    df.columns = df.columns.str.strip().str.title()
    return df

def load_sheet(sheet_name: str) -> pd.DataFrame:
    """Return a DataFrame of the entire sheet, or an Error row."""
    try:
        ws = open_ws_by_substring(sheet_name)
        df = pd.DataFrame(ws.get_all_records())
        return normalize_columns(df)
    except Exception as e:
        return pd.DataFrame([{"Error": str(e)}])

def load_sheet_df(sheet_name: str) -> pd.DataFrame:
    """Like load_sheet, but lets WorksheetNotFound bubble up."""
    ws = open_ws_by_substring(sheet_name)
    df = pd.DataFrame(ws.get_all_records())
    return normalize_columns(df)

# -------------------- DATE FILTER HELPERS --------------------
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_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:
        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:
        out = out[out[rep_col] == rep]
    return out

# -------------------- REPORT FUNCTIONS --------------------
def get_calls(rep=None):
    df = load_sheet("Calls")
    if "Call Date" not in df.columns:
        return df
    return filter_week(df, "Call Date", "Rep", rep)

def search_calls_by_date(y, m, d, rep):
    df = load_sheet("Calls")
    if "Call Date" not in df.columns:
        return df
    return filter_date(df, "Call Date", "Rep", y, m, d, rep)

def get_appointments(rep=None):
    df = load_sheet("Appointments")
    if "Appointment Date" not in df.columns:
        return df
    return filter_week(df, "Appointment Date", "Rep", rep)

def search_appointments_by_date(y, m, d, rep):
    df = load_sheet("Appointments")
    if "Appointment Date" not in df.columns:
        return df
    return filter_date(df, "Appointment Date", "Rep", y, m, d, rep)

def get_leads_detail():
    return load_sheet("AllocatedLeads")

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

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

    def top_rep(df, col):
        if "Error" in df.columns or df.empty or col not in df.columns:
            return "N/A"
        return df.groupby(col).size().idxmax()

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

def rep_options(sheet, col):
    df = load_sheet(sheet)
    if col in df.columns:
        return sorted(df[col].dropna().unique().tolist())
    return []

# -------------------- USER MANAGEMENT --------------------
def load_users() -> pd.DataFrame:
    cols = [
        "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"
    ]
    try:
        return load_sheet_df(USER_SHEET_NAME)
    except WorksheetNotFound:
        return pd.DataFrame(columns=cols)

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

# -------------------- BUILD 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(choices=rep_options("Calls","Rep"),
                                label="Optional Rep Filter",
                                allow_custom_value=True)
        calls_btn = gr.Button("Load Current Week Calls")

        with gr.Row():
            with gr.Column():
                calls_sum = gr.Dataframe(label="📊 Calls by Rep")
            with gr.Column():
                calls_det = gr.Dataframe(label="🔎 Detailed Calls")

        calls_btn.click(
            fn=lambda r: (
                get_calls(r).groupby("Rep")
                            .size()
                            .reset_index(name="Count"),
                get_calls(r)
            ),
            inputs=rep_calls,
            outputs=[calls_sum, calls_det]
        )

        gr.Markdown("### 🔍 Search Calls by Specific Date")
        y1 = gr.Textbox(label="Year")
        m1 = gr.Textbox(label="Month")
        d1 = gr.Textbox(label="Day")
        rep1 = gr.Dropdown(choices=rep_options("Calls","Rep"),
                           label="Optional Rep Filter",
                           allow_custom_value=True)
        calls_date_btn = gr.Button("Search Calls by Date")

        with gr.Row():
            with gr.Column():
                calls_date_sum = gr.Dataframe(label="📊 Calls by Rep on Date")
            with gr.Column():
                calls_date_det = gr.Dataframe(label="🔎 Detailed Calls on Date")

        calls_date_btn.click(
            fn=lambda y,m,d,r: (
                search_calls_by_date(y,m,d,r)
                  .groupby("Rep")
                  .size()
                  .reset_index(name="Count"),
                search_calls_by_date(y,m,d,r)
            ),
            inputs=[y1,m1,d1,rep1],
            outputs=[calls_date_sum, calls_date_det]
        )

    # -- Appointments Report --
    with gr.Tab("Appointments Report"):
        rep_appt = gr.Dropdown(choices=rep_options("Appointments","Rep"),
                               label="Optional Rep Filter",
                               allow_custom_value=True)
        appt_btn  = gr.Button("Load Current Week Appointments")

        with gr.Row():
            with gr.Column():
                appt_sum = gr.Dataframe(label="📊 Weekly Appointments Summary by Rep")
            with gr.Column():
                appt_det = gr.Dataframe(label="🔎 Detailed Appointments")

        appt_btn.click(
            fn=lambda r: (
                get_appointments(r)
                  .groupby("Rep")
                  .size()
                  .reset_index(name="Count"),
                get_appointments(r)
            ),
            inputs=rep_appt,
            outputs=[appt_sum, appt_det]
        )

        gr.Markdown("### 🔍 Search Appointments by Specific Date")
        y2 = gr.Textbox(label="Year")
        m2 = gr.Textbox(label="Month")
        d2 = gr.Textbox(label="Day")
        rep2 = gr.Dropdown(choices=rep_options("Appointments","Rep"),
                           label="Optional Rep Filter",
                           allow_custom_value=True)
        appt_date_btn = gr.Button("Search Appointments by Date")

        with gr.Row():
            with gr.Column():
                appt_date_sum = gr.Dataframe(label="📊 Appts by Rep on Date")
            with gr.Column():
                appt_date_det = gr.Dataframe(label="🔎 Detailed Appts on Date")

        appt_date_btn.click(
            fn=lambda y,m,d,r: (
                search_appointments_by_date(y,m,d,r)
                  .groupby("Rep")
                  .size()
                  .reset_index(name="Count"),
                search_appointments_by_date(y,m,d,r)
            ),
            inputs=[y2,m2,d2,rep2],
            outputs=[appt_date_sum, appt_date_det]
        )

    # -- Appointed Leads --
    with gr.Tab("Appointed Leads"):
        leads_btn = gr.Button("View Appointed Leads")

        with gr.Row():
            with gr.Column():
                leads_sum    = gr.Dataframe(label="📊 Leads Count by Rep")
            with gr.Column():
                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"):
        insights_btn = gr.Button("Generate Insights")
        insights_tbl = gr.Dataframe()
        insights_btn.click(fn=compute_insights, outputs=insights_tbl)

    # -- User Management --
    with gr.Tab("User Management"):
        gr.Markdown("### 🙍 Manage Users  \nEdit the grid and click **Save Users**.")
        users_tbl = gr.Dataframe(value=load_users(), interactive=True)
        save_btn  = gr.Button("Save Users")
        save_msg  = gr.Textbox(interactive=False)
        save_btn.click(fn=save_users, inputs=users_tbl, outputs=save_msg)

    app.launch()