using Common;
using CustomCodeFirstConventions.Context;
using CustomCodeFirstConventions.Model;
using System;
using System.Collections.Generic;
using System.Linq;

namespace CustomCodeFirstConventions
{
    /// <summary>
    /// This program demonstrates Custom Code First Conventions feature, introduced in Entity Framework 6
    /// Before running this application please ensure followings are done
    /// 1. Connection string is updated with correct connection details in app.config
    /// Note: This application will always clean the database before running. 
    ///       It will delete following three tables from the database
    ///     - "Employees"
    ///     - "Departments"
    ///     - "__MigrationHistory"
    /// </summary>
    class Program
    {
        private static List<string> _TableNames = new List<string> { "Employees", "Departments", "__MigrationHistory" };

        static void Main(string[] args)
        {
            try
            {
                Console.WriteLine("Cleaning up Database before use.\n");
                CommonUtility.CleanUpDB(_TableNames, DbObjectType.Table);
                Console.WriteLine("Database cleaning completed.\n");

                using (var db = new PSQLDbContext())
                {
                    // 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("\tCreated 'Employees' table,");
                    Console.WriteLine("\tCreated 'Departments' table,");
                    Console.WriteLine("\tCreated '__MigrationHistory' table,");
                    Console.WriteLine("\tSaved all records to 'Employees' table and 'Departments' table.\n");
                    Console.WriteLine("Saved records successfully to database.\n");

                    // Fetching Department 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 be employee name
                    var query = db.Employees.Join(db.Departments,
                                                  e => e.DeptNo,
                                                  d => d.DepartmentKey,
                                                  (e, d) => new
                                                  {
                                                      Name = e.EmpName,
                                                      Id = e.EmpKey,
                                                      Designation = e.Designation,
                                                      Salary = e.Salary,
                                                      Department = d.DepartmentName,
                                                      Location = d.Location
                                                  })
                                             .OrderBy(e => e.Department)
                                             .ThenBy(e => e.Name);

                    Console.WriteLine("Name,Id,Designation,Salary,Department,Location");
                    Console.WriteLine("==============================================");
                    foreach (var emp in query)
                    {
                        Console.WriteLine(emp.Name + "," + emp.Id + "," + emp.Designation + "," + emp.Salary + "," + emp.Department + "," + emp.Location);
                    }
                    Console.WriteLine("----------------------------------------------");

                }
            }
            catch (Exception ex)
            {
                CommonUtility.ProcessExceptionMessage(ex);
            }
            finally
            {
                Console.Write("Press any Key to continue:");
                Console.ReadKey(true);
            }
        }
    }
}
