-- Spixer Universal Migration File
-- This file contains the complete schema and seed data for the application.
-- It is intended to be run to set up a new database from scratch.

START TRANSACTION;

--
-- Drop Existing Tables
--

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;

CREATE TABLE `roles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `user_informations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` char(36) NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `bio` text,
  `avatar_url` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `birthdate` date DEFAULT NULL,
  `is_premium_member` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

CREATE TABLE `clubs` (
  `id` char(36) NOT NULL DEFAULT (UUID()),
  `name` varchar(255) NOT NULL,
  `description` text,
  `federation_id` char(36) DEFAULT NULL,
  `created_by` char(36) NOT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `federation_id` (`federation_id`),
  KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `events` (
  `id` char(36) NOT NULL DEFAULT (UUID()),
  `name` varchar(255) NOT NULL,
  `description` text,
  `label` text,
  `start_time` datetime NOT NULL,
  `end_time` datetime,
  `registration_end_time` datetime,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `organiser_id` char(36) DEFAULT NULL,
  `club_id` char(36) DEFAULT NULL,
  `image_url` varchar(255) DEFAULT NULL,
  `cancel_reason` text,
  `PMR` tinyint(1) NOT NULL DEFAULT '0',
  `postal_code` int NOT NULL,
  `address` text,
  `city` varchar(100) DEFAULT NULL,
  `region` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `oneplan_path` text,
  PRIMARY KEY (`id`),
  KEY `organiser_id` (`organiser_id`),
  KEY `club_id` (`club_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `stages` (
  `id` char(36) NOT NULL DEFAULT (UUID()),
  `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,
  `min_age` int DEFAULT NULL,
  `gpx_file_url` text,
  `image_url` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `event_id` (`event_id`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `registrations` (
  `id` char(36) NOT NULL DEFAULT (UUID()),
  `user_id` char(36) NOT NULL,
  `stage_id` char(36) NOT NULL,
  `type` enum('runner') NOT NULL DEFAULT 'runner',
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `bib_number` varchar(255) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`stage_id`),
  KEY `stage_id` (`stage_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `event_volunteers` (
  `id` char(36) NOT NULL DEFAULT (UUID()),
  `user_id` char(36) NOT NULL,
  `event_id` char(36) NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `role` varchar(100) DEFAULT NULL,
  `notes` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_event` (`user_id`,`event_id`),
  KEY `fk_event_volunteers_event` (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `user_roles` (
  `user_id` char(36) NOT NULL,
  `role_id` int NOT NULL,
  `stage_id` char(36) DEFAULT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `role_id` (`role_id`),
  KEY `stage_id` (`stage_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `rankings` (
  `id` char(36) NOT NULL DEFAULT (UUID()),
  `stage_id` char(36) NOT NULL,
  `user_id` char(36) NOT NULL,
  `bib_number` varchar(255) DEFAULT NULL,
  `rank_position` int DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `stage_id` (`stage_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `ranking_records` (
  `id` char(36) NOT NULL DEFAULT (UUID()),
  `ranking_id` char(36) NOT NULL,
  `face_signature` text,
  `ocr_label` varchar(255) DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `source` varchar(255) DEFAULT 'ai',
  `captured_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ranking_id` (`ranking_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_club_unique` (`user_id`,`club_id`),
  KEY `club_id` (`club_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `guest_profiles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `managed_by_id` char(36) NOT NULL,
  `guest_user_id` char(36) NOT NULL,
  `status` enum('active','unlinked') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `guest_user_id` (`guest_user_id`),
  KEY `managed_by_id` (`managed_by_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `entity_images` (
  `id` char(36) NOT NULL,
  `image_url` varchar(255) NOT NULL,
  `entity_id` char(36) NOT NULL,
  `entity_type` varchar(50) NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `entity_idx` (`entity_id`,`entity_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Seed Data
--

-- Declare UUIDs for seed data
SET @user1_id = UUID();
SET @user2_id = UUID();
SET @user3_id = UUID();
SET @user4_id = UUID();
SET @user5_id = UUID();

SET @event1_id = UUID();
SET @event2_id = UUID();
SET @event3_id = UUID();

SET @stage1_id = UUID();
SET @stage2_id = UUID();
SET @stage3_id = UUID();

-- Categories
INSERT INTO `category` (`id`, `label`) VALUES (1, 'Running'), (2, 'Trail'), (3, 'Marathon'), (4, 'Cycling');

-- Roles
INSERT INTO `roles` (`id`, `name`) VALUES (1, 'admin'), (2, 'organizer'), (3, 'runner'), (4, 'volunteer');

-- User Roles
INSERT INTO `user_roles` (`user_id`, `role_id`) VALUES
(@user1_id, 1),
(@user2_id, 2),
(@user3_id, 3),
(@user4_id, 3),
(@user5_id, 3);

-- Events
INSERT INTO `events` (`id`, `name`, `description`, `start_time`, `end_time`, `city`, `country`, `organiser_id`, `postal_code`) VALUES
(@event1_id, 'Past Marathon', 'A great marathon that happened last year.', '2024-09-01 08:00:00', '2024-09-01 14:00:00', 'Paris', 'France', @user2_id, 75001),
(@event2_id, 'Live Trail Race', 'A trail race happening right now!', '2025-08-29 09:00:00', '2025-08-29 18:00:00', 'Lyon', 'France', @user2_id, 69001),
(@event3_id, 'Upcoming City Run', 'A fun 10k run next month.', '2025-09-20 10:00:00', '2025-09-20 12:00:00', 'Marseille', 'France', @user2_id, 13001);

-- Stages
INSERT INTO `stages` (`id`, `event_id`, `name`, `category_id`, `gpx_file_url`) VALUES
(@stage1_id, @event1_id, '42k Marathon', 3, 'gpx/stage_0.gpx'),
(@stage2_id, @event2_id, '25k Mountain Trail', 2, 'gpx/stage_0.gpx'),
(@stage3_id, @event3_id, '10k City Course', 1, 'gpx/stage_0.gpx');

-- Registrations
INSERT INTO `registrations` (`id`, `user_id`, `stage_id`, `status`) VALUES
(UUID(), @user3_id, @stage1_id, 'approved'),
(UUID(), @user4_id, @stage2_id, 'approved'),
(UUID(), @user3_id, @stage3_id, 'pending');

-- Volunteer Assignments
INSERT INTO `event_volunteers` (`id`, `user_id`, `event_id`, `status`, `role`) VALUES
(UUID(), @user5_id, @event2_id, 'approved', 'Checkpoint Captain'),
(UUID(), @user1_id, @event3_id, 'pending', 'First Aid');

--
-- Constraints for dumped tables
--

ALTER TABLE `user_informations`
  ADD CONSTRAINT `user_informations_ibfk_1` FOREIGN KEY (`user_id`) 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 `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 `fk_event_volunteers_event` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_event_volunteers_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`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,
  ADD CONSTRAINT `user_roles_ibfk_3` FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE SET NULL;

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 (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

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

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 `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 `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 `user_favorite_categories`
  ADD CONSTRAINT `fk_ufc_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_ufc_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

COMMIT;