

CREATE TABLE categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(140) NOT NULL UNIQUE,
  sort_order INT NOT NULL DEFAULT 100,
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_categories_active_order (active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE cavaliers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(120) NOT NULL,
  last_name VARCHAR(120) NOT NULL,
  normalized_first_name VARCHAR(120) NOT NULL,
  normalized_last_name VARCHAR(120) NOT NULL,
  license_ffe VARCHAR(80) NULL,
  club VARCHAR(190) NULL,
  category_id INT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_cavaliers_category FOREIGN KEY (category_id) REFERENCES categories(id),
  UNIQUE KEY uniq_rider_identity_category (normalized_first_name, normalized_last_name, category_id),
  INDEX idx_cavaliers_category (category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE epreuves (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  event_date DATE NOT NULL,
  category_id INT UNSIGNED NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_epreuves_category FOREIGN KEY (category_id) REFERENCES categories(id),
  UNIQUE KEY uniq_event (name, event_date, category_id),
  INDEX idx_epreuves_date (event_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE imports (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  filename VARCHAR(255) NOT NULL,
  status ENUM('provisional', 'published', 'cancelled') NOT NULL DEFAULT 'provisional',
  admin_id INT UNSIGNED NULL,
  row_count INT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  published_at DATETIME NULL,
  CONSTRAINT fk_imports_admin FOREIGN KEY (admin_id) REFERENCES users_admin(id),
  INDEX idx_imports_status_created (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE resultats (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  import_id INT UNSIGNED NOT NULL,
  cavalier_id INT UNSIGNED NOT NULL,
  epreuve_id INT UNSIGNED NOT NULL,
  rank_position INT UNSIGNED NOT NULL,
  points INT UNSIGNED NOT NULL DEFAULT 0,
  status ENUM('provisional', 'published') NOT NULL DEFAULT 'provisional',
  created_at DATETIME NOT NULL,
  published_at DATETIME NULL,
  CONSTRAINT fk_resultats_import FOREIGN KEY (import_id) REFERENCES imports(id) ON DELETE CASCADE,
  CONSTRAINT fk_resultats_cavalier FOREIGN KEY (cavalier_id) REFERENCES cavaliers(id),
  CONSTRAINT fk_resultats_epreuve FOREIGN KEY (epreuve_id) REFERENCES epreuves(id),
  UNIQUE KEY uniq_result (cavalier_id, epreuve_id, import_id),
  INDEX idx_resultats_status (status),
  INDEX idx_resultats_ranking (status, rank_position, points)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE baremes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  rank_position INT UNSIGNED NOT NULL UNIQUE,
  points INT UNSIGNED NOT NULL DEFAULT 0,
  updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ranking_publications (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id INT UNSIGNED NULL,
  published_at DATETIME NOT NULL,
  result_count INT UNSIGNED NOT NULL DEFAULT 0,
  impacted_rider_count INT UNSIGNED NOT NULL DEFAULT 0,
  CONSTRAINT fk_publications_admin FOREIGN KEY (admin_id) REFERENCES users_admin(id),
  INDEX idx_publications_date (published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ranking_entries (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  publication_id INT UNSIGNED NOT NULL,
  category_id INT UNSIGNED NOT NULL,
  rider_id INT UNSIGNED NOT NULL,
  rank_position INT UNSIGNED NOT NULL,
  first_name VARCHAR(120) NOT NULL,
  last_name VARCHAR(120) NOT NULL,
  victories INT UNSIGNED NOT NULL DEFAULT 0,
  seconds INT UNSIGNED NOT NULL DEFAULT 0,
  thirds INT UNSIGNED NOT NULL DEFAULT 0,
  events_count INT UNSIGNED NOT NULL DEFAULT 0,
  total_points INT UNSIGNED NOT NULL DEFAULT 0,
  CONSTRAINT fk_entries_publication FOREIGN KEY (publication_id) REFERENCES ranking_publications(id) ON DELETE CASCADE,
  CONSTRAINT fk_entries_category FOREIGN KEY (category_id) REFERENCES categories(id),
  CONSTRAINT fk_entries_rider FOREIGN KEY (rider_id) REFERENCES cavaliers(id),
  INDEX idx_entries_public_category_rank (publication_id, category_id, rank_position),
  INDEX idx_entries_rider (rider_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE news (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  body TEXT NOT NULL,
  published_at DATETIME NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  INDEX idx_news_published (active, published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE admin_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  admin_id INT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  context JSON NULL,
  ip_address VARCHAR(45) NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_logs_admin FOREIGN KEY (admin_id) REFERENCES users_admin(id),
  INDEX idx_logs_created (created_at),
  INDEX idx_logs_action (action)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO categories (name, slug, sort_order, active) VALUES
('Amateur 3', 'amateur-3', 10, 1),
('Amateur 2', 'amateur-2', 20, 1),
('Amateur 1', 'amateur-1', 30, 1),
('Amateur Elite', 'amateur-elite', 40, 1),
('Pro 3', 'pro-3', 50, 1),
('Pro 2', 'pro-2', 60, 1),
('Pro 1', 'pro-1', 70, 1),
('Pro Elite', 'pro-elite', 80, 1);

INSERT INTO baremes (rank_position, points) VALUES
(1, 20), (2, 17), (3, 15), (4, 13), (5, 12),
(6, 11), (7, 10), (8, 9), (9, 8), (10, 7),
(11, 6), (12, 5), (13, 4), (14, 3), (15, 2), (16, 1);

INSERT INTO news (title, body, published_at, active) VALUES
('Ouverture du challenge', 'Les classements seront publies apres validation des premiers resultats.', NOW(), 1);
