app.py

# ==============================================================
# 🏷️ Trending Jobs Tracker 🔥  (with 1‑Month Visual Analytics)
# ==============================================================

import gradio as gr
import pandas as pd
import plotly.express as px
from datetime import datetime, timedelta, timezone
import os
import threading

# ---------- FILES ----------
DATA_FILE = "job_tracking.csv"
EXCEL_FILE = "trending_summary.xlsx"
COLLAB_FILE = "collab_edits.csv"
lock = threading.Lock()

# ensure base CSV exists
if not os.path.exists(DATA_FILE):
    df_init = pd.DataFrame(columns=["job_id", "ad_title", "moderator", "tags", "timestamp"])
    df_init.to_csv(DATA_FILE, index=False)

# ✅ includes "GUNS" tag
TAGS = ["CSR","IMMIG","PVG","ECON","CRIME","SFP",
        "ENVI","EDUC","HEALTH","SIP","COMMS","NT","GUNS"]

# ---------- CORE FUNCTIONS ----------

def load_data():
    if os.path.exists(DATA_FILE):
        return pd.read_csv(DATA_FILE)
    return pd.DataFrame(columns=["job_id","ad_title","moderator","tags","timestamp"])

def save_data(df):
    with lock:
        df.to_csv(DATA_FILE, index=False)

def add_entry(job_id, ad_title, moderator, tags):
    df = load_data()
    if not job_id or not ad_title or not moderator or not tags:
        trending = summarize_trending(df)
        return trending, "⚠️ Please complete all fields!", job_id, ad_title, moderator, tags

    # 🇵🇭 Philippine Standard Time
    ph_tz = timezone(timedelta(hours=8))
    timestamp = datetime.now(ph_tz).strftime("%Y-%m-%d %H:%M")

    tags_str = ", ".join(sorted(tags))
    new_row = {
        "job_id": str(job_id),
        "ad_title": ad_title,
        "moderator": moderator,
        "tags": tags_str,
        "timestamp": timestamp,
    }
    df = pd.concat([df, pd.DataFrame([new_row])], ignore_index=True)
    save_data(df)

    trending = summarize_trending(df)
    msg = f"✅ Entry added for Job {job_id} by {moderator} at {timestamp} (PHT)"
    return trending, msg, "", "", moderator, []  # clears inputs except Moderator

def determine_status(list_of_tagsets):
    tag_sets = [set(t.replace(" ", "").split(",")) for t in list_of_tagsets]
    if all(tag_sets[0] == s for s in tag_sets):
        return "✅ Consistent"
    intersection = set.intersection(*tag_sets)
    return "🟡 Partially Consistent" if intersection else "❌ Conflict"

def summarize_trending(df):
    if df.empty:
        return pd.DataFrame(columns=[
            "Job ID","Ad Title","Count","Collaborated Tag (editable)",
            "Status","Moderators / Tags / Date + Time"
        ])
    grouped = (
        df.groupby(["job_id","ad_title"])
          .agg({"tags":list,"moderator":list,"timestamp":list,"job_id":"count"})
          .rename(columns={"job_id":"count"}).reset_index()
    )

    records = []
    for _, r in grouped.iterrows():
        if r["count"] < 2: 
            continue
        mods, tags_list, times = r["moderator"], r["tags"], r["timestamp"]
        all_tags = [t.strip() for s in tags_list for t in s.split(",")]
        collab = ", ".join(sorted(set(all_tags)))
        status = determine_status(tags_list)
        job_id_disp = f"🔥 {r['job_id']}" if r["count"] >= 3 else r["job_id"]
        entries = [f"{m} - {t} ({ts} PHT)" for m, t, ts in zip(mods, tags_list, times)]
        joined = "\n".join(entries)
        records.append({
            "Job ID": job_id_disp,
            "Ad Title": r["ad_title"],
            "Count": r["count"],
            "Collaborated Tag (editable)": collab,
            "Status": status,
            "Moderators / Tags / Date + Time": joined
        })
    out = pd.DataFrame(records)

    # merge manual edits
    if os.path.exists(COLLAB_FILE) and not out.empty:
        saved = pd.read_csv(COLLAB_FILE)
        out = out.merge(saved[["Job ID","Collaborated Tag (editable)"]],
                        on="Job ID", how="left", suffixes=('','_saved'))
        out["Collaborated Tag (editable)"] = (
            out["Collaborated Tag (editable)_saved"]
              .combine_first(out["Collaborated Tag (editable)"])
        )
        out.drop(columns=["Collaborated Tag (editable)_saved"], inplace=True)
    return out

def update_collab_table(edited_df):
    if edited_df is not None and not edited_df.empty:
        edited_df[["Job ID","Collaborated Tag (editable)"]].to_csv(COLLAB_FILE,index=False)
    return edited_df,"💾 Collaborated tags saved and will persist after refresh."

def refresh_data():
    df = load_data()
    return summarize_trending(df), "🔄 Data refreshed."

def export_to_excel():
    df = load_data()
    summary = summarize_trending(df)
    if summary.empty:
        pd.DataFrame({"Info":["No entries available"]}).to_excel(EXCEL_FILE,index=False)
        return EXCEL_FILE
    summary["Trend Indicator"] = summary["Count"].apply(lambda x:"🔥 Most Trending" if x>=3 else "")
    summary = summary.sort_values(by="Count", ascending=False)
    summary = summary[["Job ID","Ad Title","Count","Collaborated Tag (editable)",
                       "Status","Trend Indicator","Moderators / Tags / Date + Time"]]
    with pd.ExcelWriter(EXCEL_FILE, engine="openpyxl") as w:
        summary.to_excel(w, index=False, sheet_name="Trending Summary")
    return EXCEL_FILE

def search_data(query):
    df = summarize_trending(load_data())
    if not query:
        return df, f"Showing {len(df)} rows."
    q = str(query).lower()
    mask = df.apply(lambda row: any(q in str(x).lower() for x in row), axis=1)
    filtered = df[mask]
    return filtered, f"Found {len(filtered)} matching rows."

# ---------- ANALYTICS ----------

def compute_analytics(df):
    """Compute tag + moderator summaries for last 30 days."""
    if df.empty:
        return pd.DataFrame(columns=["Tag","Frequency"]), pd.DataFrame(columns=["Moderator","Entries"])
    # parse dates
    df["timestamp_dt"] = pd.to_datetime(df["timestamp"], errors="coerce")
    ph_tz = timezone(timedelta(hours=8))
    now = datetime.now(ph_tz)
    cutoff = now - timedelta(days=30)
    df_recent = df[df["timestamp_dt"] >= cutoff]
    if df_recent.empty:
        return pd.DataFrame(columns=["Tag","Frequency"]), pd.DataFrame(columns=["Moderator","Entries"])
    tag_counts = (
        df_recent["tags"]
        .str.split(",")
        .explode()
        .str.strip()
        .value_counts()
        .reset_index()
        .rename(columns={"index":"Tag","tags":"Frequency"})
    )
    mod_counts = (
        df_recent["moderator"]
        .value_counts()
        .reset_index()
        .rename(columns={"index":"Moderator","moderator":"Entries"})
    )
    # For time series aggregation
    df_recent["date"] = df_recent["timestamp_dt"].dt.date
    daily_counts = (
        df_recent.groupby("date")["job_id"].count().reset_index(name="Entries")
    )
    return tag_counts, mod_counts, daily_counts

def generate_visuals():
    df = load_data()
    if df.empty:
        return None, None, None, "No data available for visualization."
    tags_df, mods_df, daily_df = compute_analytics(df)
    if tags_df.empty and mods_df.empty:
        return None, None, None, "No entries in the past 30 days."

    fig_tags = px.bar(
        tags_df.head(10),
        x="Tag",
        y="Frequency",
        title="🔥 Top Trending Tags (Last 30 Days)",
        color="Frequency",
        color_continuous_scale="Sunset"
    )
    fig_mods = px.bar(
        mods_df.head(10),
        x="Moderator",
        y="Entries",
        title="👩‍💻 Most Active Moderators (Last 30 Days)",
        color="Entries",
        color_continuous_scale="Peach"
    )
    fig_daily = px.line(
        daily_df,
        x="date",
        y="Entries",
        title="📆 Activity Trend (Last 30 Days)",
        markers=True
    )

    count_msg = f"Showing top 10 tags and moderators from {len(df)} total entries (Last 30 Days filter)."
    return fig_tags, fig_mods, fig_daily, count_msg

# ---------- UI CSS ----------
demo_css = """
.dataframe table{table-layout:fixed;width:100%;}
.dataframe th:nth-child(1),.dataframe td:nth-child(1){width:8%;}
.dataframe th:nth-child(2),.dataframe td:nth-child(2){width:12%;}
.dataframe th:nth-child(3),.dataframe td:nth-child(3){width:8%;}
.dataframe th:nth-child(4),.dataframe td:nth-child(4){width:25%;}
.dataframe th:nth-child(5),.dataframe td:nth-child(5){width:12%;}
.dataframe th:nth-child(6),.dataframe td:nth-child(6){width:25%;}
.dataframe td,.dataframe th{word-wrap:break-word;white-space:normal!important;}
.dataframe tr:hover td{background:#f2f2f2;}
"""

# ---------- GRADIO APP ----------
with gr.Blocks(title="Trending Jobs Tracker 🔥", css=demo_css) as demo:
    gr.Markdown("# 🏷️ Trending Jobs Tracker 🔥")
    gr.Markdown("Times displayed in **Philippine Standard Time (GMT + 8)**")

    with gr.Row():
        job_id = gr.Textbox(label="Job ID (last 4 digits)")
        ad_title = gr.Textbox(label="Ad Title")
        moderator = gr.Textbox(label="Moderator Name", value="")

    tags = gr.CheckboxGroup(TAGS, label="Select Tag(s)", interactive=True)
    with gr.Row():
        add_btn = gr.Button("Add Entry ✅", variant="primary")
        refresh_btn = gr.Button("Refresh Data 🔄")

    search_box = gr.Textbox(label="🔍 Search by Job ID or Ad Title", placeholder="Type to filter")
    search_btn = gr.Button("Search 🔍")

    msg = gr.Markdown("")
    table = gr.Dataframe(
        headers=["Job ID","Ad Title","Count","Collaborated Tag (editable)",
                 "Status","Moderators / Tags / Date + Time"],
        datatype=["str","str","number","str","str","str"],
        interactive=True, wrap=True,
        label="Trending Jobs (Editable Collaborated Tag)"
    )

    save_btn = gr.Button("💾 Save Edited Collaborated Tags")
    export_btn = gr.Button("Generate Excel 📊")
    download_btn = gr.File(label="📊 Download Trending Summary (Excel)")

    add_btn.click(add_entry, inputs=[job_id, ad_title, moderator, tags],
                  outputs=[table, msg, job_id, ad_title, moderator, tags])
    refresh_btn.click(refresh_data, outputs=[table, msg])
    save_btn.click(update_collab_table, inputs=[table], outputs=[table, msg])
    export_btn.click(export_to_excel, outputs=download_btn)
    search_btn.click(search_data, inputs=[search_box], outputs=[table, msg])

    gr.Markdown("---")
    gr.Markdown("**Legend:** ✅ Consistent | 🟡 Partial | ❌ Conflict | 🔥 Most Trending (Count ≥ 3)")
    gr.Markdown("Built for auditing and internal use © 2025")

    # ---------- VISUAL ANALYTICS ----------
    gr.Markdown("## 📊 Visual Analytics (Last 30 Days)")
    with gr.Row():
        plot_tags = gr.Plot(label="Top Trending Tags")
        plot_mods = gr.Plot(label="Most Active Moderators")
    plot_daily = gr.Plot(label="Activity Over Time (Last 30 Days)")
    viz_msg = gr.Markdown("")
    viz_btn = gr.Button("Generate Visuals 📈")

    viz_btn.click(generate_visuals, outputs=[plot_tags, plot_mods, plot_daily, viz_msg])

demo.launch()