source: WineTrackerFinal/WineTrackerWebApi/Controllers/VehicleController.cs@ 17d6948

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

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

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

        // GET: api/Vehicle
        [HttpGet]
        public async Task<ActionResult<IEnumerable<VehicleDetails>>> GetVehicle()
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                const string query = $@"
                    select 
                        v.vehicle_id as VehicleId,
                        v.registration as Registration, 
                        CONCAT('Type: ', vt.vehicle_type_name ,' | Make: ', vd.make, ' | Model: ', vd.model, ' | Capacity: ', vd.capacity) as Details
                    from vehicle v 
                    join vehicle_details vd on v.vehicle_details_id = vd.vehicle_details_id
                    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/Vehicle/5
        [HttpGet("{id}")]
        public async Task<ActionResult<Vehicle>> GetVehicle(int id)
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                var parameters = new { id = id };

                const string query = $@"
                    select 
                        v.vehicle_id as VehicleId,
                        vd.vehicle_details_id as VehicleDetailsId,
                        v.registration as Registration
                    from vehicle v 
                    join vehicle_details vd on v.vehicle_details_id = vd.vehicle_details_id
                    where v.vehicle_id = @id   
                ";
                var vehicle = await db.QueryFirstAsync<Vehicle>(query, parameters);
                return Ok(vehicle);
            }
        }

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

                        await ModelValidation.DoesEntityExistByName((NpgsqlConnection)db, (NpgsqlTransaction)transaction, "Vehicle", parameters.Registration, "registration", entityId: parameters.VehicleId);

                        string query = $@"
                        update vehicle
                        set 
                            vehicle_details_id = @VehicleDetailsId,
                            registration = @Registration
                        where vehicle_id = @VehicleId
                        ";
                        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/Vehicle
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost]
        public async Task<IActionResult> PostVehicle([FromBody] Vehicle vehicle)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new { Registration = vehicle.Registration.Trim(), VehicleDetailsId = vehicle.VehicleDetailsId };

                        string query = $@"insert into vehicle (vehicle_details_id, registration) values (@VehicleDetailsId, @Registration)";
                        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/Vehicle/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteVehicle(int id)
        {
            try
            {
                //Check if there is a shipment linked to the vehicle we are trying to delete, if so return a message and dont let the user delete the vehicle.
                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 vehicleLinkToShipmentQuery = $@"
                            select Count(*)
                            from shipment sl
                            where vehicle_id = @id";

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

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