using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text.Json;
using System.Threading.Tasks;
using Microsoft.EntityFrameworkCore;
using OfficeOpenXml;
using rgbc_sds.dal.DB;
using rgbc_sds.services.Enums;
using rgbc_sds.services.Models;

namespace rgbc_sds.services.Services
{
    /// <summary>
    /// Service for handling shipment import operations
    /// </summary>
    public class ImportService : IImportService
    {
        private readonly rgbc_sdsContext _context;
        private readonly ImportConfiguration _config;

        public ImportService(rgbc_sdsContext context)
        {
            _context = context;
            _config = new ImportConfiguration();
            
            // Set EPPlus license context
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        }

        /// <summary>
        /// Creates a new import session and validates the uploaded file
        /// </summary>
        public async Task<ImportSession> CreateImportSessionAsync(
            Stream fileStream, 
            string fileName, 
            long fileSize, 
            string importMode, 
            string createdBy)
        {
            var stopwatch = Stopwatch.StartNew();
            var session = new ImportSession
            {
                FileName = fileName,
                FileSize = fileSize,
                ImportMode = importMode,
                CreatedBy = createdBy,
                TotalRows = 0,  // Force EF to include this in INSERT
                ValidRows = 0,   // Force EF to include this in INSERT
                ErrorRows = 0    // Force EF to include this in INSERT
            };

            try
            {
                // Validate file
                await ValidateFileAsync(fileName, fileSize);

                // Process Excel file
                var excelData = await ProcessExcelFileAsync(fileStream);
                Console.WriteLine($"DEBUG: Excel file processed - Headers: {excelData.Headers.Count}, Rows: {excelData.Rows.Count}");
                
                // Validate column headers
                var headerValidation = ColumnMapping.ValidateHeaders(excelData.Headers);
                if (!headerValidation.IsValid)
                {
                    var error = ImportErrorLog.CreateFileError(
                        headerValidation.ErrorMessage,
                        $"Found headers: {string.Join(", ", headerValidation.FoundHeaders)}",
                        ErrorSeverity.Critical);
                    error.SetSession(session.Id);
                    session.ImportErrorLogs.Add(error);
                    session.Status = ImportStatus.Failed;
                    session.ErrorRows = 1;
                    session.TotalRows = 0;
                    return session;
                }

                // Validate row count
                if (excelData.Rows.Count > ImportConfiguration.MaxRecordsPerImport)
                {
                    var error = ImportErrorLog.CreateFileError(
                        $"File contains {excelData.Rows.Count} records, maximum allowed is {ImportConfiguration.MaxRecordsPerImport}",
                        null,
                        ErrorSeverity.Critical);
                    error.SetSession(session.Id);
                    session.ImportErrorLogs.Add(error);
                    session.Status = ImportStatus.Failed;
                    session.ErrorRows = 1;
                    session.TotalRows = excelData.Rows.Count;
                    return session;
                }

                // Create staging records
                var stagingRecords = await CreateStagingRecordsAsync(excelData.Rows, session.Id);
                Console.WriteLine($"DEBUG: Staging records created: {stagingRecords.Count}");
                
                // Validate staging records
                var validationResult = await ValidateStagingRecordsAsync(stagingRecords);
                Console.WriteLine($"DEBUG: Validation completed - Valid: {validationResult.ValidCount}, Errors: {validationResult.ErrorCount}");
                
                // Calculate row counts to ensure they satisfy the CHECK constraint
                var totalRows = excelData.Rows.Count;
                var validRows = validationResult.ValidCount;
                var errorRows = validationResult.ErrorCount;
                
                // Ensure the constraint: (ValidRows + ErrorRows) <= TotalRows
                // If we have more processed rows than total rows, adjust totalRows
                var processedRows = validRows + errorRows;
                if (processedRows > totalRows)
                {
                    totalRows = processedRows;
                }
                
                // Additional safety check: ensure all values are at least 0
                totalRows = Math.Max(0, totalRows);
                validRows = Math.Max(0, validRows);
                errorRows = Math.Max(0, errorRows);
                
                // Update session with validated results BEFORE saving to database
                session.TotalRows = totalRows;
                session.ValidRows = validRows;
                session.ErrorRows = errorRows;
                session.Status = validationResult.ErrorCount == 0 ? ImportStatus.Pending : ImportStatus.Failed;
                
                // Force EF to track these properties as modified
                _context.Entry(session).Property(s => s.TotalRows).IsModified = true;
                _context.Entry(session).Property(s => s.ValidRows).IsModified = true;
                _context.Entry(session).Property(s => s.ErrorRows).IsModified = true;
                
                // Debug logging to see what values we're sending
                Console.WriteLine($"DEBUG: Row Counts - Total: {totalRows}, Valid: {validRows}, Error: {errorRows}");
                Console.WriteLine($"DEBUG: Constraint Check - (Valid + Error) = {validRows + errorRows} <= {totalRows} = {(validRows + errorRows) <= totalRows}");
                
                // Add staging records and error logs
                ((List<StagingShipment>)session.StagingShipments).AddRange(stagingRecords);
                ((List<ImportErrorLog>)session.ImportErrorLogs).AddRange(validationResult.Errors);

                Console.WriteLine($"DEBUG: About to save to database - Session ID: {session.Id}");
                Console.WriteLine($"DEBUG: Staging records count: {session.StagingShipments.Count}");
                Console.WriteLine($"DEBUG: Error logs count: {session.ImportErrorLogs.Count}");

                // Save to database AFTER all properties are set and validated
                _context.ImportSessions.Add(session);
                Console.WriteLine($"DEBUG: Session added to context, calling SaveChanges...");
                await _context.SaveChangesAsync();
                Console.WriteLine($"DEBUG: SaveChanges completed successfully!");

                stopwatch.Stop();
                session.ProcessingTime = (int)stopwatch.ElapsedMilliseconds;
                await _context.SaveChangesAsync();

                return session;
            }
            catch (Exception ex)
            {
                // Log system error
                var systemError = ImportErrorLog.CreateSystemError(
                    "Failed to create import session",
                    ex.ToString(),
                    ErrorSeverity.Critical);
                systemError.SetSession(session.Id);
                session.ImportErrorLogs.Add(systemError);
                session.Status = ImportStatus.Failed;
                session.ErrorRows = 1;
                session.TotalRows = 0;

                // Save error information
                _context.ImportSessions.Add(session);
                await _context.SaveChangesAsync();

                throw;
            }
        }

        /// <summary>
        /// Gets a summary of the import session
        /// </summary>
        public async Task<ImportSession> GetImportSessionAsync(Guid sessionId)
        {
            return await _context.ImportSessions
                .Include(s => s.StagingShipments)
                .Include(s => s.ImportErrorLogs)
                .FirstOrDefaultAsync(s => s.Id == sessionId)
                ?? throw new ArgumentException($"Import session not found: {sessionId}");
        }

        /// <summary>
        /// Gets staging data for preview before import
        /// </summary>
        public async Task<(List<StagingShipment> Data, int TotalCount)> GetStagingDataForPreviewAsync(
            Guid sessionId, 
            int pageNumber = 1, 
            int pageSize = 50)
        {
            var query = _context.StagingShipments
                .Where(s => s.SessionId == sessionId)
                .OrderBy(s => s.RowNumber);

            var totalCount = await query.CountAsync();
            var data = await query
                .Skip((pageNumber - 1) * pageSize)
                .Take(pageSize)
                .ToListAsync();

            return (data, totalCount);
        }

        /// <summary>
        /// Gets validation errors for the import session
        /// </summary>
        public async Task<List<ImportErrorLog>> GetValidationErrorsAsync(Guid sessionId)
        {
            return await _context.ImportErrorLogs
                .Where(e => e.SessionId == sessionId)
                .OrderBy(e => e.RowNumber)
                .ThenBy(e => e.FieldName)
                .ToListAsync();
        }

        /// <summary>
        /// Executes the import process for validated data
        /// </summary>
        public async Task<ImportResult> ExecuteImportAsync(
            Guid sessionId, 
            IProgress<ImportProgress>? progressCallback = null)
        {
            var stopwatch = Stopwatch.StartNew();
            var result = new ImportResult();
            var progress = new ImportProgress();

            try
            {
                // Get session with staging data
                var session = await GetImportSessionAsync(sessionId);
                if (session.Status != ImportStatus.Pending)
                {
                    throw new InvalidOperationException($"Cannot import session with status: {session.Status}");
                }

                // Start processing
                session.StartProcessing();
                await _context.SaveChangesAsync();

                var validRecords = session.StagingShipments
                    .Where(s => s.Status == StagingStatus.Validated)
                    .ToList();

                progress.TotalRecords = validRecords.Count;
                progressCallback?.Report(progress);

                foreach (var record in validRecords)
                {
                    try
                    {
                        progress.CurrentRecord++;
                        progress.CurrentOperation = $"Processing record {record.RowNumber}";
                        progressCallback?.Report(progress);

                        // Check if record exists for update
                        var existingShipment = await GetExistingShipmentAsync(record.IndentNumber);
                        
                        if (existingShipment != null)
                        {
                            // Update existing shipment
                            await UpdateExistingShipmentAsync(existingShipment, record);
                            record.SetUpdateFlag(true);
                            result.RecordsProcessed++;
                        }
                        else
                        {
                            // Create new shipment
                            await CreateNewShipmentAsync(record);
                            record.SetUpdateFlag(false);
                            result.RecordsProcessed++;
                        }

                        record.MarkAsImported();
                    }
                    catch (Exception ex)
                    {
                        var error = ImportErrorLog.CreateImportError(
                            $"Failed to import record {record.RowNumber}: {ex.Message}",
                            record.RowNumber,
                            ex.ToString());
                        error.SetSession(sessionId);
                        session.ImportErrorLogs.Add(error);
                        record.MarkAsError($"Import failed: {ex.Message}");
                        result.RecordsFailed++;
                    }
                }

                // Complete session
                var success = result.RecordsFailed == 0;
                session.Complete(success);
                session.UpdateRowCounts(result.RecordsProcessed, result.RecordsFailed);

                await _context.SaveChangesAsync();

                stopwatch.Stop();
                result.ProcessingTime = stopwatch.Elapsed;
                result.IsSuccess = success;
                result.Message = success 
                    ? $"Successfully imported {result.RecordsProcessed} records"
                    : $"Import completed with {result.RecordsFailed} errors";

                return result;
            }
            catch (Exception ex)
            {
                // Log system error
                var systemError = ImportErrorLog.CreateSystemError(
                    "Import execution failed",
                    ex.ToString(),
                    ErrorSeverity.Critical);
                systemError.SetSession(sessionId);

                _context.ImportErrorLogs.Add(systemError);
                await _context.SaveChangesAsync();

                result.IsSuccess = false;
                result.Message = $"Import failed: {ex.Message}";
                result.Errors.Add(ex.ToString());

                throw;
            }
        }

        /// <summary>
        /// Cancels an import session
        /// </summary>
        public async Task<bool> CancelImportSessionAsync(Guid sessionId, string cancelledBy)
        {
            var session = await GetImportSessionAsync(sessionId);
            if (session.Status != ImportStatus.Pending)
            {
                return false;
            }

            session.Status = ImportStatus.Cancelled;
            session.Notes = $"Cancelled by {cancelledBy} at {DateTime.UtcNow:yyyy-MM-dd HH:mm:ss} UTC";
            
            await _context.SaveChangesAsync();
            return true;
        }

        /// <summary>
        /// Gets recent import sessions for the current user
        /// </summary>
        public async Task<List<ImportSession>> GetRecentImportSessionsAsync(string createdBy, int maxResults = 10)
        {
            return await _context.ImportSessions
                .Where(s => s.CreatedBy == createdBy)
                .OrderByDescending(s => s.CreatedAt)
                .Take(maxResults)
                .ToListAsync();
        }

        /// <summary>
        /// Cleans up old import sessions and staging data
        /// </summary>
        public async Task<int> CleanupOldImportSessionsAsync(int daysToKeep = 30)
        {
            var cutoffDate = DateTime.UtcNow.AddDays(-daysToKeep);
            
            var oldSessions = await _context.ImportSessions
                .Where(s => s.CreatedAt < cutoffDate)
                .ToListAsync();

            var count = oldSessions.Count;
            
            foreach (var session in oldSessions)
            {
                _context.ImportSessions.Remove(session);
            }

            await _context.SaveChangesAsync();
            return count;
        }

        #region Private Methods

        /// <summary>
        /// Validates the uploaded file
        /// </summary>
        private async Task ValidateFileAsync(string fileName, long fileSize)
        {
            // Check file extension
            var extension = Path.GetExtension(fileName).ToLowerInvariant();
            if (!ImportConfiguration.AllowedExtensions.Contains(extension))
            {
                throw new ArgumentException($"Invalid file type. Allowed types: {string.Join(", ", ImportConfiguration.AllowedExtensions)}");
            }

            // Check file size
            if (fileSize > ImportConfiguration.MaxFileSizeBytes)
            {
                throw new ArgumentException($"File size {fileSize} bytes exceeds maximum allowed size of {ImportConfiguration.MaxFileSizeBytes} bytes");
            }
        }

        /// <summary>
        /// Processes the Excel file and extracts data
        /// </summary>
        private async Task<ExcelDataResult> ProcessExcelFileAsync(Stream fileStream)
        {
            var result = new ExcelDataResult();

            using var package = new ExcelPackage(fileStream);
            var worksheet = package.Workbook.Worksheets.FirstOrDefault();
            if (worksheet == null)
            {
                throw new ArgumentException("Excel file contains no worksheets");
            }

            // Read headers from first row
            var headerRow = worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column];
            result.Headers = headerRow.Select(cell => cell.Text?.Trim() ?? string.Empty).ToList();

            // Read data rows
            var startRow = 2;
            var endRow = worksheet.Dimension.End.Row;

            for (int row = startRow; row <= endRow; row++)
            {
                var rowData = new ExcelRowData { RowNumber = row };
                
                for (int col = 1; col <= result.Headers.Count; col++)
                {
                    var header = result.Headers[col - 1];
                    var cellValue = worksheet.Cells[row, col].Text?.Trim() ?? string.Empty;
                    
                    if (!string.IsNullOrEmpty(header))
                    {
                        rowData.ColumnValues[header] = cellValue;
                    }
                }

                if (rowData.HasData)
                {
                    result.Rows.Add(rowData);
                }
            }

            return result;
        }

        /// <summary>
        /// Creates staging records from Excel data
        /// </summary>
        private async Task<List<StagingShipment>> CreateStagingRecordsAsync(List<ExcelRowData> rows, Guid sessionId)
        {
            var stagingRecords = new List<StagingShipment>();

            foreach (var row in rows)
            {
                var stagingRecord = new StagingShipment
                {
                    SessionId = sessionId,
                    RowNumber = row.RowNumber,
                    SupplierName = row.GetValue("Supplier"),
                    IndentNumber = row.GetValue("Indent Number"),
                    ShipshapeNumber = row.GetValue("Shipshape Number"),
                    MotherVessel = row.GetValue("Mother Vessel"),
                    ETA = row.GetValue("E.T.A"),
                    BillOfEntryNumber = row.GetValue("Bill of Entry Number"),
                    ContainerNumber = row.GetValue("Container Number"),
                    OffloadingPort = row.GetValue("Offloading Port"),
                    ContainerReceived = row.GetValue("Container Received"),
                    NoOfFlatcases = row.GetValue("No of Flatcases"),
                    Voyage = row.GetValue("Voyage"),
                    MBLNumber = row.GetValue("M/BL Number"),
                    RedirectToSACD = row.GetValue("Redirect to SACD")
                };

                stagingRecords.Add(stagingRecord);
            }

            return stagingRecords;
        }

        /// <summary>
        /// Validates staging records
        /// </summary>
        private async Task<ValidationResult> ValidateStagingRecordsAsync(List<StagingShipment> records)
        {
            Console.WriteLine($"DEBUG: Starting validation of {records.Count} staging records");
            var result = new ValidationResult();
            var errors = new List<ImportErrorLog>();

            foreach (var record in records)
            {
                Console.WriteLine($"DEBUG: Validating record {record.RowNumber} - Supplier: '{record.SupplierName}', Indent: '{record.IndentNumber}'");
                var recordErrors = new List<string>();

                // Required field validation
                if (string.IsNullOrWhiteSpace(record.SupplierName))
                {
                    recordErrors.Add("Supplier is required");
                }

                if (string.IsNullOrWhiteSpace(record.IndentNumber))
                {
                    recordErrors.Add("Indent Number is required");
                }

                if (string.IsNullOrWhiteSpace(record.ShipshapeNumber))
                {
                    recordErrors.Add("Shipshape Number is required");
                }

                if (string.IsNullOrWhiteSpace(record.MotherVessel))
                {
                    recordErrors.Add("Mother Vessel is required");
                }

                if (string.IsNullOrWhiteSpace(record.ETA))
                {
                    recordErrors.Add("E.T.A is required");
                }

                if (string.IsNullOrWhiteSpace(record.BillOfEntryNumber))
                {
                    recordErrors.Add("Bill of Entry Number is required");
                }

                if (string.IsNullOrWhiteSpace(record.ContainerNumber))
                {
                    recordErrors.Add("Container Number is required");
                }

                if (string.IsNullOrWhiteSpace(record.OffloadingPort))
                {
                    recordErrors.Add("Offloading Port is required");
                }
                else
                {
                    var validPorts = rgbc_sds.services.Enums.ValidPorts.AllPorts;
                    if (!validPorts.Contains(record.OffloadingPort))
                    {
                        recordErrors.Add($"Offloading Port must be one of: {string.Join(", ", validPorts)}");
                    }
                    else
                    {
                        // Additional validation: ensure port can be mapped to an ID
                        var portId = record.OffloadingPort switch
                        {
                            "Cape Town" => 1,
                            "Coega" => 2,
                            "Durban" => 3,
                            "P.E." => 4,
                            _ => 0
                        };
                        
                        if (portId == 0)
                        {
                            recordErrors.Add($"Offloading Port '{record.OffloadingPort}' cannot be mapped to a valid port ID");
                        }
                    }
                }

                if (string.IsNullOrWhiteSpace(record.ContainerReceived))
                {
                    recordErrors.Add("Container Received is required");
                }

                if (string.IsNullOrWhiteSpace(record.NoOfFlatcases))
                {
                    recordErrors.Add("No of Flatcases is required");
                }

                if (string.IsNullOrWhiteSpace(record.Voyage))
                {
                    recordErrors.Add("Voyage is required");
                }

                if (string.IsNullOrWhiteSpace(record.MBLNumber))
                {
                    recordErrors.Add("M/BL Number is required");
                }

                if (string.IsNullOrWhiteSpace(record.RedirectToSACD))
                {
                    recordErrors.Add("Redirect to SACD is required");
                }

                // Business rule validation
                if (!string.IsNullOrWhiteSpace(record.IndentNumber))
                {
                    // Check for duplicate indent numbers in the same session
                    var duplicateCount = records.Count(r => 
                        r.RowNumber != record.RowNumber && 
                        r.IndentNumber.ToLower() == record.IndentNumber.ToLower());
                    
                    if (duplicateCount > 0)
                    {
                        recordErrors.Add($"Duplicate Indent Number found in rows: {string.Join(", ", records.Where(r => r.IndentNumber.ToLower() == record.IndentNumber.ToLower()).Select(r => r.RowNumber))}");
                    }
                }

                // Date format validation
                if (!string.IsNullOrWhiteSpace(record.ETA) && !IsValidDate(record.ETA))
                {
                    recordErrors.Add("Invalid ETA date format");
                }

                if (!string.IsNullOrWhiteSpace(record.ContainerReceived) && !IsValidDate(record.ContainerReceived))
                {
                    recordErrors.Add("Invalid Container Received date format");
                }

                if (!string.IsNullOrWhiteSpace(record.RedirectToSACD) && !IsValidDate(record.RedirectToSACD))
                {
                    recordErrors.Add("Invalid Redirect to SACD date format");
                }

                // Reference validation
                if (!string.IsNullOrWhiteSpace(record.SupplierName))
                {
                    try
                    {
                        Console.WriteLine($"DEBUG: Checking if supplier '{record.SupplierName}' exists in database...");
                        var supplierExists = await _context.sdsSupplier
                            .AnyAsync(s => s.name.ToLower() == record.SupplierName.ToLower());
                        
                        Console.WriteLine($"DEBUG: Supplier '{record.SupplierName}' exists: {supplierExists}");
                        
                        if (!supplierExists)
                        {
                            recordErrors.Add($"Supplier '{record.SupplierName}' not found in system");
                        }
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"DEBUG: Error checking supplier '{record.SupplierName}': {ex.Message}");
                        recordErrors.Add($"Error validating supplier '{record.SupplierName}': {ex.Message.Substring(0, Math.Min(ex.Message.Length, 200))}");
                    }
                }

                if (recordErrors.Count > 0)
                {
                    Console.WriteLine($"DEBUG: Record {record.RowNumber} has {recordErrors.Count} errors: {string.Join(", ", recordErrors)}");
                    var error = ImportErrorLog.CreateValidationError(
                        string.Join("; ", recordErrors),
                        record.RowNumber,
                        "Multiple",
                        JsonSerializer.Serialize(recordErrors));
                    error.SetSession(record.SessionId);
                    errors.Add(error);
                    
                    record.MarkAsError(string.Join("; ", recordErrors), JsonSerializer.Serialize(recordErrors));
                    result.ErrorCount++;
                }
                else
                {
                    Console.WriteLine($"DEBUG: Record {record.RowNumber} is valid");
                    record.MarkAsValidated();
                    result.ValidCount++;
                }
                
                Console.WriteLine($"DEBUG: Record {record.RowNumber} validation completed - Errors: {recordErrors.Count}");
            }

            result.Errors = errors;
            Console.WriteLine($"DEBUG: Validation completed - Total: {records.Count}, Valid: {result.ValidCount}, Errors: {result.ErrorCount}");
            return result;
        }

        /// <summary>
        /// Gets existing shipment by indent number
        /// </summary>
        private async Task<sdsShipment?> GetExistingShipmentAsync(string indentNumber)
        {
            return await _context.sdsShipment
                .FirstOrDefaultAsync(s => s.indent_number.ToLower() == indentNumber.ToLower());
        }

        /// <summary>
        /// Updates existing shipment with staging data
        /// </summary>
        private async Task UpdateExistingShipmentAsync(sdsShipment shipment, StagingShipment staging)
        {
            // Update fields if they have values
            if (!string.IsNullOrWhiteSpace(staging.ShipshapeNumber))
                shipment.shipshape_number = staging.ShipshapeNumber;
            
            if (!string.IsNullOrWhiteSpace(staging.MotherVessel))
                shipment.mother_vessel = staging.MotherVessel;
            
            if (!string.IsNullOrWhiteSpace(staging.ETA))
                shipment.eta = ParseDate(staging.ETA);
            
            if (!string.IsNullOrWhiteSpace(staging.BillOfEntryNumber))
                shipment.bill_of_entry_number = staging.BillOfEntryNumber;
            
            if (!string.IsNullOrWhiteSpace(staging.ContainerNumber))
                shipment.container_number = staging.ContainerNumber;
            
            if (!string.IsNullOrWhiteSpace(staging.OffloadingPort))
            {
                // Map port names to IDs based on hardcoded values from frontend
                shipment.port_id = staging.OffloadingPort switch
                {
                    "Cape Town" => 1,
                    "Coega" => 2,
                    "Durban" => 3,
                    "P.E." => 4,
                    _ => null // Unknown port name
                };
            }
            
            if (!string.IsNullOrWhiteSpace(staging.ContainerReceived))
                shipment.container_received = ParseDate(staging.ContainerReceived);
            
            if (!string.IsNullOrWhiteSpace(staging.NoOfFlatcases))
                shipment.no_of_flatcases = staging.NoOfFlatcases;
            
            if (!string.IsNullOrWhiteSpace(staging.Voyage))
                shipment.voyage = staging.Voyage;
            
            if (!string.IsNullOrWhiteSpace(staging.MBLNumber))
                shipment.mbl_number = staging.MBLNumber;
            
            if (!string.IsNullOrWhiteSpace(staging.RedirectToSACD))
                shipment.redirect_to_SACD = ParseDate(staging.RedirectToSACD);

            shipment.updated_at = DateTime.Now;
            shipment.updated_by = staging.ImportSession.CreatedBy;
        }

        /// <summary>
        /// Creates new shipment from staging data
        /// </summary>
        private async Task CreateNewShipmentAsync(StagingShipment staging)
        {
            // Get supplier ID
            var supplier = await _context.sdsSupplier
                .FirstOrDefaultAsync(s => s.name.ToLower() == staging.SupplierName.ToLower());
            
            if (supplier == null)
            {
                throw new InvalidOperationException($"Supplier '{staging.SupplierName}' not found");
            }

            var shipment = new sdsShipment
            {
                user_id = null, // Will be set when user context is available
                indent_number = staging.IndentNumber,
                shipshape_number = staging.ShipshapeNumber,
                mother_vessel = staging.MotherVessel,
                eta = ParseDate(staging.ETA),
                bill_of_entry_number = staging.BillOfEntryNumber,
                container_number = staging.ContainerNumber,
                port_id = !string.IsNullOrWhiteSpace(staging.OffloadingPort) ? staging.OffloadingPort switch
                {
                    "Cape Town" => 1,
                    "Coega" => 2,
                    "Durban" => 3,
                    "P.E." => 4,
                    _ => null // Unknown port name
                } : null,
                container_received = ParseDate(staging.ContainerReceived),
                no_of_flatcases = staging.NoOfFlatcases,
                voyage = staging.Voyage,
                mbl_number = staging.MBLNumber,
                redirect_to_SACD = ParseDate(staging.RedirectToSACD),
                shipment_status = "SAVED",
                supplier_id = supplier.id,
                created_at = DateTime.Now,
                created_by = staging.ImportSession.CreatedBy,
                updated_at = DateTime.Now,
                updated_by = staging.ImportSession.CreatedBy
            };

            _context.sdsShipment.Add(shipment);
        }

        /// <summary>
        /// Validates date string format
        /// </summary>
        private bool IsValidDate(string dateString)
        {
            if (string.IsNullOrWhiteSpace(dateString))
                return true;

            return DateTime.TryParse(dateString, CultureInfo.InvariantCulture, DateTimeStyles.None, out _);
        }

        /// <summary>
        /// Parses date string to DateTime
        /// </summary>
        private DateTime? ParseDate(string dateString)
        {
            if (string.IsNullOrWhiteSpace(dateString))
                return null;

            if (DateTime.TryParse(dateString, CultureInfo.InvariantCulture, DateTimeStyles.None, out var date))
                return date;

            return null;
        }

        #endregion
    }

    #region Helper Classes

    /// <summary>
    /// Result of Excel file processing
    /// </summary>
    public class ExcelDataResult
    {
        public List<string> Headers { get; set; } = new List<string>();
        public List<ExcelRowData> Rows { get; set; } = new List<ExcelRowData>();
    }

    /// <summary>
    /// Result of validation process
    /// </summary>
    public class ValidationResult
    {
        public int ValidCount { get; set; }
        public int ErrorCount { get; set; }
        public List<ImportErrorLog> Errors { get; set; } = new List<ImportErrorLog>();
    }

    #endregion
}