-- ================================================
-- PADEL STATISTICS V2 - MySQL Database Schema
-- AFT + PV Federation Support
-- ================================================


-- ================================================
-- TABLE: teams
-- Équipes (P200 Hommes A, P50 Dames, etc.)
-- ================================================
CREATE TABLE IF NOT EXISTS `teams` (
  `id` VARCHAR(50) PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `gender` ENUM('men', 'women') NOT NULL,
  `division` VARCHAR(20) NOT NULL,
  `season` VARCHAR(10) NOT NULL,
  `federation` ENUM('AFT', 'PV', 'BOTH') DEFAULT 'BOTH',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `created_by` VARCHAR(50),
  INDEX idx_gender (gender),
  INDEX idx_division (division),
  INDEX idx_federation (federation)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- TABLE: user_teams
-- Relation many-to-many entre users et teams
-- ================================================
CREATE TABLE IF NOT EXISTS `user_teams` (
  `user_id` INT NOT NULL,
  `team_id` VARCHAR(50) NOT NULL,
  PRIMARY KEY (user_id, team_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- TABLE: players
-- Joueurs de padel
-- ================================================
CREATE TABLE IF NOT EXISTS `players` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `team_id` VARCHAR(50) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `gender` ENUM('M', 'F') NOT NULL,
  `ranking_aft` VARCHAR(10) DEFAULT 'NC',
  `ranking_pv` VARCHAR(10) DEFAULT 'NC',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
  INDEX idx_team (team_id),
  INDEX idx_gender (gender),
  INDEX idx_ranking_aft (ranking_aft),
  INDEX idx_ranking_pv (ranking_pv)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- TABLE: competition_types
-- Types de compétitions (AFT/PV spécifiques)
-- ================================================
CREATE TABLE IF NOT EXISTS `competition_types` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `federation` ENUM('AFT', 'PV') NOT NULL,
  `code` VARCHAR(50) NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `gender_allowed` ENUM('men', 'women', 'mixed', 'all') NOT NULL,
  `pool_win_factor` DECIMAL(3,2) DEFAULT 1.00,
  `pool_loss_factor` DECIMAL(3,2) DEFAULT 1.00,
  `final_win_factor` DECIMAL(3,2) DEFAULT 1.00,
  `final_loss_factor` DECIMAL(3,2) DEFAULT 1.00,
  `algorithm_factor` DECIMAL(3,2) DEFAULT 1.00,
  `active` TINYINT(1) DEFAULT 1,
  UNIQUE KEY unique_federation_code (federation, code),
  INDEX idx_federation (federation),
  INDEX idx_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- TABLE: matches
-- Matchs de padel
-- ================================================
CREATE TABLE IF NOT EXISTS `matches` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `team_id` VARCHAR(50) NOT NULL,
  `federation` ENUM('AFT', 'PV') NOT NULL,
  `competition_type_id` INT NOT NULL,
  `match_date` DATE NOT NULL,
  
  -- Vos joueurs
  `player1_id` INT NOT NULL,
  `player2_id` INT NOT NULL,
  `player1_position` ENUM('left', 'right') DEFAULT 'left',
  `player2_position` ENUM('left', 'right') DEFAULT 'right',
  
  -- Adversaires
  `opponent1_name` VARCHAR(100) NOT NULL,
  `opponent2_name` VARCHAR(100) NOT NULL,
  `opponent1_ranking` VARCHAR(10) NOT NULL,
  `opponent2_ranking` VARCHAR(10) NOT NULL,
  
  -- Détails match
  `phase` ENUM('pool', 'final') NOT NULL,
  `result` ENUM('win', 'loss') NOT NULL,
  
  -- Score (JSON pour flexibilité)
  `score_json` TEXT NOT NULL,
  
  -- Équipe adverse
  `opposing_team` VARCHAR(100) NOT NULL,
  `notes` TEXT,
  
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE,
  FOREIGN KEY (competition_type_id) REFERENCES competition_types(id),
  FOREIGN KEY (player1_id) REFERENCES players(id) ON DELETE CASCADE,
  FOREIGN KEY (player2_id) REFERENCES players(id) ON DELETE CASCADE,
  
  INDEX idx_team (team_id),
  INDEX idx_federation (federation),
  INDEX idx_date (match_date),
  INDEX idx_player1 (player1_id),
  INDEX idx_player2 (player2_id),
  INDEX idx_result (result),
  INDEX idx_phase (phase)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- TABLE: ranking_thresholds
-- Seuils pour monter/descendre de classement (AFT/PV)
-- ================================================
CREATE TABLE IF NOT EXISTS `ranking_thresholds` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `federation` ENUM('AFT', 'PV') NOT NULL,
  `gender` ENUM('M', 'F') NOT NULL,
  `ranking_level` VARCHAR(10) NOT NULL,
  `drop_threshold` DECIMAL(5,2),
  `maintain_min` DECIMAL(5,2),
  `maintain_max` DECIMAL(5,2),
  `rise_threshold` DECIMAL(5,2),
  `rise_2_threshold` DECIMAL(5,2),
  `min_matches_drop` INT DEFAULT 5,
  `min_matches_rise` INT DEFAULT 9,
  UNIQUE KEY unique_federation_gender_level (federation, gender, ranking_level),
  INDEX idx_federation (federation),
  INDEX idx_gender (gender),
  INDEX idx_ranking (ranking_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ================================================
-- INSERT DEFAULT DATA
-- ================================================

-- Types de compétitions AFT
INSERT INTO `competition_types` (federation, code, name, gender_allowed, pool_win_factor, pool_loss_factor, final_win_factor, final_loss_factor, algorithm_factor) VALUES
('AFT', 'interclub-men', 'AFT - Interclub Men', 'men', 1.00, 1.00, 1.25, 0.75, 1.00),
('AFT', 'interclub-women', 'AFT - Interclub Women', 'women', 1.00, 1.00, 1.25, 0.75, 1.00),
('AFT', 'pwb-tour-men', 'AFT - PWB Tour Men', 'men', 1.00, 1.00, 1.25, 0.75, 1.00),
('AFT', 'pwb-tour-women', 'AFT - PWB Tour Women', 'women', 1.00, 1.00, 1.25, 0.75, 1.00),
('AFT', 'pwb-tour-mixed', 'AFT - PWB Tour Mixed', 'mixed', 1.00, 1.00, 1.25, 0.75, 0.80),
('AFT', 'bpt', 'AFT - BPT (Belgian Padel Tour)', 'all', 1.00, 1.00, 1.25, 0.75, 1.20),
('AFT', 'masters', 'AFT - Masters', 'all', 1.00, 1.00, 1.25, 0.75, 1.25);

-- Types de compétitions PV
INSERT INTO `competition_types` (federation, code, name, gender_allowed, pool_win_factor, pool_loss_factor, final_win_factor, final_loss_factor, algorithm_factor) VALUES
('PV', 'interclub-men', 'PV - Interclub Men', 'men', 1.00, 1.00, 1.50, 0.50, 0.80),
('PV', 'interclub-women', 'PV - Interclub Women', 'women', 1.00, 1.00, 1.50, 0.50, 0.80),
('PV', 'interclub-open', 'PV - Interclub Open', 'all', 1.00, 1.00, 1.50, 0.50, 0.80),
('PV', 'padel-tour-men', 'PV - Padel Tour Men', 'men', 1.00, 1.00, 1.50, 0.50, 1.00),
('PV', 'padel-tour-women', 'PV - Padel Tour Women', 'women', 1.00, 1.00, 1.50, 0.50, 1.00),
('PV', 'padel-tour-mixed', 'PV - Padel Tour Mixed', 'mixed', 1.00, 1.00, 1.50, 0.50, 1.00),
('PV', 'bpt', 'PV - BPT', 'all', 1.50, 0.50, 1.50, 0.50, 1.00),
('PV', 'bk', 'PV - BK (Belgian Championships)', 'all', 1.50, 0.50, 1.50, 0.50, 1.00),
('PV', 'senior-cup', 'PV - Senior Cup', 'all', 1.00, 1.00, 1.50, 0.50, 0.80),
('PV', 'mixed-cup-men', 'PV - Mixed Cup (Men)', 'men', 1.00, 1.00, 1.50, 0.50, 0.80),
('PV', 'mixed-cup-women', 'PV - Mixed Cup (Women)', 'women', 1.00, 1.00, 1.50, 0.50, 0.80),
('PV', 'mixed-cup-mixed', 'PV - Mixed Cup (Mixed)', 'mixed', 1.00, 1.00, 1.50, 0.50, 0.20),
('PV', 'junior-u16', 'PV - Junior U16 Division 1', 'all', 1.00, 1.00, 1.50, 0.50, 0.80);

-- Seuils AFT - HOMMES
INSERT INTO `ranking_thresholds` (federation, gender, ranking_level, drop_threshold, maintain_min, maintain_max, rise_threshold, rise_2_threshold, min_matches_drop, min_matches_rise) VALUES
('AFT', 'M', 'P50', NULL, 0, 45, 45, 90, 10, 10),
('AFT', 'M', 'P100', 20, 20, 50, 50, 90, 10, 10),
('AFT', 'M', 'P200', 20, 20, 55, 55, 90, 10, 10),
('AFT', 'M', 'P300', 25, 25, 60, 60, NULL, 10, 10),
('AFT', 'M', 'P400', 30, 30, 65, 65, NULL, 10, 10),
('AFT', 'M', 'P500', 30, 30, 70, 70, NULL, 10, 10),
('AFT', 'M', 'P700', 35, 35, 75, 75, NULL, 10, 10),
('AFT', 'M', 'P1000', 35, 35, NULL, NULL, NULL, 10, 10);

-- Seuils AFT - FEMMES
INSERT INTO `ranking_thresholds` (federation, gender, ranking_level, drop_threshold, maintain_min, maintain_max, rise_threshold, rise_2_threshold, min_matches_drop, min_matches_rise) VALUES
('AFT', 'F', 'P50', NULL, 0, 45, 45, 90, 8, 8),
('AFT', 'F', 'P100', 20, 20, 50, 50, 90, 8, 8),
('AFT', 'F', 'P200', 20, 20, 55, 55, NULL, 8, 8),
('AFT', 'F', 'P300', 25, 25, 60, 60, NULL, 8, 8),
('AFT', 'F', 'P400', 30, 30, 65, 65, NULL, 8, 8),
('AFT', 'F', 'P500', 30, 30, 70, 70, NULL, 8, 8);

-- Seuils PV - HOMMES
INSERT INTO `ranking_thresholds` (federation, gender, ranking_level, drop_threshold, maintain_min, maintain_max, rise_threshold, rise_2_threshold, min_matches_drop, min_matches_rise) VALUES
('PV', 'M', 'P100', NULL, 0, 65, 65, NULL, 5, 9),
('PV', 'M', 'P200', 10, 10, 65, 65, NULL, 5, 9),
('PV', 'M', 'P300', 20, 20, 75, 75, NULL, 5, 9),
('PV', 'M', 'P400', 20, 20, 80, 80, NULL, 5, 9),
('PV', 'M', 'P500', 20, 20, 85, 85, NULL, 5, 9),
('PV', 'M', 'P700', 30, 30, 90, 90, NULL, 5, 20),
('PV', 'M', 'P1000', NULL, NULL, NULL, NULL, NULL, 5, 9);

-- Seuils PV - FEMMES
INSERT INTO `ranking_thresholds` (federation, gender, ranking_level, drop_threshold, maintain_min, maintain_max, rise_threshold, rise_2_threshold, min_matches_drop, min_matches_rise) VALUES
('PV', 'F', 'P50', NULL, 0, 65, 65, NULL, 5, 9),
('PV', 'F', 'P100', 10, 10, 65, 65, NULL, 5, 9),
('PV', 'F', 'P200', 20, 20, 75, 75, NULL, 5, 9),
('PV', 'F', 'P300', 20, 20, 85, 85, NULL, 5, 9),
('PV', 'F', 'P400', 30, 30, 90, 90, NULL, 5, 9),
('PV', 'F', 'P500', 30, 30, 90, 90, NULL, 5, 20),
('PV', 'F', 'P700', 30, 30, NULL, NULL, NULL, 5, 9);


-- ================================================
-- FIN DU SCHÉMA
-- ================================================
