enagic/代码/enagic-lilishop/enagic-plugin/DB/enagic_init.sql

-- ============================================================
-- Enagic 本希甄选小程序 - 数据库初始化脚本
-- 基于 Lilishop B2B2C V4.3
-- 执行顺序: 1. 本文件 → 2. version upgrade SQL（如有）
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ------------------------------------------------------------
-- 1. 健康布施者表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_health_advisor`;
CREATE TABLE `li_enagic_health_advisor` (
    `id`                BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `member_id`         BIGINT        NOT NULL                COMMENT '关联会员ID',
    `member_name`       VARCHAR(100)   DEFAULT NULL           COMMENT '会员名称',
    `name`              VARCHAR(50)    DEFAULT NULL           COMMENT '真实姓名',
    `id_number`         VARCHAR(20)    DEFAULT NULL           COMMENT '身份证号',
    `medal_level`       VARCHAR(20)    DEFAULT 'STAR_1'       COMMENT '勋章等级',
    `total_invites`     INT             DEFAULT 0            COMMENT '累计邀请人数',
    `total_commission`   DECIMAL(12,2)  DEFAULT 0.00         COMMENT '累计佣金总额',
    `available_commission` DECIMAL(12,2) DEFAULT 0.00         COMMENT '可用佣金',
    `frozen_commission`   DECIMAL(12,2)  DEFAULT 0.00         COMMENT '冻结佣金',
    `advisor_credits`   INT             DEFAULT 0            COMMENT '顾问积分',
    `status`            VARCHAR(20)     DEFAULT 'ACTIVE'     COMMENT '状态',
    `permanent_bind`    TINYINT(1)      DEFAULT 1            COMMENT '是否永久绑定',
    `approve_time`      DATETIME        DEFAULT NULL         COMMENT '审核时间',
    `create_time`       DATETIME        DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`       DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    `delete_flag`       TINYINT         DEFAULT 0            COMMENT '删除标记',
    PRIMARY KEY (`id`),
    KEY `idx_member_id` (`member_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='健康布施者表';

-- ------------------------------------------------------------
-- 2. 荣誉勋章表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_advisor_medal`;
CREATE TABLE `li_enagic_advisor_medal` (
    `id`              BIGINT      NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `advisor_id`      BIGINT      NOT NULL                COMMENT '健康布施者ID',
    `medal_type`      VARCHAR(20)  DEFAULT NULL           COMMENT '勋章类型',
    `medal_level`     INT          DEFAULT 1              COMMENT '勋章等级',
    `grant_reason`    VARCHAR(200) DEFAULT NULL           COMMENT '授予原因',
    `grant_time`      DATETIME     DEFAULT NULL           COMMENT '授予时间',
    `trigger_member_id` BIGINT     DEFAULT NULL           COMMENT '触发勋章的客户ID',
    `displayed`       TINYINT(1)   DEFAULT 0              COMMENT '是否已展示',
    `create_time`     DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_advisor_id` (`advisor_id`),
    KEY `idx_grant_time` (`grant_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='荣誉勋章表';

-- ------------------------------------------------------------
-- 3. VIP 配置表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_vip_config`;
CREATE TABLE `li_enagic_vip_config` (
    `id`                  BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `vip_name`            VARCHAR(50)   DEFAULT NULL           COMMENT 'VIP名称',
    `vip_image`           VARCHAR(255)  DEFAULT NULL           COMMENT 'VIP图标',
    `price`               DECIMAL(10,2)  DEFAULT 0.00         COMMENT '价格（分）',
    `upgrade_conditions` TEXT           COMMENT '升级条件JSON',
    `benefits`            TEXT           COMMENT '会员权益JSON',
    `no_expiry`           TINYINT(1)    DEFAULT 1            COMMENT '是否无到期时间',
    `gratitude_remind_days` INT         DEFAULT 30           COMMENT '感恩提醒周期（天）',
    `sort`                INT          DEFAULT 0             COMMENT '排序',
    `status`              VARCHAR(20)   DEFAULT 'OPEN'       COMMENT '状态',
    `create_time`         DATETIME      DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`          DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='VIP配置表';

INSERT INTO `li_enagic_vip_config` (`vip_name`, `price`, `no_expiry`, `status`, `benefits`, `upgrade_conditions`) VALUES
('普通会员', 0, 1, 'OPEN', '[{"type":"EXCLUSIVE_SERVICE","value":false}]', '[{"type":"INVITE_COUNT","value":0}]'),
('银牌会员', 99.00, 1, 'OPEN', '[{"type":"EXCLUSIVE_SERVICE","value":true},{"type":"DISCOUNT_RATE","value":0.95}]', '[{"type":"INVITE_COUNT","value":5}]'),
('金牌会员', 299.00, 1, 'OPEN', '[{"type":"EXCLUSIVE_SERVICE","value":true},{"type":"DISCOUNT_RATE","value":0.90},{"type":"PRIORITY_SHIPMENT","value":true}]', '[{"type":"INVITE_COUNT","value":20},{"type":"CONSUME_AMOUNT","value":5000}]'),
('钻石会员', 999.00, 1, 'OPEN', '[{"type":"EXCLUSIVE_SERVICE","value":true},{"type":"DISCOUNT_RATE","value":0.85},{"type":"PRIORITY_SHIPMENT","value":true},{"type":"EXCLUSIVE_PRODUCT","value":true}]', '[{"type":"INVITE_COUNT","value":50},{"type":"CONSUME_AMOUNT","value":20000}]');

-- ------------------------------------------------------------
-- 4. 会员VIP记录表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_member_vip`;
CREATE TABLE `li_enagic_member_vip` (
    `id`                    BIGINT      NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `member_id`             BIGINT      NOT NULL                COMMENT '会员ID',
    `member_name`           VARCHAR(100) DEFAULT NULL           COMMENT '会员名称',
    `vip_config_id`         BIGINT      DEFAULT NULL           COMMENT 'VIP配置ID',
    `vip_name`              VARCHAR(50)  DEFAULT NULL           COMMENT 'VIP名称（冗余）',
    `open_time`             DATETIME     DEFAULT NULL           COMMENT '开通时间',
    `last_gratitude_remind_time` DATETIME DEFAULT NULL        COMMENT '上次感恩提醒时间',
    `next_gratitude_remind_time` DATETIME DEFAULT NULL        COMMENT '下次感恩提醒时间',
    `total_consume_amount`   DECIMAL(12,2) DEFAULT 0.00       COMMENT '累计消费金额',
    `status`               VARCHAR(20)   DEFAULT 'ACTIVE'      COMMENT '状态',
    `create_time`          DATETIME      DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`           DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    KEY `idx_member_id` (`member_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='会员VIP记录表';

-- ------------------------------------------------------------
-- 5. 抵扣金账户表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_deduction`;
CREATE TABLE `li_enagic_deduction` (
    `id`                BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `member_id`         BIGINT        NOT NULL                COMMENT '会员ID',
    `member_name`       VARCHAR(100)   DEFAULT NULL           COMMENT '会员名称',
    `deduction_balance` DECIMAL(12,2)  DEFAULT 0.00         COMMENT '可用抵扣金余额',
    `frozen_balance`     DECIMAL(12,2)  DEFAULT 0.00         COMMENT '冻结中的抵扣金',
    `pay_password`       VARCHAR(100)   DEFAULT NULL           COMMENT '支付密码',
    `create_time`       DATETIME        DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`       DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_member_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='抵扣金账户表';

-- ------------------------------------------------------------
-- 6. 抵扣金流水表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_deduction_log`;
CREATE TABLE `li_enagic_deduction_log` (
    `id`            BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `deduction_id` BIGINT        NOT NULL                COMMENT '抵扣金账户ID',
    `member_id`     BIGINT        NOT NULL                COMMENT '会员ID',
    `amount`        DECIMAL(12,2) NOT NULL               COMMENT '变动金额',
    `before_balance` DECIMAL(12,2) DEFAULT NULL          COMMENT '变动前余额',
    `after_balance`  DECIMAL(12,2) DEFAULT NULL          COMMENT '变动后余额',
    `type`          VARCHAR(30)   NOT NULL                COMMENT '类型',
    `order_sn`      VARCHAR(50)    DEFAULT NULL           COMMENT '关联订单号',
    `remark`        VARCHAR(255)   DEFAULT NULL           COMMENT '备注',
    `create_time`   DATETIME        DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_deduction_id` (`deduction_id`),
    KEY `idx_member_id` (`member_id`),
    KEY `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='抵扣金流水表';

-- ------------------------------------------------------------
-- 7. 运维流程模板表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_operation_flow`;
CREATE TABLE `li_enagic_operation_flow` (
    `id`          BIGINT        NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `flow_name`   VARCHAR(100)  NOT NULL                COMMENT '流程名称',
    `product_id`  BIGINT         DEFAULT NULL           COMMENT '关联商品ID（null=通用）',
    `nodes`       TEXT           NOT NULL                COMMENT '流程节点JSON',
    `status`      VARCHAR(20)    DEFAULT 'OPEN'          COMMENT '状态',
    `create_time` DATETIME        DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME        DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运维流程模板表';

-- ------------------------------------------------------------
-- 8. 运维计划表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_operation_plan`;
CREATE TABLE `li_enagic_operation_plan` (
    `id`                BIGINT      NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `flow_id`           BIGINT      NOT NULL                COMMENT '运维流程ID',
    `member_id`         BIGINT      NOT NULL                COMMENT '客户ID',
    `member_name`       VARCHAR(100) DEFAULT NULL           COMMENT '客户名称',
    `product_id`        BIGINT       DEFAULT NULL           COMMENT '关联商品ID',
    `order_sn`          VARCHAR(50)  DEFAULT NULL           COMMENT '关联订单号',
    `current_node_index` INT        DEFAULT 0              COMMENT '当前节点索引',
    `current_node_name` VARCHAR(100) DEFAULT NULL          COMMENT '当前节点名称',
    `next_node_time`    DATETIME     DEFAULT NULL           COMMENT '下一个节点计划时间',
    `skip_count`        INT          DEFAULT 0              COMMENT '连续跳过次数',
    `status`            VARCHAR(20)  DEFAULT 'IN_PROGRESS'  COMMENT '状态',
    `create_time`       DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time`       DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    KEY `idx_member_id` (`member_id`),
    KEY `idx_status` (`status`),
    KEY `idx_next_node_time` (`next_node_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运维计划表';

-- ------------------------------------------------------------
-- 9. 运维记录表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_operation_record`;
CREATE TABLE `li_enagic_operation_record` (
    `id`             BIGINT      NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `plan_id`        BIGINT      NOT NULL                COMMENT '运维计划ID',
    `node_name`      VARCHAR(100) DEFAULT NULL           COMMENT '运维节点名称',
    `operator_id`    BIGINT       DEFAULT NULL           COMMENT '操作人ID',
    `operator_name`  VARCHAR(100) DEFAULT NULL           COMMENT '操作人名称',
    `content`        TEXT         DEFAULT NULL           COMMENT '操作内容',
    `result`         VARCHAR(255)  DEFAULT NULL           COMMENT '执行结果',
    `confirm_status` VARCHAR(20)  DEFAULT 'PENDING'     COMMENT '客户确认状态',
    `operate_time`   DATETIME     DEFAULT NULL           COMMENT '执行时间',
    `confirm_time`   DATETIME     DEFAULT NULL           COMMENT '确认时间',
    `create_time`    DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_plan_id` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='运维记录表';

-- ------------------------------------------------------------
-- 10. 客户移交记录表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_member_transfer`;
CREATE TABLE `li_enagic_member_transfer` (
    `id`                    BIGINT      NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `member_id`             BIGINT      NOT NULL                COMMENT '客户ID',
    `member_name`           VARCHAR(100) DEFAULT NULL           COMMENT '客户名称',
    `original_advisor_id`   BIGINT       DEFAULT NULL           COMMENT '原健康布施者ID',
    `original_advisor_name` VARCHAR(100) DEFAULT NULL           COMMENT '原健康布施者名称',
    `new_advisor_id`        BIGINT       DEFAULT NULL           COMMENT '接收方ID',
    `new_advisor_name`      VARCHAR(100) DEFAULT NULL           COMMENT '接收方名称',
    `transfer_reason`       VARCHAR(200) DEFAULT NULL           COMMENT '移交原因',
    `trigger_type`          VARCHAR(20)   DEFAULT 'AUTO'       COMMENT '触发类型',
    `status`                VARCHAR(20)   DEFAULT 'COMPLETED'  COMMENT '状态',
    `transfer_time`         DATETIME      DEFAULT NULL           COMMENT '转移时间',
    `create_time`           DATETIME      DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_member_id` (`member_id`),
    KEY `idx_trigger_type` (`trigger_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户移交记录表';

-- ------------------------------------------------------------
-- 11. 行为日志表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_behavior_log`;
CREATE TABLE `li_enagic_behavior_log` (
    `id`          BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `member_id`   BIGINT        DEFAULT NULL           COMMENT '会员ID',
    `member_name` VARCHAR(100)  DEFAULT NULL           COMMENT '会员名称',
    `event_type`  VARCHAR(50)   NOT NULL                COMMENT '事件类型',
    `event_data`  TEXT          DEFAULT NULL           COMMENT '事件数据JSON',
    `page_url`    VARCHAR(255)   DEFAULT NULL           COMMENT '页面URL',
    `device_info` VARCHAR(200)   DEFAULT NULL           COMMENT '设备信息',
    `ip`          VARCHAR(50)    DEFAULT NULL           COMMENT 'IP地址',
    `create_time` DATETIME      DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_member_id` (`member_id`),
    KEY `idx_event_type` (`event_type`),
    KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='行为日志表';

-- ------------------------------------------------------------
-- 12. 素材二维码表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_article_qr`;
CREATE TABLE `li_enagic_article_qr` (
    `id`              BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `article_id`      BIGINT       NOT NULL                COMMENT '素材ID',
    `qr_url`          VARCHAR(500)  DEFAULT NULL           COMMENT '二维码URL',
    `qr_content`      VARCHAR(500)  DEFAULT NULL           COMMENT '二维码内容',
    `bind_member_id`   BIGINT        DEFAULT NULL           COMMENT '绑定分享者ID',
    `bind_member_name` VARCHAR(100)  DEFAULT NULL           COMMENT '绑定分享者名称',
    `expire_time`     DATETIME      DEFAULT NULL           COMMENT '过期时间',
    `create_time`     DATETIME      DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_article_id` (`article_id`),
    KEY `idx_bind_member_id` (`bind_member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='素材二维码表';

-- ------------------------------------------------------------
-- 13. AI对话记录表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_ai_conversation`;
CREATE TABLE `li_enagic_ai_conversation` (
    `id`                  BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `member_id`           BIGINT        DEFAULT NULL           COMMENT '会员ID',
    `session_id`          VARCHAR(64)   DEFAULT NULL           COMMENT '会话ID',
    `question`            TEXT          NOT NULL                COMMENT '提问',
    `answer`              TEXT          NOT NULL                COMMENT '回答',
    `model`               VARCHAR(100)   DEFAULT NULL           COMMENT 'AI模型',
    `question_type`       VARCHAR(30)   DEFAULT 'OTHER'        COMMENT '问题类型',
    `has_product_recommend` TINYINT(1)  DEFAULT 0            COMMENT '是否包含产品推荐',
    `create_time`         DATETIME      DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`),
    KEY `idx_member_id` (`member_id`),
    KEY `idx_session_id` (`session_id`),
    KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI对话记录表';

-- ------------------------------------------------------------
-- 14. AI知识库表
-- ------------------------------------------------------------
DROP TABLE IF EXISTS `li_enagic_ai_knowledge`;
CREATE TABLE `li_enagic_ai_knowledge` (
    `id`          BIGINT       NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `category`   VARCHAR(30)   NOT NULL                COMMENT '分类',
    `title`       VARCHAR(200)  NOT NULL                COMMENT '标题/问题',
    `content`     TEXT          NOT NULL                COMMENT '答案内容',
    `goods_id`    BIGINT         DEFAULT NULL           COMMENT '关联商品ID',
    `goods_name`  VARCHAR(200)  DEFAULT NULL           COMMENT '关联商品名称',
    `keywords`    VARCHAR(500)  DEFAULT NULL           COMMENT '关键词',
    `status`     VARCHAR(20)   DEFAULT 'ACTIVE'        COMMENT '状态',
    `use_count`   INT            DEFAULT 0             COMMENT '使用次数',
    `create_time` DATETIME      DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `update_time` DATETIME      DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`),
    KEY `idx_category` (`category`),
    KEY `idx_goods_id` (`goods_id`),
    KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='AI知识库表';

-- ------------------------------------------------------------
-- 扩展原有 Lilishop 表
-- ------------------------------------------------------------
-- 会员表扩展
ALTER TABLE `li_member`
    ADD COLUMN `health_advisor_id` BIGINT DEFAULT NULL COMMENT '关联健康布施者ID' AFTER `store_id`,
    ADD COLUMN `advisor_credits` INT DEFAULT 0 COMMENT '顾问积分' AFTER `health_advisor_id`,
    ADD INDEX `idx_health_advisor_id` (`health_advisor_id`);

-- 会员等级表扩展
ALTER TABLE `li_member_grade`
    ADD COLUMN `upgrade_conditions` TEXT DEFAULT NULL COMMENT '升级条件JSON' AFTER `description`;

-- 素材/文章表扩展
ALTER TABLE `li_article`
    ADD COLUMN `view_role` VARCHAR(20) DEFAULT 'ALL' COMMENT '可见角色：ALL/CONSUMER_ONLY' AFTER `status`;

-- 分销表扩展
ALTER TABLE `li_distribution`
    ADD COLUMN `medal_level` VARCHAR(20) DEFAULT NULL COMMENT '勋章等级' AFTER `distribution_status`,
    ADD COLUMN `permanent_bind` TINYINT(1) DEFAULT 1 COMMENT '是否永久绑定' AFTER `medal_level`;

-- 商品表扩展（关联运维流程）
ALTER TABLE `li_goods`
    ADD COLUMN `operation_flow_id` BIGINT DEFAULT NULL COMMENT '关联运维流程ID' AFTER `goods_type`,
    ADD INDEX `idx_operation_flow_id` (`operation_flow_id`);

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- 初始化完成
-- ============================================================