source: WineTrackerFinal/WineTrackerWebApi/Controllers/ShipmentController.cs

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

namespace WineTrackerWebApi.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ShipmentController : ControllerBase
    {
        private readonly string _connectionString;
        public ShipmentController(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("DefaultConnection");
        }

        // GET: api/Shipment
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Shipment>>> GetShipment(string shipmentDate ="", string employeeNameSurname = "", string registration="")
        {
            var parameters = new { 
                ShipmentDate = !string.IsNullOrEmpty(shipmentDate) ? DateTime.Parse(shipmentDate) : DateTime.MinValue,
                employeeNameSurname = employeeNameSurname.Trim().ToLower(), 
                Registration = registration.Trim().ToLower() };

            List<string> whereConditons = new List<string>();
            if(!string.IsNullOrEmpty(shipmentDate)) whereConditons.Add("s.shipment_date = @ShipmentDate");
            if(!string.IsNullOrEmpty(employeeNameSurname)) whereConditons.Add("LOWER(CONCAT(e.employee_name, ' ', e.employee_surname)) Like '%' || @employeeNameSurname || '%'");
            if(!string.IsNullOrEmpty(registration)) whereConditons.Add("LOWER(v.registration) = @Registration");

            string whereClause = whereConditons.Count > 0 ? "where " + string.Join(" and ", whereConditons) : "";

            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                string query = $@"
                select
                    s.shipment_id as ShipmentId,
                    s.payment_id as PaymentId,
                    p.payment_status as PaymentStatus,
                    TO_CHAR(s.shipment_date, 'DD-MM-YYYY') as ShipmentDate,
                    (
                        SELECT STRING_AGG(DISTINCT c.customer_name, ', ')
                        FROM shipment_load sl
                        JOIN customer c ON sl.customer_id = c.customer_id
                        WHERE sl.shipment_id = s.shipment_id
                    ) as Customers,
                    CONCAT(e.employee_name, ' ', e.employee_surname) AS EmployeeFullName,
                    CONCAT(vd.make, ' ', vd.model, ' (', v.registration, ')') AS VehicleInfo
                FROM shipment s
                JOIN employee e ON s.employee_id = e.employee_id
                JOIN vehicle v ON s.vehicle_id = v.vehicle_id
                join vehicle_details vd on vd.vehicle_details_id = v.vehicle_details_id 
                join payment p on s.payment_id = p.payment_id
                {whereClause}
                ORDER BY s.shipment_date desc";
                var shipments = await db.QueryAsync<Shipment>(query, parameters);
                return Ok(shipments);
            }
        }

        [HttpGet("getCreateShipmentInfo")]
        public async Task<ActionResult<IEnumerable<Shipment>>> GetCreateShipmentInfo()
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                const string getEmployees = $@"
                select
                    e.employee_id as Id,
                        CONCAT(e.employee_name, ' ', e.employee_surname) AS Name
                from employee e";
                var employeeList = await db.QueryAsync<EmployeeListElement>(getEmployees);

                const string getCustomers = $@"
                select
                    c.customer_id as Id,
                        CONCAT(c.customer_name , ' (', a.city , ' ', a.street ,' ',a.building_number, ' ', a.postcode, ')') AS Name,
                    CONCAT(a.city ,' ', a.street ,' ',a.building_number,' ', a.postcode) AS Address
                from customer c
                join address a on c.address_id = a.address_id";
                var customerList = await db.QueryAsync<CustomerListElement>(getCustomers);

                string getWines = $@"
                select
                    w.wine_id as Id,
                    CONCAT(w.wine_name , ' (', wt.wine_type_name,', ', wt.wine_type_region,')') AS Name,
                    w.base_price as BasePrice
                from wine w
                join wine_type wt on w.wine_type_id = wt.wine_type_id";
                var wineList = await db.QueryAsync<WineListElement>(getWines);

                string getVehicles = $@"
                select
                    v.vehicle_id as Id,
                    CONCAT(vd.make, ' ', vd.model, ' (', v.registration, ')') AS Name,
                    edv.employee_id as EmployeeId,
                    vd.capacity as Capacity
                from employee_drives_vehicle edv
                join vehicle v on edv.vehicle_id = v.vehicle_id
                join vehicle_details vd on v.vehicle_details_id = vd.vehicle_details_id";
                var vehicleList = await db.QueryAsync<VehicleListElement>(getVehicles);

                return Ok(new{ employeeList, customerList, wineList, vehicleList });
            }
        }

        [HttpPost]
        public async Task<IActionResult> CreateShipment([FromBody]AddShipment shipment)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {

                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new
                        {
                            VehicleId = int.Parse(shipment.VehicleId),
                            ShipmentDate= DateTime.Parse(shipment.ShipmentDate),
                            EmployeeId = int.Parse(shipment.EmployeeId)
                        };

                        await ModelValidation.IsVehicleAlreadyBooked((NpgsqlConnection)db, (NpgsqlTransaction)transaction, parameters.VehicleId, parameters.ShipmentDate);

                        string addPayment = @"INSERT INTO public.Payment (Payment_Status) VALUES (0) RETURNING Payment_Id;";
                        var paymentId = await db.ExecuteScalarAsync<int>(addPayment, transaction: transaction);

                        string addShipment = $@"INSERT INTO public.Shipment (Employee_Id, Payment_Id, Vehicle_Id, Shipment_Date) VALUES (@EmployeeId, {paymentId}, @VehicleId, @ShipmentDate) RETURNING Shipment_Id;";
                        var shipmentId = await db.ExecuteScalarAsync<int>(addShipment, parameters, transaction: transaction);

                        //make a regular nested list for each loop to insert the shipment_load rows
                        var updateRows = shipment.Customers.SelectMany(s => s.Wines.Select(x => $"({shipmentId}, {int.Parse(s.CustomerId)}, {x.WineId}, {Math.Round((x.AgreedPrice / x.Quantity), 2)}, {x.BasePrice}, {x.Quantity})")).ToList();
                        string updateRowsString = string.Join(",", updateRows);

                        string addShipmentLoad = $@"INSERT INTO public.shipment_load (Shipment_Id, Customer_Id, Wine_Id, Wine_Agreed_Price, Wine_Base_Price, Wine_Quantity) VALUES {updateRowsString};";
                        var shipmentLoadId = await db.ExecuteAsync(addShipmentLoad, transaction: transaction);

                        transaction.Commit();

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

        [HttpGet("getBaseShipmentDetails/{shipmentId}")]
        public async Task<ActionResult<ShipmentBaseDetails>> GetBaseShipmentDetails(string shipmentId)
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                string query = $@"
                SELECT 
                    s.shipment_id AS ShipmentId,
                    p.payment_status AS PaymentStatus,
                    CONCAT(e.employee_name, ' ', e.employee_surname) AS EmployeeFullName,
                    CONCAT(vd.make, ' ', vd.model, ' (', v.registration, ')') AS VehicleInfo,
                    TO_CHAR(s.shipment_date, 'DD-MM-YYYY') AS ShipmentDate,
                    COUNT(DISTINCT sl.customer_id) AS NumberOfCustomers,
                    SUM(sl.wine_quantity) AS WineQuantityShipped,
                    SUM(sl.wine_base_price * sl.wine_quantity) AS ShipmentBasePrice,
                    SUM(sl.wine_agreed_price * sl.wine_quantity) AS ShipmentAgreedPrice,
                    COALESCE(ex.shipment_expenses, 0) AS ShipmentExpenses,
                    ROUND(
                        (SUM(sl.wine_agreed_price * sl.wine_quantity) - 
                        SUM(sl.wine_base_price * sl.wine_quantity) - 
                        COALESCE(ex.shipment_expenses, 0))::NUMERIC
                    , 2) AS ShipmentProfit
                FROM shipment s
                JOIN payment p ON s.payment_id = p.payment_id
                JOIN employee e ON s.employee_id = e.employee_id
                JOIN vehicle v ON s.vehicle_id = v.vehicle_id
                join vehicle_details vd on vd.vehicle_details_id = v.vehicle_details_id
                JOIN shipment_load sl ON s.shipment_id = sl.shipment_id
                LEFT JOIN (SELECT shipment_id, SUM(amount) AS shipment_expenses FROM shipment_has_expense_of_expense_type GROUP BY shipment_id) ex ON s.shipment_id = ex.shipment_id
                WHERE s.shipment_id = {int.Parse(shipmentId)}
                GROUP BY 
                    s.shipment_id, 
                    p.payment_status, 
                    e.employee_name, 
                    e.employee_surname,
                    vd.make, 
                    vd.model, 
                    v.registration, 
                    s.shipment_date, 
                    ex.shipment_expenses;";

                var shipmentDetails = await db.QueryFirstAsync<ShipmentBaseDetails>(query);
                return Ok(shipmentDetails);               
            }
        }

        
        [HttpGet("getCustomerShipmentDetails/{shipmentId}")]
        public async Task<ActionResult<IEnumerable<ShipmentCustomerDetails>>> GetCustomerShipmentDetails(string shipmentId)
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                string query = $@"
                select 
                        c.customer_name as CustomerName,
                        CONCAT(a.city , ', ', a.street, ', ',a.building_number, ', ', a.postcode) AS CustomerAddress,
                        w.wine_name as WineName, 
                        sl.wine_base_price as WineBasePrice, 
                        sl.wine_agreed_price as WineAgreedPrice, 
                        sl.wine_quantity as WineQuantity
                from shipment_load sl
                join customer c on sl.customer_id = c.customer_id
                join address a on c.address_id = a.address_id
                join wine w on sl.wine_id = w.wine_id 
                where sl.shipment_id = {int.Parse(shipmentId)}";

                var shipmentCustomerDetails = await db.QueryAsync<ShipmentCustomerDetails>(query);
                return Ok(shipmentCustomerDetails);
            }
        }

        // PUT: api/Shipment/PayShipment/5
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPut("PayShipment/{paymentId}")]
        public async Task<IActionResult> PayShipment(int paymentId)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    var parameters = new { PaymentId = paymentId };

                    string query = $@"update payment set payment_status = 1 where payment_Id = @PaymentId";
                    await db.ExecuteAsync(query, parameters);

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

        // PUT: api/Shipment/AddShipmentExpense
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost("AddShipmentExpense")]
        public async Task<IActionResult> AddShipmentExpense([FromBody] ShipmentExpense shipmentExpense)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    var parameters = new { ShipmentId = shipmentExpense.ShipmentId, ExpenseTypeId = shipmentExpense.ExpenseTypeId, Amount = shipmentExpense.Amount };

                    string query = $@"insert into shipment_has_expense_of_expense_type (expense_type_id, shipment_id, amount) values (@ExpenseTypeId, @ShipmentId, @Amount)";
                    await db.ExecuteAsync(query, parameters);

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

        // GET: api/Shipment/GetShipmentExpenses/5
        [HttpGet("GetShipmentExpenses/{shipmentId}")]
        public async Task<ActionResult<IEnumerable<ShipmentExpense>>> GetShipmentExpenses(int shipmentId)
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                var parameters = new { ShipmentId = shipmentId };

                const string query = $@"
                    select 
                            sheoet.shipment_id as ShipmentId, 
                            sheoet.expense_type_id as ExpenseTypeId, 
                            et.expense_type_name as ExpenseTypeName, 
                            sheoet.amount as Amount
                    from shipment_has_expense_of_expense_type sheoet
                    join expense_type et on et.expense_type_id = sheoet.expense_type_id 
                    where sheoet.shipment_id = @ShipmentId 
                ";
                var vehicleDetails = await db.QueryAsync<ShipmentExpense>(query, parameters);
                return Ok(vehicleDetails);
            }
        }

        // DELETE: api/Shipment/removeShipmentExpense/5
        [HttpDelete("removeShipmentExpense/{shipmentId}/{expenseTypeId}")]
        public async Task<IActionResult> RemoveShipmentExpense(int shipmentId, int expenseTypeId)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    var parameters = new { ShipmentId = shipmentId, ExpenseTypeId = expenseTypeId };

                    string query = $@"delete from shipment_has_expense_of_expense_type where shipment_id = @ShipmentId and expense_type_id = @ExpenseTypeId";
                    await db.ExecuteAsync(query, parameters);
                    return Ok();
                }
            }
            catch (Exception ex)
            {
                return BadRequest(new ErrorHandler { Name = "Error", Description = ex.Message });
            }
        }

        // DELETE: api/Shipment/deleteShipment/5
        [HttpDelete("deleteShipment/{shipmentId}/{paymentId}")]
        public async Task<IActionResult> DeleteShipment(int shipmentId, int paymentId)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new { ShipmentId = shipmentId, PaymentId = paymentId };

                        string deleteShipmentExpenses = $@"delete from shipment_has_expense_of_expense_type where shipment_id = @ShipmentId";
                        await db.ExecuteAsync(deleteShipmentExpenses, parameters, transaction: transaction);

                        string deleteShipmentLoads = $@"delete from shipment_load where shipment_id = @ShipmentId";
                        await db.ExecuteAsync(deleteShipmentLoads, parameters, transaction: transaction);

                        string deleteShipment = $@"delete from shipment where shipment_id = @ShipmentId";
                        await db.ExecuteAsync(deleteShipment, parameters, transaction: transaction);

                        string deletePayment = $@"delete from payment where payment_id = @PaymentId";
                        await db.ExecuteAsync(deletePayment, parameters, transaction: transaction);

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

        // GET: api/Shipment/GetEditableShipment/5
        [HttpGet("GetEditableShipment/{shipmentId}")]
        public async Task<ActionResult<IEnumerable<AddShipment>>> GetEditableShipment(int shipmentId)
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                var parameters = new { ShipmentId = shipmentId };

                string getShipment = $@"
                    select 
                        shipment_id as ShipmentId, 
                        TO_CHAR(shipment_date , 'yyyy-MM-dd') as ShipmentDate,
                        employee_id as EmployeeId,
                        vehicle_id as VehicleId
                    from shipment s 
                    where shipment_id = @ShipmentId";
                var editableShipment = await db.QueryFirstAsync<AddShipment>(getShipment, parameters);

                string getCustomersPerShipment = $@"
                    select distinct 
                        c.customer_id as CustomerId
                        from shipment_load sl
                    join customer c on sl.customer_id = c.customer_id
                    where sl.shipment_id = @ShipmentId";

                var customersPerShipmentIds = await db.QueryAsync<AddCustomerToShipment>(getCustomersPerShipment, parameters);

                var wineParameters = new { CustomerIds = customersPerShipmentIds.Select(x => int.Parse(x.CustomerId)).ToList(), ShipmentId = shipmentId };

                string getWinesPerCustomer = $@"
                    select 
                            sl.wine_id as WineId,
                            customer_id as CustomerId,
                            sl.wine_quantity as Quantity, 
                            sl.wine_agreed_price * sl.wine_quantity as AgreedPrice, 
                            sl.wine_base_price as BasePrice
                    from shipment_load sl
                    WHERE sl.customer_id = ANY(@CustomerIds) and sl.shipment_id = @ShipmentId";
                var winesPerCustomersDynamic = await db.QueryAsync<dynamic>(getWinesPerCustomer, wineParameters);

                var customersPerShipmentMapped = winesPerCustomersDynamic.GroupBy(x => (int)x.customerid).Select(x => new AddCustomerToShipment
                {
                    CustomerId = x.Key.ToString(),
                    Wines = x.Select(y => new AddWineToShipment
                    {
                        WineId = y.wineid.ToString(),
                        Quantity = y.quantity,
                        AgreedPrice = (decimal)y.agreedprice,
                        BasePrice = (decimal)y.baseprice
                    }).ToList()
                }).ToList();

                editableShipment.Customers = customersPerShipmentMapped;

                return Ok(editableShipment);           
            }
        }

        [HttpPost("EditShipment")]
        public async Task<ActionResult<bool>> EditShipment([FromBody] AddShipment shipment)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new { 
                            ShipmentId = int.Parse(shipment.ShipmentId), 
                            EmployeeId = int.Parse(shipment.EmployeeId), 
                            VehicleId = int.Parse(shipment.VehicleId), 
                            ShipmentDate = DateTime.Parse(shipment.ShipmentDate) 
                        };

                        await ModelValidation.IsVehicleAlreadyBooked((NpgsqlConnection)db, (NpgsqlTransaction)transaction, parameters.VehicleId, parameters.ShipmentDate, parameters.ShipmentId);

                        string editShipment = $@"
                            update public.Shipment 
                            set
                               Employee_Id = @EmployeeId,
                               Vehicle_Id = @VehicleId,
                               Shipment_Date = @ShipmentDate
                               where shipment_id = @ShipmentId";

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

                        string deleteShipmentLoads = $@"delete from shipment_load where shipment_id = @ShipmentId";
                        await db.ExecuteAsync(deleteShipmentLoads, parameters, transaction: transaction);

                        //make a regular nested for each loop to insert the shipment_load rows
                        var updateRows = shipment.Customers.SelectMany(s => s.Wines.Select(x => $"({int.Parse(shipment.ShipmentId)}, {int.Parse(s.CustomerId)}, {x.WineId}, {Math.Round((x.AgreedPrice / x.Quantity), 2)}, {x.BasePrice}, {x.Quantity})")).ToList();
                        string updateRowsString = string.Join(",", updateRows);

                        string addShipmentLoad = $@"INSERT INTO public.shipment_load (Shipment_Id, Customer_Id, Wine_Id, Wine_Agreed_Price, Wine_Base_Price, Wine_Quantity) VALUES {updateRowsString};";
                        var shipmentLoadId = await db.ExecuteAsync(addShipmentLoad, transaction: transaction);

                        transaction.Commit();

                        return Ok(true);
                    }
                }
            }
            catch (Exception ex)
            {
                return BadRequest(ex.Message);
            }
        }
    }
}