from __future__ import annotations
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import String, Integer, Float, BigInteger, Boolean, Text, UniqueConstraint, Index

class Base(DeclarativeBase):
    pass

class Match(Base):
    __tablename__ = "matches"

    id: Mapped[str] = mapped_column(String(64), primary_key=True)
    source: Mapped[str] = mapped_column(String(32), default="flashscore", index=True)
    competition_url: Mapped[str] = mapped_column(Text, default="")
    match_url: Mapped[str] = mapped_column(Text, default="")

    kickoff_ts: Mapped[int | None] = mapped_column(BigInteger, nullable=True, index=True)
    status: Mapped[str] = mapped_column(String(32), default="unknown", index=True)  # scheduled/live/finished/unknown

    home_team: Mapped[str] = mapped_column(String(160), index=True)
    away_team: Mapped[str] = mapped_column(String(160), index=True)

    minute: Mapped[int | None] = mapped_column(Integer, nullable=True)

    home_score: Mapped[int | None] = mapped_column(Integer, nullable=True)
    away_score: Mapped[int | None] = mapped_column(Integer, nullable=True)

    last_seen_ts: Mapped[int | None] = mapped_column(BigInteger, nullable=True)
    last_update_ts: Mapped[int | None] = mapped_column(BigInteger, nullable=True)

    __table_args__ = (
        Index("ix_matches_home_away", "home_team", "away_team"),
    )

class Prediction(Base):
    __tablename__ = "predictions"
    __table_args__ = (
        UniqueConstraint("match_id", "model", name="uq_prediction_match_model"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    match_id: Mapped[str] = mapped_column(String(64), index=True)
    model: Mapped[str] = mapped_column(String(64), default="poisson+form")

    p_home_win: Mapped[float] = mapped_column(Float)
    p_draw: Mapped[float] = mapped_column(Float)
    p_away_win: Mapped[float] = mapped_column(Float)

    exp_home_goals: Mapped[float] = mapped_column(Float)
    exp_away_goals: Mapped[float] = mapped_column(Float)
    confidence: Mapped[float] = mapped_column(Float, default=0.0)

    created_ts: Mapped[int] = mapped_column(BigInteger)

class MatchEvent(Base):
    __tablename__ = "match_events"
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    match_id: Mapped[str] = mapped_column(String(64), index=True)
    event_type: Mapped[str] = mapped_column(String(32), index=True)  # goal/status/etc
    payload_json: Mapped[str] = mapped_column(Text, default="{}")
    created_ts: Mapped[int] = mapped_column(BigInteger)

class MetricAggregate(Base):
    __tablename__ = "metric_aggregates"
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    model: Mapped[str] = mapped_column(String(64), index=True)
    n_matches: Mapped[int] = mapped_column(Integer)
    accuracy: Mapped[float] = mapped_column(Float)
    brier: Mapped[float] = mapped_column(Float)
    logloss: Mapped[float] = mapped_column(Float)
    updated_ts: Mapped[int] = mapped_column(BigInteger)