source: WineTrackerFinal/WineTrackerWebApi/Controllers/WineTypeController.cs

using System.Data;
using Dapper;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Npgsql;
using WineTrackerWebApi.Helpers;
using WineTrackerWebApi.Models.ErrorHandle;
using WineTrackerWebApi.Models.WineType;

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

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

        // GET: api/WineType
        [HttpGet]
        public async Task<ActionResult<IEnumerable<WineType>>> GetWineType()
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                const string query = $@"
                    select wine_type_id as WineTypeId, wine_type_name as WineTypeName, wine_type_description as WineTypeDescription, wine_type_region as WineTypeRegion
                    from wine_type wt";
                var wineTypes = await db.QueryAsync<WineType>(query);
                return Ok(wineTypes);
            }
        }

        // GET: api/WineType/5
        [HttpGet("{id}")]
        public async Task<ActionResult<WineType>> GetWineType(int id)
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                var parameters = new { id = id };

                string query = $@"
                    select wine_type_id as WineTypeId, wine_type_name as WineTypeName, wine_type_description as WineTypeDescription, wine_type_region as WineTypeRegion
                    from wine_type wt 
                    where wine_type_id = @id";

                var wineType = await db.QueryFirstAsync<WineType>(query, parameters);
                return Ok(wineType);
            }
        }

        // PUT: api/WineType/5
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPut]
        public async Task<IActionResult> PutWineType([FromBody] WineType wineType)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new { 
                            WineTypeName = wineType.WineTypeName.Trim(), 
                            WineTypeDescription = wineType.WineTypeDescription.Trim(), 
                            WineTypeId = wineType.WineTypeId,
                            WineTypeRegion = wineType.WineTypeRegion.Trim() 
                        };

                        await ModelValidation.DoesEntityExistByName((NpgsqlConnection)db, (NpgsqlTransaction)transaction, "Wine_Type", parameters.WineTypeName, entityId: parameters.WineTypeId);

                        string query = $@"
                        update wine_type 
                        set 
                            wine_type_name = @WineTypeName, 
                            wine_type_description = @WineTypeDescription,
                            wine_type_region = @WineTypeRegion
                        where wine_type_id = @WineTypeId";

                        await db.ExecuteAsync(query, parameters, transaction: transaction);

                        transaction.Commit();
                        return Ok();
                    }
                }
            }
            catch (Exception ex)
            {
                return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
            }
        }

        // POST: api/WineType
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost]
        public async Task<IActionResult> PostWineType([FromBody] WineType wineType)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new { 
                            WineTypeName = wineType.WineTypeName.Trim(), 
                            WineTypeDescription = wineType.WineTypeDescription.Trim(), 
                            WineTypeRegion = wineType.WineTypeRegion.Trim() 
                        };

                        await ModelValidation.DoesEntityExistByName((NpgsqlConnection)db, (NpgsqlTransaction)transaction, "Wine_Type", parameters.WineTypeName);

                        string query = "INSERT INTO wine_type (wine_type_name, wine_type_description, wine_type_region) VALUES (@WineTypeName, @WineTypeDescription, @WineTypeRegion);";
                        await db.ExecuteAsync(query, parameters, transaction: transaction);

                        transaction.Commit();
                        return Ok();
                    }
                }
            }
            catch (Exception ex)
            {
                return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
            }
        }

        // DELETE: api/WineType/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteWineType(int id)
        {
            try
            {
                //Check if there is a wine linked to the wine type we are trying to delete, if so return a message and dont let the user delete the wine type.
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new { id = id };
                        
                        string wineTypeLinkToWineQuery = $@"
                            select count(*)
                            from wine_type wt 
                            join wine w  on wt.wine_type_id = w.wine_type_id
                            where w.wine_type_id = @id";

                        var isWineTypeToWine = await db.QueryFirstAsync<int>(wineTypeLinkToWineQuery, parameters, transaction: transaction);
                        if (isWineTypeToWine > 0)
                            return BadRequest(new ErrorHandler { Name = "Wine Type Cannot Be Deleted", Description = "Wine Type cannot be deleted because it is linked to a customer" });

                        string query = $@"delete from wine_type where wine_type_id = @id";
                        await db.ExecuteAsync(query, parameters, transaction: transaction);

                        transaction.Commit();
                        return Ok();
                    }
                }
            }
            catch (Exception ex)
            {
                return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
            }
        }
    }
}