CREATE TABLE `events` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `slug` VARCHAR(191) NOT NULL,
    `management_token` VARCHAR(191) NULL,
    `email` VARCHAR(320) NOT NULL,
    `image_url` VARCHAR(2048) NULL,
    `title` TEXT NOT NULL,
    `category` TEXT NOT NULL,
    `date` VARCHAR(32) NOT NULL,
    `time` VARCHAR(32) NOT NULL,
    `location_type` VARCHAR(24) NOT NULL DEFAULT 'in_person',
    `venue_name` VARCHAR(191) NULL,
    `address` VARCHAR(255) NULL,
    `postal_code` VARCHAR(12) NULL,
    `city` VARCHAR(120) NULL,
    `department` VARCHAR(120) NULL,
    `online_url` VARCHAR(2048) NULL,
    `online_platform` VARCHAR(32) NOT NULL DEFAULT 'other',
    `online_platform_other` VARCHAR(120) NULL,
    `online_url_private` TINYINT(1) NOT NULL DEFAULT 1,
    `registration_mode` VARCHAR(32) NOT NULL DEFAULT 'free',
    `external_platform` VARCHAR(32) NULL,
    `external_url` VARCHAR(2048) NULL,
    `latitude` DECIMAL(10,7) NULL,
    `longitude` DECIMAL(10,7) NULL,
    `location` TEXT NOT NULL,
    `description` TEXT NOT NULL,
    `price` TEXT NOT NULL,
    `capacity` INT UNSIGNED NULL,
    `available_capacity` INT UNSIGNED NULL,
    `registrations_open` TINYINT(1) NOT NULL DEFAULT 1,
    `waitlist_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    `updated_at` VARCHAR(40) NOT NULL,
    `created_at` VARCHAR(40) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `events_slug_unique` (`slug`),
    UNIQUE KEY `events_management_token_unique` (`management_token`),
    KEY `events_created_at_index` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `users` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(320) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `display_name` VARCHAR(120) NOT NULL DEFAULT '',
    `role` VARCHAR(24) NOT NULL DEFAULT 'user',
    `favorite_location_type` VARCHAR(24) NULL,
    `favorite_venue_name` VARCHAR(191) NULL,
    `favorite_address` VARCHAR(255) NULL,
    `favorite_postal_code` VARCHAR(12) NULL,
    `favorite_city` VARCHAR(120) NULL,
    `favorite_department` VARCHAR(120) NULL,
    `email_verified_at` VARCHAR(40) NULL,
    `created_at` VARCHAR(40) NOT NULL,
    `updated_at` VARCHAR(40) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_email_unique` (`email`),
    KEY `users_role_index` (`role`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `user_tokens` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `token_hash` CHAR(64) NOT NULL,
    `token_type` VARCHAR(32) NOT NULL,
    `expires_at` VARCHAR(40) NOT NULL,
    `created_at` VARCHAR(40) NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `user_tokens_hash_unique` (`token_hash`),
    KEY `user_tokens_user_type_index` (`user_id`,`token_type`),
    CONSTRAINT `user_tokens_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

ALTER TABLE `events` ADD COLUMN `user_id` BIGINT UNSIGNED NULL AFTER `id`;
ALTER TABLE `events` ADD KEY `events_user_id_index` (`user_id`);
ALTER TABLE `events` ADD CONSTRAINT `events_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

CREATE TABLE `event_registrations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `event_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NULL,
  `participant_name` VARCHAR(120) NOT NULL,
  `email` VARCHAR(320) NOT NULL,
  `status` VARCHAR(24) NOT NULL DEFAULT 'confirmed',
  `created_at` VARCHAR(40) NOT NULL,
  `updated_at` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `registration_event_email_unique` (`event_id`,`email`),
  KEY `registration_user_index` (`user_id`), KEY `registration_email_index` (`email`),
  KEY `registration_event_status_created_index` (`event_id`,`status`,`created_at`),
  CONSTRAINT `registration_event_fk` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `registration_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `registration_access_tokens` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(320) NOT NULL,
  `token_hash` CHAR(64) NOT NULL,
  `expires_at` VARCHAR(40) NOT NULL,
  `created_at` VARCHAR(40) NOT NULL,
  PRIMARY KEY (`id`), UNIQUE KEY `registration_access_token_unique` (`token_hash`),
  KEY `registration_access_email_index` (`email`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `app_settings` (
    `setting_key` VARCHAR(120) NOT NULL,
    `setting_value` TEXT NOT NULL,
    `updated_at` VARCHAR(40) NOT NULL,
    PRIMARY KEY (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
