import sqlite3
from datetime import datetime
from pathlib import Path
import pandas as pd
import streamlit as st

# -----------------------------
# Config & constants
# -----------------------------
st.set_page_config(page_title="Topic Interest Survey", page_icon="🗳️", layout="centered")

DB_PATH = Path("interests.db")
TABLE = "responses"
HEADERS = ["timestamp", "name", "topics"]

TOPICS = [
    "Hamid — Mapping the Innovation Ecosystem of a Tech Sector",
    "Hamid — Public Narratives of Corporate AI Adoption",
    "Milad — Geography of Goods & Services (EUIPO trademarks)",
    "Milad — Cross‑Border Relations — Nordic Startup Hyperlinks",
    "Milad — AI Champions in the Periphery (Company websites)",
    "Milad — Diffusion of ISO 27001 (Company websites)",
    "Milad — Goods & Services Classification (Large corpora)",
    "Roman — Automation & Quality Evaluation (n8n logs)",
    "Roman — AI & Labour Markets (O*NET)",
    "Roman — Long‑Form Document Structuring",
    "Richard & Roman/Milad — Knowledge Graph Construction from Text",
    "Richard & Roman/Milad Optimising LLM Generation Settings",
]

# -----------------------------
# DB helpers
# -----------------------------
@st.cache_resource(show_spinner=False)
def get_conn():
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.execute("PRAGMA journal_mode=WAL;")
    conn.execute(
        f"""
        CREATE TABLE IF NOT EXISTS {TABLE} (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            timestamp TEXT NOT NULL,
            name TEXT NOT NULL,
            topics TEXT NOT NULL
        )
        """
    )
    conn.commit()
    return conn


def insert_row(conn, name: str, topics: list[str]):
    ts = datetime.now().isoformat(timespec="seconds")
    topics_str = ", ".join(topics)
    conn.execute(
        f"INSERT INTO {TABLE} (timestamp, name, topics) VALUES (?, ?, ?)",
        (ts, name.strip(), topics_str),
    )
    conn.commit()


def delete_row(conn, row_id: int):
    conn.execute(f"DELETE FROM {TABLE} WHERE id = ?", (row_id,))
    conn.commit()


def load_df(conn) -> pd.DataFrame:
    df = pd.read_sql_query(
        f"SELECT id, timestamp, name, topics FROM {TABLE} ORDER BY timestamp DESC", conn
    )
    if not df.empty:
        df["topic_list"] = df["topics"].str.split(", ")
    return df

# -----------------------------
# UI helpers
# -----------------------------

def clear_form_state():
    st.session_state.pop("name", None)
    st.session_state.pop("choices", None)


# -----------------------------
# App
# -----------------------------
conn = get_conn()

st.title("🗳️ Topic Interest Survey")
st.caption("Pick up to two topics, add your name, and see the public roster of interests.")

survey_tab, roster_tab = st.tabs(["Survey", "Roster"])

with survey_tab:
    with st.form("survey_form", clear_on_submit=False):
        name = st.text_input("Your name", key="name", placeholder="e.g., Chenghao Luo")
        choices = st.multiselect(
            "Choose topics (max 2)",
            options=TOPICS,
            key="choices",
            help="You can only pick at most two.",
        )
        submitted = st.form_submit_button("Submit interest")

        if submitted:
            # Validation
            if not name or not name.strip():
                st.error("Please enter your name.")
            elif len(choices) == 0 or len(choices) > 2:
                st.error("Please choose 1 or 2 topics.")
            else:
                try:
                    insert_row(conn, name, choices)
                    st.success("Thanks! Your interest was recorded.")
                    clear_form_state()
                except sqlite3.IntegrityError as e:
                    st.error(f"Duplicate? Could not save: {e}")
                except Exception as e:
                    st.error(f"Unexpected error: {e}")

with roster_tab:
    df = load_df(conn)
    if df is None or df.empty:
        st.info("No entries yet.")
    else:
        # Filters
        col1, col2 = st.columns([2, 1])
        with col1:
            name_filter = st.text_input("Filter by name", placeholder="type part of a name…")
        with col2:
            topic_filter = st.selectbox("Filter by topic", options=["(All)"] + TOPICS)

        df_view = df.copy()
        if name_filter:
            df_view = df_view[df_view["name"].str.contains(name_filter, case=False, na=False)]
        if topic_filter and topic_filter != "(All)":
            df_view = df_view[df_view["topics"].str.contains(topic_filter, na=False)]

        st.subheader("Roster")
        st.dataframe(
            df_view[["timestamp", "name", "topics"]].rename(
                columns={"timestamp": "Timestamp", "name": "Name", "topics": "Topics"}
            ),
            use_container_width=True,
            hide_index=True,
        )

        # Delete section
        st.divider()
        st.subheader("Delete a record")
        st.caption("Select a specific submission to delete. This cannot be undone.")
        # Build nice labels for selection
        options = [
            (
                int(r.id),
                f"#{int(r.id)} — {r.name} — {r.topics} — {r.timestamp}"
            )
            for _, r in df.iterrows()
        ]
        if options:
            selected_label = st.selectbox(
                "Choose a record",
                options=[lbl for _, lbl in options],
                index=0,
                key="delete_select",
            )
            # Map back to id
            label_to_id = {lbl: rid for rid, lbl in options}
            col_del, col_warn = st.columns([1, 3])
            with col_del:
                if st.button("🗑️ Delete selected", type="primary"):
                    delete_row(conn, label_to_id[selected_label])
                    st.success("Record deleted. Refresh the tab to see updates.")
            with col_warn:
                st.info("Tip: Use filters above to narrow the list, then delete.")
        else:
            st.caption("No records to delete.")

        # Topic counts
        st.subheader("Topic counts")
        counts = (
            df.explode("topic_list")["topic_list"].value_counts().rename_axis("Topic").reset_index(name="Count")
        )
        st.bar_chart(counts.set_index("Topic"))

        # Download CSV
        csv = df[["timestamp", "name", "topics"]].to_csv(index=False).encode("utf-8")
        st.download_button(
            "⬇️ Download CSV",
            data=csv,
            file_name="topic_interests.csv",
            mime="text/csv",
        )

# Footer
st.write("")
st.caption("Built with Streamlit + SQLite. Data stored locally in interests.db.")