models.py

from sqlalchemy import Column, Integer, String, ForeignKey, DateTime, UniqueConstraint
from sqlalchemy.orm import declarative_base, relationship
from datetime import datetime, timezone

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    username = Column(String, primary_key=True)
    email = Column(String, nullable=False, unique=True)
    password_hash = Column(String, nullable=False)
    created_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))
    watchlists = relationship("WatchlistEntry", back_populates="user")

class Team(Base):
    __tablename__ = 'teams'
    
    team_name = Column(String, primary_key=True)
    city = Column(String)
    nickname = Column(String)
    conference = Column(String)
    
    roster = relationship("Player", back_populates="team")

class Player(Base):
    __tablename__ = 'players'
    
    player_id = Column(Integer, primary_key=True, autoincrement=True)
    first_name = Column(String, nullable=False)
    last_name = Column(String, nullable=False)
    position = Column(String(2), nullable=False)
    team_name = Column(String, ForeignKey('teams.team_name'))
    
    team = relationship("Team", back_populates="roster")
    career_stats = relationship("WeeklyStat", back_populates="player")

class WeeklyStat(Base):
    __tablename__ = 'weekly_stats'
    
    player_id = Column(Integer, ForeignKey('players.player_id'), primary_key=True)
    season = Column(Integer, primary_key=True)
    week = Column(Integer, primary_key=True)
    
    passing_yards = Column(Integer, default=0)
    passing_tds = Column(Integer, default=0)
    
    rushing_yards = Column(Integer, default=0)
    rushing_tds = Column(Integer, default=0)

    receptions = Column(Integer, default=0)
    receiving_yards = Column(Integer, default=0)
    receiving_tds = Column(Integer, default=0)
    
    total_tds = Column(Integer, default=0)
    
    player = relationship("Player", back_populates="career_stats")

class WatchlistEntry(Base):
    __tablename__ = 'watchlists'
    
    watchlist_id = Column(Integer, primary_key=True)
    username = Column(String, ForeignKey('users.username'))
    player_id = Column(Integer, ForeignKey('players.player_id'))
    target_round = Column(Integer)
    scouting_notes = Column(String)
    added_at = Column(DateTime, default=lambda: datetime.now(timezone.utc))

    __table_args__ = (UniqueConstraint('username', 'player_id', name='uq_watchlist_user_player'),)

    user = relationship("User", back_populates="watchlists")
    player = relationship("Player")