-- Spixer API - MySQL Migration - Part 5: Add Sample Data (For Debug/Staging)
-- WARNING: Do not run this script on a production database that contains live data.

SET @admin_user_id = UUID();
SET @organizer_user_id = UUID();
SET @runner_user_id = UUID();
SET @volunteer_user_id = UUID();
SET @club_admin_user_id = UUID();
SET @guest_manager_user_id = UUID();
SET @future_date_1 = DATE_ADD(CURDATE(), INTERVAL 3 MONTH);
SET @future_date_2 = DATE_ADD(CURDATE(), INTERVAL 6 MONTH);
SET @federation_id = UUID();
SET @club_id_1 = UUID();
SET @club_id_2 = UUID();
SET @event_id_1 = UUID();
SET @event_id_2 = UUID();
SET @stage_id_1 = UUID();
SET @stage_id_2 = UUID();
SET @stage_id_3 = UUID();
SET @stage_id_laps = UUID();

--
-- Insert Users with different roles
--
INSERT INTO `users` (`id`, `email`, `username`, `password_hash`, `email_verified`) VALUES
(@admin_user_id, 'admin@example.com', 'admin_user', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1),
(@organizer_user_id, 'organizer@example.com', 'organizer_user', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1),
(@runner_user_id, 'runner@example.com', 'runner_user', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1),
(@volunteer_user_id, 'volunteer@example.com', 'volunteer_user', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1),
(@club_admin_user_id, 'clubadmin@example.com', 'club_admin_user', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1),
(@guest_manager_user_id, 'manager@example.com', 'guest_manager_user', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1);

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

--
-- Insert Federations and Clubs
--
INSERT INTO `federations` (`id`, `name`, `description`) VALUES
(@federation_id, 'World Running Federation', 'The global federation for all running sports.');

INSERT INTO `clubs` (`id`, `name`, `description`, `federation_id`, `created_by`) VALUES
(@club_id_1, 'City Runners Club', 'A club for urban running enthusiasts.', @federation_id, @club_admin_user_id),
(@club_id_2, 'Mountain Trail Blazers', 'Exploring trails and mountains.', @federation_id, @organizer_user_id);

INSERT INTO `club_memberships` (`user_id`, `club_id`, `role`, `license_number`) VALUES
(@club_admin_user_id, @club_id_1, 'admin', 'LIC-CLUB1-ADMIN'),
(@runner_user_id, @club_id_1, 'member', 'LIC-RUNNER-001'),
(@organizer_user_id, @club_id_2, 'admin', 'LIC-CLUB2-ADMIN');

--
-- Insert Events and Stages
--
INSERT INTO `events` (`id`, `name`, `description`, `start_time`, `end_time`, `organiser_id`, `club_id`, `postal_code`, `city`, `country`) VALUES
(@event_id_1, 'Annual City Marathon', 'The biggest marathon event of the year.', @future_date_1, DATE_ADD(@future_date_1, INTERVAL 8 HOUR), @organizer_user_id, @club_id_1, '75001', 'Paris', 'France'),
(@event_id_2, 'Mountain Challenge 50k', 'A challenging 50k trail run in the mountains.', @future_date_2, DATE_ADD(@future_date_2, INTERVAL 12 HOUR), @organizer_user_id, @club_id_2, '74400', 'Chamonix', 'France');

INSERT INTO `stages` (`id`, `event_id`, `name`, `description`, `start_time`, `end_time`, `category_id`, `max_participants`, `min_age`, `type`, `total_distance_meters`) VALUES
(@stage_id_1, @event_id_1, 'Full Marathon', 'The main 42.195km race.', @future_date_1, DATE_ADD(@future_date_1, INTERVAL 6 HOUR), 5, 5000, 18, 'point_to_point', 42195),
(@stage_id_2, @event_id_1, '10k Fun Run', 'A shorter 10km race for everyone.', @future_date_1, DATE_ADD(@future_date_1, INTERVAL 2 HOUR), 1, 10000, 14, 'point_to_point', 10000),
(@stage_id_3, @event_id_2, '50k Ultra Trail', 'The full 50km ultra trail.', @future_date_2, DATE_ADD(@future_date_2, INTERVAL 12 HOUR), 2, 500, 20, 'gpx', 50000),
(@stage_id_laps, @event_id_1, 'Kids 3-Lap Race', 'A 3-lap race for kids around the park.', @future_date_1, DATE_ADD(@future_date_1, INTERVAL 1 HOUR), 1, 200, 8, 'laps', 1200);

--
-- Insert Registrations
--
-- Runner is approved for the Full Marathon
INSERT INTO `registrations` (`id`, `user_id`, `stage_id`, `status`, `bib_number`) VALUES (UUID(), @runner_user_id, @stage_id_1, 'approved', '1001');
-- Runner has a pending registration for the 50k
INSERT INTO `registrations` (`id`, `user_id`, `stage_id`, `status`, `bib_number`) VALUES (UUID(), @runner_user_id, @stage_id_3, 'pending', '501');
-- Admin user also registers for the fun run
INSERT INTO `registrations` (`id`, `user_id`, `stage_id`, `status`, `bib_number`) VALUES (UUID(), @admin_user_id, @stage_id_2, 'approved', '2001');

--
-- Insert Volunteer Data
--
INSERT INTO `event_volunteers` (`id`, `user_id`, `event_id`, `status`, `role`, `notes`) VALUES (UUID(), @volunteer_user_id, @event_id_1, 'approved', 'Water Station Captain', 'Experienced volunteer, assigned to station 5.');
INSERT INTO `event_volunteers` (`id`, `user_id`, `event_id`, `status`, `role`, `notes`) VALUES (UUID(), @club_admin_user_id, @event_id_1, 'pending', 'Course Marshal', 'Wants to help out.');

--
-- Insert Guest Profiles
--
SET @guest_user_id = UUID();
INSERT INTO `users` (`id`, `email`, `username`, `password_hash`, `email_verified`) VALUES (@guest_user_id, CONCAT(@guest_user_id, '@guest.spixer.fr'), 'guest_child_01', 'N/A', 0);
INSERT INTO `user_informations` (`user_id`, `first_name`, `last_name`, `birthdate`) VALUES (@guest_user_id, 'Alex', 'Manager', '2012-06-15');
INSERT INTO `guest_profiles` (`managed_by_id`, `guest_user_id`) VALUES (@guest_manager_user_id, @guest_user_id);
-- Register the guest for the kids race
INSERT INTO `registrations` (`id`, `user_id`, `stage_id`, `status`, `bib_number`) VALUES (UUID(), @guest_user_id, @stage_id_laps, 'approved', 'K01');

--
-- Insert some ranking data for a completed (imaginary past) event
--
SET @past_event_id = UUID();
SET @past_stage_id = UUID();
SET @past_runner_1_id = UUID();
SET @past_runner_2_id = UUID();
SET @past_reg_1_id = UUID();
SET @past_reg_2_id = UUID();
SET @past_ranking_1_id = UUID();
SET @past_ranking_2_id = UUID();

INSERT INTO `events` (`id`, `name`, `start_time`) VALUES (@past_event_id, 'Past Charity Run', DATE_SUB(CURDATE(), INTERVAL 1 YEAR));
INSERT INTO `stages` (`id`, `event_id`, `name`, `type`) VALUES (@past_stage_id, @past_event_id, '5k Run', 'point_to_point');
INSERT INTO `users` (`id`, `email`, `username`, `password_hash`, `email_verified`) VALUES (@past_runner_1_id, 'past.runner1@example.com', 'past_runner1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1), (@past_runner_2_id, 'past.runner2@example.com', 'past_runner2', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1);
INSERT INTO `registrations` (`id`, `user_id`, `stage_id`, `status`, `bib_number`) VALUES (@past_reg_1_id, @past_runner_1_id, @past_stage_id, 'approved', 'P101'), (@past_reg_2_id, @past_runner_2_id, @past_stage_id, 'approved', 'P102');
INSERT INTO `rankings` (`id`, `stage_id`, `registration_id`, `rank_position`) VALUES (@past_ranking_1_id, @past_stage_id, @past_reg_1_id, 1), (@past_ranking_2_id, @past_stage_id, @past_reg_2_id, 2);
INSERT INTO `ranking_records` (`id`, `ranking_id`, `record_type`, `captured_at`) VALUES (UUID(), @past_ranking_1_id, 'start', DATE_SUB(CURDATE(), INTERVAL 1 YEAR)), (UUID(), @past_ranking_1_id, 'finish', DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 YEAR), INTERVAL 20 MINUTE));
INSERT INTO `ranking_records` (`id`, `ranking_id`, `record_type`, `captured_at`) VALUES (UUID(), @past_ranking_2_id, 'start', DATE_SUB(CURDATE(), INTERVAL 1 YEAR)), (UUID(), @past_ranking_2_id, 'finish', DATE_ADD(DATE_SUB(CURDATE(), INTERVAL 1 YEAR), INTERVAL 22 MINUTE));

COMMIT;
