using SKIPOperator.Context;
using SKIPOperator.Model;
using System;
using System.Linq;

namespace SKIPOperator
{
    /// <summary>
    /// This program demonstrates pagination way of displaying the records using SKIP Operator with EntityFramework 6.1
    /// 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 two tables from the database
    ///     - "Employees"
    ///     - "__MigrationHistory"
    /// </summary>
    class Program
    {
        static void Main(string[] args)
        {           
            try
            {
                using (var db = new PSQLDbContext())
                {                   
                    // Creating data for Employee
                    Console.WriteLine("Creating Employee objects.\n");
                    Employee King = new Employee { EmpName = "King", Designation = "PRESIDENT", Salary = 5000};
                    Employee Blake = new Employee { EmpName = "Blake", Designation = "MANAGER", Salary = 2850};
                    Employee Clark = new Employee { EmpName = "Clark", Designation = "MANAGER", Salary = 2450};
                    Employee Jones = new Employee { EmpName = "Jones", Designation = "MANAGER", Salary = 2975};
                    Employee Scott = new Employee { EmpName = "Scott", Designation = "ANALYST", Salary = 850};
                    Employee Ford = new Employee { EmpName = "Ford", Designation = "ANALYST", Salary = 3000};
                    Employee Smith = new Employee { EmpName = "Smith", Designation = "CLERK", Salary = 800};
                    Employee Allen = new Employee { EmpName = "Allen", Designation = "SALESMAN", Salary = 1600};
                    Employee Ward = new Employee { EmpName = "Ward", Designation = "SALESMAN", Salary = 1250};
                    Employee Martin = new Employee { EmpName = "Martin", Designation = "SALESMAN", Salary = 1250};
                    Employee Turner = new Employee { EmpName = "Turner", Designation = "SALESMAN", Salary = 1500};
                    Employee Adams = new Employee { EmpName = "Adams", Designation = "CLERK", Salary = 1100};
                    Employee James = new Employee { EmpName = "James", Designation = "CLERK", Salary = 950};
                    Employee Miller = new Employee { EmpName = "Miller", Designation = "CLERK", Salary = 1300};
            
                    Console.WriteLine("Created Employee objects.\n");
                    db.Employees.Add(King);
                    db.Employees.Add(Blake);
                    db.Employees.Add(Clark);
                    db.Employees.Add(Jones);
                    db.Employees.Add(Scott);
                    db.Employees.Add(Ford);
                    db.Employees.Add(Smith);
                    db.Employees.Add(Ward);
                    db.Employees.Add(Martin);
                    db.Employees.Add(Turner);
                    db.Employees.Add(Adams);
                    db.Employees.Add(James);
                    db.Employees.Add(Miller);
                 
                    // Save changes to database
                    // This will create Employees table
                 
                    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 '__MigrationHistory' table,");
                    Console.WriteLine("\tSaved all records to 'Employees' table ");
                    Console.WriteLine("\tSaved records successfully to database.\n");
         
                    //Number of records should be displayed per page is decided by pageSize
                    int pageSize=4;

                    //This will get the total number of records available in the Employee table
                    int numberOfRecords = db.Employees.Count();

                    //This will calculate the number of pages should be displayed based on pageSize and numberOfRecords
                    int numberOfPages = (int)(numberOfRecords / pageSize);

                    // Fetching Employee records
                    Console.WriteLine("Fetching the records saved in database.\n");

                    // We are displaying the Employee details: employee name, employee Id, employee designation,employee salary
                    // In the pagination way after sorting the employee Id in ascending order
                    //For displaying the records in pagenation manner we are using Skip operator and Take
                    for (int pageNumber = 1; pageNumber <= numberOfPages+1; pageNumber++)
                    {
                        Console.WriteLine("\nRecords In Page Number: " + pageNumber + " ==============================================================");
                        Console.WriteLine();
                        var query = db.Employees.Select(e=>new{ Name = e.EmpName,
                                                      Id = e.EmpNo,
                                                      Designation = e.Designation,
                                                      Salary = e.Salary,                              
                                                  })
                                              .OrderBy(e => e.Id)
                                              .Skip((pageNumber - 1) * pageSize)
                                              .Take(pageSize);
                        Console.WriteLine("Id,Name,Designation,Salary");             
                        Console.WriteLine("========================================================================================\n");

                        foreach (var emp in query)
                        {
                            Console.WriteLine(emp.Id + "," + emp.Name + "," + emp.Designation + "," + emp.Salary);
                        }
                        Console.WriteLine("\n----------------------------------------------------------------------------------------");
                       
                        if (pageNumber < numberOfPages + 1) {
                            Console.Write("\nPress any key for next page or press E for exit:");
                            string option = Console.ReadKey().Key.ToString();
                            if (option.ToUpper().Equals("E"))
                            {
                                break;
                            }
                         
                        }
                        else
                        {
                            Console.WriteLine("All rows are read press any key for exit:");
                            Console.ReadKey(true);
                        }                                         
                    }
                }
            }
            catch (Exception ex)
            {
                ProcessExceptionMessage(ex);
                Console.Write("Press any Key to continue:");
                Console.ReadKey();
            }          
        }

        /// <summary>
        /// Writes Exception messages to console.
        /// If Exception contains inner exception(s) then
        /// logs all inner exception messages to console
        /// </summary>
        /// <param name="ex"></param>
        private static void ProcessExceptionMessage(Exception ex)
        {
            Console.WriteLine("Exception occurred !!!");
            while (ex != null && !String.IsNullOrWhiteSpace(ex.Message))
            {
                Console.Write("-->");
                Console.WriteLine(ex.Message);
                ex = ex.InnerException;
            }
        }
    }
}