app.py

import os
import re
import pandas as pd
import streamlit as st
import snowflake.connector
from cryptography.hazmat.primitives import serialization
from datetime import datetime

# ─── Page Config ────────────────────────────────────────────
st.set_page_config(layout="wide", page_title="Social Auth Tracker", page_icon="📋")

# ─── Snowflake Connection ───────────────────────────────────
def get_conn():
    raw_key = os.getenv("snowflake_private_key")
    priv_key = serialization.load_pem_private_key(raw_key.encode(), password=None)
    return snowflake.connector.connect(
        user=os.getenv("snowflake_user"),
        account=os.getenv("snowflake_account_identifier"),
        private_key=priv_key,
        role=os.getenv("snowflake_role"),
        warehouse=os.getenv("snowflake_warehouse"),
        database=os.getenv("snowflake_database", "ANALYTICS"),
        schema=os.getenv("snowflake_schema", "REVOPS_AWS_FILES")
    )

# ─── Data Loading ───────────────────────────────────────────
@st.cache_data
def load_data():
    conn = get_conn()
    try:
        tbl = f"{conn.database}.{conn.schema}.REVOPS_SALES_SOCIAL_AUTH_TRACKER"
        df = pd.read_sql(f"SELECT * FROM {tbl}", conn)
        df.columns = df.columns.str.lower()
        if 'web_name' in df.columns:
            df['web_name'] = df['web_name'].str.replace('*','',regex=False)
        # Merge extended info
        ext_sql = (
            "SELECT site_id, site_name, entity, creator_partnerships_manager "
            "FROM ANALYTICS.SIGMA_SCRATCH.AVI_SITE_EXTENDED_SNOWFLAKE_WITH_ENTITY"
        )
        df_ext = pd.read_sql(ext_sql, conn)
        df_ext.columns = df_ext.columns.str.lower()
        df_ext['entity_lower'] = df_ext['entity'].str.lower()
        df['web_name_lower'] = df['web_name'].str.lower()
        df = df.merge(
            df_ext[['entity_lower','creator_partnerships_manager']],
            left_on='web_name_lower', right_on='entity_lower', how='left'
        )
        df.drop(columns=['web_name_lower','entity_lower'], inplace=True)
        if 'timestamp' in df.columns:
            df['timestamp'] = pd.to_datetime(df['timestamp'])
        return df
    except Exception as e:
        st.error(f"Error loading data: {e}")
        return pd.DataFrame()
    finally:
        conn.close()

# ─── Troubleshoot Link Generator ────────────────────────────
def make_troubleshoot_link(handle: str) -> str:
    h = handle.lower()
    if 'facebook' in h:
        return 'https://help.raptive.com/hc/en-us/articles/35155011344283-Comscore-Social-Authentication-Facebook-Troubleshooting'
    if 'instagram' in h:
        return 'https://help.raptive.com/hc/en-us/articles/35155497654555-Comscore-Social-Authentication-Instagram-Troubleshooting'
    if 'twitter' in h or 'x' in h:
        return 'https://help.raptive.com/hc/en-us/articles/35154607861531-Comscore-Social-Authorization-X-Twitter-Troubleshooting'
    return ''

# ─── Alert Message Generator ─────────────────────────────────
def generate_alert_message(df_alert: pd.DataFrame) -> str:
    msg = ":rotating_light: **Social Authentication Drop Alert** :rotating_light:\n"
    msg += (
        "We had some social authentications drop. These can be caused by random things like password changes, "
        "changes in payment, leaving the country, etc. If we could reach out to the sites to ask them to reinstate, "
        "that would be helpful! Thank you all!\n\n"
    )
    for manager, group in df_alert.groupby('creator_partnerships_manager'):
        mention = f"**@{manager}**" if manager else "**@Unassigned**"
        msg += f"{mention}\n"
        for site, site_grp in group.groupby('web_name'):
            msg += f"- **{site.upper()}**\n"
            raw_urls = []
            for html in site_grp['social_handle']:
                m = re.search(r'>([^<]+)<', html)
                url = m.group(1) if m else html
                full = url if url.startswith('http') else f'https://{url}'
                raw_urls.append(full)
            fb_urls = [u for u in set(raw_urls) if 'facebook' in u.lower()]
            ig_urls = [u for u in set(raw_urls) if 'instagram' in u.lower()]
            tw_urls = [u for u in set(raw_urls) if 'twitter' in u.lower() or 'x.com' in u.lower()]
            # Disconnected lines
            if fb_urls:
                fb_line = ', '.join(f"[{u}]({u})" for u in fb_urls)
                msg += f"  - Facebook disconnected: {fb_line}\n"
            if ig_urls:
                ig_line = ', '.join(f"[{u}]({u})" for u in ig_urls)
                msg += f"  - Instagram disconnected: {ig_line}\n"
            if tw_urls:
                tw_line = ', '.join(f"[{u}]({u})" for u in tw_urls)
                msg += f"  - Twitter disconnected: {tw_line}\n"
            # Troubleshooting instructions
            msg += "  - Troubleshooting instructions:\n"
            if fb_urls:
                msg += f"    - 🔧 [Facebook troubleshooting instructions]({make_troubleshoot_link(fb_urls[0])})\n"
            if ig_urls:
                msg += f"    - 🔧 [Instagram troubleshooting instructions]({make_troubleshoot_link(ig_urls[0])})\n"
            if tw_urls:
                msg += f"    - 🔧 [Twitter troubleshooting instructions]({make_troubleshoot_link(tw_urls[0])})\n"
        msg += "\n"
    return msg

# ─── App Layout ─────────────────────────────────────────────
st.title("📋 REVOPS Sales Social Auth Tracker with Partnerships & Troubleshooting")

df = load_data()
if df.empty:
    st.write("No data to display.")
else:
    # Date selector
    # Date selector
    df['date_only'] = df['timestamp'].dt.date
    
    # skip NaT so sorting works
    dates = sorted(d for d in df['date_only'].dropna().unique())
    if not dates:
        st.write("No valid dates found.")
        st.stop()
    
    selected_date = st.selectbox(
        "Select date to display:",
        [str(d) for d in dates],
        index=len(dates)-1
    )
    df = df[df['timestamp'].dt.date == datetime.fromisoformat(selected_date).date()]


    # hyperlink handles and add troubleshoot link column
    df['social_handle'] = df['social_handle'].astype(str).apply(
        lambda x: f"<a href='{x if x.startswith('http') else 'https://' + x}' target='_blank'>{x}</a>"
    )
    df['Troubleshoot Link'] = df['social_handle'].apply(
        lambda html: (
            f"<a href='{make_troubleshoot_link(re.search(r'>([^<]+)<', html).group(1))}' target='_blank'>Troubleshoot</a>"
            if re.search(r'>([^<]+)<', html) else ""
        )
    )

    # Reorder timestamp last
    cols = [c for c in df.columns if c != 'timestamp'] + ['timestamp']
    df = df[cols]

    # Collapsed alert preview
    with st.expander("Show Drop Alert Message", expanded=False):
        st.markdown(generate_alert_message(df), unsafe_allow_html=True)

    # Display data table (~25 rows)
    html = df.to_html(escape=False, index=False)
    st.markdown(
        f"<div style='height:800px; overflow-y:auto'>{html}</div>",
        unsafe_allow_html=True
    )