-- =============================================
-- Create Secure Stored Procedures for Import Operations
-- With proper error handling and validation
-- Run this script THIRD (after tables and indexes are created)
-- =============================================

-- Procedure to get import session summary (optimized)
CREATE PROCEDURE sp_GetImportSessionSummary
    @SessionId UNIQUEIDENTIFIER
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Input validation
    IF @SessionId IS NULL
    BEGIN
        RAISERROR('SessionId cannot be NULL', 16, 1);
        RETURN;
    END
    
    BEGIN TRY
        -- Single query with proper aggregation
        SELECT 
            s.Id,
            s.CreatedBy,
            s.CreatedAt,
            s.Status,
            s.TotalRows,
            s.ValidRows,
            s.ErrorRows,
            s.ProcessingTime,
            s.FileName,
            s.FileSize,
            s.ImportMode,
            s.Notes,
            s.CompletedAt,
            -- Efficient aggregation using CTE
            COALESCE(stats.PendingCount, 0) as PendingCount,
            COALESCE(stats.ValidatedCount, 0) as ValidatedCount,
            COALESCE(stats.ImportedCount, 0) as ImportedCount,
            COALESCE(stats.ErrorCount, 0) as ErrorCount
        FROM sdsImportSessions s
        OUTER APPLY (
            SELECT 
                SUM(CASE WHEN Status = 'Pending' THEN 1 ELSE 0 END) as PendingCount,
                SUM(CASE WHEN Status = 'Validated' THEN 1 ELSE 0 END) as ValidatedCount,
                SUM(CASE WHEN Status = 'Imported' THEN 1 ELSE 0 END) as ImportedCount,
                SUM(CASE WHEN Status = 'Error' THEN 1 ELSE 0 END) as ErrorCount
            FROM sdsStagingShipments 
            WHERE SessionId = s.Id
        ) stats
        WHERE s.Id = @SessionId;
        
        IF @@ROWCOUNT = 0
        BEGIN
            DECLARE @SessionIdStr NVARCHAR(36) = CAST(@SessionId AS NVARCHAR(36));
            RAISERROR('Import session not found: %s', 16, 1, @SessionIdStr);
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();
        
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END
GO

-- Procedure to get staging data for preview (paginated)
CREATE PROCEDURE sp_GetStagingDataForPreview
    @SessionId UNIQUEIDENTIFIER,
    @PageNumber INT = 1,
    @PageSize INT = 50
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Input validation
    IF @SessionId IS NULL
    BEGIN
        RAISERROR('SessionId cannot be NULL', 16, 1);
        RETURN;
    END
    
    IF @PageNumber < 1 OR @PageSize < 1 OR @PageSize > 1000
    BEGIN
        RAISERROR('Invalid pagination parameters. PageNumber >= 1, PageSize between 1 and 1000', 16, 1);
        RETURN;
    END
    
    BEGIN TRY
        -- Efficient pagination with proper ordering
        SELECT 
            ss.Id,
            ss.RowNumber,
            ss.SupplierName,
            ss.IndentNumber,
            ss.ShipshapeNumber,
            ss.MotherVessel,
            ss.ETA,
            ss.BillOfEntryNumber,
            ss.ContainerNumber,
            ss.OffloadingPort,
            ss.ContainerReceived,
            ss.NoOfFlatcases,
            ss.Voyage,
            ss.MBLNumber,
            ss.RedirectToSACD,
            ss.IsUpdate,
            ss.Status,
            ss.ErrorMessage,
            ss.ValidationErrors,
            -- Pagination metadata
            COUNT(*) OVER() as TotalRows,
            @PageNumber as CurrentPage,
            @PageSize as PageSize
        FROM sdsStagingShipments ss
        WHERE ss.SessionId = @SessionId
        ORDER BY ss.RowNumber
        OFFSET (@PageNumber - 1) * @PageSize ROWS
        FETCH NEXT @PageSize ROWS ONLY;
        
        IF @@ROWCOUNT = 0 AND @PageNumber = 1
        BEGIN
            DECLARE @SessionIdStr2 NVARCHAR(36) = CAST(@SessionId AS NVARCHAR(36));
            RAISERROR('No staging data found for session: %s', 16, 1, @SessionIdStr2);
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();
        
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END
GO

-- Procedure to get validation errors summary (optimized)
CREATE PROCEDURE sp_GetImportValidationErrors
    @SessionId UNIQUEIDENTIFIER
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Input validation
    IF @SessionId IS NULL
    BEGIN
        RAISERROR('SessionId cannot be NULL', 16, 1);
        RETURN;
    END
    
    BEGIN TRY
        SELECT 
            ErrorType,
            Severity,
            COUNT(*) as ErrorCount,
            MIN(ErrorMessage) as SampleErrorMessage,
            MIN(CreatedAt) as FirstOccurrence,
            MAX(CreatedAt) as LastOccurrence
        FROM sdsImportErrorLogs
        WHERE SessionId = @SessionId
        GROUP BY ErrorType, Severity
        ORDER BY ErrorCount DESC, Severity DESC;
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();
        
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END
GO

-- Procedure to clean up old import sessions (safe)
CREATE PROCEDURE sp_CleanupOldImportSessions
    @DaysToKeep INT = 30,
    @MaxSessionsToDelete INT = 1000, -- Safety limit
    @DryRun BIT = 1 -- Default to dry run
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Input validation
    IF @DaysToKeep < 1 OR @DaysToKeep > 365
    BEGIN
        RAISERROR('DaysToKeep must be between 1 and 365', 16, 1);
        RETURN;
    END
    
    IF @MaxSessionsToDelete < 1 OR @MaxSessionsToDelete > 10000
    BEGIN
        RAISERROR('MaxSessionsToDelete must be between 1 and 10000', 16, 1);
        RETURN;
    END
    
    BEGIN TRY
        DECLARE @CutoffDate DATETIME2(7) = DATEADD(DAY, -@DaysToKeep, GETUTCDATE());
        DECLARE @SessionsToDelete TABLE (Id UNIQUEIDENTIFIER);
        
        -- Identify sessions to delete (with safety limit)
        INSERT INTO @SessionsToDelete (Id)
        SELECT TOP (@MaxSessionsToDelete) Id
        FROM sdsImportSessions 
        WHERE CreatedAt < @CutoffDate
        AND Status IN ('Completed', 'Failed') -- Only delete finished sessions
        ORDER BY CreatedAt ASC;
        
        DECLARE @SessionCount INT = (SELECT COUNT(*) FROM @SessionsToDelete);
        
        IF @DryRun = 1
        BEGIN
            -- Dry run - just show what would be deleted
            SELECT 
                'DRY RUN - Would delete' as Action,
                @SessionCount as SessionsToDelete,
                @CutoffDate as CutoffDate,
                @DaysToKeep as DaysToKeep;
                
            SELECT 
                s.Id, s.CreatedBy, s.CreatedAt, s.Status, s.TotalRows
            FROM sdsImportSessions s
            INNER JOIN @SessionsToDelete d ON s.Id = d.Id;
        END
        ELSE
        BEGIN
            -- Actual deletion with transaction
            BEGIN TRANSACTION;
            
            BEGIN TRY
                -- Delete in proper order (respecting foreign keys)
                DELETE FROM sdsImportErrorLogs 
                WHERE SessionId IN (SELECT Id FROM @SessionsToDelete);
                
                DELETE FROM sdsStagingShipments 
                WHERE SessionId IN (SELECT Id FROM @SessionsToDelete);
                
                DELETE FROM sdsImportSessions 
                WHERE Id IN (SELECT Id FROM @SessionsToDelete);
                
                COMMIT TRANSACTION;
                
                SELECT 
                    'SUCCESS - Deleted' as Action,
                    @SessionCount as SessionsDeleted,
                    @CutoffDate as CutoffDate;
            END TRY
            BEGIN CATCH
                ROLLBACK TRANSACTION;
                THROW;
            END CATCH
        END
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
        DECLARE @ErrorState INT = ERROR_STATE();
        
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END
GO

PRINT 'Stored procedures created successfully!';
PRINT 'Procedures created: sp_GetImportSessionSummary, sp_GetStagingDataForPreview, sp_GetImportValidationErrors, sp_CleanupOldImportSessions';
