-- schema.sql — Minimales Datenmodell

CREATE TABLE IF NOT EXISTS races (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  venue VARCHAR(120) NOT NULL,
  start_time DATETIME NOT NULL,
  max_horses TINYINT UNSIGNED NOT NULL DEFAULT 15
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS horses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  race_id INT NOT NULL,
  number TINYINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  FOREIGN KEY (race_id) REFERENCES races(id) ON DELETE CASCADE,
  UNIQUE KEY race_number_unique (race_id, number)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  race_id INT NOT NULL,
  stake_cents INT NOT NULL,
  bet_types VARCHAR(64) NOT NULL, -- comma-separated: sieg,platz,zweier,dreier
  rank1 TINYINT UNSIGNED NULL,
  rank2 TINYINT UNSIGNED NULL,
  rank3 TINYINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (race_id) REFERENCES races(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Demo-Daten
INSERT INTO races (name, venue, start_time) VALUES
('Nachtrennen 1', 'Trabrennbahn Bahrenfeld', DATE_ADD(NOW(), INTERVAL 1 DAY));

SET @race := LAST_INSERT_ID();

INSERT INTO horses (race_id, number, name) VALUES
(@race,1,'Blitzstern'),
(@race,2,'Nordwind'),
(@race,3,'Altona Express'),
(@race,4,'Hanse Lady'),
(@race,5,'Elbprinz');
