Input File Blob Storage - Implementation Plan

Version: 1.0 Date: October 20, 2025 Status: Ready for Implementation Design Doc: input_file_blob_storage_design.md

📋 Implementation Phases

Phase 1: Database Schema & Models ⏱️ 4-6 hours

Phase 2: Storage Services ⏱️ 6-8 hours

Phase 3: API Layer ⏱️ 6-8 hours

Phase 4: EpanetConsoleCore Changes ⏱️ 4-6 hours

Phase 5: Migration & Testing ⏱️ 6-8 hours

Phase 6: Frontend (Future) ⏱️ 8-12 hours

Total Estimated Time: 34-48 hours (without frontend)

Phase 1: Database Schema & Models

1.1 Create InputFile Entity

File: efcorelibraries/HTModels/Models/EN2Models/InputFile.cs (NEW)

namespace EN2Models
{
  using System;
  using System.Collections.Generic;
  using System.ComponentModel.DataAnnotations;
  using System.ComponentModel.DataAnnotations.Schema;

  /// <summary>
  /// Represents a stored EPANET input file.
  /// Files are stored in Azure Blob Storage (or local file system for testing).
  /// </summary>
  [Table("InputFiles")]
  public class InputFile
  {
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    /// <summary>
    /// Display name for the input file.
    /// </summary>
    [Required]
    [MaxLength(255)]
    public string Name { get; set; }

    /// <summary>
    /// Optional description of the input file.
    /// </summary>
    public string Description { get; set; }

    /// <summary>
    /// Storage provider type (Azure, Local, etc.).
    /// </summary>
    [Required]
    public StorageProvider StorageProvider { get; set; }

    /// <summary>
    /// Container name where the file is stored.
    /// For Azure: blob container name.
    /// For Local: subdirectory name.
    /// </summary>
    [Required]
    [MaxLength(255)]
    public string ContainerName { get; set; }

    /// <summary>
    /// Blob/file name within the container.
    /// </summary>
    [Required]
    [MaxLength(500)]
    public string BlobName { get; set; }

    /// <summary>
    /// File size in bytes.
    /// </summary>
    [Required]
    public long FileSizeBytes { get; set; }

    /// <summary>
    /// SHA256 hash of the file content for integrity verification.
    /// </summary>
    [MaxLength(64)]
    public string FileHash { get; set; }

    /// <summary>
    /// MIME content type (typically "text/plain").
    /// </summary>
    [MaxLength(100)]
    public string ContentType { get; set; }

    /// <summary>
    /// Indicates if the file is active (soft delete flag).
    /// </summary>
    [Required]
    public bool IsActive { get; set; } = true;

    /// <summary>
    /// Timestamp when the file was uploaded.
    /// </summary>
    [Required]
    public DateTime UploadedAt { get; set; }

    /// <summary>
    /// User or system that uploaded the file.
    /// </summary>
    [MaxLength(255)]
    public string UploadedBy { get; set; }

    /// <summary>
    /// Timestamp of last access (for usage tracking).
    /// </summary>
    public DateTime? LastAccessedAt { get; set; }

    // Navigation properties
    public ICollection<Scenario> Scenarios { get; set; }
  }

  /// <summary>
  /// Storage provider types.
  /// </summary>
  public enum StorageProvider
  {
    AzureBlob = 1,
    LocalFileSystem = 2
  }
}

1.2 Update Scenario Entity

File: efcorelibraries/HTModels/Models/EN2Models/Scenario.cs (MODIFY)

Add these properties:

// Add after existing properties:

/// <summary>
/// Foreign key to the base input file for this scenario.
/// </summary>
public int? BaseInputFileId { get; set; }

/// <summary>
/// Navigation property to the base input file.
/// </summary>
public InputFile BaseInputFile { get; set; }

// NOTE: Keep existing InputFile property during migration
// public string InputFile { get; set; }

1.3 Update DbContext

File: efcorelibraries/EN2PostgresContext/EN2PostgresContext.cs (MODIFY)

Add DbSet:

public DbSet<InputFile> InputFiles { get; set; }

Add to OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
  // ... existing configuration

  // InputFile configuration
  modelBuilder.Entity<InputFile>(entity =>
  {
    entity.HasKey(e => e.Id);

    entity.Property(e => e.Name).IsRequired().HasMaxLength(255);
    entity.Property(e => e.ContainerName).IsRequired().HasMaxLength(255);
    entity.Property(e => e.BlobName).IsRequired().HasMaxLength(500);
    entity.Property(e => e.FileSizeBytes).IsRequired();
    entity.Property(e => e.IsActive).IsRequired().HasDefaultValue(true);
    entity.Property(e => e.UploadedAt).IsRequired();
    entity.Property(e => e.ContentType).HasDefaultValue("text/plain");

    // Unique constraint on container + blob name
    entity.HasIndex(e => new { e.ContainerName, e.BlobName })
      .IsUnique()
      .HasDatabaseName("UQ_InputFile_Container_Blob");

    // Indexes
    entity.HasIndex(e => e.IsActive).HasDatabaseName("IX_InputFiles_IsActive");
    entity.HasIndex(e => e.ContainerName).HasDatabaseName("IX_InputFiles_ContainerName");
  });

  // Scenario configuration (add FK)
  modelBuilder.Entity<Scenario>(entity =>
  {
    // ... existing configuration

    entity.HasOne(e => e.BaseInputFile)
      .WithMany(e => e.Scenarios)
      .HasForeignKey(e => e.BaseInputFileId)
      .OnDelete(DeleteBehavior.Restrict);  // Prevent deletion of files in use
  });
}

1.4 Create EF Core Migration

Command:

cd efcorelibraries/EN2PostgresContext
dotnet ef migrations add AddInputFileModel --startup-project ../EN2PostgresContext

Review generated migration, then apply:

dotnet ef database update --startup-project ../EN2PostgresContext

1.5 Manual Data Migration Script

File: efcorelibraries/scripts/migrate_input_files.sql (NEW)

-- ============================================================================
-- Input File Data Migration Script
-- ============================================================================
-- Purpose: Migrate existing Scenario.InputFile paths to InputFiles table
-- Date: October 20, 2025
-- ============================================================================

BEGIN;

-- Step 1: Create InputFile records for each unique InputFile path
INSERT INTO "InputFiles"
  ("Name", "Description", "StorageProvider", "ContainerName", "BlobName",
   "FileSizeBytes", "IsActive", "UploadedAt", "UploadedBy", "ContentType")
SELECT DISTINCT
  -- Extract filename from path
  CASE
    WHEN "InputFile" LIKE '%/%' THEN
      substring("InputFile" from '([^/]+)$')
    WHEN "InputFile" LIKE '%\\%' THEN
      substring("InputFile" from '([^\\]+)$')
    ELSE
      "InputFile"
  END as "Name",

  'Migrated from legacy scenario' as "Description",
  2 as "StorageProvider",  -- LocalFileSystem
  'legacy-files' as "ContainerName",
  "InputFile" as "BlobName",  -- Store original path
  0 as "FileSizeBytes",  -- Update manually if needed
  true as "IsActive",
  NOW() as "UploadedAt",
  'system-migration' as "UploadedBy",
  'text/plain' as "ContentType"
FROM "Scenarios"
WHERE "InputFile" IS NOT NULL
  AND "InputFile" != ''
  AND NOT EXISTS (
    -- Don't create duplicates if script run multiple times
    SELECT 1 FROM "InputFiles" WHERE "BlobName" = "Scenarios"."InputFile"
  );

-- Step 2: Update Scenarios to reference InputFiles
UPDATE "Scenarios" s
SET "BaseInputFileId" = (
  SELECT i."Id"
  FROM "InputFiles" i
  WHERE i."BlobName" = s."InputFile"
  LIMIT 1
)
WHERE s."InputFile" IS NOT NULL
  AND s."InputFile" != ''
  AND s."BaseInputFileId" IS NULL;

-- Step 3: Verification
SELECT
  'Migration Summary' as "Info",
  (SELECT COUNT(*) FROM "InputFiles") as "Total InputFiles",
  (SELECT COUNT(*) FROM "Scenarios" WHERE "BaseInputFileId" IS NOT NULL) as "Scenarios Migrated",
  (SELECT COUNT(*) FROM "Scenarios" WHERE "InputFile" IS NOT NULL AND "BaseInputFileId" IS NULL) as "Scenarios Not Migrated";

COMMIT;

-- ============================================================================
-- After verification, optionally make BaseInputFileId NOT NULL
-- ============================================================================
-- ALTER TABLE "Scenarios" ALTER COLUMN "BaseInputFileId" SET NOT NULL;

-- ============================================================================
-- Eventually remove old column (after blob migration complete)
-- ============================================================================
-- ALTER TABLE "Scenarios" RENAME COLUMN "InputFile" TO "InputFile_OLD";
-- Later: ALTER TABLE "Scenarios" DROP COLUMN "InputFile_OLD";

Phase 2: Storage Services

2.1 Install NuGet Packages

File: dwd-api-aspnet/DwdApiAspNet/DwdApiAspNet.csproj (MODIFY)

<ItemGroup>
  <PackageReference Include="Azure.Storage.Blobs" Version="12.19.1" />
  <PackageReference Include="Azure.Identity" Version="1.10.4" />
</ItemGroup>

File: epanetconsolecore/EpanetConsoleCore/EpanetConsoleCore.csproj (MODIFY)

<ItemGroup>
  <PackageReference Include="Azure.Storage.Blobs" Version="12.19.1" />
</ItemGroup>

2.2 Create Storage Service Interface

File: dwd-api-aspnet/DwdApiAspNet/Services/IInputFileStorageService.cs (NEW)

namespace DwdApiAspNet.Services
{
  using System;
  using System.IO;
  using System.Threading;
  using System.Threading.Tasks;

  /// <summary>
  /// Interface for input file storage operations.
  /// </summary>
  public interface IInputFileStorageService
  {
    /// <summary>
    /// Uploads a file to storage and returns the blob name.
    /// </summary>
    Task<string> UploadAsync(
      Stream fileStream,
      string containerName,
      string fileName,
      CancellationToken cancellationToken = default);

    /// <summary>
    /// Downloads a file to a stream.
    /// </summary>
    Task<Stream> DownloadAsync(
      string containerName,
      string blobName,
      CancellationToken cancellationToken = default);

    /// <summary>
    /// Downloads a file to a specific local path.
    /// </summary>
    Task<string> DownloadToFileAsync(
      string containerName,
      string blobName,
      string localPath,
      CancellationToken cancellationToken = default);

    /// <summary>
    /// Deletes a file from storage.
    /// </summary>
    Task DeleteAsync(
      string containerName,
      string blobName,
      CancellationToken cancellationToken = default);

    /// <summary>
    /// Checks if a file exists in storage.
    /// </summary>
    Task<bool> ExistsAsync(
      string containerName,
      string blobName,
      CancellationToken cancellationToken = default);

    /// <summary>
    /// Gets file size in bytes.
    /// </summary>
    Task<long> GetFileSizeAsync(
      string containerName,
      string blobName,
      CancellationToken cancellationToken = default);
  }
}

2.3 Create Azure Blob Implementation

File: dwd-api-aspnet/DwdApiAspNet/Services/AzureBlobStorageService.cs (NEW)

namespace DwdApiAspNet.Services
{
  using System;
  using System.IO;
  using System.Threading;
  using System.Threading.Tasks;
  using Azure.Storage.Blobs;
  using Azure.Storage.Blobs.Models;
  using Microsoft.Extensions.Configuration;
  using Microsoft.Extensions.Logging;

  public class AzureBlobStorageService : IInputFileStorageService
  {
    private readonly BlobServiceClient _blobServiceClient;
    private readonly ILogger<AzureBlobStorageService> _logger;

    public AzureBlobStorageService(
      IConfiguration configuration,
      ILogger<AzureBlobStorageService> logger)
    {
      var serviceUrl = configuration["InputFileStorage:AzureBlob:ServiceUrl"];
      var accountKey = configuration["InputFileStorage:AzureBlob:AdminAccountKey"];

      if (string.IsNullOrEmpty(serviceUrl))
      {
        throw new InvalidOperationException("Azure Blob Storage ServiceUrl not configured");
      }

      // Use account key for admin operations (upload, delete)
      if (!string.IsNullOrEmpty(accountKey))
      {
        var accountName = new Uri(serviceUrl).Host.Split('.')[0];
        var credential = new Azure.Storage.StorageSharedKeyCredential(accountName, accountKey);
        _blobServiceClient = new BlobServiceClient(new Uri(serviceUrl), credential);
      }
      else
      {
        // Fallback to default Azure credentials (Managed Identity, etc.)
        _blobServiceClient = new BlobServiceClient(new Uri(serviceUrl), new Azure.Identity.DefaultAzureCredential());
      }

      _logger = logger;
    }

    public async Task<string> UploadAsync(
      Stream fileStream,
      string containerName,
      string fileName,
      CancellationToken cancellationToken = default)
    {
      try
      {
        var containerClient = _blobServiceClient.GetBlobContainerClient(containerName);

        // Create container if it doesn't exist
        await containerClient.CreateIfNotExistsAsync(
          PublicAccessType.None,
          cancellationToken: cancellationToken);

        var blobClient = containerClient.GetBlobClient(fileName);

        // Upload with options
        var options = new BlobUploadOptions
        {
          HttpHeaders = new BlobHttpHeaders
          {
            ContentType = "text/plain"
          }
        };

        await blobClient.UploadAsync(
          fileStream,
          options,
          cancellationToken: cancellationToken);

        _logger.LogInformation($"Uploaded {fileName} to container {containerName}");

        return fileName;
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Failed to upload {fileName} to {containerName}");
        throw;
      }
    }

    public async Task<Stream> DownloadAsync(
      string containerName,
      string blobName,
      CancellationToken cancellationToken = default)
    {
      try
      {
        var containerClient = _blobServiceClient.GetBlobContainerClient(containerName);
        var blobClient = containerClient.GetBlobClient(blobName);

        var response = await blobClient.DownloadAsync(cancellationToken);

        _logger.LogInformation($"Downloaded {blobName} from container {containerName}");

        return response.Value.Content;
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Failed to download {blobName} from {containerName}");
        throw;
      }
    }

    public async Task<string> DownloadToFileAsync(
      string containerName,
      string blobName,
      string localPath,
      CancellationToken cancellationToken = default)
    {
      try
      {
        // Ensure directory exists
        var directory = Path.GetDirectoryName(localPath);
        if (!string.IsNullOrEmpty(directory))
        {
          Directory.CreateDirectory(directory);
        }

        var containerClient = _blobServiceClient.GetBlobContainerClient(containerName);
        var blobClient = containerClient.GetBlobClient(blobName);

        await blobClient.DownloadToAsync(localPath, cancellationToken);

        _logger.LogInformation($"Downloaded {blobName} from {containerName} to {localPath}");

        return localPath;
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Failed to download {blobName} to {localPath}");
        throw;
      }
    }

    public async Task DeleteAsync(
      string containerName,
      string blobName,
      CancellationToken cancellationToken = default)
    {
      try
      {
        var containerClient = _blobServiceClient.GetBlobContainerClient(containerName);
        var blobClient = containerClient.GetBlobClient(blobName);

        await blobClient.DeleteIfExistsAsync(
          DeleteSnapshotsOption.IncludeSnapshots,
          cancellationToken: cancellationToken);

        _logger.LogInformation($"Deleted {blobName} from container {containerName}");
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Failed to delete {blobName} from {containerName}");
        throw;
      }
    }

    public async Task<bool> ExistsAsync(
      string containerName,
      string blobName,
      CancellationToken cancellationToken = default)
    {
      try
      {
        var containerClient = _blobServiceClient.GetBlobContainerClient(containerName);
        var blobClient = containerClient.GetBlobClient(blobName);

        return await blobClient.ExistsAsync(cancellationToken);
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Failed to check existence of {blobName}");
        return false;
      }
    }

    public async Task<long> GetFileSizeAsync(
      string containerName,
      string blobName,
      CancellationToken cancellationToken = default)
    {
      try
      {
        var containerClient = _blobServiceClient.GetBlobContainerClient(containerName);
        var blobClient = containerClient.GetBlobClient(blobName);

        var properties = await blobClient.GetPropertiesAsync(cancellationToken: cancellationToken);

        return properties.Value.ContentLength;
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Failed to get size of {blobName}");
        throw;
      }
    }
  }
}

2.4 Create Local File System Implementation

File: dwd-api-aspnet/DwdApiAspNet/Services/LocalFileStorageService.cs (NEW)

namespace DwdApiAspNet.Services
{
  using System;
  using System.IO;
  using System.Threading;
  using System.Threading.Tasks;
  using Microsoft.Extensions.Configuration;
  using Microsoft.Extensions.Logging;

  public class LocalFileStorageService : IInputFileStorageService
  {
    private readonly string _basePath;
    private readonly ILogger<LocalFileStorageService> _logger;

    public LocalFileStorageService(
      IConfiguration configuration,
      ILogger<LocalFileStorageService> logger)
    {
      _basePath = configuration["InputFileStorage:LocalFileSystem:BasePath"];

      if (string.IsNullOrEmpty(_basePath))
      {
        throw new InvalidOperationException("Local file storage BasePath not configured");
      }

      // Ensure base directory exists
      Directory.CreateDirectory(_basePath);

      _logger = logger;
    }

    private string GetFullPath(string containerName, string blobName)
    {
      var containerPath = Path.Combine(_basePath, containerName);
      return Path.Combine(containerPath, blobName);
    }

    public async Task<string> UploadAsync(
      Stream fileStream,
      string containerName,
      string fileName,
      CancellationToken cancellationToken = default)
    {
      try
      {
        var containerPath = Path.Combine(_basePath, containerName);
        Directory.CreateDirectory(containerPath);

        var filePath = Path.Combine(containerPath, fileName);

        using (var fileStream2 = new FileStream(filePath, FileMode.Create, FileAccess.Write))
        {
          await fileStream.CopyToAsync(fileStream2, cancellationToken);
        }

        _logger.LogInformation($"Uploaded {fileName} to {containerPath}");

        return fileName;
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Failed to upload {fileName} to local storage");
        throw;
      }
    }

    public async Task<Stream> DownloadAsync(
      string containerName,
      string blobName,
      CancellationToken cancellationToken = default)
    {
      try
      {
        var filePath = GetFullPath(containerName, blobName);

        if (!File.Exists(filePath))
        {
          throw new FileNotFoundException($"File not found: {filePath}");
        }

        var memoryStream = new MemoryStream();
        using (var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
        {
          await fileStream.CopyToAsync(memoryStream, cancellationToken);
        }

        memoryStream.Position = 0;
        return memoryStream;
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Failed to download {blobName} from local storage");
        throw;
      }
    }

    public async Task<string> DownloadToFileAsync(
      string containerName,
      string blobName,
      string localPath,
      CancellationToken cancellationToken = default)
    {
      try
      {
        var sourcePath = GetFullPath(containerName, blobName);

        if (!File.Exists(sourcePath))
        {
          throw new FileNotFoundException($"File not found: {sourcePath}");
        }

        // Ensure directory exists
        var directory = Path.GetDirectoryName(localPath);
        if (!string.IsNullOrEmpty(directory))
        {
          Directory.CreateDirectory(directory);
        }

        // Copy file
        using (var sourceStream = new FileStream(sourcePath, FileMode.Open, FileAccess.Read))
        using (var destStream = new FileStream(localPath, FileMode.Create, FileAccess.Write))
        {
          await sourceStream.CopyToAsync(destStream, cancellationToken);
        }

        _logger.LogInformation($"Downloaded {blobName} to {localPath}");

        return localPath;
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Failed to download {blobName} to {localPath}");
        throw;
      }
    }

    public Task DeleteAsync(
      string containerName,
      string blobName,
      CancellationToken cancellationToken = default)
    {
      try
      {
        var filePath = GetFullPath(containerName, blobName);

        if (File.Exists(filePath))
        {
          File.Delete(filePath);
          _logger.LogInformation($"Deleted {blobName} from local storage");
        }

        return Task.CompletedTask;
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Failed to delete {blobName} from local storage");
        throw;
      }
    }

    public Task<bool> ExistsAsync(
      string containerName,
      string blobName,
      CancellationToken cancellationToken = default)
    {
      var filePath = GetFullPath(containerName, blobName);
      return Task.FromResult(File.Exists(filePath));
    }

    public Task<long> GetFileSizeAsync(
      string containerName,
      string blobName,
      CancellationToken cancellationToken = default)
    {
      try
      {
        var filePath = GetFullPath(containerName, blobName);

        if (!File.Exists(filePath))
        {
          throw new FileNotFoundException($"File not found: {filePath}");
        }

        var fileInfo = new FileInfo(filePath);
        return Task.FromResult(fileInfo.Length);
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Failed to get size of {blobName}");
        throw;
      }
    }
  }
}

2.5 Register Services in Startup

File: dwd-api-aspnet/DwdApiAspNet/Program.cs (MODIFY)

// Add after existing service registrations:

// Register input file storage service based on configuration
var storageProvider = builder.Configuration["InputFileStorage:Provider"];
if (storageProvider == "AzureBlob")
{
  builder.Services.AddSingleton<IInputFileStorageService, AzureBlobStorageService>();
}
else if (storageProvider == "LocalFileSystem")
{
  builder.Services.AddSingleton<IInputFileStorageService, LocalFileStorageService>();
}
else
{
  throw new InvalidOperationException($"Unknown storage provider: {storageProvider}");
}

Phase 3: API Layer

3.1 Create InputFile DTOs

File: dwd-api-aspnet/DwdApiAspNet/Models/InputFileDto.cs (NEW)

namespace DwdApiAspNet.Models
{
  using System;
  using EN2Models;

  public class InputFileDto
  {
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public string StorageProvider { get; set; }
    public string ContainerName { get; set; }
    public long FileSizeBytes { get; set; }
    public string FileSizeMB { get; set; }  // Human-readable
    public bool IsActive { get; set; }
    public DateTime UploadedAt { get; set; }
    public string UploadedBy { get; set; }
    public DateTime? LastAccessedAt { get; set; }
    public int ScenarioCount { get; set; }  // Number of scenarios using this file
  }

  public class CreateInputFileRequest
  {
    public string Name { get; set; }
    public string Description { get; set; }
    public string ContainerName { get; set; }
  }

  public class UploadInputFileRequest
  {
    public IFormFile File { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public string ContainerName { get; set; }
  }
}

3.2 Create InputFileController

File: dwd-api-aspnet/DwdApiAspNet/Controllers/InputFileController.cs (NEW)

namespace DwdApiAspNet.Controllers
{
  using System;
  using System.Collections.Generic;
  using System.IO;
  using System.Linq;
  using System.Security.Cryptography;
  using System.Threading.Tasks;
  using Microsoft.AspNetCore.Http;
  using Microsoft.AspNetCore.Mvc;
  using Microsoft.EntityFrameworkCore;
  using Microsoft.Extensions.Logging;
  using EN2Models;
  using EN2PostgresContext;
  using DwdApiAspNet.Models;
  using DwdApiAspNet.Services;

  [ApiController]
  [Route("api/[controller]")]
  public class InputFileController : ControllerBase
  {
    private readonly EN2PostgresContext _context;
    private readonly IInputFileStorageService _storageService;
    private readonly ILogger<InputFileController> _logger;

    public InputFileController(
      EN2PostgresContext context,
      IInputFileStorageService storageService,
      ILogger<InputFileController> logger)
    {
      _context = context;
      _storageService = storageService;
      _logger = logger;
    }

    // GET: api/InputFile
    [HttpGet]
    public async Task<ActionResult<IEnumerable<InputFileDto>>> GetInputFiles()
    {
      try
      {
        var inputFiles = await _context.InputFiles
          .Where(f => f.IsActive)
          .OrderByDescending(f => f.UploadedAt)
          .ToListAsync();

        var dtos = inputFiles.Select(f => MapToDto(f)).ToList();

        return Ok(dtos);
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, "Error retrieving input files");
        return StatusCode(500, new { Message = "Error retrieving input files", Error = ex.Message });
      }
    }

    // GET: api/InputFile/5
    [HttpGet("{id}")]
    public async Task<ActionResult<InputFileDto>> GetInputFile(int id)
    {
      try
      {
        var inputFile = await _context.InputFiles
          .Include(f => f.Scenarios)
          .FirstOrDefaultAsync(f => f.Id == id);

        if (inputFile == null)
        {
          return NotFound(new { Message = $"Input file {id} not found" });
        }

        return Ok(MapToDto(inputFile));
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Error retrieving input file {id}");
        return StatusCode(500, new { Message = "Error retrieving input file", Error = ex.Message });
      }
    }

    // POST: api/InputFile/upload
    [HttpPost("upload")]
    [RequestSizeLimit(500_000_000)]  // 500MB limit
    public async Task<ActionResult<InputFileDto>> UploadInputFile([FromForm] UploadInputFileRequest request)
    {
      try
      {
        if (request.File == null || request.File.Length == 0)
        {
          return BadRequest(new { Message = "No file provided" });
        }

        // Validate file extension
        var extension = Path.GetExtension(request.File.FileName).ToLowerInvariant();
        if (extension != ".inp")
        {
          return BadRequest(new { Message = "Only .inp files are allowed" });
        }

        // Validate container name
        if (string.IsNullOrWhiteSpace(request.ContainerName))
        {
          return BadRequest(new { Message = "Container name is required" });
        }

        // Use provided name or fall back to original filename
        var name = string.IsNullOrWhiteSpace(request.Name)
          ? Path.GetFileNameWithoutExtension(request.File.FileName)
          : request.Name;

        // Generate blob name (use original filename or sanitize)
        var blobName = SanitizeFileName(request.File.FileName);

        // Calculate file hash
        string fileHash;
        using (var stream = request.File.OpenReadStream())
        {
          using (var sha256 = SHA256.Create())
          {
            var hashBytes = await sha256.ComputeHashAsync(stream);
            fileHash = BitConverter.ToString(hashBytes).Replace("-", "").ToLowerInvariant();
          }
        }

        // Upload to storage
        using (var stream = request.File.OpenReadStream())
        {
          await _storageService.UploadAsync(
            stream,
            request.ContainerName,
            blobName);
        }

        // Create database record
        var inputFile = new InputFile
        {
          Name = name,
          Description = request.Description,
          StorageProvider = StorageProvider.AzureBlob,  // TODO: Get from config
          ContainerName = request.ContainerName,
          BlobName = blobName,
          FileSizeBytes = request.File.Length,
          FileHash = fileHash,
          ContentType = request.File.ContentType ?? "text/plain",
          IsActive = true,
          UploadedAt = DateTime.UtcNow,
          UploadedBy = "api-user"  // TODO: Get from auth context
        };

        _context.InputFiles.Add(inputFile);
        await _context.SaveChangesAsync();

        _logger.LogInformation($"Uploaded input file {inputFile.Id}: {inputFile.Name}");

        return CreatedAtAction(nameof(GetInputFile), new { id = inputFile.Id }, MapToDto(inputFile));
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, "Error uploading input file");
        return StatusCode(500, new { Message = "Error uploading input file", Error = ex.Message });
      }
    }

    // GET: api/InputFile/5/download
    [HttpGet("{id}/download")]
    public async Task<IActionResult> DownloadInputFile(int id)
    {
      try
      {
        var inputFile = await _context.InputFiles.FindAsync(id);

        if (inputFile == null)
        {
          return NotFound(new { Message = $"Input file {id} not found" });
        }

        // Download from storage
        var stream = await _storageService.DownloadAsync(
          inputFile.ContainerName,
          inputFile.BlobName);

        // Update last accessed time
        inputFile.LastAccessedAt = DateTime.UtcNow;
        await _context.SaveChangesAsync();

        return File(stream, "text/plain", $"{inputFile.Name}.inp");
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Error downloading input file {id}");
        return StatusCode(500, new { Message = "Error downloading input file", Error = ex.Message });
      }
    }

    // DELETE: api/InputFile/5
    [HttpDelete("{id}")]
    public async Task<IActionResult> DeleteInputFile(int id)
    {
      try
      {
        var inputFile = await _context.InputFiles
          .Include(f => f.Scenarios)
          .FirstOrDefaultAsync(f => f.Id == id);

        if (inputFile == null)
        {
          return NotFound(new { Message = $"Input file {id} not found" });
        }

        // Check if any scenarios are using this file
        if (inputFile.Scenarios != null && inputFile.Scenarios.Any())
        {
          return BadRequest(new
          {
            Message = "Cannot delete input file that is in use by scenarios",
            ScenarioCount = inputFile.Scenarios.Count
          });
        }

        // Soft delete
        inputFile.IsActive = false;
        await _context.SaveChangesAsync();

        _logger.LogInformation($"Soft deleted input file {id}");

        return NoContent();
      }
      catch (Exception ex)
      {
        _logger.LogError(ex, $"Error deleting input file {id}");
        return StatusCode(500, new { Message = "Error deleting input file", Error = ex.Message });
      }
    }

    private InputFileDto MapToDto(InputFile inputFile)
    {
      return new InputFileDto
      {
        Id = inputFile.Id,
        Name = inputFile.Name,
        Description = inputFile.Description,
        StorageProvider = inputFile.StorageProvider.ToString(),
        ContainerName = inputFile.ContainerName,
        FileSizeBytes = inputFile.FileSizeBytes,
        FileSizeMB = $"{inputFile.FileSizeBytes / 1024.0 / 1024.0:F2} MB",
        IsActive = inputFile.IsActive,
        UploadedAt = inputFile.UploadedAt,
        UploadedBy = inputFile.UploadedBy,
        LastAccessedAt = inputFile.LastAccessedAt,
        ScenarioCount = inputFile.Scenarios?.Count ?? 0
      };
    }

    private string SanitizeFileName(string fileName)
    {
      var invalidChars = Path.GetInvalidFileNameChars();
      var sanitized = new string(fileName.Select(c =>
        invalidChars.Contains(c) ? '_' : c
      ).ToArray());

      return sanitized;
    }
  }
}

3.3 Update ScenarioController

File: dwd-api-aspnet/DwdApiAspNet/Controllers/ScenarioController.cs (MODIFY)

Update MapToDto to include InputFile:

private ScenarioResponseDto MapToDto(Scenario scenario, Options defaultOptions)
{
  // ... existing code

  return new ScenarioResponseDto
  {
    Id = scenario.Id,
    Name = scenario.Name,
    // ... existing properties

    // NEW: Include InputFile information
    BaseInputFileId = scenario.BaseInputFileId,
    BaseInputFileName = scenario.BaseInputFile?.Name,

    // ... rest of existing properties
  };
}

Update ScenarioResponseDto:

public class ScenarioResponseDto
{
  // ... existing properties

  public int? BaseInputFileId { get; set; }
  public string BaseInputFileName { get; set; }
}

Update GetScenario and GetScenarios to include BaseInputFile:

var scenario = await _context.Scenarios
  .Include(s => s.Options)
  .Include(s => s.BaseInputFile)  // NEW
  .FirstOrDefaultAsync(s => s.Id == id);

Phase 4: EpanetConsoleCore Changes

4.1 Create Temp File Manager

File: epanetconsolecore/EpanetConsoleCore/Services/TempFileManager.cs (NEW)

namespace EpanetConsoleCore.Services
{
  using System;
  using System.IO;

  /// <summary>
  /// Manages temporary file directories for EPANET runs.
  /// </summary>
  public static class TempFileManager
  {
    private static readonly string TempBasePath =
      Path.Combine(Path.GetTempPath(), "HydroTrek", "EpanetInputFiles");

    /// <summary>
    /// Creates a unique temp directory for a scenario run.
    /// </summary>
    public static string CreateTempDirectory(int scenarioId)
    {
      var guid = Guid.NewGuid().ToString("N").Substring(0, 8);
      var dirPath = Path.Combine(TempBasePath, $"scenario_{scenarioId}_{guid}");
      Directory.CreateDirectory(dirPath);
      return dirPath;
    }

    /// <summary>
    /// Cleans up a temp directory and all its contents.
    /// </summary>
    public static void CleanupTempDirectory(string dirPath)
    {
      try
      {
        if (Directory.Exists(dirPath))
        {
          Directory.Delete(dirPath, recursive: true);
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine($"Warning: Failed to cleanup {dirPath}: {ex.Message}");
      }
    }

    /// <summary>
    /// Cleans up orphaned temp directories older than specified age.
    /// </summary>
    public static void CleanupOrphanedTempFiles(TimeSpan maxAge)
    {
      if (!Directory.Exists(TempBasePath))
      {
        return;
      }

      var cutoffTime = DateTime.UtcNow - maxAge;

      foreach (var dir in Directory.GetDirectories(TempBasePath))
      {
        try
        {
          var dirInfo = new DirectoryInfo(dir);
          if (dirInfo.LastWriteTimeUtc < cutoffTime)
          {
            Directory.Delete(dir, recursive: true);
            Console.WriteLine($"Cleaned up orphaned temp directory: {dir}");
          }
        }
        catch (Exception ex)
        {
          Console.WriteLine($"Warning: Failed to cleanup orphaned directory {dir}: {ex.Message}");
        }
      }
    }
  }
}

4.2 Copy Storage Services to EpanetConsoleCore

Copy the following files from API project:

IInputFileStorageService.cs
AzureBlobStorageService.cs
LocalFileStorageService.cs

Or create shared library (better approach):

File: efcorelibraries/HTModels/Services/IInputFileStorageService.cs (NEW - shared)

Move interface and implementations to shared location so both API and EpanetConsoleCore can reference them.

4.3 Update Program.cs Main

File: epanetconsolecore/EpanetConsoleCore/Program.cs (MODIFY)

public static async Task Main(string[] args)
{
  // ... existing argument parsing

  logger = HTLogger.HTLogger.GetDefaultLogger();

  // Cleanup orphaned temp files on startup
  logger.LogInformation("Cleaning up orphaned temp files...");
  TempFileManager.CleanupOrphanedTempFiles(TimeSpan.FromHours(24));

  if (exportOnly)
  {
    await ExportScenarioInputFile(pgConnStr, scenarioId);
  }
  else
  {
    await dwdRun(pgConnStr);
  }
}

4.4 Update dwdRun Method

File: epanetconsolecore/EpanetConsoleCore/Program.cs (MODIFY)

private static async Task dwdRun(string pgConnStr)
{
  string tempDir = null;
  string localInpPath = null;

  try
  {
    // Initialize database context
    enPgOptions = new DbContextOptionsBuilder<EN2PostgresContext>()
      .UseNpgsql(pgConnStr)
      .Options;
    enContext = new EN2PostgresContext(enPgOptions);

    // Load scenario with InputFile
    var scenario = await enContext.Scenarios
      .Include(s => s.Options)
      .Include(s => s.BaseInputFile)  // NEW: Include InputFile
      .FirstOrDefaultAsync(s => s.Id == scenarioId);

    if (scenario == null)
    {
      throw new Exception($"Scenario {scenarioId} not found in database");
    }

    logger.LogInformation($"Loaded scenario: {scenario.Name}");

    // NEW: Download input file from blob storage
    if (scenario.BaseInputFile == null)
    {
      throw new Exception($"Scenario {scenarioId} has no base input file assigned");
    }

    logger.LogInformation($"Preparing input file: {scenario.BaseInputFile.Name}");
    logger.LogInformation($"Container: {scenario.BaseInputFile.ContainerName}, Blob: {scenario.BaseInputFile.BlobName}");

    // Create temp directory for this run
    tempDir = TempFileManager.CreateTempDirectory(scenarioId);
    localInpPath = Path.Combine(tempDir, "network.inp");

    // Get storage service based on provider
    var storageService = GetStorageService(scenario.BaseInputFile.StorageProvider);

    // Download input file to temp location
    await storageService.DownloadToFileAsync(
      scenario.BaseInputFile.ContainerName,
      scenario.BaseInputFile.BlobName,
      localInpPath);

    logger.LogInformation($"Input file downloaded to: {localInpPath}");

    // Update last accessed time
    scenario.BaseInputFile.LastAccessedAt = DateTime.UtcNow;
    await enContext.SaveChangesAsync();

    // Get options (use scenario's options or default)
    var options = scenario.Options;
    if (options == null)
    {
      options = await enContext.Options.FirstOrDefaultAsync(o => o.IsDefault)
        ?? await enContext.Options.FirstOrDefaultAsync();
      if (options == null)
      {
        throw new Exception("No options configuration found. Please create a default Options preset.");
      }
    }

    // Update scenario status to Running
    scenario.Status = ScenarioStatus.Running;
    scenario.LastRunAt = DateTimeOffset.Now.ToUnixTimeSeconds();
    scenario.ExecutionStartTime = scenario.LastRunAt;
    scenario.ExecutionEndTime = null;
    scenario.ExecutionDurationSeconds = null;
    scenario.ErrorMessage = null;
    scenario.ErrorCode = null;
    scenario.ProgressPercentage = 0;
    scenario.CurrentStep = "Initializing";
    await enContext.SaveChangesAsync();

    // Continue with existing simulation logic using localInpPath
    bool doQuality = scenario.DoQuality;
    string msxFile = scenario.MsxFile;
    bool modifyMultiplier = scenario.ModifyGlobalDemandMultiplier;
    bool historyRun = scenario.HistoryRun;
    bool benchmark = scenario.Benchmark;
    bool doScada = scenario.ScadaAvailable;

    if (historyRun)
    {
      simStartUnixtime = scenario.StartUnixtime;
      simEndUnixtime = scenario.ScadaEndUnixtime;
    }

    var speciesList = new Dictionary<int, string>();

    var filenameBase = Path.GetFileNameWithoutExtension(localInpPath);
    var inpBase = Path.Combine(Path.GetDirectoryName(localInpPath), filenameBase);
    epanetWrapper = new EpanetWrapper(localInpPath, $"{inpBase}.rpt", $"{inpBase}.out", logger);

    if (doQuality)
    {
      doMsx = scenario.DoMsx;
    }

    logger.LogInformation("Opening inp file...");

    // ... REST OF EXISTING dwdRun() CODE UNCHANGED ...

  }
  catch (Exception ex)
  {
    logger.LogError($"Error executing scenario: {ex.Message}");

    // Update scenario status to Failed
    if (enContext != null)
    {
      try
      {
        var scenario = await enContext.Scenarios.FindAsync(scenarioId);
        if (scenario != null)
        {
          scenario.Status = ScenarioStatus.Failed;
          scenario.ErrorMessage = ex.Message;
          scenario.ExecutionEndTime = DateTimeOffset.Now.ToUnixTimeSeconds();

          if (scenario.ExecutionStartTime.HasValue)
          {
            scenario.ExecutionDurationSeconds = scenario.ExecutionEndTime.Value - scenario.ExecutionStartTime.Value;
          }

          enContext.Entry(scenario).State = EntityState.Modified;
          await enContext.SaveChangesAsync();
        }
      }
      catch (Exception dbEx)
      {
        logger.LogError($"Failed to update scenario status to Failed: {dbEx.Message}");
      }
    }

    throw;
  }
  finally
  {
    // NEW: Cleanup temp directory
    if (tempDir != null)
    {
      logger.LogInformation($"Cleaning up temp directory: {tempDir}");
      TempFileManager.CleanupTempDirectory(tempDir);
    }

    // Existing cleanup
    epanetWrapper?.Dispose();
    if (enContext != null)
    {
      await enContext.DisposeAsync();
    }
  }
}

private static IInputFileStorageService GetStorageService(StorageProvider provider)
{
  // Get configuration
  var storageProviderConfig = configuration["InputFileStorage:Provider"];

  // Create logger
  var loggerFactory = LoggerFactory.Create(builder => builder.AddConsole());
  var serviceLogger = loggerFactory.CreateLogger<AzureBlobStorageService>();

  return provider switch
  {
    StorageProvider.AzureBlob => new AzureBlobStorageService(configuration, serviceLogger),
    StorageProvider.LocalFileSystem => new LocalFileStorageService(configuration, new LoggerFactory().CreateLogger<LocalFileStorageService>()),
    _ => throw new NotSupportedException($"Storage provider {provider} not supported")
  };
}

4.5 Update ExportScenarioInputFile Method

File: epanetconsolecore/EpanetConsoleCore/Program.cs (MODIFY)

Apply similar changes to the export method:

private static async Task ExportScenarioInputFile(string pgConnStr, int scenarioId)
{
  EpanetWrapper localEpanetWrapper = null;
  EN2PostgresContext localContext = null;
  string tempDir = null;
  string localInpPath = null;

  try
  {
    // ... existing initialization

    // Load scenario with options AND InputFile
    var scenario = await localContext.Scenarios
      .Include(s => s.Options)
      .Include(s => s.BaseInputFile)  // NEW
      .FirstOrDefaultAsync(s => s.Id == scenarioId);

    if (scenario == null)
    {
      throw new Exception($"Scenario {scenarioId} not found in database");
    }

    // NEW: Download input file from blob storage
    if (scenario.BaseInputFile == null)
    {
      throw new Exception($"Scenario {scenarioId} has no base input file assigned");
    }

    logger.LogInformation($"Preparing input file: {scenario.BaseInputFile.Name}");

    // Create temp directory
    tempDir = TempFileManager.CreateTempDirectory(scenarioId);
    localInpPath = Path.Combine(tempDir, "network.inp");

    // Download input file
    var storageService = GetStorageService(scenario.BaseInputFile.StorageProvider);
    await storageService.DownloadToFileAsync(
      scenario.BaseInputFile.ContainerName,
      scenario.BaseInputFile.BlobName,
      localInpPath);

    logger.LogInformation($"Input file downloaded to: {localInpPath}");

    // ... REST OF EXISTING EXPORT LOGIC USING localInpPath ...

  }
  finally
  {
    // Cleanup
    localEpanetWrapper?.Dispose();
    if (localContext != null)
    {
      await localContext.DisposeAsync();
    }
    if (tempDir != null)
    {
      TempFileManager.CleanupTempDirectory(tempDir);
    }
  }
}

Phase 5: Migration & Testing

5.1 Migration Checklist

Run EF Core migration to create InputFiles table
Run data migration script to migrate existing scenarios
Verify all scenarios have BaseInputFileId
Test with LocalFileSystem provider first
Upload files to Azure Blob Storage
Update InputFile records to point to blobs
Change StorageProvider to AzureBlob
Test scenarios with blob storage
Remove old InputFile column (optional)

5.2 Testing Scenarios

Test 1: Upload Input File

curl -X POST http://localhost:5000/api/InputFile/upload \
  -F "file=@network.inp" \
  -F "name=Test Network" \
  -F "containerName=test-network"

Test 2: List Input Files

curl http://localhost:5000/api/InputFile

Test 3: Create Scenario with InputFile

curl -X POST http://localhost:5000/api/Scenario \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Test Scenario",
    "baseInputFileId": 1
  }'

Test 4: Run Scenario (with blob download)

curl -X POST http://localhost:5000/api/Scenario/start/1

Test 5: Export Scenario (with blob download)

curl -OJ http://localhost:5000/api/Scenario/download/1

5.3 Configuration for Testing

appsettings.Development.json:

{
  "InputFileStorage": {
    "Provider": "LocalFileSystem",
    "LocalFileSystem": {
      "BasePath": "C:\\Temp\\HydroTrek\\InputFiles"
    },
    "TempFileCleanup": {
      "Enabled": true,
      "MaxAgeHours": 1
    }
  }
}

appsettings.Production.json:

{
  "InputFileStorage": {
    "Provider": "AzureBlob",
    "AzureBlob": {
      "ServiceUrl": "https://hydrotrekst.blob.core.windows.net",
      "AdminAccountKey": "{{KEY_FROM_KEYVAULT}}",
      "Containers": {
        "network-client1": {},
        "network-client2": {}
      }
    },
    "TempFileCleanup": {
      "Enabled": true,
      "MaxAgeHours": 24
    }
  }
}

Phase 6: Frontend (Future Work)

6.1 Input File Management Page

List all input files
Upload new input files
Download input files
Delete input files (with warning if in use)
View file details and scenarios using it

6.2 Scenario Form Updates

Dropdown to select BaseInputFile instead of text input
"Upload New" button to upload while creating scenario
Display file size and upload date

Implementation Order

Phase 1: Database models and migration (Day 1)
Phase 2: Storage services (Day 1-2)
Phase 3: API endpoints (Day 2-3)
Phase 4: EpanetConsoleCore changes (Day 3-4)
Phase 5: Migration and testing (Day 4-5)
Phase 6: Frontend (Day 6-8)

Success Criteria

Ready to start implementation! 🚀

Begin with Phase 1 when ready.

Input File Blob Storage - Implementation Plan

📋 Implementation Phases​

Phase 1: Database Schema & Models ⏱️ 4-6 hours​

Phase 2: Storage Services ⏱️ 6-8 hours​

Phase 3: API Layer ⏱️ 6-8 hours​

Phase 4: EpanetConsoleCore Changes ⏱️ 4-6 hours​

Phase 5: Migration & Testing ⏱️ 6-8 hours​

Phase 6: Frontend (Future) ⏱️ 8-12 hours​

Phase 1: Database Schema & Models​

1.1 Create InputFile Entity​

1.2 Update Scenario Entity​

1.3 Update DbContext​

1.4 Create EF Core Migration​

1.5 Manual Data Migration Script​

Phase 2: Storage Services​

2.1 Install NuGet Packages​

2.2 Create Storage Service Interface​

2.3 Create Azure Blob Implementation​

2.4 Create Local File System Implementation​

2.5 Register Services in Startup​

Phase 3: API Layer​

3.1 Create InputFile DTOs​

3.2 Create InputFileController​

3.3 Update ScenarioController​

Phase 4: EpanetConsoleCore Changes​

4.1 Create Temp File Manager​

4.2 Copy Storage Services to EpanetConsoleCore​

4.3 Update Program.cs Main​

4.4 Update dwdRun Method​

4.5 Update ExportScenarioInputFile Method​

Phase 5: Migration & Testing​

5.1 Migration Checklist​

5.2 Testing Scenarios​

5.3 Configuration for Testing​

Phase 6: Frontend (Future Work)​

6.1 Input File Management Page​

6.2 Scenario Form Updates​

Implementation Order​

Success Criteria​

📋 Implementation Phases

Phase 1: Database Schema & Models ⏱️ 4-6 hours

Phase 2: Storage Services ⏱️ 6-8 hours

Phase 3: API Layer ⏱️ 6-8 hours

Phase 4: EpanetConsoleCore Changes ⏱️ 4-6 hours

Phase 5: Migration & Testing ⏱️ 6-8 hours

Phase 6: Frontend (Future) ⏱️ 8-12 hours

Phase 1: Database Schema & Models

1.1 Create InputFile Entity

1.2 Update Scenario Entity

1.3 Update DbContext

1.4 Create EF Core Migration

1.5 Manual Data Migration Script

Phase 2: Storage Services

2.1 Install NuGet Packages

2.2 Create Storage Service Interface

2.3 Create Azure Blob Implementation

2.4 Create Local File System Implementation

2.5 Register Services in Startup

Phase 3: API Layer

3.1 Create InputFile DTOs

3.2 Create InputFileController

3.3 Update ScenarioController

Phase 4: EpanetConsoleCore Changes

4.1 Create Temp File Manager

4.2 Copy Storage Services to EpanetConsoleCore

4.3 Update Program.cs Main

4.4 Update dwdRun Method

4.5 Update ExportScenarioInputFile Method

Phase 5: Migration & Testing

5.1 Migration Checklist

5.2 Testing Scenarios

5.3 Configuration for Testing

Phase 6: Frontend (Future Work)

6.1 Input File Management Page

6.2 Scenario Form Updates

Implementation Order

Success Criteria