-- =============================================
-- Create Performance Indexes for Import Tables
-- Optimized for common query patterns
-- Run this script SECOND (after tables are created)
-- =============================================

-- ImportSessions table indexes
CREATE NONCLUSTERED INDEX IX_sdsImportSessions_Status_CreatedAt 
    ON sdsImportSessions(Status, CreatedAt) 
    INCLUDE (TotalRows, ValidRows, ErrorRows);

CREATE NONCLUSTERED INDEX IX_sdsImportSessions_CreatedBy_CreatedAt 
    ON sdsImportSessions(CreatedBy, CreatedAt) 
    INCLUDE (Status, TotalRows);

-- StagingShipments table indexes
CREATE NONCLUSTERED INDEX IX_sdsStagingShipments_SessionId_Status 
    ON sdsStagingShipments(SessionId, Status) 
    INCLUDE (RowNumber, IndentNumber, SupplierName);

CREATE NONCLUSTERED INDEX IX_sdsStagingShipments_IndentNumber 
    ON sdsStagingShipments(IndentNumber) 
    WHERE IndentNumber IS NOT NULL;

CREATE NONCLUSTERED INDEX IX_sdsStagingShipments_RowNumber_SessionId 
    ON sdsStagingShipments(RowNumber, SessionId) 
    INCLUDE (Status, ErrorMessage);

-- ImportErrorLogs table indexes
CREATE NONCLUSTERED INDEX IX_sdsImportErrorLogs_SessionId_ErrorType 
    ON sdsImportErrorLogs(SessionId, ErrorType) 
    INCLUDE (Severity, CreatedAt);

CREATE NONCLUSTERED INDEX IX_sdsImportErrorLogs_Severity_CreatedAt 
    ON sdsImportErrorLogs(Severity, CreatedAt) 
    INCLUDE (SessionId, ErrorType, ErrorMessage);

-- Filtered indexes for better performance
CREATE NONCLUSTERED INDEX IX_sdsImportErrorLogs_ActiveErrors 
    ON sdsImportErrorLogs(SessionId, RowNumber) 
    WHERE Severity IN ('Error', 'Critical');

-- Statistics for query optimization
CREATE STATISTICS ST_sdsImportSessions_Status_TotalRows 
    ON sdsImportSessions(Status, TotalRows);

CREATE STATISTICS ST_sdsStagingShipments_SessionId_Status 
    ON sdsStagingShipments(SessionId, Status);

PRINT 'Performance indexes created successfully!';
PRINT 'Indexes created for sdsImportSessions, sdsStagingShipments, and sdsImportErrorLogs';
PRINT 'Statistics created for query optimization';
