source: WineTrackerFinal/WineTrackerWebApi/Controllers/CustomerTypeController.cs

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

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

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

        // GET: api/CustomerType
        [HttpGet]
        public async Task<ActionResult<IEnumerable<CustomerType>>> GetCustomerType()
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                const string query = $@"
                    select customer_type_id as CustomerTypeId, customer_type_name as CustomerTypeName, customer_type_description as CustomerTypeDescription
                    from customer_type ct";
                var customerTypes = await db.QueryAsync<CustomerType>(query);
                return Ok(customerTypes);
            }
        }

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

                string query = $@"
                    select customer_type_id as CustomerTypeId, customer_type_name as CustomerTypeName, customer_type_description as CustomerTypeDescription
                    from customer_type ct 
                    where customer_type_id = @id";

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

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

                        await ModelValidation.DoesEntityExistByName((NpgsqlConnection)db, (NpgsqlTransaction)transaction, "Customer_Type", parameters.CustomerTypeName, entityId: parameters.CustomerTypeId);

                        string query = $@"
                        update customer_type 
                        set 
                            customer_type_name = @CustomerTypeName, 
                            customer_type_description = @CustomerTypeDescription 
                        where customer_type_id = @CustomerTypeId";

                        var updatedColumns = 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/CustomerType
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost]
        public async Task<IActionResult> PostCustomerType([FromBody] CustomerType customerType)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new { CustomerTypeName = customerType.CustomerTypeName.Trim(), CustomerTypeDescription = customerType.CustomerTypeDescription.Trim() };

                        await ModelValidation.DoesEntityExistByName((NpgsqlConnection)db, (NpgsqlTransaction)transaction, "Customer_Type", parameters.CustomerTypeName);

                        string query = "INSERT INTO customer_type (customer_type_name, customer_type_description) VALUES (@CustomerTypeName, @CustomerTypeDescription);";
                        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/CustomerType/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteCustomerType(int id)
        {
            try
            {
                //Check if there is a customer linked to the customer type we are trying to delete, if so return a message and dont let the user delete the customer 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 customerTypeLinkToCustomerQuery = $@"
                            select count(*)
                            from customer_type ct 
                            join customer c  on ct.customer_type_id = c.customer_type_id 
                            where c.customer_type_id = @id";

                        var isCustomerTypeLinkedToCustomer = await db.QueryFirstAsync<int>(customerTypeLinkToCustomerQuery, parameters, transaction: transaction);

                        if (isCustomerTypeLinkedToCustomer > 0)
                            return BadRequest(new ErrorHandler { Name = "Customer Type Cannot Be Deleted", Description = "Customer Type cannot be deleted because it is linked to a customer" });

                        string query = $@"delete from customer_type where customer_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 });
            }           
        }
    }
}