source: WineTrackerFinal/WineTrackerWebApi/Controllers/ReportController.cs

using Dapper;
using Microsoft.AspNetCore.Mvc;
using Npgsql;
using System.Data;
using System.Text;
using WineTrackerWebApi.Models.ErrorHandle;
using WineTrackerWebApi.Models.Report;
using WineTrackerWebApi.Models.WineType;

namespace WineTrackerWebApi.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ReportsController : ControllerBase
    {
        private readonly string _connectionString;

        public ReportsController(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("DefaultConnection");
        }

        // GET: api/Reports/GetWineReport
        [HttpGet("getShippedWinesReport/{month}/{year}")]
        public async Task<IActionResult> GetShippedWineReport(int month, int year)
        {
            try 
            { 
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    var parameters = new DynamicParameters();
                    parameters.Add("@Month", month, DbType.Int32);
                    parameters.Add("@Year", year, DbType.Int32);

                    const string query = "SELECT * FROM GetShippedWinesReport(@Month, @Year)";
                    var shippedWineReport = await db.QueryAsync<WineReport>(query, parameters);

                    // Convert the list of WineReport to a CSV format
                    var csvContent = GenerateCsv(shippedWineReport);

                    // Return the CSV file as a download
                    var fileName = $"ShippedWineReport_{month}_{year}.csv";
                    var byteArray = Encoding.UTF8.GetBytes(csvContent);
                    return File(byteArray, "text/csv", fileName);
                }
            }
            catch (Exception ex)
            {
                return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
            }
        }

        [HttpGet("getCustomerShipmentsReport/{month}/{year}")]
        public async Task<IActionResult> GetCustomerShipmentsReport(int month, int year)
        {
            try 
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    var parameters = new DynamicParameters();
                    parameters.Add("@Month", month, DbType.Int32);
                    parameters.Add("@Year", year, DbType.Int32);

                    const string query = "SELECT * FROM GetCustomerShipmentsReport(@Month, @Year)";
                    var customerShipments = await db.QueryAsync<CustomerReport>(query, parameters);

                    // Convert the list of WineReport to a CSV format
                    var csvContent = GenerateCsv(customerShipments);

                    // Return the CSV file as a download
                    var fileName = $"CustomerShipmentsReport_{month}_{year}.csv";
                    var byteArray = Encoding.UTF8.GetBytes(csvContent);
                    return File(byteArray, "text/csv", fileName);
                }
            }
            catch (Exception ex)
            {
                return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
            }
        }     

        private string GenerateCsv<T>(IEnumerable<T> reports)
        {
            var csvBuilder = new StringBuilder();
            var headers = typeof(T).GetProperties().Select(p => p.Name).ToArray();

            // Add header row
            csvBuilder.AppendLine(string.Join(",", headers));

            // Add data rows
            foreach (var report in reports)
            {
                var row = headers.Select(header =>
                {
                    var propertyValue = report.GetType().GetProperty(header).GetValue(report, null);
                    return propertyValue?.ToString().Replace(",", ""); // Remove commas in case of text fields
                }).ToArray();

                csvBuilder.AppendLine(string.Join(",", row));
            }

            return csvBuilder.ToString();
        }
    }
}