kknongchang_2506/database/sql/create_pet_active_skills_table.sql

-- 创建宠物激活技能表
-- 用于记录宠物当前激活的技能状态，支持定时任务自动处理

CREATE TABLE `kku_pet_active_skills` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `pet_id` bigint(20) unsigned NOT NULL COMMENT '宠物ID',
  `skill_id` int(10) unsigned NOT NULL COMMENT '技能ID',
  `skill_name` varchar(50) NOT NULL COMMENT '技能名称',
  `start_time` timestamp NOT NULL COMMENT '开始时间',
  `end_time` timestamp NOT NULL COMMENT '结束时间',
  `status` enum('active','expired','cancelled') NOT NULL DEFAULT 'active' COMMENT '状态：active-生效中，expired-已过期，cancelled-已取消',
  `config` json DEFAULT NULL COMMENT '技能配置信息',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_pet_status` (`pet_id`,`status`),
  KEY `idx_skill_status` (`skill_name`,`status`),
  KEY `idx_end_time_status` (`end_time`,`status`),
  KEY `idx_start_time` (`start_time`),
  CONSTRAINT `kku_pet_active_skills_pet_id_foreign` FOREIGN KEY (`pet_id`) REFERENCES `kku_pet_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `kku_pet_active_skills_skill_id_foreign` FOREIGN KEY (`skill_id`) REFERENCES `kku_pet_skills` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='宠物激活技能表 - 记录宠物当前激活的技能状态';

-- 插入示例数据（可选）
-- INSERT INTO `kku_pet_active_skills` (`pet_id`, `skill_id`, `skill_name`, `start_time`, `end_time`, `status`, `config`) VALUES
-- (1, 1, '自动收菜', NOW(), DATE_ADD(NOW(), INTERVAL 2 HOUR), 'active', '{"auto_harvest": true, "check_interval": 60, "last_check_time": "2024-12-30 17:45:00"}'),
-- (2, 2, '自动播种', NOW(), DATE_ADD(NOW(), INTERVAL 4 HOUR), 'active', '{"auto_plant": true, "check_interval": 60, "last_check_time": "2024-12-30 17:45:00", "preferred_seeds": [1001, 1002]}'),
-- (3, 3, '灾害防护', NOW(), DATE_ADD(NOW(), INTERVAL 6 HOUR), 'active', '{"disaster_protection": true, "protected_types": ["all"], "check_interval": 300, "last_check_time": "2024-12-30 17:45:00"}');