-- =============================================
-- Create Views for Import Data Analysis
-- With proper indexing hints and performance optimization
-- Run this script FOURTH (after all tables, indexes, and procedures are created)
-- =============================================

-- View for import session overview (with computed columns)
CREATE VIEW vw_ImportSessionOverview 
WITH SCHEMABINDING
AS
SELECT 
    s.Id,
    s.CreatedBy,
    s.CreatedAt,
    s.Status,
    s.TotalRows,
    s.ValidRows,
    s.ErrorRows,
    s.ProcessingTime,
    s.FileName,
    s.FileSize,
    s.ImportMode,
    s.CompletedAt,
    -- Calculated fields
    CASE 
        WHEN s.TotalRows > 0 THEN 
            CAST(CAST(s.ValidRows AS FLOAT) / CAST(s.TotalRows AS FLOAT) * 100 AS DECIMAL(5,2))
        ELSE 0 
    END as SuccessRate,
    -- Processing speed (rows per second)
    CASE 
        WHEN s.ProcessingTime > 0 THEN 
            CAST(CAST(s.ValidRows AS FLOAT) / CAST(s.ProcessingTime AS FLOAT) * 1000 AS DECIMAL(10,2))
        ELSE 0 
    END as RowsPerSecond,
    -- Duration
    CASE 
        WHEN s.CompletedAt IS NOT NULL THEN 
            DATEDIFF(MILLISECOND, s.CreatedAt, s.CompletedAt)
        ELSE NULL 
    END as DurationMs
FROM dbo.sdsImportSessions s;
GO

-- Create unique clustered index for the view
CREATE UNIQUE CLUSTERED INDEX IX_vw_ImportSessionOverview_Id 
    ON vw_ImportSessionOverview(Id);
GO

-- View for recent import errors (simplified for indexing)
CREATE VIEW vw_RecentImportErrors 
WITH SCHEMABINDING
AS
SELECT 
    s.Id as SessionId,
    s.CreatedBy,
    s.CreatedAt,
    s.FileName,
    e.ErrorType,
    e.ErrorMessage,
    e.RowNumber,
    e.FieldName,
    e.Severity,
    e.CreatedAt as ErrorTime
FROM dbo.sdsImportSessions s
INNER JOIN dbo.sdsImportErrorLogs e ON s.Id = e.SessionId
WHERE e.Severity IN ('Error', 'Critical');
GO

-- Create unique clustered index for the view
CREATE UNIQUE CLUSTERED INDEX IX_vw_RecentImportErrors_SessionId_RowNumber 
    ON vw_RecentImportErrors(SessionId, RowNumber);
GO

-- Create non-clustered index for the view
CREATE NONCLUSTERED INDEX IX_vw_RecentImportErrors_ErrorTime 
    ON vw_RecentImportErrors(ErrorTime) 
    INCLUDE (SessionId, ErrorType, Severity);
GO

-- View for import statistics (simplified for indexing)
CREATE VIEW vw_ImportStatistics 
WITH SCHEMABINDING
AS
SELECT 
    CAST(CreatedAt AS DATE) as ImportDate,
    COUNT_BIG(*) as TotalImports,
    SUM(TotalRows) as TotalRowsProcessed,
    SUM(ValidRows) as TotalRowsImported,
    SUM(ErrorRows) as TotalRowsWithErrors,
    SUM(CAST(ISNULL(ProcessingTime, 0) AS BIGINT)) as TotalProcessingTimeMs
FROM dbo.sdsImportSessions
WHERE Status = 'Completed'
GROUP BY CAST(CreatedAt AS DATE);
GO

-- Create index for the view
CREATE UNIQUE CLUSTERED INDEX IX_vw_ImportStatistics_ImportDate 
    ON vw_ImportStatistics(ImportDate);
GO

PRINT 'Performance views created successfully!';
PRINT 'Views created: vw_ImportSessionOverview, vw_RecentImportErrors, vw_ImportStatistics';
PRINT 'Clustered indexes created for view optimization';
