using System;
using System.Collections.Generic;
using System.Linq;
using System.Collections;
using NorthwindDataAccess;
using HelperFunctions;

namespace Scratch
{
    class Program
    {
        static void Main(string[] args)
        {
            #region unused
            var itemTotalCounts =
                from c in Northwind.OrderDetails
                group c by c.OrderID into grouped
                orderby grouped.Count() descending
                select new
                {
                    OrderID = grouped.Key,
                    Count = grouped.Count()
                };

            var allCustomers =
                from c in Northwind.Customers
                select new
                    {
                        Name = c.ContactName
                    };

            var all =
                from c in Northwind.Customers
                join o in Northwind.Orders on
                c.CustomerID equals o.CustomerID into joined
                from co in joined.DefaultIfEmpty()
                let OrderID = co == null ? -1 : co.OrderID
                orderby OrderID
                select new { c.ContactName, OrderID };


            var customerPhone =
                from c in Northwind.Customers
                orderby c.Phone.Replace("(", "").Replace(")", "")
                select c.Phone;


            var customerName =
                from c in Northwind.Customers
                select c.ContactName;
            #endregion

            var employeesAndSupers =
                from e in Northwind.Employees
                join s in Northwind.Employees on
                e.ReportsTo equals s.EmployeeID into joined
                from rt in joined.DefaultIfEmpty()
                select new
                    {
                        EmpName = e.FirstName + " " + e.LastName,
                        BossName = rt.FirstName + " " + rt.LastName,
                        BossPhoneNbr = rt.HomePhone != null ? rt.HomePhone : ""
                    };

            var employeesFirstAndLastNamesWithPhoneNumbers =
                from e in Northwind.Employees
                select new
                    {
                        Name = e.FirstName + " " + e.LastName,
                        PhoneNbr = e.HomePhone
                    };

            var employeesFromWashingtonState =
                from e in Northwind.Employees
                where e.Region == "WA"
                select e.FirstName + " " + e.LastName;

            var employeesFromTheSameRegionAsCustomers =
                from e in Northwind.Employees
                join c in Northwind.Customers on
                e.Region equals c.Region
                select e.FirstName + " " + e.LastName;

            var allProductsWithCategories =
                from p in Northwind.Products
                join c in Northwind.Categories on
                p.CategoryID equals c.CategoryID
                select new
                    {
                        p.ProductName,
                        c.CategoryName
                    };

            var avgPriceOfProduct =
                from p in Northwind.Products
                select p.UnitPrice;

            var allOrders =
                from o in Northwind.Orders
                select o.OrderID;

            var customersWhoHaventBoughtAnythingLikeEver =
                from c in Northwind.Customers
                where !Northwind.Orders.Any(o => o.CustomerID == c.CustomerID)
                select c.ContactName;

            var productsThatAreBelowTheirReorderPoint =
                from p in Northwind.Products
                where p.UnitsInStock + p.UnitsOnOrder < p.ReorderLevel && !p.Discontinued
                select new
                    {
                        p.ProductID,
                        p.ProductName,
                        p.UnitsInStock,
                        p.UnitsOnOrder,
                        p.ReorderLevel
                    };

            var doStuff =
                from c in Northwind.Customers
                let orderCustomerCombo =
                    from od in Northwind.OrderDetails
                    join o in Northwind.Orders on
                    od.OrderID equals o.OrderID
                    group new { 
                        OrderID = od.OrderID, 
                        CustomerID = o.CustomerID 
                    }
                        by o.OrderID
                where !orderCustomerCombo.Any(g => g.Any(a => a.CustomerID.Equals(c.CustomerID)))
                select c.ContactName;
            
            doStuff.P();

            //var doingSweetStuffWithLINQIsReallyPrettySimpleOnceYouGetDownToItYaKnow =
            //    from od in Northwind.OrderDetails
            //    join p in Northwind.Products on
            //    od.ProductID equals p.ProductID
            //    where p.Discontinued
            //    group od by od.ProductID into grouped
            //    orderby grouped.Count() descending

            //    }
            //    ;
            //};

            //var employeesEmployedForOver14Years =
            //    from e in Northwind.Employees
            //    where DateTime.Now.Year - e.HireDate.Year > 14
            //    select e.FirstName + " " + e.LastName;


            //var ordersWithItemCounts =
            //    from o in Northwind.Orders
            //    join d in Northwind.OrderDetails on
            //    o.OrderID equals d.OrderID
            //    join p in Northwind.Products on
            //    d.ProductID equals p.ProductID
            //    let total = d.Quantity * d.UnitPrice
            //    group new
            //    {
            //        d.OrderID,
            //        d.ProductID,
            //        d.Quantity,
            //        d.UnitPrice,
            //        total
            //    }
            //        by d.OrderID;

            //foreach (var group in ordersWithItemCounts.Take(3))
            //    group.P();

            //doingSweetStuffWithLINQIsReallyPrettySimpleOnceYouGetDownToItYaKnow.Distinct().P();
            //ordersWithItemCounts.P();
            //employeesEmployedForOver14Years.P();
            //productsThatAreBelowTheirReorderPoint.P();
            //customersWhoHaventBoughtAnythingLikeEver.P();
            //SelectProductsSoldForOrderID(11077).P();
            //allProductsWithCategories.P();
            //employeesAndSupers.P();
            //employeesFromWashingtonState.P();
            //employeesFromTheSameRegionAsCustomers.Distinct().P();
            //employeesFirstAndLastNamesWithPhoneNumbers.P();
            //avgPriceOfProduct.Average().P();

            Console.ReadKey();
        }

        static void ShowFirstThree<T>(IEnumerable<T> enumerable)
        {
            foreach (T item in enumerable.Take(3))
                ReflectionHelper.PropertiesToString(item).P();
        }

        static IEnumerable SelectProductsSoldForOrderID(int OrderID)
        {
            var ordersWithItems =
                from o in Northwind.Orders
                join d in Northwind.OrderDetails on
                o.OrderID equals d.OrderID
                join p in Northwind.Products on
                d.ProductID equals p.ProductID
                where o.OrderID == OrderID
                select new
                    {
                        ProductName = p.ProductName,
                        ProductPrice = d.UnitPrice,
                        ProductQty = d.Quantity,
                        TotalPaid = d.UnitPrice * d.Quantity
                    };

            return ordersWithItems;
        }
    }
}