-- 产品会话管理相关表结构
-- 创建时间: 2026-04-01
-- 版本: 1.0

-- =============================================
-- 产品会话表
-- =============================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mdl_production_session]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[mdl_production_session] (
        [SessionId] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_mdl_production_session_SessionId] DEFAULT (NEWID()),
        [ProductTypeCode] NVARCHAR(50) NOT NULL,
        [ProductTypeName] NVARCHAR(200) NOT NULL,
        [StationId] NVARCHAR(50) NOT NULL,
        [StationName] NVARCHAR(200) NOT NULL,
        [OperatorId] NVARCHAR(50) NOT NULL,
        [OperatorName] NVARCHAR(100) NOT NULL,
        [ShiftId] NVARCHAR(50) NOT NULL,
        [ShiftName] NVARCHAR(100) NOT NULL,
        [StartedAtUtc] DATETIME2(3) NOT NULL,
        [EndedAtUtc] DATETIME2(3) NULL,
        [Status] INT NOT NULL CONSTRAINT [DF_mdl_production_session_Status] DEFAULT (0), -- 0=InProgress, 1=CompletedOk, 2=CompletedNg, 3=Cancelled, 4=Paused
        [Result] INT NOT NULL CONSTRAINT [DF_mdl_production_session_Result] DEFAULT (0), -- 0=Pending, 1=Ok, 2=Ng
        [CurrentLayer] INT NOT NULL CONSTRAINT [DF_mdl_production_session_CurrentLayer] DEFAULT (0),
        [TotalLayers] INT NOT NULL CONSTRAINT [DF_mdl_production_session_TotalLayers] DEFAULT (0),
        [NgReason] NVARCHAR(500) NULL,
        [Remark] NVARCHAR(1000) NULL,
        [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_mdl_production_session_CreatedAtUtc] DEFAULT (SYSUTCDATETIME()),
        [UpdatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_mdl_production_session_UpdatedAtUtc] DEFAULT (SYSUTCDATETIME()),
        [CreatedBy] NVARCHAR(100) NOT NULL CONSTRAINT [DF_mdl_production_session_CreatedBy] DEFAULT ('System'),
        [UpdatedBy] NVARCHAR(100) NOT NULL CONSTRAINT [DF_mdl_production_session_UpdatedBy] DEFAULT ('System'),
        CONSTRAINT [PK_mdl_production_session] PRIMARY KEY CLUSTERED ([SessionId] ASC)
    );

    -- 添加索引
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_StationId_Status] ON [dbo].[mdl_production_session] ([StationId], [Status]);
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_ProductTypeCode] ON [dbo].[mdl_production_session] ([ProductTypeCode]);
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_OperatorId] ON [dbo].[mdl_production_session] ([OperatorId]);
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_ShiftId] ON [dbo].[mdl_production_session] ([ShiftId]);
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_StartedAtUtc] ON [dbo].[mdl_production_session] ([StartedAtUtc]);
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_EndedAtUtc] ON [dbo].[mdl_production_session] ([EndedAtUtc]);
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_Status_Result] ON [dbo].[mdl_production_session] ([Status], [Result]);

    PRINT '创建表 mdl_production_session 成功';
END
ELSE
BEGIN
    PRINT '表 mdl_production_session 已存在，跳过创建';
END

-- =============================================
-- 产品会话事件表
-- =============================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mdl_production_session_event]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[mdl_production_session_event] (
        [EventId] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_mdl_production_session_event_EventId] DEFAULT (NEWID()),
        [SessionId] UNIQUEIDENTIFIER NOT NULL,
        [EventType] INT NOT NULL, -- 0=SessionCreated, 1=SessionArchived, 2=ProgressUpdated, 3=SessionPaused, 4=SessionResumed, 5=SessionCancelled, 6=LayerStarted, 7=LayerCompleted, 8=NgDetected, 9=ManualIntervention
        [Description] NVARCHAR(500) NOT NULL,
        [OperatorName] NVARCHAR(100) NOT NULL,
        [EventTimeUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_mdl_production_session_event_EventTimeUtc] DEFAULT (SYSUTCDATETIME()),
        [ExtendedProperties] NVARCHAR(MAX) NULL, -- JSON格式的扩展属性
        [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_mdl_production_session_event_CreatedAtUtc] DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT [PK_mdl_production_session_event] PRIMARY KEY CLUSTERED ([EventId] ASC)
    );

    -- 添加索引
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_event_SessionId] ON [dbo].[mdl_production_session_event] ([SessionId]);
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_event_EventType] ON [dbo].[mdl_production_session_event] ([EventType]);
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_event_EventTimeUtc] ON [dbo].[mdl_production_session_event] ([EventTimeUtc]);

    -- 添加外键约束
    ALTER TABLE [dbo].[mdl_production_session_event] WITH CHECK ADD CONSTRAINT [FK_mdl_production_session_event_mdl_production_session] FOREIGN KEY([SessionId])
    REFERENCES [dbo].[mdl_production_session] ([SessionId])
    ON DELETE CASCADE;

    PRINT '创建表 mdl_production_session_event 成功';
END
ELSE
BEGIN
    PRINT '表 mdl_production_session_event 已存在，跳过创建';
END

-- =============================================
-- 产品会话快照表（可选，用于保存会话的详细状态快照）
-- =============================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mdl_production_session_snapshot]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[mdl_production_session_snapshot] (
        [SnapshotId] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_mdl_production_session_snapshot_SnapshotId] DEFAULT (NEWID()),
        [SessionId] UNIQUEIDENTIFIER NOT NULL,
        [LayerNumber] INT NOT NULL,
        [SnapshotType] INT NOT NULL, -- 0=LayerStart, 1=LayerEnd, 2=NgDetected, 3=ManualIntervention
        [SnapshotData] NVARCHAR(MAX) NOT NULL, -- JSON格式的快照数据
        [Images] NVARCHAR(MAX) NULL, -- JSON格式的图片路径列表
        [InferenceResults] NVARCHAR(MAX) NULL, -- JSON格式的推理结果
        [RuleResults] NVARCHAR(MAX) NULL, -- JSON格式的规则检查结果
        [OperatorName] NVARCHAR(100) NULL,
        [SnapshotTimeUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_mdl_production_session_snapshot_SnapshotTimeUtc] DEFAULT (SYSUTCDATETIME()),
        [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_mdl_production_session_snapshot_CreatedAtUtc] DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT [PK_mdl_production_session_snapshot] PRIMARY KEY CLUSTERED ([SnapshotId] ASC)
    );

    -- 添加索引
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_snapshot_SessionId] ON [dbo].[mdl_production_session_snapshot] ([SessionId]);
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_snapshot_LayerNumber] ON [dbo].[mdl_production_session_snapshot] ([LayerNumber]);
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_snapshot_SnapshotType] ON [dbo].[mdl_production_session_snapshot] ([SnapshotType]);
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_snapshot_SnapshotTimeUtc] ON [dbo].[mdl_production_session_snapshot] ([SnapshotTimeUtc]);

    -- 添加外键约束
    ALTER TABLE [dbo].[mdl_production_session_snapshot] WITH CHECK ADD CONSTRAINT [FK_mdl_production_session_snapshot_mdl_production_session] FOREIGN KEY([SessionId])
    REFERENCES [dbo].[mdl_production_session] ([SessionId])
    ON DELETE CASCADE;

    PRINT '创建表 mdl_production_session_snapshot 成功';
END
ELSE
BEGIN
    PRINT '表 mdl_production_session_snapshot 已存在，跳过创建';
END

-- =============================================
-- 产品会话配置表（可选，用于存储会话相关的配置信息）
-- =============================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mdl_production_session_config]') AND type in (N'U'))
BEGIN
    CREATE TABLE [dbo].[mdl_production_session_config] (
        [ConfigId] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_mdl_production_session_config_ConfigId] DEFAULT (NEWID()),
        [SessionId] UNIQUEIDENTIFIER NOT NULL,
        [ConfigType] NVARCHAR(50) NOT NULL, -- 配置类型：Camera, Inference, Rules, etc.
        [ConfigKey] NVARCHAR(100) NOT NULL,
        [ConfigValue] NVARCHAR(MAX) NOT NULL,
        [ConfigVersion] NVARCHAR(20) NULL,
        [IsActive] BIT NOT NULL CONSTRAINT [DF_mdl_production_session_config_IsActive] DEFAULT (1),
        [CreatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_mdl_production_session_config_CreatedAtUtc] DEFAULT (SYSUTCDATETIME()),
        [UpdatedAtUtc] DATETIME2(3) NOT NULL CONSTRAINT [DF_mdl_production_session_config_UpdatedAtUtc] DEFAULT (SYSUTCDATETIME()),
        CONSTRAINT [PK_mdl_production_session_config] PRIMARY KEY CLUSTERED ([ConfigId] ASC)
    );

    -- 添加索引
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_config_SessionId] ON [dbo].[mdl_production_session_config] ([SessionId]);
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_config_ConfigType] ON [dbo].[mdl_production_session_config] ([ConfigType]);
    CREATE NONCLUSTERED INDEX [IX_mdl_production_session_config_ConfigKey] ON [dbo].[mdl_production_session_config] ([ConfigKey]);

    -- 添加外键约束
    ALTER TABLE [dbo].[mdl_production_session_config] WITH CHECK ADD CONSTRAINT [FK_mdl_production_session_config_mdl_production_session] FOREIGN KEY([SessionId])
    REFERENCES [dbo].[mdl_production_session] ([SessionId])
    ON DELETE CASCADE;

    PRINT '创建表 mdl_production_session_config 成功';
END
ELSE
BEGIN
    PRINT '表 mdl_production_session_config 已存在，跳过创建';
END

-- =============================================
-- 添加表注释
-- =============================================
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mdl_production_session]') AND type in (N'U'))
BEGIN
    EXEC sp_addextendedproperty 
        @name = N'MS_Description', 
        @value = N'产品会话表，记录每个产品的装配过程信息',
        @level0type = N'SCHEMA', @level0name = N'dbo',
        @level1type = N'TABLE', @level1name = N'mdl_production_session';
END

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mdl_production_session_event]') AND type in (N'U'))
BEGIN
    EXEC sp_addextendedproperty 
        @name = N'MS_Description', 
        @value = N'产品会话事件表，记录会话过程中的关键事件',
        @level0type = N'SCHEMA', @level0name = N'dbo',
        @level1type = N'TABLE', @level1name = N'mdl_production_session_event';
END

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mdl_production_session_snapshot]') AND type in (N'U'))
BEGIN
    EXEC sp_addextendedproperty 
        @name = N'MS_Description', 
        @value = N'产品会话快照表，保存会话的详细状态快照数据',
        @level0type = N'SCHEMA', @level0name = N'dbo',
        @level1type = N'TABLE', @level1name = N'mdl_production_session_snapshot';
END

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[mdl_production_session_config]') AND type in (N'U'))
BEGIN
    EXEC sp_addextendedproperty 
        @name = N'MS_Description', 
        @value = N'产品会话配置表，存储会话相关的配置信息',
        @level0type = N'SCHEMA', @level0name = N'dbo',
        @level1type = N'TABLE', @level1name = N'mdl_production_session_config';
END

-- =============================================
-- 创建视图：会话统计视图
-- =============================================
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vw_production_session_statistics]'))
BEGIN
    EXEC (N'
    CREATE VIEW [dbo].[vw_production_session_statistics] AS
    SELECT 
        s.ProductTypeCode,
        s.ProductTypeName,
        s.StationId,
        s.StationName,
        s.OperatorId,
        s.OperatorName,
        s.ShiftId,
        s.ShiftName,
        CAST(s.StartedAtUtc AS DATE) AS SessionDate,
        COUNT(*) AS TotalSessions,
        SUM(CASE WHEN s.Result = 1 THEN 1 ELSE 0 END) AS OkSessions,
        SUM(CASE WHEN s.Result = 2 THEN 1 ELSE 0 END) AS NgSessions,
        SUM(CASE WHEN s.Status = 0 THEN 1 ELSE 0 END) AS InProgressSessions,
        SUM(CASE WHEN s.Status = 3 THEN 1 ELSE 0 END) AS CancelledSessions,
        SUM(CASE WHEN s.Status = 4 THEN 1 ELSE 0 END) AS PausedSessions,
        CAST(CASE WHEN COUNT(*) > 0 THEN SUM(CASE WHEN s.Result = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) ELSE 0 END AS DECIMAL(5,2)) AS PassRate,
        AVG(CASE WHEN s.EndedAtUtc IS NOT NULL THEN DATEDIFF(SECOND, s.StartedAtUtc, s.EndedAtUtc) END) AS AvgProcessingTimeSeconds
    FROM [dbo].[mdl_production_session] s
    GROUP BY 
        s.ProductTypeCode, s.ProductTypeName,
        s.StationId, s.StationName,
        s.OperatorId, s.OperatorName,
        s.ShiftId, s.ShiftName,
        CAST(s.StartedAtUtc AS DATE)
    ');

    PRINT '创建视图 vw_production_session_statistics 成功';
END
ELSE
BEGIN
    PRINT '视图 vw_production_session_statistics 已存在，跳过创建';
END

-- =============================================
-- 创建存储过程：清理过期会话
-- =============================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_cleanup_expired_sessions]') AND type in (N'P', N'PC'))
BEGIN
    EXEC (N'
    CREATE PROCEDURE [dbo].[sp_cleanup_expired_sessions]
        @RetentionDays INT = 30,
        @CleanedCount INT OUTPUT,
        @FreedSpaceBytes BIGINT OUTPUT
    AS
    BEGIN
        SET NOCOUNT ON;
        
        DECLARE @CutoffDate DATETIME2 = DATEADD(DAY, -@RetentionDays, SYSUTCDATETIME());
        DECLARE @DeletedCount INT = 0;
        
        -- 计算要删除的会话数量
        SELECT @DeletedCount = COUNT(*) 
        FROM [dbo].[mdl_production_session] 
        WHERE EndedAtUtc < @CutoffDate;
        
        -- 删除过期会话（级联删除相关事件和快照）
        DELETE FROM [dbo].[mdl_production_session] 
        WHERE EndedAtUtc < @CutoffDate;
        
        -- 返回结果
        SET @CleanedCount = @DeletedCount;
        SET @FreedSpaceBytes = @DeletedCount * 1024; -- 假设每个会话占用1KB
        
        RETURN 0;
    END
    ');

    PRINT '创建存储过程 sp_cleanup_expired_sessions 成功';
END
ELSE
BEGIN
    PRINT '存储过程 sp_cleanup_expired_sessions 已存在，跳过创建';
END

-- =============================================
-- 创建存储过程：获取会话统计信息
-- =============================================
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_get_session_statistics]') AND type in (N'P', N'PC'))
BEGIN
    EXEC (N'
    CREATE PROCEDURE [dbo].[sp_get_session_statistics]
        @StartTime DATETIME2,
        @EndTime DATETIME2,
        @ProductTypeCode NVARCHAR(50) = NULL,
        @StationId NVARCHAR(50) = NULL,
        @OperatorId NVARCHAR(50) = NULL,
        @ShiftId NVARCHAR(50) = NULL
    AS
    BEGIN
        SET NOCOUNT ON;
        
        SELECT 
            COUNT(*) AS TotalSessions,
            SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) AS OkSessions,
            SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS NgSessions,
            SUM(CASE WHEN Status = 0 THEN 1 ELSE 0 END) AS InProgressSessions,
            SUM(CASE WHEN Status = 3 THEN 1 ELSE 0 END) AS CancelledSessions,
            SUM(CASE WHEN Status = 4 THEN 1 ELSE 0 END) AS PausedSessions,
            CAST(CASE WHEN COUNT(*) > 0 THEN SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) ELSE 0 END AS DECIMAL(5,2)) AS PassRate,
            AVG(CASE WHEN EndedAtUtc IS NOT NULL THEN DATEDIFF(SECOND, StartedAtUtc, EndedAtUtc) END) AS AvgProcessingTimeSeconds,
            COUNT(*) / NULLIF(SUM(DATEDIFF(HOUR, StartedAtUtc, EndedAtUtc)), 0) AS ThroughputPerHour
        FROM [dbo].[mdl_production_session]
        WHERE StartedAtUtc >= @StartTime 
          AND StartedAtUtc <= @EndTime
          AND (@ProductTypeCode IS NULL OR ProductTypeCode = @ProductTypeCode)
          AND (@StationId IS NULL OR StationId = @StationId)
          AND (@OperatorId IS NULL OR OperatorId = @OperatorId)
          AND (@ShiftId IS NULL OR ShiftId = @ShiftId);
        
        -- 按产品类型分组统计
        SELECT 
            ProductTypeCode,
            ProductTypeName,
            COUNT(*) AS TotalSessions,
            SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) AS OkSessions,
            SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS NgSessions,
            CAST(CASE WHEN COUNT(*) > 0 THEN SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) ELSE 0 END AS DECIMAL(5,2)) AS PassRate
        FROM [dbo].[mdl_production_session]
        WHERE StartedAtUtc >= @StartTime 
          AND StartedAtUtc <= @EndTime
          AND (@ProductTypeCode IS NULL OR ProductTypeCode = @ProductTypeCode)
          AND (@StationId IS NULL OR StationId = @StationId)
          AND (@OperatorId IS NULL OR OperatorId = @OperatorId)
          AND (@ShiftId IS NULL OR ShiftId = @ShiftId)
        GROUP BY ProductTypeCode, ProductTypeName
        ORDER BY TotalSessions DESC;
        
        -- 按工位分组统计
        SELECT 
            StationId,
            StationName,
            COUNT(*) AS TotalSessions,
            SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) AS OkSessions,
            SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS NgSessions,
            CAST(CASE WHEN COUNT(*) > 0 THEN SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) ELSE 0 END AS DECIMAL(5,2)) AS PassRate,
            COUNT(*) / NULLIF(AVG(DATEDIFF(HOUR, StartedAtUtc, EndedAtUtc)), 0) AS ThroughputPerHour
        FROM [dbo].[mdl_production_session]
        WHERE StartedAtUtc >= @StartTime 
          AND StartedAtUtc <= @EndTime
          AND (@ProductTypeCode IS NULL OR ProductTypeCode = @ProductTypeCode)
          AND (@StationId IS NULL OR StationId = @StationId)
          AND (@OperatorId IS NULL OR OperatorId = @OperatorId)
          AND (@ShiftId IS NULL OR ShiftId = @ShiftId)
          AND EndedAtUtc IS NOT NULL
        GROUP BY StationId, StationName
        ORDER BY TotalSessions DESC;
        
        -- 按操作员分组统计
        SELECT 
            OperatorId,
            OperatorName,
            COUNT(*) AS TotalSessions,
            SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) AS OkSessions,
            SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS NgSessions,
            CAST(CASE WHEN COUNT(*) > 0 THEN SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) ELSE 0 END AS DECIMAL(5,2)) AS PassRate,
            AVG(DATEDIFF(SECOND, StartedAtUtc, EndedAtUtc)) AS AvgProcessingTimeSeconds
        FROM [dbo].[mdl_production_session]
        WHERE StartedAtUtc >= @StartTime 
          AND StartedAtUtc <= @EndTime
          AND (@ProductTypeCode IS NULL OR ProductTypeCode = @ProductTypeCode)
          AND (@StationId IS NULL OR StationId = @StationId)
          AND (@OperatorId IS NULL OR OperatorId = @OperatorId)
          AND (@ShiftId IS NULL OR ShiftId = @ShiftId)
          AND EndedAtUtc IS NOT NULL
        GROUP BY OperatorId, OperatorName
        ORDER BY TotalSessions DESC;
        
        -- 按日期分组统计
        SELECT 
            CAST(StartedAtUtc AS DATE) AS SessionDate,
            COUNT(*) AS TotalSessions,
            SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) AS OkSessions,
            SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS NgSessions,
            CAST(CASE WHEN COUNT(*) > 0 THEN SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) ELSE 0 END AS DECIMAL(5,2)) AS PassRate,
            COUNT(*) / NULLIF(AVG(DATEDIFF(HOUR, StartedAtUtc, EndedAtUtc)), 0) AS ThroughputPerHour
        FROM [dbo].[mdl_production_session]
        WHERE StartedAtUtc >= @StartTime 
          AND StartedAtUtc <= @EndTime
          AND (@ProductTypeCode IS NULL OR ProductTypeCode = @ProductTypeCode)
          AND (@StationId IS NULL OR StationId = @StationId)
          AND (@OperatorId IS NULL OR OperatorId = @OperatorId)
          AND (@ShiftId IS NULL OR ShiftId = @ShiftId)
          AND EndedAtUtc IS NOT NULL
        GROUP BY CAST(StartedAtUtc AS DATE)
        ORDER BY SessionDate;
        
        -- 按班次分组统计
        SELECT 
            ShiftId,
            ShiftName,
            COUNT(*) AS TotalSessions,
            SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) AS OkSessions,
            SUM(CASE WHEN Result = 2 THEN 1 ELSE 0 END) AS NgSessions,
            CAST(CASE WHEN COUNT(*) > 0 THEN SUM(CASE WHEN Result = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) ELSE 0 END AS DECIMAL(5,2)) AS PassRate,
            COUNT(*) / NULLIF(AVG(DATEDIFF(HOUR, StartedAtUtc, EndedAtUtc)), 0) AS ThroughputPerHour
        FROM [dbo].[mdl_production_session]
        WHERE StartedAtUtc >= @StartTime 
          AND StartedAtUtc <= @EndTime
          AND (@ProductTypeCode IS NULL OR ProductTypeCode = @ProductTypeCode)
          AND (@StationId IS NULL OR StationId = @StationId)
          AND (@OperatorId IS NULL OR OperatorId = @OperatorId)
          AND (@ShiftId IS NULL OR ShiftId = @ShiftId)
          AND EndedAtUtc IS NOT NULL
        GROUP BY ShiftId, ShiftName
        ORDER BY TotalSessions DESC;
    END
    ');

    PRINT '创建存储过程 sp_get_session_statistics 成功';
END
ELSE
BEGIN
    PRINT '存储过程 sp_get_session_statistics 已存在，跳过创建';
END

PRINT '产品会话管理相关表结构创建完成';