using Common;
using SetInitializer.DropCreateDatabaseIfModelChanges.Context;
using SetInitializer.DropCreateDatabaseIfModelChanges.Model;
using System;
using System.Collections.Generic;
using System.Linq;

namespace SetInitializer.DropCreateDatabaseIfModelChanges
{
    /// <summary>
    /// This program demonstrates Set Initializer (Drop and Create Database if Model Changes) feature
    /// Before running this application please ensure followings are done
    /// 1. Connection string is updated with correct connection details in app.config
    /// 2. Only before the first run, ensure to delete following three tables from the database if exist
    ///     - "Departments"
    ///     - "Employees"
    ///     - "__MigrationHistory"
    ///     
	/// In this DropCreateDatabaseIfModelChanges SetInitializer sample, this application needs to run twice.
    /// In second run, please ensure that code written just after the comment "Uncomment the following piece of code in second run." is uncommented.
    /// with this the application tries to add a column named EmpStrength and due to DropCreateDatabaseIfModelChanges SetInitializer, database objects gets dropped and created reflecting newly added column.
    /// </summary>
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                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);

                    // Uncomment the following piece of code in second run.
                    // This code adds a new column 'EmpStrength' to the Departments table
                    // and holds data for each row of Departments table
                    // Also uncomment the property 'EmpStrength' in Department class

                    /*
                    ACCOUNTING.EmpStrength = ACCOUNTING.Employees.Count();
                    RESEARCH.EmpStrength = RESEARCH.Employees.Count();
                    SALES.EmpStrength = SALES.Employees.Count();
                    OPERATIONS.EmpStrength = OPERATIONS.Employees.Count();
                    Console.WriteLine("Adding new Column 'EmpStrength' to Departments table.\nThis change should be reflected in Departments table.\n");
                    */

                    // 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");

                    // Uncomment the following piece of code in second run.
                    // Fetching Department records

                    /*
                    var query = db.Departments.OrderBy(d => d.DepartmentName);
                    Console.WriteLine("Name,No.,Location,Strength");
                    Console.WriteLine("==========================");
                    foreach (var dept in query)
                    {
                        Console.WriteLine(dept.DepartmentName + "," + dept.DepartmentNo + "," + dept.Location + "," + dept.EmpStrength);
                    }
                    Console.WriteLine("---------------------------");
                    */

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