-- =====================================================
-- 微信小程序会员绑定系统数据库表结构
-- 创建日期: 2025-09-30
-- 版本: v1.0
-- 描述: 包含会员信息、绑定记录、权益配置等核心表结构
-- =====================================================

-- 设置字符集
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================
-- 1. 会员信息表 (member_info)
-- 用途: 存储用户会员身份信息和状态
-- =====================================================
DROP TABLE IF EXISTS `member_info`;
CREATE TABLE `member_info` (
  `member_id` varchar(64) NOT NULL COMMENT '会员ID，主键，格式：MB + 时间戳 + 随机数',
  `openid` varchar(128) NOT NULL COMMENT '微信用户openid，唯一标识',
  `union_id` varchar(128) DEFAULT NULL COMMENT '微信用户unionid，跨应用唯一标识',
  `member_level` varchar(16) NOT NULL DEFAULT 'FREE' COMMENT '会员等级：FREE-免费用户，VIP-基础会员，SVIP-高级会员',
  `member_status` varchar(16) NOT NULL DEFAULT 'ACTIVE' COMMENT '会员状态：ACTIVE-有效，EXPIRED-过期，SUSPENDED-暂停，CANCELLED-取消',
  `start_time` datetime DEFAULT NULL COMMENT '会员开始时间',
  `end_time` datetime DEFAULT NULL COMMENT '会员结束时间，NULL表示永久有效',
  `purchase_order_id` varchar(64) DEFAULT NULL COMMENT '购买订单ID，关联支付订单',
  `auto_renew` tinyint DEFAULT 0 COMMENT '是否自动续费：0-否，1-是',
  `renew_count` int DEFAULT 0 COMMENT '续费次数统计',
  `total_amount` decimal(10,2) DEFAULT 0.00 COMMENT '累计消费金额',
  `points` int DEFAULT 0 COMMENT '会员积分',
  `invite_code` varchar(32) DEFAULT NULL COMMENT '邀请码',
  `invited_by` varchar(64) DEFAULT NULL COMMENT '邀请人会员ID',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` tinyint DEFAULT 0 COMMENT '是否删除：0-未删除，1-已删除',
  PRIMARY KEY (`member_id`),
  UNIQUE KEY `uk_openid` (`openid`),
  UNIQUE KEY `uk_invite_code` (`invite_code`),
  KEY `idx_member_level` (`member_level`),
  KEY `idx_member_status` (`member_status`),
  KEY `idx_end_time` (`end_time`),
  KEY `idx_invited_by` (`invited_by`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员信息表';

-- =====================================================
-- 2. 会员绑定记录表 (member_bind_log)
-- 用途: 记录会员绑定、升级、降级等操作历史
-- =====================================================
DROP TABLE IF EXISTS `member_bind_log`;
CREATE TABLE `member_bind_log` (
  `log_id` bigint NOT NULL AUTO_INCREMENT COMMENT '日志ID，主键',
  `member_id` varchar(64) NOT NULL COMMENT '会员ID',
  `openid` varchar(128) NOT NULL COMMENT '微信用户openid',
  `bind_type` varchar(16) NOT NULL COMMENT '绑定类型：BIND-首次绑定，UNBIND-解绑，UPGRADE-升级，DOWNGRADE-降级，RENEW-续费',
  `old_level` varchar(16) DEFAULT NULL COMMENT '原会员等级',
  `new_level` varchar(16) NOT NULL COMMENT '新会员等级',
  `old_end_time` datetime DEFAULT NULL COMMENT '原到期时间',
  `new_end_time` datetime DEFAULT NULL COMMENT '新到期时间',
  `bind_source` varchar(32) DEFAULT NULL COMMENT '绑定来源：PURCHASE-购买，GIFT-赠送，ADMIN-管理员操作，INVITE-邀请奖励',
  `related_order_id` varchar(64) DEFAULT NULL COMMENT '关联订单ID',
  `amount` decimal(10,2) DEFAULT NULL COMMENT '相关金额',
  `duration_days` int DEFAULT NULL COMMENT '增加天数',
  `operator_id` varchar(64) DEFAULT NULL COMMENT '操作员ID（管理员操作时使用）',
  `bind_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '绑定时间',
  `client_ip` varchar(45) DEFAULT NULL COMMENT '客户端IP地址',
  `user_agent` varchar(500) DEFAULT NULL COMMENT '用户代理信息',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注信息',
  PRIMARY KEY (`log_id`),
  KEY `idx_member_id` (`member_id`),
  KEY `idx_openid` (`openid`),
  KEY `idx_bind_type` (`bind_type`),
  KEY `idx_bind_time` (`bind_time`),
  KEY `idx_related_order_id` (`related_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员绑定记录表';

-- =====================================================
-- 3. 会员权益配置表 (member_benefit_config)
-- 用途: 配置不同等级会员的权益内容
-- =====================================================
DROP TABLE IF EXISTS `member_benefit_config`;
CREATE TABLE `member_benefit_config` (
  `config_id` int NOT NULL AUTO_INCREMENT COMMENT '配置ID，主键',
  `member_level` varchar(16) NOT NULL COMMENT '会员等级：FREE/VIP/SVIP',
  `benefit_code` varchar(32) NOT NULL COMMENT '权益代码，唯一标识',
  `benefit_name` varchar(100) NOT NULL COMMENT '权益名称',
  `benefit_desc` varchar(500) DEFAULT NULL COMMENT '权益描述',
  `benefit_type` varchar(16) NOT NULL DEFAULT 'FEATURE' COMMENT '权益类型：FEATURE-功能权益，QUOTA-配额权益，SERVICE-服务权益',
  `benefit_value` varchar(100) DEFAULT NULL COMMENT '权益值：unlimited-无限制，数字-具体数量',
  `daily_limit` int DEFAULT NULL COMMENT '每日限制数量，NULL表示无限制',
  `monthly_limit` int DEFAULT NULL COMMENT '每月限制数量，NULL表示无限制',
  `is_enabled` tinyint DEFAULT 1 COMMENT '是否启用：0-禁用，1-启用',
  `sort_order` int DEFAULT 0 COMMENT '排序顺序，数字越小越靠前',
  `icon_url` varchar(255) DEFAULT NULL COMMENT '权益图标URL',
  `detail_url` varchar(255) DEFAULT NULL COMMENT '权益详情页URL',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`config_id`),
  UNIQUE KEY `uk_level_code` (`member_level`, `benefit_code`),
  KEY `idx_member_level` (`member_level`),
  KEY `idx_benefit_code` (`benefit_code`),
  KEY `idx_benefit_type` (`benefit_type`),
  KEY `idx_sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员权益配置表';

-- =====================================================
-- 4. 会员权益使用记录表 (member_benefit_usage)
-- 用途: 记录会员权益的使用情况和统计
-- =====================================================
DROP TABLE IF EXISTS `member_benefit_usage`;
CREATE TABLE `member_benefit_usage` (
  `usage_id` bigint NOT NULL AUTO_INCREMENT COMMENT '使用记录ID，主键',
  `member_id` varchar(64) NOT NULL COMMENT '会员ID',
  `openid` varchar(128) NOT NULL COMMENT '微信用户openid',
  `benefit_code` varchar(32) NOT NULL COMMENT '权益代码',
  `usage_date` date NOT NULL COMMENT '使用日期',
  `usage_count` int DEFAULT 1 COMMENT '使用次数',
  `daily_total` int DEFAULT 0 COMMENT '当日累计使用次数',
  `monthly_total` int DEFAULT 0 COMMENT '当月累计使用次数',
  `request_id` varchar(64) DEFAULT NULL COMMENT '请求ID，用于追踪具体请求',
  `usage_detail` json DEFAULT NULL COMMENT '使用详情，JSON格式存储',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`usage_id`),
  UNIQUE KEY `uk_member_benefit_date` (`member_id`, `benefit_code`, `usage_date`),
  KEY `idx_openid` (`openid`),
  KEY `idx_benefit_code` (`benefit_code`),
  KEY `idx_usage_date` (`usage_date`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员权益使用记录表';

-- =====================================================
-- 5. 会员等级配置表 (member_level_config)
-- 用途: 配置会员等级的基本信息和规则
-- =====================================================
DROP TABLE IF EXISTS `member_level_config`;
CREATE TABLE `member_level_config` (
  `level_id` int NOT NULL AUTO_INCREMENT COMMENT '等级ID，主键',
  `level_code` varchar(16) NOT NULL COMMENT '等级代码：FREE/VIP/SVIP',
  `level_name` varchar(50) NOT NULL COMMENT '等级名称',
  `level_desc` varchar(200) DEFAULT NULL COMMENT '等级描述',
  `level_order` int NOT NULL DEFAULT 0 COMMENT '等级顺序，数字越大等级越高',
  `price_monthly` decimal(10,2) DEFAULT NULL COMMENT '月费价格',
  `price_yearly` decimal(10,2) DEFAULT NULL COMMENT '年费价格',
  `discount_yearly` decimal(5,2) DEFAULT NULL COMMENT '年费折扣率（0-1）',
  `max_devices` int DEFAULT 1 COMMENT '最大设备数量',
  `support_level` varchar(16) DEFAULT 'BASIC' COMMENT '支持等级：BASIC-基础，PRIORITY-优先，EXCLUSIVE-专属',
  `badge_color` varchar(7) DEFAULT '#999999' COMMENT '徽章颜色，十六进制色值',
  `badge_icon` varchar(255) DEFAULT NULL COMMENT '徽章图标URL',
  `is_enabled` tinyint DEFAULT 1 COMMENT '是否启用：0-禁用，1-启用',
  `create_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`level_id`),
  UNIQUE KEY `uk_level_code` (`level_code`),
  KEY `idx_level_order` (`level_order`),
  KEY `idx_is_enabled` (`is_enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员等级配置表';

-- =====================================================
-- 6. 会员邀请记录表 (member_invite_log)
-- 用途: 记录会员邀请关系和奖励发放
-- =====================================================
DROP TABLE IF EXISTS `member_invite_log`;
CREATE TABLE `member_invite_log` (
  `invite_id` bigint NOT NULL AUTO_INCREMENT COMMENT '邀请记录ID，主键',
  `inviter_id` varchar(64) NOT NULL COMMENT '邀请人会员ID',
  `inviter_openid` varchar(128) NOT NULL COMMENT '邀请人openid',
  `invitee_id` varchar(64) DEFAULT NULL COMMENT '被邀请人会员ID',
  `invitee_openid` varchar(128) NOT NULL COMMENT '被邀请人openid',
  `invite_code` varchar(32) NOT NULL COMMENT '邀请码',
  `invite_status` varchar(16) DEFAULT 'PENDING' COMMENT '邀请状态：PENDING-待接受，ACCEPTED-已接受，REWARDED-已奖励',
  `reward_type` varchar(16) DEFAULT NULL COMMENT '奖励类型：POINTS-积分，DAYS-天数，LEVEL-等级',
  `reward_value` varchar(50) DEFAULT NULL COMMENT '奖励值',
  `inviter_reward` varchar(50) DEFAULT NULL COMMENT '邀请人奖励',
  `invitee_reward` varchar(50) DEFAULT NULL COMMENT '被邀请人奖励',
  `invite_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '邀请时间',
  `accept_time` datetime DEFAULT NULL COMMENT '接受时间',
  `reward_time` datetime DEFAULT NULL COMMENT '奖励发放时间',
  `expire_time` datetime DEFAULT NULL COMMENT '邀请过期时间',
  `remark` varchar(500) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`invite_id`),
  UNIQUE KEY `uk_invite_code` (`invite_code`),
  KEY `idx_inviter_id` (`inviter_id`),
  KEY `idx_invitee_id` (`invitee_id`),
  KEY `idx_invite_status` (`invite_status`),
  KEY `idx_invite_time` (`invite_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员邀请记录表';

-- =====================================================
-- 初始化数据
-- =====================================================

-- 插入会员等级配置数据
INSERT INTO `member_level_config` (`level_code`, `level_name`, `level_desc`, `level_order`, `price_monthly`, `price_yearly`, `discount_yearly`, `max_devices`, `support_level`, `badge_color`, `is_enabled`) VALUES
('FREE', '免费用户', '基础功能使用，每日有限制', 0, NULL, NULL, NULL, 1, 'BASIC', '#999999', 1),
('VIP', 'VIP会员', '高级功能无限制使用，优先客服支持', 1, 29.90, 299.00, 0.16, 3, 'PRIORITY', '#FFD700', 1),
('SVIP', 'SVIP会员', '全功能无限制，专属客服，API访问', 2, 99.90, 999.00, 0.17, 10, 'EXCLUSIVE', '#FF6B35', 1);

-- 插入会员权益配置数据
INSERT INTO `member_benefit_config` (`member_level`, `benefit_code`, `benefit_name`, `benefit_desc`, `benefit_type`, `benefit_value`, `daily_limit`, `monthly_limit`, `sort_order`) VALUES
-- 免费用户权益
('FREE', 'basic_chat', '基础对话', '每日50次AI对话', 'QUOTA', '50', 50, 1500, 1),
('FREE', 'basic_voice', '基础语音', '每日20次语音识别', 'QUOTA', '20', 20, 600, 2),
('FREE', 'basic_image', '基础图像', '每日5次图像识别', 'QUOTA', '5', 5, 150, 3),
('FREE', 'community_support', '社区支持', '社区问答支持', 'SERVICE', 'enabled', NULL, NULL, 4),

-- VIP会员权益
('VIP', 'advanced_chat', '高级对话', '无限制AI对话功能', 'QUOTA', 'unlimited', NULL, NULL, 1),
('VIP', 'advanced_voice', '高级语音', '无限制语音识别和合成', 'QUOTA', 'unlimited', NULL, NULL, 2),
('VIP', 'advanced_image', '高级图像', '每日100次图像处理', 'QUOTA', '100', 100, 3000, 3),
('VIP', 'priority_support', '优先支持', '客服优先响应，24小时内回复', 'SERVICE', 'enabled', NULL, NULL, 4),
('VIP', 'custom_model', '自定义模型', '使用自定义AI模型', 'FEATURE', 'enabled', NULL, NULL, 5),
('VIP', 'export_data', '数据导出', '对话记录导出功能', 'FEATURE', 'enabled', NULL, NULL, 6),
('VIP', 'ad_free', '无广告', '享受无广告体验', 'FEATURE', 'enabled', NULL, NULL, 7),

-- SVIP会员权益
('SVIP', 'premium_chat', '专属对话', '专属AI模型，无限制对话', 'QUOTA', 'unlimited', NULL, NULL, 1),
('SVIP', 'premium_voice', '专属语音', '高质量语音合成，无限制', 'QUOTA', 'unlimited', NULL, NULL, 2),
('SVIP', 'premium_image', '专属图像', '无限制图像处理和生成', 'QUOTA', 'unlimited', NULL, NULL, 3),
('SVIP', 'exclusive_support', '专属服务', '一对一专属客服，1小时内响应', 'SERVICE', 'enabled', NULL, NULL, 4),
('SVIP', 'api_access', 'API访问', '开放API接口调用权限', 'FEATURE', 'enabled', NULL, NULL, 5),
('SVIP', 'advanced_export', '高级导出', '全量数据导出和分析', 'FEATURE', 'enabled', NULL, NULL, 6),
('SVIP', 'beta_features', '内测功能', '优先体验新功能', 'FEATURE', 'enabled', NULL, NULL, 7),
('SVIP', 'custom_training', '模型训练', '个人专属模型训练', 'FEATURE', 'enabled', NULL, NULL, 8),
('SVIP', 'white_label', '白标服务', '定制化品牌服务', 'SERVICE', 'enabled', NULL, NULL, 9);

-- =====================================================
-- 创建索引优化查询性能
-- =====================================================

-- 会员信息表复合索引
CREATE INDEX `idx_member_level_status` ON `member_info` (`member_level`, `member_status`);
CREATE INDEX `idx_end_time_status` ON `member_info` (`end_time`, `member_status`);

-- 绑定记录表复合索引
CREATE INDEX `idx_member_bind_time` ON `member_bind_log` (`member_id`, `bind_time`);
CREATE INDEX `idx_openid_bind_time` ON `member_bind_log` (`openid`, `bind_time`);

-- 权益使用记录表复合索引
CREATE INDEX `idx_member_benefit_month` ON `member_benefit_usage` (`member_id`, `benefit_code`, `usage_date`);

-- =====================================================
-- 创建视图简化查询
-- =====================================================

-- 会员信息视图（包含等级配置）
CREATE VIEW `v_member_info` AS
SELECT 
    mi.member_id,
    mi.openid,
    mi.member_level,
    mi.member_status,
    mi.start_time,
    mi.end_time,
    mi.auto_renew,
    mi.points,
    mi.total_amount,
    mi.create_time,
    mlc.level_name,
    mlc.level_desc,
    mlc.badge_color,
    mlc.support_level,
    CASE 
        WHEN mi.end_time IS NULL THEN 1
        WHEN mi.end_time > NOW() THEN 1
        ELSE 0
    END AS is_valid,
    CASE 
        WHEN mi.end_time IS NULL THEN NULL
        ELSE DATEDIFF(mi.end_time, NOW())
    END AS remaining_days
FROM member_info mi
LEFT JOIN member_level_config mlc ON mi.member_level = mlc.level_code
WHERE mi.deleted = 0;

-- 权益统计视图
CREATE VIEW `v_benefit_stats` AS
SELECT 
    mbu.member_id,
    mbu.benefit_code,
    mbu.usage_date,
    mbu.daily_total,
    mbu.monthly_total,
    mbc.benefit_name,
    mbc.daily_limit,
    mbc.monthly_limit,
    CASE 
        WHEN mbc.daily_limit IS NULL THEN 1
        WHEN mbu.daily_total < mbc.daily_limit THEN 1
        ELSE 0
    END AS daily_available,
    CASE 
        WHEN mbc.monthly_limit IS NULL THEN 1
        WHEN mbu.monthly_total < mbc.monthly_limit THEN 1
        ELSE 0
    END AS monthly_available
FROM member_benefit_usage mbu
LEFT JOIN member_benefit_config mbc ON mbu.benefit_code = mbc.benefit_code;

-- =====================================================
-- 存储过程
-- =====================================================

DELIMITER $$

-- 检查会员权益存储过程
CREATE PROCEDURE `sp_check_member_benefit`(
    IN p_openid VARCHAR(128),
    IN p_benefit_code VARCHAR(32),
    OUT p_has_permission TINYINT,
    OUT p_remaining_count INT,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE v_member_level VARCHAR(16);
    DECLARE v_member_status VARCHAR(16);
    DECLARE v_end_time DATETIME;
    DECLARE v_daily_limit INT;
    DECLARE v_daily_used INT DEFAULT 0;
    DECLARE v_benefit_enabled TINYINT DEFAULT 0;
    
    -- 初始化返回值
    SET p_has_permission = 0;
    SET p_remaining_count = 0;
    SET p_message = '';
    
    -- 获取会员信息
    SELECT member_level, member_status, end_time 
    INTO v_member_level, v_member_status, v_end_time
    FROM member_info 
    WHERE openid = p_openid AND deleted = 0;
    
    -- 检查会员是否存在
    IF v_member_level IS NULL THEN
        SET p_message = '用户不存在';
        LEAVE sp_check_member_benefit;
    END IF;
    
    -- 检查会员状态
    IF v_member_status != 'ACTIVE' THEN
        SET p_message = '会员状态异常';
        LEAVE sp_check_member_benefit;
    END IF;
    
    -- 检查会员是否过期
    IF v_end_time IS NOT NULL AND v_end_time < NOW() THEN
        SET p_message = '会员已过期';
        LEAVE sp_check_member_benefit;
    END IF;
    
    -- 检查权益配置
    SELECT daily_limit, is_enabled
    INTO v_daily_limit, v_benefit_enabled
    FROM member_benefit_config
    WHERE member_level = v_member_level AND benefit_code = p_benefit_code;
    
    -- 检查权益是否存在且启用
    IF v_benefit_enabled != 1 THEN
        SET p_message = '权益不存在或未启用';
        LEAVE sp_check_member_benefit;
    END IF;
    
    -- 检查每日使用限制
    IF v_daily_limit IS NOT NULL THEN
        SELECT IFNULL(daily_total, 0)
        INTO v_daily_used
        FROM member_benefit_usage
        WHERE member_id = (SELECT member_id FROM member_info WHERE openid = p_openid)
          AND benefit_code = p_benefit_code
          AND usage_date = CURDATE();
        
        IF v_daily_used >= v_daily_limit THEN
            SET p_message = '今日使用次数已达上限';
            LEAVE sp_check_member_benefit;
        END IF;
        
        SET p_remaining_count = v_daily_limit - v_daily_used;
    ELSE
        SET p_remaining_count = -1; -- 无限制
    END IF;
    
    -- 权益验证通过
    SET p_has_permission = 1;
    SET p_message = '验证通过';
    
END$$

DELIMITER ;

-- =====================================================
-- 触发器
-- =====================================================

DELIMITER $$

-- 会员信息更新触发器
CREATE TRIGGER `tr_member_info_update` 
BEFORE UPDATE ON `member_info`
FOR EACH ROW
BEGIN
    -- 自动更新修改时间
    SET NEW.update_time = NOW();
    
    -- 检查会员过期状态
    IF NEW.end_time IS NOT NULL AND NEW.end_time < NOW() AND NEW.member_status = 'ACTIVE' THEN
        SET NEW.member_status = 'EXPIRED';
    END IF;
END$$

DELIMITER ;

-- =====================================================
-- 设置外键约束
-- =====================================================
SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================
-- 数据库表结构创建完成
-- =====================================================

-- 显示创建的表
SHOW TABLES LIKE 'member_%';

-- 显示表结构统计
SELECT 
    TABLE_NAME as '表名',
    TABLE_COMMENT as '表注释',
    TABLE_ROWS as '预估行数',
    ROUND(DATA_LENGTH/1024/1024, 2) as '数据大小(MB)'
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = DATABASE() 
  AND TABLE_NAME LIKE 'member_%'
ORDER BY TABLE_NAME;