import sqlite3
import threading
import time
import re
from datetime import datetime

import pandas as pd
import streamlit as st

# =========================
# App Config
# =========================
st.set_page_config(page_title="Expo Game Timer", page_icon="⏱️", layout="centered")

DB_PATH = "game.db"
DB_LOCK = threading.Lock()
TICK_SECONDS = 0.1  # ~10 fps refresh while the timer is running

# =========================
# DB Utilities
# =========================
def init_db():
    with DB_LOCK:
        conn = sqlite3.connect(DB_PATH, check_same_thread=False, timeout=10)
        cur = conn.cursor()
        cur.execute("PRAGMA journal_mode=WAL;")
        cur.execute(
            """
            CREATE TABLE IF NOT EXISTS results (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT NOT NULL,
                seconds REAL NOT NULL,
                created_at TEXT NOT NULL
            )
            """
        )
        conn.commit()
        conn.close()

def insert_result(name: str, email: str, seconds: float):
    now = datetime.utcnow().isoformat()
    with DB_LOCK:
        conn = sqlite3.connect(DB_PATH, check_same_thread=False, timeout=10)
        cur = conn.cursor()
        cur.execute(
            "INSERT INTO results (name, email, seconds, created_at) VALUES (?, ?, ?, ?)",
            (name.strip(), email.strip().lower(), float(seconds), now),
        )
        conn.commit()
        conn.close()
    load_all_results.clear()  # bust cache so dashboard updates instantly

@st.cache_data(show_spinner=False)
def load_all_results() -> pd.DataFrame:
    with DB_LOCK:
        conn = sqlite3.connect(DB_PATH, check_same_thread=False, timeout=10)
        df = pd.read_sql_query(
            "SELECT id, name, email, seconds, created_at FROM results ORDER BY id DESC",
            conn,
        )
        conn.close()
    return df

# =========================
# Helpers
# =========================
EMAIL_RE = re.compile(r"^[A-Za-z0-9._%+\-]+@[A-Za-z0-9.\-]+\.[A-Za-z]{2,}$")

def valid_email(email: str) -> bool:
    return bool(EMAIL_RE.match(email or ""))

def format_seconds(s: float) -> str:
    # mm:ss.mmm
    m, sec = divmod(max(float(s), 0.0), 60)
    return f"{int(m):02d}:{sec:06.3f}"

def ensure_session_state():
    ss = st.session_state
    if "start_time" not in ss:   # None means not currently running
        ss.start_time = None
    if "accumulated" not in ss:  # seconds already accrued from past runs
        ss.accumulated = 0.0
    if "name" not in ss:
        ss.name = ""
    if "email" not in ss:
        ss.email = ""

def is_running() -> bool:
    return st.session_state.start_time is not None

def current_elapsed() -> float:
    """Total elapsed = accumulated + (now - start_time if running)."""
    ss = st.session_state
    if ss.start_time is None:
        return ss.accumulated
    return ss.accumulated + (time.perf_counter() - ss.start_time)

def start_timer():
    if st.session_state.start_time is None:
        st.session_state.start_time = time.perf_counter()

def stop_timer():
    if st.session_state.start_time is not None:
        st.session_state.accumulated += (time.perf_counter() - st.session_state.start_time)
        st.session_state.start_time = None

def reset_timer():
    st.session_state.start_time = None
    st.session_state.accumulated = 0.0

def safe_rerun():
    try:
        st.rerun()
    except Exception:
        st.experimental_rerun()

# =========================
# UI
# =========================
def header():
    st.markdown(
        """
        <div style="text-align:center; margin-bottom: 0.5rem;">
            <h1 style="margin-bottom:0">⏱️ Expo Game Timer</h1>
            <p style="color:#666; margin-top:0.25rem">Record participants, time their run, track a live leaderboard, and export results.</p>
        </div>
        """,
        unsafe_allow_html=True,
    )

def participant_form():
    c1, c2 = st.columns(2)
    with c1:
        st.text_input("Participant Name", key="name", placeholder="Jane Doe")
    with c2:
        st.text_input("Email", key="email", placeholder="jane@example.com")

def stopwatch_card():
    ensure_session_state()

    st.markdown("### Stopwatch")
    with st.container(border=True):
        # Display (updates continuously while running)
        elapsed = current_elapsed()
        st.markdown(
            f"<div style='font-size:3rem; text-align:center; font-variant-numeric: tabular-nums;'>{format_seconds(elapsed)}</div>",
            unsafe_allow_html=True,
        )

        b1, b2, b3 = st.columns(3)
        with b1:
            if st.button("▶️ Start", use_container_width=True, disabled=is_running()):
                start_timer()
                safe_rerun()
        with b2:
            if st.button("⏸️ Stop", use_container_width=True, disabled=not is_running()):
                stop_timer()
                safe_rerun()
        with b3:
            if st.button("↺ Reset", use_container_width=True, disabled=(current_elapsed() == 0.0 and not is_running())):
                reset_timer()
                safe_rerun()

        st.caption("Tip: Start the timer when the game begins and press Stop as soon as they finish. Then Save Result.")

        st.divider()
        save_col1, save_col2 = st.columns([2, 1])
        with save_col1:
            st.write("**Save this run**")
            if not st.session_state.name.strip():
                st.info("Enter a participant name.")
            if not st.session_state.email.strip():
                st.info("Enter a valid email.")
            if st.session_state.email and not valid_email(st.session_state.email):
                st.error("Please enter a valid email address.")

        with save_col2:
            disabled_save = (
                not st.session_state.name.strip()
                or not valid_email(st.session_state.email)
                or current_elapsed() <= 0.0
                or is_running()  # don't allow saving while the timer is running
            )
            if st.button("💾 Save Result", type="primary", use_container_width=True, disabled=disabled_save):
                secs = round(current_elapsed(), 3)
                try:
                    insert_result(st.session_state.name, st.session_state.email, secs)
                    st.success(f"Saved: {st.session_state.name} — {format_seconds(secs)}")
                    reset_timer()
                except Exception as e:
                    st.error(f"Failed to save result: {e}")
                safe_rerun()

    # Auto-refresh while running (simple, robust pattern)
    if is_running():
        time.sleep(TICK_SECONDS)
        safe_rerun()

def dashboard():
    st.markdown("### Dashboard")
    with st.container(border=True):
        df = load_all_results()
        if df.empty:
            st.info("No results yet. Save the first run to see stats and leaderboard.")
            return

        # Quick stats
        total = len(df)
        best = df["seconds"].min()
        avg = df["seconds"].mean()

        s1, s2, s3 = st.columns(3)
        s1.metric("Total Participants (runs)", total)
        s2.metric("Best Time", format_seconds(best))
        s3.metric("Average Time", format_seconds(avg))

        st.markdown("#### 🏆 Top 3 Fastest")
        top3 = df.sort_values("seconds", ascending=True).head(3).copy()
        top3["Time"] = top3["seconds"].apply(format_seconds)
        st.dataframe(
            top3[["name", "email", "Time", "created_at"]]
            .rename(columns={"name": "Name", "email": "Email", "created_at": "Recorded (UTC)"}),
            hide_index=True,
            use_container_width=True,
        )

        # --- No "All Results" table displayed ---
        # Still provide CSV of the full dataset
        csv_df = df.copy()
        csv_df["time_formatted"] = csv_df["seconds"].apply(format_seconds)
        st.download_button(
            label="⬇️ Download all results (CSV)",
            data=csv_df.to_csv(index=False).encode("utf-8"),
            file_name="game_results.csv",
            mime="text/csv",
            use_container_width=True,
        )


def footer_note():
    st.caption(
        "Data is stored in a local SQLite database (`game.db`). "
        "Note: if the Space restarts or is rebuilt, the DB resets. "
        "Multiple attempts per email are allowed; use the CSV to post-process if you want best-per-email."
    )

# =========================
# Main
# =========================
def main():
    init_db()
    header()
    participant_form()
    stopwatch_card()
    dashboard()
    footer_note()

if __name__ == "__main__":
    main()