-- Full production database migration for Spixer API
-- This script is designed to be idempotent and safe to run on an existing database.
-- It follows the pattern: DROP -> CREATE -> INSERT -> ALTER


-- Drop dependent tables first to avoid foreign key constraint errors

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

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

--
-- Table structure for table `federations`
--
CREATE TABLE `federations` (
  `id` char(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp 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,
  `name` varchar(255) NOT NULL,
  `description` text,
  `federation_id` char(36) DEFAULT NULL,
  `created_by` char(36) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp 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 `events`
--
CREATE TABLE `events` (
  `id` char(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `label` varchar(255) DEFAULT NULL,
  `start_time` timestamp NOT NULL,
  `end_time` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `organiser_id` char(36) DEFAULT NULL,
  `club_id` char(36) DEFAULT NULL,
  `PMR` tinyint(1) NOT NULL DEFAULT '0',
  `postal_code` varchar(10) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `region` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `oneplan_path` text,
  PRIMARY KEY (`id`)
) 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` timestamp NULL DEFAULT NULL,
  `end_time` timestamp NULL DEFAULT NULL,
  `registration_end_time` timestamp NULL DEFAULT NULL,
  `category_id` int DEFAULT NULL,
  `max_participants` int DEFAULT '100',
  `min_age` int DEFAULT NULL,
  `gpx_file_url` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type` varchar(20) NOT NULL DEFAULT 'gpx',
  `lap_distance_meters` int DEFAULT NULL,
  `required_laps` int DEFAULT NULL,
  `total_distance_meters` int DEFAULT NULL,
  `bib_number_min` int DEFAULT NULL,
  `bib_number_max` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `entity_images`
--
CREATE TABLE `entity_images` (
  `id` char(36) NOT NULL,
  `image_url` text NOT NULL,
  `entity_id` char(36) NOT NULL,
  `entity_type` enum('event','club','stage') NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_entity` (`entity_type`,`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Table structure for table `club_memberships`
--
CREATE TABLE `club_memberships` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` char(36) NOT NULL,
  `club_id` char(36) NOT NULL,
  `role` enum('admin','member','pending') NOT NULL DEFAULT 'pending',
  `license_number` varchar(255) DEFAULT NULL,
  `joined_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`club_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Other tables...
CREATE TABLE `user_roles` ( `user_id` char(36) NOT NULL, `role_id` int NOT NULL, PRIMARY KEY (`user_id`,`role_id`)) ENGINE=InnoDB;
CREATE TABLE `user_informations` ( `id` int NOT NULL AUTO_INCREMENT, `user_id` char(36) NOT NULL, `first_name` blob, `last_name` blob, `bio` blob, `avatar_url` text, `location` blob, `birthdate` timestamp NULL DEFAULT NULL, `is_premium_member` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `user_id` (`user_id`)) ENGINE=InnoDB;
CREATE TABLE `user_favorite_categories` ( `id` int NOT NULL AUTO_INCREMENT, `user_id` char(36) NOT NULL, `category_id` int NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `user_id` (`user_id`,`category_id`)) ENGINE=InnoDB;
CREATE TABLE `registrations` ( `id` char(36) NOT NULL, `user_id` char(36) NOT NULL, `stage_id` char(36) NOT NULL, `status` enum('pending','approved','rejected') DEFAULT 'pending', `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB;
CREATE TABLE `event_volunteers` ( `id` char(36) NOT NULL, `user_id` char(36) NOT NULL, `event_id` char(36) NOT NULL, `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending', `role` varchar(255) DEFAULT NULL, `notes` text, PRIMARY KEY (`id`), UNIQUE KEY `user_id` (`user_id`,`event_id`)) ENGINE=InnoDB;
CREATE TABLE `rankings` ( `id` char(36) NOT NULL, `stage_id` char(36) NOT NULL, `registration_id` char(36) NOT NULL, `rank_position` int DEFAULT NULL, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB;

CREATE TABLE `ranking_checkins` (
  `id` char(36) NOT NULL,
  `ranking_id` char(36) NOT NULL,
  `stage_id` char(36) NOT NULL,
  `checkin_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `lap_number` int DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `recorded_by_user_id` char(36) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `ranking_records` ( `id` char(36) NOT NULL, `ranking_id` char(36) NOT NULL, `latitude` double DEFAULT NULL, `longitude` double DEFAULT NULL, `captured_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`)) ENGINE=InnoDB;
CREATE TABLE `guest_profiles` ( `id` int NOT NULL AUTO_INCREMENT, `managed_by_id` char(36) NOT NULL, `guest_user_id` char(36) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `guest_user_id` (`guest_user_id`)) ENGINE=InnoDB;
CREATE TABLE `payments` ( `id` char(36) NOT NULL, `user_id` char(36) NOT NULL, `amount` int NOT NULL, `currency` varchar(3) NOT NULL, `status` varchar(255) NOT NULL, `stripe_payment_intent_id` varchar(255) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
CREATE TABLE `price` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `amount` int NOT NULL, `currency` varchar(3) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
CREATE TABLE `newsletter_subscribers` ( `id` int NOT NULL AUTO_INCREMENT, `email` varchar(255) NOT NULL, `subscribed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `email` (`email`)) ENGINE=InnoDB;

--
-- Seed data
--
INSERT INTO `category` (`id`, `label`) VALUES (1, 'Course à pied'), (2, 'Trail'), (3, 'VTT'), (4, 'Triathlon');
INSERT INTO `roles` (`id`, `name`) VALUES (1, 'admin'), (2, 'organizer'), (3, 'runner'), (4, 'volunteer');

SET @admin_user_id = UUID();
SET @organizer_user_id = UUID();
SET @runner_user_id = UUID();
SET @federation_id = UUID();
SET @club_id = UUID();
SET @event_id = UUID();
SET @stage_id = UUID();

INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES
(@admin_user_id, 1),
(@organizer_user_id, 2),
(@runner_user_id, 3);

INSERT INTO `federations` (`id`, `name`) VALUES (@federation_id, 'Fédération Française de Course');

INSERT INTO `clubs` (`id`, `name`, `federation_id`, `created_by`) VALUES
(@club_id, 'Spixer Running Club', @federation_id, @organizer_user_id);

INSERT INTO `club_memberships` (`user_id`, `club_id`, `role`) VALUES
(@organizer_user_id, @club_id, 'admin'),
(@runner_user_id, @club_id, 'member');

INSERT INTO `events` (`id`, `name`, `start_time`, `organiser_id`, `club_id`, `postal_code`) VALUES
(@event_id, 'Course de Paris', '2025-10-26 09:00:00', @organizer_user_id, @club_id, '75001');

INSERT INTO `stages` (`id`, `event_id`, `name`, `category_id`) VALUES
(@stage_id, @event_id, '10km de la Tour Eiffel', 1);

INSERT INTO `entity_images` (`id`, `entity_id`, `entity_type`, `image_url`) VALUES
(UUID(), @event_id, 'event', 'https://example.com/images/event_paris.jpg'),
(UUID(), @club_id, 'club', 'https://example.com/images/club_logo.png'),
(UUID(), @stage_id, 'stage', 'https://example.com/images/stage_eiffel.jpg');

--
-- Constraints for dumped tables
--
ALTER TABLE `clubs`
  ADD CONSTRAINT `clubs_ibfk_1` FOREIGN KEY (`federation_id`) REFERENCES `federations` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `clubs_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE;

ALTER TABLE `events`
  ADD CONSTRAINT `events_ibfk_1` FOREIGN KEY (`organiser_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `events_ibfk_2` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE SET NULL;

ALTER TABLE `stages`
  ADD CONSTRAINT `stages_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `stages_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE SET NULL;

ALTER TABLE `club_memberships`
  ADD CONSTRAINT `club_memberships_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `club_memberships_ibfk_2` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE;

ALTER TABLE `user_roles`
  ADD CONSTRAINT `user_roles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `user_roles_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE;

ALTER TABLE `user_informations`
  ADD CONSTRAINT `user_informations_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

ALTER TABLE `user_favorite_categories`
  ADD CONSTRAINT `user_favorite_categories_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `user_favorite_categories_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE;

ALTER TABLE `registrations`
  ADD CONSTRAINT `registrations_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `registrations_ibfk_2` FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE;

ALTER TABLE `event_volunteers`
  ADD CONSTRAINT `event_volunteers_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `event_volunteers_ibfk_2` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE;

ALTER TABLE `rankings`
  ADD CONSTRAINT `rankings_ibfk_1` FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `rankings_ibfk_2` FOREIGN KEY (`registration_id`) REFERENCES `registrations` (`id`) ON DELETE CASCADE;

ALTER TABLE `ranking_checkins`
  ADD CONSTRAINT `ranking_checkins_ibfk_1` FOREIGN KEY (`ranking_id`) REFERENCES `rankings` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `ranking_checkins_ibfk_2` FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `ranking_checkins_ibfk_3` FOREIGN KEY (`recorded_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

ALTER TABLE `ranking_records`
  ADD CONSTRAINT `ranking_records_ibfk_1` FOREIGN KEY (`ranking_id`) REFERENCES `rankings` (`id`) ON DELETE CASCADE;

ALTER TABLE `guest_profiles`
  ADD CONSTRAINT `guest_profiles_ibfk_1` FOREIGN KEY (`managed_by_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `guest_profiles_ibfk_2` FOREIGN KEY (`guest_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

ALTER TABLE `payments`
  ADD CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;


-- Migration to add checkpoint and assignment tables, and rework the ranking_records table.

-- 1. Create stage_checkpoints table
CREATE TABLE `stage_checkpoints` (
  `id` char(36) NOT NULL,
  `stage_id` char(36) NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_stage_checkpoints_stage` FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE
);
CREATE INDEX `idx_stage_checkpoints_stage` ON `stage_checkpoints` (`stage_id`);

-- 2. Create checkpoint_assignments table
CREATE TABLE `checkpoint_assignments` (
  `id` char(36) NOT NULL,
  `checkpoint_id` char(36) NOT NULL,
  `volunteer_user_id` char(36) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE (`checkpoint_id`, `volunteer_user_id`),
  CONSTRAINT `fk_checkpoint_assignments_checkpoint` FOREIGN KEY (`checkpoint_id`) REFERENCES `stage_checkpoints` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_checkpoint_assignments_user` FOREIGN KEY (`volunteer_user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
);
CREATE INDEX `idx_checkpoint_assignments_checkpoint` ON `checkpoint_assignments` (`checkpoint_id`);
CREATE INDEX `idx_checkpoint_assignments_user` ON `checkpoint_assignments` (`volunteer_user_id`);


-- 3. Drop old ranking tables

-- 4. Create new ranking_records table
CREATE TABLE `ranking_records` (
  `id` char(36) NOT NULL,
  `ranking_id` char(36) NOT NULL,
  `record_type` varchar(255) NOT NULL,
  `recorded_by_user_id` char(36) DEFAULT NULL,
  `captured_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `bib_number` varchar(255) DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_ranking_records_ranking` FOREIGN KEY (`ranking_id`) REFERENCES `rankings` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ranking_records_user` FOREIGN KEY (`recorded_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
);
CREATE INDEX `idx_ranking_records_ranking` ON `ranking_records` (`ranking_id`);
CREATE INDEX `idx_ranking_records_user` ON `ranking_records` (`recorded_by_user_id`);
