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"

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

# -------------------- QUOTES TAB UTILS --------------------
def get_quotes_df():
    df = load_sheet_df("LiveQuotes")
    df.columns = [c.strip() for c in df.columns]
    return df

def rep_choices_quotes():
    df = get_quotes_df()
    return sorted(df["Rep"].dropna().unique().tolist()) if "Rep" in df else []

def quote_year_choices():
    df = get_quotes_df()
    if "Year" in df.columns:
        years = sorted(df["Year"].dropna().unique().astype(str))
        return years
    if "Date" in df.columns:
        years = pd.to_datetime(df["Date"], errors="coerce").dt.year.dropna().unique()
        return sorted(years.astype(str))
    return []

def quote_month_choices(year=None):
    """
    Returns a sorted list of valid month strings for a given year, always at least [''].
    Also prints debug output to help troubleshoot.
    """
    df = get_quotes_df()
    if (
        year
        and "Year" in df.columns
        and "Month" in df.columns
        and not df.empty
    ):
        subset = df[df["Year"].astype(str) == str(year)]
        if subset.empty:
            print(f"[DEBUG] No quotes found for year {year}. Returning [''].")
            return [""]
        try:
            months = pd.to_numeric(subset["Month"], errors="coerce").dropna().astype(int)
            months = [str(m) for m in months if 1 <= m <= 12]
            months = sorted(set(months))
            result = [""] + months if months else [""]
            print(f"[DEBUG] Year {year}: Months dropdown = {result}")
            return result
        except Exception as e:
            print(f"[DEBUG] Exception in quote_month_choices for year {year}: {e}")
            return [""]
    print(f"[DEBUG] No valid year or columns missing. Returning [''].")
    return [""]

def quotes_summary(year=None, month=None):
    df = get_quotes_df()
    if "Rep" not in df.columns or "Total" not in df.columns:
        return pd.DataFrame([{"Error": "Missing Rep or Total column"}])
    if year and "Year" in df.columns:
        df = df[df["Year"].astype(str) == str(year)]
        if month and "Month" in df.columns:
            df = df[df["Month"].astype(str) == str(month)]
    df["Total"] = pd.to_numeric(df["Total"].astype(str).str.replace(",", ""), errors="coerce")
    summary = (
        df.groupby("Rep")
        .agg({"Document No.": "count", "Total": "sum"})
        .rename(columns={"Document No.": "Total Quotes", "Total": "Total Value"})
        .reset_index()
    )
    summary["Total Value"] = summary["Total Value"].fillna(0).round(2)
    return summary

def get_rep_quotes_filtered(rep, year=None, month=None):
    df = get_quotes_df()
    if "Rep" not in df.columns:
        return pd.DataFrame([{"Error": "Missing Rep column"}])
    df = df[df["Rep"] == rep]
    if year and "Year" in df.columns:
        df = df[df["Year"].astype(str) == str(year)]
        if month and "Month" in df.columns:
            df = df[df["Month"].astype(str) == str(month)]
    return df

def update_month_choices_summary(year):
    months = quote_month_choices(year)
    print(f"[DEBUG] update_month_choices_summary({year}) -> {months}")
    return gr.Dropdown.update(choices=months, value="")

def update_month_choices(year):
    months = quote_month_choices(year)
    print(f"[DEBUG] update_month_choices({year}) -> {months}")
    return gr.Dropdown.update(choices=months, value="")

# -------------------- 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("Allocated 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])

    # -- Quotes Tab (NEW) --
    with gr.Tab("Quotes"):
        gr.Markdown("### 📈 Quotes Summary by Rep")
        year_qs = gr.Dropdown(choices=[""] + quote_year_choices(), label="Year (optional)", value="")
        month_qs = gr.Dropdown(choices=[""], label="Month (optional, needs year)", value="")
        btn_qs = gr.Button("Show Quotes Summary")
        sum_qs = gr.Dataframe(label="Summary by Rep")

        # Dynamic month options for summary
        year_qs.change(update_month_choices_summary, year_qs, month_qs)

        def quotes_summary_wrapper(year, month):
            return quotes_summary(year if year else None, month if month else None)
        btn_qs.click(quotes_summary_wrapper, [year_qs, month_qs], sum_qs)

        gr.Markdown("### 🔎 View All Quotes for a Rep, Year, and Month")
        rep_q = gr.Dropdown(choices=rep_choices_quotes(), label="Select Rep")
        year_q = gr.Dropdown(choices=[""] + quote_year_choices(), label="Year (optional)", value="")
        month_q = gr.Dropdown(choices=[""], label="Month (optional, needs year)", value="")
        btn_qr = gr.Button("Show Quotes")
        tbl_qr = gr.Dataframe(label="Quotes for Selection")

        # Dynamic month options for rep quotes
        year_q.change(update_month_choices, year_q, month_q)

        def get_rep_quotes_filtered_wrapper(rep, year, month):
            return get_rep_quotes_filtered(rep, year if year else None, month if month else None)
        btn_qr.click(get_rep_quotes_filtered_wrapper, [rep_q, year_q, month_q], tbl_qr)

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