-- =============================================
-- 材料仓库管理与管线划分优化 - 数据库变更脚本
-- 生成时间: 2026-03-05
-- 说明: 请在执行前备份数据库
-- =============================================

-- =============================================
-- 第一部分: 创建材料仓库表
-- =============================================

-- 检查表是否存在，如果存在则删除（仅用于开发环境）
-- IF OBJECT_ID('dbo.Base_Warehouse', 'U') IS NOT NULL
--     DROP TABLE dbo.Base_Warehouse;
-- GO

-- 创建材料仓库表
CREATE TABLE dbo.Base_Warehouse (
    WarehouseId NVARCHAR(50) NOT NULL,
    WarehouseName NVARCHAR(200) NOT NULL,
    Remark NVARCHAR(500) NULL,
    ProjectId NVARCHAR(50) NOT NULL,
    CreateUserId NVARCHAR(50) NULL,
    CreateTime DATETIME NULL,
    ModifyUserId NVARCHAR(50) NULL,
    ModifyTime DATETIME NULL,
    CONSTRAINT PK_Base_Warehouse PRIMARY KEY CLUSTERED (WarehouseId ASC),
    CONSTRAINT FK_Base_Warehouse_Project FOREIGN KEY (ProjectId)
        REFERENCES dbo.Project_Project (ProjectId)
        ON DELETE NO ACTION
);
GO

-- 创建索引以提升查询性能
CREATE NONCLUSTERED INDEX IX_Base_Warehouse_ProjectId
    ON dbo.Base_Warehouse(ProjectId);
GO

CREATE NONCLUSTERED INDEX IX_Base_Warehouse_WarehouseName
    ON dbo.Base_Warehouse(WarehouseName);
GO

-- 添加表说明
EXEC sys.sp_addextendedproperty
    @name=N'MS_Description',
    @value=N'材料仓库基础信息表' ,
    @level0type=N'SCHEMA',
    @level0name=N'dbo',
    @level1type=N'TABLE',
    @level1name=N'Base_Warehouse';
GO

-- 添加字段说明
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仓库ID（主键）' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_Warehouse', @level2type=N'COLUMN',@level2name=N'WarehouseId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'仓库名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_Warehouse', @level2type=N'COLUMN',@level2name=N'WarehouseName';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'备注' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_Warehouse', @level2type=N'COLUMN',@level2name=N'Remark';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'项目ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_Warehouse', @level2type=N'COLUMN',@level2name=N'ProjectId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建人ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_Warehouse', @level2type=N'COLUMN',@level2name=N'CreateUserId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'创建时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_Warehouse', @level2type=N'COLUMN',@level2name=N'CreateTime';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改人ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_Warehouse', @level2type=N'COLUMN',@level2name=N'ModifyUserId';
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'修改时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Base_Warehouse', @level2type=N'COLUMN',@level2name=N'ModifyTime';
GO

PRINT '材料仓库表创建完成';
GO

-- =============================================
-- 第二部分: 为管线表添加仓库字段
-- =============================================

-- 检查字段是否已存在
IF NOT EXISTS (
    SELECT 1 FROM sys.columns
    WHERE object_id = OBJECT_ID('dbo.HJGL_Pipeline')
    AND name = 'WarehouseId'
)
BEGIN
    -- 添加 WarehouseId 字段（允许NULL以兼容现有数据）
    ALTER TABLE dbo.HJGL_Pipeline
    ADD WarehouseId NVARCHAR(50) NULL;
    GO

    -- 添加字段说明
    EXEC sys.sp_addextendedproperty
        @name=N'MS_Description',
        @value=N'仓库ID（关联Base_Warehouse表）' ,
        @level0type=N'SCHEMA',
        @level0name=N'dbo',
        @level1type=N'TABLE',
        @level1name=N'HJGL_Pipeline',
        @level2type=N'COLUMN',
        @level2name=N'WarehouseId';
    GO

    -- 添加外键约束（可选，根据实际需求决定是否启用）
    -- ALTER TABLE dbo.HJGL_Pipeline
    -- ADD CONSTRAINT FK_HJGL_Pipeline_Warehouse
    -- FOREIGN KEY (WarehouseId)
    -- REFERENCES dbo.Base_Warehouse(WarehouseId)
    -- ON DELETE SET NULL;
    -- GO

    PRINT '管线表WarehouseId字段添加完成';
END
ELSE
BEGIN
    PRINT '管线表WarehouseId字段已存在，跳过添加';
END
GO

-- =============================================
-- 第三部分: 验证脚本
-- =============================================

-- 验证表和字段创建成功
SELECT
    'Base_Warehouse表' AS 对象类型,
    CASE WHEN EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Base_Warehouse')
         THEN '已创建 ✓'
         ELSE '未创建 ✗'
    END AS 状态;

SELECT
    'HJGL_Pipeline.WarehouseId字段' AS 对象类型,
    CASE WHEN EXISTS (
        SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_NAME = 'HJGL_Pipeline'
        AND COLUMN_NAME = 'WarehouseId'
    )
    THEN '已添加 ✓'
    ELSE '未添加 ✗'
    END AS 状态;
GO

PRINT '========================================';
PRINT '数据库变更脚本执行完成！';
PRINT '========================================';
PRINT '';
PRINT '重要提示：';
PRINT '1. 请在生产环境执行前备份数据库';
PRINT '2. 建议在维护窗口执行ALTER TABLE操作';
PRINT '3. 现有管线数据的WarehouseId默认为NULL';
PRINT '4. 可通过批量设置功能逐步完善数据';
PRINT '========================================';
GO