ylrz_saas_pharmacy/java/sql/manual/lobster_execution_tenant32_deploy.sql

-- =============================================================================
-- 数字员工双模式 + 千人千面 为 租户32 指定配置部署
-- 目标库： ylrz_saas_tenant_1 (company_id = 32)
-- 全局说明： ylrz_saas (tenant_sys_menu)
--
-- 执行方式（DataGrip / Navicat）：
--   1) 选择租户库，执行「PART A」全部
--   2) 选择切换到 ylrz_saas，执行「PART B」
--   3) 重启 fs-saas-company / fs-saas-admin
--
-- 依赖 Flyway：
--   V20260615_01__lobster_execution_mode.sql
--   V20260615_02__lobster_execution_config_menu.sql
--   V20260615_03__lobster_template_execution_policy.sql
-- =============================================================================

-- ===================== PART A: 租户库 ylrz_saas_tenant_1 =====================
-- USE ylrz_saas_tenant_1;

-- A1) 租户级别执行配置表
CREATE TABLE IF NOT EXISTS `lobster_execution_config`
(
    `id`                             bigint       NOT NULL AUTO_INCREMENT,
    `company_id`                     bigint       NOT NULL COMMENT 'tenant company id',
    `default_execution_mode`         varchar(16)  NOT NULL DEFAULT 'HYBRID' COMMENT 'PROMPT/INSTANCE/HYBRID',
    `enable_content_personalization` tinyint      NOT NULL DEFAULT 1,
    `enable_flow_personalization`    tinyint      NOT NULL DEFAULT 1,
    `strict_fixed_workflow`          tinyint      NOT NULL DEFAULT 0,
    `auto_start_instance_on_tag`     tinyint      NOT NULL DEFAULT 0,
    `create_by`                      varchar(64)           DEFAULT NULL,
    `create_time`                    datetime              DEFAULT CURRENT_TIMESTAMP,
    `update_by`                      varchar(64)           DEFAULT NULL,
    `update_time`                    datetime              DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='lobster execution policy';

-- A2) 模板表 execution_mode
SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_workflow_lobster' AND COLUMN_NAME = 'execution_mode');
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE company_workflow_lobster ADD COLUMN execution_mode varchar(16) DEFAULT NULL COMMENT ''PROMPT/INSTANCE/HYBRID'' AFTER canvas_data',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- A3) 任务表 external_contact_id
SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_workflow_lobster_task' AND COLUMN_NAME = 'external_contact_id');
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE company_workflow_lobster_task ADD COLUMN external_contact_id bigint DEFAULT NULL COMMENT ''qw_external_contact.id'' AFTER binding_id',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- A4) 模板级千人千面（null = 继承租户 lobster_execution_config）
SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_workflow_lobster' AND COLUMN_NAME = 'enable_content_personalization');
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE company_workflow_lobster ADD COLUMN enable_content_personalization tinyint DEFAULT NULL COMMENT ''null=inherit tenant'' AFTER execution_mode',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_workflow_lobster' AND COLUMN_NAME = 'enable_flow_personalization');
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE company_workflow_lobster ADD COLUMN enable_flow_personalization tinyint DEFAULT NULL COMMENT ''null=inherit tenant'' AFTER enable_content_personalization',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_workflow_lobster' AND COLUMN_NAME = 'strict_fixed_workflow');
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE company_workflow_lobster ADD COLUMN strict_fixed_workflow tinyint DEFAULT NULL COMMENT ''null=inherit tenant'' AFTER enable_flow_personalization',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'company_workflow_lobster' AND COLUMN_NAME = 'auto_start_instance_on_tag');
SET @ddl := IF(@col_exists = 0,
    'ALTER TABLE company_workflow_lobster ADD COLUMN auto_start_instance_on_tag tinyint DEFAULT NULL COMMENT ''null=inherit tenant'' AFTER strict_fixed_workflow',
    'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- A5) 租户菜单新增执行配置菜单 (29947)
INSERT IGNORE 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 (29947, '执行配置', 29900, 22, 'execution-config', 'lobster/execution-config/index', 'C', 'el-icon-setting', '0', '0', 0, 0, 'workflow:lobster:edit', 'admin', NOW(), 'Prompt/Instance/Hybrid switches');

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 menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, NULL, remark
FROM sys_menu WHERE menu_id = 29947;

-- A6) 预选租户32 默认执行配置（可按需修改后执行）
-- INSERT INTO lobster_execution_config
-- (company_id, default_execution_mode, enable_content_personalization, enable_flow_personalization, strict_fixed_workflow, auto_start_instance_on_tag, create_by, create_time)
-- VALUES (32, 'HYBRID', 1, 1, 0, 0, 'manual_deploy', NOW())
-- ON DUPLICATE KEY UPDATE update_time = NOW();

-- 验证
SELECT 'lobster_execution_config' AS tbl, COUNT(*) AS cnt FROM lobster_execution_config;
SELECT menu_id, menu_name, path FROM sys_menu WHERE menu_id = 29947;
SHOW COLUMNS FROM company_workflow_lobster LIKE 'enable_%';
SHOW COLUMNS FROM company_workflow_lobster_task LIKE 'external_contact_id';


-- ===================== PART B: 全局库 ylrz_saas =====================
-- USE ylrz_saas;

INSERT IGNORE 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
(29947, '执行配置', 29900, 22, 'execution-config', 'lobster/execution-config/index', 'C', 'el-icon-setting', '0', '0', 0, 0, 'workflow:lobster:edit', 'admin', NOW(), 'Prompt/Instance/Hybrid switches');

INSERT IGNORE INTO tenant_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 menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, NULL, remark
FROM tenant_sys_menu
WHERE menu_id = 29947;

SELECT menu_id, menu_name, path FROM tenant_sys_menu WHERE menu_id = 29947;