source: WineTrackerFinal/WineTrackerWebApi/Controllers/VehicleDetailsController.cs

using Dapper;
using Microsoft.AspNetCore.Mvc;
using Npgsql;
using System.Data;
using WineTrackerWebApi.Helpers;
using WineTrackerWebApi.Models.Employee;
using WineTrackerWebApi.Models.ErrorHandle;
using WineTrackerWebApi.Models.VehicleDetails;

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

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

        // GET: api/VehicleDetails
        [HttpGet]
        public async Task<ActionResult<IEnumerable<VehicleDetails>>> GetVehicleDetails()
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                const string query = $@"
                    select 
                            vd.vehicle_details_id as VehicleDetailsId, 
                            vt.vehicle_type_id as VehicleTypeId, 
                            vt.vehicle_type_name as VehicleTypeName, 
                            vd.make as Make, 
                            vd.model as Model, 
                            vd.capacity as Capacity
                    from vehicle_details vd 
                    join vehicle_type vt on vd.vehicle_type_id = vt.vehicle_type_id 
                ";
                var vehicleDetails = await db.QueryAsync<VehicleDetails>(query);
                return Ok(vehicleDetails);
            }
        }

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

                const string query = $@"
                    select 
                            vd.vehicle_details_id as VehicleDetailsId, 
                            vt.vehicle_type_id as VehicleTypeId,
                        vt.vehicle_type_name as VehicleTypeName, 
                            vd.make as Make, 
                            vd.model as Model, 
                            vd.capacity as Capacity
                    from vehicle_details vd 
                    join vehicle_type vt on vd.vehicle_type_id = vt.vehicle_type_id
                    where vd.vehicle_details_id = @id   
                ";
                var vehicleDetails = await db.QueryFirstAsync<VehicleDetails>(query, parameters);
                return Ok(vehicleDetails);
            }
        }

        // PUT: api/VehicleDetails/5
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPut]
        public async Task<IActionResult> PutVehicleDetails([FromBody] VehicleDetails vehicleDetails)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new
                        {
                            Make = vehicleDetails.Make.Trim(),
                            Model = vehicleDetails.Model.Trim(),
                            Capacity = vehicleDetails.Capacity,
                            VehicleTypeId = vehicleDetails.VehicleTypeId,
                            VehicleDetailsId = vehicleDetails.VehicleDetailsId
                        };

                        await ModelValidation.DoVehicleDetailsExist((NpgsqlConnection)db, (NpgsqlTransaction)transaction, parameters.Make, parameters.Model, parameters.Capacity, parameters.VehicleDetailsId);

                        string query = $@"
                        update vehicle_details
                        set 
                            make = @Make,
                            model = @Model,
                            capacity = @Capacity,
                            vehicle_type_id = @VehicleTypeId
                        where vehicle_details_id = @VehicleDetailsId
                        ";
                        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/VehicleDetails
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost]
        public async Task<IActionResult> PostVehicleDetails([FromBody] VehicleDetails vehicleDetails)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new
                        {
                            Make = vehicleDetails.Make.Trim(),
                            Model = vehicleDetails.Model.Trim(),
                            Capacity = vehicleDetails.Capacity,
                            VehicleTypeId = vehicleDetails.VehicleTypeId,
                            VehicleDetailsId = vehicleDetails.VehicleDetailsId
                        };

                        await ModelValidation.DoVehicleDetailsExist((NpgsqlConnection)db, (NpgsqlTransaction)transaction, parameters.Make, parameters.Model, parameters.Capacity);

                        string query = $@"insert into vehicle_details (make, model, capacity, vehicle_type_id) values (@Make, @Model, @Capacity, @VehicleTypeId)";
                        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/VehicleDetails/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteVehicleDetails(int id)
        {
            try
            {
                //Check if there is a vehicle linked to the vehicle details we are trying to delete, if so return a message and dont let the user delete the vehicle details.
                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 vehicleDetailsLinkToVehicleQuery = $@"
                            select count(*)
                            from vehicle_details vd 
                            join vehicle v on vd.vehicle_details_id = v.vehicle_details_id
                            where vd.vehicle_details_id = @id";

                        var isVehicleDetailsLinkedToVehicle = await db.QueryFirstAsync<int>(vehicleDetailsLinkToVehicleQuery, parameters, transaction: transaction);
                        if (isVehicleDetailsLinkedToVehicle > 0)
                            return BadRequest(new ErrorHandler { Name = "Vehicle Details Cannot Be Deleted", Description = "Vehicle Details cannot be deleted because they are linked to a vehicle" });

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