-- Conference Accommodation Types Table
CREATE TABLE IF NOT EXISTS `conference_accommodation_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conference_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `capacity` int(11) NOT NULL DEFAULT '1',
  `total_rooms` int(11) NOT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `conference_id` (`conference_id`),
  CONSTRAINT `conference_accommodation_types_ibfk_1` FOREIGN KEY (`conference_id`) REFERENCES `conferences` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Conference Room Assignments Table
CREATE TABLE IF NOT EXISTS `conference_room_assignments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `registration_id` int(11) NOT NULL,
  `accommodation_type_id` int(11) NOT NULL,
  `room_number` varchar(50) NOT NULL,
  `check_in_date` datetime DEFAULT NULL,
  `check_out_date` datetime DEFAULT NULL,
  `notes` text,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `updated_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `registration_id` (`registration_id`),
  KEY `accommodation_type_id` (`accommodation_type_id`),
  CONSTRAINT `conference_room_assignments_ibfk_1` FOREIGN KEY (`registration_id`) REFERENCES `conference_registrations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `conference_room_assignments_ibfk_2` FOREIGN KEY (`accommodation_type_id`) REFERENCES `conference_accommodation_types` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
