-- Spiker Race-Tracking System Database Migration Script
-- Version: 4.0
-- Description: This script drops all existing tables and recreates the schema from scratch with all optimizations.
-- WARNING: This script is DESTRUCTIVE and will DELETE ALL DATA.




--
-- Database: `spixerkviadex`
--

-- --------------------------------------------------------

--
-- Table structure for table `federations`
--
CREATE TABLE `federations` (
  `id` char(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `clubs`
--
CREATE TABLE `clubs` (
  `id` char(36) NOT NULL,
  `federation_id` char(36) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_clubs_federation` (`federation_id`),
  CONSTRAINT `fk_clubs_federation` FOREIGN KEY (`federation_id`) REFERENCES `federations` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `club_members`
--
CREATE TABLE `club_members` (
  `id` char(36) NOT NULL,
  `club_id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_club` (`user_id`,`club_id`),
  KEY `fk_club_members_club` (`club_id`),
  KEY `fk_club_members_user` (`user_id`),
  CONSTRAINT `fk_club_members_club` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_club_members_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `club_roles`
--
CREATE TABLE `club_roles` (
  `id` char(36) NOT NULL,
  `club_id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `role` enum('admin','member') NOT NULL DEFAULT 'member',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_club_role` (`user_id`,`club_id`),
  KEY `fk_club_roles_club` (`club_id`),
  KEY `fk_club_roles_user` (`user_id`),
  CONSTRAINT `fk_club_roles_club` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_club_roles_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `roles`
--
CREATE TABLE `roles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL UNIQUE COMMENT 'The name of the role (e.g., admin, organizer, runner)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Defines user roles for RBAC';

INSERT INTO `roles` (`name`) VALUES
('admin'),
('organizer'),
('runner'),
('volunteer');

--
-- Table structure for table `user_roles`
--
CREATE TABLE `user_roles` (
  `user_id` char(36) NOT NULL COMMENT 'Unique identifier for the user (UUID).',
  `stage_id` char(36) DEFAULT NULL COMMENT 'Identifier for the stage. Can be NULL for administrators.',
  `role_id` int NOT NULL COMMENT 'Identifier for the user''s role.'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Maps users to their assigned roles';


ALTER TABLE `user_roles`
  ADD PRIMARY KEY (`user_id`,`role_id`),
  ADD KEY `fk_user_roles_role` (`role_id`),
  ADD KEY `fk_stage_id` (`stage_id`);


ALTER TABLE `user_roles`
  ADD CONSTRAINT `fk_stage_id` FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_user_roles_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_user_roles_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Table structure for table `category`
--
CREATE TABLE `category` (
  `id` int NOT NULL AUTO_INCREMENT,
  `label` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `category` (`id`, `label`) VALUES
(1, 'Course à pied'),
(2, 'Trail'),
(3, 'Marathon'),
(4, 'VTT (Vélo Tout Terrain)'),
(5, 'Cyclisme sur route'),
(6, 'Triathlon'),
(7, 'Randonnée');

--
-- Table structure for table `events`
--
CREATE TABLE `events` (
  `id` char(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `label` varchar(100) DEFAULT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `organiser_id` char(36) DEFAULT NULL,
  `club_id` char(36) DEFAULT NULL,
  `cancel_reason` text,
  `PMR` tinyint(1) NOT NULL DEFAULT '0',
  `postal_code` varchar(20) NOT NULL,
  `address` text,
  `city` varchar(100) DEFAULT NULL,
  `region` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `oneplan_path` text,
  `is_draft` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `fk_events_organiser` (`organiser_id`),
  KEY `fk_events_club` (`club_id`),
  CONSTRAINT `fk_events_organiser` FOREIGN KEY (`organiser_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_events_club` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `stages`
--
CREATE TABLE `stages` (
  `id` char(36) NOT NULL,
  `event_id` char(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `registration_end_time` datetime DEFAULT NULL,
  `category_id` int DEFAULT NULL,
  `max_participants` int DEFAULT '100',
  `gpx_file_path` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_stages_event` (`event_id`),
  KEY `fk_stages_category` (`category_id`),
  CONSTRAINT `fk_stages_event` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_stages_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `registrations`
--
CREATE TABLE `registrations` (
  `id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `stage_id` char(36) NOT NULL,
  `type` enum('runner','volunteer') DEFAULT 'runner',
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `registration_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_stage_registration` (`user_id`,`stage_id`),
  KEY `fk_registrations_user` (`user_id`),
  KEY `fk_registrations_stage` (`stage_id`),
  CONSTRAINT `fk_registrations_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_registrations_stage` FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `rankings`
--
CREATE TABLE `rankings` (
  `id` char(36) NOT NULL,
  `stage_id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `bib_number` varchar(50) DEFAULT NULL,
  `rank_position` int DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_rankings_user` (`user_id`),
  KEY `idx_rankings_stage_position` (`stage_id`,`rank_position`),
  CONSTRAINT `fk_rankings_stage` FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_rankings_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `check_positive_rank` CHECK ((`rank_position` > 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `ranking_records`
--
CREATE TABLE `ranking_records` (
  `id` char(36) NOT NULL,
  `ranking_id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `stage_id` char(36) NOT NULL,
  `face_signature` varchar(255) DEFAULT NULL,
  `ocr_label` varchar(50) DEFAULT NULL,
  `latitude` decimal(10,8) DEFAULT NULL,
  `longitude` decimal(11,8) DEFAULT NULL,
  `source` varchar(30) DEFAULT 'ai',
  `captured_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_ranking_records_ranking` (`ranking_id`),
  KEY `idx_ranking_records_stage_user_time` (`stage_id`,`user_id`,`captured_at`),
  CONSTRAINT `fk_ranking_records_ranking` FOREIGN KEY (`ranking_id`) REFERENCES `rankings` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ranking_records_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ranking_records_stage` FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `user_informations`
--
CREATE TABLE `user_informations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` char(36) NOT NULL,
  `bio` varbinary(1024) DEFAULT NULL,
  `avatar_url` varchar(255) DEFAULT NULL,
  `location` varbinary(255) DEFAULT NULL,
  `is_premium_member` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `first_name` varbinary(255) DEFAULT NULL,
  `last_name` varbinary(255) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  CONSTRAINT `fk_userinfo_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `payments`
--
CREATE TABLE `payments` (
  `id` char(36) NOT NULL,
  `registration_id` char(36) NOT NULL,
  `stripe_charge_id` varchar(255) NOT NULL,
  `amount_paid` decimal(10,2) NOT NULL,
  `currency` varchar(3) NOT NULL,
  `commission_earned` decimal(10,2) NOT NULL,
  `status` enum('pending','succeeded','failed','refunded') NOT NULL DEFAULT 'pending',
  `paid_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `stripe_charge_id` (`stripe_charge_id`),
  KEY `fk_payments_registration` (`registration_id`),
  CONSTRAINT `fk_payments_registration` FOREIGN KEY (`registration_id`) REFERENCES `registrations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `check_positive_amount` CHECK ((`amount_paid` >= 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `newsletter_subscribers`
--
CREATE TABLE `newsletter_subscribers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `email` varchar(255) NOT NULL,
  `subscribed_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `price`
--
CREATE TABLE `price` (
  `id` int NOT NULL AUTO_INCREMENT,
  `stage_id` char(36) NOT NULL,
  `amount` int NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'eur',
  `commission_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `commission_currency` varchar(3) NOT NULL DEFAULT 'eur',
  PRIMARY KEY (`id`),
  UNIQUE KEY `stage_id` (`stage_id`),
  CONSTRAINT `fk_price_stage` FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `user_favorite_categories`
--
CREATE TABLE `user_favorite_categories` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` char(36) NOT NULL,
  `category_id` int NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_category` (`user_id`,`category_id`),
  KEY `idx_ufc_user` (`user_id`),
  KEY `idx_ufc_category` (`category_id`),
  CONSTRAINT `fk_ufc_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ufc_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Assign admin role to the new user
INSERT INTO `user_roles` (`user_id`, `role_id`, `stage_id`) VALUES
('a7a1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 1, NULL);

-- Create events
INSERT INTO `events` (`id`, `name`, `description`, `start_time`, `end_time`, `postal_code`, `city`, `country`, `organiser_id`, `is_draft`) VALUES
('e1e1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'Paris Marathon', 'The annual marathon in Paris.', '2025-10-26 09:00:00', '2025-10-26 18:00:00', '75000', 'Paris', 'France', 'b8b1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 0),
('f2f1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'Lyon Urban Trail', 'A beautiful trail run through the city of Lyon.', '2025-11-15 10:00:00', '2025-11-15 17:00:00', '69000', 'Lyon', 'France', 'b8b1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 0);

-- Create stages
INSERT INTO `stages` (`id`, `event_id`, `name`, `description`, `start_time`, `end_time`, `registration_end_time`, `category_id`) VALUES
('a1a1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'e1e1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', '10km Race', 'A fast 10km race.', '2025-10-26 09:30:00', '2025-10-26 11:00:00', '2025-10-20 23:59:59', 1),
('b2b1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'e1e1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'Half Marathon', 'A challenging half marathon.', '2025-10-26 10:00:00', '2025-10-26 13:00:00', '2025-10-20 23:59:59', 3),
('c3c1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'f2f1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', '15km Urban Trail', 'A mix of stairs, roads, and parks.', '2025-11-15 10:30:00', '2025-11-15 12:30:00', '2025-11-10 23:59:59', 2);

-- Assign organizer role
INSERT INTO `user_roles` (`user_id`, `role_id`, `stage_id`) VALUES
('b8b1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 2, 'a1a1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e');

-- Create registrations
INSERT INTO `registrations` (`id`, `user_id`, `stage_id`, `type`, `status`) VALUES
('d4d1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'c9c1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'a1a1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'runner', 'approved'),
('e5e1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'd0d1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'b2b1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'volunteer', 'approved');

--
-- Seeding club data
--

-- Create federations
INSERT INTO `federations` (`id`, `name`, `description`) VALUES
('fed1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'French Athletics Federation', 'The main governing body for the sport of athletics in France.'),
('fed2e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'French Triathlon Federation', 'The official governing body for triathlon in France.');

-- Create clubs
INSERT INTO `clubs` (`id`, `federation_id`, `name`, `description`) VALUES
('club1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'fed1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'Paris Runners Club', 'A club for runners in the Paris area.'),
('club2e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'fed2e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'Lyon Triathlon Club', 'A club for triathletes in the Lyon area.');

-- Create club members
INSERT INTO `club_members` (`id`, `club_id`, `user_id`) VALUES
('cmem1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'club1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'b8b1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e'),
('cmem2e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'club1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'c9c1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e'),
('cmem3e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'club2e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'd0d1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e');

-- Create club roles
INSERT INTO `club_roles` (`id`, `club_id`, `user_id`, `role`) VALUES
('crole1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'club1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'b8b1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'admin'),
('crole2e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'club1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'c9c1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'member'),
('crole3e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'club2e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'd0d1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e', 'member');

-- Associate Paris Marathon with Paris Runners Club
UPDATE `events` SET `club_id` = 'club1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e' WHERE `id` = 'e1e1e7e4-6e5a-4a2e-9d2a-7e2a1e7e4a2e';
