using System;
using System.Data;
using System.Data.SqlClient;
using NorthwindAPI.BusinessObject;
using System.Collections.Generic;
using System.Threading.Tasks;
 
namespace NorthwindAPI.DataLayer.Base
{
     /// <summary>
     /// Base class for ProductsDataLayer.  Do not make changes to this class,
     /// instead, put additional code in the ProductsDataLayer class
     /// </summary>
     internal class ProductsDataLayerBase
     {
         // constructor
         internal ProductsDataLayerBase()
         {
         }
 
         /// <summary>
         /// Selects a record by primary key(s)
         /// </summary>
         internal static async Task<Products> SelectByPrimaryKeyAsync(int productID)
         {
              Products objProducts = null;
              string dynamicSqlScript = ProductsSql.SelectByPrimaryKey();
 
              using (SqlConnection connection = new SqlConnection(AppSettings.GetConnectionString()))
              {
                  connection.Open();
 
                  using (SqlCommand command = new SqlCommand(dynamicSqlScript, connection))
                  {
                      command.CommandType = CommandType.Text;
 
                      // parameters
                      command.Parameters.AddWithValue("@productID", productID);
 
                      using (SqlDataAdapter da = new SqlDataAdapter(command))
                      {
                          DataTable dt = new DataTable();
                          await Task.Run(() => da.Fill(dt));
 
                          if (dt != null)
                          {
                              if (dt.Rows.Count > 0)
                              {
                                  objProducts = CreateProductsFromDataRowShared(dt.Rows[0]);
                              }
                          }
                      }
                  }
              }
 
              return objProducts;
         }
 
         /// <summary>
         /// Gets the total number of records in the Products table
         /// </summary>
         internal static async Task<int> GetRecordCountAsync()
         {
             string sql = ProductsSql.GetRecordCount();
             return await GetRecordCountSharedAsync(null, null, null, false, sql);
         }
 
         /// <summary>
         /// Gets the total number of records in the Products table by SupplierID
         /// </summary>
         internal static async Task<int> GetRecordCountBySupplierIDAsync(int? supplierID)
         {
             string sql = ProductsSql.GetRecordCountBySupplierID();
             return await GetRecordCountSharedAsync(null, "supplierID", supplierID, false, sql);
         }
 
         /// <summary>
         /// Gets the total number of records in the Products table by CategoryID
         /// </summary>
         internal static async Task<int> GetRecordCountByCategoryIDAsync(int? categoryID)
         {
             string sql = ProductsSql.GetRecordCountByCategoryID();
             return await GetRecordCountSharedAsync(null, "categoryID", categoryID, false, sql);
         }
 
         internal static async Task<int> GetRecordCountSharedAsync(string storedProcName = null, string param = null, object paramValue = null, bool isUseStoredProc = true, string dynamicSqlScript = null)
         {
              int recordCount = 0;
 
              using (SqlConnection connection = new SqlConnection(AppSettings.GetConnectionString()))
              {
                  connection.Open();
 
                  using (SqlCommand command = new SqlCommand(dynamicSqlScript, connection))
                  {
                      command.CommandType = CommandType.Text;
 
                      if (paramValue is null)
                          paramValue = DBNull.Value;
 
                      // parameters
                      switch (param)
                      {
                          case "supplierID":
                              command.Parameters.AddWithValue("@supplierID", paramValue);
                              break;
                          case "categoryID":
                              command.Parameters.AddWithValue("@categoryID", paramValue);
                              break;
                          default:
                              break;
                      }
 
                      using (SqlDataAdapter da = new SqlDataAdapter(command))
                      {
                          DataTable dt = new DataTable();
                          await Task.Run(() => da.Fill(dt));
 
                          if (dt != null)
                          {
                              if (dt.Rows.Count > 0)
                              {
                                  recordCount = (int)dt.Rows[0]["RecordCount"];
                              }
                          }
                      }
                  }
              }
 
              return recordCount;
         }
 
         /// <summary>
         /// Gets the total number of records in the Products table based on search parameters
         /// </summary>
         internal static async Task<int> GetRecordCountDynamicWhereAsync(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued)
         {
              int recordCount = 0;
              string dynamicSqlScript = ProductsSql.GetRecordCountDynamicWhere();
 
              using (SqlConnection connection = new SqlConnection(AppSettings.GetConnectionString()))
              {
                  connection.Open();
 
                  using (SqlCommand command = new SqlCommand(dynamicSqlScript, connection))
                  {
                      command.CommandType = CommandType.Text;
 
                      // search parameters
                      AddSearchCommandParamsShared(command, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued);
 
                      using (SqlDataAdapter da = new SqlDataAdapter(command))
                      {
                          DataTable dt = new DataTable();
                          await Task.Run(() => da.Fill(dt));
 
                          if (dt != null)
                          {
                              if (dt.Rows.Count > 0)
                              {
                                  recordCount = (int)dt.Rows[0]["RecordCount"];
                              }
                          }
                      }
                  }
              }
 
              return recordCount;
         }
 
         /// <summary>
         /// Selects Products records sorted by the sortByExpression and returns records from the startRowIndex with rows (# of rows)
         /// </summary>
         internal static async Task<List<Products>> SelectSkipAndTakeAsync(string sortByExpression, int startRowIndex, int rows)
         {
             string dynamicSqlScript = ProductsSql.SelectSkipAndTake();
             return await SelectSharedAsync(dynamicSqlScript, null, null, sortByExpression, startRowIndex, rows);
         }
 
         /// <summary>
         /// Selects records by SupplierID as a collection (List) of Products sorted by the sortByExpression.
         /// </summary>
         internal static async Task<List<Products>> SelectSkipAndTakeBySupplierIDAsync(string sortByExpression, int startRowIndex, int rows, int? supplierID)
         {
             string dynamicSqlScript = ProductsSql.SelectSkipAndTakeBySupplierID();
             return await SelectSharedAsync(dynamicSqlScript, "supplierID", supplierID, sortByExpression, startRowIndex, rows);
         }
 
         /// <summary>
         /// Selects records by CategoryID as a collection (List) of Products sorted by the sortByExpression.
         /// </summary>
         internal static async Task<List<Products>> SelectSkipAndTakeByCategoryIDAsync(string sortByExpression, int startRowIndex, int rows, int? categoryID)
         {
             string dynamicSqlScript = ProductsSql.SelectSkipAndTakeByCategoryID();
             return await SelectSharedAsync(dynamicSqlScript, "categoryID", categoryID, sortByExpression, startRowIndex, rows);
         }
 
         /// <summary>
         /// Selects Products records sorted by the sortByExpression and returns records from the startRowIndex with rows (# of records) based on search parameters
         /// </summary>
         internal static async Task<List<Products>> SelectSkipAndTakeDynamicWhereAsync(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued, string sortByExpression, int startRowIndex, int rows)
         {
              List<Products> objProductsCol = null;
              string dynamicSqlScript = ProductsSql.SelectSkipAndTakeDynamicWhere();
 
              using (SqlConnection connection = new SqlConnection(AppSettings.GetConnectionString()))
              {
                  connection.Open();
 
                  using (SqlCommand command = new SqlCommand(dynamicSqlScript, connection))
                  {
                      command.CommandType = CommandType.Text;
 
                      // select, skip, take, sort parameters
                      command.Parameters.AddWithValue("@start", startRowIndex);
                      command.Parameters.AddWithValue("@numberOfRows", rows);
                      command.Parameters.AddWithValue("@sortByExpression", sortByExpression);
 
                      // search parameters
                      AddSearchCommandParamsShared(command, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued);
 
                      using (SqlDataAdapter da = new SqlDataAdapter(command))
                      {
                          DataTable dt = new DataTable();
                          await Task.Run(() => da.Fill(dt));
 
                          if (dt != null)
                          {
                              if (dt.Rows.Count > 0)
                              {
                                  objProductsCol = new List<Products>();
 
                                  foreach (DataRow dr in dt.Rows)
                                  {
                                      Products objProducts = CreateProductsFromDataRowShared(dr);
                                      objProductsCol.Add(objProducts);
                                  }
                              }
                          }
                      }
                  }
              }
 
              return objProductsCol;
         }
 
         /// <summary>
         /// Selects all Products
         /// </summary>
         internal static async Task<List<Products>> SelectAllAsync()
         {
             string dynamicSqlScript = ProductsSql.SelectAll();
             return await SelectSharedAsync(dynamicSqlScript, String.Empty, null);
         }
 
         /// <summary>
         /// Selects records based on the passed filters as a collection (List) of Products.
         /// </summary>
         internal static async Task<List<Products>> SelectAllDynamicWhereAsync(int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued)
         {
              List<Products> objProductsCol = null;
              string dynamicSqlScript = ProductsSql.SelectAllDynamicWhere();
 
              using (SqlConnection connection = new SqlConnection(AppSettings.GetConnectionString()))
              {
                  connection.Open();
 
                  using (SqlCommand command = new SqlCommand(dynamicSqlScript, connection))
                  {
                      command.CommandType = CommandType.Text;
 
                      // search parameters
                      AddSearchCommandParamsShared(command, productID, productName, supplierID, categoryID, quantityPerUnit, unitPrice, unitsInStock, unitsOnOrder, reorderLevel, discontinued);
 
                      using (SqlDataAdapter da = new SqlDataAdapter(command))
                      {
                          DataTable dt = new DataTable();
                          await Task.Run(() => da.Fill(dt));
 
                          if (dt != null)
                          {
                              if (dt.Rows.Count > 0)
                              {
                                  objProductsCol = new List<Products>();
 
                                  foreach (DataRow dr in dt.Rows)
                                  {
                                      Products objProducts = CreateProductsFromDataRowShared(dr);
                                      objProductsCol.Add(objProducts);
                                  }
                              }
                          }
                      }
                  }
              }
 
              return objProductsCol;
         }
 
         /// <summary>
         /// Selects all Products by Suppliers, related to column SupplierID
         /// </summary>
         internal static async Task<List<Products>> SelectProductsCollectionBySupplierIDAsync(int supplierID)
         {
             string dynamicSqlScript = ProductsSql.SelectAllBySupplierID();
             return await SelectSharedAsync(dynamicSqlScript, "supplierID", supplierID);
         }
 
         /// <summary>
         /// Selects all Products by Categories, related to column CategoryID
         /// </summary>
         internal static async Task<List<Products>> SelectProductsCollectionByCategoryIDAsync(int categoryID)
         {
             string dynamicSqlScript = ProductsSql.SelectAllByCategoryID();
             return await SelectSharedAsync(dynamicSqlScript, "categoryID", categoryID);
         }
 
         /// <summary>
         /// Selects ProductID and ProductName columns for use with a DropDownList web control
         /// </summary>
         internal static async Task<List<Products>> SelectProductsDropDownListDataAsync()
         {
              List<Products> objProductsCol = null;
              string dynamicSqlScript = ProductsSql.SelectProductsDropDownListData();
 
              using (SqlConnection connection = new SqlConnection(AppSettings.GetConnectionString()))
              {
                  connection.Open();
 
                  using (SqlCommand command = new SqlCommand(dynamicSqlScript, connection))
                  {
                      command.CommandType = CommandType.Text;
 
                      using (SqlDataAdapter da = new SqlDataAdapter(command))
                      {
                          DataTable dt = new DataTable();
                          await Task.Run(() => da.Fill(dt));
 
                          if (dt != null)
                          {
                              if (dt.Rows.Count > 0)
                              {
                                  objProductsCol = new List<Products>();
 
                                  foreach (DataRow dr in dt.Rows)
                                  {
                                      Products objProducts = new Products();
                                      objProducts.ProductID = (int)dr["ProductID"];
                                      objProducts.ProductName = (string)(dr["ProductName"]);
 
                                      objProductsCol.Add(objProducts);
                                  }
                              }
                          }
                      }
                  }
              }
 
              return objProductsCol;
         }
 
         internal static async Task<List<Products>> SelectSharedAsync(string dynamicSqlScript, string param, object paramValue, string sortByExpression = null, int? startRowIndex = null, int? rows = null)
         {
              List<Products> objProductsCol = null;
 
              using (SqlConnection connection = new SqlConnection(AppSettings.GetConnectionString()))
              {
                  connection.Open();
 
                  using (SqlCommand command = new SqlCommand(dynamicSqlScript, connection))
                  {
                      command.CommandType = CommandType.Text;
 
                      if (paramValue is null)
                          paramValue = DBNull.Value;
 
                      // select, skip, take, sort parameters
                      if (!String.IsNullOrEmpty(sortByExpression) && startRowIndex != null && rows != null)
                      {
                          command.Parameters.AddWithValue("@start", startRowIndex.Value);
                          command.Parameters.AddWithValue("@numberOfRows", rows.Value);
                          command.Parameters.AddWithValue("@sortByExpression", sortByExpression);
                      }
 
                      // parameters
                      switch (param)
                      {
                          case "supplierID":
                              command.Parameters.AddWithValue("@supplierID", paramValue);
                              break;
                          case "categoryID":
                              command.Parameters.AddWithValue("@categoryID", paramValue);
                              break;
                          default:
                              break;
                      }
 
                      using (SqlDataAdapter da = new SqlDataAdapter(command))
                      {
                          DataTable dt = new DataTable();
                          await Task.Run(() => da.Fill(dt));
 
                          if (dt != null)
                          {
                              if (dt.Rows.Count > 0)
                              {
                                  objProductsCol = new List<Products>();
 
                                  foreach (DataRow dr in dt.Rows)
                                  {
                                      Products objProducts = CreateProductsFromDataRowShared(dr);
                                      objProductsCol.Add(objProducts);
                                  }
                              }
                          }
                      }
                  }
              }
 
              return objProductsCol;
         }
 
         /// <summary>
         /// Inserts a record
         /// </summary>
         internal static async Task<int> InsertAsync(Products objProducts)
         {
             return  await InsertUpdateAsync(objProducts, false);
         }
 
         /// <summary>
         /// Updates a record
         /// </summary>
         internal static async Task UpdateAsync(Products objProducts)
         {
             await InsertUpdateAsync(objProducts, true);
         }
 
         private static async Task<int> InsertUpdateAsync(Products objProducts, bool isUpdate)
         {
              int newlyCreatedProductID = objProducts.ProductID;
 
              string dynamicSqlScript = String.Empty;
 
              if (isUpdate)
                  dynamicSqlScript = ProductsSql.Update();
              else
                  dynamicSqlScript = ProductsSql.Insert();
 
              object supplierID = objProducts.SupplierID;
              object categoryID = objProducts.CategoryID;
              object quantityPerUnit = objProducts.QuantityPerUnit;
              object unitPrice = objProducts.UnitPrice;
              object unitsInStock = objProducts.UnitsInStock;
              object unitsOnOrder = objProducts.UnitsOnOrder;
              object reorderLevel = objProducts.ReorderLevel;
 
              if (objProducts.SupplierID is null)
                  supplierID = System.DBNull.Value;
 
              if (objProducts.CategoryID is null)
                  categoryID = System.DBNull.Value;
 
              if (String.IsNullOrEmpty(objProducts.QuantityPerUnit))
                  quantityPerUnit = System.DBNull.Value;
 
              if (objProducts.UnitPrice is null)
                  unitPrice = System.DBNull.Value;
 
              if (objProducts.UnitsInStock is null)
                  unitsInStock = System.DBNull.Value;
 
              if (objProducts.UnitsOnOrder is null)
                  unitsOnOrder = System.DBNull.Value;
 
              if (objProducts.ReorderLevel is null)
                  reorderLevel = System.DBNull.Value;
 
              using (SqlConnection connection = new SqlConnection(AppSettings.GetConnectionString()))
              {
                  connection.Open();
 
                  using (SqlCommand command = new SqlCommand(dynamicSqlScript, connection))
                  {
                      command.CommandType = CommandType.Text;
 
                      // parameters
                      if (isUpdate)
                      {
                          // for update only
                          command.Parameters.AddWithValue("@productID", objProducts.ProductID);
                      }
 
                      command.Parameters.AddWithValue("@productName", objProducts.ProductName);
                      command.Parameters.AddWithValue("@supplierID", supplierID);
                      command.Parameters.AddWithValue("@categoryID", categoryID);
                      command.Parameters.AddWithValue("@quantityPerUnit", quantityPerUnit);
                      command.Parameters.AddWithValue("@unitPrice", unitPrice);
                      command.Parameters.AddWithValue("@unitsInStock", unitsInStock);
                      command.Parameters.AddWithValue("@unitsOnOrder", unitsOnOrder);
                      command.Parameters.AddWithValue("@reorderLevel", reorderLevel);
                      command.Parameters.AddWithValue("@discontinued", objProducts.Discontinued);
 
                      if (isUpdate)
                          await command.ExecuteNonQueryAsync();
                      else
                          newlyCreatedProductID = (int)command.ExecuteScalar();
                  }
              }
 
              return newlyCreatedProductID;
         }
 
         /// <summary>
         /// Deletes a record based on primary key(s)
         /// </summary>
         internal static async Task DeleteAsync(int productID)
         {
              string dynamicSqlScript = ProductsSql.Delete();
 
              using (SqlConnection connection = new SqlConnection(AppSettings.GetConnectionString()))
              {
                  connection.Open();
 
                  using (SqlCommand command = new SqlCommand(dynamicSqlScript, connection))
                  {
                      command.CommandType = CommandType.Text;
 
                      // parameters
                      command.Parameters.AddWithValue("@productID", productID);
 
                      // execute
                      await command.ExecuteNonQueryAsync();
                  }
              }
         }
 
         /// <summary>
         /// Adds search parameters to the Command object
         /// </summary>
         private static void AddSearchCommandParamsShared(SqlCommand command, int? productID, string productName, int? supplierID, int? categoryID, string quantityPerUnit, decimal? unitPrice, Int16? unitsInStock, Int16? unitsOnOrder, Int16? reorderLevel, bool? discontinued)
         {
              if(productID != null)
                  command.Parameters.AddWithValue("@productID", productID);
              else
                  command.Parameters.AddWithValue("@productID", System.DBNull.Value);
 
              if(!String.IsNullOrEmpty(productName))
                  command.Parameters.AddWithValue("@productName", productName);
              else
                  command.Parameters.AddWithValue("@productName", System.DBNull.Value);
 
              if(supplierID != null)
                  command.Parameters.AddWithValue("@supplierID", supplierID);
              else
                  command.Parameters.AddWithValue("@supplierID", System.DBNull.Value);
 
              if(categoryID != null)
                  command.Parameters.AddWithValue("@categoryID", categoryID);
              else
                  command.Parameters.AddWithValue("@categoryID", System.DBNull.Value);
 
              if(!String.IsNullOrEmpty(quantityPerUnit))
                  command.Parameters.AddWithValue("@quantityPerUnit", quantityPerUnit);
              else
                  command.Parameters.AddWithValue("@quantityPerUnit", System.DBNull.Value);
 
              if(unitPrice != null)
                  command.Parameters.AddWithValue("@unitPrice", unitPrice);
              else
                  command.Parameters.AddWithValue("@unitPrice", System.DBNull.Value);
 
              if(unitsInStock != null)
                  command.Parameters.AddWithValue("@unitsInStock", unitsInStock);
              else
                  command.Parameters.AddWithValue("@unitsInStock", System.DBNull.Value);
 
              if(unitsOnOrder != null)
                  command.Parameters.AddWithValue("@unitsOnOrder", unitsOnOrder);
              else
                  command.Parameters.AddWithValue("@unitsOnOrder", System.DBNull.Value);
 
              if(reorderLevel != null)
                  command.Parameters.AddWithValue("@reorderLevel", reorderLevel);
              else
                  command.Parameters.AddWithValue("@reorderLevel", System.DBNull.Value);
 
              if(discontinued != null)
                  command.Parameters.AddWithValue("@discontinued", discontinued);
              else
                  command.Parameters.AddWithValue("@discontinued", System.DBNull.Value);
 
         }
 
         /// <summary>
         /// Creates a Products object from the passed data row
         /// </summary>
         private static Products CreateProductsFromDataRowShared(DataRow dr)
         {
             Products objProducts = new Products();
 
             objProducts.ProductID = (int)dr["ProductID"];
             objProducts.ProductName = dr["ProductName"].ToString();
 
             if (dr["SupplierID"] != System.DBNull.Value)
                 objProducts.SupplierID = (int)dr["SupplierID"];
             else
                 objProducts.SupplierID = null;
 
 
             if (dr["CategoryID"] != System.DBNull.Value)
                 objProducts.CategoryID = (int)dr["CategoryID"];
             else
                 objProducts.CategoryID = null;
 
 
             if (dr["QuantityPerUnit"] != System.DBNull.Value)
                 objProducts.QuantityPerUnit = dr["QuantityPerUnit"].ToString();
             else
                 objProducts.QuantityPerUnit = null;
 
             if (dr["UnitPrice"] != System.DBNull.Value)
                 objProducts.UnitPrice = (decimal)dr["UnitPrice"];
             else
                 objProducts.UnitPrice = null;
 
             if (dr["UnitsInStock"] != System.DBNull.Value)
                 objProducts.UnitsInStock = (Int16)dr["UnitsInStock"];
             else
                 objProducts.UnitsInStock = null;
 
             if (dr["UnitsOnOrder"] != System.DBNull.Value)
                 objProducts.UnitsOnOrder = (Int16)dr["UnitsOnOrder"];
             else
                 objProducts.UnitsOnOrder = null;
 
             if (dr["ReorderLevel"] != System.DBNull.Value)
                 objProducts.ReorderLevel = (Int16)dr["ReorderLevel"];
             else
                 objProducts.ReorderLevel = null;
             objProducts.Discontinued = (bool)dr["Discontinued"];
 
             return objProducts;
         }
     }
}