kknongchang_2506/app/Module/SocialFarm/Databases/createsql/social_farm_tables.sql

-- SocialFarm模块数据表创建SQL
-- 创建时间: 2025-07-02
-- 模块: SocialFarm
-- 描述: 社交农场系统相关数据表

-- 1. 偷菜记录表
CREATE TABLE `kku_social_farm_steal_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `stealer_id` bigint(20) unsigned NOT NULL COMMENT '偷菜者用户ID',
  `owner_id` bigint(20) unsigned NOT NULL COMMENT '农场主用户ID',
  `land_id` bigint(20) unsigned NOT NULL COMMENT '被偷的土地ID',
  `crop_id` bigint(20) unsigned NOT NULL COMMENT '被偷的作物ID',
  `item_id` bigint(20) unsigned NOT NULL COMMENT '偷到的物品ID',
  `item_amount` int(11) NOT NULL DEFAULT '0' COMMENT '偷到的物品数量',
  `original_amount` int(11) NOT NULL DEFAULT '0' COMMENT '作物原始数量',
  `steal_ratio` decimal(5,4) NOT NULL DEFAULT '0.0000' COMMENT '偷菜比例',
  `steal_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '偷菜时间',
  `steal_status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '偷菜状态:1成功,2失败,3被保护',
  `ip_address` varchar(45) DEFAULT NULL COMMENT '偷菜者IP地址',
  `user_agent` text COMMENT '用户代理信息',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_stealer_time` (`stealer_id`, `steal_time`),
  KEY `idx_owner_time` (`owner_id`, `steal_time`),
  KEY `idx_land_crop` (`land_id`, `crop_id`),
  KEY `idx_steal_status` (`steal_status`, `steal_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='偷菜记录表';

-- 2. 农场访问记录表
CREATE TABLE `kku_social_farm_visit_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `visitor_id` bigint(20) unsigned NOT NULL COMMENT '访问者用户ID',
  `owner_id` bigint(20) unsigned NOT NULL COMMENT '农场主用户ID',
  `visit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '访问时间',
  `visit_type` tinyint(4) NOT NULL DEFAULT '1' COMMENT '访问类型:1普通访问,2偷菜访问,3互助访问',
  `actions` json DEFAULT NULL COMMENT '访问期间的行为记录',
  `duration` int(11) NOT NULL DEFAULT '0' COMMENT '访问持续时间(秒)',
  `steal_count` int(11) NOT NULL DEFAULT '0' COMMENT '本次访问偷菜次数',
  `help_count` int(11) NOT NULL DEFAULT '0' COMMENT '本次访问互助次数',
  `ip_address` varchar(45) DEFAULT NULL COMMENT '访问者IP地址',
  `user_agent` text COMMENT '用户代理信息',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_visitor_time` (`visitor_id`, `visit_time`),
  KEY `idx_owner_time` (`owner_id`, `visit_time`),
  KEY `idx_visit_type` (`visit_type`, `visit_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='农场访问记录表';

-- 3. 社交设置表
CREATE TABLE `kku_social_farm_settings` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `allow_steal` tinyint(4) NOT NULL DEFAULT '1' COMMENT '允许偷菜:1允许,0禁止',
  `allow_help` tinyint(4) NOT NULL DEFAULT '1' COMMENT '允许互助:1允许,0禁止',
  `allow_visit` tinyint(4) NOT NULL DEFAULT '1' COMMENT '允许访问:1允许,0禁止',
  `steal_protection_hours` int(11) NOT NULL DEFAULT '0' COMMENT '偷菜保护时长(小时)',
  `daily_steal_limit` int(11) NOT NULL DEFAULT '10' COMMENT '每日被偷次数限制',
  `daily_help_limit` int(11) NOT NULL DEFAULT '20' COMMENT '每日被帮助次数限制',
  `notification_enabled` tinyint(4) NOT NULL DEFAULT '1' COMMENT '通知开关:1开启,0关闭',
  `auto_revenge` tinyint(4) NOT NULL DEFAULT '0' COMMENT '自动反偷:1开启,0关闭',
  `friend_only` tinyint(4) NOT NULL DEFAULT '1' COMMENT '仅好友可访问:1是,0否',
  `blacklist_users` json DEFAULT NULL COMMENT '黑名单用户ID列表',
  `whitelist_users` json DEFAULT NULL COMMENT '白名单用户ID列表',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='社交农场设置表';

-- 4. 互助记录表
CREATE TABLE `kku_social_farm_help_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `helper_id` bigint(20) unsigned NOT NULL COMMENT '帮助者用户ID',
  `owner_id` bigint(20) unsigned NOT NULL COMMENT '农场主用户ID',
  `land_id` bigint(20) unsigned NOT NULL COMMENT '帮助的土地ID',
  `help_type` tinyint(4) NOT NULL COMMENT '帮助类型:1浇水,2施肥,3除草,4杀虫,5收获',
  `help_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '帮助时间',
  `item_consumed_id` bigint(20) unsigned DEFAULT NULL COMMENT '消耗的道具ID',
  `item_consumed_amount` int(11) NOT NULL DEFAULT '0' COMMENT '消耗的道具数量',
  `reward_item_id` bigint(20) unsigned DEFAULT NULL COMMENT '奖励物品ID',
  `reward_amount` int(11) NOT NULL DEFAULT '0' COMMENT '奖励数量',
  `exp_reward` int(11) NOT NULL DEFAULT '0' COMMENT '经验奖励',
  `help_effect` json DEFAULT NULL COMMENT '帮助效果详情',
  `ip_address` varchar(45) DEFAULT NULL COMMENT '帮助者IP地址',
  `user_agent` text COMMENT '用户代理信息',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_helper_time` (`helper_id`, `help_time`),
  KEY `idx_owner_time` (`owner_id`, `help_time`),
  KEY `idx_land_type` (`land_id`, `help_type`),
  KEY `idx_help_type` (`help_type`, `help_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='互助记录表';

-- 5. 社交统计表
CREATE TABLE `kku_social_farm_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `stat_date` date NOT NULL COMMENT '统计日期',
  `steal_count` int(11) NOT NULL DEFAULT '0' COMMENT '偷菜次数',
  `stolen_count` int(11) NOT NULL DEFAULT '0' COMMENT '被偷次数',
  `help_count` int(11) NOT NULL DEFAULT '0' COMMENT '帮助次数',
  `helped_count` int(11) NOT NULL DEFAULT '0' COMMENT '被帮助次数',
  `visit_count` int(11) NOT NULL DEFAULT '0' COMMENT '访问次数',
  `visited_count` int(11) NOT NULL DEFAULT '0' COMMENT '被访问次数',
  `steal_items_gained` int(11) NOT NULL DEFAULT '0' COMMENT '偷菜获得物品数',
  `steal_items_lost` int(11) NOT NULL DEFAULT '0' COMMENT '被偷失去物品数',
  `help_rewards_gained` int(11) NOT NULL DEFAULT '0' COMMENT '互助获得奖励数',
  `total_exp_gained` int(11) NOT NULL DEFAULT '0' COMMENT '总经验获得',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_date` (`user_id`, `stat_date`),
  KEY `idx_stat_date` (`stat_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='社交农场统计表';

-- 6. 偷菜保护记录表
CREATE TABLE `kku_social_farm_protections` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `user_id` bigint(20) unsigned NOT NULL COMMENT '用户ID',
  `land_id` bigint(20) unsigned DEFAULT NULL COMMENT '土地ID(NULL表示全农场保护)',
  `protection_type` tinyint(4) NOT NULL COMMENT '保护类型:1时间保护,2道具保护,3系统保护',
  `start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '保护开始时间',
  `end_time` timestamp NOT NULL COMMENT '保护结束时间',
  `protection_item_id` bigint(20) unsigned DEFAULT NULL COMMENT '保护道具ID',
  `is_active` tinyint(4) NOT NULL DEFAULT '1' COMMENT '是否激活:1激活,0失效',
  `created_by` bigint(20) unsigned DEFAULT NULL COMMENT '创建者ID',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_active` (`user_id`, `is_active`),
  KEY `idx_land_active` (`land_id`, `is_active`),
  KEY `idx_end_time` (`end_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='偷菜保护记录表';

-- 插入默认配置数据
-- 注意：实际使用时需要根据具体需求调整这些默认值

-- 为现有用户创建默认社交设置（如果需要）
-- INSERT INTO kku_social_farm_settings (user_id) 
-- SELECT id FROM kku_users 
-- WHERE id NOT IN (SELECT user_id FROM kku_social_farm_settings);

-- 创建索引优化查询性能
-- 这些索引在表创建时已经包含，这里仅作为说明

-- 偷菜记录表的复合索引
-- ALTER TABLE kku_social_farm_steal_logs ADD INDEX idx_stealer_owner_time (stealer_id, owner_id, steal_time);

-- 访问记录表的复合索引  
-- ALTER TABLE kku_social_farm_visit_logs ADD INDEX idx_visitor_owner_time (visitor_id, owner_id, visit_time);

-- 互助记录表的复合索引
-- ALTER TABLE kku_social_farm_help_logs ADD INDEX idx_helper_owner_time (helper_id, owner_id, help_time);

-- 统计表的日期范围索引
-- ALTER TABLE kku_social_farm_stats ADD INDEX idx_user_date_range (user_id, stat_date);

-- 保护表的时间范围索引
-- ALTER TABLE kku_social_farm_protections ADD INDEX idx_time_range (start_time, end_time);

-- 创建视图用于常用查询（可选）

-- 用户每日偷菜统计视图
CREATE VIEW v_daily_steal_stats AS
SELECT 
    user_id,
    DATE(steal_time) as steal_date,
    COUNT(*) as steal_count,
    SUM(item_amount) as total_items
FROM kku_social_farm_steal_logs 
WHERE steal_status = 1
GROUP BY user_id, DATE(steal_time);

-- 用户社交活跃度视图
CREATE VIEW v_user_social_activity AS
SELECT 
    u.id as user_id,
    u.nickname,
    COALESCE(s.steal_count, 0) as today_steal_count,
    COALESCE(s.help_count, 0) as today_help_count,
    COALESCE(s.visit_count, 0) as today_visit_count,
    COALESCE(settings.allow_steal, 1) as allow_steal,
    COALESCE(settings.allow_help, 1) as allow_help,
    COALESCE(settings.allow_visit, 1) as allow_visit
FROM kku_users u
LEFT JOIN kku_social_farm_stats s ON u.id = s.user_id AND s.stat_date = CURDATE()
LEFT JOIN kku_social_farm_settings settings ON u.id = settings.user_id;

-- 添加表注释说明
ALTER TABLE kku_social_farm_steal_logs COMMENT = '偷菜记录表 - 记录所有偷菜行为的详细信息';
ALTER TABLE kku_social_farm_visit_logs COMMENT = '农场访问记录表 - 记录用户访问好友农场的行为';
ALTER TABLE kku_social_farm_settings COMMENT = '社交设置表 - 存储用户的社交农场相关设置';
ALTER TABLE kku_social_farm_help_logs COMMENT = '互助记录表 - 记录用户间的互助行为';
ALTER TABLE kku_social_farm_stats COMMENT = '社交统计表 - 记录用户的社交行为统计数据';
ALTER TABLE kku_social_farm_protections COMMENT = '偷菜保护记录表 - 记录偷菜保护状态';

-- 数据表创建完成
-- 请根据实际需求调整表结构和索引策略
-- 建议在生产环境部署前进行充分的性能测试