source: WineTrackerFinal/WineTrackerWebApi/Controllers/ExpenseTypeController.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.ExpenseType;

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

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

        // GET: api/ExpenseType
        [HttpGet]
        public async Task<ActionResult<IEnumerable<ExpenseType>>> GetExpenseType()
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                const string query = $@"
                    select expense_type_id as ExpenseTypeId, expense_type_name as ExpenseTypeName, expense_type_description as ExpenseTypeDescription
                    from expense_type et";
                var expenseTypes = await db.QueryAsync<ExpenseType>(query);
                return Ok(expenseTypes);
            }
        }

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

                string query = $@"
                    select expense_type_id as ExpenseTypeId, expense_type_name as ExpenseTypeName, expense_type_description as ExpenseTypeDescription 
                    from expense_type et 
                    where expense_type_id = @id";

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

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

                        await ModelValidation.DoesEntityExistByName((NpgsqlConnection)db, (NpgsqlTransaction)transaction, "Expense_Type", parameters.ExpenseTypeName, entityId: parameters.ExpenseTypeId);

                        string query = $@"
                        update expense_type 
                        set 
                            expense_type_name = @ExpenseTypeName, 
                            expense_type_description = @ExpenseTypeDescription 
                        where expense_type_id = @ExpenseTypeId";

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

                        await ModelValidation.DoesEntityExistByName((NpgsqlConnection)db, (NpgsqlTransaction)transaction, "Expense_Type", parameters.ExpenseTypeName);

                        string query = "INSERT INTO expense_type (expense_type_name, expense_type_description) VALUES (@ExpenseTypeName, @ExpenseTypeDescription);";
                        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/ExpenseType/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteExpenseType(int id)
        {
            try
            {
                //Check if there is a shipment which has an expense of this type, if so return a message and dont let the user delete the expense 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 expenseTypeLinkToShipmentQuery = $@"
                            select count(*)
                            from expense_type et 
                            join shipment_has_expense_of_expense_type sheoet on et.expense_type_id = sheoet.expense_type_id
                            where et.expense_type_id = @id";

                        var isExpenseTypeLinkedToShipment = await db.QueryFirstAsync<int>(expenseTypeLinkToShipmentQuery, parameters, transaction: transaction);
                        if (isExpenseTypeLinkedToShipment > 0)
                            return BadRequest(new ErrorHandler { Name = "Expense Type Cannot Be Deleted", Description = "Expense Type cannot be deleted because a Shipment has an expense of that type." });

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