app.py

import gradio as gr
import pandas as pd
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from datetime import datetime, timedelta

# -------------------- 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(df: pd.DataFrame) -> pd.DataFrame:
    df.columns = df.columns.str.strip().str.title()
    return df

# Replace get_all_records() to avoid duplicate-header errors
def load_sheet(sheet_name: str) -> pd.DataFrame:
    try:
        ws = client.open_by_url(sheet_url).worksheet(sheet_name)
        all_values = ws.get_all_values()
        if not all_values:
            return pd.DataFrame()
        headers = [h.strip().title() for h in all_values[0]]
        data = all_values[1:]
        return pd.DataFrame(data, columns=headers)
    except Exception as e:
        return pd.DataFrame([{"Error": str(e)}])

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

def filter_week(df: pd.DataFrame, date_col: str, rep_col: str = None, rep=None):
    if date_col not in df.columns:
        return df
    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 df.columns:
        out = out[out[rep_col] == rep]
    return out

def filter_date(df: pd.DataFrame, date_col: str, rep_col: str, y, m, d, rep):
    try:
        target = datetime(int(y), int(m), int(d)).date()
    except:
        return pd.DataFrame([{"Error": "Invalid date input"}])
    if date_col not in df.columns:
        return pd.DataFrame([{"Error": f"Missing '{date_col}' column"}])
    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 df.columns:
        out = out[out[rep_col] == rep]
    return out

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

def get_appointments(rep=None):
    df = load_sheet("Appointments")
    return filter_week(df, "Appointment Date", "Rep", rep)

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

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

# Leads

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

def get_leads_summary():
    df = get_leads_detail()
    if "Assigned Rep" not in df.columns:
        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):
        if col in df.columns and not df.empty:
            try:
                return df[col].mode()[0]
            except:
                return "N/A"
        return "N/A"
    insights = 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")},
    ])
    return insights

# -------------------- DROPDOWN OPTIONS --------------------
def rep_options(sheet_name, rep_col):
    df = load_sheet(sheet_name)
    if rep_col in df.columns:
        return sorted(df[rep_col].dropna().unique().tolist())
    return []

# -------------------- USER MANAGEMENT --------------------
def save_users(df):
    ws = client.open_by_url(sheet_url).worksheet("User")
    headers = df.columns.tolist()
    rows = df.fillna("").values.tolist()
    ws.clear()
    ws.update([headers] + rows)
    return pd.DataFrame([{"Status": "Users saved."}])

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

    with gr.Tab("Calls Report"):
        rep_calls = gr.Dropdown(label="Optional Rep Filter",
                                choices=rep_options("Calls","Rep"),
                                allow_custom_value=True)
        calls_btn = gr.Button("Load Current Week Calls")
        calls_tbl = gr.Dataframe()
        calls_btn.click(fn=get_calls, inputs=rep_calls, outputs=calls_tbl)

        gr.Markdown("### 🔍 Search Calls by Specific Date")
        y1,m1,d1 = gr.Textbox(label="Year"), gr.Textbox(label="Month"), gr.Textbox(label="Day")
        rep1 = gr.Dropdown(label="Optional Rep Filter",
                           choices=rep_options("Calls","Rep"),
                           allow_custom_value=True)
        calls_date_btn = gr.Button("Search Calls by Date")
        calls_date_tbl = gr.Dataframe()
        calls_date_btn.click(fn=search_calls_by_date,
                              inputs=[y1,m1,d1,rep1],
                              outputs=calls_date_tbl)

    with gr.Tab("Appointments Report"):
        rep_appt = gr.Dropdown(label="Optional Rep Filter",
                               choices=rep_options("Appointments","Rep"),
                               allow_custom_value=True)
        appt_btn = gr.Button("Load Current Week Appointments")
        appt_summary = gr.Dataframe(label="📊 Weekly Appointments Summary by Rep")
        appt_tbl = gr.Dataframe()
        appt_btn.click(
            fn=lambda rep: (
                get_appointments(rep).groupby("Rep").size().reset_index(name="Count"),
                get_appointments(rep)
            ),
            inputs=rep_appt,
            outputs=[appt_summary, appt_tbl]
        )

        gr.Markdown("### 🔍 Search Appointments by Specific Date")
        y2,m2,d2 = gr.Textbox(label="Year"), gr.Textbox(label="Month"), gr.Textbox(label="Day")
        rep2 = gr.Dropdown(label="Optional Rep Filter",
                           choices=rep_options("Appointments","Rep"),
                           allow_custom_value=True)
        appt_date_btn = gr.Button("Search Appointments by Date")
        appt_date_sum = gr.Dataframe(label="📊 Appointments Summary for Date by Rep")
        appt_date_tbl = gr.Dataframe()
        appt_date_btn.click(
            fn=lambda y,m,d,rep: (
                search_appointments_by_date(y,m,d,rep)
                    .groupby("Rep").size().reset_index(name="Count"),
                search_appointments_by_date(y,m,d,rep)
            ),
            inputs=[y2,m2,d2,rep2],
            outputs=[appt_date_sum, appt_date_tbl]
        )

    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(fn=lambda: (get_leads_summary(), get_leads_detail()),
                        outputs=[leads_sum, leads_det])

    with gr.Tab("Insights"):
        insights_btn = gr.Button("Generate Insights")
        insights_tbl = gr.Dataframe()
        insights_btn.click(fn=compute_insights, outputs=insights_tbl)

    with gr.Tab("User Management"):
        gr.Markdown("## 👤 Manage Users\nEdit the grid and click **Save Users** to push changes.")
        users_df = load_sheet("User")
        users_grid = gr.Dataframe(value=users_df, interactive=True)
        save_btn = gr.Button("Save Users")
        status = gr.Dataframe()
        save_btn.click(fn=save_users, inputs=users_grid, outputs=status)

app.launch()