from datetime import datetime
from app.extensions import db


class PlayerStat(db.Model):
    """Raw play statistics per user per game."""
    __tablename__ = "player_stats"
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    game_id = db.Column(db.Integer, db.ForeignKey("games.id"), nullable=False)
    matches_played = db.Column(db.Integer, default=0)
    wins = db.Column(db.Integer, default=0)
    losses = db.Column(db.Integer, default=0)
    draws = db.Column(db.Integer, default=0)
    play_time_hours = db.Column(db.Float, default=0.0)
    first_match_date = db.Column(db.DateTime)
    last_match_date = db.Column(db.DateTime)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow)

    __table_args__ = (db.UniqueConstraint("user_id", "game_id", name="uq_player_stat"),)


class PointEvent(db.Model):
    """Immutable point-ledger row.

    Every time a user earns points (match win, participation, admin grant)
    one row is written here.  Time-windowed leaderboards (weekly = 7 days,
    monthly = 30 days) are computed by SUM() over this table, so points
    naturally "expire" from a window as they age out of it.
    """
    __tablename__ = "point_events"
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False, index=True)
    game_id = db.Column(db.Integer, db.ForeignKey("games.id"), nullable=False, index=True)
    match_id = db.Column(db.Integer, db.ForeignKey("matches.id"), nullable=True)
    kind = db.Column(db.String(20), default="participation")  # win | participation | admin
    points = db.Column(db.Integer, default=0)
    prize = db.Column(db.Float, default=0.0)  # USDT prize paid together with this event
    reason = db.Column(db.String(150), default="")
    created_at = db.Column(db.DateTime, default=datetime.utcnow, index=True)

    user = db.relationship("User", foreign_keys=[user_id])
    game = db.relationship("Game")
    match = db.relationship("Match")


class LeaderboardEntry(db.Model):
    __tablename__ = "leaderboard_entries"
    id = db.Column(db.Integer, primary_key=True)
    game_id = db.Column(db.Integer, db.ForeignKey("games.id"), nullable=False)
    user_id = db.Column(db.Integer, db.ForeignKey("users.id"), nullable=False)
    rank = db.Column(db.Integer, default=0)
    win_rate = db.Column(db.Float, default=0.0)
    eligibility_score = db.Column(db.Float, default=0.0)  # composite score
    qualified = db.Column(db.Boolean, default=False)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow)

    __table_args__ = (db.UniqueConstraint("game_id", "user_id", name="uq_leaderboard"),)

    user = db.relationship("User")
    game = db.relationship("Game")