SET @db_name = DATABASE();

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sys_user' AND COLUMN_NAME = 'real_name'
  ),
  'SELECT 1',
  'ALTER TABLE `sys_user` ADD COLUMN `real_name` VARCHAR(64) NULL COMMENT ''人员名称'' AFTER `nickname`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sys_user' AND COLUMN_NAME = 'phone'
  ),
  'SELECT 1',
  'ALTER TABLE `sys_user` ADD COLUMN `phone` VARCHAR(20) NULL COMMENT ''联系电话'' AFTER `real_name`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sys_user' AND COLUMN_NAME = 'remark'
  ),
  'SELECT 1',
  'ALTER TABLE `sys_user` ADD COLUMN `remark` VARCHAR(255) NULL COMMENT ''备注'' AFTER `phone`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sys_user' AND COLUMN_NAME = 'deleted'
  ),
  'SELECT 1',
  'ALTER TABLE `sys_user` ADD COLUMN `deleted` TINYINT(1) NOT NULL DEFAULT 0 AFTER `status`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sys_user' AND COLUMN_NAME = 'last_login_at'
  ),
  'SELECT 1',
  'ALTER TABLE `sys_user` ADD COLUMN `last_login_at` DATETIME NULL COMMENT ''最后登录时间'' AFTER `deleted`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sys_user' AND COLUMN_NAME = 'created_by'
  ),
  'SELECT 1',
  'ALTER TABLE `sys_user` ADD COLUMN `created_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''创建人'' AFTER `last_login_at`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sys_user' AND COLUMN_NAME = 'updated_by'
  ),
  'SELECT 1',
  'ALTER TABLE `sys_user` ADD COLUMN `updated_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''更新人'' AFTER `created_by`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sys_user' AND INDEX_NAME = 'uk_sys_user_phone'
  ),
  'SELECT 1',
  'CREATE UNIQUE INDEX `uk_sys_user_phone` ON `sys_user` (`phone`)'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `sys_user`
SET `created_by` = '系统初始化'
WHERE `created_by` IS NULL OR TRIM(`created_by`) = '';

UPDATE `sys_user`
SET `updated_by` = '系统初始化'
WHERE `updated_by` IS NULL OR TRIM(`updated_by`) = '';

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sys_user' AND INDEX_NAME = 'idx_sys_user_status'
  ),
  'SELECT 1',
  'CREATE INDEX `idx_sys_user_status` ON `sys_user` (`status`)'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS `sys_role` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `role_name` VARCHAR(64) NOT NULL COMMENT '角色名称',
  `role_code` VARCHAR(64) NOT NULL COMMENT '角色编码',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1启用 0禁用',
  `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
  `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`),
  UNIQUE KEY `uk_sys_role_name` (`role_name`),
  UNIQUE KEY `uk_sys_role_code` (`role_code`),
  KEY `idx_sys_role_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后台角色表';

CREATE TABLE IF NOT EXISTS `sys_menu` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `parent_id` BIGINT NOT NULL DEFAULT 0 COMMENT '父级ID，0表示根节点',
  `name` VARCHAR(64) NOT NULL COMMENT '权限/菜单名称',
  `menu_code` VARCHAR(64) NOT NULL COMMENT '菜单编码',
  `type` CHAR(1) NOT NULL COMMENT '类型：M模块 C菜单 B按钮',
  `path` VARCHAR(128) DEFAULT NULL COMMENT '路由路径',
  `component` VARCHAR(128) DEFAULT NULL COMMENT '前端组件',
  `icon` VARCHAR(64) DEFAULT NULL COMMENT '图标',
  `sort` INT NOT NULL DEFAULT 0 COMMENT '排序值',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '状态：1启用 0禁用',
  `visible` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否显示：1显示 0隐藏',
  `permission_key` VARCHAR(128) DEFAULT NULL COMMENT '权限标识',
  `remark` VARCHAR(255) DEFAULT NULL COMMENT '备注',
  `deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sys_menu_code` (`menu_code`),
  KEY `idx_sys_menu_parent_id` (`parent_id`),
  KEY `idx_sys_menu_type` (`type`),
  KEY `idx_sys_menu_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='后台菜单与权限表';

CREATE TABLE IF NOT EXISTS `sys_user_role` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT NOT NULL COMMENT '帐号ID',
  `role_id` BIGINT NOT NULL COMMENT '角色ID',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sys_user_role` (`user_id`, `role_id`),
  KEY `idx_sys_user_role_role_id` (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='帐号角色关联表';

CREATE TABLE IF NOT EXISTS `sys_role_menu` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `role_id` BIGINT NOT NULL COMMENT '角色ID',
  `menu_id` BIGINT NOT NULL COMMENT '菜单/权限ID',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sys_role_menu` (`role_id`, `menu_id`),
  KEY `idx_sys_role_menu_menu_id` (`menu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色菜单权限关联表';

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sys_role' AND COLUMN_NAME = 'created_by'
  ),
  'SELECT 1',
  'ALTER TABLE `sys_role` ADD COLUMN `created_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''创建人'' AFTER `deleted`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sys_role' AND COLUMN_NAME = 'updated_by'
  ),
  'SELECT 1',
  'ALTER TABLE `sys_role` ADD COLUMN `updated_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''更新人'' AFTER `created_by`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `sys_role`
SET `created_by` = '系统初始化'
WHERE `created_by` IS NULL OR TRIM(`created_by`) = '';

UPDATE `sys_role`
SET `updated_by` = '系统初始化'
WHERE `updated_by` IS NULL OR TRIM(`updated_by`) = '';

INSERT IGNORE INTO `sys_role` (`id`, `role_name`, `role_code`, `status`, `remark`, `deleted`, `created_by`, `updated_by`)
VALUES (1, '超级管理员', 'SUPER_ADMIN', 1, '默认拥有全部后台权限', 0, '系统初始化', '系统初始化');

INSERT IGNORE INTO `sys_menu`
(`id`, `parent_id`, `name`, `menu_code`, `type`, `path`, `component`, `icon`, `sort`, `status`, `visible`, `permission_key`, `remark`, `deleted`)
VALUES
(1000, 0, '系统与安全', 'system_security', 'M', '/system', NULL, 'setting', 100, 1, 1, NULL, '系统与安全模块', 0),
(1100, 1000, '帐号管理', 'system_account', 'C', '/system/account', 'Account/UserList', NULL, 10, 1, 1, 'system:account:view', '帐号管理菜单', 0),
(1101, 1100, '新增帐号', 'system_account_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:account:add', '帐号新增按钮', 0),
(1102, 1100, '编辑帐号', 'system_account_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'system:account:edit', '帐号编辑按钮', 0),
(1103, 1100, '启用禁用', 'system_account_status', 'B', NULL, NULL, NULL, 3, 1, 1, 'system:account:status', '帐号状态按钮', 0),
(1104, 1100, '重置密码', 'system_account_reset_password', 'B', NULL, NULL, NULL, 4, 1, 1, 'system:account:reset-password', '重置密码按钮', 0),
(1105, 1100, '删除帐号', 'system_account_delete', 'B', NULL, NULL, NULL, 5, 1, 1, 'system:account:delete', '删除帐号按钮', 0),
(1200, 1000, '角色管理', 'system_role', 'C', '/system/role', 'Account/RoleList', NULL, 20, 1, 1, 'system:role:view', '角色管理菜单', 0),
(1201, 1200, '角色详情', 'system_role_detail', 'B', NULL, NULL, NULL, 1, 1, 1, 'system:role:detail', '角色详情按钮', 0),
(1202, 1200, '新增角色', 'system_role_add', 'B', NULL, NULL, NULL, 2, 1, 1, 'system:role:add', '角色新增按钮', 0),
(1203, 1200, '编辑角色', 'system_role_edit', 'B', NULL, NULL, NULL, 3, 1, 1, 'system:role:edit', '角色编辑按钮', 0),
(1204, 1200, '删除角色', 'system_role_delete', 'B', NULL, NULL, NULL, 4, 1, 1, 'system:role:delete', '角色删除按钮', 0),
(2000, 0, '用户与会员', 'member_root', 'M', '/member', NULL, 'team', 200, 1, 1, NULL, '用户与会员模块', 0),
(2100, 2000, '用户', 'member_user_group', 'M', '/member/user', NULL, NULL, 10, 1, 1, NULL, '用户分组菜单', 0),
(2110, 2100, '用户管理', 'member_user_manage', 'C', '/member/user/manage', 'Member/UserList', NULL, 10, 1, 1, 'member:user:view', '用户管理菜单', 0),
(2118, 2100, '诊所管理', 'member_clinic_manage', 'C', '/member/user/clinic', 'Member/ClinicList', NULL, 11, 1, 1, 'member:clinic:view', '诊所管理菜单', 0),
(2119, 2100, '会员用户', 'member_only_user_manage', 'C', '/member/user/member-users', 'Member/MemberUserList', NULL, 12, 1, 1, 'member:user:member:view', '会员用户菜单', 0),
(2200, 2000, '会员', 'member_vip_group', 'M', '/member/vip', NULL, NULL, 20, 1, 1, NULL, '会员分组菜单', 0),
(2210, 2200, '会员价格设置', 'member_vip_price_settings', 'C', '/member/vip/price', 'Member/VipPriceSettings', NULL, 10, 1, 1, 'member:vip-price:view', '会员价格设置菜单', 0),
(2211, 2210, '编辑会员价格设置', 'member_vip_price_settings_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'member:vip-price:edit', '会员价格设置编辑按钮', 0),
(2220, 2200, '会员介绍设置', 'member_vip_intro_settings', 'C', '/member/vip/intro', 'Member/VipIntroSettings', NULL, 20, 1, 1, 'member:vip-intro:view', '会员介绍设置菜单', 0),
(2221, 2220, '编辑会员介绍设置', 'member_vip_intro_settings_edit', 'B', NULL, NULL, NULL, 1, 1, 1, 'member:vip-intro:edit', '会员介绍设置编辑按钮', 0),
(2111, 2110, '用户详情', 'member_user_detail', 'B', NULL, NULL, NULL, 1, 1, 1, 'member:user:detail', '用户详情按钮', 0),
(2112, 2110, '编辑用户', 'member_user_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'member:user:edit', '编辑用户按钮', 0),
(2113, 2110, '启用禁用用户', 'member_user_status', 'B', NULL, NULL, NULL, 3, 1, 1, 'member:user:status', '启用禁用用户按钮', 0),
(2114, 2110, '重置用户密码', 'member_user_reset_password', 'B', NULL, NULL, NULL, 4, 1, 1, 'member:user:reset-password', '重置用户密码按钮', 0),
(2115, 2110, '删除用户', 'member_user_delete', 'B', NULL, NULL, NULL, 5, 1, 1, 'member:user:delete', '删除用户按钮', 0),
(2116, 2110, '导出用户', 'member_user_export', 'B', NULL, NULL, NULL, 6, 1, 1, 'member:user:export', '导出用户按钮', 0),
(2117, 2110, '解绑微信', 'member_user_unbind_wechat', 'B', NULL, NULL, NULL, 7, 1, 1, 'member:user:unbind-wechat', '解绑微信按钮', 0),
(3000, 0, '课程与学习', 'live_root', 'M', '/live', NULL, 'read', 300, 1, 1, NULL, '课程与学习模块', 0),
(3050, 3000, '课程管理', 'live_course_manage_root', 'M', '/live/course', NULL, NULL, 10, 1, 1, NULL, '课程管理目录', 0),
(3060, 3050, '养生直播', 'live_course_broadcast_manage', 'C', '/live/course/broadcast', 'Live/BroadcastList', NULL, 10, 1, 1, 'live:course:view', '养生直播菜单', 0),
(3061, 3060, '直播详情', 'live_course_detail', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:course:detail', '养生直播详情按钮', 0),
(3062, 3060, '编辑直播', 'live_course_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'live:course:edit', '养生直播编辑按钮', 0),
(3063, 3060, '启用禁用直播', 'live_course_status', 'B', NULL, NULL, NULL, 3, 1, 1, 'live:course:status', '养生直播状态按钮', 0),
(3070, 3060, '场次管理', 'live_session_manage', 'B', NULL, NULL, NULL, 10, 1, 1, 'live:session:detail', '直播场次管理权限', 0),
(3071, 3060, '新增场次', 'live_session_add', 'B', NULL, NULL, NULL, 11, 1, 1, 'live:session:add', '直播场次新增按钮', 0),
(3072, 3060, '编辑场次', 'live_session_edit', 'B', NULL, NULL, NULL, 12, 1, 1, 'live:session:edit', '直播场次编辑按钮', 0),
(3073, 3060, '开播场次', 'live_session_start', 'B', NULL, NULL, NULL, 13, 1, 1, 'live:session:start', '直播场次开播按钮', 0),
(3074, 3060, '结束场次', 'live_session_end', 'B', NULL, NULL, NULL, 14, 1, 1, 'live:session:end', '直播场次结束按钮', 0),
(3075, 3060, '复制场次', 'live_session_copy', 'B', NULL, NULL, NULL, 15, 1, 1, 'live:session:copy', '直播场次复制按钮', 0),
(3076, 3060, '删除场次', 'live_session_delete', 'B', NULL, NULL, NULL, 16, 1, 1, 'live:session:delete', '直播场次删除按钮', 0),
(3077, 3060, '推送开播提醒', 'live_session_push_notification', 'B', NULL, NULL, NULL, 17, 1, 1, 'live:session:push-notification', '直播场次推送提醒按钮', 0),
(3080, 3060, '预约记录', 'live_reserve_view', 'B', NULL, NULL, NULL, 20, 1, 1, 'live:reserve:view', '直播预约记录查看权限', 0),
(3081, 3060, '观看记录', 'live_watch_view', 'B', NULL, NULL, NULL, 21, 1, 1, 'live:watch:view', '直播观看记录查看权限', 0),
(3082, 3060, '答题记录', 'live_quiz_view', 'B', NULL, NULL, NULL, 22, 1, 1, 'live:quiz:view', '直播答题记录查看权限', 0),
(3100, 3050, '授课专家管理', 'live_expert_manage', 'C', '/live/course/expert', 'Live/ExpertList', NULL, 30, 1, 1, 'live:expert:view', '授课专家管理菜单', 0),
(3101, 3100, '新增授课专家', 'live_expert_add', 'B', NULL, NULL, NULL, 1, 1, 1, 'live:expert:add', '授课专家新增按钮', 0),
(3102, 3100, '编辑授课专家', 'live_expert_edit', 'B', NULL, NULL, NULL, 2, 1, 1, 'live:expert:edit', '授课专家编辑按钮', 0),
(3103, 3100, '启用禁用授课专家', 'live_expert_status', 'B', NULL, NULL, NULL, 3, 1, 1, 'live:expert:status', '授课专家状态按钮', 0),
(3104, 3100, '删除授课专家', 'live_expert_delete', 'B', NULL, NULL, NULL, 4, 1, 1, 'live:expert:delete', '授课专家删除按钮', 0);

INSERT IGNORE INTO `sys_role_menu` (`role_id`, `menu_id`)
VALUES
(1, 1000), (1, 1100), (1, 1101), (1, 1102), (1, 1103), (1, 1104), (1, 1105),
(1, 1200), (1, 1201), (1, 1202), (1, 1203), (1, 1204),
(1, 2000), (1, 2100), (1, 2110), (1, 2111), (1, 2112), (1, 2113), (1, 2114), (1, 2115), (1, 2116), (1, 2117), (1, 2118), (1, 2119),
(1, 2200), (1, 2210), (1, 2211), (1, 2220), (1, 2221),
(1, 3000), (1, 3050), (1, 3060), (1, 3061), (1, 3062), (1, 3063),
(1, 3070), (1, 3071), (1, 3072), (1, 3073), (1, 3074), (1, 3075), (1, 3076), (1, 3077),
(1, 3080), (1, 3081), (1, 3082),
(1, 3100), (1, 3101), (1, 3102), (1, 3103), (1, 3104);

SET @member_user_table = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'tcm_app_user'
  ),
  'tcm_app_user',
  IF(
    EXISTS (
      SELECT 1
      FROM information_schema.TABLES
      WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'app_user'
    ),
    'app_user',
  'student'
  )
);

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @member_user_table AND COLUMN_NAME = 'wechat_openid'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @member_user_table, '` ADD COLUMN `wechat_openid` VARCHAR(128) NULL COMMENT ''微信OpenID'' AFTER `avatar`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @member_user_table AND COLUMN_NAME = 'wechat_nickname'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @member_user_table, '` ADD COLUMN `wechat_nickname` VARCHAR(64) NULL COMMENT ''微信昵称'' AFTER `wechat_openid`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @member_user_table AND COLUMN_NAME = 'wechat_avatar'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @member_user_table, '` ADD COLUMN `wechat_avatar` VARCHAR(255) NULL COMMENT ''微信头像URL'' AFTER `wechat_nickname`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @member_user_table AND COLUMN_NAME = 'wechat_bound_at'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @member_user_table, '` ADD COLUMN `wechat_bound_at` DATETIME NULL COMMENT ''微信绑定时间'' AFTER `wechat_avatar`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sys_menu' AND COLUMN_NAME = 'created_by'
  ),
  'SELECT 1',
  'ALTER TABLE `sys_menu` ADD COLUMN `created_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''创建人'' AFTER `deleted`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sys_menu' AND COLUMN_NAME = 'updated_by'
  ),
  'SELECT 1',
  'ALTER TABLE `sys_menu` ADD COLUMN `updated_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''更新人'' AFTER `created_by`'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

UPDATE `sys_menu`
SET `created_by` = '系统初始化'
WHERE `created_by` IS NULL OR TRIM(`created_by`) = '';

UPDATE `sys_menu`
SET `updated_by` = '系统初始化'
WHERE `updated_by` IS NULL OR TRIM(`updated_by`) = '';

SET @expert_table = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'tcm_expert'
  ),
  'tcm_expert',
  'expert'
);

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @expert_table AND COLUMN_NAME = 'created_by'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @expert_table, '` ADD COLUMN `created_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''创建人'' AFTER `deleted`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @expert_table AND COLUMN_NAME = 'updated_by'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @expert_table, '` ADD COLUMN `updated_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''更新人'' AFTER `created_by`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @expert_table AND COLUMN_NAME = 'specialty'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @expert_table, '` ADD COLUMN `specialty` VARCHAR(500) NULL COMMENT ''擅长领域'' AFTER `avatar`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @expert_table AND COLUMN_NAME = 'intro'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @expert_table, '` ADD COLUMN `intro` TEXT NULL COMMENT ''专家介绍'' AFTER `specialty`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT(
  'UPDATE `', @expert_table, '` SET `created_by` = ''系统初始化'' WHERE `created_by` IS NULL OR TRIM(`created_by`) = '''''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT(
  'UPDATE `', @expert_table, '` SET `updated_by` = ''系统初始化'' WHERE `updated_by` IS NULL OR TRIM(`updated_by`) = '''''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @clinic_table = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'tcm_clinic'
  ),
  'tcm_clinic',
  'clinic'
);

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @clinic_table AND COLUMN_NAME = 'created_by'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @clinic_table, '` ADD COLUMN `created_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''创建人'' AFTER `deleted`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @clinic_table AND COLUMN_NAME = 'updated_by'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @clinic_table, '` ADD COLUMN `updated_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''更新人'' AFTER `created_by`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT(
  'UPDATE `', @clinic_table, '` SET `created_by` = ''系统初始化'' WHERE `created_by` IS NULL OR TRIM(`created_by`) = '''''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT(
  'UPDATE `', @clinic_table, '` SET `updated_by` = ''系统初始化'' WHERE `updated_by` IS NULL OR TRIM(`updated_by`) = '''''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @member_user_table AND COLUMN_NAME = 'created_by'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @member_user_table, '` ADD COLUMN `created_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''创建人'' AFTER `deleted`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @member_user_table AND COLUMN_NAME = 'updated_by'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @member_user_table, '` ADD COLUMN `updated_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''更新人'' AFTER `created_by`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT(
  'UPDATE `', @member_user_table, '` SET `created_by` = ''系统初始化'' WHERE `created_by` IS NULL OR TRIM(`created_by`) = '''''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT(
  'UPDATE `', @member_user_table, '` SET `updated_by` = ''系统初始化'' WHERE `updated_by` IS NULL OR TRIM(`updated_by`) = '''''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @live_course_table = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'tcm_live_course'
  ),
  'tcm_live_course',
  'live_course'
);

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @live_course_table AND COLUMN_NAME = 'created_by'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @live_course_table, '` ADD COLUMN `created_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''创建人'' AFTER `deleted`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @live_course_table AND COLUMN_NAME = 'updated_by'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @live_course_table, '` ADD COLUMN `updated_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''更新人'' AFTER `created_by`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT(
  'UPDATE `', @live_course_table, '` SET `created_by` = ''系统初始化'' WHERE `created_by` IS NULL OR TRIM(`created_by`) = '''''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT(
  'UPDATE `', @live_course_table, '` SET `updated_by` = ''系统初始化'' WHERE `updated_by` IS NULL OR TRIM(`updated_by`) = '''''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @live_session_table = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'tcm_live_session'
  ),
  'tcm_live_session',
  'live_session'
);

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @live_session_table AND COLUMN_NAME = 'created_by'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @live_session_table, '` ADD COLUMN `created_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''创建人'' AFTER `deleted`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @live_session_table AND COLUMN_NAME = 'updated_by'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @live_session_table, '` ADD COLUMN `updated_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''更新人'' AFTER `created_by`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT(
  'UPDATE `', @live_session_table, '` SET `created_by` = ''系统初始化'' WHERE `created_by` IS NULL OR TRIM(`created_by`) = '''''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT(
  'UPDATE `', @live_session_table, '` SET `updated_by` = ''系统初始化'' WHERE `updated_by` IS NULL OR TRIM(`updated_by`) = '''''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @live_session_question_table = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'tcm_live_session_question'
  ),
  'tcm_live_session_question',
  'live_session_question'
);

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @live_session_question_table AND COLUMN_NAME = 'created_by'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @live_session_question_table, '` ADD COLUMN `created_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''创建人'' AFTER `deleted`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = IF(
  EXISTS (
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = @live_session_question_table AND COLUMN_NAME = 'updated_by'
  ),
  'SELECT 1',
  CONCAT('ALTER TABLE `', @live_session_question_table, '` ADD COLUMN `updated_by` VARCHAR(64) NOT NULL DEFAULT ''系统初始化'' COMMENT ''更新人'' AFTER `created_by`')
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT(
  'UPDATE `', @live_session_question_table, '` SET `created_by` = ''系统初始化'' WHERE `created_by` IS NULL OR TRIM(`created_by`) = '''''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = CONCAT(
  'UPDATE `', @live_session_question_table, '` SET `updated_by` = ''系统初始化'' WHERE `updated_by` IS NULL OR TRIM(`updated_by`) = '''''
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

INSERT IGNORE INTO `sys_user_role` (`user_id`, `role_id`)
SELECT `id`, 1
FROM `sys_user`
WHERE `username` = 'admin';