Database schema

CREATE TABLE IF NOT EXISTS races (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  season INTEGER NOT NULL,
  round INTEGER NOT NULL,
  name TEXT NOT NULL,
  circuit_name TEXT NOT NULL,
  circuit_id TEXT NOT NULL,
  locality TEXT,
  country TEXT,
  date TEXT NOT NULL,
  time TEXT,
  wikipedia_url TEXT,
  UNIQUE(season, round)
);

CREATE TABLE IF NOT EXISTS race_entries (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  race_id INTEGER NOT NULL REFERENCES races(id),
  jolpica_driver_id TEXT NOT NULL,
  driver_code TEXT NOT NULL,
  driver_name TEXT NOT NULL,
  constructor TEXT NOT NULL,
  grid_position INTEGER,
  finish_position INTEGER,
  status TEXT NOT NULL,
  points REAL NOT NULL DEFAULT 0,
  fastest_lap INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE IF NOT EXISTS standings_snapshots (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  race_id INTEGER NOT NULL REFERENCES races(id),
  snapshot_type TEXT NOT NULL CHECK(snapshot_type IN ('before', 'after')),
  entity_type TEXT NOT NULL CHECK(entity_type IN ('driver', 'constructor')),
  position INTEGER NOT NULL,
  entity_id TEXT NOT NULL,
  entity_name TEXT NOT NULL,
  points REAL NOT NULL,
  wins INTEGER NOT NULL DEFAULT 0
);

export interface Race {
  id: number;
  season: number;
  round: number;
  name: string;
  circuit_name: string;
  circuit_id: string;
  locality: string | null;
  country: string | null;
  date: string;       // "YYYY-MM-DD"
  time: string | null; // "HH:MM:SSZ" UTC, or null
  wikipedia_url: string | null;
}

export interface RaceEntry {
  id: number;
  race_id: number;
  jolpica_driver_id: string;
  driver_code: string;
  driver_name: string;
  constructor: string;
  grid_position: number | null;
  finish_position: number | null;
  status: string;
  points: number;
  fastest_lap: number; // 0 | 1
}

export interface StandingsSnapshot {
  id: number;
  race_id: number;
  snapshot_type: 'before' | 'after';
  entity_type: 'driver' | 'constructor';
  position: number;
  entity_id: string;
  entity_name: string;
  points: number;
  wins: number;
}

ALTER TABLE races RENAME COLUMN url TO wikipedia_url;
ALTER TABLE race_entries RENAME COLUMN driver_id TO jolpica_driver_id;

races (1) ──< (N) race_entries
  └───< (N) standings_snapshots