source: WineTrackerFinal/WineTrackerWebApi/Controllers/WarehouseController.cs

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

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

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

        // GET: api/Warehouse
        [HttpGet]
        public async Task<ActionResult<IEnumerable<WarehouseDetails>>> GetWarehouse()
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                const string query = $@"
                    select 
                        w.warehouse_Id as WarehouseId,
                        a.address_Id as AddressId,
                        w.warehouse_name as WarehouseName, 
                        concat(a.street,' ', a.building_number, ', ', a.city, ' ', a.postcode) as WarehouseAddress
                    from warehouse w 
                    join address a on w.address_id = a.address_id";
                var warehouses = await db.QueryAsync<WarehouseDetails>(query);
                return Ok(warehouses);
            }
        }

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

                string query = $@"
                    select 
                        w.warehouse_Id as WarehouseId, 
                        w.warehouse_name as WarehouseName, 
                        a.address_Id as AddressId,
                        a.street as Street, 
                        a.building_number as BuildingNumber, 
                        a.city as City, 
                        a.postcode as PostCode
                    from warehouse w 
                    join address a on w.address_id = a.address_id
                    where w.warehouse_id = @id";

                var result = await db.QueryFirstAsync<dynamic>(query, parameters);

                var warehouse = new Warehouse
                {
                    WarehouseId = result.warehouseid,
                    WarehouseName = result.warehousename,
                    Address = new Address
                    {
                        AddressId = result.addressid,
                        Street = result.street,
                        BuildingNumber = result.buildingnumber,
                        City = result.city,
                        PostCode = result.postcode
                    }
                };

                return Ok(warehouse);
            }
        }

        // PUT: api/Warehouse/5
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPut]
        public async Task<IActionResult> PutWarehouse([FromBody] Warehouse warehouse)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new
                        {
                            WarehouseName = warehouse.WarehouseName.Trim(),
                            Street = warehouse.Address.Street.Trim(),
                            BuildingNumber = warehouse.Address.BuildingNumber,
                            City = warehouse.Address.City.Trim(),
                            Postcode = warehouse.Address.PostCode.Trim(),
                            WarehouseId = warehouse.WarehouseId,
                            AddressId = warehouse.Address.AddressId,
                        };

                        await ModelValidation.DoesEntityExistByName((NpgsqlConnection)db, (NpgsqlTransaction)transaction, "Warehouse", parameters.WarehouseName, entityId: parameters.WarehouseId);

                        string updateWarehouse = $@"update warehouse set warehouse_Name = @WarehouseName where warehouse_Id = @WarehouseId";
                        await db.ExecuteAsync(updateWarehouse, parameters, transaction: transaction);

                        string updateAddress = $@"
                            update Address 
                            set 
                                Street = @Street,
                                Building_Number = @BuildingNumber,
                                City = @City,
                                PostCode = @Postcode
                            where Address_Id = @AddressId";

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

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

        // POST: api/Warehouse
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost]
        public async Task<IActionResult> PostWarehouse([FromBody] Warehouse warehouse)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new { 
                            WarehouseName = warehouse.WarehouseName.Trim(), 
                            Street = warehouse.Address.Street.Trim(), 
                            BuildingNumber = warehouse.Address.BuildingNumber,
                            City = warehouse.Address.City.Trim(), 
                            Postcode = warehouse.Address.PostCode.Trim() 
                        };

                        await ModelValidation.DoesEntityExistByName((NpgsqlConnection)db, (NpgsqlTransaction)transaction, "Warehouse", parameters.WarehouseName);

                        string addAddress = @"INSERT INTO public.Address (Street, Building_Number, City, PostCode) VALUES (@Street, @BuildingNumber, @City, @Postcode) RETURNING Address_Id;";
                        var addressId = await db.ExecuteScalarAsync<int>(addAddress, parameters, transaction: transaction);

                        string addWarehouse = $@"INSERT INTO public.Warehouse (Warehouse_Name, Address_Id) VALUES (@WarehouseName, {addressId});";
                        await db.ExecuteAsync(addWarehouse, parameters, transaction: transaction);

                        transaction.Commit();

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

        // DELETE: api/Warehouse/5
        [HttpDelete("{warehouseId}/{addressId}")]
        public async Task<IActionResult> DeleteWarehouse(int warehouseId, int addressId)
        {
            try
            {
                //Check if there is an employee linked to the warehouse we are trying to delete, if so return a message and dont let the user delete the warehouse.
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new { warehouseId = warehouseId, addressId = addressId };

                        string warehouseLinkToEmployeeQuery = $@"
                            select count(*)
                            from warehouse w  
                            join employee e on w.warehouse_id = e.warehouse_id 
                            where e.warehouse_id  = @warehouseId";

                        var isWarehouseLinkedToEmployee = await db.QueryFirstAsync<int>(warehouseLinkToEmployeeQuery, parameters, transaction: transaction);
                        if (isWarehouseLinkedToEmployee > 0)
                            return BadRequest(new ErrorHandler { Name = "Warehouse Cannot Be Deleted", Description = "Warehouse cannot be deleted because it is linked to an employee" });

                        string deleteWarehouse = $@"delete from warehouse where warehouse_id = @warehouseId";
                        string deleteAddress = $@"delete from address where address_Id = @addressId";

                        await db.ExecuteAsync(deleteWarehouse, parameters, transaction: transaction);
                        await db.ExecuteAsync(deleteAddress, parameters, transaction: transaction);

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