""" HK Racing Analytics - Database Models (SQLModel) """ from sqlmodel import SQLModel, Field from typing import Optional from datetime import date, datetime from uuid import UUID, uuid4 class Horse(SQLModel, table=True): """Horse master data""" horse_id: str = Field(primary_key=True, max_length=20) horse_name: str = Field(max_length=100, index=True) horse_name_en: Optional[str] = Field(max_length=100) country_of_origin: Optional[str] = Field(max_length=50) age: Optional[int] sex: Optional[str] = Field(max_length=10) color: Optional[str] = Field(max_length=30) sire: Optional[str] = Field(max_length=100) dam: Optional[str] = Field(max_length=100) dam_sire: Optional[str] = Field(max_length=100) owner: Optional[str] = Field(max_length=100) created_at: datetime = Field(default_factory=datetime.utcnow) updated_at: datetime = Field(default_factory=datetime.utcnow) class Jockey(SQLModel, table=True): """Jockey master data""" jockey_id: str = Field(primary_key=True, max_length=20) jockey_name: str = Field(max_length=100, index=True) jockey_name_en: Optional[str] = Field(max_length=100) license_type: Optional[str] = Field(max_length=20) nationality: Optional[str] = Field(max_length=50) created_at: datetime = Field(default_factory=datetime.utcnow) class Trainer(SQLModel, table=True): """Trainer master data""" trainer_id: str = Field(primary_key=True, max_length=20) trainer_name: str = Field(max_length=100, index=True) trainer_name_en: Optional[str] = Field(max_length=100) nationality: Optional[str] = Field(max_length=50) created_at: datetime = Field(default_factory=datetime.utcnow) class Race(SQLModel, table=True): """Race data""" race_id: str = Field(primary_key=True, max_length=30) race_date: date = Field(index=True) race_time: Optional[str] venue: str = Field(max_length=20, index=True) race_number: int race_name: Optional[str] = Field(max_length=200) race_class: Optional[str] = Field(max_length=20) race_type: Optional[str] = Field(max_length=50) distance: Optional[int] track: Optional[str] = Field(max_length=20) course: Optional[str] = Field(max_length=20) track_condition: Optional[str] = Field(max_length=20) going: Optional[str] = Field(max_length=50) prize_money_hkd: Optional[float] weather: Optional[str] = Field(max_length=20) created_at: datetime = Field(default_factory=datetime.utcnow) class Runner(SQLModel, table=True): """Runner (horse in race) data""" runner_id: str = Field(default_factory=lambda: str(uuid4()), primary_key=True) race_id: str = Field(foreign_key="race.race_id", index=True) horse_id: str = Field(foreign_key="horse.horse_id", index=True) jockey_id: Optional[str] = Field(foreign_key="jockey.jockey_id") trainer_id: Optional[str] = Field(foreign_key="trainer.trainer_id") barrier: Optional[int] horse_number: Optional[int] weight_carried: Optional[float] handicap_rating: Optional[int] declared_weight: Optional[float] gear: Optional[str] = Field(max_length=100) last_6_runs: Optional[str] = Field(max_length=20) days_since_last_run: Optional[int] created_at: datetime = Field(default_factory=datetime.utcnow) class Result(SQLModel, table=True): """Race result data""" result_id: str = Field(default_factory=lambda: str(uuid4()), primary_key=True) runner_id: str = Field(foreign_key="runner.runner_id") race_id: str = Field(foreign_key="race.race_id") finishing_position: Optional[int] finish_time: Optional[float] margin: Optional[float] lengths_behind: Optional[float] sectional_time: Optional[float] running_position_600m: Optional[int] running_position_400m: Optional[int] running_position_200m: Optional[int] created_at: datetime = Field(default_factory=datetime.utcnow) class OddsHistory(SQLModel, table=True): """Odds history""" odds_id: str = Field(default_factory=lambda: str(uuid4()), primary_key=True) runner_id: str = Field(foreign_key="runner.runner_id") race_id: str = Field(foreign_key="race.race_id") timestamp: datetime win_odds: Optional[float] place_odds: Optional[float] created_at: datetime = Field(default_factory=datetime.utcnow) class Features(SQLModel, table=True): """Engineered features for ML""" feature_id: str = Field(default_factory=lambda: str(uuid4()), primary_key=True) runner_id: str = Field(foreign_key="runner.runner_id", unique=True) race_id: str = Field(foreign_key="race.race_id") feature_date: datetime = Field(default_factory=datetime.utcnow) # Form features avg_finish_pos_last_6: Optional[float] win_rate_last_6: Optional[float] place_rate_last_6: Optional[float] days_since_last_run: Optional[int] runs_in_last_30_days: Optional[int] # Class features class_rating: Optional[int] class_diff_from_last: Optional[int] # Distance features distance_starts: Optional[int] distance_win_rate: Optional[float] distance_place_rate: Optional[float] distance_avg_margin: Optional[float] # Track features track_starts: Optional[int] track_win_rate: Optional[float] track_place_rate: Optional[float] # Jockey features jockey_win_rate_30_days: Optional[float] jockey_place_rate_30_days: Optional[float] jockey_horse_win_rate: Optional[float] # Trainer features trainer_win_rate_30_days: Optional[float] trainer_place_rate_30_days: Optional[float] trainer_horse_win_rate: Optional[float] # Weight features weight_diff_from_last: Optional[float] weight_above_min: Optional[float] # Barrier features barrier_win_rate: Optional[float] barrier_place_rate: Optional[float] # Market features opening_odds: Optional[float] odds_movement_pct: Optional[float] market_support: Optional[bool] class Prediction(SQLModel, table=True): """Model predictions""" prediction_id: str = Field(default_factory=lambda: str(uuid4()), primary_key=True) runner_id: str = Field(foreign_key="runner.runner_id") race_id: str = Field(foreign_key="race.race_id") model_version: str = Field(max_length=20) prediction_timestamp: datetime = Field(default_factory=datetime.utcnow) # Probability estimates win_probability: float place_probability: float # Rankings win_rank: Optional[int] place_rank: Optional[int] # Confidence confidence_score: Optional[float] # Explainability top_features: Optional[str] # JSON string created_at: datetime = Field(default_factory=datetime.utcnow)