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

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

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

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

        // GET: api/Employee
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Employee>>> GetEmployee()
        {
            using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
            {
                const string query = $@"
                    select 
                        e.employee_id as EmployeeId, 
                        e.warehouse_id as WarehouseId,
                        w.warehouse_name as WarehouseName,
                        e.employee_name as EmployeeName, 
                        e.employee_surname as EmployeeSurname
                    from employee e
                    join warehouse w on w.warehouse_id = e.warehouse_id";
                var employees = await db.QueryAsync<Employee>(query);
                return Ok(employees);
            }
        }

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

                string query = $@"
                    select 
                        e.employee_id as EmployeeId, 
                        e.warehouse_id as WarehouseId,
                        w.warehouse_name as WarehouseName,
                        e.employee_name as EmployeeName, 
                        e.employee_surname as EmployeeSurname
                    from employee e
                    join warehouse w on w.warehouse_id = e.warehouse_id
                    where e.employee_id = @id";

                var employee = await db.QueryFirstAsync<Employee>(query, parameters);

                return Ok(employee);
            }
        }

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

                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 employee_drives_vehicle edv 
                    join vehicle v on v.vehicle_id = edv.vehicle_id 
                    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 
                    where edv.employee_id = @id";

                var employeeVehicleDetails = await db.QueryAsync<VehicleDetails>(query, parameters);

                return Ok(employeeVehicleDetails);
            }
        }

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

                        await ModelValidation.DoesEmployeeExist((NpgsqlConnection)db, (NpgsqlTransaction)transaction, parameters.EmployeeName, parameters.EmployeeSurname, parameters.EmployeeId);

                        string updateCustomer = $@"
                        update employee 
                        set 
                            warehouse_id = @WarehouseId,
                            employee_name = @EmployeeName,
                            employee_surname = @EmployeeSurname
                        where employee_id = @EmployeeId";

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

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

        // POST: api/Employee
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost]
        public async Task<IActionResult> PostEmployee([FromBody] Employee employee)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    db.Open();
                    using (var transaction = db.BeginTransaction()) // Start a transaction
                    {
                        var parameters = new
                        {
                            EmployeeId = employee.EmployeeId,
                            WarehouseId = employee.WarehouseId,
                            EmployeeName = employee.EmployeeName.Trim(),
                            EmployeeSurname = employee.EmployeeSurname.Trim(),
                        };

                        await ModelValidation.DoesEmployeeExist((NpgsqlConnection)db, (NpgsqlTransaction)transaction, parameters.EmployeeName, parameters.EmployeeSurname);

                        string addEmployee = $@"
                        INSERT INTO public.employee (warehouse_id, employee_name, employee_surname) 
                        VALUES (@WarehouseId, @EmployeeName, @EmployeeSurname);";

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

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

        // POST: api/EmployeeVehicle
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost("PostEmployeeVehicle")]
        public async Task<ActionResult<EmployeeVehicle>> PostEmployeeVehicle([FromBody] EmployeeVehicle employeeVehicle)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {

                    var parameters = new
                    {
                        EmployeeId = employeeVehicle.EmployeeId,
                        VehicleId = employeeVehicle.VehicleId,
                    };

                    string addEmployeeVehicle = $@"
                    INSERT INTO public.employee_drives_vehicle (employee_id, vehicle_Id) 
                    VALUES (@EmployeeId, @VehicleId);";

                    await db.ExecuteAsync(addEmployeeVehicle, parameters);

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

        // DELETE: api/Employee/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteEmployee(int id)
        {
            try
            {
                //Check if there is a shipment load linked to the employee we are trying to delete, if so return a message and dont let the user delete the employee.
                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 employeeLinkToShipmentLoadQuery = $@"
                            select Count(*)
                            from shipment s 
                            where employee_id = @id";

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

                        string deleteEmployee = $@"delete from employee where employee_id = @id";
                        string deleteEmployeeVehicleLink = $@"delete from employee_drives_vehicle edv where employee_id = @id";

                        await db.ExecuteAsync(deleteEmployeeVehicleLink, parameters, transaction: transaction);
                        await db.ExecuteAsync(deleteEmployee, parameters, transaction: transaction);

                        transaction.Commit();

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

        // DELETE: api/Employee/5
        [HttpDelete("RemoveVehicleFromEmployee/{employeeId}/{vehicleId}")]
        public async Task<IActionResult> RemoveVehicleFromEmployee(int employeeId, int vehicleId)
        {
            try
            {
                using (IDbConnection db = new NpgsqlConnection(_connectionString)) // Use NpgsqlConnection for PostgreSQL
                {
                    var parameters = new { employeeId = employeeId, vehicleId = vehicleId };

                    string deleteEmployeeVehicleLink = $@"delete from employee_drives_vehicle edv where employee_id = @employeeId and vehicle_id = @vehicleId";

                    await db.ExecuteAsync(deleteEmployeeVehicleLink, parameters);

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