using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using System.Xml;
using System.Xml.Linq;
using System.Xml.Serialization;

namespace ServiceFinal
{
    class ProductService : IProductService
    {

        public Product GetProduct(int id)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var productEntity = (from p in context.Products
                                     where p.ProductID == id
                                     select p).FirstOrDefault();
                if (productEntity == null)
                {
                    throw new Exception("Invalid product ID");
                }
                else
                    return productEntity;
            }
        }

        public List<Product> GetProducts()
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var list = (from p in context.Products select p).ToList();
                return list;
            }
        }

        public void SetProduct(string Name, string Color, string ProductNumber, decimal StandardCost, decimal ListPrice, int ProductCategoryID, int ProductModelID)
        {
            try
            {
                AdventureWorksLT2008R2Entities context = new AdventureWorksLT2008R2Entities();
                Product p = new Product()
                {
                    Color = Color,
                    Name = Name,
                    ProductNumber = ProductNumber,
                    StandardCost = StandardCost,
                    ListPrice = ListPrice,
                    ProductCategoryID = ProductCategoryID,
                    ProductModelID = ProductModelID
                };
                context.Products.Add(p);
                context.SaveChanges();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.InnerException);
            }
        }

        public bool IsValid(string NameText, string ProductNumberText)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var list = (from p in context.Products select p).ToList();
                foreach (var a in list)
                {
                    if (a.Name.Equals(NameText) || a.ProductNumber.Equals(ProductNumberText))
                    {
                        return false;
                    }
                }
                return true;
            }
        }

        public void DeleteProduct(int id)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var productToRemove = context.Products.FirstOrDefault(a => a.ProductID == id);

                if (productToRemove != null)
                {
                    context.Products.Remove(productToRemove);
                }
                context.SaveChanges();
            }
        }

        public void UpdateProduct(Product prod)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var product = context.Products.Where(p => p.ProductID == prod.ProductID).FirstOrDefault();
                if (product != null)
                {
                    product.Name = prod.Name;
                    product.ProductNumber = prod.ProductNumber;
                    product.Color = prod.Color;
                    product.ListPrice = prod.ListPrice;
                    product.StandardCost = prod.StandardCost;
                    product.ProductCategoryID = prod.ProductCategoryID;
                    product.ProductModelID = prod.ProductModelID;
                }
                context.SaveChanges();
            }

        }

        public void DeleteProducts(List<int> ids)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                context.Products.RemoveRange(context.Products.Where(p => ids.Contains(p.ProductID)));
                context.SaveChanges();
            }
        }

        public void InsertProducts(List<Product> list)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                foreach (var item in list)
                {
                    context.Products.Add(item);
                }
                context.SaveChanges();
            }
        }

        public HashSet<string> SelectCategoryName()
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var categoryNameAll = (from p in context.Products
                                       join c in context.ProductCategories on p.ProductCategoryID equals c.ProductCategoryID
                                       select c.Name).ToList();
                HashSet<string> categoryName = new HashSet<string>();
                foreach (var name in categoryNameAll)
                {
                    categoryName.Add(name);
                }
                return categoryName;
            }

        }

        public HashSet<string> SelectModel()
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var categoryNameAll = (from p in context.Products
                                       join m in context.ProductModels on p.ProductModelID equals m.ProductModelID
                                       select m.Name).ToList();
                HashSet<string> categoryName = new HashSet<string>();
                foreach (var name in categoryNameAll)
                {
                    categoryName.Add(name);
                }
                return categoryName;
            }
        }

        public string GetCategoryName(int? id)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                string categName = String.Empty;
                try
                {
                    var categoryName = (from c in context.ProductCategories
                                        join p in context.Products on c.ProductCategoryID equals p.ProductCategoryID
                                        where c.ProductCategoryID == id
                                        select c.Name).First().ToString();
                    categName = categoryName;
                }
                catch (Exception ex)
                {
                    return "None";
                }
                return categName;
            }
        }

        public string GetModelName(int? id)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                string prodName = String.Empty;
                try
                {
                    var productName = (from p in context.Products
                                       join m in context.ProductModels on p.ProductModelID equals m.ProductModelID
                                       where m.ProductModelID == id
                                       select m.Name).First().ToString();
                    prodName = productName;
                }
                catch (Exception ex)
                {
                    return "None";
                }
                return prodName;
            }
        }

        public int GetCategoryID(string category)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                int ID = 0;
                try
                {
                    var categoryID = (from c in context.ProductCategories
                                      join p in context.Products on c.ProductCategoryID equals p.ProductCategoryID
                                      where c.Name == category
                                      select c.ProductCategoryID).First();
                    ID = categoryID;
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
                return ID;
            }

        }

        public int GetModelID(string model)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var modelID = (from m in context.ProductModels
                               join p in context.Products on m.ProductModelID equals p.ProductModelID
                               where m.Name == model
                               select m.ProductModelID).First();
                return modelID;
            }

        }

        public List<SalesOrderHeader> GetSalesOrder()
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var list = context.SalesOrderHeaders.ToList();
                return list;
            }
        }

        public SalesOrderHeader GetOrder(int id)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var productEntity = (from order in context.SalesOrderHeaders
                                     where order.SalesOrderID == id
                                     select order).FirstOrDefault();
                if (productEntity == null)
                {
                    throw new Exception("Invalid order ID");
                }
                else
                    return productEntity;
            }
        }

        public List<Product> GetTheWantedProduct(int id)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {

                var productToDisplay = (from oh in context.SalesOrderHeaders
                                        join od in context.SalesOrderDetails
                                        on oh.SalesOrderID equals od.SalesOrderID
                                        where oh.SalesOrderID == id
                                        join p in context.Products
                                        on od.ProductID equals p.ProductID
                                        select p).ToList();
                return productToDisplay;
            }

        }
        public Customer AddCustomer(string Title, string FirstName, string LastName)
        {

            using (var context = new AdventureWorksLT2008R2Entities())
            {
                Customer customer = new Customer()
                {
                    Title = Title,
                    FirstName = FirstName,
                    LastName = LastName
                };
                context.Customers.Add(customer);
                context.SaveChanges();
                return customer;
            }
        }

        public Address AddAddress(string Street, string City, string StateProvince, string Country, string PostalCode)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                Address address = new Address()
                {
                    AddressLine1 = Street,
                    City = City,
                    StateProvince = StateProvince,
                    CountryRegion = Country,
                    PostalCode = PostalCode
                };
                context.Addresses.Add(address);
                context.SaveChanges();
                return address;
            }
        }

        public SalesOrderHeader AddOrderHeader(int customerID, int addressID)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                SalesOrderHeader soh = new SalesOrderHeader()
                {
                    CustomerID = customerID,
                    ShipToAddressID = addressID,
                    BillToAddressID = addressID
                };
                context.SalesOrderHeaders.Add(soh);
                context.SaveChanges();
                return soh;
            }
        }

        public void AddOrderDetail(List<string> products, int salesOrderID)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                foreach (var product in products)
                {
                    SalesOrderDetail sod = new SalesOrderDetail()
                    {
                        SalesOrderID = salesOrderID,
                        ProductID = context.Products.First(a => a.Name == product).ProductID,
                        UnitPrice = context.Products.First(a => a.Name == product).ListPrice,
                        UnitPriceDiscount = 0
                    };
                    context.SalesOrderDetails.Add(sod);
                    context.SaveChanges();
                }
            }
        }

        public SalesOrderHeader GetOrderHeader(int id)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var orderEntity = (from o in context.SalesOrderHeaders
                                   where o.SalesOrderID == id
                                   select o).FirstOrDefault();
                if (orderEntity == null)
                {
                    throw new Exception("Invalid order ID");
                }
                else
                    return orderEntity;
            }
        }

        public Customer GetCustomer(int id)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var customer = (from order in context.SalesOrderHeaders
                                where order.SalesOrderID == id
                                join cust in context.Customers
                                on order.CustomerID equals cust.CustomerID
                                select cust).First();
                return customer;
            }
        }

        public Address GetTheAddress(int id)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var address = (from order in context.SalesOrderHeaders
                               where order.SalesOrderID == id
                               join add in context.Addresses
                               on order.ShipToAddressID equals add.AddressID
                               select add).First();
                return address;
            }
        }

        public List<string> GetTheOrderedProducts(int id)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var orderedProducts = (from order in context.SalesOrderHeaders
                                       where order.SalesOrderID == id
                                       join details in context.SalesOrderDetails
                                       on order.SalesOrderID equals details.SalesOrderID
                                       join products in context.Products
                                       on details.ProductID equals products.ProductID
                                       select products.Name).ToList();
                return orderedProducts;
            }
        }
        public void DeleteOrders(List<int> ids)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                context.SalesOrderHeaders.RemoveRange(context.SalesOrderHeaders.Where(order => ids.Contains(order.SalesOrderID)));
                context.SaveChanges();
            }
        }

        public void ModifyCustomer(int id, string firstName, string lastName)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var customer = (from order in context.SalesOrderHeaders
                                where order.SalesOrderID == id
                                join cust in context.Customers
                                on order.CustomerID equals cust.CustomerID
                                select cust).First();
                customer.FirstName = firstName;
                customer.LastName = lastName;
                context.SaveChanges();
            }

        }

        public void ModifyAddress(int id, string Street, string City, string StateProvince, string Country, string PostalCode)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var address = (from order in context.SalesOrderHeaders
                               where order.SalesOrderID == id
                               join addr in context.Addresses
                               on order.ShipToAddressID equals addr.AddressID
                               select addr).First();
                address.AddressLine1 = Street;
                address.City = City;
                address.StateProvince = StateProvince;
                address.CountryRegion = Country;
                address.PostalCode = PostalCode;
                context.SaveChanges();
            }
        }
        public void ModifyTheOrderedProducts(int salesOrderID, List<string> newProducts, List<string> checkedProducts)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var productsToAdd = AddOrderedProducts(newProducts, checkedProducts);
                var productsToDelete = DeleteTheUnwantedProducts(newProducts, checkedProducts);

                foreach (var product in productsToAdd)
                {
                    SalesOrderDetail sod = sod = new SalesOrderDetail();
                    sod.SalesOrderID = salesOrderID;
                    sod.ProductID = context.Products.First(a => a.Name == product).ProductID;
                    sod.UnitPrice = context.Products.First(a => a.Name == product).ListPrice;
                    sod.UnitPriceDiscount = 0;
                    context.SalesOrderDetails.Add(sod);
                    context.SaveChanges();

                }
                foreach (var product in productsToDelete)
                {

                    var productID = context.Products.First(p => p.Name == product).ProductID;

                    var salesDetailIDToDelete = context.SalesOrderDetails.First(detail => detail.ProductID == productID
                                                && detail.SalesOrderID == salesOrderID).SalesOrderDetailID;

                    var salesOrder = context.SalesOrderDetails.Where(detail => detail.SalesOrderDetailID == salesDetailIDToDelete)
                                                              .FirstOrDefault();
                    if (salesOrder != null)
                    {
                        context.SalesOrderDetails.Remove(salesOrder);
                        context.SaveChanges();
                    }
                }

            }
        }

        private List<string> AddOrderedProducts(List<string> newProducts, List<string> checkedProducts)
        {
            var productsToAdd = new List<string>();
            foreach (var product in newProducts)
            {
                if (checkedProducts.Contains(product) == false)
                {
                    productsToAdd.Add(product);
                }
            }
            return productsToAdd;
        }

        private List<string> DeleteTheUnwantedProducts(List<string> newProducts, List<string> checkedProducts)
        {
            var productsToDelete = new List<string>();
            foreach (var product in checkedProducts)
            {
                if (newProducts.Contains(product) == false)
                {
                    productsToDelete.Add(product);
                }
            }
            return productsToDelete;
        }

        public List<User> GetUsers()
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var list = (from user in context.Users select user).ToList();
                return list;
            }
        }

        public User AddUser(string username, string password)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                User user = new User()
                {
                    Username = username,
                    Password = Hash(password)
                };
                context.Users.Add(user);
                context.SaveChanges();
                return user;
            }
        }

        private string Hash(string password)
        {
            var bytes = new UTF8Encoding().GetBytes(password);
            var hashBytes = System.Security.Cryptography.MD5.Create().ComputeHash(bytes);
            return Convert.ToBase64String(hashBytes);
        }

        public string GetUserPassword(string username)
        {
            using (var context = new AdventureWorksLT2008R2Entities())
            {
                var pass = context.Users.First(u => u.Username == username).Password;
                return pass;
            }
        }
    }
}