app.py

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

# ------------------ AUTH ------------------
VALID_USERS = {
    "andrew@lortechnologies.com": "Pass.123",
    "donovanm@bid4cars.co.za": "Pass.123",
    "grant@bid4cars.co.za": "Pass.123",
    "phonnie@c2group.co.za": "Pass.123"
}

# ------------------ GOOGLE SHEET SETUP ------------------
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"

# ------------------ SHEET ACCESS UTILS ------------------
def load_sheet(sheet_name):
    sheet = client.open_by_url(sheet_url).worksheet(sheet_name)
    data = sheet.get_all_records()
    return pd.DataFrame(data)

# ------------------ REPORT UTILS ------------------
def parse_date(date_str):
    try:
        return pd.to_datetime(date_str, errors='coerce').date()
    except:
        return None

def get_week_range(start_date):
    start = start_date - timedelta(days=start_date.weekday())
    end = start + timedelta(days=6)
    return start, end

def get_month_range(date_obj):
    start = date_obj.replace(day=1)
    next_month = start + pd.DateOffset(months=1)
    end = (next_month - timedelta(days=1)).date()
    return start.date(), end

# ------------------ CALLS ------------------
def filter_calls_by_date_range(start, end):
    df = load_sheet("Calls")
    df['Date'] = pd.to_datetime(df['Date'], errors='coerce').dt.date
    return df[(df['Date'] >= start) & (df['Date'] <= end)]

# ------------------ APPOINTMENTS ------------------
def filter_appointments_by_date_range(start, end):
    df = load_sheet("Appointments")
    df['Date'] = pd.to_datetime(df['Date'], errors='coerce').dt.date
    return df[(df['Date'] >= start) & (df['Date'] <= end)]

# ------------------ APPOINTED LEADS ------------------
def appointed_leads_table():
    df = load_sheet("Appointed Leads")
    return df.groupby('Rep')['Customer Name'].apply(list).reset_index()

# ------------------ INTERACTIVE QUERY ------------------
def search_table(sheet_name, field, keyword):
    df = load_sheet(sheet_name)
    if field not in df.columns:
        return pd.DataFrame(), "Field not found."
    results = df[df[field].astype(str).str.contains(keyword, case=False, na=False)]
    return results, f"Found {len(results)} results."

# ------------------ UI ------------------
with gr.Blocks() as app:
    with gr.Row():
        with gr.Column(visible=True) as login_ui:
            gr.Markdown("## \ud83d\udd10 Login Required")
            email = gr.Textbox(label="Email")
            password = gr.Textbox(label="Password", type="password")
            login_btn = gr.Button("Login")
            login_msg = gr.Markdown("")

        with gr.Column(visible=False) as main_ui:
            gr.Markdown("## \ud83d\udcc2 Graffiti Admin Dashboard")

            with gr.Tab("\ud83d\udcc5 Calls Report"):
                report_type_call = gr.Radio(["Weekly", "Monthly"], label="Report Type")
                date_input_call = gr.Textbox(label="Enter Start Date (YYYY-MM-DD)")
                calls_table = gr.Dataframe()
                
                def generate_call_report(rt, date_str):
                    date = parse_date(date_str)
                    if not date:
                        return pd.DataFrame()
                    start, end = get_week_range(date) if rt == "Weekly" else get_month_range(date)
                    return filter_calls_by_date_range(start, end)
                
                report_type_call.change(generate_call_report, inputs=[report_type_call, date_input_call], outputs=calls_table)
                date_input_call.change(generate_call_report, inputs=[report_type_call, date_input_call], outputs=calls_table)

            with gr.Tab("\ud83d\udcc5 Appointments Report"):
                report_type_appt = gr.Radio(["Weekly", "Monthly"], label="Report Type")
                date_input_appt = gr.Textbox(label="Enter Start Date (YYYY-MM-DD)")
                appt_table = gr.Dataframe()

                def generate_appt_report(rt, date_str):
                    date = parse_date(date_str)
                    if not date:
                        return pd.DataFrame()
                    start, end = get_week_range(date) if rt == "Weekly" else get_month_range(date)
                    return filter_appointments_by_date_range(start, end)

                report_type_appt.change(generate_appt_report, inputs=[report_type_appt, date_input_appt], outputs=appt_table)
                date_input_appt.change(generate_appt_report, inputs=[report_type_appt, date_input_appt], outputs=appt_table)

            with gr.Tab("\ud83e\uddcd Appointed Leads"):
                leads_btn = gr.Button("View Appointed Leads")
                leads_output = gr.Dataframe()
                leads_btn.click(fn=appointed_leads_table, outputs=leads_output)

            with gr.Tab("\ud83d\udd0d Query Live Sheets"):
                sheet_choice = gr.Dropdown(choices=["LiveQuotes", "LiveCustomer", "LiveJobBags"], label="Select Sheet")
                field_input = gr.Textbox(label="Field (column name)")
                keyword_input = gr.Textbox(label="Keyword to search")
                query_btn = gr.Button("Search")
                query_table = gr.Dataframe()
                query_info = gr.Markdown()
                query_btn.click(fn=search_table, inputs=[sheet_choice, field_input, keyword_input], outputs=[query_table, query_info])

    def do_login(user, pw):
        if VALID_USERS.get(user) == pw:
            return gr.update(visible=False), gr.update(visible=True), ""
        else:
            return gr.update(visible=True), gr.update(visible=False), "\u274c Invalid email or password."

    login_btn.click(fn=do_login, inputs=[email, password], outputs=[login_ui, main_ui, login_msg])

app.launch()