using CodeFirstWorkflow.Context;
using CodeFirstWorkflow.Model;
using System;
using System.Collections.Generic;
using System.Linq;

namespace CodeFirstWorkflow
{
    /// <summary>
    /// This program demonstrates CRUD operation using "CodeFirst" work flow using relational data
    /// Before running this application please ensure followings are done
    /// 1. Connection string is updated with correct connection details in DepartmentEmployeeContext.cs
    /// Note: This application will always 
    /// 1. Create a database mentioned in connection string (if not present already)
    /// 2. Drop the database at the end of test suite
    /// Also if you are using an existing database make sure it's clean. 
    /// </summary>
    class Program
    {
        static void Main(string[] args)
        {
            using (var db = new DepartmentEmployeeContext())
            {
                // Making sure database is created
                db.Database.EnsureCreated();
                Console.WriteLine("\nCreated database and tables\n");

                Console.WriteLine("\n=======================");
                Console.WriteLine("= Performing (C)reate =");
                Console.WriteLine("=======================\n");

                PerformCreate(db);

                Console.WriteLine("\n=====================");
                Console.WriteLine("= Performing (R)ead =");
                Console.WriteLine("=====================\n");

                PerformRead(db);

                Console.WriteLine("\n=======================");
                Console.WriteLine("= Performing (U)pdate =");
                Console.WriteLine("=======================\n");

                PerformUpdate(db);

                Console.WriteLine("\n=======================");
                Console.WriteLine("= Performing (D)elete =");
                Console.WriteLine("=======================\n");

                PerformDelete(db);

                // Making sure database is deleted
                db.Database.EnsureDeleted();
                Console.WriteLine("\nDropped database\n");
            }
        }

        private static void PerformCreate(DepartmentEmployeeContext db)
        {
            // Creating data for Department
            Console.WriteLine("Creating Department objects.\n");
            Department ACCOUNTING = new Department { DepartmentName = "ACCOUNTING", Location = "NEW YORK", Employees = new List<Employee>() };
            Department RESEARCH = new Department { DepartmentName = "RESEARCH", Location = "DALLAS", Employees = new List<Employee>() };
            Department SALES = new Department { DepartmentName = "SALES", Location = "CHICAGO", Employees = new List<Employee>() };
            Department OPERATIONS = new Department { DepartmentName = "OPERATIONS", Location = "BOSTON", Employees = new List<Employee>() };
            Console.WriteLine("Created Department objects.\n");

            // Creating data for Employee
            Console.WriteLine("Creating Employee objects.\n");
            Employee King = new Employee { EmpName = "King", Designation = "PRESIDENT", Salary = 5000, Department = ACCOUNTING };
            Employee Blake = new Employee { EmpName = "Blake", Designation = "MANAGER", Salary = 2850, Department = SALES };
            Employee Clark = new Employee { EmpName = "Clark", Designation = "MANAGER", Salary = 2450, Department = ACCOUNTING };
            Employee Jones = new Employee { EmpName = "Jones", Designation = "MANAGER", Salary = 2975, Department = RESEARCH };
            Employee Scott = new Employee { EmpName = "Scott", Designation = "ANALYST", Salary = 850, Department = RESEARCH };
            Employee Ford = new Employee { EmpName = "Ford", Designation = "ANALYST", Salary = 3000, Department = RESEARCH };
            Employee Smith = new Employee { EmpName = "Smith", Designation = "CLERK", Salary = 800, Department = RESEARCH };
            Employee Allen = new Employee { EmpName = "Allen", Designation = "SALESMAN", Salary = 1600, Department = SALES };
            Employee Ward = new Employee { EmpName = "Ward", Designation = "SALESMAN", Salary = 1250, Department = SALES };
            Employee Martin = new Employee { EmpName = "Martin", Designation = "SALESMAN", Salary = 1250, Department = SALES };
            Employee Turner = new Employee { EmpName = "Turner", Designation = "SALESMAN", Salary = 1500, Department = SALES };
            Employee Adams = new Employee { EmpName = "Adams", Designation = "CLERK", Salary = 1100, Department = RESEARCH };
            Employee James = new Employee { EmpName = "James", Designation = "CLERK", Salary = 950, Department = SALES };
            Employee Miller = new Employee { EmpName = "Miller", Designation = "CLERK", Salary = 1300, Department = ACCOUNTING };
            Console.WriteLine("Created Employee objects.\n");

            //Relating Employee objects to Department objects
            ACCOUNTING.Employees.Add(King);
            ACCOUNTING.Employees.Add(Clark);
            ACCOUNTING.Employees.Add(Miller);

            RESEARCH.Employees.Add(Jones);
            RESEARCH.Employees.Add(Scott);
            RESEARCH.Employees.Add(Ford);
            RESEARCH.Employees.Add(Smith);
            RESEARCH.Employees.Add(Adams);

            SALES.Employees.Add(Blake);
            SALES.Employees.Add(Allen);
            SALES.Employees.Add(Ward);
            SALES.Employees.Add(Martin);
            SALES.Employees.Add(Turner);
            SALES.Employees.Add(James);

            // Add objects to context
            Console.WriteLine("Adding Employee and Department objects to database context.\n");
            db.Departments.Add(ACCOUNTING);
            db.Departments.Add(RESEARCH);
            db.Departments.Add(SALES);
            db.Departments.Add(OPERATIONS);
            Console.WriteLine("Added Employee and Department objects to database context.\n");

            // Save changes to database
            // This will create Employees table and Departments table
            // will add Employee records and Department records created above
            Console.WriteLine("Saving objects in context to database. It may take some time. Please wait...\n");
            db.SaveChanges();
            Console.WriteLine("Saved records successfully to database.\n");
        }

        private static void PerformRead(DepartmentEmployeeContext db)
        {
            // Fetching records
            Console.WriteLine("Fetching the records saved in database.\n");

            // We are performing a join between Employees and Departments table
            // and we are selecting employee name, employee Id, employee designation,
            // employee salary, employee department name and employee department location
            // We are sorting the resulted data first by employee department name and then by employee name
            var query = db.Employees.Join(db.Departments,
                                          e => e.DeptNo,
                                          d => d.DepartmentNo,
                                          (e, d) => new
                                          {
                                              Name = e.EmpName,
                                              Id = e.EmpNo,
                                              Designation = e.Designation,
                                              Salary = e.Salary,
                                              Department = d.DepartmentName,
                                              Location = d.Location
                                          })
                                     .OrderBy(e => e.Department)
                                     .ThenBy(e => e.Name);

            Console.WriteLine($"{"Name",-10} - {"Id",-5} - {"Designation",-15} - {"Salary",-10} - {"Department",-10} - {"Location",-10}");
            for (int i = 1; i < 75; i++) Console.Write("=");
            Console.WriteLine();
            foreach (var emp in query)
            {
                Console.WriteLine($"{emp.Name,-10} - {emp.Id,-5} - {emp.Designation,-15} - {emp.Salary,-10} - {emp.Department,-10} - {emp.Location,-10}");
            }
            for (int i = 1; i < 75; i++) Console.Write("-");
            Console.WriteLine("\n");
        }

        private static void PerformUpdate(DepartmentEmployeeContext db)
        {
            // Fetching all Department before update
            Console.WriteLine("Fetching the departments saved in database.\n");

            FetchAllDepartments(db);

            Console.WriteLine("Updating department located in BOSTON and setting location to LA\n");
            db.Departments.Where(d => d.Location.Equals("BOSTON")).FirstOrDefault().Location = "LA";
            Console.WriteLine($"{db.SaveChanges()} records successfully updated in database.\n");

            // Fetching all department after update
            Console.WriteLine("Fetching the updated departments from database.\n");

            FetchAllDepartments(db);
        }

        private static void PerformDelete(DepartmentEmployeeContext db)
        {
            Console.WriteLine("Deleting the department located in CHICAGO\n");
            db.Departments.Remove(db.Departments.Where(d => d.Location.Equals("CHICAGO")).FirstOrDefault());
            Console.WriteLine($"{db.SaveChanges()} records successfully deleted from database.\n");

            // Fetching all Department after delete
            Console.WriteLine("Fetching updated departments from database after delete.\n");

            FetchAllDepartments(db);

            PerformRead(db);
        }

        private static void FetchAllDepartments(DepartmentEmployeeContext db)
        {
            Console.WriteLine($"{"Name",-10} - {"No",-5} - {"Location",-10}");
            for (int i = 1; i < 31; i++) Console.Write("=");
            Console.WriteLine();
            foreach (var dept in db.Departments)
            {
                Console.WriteLine($"{dept.DepartmentName,-10} - {dept.DepartmentNo,-5} - {dept.Location,-15}");
            }
            for (int i = 1; i < 31; i++) Console.Write("-");
            Console.WriteLine("\n");
        }
    }
}
