"""Seated List Export — Streamlit in Snowflake app.

Transforms RAW_SEATED_OPT_INS data into the Fansifter CRM upload template
and provides a CSV download with unmasked PII.
"""

import streamlit as st
import pandas as pd
from datetime import date, timedelta

import queries

st.set_page_config(page_title="Seated List Export", page_icon="🎫", layout="wide")


# ── Snowflake session ────────────────────────────────────────────────────────

@st.cache_resource
def get_session():
    return st.connection("snowflake").session()


session = get_session()


# ── Helpers ──────────────────────────────────────────────────────────────────

@st.cache_data(ttl=600)
def fetch_artists() -> list[str]:
    df = session.sql(queries.artist_list_query()).to_pandas()
    return df["ARTIST_NAME"].tolist()


@st.cache_data(ttl=600)
def fetch_sources() -> list[str]:
    df = session.sql(queries.source_list_query()).to_pandas()
    return df["SOURCE"].tolist()


def fetch_date_range(artist: str) -> tuple[date, date]:
    df = session.sql(queries.date_range_query(artist)).to_pandas()
    row = df.iloc[0]
    min_d = pd.to_datetime(row["MIN_DATE"]).date() if pd.notna(row["MIN_DATE"]) else date.today() - timedelta(days=365)
    max_d = pd.to_datetime(row["MAX_DATE"]).date() if pd.notna(row["MAX_DATE"]) else date.today()
    return min_d, max_d


# ── Sidebar filters ─────────────────────────────────────────────────────────

st.sidebar.title("🎫 Seated List Export")
st.sidebar.markdown("Export Seated opt-in data formatted for Fansifter CRM upload.")

artists = fetch_artists()
artist = st.sidebar.selectbox("Artist", artists, index=None, placeholder="Select an artist…")

if artist:
    min_date, max_date = fetch_date_range(artist)
    today = date.today()
    # Default to today's report date for daily exports; clamp to available range
    default_date = min(max(today, min_date), max_date)

    col1, col2 = st.sidebar.columns(2)
    start_date = col1.date_input("Report date (start)", value=default_date, min_value=min_date, max_value=max_date)
    end_date = col2.date_input("Report date (end)", value=default_date, min_value=min_date, max_value=max_date)

    all_sources = fetch_sources()
    sources = st.sidebar.multiselect("Source filter", all_sources, default=all_sources)

    label = st.sidebar.text_input("Label (Required)", placeholder="e.g. Sony Music Nashville")

    invalid_date_range = start_date > end_date
    if invalid_date_range:
        st.sidebar.error("Report date (end) must be on or after report date (start).")

    run = st.sidebar.button("Generate Export", type="primary", disabled=(not label.strip() or invalid_date_range))
else:
    run = False


# ── Main content ─────────────────────────────────────────────────────────────

if not artist:
    st.title("Seated List Export")
    st.info("Select an artist from the sidebar to get started.")
    st.stop()

if not run and "export_df" not in st.session_state:
    st.title("Seated List Export")
    st.info("Configure filters and click **Generate Export**.")
    st.stop()

if run:
    with st.spinner("Querying Snowflake…"):
        # Fetch summary
        summary_df = session.sql(
            queries.summary_query(artist, str(start_date), str(end_date))
        ).to_pandas()

        # Fetch export data
        export_df = session.sql(
            queries.export_query(
                artist,
                str(start_date),
                str(end_date),
                label,
                sources if sources != fetch_sources() else None,
            )
        ).to_pandas()

    st.session_state["export_df"] = export_df
    st.session_state["summary_df"] = summary_df
    st.session_state["export_artist"] = artist
    st.session_state["export_label"] = label

export_df = st.session_state["export_df"]
summary_df = st.session_state["summary_df"]

# ── Header metrics ───────────────────────────────────────────────────────────

st.title(f"Export: {st.session_state.get('export_artist', artist)}")

total_rows = len(export_df)
unique_emails = export_df["Email (Required)"].nunique()

m1, m2, m3 = st.columns(3)
m1.metric("Total Rows", f"{total_rows:,}")
m2.metric("Unique Emails", f"{unique_emails:,}")
m3.metric("Label", st.session_state.get("export_label", label))

# ── Source breakdown ─────────────────────────────────────────────────────────

if not summary_df.empty:
    st.subheader("Source Breakdown")
    st.dataframe(summary_df, use_container_width=True, hide_index=True)

# ── Preview table ────────────────────────────────────────────────────────────

st.subheader("Export Preview")

if export_df.empty:
    st.warning("No rows match the selected filters.")
    st.stop()

st.dataframe(export_df.head(500), use_container_width=True, hide_index=True)

if total_rows > 500:
    st.caption(f"Showing first 500 of {total_rows:,} rows.")

# ── Download ─────────────────────────────────────────────────────────────────

csv = export_df.to_csv(index=False)
filename = f"seated_export_{artist.replace(' ', '_')}_{date.today().isoformat()}.csv"

st.download_button(
    label=f"⬇ Download CSV ({total_rows:,} rows)",
    data=csv,
    file_name=filename,
    mime="text/csv",
    type="primary",
)