AiDOPWarehouse/tools/sql/add_tenant_id_to_business_tables.sql

-- ========================================================================
-- AiDOP插件多租户改造：为业务表添加 tenant_id 字段
-- 生成时间：2026-04-22
-- 字段名统一为：tenant_id（小写下划线格式）
-- ========================================================================

USE aidopdev;

-- ========================================================================
-- Order 模块业务表
-- ========================================================================

-- 1. 合同评审主表
ALTER TABLE ado_contract_review
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）' AFTER UpdateTime;

-- 2. 合同评审流程节点表
ALTER TABLE ado_contract_review_flow
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）' AFTER NodeStatus;

-- 3. 计划联动看板
ALTER TABLE LinkagePlan
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）' AFTER Background;

-- 4. 产品设计主表
ALTER TABLE ProductDesign
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 5. 产品设计BOM表
ALTER TABLE ProductDesignBom
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 6. 产品设计工艺表
ALTER TABLE ProductDesignRouting
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 7. 销售订单明细表
ALTER TABLE crm_seorderentry
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 8. 发货计划主表
ALTER TABLE ShippingPlan
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 9. 发货计划明细表
ALTER TABLE ShippingPlanDetail
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 10. ASN发货单主表
ALTER TABLE AsnShipperMaster
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- 11. ASN发货单明细表
ALTER TABLE AsnShipperDetail
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- ========================================================================
-- Production 模块业务表
-- ========================================================================

-- 12. 排产异常日志
ALTER TABLE ScheduleExceptionMaster
ADD COLUMN IF NOT EXISTS tenant_id BIGINT NULL COMMENT '租户ID（多租户隔离）';

-- ========================================================================
-- 创建租户ID索引（提升查询性能）
-- ========================================================================

-- 检查并创建索引（如果不存在）
SET @db = 'aidopdev';

-- Order 模块索引
SET @table = 'ado_contract_review';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'ado_contract_review_flow';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'LinkagePlan';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'ProductDesign';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'ProductDesignBom';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'ProductDesignRouting';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'crm_seorder';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'crm_seorderentry';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'ShippingPlan';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'ShippingPlanDetail';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'AsnShipperMaster';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'AsnShipperDetail';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @table = 'ScheduleExceptionMaster';
SET @sql = CONCAT('CREATE INDEX IF NOT EXISTS idx_tenant_id ON ', @db, '.', @table, '(tenant_id)');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ========================================================================
-- 验证：检查所有业务表的 tenant_id 字段
-- ========================================================================

SELECT '业务表 tenant_id 字段检查' AS 检查项;

SELECT 
    TABLE_NAME AS 表名,
    COLUMN_NAME AS 字段名,
    COLUMN_TYPE AS 字段类型,
    IS_NULLABLE AS 是否可空,
    COLUMN_COMMENT AS 注释
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'aidopdev'
  AND TABLE_NAME IN (
      'ado_contract_review', 
      'ado_contract_review_flow',
      'LinkagePlan',
      'ProductDesign',
      'ProductDesignBom',
      'ProductDesignRouting',
      'crm_seorder',
      'crm_seorderentry',
      'ShippingPlan',
      'ShippingPlanDetail',
      'AsnShipperMaster',
      'AsnShipperDetail',
      'ScheduleExceptionMaster'
  )
  AND COLUMN_NAME = 'tenant_id'
ORDER BY TABLE_NAME;

-- ========================================================================
-- 验证：检查索引创建情况
-- ========================================================================

SELECT '业务表 tenant_id 索引检查' AS 检查项;

SELECT 
    TABLE_NAME AS 表名,
    INDEX_NAME AS 索引名,
    COLUMN_NAME AS 字段名,
    SEQ_IN_INDEX AS 序号,
    NON_UNIQUE AS 非唯一
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'aidopdev'
  AND TABLE_NAME IN (
      'ado_contract_review', 
      'ado_contract_review_flow',
      'LinkagePlan',
      'ProductDesign',
      'ProductDesignBom',
      'ProductDesignRouting',
      'crm_seorder',
      'crm_seorderentry',
      'ShippingPlan',
      'ShippingPlanDetail',
      'AsnShipperMaster',
      'AsnShipperDetail',
      'ScheduleExceptionMaster'
  )
  AND COLUMN_NAME = 'tenant_id'
ORDER BY TABLE_NAME;

-- ========================================================================
-- 完成！
-- ========================================================================
SELECT '✅ 业务表 tenant_id 字段添加完成！' AS 状态;