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

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

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

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

        // GET: api/Customer
        [HttpGet]
        public async Task<ActionResult<IEnumerable<CustomerDetails>>> GetCustomer()
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                const string query = $@"
                    select 
                        c.customer_Id as CustomerId,
                        c.customer_name as CustomerName,
                        ct.customer_type_name as CustomerTypeName,
                        c.customer_email as CustomerEmail,
                        c.customer_phone_number as CustomerPhoneNumber,
                        a.address_Id as AddressId,
                        concat(a.street,' ', a.building_number, ', ', a.city, ' ', a.postcode) as CustomerAddress
                    from customer c
                    join address a on c.address_id = a.address_id
                    join customer_type ct on c.customer_type_id = ct.customer_type_id";
                var customers = await db.QueryAsync<CustomerDetails>(query);
                return Ok(customers);
            }
        }

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

                string query = $@"
                    select 
                        c.customer_Id as CustomerId,
                        c.customer_name as CustomerName,
                        c.customer_type_id as CustomerTypeId,
                        c.customer_email as CustomerEmail,
                        c.customer_phone_number as CustomerPhoneNumber,
                        a.address_Id as AddressId,
                        a.street as Street, 
                        a.building_number as BuildingNumber, 
                        a.city as City, 
                        a.postcode as PostCode
                    from customer c
                    join address a on c.address_id = a.address_id
                    where c.customer_id = @id";

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

                var customer = new Customer
                {
                    CustomerId = result.customerid,
                    CustomerName = result.customername,
                    CustomerTypeId = result.customertypeid,
                    CustomerEmail = result.customeremail,
                    CustomerPhoneNumber = result.customerphonenumber,
                    Address = new Address
                    {
                        AddressId = result.addressid,
                        Street = result.street,
                        BuildingNumber = result.buildingnumber,
                        City = result.city,
                        PostCode = result.postcode
                    }
                };

                return Ok(customer);
            }
        }

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

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

                        string updateCustomer = $@"
                            update customer 
                            set 
                                customer_name = @CustomerName,
                                customer_type_id = @CustomerTypeId,
                                customer_email = @CustomerEmail,
                                customer_phone_number = @CustomerPhoneNumber
                                where customer_Id = @CustomerId";

                        var updatedCustomerRows = await db.ExecuteAsync(updateCustomer, parameters, transaction: transaction);

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

                        var updatedAddressRows = 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/Customer
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost]
        public async Task<IActionResult> PostCustomer([FromBody] Customer customer)
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                try
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new
                        {
                            CustomerName = customer.CustomerName.Trim(),
                            CustomerTypeId = customer.CustomerTypeId,
                            CustomerEmail = customer.CustomerEmail.Trim(),
                            CustomerPhoneNumber = customer.CustomerPhoneNumber.Trim(),
                            Street = customer.Address.Street.Trim(),
                            BuildingNumber = customer.Address.BuildingNumber,
                            City = customer.Address.City.Trim(),
                            Postcode = customer.Address.PostCode.Trim(),
                        };

                        await ModelValidation.DoesEntityExistByName((NpgsqlConnection)db, (NpgsqlTransaction)transaction, "Customer", parameters.CustomerName);

                        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 addCustomer = $@"
                            INSERT INTO public.Customer (Customer_Name, Customer_Email, Customer_Phone_Number, Customer_Type_Id, Address_Id) 
                            VALUES (@CustomerName, @CustomerEmail, @CustomerPhoneNumber, @CustomerTypeId, {addressId});";
                        
                        var addressAddedRows = await db.ExecuteAsync(addCustomer, parameters, transaction: transaction);

                        transaction.Commit();

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

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

                        string customerLinkToShipmentLoadQuery = $@"
                            select count(*)
                            from shipment_load sl 
                            where customer_id = @customerId";

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

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

                        string deleteCustomer = $@"delete from customer where customer_id = @customerId";
                        string deleteAddress = $@"delete from address where address_Id = @addressId";

                        int customerDeletedRows = await db.ExecuteAsync(deleteCustomer, parameters, transaction: transaction);
                        int addressDeletedRows = await db.ExecuteAsync(deleteAddress, parameters, transaction: transaction);

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