ylrz_saas_pharmacy/java/sql/lobster_store_operation_menu.sql

-- ============================================================================
-- 门店运营与医药工作台菜单优化脚本
--
-- 目标：
--   1. 管理端(saasmgnui)新增门店运营管理子菜单（业绩/交接班/接待/考勤）
--   2. 药师端(saaspharmacistui)新增"用药咨询工作台"菜单
--   3. 医生端(saasdoctorui)新增"在线问诊工作台"菜单
--   4. 精简药师/医生端可见菜单，隐藏管理类功能
--
-- 使用说明：
--   主库执行 PART A（sys_menu）
--   租户库逐租户执行 PART B（tenant_sys_menu）
--   menu_id 使用 31000~31020 区段，不与现有 29xxx 冲突
-- ============================================================================

-- ######################################################################
-- PART A：主库 sys_menu
-- ######################################################################

-- A.1 门店运营管理父菜单
REPLACE INTO sys_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark) VALUES
(31000, '门店运营', 0, 90, '/storeOperation', '', 'M', 'el-icon-s-shop', '0', '0', 0, 0, NULL, 'admin', NOW(), 'Store Operation Hub'),

-- A.2 门店运营子菜单
(31001, '员工业绩', 31000, 1, 'staffPerformance', 'store/storeStaffPerformance/index', 'C', 'el-icon-s-marketing', '0', '0', 0, 0, 'store:operation:performance', 'admin', NOW(), NULL),
(31002, '交接班管理', 31000, 2, 'shiftManage', 'store/storeShiftManage/index', 'C', 'el-icon-sort', '0', '0', 0, 0, 'store:operation:shift', 'admin', NOW(), NULL),
(31003, '到店接待', 31000, 3, 'receptionManage', 'store/storeReceptionManage/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:operation:reception', 'admin', NOW(), NULL),
(31004, '考勤打卡', 31000, 4, 'attendanceManage', 'store/storeAttendanceManage/index', 'C', 'el-icon-time', '0', '0', 0, 0, 'store:operation:attendance', 'admin', NOW(), NULL),
(31005, '企微客户', 31000, 5, 'qwCustomer', 'store/storeQwCustomer/index', 'C', 'el-icon-chat-line-round', '0', '0', 0, 0, 'store:operation:qwCustomer', 'admin', NOW(), 'Store QW Customer'),
(31006, '采购建议', 31000, 6, 'purchaseSuggestion', 'store/purchaseSuggestion/index', 'C', 'el-icon-shopping-bag-2', '0', '0', 0, 0, 'store:operation:purchase', 'admin', NOW(), 'Purchase Suggestion'),
(31007, '挂号分诊', 31000, 7, 'clinicRegistration', 'store/clinicRegistration/index', 'C', 'el-icon-document-add', '0', '0', 0, 0, 'store:clinic:registration', 'admin', NOW(), 'Clinic Registration'),
(31008, '就诊叫号', 31000, 8, 'clinicQueue', 'store/clinicQueue/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:clinic:queue', 'admin', NOW(), 'Clinic Queue'),
(31009, '医生排班', 31000, 9, 'doctorSchedule', 'store/doctorSchedule/index', 'C', 'el-icon-date', '0', '0', 0, 0, 'store:clinic:schedule', 'admin', NOW(), 'Doctor Schedule'),

-- A.3 药师工作台菜单
(31010, '用药咨询工作台', 0, 50, '/imWorkbench', 'pharmacist/imWorkbench/index', 'C', 'el-icon-chat-dot-round', '0', '0', 0, 0, 'pharmacist:imworkbench', 'admin', NOW(), 'Pharmacist IM Workbench'),
(31011, '药品销售统计', 0, 51, '/drugStatistics', 'pharmacist/drugStatistics/index', 'C', 'el-icon-pie-chart', '0', '0', 0, 0, 'pharmacist:drugstats', 'admin', NOW(), 'Drug Sales Statistics'),
(31012, '慢病用药周期', 0, 52, '/drugCycle', 'pharmacist/drugCycle/index', 'C', 'el-icon-alarm-clock', '0', '0', 0, 0, 'pharmacist:drugcycle', 'admin', NOW(), 'Drug Cycle Dashboard'),

-- A.4 医生工作台菜单
(31015, '在线问诊工作台', 0, 50, '/consultWorkbench', 'doctor/consultWorkbench/index', 'C', 'el-icon-video-camera', '0', '0', 0, 0, 'doctor:consultworkbench', 'admin', NOW(), 'Doctor Consult Workbench'),
(31016, '患者健康档案', 0, 51, '/patientRecord', 'doctor/patientRecord/index', 'C', 'el-icon-document', '0', '0', 0, 0, 'doctor:patientrecord', 'admin', NOW(), 'Patient Health Record'),
(31017, '随访管理', 0, 52, '/followUp', 'doctor/followUp/index', 'C', 'el-icon-calendar', '0', '0', 0, 0, 'doctor:followup', 'admin', NOW(), 'Follow-up Management'),
(31018, '病历管理', 0, 53, '/medicalRecord', 'doctor/medicalRecord/index', 'C', 'el-icon-tickets', '0', '0', 0, 0, 'doctor:medicalrecord', 'admin', NOW(), 'Medical Record Management');

-- A.4b 企微客户菜单授权
INSERT IGNORE INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, 31005 FROM sys_role_menu r WHERE r.menu_id = 31000
  AND NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id = r.role_id AND x.menu_id = 31005);

-- A.5 同步到 company_menu（销售端子集）
INSERT IGNORE INTO company_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, company_id, remark)
SELECT s.menu_id, s.menu_name, s.parent_id, s.order_num, s.path, s.component, s.is_frame, s.is_cache, s.menu_type, s.visible, s.status, s.perms, s.icon, s.create_by, s.create_time, c.company_id, s.remark
FROM sys_menu s
CROSS JOIN (SELECT DISTINCT company_id FROM company_menu WHERE company_id IS NOT NULL) c
WHERE s.menu_id IN (31000, 31001, 31002, 31003, 31004, 31005, 31006, 31007, 31008, 31009)
  AND NOT EXISTS (SELECT 1 FROM company_menu x WHERE x.menu_id = s.menu_id AND x.company_id = c.company_id);

-- A.6 补全角色授权（门店运营菜单授权给门店管理员角色）
INSERT IGNORE INTO sys_role_menu (role_id, menu_id)
SELECT r.role_id, s.menu_id FROM sys_role_menu r CROSS JOIN sys_menu s
WHERE r.menu_id = 31000 AND s.parent_id = 31000 AND s.menu_type = 'C' AND s.visible = '0'
  AND NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id = r.role_id AND x.menu_id = s.menu_id);

-- A.7 药师端菜单精简：隐藏管理类菜单（设置 visible=1 隐藏）
-- 以下菜单对药师角色隐藏（billing/saas/system/monitor/lobster管理功能等）
-- 通过 sys_role_menu 删除药师角色对这些菜单的关联
-- 注意：此操作不影响管理端角色，仅清理药师/医生角色不需要的菜单关联
-- 如需执行，请取消注释并替换 :pharmacistRoleId / :doctorRoleId 为实际角色ID

-- DELETE FROM sys_role_menu WHERE role_id = :pharmacistRoleId AND menu_id IN (
--   SELECT menu_id FROM sys_menu WHERE path IN (
--     'billing','saas','system','monitor','fastGpt','qdrant','sop',
--     'lobster','tenant','tool','tag','moduleUsage','taskStatistics',
--     'sensitive','callRecord','aiSipCall','aicall','aiob','aiChatQuality'
--   )
-- );

-- DELETE FROM sys_role_menu WHERE role_id = :doctorRoleId AND menu_id IN (
--   SELECT menu_id FROM sys_menu WHERE path IN (
--     'billing','saas','system','monitor','fastGpt','qdrant','sop',
--     'lobster','tenant','tool','tag','moduleUsage','taskStatistics',
--     'sensitive','callRecord','aiSipCall','aicall','aiob','aiChatQuality',
--     'shop','store','supplier','supplychain','marketing','adv'
--   )
-- );


-- ######################################################################
-- PART B：租户库 tenant_sys_menu
-- ######################################################################

REPLACE INTO tenant_sys_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark) VALUES
(31000, '门店运营', 0, 90, '/storeOperation', '', 'M', 'el-icon-s-shop', '0', '0', 0, 0, NULL, 'admin', NOW(), 'Store Operation Hub'),
(31001, '员工业绩', 31000, 1, 'staffPerformance', 'store/storeStaffPerformance/index', 'C', 'el-icon-s-marketing', '0', '0', 0, 0, 'store:operation:performance', 'admin', NOW(), NULL),
(31002, '交接班管理', 31000, 2, 'shiftManage', 'store/storeShiftManage/index', 'C', 'el-icon-sort', '0', '0', 0, 0, 'store:operation:shift', 'admin', NOW(), NULL),
(31003, '到店接待', 31000, 3, 'receptionManage', 'store/storeReceptionManage/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:operation:reception', 'admin', NOW(), NULL),
(31004, '考勤打卡', 31000, 4, 'attendanceManage', 'store/storeAttendanceManage/index', 'C', 'el-icon-time', '0', '0', 0, 0, 'store:operation:attendance', 'admin', NOW(), NULL),
(31005, '企微客户', 31000, 5, 'qwCustomer', 'store/storeQwCustomer/index', 'C', 'el-icon-chat-line-round', '0', '0', 0, 0, 'store:operation:qwCustomer', 'admin', NOW(), 'Store QW Customer'),
(31006, '采购建议', 31000, 6, 'purchaseSuggestion', 'store/purchaseSuggestion/index', 'C', 'el-icon-shopping-bag-2', '0', '0', 0, 0, 'store:operation:purchase', 'admin', NOW(), 'Purchase Suggestion'),
(31007, '挂号分诊', 31000, 7, 'clinicRegistration', 'store/clinicRegistration/index', 'C', 'el-icon-document-add', '0', '0', 0, 0, 'store:clinic:registration', 'admin', NOW(), 'Clinic Registration'),
(31008, '就诊叫号', 31000, 8, 'clinicQueue', 'store/clinicQueue/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:clinic:queue', 'admin', NOW(), 'Clinic Queue'),
(31009, '医生排班', 31000, 9, 'doctorSchedule', 'store/doctorSchedule/index', 'C', 'el-icon-date', '0', '0', 0, 0, 'store:clinic:schedule', 'admin', NOW(), 'Doctor Schedule'),
(31010, '用药咨询工作台', 0, 50, '/imWorkbench', 'pharmacist/imWorkbench/index', 'C', 'el-icon-chat-dot-round', '0', '0', 0, 0, 'pharmacist:imworkbench', 'admin', NOW(), 'Pharmacist IM Workbench'),
(31011, '药品销售统计', 0, 51, '/drugStatistics', 'pharmacist/drugStatistics/index', 'C', 'el-icon-pie-chart', '0', '0', 0, 0, 'pharmacist:drugstats', 'admin', NOW(), 'Drug Sales Statistics'),
(31012, '慢病用药周期', 0, 52, '/drugCycle', 'pharmacist/drugCycle/index', 'C', 'el-icon-alarm-clock', '0', '0', 0, 0, 'pharmacist:drugcycle', 'admin', NOW(), 'Drug Cycle Dashboard'),
(31015, '在线问诊工作台', 0, 50, '/consultWorkbench', 'doctor/consultWorkbench/index', 'C', 'el-icon-video-camera', '0', '0', 0, 0, 'doctor:consultworkbench', 'admin', NOW(), 'Doctor Consult Workbench'),
(31016, '患者健康档案', 0, 51, '/patientRecord', 'doctor/patientRecord/index', 'C', 'el-icon-document', '0', '0', 0, 0, 'doctor:patientrecord', 'admin', NOW(), 'Patient Health Record'),
(31017, '随访管理', 0, 52, '/followUp', 'doctor/followUp/index', 'C', 'el-icon-calendar', '0', '0', 0, 0, 'doctor:followup', 'admin', NOW(), 'Follow-up Management'),
(31018, '病历管理', 0, 53, '/medicalRecord', 'doctor/medicalRecord/index', 'C', 'el-icon-tickets', '0', '0', 0, 0, 'doctor:medicalrecord', 'admin', NOW(), 'Medical Record Management');

-- 同步 tenant_company_menu
REPLACE INTO tenant_company_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark) VALUES
(31000, '门店运营', 0, 90, '/storeOperation', '', 'M', 'el-icon-s-shop', '0', '0', 0, 0, NULL, 'admin', NOW(), 'Store Operation Hub'),
(31001, '员工业绩', 31000, 1, 'staffPerformance', 'store/storeStaffPerformance/index', 'C', 'el-icon-s-marketing', '0', '0', 0, 0, 'store:operation:performance', 'admin', NOW(), NULL),
(31002, '交接班管理', 31000, 2, 'shiftManage', 'store/storeShiftManage/index', 'C', 'el-icon-sort', '0', '0', 0, 0, 'store:operation:shift', 'admin', NOW(), NULL),
(31003, '到店接待', 31000, 3, 'receptionManage', 'store/storeReceptionManage/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:operation:reception', 'admin', NOW(), NULL),
(31004, '考勤打卡', 31000, 4, 'attendanceManage', 'store/storeAttendanceManage/index', 'C', 'el-icon-time', '0', '0', 0, 0, 'store:operation:attendance', 'admin', NOW(), NULL),
(31005, '企微客户', 31000, 5, 'qwCustomer', 'store/storeQwCustomer/index', 'C', 'el-icon-chat-line-round', '0', '0', 0, 0, 'store:operation:qwCustomer', 'admin', NOW(), 'Store QW Customer'),
(31006, '采购建议', 31000, 6, 'purchaseSuggestion', 'store/purchaseSuggestion/index', 'C', 'el-icon-shopping-bag-2', '0', '0', 0, 0, 'store:operation:purchase', 'admin', NOW(), 'Purchase Suggestion'),
(31007, '挂号分诊', 31000, 7, 'clinicRegistration', 'store/clinicRegistration/index', 'C', 'el-icon-document-add', '0', '0', 0, 0, 'store:clinic:registration', 'admin', NOW(), 'Clinic Registration'),
(31008, '就诊叫号', 31000, 8, 'clinicQueue', 'store/clinicQueue/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:clinic:queue', 'admin', NOW(), 'Clinic Queue'),
(31009, '医生排班', 31000, 9, 'doctorSchedule', 'store/doctorSchedule/index', 'C', 'el-icon-date', '0', '0', 0, 0, 'store:clinic:schedule', 'admin', NOW(), 'Doctor Schedule');

-- 同步 tenant_company_menu 诊所模块
REPLACE INTO tenant_company_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, icon, visible, status, is_frame, is_cache, perms, create_by, create_time, remark) VALUES
(31007, '挂号分诊', 31000, 7, 'clinicRegistration', 'store/clinicRegistration/index', 'C', 'el-icon-document-add', '0', '0', 0, 0, 'store:clinic:registration', 'admin', NOW(), 'Clinic Registration'),
(31008, '就诊叫号', 31000, 8, 'clinicQueue', 'store/clinicQueue/index', 'C', 'el-icon-bell', '0', '0', 0, 0, 'store:clinic:queue', 'admin', NOW(), 'Clinic Queue'),
(31009, '医生排班', 31000, 9, 'doctorSchedule', 'store/doctorSchedule/index', 'C', 'el-icon-date', '0', '0', 0, 0, 'store:clinic:schedule', 'admin', NOW(), 'Doctor Schedule');

-- ######################################################################
-- PART C：DDL 用药反馈表
-- ######################################################################
CREATE TABLE IF NOT EXISTS med_feedback (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    patient_id      BIGINT NOT NULL COMMENT '患者ID',
    drug_name       VARCHAR(200) NOT NULL COMMENT '药品名称',
    effect_rating   TINYINT DEFAULT 3 COMMENT '疗效评价: 3显效 2有效 1无效',
    has_adverse_reaction TINYINT DEFAULT 0 COMMENT '是否有不良反应: 0无 1有',
    feedback_content TEXT COMMENT '反馈内容',
    doctor_id       BIGINT COMMENT '记录医生ID',
    doctor_name     VARCHAR(100) COMMENT '记录医生姓名',
    create_time     DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_patient (patient_id),
    INDEX idx_doctor (doctor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用药反馈记录表';

-- ######################################################################
-- PART D：DDL 诊所模块表
-- ######################################################################

CREATE TABLE IF NOT EXISTS clinic_registration (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    store_id        BIGINT NOT NULL COMMENT '门店ID',
    patient_name    VARCHAR(100) NOT NULL COMMENT '患者姓名',
    patient_phone   VARCHAR(20) COMMENT '患者手机号',
    patient_gender  TINYINT DEFAULT 1 COMMENT '性别 0女 1男',
    patient_age     INT COMMENT '年龄',
    reg_type        TINYINT DEFAULT 0 COMMENT '挂号类型 0普通 1专家 2急诊',
    department      VARCHAR(100) COMMENT '科室',
    doctor_id       BIGINT COMMENT '医生ID',
    doctor_name     VARCHAR(100) COMMENT '医生姓名',
    symptom         TEXT COMMENT '主诉症状',
    reg_fee        DECIMAL(10,2) DEFAULT 0 COMMENT '挂号费',
    queue_no       INT DEFAULT 0 COMMENT '排队号',
    status          TINYINT DEFAULT 0 COMMENT '状态 0待就诊 1就诊中 2已完成 3已退号',
    reg_time       DATETIME COMMENT '挂号时间',
    visit_time     DATETIME COMMENT '就诊时间',
    remark          VARCHAR(500) COMMENT '备注',
    create_by       VARCHAR(64) DEFAULT '' COMMENT '创建者',
    create_time     DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_by       VARCHAR(64) DEFAULT '' COMMENT '更新者',
    update_time     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_store_date (store_id, DATE(reg_time)),
    INDEX idx_status (status),
    INDEX idx_doctor (doctor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='诊所挂号表';

CREATE TABLE IF NOT EXISTS doctor_schedule (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    store_id        BIGINT NOT NULL COMMENT '门店ID',
    doctor_id       BIGINT COMMENT '医生ID',
    doctor_name     VARCHAR(100) NOT NULL COMMENT '医生姓名',
    schedule_date   DATE NOT NULL COMMENT '排班日期',
    shift           TINYINT DEFAULT 0 COMMENT '班次 0上午 1下午 2全天',
    max_patients    INT DEFAULT 20 COMMENT '最大接诊量',
    current_patients INT DEFAULT 0 COMMENT '已挂号数',
    status          TINYINT DEFAULT 0 COMMENT '状态 0可挂号 1已满 2停诊',
    department      VARCHAR(100) COMMENT '科室',
    remark          VARCHAR(500) COMMENT '备注',
    create_by       VARCHAR(64) DEFAULT '',
    create_time     DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_by       VARCHAR(64) DEFAULT '',
    update_time     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_store_date (store_id, schedule_date),
    INDEX idx_doctor (doctor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='医生排班表';

CREATE TABLE IF NOT EXISTS clinic_billing (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    store_id        BIGINT NOT NULL COMMENT '门店ID',
    registration_id BIGINT COMMENT '挂号ID',
    patient_name    VARCHAR(100) NOT NULL COMMENT '患者姓名',
    patient_phone   VARCHAR(20) COMMENT '患者手机号',
    doctor_name     VARCHAR(100) COMMENT '接诊医生',
    diagnose        VARCHAR(500) COMMENT '诊断',
    reg_fee        DECIMAL(10,2) DEFAULT 0 COMMENT '挂号费',
    exam_fee        DECIMAL(10,2) DEFAULT 0 COMMENT '检查费',
    drug_fee        DECIMAL(10,2) DEFAULT 0 COMMENT '药品费',
    other_fee        DECIMAL(10,2) DEFAULT 0 COMMENT '其他费用',
    total_amount    DECIMAL(10,2) DEFAULT 0 COMMENT '总金额',
    pay_method      TINYINT DEFAULT 0 COMMENT '支付方式 0现金 1微信 2支付宝 3医保 4银行卡',
    status          TINYINT DEFAULT 0 COMMENT '状态 0待支付 1已支付 2已退款',
    billing_time    DATETIME COMMENT '结算时间',
    remark          VARCHAR(500) COMMENT '备注',
    create_by       VARCHAR(64) DEFAULT '',
    create_time     DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_by       VARCHAR(64) DEFAULT '',
    update_time     DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_store (store_id),
    INDEX idx_registration (registration_id),
    INDEX idx_billing_date (DATE(billing_time))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='诊所收费结算表';