ALTER TABLE tcm_live_course
ADD COLUMN `password` VARCHAR(255) DEFAULT NULL COMMENT '直播间密码' AFTER `status`;

CREATE TABLE tcm_provincial_agent (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '代理ID，主键，自增',
    agent_name VARCHAR(50) NOT NULL COMMENT '省区负责人姓名',
    province VARCHAR(20) NOT NULL COMMENT '负责省份名称（如：四川省）',
    bank_name VARCHAR(100) COMMENT '开户银行名称（如：中国建设银行）',
    account_number VARCHAR(30) COMMENT '银行账号',
    account_holder VARCHAR(50) COMMENT '账户名称/户名（即开户人姓名或公司名称）',
    branch_name VARCHAR(100) COMMENT '开户支行名称（如：成都高新支行）',
    contact_info VARCHAR(200) COMMENT '联系信息（可填电话、邮箱、手机号等）',
    status TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1启用，0禁用，2注销',
    address VARCHAR(255) COMMENT '详细地址（如：成都市高新区天府大道XX号）',
    `deleted` tinyint(1) NOT NULL DEFAULT '0',
  `created_by` varchar(64) NOT NULL DEFAULT '系统初始化' COMMENT '创建人',
  `updated_by` varchar(64) NOT NULL DEFAULT '系统初始化' COMMENT '更新人',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) COMMENT='省代理人员信息表，存储代理的基本资料及银行账户信息';

CREATE TABLE tcm_agent_region (
    id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID，主键，自增',
    agent_id BIGINT NOT NULL COMMENT '关联省代理人员ID，外键指向 provincial_agent.id',
    region_code CHAR(6) NOT NULL COMMENT '6位行政区划编码（如：510101 表示成都市辖区）',
    region_name VARCHAR(100) COMMENT '区域名称（冗余字段，便于直接展示，如：成都市）',
    region_level TINYINT COMMENT '区域行政级别：1-省级，2-市级，3-区县级，4-乡镇级（可根据编码前缀判断）',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
    UNIQUE KEY uk_agent_region (agent_id, region_code) COMMENT '联合唯一索引，防止同一代理重复添加同一区域'
) COMMENT='代理负责区域表，存储每个省代理所管辖的具体区域（一对多关系，一个代理可负责多个区域）';

-- 为 tcm_clinic 表新增代理关联字段
ALTER TABLE tcm_clinic
ADD COLUMN agent_id BIGINT COMMENT '负责该诊所的省代理ID，tcm_provincial_agent.id'
AFTER name;

INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, sort, status, visible, permission_key, remark, deleted) VALUES
(5046, 2100, '省代理管理', 'member_agent_manage', 'C', '/member/user/agent', 'Member/AgentList', 12, 1, 1, 'member:agent:view', '省代理管理菜单', 0),
(5047, 5046, '新增省代理', 'member_agent_add', 'B', NULL, NULL, 1, 1, 1, 'member:agent:add', '新增省代理权限', 0),
(5048, 5046, '编辑省代理', 'member_agent_edit', 'B', NULL, NULL, 2, 1, 1, 'member:agent:edit', '编辑省代理权限', 0),
(5049, 5046, '删除省代理', 'member_agent_delete', 'B', NULL, NULL, 3, 1, 1, 'member:agent:delete', '删除省代理权限', 0),
(5050, 5046, '省代理详情', 'member_agent_detail', 'B', NULL, NULL, 4, 1, 1, 'member:agent:detail', '省代理详情权限', 0);


-- 养生直播：新增设置直播间密码按钮权限
INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (3064, 3060, '设置直播间密码', 'live_course_password', 'B', NULL, NULL, NULL, 4, 1, 1, 'live:course:password', '设置直播间密码按钮', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name),
menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status),
visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = VALUES(deleted);


-- 为 tcm_clinic 表新增银行信息字段（全部加在 status 字段后面）
ALTER TABLE tcm_clinic
ADD COLUMN bank_name VARCHAR(100) COMMENT '开户银行名称（如：中国建设银行）' AFTER status,
ADD COLUMN account_number VARCHAR(30) COMMENT '银行账号' AFTER bank_name,
ADD COLUMN account_holder VARCHAR(50) COMMENT '账户名称/户名（即开户人姓名或公司名称）' AFTER account_number,
ADD COLUMN branch_name VARCHAR(100) COMMENT '开户支行名称（如：成都高新支行）' AFTER account_holder;

INSERT INTO sys_menu (id, parent_id, name, menu_code, type, sort, status, visible, permission_key, remark, deleted)
VALUES (5045, 2118, '编辑', 'member_clinic_edit', 'B', 1, 1, 1, 'member:clinic:edit', '编辑诊所按钮', 0)
ON DUPLICATE KEY UPDATE permission_key = VALUES(permission_key), deleted = 0;

-- 微赞直播间单例设置：仅保留 Banner 图片地址和直播间地址两个业务字段
CREATE TABLE IF NOT EXISTS tcm_live_room_setting (
    banner_image_url VARCHAR(500) NOT NULL COMMENT 'Banner图片地址',
    live_room_url VARCHAR(500) NULL COMMENT '直播间地址'
) COMMENT='微赞直播间设置';

INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (6000, 3050, '微赞直播间设置', 'live_room_setting', 'C', '/live/course/live-room-setting', 'Live/LiveRoomSetting', NULL, 18, 1, 1, 'live:room-setting:view', '微赞直播间设置菜单', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), path = VALUES(path), component = VALUES(component), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;

INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (6001, 6000, '编辑微赞直播间设置', 'live_room_setting_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:room-setting:edit', '微赞直播间设置编辑权限', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;

-- 项目介绍菜单和权限配置
-- 添加时间：2026-08-20
-- 说明：为项目介绍功能添加菜单和按钮权限

-- 项目介绍菜单（id: 6010）
INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (6010, 3050, '项目介绍', 'live_project_introduction_manage', 'C', '/live/course/project-introduction', 'Live/ProjectIntroductionList', NULL, 60, 1, 1, 'live:project-introduction:view', '项目介绍菜单', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), path = VALUES(path), component = VALUES(component), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;

-- 新增项目介绍按钮（id: 6011）
INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (6011, 6010, '新增项目介绍', 'live_project_introduction_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:project-introduction:add', '项目介绍新增按钮', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;

-- 编辑项目介绍按钮（id: 6012）
INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (6012, 6010, '编辑项目介绍', 'live_project_introduction_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'live:project-introduction:edit', '项目介绍编辑按钮', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;

-- 删除项目介绍按钮（id: 6013）
INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (6013, 6010, '删除项目介绍', 'live_project_introduction_delete', 'B', NULL, NULL, NULL, 3, 1, 1, 'live:project-introduction:delete', '项目介绍删除按钮', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;

-- ----------------------------
-- 项目介绍表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `tcm_project_introduction` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(128) NOT NULL COMMENT '标题',
  `cover_img` VARCHAR(512) DEFAULT NULL COMMENT '封面图',
  `category` VARCHAR(32) NOT NULL COMMENT '类别：platform(平台介绍)、mode(营销模式)、action(营销动作)',
  `content_type` TINYINT NOT NULL COMMENT '类型：1图文 2视频',
  `content` MEDIUMTEXT COMMENT '图文内容（富文本HTML）',
  `video_url` VARCHAR(512) DEFAULT NULL COMMENT '视频地址',
  `video_file_name` VARCHAR(255) DEFAULT NULL COMMENT '视频文件名',
  `video_description` VARCHAR(255) DEFAULT NULL COMMENT '视频介绍',
  `click_count` INT NOT NULL DEFAULT 0 COMMENT '点击量',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1启用 0禁用',
  `deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `created_by` VARCHAR(64) NOT NULL DEFAULT '系统' COMMENT '创建人',
  `updated_by` VARCHAR(64) NOT NULL DEFAULT '系统' COMMENT '更新人',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_category` (`category`),
  KEY `idx_content_type` (`content_type`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目介绍表';

-- ----------------------------
-- 项目介绍密码配置表
-- ----------------------------
CREATE TABLE IF NOT EXISTS `tcm_project_introduction_password` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `password_enabled` TINYINT NOT NULL DEFAULT 0 COMMENT '是否启用密码访问：1启用 0不启用',
  `password` VARCHAR(255) DEFAULT NULL COMMENT '访问密码（加密）',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目介绍密码配置表';

-- 项目介绍密码菜单（id: 6014）
INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (6014, 3050, '项目介绍密码', 'live_project_introduction_password', 'C', '/live/course/project-introduction-password', 'Live/ProjectIntroductionPassword', NULL, 61, 1, 1, 'live:project-introduction-password:view', '项目介绍密码菜单', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), path = VALUES(path), component = VALUES(component), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;

-- 编辑项目介绍密码按钮（id: 6015）
INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (6015, 6014, '编辑项目介绍密码', 'live_project_introduction_password_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:project-introduction-password:edit', '项目介绍密码编辑按钮', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;


ALTER TABLE `tcm_health_popularization`
ADD COLUMN `video_description` VARCHAR(255) DEFAULT NULL COMMENT '视频介绍'
AFTER `video_file_name`;


-- ----------------------------
-- 新增二级菜单：宣传知识（与课程管理平级，父级为课程与学习 3000）
-- 并将「项目介绍」「项目介绍密码」菜单从课程管理(3050)迁移到宣传知识下
-- 添加时间：2026-08-22
-- ----------------------------

-- 宣传知识菜单（id: 6100）
INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (6100, 3000, '宣传知识', 'live_promotion_root', 'M', '/live/promotion', NULL, NULL, 20, 1, 1, NULL, '宣传知识目录', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), path = VALUES(path), component = VALUES(component), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;

-- 将「项目介绍」(6010) 迁移到「宣传知识」(6100) 下，并将路由前缀改为 /live/promotion
UPDATE sys_menu SET parent_id = 6100, path = '/live/promotion/project-introduction' WHERE id = 6010;

-- 将「项目介绍密码」(6014) 迁移到「宣传知识」(6100) 下，并将路由前缀改为 /live/promotion
UPDATE sys_menu SET parent_id = 6100, path = '/live/promotion/project-introduction-password' WHERE id = 6014;

-- ----------------------------
-- 「健康科普」改名为「健康知识」，并从课程管理(3050)迁移到宣传知识(6100)下
-- 主菜单 id: 3120，子按钮 3121/3122/3123 随父级一起迁移（parent_id 不变，无需单独处理）
-- 添加时间：2026-08-22
-- ----------------------------
UPDATE sys_menu
SET parent_id = 6100,
    name = '健康知识',
    path = '/live/promotion/health-popularization',
    remark = '健康知识菜单'
WHERE id = 3120;

-- 子按钮随主菜单改名（parent_id 仍为 3120，不变）
UPDATE sys_menu SET name = '新增健康知识', remark = '健康知识新增按钮' WHERE id = 3121;
UPDATE sys_menu SET name = '编辑健康知识', remark = '健康知识编辑按钮' WHERE id = 3122;
UPDATE sys_menu SET name = '删除健康知识', remark = '健康知识删除按钮' WHERE id = 3123;

-- ----------------------------
-- 新增「养生科普」菜单及按钮权限（宣传知识 6100 下，与健康知识平级）
-- 养生科普与健康知识共用 tcm_health_popularization 表，contentType=2（仅视频）
-- 主菜单 id: 6110，子按钮 6111/6112/6113
-- 添加时间：2026-08-22
-- ----------------------------
INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (6110, 6100, '养生科普', 'live_health_promotion_manage', 'C', '/live/promotion/health-promotion', 'Live/HealthPromotionList', NULL, 51, 1, 1, 'live:health-promotion:view', '养生科普菜单', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), path = VALUES(path), component = VALUES(component), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;

INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (6111, 6110, '新增养生科普', 'live_health_promotion_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:health-promotion:add', '养生科普新增按钮', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;

INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (6112, 6110, '编辑养生科普', 'live_health_promotion_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'live:health-promotion:edit', '养生科普编辑按钮', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;

INSERT INTO sys_menu (id, parent_id, name, menu_code, type, path, component, icon, sort, status, visible, permission_key, remark, deleted)
VALUES (6113, 6110, '删除养生科普', 'live_health_promotion_delete', 'B', NULL, NULL, NULL, 3, 1, 1, 'live:health-promotion:delete', '养生科普删除按钮', 0)
ON DUPLICATE KEY UPDATE parent_id = VALUES(parent_id), name = VALUES(name), menu_code = VALUES(menu_code), type = VALUES(type), sort = VALUES(sort), status = VALUES(status), visible = VALUES(visible), permission_key = VALUES(permission_key), remark = VALUES(remark), deleted = 0;