-- 旅行活动报名表
CREATE TABLE IF NOT EXISTS travel_registrations (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '报名记录ID',
    travel_plan_id INT NOT NULL COMMENT '旅行计划ID',
    user_id INT NOT NULL COMMENT '报名用户ID',
    message TEXT COMMENT '报名留言',
    emergency_contact VARCHAR(50) COMMENT '紧急联系人',
    emergency_phone VARCHAR(20) COMMENT '紧急联系电话',
    status ENUM('pending', 'approved', 'rejected', 'cancelled') DEFAULT 'pending' COMMENT '报名状态',
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '报名时间',
    responded_at TIMESTAMP NULL COMMENT '审核时间',
    reject_reason VARCHAR(200) COMMENT '拒绝原因',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    FOREIGN KEY (travel_plan_id) REFERENCES travel_plans(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    
    INDEX idx_travel_plan_id (travel_plan_id),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_applied_at (applied_at),
    
    UNIQUE KEY unique_user_travel (user_id, travel_plan_id) COMMENT '同一用户不能重复报名同一活动'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='旅行活动报名表';