-- Migration to add tables for club management feature
-- Date: 2025-08-26

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Table structure for table `federations`
--
CREATE TABLE `federations` (
  `id` char(36) NOT NULL COMMENT 'Unique identifier for the federation (UUID)',
  `name` varchar(255) NOT NULL COMMENT 'Name of the federation',
  `description` text COMMENT 'Description of the federation',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `federations`
  ADD PRIMARY KEY (`id`);

--
-- Table structure for table `clubs`
--
CREATE TABLE `clubs` (
  `id` char(36) NOT NULL COMMENT 'Unique identifier for the club (UUID)',
  `name` varchar(255) NOT NULL COMMENT 'Name of the club',
  `description` text COMMENT 'Description of the club',
  `federation_id` char(36) DEFAULT NULL COMMENT 'Foreign key to the federations table',
  `created_by` char(36) NOT NULL COMMENT 'Foreign key to the users table (creator of the club)',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `clubs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_clubs_federation` (`federation_id`),
  ADD KEY `fk_clubs_created_by` (`created_by`);

ALTER TABLE `clubs`
  ADD CONSTRAINT `fk_clubs_federation` FOREIGN KEY (`federation_id`) REFERENCES `federations` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_clubs_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Table structure for table `club_memberships`
--
CREATE TABLE `club_memberships` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` char(36) NOT NULL COMMENT 'Foreign key to the users table',
  `club_id` char(36) NOT NULL COMMENT 'Foreign key to the clubs table',
  `role` enum('admin','member','pending') NOT NULL DEFAULT 'pending' COMMENT 'Role of the user in the club',
  `license_number` varchar(255) DEFAULT NULL COMMENT 'License number of the user for the federation',
  `joined_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_club` (`user_id`,`club_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `club_memberships`
  ADD KEY `fk_cm_user` (`user_id`),
  ADD KEY `fk_cm_club` (`club_id`);

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

COMMIT;
