app.py

import streamlit as st
import pandas as pd
from sqlalchemy import text
from sqlalchemy.orm import Session
from database import DatabaseConnector
from models import User, WatchlistEntry
from werkzeug.security import generate_password_hash, check_password_hash

st.set_page_config(page_title="Fantasy Draft Board", layout="wide")

@st.cache_resource
def get_db_engine():
    db = DatabaseConnector()
    return db.engine

engine = get_db_engine()


if 'logged_in' not in st.session_state:
    st.session_state['logged_in'] = False
if 'username' not in st.session_state:
    st.session_state['username'] = None


# Login/Signup
if not st.session_state['logged_in']:
    st.title("🏈 Fantasy Football Draft Board")
    st.markdown("Please log in or create an account to view player stats and build your watchlist.")
    
    tab_login, tab_signup = st.tabs(["Login", "Sign Up"])
    
    with tab_login:
        with st.form("login_form"):
            login_user = st.text_input("Username")
            login_pass = st.text_input("Password", type="password")
            submit_login = st.form_submit_button("Log In")
            
            if submit_login:
                with Session(engine) as session:
                    user = session.query(User).filter_by(username=login_user).first()
                    if user and check_password_hash(user.password_hash, login_pass):
                        st.session_state['logged_in'] = True
                        st.session_state['username'] = user.username
                        st.rerun()
                    else:
                        st.error("Invalid username or password.")
                        
    with tab_signup:
        with st.form("signup_form"):
            new_user = st.text_input("Choose a Username")
            new_email = st.text_input("Email Address")
            new_pass = st.text_input("Choose a Password", type="password")
            submit_signup = st.form_submit_button("Create Account")
            
            if submit_signup:
                if not new_user or not new_email or not new_pass:
                    st.error("Please fill out all fields.")
                else:
                    with Session(engine) as session:
                        existing_username = session.query(User).filter_by(username=new_user).first()
                        
                        existing_email = session.query(User).filter_by(email=new_email).first()
                        
                        if existing_username:
                            st.error("Username already taken. Please choose another.")
                        elif existing_email:
                            st.error("An account with this email address already exists. Please log in.")
                        else:
                            hashed_pw = generate_password_hash(new_pass)
                            new_account = User(username=new_user, email=new_email, password_hash=hashed_pw)
                            session.add(new_account)
                            session.commit()
                            st.success("Account created successfully! You can now log in.")


# Main page

else:
    # Sidebar Logout Button
    st.sidebar.markdown(f"**Logged in as: {st.session_state['username']}**")
    if st.sidebar.button("Log Out"):
        st.session_state['logged_in'] = False
        st.session_state['username'] = None
        st.rerun()
        
    st.sidebar.divider()

    st.title("🏈 Fantasy Football Draft Board")
    st.markdown("Filter player statistics and build your target draft list.")
    tab_board, tab_watchlist = st.tabs(["📈 Draft Board", "📋 My Watchlist"])

    # Draftboard Tab
    with tab_board:
        st.sidebar.header("Draft Filters")

        selected_year = st.sidebar.selectbox(
            "Select Season:",
            options=[2024, 2023, 2022],
            index=0 
        )

        query = """
            SELECT 
                p.player_id AS "ID",
                p.first_name || ' ' || p.last_name AS "Player Name",
                p.position AS "Pos",
                p.team_name AS "Team",
                COUNT(w.week) AS "Games",
                SUM(w.passing_yards) AS "Pass Yds",
                SUM(w.rushing_yards) AS "Rush Yds",
                SUM(w.receiving_yards) AS "Rec Yds",
                SUM(w.total_tds) AS "Total TDs",
                ROUND(
                    (
                        (SUM(w.passing_yards) * 0.04) + 
                        (SUM(w.rushing_yards) * 0.1) + 
                        (SUM(w.receiving_yards) * 0.1) + 
                        (SUM(w.total_tds) * 6.0)
                    ) / COUNT(w.week), 
                1) AS "PPG"
            FROM players p
            JOIN weekly_stats w ON p.player_id = w.player_id
            WHERE w.season = :season
            GROUP BY p.player_id, p.first_name, p.last_name, p.position, p.team_name
            ORDER BY "PPG" DESC
        """

        with engine.connect() as conn:
            df = pd.read_sql(text(query), conn, params={"season": selected_year})

        supported_positions = ['QB', 'RB', 'WR', 'TE']
        selected_positions = st.sidebar.multiselect(
            "Filter by Position:",
            options=supported_positions,
            default=supported_positions 
        )

        min_games = st.sidebar.slider("Minimum Games Played:", min_value=1, max_value=17, value=1)

        if selected_positions:
            filtered_df = df[
                (df['Pos'].isin(selected_positions)) & 
                (df['Games'] >= min_games)
            ]
        else:
            filtered_df = df[df['Games'] >= min_games]

        st.subheader(f"Top Prospects ({selected_year})")

        st.dataframe(
            filtered_df.drop(columns=["ID"]),
            use_container_width=True,
            hide_index=True
        )

        st.divider()
        st.subheader("Add to Watchlist")
        
        player_choices = {f"{row['Player Name']} ({row['Pos']} - {row['Team']})": row['ID'] for index, row in filtered_df.iterrows()}
        
        with st.form("add_watchlist_form"):
            col1, col2 = st.columns([3, 1])
            with col1:
                selected_player_name = st.selectbox("Select Player:", options=list(player_choices.keys()))
            with col2:
                target_round = st.number_input("Target Round:", min_value=1, max_value=16, value=1)
                
            notes = st.text_input("Scouting Notes:")
            submitted = st.form_submit_button("Save to Watchlist")
            
            if submitted and selected_player_name:
                selected_id = int(player_choices[selected_player_name])
                current_username = st.session_state['username']
                
                with Session(engine) as session:
                    entry = session.query(WatchlistEntry).filter_by(username=current_username, player_id=selected_id).first()
                    if not entry:
                        entry = WatchlistEntry(username=current_username, player_id=selected_id)
                        session.add(entry)
                    
                    entry.target_round = target_round
                    entry.scouting_notes = notes
                    session.commit()
                    st.success(f"Added {selected_player_name.split(' (')[0]} to watchlist!")

    # Watchlist Tab
    with tab_watchlist:
        st.subheader(f"📋 {st.session_state['username']}'s Targeted Players")
        
        current_username = st.session_state['username']
        watchlist_query = """
            SELECT 
                w.target_round AS "Target Round",
                p.first_name || ' ' || p.last_name AS "Player Name",
                p.position AS "Pos",
                p.team_name AS "Team",
                w.scouting_notes AS "Scouting Notes"
            FROM watchlists w
            JOIN players p ON w.player_id = p.player_id
            WHERE w.username = :username
            ORDER BY w.target_round ASC
        """
        
        with engine.connect() as conn:
            watchlist_df = pd.read_sql(text(watchlist_query), conn, params={"username": current_username})
            
        if watchlist_df.empty:
            st.info("Your watchlist is empty. Go to the Draft Board tab to add players.")
        else:
            st.dataframe(
                watchlist_df,
                use_container_width=True,
                hide_index=True
            )