IntroductionToEntityFramework

using Microsoft.EntityFrameworkCore;
using SoftUni.Data;
using SoftUni.Models;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;

namespace SoftUni
{
    public class StartUp
    {
        public static void Main(string[] args)
        {
            using (SoftUniContext context = new SoftUniContext())
            {
                Console.WriteLine(IncreaseSalaries(context));
            }
        }
        //15
        public static string RemoveTown(SoftUniContext context)
        {
            StringBuilder sb = new StringBuilder();
            var empToNull = context.Employees.Where(x => x.Address.Town.Name == "Seattle").ToList(); ;
            var addressToDel = context.Addresses.Where(n => n.Town.Name == "Seattle").ToList();
            var townToDel = context.Towns.FirstOrDefault(n => n.Name == "Seattle");


            foreach (var emp in empToNull)
            {
                emp.AddressId = null;
            }
            context.SaveChanges();

            context.Addresses.RemoveRange(addressToDel);
            int count = context.SaveChanges();
            context.Towns.Remove(townToDel);
            context.SaveChanges();

            sb.AppendLine($"{count} addresses in Seattle were deleted");

            return sb.ToString().Trim();
        }


        //14
        public static string DeleteProjectById(SoftUniContext context)
        {
            StringBuilder sb = new StringBuilder();

            var projectToDelete = context.EmployeesProjects.Where(p => p.ProjectId == 2);

            context.EmployeesProjects.RemoveRange(projectToDelete);
            var projectToDel = context.Projects.FirstOrDefault(p => p.ProjectId == 2);
            context.Projects.Remove(projectToDel);
            context.SaveChanges();

            var projectToPrint = context.Projects.Take(10)
                .Select(p => new
                {
                    p.Name
                }).ToList();

            foreach (var pro in projectToPrint)
            {
                sb.AppendLine(pro.Name);
            }

            return sb.ToString().Trim();
        }


        //13
        public static string GetEmployeesByFirstNameStartingWithSa(SoftUniContext context)
        {
            StringBuilder sb = new StringBuilder();

            var emp = context.Employees.Where(n => EF.Functions.Like(n.FirstName, ("sa%")))
                .Select(e => new
                {
                    e.FirstName,
                    e.LastName,
                    e.JobTitle,
                    e.Salary
                }).OrderBy(x => x.FirstName).ThenBy(x => x.LastName).ToList();

            foreach (var item in emp)
            {
                sb.AppendLine($"{item.FirstName} {item.LastName} - {item.JobTitle} - (${item.Salary:F2})");
            }

            return sb.ToString().Trim();
        }


        //12
        public static string IncreaseSalaries(SoftUniContext context)
        {
            StringBuilder sb = new StringBuilder();

            List<String> depContr = new List<string>()
            {
                "Engineering","Tool Design","Marketing","Information Services"
            };

            List<Employee> employees = context.Employees
                .Where(d => depContr.Contains(d.Department.Name))
                .OrderBy(e => e.FirstName).ThenBy(e => e.LastName).ToList();

            foreach (var item in employees)
            {
                item.Salary *= 1.12M;
            }

            context.SaveChanges();

            List<Employee> updEmp = context.Employees
                .Where(d => depContr.Contains(d.Department.Name))
                .OrderBy(e => e.FirstName).ThenBy(e => e.LastName).ToList();

            foreach (var item in updEmp)
            {
                sb.AppendLine($"{item.FirstName} {item.LastName} (${item.Salary:F2})");
            }



            return sb.ToString().Trim();
        }


        //11
        public static string GetLatestProjects(SoftUniContext context)
        {
            StringBuilder sb = new StringBuilder();

            var projects = context.Projects.OrderByDescending(p => p.StartDate).Take(10)
                .Select(p => new
                {
                    p.Name,
                    p.Description,
                    p.StartDate
                }).OrderBy(p => p.Name).ToList();


            foreach (var pro in projects)
            {
                sb.AppendLine(pro.Name);
                sb.AppendLine(pro.Description);
                sb.AppendLine(pro.StartDate.ToString("M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture));
            }

            return sb.ToString().Trim();
        }

        //10
        public static string GetDepartmentsWithMoreThan5Employees(SoftUniContext context)
        {
            StringBuilder sb = new StringBuilder();

            var departments = context.Departments
                .Where(d => d.Employees.Count > 5)
                .OrderBy(d => d.Employees.Count).ThenBy(d => d.Name)
                .Select(d => new
                {
                    d.Name,
                    ManagerName = d.Manager.FirstName + " " + d.Manager.LastName,
                    Employees = d.Employees.Select(e => new
                    {
                        EmployeeName = e.FirstName + " " + e.LastName,
                        e.JobTitle
                    }).OrderBy(n => n.EmployeeName).ToList()
                }).ToList();

            foreach (var dep in departments)
            {
                sb.AppendLine($"{dep.Name} - {dep.ManagerName}");

                foreach (var emp in dep.Employees)
                {
                    sb.AppendLine($"{emp.EmployeeName} - {emp.JobTitle}");
                }
            }


            return sb.ToString().Trim();
        }

        //09
        public static string GetEmployee147(SoftUniContext context)
        {
            StringBuilder sb = new StringBuilder();

            var emp = context.Employees
                .Select(e => new
                {
                    e.FirstName,
                    e.LastName,
                    e.JobTitle,
                    e.EmployeeId,
                    Projects = e.EmployeesProjects.Select(p => new
                    {
                        p.Project.Name
                    }).OrderBy(n => n.Name).ToList()
                })
                .FirstOrDefault(i => i.EmployeeId == 147);

            sb.AppendLine($"{emp.FirstName} {emp.LastName} - {emp.JobTitle}");

            foreach (var project in emp.Projects)
            {
                sb.AppendLine(project.Name);
            }



            return sb.ToString().Trim();
        }

        //08
        public static string GetAddressesByTown(SoftUniContext context)
        {
            StringBuilder stringBuilder = new StringBuilder();

            var addressesCount = context.Addresses
                .Select(a => new
                {
                    a.AddressText,
                    a.Town.Name,
                    a.Employees.Count,
                }).OrderByDescending(c => c.Count).ThenBy(t => t.Name).Take(10).ToList();

            foreach (var address in addressesCount)
            {
                stringBuilder.AppendLine($"{address.AddressText}, {address.Name} - {address.Count} employees");
            }

            return stringBuilder.ToString().Trim();
        }

        //07
        public static string GetEmployeesInPeriod(SoftUniContext context)
        {
            StringBuilder sb = new StringBuilder();


            var employeeProjectList = context.Employees
                .Where(p => p.EmployeesProjects.Any(s => s.Project.StartDate.Year >= 2001 && s.Project.StartDate.Year <= 2003)).Take(10)
                .Select(e => new
                {
                    EmployeeName = e.FirstName + " " + e.LastName,
                    ManagerName = e.Manager.FirstName + " " + e.Manager.LastName,
                    Projects = e.EmployeesProjects
                    .Select(t => new
                    {
                        t.Project.Name,
                        t.Project.StartDate,
                        t.Project.EndDate
                    }).ToList()
                })
               .ToList();

            foreach (var emp in employeeProjectList)
            {
                sb.AppendLine($"{emp.EmployeeName} - Manager: {emp.ManagerName}");

                foreach (var project in emp.Projects)
                {
                    string startDate = project.StartDate.ToString("M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture);

                    string endDate = project.EndDate.HasValue ? project.EndDate.Value.ToString("M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture) : "not finished";

                    sb.AppendLine($"--{project.Name} - {startDate} - {endDate}");
                }
            }

            return sb.ToString().Trim();
        }

        //06
        public static string AddNewAddressToEmployee(SoftUniContext context)
        {
            StringBuilder sb = new StringBuilder();

            Address address = new Address
            {
                AddressText = "Vitoshka 15",
                TownId = 4
            };

            context.Addresses.Add(address);

            List<Employee> nakovs = context.Employees.Where(e => e.LastName == "Nakov").ToList();

            foreach (var item in nakovs)
            {
                item.Address = address;
            }

            context.SaveChanges();

            var employees = context.Employees.OrderByDescending(e => e.AddressId)
                .Select(e => e.Address.AddressText).Take(10).ToList();


            foreach (var item in employees)
            {
                sb.AppendLine(item);
            }

            return sb.ToString().TrimEnd();
        }
        //05
        public static string GetEmployeesFromResearchAndDevelopment(SoftUniContext context)
        {
            StringBuilder sb = new StringBuilder();

            var emps = context.Employees
                .Select(e => new
                {
                    e.FirstName,
                    e.LastName,
                    DepartmentName = e.Department.Name,
                    e.Salary
                }).Where(d => d.DepartmentName == "Research and Development")
                .OrderBy(s => s.Salary)
                .ThenByDescending(n => n.FirstName);

            foreach (var emp in emps)
            {
                sb.AppendLine($"{emp.FirstName} {emp.LastName} from Research and Development - ${emp.Salary:F2}");
            }



            return sb.ToString().TrimEnd();
        }

        //04
        public static string GetEmployeesWithSalaryOver50000(SoftUniContext context)
        {
            StringBuilder sb = new StringBuilder();

            var empOver50000 = context.Employees
                .Select(e => new
                {
                    e.FirstName,
                    e.Salary
                }).Where(s => s.Salary > 50000).OrderBy(e => e.FirstName);

            foreach (var emp in empOver50000)
            {
                sb.AppendLine($"{emp.FirstName} - {emp.Salary:F2}");
            }

            return sb.ToString().TrimEnd();
        }
        //03
        public static string GetEmployeesFullInformation(SoftUniContext context)
        {
            StringBuilder sb = new StringBuilder();

            var emps = context.Employees
                .Select(e => new
                {
                    e.FirstName,
                    e.LastName,
                    e.MiddleName,
                    e.JobTitle,
                    e.Salary,
                    e.EmployeeId
                }).OrderBy(e => e.EmployeeId);

            foreach (var emp in emps)
            {
                sb.AppendLine($"{emp.FirstName} {emp.LastName} {emp.MiddleName} {emp.JobTitle} {emp.Salary:F2}");
            }


            return sb.ToString().TrimEnd();
        }
    }
}