using Noundry.Tuxedo.Exceptions;

// Auto-detect database provider
var wrappedConn = connection.WithExceptionTranslation();

// Or specify explicitly
var sqlServerConn = new SqlConnection(connectionString)
    .WithSqlServerExceptionTranslation();

try
{
    await wrappedConn.InsertAsync(user);
}
catch (UniqueConstraintException ex)
{
    // Access detailed constraint information
    if (ex.ColumnName?.Contains("Email") == true)
        return "Email already registered";

    return $"Duplicate value for {ex.ColumnName}";
}
catch (CannotInsertNullException ex)
{
    return $"{ex.ColumnName} is required";
}
catch (ReferenceConstraintException ex)
{
    return "Invalid foreign key reference";
}
catch (MaxLengthExceededException ex)
{
    return $"{ex.ColumnName} exceeds maximum length";
}

public async Task<Result> RegisterUserAsync(UserRegistration registration)
{
    var wrappedConn = _connection.WithExceptionTranslation();

    try
    {
        var user = new User
        {
            Email = registration.Email,
            Username = registration.Username,
            PasswordHash = HashPassword(registration.Password)
        };

        await wrappedConn.InsertAsync(user);
        return Result.Success();
    }
    catch (UniqueConstraintException ex) when (ex.ColumnName?.Contains("Email") == true)
    {
        return Result.Error("This email address is already registered");
    }
    catch (UniqueConstraintException ex) when (ex.ColumnName?.Contains("Username") == true)
    {
        return Result.Error("This username is already taken");
    }
    catch (CannotInsertNullException ex)
    {
        return Result.Error($"{ex.ColumnName} is required");
    }
}

using Noundry.Tuxedo.Resiliency;
using Noundry.Tuxedo.Exceptions;
using Polly;

var retryPolicy = Policy
    .Handle<Exception>()
    .WaitAndRetryAsync(3, attempt => TimeSpan.FromSeconds(attempt));

// Exception translation + Retry policy (order doesn't matter)
var connection = new SqlConnection(connectionString)
    .WithExceptionTranslation()
    .WithRetryPolicy(retryPolicy);

public async Task<BulkOperationResult> ImportProductsAsync(List<Product> products)
{
    var wrappedConn = _connection.WithExceptionTranslation();
    var result = new BulkOperationResult();

    using var transaction = await wrappedConn.BeginTransactionAsync();
    try
    {
        foreach (var product in products)
        {
            try
            {
                await wrappedConn.InsertAsync(product, transaction);
                result.SuccessCount++;
            }
            catch (UniqueConstraintException ex)
            {
                result.Errors.Add($"Duplicate SKU: {product.SKU}");
                result.FailureCount++;
            }
            catch (MaxLengthExceededException ex)
            {
                result.Errors.Add($"{ex.ColumnName} exceeds max length: {product.Name}");
                result.FailureCount++;
            }
        }

        await transaction.CommitAsync();
        return result;
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

using Noundry.Tuxedo.Contrib; // Entity extensions

// Get entity by ID
var product = await _connection.GetAsync<Product>(123);

// Get all entities
var allProducts = await _connection.GetAllAsync<Product>();

// Insert entity and get generated ID
var newProduct = new Product
{
    Name = "Laptop",
    Price = 999.99m,
    Category = "Electronics",
    Stock = 50
};
var productId = await _connection.InsertAsync(newProduct);

// Update entire entity
product.Price = 899.99m;
product.Stock = 45;
await _connection.UpdateAsync(product);

// Partial update - only specific properties
product.Price = 849.99m;
await _connection.UpdateAsync(product,
    propertiesToUpdate: new[] { "Price" });

// Update by key values (no need to fetch first)
await _connection.UpdateAsync<Product>(
    keyValues: new { Id = 123 },
    updateValues: new { Stock = 40, LastModified = DateTime.UtcNow });

// Delete entity
await _connection.DeleteAsync(product);

// Delete all entities of type
await _connection.DeleteAllAsync<TempProduct>();

using Noundry.Tuxedo.QueryBuilder;

// Basic query with strongly-typed expressions
var electronics = await QueryBuilder.Query<Product>()
    .Where(p => p.Category == "Electronics")
    .Where(p => p.Price > 100)
    .OrderByDescending(p => p.Price)
    .Take(10)
    .ToListAsync(_connection);

// WhereIn for multiple values
var categoryIds = new[] { 1, 2, 3, 4 };
var filtered = await QueryBuilder.Query<Product>()
    .WhereIn(p => p.CategoryId, categoryIds)
    .WhereNotIn(p => p.StatusId, new[] { 99, 100 })
    .ToListAsync(_connection);

// WhereBetween for range queries
var priceRange = await QueryBuilder.Query<Product>()
    .WhereBetween(p => p.Price, 10.00m, 99.99m)
    .WhereBetween(p => p.CreatedDate, startDate, endDate)
    .ToListAsync(_connection);

// NULL checks
var withDescription = await QueryBuilder.Query<Product>()
    .WhereNotNull(p => p.Description)
    .WhereNull(p => p.DeletedAt)
    .ToListAsync(_connection);

// Complex boolean logic with AND/OR
var complex = await QueryBuilder.Query<Product>()
    .Where(p => p.Category == "Electronics")
    .Or(p => p.Featured == true)
    .And(p => p.InStock == true)
    .ToListAsync(_connection);

public async Task<IEnumerable<Product>> SearchProductsAsync(
    string searchTerm,
    decimal? minPrice,
    decimal? maxPrice,
    int[] categoryIds,
    bool? inStockOnly,
    int page = 0,
    int pageSize = 20)
{
    // Build query dynamically based on provided filters
    var query = QueryBuilder.Query<Product>();

    // Add search term if provided
    if (!string.IsNullOrEmpty(searchTerm))
        query.Where("Name LIKE @searchTerm OR Description LIKE @searchTerm",
            new { searchTerm = $"%{searchTerm}%" });

    // Add price range filters
    if (minPrice.HasValue)
        query.Where(p => p.Price >= minPrice.Value);

    if (maxPrice.HasValue)
        query.Where(p => p.Price <= maxPrice.Value);

    // Add category filter
    if (categoryIds?.Any() == true)
        query.WhereIn(p => p.CategoryId, categoryIds);

    // Add stock filter
    if (inStockOnly == true)
        query.Where(p => p.Stock > 0);

    // Apply sorting and pagination
    query.OrderBy(p => p.Name)
         .Skip(page * pageSize)
         .Take(pageSize);

    return await query.ToListAsync(_connection);
}

// INNER JOIN with type-safe expressions
var productsWithCategories = await QueryBuilder.Query<Product>()
    .InnerJoin<Category>((p, c) => p.CategoryId == c.Id)
    .Where(p => p.Price > 50)
    .Select("p.*, c.Name as CategoryName")
    .ToListAsync(_connection);

// LEFT JOIN to include products without categories
var allProductsWithOptionalCategory = await QueryBuilder.Query<Product>()
    .LeftJoin<Category>((p, c) => p.CategoryId == c.Id)
    .OrderBy(p => p.Name)
    .ToListAsync(_connection);

// Multiple JOINs for complex queries
var orderDetails = await QueryBuilder.Query<Order>()
    .InnerJoin<Customer>((o, c) => o.CustomerId == c.Id)
    .InnerJoin<Product>((o, p) => o.ProductId == p.Id)
    .WhereBetween(o => o.OrderDate, DateTime.Today.AddDays(-30), DateTime.Today)
    .Select("o.*, c.Name as CustomerName, p.Name as ProductName")
    .ToListAsync(_connection);

// COUNT aggregation
var activeProductCount = await QueryBuilder.Query<Product>()
    .Where(p => p.Active == true)
    .CountAsync(_connection);

// GROUP BY with aggregations
var categoryStats = await QueryBuilder.Query<Product>()
    .GroupBy(p => p.Category)
    .Having(p => p.Price > 10)
    .Select("Category, COUNT(*) as Count, AVG(Price) as AvgPrice, MAX(Price) as MaxPrice")
    .ToListAsync<CategoryStats>(_connection);

public async Task<Result> ProcessOrderAsync(Order order)
{
    var wrappedConn = _connection
        .WithExceptionTranslation()
        .WithRetryPolicy(_retryPolicy);

    using var transaction = await wrappedConn.BeginTransactionAsync();
    try
    {
        // 1. Insert order
        var orderId = await wrappedConn.InsertAsync(order, transaction);

        // 2. Update inventory
        foreach (var item in order.Items)
        {
            var rowsAffected = await wrappedConn.ExecuteAsync(
                "UPDATE Products SET Stock = Stock - @Quantity WHERE Id = @ProductId AND Stock >= @Quantity",
                new { item.Quantity, item.ProductId }, transaction);

            if (rowsAffected == 0)
                throw new InsufficientStockException(item.ProductId);
        }

        // 3. Create audit log
        await wrappedConn.InsertAsync(new AuditLog
        {
            EntityType = "Order",
            EntityId = orderId,
            Action = "Created",
            UserId = order.UserId,
            Timestamp = DateTime.UtcNow
        }, transaction);

        await transaction.CommitAsync();
        return Result.Success(orderId);
    }
    catch (ReferenceConstraintException ex)
    {
        await transaction.RollbackAsync();
        return Result.Error("Invalid customer or product reference");
    }
    catch (InsufficientStockException ex)
    {
        await transaction.RollbackAsync();
        return Result.Error($"Insufficient stock for product {ex.ProductId}");
    }
    catch
    {
        await transaction.RollbackAsync();
        throw;
    }
}

// Combine entity extensions with QueryBuilder for optimal performance
public async Task<DashboardData> GetDashboardDataAsync(int userId)
{
    // 1. Get user by ID (strongly-typed)
    var user = await _connection.GetAsync<User>(userId);

    // 2. Get recent orders with QueryBuilder (filtered and paginated)
    var recentOrders = await QueryBuilder.Query<Order>()
        .Where(o => o.UserId == userId)
        .OrderByDescending(o => o.OrderDate)
        .Take(10)
        .ToListAsync(_connection);

    // 3. Get order count
    var totalOrders = await QueryBuilder.Query<Order>()
        .Where(o => o.UserId == userId)
        .CountAsync(_connection);

    // 4. Calculate total revenue with aggregation query
    var totalRevenue = await _connection.ExecuteScalarAsync<decimal?>(
        @"SELECT SUM(TotalAmount)
          FROM Orders
          WHERE UserId = @UserId AND Status = @Status",
        new { UserId = userId, Status = "Completed" }) ?? 0;

    return new DashboardData
    {
        User = user,
        RecentOrders = recentOrders,
        TotalOrders = totalOrders,
        TotalRevenue = totalRevenue
    };
}

// Efficient pagination with total count
public async Task<PagedResult<Product>> GetPagedProductsAsync(
    string category,
    int page,
    int pageSize)
{
    // Build base query
    var query = QueryBuilder.Query<Product>()
        .Where(p => p.Category == category)
        .Where(p => p.Active == true);

    // Get total count (before pagination)
    var totalCount = await query.CountAsync(_connection);

    // Get paginated results
    var products = await query
        .OrderBy(p => p.Name)
        .Skip(page * pageSize)
        .Take(pageSize)
        .ToListAsync(_connection);

    return new PagedResult<Product>
    {
        Items = products,
        TotalCount = totalCount,
        Page = page,
        PageSize = pageSize
    };
}

// Pages/Auth/Login.cshtml.cs
public class LoginModel : PageModel
{
    private readonly IOAuthService _oauthService;
    private readonly ILogger<LoginModel> _logger;

    public LoginModel(IOAuthService oauthService, ILogger<LoginModel> logger)
    {
        _oauthService = oauthService;
        _logger = logger;
    }

    public IActionResult OnGetAsync(string provider, string returnUrl = null)
    {
        try
        {
            // Generate secure state and PKCE parameters
            var authRequest = _oauthService.CreateAuthorizationRequest(
                provider,
                returnUrl ?? "/Dashboard"
            );

            // Store state in secure cookie for validation
            Response.Cookies.Append("oauth_state", authRequest.State, new CookieOptions
            {
                HttpOnly = true,
                Secure = true,
                SameSite = SameSiteMode.Lax,
                MaxAge = TimeSpan.FromMinutes(10)
            });

            return Redirect(authRequest.AuthorizationUrl);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "OAuth authorization request failed for provider: {Provider}", provider);
            TempData["Error"] = "Authentication initialization failed";
            return RedirectToPage("/Error");
        }
    }

    public async Task<IActionResult> OnGetCallbackAsync(string code, string state)
    {
        try
        {
            // Validate state to prevent CSRF
            var savedState = Request.Cookies["oauth_state"];
            if (savedState != state)
            {
                _logger.LogWarning("OAuth state mismatch - possible CSRF attack");
                return RedirectToPage("/Error");
            }

            // Exchange code for tokens
            var tokenResponse = await _oauthService.ExchangeCodeForTokensAsync(code, state);

            // Create user session with JWT
            await SignInUserAsync(tokenResponse);

            // Clean up state cookie
            Response.Cookies.Delete("oauth_state");

            return Redirect(tokenResponse.ReturnUrl ?? "/Dashboard");
        }
        catch (OAuthException ex)
        {
            _logger.LogError(ex, "OAuth callback failed: {Error}", ex.Message);
            TempData["Error"] = "Authentication failed. Please try again.";
            return RedirectToPage("/Auth/Login");
        }
    }
}

// Middleware/TokenRefreshMiddleware.cs
public class TokenRefreshMiddleware
{
    private readonly RequestDelegate _next;
    private readonly ILogger<TokenRefreshMiddleware> _logger;

    public async Task InvokeAsync(
        HttpContext context,
        ITokenService tokenService)
    {
        if (context.User.Identity?.IsAuthenticated == true)
        {
            // Check token expiration
            var expClaim = context.User.FindFirst("exp")?.Value;
            if (long.TryParse(expClaim, out var exp))
            {
                var expiryTime = DateTimeOffset.FromUnixTimeSeconds(exp);
                var timeUntilExpiry = expiryTime - DateTimeOffset.UtcNow;

                // Refresh if within 5 minutes of expiry
                if (timeUntilExpiry.TotalMinutes < 5)
                {
                    try
                    {
                        var refreshToken = context.Request.Cookies["refresh_token"];
                        if (!string.IsNullOrEmpty(refreshToken))
                        {
                            var newTokens = await tokenService.RefreshTokensAsync(refreshToken);

                            // Update access token cookie
                            context.Response.Cookies.Append("access_token", newTokens.AccessToken,
                                new CookieOptions
                                {
                                    HttpOnly = true,
                                    Secure = true,
                                    SameSite = SameSiteMode.Strict,
                                    MaxAge = TimeSpan.FromHours(1)
                                });

                            _logger.LogInformation("Access token refreshed for user: {UserId}",
                                context.User.FindFirst("sub")?.Value);
                        }
                    }
                    catch (Exception ex)
                    {
                        _logger.LogError(ex, "Token refresh failed");
                        // Allow request to continue - user will need to re-authenticate
                    }
                }
            }
        }

        await _next(context);
    }
}

// Services/AuthenticatedApiClient.cs
public class AuthenticatedApiClient
{
    private readonly HttpClient _httpClient;
    private readonly ITokenService _tokenService;
    private readonly IHttpContextAccessor _httpContextAccessor;

    public async Task<T> GetAsync<T>(string url)
    {
        var token = await GetValidTokenAsync();
        _httpClient.DefaultRequestHeaders.Authorization =
            new AuthenticationHeaderValue("Bearer", token);

        var response = await _httpClient.GetAsync(url);

        if (response.StatusCode == HttpStatusCode.Unauthorized)
        {
            // Attempt token refresh on 401
            var refreshedToken = await RefreshAndGetTokenAsync();
            if (refreshedToken != null)
            {
                _httpClient.DefaultRequestHeaders.Authorization =
                    new AuthenticationHeaderValue("Bearer", refreshedToken);
                response = await _httpClient.GetAsync(url);
            }
        }

        response.EnsureSuccessStatusCode();
        var content = await response.Content.ReadAsStringAsync();
        return JsonSerializer.Deserialize<T>(content);
    }

    private async Task<string> GetValidTokenAsync()
    {
        var context = _httpContextAccessor.HttpContext;
        var token = context?.Request.Cookies["access_token"];

        // Validate token expiration
        if (_tokenService.IsTokenExpired(token))
        {
            return await RefreshAndGetTokenAsync();
        }

        return token;
    }

    private async Task<string> RefreshAndGetTokenAsync()
    {
        var context = _httpContextAccessor.HttpContext;
        var refreshToken = context?.Request.Cookies["refresh_token"];

        if (string.IsNullOrEmpty(refreshToken))
            throw new UnauthorizedException("No refresh token available");

        var newTokens = await _tokenService.RefreshTokensAsync(refreshToken);

        // Update cookies
        context.Response.Cookies.Append("access_token", newTokens.AccessToken,
            new CookieOptions { HttpOnly = true, Secure = true });

        return newTokens.AccessToken;
    }
}

using System.Data;
using Noundry.Tuxedo.Patterns;

public class ProductService
{
    private readonly TuxedoRepository<Product> _productRepo;
    private readonly IDbConnection _connection;

    public ProductService(IDbConnection connection)
    {
        _connection = connection;
        _productRepo = new TuxedoRepository<Product>(connection);
    }

    // Find with LINQ expressions
    public async Task<IEnumerable<Product>> GetLowStockProductsAsync(int threshold)
    {
        return await _productRepo.FindAsync(p => p.Stock < threshold && !p.Discontinued);
    }

    // Count with predicate
    public async Task<int> GetActiveProductCountAsync()
    {
        return await _productRepo.CountAsync(p => p.IsActive);
    }

    // Paginated results
    public async Task<PagedResult<Product>> GetProductsPagedAsync(int page, int size)
    {
        return await _productRepo.GetPagedAsync(page, size, p => p.IsActive);
    }
}

using System.Data;
using Noundry.Tuxedo.QueryBuilder;
using Noundry.Tuxedo.DependencyInjection;

public class ProductSearchService
{
    private readonly IDbConnection _connection;

    public ProductSearchService(IDbConnection connection)
    {
        _connection = connection;
    }

    public async Task<IEnumerable<Product>> SearchAsync(SearchCriteria criteria)
    {
        var builder = new QueryBuilder<Product>(TuxedoDialect.SqlServer);

        builder.SelectAll();

        if (!string.IsNullOrEmpty(criteria.Category))
            builder.Where(p => p.Category == criteria.Category);

        if (criteria.MinPrice.HasValue && criteria.MaxPrice.HasValue)
            builder.WhereBetween(p => p.Price, criteria.MinPrice.Value, criteria.MaxPrice.Value);

        if (criteria.Tags?.Any() == true)
            builder.WhereIn(p => p.TagId, criteria.Tags);

        builder.OrderBy(p => p.Name);

        if (criteria.PageSize > 0)
        {
            builder.Skip(criteria.Page * criteria.PageSize);
            builder.Take(criteria.PageSize);
        }

        return await builder.ToListAsync(_connection);
    }
}

using System.Data;
using Noundry.Tuxedo.Patterns;

public class OrderService
{
    private readonly IDbConnection _connection;

    public OrderService(IDbConnection connection)
    {
        _connection = connection;
    }

    public async Task<Order> CreateOrderAsync(Order order)
    {
        using var uow = new UnitOfWork(_connection);

        // Create repositories within the same transaction
        var orderRepo = uow.GetRepository<Order>();
        var productRepo = uow.GetRepository<Product>();
        var inventoryRepo = uow.GetRepository<InventoryLog>();

        try
        {
            // 1. Create order
            await orderRepo.AddAsync(order);

            // 2. Reduce inventory for each item
            foreach (var item in order.Items)
            {
                var product = await productRepo.GetByIdAsync(item.ProductId);

                if (product.Stock < item.Quantity)
                    throw new InvalidOperationException($"Insufficient stock for {product.Name}");

                product.Stock -= item.Quantity;
                await productRepo.UpdateAsync(product);

                // 3. Log inventory change
                await inventoryRepo.AddAsync(new InventoryLog
                {
                    ProductId = product.Id,
                    Change = -item.Quantity,
                    Reason = $"Order {order.Id}",
                    Timestamp = DateTime.UtcNow
                });
            }

            // Commit all changes atomically
            await uow.CommitAsync();
            return order;
        }
        catch
        {
            await uow.RollbackAsync();
            throw;
        }
    }
}

using System.Data;
using Noundry.Tuxedo;

public class ReportService
{
    private readonly IDbConnection _connection;

    public ReportService(IDbConnection connection)
    {
        _connection = connection;
    }

    // Complex aggregation query
    public async Task<IEnumerable<SalesReport>> GetMonthlySalesAsync(int year)
    {
        var sql = @"
            SELECT
                MONTH(OrderDate) AS Month,
                COUNT(*) AS OrderCount,
                SUM(TotalAmount) AS TotalSales,
                AVG(TotalAmount) AS AverageSale
            FROM Orders
            WHERE YEAR(OrderDate) = @Year
            GROUP BY MONTH(OrderDate)
            ORDER BY Month";

        return await _connection.QueryAsync<SalesReport>(sql, new { Year = year });
    }

    // Multi-mapping for complex objects
    public async Task<IEnumerable<OrderWithDetails>> GetOrdersWithCustomerAsync()
    {
        var sql = @"
            SELECT o.*, c.*
            FROM Orders o
            INNER JOIN Customers c ON o.CustomerId = c.Id
            WHERE o.Status = @Status";

        var orders = await _connection.QueryAsync<Order, Customer, OrderWithDetails>(
            sql,
            (order, customer) => new OrderWithDetails
            {
                Order = order,
                Customer = customer
            },
            new { Status = "Active" },
            splitOn: "Id"
        );

        return orders;
    }

    // Stored procedure execution
    public async Task<int> ArchiveOldOrdersAsync(int daysOld)
    {
        return await _connection.ExecuteAsync(
            "sp_ArchiveOrders",
            new { DaysOld = daysOld },
            commandType: CommandType.StoredProcedure
        );
    }
}

using Noundry.Tuxedo.Auditor;
using Noundry.Tuxedo.Auditor.UserProviders;

// Wrap connection with automatic auditing
var auditedConn = connection.WithAuditing(config =>
{
    config.UserProvider = new StaticAuditUserProvider("admin");
    config.AuditTableName = "audit_log"; // Optional, default
});

// All CRUD operations are automatically audited
await auditedConn.InsertAsync(product);  // Logged: Insert with new values
await auditedConn.UpdateAsync(product);  // Logged: Update with old/new values + changed columns
await auditedConn.DeleteAsync(product);  // Logged: Delete with old values

// Static user (testing, background jobs)
config.UserProvider = new StaticAuditUserProvider("system");

// Delegate provider (custom logic)
config.UserProvider = new DelegateAuditUserProvider(() =>
{
    return Thread.CurrentPrincipal?.Identity?.Name ?? "Anonymous";
});

// HTTP Context (ASP.NET Core web apps)
config.UserProvider = new HttpContextAuditUserProvider(httpContextAccessor);

// In Program.cs or Startup.cs
services.AddHttpContextAccessor();

services.AddScoped<IDbConnection>(sp =>
{
    var httpContext = sp.GetRequiredService<IHttpContextAccessor>();
    var connectionString = sp.GetRequiredService<IConfiguration>()
        .GetConnectionString("DefaultConnection");

    var connection = new SqlConnection(connectionString);

    return connection.WithAuditing(config =>
    {
        config.UserProvider = new HttpContextAuditUserProvider(httpContext);
        config.ExcludeTables("Logs", "TempData");
    });
});

public class User
{
    [Key]
    public int Id { get; set; }
    public string Email { get; set; }
    public bool IsDeleted { get; set; }
    public DateTime? DeletedAt { get; set; }
}

// Regular update
user.Email = "newemail@example.com";
await auditedConn.UpdateAsync(user);
// Event Type: "Update"

// Soft delete (automatically detected)
user.IsDeleted = true;
user.DeletedAt = DateTime.UtcNow;
await auditedConn.UpdateAsync(user);
// Event Type: "SoftDelete"

using Noundry.Tuxedo.Auditor;
using Noundry.Tuxedo.Exceptions;
using Noundry.Tuxedo.Resiliency;

// Combine all three features (order doesn't matter!)
var connection = new SqlConnection(connectionString)
    .WithAuditing(config => config.UserProvider = new StaticAuditUserProvider("admin"))
    .WithExceptionTranslation()
    .WithRetryPolicy(retryPolicy);

// Get audit history for a specific entity
var history = await connection.QueryAsync<AuditEvent>(@"
    SELECT * FROM audit_log
    WHERE table_name = @Table AND entity_id = @EntityId
    ORDER BY timestamp DESC",
    new { Table = "Users", EntityId = "123" }
);

// Get all changes by a specific user
var userChanges = await connection.QueryAsync<AuditEvent>(
    "SELECT * FROM audit_log WHERE username = @Username ORDER BY timestamp DESC",
    new { Username = "admin" }
);

// Get today's changes
var todaysChanges = await connection.QueryAsync<AuditEvent>(@"
    SELECT * FROM audit_log
    WHERE timestamp >= @StartOfDay
    ORDER BY timestamp DESC",
    new { StartOfDay = DateTime.Today }
);

# Launch interactive CLI with beautiful prompts
$ slurp data.csv

# The CLI will guide you through:
# - Database provider selection
# - Connection configuration
# - Table name and schema options

# Direct command with all parameters
$ slurp data.csv -p postgres -s localhost -d mydb -u myuser --password mypass

# SQL Server example
$ slurp sales.csv -p sqlserver -s localhost -d Sales -u sa --password MyPass123!

# SQLite example (simple local database)
$ slurp inventory.csv -p sqlite -d inventory.db

using Noundry.Slurp;

// Configure the ingestion engine
var config = new SlurpConfiguration
{
    Provider = "postgres",
    Server = "localhost",
    Database = "mydb",
    Username = "user",
    Password = "pass"
};

// Create and run the engine
var engine = new SlurpEngine(config);
var result = await engine.IngestAsync("data.csv");

// Check results
Console.WriteLine($"Ingested {result.RowCount} rows in {result.ElapsedTime}");

# Start the interactive generator
$ noundry-connector generate

# Or use command line options:
$ noundry-connector generate \
  --url "https://jsonplaceholder.typicode.com" \
  --client-name "JsonPlaceholderClient" \
  --namespace "MyApp.ApiClients"

// Register in DI
builder.Services.AddJsonPlaceholderClient("https://jsonplaceholder.typicode.com");

// Inject and use
public async Task<IEnumerable<Post>> GetRecentPostsAsync()
{
    var posts = await _client.GetAllPostsAsync();
    return posts.Where(p => p.UserId <= 5)
                    .OrderByDescending(p => p.Id)
                    .Take(10);
}

// 1. Define your model
public class User
{
    [JsonPropertyName("id")]
    public int Id { get; set; }

    [JsonPropertyName("email")]
    public string Email { get; set; }
}

// 2. Define Refit interface
public interface IUserApi
{
    [Get("/users")]
    Task<IEnumerable<User>> GetUsersAsync();

    [Post("/users")]
    Task<User> CreateUserAsync([Body] User user);
}

// 3. Register in DI
services.AddConnector<IUserApi>(options =>
{
    options.BaseUrl = "https://api.example.com";
});

services.AddTokenAuthentication("your-api-token");
services.AddConnector<IYourApi>(opt => opt.BaseUrl = "https://api.example.com");

services.AddOAuthAuthentication(config =>
{
    config.ClientId = configuration["GitHub:ClientId"];
    config.ClientSecret = configuration["GitHub:ClientSecret"];
    config.TokenEndpoint = "https://github.com/login/oauth/access_token";
});

var orders = await orderClient.GetOrdersAsync();

var vipCustomers = orders
    .GroupBy(o => o.CustomerId)
    .Select(g => new {
        CustomerId = g.Key,
        TotalSpent = g.Sum(o => o.TotalAmount),
        OrderCount = g.Count()
    })
    .Where(c => c.TotalSpent > 10000)
    .OrderByDescending(c => c.TotalSpent);

public async Task<List<Product>> GetRecommendationsAsync(int customerId)
{
    var orders = await _orderApi.GetCustomerOrdersAsync(customerId);
    var products = await _productApi.GetProductsAsync();

    var preferredCategories = orders
        .SelectMany(o => o.Items)
        .Join(products, item => item.ProductId, p => p.Id, (i, p) => p.CategoryId)
        .GroupBy(cat => cat)
        .OrderByDescending(g => g.Count())
        .Take(3);

    return products
        .Where(p => preferredCategories.Contains(p.CategoryId))
        .OrderByDescending(p => p.Rating)
        .Take(10)
        .ToList();
}

# .github/workflows/deploy.yml
name: Deploy to Production

on:
  push:
    branches: [main]

jobs:
  deploy:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3

      # Store encryption key in GitHub Secrets
      - name: Setup environment
        env:
          DOTENVX_KEY: ${{ secrets.DOTENVX_ENCRYPTION_KEY }}
        run: |
          # Encrypt production secrets
          dotnet tool install -g Noundry.DotEnvX.Cli
          dotenvx encrypt --file .env.production --key $DOTENVX_KEY

          # Verify encrypted values
          dotenvx validate --file .env.production

      - name: Deploy application
        run: |
          dotnet publish -c Release
          dotnet deploy --environment production

// Services/EnvironmentSecretManager.cs
public class EnvironmentSecretManager
{
    private readonly IConfiguration _configuration;
    private readonly IWebHostEnvironment _environment;

    public EnvironmentSecretManager(
        IConfiguration configuration,
        IWebHostEnvironment environment)
    {
        _configuration = configuration;
        _environment = environment;
    }

    public string GetSecret(string key)
    {
        // Development: Use plain text secrets from .env.development
        if (_environment.IsDevelopment())
        {
            return _configuration[key] ?? throw new InvalidOperationException(
                $"Secret '{key}' not found in .env.development");
        }

        // Staging: Use encrypted secrets from .env.staging
        if (_environment.IsStaging())
        {
            var stagingValue = _configuration[$"STAGING_{key}"] ?? _configuration[key];
            return stagingValue ?? throw new InvalidOperationException(
                $"Secret '{key}' not found in .env.staging");
        }

        // Production: Use encrypted secrets with fallback to system environment
        if (_environment.IsProduction())
        {
            var prodValue = _configuration[$"PROD_{key}"]
                          ?? _configuration[key]
                          ?? Environment.GetEnvironmentVariable(key);

            if (string.IsNullOrEmpty(prodValue))
            {
                // Log security event
                throw new SecurityException(
                    $"Required production secret '{key}' not found. Application cannot start.");
            }

            return prodValue;
        }

        throw new InvalidOperationException("Unknown environment");
    }
}

// Program.cs - Setup
builder.Configuration.AddDotEnvX(options =>
{
    // Load environment-specific file
    var env = builder.Environment.EnvironmentName.ToLower();
    options.Files = new[] { ".env", $".env.{env}" };

    // Get encryption key from system environment (set by deployment platform)
    options.EncryptionKey = Environment.GetEnvironmentVariable("DOTENVX_KEY");

    // Require encryption in production
    options.RequireEncryption = builder.Environment.IsProduction();
});

// Services/KeyRotationService.cs
public class KeyRotationService
{
    private readonly ILogger<KeyRotationService> _logger;

    public async Task RotateEncryptionKeyAsync(
        string envFilePath,
        string oldKey,
        string newKey)
    {
        _logger.LogInformation("Starting encryption key rotation for {FilePath}", envFilePath);

        try
        {
            // 1. Read encrypted file with old key
            var oldEncryptor = new DotEnvXEncryptor(oldKey);
            var envVariables = await File.ReadAllLinesAsync(envFilePath);

            // 2. Decrypt all encrypted values
            var decryptedValues = new Dictionary<string, string>();
            foreach (var line in envVariables)
            {
                if (line.Contains("encrypted:"))
                {
                    var parts = line.Split('=', 2);
                    var key = parts[0].Trim();
                    var encryptedValue = parts[1].Replace("encrypted:", "").Trim();

                    var decrypted = oldEncryptor.Decrypt(encryptedValue);
                    decryptedValues[key] = decrypted;
                }
            }

            // 3. Re-encrypt with new key
            var newEncryptor = new DotEnvXEncryptor(newKey);
            var newLines = new List<string>();

            foreach (var line in envVariables)
            {
                if (line.Contains("encrypted:"))
                {
                    var key = line.Split('=')[0].Trim();
                    var reencrypted = newEncryptor.Encrypt(decryptedValues[key]);
                    newLines.Add($"{key}=encrypted:{reencrypted}");
                }
                else
                {
                    newLines.Add(line);
                }
            }

            // 4. Backup old file
            var backupPath = $"{envFilePath}.backup.{DateTime.UtcNow:yyyyMMddHHmmss}";
            File.Copy(envFilePath, backupPath);

            // 5. Write new encrypted file
            await File.WriteAllLinesAsync(envFilePath, newLines);

            _logger.LogInformation("Key rotation completed successfully. Backup saved to {BackupPath}", backupPath);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Key rotation failed");
            throw;
        }
    }
}

# .env.example (checked into git - no secrets)
APP_NAME=MyApp
DATABASE_HOST=localhost
DATABASE_PORT=5432
DATABASE_NAME=myapp_dev

# Placeholders for required secrets
DATABASE_PASSWORD=<your-local-db-password>
JWT_SECRET=<generate-32-char-secret>
OAUTH_CLIENT_SECRET=<get-from-google-console>

# .env.local (NOT checked into git - actual secrets)
DATABASE_PASSWORD=my-local-password-123
JWT_SECRET=abcdef1234567890abcdef1234567890
OAUTH_CLIENT_SECRET=actual-oauth-secret-from-google

# .gitignore
.env.local
.env.*.local
.env.production

# Setup script for new developers
# scripts/setup-env.sh
#!/bin/bash

if [ ! -f .env.local ]; then
    echo "Creating .env.local from .env.example..."
    cp .env.example .env.local

    # Generate secure JWT secret
    JWT_SECRET=$(openssl rand -base64 32)
    sed -i "s/<generate-32-char-secret>/$JWT_SECRET/" .env.local

    echo "✅ .env.local created successfully"
    echo "⚠️  Please update remaining placeholders in .env.local"
else
    echo ".env.local already exists"
fi