using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Linq.Expressions;

using BLToolkit.Data;
using BLToolkit.Data.Linq;

namespace Linq.Demo
{
	using DataModel;

	static class Program
	{
		static void Main()
		{
			DbManager.TraceSwitch = new TraceSwitch("DbManager", "DbManager trace switch", "Info");

			FirstTest();
			CountTest();
			SingleTableTest();
			SelectManyTest();
			InnerJoinTest();
			LeftJoinTest();
			AssociationInnerJoinTest();
			AssociationCountTest();
			AssociationObjectTest();
			MultiLevelAssociationTest();
			CompareAssociationTest();
			GroupByAssociationTest();
			InheritanceTest1();
			InheritanceTest2();
			StringLengthTest();
			StringCompareTest();
			MathRoundCompare1Test();
			MathRoundCompare2Test();
			MathRoundCompare3Test();
			SimpleSelectTest();
			InsertTest1();
			InsertTest2();
			MultipleInsertTest1();
			MultipleInsertTest2();
			InsertWithIdentityTest1();
			InsertWithIdentityTest2();
			UpdateTest1();
			UpdateTest2();
			UpdateTest3();
			SelfUpdateTest();
			DeleteTest1();
			DeleteTest2();
			SqlLengthTest();
			SqlRoundCompare1Test();
			SqlSelectLengthTest();
			SqlSelectLengthAsSqlTest();
			RoundToEvenTest();
			MethodExpressionTest();
			CompiledQueryTest();
		}

		static void FirstTest()
		{
			using (var db = new NorthwindDB())
			{
				var query = db.Employee;

				foreach (var employee in query)
				{
					Console.WriteLine("{0} {1}", employee.EmployeeID, employee.FirstName);
				}
			}
		}

		static void CountTest()
		{
			using (var db = new NorthwindDB())
			{
				int count = db.Employee.Count();

				Console.WriteLine(count);
			}
		}

		static void SingleTableTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from e in db.Employee
					where e.EmployeeID > 5
					orderby e.LastName, e.FirstName
					select e;

				foreach (var employee in query)
				{
					Console.WriteLine("{0} {1}, {2}", employee.EmployeeID, employee.LastName, employee.FirstName);
				}
			}
		}

		static void SelectManyTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from c in db.Category
					from p in db.Product
					where p.CategoryID == c.CategoryID
					select new
					{
						c.CategoryName,
						p.ProductName
					};

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void InnerJoinTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from p in db.Product
					join c in db.Category on p.CategoryID equals c.CategoryID
					select new
					{
						c.CategoryName,
						p.ProductName
					};

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void LeftJoinTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from p in db.Product
					join c in db.Category on p.CategoryID equals c.CategoryID into g
					from c in g.DefaultIfEmpty()
					select new
					{
						c.CategoryName,
						p.ProductName
					};

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void AssociationInnerJoinTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from p in db.Product
					select new
					{
						p.Category.CategoryName,
						p.ProductName
					};

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void AssociationCountTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from p in db.Product
					select new
					{
						p.OrderDetails.Count,
						p.ProductName
					};

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void AssociationObjectTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from o in db.Order
					select new Order
					{
						OrderID  = o.OrderID,
						Customer = o.Customer
					};

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void MultiLevelAssociationTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from o in db.OrderDetail
					select new
					{
						o.Product.ProductName,
						o.Order.OrderID,
						o.Order.Employee.ReportsToEmployee.Region
					};

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void CompareAssociationTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from o in db.Order
					from t in db.EmployeeTerritory
					where o.Employee == t.Employee
					select new
					{
						o.OrderID,
						o.EmployeeID,
						t.TerritoryID
					};

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void GroupByAssociationTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from p in db.Product
					group p by p.Category into g
					where g.Count() == 12
					select g.Key.CategoryName;

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void InheritanceTest1()
		{
			using (var db = new NorthwindDB())
			{
				var query = from p in db.DiscontinuedProduct select p;

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void InheritanceTest2()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from p in db.Product
					where p is DiscontinuedProduct
					select p;

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void StringLengthTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from c in db.Customer
					where c.ContactName.Length > 5
					select c.ContactName;

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void StringCompareTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from c in db.Customer
					where c.ContactName.CompareTo("John") > 0
					select c.ContactName;

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void MathRoundCompare1Test()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from o in db.Order
					where Math.Round(o.Freight.Value) >= 10
					select o.Freight;

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void MathRoundCompare2Test()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from o in db.Order
					where Math.Round(o.OrderDetails.Sum(d => d.Quantity * d.UnitPrice)) >= 10
					select o.Freight;

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void MathRoundCompare3Test()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from o in db.Order
					let sum = o.OrderDetails.Sum(d => d.Quantity * d.UnitPrice)
					where Math.Round(sum) >= 10
					select o.Freight;

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void SimpleSelectTest()
		{
			using (var db = new NorthwindDB())
			{
				var value = db.Select(() => Sql.CurrentTimestamp);

				Console.WriteLine(value);
			}
		}

		static void InsertTest1()
		{
			using (var db = new NorthwindDB())
			{
				var value = db.Employee.Insert(() => new Employee
				{
					FirstName = "John",
					LastName  = "Shepard",
					Title     = "Spectre",
					HireDate  = Sql.CurrentTimestamp
				});

				Console.WriteLine(value);

				db.Employee.Delete(e => e.LastName == "Shepard");
			}
		}

		static void InsertTest2()
		{
			using (var db = new NorthwindDB())
			{
				var value =
					db
						.Into(db.Employee)
							.Value(e => e.FirstName, "John")
							.Value(e => e.LastName,  "Shepard")
							.Value(e => e.Title,     "Spectre")
							.Value(e => e.HireDate,  () => Sql.CurrentTimestamp)
						.Insert();

				Console.WriteLine(value);

				db.Employee.Delete(e => e.LastName == "Shepard");
			}
		}

		static void MultipleInsertTest1()
		{
			using (var db = new NorthwindDB())
			{
				var value =
					db.Region
						.Where(r => r.RegionID > 2)
						.Insert(db.Region, r => new Region()
						{
							RegionID          = r.RegionID + 100,
							RegionDescription = "Copy Of " + r.RegionDescription
						});

				Console.WriteLine(value);

				db.Region.Delete(r => r.RegionDescription.StartsWith("Copy Of "));
			}
		}

		static void MultipleInsertTest2()
		{
			using (var db = new NorthwindDB())
			{
				var value =
					db.Region
						.Where(r => r.RegionID > 2)
						.Into(db.Region)
							.Value(r => r.RegionID,          r => r.RegionID + 100)
							.Value(r => r.RegionDescription, r => "Copy Of " + r.RegionDescription)
						.Insert();

				Console.WriteLine(value);

				db.Region.Delete(r => r.RegionDescription.StartsWith("Copy Of "));
			}
		}

		static void InsertWithIdentityTest1()
		{
			using (var db = new NorthwindDB())
			{
				var value = db.Employee.InsertWithIdentity(() => new Employee
				{
					FirstName = "John",
					LastName  = "Shepard",
					Title     = "Spectre",
					HireDate  = Sql.CurrentTimestamp
				});

				Console.WriteLine(value);

				db.Employee.Delete(e => e.EmployeeID == Convert.ToInt32(value));
			}
		}

		static void InsertWithIdentityTest2()
		{
			using (var db = new NorthwindDB())
			{
				var value =
					db
						.Into(db.Employee)
							.Value(e => e.FirstName, "John")
							.Value(e => e.LastName,  "Shepard")
							.Value(e => e.Title,     () => "Spectre")
							.Value(e => e.HireDate,  () => Sql.CurrentTimestamp)
						.InsertWithIdentity();

				Console.WriteLine(value);

				db.Employee.Delete(e => e.EmployeeID == Convert.ToInt32(value));
			}
		}

		static void UpdateTest1()
		{
			using (var db = new NorthwindDB())
			{
				var value =
					db.Employee
						.Update(
							e => e.Title == "Spectre",
							e => new Employee
							{
								Title = "Commander"
							});

				Console.WriteLine(value);
			}
		}

		static void UpdateTest2()
		{
			using (var db = new NorthwindDB())
			{
				var value =
					db.Employee
						.Where(e => e.Title == "Spectre")
						.Update(e => new Employee
						{
							Title = "Commander"
						});

				Console.WriteLine(value);
			}
		}

		static void UpdateTest3()
		{
			using (var db = new NorthwindDB())
			{
				var value =
					db.Employee
						.Where(e => e.Title == "Spectre")
						.Set(e => e.Title, "Commander")
						.Update();

				Console.WriteLine(value);
			}
		}

		static void SelfUpdateTest()
		{
			using (var db = new NorthwindDB())
			{
				var value =
					db.Employee
						.Where(e => e.Title == "Spectre")
						.Set(e => e.HireDate, e => e.HireDate.Value.AddDays(10))
						.Update();

				Console.WriteLine(value);
			}
		}

		static void DeleteTest1()
		{
			using (var db = new NorthwindDB())
			{
				var value = db.Employee.Delete(e => e.Title == "Spectre");

				Console.WriteLine(value);
			}
		}

		static void DeleteTest2()
		{
			using (var db = new NorthwindDB())
			{
				var value =
					db.Employee
						.Where(e => e.Title == "Spectre")
						.Delete();

				Console.WriteLine(value);
			}
		}

		static void SqlLengthTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from c in db.Customer
					where Sql.Length(c.ContactName) > 5
					select c.ContactName;

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void SqlRoundCompare1Test()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from o in db.Order
					where Sql.Round(o.Freight) >= 10
					select o.Freight;

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void SqlSelectLengthTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from c in db.Customer
					select Sql.Length(c.ContactName);

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static void SqlSelectLengthAsSqlTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from c in db.Customer
					select Sql.AsSql(Sql.Length(c.ContactName));

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static decimal? RoundToEven(decimal? value)
		{
			return
				value - Sql.Floor(value) == 0.5m && Sql.Floor(value) % 2 == 0?
					Sql.Floor(value) :
					Sql.Round(value);
		}

		static void RoundToEvenTest()
		{
			Expressions.MapMember<decimal?,decimal?>(
				value => RoundToEven(value),
				value =>
					value - Sql.Floor(value) == 0.5m && Sql.Floor(value) % 2 == 0?
						Sql.Floor(value) :
						Sql.Round(value));

			using (var db = new NorthwindDB())
			{
				var query =
					from o in db.Order
					let sum = o.OrderDetails.Sum(d => d.Quantity * d.UnitPrice)
					where RoundToEven(sum) >= 10
					select o.Freight;

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		[MethodExpression("OrderCountExpression")]
		static int OrderCount(Customer customer, string region)
		{
			throw new InvalidOperationException();
		}

		static Expression<Func<Customer,string,int>> OrderCountExpression()
		{
			return (customer, region) => customer.Orders.Count(o => o.ShipRegion == region);
		}

		static void MethodExpressionTest()
		{
			using (var db = new NorthwindDB())
			{
				var query =
					from c in db.Customer
					select new
					{
						sum1 = OrderCount(c, "SP"),
						sum2 = OrderCount(c, "NM")
					};

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}

		static Func<NorthwindDB,int,IEnumerable<Employee>> _query =
			CompiledQuery.Compile<NorthwindDB,int,IEnumerable<Employee>>((db, n) =>
				from e in db.Employee
				where e.EmployeeID > n
				orderby e.LastName, e.FirstName
				select e
			);

		static void CompiledQueryTest()
		{
			using (var db = new NorthwindDB())
			{
				var query = _query(db, 5);

				foreach (var item in query)
				{
					Console.WriteLine(item);
				}
			}
		}
	}
}

