/*
 * Dapper Setup in an ASP.NET Core Project
 * 
 * This code block demonstrates how to install Dapper via NuGet and
 * configure a basic database connection string in appsettings.json.
 * It sets the foundation for Dapper usage by defining the database
 * connection that Dapper will utilize for its operations.
 */

// 1. Install Dapper NuGet Package:
// Open your Package Manager Console and run:
// Install-Package Dapper

// Alternatively, use the .NET CLI:
// dotnet add package Dapper

// 2. Configure your Connection String in appsettings.json:
// This is a standard practice in ASP.NET Core to manage database connections.
// Replace 'YourDatabaseName', 'YourUser', and 'YourPassword' with actual values.
// For production, always use secure methods for credentials (e.g., Azure Key Vault, environment variables).
{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=YourDatabaseName;User ID=YourUser;Password=YourPassword;TrustServerCertificate=True"
  }
}

// 3. Example of how to retrieve the connection string in your C# code (e.g., in Startup.cs or Program.cs for .NET 6+):
// We will demonstrate DI setup in a later section, but this shows basic retrieval.
/*
using Microsoft.Extensions.Configuration;

public class Startup
{
    public IConfiguration Configuration { get; }

    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public void ConfigureServices(IServiceCollection services)
    {
        string connectionString = Configuration.GetConnectionString("DefaultConnection");
        // You would then use this connection string to create IDbConnection instances.
        // Example: services.AddScoped<IDbConnection>(sp => new SqlConnection(connectionString));
    }
}
*/

/*
 * Core Dapper Operations: CRUD and Advanced Querying
 * 
 * This block demonstrates essential Dapper methods for common database operations
 * (Select, Insert, Update, Delete) and introduces advanced features like
 * parameterized queries and multi-mapping. It provides a practical guide
 * to executing raw SQL efficiently.
 */

using Dapper;
using Microsoft.Data.SqlClient; // Or other database providers like Npgsql, MySql.Data
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

// Assume connectionString is configured as shown in the previous section.
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public int ProductId { get; set; }
}

public class OrderDetail
{
    public int OrderId { get; set; }
    public Product Product { get; set; }
    public Order Order { get; set; }
}

public class DapperRepository
{
    private readonly string _connectionString;

    public DapperRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    // Method to get a single product by Id (SELECT)
    public async Task<Product> GetProductByIdAsync(int id)
    {
        using IDbConnection connection = new SqlConnection(_connectionString);
        string sql = "SELECT Id, Name, Price FROM Products WHERE Id = @Id;";
        return await connection.QueryFirstOrDefaultAsync<Product>(sql, new { Id = id });
    }

    // Method to get all products (SELECT ALL)
    public async Task<IEnumerable<Product>> GetAllProductsAsync()
    {
        using IDbConnection connection = new SqlConnection(_connectionString);
        string sql = "SELECT Id, Name, Price FROM Products;";
        return await connection.QueryAsync<Product>(sql);
    }

    // Method to insert a new product (INSERT)
    public async Task<int> AddProductAsync(Product product)
    {
        using IDbConnection connection = new SqlConnection(_connectionString);
        string sql = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price); SELECT SCOPE_IDENTITY();";
        // Using SCOPE_IDENTITY() for SQL Server to return the newly generated ID.
        return await connection.ExecuteScalarAsync<int>(sql, product);
    }

    // Method to update an existing product (UPDATE)
    public async Task<bool> UpdateProductAsync(Product product)
    {
        using IDbConnection connection = new SqlConnection(_connectionString);
        string sql = "UPDATE Products SET Name = @Name, Price = @Price WHERE Id = @Id;";
        int rowsAffected = await connection.ExecuteAsync(sql, product);
        return rowsAffected > 0;
    }

    // Method to delete a product (DELETE)
    public async Task<bool> DeleteProductAsync(int id)
    {
        using IDbConnection connection = new SqlConnection(_connectionString);
        string sql = "DELETE FROM Products WHERE Id = @Id;";
        int rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
        return rowsAffected > 0;
    }

    // Method demonstrating multi-mapping for complex objects (e.g., Order with Product)
    // This maps one row to multiple objects, based on a split-on column.
    public async Task<IEnumerable<OrderDetail>> GetOrderDetailsAsync()
    {
        using IDbConnection connection = new SqlConnection(_connectionString);
        string sql = @"SELECT o.Id AS OrderId, o.OrderDate, p.Id AS ProductId, p.Name, p.Price 
                       FROM Orders o JOIN Products p ON o.ProductId = p.Id;";

        return await connection.QueryAsync<Order, Product, OrderDetail>(
            sql,
            (order, product) => 
            {
                var orderDetail = new OrderDetail { Order = order, Product = product };
                // You might need to set common IDs if they exist in both objects
                // e.Product.Id = product.Id; // or similar mapping
                return orderDetail;
            },
            splitOn: "ProductId"); // Specifies the column where the split between types occurs
    }

    // Method demonstrating dynamic parameters for more flexible queries
    public async Task<IEnumerable<Product>> SearchProductsAsync(string searchTerm)
    {
        using IDbConnection connection = new SqlConnection(_connectionString);
        var parameters = new DynamicParameters();
        parameters.Add("@SearchTerm", "%" + searchTerm + "%");
        string sql = "SELECT Id, Name, Price FROM Products WHERE Name LIKE @SearchTerm;";
        return await connection.QueryAsync<Product>(sql, parameters);
    }

    // Method to execute a stored procedure
    public async Task<IEnumerable<Product>> GetProductsByStoredProcedureAsync()
    {
        using IDbConnection connection = new SqlConnection(_connectionString);
        // Ensure 'GetActiveProducts' stored procedure exists in your database.
        string spName = "GetActiveProducts"; 
        return await connection.QueryAsync<Product>(spName, commandType: CommandType.StoredProcedure);
    }
}

/*
// Example of how to use the DapperRepository in an ASP.NET Core service or controller:
public class ProductService
{
    private readonly DapperRepository _repository;

    public ProductService(DapperRepository repository)
    {
        _repository = repository;
    }

    public async Task<Product> RetrieveProduct(int id)
    {
        return await _repository.GetProductByIdAsync(id);
    }

    public async Task AddNewProduct(Product newProduct)
    {
        await _repository.AddProductAsync(newProduct);
    }
}
*/

/*
 * Optimizing Data Flow with Dapper: Async Operations and Transactions
 * 
 * This code demonstrates crucial techniques for maximizing Dapper's performance
 * and ensuring data integrity: asynchronous query execution and robust transaction management.
 * These practices are vital for scalable and reliable microservices.
 */

using Dapper;
using Microsoft.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;

public class PerformanceOptimizedRepository
{
    private readonly string _connectionString;

    public PerformanceOptimizedRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    // 1. Asynchronous Data Retrieval: Essential for non-blocking I/O in web applications.
    // Using QueryAsync significantly improves application responsiveness under load.
    public async Task<IEnumerable<Product>> GetProductsAsync()
    {
        using IDbConnection connection = new SqlConnection(_connectionString);
        string sql = "SELECT Id, Name, Price FROM Products ORDER BY Name;";
        return await connection.QueryAsync<Product>(sql);
    }

    // 2. Asynchronous Data Manipulation: Apply async to INSERT/UPDATE/DELETE as well.
    public async Task<int> AddProductAsync(Product product)
    {
        using IDbConnection connection = new SqlConnection(_connectionString);
        string sql = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price); SELECT SCOPE_IDENTITY();";
        return await connection.ExecuteScalarAsync<int>(sql, product);
    }

    // 3. Transaction Management: Ensuring Atomicity for multiple operations.
    // This method demonstrates a transaction that inserts a product and updates another.
    // If any operation fails, all changes are rolled back.
    public async Task<bool> PerformTransactionalUpdateAsync(Product newProduct, Product productToUpdate)
    {
        using IDbConnection connection = new SqlConnection(_connectionString);
        connection.Open(); // Open connection explicitly for transaction scope
        using IDbTransaction transaction = connection.BeginTransaction();
        try
        {
            // Insert new product within the transaction
            string insertSql = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price);";
            await connection.ExecuteAsync(insertSql, newProduct, transaction: transaction);

            // Update existing product within the transaction
            string updateSql = "UPDATE Products SET Name = @Name, Price = @Price WHERE Id = @Id;";
            await connection.ExecuteAsync(updateSql, productToUpdate, transaction: transaction);

            transaction.Commit(); // Commit all changes if successful
            return true;
        }
        catch (Exception ex)
        {
            // Log the exception (e.g., using a logger)
            Console.WriteLine($"Transaction failed: {ex.Message}");
            transaction.Rollback(); // Rollback all changes if an error occurs
            return false;
        }
    }

    // 4. Buffered vs. Unbuffered Queries (Streaming):
    // Default Dapper Query methods are buffered (load all results into memory).
    // Using CommandFlags.Buffered = false for large datasets to stream results.
    // Note: This requires the connection to remain open until enumeration completes.
    public async IAsyncEnumerable<Product> StreamLargeProductDatasetAsync()
    {
        using IDbConnection connection = new SqlConnection(_connectionString);
        connection.Open(); // Keep connection open for streaming

        // CommandFlags.None means it's unbuffered for Query, enabling streaming.
        // Ensure to handle connection closure carefully when enumerating the results.
        foreach (var product in await connection.QueryAsync<Product>("SELECT Id, Name, Price FROM Products WHERE Price > 1000 ORDER BY Id;", buffered: false))
        { 
            yield return product;
        }
        // Connection will be closed when the 'using' block exits, after enumeration.
    }
}

/*
 * Architecting with Dapper: Strategic Integration in ASP.NET Core
 * 
 * This code outlines how to integrate Dapper into an ASP.NET Core application
 * using Dependency Injection (DI) and the Repository Pattern. This setup promotes
 * maintainability, testability, and scalability by decoupling database access logic.
 */

using Dapper;
using Microsoft.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;

// 1. Define an Interface for your Repository (Contract-First Design)
public interface IProductRepository
{
    Task<Product> GetByIdAsync(int id);
    Task<IEnumerable<Product>> GetAllAsync();
    Task<int> AddAsync(Product product);
    Task<bool> UpdateAsync(Product product);
    Task<bool> DeleteAsync(int id);
}

// 2. Implement the Repository using Dapper
public class ProductRepository : IProductRepository
{
    private readonly string _connectionString;

    // Inject the connection string (or IConfiguration) via constructor
    public ProductRepository(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("DefaultConnection");
    }

    // Helper method to create a new IDbConnection instance
    private IDbConnection CreateConnection() => new SqlConnection(_connectionString);

    public async Task<Product> GetByIdAsync(int id)
    {
        using IDbConnection connection = CreateConnection();
        return await connection.QueryFirstOrDefaultAsync<Product>(
            "SELECT Id, Name, Price FROM Products WHERE Id = @Id", new { Id = id });
    }

    public async Task<IEnumerable<Product>> GetAllAsync()
    {
        using IDbConnection connection = CreateConnection();
        return await connection.QueryAsync<Product>("SELECT Id, Name, Price FROM Products");
    }

    public async Task<int> AddAsync(Product product)
    {
        using IDbConnection connection = CreateConnection();
        string sql = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price); SELECT SCOPE_IDENTITY();";
        return await connection.ExecuteScalarAsync<int>(sql, product);
    }

    public async Task<bool> UpdateAsync(Product product)
    {
        using IDbConnection connection = CreateConnection();
        string sql = "UPDATE Products SET Name = @Name, Price = @Price WHERE Id = @Id;";
        int rowsAffected = await connection.ExecuteAsync(sql, product);
        return rowsAffected > 0;
    }

    public async Task<bool> DeleteAsync(int id)
    {
        using IDbConnection connection = CreateConnection();
        string sql = "DELETE FROM Products WHERE Id = @Id;";
        int rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
        return rowsAffected > 0;
    }
}

// 3. Register your Repository and Services in Program.cs (for .NET 6+) or Startup.cs
// This configures Dependency Injection to provide instances of IProductRepository.
public static class ServiceCollectionExtensions
{
    public static IServiceCollection AddDapperDataAccess(this IServiceCollection services, IConfiguration configuration)
    {
        // Register IProductRepository to be resolved by DI.
        // We use Scoped lifecycle for typical web request scenarios.
        services.AddScoped<IProductRepository, ProductRepository>();

        // If you need to inject IConfiguration directly into the repository, it's already available
        // via default DI setup in ASP.NET Core.
        return services;
    }
}

// 4. Example Usage in an ASP.NET Core Controller or Service
/*
// In your Program.cs or Startup.cs:
// builder.Services.AddDapperDataAccess(builder.Configuration);

// In a controller:
using Microsoft.AspNetCore.Mvc;

[ApiController]
[Route("[controller]")]
public class ProductsController : ControllerBase
{
    private readonly IProductRepository _productRepository;

    public ProductsController(IProductRepository productRepository)
    {
        _productRepository = productRepository;
    }

    [HttpGet("{id}")]
    public async Task<ActionResult<Product>> Get(int id)
    {
        var product = await _productRepository.GetByIdAsync(id);
        if (product == null) return NotFound();
        return Ok(product);
    }

    [HttpPost]
    public async Task<ActionResult<Product>> Post([FromBody] Product newProduct)
    {
        int newId = await _productRepository.AddAsync(newProduct);
        newProduct.Id = newId; // Assign the new ID
        return CreatedAtAction(nameof(Get), new { id = newId }, newProduct);
    }
}
*/