using Common;
using IndexAnnotation.Context;
using IndexAnnotation.Model;
using Pervasive.Data.SqlClient;
using System;
using System.Collections.Generic;
using System.Data;

namespace IndexAnnotation
{
    
    /// <summary>
    /// This demonstrates the[Index] Annotation feature - introduced in Entity Framework 6.1.2 - https://entityframework.codeplex.com/workitem/57
    /// Before running this application please ensure the following
    /// 1. Connection string is updated with correct connection details in "app.config"
    /// Note: This application will always clean the database before running as the SetInitializer is set to "DropCreateDatabaseAlways". 
    ///     It will delete following three tables from the database
    ///     - "Customers"
    ///     - "Orders"
    ///     - "__MigrationHistory"
    /// </summary>
    class Program
    {
        private static List<string> _TableNames = new List<string> { "Customers", "Orders", "__MigrationHistory" };

        static void Main(string[] args)
        {
            Console.WriteLine("\n\n **************** Sample to Demonstrate the [Index] Annotation of EF 6.1 ***************** \n");
           
            try
            {
                Console.WriteLine("Cleaning up Database before use.\n");
                CommonUtility.CleanUpDB(_TableNames, DbObjectType.Table);
                Console.WriteLine("Database cleaning completed.\n");

                using (var context = new PSQLDbContext())
                {
                    //Uncomment the following if you want to see the Database Log
                    //context.Database.Log = Console.Write;

                    // Creating data for Customers and Orders Tables                   
                    Console.WriteLine("Creating 'Customer' & 'Order' Objects.... \n ");
                    Customer cust1 = new Customer { custID = 100, custName = "John", Orders = new List<Order>() };
                    Customer cust2 = new Customer { custID = 200, custName = "Dave", Orders = new List<Order>() };
                    Customer cust3 = new Customer { custID = 300, custName = "Tom", Orders = new List<Order>() };


                    Order order1 = new Order { orderID = 1, orderDetails = "TV , Laptop", nonKeyDefault = 333 };
                    Order order2 = new Order { orderID = 2, orderDetails = "Charger , SmartPhone", nonKeyDefault = 222 };

                    Console.WriteLine("Created the 'Customer' & 'Order' Objects....\n");

                    //Relate the Tables
                    cust1.Orders.Add(order1);
                    cust2.Orders.Add(order2);


                    //Adding the tables created above to the Context
                    Console.WriteLine("Adding the 'Customer' and 'Order' Objects to the DB Context... This may a take a while... \n");
                    context.Customers.Add(cust1);
                    context.Customers.Add(cust2);


                    Console.WriteLine("Saving the Objects in the Context to the Database.. ");
                    context.SaveChanges();
                    Console.WriteLine("\tCreated 'Customers' table,");
                    Console.WriteLine("\tCreated 'Orders' table,");
                    Console.WriteLine("\tCreated '__MigrationHistory' table,");
                    Console.WriteLine("\tCreated 'Indexes' ");
                    Console.WriteLine("\tSaved all records to the 'Customers' table and 'Orders' table.\n");

                    // Lets Fetch and display the Indexes generated on the Tables 

                    string conString = System.Configuration.ConfigurationManager.ConnectionStrings["PSQLDbContext"].ConnectionString;
                    PsqlConnection con = new PsqlConnection(conString);
                    con.Open();                    
                   
                    displayIndexes(con, "Customers");
                    displayIndexes(con, "Orders");
                                                    
                }
            }
            catch (Exception ex)
            {
                CommonUtility.ProcessExceptionMessage(ex);                
            }
            finally
            {
                Console.Write("Press any Key to continue:");
                Console.ReadKey();
            }
            
        }


        public static void displayIndexes(PsqlConnection con, string tableName)
        {
            Console.WriteLine("\nLets see the Indexes we generated on the "+ tableName+ " Table \n");

            var indexes = con.GetSchema("Indexes", new[] { null, tableName });
            foreach (DataRow row in indexes.Rows)
            {
                string indexName = row["INDEX_NAME"].ToString();

                // We want to display only the Index we are interested in.
                if (indexName.Equals("CustomIndex"))
                {
                    Console.WriteLine(" Table Name  :  " + row["TABLE_NAME"].ToString());
                    Console.WriteLine(" Column Name :  " + row["COLUMN_NAME"].ToString());
                    Console.WriteLine(" Index  Name :  " + indexName);
                    Console.WriteLine(" Is Unique?  :  " + row["UNIQUE"].ToString());

                }
                if (tableName.Equals("Orders"))
                {
                    if (indexName.Equals("IX_nonKeyDefault") || indexName.Equals("FKIndex"))
                    {
                        Console.WriteLine(" Table Name  :  " + row["TABLE_NAME"].ToString());
                        Console.WriteLine(" Column Name :  " + row["COLUMN_NAME"].ToString());
                        Console.WriteLine(" Index  Name :  " + indexName);
                        Console.WriteLine(" Is Unique?  :  " + row["UNIQUE"].ToString() + "\n\n");
                    }
                }
            }
        }
    }
}
