-- =============================================
-- Create Import Tables for Shipment Import System
-- Production Ready with Proper Constraints
-- Run this script FIRST
-- =============================================

-- Import Sessions table - tracks each import operation
CREATE TABLE sdsImportSessions (
    Id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    CreatedBy NVARCHAR(100) NOT NULL,
    CreatedAt DATETIME2(7) NOT NULL DEFAULT GETUTCDATE(),
    Status NVARCHAR(20) NOT NULL DEFAULT 'Pending',
    TotalRows INT NOT NULL CHECK (TotalRows >= 0),
    ValidRows INT NOT NULL DEFAULT 0 CHECK (ValidRows >= 0),
    ErrorRows INT NOT NULL DEFAULT 0 CHECK (ErrorRows >= 0),
    ProcessingTime INT NULL CHECK (ProcessingTime >= 0),
    FileName NVARCHAR(255) NULL,
    FileSize BIGINT NULL CHECK (FileSize >= 0),
    ImportMode NVARCHAR(20) NULL CHECK (ImportMode IN ('UpdateExisting', 'AddNewOnly', 'ReplaceAll')),
    Notes NVARCHAR(500) NULL,
    CompletedAt DATETIME2(7) NULL,
    -- Ensure data consistency
    CONSTRAINT CK_sdsImportSessions_RowCounts CHECK (ValidRows + ErrorRows <= TotalRows),
    CONSTRAINT CK_sdsImportSessions_ProcessingTime CHECK (ProcessingTime IS NULL OR ProcessingTime >= 0)
);

-- Staging Shipments table - holds validated data before import
CREATE TABLE sdsStagingShipments (
    Id BIGINT IDENTITY(1,1) PRIMARY KEY,
    SessionId UNIQUEIDENTIFIER NOT NULL,
    RowNumber INT NOT NULL CHECK (RowNumber > 0),
    SupplierName NVARCHAR(200) NOT NULL,
    IndentNumber NVARCHAR(100) NOT NULL,
    ShipshapeNumber NVARCHAR(100) NULL,
    MotherVessel NVARCHAR(200) NULL,
    ETA NVARCHAR(20) NULL,
    BillOfEntryNumber NVARCHAR(100) NULL,
    ContainerNumber NVARCHAR(100) NULL,
    OffloadingPort NVARCHAR(100) NULL,
    ContainerReceived NVARCHAR(20) NULL,
    NoOfFlatcases NVARCHAR(50) NULL,
    Voyage NVARCHAR(100) NULL,
    MBLNumber NVARCHAR(100) NULL,
    RedirectToSACD NVARCHAR(20) NULL,
    IsUpdate BIT NOT NULL DEFAULT 0,
    Status NVARCHAR(20) NOT NULL DEFAULT 'Pending' 
        CHECK (Status IN ('Pending', 'Validated', 'Imported', 'Error')),
    ErrorMessage NVARCHAR(500) NULL,
    ValidationErrors NVARCHAR(MAX) NULL,
    ProcessedAt DATETIME2(7) NULL,
    -- Foreign key with proper cascade
    CONSTRAINT FK_sdsStagingShipments_sdsImportSessions 
        FOREIGN KEY (SessionId) REFERENCES sdsImportSessions(Id) 
        ON DELETE CASCADE ON UPDATE CASCADE
);

-- Import Error Log table - comprehensive error tracking
CREATE TABLE sdsImportErrorLogs (
    Id BIGINT IDENTITY(1,1) PRIMARY KEY,
    SessionId UNIQUEIDENTIFIER NULL,
    ErrorType NVARCHAR(50) NOT NULL 
        CHECK (ErrorType IN ('File', 'Validation', 'Import', 'System')),
    ErrorMessage NVARCHAR(500) NOT NULL,
    ErrorDetails NVARCHAR(MAX) NULL,
    RowNumber INT NULL CHECK (RowNumber IS NULL OR RowNumber > 0),
    FieldName NVARCHAR(100) NULL,
    Severity NVARCHAR(20) NOT NULL DEFAULT 'Error' 
        CHECK (Severity IN ('Info', 'Warning', 'Error', 'Critical')),
    CreatedAt DATETIME2(7) NOT NULL DEFAULT GETUTCDATE(),
    -- Foreign key with proper cascade
    CONSTRAINT FK_sdsImportErrorLogs_sdsImportSessions 
        FOREIGN KEY (SessionId) REFERENCES sdsImportSessions(Id) 
        ON DELETE SET NULL ON UPDATE CASCADE
);

-- Add proper documentation
EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'Import session tracking table with business rule constraints', 
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE', @level1name = N'sdsImportSessions';

EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'Staging table for shipment data with validation constraints', 
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE', @level1name = N'sdsStagingShipments';

EXEC sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'Error logging for import operations with severity levels', 
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE', @level1name = N'sdsImportErrorLogs';

PRINT 'Import tables created successfully!';
PRINT 'Tables created: sdsImportSessions, sdsStagingShipments, sdsImportErrorLogs';
