-- Conferences Table
CREATE TABLE IF NOT EXISTS `conferences` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `registration_deadline` date NOT NULL,
  `venue` varchar(255) NOT NULL,
  `type` enum('circuit','diocese','connexional') NOT NULL,
  `entity_id` int(11) NOT NULL COMMENT 'ID of circuit, diocese, or connexional entity',
  `fee_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `max_participants` int(11) DEFAULT NULL,
  `status` enum('upcoming','ongoing','completed','cancelled') NOT NULL DEFAULT 'upcoming',
  `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 `entity_id` (`entity_id`),
  KEY `created_by` (`created_by`),
  KEY `type` (`type`),
  KEY `status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Conference Registrations Table
CREATE TABLE IF NOT EXISTS `conference_registrations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conference_id` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `registration_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `payment_status` enum('pending','paid','waived') NOT NULL DEFAULT 'pending',
  `payment_amount` decimal(10,2) DEFAULT NULL,
  `payment_date` datetime DEFAULT NULL,
  `payment_reference` varchar(100) DEFAULT NULL,
  `accommodation_required` tinyint(1) NOT NULL DEFAULT '0',
  `dietary_requirements` text,
  `special_needs` text,
  `attendance_status` enum('registered','confirmed','attended','absent') NOT NULL DEFAULT 'registered',
  `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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `conference_member` (`conference_id`,`member_id`),
  KEY `conference_id` (`conference_id`),
  KEY `member_id` (`member_id`),
  KEY `payment_status` (`payment_status`),
  KEY `attendance_status` (`attendance_status`),
  CONSTRAINT `conference_registrations_ibfk_1` FOREIGN KEY (`conference_id`) REFERENCES `conferences` (`id`) ON DELETE CASCADE,
  CONSTRAINT `conference_registrations_ibfk_2` FOREIGN KEY (`member_id`) REFERENCES `members` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Conference Sessions Table
CREATE TABLE IF NOT EXISTS `conference_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conference_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `venue` varchar(255) DEFAULT NULL,
  `facilitator` varchar(255) DEFAULT 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_sessions_ibfk_1` FOREIGN KEY (`conference_id`) REFERENCES `conferences` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Conference Attendance Table
CREATE TABLE IF NOT EXISTS `conference_attendance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `registration_id` int(11) NOT NULL,
  `session_id` int(11) NOT NULL,
  `status` enum('present','absent','late') NOT NULL DEFAULT 'present',
  `check_in_time` datetime DEFAULT NULL,
  `notes` text,
  `recorded_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`),
  UNIQUE KEY `registration_session` (`registration_id`,`session_id`),
  KEY `registration_id` (`registration_id`),
  KEY `session_id` (`session_id`),
  KEY `recorded_by` (`recorded_by`),
  CONSTRAINT `conference_attendance_ibfk_1` FOREIGN KEY (`registration_id`) REFERENCES `conference_registrations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `conference_attendance_ibfk_2` FOREIGN KEY (`session_id`) REFERENCES `conference_sessions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
