import pandas as pd
import gspread
import gradio as gr
from oauth2client.service_account import ServiceAccountCredentials
from datetime import datetime
from geopy.distance import geodesic
import folium
from io import BytesIO

# Google Sheets Auth
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]
creds = ServiceAccountCredentials.from_json_keyfile_name("credentials.json", scope)
client = gspread.authorize(creds)
sheet_file = client.open("userAccess")

# Load Data
def load_tab(sheet_name):
    try:
        df = pd.DataFrame(sheet_file.worksheet(sheet_name).get_all_records())
        return df
    except:
        return pd.DataFrame(["⚠️ Could not load sheet."], columns=["Error"])

# GPS calculations
def calculate_gps_data(df):
    df = df.sort_values(['Date', 'Time'])
    df[['Latitude', 'Longitude']] = df['Location'].str.split(', ', expand=True).astype(float)
    df['Kms Travelled'] = df[['Latitude', 'Longitude']].shift().apply(
        lambda row: geodesic((row['Latitude'], row['Longitude']), (row.name[0], row.name[1])).km if pd.notnull(row['Latitude']) else 0,
        axis=1)
    df['Duration Between Calls (min)'] = df[['Date', 'Time']].apply(
        lambda row: pd.to_datetime(row['Date'] + ' ' + row['Time']), axis=1
    ).diff().dt.total_seconds().div(60)
    df.fillna({'Kms Travelled': 0, 'Duration Between Calls (min)': 0}, inplace=True)
    return df

# Load and process Field Sales data
field_sales_df = calculate_gps_data(load_tab("Field Sales"))

# Map generation
def generate_map(df):
    if df.empty or df[['Latitude', 'Longitude']].isna().all().all():
        return None

    coords = df[['Latitude', 'Longitude']].dropna().values
    map_center = coords[0]
    m = folium.Map(location=map_center, zoom_start=12)

    for idx, coord in enumerate(coords):
        folium.Marker(location=coord, popup=f"Visit {idx+1}").add_to(m)

    folium.PolyLine(coords, color='blue').add_to(m)

    buf = BytesIO()
    m.save(buf, close_file=False)
    return buf.getvalue().decode()

# Gradio Interface
with gr.Blocks() as app:
    gr.Markdown("## 🚗 CarMat Dashboard")

    unique_dates = sorted(field_sales_df['Date'].unique(), reverse=True)

    # Field Sales Tab
    with gr.Tab("🗺️ Field Sales"):
        date_selector = gr.Dropdown(label="Select Date", choices=unique_dates)
        data_output = gr.DataFrame()
        map_html = gr.HTML()

        def update_field_sales(date):
            day_df = field_sales_df[field_sales_df['Date'] == date]
            map_render = generate_map(day_df)
            return day_df, map_render

        date_selector.change(fn=update_field_sales, inputs=date_selector, outputs=[data_output, map_html])

    # Summary Tab
    with gr.Tab("📊 Summary"):
        date_summary = gr.Dropdown(label="Select Date", choices=unique_dates)
        summary_visits = gr.DataFrame()

        def update_summary(date):
            day_df = field_sales_df[field_sales_df['Date'] == date]
            visits = day_df.groupby("Rep").size().reset_index(name="Total Visits")
            return visits

        date_summary.change(fn=update_summary, inputs=date_summary, outputs=summary_visits)

    # Orders Tab
    with gr.Tab("📦 Orders"):
        order_date = gr.Dropdown(label="Select Date", choices=unique_dates)
        orders_output = gr.DataFrame()

        def orders_summary(date):
            day_df = field_sales_df[field_sales_df['Date'] == date]
            orders_df = day_df[day_df["Order Received"] == "Yes"]
            summary = orders_df.groupby("Rep").agg({
                "Order Value": "sum",
                "Order Received": "count"
            }).rename(columns={"Order Received": "Orders Count"}).reset_index()
            return summary

        order_date.change(fn=orders_summary, inputs=order_date, outputs=orders_output)

app.launch()